اکسل فرمول بندی فرمول محرمانه

اضافه کردن قالب بندی شرطی در اکسل به شما امکان می دهد گزینه های مختلف قالب بندی را به یک سلول یا محدوده سلول هایی که شرایط خاصی را تعیین می کنند اعمال کنید.

گزینه های قالب بندی تنها زمانی اعمال می شود که سلول های انتخاب شده مطابق با این شرایط تنظیم شده باشند.

گزینه های قالب بندی که می توانند مورد استفاده قرار گیرند عبارتند از فونت و تغییر رنگ پس زمینه، سبک فونت، مرز های سلولی و اضافه کردن فرمت به داده ها.

از سال 2007 اکسل، اکسل تعدادی از گزینه های داخلی را برای شرایطی که معمولا مورد استفاده قرار می گیرد، مانند پیدا کردن اعداد که بیشتر یا کمتر از یک مقدار معین یا پیدا کردن اعداد هستند که بالاتر یا کمتر از مقدار متوسط ​​هستند، داشته است .

علاوه بر این گزینه های از پیش تعیین شده، همچنین می توانید قوانین قالب بندی شرطی مشروح را با استفاده از فرمول های اکسل برای آزمایش شرایط مشخص شده توسط کاربر ایجاد کنید.

اعمال قوانین چندگانه

برای بررسی شرایط مختلف، بیش از یک قاعده می تواند به داده های مشابه اعمال شود. برای مثال، داده های بودجه ممکن است شرایطی را تنظیم کنند که تغییرات قالب بندی را اعمال می کند زمانی که سطوح خاصی مانند 50٪، 75٪ و 100٪ کل بودجه صرف شود.

در چنین شرایطی، اکسل ابتدا تعیین می کند که آیا قواعد مختلفی درگیر هستند یا خیر، و اگر چنین باشد، برنامه به ترتیب اولویت تعیین می کند که کدام قاعده قالب بندی شرطی برای داده ها اعمال می شود.

مثال: پیدا کردن داده هایی که بیش از 25٪ و 50٪ با فرمت های شرطی افزایش می یابد

در مثال زیر دو قوانین قالب بندی شرطی متعلق به سلول B2 به B5 اعمال می شود.

همانطور که در تصویر بالا دیده می شود، اگر هر یک از شرایط فوق درست باشد، رنگ پس زمینه سلول یا سلول ها در محدوده B1: B4 تغییر خواهد کرد.

قوانین مورد استفاده برای انجام این کار،

= (A2-B2) / A2> 25٪ = (A2-B2) / A2> 50٪

با استفاده از قالب بندی مشروط وارد خواهد شد. جعبه محاوره Rule Formatting جدید .

وارد کردن داده های آموزشی

  1. داده ها را به سلول های A1 تا C5 وارد کنید، همانطور که در تصویر بالا دیده می شود

توجه داشته باشید: مرحله 3 آموزش، فرمول هایی را برای سلول های C2: C4 اضافه خواهد کرد که تفاوت درصد دقیق بین مقادیر سلول های A2: A5 و B2: B5 را نشان می دهد تا دقت قواعد قالب بندی شرطی را بررسی کنند.

تنظیم قوانین Formatting Condtional

با استفاده از فرمول برای قالب بندی شرطی در اکسل © Ted فرانسوی

همانطور که ذکر شد، قوانین قالب بندی شرطی که برای دو شرایط بررسی می شوند با استفاده از قالب بندی شرطی جعبه محاوره Rule Formatting وارد می شوند.

تنظیم قالب بندی شرطی برای پیدا کردن بیش از 25٪ افزایش

  1. برجسته سلول B2 به B5 در برگه.
  2. روی برگه Home روی نوار کلیک کنید.
  3. برای باز کردن منو کشویی روی نماد قالب بندی قراردادی در نوار کلیک کنید.
  4. گزینه New Rule را انتخاب کنید تا کادر محاوره ای New Formatting Rule را باز کنید که در تصویر بالا دیده می شود.
  5. در نیمه بالای کادر محاوره ای، روی آخرین گزینه کلیک کنید: از یک فرمول برای تعیین سلول هایی که برای قالب بندی استفاده می کنید استفاده کنید.
  6. در نیمه پایین کادر محاوره ای، در مقادیر فرمت که در آن این فرمول درست است: خط را کلیک کنید.
  7. فرمول : = (A2-B2) / A2> 25٪ در فضای ارائه شده
  8. روی دکمه Format کلیک کنید تا کادر کادر Format Cells باز شود.
  9. در این کادر محاوره ای، روی برگه Fill کلیک کنید و یک رنگ پر رنگ آبی رنگ را انتخاب کنید.
  10. برای بستن کادر محاوره ای و بازگشت به برگه، روی OK دو بار کلیک کنید.
  11. در این مرحله رنگ پس زمینه سلولهای B3 و B5 باید آبی باشد.

تنظیم قالب بندی شرطی برای یافتن بیش از 50٪ افزایش

  1. با سلول های B2 تا B5 هنوز انتخاب شده است، مراحل 1 تا 6 را در بالا تکرار کنید.
  2. فرمول: = (A2-B2) / A2> 50٪ در فضای ارائه شده.
  3. روی دکمه Format کلیک کنید تا کادر کادر Format Cells باز شود.
  4. روی برگه Fill کلیک کنید و رنگ پر رنگ را انتخاب کنید.
  5. برای بستن کادر محاوره ای و بازگشت به برگه، روی OK دو بار کلیک کنید.
  6. رنگ پس زمینه سلول B3 هنوز باید آبی باشد که نشان می دهد تفاوت درصد بین اعداد در سلول های A3 و B3 بیشتر از 25٪، اما کمتر از 50٪ و یا برابر آن است.
  7. رنگ پس زمینه سلول B5 باید به قرمز تغییر کند که نشان می دهد تفاوت درصد بین عدد در سلول های A5 و B5 بیش از 50٪ است.

بررسی قوانین قالب بندی شرطی

بررسی قوانین قالب بندی شرطی © Ted فرانسوی

محاسبه٪ تفاوت

برای بررسی اینکه قوانین قالب بندی شرطی وارد شده اند، می توانیم فرمول را به سلول های C2: C5 وارد کنیم که تفاوت درصد دقیق بین اعداد در محدوده A2: A5 و B2: B5 را محاسبه می کند.

  1. بر روی سلول C2 کلیک کنید تا آن سلول فعال باشد.
  2. فرمول = (A2-B2) / A2 را وارد کنید و کلید Enter را در صفحه کلید فشار دهید.
  3. پاسخ 10٪ باید در سلول C2 ظاهر شود، که نشان می دهد که تعداد سلول A2 10٪ بزرگتر از تعداد سلول B2 است.
  4. ممکن است لازم باشد قالب بندی سلول C2 را تغییر دهید تا پاسخ به عنوان یک درصد نمایش داده شود.
  5. از سلول پر شده برای کپی فرمول از سلول C2 به سلول C3 تا C5 استفاده کنید.
  6. پاسخ برای سلول های C3 تا C5 باید: 30٪، 25٪ و 60٪ باشد.
  7. پاسخ در این سلول ها نشان می دهد که قوانین قالب بندی شرطی درست شده اند زیرا تفاوت بین سلول های A3 و B3 بیشتر از 25٪ است و تفاوت بین سلول های A5 و B5 بیش از 50٪ است.
  8. سلول B4 رنگ را تغییر نداد، زیرا تفاوت بین سلول های A4 و B4 برابر با 25٪ است و قانون حکاکی شرطی ما مشخص شده است که یک درصد بیش از 25٪ برای تغییر رنگ پس زمینه به آبی نیاز است.

سفارش اولویت برای قوانین قالب بندی شرطی

مدیر قوانین قالب بندی محموله اکسل © Ted فرانسوی

اعمال قوانین قالب بندی اختیاری

هنگامی که قوانین چندگانه به طیف وسیعی از داده ها اعمال می شود، اکسل ابتدا تعیین می کند که آیا قوانین منجر می شوند یا خیر.

قوانین متضاد هستند که در آن گزینه های قالب بندی انتخاب شده برای هر قانون هر دو نمی توانند به همان داده ها اعمال شوند .

در مثال مورد استفاده در این آموزش، قوانین از هر دو قانون استفاده می کنند همان گزینه قالب بندی - تغییر رنگ پس زمینه سلول.

در شرایطی که قاعده دوم درست است (تفاوت در مقدار بیش از 50٪ بین دو سلول است)، اولین قاعده (تفاوت در مقدار بیش از 25٪) نیز صادق است.

اکسل سفارش اولویت

از آنجا که یک سلول نمی تواند همزمان یک پس زمینه قرمز و آبی داشته باشد، اکسل باید بداند که کدام شرط قالب بندی شرطی باید اعمال شود.

کدام یک از قوانین استفاده می شود، با ترتیب اولویت اکسل تعیین می شود، که بیان می کند که قاعده ای که در لیست در کادر محاوره ای مدیریت قالب بندی شرایط شرطی بالاتر است.

همانطور که در تصویر بالا نشان داده شده است، قانون دوم استفاده شده در این آموزش (= (A2-B2) / A2> 50٪) در لیست بیشتر است و بنابراین بالاتر از قانون اول است.

در نتیجه رنگ پس زمینه سلول B5 به قرمز تغییر می کند.

به طور پیش فرض، قوانین جدید به بالای لیست اضافه می شوند و بنابراین بالاترین امتیاز را دارند.

برای تغییر جهت اولویت، از دکمه های فلش بالا و پایین در کادر محاوره ای که در تصویر بالا مشخص شده استفاده کنید.

اعمال قوانین غیر متضاد

اگر دو یا چند قاعده قالب بندی شرطی در تعارض نباشد، هر دو مورد اعمال می شوند زمانی که شرط هر قاعده تست درست می شود.

اگر اولین قاعده قالب بندی شرطی در مثال ما (= (A2-B2) / A2> 25٪) دامنه سلول B2: B5 را با یک مرزی آبی جایگزین رنگ پسزمینه فریم، دو قاعده قالب بندی شرطی از زمان هر دو فرمت را می توان بدون دخالت با دیگر اعمال کرد.

در نتیجه، سلول B5 هر دو مرز آبی و یک رنگ پس زمینه قرمز را دارد، زیرا تفاوت بین اعداد در سلول های A5 و B5 بیشتر از 25 و 50 درصد است.

قالب بندی شرطی در مقابل قالب بندی منظم

در مورد درگیری های بین قوانین قالب بندی شرطی و گزینه های قالب بندی دستی به صورت دستی، قانون قالب بندی شرط همیشه اهمیت دارد و به جای هر گزینه قالب بندی دستی اضافه می شود.

اگر یک رنگ پسزمینه زرد ابتدا به سلول های B2 تا B5 در مثال اعمال شود، پس از آنکه قوانین قالب بندی مشروط اضافه شدند، فقط سلول های B2 و B4 زرد می شوند.

از آنجا که قوانین قالب بندی شرطی وارد شده به سلول های B3 و B5 اعمال می شوند، رنگ پس زمینه آنها از زرد به آبی و قرمز تغییر می کند.