آموزش قالب بندی شرطی در اکسل

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

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

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

قالب بندی شرطی چیست؟

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

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

همچنین می تواند مجموعه داده های بزرگ را با استفاده از فرآیند سازماندهی بصری قابل درک کند. علاوه بر این، قالب‌بندی شرطی می‌تواند صفحه‌گسترده شما را (که قبلاً برای ذخیره داده‌ها استفاده می‌شد) به یک سیستم «هشدار» قابل اعتماد تبدیل کند که اطلاعات کلیدی را برجسته می‌کند و به شما امکان می‌دهد تا کارهایی را که باید در یک دوره زمانی خاص انجام دهید کنترل و تسلط داشته باشید.

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

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

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

• منطق If-then: همه قوانین قالب بندی شرطی مبتنی بر منطق اگر-آنگاه ساده هستند: این قانون به معنای اگر-آنگاه است، یعنی اگر شرط X درست باشد، قالب بندی Y اعمال می شود (همانطور که p → q نیز نوشته می شود، یعنی اگر p درست است، q را اعمال کنید). بدون نیاز به کدنویسی، اکسل و سایر برنامه های صفحه گسترده دارای پارامترهای داخلی هستند که می توانید به راحتی شرایط مورد نظر خود را انتخاب کنید.
• شرایط از پیش تعریف شده: اکسل دارای کتابخانه بزرگی از قوانین یا شرایط از پیش تعریف شده است که تقریباً تمام توابع مورد نیاز کاربران مبتدی را شامل می شود.
• شرایط سفارشی: برای زمانی که می خواهید یک شرط از پیش تعریف شده را دستکاری کنید و قانون یا شرط خود را ایجاد کنید. در صورت لزوم می توانید از فرمول های اکسل در قوانین خود استفاده کنید.
• اعمال شرایط متعدد: می توانید چندین شرط را برای یک سلول یا محدوده سلول اعمال کنید. البته باید از سلسله مراتب و اولویت قوانین آگاه باشید.

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

درک قالب بندی شرطی

ویژگی قالب‌بندی شرطی در اکسل به شما امکان می‌دهد قالب‌بندی سلول (پس‌زمینه یا متن) را بر اساس قوانین تنظیم شده به صورت پویا تغییر دهید.

فرض کنید در مثال زیر در ستون سال گذشته، می‌خواهیم رنگ پس‌زمینه سلول‌های دارای مقادیر کمتر از 100 را به قرمز تغییر دهیم و آن‌ها را برجسته کنیم. قانون قالب بندی شرطی به این صورت است: “اگر مقدار سلول در محدوده سلول های انتخاب شده کمتر از 100 باشد، رنگ پس زمینه سلول به قرمز تغییر می کند”. همانطور که در شکل زیر می بینید:

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

• محدوده: با انتخاب سلول هایی که می خواهید قانون روی آنها اعمال شود، شروع می کنید. کل صفحه یا تعدادی سطر یا ستون ممکن است انتخاب شود.
• شرط: شرط همان قسمت «اگر» در منطق if-then است. شما می توانید بیش از دوازده گزینه مانند: بزرگتر از، کمتر از و غیره را انتخاب کنید.
• قالب بندی: قسمت «سپس» در منطق if-then است. اکسل برای هر شرط یک سبک پیش فرض دارد، اما می توانید آن را سفارشی کنید.

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

ترکیب‌های زیادی از قالب‌بندی‌ها و سبک‌ها وجود دارد، اما روند همه یکسان است. یک روند کلی وجود دارد:

1- محدوده ای را انتخاب کنید.

۲- به تب Home رفته و روی گزینه Conditional Formatting کلیک کنید.

3- انتخاب قانون (یا شرایط عرف).

4- انتخاب سبک قالب بندی.

5- بر روی OK کلیک کنید.

1- انتخاب محدوده

قبل از باز کردن نوار ابزار Conditional Formatting، محدوده داده مورد نظر خود را انتخاب کنید. محدوده می تواند یک ردیف، یک ستون یا حتی یک صفحه کامل باشد. در اینجا محدوده Last Year انتخاب شده است.

2- انتخاب قانون

پس از انتخاب محدوده، به تب Home رفته و روی دکمه Conditional Formatting کلیک کنید.

دو قانون اساسی در بالای در لیست کشویی خواهید دید: Highlight Cell Rules و Top/Bottom Rules.

دو قانون اساسی را در بالای لیست کشویی مشاهده خواهید کرد: قوانین سلولی برجسته و قوانین بالا/پایین.

در قسمت Highlight Cell Rules، گزینه های زیر وجود دارد:

بزرگتر از

• کمتر از
• بین (بین دو مقدار)
• مساوی با

متنی که حاوی

تاریخ در حال وقوع

مقادیر تکراری

در بخش قوانین بالا/پایین گزینه های زیر وجود دارد:

• 10 مورد برتر (10 مورد اول)
• 10% برتر
• 10 مورد پایین

10% پایین

• بالاتر از میانگین

زیر میانگین

قانونی را انتخاب کنید که متناسب با نیاز شما باشد. در این مثال از قانون Greater Than استفاده شده است. بنابراین روی Highlight Cell Rules > Greater Than کلیک کنید.

3- انتخاب قالب بندی

پس از انتخاب قانون، پنجره ای برای قالب بندی باز می شود. در سمت چپ، مقداری را در سلول های Format وارد کنید که بزرگتر از هستند.

در اینجا مقدار 140 برای برجسته کردن مقادیر بیشتر از 140 در ستون سال گذشته وارد شده است.

در کنار کادر، یک منوی کشویی برای سبک قالب بندی وجود دارد. می توانید یکی از گزینه های زیر را انتخاب کنید:

قرمز روشن را با متن قرمز تیره پر کنید

• پر کردن زرد با متن زرد تیره (پس زمینه زرد با رنگ متن زرد تیره)
• پر کردن سبز با متن سبز تیره (پس زمینه سبز با رنگ متن سبز تیره)
• پر کردن قرمز روشن (پس‌زمینه قرمز روشن)
• متن قرمز (رنگ متن قرمز)
• مرز قرمز

این گزینه های قالب بندی برای همه قوانین یکسان هستند. در اینجا گزینه Green Fill with Dark Green Text انتخاب شده است.

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

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

قوانین روشن

برای پاک کردن همه قوانین، به Conditional Formatting > Clear Rules بروید و Clear Rules را از Entire Sheet انتخاب کنید. همچنین می توانید روی گزینه Clear Rules from Selected Cells کلیک کنید تا قوانین محدوده انتخاب شده حذف شود.

انواع قوانین و سبک های قالب بندی

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

قوانین سلول را برجسته کنید

چهار قانون اصلی سلول عبارتند از:

Greater than: سلول هایی را که مقدار آنها بیشتر از مقدار معینی است متمایز می کند.

Less than: سلول هایی را که مقدار آنها کوچکتر از مقدار معینی است متمایز می کند.

Between: سلول هایی را که مقدار آنها برابر با مقدار معینی است متمایز می کند.

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

عملکرد این قوانین ساده تر و نحوه کار آنها از نام آنها مشخص است، اما ما سه قانون بعدی را با یک مثال توضیح می دهیم:

Text That Contains: این قانون سلول هایی را که حاوی متن خاصی هستند برجسته می کند. برای مثال اگر می‌خواهید یک نام تکراری را برجسته کنید، می‌تواند مفید باشد.

در اینجا از این قانون برای تشخیص نام Tampa در محدوده ستون Office استفاده می کنیم.

A Date Occurring: ا این یک قانون پویا است. می توانید سلول های حاوی تاریخ های ماه گذشته، هفته گذشته، ماه آینده و غیره را برجسته کنید.

Duplicate Values: این قانون برای یافتن مقادیر تکراری و منحصر به فرد استفاده می شود. محدوده را انتخاب کنید و قانون مقادیر تکراری را انتخاب کنید. در اینجا می توانید Duplicate را برای مقادیر تکراری و Unique را برای مقادیر منحصر به فرد برای اعمال قانون انتخاب کنید.

قوانین Top/Bottom Rules

قوانین بالا/پایین کمک می کند تا به سرعت بهترین و بدترین عملکرد محدوده انتخاب شده را بدون نیاز به استفاده از عملیات ریاضی برجسته کنید.

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

پس از انتخاب قانون 10 مورد برتر (برای 10 مورد اول)، می توانید آن را هر چند بار که بخواهید تغییر دهید – یعنی می توانید اولین مورد، پنج مورد برتر یا 100 مورد برتر را برجسته کنید.

در این مثال، از این قانون برای برجسته کردن سه مورد بالا در ستون Last Year استفاده می شود.

این روند برای قوانین 10% بالا، 10 مورد پایین و قوانین 10% پایین یکسان است.

• Data Bars، Color Scales و Icon Sets

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

• نوارهای داده یک نمودار میله ای رنگی را در پس زمینه سلول اضافه می کند. می توانید از بین رنگ ها انتخاب کنید.

محدوده سلول های مورد نظر خود را انتخاب کنید و قانون Data Bars را اعمال کنید تا داده ها به صورت نمودار میله ای ظاهر شوند. هرچه این عدد بیشتر باشد، نوار طولانی تر است.

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

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

•  Icon Sets آخرین گزینه در قسمت قالب بندی شرطی است. این ویژگی آیکون های کوچکی را به لبه سلول اضافه می کند. مشابه ویژگی Color Scales. آیکون ها به صورت پویا بر اساس مقدار عددی اضافه می شوند و با ویرایش مقادیر تغییر می کنند. می توانید بین فلش ها، شکل ها، دایره های رنگی و سایر نمادها یکی را انتخاب کنید.

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

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

با مثال زیر نحوه استفاده از قالب بندی شرطی با فرمول ها را توضیح می دهیم.

1- محدوده A1:E5 را انتخاب می کنیم.

2- در تب Home در گروه Styles بر روی Conditional Formatting کلیک می کنیم.

3- روی New Rule کلیک می کنیم.

4- در قسمت Select a Rule Type نوع قانون را مشخص کنید. در اینجا ما روی “استفاده از فرمول برای تعیین اینکه کدام سلول ها را قالب بندی کنید” کلیک می کنیم.

5- فرمول مورد نظر خود را در کادر مقادیر Format که این فرمول درست است تایپ کنید. در اینجا فرمول ISODD(A1)= را برای تشخیص عدد فرد وارد می کنیم.

نکته: همیشه فرمول سلول بالا سمت چپ محدوده را بنویسید. اکسل به طور خودکار فرمول را در سلول های دیگر کپی می کند. بنابراین سلول A2 حاوی فرمول ISODD(A2)=، سلول A3 دارای فرمول ISODD(A3)= و غیره است.

6- دکمه Format را بزنید و یک سبک برای قالب بندی تعیین کنید.

7- در نهایت OK را بزنید.

اکسل تمام اعداد فرد را در محدوده برجسته می کند.

بیایید نمونه دیگری را ببینیم.

1- محدوده A2:D7 را انتخاب می کنیم.

2- مراحل 2-4 مثال بالا را تکرار می کنیم.

3- فرمول C2=”USA$=” را وارد می کنیم تا ردیف های حاوی مقدار USA برجسته شود.

4- سبک قالب بندی را انتخاب کرده و OK کنید.

5- در نتیجه اکسل تمام سطرهای حاوی مقدار USA را هایلایت می کند.

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