آموزش ابزار CONDITIONAL FORMATTING در نرم افزار اکسل(قالب بندی شرطی)

ابزار CONDITIONAL FORMATTING در اکسل

آموزش CONDITIONAL FORMATTING : این ابزار برای ایجاد فرمت های شرطی بکار می روند. برای مثال می‌توانیم به یک سلول دستور دهیم اگر عددی در آن وارد شد و آن عدد بزرگتر از ۱۱ باشد رنگ عدد قرمز و اگر کوچکتر از ۱۱ باشد رنگ آن سبز شود. برای توضیح بیشتر این ابزار به طرح چند مثال میپردازیم. ابزار CONDITIONAL FORMATTING  شامل چندین حالت درون خود است که با کلیک بر آن نمایان میشود.

گزینه های فرمتهای شرطی (Conditional Formatting) بصورت زیر می باشند.

Highlight Cells Rules

• Greater than شرط بزرگتر از یک مقدار خاص می باشد Less Than: شرط کوچکتر از یک مقدار خاص می باشد.
• Between شرط بین دو مقدار می باشد.
• Equal To شرط مساوی یک مقدار می باشد.
• Text that Contains شرط حاوی یک متن مشخص باشد. A date Occurring شرط حاوی یک تاریخ مشخص باشد. Duplicate Values شرط حاوی مقادیر تکراری باشد.

Top/Bottom Rules

• % ۱۰ n:،Top% از بیشترین مقادیر یک محدوده را نشان می دهد. (که n از ۱ تا ۱۰۰ است)
• Bottom 10 Items: ده عددی که کمترین مقدار را دارد مشخص می کند.
• Top 10 Items: ده عددی که بیشترین مقدار را دارد مشخص می کند.
• % ۱۰ n:،Bottom% از کمترین مقادیر یک محدوده را نشان می دهد. (که n از ۱ تا ۱۰۰ است)
• Above Average: مقادیر بالاتر از میانگین تمام اعداد را نشان می دهد.
• Below Average: مقادیر پایین تر از میانگین تمام اعداد را نشان می دهد.

Data Bars

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

Color Scales

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

Icon Sets

این قسمت بر اساس یک سری آیکن هایی مقادیر سلول ها را مشخص می کند.

مثال ۱ برای آموزش CONDITIONAL FORMATTIN

فروش ماهانه یک شرکت در طول سال ۱۳۹۲ در محیط اکسل همانند تصویر زیر وجود دارد. میخواهیم فرمت این اعداد را طوری تنظیم کنیم که اگر فروش هر ماه کمتر از ۳۰۰۰۰۰ ریال باشد، رنگ زمینه آن سلول قرمز شود.

برای این کار ابتدا این اعداد را با هم انتخاب میکنیم.

سپس از ابزار FORMATTING CONDITIONAL از قسمت CELLS HIGHLIGHT RULES گزینه THAN LESS را انتخاب میکنیم.

در کادر باز شده عدد ۳۰۰۰۰۰۰ ر را مینویسیم و در لیست فرمت آن گزینه LIGHT RED FILL را انتخاب و روی گزینه OK کلیک میکنیم.

خواهیم دید اعداد کوچکتر از ۳۰۰۰۰۰۰ دارای پس زمینه قرمز شده اند.

برای پاک کردن یک فرمت شرطی از سلول ها کافیست ابتدا آنها را انتخاب کنید و سپس از ابزار CONDITIONAL FORMATTING از قسمت CLEAR RULES همانند شکل زیر عمل می کنیم.

گزینه اول برای پاک کردن فرمت از سلولهای انتخاب شده و گزینه دوم برای پاک کردن تمانی فرمتهای شرطی موجود در کاربرگ کاربرد دارد.

آموزش مرتبط برای شما : آموزش اکسل مقدماتی تا پیشرفته کاملا رایگان

مثال ۲ استفاده از Conditional Formatting در تولید

الف) در کدام روز از هفته اول مهر ماه از قطعه A بیشتر از ۲۰۰۰ عدد تولید شده است؟

جهت پاسخ به این سوال محدوده B3 تا B9 را انتخاب نمائید. سپس از Conditional Formatting گزینه Greater than را کلیک کنید و در پنجره باز شده عدد ۲۰۰۰ را وارد کنید. با این کار سلول هایی که بزرگتر از ۲۰۰۰ هستند به رنگ قرمز مشخص می شوند.

ب) تعداد تولیدات قطعه B را با Data Bars مشخص کنید؟
در ادامه آموزش CONDITIONAL FORMATTING جهت پاسخ به این سوال محدوده C13 تا C19 را انتخاب نمائید سپس از Conditional Formatting گزینه Data Bars را کلیک کرده و رنگ مورد نظر را انتخاب نمائید.

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

جهت پاسخ به این سوال محدوده B23 تا D29 را انتخاب نمائید سپس از Conditional Formatting گزینه Equal To را کلیک کنید. و در پنجره باز شده فرمول (۲۹$max($B$23:$D= وارد کنید. با این فرمول ماکزیمم تعداد تولید از بین این سه قطعه مشخص می- شود که قطعه B است و در تاریخ ۹۳/۰۷/۰۵ تولید شده است.

نحوه حذف فرمت های شرطی(CONDITIONAL FORMATTING)

اشتباه رایجی که در حذف Conditional Formatting وجود دارد این است که کاربران اطلاعات داخل آن را Delete می کنند در حالی که این کار فرمت های اعمال شده را حذف نمی کند. برای حذف آن باید از پنجره Conditional Formatting گزینه Clear Rules را انتخاب کنید.

در ادامه چند سوال یا مثال مطرح می‌شود اما پاسخ آن را باید خودتان بدهید! تمرینی است برای شما تا بتوانید خودتان برای آموزش CONDITIONAL FORMATTING محک بزنید. اگر جواب را پیدا کردید برای کامنت بگذارید 😉

مثال ۳ برای آموزش CONDITIONAL FORMATTING : هزینه های حسابداری پرداخت شده

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

مثال ۴ برای آموزش CONDITIONAL FORMATTING : اضافه شدن سطر جدید

با توجه به شکل زیر چطور میتوان با کاندیشنال فرمتینگ عملیاتی را انجام داد که با وارد شدن شماره ردیف جدید کل سطر رنگی شود؟

آموزش مرتبط : ایجاد سری های تکرار شونده در اکسل

مثال ۴ برای آموزش CONDITIONAL FORMATTING زمانبندی با حرف C

الف) در سلول H4 فرمولی بنویسید که با وارد نمودن زمان شروع و پایان در سلولهای E4 و F4 رنگ این سلول را آبی کند.

ب) فرمول حالت الف را طوری تغییر دهید که وقتی حرف انگلیسی C در سلول G4 وارد شد رنگ آبی سلول به رنگ قرمز تبدیل شود. این رنگ نشان دهنده بحرانی بودن فعالیت می باشد. توجه داشته باشید فرمول را طوری بنویسید که بتوان برای بقیه سلول ها هم کپی نمود.

گزینه Stop if true در کاندیشنال فرمتینگ چیست؟

این گزینه برای زمانی است که شما چندین فرمت شرطی را بر روی یک ناحیه اعمال می کنید و اگر تیک این گزینه زده شود اکسل از اعمال فرمت های دیگر خودداری می کند.
در شکل زیر دو فرمت اعمال شده است. فرمت اول: مقادیر بزرگتر از ۵۵ رنگی شود. فرمت دوم: استفاده از Icon Sets.

همانطور که در شکل بالا مشاهده می کنید برای سلول هایی که فرمت اول یعنی بزرگتر از ۵۵ بودن اعمال شد فرمت بعدی که Icon Sets بود اعمال نشد و اکسل ادامه فرمت ها برای این ناحیه متوقف کرد.
نکته تیک گزینه Stop if true برای قسمتهای Icon Sets Color Scales Data Base غیر فعال است.
نکته ترتیب اعمال فرمتها در کاندیشنال فرمتیگ مهم است و اکسل فرمت ها را از بالا به پایین اجرا می کند.

مثال ۵ برای آموزش CONDITIONAL FORMATTING : نمایش درصد پیشرفت

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

مثال ۶ برای آموزش CONDITIONAL FORMATTING : رنگی شدن ضمانت نامه انتخاب شده

با توجه به شکل زیر چطور میتوان با کاندیشنال فرمتینگ عملیاتی را انجام داد که ردیف مربوط به سلول J2 رنگی شود؟

مثال ۷ برای آموزش CONDITIONAL FORMATTING : بزرگترین نمره

الف) در سلول A1 فرمولی نوشته و رو به پایین کپی کنید که بزرگترین امتیاز را با فلش نشان دهد.
ب) با استفاده از Conditional Formatting روی سلولهای B2 تا B9 تغییراتی را اعمال کنید که اکسل بصورت خودکار بزرگترین عدد را با رنگ قرمز مشخص کند.

امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش CONDITIONAL FORMATTING بود، لذت برده باشید. هر سوال و نظری دارید برای ما کامنت بذارید، کمتر از یک روز پاسخ داده می‌شه 😉