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

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

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

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

آموزش ویدیویی

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

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

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

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

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

نکات اولیه قالب بندی شرطی

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5- کلیک روی OK.

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

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

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

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

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

در بخش Highlight Cell Rules گزینه های زیر وجود دارد:

• Greater Than (بزرگتر از)
• Less Than (کمتر از)
• Between (بین دو مقدار)
• Equal To (مساوی با)
• Text That Contains (متن حاوی)
• A Date Occurring (تاریخ وقوع)
• Duplicate Values (مقادیر تکراری)

در بخش Top/Bottom Rules گزینه های زیر وجود دارد:

• Top 10 Items (10 مورد اول)
• Top 10% (10 درصد بالا)
• Bottom 10 Items (10 مورد آخر)
• Bottom 10% (10 درصد پایین)
• Above Average (بالاتر از میانگین)
• Below Average (پایین تر از میانگین)

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

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

پس از انتخاب قانون، یک پنجره برای قالب بندی باز می شود. در سمت چپ یک مقدار در بخش Format cells that are Greater Than وارد کنید.

اینجا، مقدار 140 را وارد شده است که می خواهد مقادیر بیشتر از 140 را در ستون last year را برجسته کند.

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

• Light Red Fill with Dark Red Text (پس زمینه قرمز روشن با رنگ متن قرمز تیره)
• Yellow Fill with Dark Yellow Text (پس زمینه زرد با رنگ متن زرد تیره)
• Green Fill with Dark Green Text (پس زمینه سبز را با رنگ متن سبز تیره)
• Light Red Fill (پس زمینه قرمز روشن)
• Red Text (رنگ متن قرمز)
• Red Border (حاشیه قرمز)

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

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

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

پاک کردن قوانین

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

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

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

قوانین Highlight Cell Rules

چهار قانون اول Highlight Cell Rules عبارتند از:

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

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

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

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

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

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

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

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

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

قوانین Top/Bottom Rules

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

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

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

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

فرآیند برای قوانین Top 10%، Bottom 10 Items و Bottom 10% نیز به همین صورت است.

• Data Bars، Color Scales و Icon Sets

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

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

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

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

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

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

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

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

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

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

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

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

4- در قسمت Select a Rule Type نوع قانون را مشخص کنید. در اینجا روی “Use a formula to determine which cells to format” کلیک می کنیم.

5- در کادر Format values where this formula is true فرمول مورد نظر خود را تایپ کنید. در اینجا، فرمول 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 را برجسته می کند.

لیست همه دروس کاربردی اکسل ما