قوانینی برای استفاده ساده تر از قابلیت Conditional Formatting

ارسال توسط

آذر ۷, ۱۴۰۳

در آبان ۱, ۱۴۰۲

قوانین Conditional Formatting

Conditional Formatting یکی از امکانات پر کاربرد در Excel است. اما گاهی اوقات کاربران تمایل دارند که نوعی Conditional Formatting ایجاد نمایند که فراتر از مقایسه ارزش یک سلول با یک مقدار خاص یا یک سلول دیگر باشد.

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

ما می‌خواهیم محدوده هایی را مشخص نماییم که در آنها تاخیر در پروژه وجود دارد (Actual% Complete<Plan% Complete).

Conditional Formatting در حالت معمولی (By default) یک absolute reference ایجاد می نماید. بدین معنی که تمام سلولهایی که مشخص می گردند، با همان خانه مرجع مقایسه می شوند.

۱- راه خسته کننده! نوشتن تعداد زیاد Conditional Formatting برای تک تک سلولها

برای تعریف قوانین پیچیده Conditional Formatting کاربران معمولا به این اشتباه می افتند که برای تک تک سلولها Conditional Formatting تعریف می نمایند. در مثال، سلول C2 را انتخاب می نماییم. سپس

“Conditional Formatting -> New Rule -> Format only cells that contain”

در قسمت Edit The Rule Description، کوچکتر از (Less Than) را انتخاب می نماییم، سلول B2 را که می‌خواهیم برای ما مقایسه نماید انتخاب می کنیم. توجه کنید که سلول B2به صورت absolute reference ($B$2) انتخاب می گردد.

سپس فرمتی که میخواهیم در صورت وجود شرایط داشته باشد را انتخاب می کنیم. در این مثال فونت Actual%Complete را قرمز انتخاب می نماییم.

برای اینکه این عمل را بتوانیم به طور ستونی انجام دهیم، کاربر باید برای تک تک سلول ها Conditional Formatting را تکرار نماید و خانه مجاور آن را برای مقایسه انتخاب کند.

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

مشکل عملکردی نرم افزار: نوشتن تعداد زیاد قوانین Conditional Formatting سرعت کار نرم افزار را پایین می آورد.

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

۲- Relative Reference! استفاده از تنها یک قانون Conditional Formatting

راه دیگری وجود دارد که تنها یک قانون Conditional Formatting را در تمام محدوده بکار بگیریم. برای انجام آن، تمام محدوده‌ای را که می خواهید قانون Conditional Formatting در آن اجرا شود را انتخاب کنید (در مثال ما C2:C12) و سپس

“Conditional Formatting -> Highlight Cell Rules -> Less Than”

در قسمت Edit The Rule Description، کوچکتر از (Less Than) را انتخاب می نماییم، دوباره سلول B2 را که می‌خواهیم برای ما مقایسه نماید انتخاب می کنیم. توجه کنید که سلول B2به صورت absolute reference ($B$2) انتخاب می گردد. اکنون باید آنرا به حالت Relative Reference تبدیل کنیم یعنی (B2).

حالا ما تنها یک قانون Conditional Formatting داریم که در آن تمام مقادیر سلولها مقایسه می شوند و در این حالت دقیقا نتیجه مشابه حالت قبل را داریم. نکته جالب اینکه اگر یک ردیف جدید اضافه نماییم، قانون برای ردیف جدید به طور خودکار ایجاد می گردد. برای مثال فرض کنید اطلاعات پروژه جدید در H2 را به مجموعه زیر اضافه نماییم.

قوانین مرجع Relative در Conditional Formatting چگونه عمل می کند؟

در قوانین Conditional Formatting همیشه سلول ها با سلول بالایی سمت چپ تمام سلولهای انتخاب شده مقایسه می شوند. در مثال ما، سلول C2 با سلول B2 در سمت چپ خود مقایسه می گردد. بنابراین تمام سلولهای دیگر در محدوده انتخاب شده نیز با سلول سمت چپ خود مقایسه خواهند شد.

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

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