تفکیک اعداد با استفاده فرمت دهی شرطی

نمایش N داده بزرگ یا کوچک در یک محدوده با استفاده از فرمت دهی شرطی

نمایش N داده بزرگ یا کوچک (مثلا ۵ داده ای که از همه بزرگتر یا کوچک تر است) در یک محدوده از طریق تغییر نمایش گرافیکی (مثلا تغییر رنگ سلول یا فونت متن) جزو موضوع های پرکاربردی هست که اکثر کاربران اکسل با اون مواجه شدن و اولین راهکاری که برای حل این مسئله وجود داره استفاده از ابزار Conditional Formatting هست. در این مقاله قصد داریم تا با استفاده از ابزار Conditional Formatting سه روش مختلف رو برای این فرمت دهی شرطی بررسی کنیم تا شما از بین این روش ها کاربردی ترین رو برای حل مسئله انتخاب کنید.

روش اول: استفاده از قوانین (رول ها) از پیش تعریف شده در ابزار Conditional Formatting

این روش سریعترین راه برای تعیین N داده بزرگ یا کوچک در یک محدوده هست. فرض کنید یک محدوده از داده هایی داریم که نشون دهنده دمای هوای ۲۰ شهر در یک روز هست. حالا می‏خوایم تو این محدوده ۳ تا از بیشترین دماها رو با رنگ قرمز و ۳ تا از کمترین دماها رو با رنگ سبز مشخص کنیم. برای این کار مراحل زیر رو به ترتیب انجام میدیم:

گام اول: محدوده ای که شامل دمای هوای ۲۰ شهر هست رو انتخاب می کنیم؛

گام دوم: از منوی Home و از زیرمجموعه Styles گزینه Conditional Formatting رو انتخاب می‏کنیم؛

گام سوم: از فروریز نمایش داده شده گزینه Top/Bottom Rules رو انتخاب می‏کنیم و یکی از گزینه‏های Top 10 Items یا Bottom 10 Items رو انتخاب می کنیم؛ (شکل ۱)

شکل ۱- استفاده از قوانین از پیش تعریف شده در conditional formatting

گام چهارم: در پنجره ای که باز شده مطابق شکل ۲ تعداد داده های مورد نظرمون رو (منظور همون عدد N) مشخص می کنیم که اینجا عدد ۳ رو می‏ذاریم و در فیلد بعدی فرمتی که می خوایم داده هامون داشته باشن رو تعریف می‏کنیم (مثلا رنگ متن قرمز یا زرد).

در صورتی که فرمتی مدنظرتون هست که در لیست نیست می تونید از قسمت Custom Format فرمت مورد نظر خودتون رو ایجاد کنید. بعد از تعریف فرمت مورد نظرتون بر روی گزینه ok کلیک کنید تا تغییرات در محدوده داده ها اعمال بشه.

شکل ۲- انتخاب فرمت و تعداد اعداد مورد نظر

برای نمایش ۳ دمای پایین باید یکبار دیگه گام های سوم و چهارم طی بشه.

بعد از زدن کلید ok محدوده داده ها به شکل زیر تغییر می کنه.

شکل ۳- نمایش ۳ دمای بیشتر و کمتر

روش دوم: استفاده از گزینه New Rule برای فرمت دهی شرطی

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

گام اول: محدوده ای که شامل دمای هوای ۲۰ شهر هست رو انتخاب می کنیم؛

گام دوم: از منوی Home و از زیرمجموعه Styles گزینه Conditional Formatting رو انتخاب می‏کنیم و از فروریز نمایش داده شده گزینه New Rule رو انتخاب می‏کنیم؛

گام سوم: مطابق شکل ۴ در پنجره نمایش داده شده گزینه Format only top or bottom ranked values رو انتخاب می‏کنیم؛

شکل ۴- دسترسی به رول های آماده از قسمت New Rule جهت فرمت دهی شرطی

گام چهارم: در فرو ریز نمایش داده شده گزینه Top/Bottom رو انتخاب می کنیم و در فیلد بعدی تعداد داده های مورد نظرمون (که اینجا ۳ هست) رو وارد می‏کنیم؛

گام پنجم: روی گزینه Format کلیک می‏کنیم و فرمت مورد نظرمون رو تعریف می‏کنیم و دکمهok می‏زنیم و حالا تغییرات رو در محدوده مورد نظر مشاهده می‏کنیم مطابق شکل ۵

شکل ۵- هایلایت سه داده بزرگ در مجموعه داده ها

روش سوم: فرمول نویسی در Conditional Formatting

دو روشی که توضیح دادیم و براساس قوانین از پیش تعریف شده در Conditional Formatting بودند روش های راحت برای رسیدن به جواب هستن اما مشکلی که دارن اینکه اگر تعداد داده ها تغییر کنه (مثلا بخوایم ۳ رو به ۵ تغییر بدیم) باید بریم به صورت دستی عدد ۵ رو مطابق شکل ۴ و شکل ۲ جایگزین عدد ۳ کنیم. اما روش سوم این مزیت رو داره که شما می تونید به راحتی با تغییر عدد تغییرات رو در محدوده داده هاتون ببینید بدون اینکه در Conditional Formatting تغییر ایجاد کنید. برای اینکار از فرمول نویسی در Conditional Formatting استفاده می کنیم.

همونطور که می دونید توابع Large و Small به ما K امین داده بزرگ و کوچک در یک محدوده عددی رو میدن پس برای فرمول نویسی در Conditional Formatting می تونیم از این دو تا تابع (بسته به اینکه اعداد بزرگ یا کوچک رو می خوایم) استفاده کنیم. روش کار به شکل زیر هست:

گام اول: دو تا سلول کمکی ایجاد می کنیم و به ترتیب تعداد اعداد بزرگ و کوچک که می خوایم توی محدوده نمایش داده بشه رو تو این دو تا سلول می نویسم مثل شکل ۶

شکل ۶- تعریف سلول کمکی جهت فرمت دهی شرطی

گام دوم: محدوده داده ها رو انتخاب می کنیم

گام سوم: از منوی Home و از زیرمجموعه Styles گزینه Conditional Formatting رو انتخاب می کنیم و از فروریز نمایش داده شده گزینه Use a formula to determine which cells to format رو انتخاب می کنیم

گام چهارم: در فیلد Format values where this formula is true مطابق شکل ۷ یکی از دو فرمول زیر رو (بسته به اینکه بخوایم داده های بزرگ رو نشون بده یا کوچک) می نویسیم.

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

= upper_left_cell >= LARGE(range, k)

که با توجه به محدوده داده ای که داریم این فرمول به شکل زیر نوشته می شه:

=A2 >= LARGE($A$2:$D$6, $G$4)

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

= upper_left_cell <= SMALL(range, k)

که با توجه به محدوده داده ای که داریم این فرمول به شکل زیر نوشته می شه:

=A2 <= SMALL($A$2:$D$6, $G$5)

شکل۷- فرمول نویسی در Conditional Formatting

نحوه عملکرد فرمول:

منظور از upper_left_cell سلولی هست که در محدوده داده ها در گوشه بالایی سمت چپ محدوده قرار گرفته که اینجا با توجه به محدوده ای که ما داریم منظور همون سلول A2 هست. دقت کنید که آدرس دهی این سلول در فرمول کاملا آزاد هست (یعنی هیچ $ نداره) چون که این فرمول تک تک سلول های موجود در محدوده رو ($A$2:$D$6) یکی یکی چک می کنه و با خروجی تابع LARGE ($A$2:$D$6, $G$4) که در واقع سومین عدد بزرگ موجود در محدوده هست (در اینجا عدد ۴۰) مقایسه می کنه و اگر داده موجود در اون سلول از عدد ۴۰ بزرگتر یا مساوی باشه سلول رو به فرمتی که تعریف کردیم در میاره.
دقیقا همین منطق برای فرمول =A2<=SMALL($A$2:$D$6, $G$5)صادق هست با این تفاوت که این فرمول تک تک داده های موجود در محدوده رو با سومین عدد کوچک محدوده یعنی عدد ۷ مقایسه می کنه و اگر داده موجود در اون سلول از عدد ۷ کوچکتر یا مساوی باشه سلول رو به فرمتی که تعریف کردیم در میاره.

گام پنجم: بعد از اینکه فرمول رو نوشتیم بر روی گزینه Format کلیک می کنیم و فرمت دلخواهمون رو انتخاب می کنیم و دکمه OK می زنیم.

گام ششم: در پنجره New Formatting rule هم دکمه OK رو می زنیم و حالا می تونیم نتیجه رو مطابق شکل ۸ در محدوده داده ببینیم.

شکل ۸- هایلایت N داده بالا و پایین بصورت متغیر

حالا در صورتی که اعداد موجود در سلول های G4 و G5 رو مطابق ویدئو زیر تغییر بدین براحتی مشاهده می‏کنید که محدوده داده هاتون چه تغییری می کنه و دیگه نیازی نیست که اعداد رو در Conditional Formatting تغییر بدین.

در ادامه دو مثال کاربردی از فرمول نویسی با استفاده از توابع LARGE و SMALL در Conditional Formatting رو بررسی می کنیم.

مثال ۱: نحوه نمایش سطرهای حاوی N داده بیشتر یا کمتر

با توجه به اینکه هیچ Rule از پیش تعریف شده ای در Conditional Formatting برای انجام این کار وجود نداره پس باید با فرمول نویسی در Conditional Formatting این کار رو انجام بدیم. فرمولی که اینجا استفاده میشه مشابه فرمول های بخش قبلی هست فقط مقداری تغییرات باید بدیم. روش کار با یک مثال توضیح میدیم.

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

گام اول: چون عدد N متغیر هست پس اول از همه دو تا سلول کمکی ایجاد می کنیم. (سلول های D3 و D4)

گام دوم: محدوده داده هامون رو انتخاب می‏کنیم که شامل اسامی و نمرات هست؛

گام سوم:برای مشخص کردن سطرهای حاوی N تا از بیشترین و کمترین نمرات در Conditional Formatting فرمول های زیر رو می نویسیم:

فرمول تعیین سطرهای حاوی N نمره بیشتر

=$B2 >= LARGE($B$2:$B$11, $D$3)

فرمول تعیین سطرهای حاوی N نمره کمتر

=$B2 <= SMALL($B$2:$B$11, $D$4)

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

شکل ۹- رنگی کردن سطر مربوط به N داده بزرگ و کوچک

مشابه مثال قبل (در مورد دمای هوا ۲۰ شهر) Conditional Formatting تعریف شده در این مثال هم انعطاف پذیر هست و می تونیم با تغییر اعداد درج شده در سلول های D3 و D4 بلافاصله نتیجه رو در محدوده مورد نظر ببینیم.

مثال ۲: نحوه نمایش N داده بزرگ در هر سطر

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

برای اینکار مطابق شکل ۱۰ اول محدوده داده ها رو (یعنی $B$2:$G$6) رو انتخاب می‏کنیم و در Conditional Formatting فرمول رو به صورت زیر می نویسیم:

=B2> = LARGE($B2:$G2,3)

شکل ۱۰- فرمت دهی N داده ی برتر در هر سطر

نحوه عملکرد فرمول:

در فرمول بالا تابع LARGE سومین عدد بزرگ رو در محدوده $B2:$G2 نشون میده که عدد ۱۸ هست. طبق فرمول عدد موجود در سلول B2 (که ۱۵ هست) با عدد ۱۸ مقایسه میشه و چون کمتر از ۱۸ هست رنگ سلول تغییری نمی کنه.

برای سلول C2 فرمول زیر اعمال میشه

=C2 >= LARGE($B2:$G2,3)

و چون خروجی فرمول برای سلول C2 مقدار TRUE هست رنگ سلول سبز میشه.

برای سلول B3 که در سطر سوم قرار داره فرمول زیر اعمال میشه:

=B3 >= LARGE($B3:$G3,3)

دقت کنید که در سطر سوم محدوده تابع LARGE تغییر کرده.

در واقع نحوه آدرس دهی درست محدوده ها (استفاده درست از $) در فرمول نویسی باعث می شه که با یکبار فرمول نویسی تغییرات مورد نظر در یک محدوده برای تک تک سلول ها اعمال بشه درست همون اتفاقی که در مثال های ۱ و ۲ افتاد.

در این مقاله سه روش برای تعیین N داده بالا و پایین (یا فرمت دهی شرطی) ارائه کردیم. برای درک بهتر میتونید مقاله مربوط به توابع Small و Large رو مطالعه کنید.

مقالات پیشنهادی:

برای یادگیری نکات بیشتر مقالات زیر رو نگاه کنید:

ایجاد Alarm (هشدار) در اکسل

اطلاعات تکراری و Condtional Formatting

مخفی کردن صفر در اکسل