قالب بندی شرطی در اکسل - با جزئیات با مثال

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

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

با رفتن به بخش «سبک‌ها» می‌توانید «قالب‌بندی شرطی» را در اولین برگه نوار پیدا کنید. 

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

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

سپس یک سری اعداد در محدوده A1:A11 مشخص کردیم.

بیایید یک مجموعه داده مناسب را قالب بندی کنیم. سپس منوی تنظیمات “Conditional Formatting” را باز می کنیم و معیارهای انتخاب داده های مورد نیاز را تنظیم می کنیم. برای مثال، در مورد ما، معیار "بیشتر" را انتخاب می کنیم. 

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

سپس با دکمه OK پیکربندی را کامل کنید.

قالب بندی شرطی برای چیست؟

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

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

بیایید مثالی بزنیم.

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

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

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

و در واقع، پیمایش بسیار ساده تر است. 

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

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

بر اساس مقدار یک سلول دیگر

حال بیایید نگاهی به مثال عملی زیر بیاندازیم.

فرض کنید چنین جدولی داریم و با وظیفه برجسته کردن سطرهایی که حاوی مقادیر خاصی هستند روبرو هستیم. 

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

توالی اقدامات ما در این شرایط به شرح زیر خواهد بود:

1. محدوده ای از مقادیر را انتخاب کنید. 
2. روی دکمه "قالب بندی مشروط" - "ایجاد قانون" کلیک کنید.
3. لطفا توجه داشته باشید که در مورد ما، شما باید فرمول را در قالب یک قانون اعمال کنید. بعد، از تابع استفاده می کنیم IFبرای برجسته کردن خطوط منطبق 

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

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

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

این چیزی است که معیارهای ما به نظر می رسد.

و در نهایت، در نتیجه چنین پایگاه داده ای داریم.

شرایط چندگانه

برای وضوح، بیایید محدوده A1:A11 را از جدول بگیریم تا نشان دهیم چگونه، در یک مثال عملی واقعی، می‌توانید از چندین شرط برای تعیین قالب‌بندی سلول استفاده کنید.

شرایط خود به شرح زیر است: اگر عدد در سلول از مقدار 6 بیشتر شود، با رنگ قرمز برجسته می شود. اگر سبز باشد، رنگ آن سبز است. و در نهایت، بزرگترین اعداد، بیش از 20، با رنگ زرد برجسته خواهند شد. 

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

1. روش 1. ما محدوده خود را انتخاب می کنیم، پس از آن، در منوی تنظیمات قالب بندی شرطی، چنین قانون انتخاب سلولی را به عنوان "بیشتر" انتخاب کنید. عدد 6 در سمت چپ نوشته شده و قالب بندی در سمت راست تنظیم شده است. در مورد ما، پر باید قرمز باشد. پس از آن، چرخه دو بار تکرار می شود، اما پارامترهای دیگر از قبل تنظیم شده اند - به ترتیب بیش از 10 و رنگ سبز و بیش از 20 و رنگ زرد. چنین نتیجه ای خواهید گرفت.
   

   

2. روش 2. به منوی تنظیمات اصلی ابزار Excel "Conditional Formatting" بروید. در آنجا منوی "Create Rule" را پیدا کرده و روی این مورد کلیک چپ می کنیم.
   

   

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

   

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

و این هم یک اسکرین شات برای درک بهتر.

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

قالب بندی تاریخ مشروط

حالا بیایید ببینیم قالب بندی شرطی چگونه با تاریخ ها کار می کند. ما چنین محدوده باحالی داریم.

در این مورد، شما باید یک قانون قالب بندی شرطی مانند "تاریخ" را تنظیم کنید.

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

ما فقط باید مورد مناسب را انتخاب کنیم و روی دکمه "OK" کلیک کنیم.

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

با استفاده از فرمول ها

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

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

ردیف ها (بر اساس مقدار سلول)

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

نحوه ایجاد یک قانون

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

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

یک دکمه "ایجاد قانون" وجود دارد. همچنین می‌توانید روی دکمه «مدیریت قوانین» کلیک کنید، پس از آن یک کادر محاوره‌ای باز می‌شود که از طریق آن می‌توانید با کلیک کردن روی دکمه مربوطه در گوشه سمت چپ بالا، یک قانون ایجاد کنید. 

همچنین می توانید در این پنجره قوانینی را که قبلا اعمال شده اند مشاهده کنید.

قوانین انتخاب سلول

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

قالب بندی تمام سلول ها بر اساس مقادیر آنها

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

فقط سلول های یکتا یا تکراری را قالب بندی کنید

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

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

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

برای قالب بندی مقادیر زیر یا بالاتر از حد متوسط، یک گزینه خاص نیز در همان منو وجود دارد. اما باید زیر منوی دیگری را انتخاب کنید - "قوانین انتخاب اولین و آخرین مقادیر". 

فرمت کردن فقط مقادیر اول و آخر

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

قالب بندی فقط سلول هایی با محتوای خاص

برای قالب بندی تنها سلول هایی با محتوای خاص، باید قانون قالب بندی Equal To یا Text Contains را انتخاب کنید. تفاوت آنها در این است که در حالت اول، رشته باید کاملاً با معیار مطابقت داشته باشد و در مورد دوم فقط تا حدی. 

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