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

 

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

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

  • مقررات سلول برجسته
  • قوانین بالا / پایین

شما همچنین می توانید قوانین خود را تعریف کنید. شما می توانید:

  • یک قانون اضافه کنید
  • یک قانون موجود را پاک کنید
  • قوانین تعریف شده را مدیریت کنید

علاوه بر این، شما گزینه های مختلف قالب بندی در Excel را انتخاب کرده اید که برای Visualization Data مناسب هستند :

  • نوار داده ها
  • مقیاس رنگ
  • تنظیم آیکون

قالب بندی محرمانه بر روی نسخه های Excel 2007، Excel 2010، Excel 2013 ارتقا یافته است. مثال هایی که در این فصل عنوان می شود از اکسل 2013 است.
در بخش های زیر، شما قوانین قالب بندی شرطی، گزینه های قالب بندی و نحوه کار با قوانین را درک خواهید کرد.

 

قوانین سلول های برجسته
شما می توانید از قانون سلول برجسته ،برای سلول هایی استفاده کنیدکه مقادیر آن شامل معیار های زیر می شوند:

  • اعداد در یک محدوده عددی داده شده 
  • بزرگتر از
  • کمتر از
  • بین
  • مساوی با
  • تاریخ وقوع در محدوده مشخصی از تاریخ فعلی -
  • دیروز
  • امروز
  • فردا
  • در 7 روز گذشته
  • هفته گذشته
  • این هفته
  • هفته بعد
  • ماه گذشته
  • این ماه
  • ماه بعد

ارزش هایی که تکراری یا منحصر به فرد هستند.

  • مراحل را دنبال کنید تا سلولهای قالب بندی را بصورت شرطی، در محدوده ای که به صورت شرطی قالب بندی شده انتخاب کنید.
  • روی Conditional Formatting  در گروه Styles زیر تب Home کلیک کنید.
  • در Highlight Cells Rules روی نکات برجسته سلول ها کلیک کنید.

 

 

  • روی Greater Than کلیک کنید و مشخص کنید> 750. بزرگتر از 750رنگ سبز را انتخاب می کنید.
  • کلیک کنید Less Than <500 را مشخص کنید. کمتر از 500رنگ قرمز را انتخاب می کنید.
  • روی میانبر Between  کلیک کنید و 500 و 750 را مشخص کنید. بین این دو عدد رنگ زرد را انتخاب می کنید.

 

 

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

 

 

 

قوانین بالا / پایین
شما می توانید از قوانین بالا / پایین استفاده کنید تا یک قالب را برای سلول هایی که مقادیر هر یک از معیارهای زیر را برآورده می کنند،داشته باشید.

  • 10 عدد برتر - سلول هایی که در بالای N قرار دارند، که 1 <= N <= 1000 است.
  • 10٪ برتر - سلول هایی که در بالای n درصد قرار دارند، که در آن 1 <= n <= 100 است.
  • پایین 10 مورد - سلول هایی که در پایین N قرار دارند، که 1 <= N <= 1000 است.
  • پایین 10٪ - سلول هایی که در پایین n٪ قرار دارند، که در آن 1 <= n <= 100 است.
  • بالاتر از میانگین - سلول هایی که برای محدوده انتخاب شده بالاتر از حد متوسط هستند.
  • در زیر میانگین - سلول هایی که برای محدوده انتخابی زیر میانگین هستند.

برای تعیین مقررات بالا / پایین، مراحل زیر را انجام دهید.

  • محدوده ای را که به صورت شرطی قالب بندی شده انتخاب کنید.
  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • از Top/Bottom Rules روی منوی کشویی کلیک کنید. گزینه های Top/Bottom ظاهر می شود.

 

  • روی Top Ten Items کلیک کنید و 5 را انتخاب کنید و رنگ سبز را انتخاب کنید.
  •  روی Bottom Ten Items کلیک کنید و 5 را انتخاب کنیدو رنگ قرمز را انتخاب کنید.
     

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

 

  • تکرار سه مرحله اول که در بالا ذکر شد.
  • بالای ده٪ کلیک کنید و مشخص کنید 5. رنگ سبز را انتخاب کنید.
  • کلیک کنید پایین ده٪ و مشخص 5. رنگ قرمز را انتخاب کنید.

 

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

  • تکرار سه مرحله اول که در بالا ذکر شد.
  • کلیک کنید  Top Ten% رنگ سبز را انتخاب کنید
  • کلیک کنید Bottom Ten%. رنگ قرمز را انتخاب کنید.

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

 

نوار داده ها

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

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

  • محدوده ای را انتخاب کنید که به صورت شرطی قالب بندی شود.
  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • روی نوار داده ها Data Bars از منوی کشویی کلیک کنید. گزینه های Gradient Fill و Fill ظاهر می شوند.

 

روی نوار داده Data Bars آبی در گزینه Gradient Fill کلیک کنید.

 

  • تکرار سه مرحله اول
  • با کلیک بر روی نوار داده Data Bars آبی گزینه های جامد را پر کنید.


 

 

مقیاس رنگ

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

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

  1. مقیاس سه رنگ
  • سبز - زرد - مقیاس رنگ قرمز
  • قرمز - زرد - مقیاس رنگ سبز
  • سبز - سفید - مقیاس رنگ قرمز
  • قرمز - سفید - مقیاس رنگ سبز
  • آبی - سفید - مقیاس رنگ قرمز
  • قرمز - سفید - مقیاس رنگ آبی

     2.مقیاس دو رنگ

  • سفید - مقیاس رنگ قرمز
  • قرمز - مقیاس رنگ سفید
  • سبز - مقیاس رنگ سفید
  • سفید - مقیاس رنگ سبز
  • سبز - مقیاس رنگ زرد
  • زرد - مقیاس رنگ سبز

مراحل زیر را دنبال کنید :

  • محدوده را به صورت شرطی قالب بندی کنید.
  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • از منوی کشویی روی Color Scales کلیک کنید. گزینه های مقیاس رنگ ظاهر می شوند.
  • روی مقیاس رنگ سبز - زرد - رنگ قرمز کلیک کنید.

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

  • تکرار سه مرحله اول
  • روی مقیاس رنگ سبز - سفید کلیک کنید.

داده ها بر اساس مقیاس رنگی سبز - سفید در محدوده انتخاب شده برجسته خواهند شد.

 

تنظیم آیکون

شما می توانید از مجموعه آیکون برای تجسم تفاوت های عددی استفاده کنید. مجموعه آیکون های موجود در دسترس هستند .

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

  • محدوده ای را که به صورت شرطی قالب بندی شده انتخاب کنید.
  • روی  Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • روی Icon Sets از منوی کشویی کلیک کنید. گزینه های مجموعه آیکون ظاهر می شود.
  • بر روی سه رنگ فلش کلیک کنید.

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

  • با تکرار سه مرحله اول گزینه های مجموعه آیکون ظاهر می شود.
  • 5 امتیاز را انتخاب کنید. آیکون های رتبه بندی در کنار داده ها بر اساس مقادیر در محدوده انتخاب شده ظاهر می شوند.

 

 

قانون جدید

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

دو راه برای استفاده از قانون جدید وجود دارد :

  • با گزینه New Rule از منوی کشویی
  • با دکمه New Rule در منوی مدیریت قوانین

با گزینه New Rule از منوی کشویی

  • محدوده را به صورت شرطی قالب بندی کنید.
  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • از منوی کشویی روی New Rule کلیک کنید.

کادر محاوره ای New Formatting Rule ظاهر می شود.

  • از فیلد Select a Rule Type را انتخاب کنید، برای تعیین سلولهایی که برای فرمت کردن انتخاب کنید، از یک فرمول استفاده کنید. ویرایش جعبه توضیحات ظاهر می شود.
  • در مقادیر قالب که در آن این فرمول درست است: فرمول را تایپ کنید.
  • روی format button کلیک کنید و روی OK کلیک کنید.

سلولهایی که حاوی مقادیر با فرمول TRUE هستند، به صورت تعریف شده قالب بندی می شوند.

با دکمه جدید قانون در کادر محاوره ای مدیریت قوانین

  • محدوده ای را که به صورت شرطی قالب بندی شده انتخاب کنید.
  • روی Conditional Formatting در گروه Styles زیر تب  Home کلیک کنید.
  • از منوی کشویی روی Manage Rules کلیک کنید.

کادر محاوره ای فرمت قوانین شرطی Conditional Formatting Rules Manager  ظاهر می شود.

روی دکمه New Rule کلیک کنید.

کادر محاوره ای New Formatting Rule ظاهر می شود.

تکرار مراحل داده شده در بالا برای تعریف فرمول و فرمت شما می باشد.

کادر محاوره ای قوانین قالب بندی شرطی Conditional Formatting Rules Manager با خط New Rule مشخص شده ظاهر می شود. روی دکمه Apply  کلیک کنید.

سلولهایی که حاوی مقادیر با فرمول TRUE هستند، به صورت تعریف شده قالب بندی می شوند.

 

پاک کردن قوانین

شما می توانید قوانین را پاک کنید تا تمامی فرمت های شرطی که برای آن ایجاد کرده اید را حذف کنید

  • سلولهای انتخاب شده
  • برگه فعلی
  • جدول انتخاب شده
  • PivotTable انتخاب شده

مراحل داده شده را دنبال کنید -

  • محدوده را انتخاب کنید / روی یک برگه کلیک کنید / روی جدولی کلیک کنید> PivotTable که قوانین قالب بندی شرطی باید حذف شود.
  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • روی Clear Rules از منوی کشویی کلیک کنید. گزینه های حذف قوانین ظاهر می شود.

گزینه مناسب را انتخاب کنید. قالب بندی شرطی از Range / Worksheet / Table / PivotTable حذف می شود.

 

مدیریت قوانین

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

  • روی Conditional Formatting در گروه Styles زیر تب Home  کلیک کنید.
  • از منوی کشویی روی Manage Rules کلیک کنید.

Conditional Formatting Rules Manager ظاهر می شود.

 Show formatting rules بر روی این برگه و سایر صفحات، جداول، پیوند جدول اگر با قوانین قالب بندی شرطی ظاهر شوند، کلیک کنید.

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

شما می توانید یک قانون جدید اضافه کنید، ویرایش یک قانون و حذف یک قانون.

  • شما قبلا قوانین جدید را در بخش قبلی دیده اید. شما می توانید یک قانون را با انتخاب قانون و کلیک روی حذف قانون حذف کنید. قانون برجسته حذف شده است.
  • برای ویرایش یک Rule، RULE را انتخاب کرده و روی Edit Rule کلیک کنید. جعبه محاوره Rule Formatting ظاهر می شود.
  • شما می توانید
  • یک نوع قانون را انتخاب کنید
  • توصیف قانون را ویرایش کنید
  • فرمت ویرایش
  • هنگامی که تغییرات انجام می شود، روی OK کلیک کنید.
  • تغییرات Conditional Formatting Rules Manager در قانون مدیریت فرمت قوانین شرطی منعکس می شود. روی Apply کلیک کنید.
  • داده ها بر اساس قانون Conditional Formatting Rule. اصلاح شده وبرجسته می شوند.

 

نظرات کاربران

نظر خودتان را بنویسید