بررسی تخصصی ویژگی conditional formatting

این درس از دوره آموزش اکسل به مبحث قالب‌بندی شرطی (Conditional Formatting) اختصاص دارد و نحوه اعمال قواعد خاص بر داده‌ها را برای برجسته‌سازی مقادیر خاص، مقایسه داده‌ها و نمایش بصری بهتر آموزش می‌دهد. در این آموزش ابزارهایی مانند Data Bars، Icon Sets و Color Scales به‌تفصیل بررسی شده و مثال‌هایی عملی برای استفاده از Top/Bottom Rules و دیگر تنظیمات پیشرفته ارائه می‌شود. این بخش برای کاربران حرفه‌ای طراحی شده و تاکید بر بهینه‌سازی تجزیه‌وتحلیل داده‌ها دارد.

ویژگی Conditional Formatting یا قالب بندی شرطی اکسل، در منو Home، یکی از ابزارهای مهم برای انتخاب داده است، گزینه آن اینجا قرار دارد. نظر به اینکه شما از این ویژگی استفاده میکنید، بگذارید اول من کلیک کنم روی این دکمه، درست اینجا، روی منو Home، خب شما توضیح آنرا همینجا میتوانید ببینید. اما با توجه به اینکه شما اغلب این را استفاده میکنید، ویژگیهای مختلف آنرا سرتاسر ورکشیت میتوانید اعمال کنید. بنابراین اگر شما میخواهید با داده‌ها سروکار داشته باشید، و اگر فایل اکسل مربوط به این درس را از سایت طهاکو دات آی آر دانلود کرده باشید، بهتر است به سمت چپ صفحه نمایشتان بروید، چون این قسمت داده‌های زیادی دارد. اما شاید من بخواهم، ستون H یا ستون I را انتخاب کنم. پس اجازه بدهید کمی بیایم سمت راست شیت. حالا تصور کنید میخواهیم این حقوقها را به روشهای مختلفی نشان بدهیم.
من ستون H را انتخاب میکنم. البته من در حال حاضر به شما پیشنهاد نمیکنم که هر زمانی از Conditional Formatting استفاده میکنید، باید یک ستون انتخاب کرده باشید، ولی خب، معمولاً شما این کار را انجام دادید. من میخواهم ستون H را یک تغییری بدهم. اعداد داخل این ستون ممکن است یا خیلی بزرگ یا شاید هم خیلی کوچک باشند. میخواهم یک قالب بندی بر اساس یکسری قید و شرط برای اینها فعال کنم. کاری که میخواهیم انجام بدهیم ممکن است یک چیز ساده و آسانی شبیه highlight cell rules و بعد هم greater than باشد. به عبارت دیگر، بیایید حقوقهایی که بیشتر از مقدار مشخصی هستند را متفاوت با بقیه نشان بدهیم. خب میبینید که در این پنجره‌ای که باز شده، خود اکسل اینجا، یک پیشنهاد ایجاد میکند. من میخواهم آنرا تغییر بدهم. میخواهم کم و بیش نقطه میانی مقادیر این لیست را انتخاب کنم، پس می‌آیم اینجا 70000 را قرار میدهم.
و ویژگی‌ای که الان فعال است و ما داریم آنرا سمت چپ میبینم Red fill dark textاست، اما اینجا گزینه‌های دیگری هم وجود دارد، و اگر از بعضی از اینها خوشمان نیامد میتوانیم بعضی دیگر را امتحان کنیم. به عبارت دیگر، پایین این لیست، میتوانیم گزینه Custom Format را انتخاب کنیم تا قالب بندی خود را به صورت سفارشی در بیاوریم. بنابراین میتوانیم برای آنها رنگ زرد، آبی یا سبز انتخاب کنیم. یا border بگذاریم، یا فونت را تغییر بدهیم یا هر چیز دیگر. اگر شما عجله دارید، اینجا fill خیلی واضح است، پس من می‌آیم آنها را با رنگ زرد پر میکنم. ok را کلیک میکنم، دوباره اینجا هم ok میکنم. بنابراین در یک نگاه، میتوانیم آنهایی که از 70000 بالاتر هستند را ببینیم. در ضمن این به صورت پویا یا داینامیک است. یعنی نیاز نیست ما به آن بگوییم برای فلان سلول این رنگ و این گزینه را انتخاب کن و برای فلان سلول دیگر رنگ دیگری. یک جورایی این پدیده قدرتمند مایکروسافت خودش همه چیز را میفهمد و اتوماتیک همه دستوراتی که از قبل به آن دادیم را اعمال میکند.
بنابراین اگر من بیایم این را کم کنم و آنرا بگذارم 69000، و بعد هم اینتر را بزنم، میبینیم که دیگر رنگ زمینه آن زرد نیست. حالا اگر بیایم یکی از این پایینی‌ها را، اینجا، به 71000 تغییر بدهم و اینتر بزنم رنگ زمینه آن زرد میشود و برای بقیه هم به همین صورت میشود. شما دیدید که چطور این کارها، خیلی واضح از طریق ویژگیها توصیف میشوند. اجازه بدهید برگردیم اینجا و یکسری دیگر از ویژگیها را ببینیم. خب اگر ما دیگر نخواهیم این ویژگی که به ستون خود داده بودیم را داشته باشیم، چون خیلی اوقات ممکن است بخواهیم یک ویژگی را برداریم، برمیگردیم به Conditional Formatting و از اینجا روی clear rules میزنیم. اگر ما قالب بندی شرطی دیگری در ورک شیت داشته باشیم، میتوانیم به کمک این گزینه، یعنی Clear Rules From Entire Sheet همه آنها را پاک کنیم.
پس ما خیلی راحت توانستیم قوانین و قوائدی را که تعریف کردیم از سلولهای انتخاب شده اینجا پاک کنیم. اما اجازه بدهید برگردم و به بعضی ویژگیهای دیگر یک نگاهی کنیم. رول TopBottom. شاید ما فقط بخواهیم مقادیری از ستون compensation، که بالاتر از میانگین هستند، برای ما مشخص شوند. احتمالاً این کار مهمی نیست که اینجا انجام بدهیم، چون افراد وضعیتهای مختلفی دارند و میانیگن آنها یک عدد گمراه کننده‌ای میشود، اما شما میتوانید تصور کنید دارید از آن استفاده میکنید. این یکی آنهایی را که پایینتر از 10 درصد هستند را مشخص میکند و این بالا 10 درصد را و به همین ترتیب بقیه آنها. اکسل همه محاسبات را با تجزیه و تحلیل داده‌ها برای محدوده انتخاب شده انجام میدهد. یک ویژگی ویژوالی واضحتر، ممکن است گزینه Data Bars باشد.
و همینطور که من دارم روی اینها حرکت میکنم، شما میتوانید ببینید در ستون H چه اتفاقی می‌افتد. الان ممکن است شما فکر کنید که اگر این ستون را عریضتر کنیم، میتوانیم این میله‌ها را بهتر ببینیم. اما اینطور هم نیست. Solid fill یک کمی فرق میکند، بعضی از رنگهای آن تیره است، و اعداد آن نسبتاً خوب نشان داده نمیشوند و تار و مبهم به نظر میرسند، به خصوص اگر رنگ میله‌ها تیره‌تر باشد. پس ممکن است این رنگ زرد طلایی اینجا بهتر کار کند. اگر من بیایم ستون را پهنتر کنم میله‌ها هم در امتداد اعداد بزرگتر، بلندتر نشان داده میشوند. اینجا یک گزینه‌ای وجود دارد که از طریق آن میتوانید دیگر اعداد را نشان ندهید.
شما میتوانید تنظیم کنید که متن اینها سفید نشان داده بشود، اما احتمالاً این بهترین روش برای انجام کارمان نخواهد بود. میتوانیم به Conditional Formatting برگردیم. وقتی میخواهید یک تنظیمی را تغییر بدهید، گاهی اوقات گزینهmanage rules را باید انتخاب کنید تا قوانین و قوائد یا همان رول‌هایتان را مدیریت کنید. به عبارت دیگر، یک قانونی اینجا دارد استفاده میشود. اجازه بدهید من بروم به manage، خب پنجره Conditional Formatting rule باز میشود. ممکن است شما قبلاً این را ندیده باشید، اینجا یک گزینه‌ای به اسم Edit Rule وجود دارد و اینجا بین همه گزینه‌های مختلف، یک گزینه‌ای وجود دارد، گزینه Show Bar Only. این گزینه را تیک میزنم.
روی OK و دوباره OK کلیک میکنم. الان میتوانیم این را ببینیم. پس شما ممکن است بخواهید یک ارائه‌ای از این داده‌ها و نام افرادی که اینجا هستند، درست کنید، ولی ممکن است نخواهید حقوق و دستمزد واقعی آنها معلوم شود، اما میخواهید حقوق تقریبی آنها را در مقایسه با دیگران نشان بدهید. الان اگر اتفاق بیافتد که روی اینها قرار بگیرید، و اگر روی یکی از اینها کلیک کنید، میتوانید آنرا در نوار فرمول ببینید. بنابراین ما داده‌های آنها را حذف نکردیم. ما فقط آنها را به دلایلی پنهان کردیم. الان برگردید به ستون H، اول از همه، بگذارید این اسکرول را به سمت راست ببرم. ستون H را انتخاب میکنم.
برمیگردم به Conditional Formatting. اگر ما بخواهیم دوباره آن اعداد برگردند، بگذارید خلاصه آن را بگویم، ما دوباره میخواهیم برگردیم و قوانین و قواعد را مدیریت کنیم. پس روی manage rules میزنم. قوانین و رولهای مورد بحث، درست اینجا هستند، الان فقط یک قانون داریم درست در این قسمت. میروم سراغ Edit Rule، و تیک Show Bar Only را برمیدارم. حالا روی ok و دوباره ok کلیک میکنم و اگر شما بخواهید از این ویژگی به خاطر فعال کردن یک ویژگی جدید، خلاص بشوید، اشتباهاً، چیزی که گاهی اوقات انجام میدهید می‌آیید گزینه color scales را روی میله‌هایتان فعال میکنید، و این واقعاً چیزی که شما میخواهید نیست. بنابراین من اول می‌آیم اینجا با clear rules، و با این گزینه آن، قوانین را از سلولهای انتخاب شده پاک میکنم و برمیگردم به Conditional Formatting، و یک نگاهی به Color Scales میکنم.
الان چیزی که ممکن است اولش ببینید، ممکن است به شما بگوید این 3 رنگی است، یا این دو رنگی است، اما در حقیقت اینها یک زنجیره‌ای از رنگها هستند. برای مثال، اگر من این گزینه را انتخاب کنم، حقوقهای بالاتر قرمز میشوند و پایینی‌ها سبز میشوند، و اولین دکمه اینجا برعکس آن است. اما شاهد فقط 3 رنگ نیستیم، به نظر می‌آمد ما قرمز، زرد و سبز را ببینیم. در حقیقت ما داریم اینجا سبز روشن را میبینیم، سبز متمایل به زرد یا هر چیزی که میشود اسم آنرا گذاشت. اینجا هم رنگی نارنجی است. بنابراین اگر من بیایم اینجا روی منو Data و روی دکمه AZ، یا ZA کلیک کنم تا داده‌ها در برگه اطلاعات ما یا همان دیتا تب ما مرتب شوند، اجازه بدهید من روی AZ کلیک کنم، مثل این، میبینید که رنگهای قرمز زیادی اینجا هستند.
اما همینطور که داریم پایین می‌آییم، هر مقدار حقوقها بیشتر میشوند، میبینید که چطور این رنگها روشن و روشنتر میشوند. بنابراین شما میتوانید بگویید هر کدام از اینها، اگر مقدار compensation شان متفاوت باشد یک کمی متفاوت است. داریم میرویم به رنگ زرد و بعد هم سبز متمایل به زرد و بقیه آنها. در نهایت، رنگ سلولهای ما سبز تیره میشود. شما مجبور نیستید هر وقت این رنگها را فعال میکنید این مسیر را بروید، اما آن به شما میفهماند و این حس را میدهد که چطور این ابزارها دارند کار میکنند. من کنترل z را میزنم تا unsort یا نامرتب بشوند دقیقاً مثل اولش. پس این هم یک گزینه دیگر بود. و دوباره، داده‌هایمان را نشان میدهد و همینطور اینجا، اگر بخواهیم، گزینه‌های دیگری هم داریم، پنهان کردن حقوق، پنهان کردن مقادیر واقعی، مقدار پاداش یا compensation.
برمیگردم به منو Home، و گزینه Conditional Formatting را انتخاب میکنیم. و یکبار دیگر، میخواهم قوانین را پاک کنم، چون گزینه‌های دیگری اینجا مورد علاقه بعضی افراد هستند، برای اطمینان، اسم آن icon sets است. الان اینجا شما میتوانید داده‌های خود را به 3 تایی یا 4 تایی یا 5 تایی تقسیم کنید. با این روش، اگر ما 3 تایی را انتخاب کنیم، این به این معنی نیست که ما تعداد مساوی فلش قرمز رنگ، سبز رنگ و زرد رنگ داریم. به این معنی است که مقادیر تجزیه و تحلیل میشوند و به 3 دسته و بازه تقسیم میشوند، و آنهایی که مقدارشان بالاتر از سومین مقدار است همانطور که معقول است سبز میشود، و همینطور مقادیر بعدی را با این فلشهایی که میبینید، مثلاً کمترین مقدار با فلش رو به پایین قرمز رنگ، قابل نمایش دادن میشوند.
بنابراین ما لزوماً نمیخواهیم ببینیم مقادیر مشابه ورودی، فلشهای مشابه داشته باشند. اینجا یک طراحی مجدد از نسخه‌های قبلی وجود دارد، فلشها یک کمی بیشتر، با سبک خاصی طراحی شدند. ما بعضی گزینه‌های دیگر را هم اینجا میبینیم. چهار فلشه، پنج فلشه و بقیه. و حتی به اینها اسم هم نسبت دادند. اینها چراغ راهنمایی هستند، اینها هم چراغ راهنمایی حاشیه دار هستند. این بدون حاشیه بود و به همین ترتیب. گزینه‌های مختلف و icon sets یا مجوعه آیکنهای مختلف. و به خاطر اینکه پیشنمایش زنده این افکتها را داریم میبینیم و در این کادرها هم مایکروسافت در مورد آنها صحبت کرده، قبل از اینکه هر کدام از اینها را انتخاب کنیم، میبینیم در ستون H چه اتفاقی دارد می‌افتد و به راحتی میتوانیم از قابلیتی که مایکروسافت به آنLive Preview effect میگوید، استفاده کنیم.
شما میتوانید ببینید چطور این ابزار، بعضی مقدارهای ویژوالی مختلفی برای داده‌های اینجا را به ما میدهد. اینجا هم همینطور، اگر شما فقط بخواهید اندازه نسبی یک مقدار compensation نشان داده شود، شما میتوانید این گزینه را، درست اینجا، انتخاب کنید و شبیه کاری که قبلاً میکردیم، آنرا انجام بدهیم. برمیگردم به Conditional Formatting وmanage the rules حالا edit rule را انتخاب میکنم، و دقت کنید برای این مورد، Show Icon Only، را فعال میکنم و ok و دوباره ok میکنم. و الان ما مقدار دقیق compensation را نمیبینیم. بنابراین اینجا تعداد زیادی گزینه داریم. اینها به صورت پویا هستند، شما میتوانید اینها را استفاده کنید، احتمالاً میتوانیم آنرا در ستون I هم استفاده کنیم، میتوانیم آنرا در ستون F هم استفاده کنیم.
این را بگویم کار کردن با خیلی از انتخابهایی که اینجا هستند برای اینکه با آنها آشنا شوید نسبتاً ساده است و باز هم میگویم آنها به صورت داینامیک و پویا هستند. اگر شما با لیست بزرگی شبیه به این دارید کار میکنید، من فکر میکنم راه درست آن این است که کل ستون را انتخاب کنید و روی حروف ستونها این بالا کلیک کنید، چون دیگر اینطوری نگران قالب بندی رکوردهای جدیدی که به پایین ستون اضافه میشوند، نمیشوید. یا اگر کاهش شدید ناگهانی داشته باشند و یک قسمتی از بین برود و چون مقیاس ارزیابی ما کل ستون نیست بخواهد اسمشان ناپدید بشود و یا بازدهی بدی به ما بدهد. شما نمیخواهید در مورد اینها نگران بشوید. بنابراین این ویژگی بزرگی است که داده‌ها را با آنها، نشان میدهند. من میخواهم اینجا چند بار کنترل z بزنم. من فکر میکنم بهتر است برای این مورد، اینها را ببینیم. اما شما برای اینکه چطور بخواهید از این ویژگی استفاده کنید حتماً دیدگاه‌های مختلفی خواهید داشت، پس Conditional Formatting در منو Home پیدا میشود.