۱۰ ترفند اکسل برای شرطی سازی را یاد بگیرید

قالب‌بندی شرطی یا همان Conditional Formatting در نرم‌افزار Excel طرح‌‌بندی و طراحی صفحات اکسل شما را وارد سطح جدید می‌کند؛ به این ترتیب، شما درک کامل‌تری از اطلاعات خود به دست خواهید آورد و در یک چشم به هم زدن متوجه نشانه‌های مهم خواهید شد.

در ادامه، به ۱۰ ترفند گام به گام برای شرطی‌سازی در اکسل اشاره خواهیم کرد:

۱. هایلایت سلول‌های عددی

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

مثال: فرض کنید انباردار یک کارخانه هستید؛ یک فهرست موجودی اجناس را در نظر بگیرید. اگر مقدار یک آیتم به زیر ۱۰۰ برسد، باید دستور خریداری این جنس را بدهید. چنانچه با قالب‌بندی شرطی آشنا نباشید، باید کل سلول‌های اکسل را بگردید و آیتم‌های زیر ۱۰۰ را پیدا کنید! مطمئنا چنین روشی در یک مجموعه‌ی ۵۰ ردیفی موثر نخواهد بود؛ ضمن این که احتمال خطای انسانی و ندیدن گزینه‌های زیر ۱۰۰ بالا می‌رود. در عوض اجازه دهید قالب‌بندی شرطی این کار را برای شما انجام دهد!

اولین کاری که باید انجام دهید این است که ستون اعداد مورد نظر را انتخاب کنید. در تب Home بر روی Conditional Formatting کلیک کنید. سپس گزینه‌ی Highlight cells rules و پس از آن ...Less than را انتخاب کنید.

پس از این، یک باکس برای شما باز می‌شود که در سمت چپ آن عدد مورد نظر که در این مثال ۱۰۰ است را قرار می‌دهیم.

در سمت راست هم گزینه‌های مربوط به رنگ متن یا سلول را انتخاب می‌کنیم.

چنانچه از فرمت‌های پیش‌فرض راضی نبودید و قصد تغییر آن را داشتی، می‌توانید از فلش سمت راست گزینه‌ی ...Custom Format را انتخاب کرده و موارد متنوع‌تری را برای ظاهر سلول‌ها انتخاب کنید.

پس از آن، تنها کاری که لازم است انجام دهید، چندین بار کلیک بر روی موس است.

پس از کلیک بر روی OK، تمام سلول‌هایی که عدد آن‌ها زیر ۱۰۰ است، مشخص می‌شوند.

۲. هایلایت سلول‌های دارای متن

در ترفند ۱، آموختید که چطور مقادیر کم‌تر از ۱۰۰ را هایلایت کنید. از همین روش می‌توانید برای سلول‌های دارای داده‌ی متنی استفاده کنید. فرض کنید می‌خواهیم سلول‌های شامل عبارت M compatible را هایلایت کنیم. در فایل مورد نظر ستون مربوط به داده‌های متنی را انتخاب می‌کنیم.

سپس مانند ترفند ۱ از منوی Home گزینه‌ی Formatting Conditional را انتخاب کرده و گزینه‌های ...Hightlight Cells Rules و Text that Contains را انتخاب می‌کنیم. باکس مورد نظر مشابه حالت ترفند ۱ باز می‌شود.

کافی است عبارت مورد نظر خود را در سلول سمت چپ تایپ کنید تا کلیه‌ی سلول‌های دارای آن عبارت هایلات شوند.

۳. ویرایش شرط در اکسل

گاهی اوقات نیاز به ویرایش یک شرط دارید. خوشبختانه ویرایش یک شرط در اکسل بسیار ساده است. از طریق منوی Home وارد Conditional Formatting شده و گزینه‌ی ...Manage Rules را انتخاب کنید.

پس از آن یک باکس در صفحه باز می‌شود:

در بالای باکس، مقابل عبارت Show formatting rules for حالت پیش‌فرض Current Selection انتخاب شده است. این گزینه را به This Worksheet تغییر دهید. شرطی را که می‌خواهید تغییر بدهید، از فهرست شرط‌ها انتخاب کنید. پس از انتخاب شرط، روی دکمه‌ی ...Edit Rule کلیک کنید. باکس دیگری ظاهر خواهد شد. در اینجا می‌توانید شرط خود را ویرایش کنید.

۴. حذف شرط در اکسل

گاهی اوقات ورک‌بوک شما بیش از حد دارای شرط شده است و همین مسئله شما را سردرگم کرده است. در این حالت ممکن است بخواهید بعضی (یا کل) شرط‌ها را پاک کنید. از طریق Home‌ وارد Conditional Formatting شده و گزینه‌ی Clear Rules را انتخاب کنید.

سپس بر روی Clear Rules from Entire Sheet کلیک کنید.

به همین سادگی! این روش تمام شرط‌ها را پاک می‌کند. اما ممکن است بخواهید بعضی شرط‌ها در اکسل شما باقی بمانند. در این حالت، از طریق Conditional Formatting گزینه‌ی ...Manage Rules را انتخاب کنید. این باکس به شما نشان می‌دهد که کدام شرط‌ها برقرار هستند.

برای پاک کردن یک یا چند شرط به خصوص، روی فلش کشویی کلیک کرده و به جای Current Selection پیش‌فرض گزینه‌ی This Worksheet را انتخاب کنید. آن را به This Worksheet تغییر دهید.

شرطی را که نمی‌خواهید، انتخاب کنید. بر روی Delete Rule کلیک کنید. پنجره‌ی باز شده را ببندید و دکمه‌ی OK‌ را بزنید.

۵. پیدا کردن ۸ آیتم حداقل

دوباره همان مثال انباردار را در نظر بگیرید. فرض کنید شرایطی پیش آمده که تامین‌کننده‌ی موجودی شما قرار است برای سه ماه غیر قبال دسترش باشد. قبل از این که این اتفاق بیافتد شما باید موجودی خود را بررسی کنید تا در این سه ماه به مشکل کمبود نخورید. بنابراین به ذهن‌تان خطور می‌کند که فهرست ۸ قلمی که کم‌ترین مقدار آن‌ها موجود است را در ۱۵ دقیقه اعلام کنید. چطور چنین چیزی ممکن است؟ در این حالت، اکسل امکانی را دارد که به شما کمک خواهد کرد.

از طریق Home‌ وارد Conditional Formatting شده و گزینه‌ی Top/Bottom Rules و پس از آن ...Bottom 10 Items را انتخاب کنید.

در سمت چپ پنجره‌ی باز شده، می‌توانید روی فلش کشویی کلیک کرده و تعداد کم‌ترین اعداد را تغییر دهید. چنانچه بخواهید ۲۰ آیتم که کم‌ترین موجودی را دارند، مشخص شوند عدد ۲۰ را وارد کنید.

در این مثال، پیش‌فرض ۱۰ را به ۸ تغییر می‌دهیم. در سمت راست قالب پیشنهادی برای ۸ سلولی که کم‌ترین مقدار را دارند، مشخص می‌کنیم.

۶. نمایش گرافیکی داده‌ها با دیتا بار یا نوار داده

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

دیتابارها به سادگی به شکل میله در سلول‌های اکسل نشان داده می‌شوند. طول هر دیتابار بر اساس مقدار عددی هر سلول با سلول دیگر تعیین می‌شود. بنابراین، یک دیتابار کوتاه نشان می دهد که عدد این سلول در مقایسه با دیگر سلول‌ها مقدار کم‌تری دارد و دیتابار بلند بیان‌گر این است که عدد سلول در مقایسه با اعداد سلول‌های دیگر مقدار بزرگ‌تری دارد. برای فعال کردن امکان دیتا بار، سلول‌های مورد نظر را انتخاب می‌کنیم. از طریق Home‌ وارد Conditional Formatting شده و گزینه‌ی Data Bars را انتخاب می‌کنیم. در اینجا گزینه‌های مختلف برای نمایش دیتابار را مشاهده می‌کنیم.

این قالب‌ها به دو دسته‌ی Gradient Fill و Solid Fill تقسیم می‌شوند.

• در حالت Gradient Fill رنگ‌بندی دیتابار به شکلی است که رنگ‌ها حالت کم‌حال دارند.
• در حالت Solid Fill رنگ‌ها دقیقا به همان شکلی که هستند، نمایش داده می‌شوند. انتخاب هر کدام از این دو حالت، کاملا به سلیقه‌ی شما بستگی دارد.

۷. گزینه‌ی Color Scales در فرمت‌دهی شرطی

Color Scales ابزاری مشابه با دیتا بار است و اطلاعات را به صورت فوری به کاربر منتقل می‌کند. تفاوت Color Scales با دیتابار روش نمایش داده‌ها است.

در واقع، Color Scales با استفاده از رنگ‌ها تفاوت داده‌ها را نشان می‌دهد. قاعده‌ی کلی به این صورت است که:

• یک رنگ مشخص به سلول حاوی کم‌ترین داده را اختصاص می‌دهیم.
• رنگ دوم را به سلولی که بزرگ‌ترین داده را دارد، اختصاص می دهیم.
• باقی سلول‌ها که مقداری بین رقم این دو سلول دارند، به صورت ترکیبی از دو رنگ انتخابی نمایش داده می‌شوند.
• شما می توانید از سه رنگ هم استفاده کنید. به این ترتیب رنگ سوم را برای مقدار میانی انتخاب می‌کنید.

روش کار به این ترتیب است:

سلول‌های مورد نظر را انتخاب کنید. از طریق Home‌ وارد Conditional Formatting شده و گزینه‌ی Color Scales را انتخاب کنید. در کادر کشویی باز شده می‌تواند حالت رنگی مورد نظر را انتخاب کنید.

۸. قرار دادن آیکون در سلول‌ها

استفاده از آیکون‌ها در سلول های حاوی داده هم موضوع جالبی است. روش کار به این صورت خواهد بود: ستون داده‌های مورد نظر را انتخاب کنید. از طریق Home‌ وارد Conditional Formatting شده و گزینه‌ی Set Icons را انتخاب کنید.

با انتخاب این گزینه یک کادر کشویی باز می‌شود که شامل آیکون‌های مختلف از قبیل فلش‌های جهت‌دار، چراغ‌های ترافیکی و پرچم و دیگر موارد است. بعضی گزینه‌ها شامل ۳ آیکون و بعضی شامل تعداد بیشتری است. در حالت فلش سه گزینه، فلش سبز در کنار داده نشانه‌ی آن است که مقدار سلول از مقادیر ۳۳٪ سلول‌های دیگر بالاتر است. فلش زد به معنای میانه بودن و فلش قرمز به معنای این است که داده‌ی موجود از ۳۳ درصد سلول ها مقدار کم‌تری دارد. در مورد ۴ آیکون این نسبت به ۲۵ درصد و در حالت ۵ آیکون این نسبت به ۲۰ درصد می‌رسد.

۹. پیدا کردن مقادیر تکراری

از Conditional formatting برای پیدا کردن مقادیر تکراری در اکسل می‌توان استفاده کرد. بین دو حالت حذف موارد تکراری و شناسایی موارد تکراری تفاوت وجود دارد. در گزینه‌ی Remove duplicate گزینه‌های کپی بدون آن‌ها که به مشاهده‌ی شما برسند، حذف می‌شوند.

در مواقعی که نیاز به سرعت عمل دارید این گزینه مفید خواهد بود؛ اما در مواقعی که نیاز به نظارت محتوای سلول‌ها دارید، لازم است ابتدا موارد کپی را شناسایی کنید. برای شناساسی موارد کپی شده ستون مورد نظر را انتخاب کنید. فرقی ندارد که داده‌های موجود در ستون عدد یا متن باشند. از طریق منوی Home وارد Conditional Formatting شده و گزینه‌ی Highlight Cells Rules را انتخاب و در کادر کشویی باز شده بر روی Duplicate Values… کلیک کنید.

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

۱۰. پنهان کردن مقادیر صفر

یکی از چالش‌های مرسوم در اکسل، پنهان کردن مقادیر صفر است. این کار را از طریق راه‌های مختلف می‌توان انجام داد. یکی از این روش‌ها تغییر در بخش تنظیمات اکسل است که تمام صفرها را مخفی خواهد کرد. اما چنین روشی مشکلات دیگری ایجاد می‌کند؛ بنابراین سراغ راهکار هوشمندانه‌ی دیگری می‌رویم که استفاده از Conditional Formatting است.

 ستون یا ستون هایی که دارای عدد صفر هستند را انتخاب کنید. می توانید CTRL+A را زده و کل ورکشیت خود را انتخاب کنید. از طریق منوی Home وارد Conditional Formatting شده و Highlight Cells Rules را انتخاب کنید. بر روی ...Equal to کلیک کنید.

در این حالت پنجره‌ای جدید با دو فیلد باز خواهد شد. در فیلد سمت چپ عدد صفر را وارد کنید. در فیلد سمت راست بر روی فلش کشویی کلیک کرده و در کادر کشویی باز شده Custom Format را انتخاب کنید. در پنجره ی جدید، وارد زبانه‌ی Font شده و رنگ آن را از حالت automatic/black به white (یا هر رنگی که پس‌زمینه‌ی سلول است) تغییر دهید.

حالا تمام صفرهای شیت اکسل شما مخفی خواهند شد!