قالب‌بندی شرطی در Excel
فرض کنید می‌خواهید با استفاده از کاربرگ‌های برنامه اکسل، نمرات دانش آموزان را کنترل کنید. به این صورت که نمرات خوب از نمرات متوسط و ضعیف به‌راحتی قابل تفکیک باشند. باید دید برای این امر چه‌کار باید بکنید؟ می‌توانید از قالب بندی شرطی یا conditional formatting استفاده نموده و تغییرات مختلفی را در ظاهر یک سل (Cell)، بر اساس مقدار آن، ایجاد نمایید.
با قالب بندی شرطی قادر خواهید بود برای هر سل، سه شرط تعیین کنید که با فرمت فعلی آن سل، مجموعاً چهار شرط می‌شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه‌گذاری (shading) هر سل را تعیین کنید که البته این تغییرات، شامل فرمت عددی و نوع چیدمان سل نمی‌شود. برای استفاده از قالب‌بندی شرطی در اکسل، باید شروطی را انتخاب کنید که با مقادیر false و true قابل ارزیابی باشند. مثلا جمله "آیا عدد بزرگ‌تر از 10 است؟" را می‌توان با false یا true ارزیابی کرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سل انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یک سل تعیین کرد که اگر شرط اول، ارزش true داشت، تغییرات انجام شده و بقیه شرط‌ها نادیده گرفته خواهند شد.
حال این معلم می خواهد رنگ هر سل را بر اساس نمره موجود در آن، تعیین کند. بدین صورت که خانه‌های دارای نمره کمتر از 50 به‌رنگ‌قرمز، بین 50 و 65 به‌رنگ سبز، بین 65 و 75 به‌رنگ بنفش، و نمرات بالاتر از 75، به‌رنگ آبی درآیند.
برای این‌کار، ابتدا رنگ فونت کلیه سل‌های حاوی نمرات را آبی کنید. سپس آن‌ها را انتخاب نموده و در منوی Format، روی Conditional Formating کلیک کنید. در پنجره باز شده، کادر اول را در حالت Cell Value Is باقی بگذارید. از کادر دوم، گزینه less than را انتخاب و در کادر روبه‌روی آن، عدد 50 را تایپ کنید. سپس دکمه Format واقع در سمت راست را کلیک کرده ودر زبانه Font، رنگ قرمز را برای اعداد کمتر از 50 انتخاب نمایید. با زدن OK، به کادر قبلی برگشته ودکمه Add در پایین صفحه را کلیک کنید.
می توانید تا سه شرط را برای هر سل تعیین کنید.
حالا برای تعیین شرط دوم، کادر مقابل Cell Value Is را به less than، و در کادر روبه‌روی آن، عدد 65 را بنویسید. سپس دکمه Format را زده و در کادر باز شده، رنگ سبز را انتخاب کنید. یک‌بار دیگر بر روی Add کلیک کنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در کادر اول Cell Value Is، در کادر دوم less than، و در کادر روبه‌روی آن، عدد 75 را تایپ کنید. سپس دکمه Format را کلیک کرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت کنید، می‌بینید که دکمه Add بعد از اعمال سومین شرط، غیر فعال می‌شود. چرا که شما نمی‌توانید بیش از سه شرط برای یک سل تعیین کنید. OK را بزنید و نتیجه را ملاحظه نمایید.
راه ساده‌تر آن‌است که قالب‌بندی‌های مورد نظرتان را به‌صورت یکجا بر تمامی سل‌ها اعمال کنید. اما اگر خواستید، می‌توانید بااستفاده از دکمه FormatPainter، قالب‌های شرطی و فرمت اولیه یک سل را به سل‌های دیگر کپی نمایید.
در ضمن برای این‌که در آینده بدانید قالب‌بندی‌های شرطی بر روی کدام‌یک از سل‌های کاربرگتان اعمال شده است، از منوی Edit، گزینه Go To را کلیک و در کادر باز شده، دکمه Special را کلیک کنید. در پنجره باز شده، دکمه رادیویی Conditional Formats را علا‌مت زده و OK کنید. با این‌کار کلیه سل‌های دارای قالب‌بندی شرطی، با رنگ جداگانه نمایش داده می‌شوند.
می‌توانید فرمتی را روی یک سطر با توجه به تعداد یک سل خاص، اعمال نمایید.
همچنین می‌توان فرمت یک ردیف را بر اساس داده های یک سل در آن ردیف تعیین کرد. فرض کنید شماره ستون‌ها را در ردیف یک می‌نویسید، نام دانش آموزان را در ستونA، و نمره آن‌ها را نیز در ستونB. کلیه سل‌ها از خانه 2 A تا آخرین آن‌ها در ستون B را انتخاب کنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه Formula Is و در لیست دوم، شرط 50>2=$B را نوشته و رنگ متن را نیز تنظیم کنید. با استفاده از دکمه Add، شروط دوم و سوم را به ترتیب 65> B2 $= و 75 >2B $= تعیین نمایید. برای کنترل مجدد تغییرات هر سل، ابتدا سل A2 را انتخاب کنید و به مسیر Format / Conditional Formatting بروید.
این سل بر اساس مقدار موجود در سل B2 تغییر خواهد کرد. فرمت سل A3 نیز براساس سل B3 تغییر خواهد نمود و به همین ترتیب تا آخر. بخش B$ فرمول برای مقایسه دوبه‌دو بین ستون‌ها تنظیم می‌شود تا بتواند تغییرات هر ردیف را به‌طور جداگانه انجام دهد. همچنین می‌توانید از شرط Formula Is برای دیگر ردیف‌ها نیز استفاده کنید. مثلاً بعد از انتخاب ردیف‌های موردنظر از فرمول‌های (Mod(Row(),2= و ((Not(Mod(Row(),2= استفاده نمایید. برای این‌که به‌جای ردیف‌ها، در ستون‌ها تغییرات ایجاد کنید، در فرمول آن‌ها به‌جای () Row از () Column استفاده نمایید. اگر هم خواستید، می‌توانید در شروط ایجاد شده در ردیف‌های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده کنید.
استفاده از فرمول‌های پیچیده‌تر در ایجاد تغییرات شرطی، امکانات قوی‌تری را در اختیار شما قرار می‌دهد. مثلاً برای این‌که در محدوده B2:B50 اعدادی که بیش از یک‌بار آمده‌اند مشخص شوند، می‌توان در جلو لیست Formula Is فرمول 1
با قالب بندی شرطی قادر خواهید بود برای هر سل، سه شرط تعیین کنید که با فرمت فعلی آن سل، مجموعاً چهار شرط می‌شود. با اعمال هر شرط، می توانید نوع فونت، خطوط حاشیه، و نوع سایه‌گذاری (shading) هر سل را تعیین کنید که البته این تغییرات، شامل فرمت عددی و نوع چیدمان سل نمی‌شود. برای استفاده از قالب‌بندی شرطی در اکسل، باید شروطی را انتخاب کنید که با مقادیر false و true قابل ارزیابی باشند. مثلا جمله "آیا عدد بزرگ‌تر از 10 است؟" را می‌توان با false یا true ارزیابی کرد.
اما جمله "مقدار عدد چقدر است؟" به این روش قابل ارزیابی نیست. بدین ترتیب، اگرنتیجه شرط، true باشد، تغییرات در سل انجام می شود وگرنه تغییری در آن ایجاد نخواهد شد. همچنین می توان به ترتیب تا سه شرط برای یک سل تعیین کرد که اگر شرط اول، ارزش true داشت، تغییرات انجام شده و بقیه شرط‌ها نادیده گرفته خواهند شد.
حال این معلم می خواهد رنگ هر سل را بر اساس نمره موجود در آن، تعیین کند. بدین صورت که خانه‌های دارای نمره کمتر از 50 به‌رنگ‌قرمز، بین 50 و 65 به‌رنگ سبز، بین 65 و 75 به‌رنگ بنفش، و نمرات بالاتر از 75، به‌رنگ آبی درآیند.
برای این‌کار، ابتدا رنگ فونت کلیه سل‌های حاوی نمرات را آبی کنید. سپس آن‌ها را انتخاب نموده و در منوی Format، روی Conditional Formating کلیک کنید. در پنجره باز شده، کادر اول را در حالت Cell Value Is باقی بگذارید. از کادر دوم، گزینه less than را انتخاب و در کادر روبه‌روی آن، عدد 50 را تایپ کنید. سپس دکمه Format واقع در سمت راست را کلیک کرده ودر زبانه Font، رنگ قرمز را برای اعداد کمتر از 50 انتخاب نمایید. با زدن OK، به کادر قبلی برگشته ودکمه Add در پایین صفحه را کلیک کنید.
می توانید تا سه شرط را برای هر سل تعیین کنید.
حالا برای تعیین شرط دوم، کادر مقابل Cell Value Is را به less than، و در کادر روبه‌روی آن، عدد 65 را بنویسید. سپس دکمه Format را زده و در کادر باز شده، رنگ سبز را انتخاب کنید. یک‌بار دیگر بر روی Add کلیک کنید و شرط آخر را نیز بدین ترتیب تعیین نمایید: در کادر اول Cell Value Is، در کادر دوم less than، و در کادر روبه‌روی آن، عدد 75 را تایپ کنید. سپس دکمه Format را کلیک کرده و برای این شرط، رنگ بنفش را انتخاب نمایید. اگر دقت کنید، می‌بینید که دکمه Add بعد از اعمال سومین شرط، غیر فعال می‌شود. چرا که شما نمی‌توانید بیش از سه شرط برای یک سل تعیین کنید. OK را بزنید و نتیجه را ملاحظه نمایید.
راه ساده‌تر آن‌است که قالب‌بندی‌های مورد نظرتان را به‌صورت یکجا بر تمامی سل‌ها اعمال کنید. اما اگر خواستید، می‌توانید بااستفاده از دکمه FormatPainter، قالب‌های شرطی و فرمت اولیه یک سل را به سل‌های دیگر کپی نمایید.
در ضمن برای این‌که در آینده بدانید قالب‌بندی‌های شرطی بر روی کدام‌یک از سل‌های کاربرگتان اعمال شده است، از منوی Edit، گزینه Go To را کلیک و در کادر باز شده، دکمه Special را کلیک کنید. در پنجره باز شده، دکمه رادیویی Conditional Formats را علا‌مت زده و OK کنید. با این‌کار کلیه سل‌های دارای قالب‌بندی شرطی، با رنگ جداگانه نمایش داده می‌شوند.
می‌توانید فرمتی را روی یک سطر با توجه به تعداد یک سل خاص، اعمال نمایید.
همچنین می‌توان فرمت یک ردیف را بر اساس داده های یک سل در آن ردیف تعیین کرد. فرض کنید شماره ستون‌ها را در ردیف یک می‌نویسید، نام دانش آموزان را در ستونA، و نمره آن‌ها را نیز در ستونB. کلیه سل‌ها از خانه 2 A تا آخرین آن‌ها در ستون B را انتخاب کنید. از مسیر Format / ConditionalFormatting در لیست اول، گزینه Formula Is و در لیست دوم، شرط 50>2=$B را نوشته و رنگ متن را نیز تنظیم کنید. با استفاده از دکمه Add، شروط دوم و سوم را به ترتیب 65> B2 $= و 75 >2B $= تعیین نمایید. برای کنترل مجدد تغییرات هر سل، ابتدا سل A2 را انتخاب کنید و به مسیر Format / Conditional Formatting بروید.
این سل بر اساس مقدار موجود در سل B2 تغییر خواهد کرد. فرمت سل A3 نیز براساس سل B3 تغییر خواهد نمود و به همین ترتیب تا آخر. بخش B$ فرمول برای مقایسه دوبه‌دو بین ستون‌ها تنظیم می‌شود تا بتواند تغییرات هر ردیف را به‌طور جداگانه انجام دهد. همچنین می‌توانید از شرط Formula Is برای دیگر ردیف‌ها نیز استفاده کنید. مثلاً بعد از انتخاب ردیف‌های موردنظر از فرمول‌های (Mod(Row(),2= و ((Not(Mod(Row(),2= استفاده نمایید. برای این‌که به‌جای ردیف‌ها، در ستون‌ها تغییرات ایجاد کنید، در فرمول آن‌ها به‌جای () Row از () Column استفاده نمایید. اگر هم خواستید، می‌توانید در شروط ایجاد شده در ردیف‌های دیگر، از تغییراتی مانند رنگ و سایه گذاری نیز استفاده کنید.
استفاده از فرمول‌های پیچیده‌تر در ایجاد تغییرات شرطی، امکانات قوی‌تری را در اختیار شما قرار می‌دهد. مثلاً برای این‌که در محدوده B2:B50 اعدادی که بیش از یک‌بار آمده‌اند مشخص شوند، می‌توان در جلو لیست Formula Is فرمول 1
- لینک منبع
تاریخ: چهارشنبه , 01 آذر 1402 (12:39)
- گزارش تخلف مطلب