قالببندی شرطی (Conditional Formatting)- بخش 3
08 اسفند 1400
دقیقه
در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش دوم قالب بندی شرطی (Conditional Formatting) پرداختیم، در این مقاله به ادامه آموزش می پردازیم.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش دوم قالب بندی شرطی (Conditional Formatting) پرداختیم، در این مقاله به ادامه آموزش می پردازیم.
برای مطالعه بخش اول روی لینک کلیک کنید.
لیستی از دادهها در اختیار ما قرار داده شده است، چگونه میتوان دادههای آخر هفته را با رنگ خاصی مشخص کرد؟
فایلی به نام Weekendformatting.xlsx (تصویر 27-24 را ببینید) حاوی دادههای فراوانی است. میخواهیم تمامی روزهای شنبه و یکشنبه را به رنگ قرمز مشخص کنیم. برای انجام این کار ابتدا فرمول =WEEKDAY(C6,2) را از سلول D6 به محدوده D7:D69 کپی میکنیم. انتخاب Type=2 برای تابع WEEKDAY عدد 1 را برای هر دوشنبه، عدد 2 را برای هر سهشنبه و … برمیگرداند، بنابراین تابع مربوطه عدد 6 را برای هر شنبه و عدد 7 را برای هریک شنبه برمیگرداند.
تصویر 27-24 استفاده از تابع WEEKDAY برای مشخصکردن روزهای هفته به رنگ قرمز
حالا محدوده D6:D69 را انتخاب میکنیم، روی گزینه Conditional Formatting کلیک کرده سپس روی گزینه New Rule کلیک میکنیم و گزینه فرمول را انتخاب میکنیم. کادر محاورهای را همچون تصویر 28-24 پر میکنیم.
بعد از اینکه بر دکمه Ok کلیک کردیم، هریک از تاریخهای روزهای هفته که برابر با 6 (روز شنبه) و یا 7 (روز یکشنبه) باشد به رنگ قرمز نشان داده میشود. فرمول =OR(D6=6,D6=7) دلالت بر این دارد که مدخل سلول دارای رقم 6 یا 7 رنگ قرمز فونت را فعال خواهد نمود. البته در اینجا می توان از گزینه Format Only Cells That Contain (قالببندی تنها سلولهایی که حاوی …) و از >=6 و یا>5 برای دستیابی به همان قالببندی استفاده کرد.
تصویر 28-24 تنظیم یکقاعده برای نمایش روزهای آخر هفته به رنگ قرمز
مربی بسکتبال به هریک از بازیکنان بر اساس توانایی بازیکن در دفاع کردن، حمله کردن و یا میانداری رتبهای میان یک تا ده داده است، آیا میتوان کاربرگی ایجاد کرد که به شکل بصری توانایی هریک از بازیکنان در موقعیتی که مربی برای آنها تعیین کرده است را نشان دهد؟
فایلی به نام Basketball.xlsx که در تصویر 29-24 نشاندادهشده حاوی رتبههایی است که به 20 بازیکن برای هریک از پستها و پستهای (1= مدافع، 2= حمله، 3= میاندار) بازی شده توسط هر بازیکن داده شده است. در اینجا هدف آن است که سلولهایی را که دارای رتبهبندیهای هر بازیکن برای پستهایی که به وی اختصاصدادهشده است را قرمز کنیم.
تصویر 29-24 رتبهبندی توانایی هر بازیکن برای بازی در پستش
کار را با انتخاب محدوده C3:E22 که حاوی رتبههای بازیکنها است آغاز میکنیم. بر روی گزینه Conditional Formatting و سپس Manage Rules کلیک میکنیم. حالا بر روی گزینه New Rule کلیک کرده و گزینه فرمول را انتخاب میکنیم. اکنون کادر محاورهای را همانند تصویر 30-24 پر میکنیم.
تصویر 30-24 تنظیم یکقاعده برای نمایش رتبههای بازیکنان به رنگ قرمز.
فرمول =$A3=C$1 موقعیت هر بازیکن را با سرستون (1،2 و یا 3) در ردیف اول مقایسه میکند. اگر موقعیت تخصیصدادهشده به بازیکن 1 (مدافع) باشد رتبههای او در ستون C که رتبه مدافع است قرمز خواهد شد. درست همانند همان، اگر موقعیت تخصیصدادهشده به بازیگر عدد 2 باشد رتبه حمله او در ستون D به رنگ قرمز درخواهد آمد. بالاخره اگر موقعیت تخصیصدادهشده به بازیگر 3 باشد رتبه میانداری او در ستون E به رنگ قرمز درخواهد آمد. توجه کنید که اگر علامت دالر را به همراه حرف A و عدد 1 به فرمول اضافه نکنید، فرمول بهدرستی در سراسر سلولها کپی نخواهد شد.
همچنین توجه کنید که اکسل 2019 این امکان را در اختیار قرار میدهد که قالببندی شرطی را با فرمولهایی که به دادههایی در سایر کاربرگها ارجاع دارند ایجاد کنید.
گزینه Stop If True در کادر محاورهای Manage Rules چه کاری انجام میدهد؟
فرض کنید برای قاعده ای گزینه Stop If True را انتخاب کرده باشید. اگر سلولی این قاعده را برآورده کند، تمامی قواعد متقدم بعدی نادیده گرفته میشوند. برای نمایش گزینه Stop If True از فایلی به نام Incom.xlsx استفاده میکنیم. این فایل درآمد متوسط هریک از ایالات آمریکا را در سالهای بین 1984 تا 2010 نشان میدهد. فرض کنید (همانطور که در تصویر 31-24 نشاندادهشده) که میخواهید پیکانهای سربالایی ده ایالتی را که بالاترین درآمد متوسط را در سال 2010 داشته باشند مشخص کنند و برای سایر ایالات هیچ آیکونی نمایش داده نشود. نکته آن است که در اولین قالببندی No Format را برای 40 ایالتی که دارای کمترین درآمد متوسط هستند انتخاب کرده و بعد گزینه Stop If True را انتخاب کنید. پس از آن مجموعه آیکون موردنظر را ایجاد کنید.
تصویر 31-24 استفاده از پیکانهای سربالا برای مشخصکردن 10 ایالت با بیشترین درآمد متوسط
تنظیماتی که پیکانهای 40 ایالت با کمترین درآمد متوسط در سال 2010 را پنهان میسازند در تصویر 32-24 نشاندادهشده است.
تصویر 32-24 تنظیماتی برای اطمینان حاصل کردن از اینکه ده درآمد متوسط برتر با پیکانهای سربالا نمایش داده شوند.
تنظیمات قاعده دوم در تصویر 33-24 نمایشدادهشده است. توجه کنید که 20 درصد برتر ایالات توسط فرمول .2*50 = 10 ایجاد میشود.
تصویر 33-24 تنظیماتی برای مطمئن شدن از اینکه 10 ایالات دارای درآمد متوسط بیشتر دارای پیکانهای سربالا خواهند بود.
چگونه میتوان از گزینه Format Painter برای کپیکردن یک قالببندی شرطی استفاده نمود؟
گزینه Format Painter (آیکون قلم موی نشاندادهشده در تصویر 34-24) شما را قادر میسازد تا قالببندی موردنظر (شامل قالببندیهای شرطی) را از هر سلولی یا گروهی از سلولها به هر گروه دیگری از سلولها اعمال نمایید. میتوان بهسادگی سلول یا گروه سلولهای دارای قالببندی موردنظر را انتخاب کرد و بعد روی آیکون گزینه Format Painter کلیک کرد. بعد از آن از قلم موی موردنظر برای انتخاب سلولهایی که میخواهید قالببندی بر آنها اعمال شود استفاده نمود.
تصویر 34-24 آیکون Format Painter در تب Home
اگر تمایل داشته باشید که قالببندی را به محدودهای از سلولها که در مجاورت هم نیستند اعمال کنید، روی آیکون Format Painter دو بار کلیک کنید و بعد تمامی سلولهایی را که میخواهید قالببندی بر آنها کپی شود را انتخاب نمایید. برای اینکه حالت Format Painter را از بین ببرید دوباره روی آیکون آن کلیک کنید.
مسئلههای این فصل:
- با استفاده از دادهها در فایلی به نام Sandp.xlsx از قالببندی شرطی برای موقعیتهای زیر استفاده کنید:
- هر ماه که ارزش شاخص اساندپی افزایش پیدا کرده رابه حالت پررنگ (Bold) تغییر داده و هر ماه را که ارزش شاخص اساندپی کاهش پیدا کرده را به حالت زیرخط دار (Underline) تغییر دهید.
- هر ماه که شاخص اساندپی به حداکثر 2 درصد افزایش پیدا کرده را سبزرنگ کنید.
- بزرگترین مقدار ارزش شاخص اس اند پی را به رنگ قرمز و کوچکترین را به رنگ بنفش تغییر دهید.
2. با استفاده از دادههای فایلی به نام Toysrusformat.xlsx تمام فصلهایی را که درآمد حداقل در طی دو فصل گذشته افزایش پیدا کرده است را به رنگ قرمز مشخص کنید. درآمدهای فصل چهارم را به رنگ آبی و فصل اول را به رنگ قرمز درآورید.
3. فایلی به نام Test.xlsx حاوی نمرات امتحانی دانشآموزان میباشد. ده دانشآموز برتر کلاس نمره A دریافت کردهاند، بیست دانشآموز دیگر نمره B و سایر دانشآموزان نمره C دریافت نمودهاند. نمرات A را به رنگ قرمز، نمرات B را به رنگ سبز و نمرات C را به رنگ آبی درآورید. اشاره: فرمول =LARGE(D4:D63,10) به شما دهمین نمره برتر این آزمون را میدهد.
4. در فایلی به نام Weekendformatting.xlsx تمامی روزهای تعطیل را به رنگ قرمز تغییر دهید. تمامی روزهای دیگر که در اولین ده روز ماه رخ میدهند را نیز به رنگ آبی درآورید. وقتی هر دورنگ به سلولها اعمال میشود کدام رنگ متقدم است؟
5. فرض کنید هریک از کارگران بخش مالی شرکت مایکروسافت به یکی از چهار گروه منصوب شدهاند. ناظر هر یک از این گروهها به هر کارگر از صفر تا ده رتبه داده است و هریک از کارگران میزان رضایتمندی خود را از چهار گروه رتبهبندی کردهاند. (فایلی به نام Satissuper.xlsx را ببینید). رتبهبندیهای ناظران و رتبهبندیهای میزان رضایت هریک از کارگران بر اساس گروهی که هر یک از کارگران به آن تعلق دارند را مشخص نمایید.
6. فایلی به نام Varianceanalysis.xlsx حاوی پیشبینی سود ماهانه و فروش واقعی ماهیانه میباشد. واریانس (اختلاف) فروش یک ماه برابر است با:
(فروش واقعی – فروش پیشبینیشده) / فروش پیشبینیشده
تمامی ماههایی دارای واریانس مطلوب حداقل 20 درصد را با رنگ قرمز مشخص کنید. تمامی ماههای دارای واریانس نامطلوب کمتر از 20 درصد را به رنگ سبز مشخص کنید.
7. برای مثالِ قیمت داروها (فایلی به نام Offsetcost.xlsx از فصل 22 بنام تابع OFFSET را ببینید) کاربرگ را بهگونهای قالببندی کنید که تمامی هزینههای فاز اول به رنگ قرمز نمایشدادهشده، هزینههای فاز دوم به رنگ سبز و هزینههای فاز سوم به رنگ بنفش نمایش داده شوند.
8. فایلی به نام Names.xlsx حاوی فهرستی از اسامی میباشد. تمامی نامهای دو بار تکرار شده را به رنگ سبز و تمام نامهای حاوی حروف Ja را به رنگ قرمز مشخص کنید.
9. فایلی به نام Duedates.xlsx حاوی تاریخ پرداخت فاکتورهای مختلفی است. فاکتورهایی را که تاریخ پرداخت آنها انتهای ماه آینده است را با رنگ قرمز مشخص کنید.
10. در فایلی به نام Historicalinvest.xlsx قالببندی شرطی را بهگونهای با سه آیکون تنظیم کنید که 10 درصد از بازدهها دارای پیکان سربالا، ده درصد دارای پیکان سرپایین و 80 درصد دارای پیکان افقی باشند.
11. فایلی به نام Nbasalaries.xlsx حاوی دستمزدهای بازیکنهای NBA به میلیون دلار میباشد. نوارهای داده را بهگونهای تنظیم کنید که این دادهها را به شکلی خلاصه نمایش دهند. بازیکنهای دارای دستمزد کمتر از 1 میلیون دلار میبایست کوتاهترین نوار داده و بازیکنانی که بیش از 15 میلیون دلار میگیرند میبایست بلندترین نوار داده را داشته باشند.
12. مقیاس سه رنگی برای نمایش خلاصه دستمزد بازیکنان NBA تنظیم کنید. رنگ ده درصد کمترین تمام دستمزدها را به رنگ سبز و ده درصد بالاترین دستمزدها را به رنگ قرمز تنظیم کنید.
13. پنج آیکون برای نشاندادن خلاصهای از دادههای بازیکنهای NBA استفاده کنید. برای هریک از این آیکونها نقاط توقفی در دستمزدهای 3 میلیون دلاری، 6 میلیون دلاری، 9 میلیون دلاری و 12 میلیون دلاری تعیین کنید.
14. چگونه میتوانید تنظیمات را بهگونهای انجام دهید که آیکونها برای بازیکنهایی که دستمزدهایی میان 7 میلیون دلار و 8 میلیون دلار دارند نمایش داده شوند؟ راهنمایی: از گزینه Stop If True استفاده کنید.
15. فایلی به نام Fractiondefective.xlsx حاوی درصدی از دستگاههای معیوب تولید روزانه است. تولید روزانه اگر دو درصد یا کمتر از واحدهای تولیدی معیوب باشند تولیدی مطلوب محسوب میشود. تمامی روزهای دارای تولید مطلوب را با پرچمی سبزرنگ مشخص کنید. در کاربرگ دیگری تمامی روزهای مطلوب را با پرچم قرمز رنگی مشخص کنید. راهنمایی: از گزینه Stop If True برای مطمئن شدن از اینکه هیچ آیکونی در سلولهای حاوی ارقامی کمتر از 2 ظاهر نخواهد شد استفاده کنید.
16. فایلی به نام Globalwarming2011.xlsx حاوی میانگین دمای جهان در سالهای 1881 تا 2011 به درجه سلسیوس میباشد. از مقیاسهای رنگی برای اطمینان حاصل کردن از اینکه دمای پایین با رنگ آبی، دمای متوسط با رنگ زرد و دمای بالا با رنگ قرمز مشخص شوند استفاده کنید.
17. فرض کنید برای صندوق پسانداز دانشگاه فرزند خود پول پسانداز میکنید. میخواهید هرسال مقدار یکسانی از پول را به صندوق واریز کنید. هدف شما آن است که 100000 دلار پول پسانداز کنید. سود سرمایهگذاری سالیانه از 4 درصد تا 15 درصد میباشد و تعداد سالهای سرمایهگذاری از 5 تا 15 سال میباشد، میزان مشارکت سالیانه خود را مشخص نمایید. فرض کنید میتوانید سالیانه 10000 دلار پسانداز کنید. حالا از قالببندی شرطی استفاده کنید تا برای هریک نرخهای سود سالیانه، حداقل سالی را که برای جمعآوری 100000 دلار لازم است را مشخص کنید.
18. فایلی به نام Amazon.xlsx حاوی درآمدهای فصلی شرکت Amazon.com میباشد. از قالببندی شرطی استفاده کنید تا مطمئن شوید فروش هریک از فصلها با رنگ متفاوتی مشخص شده باشد.
19. قالببندی شرطی ای تنظیم کنید که محدودهA1:H8 را مثال صفحه شطرنج با رنگهای سفید و سیاه متناوب رنگآمیزی کند. تابعROW() شماره ردیف یک سلول و تابع Column() شماره ستون یک سلول را میدهند.
20. هنگامی که دانشآموزان ویویان امتحان حسابداری میدهند، به آنها گفته میشود که نام مستعار ایمیل (نامی که بهجای آدرس ایمیل نوشته میشود) خود را بر برگه امتحانی ماشینخوان بنویسند. اغلب آنها دچار اشتباه میشوند. ویویان فهرستی از نامهای واقعی ایمیل دانشآموزان در کلاس دارد. او مایل است نام آدرسهای ایمیل غیرواقعی که توسط دانشآموزان وارد شده را به رنگ زرد مشخص کند. این کار به او کمک میکند تا بهآسانی برگهها را تصحیح کند. فایلی به نام Scantrons حاوی اطلاعات ضروری است. به ویویان کمک کنید.
21. فایلی به نام Top5.xlsx حاوی درآمدهای 50 شرکت است. برای هرسال، شرکتهایی را که پنج درآمد بیشتر را دارند را مشخص نمایید. راهنمایی: فرمول: =LARGE(B1:B50,5) میتواند پنجمین شرکت دارای بالاترین رقم را در محدوده B1:B50 را به شما نشان دهد.
22. فایلی به نام Threetimes.xlsx حاوی فهرستی از نامها میباشد. هریک از نامهایی را که حداقل سه بار تکرار شدهاند را مشخص نمایید.
23. فایلی به نام GNP.xlsx حاوی سوابق سهماهه تولید ناخالص ملی ایالات متحده میباشد. از مزیت نوارهای داده برای مدیریت مقادیر منفی استفاده کنید تا درصد نرخهای رشد سهماهه تولید ناخالص ملی را مشخص نمایید.
24. از دادههای فایلی به نام Globalwarming2011temp.xlsx (از پوشه Templates) استفاده کنید، سالهایی (و نه دماهایی) را که دما در آن بالای حد متوسط است را مشخص نمایید.
25. فایلی به نام Accountsms.xlsx (تصویر 35-24 را ببینید) که دستمزدهای هفتگی هزاران دلاری (فهرست شده به تاریخ زمان پرداخت) پرداخت شده به مشاوران مالی بسیار حرفهای را نشان میدهد. مثلاً بریتنی در اولین هفته 91000 دلار کسب کرده و در آخرین هفته 57000 دلار کسب کرده است. این موارد را دنبال کنید:
- از گزینه فرمول برای مشخصکردن نام هر فرد به رنگ زرد استفاده کنید
- از گزینه فرمول برای مشخصکردن دستمزد هفته اول هر شخص به رنگ نارنجی استفاده کنید.
تصویر 35-24 دادههای مسئله شماره 25
26. فایلی به نام Shading.xlsx حاوی دادههای 25 فصل از فروش است. ردیفهای متناوب را به رنگهای سبز و زرد رنگآمیزی کنید. سپس آنها را با چهار ردیف به رنگ سبز و چهار ردیف به رنگ زرد به شکل متناوب رنگآمیزی کنید و به همین ترتیب ادامه دهید.
27. فایلی به نام Problem27data.xlsx حاوی نام چندین نفر است. نامی در سلول E2 وارد میکنیم. از قالببندی شرطی استفاده کنید تا نام هر شخصی را که وارد میشود را مشخص نمایید.
28. فایلی به نامProblem28data.xlsx حاوی اطلاعاتی درباره رأی دهندگان در 20 حوزه رأیگیری است. عدد محدوده رأیگیری را در سلول I2 وارد میکنیم. وظیفه شما آن است که قالببندی ای تنظیم کنید که تمام ردیفهای داده شامل محدودههای لیست شده در سلول I2 را مشخص نماید.
29. در فایلی به نام Problems29data.xlsx ستون های E-I حاوی اطلاعات زیر است. از قالببندی شرطی برای مشخصکردن هر ردیف از دادهها برای روزهایی که سهام فروخته شده استفاده کنید. دادههای زیر را نشان دهید:
- قیمت اولیه یک سهام
- آیا در ابتدای روز صاحب سهام بودیم؟
- آیا در آن روز سهام فروختیم؟
- آیا در انتهای روز مالک سهام بودیم؟
30. فایلی به نام Problem30data.xlsx حاوی آمار بازیکنهای خط حمله NFL است. در سلولJ21 تعداد یاردهای پیموده شده توسط هر بازیکن را به شکل عددی (X) وارد میکنم. از قالببندی شرطی برای مشخصکردن تمامی اطلاعات بازیکنهای خط حمله که دارای YDs>=x هستند استفاده کنید. اگر در سلول J2 عدد 4500 را وارد کنیم بنابراین اطلاعات هر بازیکن خط حمله دارای YDs>=4500 میبایست مشخص شود.
31. از اطلاعات فایلی به نام Amazon.Xlsx استفاده کنید، فصلهایی را که در آن فروش از فروش دو فصل آخر بیشتر بوده است را پیدا و مشخص نمایید.
32. در فایلی به نام Problem32data.xlsx اطلاعاتی از 2423 مبادله مالی در اختیار شما قرار داده شده است. اعتقاد دارید اگر یکی از دو شرط زیر درست باشند تقلبی رخداده است:
- مبلغ چکها حداقل 500 دلار باشد و هیچ کسی آنها را تأیید نکرده باشد.
- مبلغ چک حداقل 500 دلار باشد و یک شخص آنها را تأیید و نقد کرده باشد.
تمامی این مبادلات مشکوک را مشخص نمایید.
33. فایلی به نام Problem33data.xlsx اطلاعاتی درباره 2000 شرکت را در اختیار شما قرار داده است. در سلول K3 نام کشور وارده شده و در سلول L3 صنعت موردنظر وارد شده است؛ بنابراین صفحه گستردهای که تنظیم میکنید میبایست تمامی اطلاعات شرکتها در کشور و صنعت انتخاب شده را مشخص نماید.
34. فایلی به نام problem34data.xlsx حاوی آدرسهایی است که چکها به آنجا ارسال شدهاند. اگر آدرس حاوی عبارت P.O.Box باشد احتمالاً مبادله مالی متقلبانهای در جریان بوده است. از قالببندی شرطی استفاده کنید تا تمامی آدرسهای حاوی P.O.Box را مشخص کنید. راهنمایی: تابع SEARCH به بزرگی و کوچکی حروف حساس نیست و تابع ISERROR اگر و تنها اگر فرمولی خطا به دست بیاورد عبارت True را برمیگرداند.
35. فایلی به نام Problem35data.xlsx چکهایی که نقد شدهاند را به نظم عددی مرتب کرده است. برخی اوقات عدد یک چک جاافتاده است. از قالببندی شرطی استفاده کنید تا تمامی عدد چکهایی که چکهای ماقبل آنها نقد نشدهاند را مشخص کنید. مثلاً چک شماره 15 میبایست مشخص شود چون چک شماره 14 نقد نشده است.
36. فایلی به نام Problem36data.xlsx حاوی اطلاعات زیر درباره بسیاری از بازپرداختها است: شماره صورتحساب، مبلغ و شماره فروشنده. تمام مبادلاتی را که مبلغ و شمارهحساب بیش از یکبار تکرار شده باشند را مشخص کنید.