قالب‌بندی شرطی در Excel

فرض كنید معلمی هستید كه می‌خواهید با استفاده از كاربرگ‌های‌ ‌برنامه اكسل، نمرات دانش آموزان را كنترل كنید. به این صورت كه نمرات خوب از نمرات متوسط و ضعیف به‌راحتی قابل تفكیك باشند. باید دید برای این امر چه‌كار باید بكنید؟ می‌توانید از قالب بندی شرطی یا conditional formatting استفاده نموده و تغییرات مختلفی را در ظاهر یك سل (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سل، سه شرط تعیین كنید كه با فرمت فعلی آن سل، مجموعاً چهار شرط می‌شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه‌گذاری ‌‌(‌shading) هر سل را تعیین كنید كه البته این تغییرات، شامل فرمت عددی و نوع چیدمان سل نمی‌شود. برای استفاده از قالب‌بندی شرطی، باید شروطی را انتخاب كنید كه با مقادیر false و ‌true قابل ارزیابی باشند. مثلا جمله "آیا عدد بزرگ‌تر از ۱۰ است؟" را می‌توان با false یا true ارزیابی كرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سل انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یك سل تعیین كرد كه اگر شرط اول، ارزش ‌true داشت، تغییرات انجام شده و بقیه شرط‌ها نادیده گرفته خواهند شد.
حال این معلم می خواهد رنگ هر سل را بر اساس نمره موجود در آن، تعیین كند. بدین صورت كه خانه‌های دارای نمره كمتر از ۵۰ به‌رنگ‌قرمز، بین ۵۰ و ۶۵ به‌رنگ ‌سبز، بین ۶۵ و ۷۵ به‌رنگ ‌بنفش، و نمرات بالاتر از ۷۵، به‌رنگ آبی درآیند.
برای این‌كار، ابتدا رنگ فونت كلیه سل‌های حاوی نمرات را آبی كنید. سپس آن‌ها را انتخاب نموده و در منوی Format، روی Conditional Formating كلیك كنید. در پنجره باز شده، كادر اول را در حالت Cell Value Is باقی بگذارید. از كادر دوم، گزینه ‌less than را انتخاب و در كادر روبه‌روی آن، عدد ۵۰ را تایپ كنید. سپس دكمه ‌Format واقع در سمت راست را كلیك كرده ودر زبانه Font، رنگ قرمز را برای اعداد كمتر از ۵۰ انتخاب نمایید. با زدن ‌OK، به كادر قبلی برگشته ودكمه Add در پایین صفحه را كلیك كنید.
می توانید تا سه شرط را برای هر سل تعیین كنید.
حالا برای تعیین شرط دوم، كادر مقابل Cell Value Is را به ‌less than، و در كادر روبه‌روی آن، عدد ۶۵ را بنویسید. سپس دكمه Format را زده و در كادر باز شده، رنگ سبز را انتخاب كنید. یك‌بار دیگر بر روی Add كلیك كنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در كادر اول Cell Value Is، در كادر دوم less than، و در كادر روبه‌روی آن، عدد ۷۵ را تایپ كنید. سپس دكمه ‌Format را كلیك كرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت كنید، می‌بینید كه دكمه Add بعد از اعمال سومین شرط، غیر فعال می‌شود. چرا كه شما نمی‌توانید بیش از سه شرط برای یك سل تعیین كنید. OK را بزنید و نتیجه را ملاحظه نمایید. ‌
راه ساده‌تر آن‌است كه قالب‌بندی‌های مورد نظرتان را به‌صورت یكجا بر تمامی سل‌ها اعمال كنید. اما اگر خواستید، می‌توانید بااستفاده از دكمه ‌FormatPainter، قالب‌های شرطی و فرمت اولیه یك سل را به سل‌های دیگر كپی نمایید.
در ضمن برای این‌كه در آینده بدانید قالب‌بندی‌های شرطی بر روی كدام‌یك از سل‌های كاربرگتان اعمال شده است، از منوی Edit،‌ گزینه Go To را كلیك و در كادر باز شده، دكمه ‌Special را كلیك كنید. در پنجره باز شده، دكمه رادیویی ‌Conditional Formats را علا‌مت زده و ‌OK كنید. با این‌كار كلیه سل‌های دارای قالب‌بندی شرطی، با رنگ جداگانه نمایش داده می‌شوند.‌
می‌توانید فرمتی را روی یك سطر با توجه به تعداد یك سل خاص، اعمال نمایید.
همچنین می‌توان فرمت یك ردیف را بر اساس داده های یك سل در آن ردیف تعیین كرد. فرض كنید شماره ستون‌ها را در ردیف یك می‌نویسید، نام دانش آموزان را در ستون‌‌A ، و نمره آن‌ها را نیز در ستونB. كلیه سل‌ها از خانه ‌‌۲ A تا آخرین آن‌ها در ستون B را انتخاب كنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه ‌Formula Is و در لیست دوم، شرط ‌‌۵۰>۲=‌‌$B را نوشته و رنگ متن را نیز تنظیم كنید. با استفاده از دكمه Add، شروط دوم و سوم را به ترتیب ۶۵> B۲ $= و ۷۵ >‌‌۲‌B $= تعیین نمایید. برای كنترل مجدد تغییرات هر سل، ابتدا سل ‌‌A۲ را انتخاب كنید و به مسیر Format / Conditional Formatting بروید.
این سل بر اساس مقدار موجود در سل B۲ تغییر خواهد كرد. فرمت سل ‌‌‌A۳ نیز براساس سل B۳ تغییر خواهد نمود و به همین ترتیب تا آخر. بخش‌ B$ فرمول برای مقایسه دوبه‌دو بین ستون‌ها تنظیم می‌شود تا بتواند تغییرات هر ردیف را به‌طور جداگانه انجام دهد. همچنین می‌توانید از شرط Formula Is برای دیگر ردیف‌ها نیز استفاده كنید. مثلاً بعد از انتخاب ردیف‌های موردنظر از فرمول‌های (Mod(Row(),۲= و ((Not(Mod(Row(),۲= استفاده نمایید. برای این‌كه به‌جای ردیف‌ها، در ستون‌ها تغییرات ایجاد كنید، در فرمول آن‌ها به‌جای ()‌ Row از () Column استفاده نمایید. اگر هم خواستید، می‌توانید در شروط ایجاد شده در ردیف‌های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده كنید.
استفاده از فرمول‌های پیچیده‌تر در ایجاد تغییرات شرطی، امكانات قوی‌تری را در اختیار شما قرار می‌دهد. مثلاً برای این‌كه در محدوده‌‌ B۲:B۵۰ اعدادی كه بیش از یك‌بار آمده‌اند مشخص شوند، می‌توان در جلو لیست Formula Is فرمول‌‌ ۱<(COUNTIF($B$۲:$B$۵۰;$B۲= را نوشت. برای پیدا كردن سل حاوی بزرگ‌ترین مقدار (یا ردیف حاوی سل بزرگ‌ترین مقدار) نیز می‌توان از فرمول (۵۰$B۲=MAX($B$۲:$B$=استفاده نمود. اگر هم خواستید فرمولتان محدوده وسیع‌تری را در یك ستون پوشش دهد، در این فرمول به‌جای ‌‌۵۰$B۲:$B$ از B:$B$ استفاده كنید.‌

منبع: پی‌سی مگزین‌
ترجمه: داود رضایی‌

منبع : ماهنامه شبکه