در اکسل، سلول را پر می کنیم و یکی دیگر پر می شود. نحوه ایجاد تغییر رنگ سلول اکسل بسته به مقدار

آیا نیاز به برجسته کردن مقادیر تکراری در یک ستون دارید؟ آیا باید 5 حداکثر سلول اول را انتخاب کنید؟ آیا برای وضوح نیاز به یک مقیاس حرارتی دارید (رنگ بسته به افزایش/کاهش مقدار سلول تغییر می کند)؟ در اکسل، برجسته کردن سلول ها با رنگ را می توان خیلی سریع انجام داد. در اکسل، یک تابع خاص به نام "قالب بندی مشروط" مسئول برجسته کردن سلول ها با رنگ است. به شدت توصیه می شود! ادامه مطلب را در زیر بخوانید:

برای شروع، در نوار وظیفه در منوی اصلی، بخش Styles را پیدا کرده و روی دکمه Conditional Formatting کلیک کنید.

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

اکنون بیشتر در مورد مفیدترین آنها:

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

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

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

برجسته کردن مقادیر تکراری، از جمله در چندین ستون

برای برجسته کردن تمام مقادیر تکراری، منوی مناسب را انتخاب کنید (به تصویر در ابتدای مقاله مراجعه کنید). بعد، پنجره قالب بندی دوباره ظاهر می شود. آن را همانطور که دوست دارید تنظیم کنید.

اگر بخواهید تکرارها را در دو یا چند ستون بیابید، مثلاً وقتی نام کامل شما در ستون های مختلف است، چه کاری باید انجام دهید؟ ستون دیگری ایجاد کنید و مقادیر را با فرمول = ترکیب کنید. در یک سلول جداگانه، IvanovIvanIvanovich نوشته شده است، سپس با استفاده از این ستون می توانید مقادیر تکراری را انتخاب کنید. درک این نکته مهم است که اگر ترتیب کلمات متفاوت باشد، اکسل چنین خطوطی را غیر تکراری در نظر می گیرد.

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

برای این کار به قسمت Rules برای انتخاب سلول اول و آخر رفته و مورد مورد نظر را انتخاب کنید. علاوه بر این واقعیت که می‌توانید اولین/آخرین مقادیر را برجسته کنید (از جمله درصد)، می‌توانید از قابلیت برجسته کردن داده‌ها در بالا و پایین‌تر از میانگین استفاده کنید (من اغلب از آن استفاده می‌کنم). بسیار راحت برای مشاهده نتایجی که با معمول یا متوسط ​​متفاوت است!

ساختن نمودار حرارتی و هیستوگرام

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

هیستوگرام در یک سلول (تصویر زیر) همچنین یک تابع بسیار مفید برای شناسایی تغییرات در مقادیر و مقایسه آنها است.

برجسته کردن سلول های حاوی متن خاص با رنگ

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

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

هایلایت رنگ اکسل. فیلتر بر اساس رنگ

علاوه بر گزینه های بالا، می توانید سلول های انتخاب شده را با استفاده از یک فیلتر معمولی بر اساس رنگ فیلتر کنید. در کمال تعجب، تعداد کمی از مردم در این مورد می دانند - ظاهراً بازتاب نسخه 2003 - این ویژگی وجود نداشت.

بررسی شرایط قالب بندی

برای بررسی اینکه چه قالب‌بندی شرطی را قبلاً تنظیم کرده‌اید، به صفحه اصلی - قالب‌بندی شرطی - مدیریت قوانین بروید، در اینجا می‌توانید شرایط از قبل تنظیم شده برای برجسته کردن سلول‌ها با رنگ را ویرایش کنید و همچنین اولویت قالب‌بندی مشخص شده را انتخاب کنید (هر کدام بالاتر است مهمتر است، می توانید آن را با دکمه ها تغییر دهید - فلش)

محدوده قالب بندی شرطی نامعتبر است

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

درباره ترمزهای اکسل و دلایل آن بیشتر بخوانید. این مقاله به بیش از صد نفر کمک کرده است؛)

خوشحالم که با برجسته کردن سلول ها بر اساس شرایط در اکسل آشنا شدید.

مقاله ما را در شبکه های اجتماعی خود به اشتراک بگذارید:

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

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

برای این منظور، شما باید از یک ماکرو بسیار ساده یا بهتر است بگوییم یک تابع سفارشی استفاده کنید ColorNom، به ما این امکان را می دهد که کد عددی رنگ پر را بیرون بکشیم و سپس با استفاده از این کد تعداد کل سلول ها را با استفاده از تکنیک های توضیح داده شده در مقاله شمارش می کنیم.

پس بیایید شروع کنیم. برای انجام این کار به ویرایشگر ویژوال بیسیک بروید:

V اکسل 2003مطبوعات سرویس، به علاوه ماکروو سپس ویرایشگر ویژوال بیسیک.

V اکسل 2007، 2010 و 2013متفاوت انجام می شود به بخش بروید توسعه دهنده، سپس انتخاب کنید ویژوال بیسیک

توجه!بخش نوار ابزار توسعه دهندهدر اکسل 2007 به طور پیش فرض در دسترس است، اما در اکسل 2010 و 2013 باید فعال باشد. این به ویژه برای آن دسته از کاربرانی که اغلب با ماکروها کار می کنند مفید است. برای فعال کردن نوار ابزار Developer در اکسل 2010 یا 2013، باید اجرا کنید فایل | گزینه ها | روبان را سفارشی کنیدپس از این، در سمت راست باید کادر کنار کتیبه را علامت بزنید توسعه دهنده

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

تابع عمومی ColorNom (سلول به عنوان محدوده)
ColorNom = Cell.Font.ColorIndex
تابع پایان

مهم! هنگام استفاده از قالب بندی شرطی، نمی توانید شماره رنگ سلول را با استفاده از این تابع پیدا کنید. علاوه بر این، هنگام تغییر رنگ سلول، اکسل مقادیر را مجدداً محاسبه نمی کند، باید این کار را به صورت دستی با فشار دادن Ctrl+Alt+F9 انجام دهید، در غیر این صورت با باز کردن مجدد فایل، تغییرات ایجاد می شود. این به این دلیل رخ می دهد که اکسل تغییر رنگ سلول را به عنوان ویرایش فرمول در نظر نمی گیرد. از این نظر، اگر این مهم است، می‌توانید با افزودن تابعی که دائماً مجدداً محاسبه می‌شود و بر تعیین رنگ سلول تأثیری ندارد، تغییری در خود فرمول ایجاد کنید. برای مثال، تابع تعیین تاریخ جاری را ضرب در صفر مشخص کنید.
در مورد ما، تابع به این شکل خواهد بود.

=ColorNom (A1)+Today()*0

مثالی از شمارش تعداد مقادیر بر اساس رنگ پر سلولی در اکسل

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

آرگومان های این تابع به این شکل است

COUNTIF (محدوده، معیار)

بیایید فرمول را بنویسیم:

COUNTIF($B$1:$B$8 ;E2)

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

دانلود فایل نمونه: (فایل با پشتیبانی ماکرو)

انسان ها به گونه ای طراحی شده اند که رنگ ها را بهتر از اعداد خالی درک می کنند - بیهوده نیست که مهمترین قسمت های نمودارها یا خطوط جدول که ارزش توجه به آنها را دارند معمولاً با رنگ متضاد برجسته می شوند. با این حال، علامت گذاری دستی سلول های جدول "خوب" و "بد" در اکسل جالب ترین کار نیست. اگر فقط MS Excel می توانست این کار را به تنهایی انجام دهد و حتی در صورت تغییر وضعیت، همین رنگ ها را به طور خودکار تغییر دهد...

اما او از قبل می داند که چگونه این کار را به خوبی انجام دهد - تنها کاری که باید انجام دهیم این است که کمی به او کمک کنیم!

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

ابتدا محدوده داده ها یعنی محتویات ستون دوم جدول MS Excel را انتخاب کرده و سپس به قسمت “ خانه"، جایی که در گروه "Styles" ما " را فعال می کنیم قالب بندی شرطی"، و در لیست کشویی " را انتخاب کنید یک قانون ایجاد کنید«.

در پنجره «ایجاد قانون قالب‌بندی» که ظاهر می‌شود، نوع قانون را انتخاب کنید: «فرمت فقط سلول‌هایی که حاوی هستند» و در سازنده زیر، پارامترها را تنظیم کنید: «مقدار سلول»، «کمتر از» و به‌صورت دستی «لبه» خود را وارد کنید: عدد 20

روی دکمه "Format" در زیر کلیک کنید، به تب "پر کردن" بروید و رنگ قرمز را انتخاب کنید. روی "OK" کلیک کنید.

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

به جدول نگاه کنید - ما به وضوح سیب و نارنگی بسیار کمی داریم، وقت آن است که یک خرید جدید انجام دهیم!

عالی است، داده ها قبلاً با رنگ برجسته شده اند!

اکنون، بر اساس قیاس، بیایید قانون دیگری ایجاد کنیم - فقط این بار با پارامترهای "Cell Value"، "Greater"، 20. رنگ سبز را به عنوان پر مشخص می کنیم. آماده.

این برای من کافی به نظر نمی رسید - خواندن متن سیاه روی پس زمینه قرمز و سبز دشوار است، بنابراین تصمیم گرفتم قوانین خود را کمی تزئین کنم و رنگ متن را با رنگ سفید جایگزین کنم. برای انجام این کار، ابزار Conditional Formatting را باز کنید، اما به جای انتخاب New Rule، آن را انتخاب کنید مدیریت قوانین"، در زیر.

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

اکنون نه تنها پس زمینه سلول های جدول، بلکه رنگ فونت را نیز تغییر داده ام

بیایید سعی کنیم مقادیر "بد" را به "خوب" تغییر دهیم؟ یک بار و انجام شد - به محض اینکه سلول های مربوطه حاوی مقادیری بودند که تحت یکی از قوانین قرار می گرفتند، رنگ به طور خودکار تغییر کرد.

ما مقادیر جدول اکسل خود را تغییر می دهیم ... همه چیز کار می کند!

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

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

شرایط سایه زدن سلول های اکسل در ویندوز

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

شرایط سایه زدن سلول های اکسل در مک

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

تصویر: © Mika Baumeister – Unsplash.com

فرض کنید یکی از وظایف ما این است که اطلاعاتی را وارد کنیم که آیا مشتری در ماه جاری سفارش داده است یا خیر. سپس بر اساس اطلاعات دریافتی، لازم است سلول ها را بر اساس رنگ برجسته کنید: کدام یک از مشتریان در طول 3 ماه گذشته یک سفارش انجام نداده است. برای چنین مشتریانی، باید پیشنهاد را دوباره ارسال کنید.

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

به طور خودکار سلول ها را با تاریخ پر کنید

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

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

این راه حل ارائه شده باید برخی از گردش های کاری را خودکار کند و تجزیه و تحلیل داده های بصری را ساده کند.

به طور خودکار سلول ها را با تاریخ های فعلی پر کنید

اول از همه برای ثبت نام با شماره مشتری، سرفصل ستون ها با رنگ سبز و ماه های جاری ایجاد می کنیم که به طور خودکار بازه های زمانی نمایش داده می شود. برای این کار فرمول زیر را در سلول B1 وارد کنید:

فرمول تولید خودکار ماه های خروجی چگونه کار می کند؟

در شکل، فرمول مدت زمان سپری شده از تاریخ نگارش مقاله را نشان می دهد: 1396/09/17. اولین آرگومان در تابع DATA حاوی فرمولی است که به لطف توابع YEAR و TODAY همیشه سال جاری را در تاریخ امروز برمی گرداند. آرگومان دوم عدد ماه (-1) را مشخص می کند. عدد منفی به این معنی است که ما علاقه مندیم که در زمان گذشته چه ماهی بوده است. شرایط مثال برای آرگومان دوم با مقدار:

• 1 - به معنای اولین ماه (ژانویه) در سالی است که در استدلال اول مشخص شده است.
• 0 مربوط به 1 ماه پیش است.
• -1 2 ماه است. بازگشت از ابتدای سال جاری (یعنی: 10/01/2016).

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

همانطور که می بینید، تابع DATE اکنون از مقدار سلول B1 استفاده می کند و عدد ماه را نسبت به سلول قبلی 1 افزایش می دهد. در نتیجه، ما 1 را دریافت می کنیم - تاریخ ماه بعد.

حالا این فرمول را از سلول C1 به بقیه سرصفحه های ستون در محدوده D1:L1 کپی کنید.

محدوده سلول B1:L1 را انتخاب کنید و ابزار را انتخاب کنید: "HOME" - "Cells" - "Format Cells" یا به سادگی CTRL+1 را فشار دهید. در کادر محاوره‌ای که ظاهر می‌شود، در تب “Number” در قسمت “Number formats:” گزینه “(all formats)” را انتخاب کنید. در قسمت «نوع:» مقدار MMM.YY (حروف بزرگ ضروری) را وارد کنید. با تشکر از این، ما یک نمایش کوتاه از مقادیر تاریخ را در هدرهای ثبت دریافت خواهیم کرد، که تجزیه و تحلیل بصری را ساده می کند و به دلیل خوانایی بهتر، آن را راحت تر می کند.

توجه داشته باشید!هنگامی که ماه ژانویه (D1) فرا می رسد، فرمول به طور خودکار تاریخ را به سال بعد تغییر می دهد.



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

اکنون باید سلول های مربوط به ماه جاری را برجسته کنید. به لطف این، می توانیم به راحتی ستونی را که باید داده های فعلی این ماه را در آن وارد کنیم، پیدا کنیم. برای این:

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

فرمول برجسته کردن رنگ ستون بر اساس شرایط چگونه کار می کند؟

از آنجایی که قبل از ایجاد قانون قالب‌بندی شرطی، کل منطقه جدول را برای وارد کردن داده‌های حروف کوچک پوشش دادیم، قالب‌بندی برای هر سلول در آن محدوده B2:L15 فعال خواهد بود. مرجع مختلط در فرمول B$1 (آدرس مطلق فقط برای سطرها، آدرس نسبی برای ستون ها) به این معنی است که فرمول همیشه به سطر اول هر ستون اشاره دارد.

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

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

توجه داشته باشید!در شرایط این فرمول، آخرین آرگومان تابع DATE مانند فرمول ها در تعریف تاریخ برای سرفصل های ستون ثبت، روی 1 تنظیم می شود.

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

بخش جدولی قالب بندی شده است، اکنون بیایید آن را با مقدار متنی "order" به ترتیب ترکیبی از مشتریان برای ماه های جاری و قبل پر کنیم.

نحوه برجسته کردن سلول ها به رنگ قرمز بر اساس شرایط

اکنون باید سلول های قرمز رنگ را با تعداد مشتریانی که به مدت 3 ماه حتی یک سفارش انجام نداده اند برجسته کنیم. برای این:

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

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

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

بخش مهم برای شرط هایلایت رنگ ما در اولین آرگومان تابع OFFSET است. تعیین می کند که از کدام ماه باید جبران را شروع کرد. در این مثال، این سلول D2 است، یعنی ابتدای سال ژانویه است. به طور طبیعی، برای سلول های باقی مانده در ستون، شماره ردیف سلول پایه با شماره ردیفی که در آن قرار دارد مطابقت دارد. 2 آرگومان بعدی تابع OFFSET تعیین می کند که افست روی چند سطر و ستون باید انجام شود. از آنجایی که محاسبات را برای هر کلاینت در همان ردیف انجام خواهیم داد، مقدار افست سطرها را به صورت –¬ 0 مشخص می کنیم.

در عین حال برای محاسبه مقدار آرگومان سوم (تغییر ستون) از فرمول تودرتوی MONTH(TODAY()) استفاده می کنیم که مطابق با شرایط، تعداد ماه جاری در سال جاری را برمی گرداند. عدد 4 را از عدد ماه محاسبه شده با فرمول کم می کنیم، یعنی در مورد نوامبر 8 ستون افست می گیریم. و، به عنوان مثال، برای ژوئن - فقط 2 ستون.

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

اولین تابع در فرمول COUNTIF با استفاده از تابع OFFSET، تعداد دفعاتی که مقدار متن "order" در محدوده بازگشتی ظاهر می شود را آزمایش می کند. اگر تابع مقدار 0 را برگرداند، به این معنی است که به مدت 3 ماه هیچ سفارشی از مشتری با این شماره وجود نداشته است. و مطابق با شرایط ما، سلول با شماره این مشتری با رنگ پر قرمز برجسته می شود.

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

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