آموزش فرمول نویسی در Conditional Formatting در اکسل

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

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

تغییر رنگ سلول و ردیف به روش دستی در Excel

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

تغییر دادن رنگ یک ردیف یا ستون و همین‌طور یک یا چند سلول به صورت دستی بسیار ساده است. کافی است از گزینه‌های گروه Font در تب Home استفاده کنید.

برای رنگی کردن یک ردیف در اکسل به صورت زیر عمل کنید:

• روی شماره ردیف در سمت راست کلیک کنید تا انتخاب شود.
• برای انتخاب چند ردیف، ستون یا سلول، کلید Ctrl را نگه دارید و روی شماره‌ی ردیف‌ها یا نام ستون‌ها و یا سلول‌ها کلیک کنید.
• روی آیکون سطل رنگ در تب Home بالای صفحه کلیک کنید.
• رنگ موردنظر را انتخاب کنید.

به همین ترتیب تغییر دادن فونت، ضخیم کردن یا مورب کردن حروف، تغییر دادن فونت و اندازه فونت و غیره به صورت دستی ساده است.

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

تغییر رنگ سطر مورد نظر با استفاده از فرمت گذاری شرطی در اکسل 

در توضیح روش استفاده از Conditional Formatting به مثالی که شاید سوال بسیاری از کاربران اکسل باشد می‌پردازیم:

به عنوان مثال برای تغییر دادن رنگ ردیف عنوان بر اساس مقدار یک سلول خاص، مراحل زیر را طی کنید:

• ابتدا محدوده ردیف‌ها یا جدول را انتخاب کنید.
• در تب Home روی Conditional Formatting کلیک کنید.
• سپس روی Manage Rules یا مدیریت قواعد شرطی کلیک کنید.

• روی New Rule یا ایجاد قاعده‌ی جدید کلیک کنید.

• برای مشخص کردن سلولی که استایل آن تغییر می‌کند روی Use A Formula To Determine Which Cells To Format کلیک کنید.

• برای متفاوت کردن رنگ ردیف عنوان که سلول‌های آن متنی است، از تابع ISTEXT استفاده کنید.
• نکته مهم این است که با استفاده از علامت $ اشاره به ستون یا ردیف را مطلق کنید.
• می‌توانید با زدن F4 آدرس‌دهی و اشاره به ردیف و ستون را مطلق یا نسبی کنید.

به عنوان مثال خروجی فرمول زیر این است که آیا سلول موجود در ستون G و ردیف‌های مختلف، متنی است یا خیر.

• در ادامه روی Format کلیک کنید.
• استایل و فرمت سلول‌های ردیف عنوان جدول را مشخص کنید.
•  روی OK کلیک کنید.
• در پایین پنجره Edit Formatting Rule پیش‌نمایش ظاهر سلول‌ها را بررسی کنید.
• در پایان روی OK کلیک کنید.

• روی Apply کلیک کنید تا نتیجه اعمال فرمتینگ شرطی بر اساس قاعده ایجاد شده را ببینید.

ما در این مثال از تابعی برای شناسایی متنی بودن سلول استفاده کردیم ولیکن می‌توانید هر شرط ساده‌تر یا پیچیده‌تر دیگری را بنویسید.

تغییر رنگ سلول های یک ردیف اکسل با مقایسه اعداد

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

به عنوان مثال فرض کنید که:

• شرط اول این است که ستون A خالی نباشد! برای این شرط از فرمول ساده $A1<>"" استفاده کنید. استفاده از نماد $ برای این است که در اعمال کردن این فرمول برای سلول‌های سایر ستون‌ها، ارجاع به ستون A ثابت و مطلق باقی بماند.
• شرط دوم این است که سلول موجود در ستون B خالی باشد. از فرمول ساده‌ی $B1="" استفاده می‌کنیم.
• شرط سوم این است که در ستون A عبارتی نظیر جمع‌بندی جدول یا مجموع موجود نباشد. به عنوان مثال فرمول $A1<>"Grand Total" به این معنی است که نباید سلول موجود در ستون A، حاوی عبارت متنی Grand Total باشد.

و اما روش تغییر رنگ سلول‌های انتخاب شده در اکسل با توجه به هر سه شرط فوق، از تابع AND برای ترکیب کردن شرایط استفاده می‌کنیم:

بنابراین برای رنگی کردن سطر اکسل بر اساس چند شرط، مراحل زیر را طی کنید:

• سلول‌های جدول را انتخاب کنید.
• در تب Home روی Conditional Formatting کلیک کنید.
• روی Manage Rules کلیک کنید.
• روی New Rule کلیک کنید.
• روی Use A Formula To Determine Which Cells To Format کلیک کنید.
• در کادر فرمول، فرمول موردنظر را بنویسید.
• روی Format کلیک کنید.
• رنگ و ظاهر سلول‌های ردیف را انتخاب کنید.
• در همه پنجره‌های باز شده روی OK کلیک کنید و نتیجه را بررسی کنید.

به همین ترتیب می‌توانید فرمول دیگری برای رنگی و متفاوت کردن ردیف‌های خاصی نظیر ردیف جمع‌بندی اضافه کنید.

ویرایش و حذف قواعد فرمت کردن شرطی در اکسل

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

هر زمان قاعده و قانون جدیدی نیاز به ویرایش کردن دارد مراحل زیر را طی کنید:

• در منوی Home اکسل روی Conditional Formatting کلیک کنید.
• روی Manage Rule یا مدیریت قواعد کلیک کنید.
• قاعده موردنظر را انتخاب کنید.
• سپس روی Edit Rule کلیک کنید و آن را تغییر دهید.
• حذف کردن قواعد اشتباه یا اضافه به کمک دکمه Delete Rule نیز امکان‌پذیر است.

howtogeekاینتوتک