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

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

نحوه تغییر رنگ سلول در اکسل با شرط

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

فیلم آموزش اکسل Microsoft Excel 2021 در فرادرس

کلیک کنید

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

در اینجا می‌خواهیم رنگ سلول‌ها را در محدوده مشخصی از داده‌‌ها به‌گونه‌ای تغییر دهیم که با عوض شدن مقدار داده این کار به صورت خودکار در سایر سلول‌ها نیز انجام شود. برای این منظور با استفاده از قالب‌بندی شرطی اکسل، مقادیر بزرگتر، کوچکتر یا بین دو عدد از داده مد نظر مشخص خواهند شد. به عنوان مثال جدولی شامل داده‌های قیمت گازوئیل در شهرهای مختلف دارید و هدف اصلی نمایش مقادیر بیشتر از ۳٫۷ به رنگ قرمز و مقادیر مساوی یا بزرگتر از ۳٫۴۵ به رنگ سبز است.

برای این کار به ترتیب مراحل زیر را انجام می‌دهیم:

۱. در جدول داده خود، محدوده موردنظر، که در مثال بالا $B$2:$H$10   است، را انتخاب می‌کنیم. (توجه داشته باشید که نام ستون‌ها و همچنین ستون اول شامل اسامی شهر‌ها، انتخاب نمی‌شوند.)

۲. در تب «Home»، به قسمت «Conditional Formatting» و سپس «...New Rule» بروید.

۳. در پنجره «New Formatting Rule» قسمت «Format only cells that contain» را که در بخش بالایی «Select a Rule Type» قرار دارد، انتخاب کنید.

۴. در بخش پایینی باکس «Format Only Cells with section» شرط خود را تعریف کنید. در مثال ما «مقدار سلول» (Cell Value) اعداد بزرگتر از ۳٫۷ است.

سپس برای تعیین رنگ پیش‌زمینه محدوده اعداد مد نظر، بر روی دکمه «Format» کلیک کنید.

۵. در باکس «Format Cells » به تب «Fill» بروید، رنگ دلخواه خود، که در مثال صورتی است، را انتخاب کنید و بر روی دکمه «OK» بزنید.

۶. در پنجره New Formatting Rule پیش‌نمایش رنگ انتخابی در باکس «Preview» قابل مشاهده است. برای تایید نهایی دکمه OK را بزنید.

در نهایت سلول‌های موردنظر در جدول داده‌های مثال، صورتی‌رنگ خواهند شد:

اما چون می‌خواهیم شرط دیگری را نیز در جدول اعمال کنیم، فعالیت همچنان ادامه خواهد داشت. قدم بعدی تغییر رنگ سلول‌ها با مقادیر مساوی یا کمتر از ۳٫۴۵ به رنگ سبز است. بنابراین مجدد بر دکمه New Rule کلیک کرده و مراحل ۳ تا ۶ را به شکل زیر با مشخص کردن شرط‌های مد نظرمان تکرار می‌کنیم.

بعد از اتمام کار و زدن دکمه OK جدول به شکل زیر در خواهد آمد که در آن محدوده‌های تعیین شده دارای شرط به رنگ‌های مختلف قابل مشاهده‌اند:

می‌توانید همین روش را برای تغییر رنگ فونت اعداد بر اساس مقدار آن‌ها بکار ببرید. بنابراین همان طور که در مرحله پنجم توضیح داده شد، در باکس Format Cells به تب «Font» بروید و رنگ دلخواه‌تان را انتخاب کنید.

تغییر استاتیک رنگ سلول با شرط موقتی

در این بخش هدف تغییر رنگ سلول بر اساس مقدار  است و می‌خواهیم که رنگ آن با عوض شدن مقدار داده، بدون تغییر باقی بماند. برای این کار ابتدا با استفاده از تابع «Find All» یا افزونه «Select Special Cells» همه سلول‌های شامل مقدار مورد نظر را پیدا می‌کنیم. سپس فرمت آن‌ها را با استفاده از بخش Format Cells تغییر می‌دهیم. توجه داشته باشید که این حالت در سایر منابع کمتر مورد بحث قرار گرفته‌اند، بنابراین راه‌حل مستقیم و یکپارچه‌ای در این خصوص وجود ندارد. در هر حال اگر به دنبال ثابت نگاه داشتن رنگ سلول به صورت استاتیک هستید، مطابق مراحل زیر عمل کنید. این شرایط تا زمانی که به صورت دستی آن را تغییر نداده‌اید، برقرار هستند.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

جستجو و انتخاب مقادیر

بر حسب اینکه به‌دنبال چه نوع داده‌ای در جدول خود هستید، سناریوهای مختلفی در این خصوص وجود دارد. اگر نیاز به تغییر رنگ سلول‌ها با یک مقدار مشخص (به عنوان مثال ۵۰، ۱۰۰، یا ۳٫۴) دارید، پس از ورود به تب Home و بخش «Editing» بر روی «Find Select» و بعد «...Find» کلیک کنید.

مقدار موردنظر خود را در باکس وارد کنید و دکمه Find All را بزنید.

نکته:‌ با کلیک کردن بر دکمه «Option» که در سمت راست پایین دکمه‌های «Find» و «Replace» قرار دارد و ورود به بخش جستجوی پیشرفته انتخاب‌های متنوعی مانند «Match Case» و «Match entire cell content» خواهید داشت. همچنین می‌توانید از کاراکترهای عمومی ستاره (*)   برای پیدا کردن رشته‌ای از کاراکترها یا علامت سوال (؟)   برای پیدا کردن یک کاراکتر تک استفاده کنید. به عنوان نمونه در مثال قبل، برای پیدا کردن همه قیمت‌های موجود در جدول بین ۳٫۷ و ۳٫۷۹۹۹ محدوده اعداد را به شکل زیر مشخص می‌کنیم.

سپس بر روی مقادیر پیدا شده، که در قسمت پایین پنجره Find and Replace قابل مشاهده‌اند، کلیک کرده و به کمک دکمه «Ctrl+A» آن‌ها را انتخاب می‌کنیم. در انتها نیز دکمه «Close» را می‌زنیم.

به این شکل همه سلول‌های دارای مقدار مشخص شده، به کمک تابع Find All در اکسل قابل انتخاب هستند. البته در مثال قبل نیاز به پیدا کردن همه قیمت‌های گازوئیل بالاتر از ۳٫۷ داریم، اما استفاده از تابع Find and Replace کمک زیادی در این خصوص نمی‌کند. برای رفع این مشکل ابزار دیگری به نام «Select Special Cells» وجود دارد که از طریق آن می‌توان همه مقادیر موجود در یک محدوده معین، به عنوان مثال مقادیر بین ۱- و ۴۵ یا حداکثر/حداقل مقدار موجود در یک ستون، ردیف یا محدوده جدول داده‌ها را پیدا کرد و رنگ آن‌ها را تغییر داد.

با کلیک بر روی دکمه «Select by Value» و مشخص کردن محدوده جستجو در پنجره باز شده، این موضوع به راحتی قابل انجام است. در مثال مورد بحث، هدف پیدا کردن اعداد بزرگتر از ۳٫۷ است. با انجام مراحل فوق مشخص می‌شود که ۲۳ سلول شامل این شرط می‌شوند.

تغییر رنگ سلول‌های انتخاب شده با استفاده از Format Cells

فرقی ندارد که همه سلول‌های دارای یک مقدار مشخص را به کمک کدام روش Find and Replace یا Select Special Cells پیدا کرده باشید، در هر حال هدف نهایی، تغییر رنگ سلول در صورت عوض شدن مقدار آن است. برای این منظور ابتدا باید باکس Format Cells را باز کرد. راه میانبر این کار استفاده از کلید ترکیبی 1+Ctrl است. البته می‌توان به کمک راست کلیک بر روی بخش مد نظر و سپس انتخاب Format Cells از منوی باز شده یا با استفاده از تب Home و انتخاب مسیر ...Cells group > Format > Format Cells نیز همین کار را انجام داد. حال در این مثال می‌خواهیم رنگ سلول را به نارنجی تغییر دهیم.

اگر در نظر دارید رنگ سلول را بدون هیچ تغییری در سایر موارد عوض کنید نیز کافی‌ست بر روی دکمه Fill color کلیک کرده و سپس رنگ دلخواه‌تان را انتخاب کنید.

و در نهایت سلول‌های مد نظر به رنگ نارنجی در خواهند آمد:

بر خلاف حالت قبل، در اینجا رنگ سلول ثابت است و با عوض کردن مقدار داده تغییری نمی‌کند.

تغییر رنگ یک سلول مشخص برای سلول های خالی و خطاهای فرمول

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

• استفاده از فرمول اکسل
• تغییر رنگ استاتیک

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

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

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

۱. در تب Home و بخش «Styles» مسیر Conditional Formatting > New Rule را انتخاب کنید.

۲. در پنجره New Formatting Rule به بخش «Use a formula to determine which cells to format» بروید و سپس یکی از فرمول‌های زیر را در قسمت «Format values where this formula is true» وارد کنید:

• =IsBlank()   برای تغییر رنگ سلول‌های خالی
• =IsError()   برای تغییر رنگ سلول‌های دارای فرمول با پیغام خطا

چون در اینجا هدف تغییر رنگ سلول‌های خالی است، فرمول =IsBlank()   را وارد کرده و دکمه ماوس را بین دو پرانتز می‌گذاریم. سپس برای انتخاب محدوده مشخص بر روی دکمه «Collapse Dialoge» در قسمت راست پنجره کلیک می‌کنیم. البته محدوده موردنظر به صورت دستی نیز قابل تایپ است (به عنوان مثال =IsBlank(B2:H12)  )

۳. با کلیک بر روی دکمه Format در تب Fill رنگ دلخواه خود در سلول را انتخاب کنید و در نهایت دکمه OK را بزنید.

پیش‌نمایش فعالیت به شکل زیر خواهد شد:

۴. اگر پیش‌نمایش مورد تایید باشد، پس از زدن دکمه OK رنگ سلول‌های خالی تغییر خواهند کرد:

برای دیدن تصویر بزرگتر روی عکس کلیک کنید.

تغییر رنگ سلولهای خاص به صورت استاتیک

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

۱. پس از انتخاب جدول داده یا محدوده اعداد، با فشردن دکمه F5 پنجره «Go To» را باز کرده و روی دکمه «...Special» کلیک کنید.

۲. برای انتخاب همه سلول‌های خالی در باکس «Go to Special» دکمه «Blanks» را بزنید.

با کمک مسیر Formulas > Errors همه سلول‌های دارای فرمول با پیغام خطا هایلایت خواهند شد. البته بر حسب نیاز از سایر موارد مشخص شده در تصویر هم می‌توانید استفاده کنید.

۳. در نهایت با این روش، هر نوع تغییر فرمت یا عوض کردن رنگ سلول قابل انجام است.

جمع‌بندی

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