نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، گرافیک و فناوری اطلاعات فعالیت دارم.

  • قالب‌بندی شرطی توسط ابزار Conditional Formatting

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

ابزار Conditional Formatting یک سری الگو‌های آماده برای بصری‌سازی داده‌ها ارائه می‌دهد و در جایی که این الگوها پاسخگو نباشند می‌توانیم در این ابزار الگوی مورد نظر خود را ایجاد کنیم. این ابزار از طریق مسیر زیر قابل دسترس می‌باشد.

  • Home
  • Styles
  • Conditional Formatting

الگو‌های ابزار Conditional Formatting

Highlight Cells Rules

این الگو شامل گزینه‌های متفاوتی می‌باشد که در جدول 1 کاربرد آنها را بررسی می‌کنیم.

گزینه‌هاشرح
Greater Thanاعداد بزرگتر از عدد تعیین شده را قالب‌بندی می‌کند.
Less Thanاعداد کوچکتر از عدد تعیین شده را قالب‌بندی می‌کند.
Betweenاعداد بین دو عدد تعیین شده را قالب‌بندی می‌کند.
Equal Toاعداد برابر با عدد تعیین شده را قالب‌بندی می‌کند.
Text that Containsرشته‌هایی که شامل عبارت متنی تعیین شده باشد را قالب‌بندی می‌کند.
A Date Occurringتاریخ‌ها را با شرط تعیین شده قالب‌بندی می‌کند.
Duplicate Valuesمقادیر تکراری یا غیرتکراری را قالب‌بندی می‌کند.

گزینه‌های الگو Highlight Cells Rules

مثال

فرض کنید می‌خواهیم در یک محدوده اعداد بزرگتر از 10 را قالب‌بندی کنیم. محدوده مورد نظر و سپس گزینه Greater Than را انتخاب می‌کنیم. در کادر سمت چپ عدد 10 و در کادر سمت راست قالب مورد نظر را انتخاب کرده و به روی دکمه Ok کلیک می‌کنیم. قالب و ظاهر سلول‌هایی که با شرط تطابق داشته باشند تغییر می‌‌کند.

تعیین شرط و قالب برای اعداد بزرگتر از 10

تعیین شرط و قالب برای اعداد بزرگتر از 10

قالب پیش‌فرض این ابزار به رنگ قرمز کم‌رنگ برای زمینه و قرمز پررنگ برای فونت می‌باشد. می‌توانیم قالب دلخواه خود را در قسمت Custom Format تعیین کنیم.

Top/Bottom Rules

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

گزینه‌هاشرح
Top 10 Items10 آیتم که بزرگتر از بقیه هستند را قالب‌بندی می‌کند.
Top 10 %10 درصد از آیتم‌ها که بزرگتر از بقیه هستند را قالب‌بندی می‌کند.
Bottom 10 Items10 آیتم که کوچکتر از بقیه هستند را قالب‌بندی می‌کند.
Bottom 10 %10 درصد از آیتم‌ها که کوچکتر از بقیه هستند را قالب‌بندی می‌کند.
Above Averageاعداد بزرگتر از میانگین کل اعداد را قالب‌بندی می‌کند.
Below Averageاعداد کوچکتر از میانگین کل اعداد را قالب‌بندی می‌کند.

گزینه‌های الگو Top/Bottom Rules

عدد 10 پیش‌فرض این الگو در ابزار Conditional Formatting می‌باشد، با انتخاب هر یک از شرایط جدول 2 می‌توانیم این مقدار را تغییر دهیم.

مثال

فرض کنید می‌خواهیم در یک محدوده 5 آیتم که کوچکتر از بقیه هستند را قالب‌بندی کنیم. محدوده مورد نظر و سپس گزینه Bottom 10 Items را انتخاب می‌کنیم. کادر سمت چپ عدد 5 و در کادر سمت راست قالب مورد نظر را انتخاب کرده و به روی دکمه Ok کلیک می‌کنیم. قالب و ظاهر سلول‌هایی که با شرط تطابق داشته باشند تغییر می‌‌کند.

تعیین شرط و قالب برای 5 آیتم کوچکتر در یک محدوده

تعیین شرط و قالب برای 5 آیتم کوچکتر در یک محدوده

Data Bars

این الگو نوار رنگی باریکی است که میزان پیشرفت در یک محدوده را نشان می‌دهد، این نوار از یک طرف سلول به طرف دیگر گسترش می‌یابد. مقادیر کوچکتر رنگ کمتری می‌گیرند و بزرگترین مقدار تا 100% پرشدگی را به دنبال دارد. مثلا مطابق با تصویر 3، سلول A6 در محدوده A1:A10 به دلیل داشتن بیشترین مقدار بیشترین پرشدگی را به خود اختصاص می‌دهد و پرشدگی سلول‌های دیگر به تناسب آن محاسبه می‌شود.

نمایش میزان پیشرفت با الگو Data Bars

نمایش میزان پیشرفت با الگو Data Bars

Color Scales

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

نمایش داده‌ها در یک طیف رنگی با الگو Color Scales

نمایش داده‌ها در یک طیف رنگی با الگو Color Scales

Icon Sets

این الگو با توجه به اعداد، نمادهایی (Icon) را برای آنها اختصاص می‌دهد و پیش‌فرض آن درصد داده‌ها می‌باشد. مثلا اعداد بزرگتر یا مساوی 67 درصد با نماد سبز، اعداد کوچکتر از 67 درصد و بزرگتر یا مساوی 33 درصد با نماد زرد و اعداد کوچکتر از 33 درصد را با نماد قرمز قالب‌بندی می‌کند.

استفاده از الگو Icon Sets برای دسته‌بندی داده‌ها

استفاده از الگو Icon Sets برای دسته‌بندی داده‌ها

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

  • Home
  • Styles
  • Conditional Formatting
  • Manage Rules

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

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

ایجاد الگو جدید

اگر هیچ یک از الگوهای آماده اکسل نیاز ما را برطرف نکرد می‌توانیم با رفتن به مسیر زیر یک الگو جدید ایجاد کنیم، این الگوها امکانات بیشتری را در اختیار کاربر می‌گذارد. پنجره New Formatting Rule شامل گزینه‌های متفاوتی بوده که در جدول 3 کاربرد آنها را بررسی می‌کنیم.

  • Home
  • Styles
  • Conditional Formatting
  • New Rules
گزینه‌هاشرح
Format all cells based on their values
  1. 2-Color Scale: از اعداد بزرگ به کوچک یا کوچک به بزرگ طیف رنگی با دو رنگ مرجع ایجاد می‌کند.
  2. 3-Color Scale: از اعداد بزرگ به کوچک یا کوچک به بزرگ طیف رنگی با سه رنگ مرجع ایجاد می‌کند.
  3. Data Bar: نوار رنگی باریکی است که میزان پیشرفت یک محدوده را نشان می‌دهد، این نوار از یک طرف سلول به طرف دیگر گسترش می‌یابد. مقادیر کوچکتر رنگ کمتری می‌گیرند و بزرگترین مقدار تا 100% پرشدگی را به دنبال دارد.
  4. Icon Sets: با توجه به اعداد، نمادهایی (Icon) را برای آنها اختصاص می‌دهد و پیش‌فرض آن درصد داده‌ها می‌باشد.
Format only cells than contain
  1. Cell Value
    • between: اعداد بین دو عدد تعیین شده را قالب‌بندی می‌کند.
    • not between: اعدادی که بین دو عدد تعیین شده نیستند را قالب‌بندی می‌کند.
    • equal to: اعداد برابر با عدد تعیین شده را قالب‌بندی می‌کند.
    • not equal to: اعدادی که برابر با عدد تعیین شده نیستند را قالب‌بندی می‌کند.
    • greater than: اعداد بزرگتر از عدد تعیین شده را قالب‌بندی می‌کند.
    • less than: اعداد کوچکتر از عدد تعیین شده را قالب‌بندی می‌کند.
    • greater than or equal to: اعداد بزرگتر یا مساوی عدد تعیین شده را قالب‌بندی می‌کند.
    • less than or equal to: اعداد کوچکتر یا مساوی عدد تعیین شده را قالب‌بندی می‌کند.
  2. Specific Text
    • containing: رشته‌هایی که شامل عبارت متنی تعیین شده باشد را قالب‌بندی می‌کند.
    • not containing: رشته‌هایی که شامل عبارت متنی تعیین شده نباشد را قالب‌بندی می‌کند.
    • beginning with: رشته‌هایی که با عبارت متنی تعیین شده شروع شود را قالب‌بندی می‌کند.
    • ending with: رشته‌هایی که به عبارت متنی تعیین شده ختم شود را قالب‌بندی می‌کند.
  3. Dates Occurring
    • Yesterday: تاریخ دیروز را قالب‌بندی می‌کند.
    • Today: تاریخ امروز را قالب‌بندی می‌کند.
    • Tomorrow: تاریخ فردا را قالب‌بندی می‌کند.
    • In the last 7 days: تاریخ‌های 7 روز پیش را قالب‌بندی می‌کند.
    • Last week: تاریخ‌های هفته قبل را قالب‌بندی می‌کند.
    • This week: تاریخ‌های هفته جاری را قالب‌بندی می‌کند.
    • Next week: تاریخ‌های هفته بعد را قالب‌بندی می‌کند.
    • Last month: تاریخ‌های ماه قبل را قالب‌بندی می‌کند.
    • This month: تاریخ‌های ماه جاری را قالب‌بندی می‌کند.
    • Next month: تاریخ‌های ماه بعد را قالب‌بندی می‌کند.
  4. Blanks: سلول‌هایی که خالی هستند را قالب‌بندی می‌کند.
  5. No Blanks: سلول‌هایی که خالی نیستند را قالب‌بندی می‌کند.
  6. Errors: سلول‌هایی که دارای مقدار خطا هستند را قالب‌بندی می‌کند.
  7. No Errors: سلول‌هایی که دارای مقدار خطا نیستند را قالب‌بندی می‌کند.
Format only top or bottom ranked values
  1. Top: n آیتم که بزرگتر از بقیه هستند را قالب‌بندی می‌کند.
    • % of the selected range: n درصد از آیتم‌ها که بزرگتر از بقیه هستند را قالب‌بندی می‌کند.
  2. Bottom: n آیتم که کوچکتر از بقیه هستند را قالب‌بندی می‌کند.
    • % of the selected range: n درصد از آیتم‌ها که کوچکتر از بقیه هستند را قالب‌بندی می‌کند.
Format only values that are above or below average
  1. Above: اعداد بزرگتر از میانگین کل اعداد را قالب‌بندی می‌کند.
  2. Below: اعداد کوچکتر از میانگین کل اعداد را قالب‌بندی می‌کند.
  3. equal or above: اعداد بزرگتر یا مساوی میانگین کل اعداد را قالب‌بندی می‌کند.
  4. equal or below: اعداد کوچکتر یا مساوی میانگین کل اعداد را قالب‌بندی می‌کند.
  5. 1 std dev above: اعداد بزرگتر از محدوده انحراف معیار در اندازه کم را قالب‌بندی می‌کند.
  6. 1 std dev below: اعداد کوچکتر از محدوده انحراف معیار در اندازه کم را قالب‌بندی می‌کند.
  7. 2 std dev above: اعداد بزرگتر از محدوده انحراف معیار در اندازه متوسط را قالب‌بندی می‌کند.
  8. 2 std dev below: اعداد کوچکتر از محدوده انحراف معیار در اندازه متوسط را قالب‌بندی می‌کند.
  9. 3 std dev above: اعداد بزرگتر از محدوده انحراف معیار در اندازه زیاد را قالب‌بندی می‌کند.
  10. 3 std dev below: اعداد کوچکتر از محدوده انحراف معیار در اندازه زیاد را قالب‌بندی می‌کند.
Format only unique or duplicate values
  1. duplicate: مقادیر تکراری را قالب‌بندی می‌کند.
  2. unique: مقادیر غیرتکراری را قالب‌بندی می‌کند.
Use a formula to determine which cells to formatسلول‌ها را بر اساس فرمول تعیین شده قالب‌بندی می‌کند.

گزینه‌های New Rule برای ایجاد الگو جدید

ایجاد الگو جدید در پنجره New Formatting Rule

ایجاد الگو جدید در پنجره New Formatting Rule

بیشتر بخوانید: بکارگیری تاریخ شمسی

حذف الگوها

برای حذف الگوهای ایجاد شده می‌توانیم به مسیر زیر برویم. این قسمت شامل گزینه‌های متفاوتی می‌باشد که در جدول 4 کاربرد آنها را بررسی می‌کنیم.

  • Home
  • Styles
  • Conditional Formatting
  • Clear Rules
گزینه‌هاشرح
Clear Rules form Selected Cellsالگوهای سلول‌های انتخاب شده را حذف می‌کند.
Clear Rules from Entire Sheetهمه الگوهای شیت را حذف می‌کند.
Clear Rules from This Tableالگوهای جدول انتخاب شده را حذف می‌کند.
Clear Rules from This PivotTableالگوهای PivotTable انتخاب شده را حذف می‌کند.

گزینه‌های Clear Rules برای حذف الگوها

مدیریت الگوها

برای ویرایش، حذف یا تغییر اولویت الگوهای ایجاد شده می‌توانیم به مسیر زیر برویم.

  • Home
  • Styles
  • Conditional Formatting
  • Manage Rules
  1. گزینه New Rule یک الگوی جدید ایجاد می‌کند.
  2. گزینه Edit Rule الگوی انتخاب شده را ویرایش می‌کند.
  3. گزینه Delete Rule الگوی انتخاب شده را حذف می‌کند.
  4. پیکان‌های رو به بالا و پایین اولویت الگو‌ها را تغییر می‌دهند.

گزینه Current Selection الگوهای سلول‌های انتخاب شده و گزینه This Worksheet همه الگوهای شیت را در پنجره Manage Rules نشان می‌دهد.

در ابزار Conditional Formatting صرفا نیازی نیست که از یک الگو برای هر سلول استفاده کنیم و می‌توانیم هر تعداد الگو که نیاز است را به یک سلول ارجاع دهیم. فرض کنید می‌خواهیم در یک محدوده 3 الگو متفاوت ایجاد کنیم که اعداد بزرگتر یا مساوی 10 به رنگ قرمز، اعداد بزرگتر یا مساوی 15 به رنگ زرد و اعداد بزرگتر یا مساوی 18 به رنگ سبز قالب‌بندی شوند. محدوده مورد نظر را انتخاب کرده و برای هر شرط یک الگو ایجاد می‌کنیم.

  • در ابزار Conditional Formatting گزینه Manage Rules را انتخاب می‌کنیم.
  • الگویی که باید در ابتدا پیاده‌سازی شود را انتخاب کرده و به کمک پیکان رو به بالا، آن را به بالای لیست انتقال می‌دهیم. همین کار را برای الگویی که دومین اولویت را دارا است ادامه می‌دهیم.
  • گزینه Stop If True را برای الگو اول و دوم فعال می‌کنیم. زیرا نمی‌خواهیم اگر یک شرط اعمال شد، شرط دیگری نیز اعمال شود.

ایجاد چند الگو برای یک سلول به کمک Manage Rules

ایجاد چند الگو برای یک سلول به کمک Manage Rules