2 راه برای تغییر پس زمینه در اکسل

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

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

تغییر رنگ پس زمینه سلول پویا

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

راه حل: برای این کار، تابع "قالب بندی شرطی" اکسل به سلول های رنگی با اعداد بزرگتر از X، کمتر از Y یا بین X و Y ارائه می شود.

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

توجه داشته باشید: اسکرین شات در نسخه 2010 این برنامه گرفته شده است. اما این تأثیری ندارد، زیرا توالی اقدامات یکسان است، صرف نظر از اینکه شخص از کدام نسخه - آخرین یا نه - استفاده می کند.

بنابراین کاری که باید انجام دهید (گام به گام):

1. سلول هایی را که رنگ باید در آنها ویرایش شود انتخاب کنید. به عنوان مثال، محدوده $B$2:$H$10 (نام ستون ها و ستون اول که نام ایالت ها را فهرست می کند، از نمونه حذف می شوند).

2. با کلیک بر روی "خانه" در گروه "سبک". یک مورد وجود خواهد داشت "قالب بندی مشروط". در آنجا نیز باید انتخاب کنید "قانون جدید". در نسخه انگلیسی اکسل، ترتیب مراحل به شرح زیر است: «صفحه اصلی»، «گروه سبک‌ها»، «قالب‌بندی مشروط > قانون جدید».

3. در پنجره باز شده، کادر را علامت بزنید "فقط سلول هایی را قالب بندی کنید که حاوی" (در نسخه انگلیسی «فقط سلول‌هایی را قالب‌بندی کنید که حاوی هستند».

4. در پایین این پنجره در زیر کتیبه "فقط سلول هایی را قالب بندی کنید که شرایط زیر را دارند" (فقط سلول‌ها را با قالب‌بندی) می‌توانید قوانینی را که قالب‌بندی انجام می‌شود اختصاص دهید. ما فرمت را برای مقدار مشخص شده در سلول ها انتخاب کرده ایم، همانطور که در تصویر مشاهده می کنید، باید بزرگتر از 3.7 باشد: 

5. سپس بر روی دکمه کلیک کنید "قالب". پنجره ای با ناحیه انتخاب رنگ پس زمینه در سمت چپ ظاهر می شود. اما قبل از آن، باید برگه را باز کنید "پر کردن" («پر کردن»). در این مورد قرمز است. پس از آن، بر روی دکمه "OK" کلیک کنید.

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

در نتیجه، چیزی شبیه این دریافت می کنید:

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

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

بالاترین و کمترین قیمت سوخت را در ایالت های مختلف نشان می دهد و شما می توانید بلافاصله تعیین کنید که در کجا وضعیت خوش بینانه تر است (البته در تگزاس).

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

در نتیجه، جدولی مانند زیر دریافت خواهید کرد:

چگونه می توان رنگ سلول را حتی در صورت تغییر مقدار یکسان نگه داشت؟

کار: باید پس زمینه را طوری رنگ کنید که هرگز تغییر نکند، حتی اگر پس زمینه در آینده تغییر کند.

راه حل: با استفاده از تابع اکسل تمام سلول های دارای تعداد مشخص را پیدا کنید "پیدا کردن همه" «یافتن همه» یا افزونه "انتخاب سلول های خاص" ("Select Special Cells")، و سپس قالب سلول را با استفاده از تابع ویرایش کنید "فرمت سلول ها" («قالب‌بندی سلول‌ها»).

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

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

بسته به نوع ارزش خاصی که باید پیدا شود، چندین روش ممکن وجود دارد.

اگر نیاز به تعیین سلول هایی با یک مقدار خاص با پس زمینه خاص دارید، باید به برگه بروید "خانه" و انتخاب کنید "یافتن و انتخاب کردن" - "یافتن".

مقادیر مورد نیاز را وارد کرده و روی آن کلیک کنید "پیدا کردن همه".

کمک: می توانید روی دکمه کلیک کنید "گزینه ها" در سمت راست برای استفاده از برخی تنظیمات اضافی: مکان جستجو، نحوه مشاهده، رعایت حروف بزرگ و کوچک و غیره. همچنین می توانید کاراکترهای اضافی مانند ستاره (*) بنویسید تا تمام خطوط حاوی این مقادیر را پیدا کنید. اگر از علامت سوال استفاده می کنید، می توانید هر کاراکتری را پیدا کنید.

در مثال قبلی، اگر بخواهیم تمام قیمت‌های سوخت را بین 3,7 تا 3,799 دلار پیدا کنیم، می‌توانیم جستجوی خود را اصلاح کنیم.

حالا هر یک از مقادیری را که برنامه در پایین کادر محاوره ای پیدا کرده انتخاب کنید و روی یکی از آنها کلیک کنید. پس از آن، کلید ترکیبی "Ctrl-A" را فشار دهید تا تمام نتایج انتخاب شوند. بعد، روی دکمه "بستن" کلیک کنید. 

در اینجا نحوه انتخاب همه سلول ها با مقادیر خاص با استفاده از ویژگی Find All آورده شده است. در مثال ما، ما باید تمام قیمت های سوخت بالای 3,7 دلار را پیدا کنیم، و متأسفانه اکسل به شما اجازه نمی دهد این کار را با استفاده از تابع Find and Replace انجام دهید.

"بشکه عسل" در اینجا آشکار می شود زیرا ابزار دیگری وجود دارد که به چنین کارهای پیچیده ای کمک می کند. به آن Select Special Cells می گویند. این افزونه (که باید به طور جداگانه در اکسل نصب شود) کمک خواهد کرد:

• همه مقادیر را در یک محدوده خاص پیدا کنید، به عنوان مثال بین -1 و 45،
• حداکثر یا حداقل مقدار را در یک ستون بدست آورید،
• یک رشته یا محدوده پیدا کنید،
• سلول ها را بر اساس رنگ پس زمینه و موارد دیگر پیدا کنید.

پس از نصب افزونه، به سادگی روی دکمه کلیک کنید "انتخاب بر اساس مقدار" ("Select by Value") و سپس عبارت جستجو را در پنجره افزونه اصلاح کنید. در مثال ما به دنبال اعداد بزرگتر از 3,7،XNUMX هستیم. مطبوعات "انتخاب کنید" ("انتخاب")، و در یک ثانیه نتیجه ای مانند زیر دریافت خواهید کرد:

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

تغییر پس زمینه سلول های انتخاب شده از طریق پنجره "Format Cells".

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

برای این کار باید یک پنجره باز کنید "قالب سلولی"با فشردن کلید Ctrl + 1 (همچنین می توانید روی سلول های انتخاب شده راست کلیک کرده و روی مورد "Cell formatting" کلیک چپ کنید) و قالب بندی مورد نیاز خود را تنظیم کنید.

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

اگر نیاز به ویرایش رنگ پس زمینه بدون تغییر سایر پارامترهای ظاهری دارید، می توانید به سادگی روی آن کلیک کنید "پر کردن رنگ" و رنگی را انتخاب کنید که کاملا به شما می آید.

نتیجه جدولی مانند این است:

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

ویرایش رنگ پس زمینه برای سلول های خاص (خالی یا دارای خطا هنگام نوشتن فرمول)

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

استفاده از فرمول برای ویرایش پس زمینه

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

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

1. روی زبانه "خانه" باید روی آن کلیک کنید "قالب بندی مشروط" ->  "قانون جدید" (همانند مرحله 2 از بخش اول "تغییر رنگ پس زمینه به صورت پویا".

2. بعد، شما باید مورد را انتخاب کنید «از فرمول برای تعیین…».

3. فرمول را وارد کنید =IsBlank() (ISBLANK در نسخه)، اگر می خواهید پس زمینه یک سلول خالی را ویرایش کنید، یا =IsError() (ISERROR در نسخه)، اگر باید سلولی را پیدا کنید که در آن یک فرمول اشتباه نوشته شده است. از آنجایی که در این حالت باید سلول های خالی را ویرایش کنیم، فرمول را وارد می کنیم =IsBlank()و سپس مکان نما را بین پرانتز قرار دهید و روی دکمه کنار فیلد ورودی فرمول کلیک کنید. پس از این دستکاری ها، محدوده ای از سلول ها باید به صورت دستی انتخاب شوند. علاوه بر این، شما می توانید محدوده را خودتان مشخص کنید، به عنوان مثال، =IsBlank(B2:H12).

4. بر روی دکمه “Format” کلیک کنید و رنگ پس زمینه مناسب را انتخاب کنید و همه کارها را همانطور که در بند 5 قسمت “تغییر رنگ پس زمینه سلول پویا” توضیح داده شده انجام دهید و سپس روی “OK” کلیک کنید. در آنجا می توانید رنگ سلول را نیز ببینید. پنجره چیزی شبیه به این خواهد بود.

5. اگر پس زمینه سلول را دوست دارید، باید روی دکمه "OK" کلیک کنید و تغییرات بلافاصله در جدول اعمال می شود.

تغییر استاتیک رنگ پس زمینه سلول های خاص

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

اگر نیاز به تغییر دائمی سلول های خاص (خالی یا حاوی خطا) دارید، این دستورالعمل ها را دنبال کنید:

1. سند یا چندین سلول خود را انتخاب کنید و F5 را فشار دهید تا پنجره Go To باز شود و سپس دکمه را فشار دهید "برجسته".
2. در کادر محاوره ای که باز می شود، دکمه «Blanks» یا «Emptycells» (بسته به نسخه برنامه – یا انگلیسی) را برای انتخاب سلول های خالی انتخاب کنید.
3. اگر نیاز به برجسته کردن سلول‌هایی دارید که فرمول‌هایی با خطا دارند، باید مورد را انتخاب کنید "فرمول ها" و کنار کلمه "Errors" یک چک باکس بگذارید. مطابق تصویر بالا، سلول ها را می توان با توجه به هر پارامتری انتخاب کرد و هر یک از تنظیمات توصیف شده در صورت لزوم در دسترس است.
4. در نهایت، باید رنگ پس‌زمینه سلول‌های انتخاب شده را تغییر دهید یا آنها را به روش دیگری سفارشی کنید. برای این کار از روشی که در بالا توضیح داده شد استفاده کنید.

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

چگونه از اکسل بیشترین بهره را ببریم؟

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

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

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

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