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

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

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

مبانی قالب بندی شرطی در اکسل

با استفاده از قالب بندی شرطی، می توانیم:

• مقادیر رنگ
• تغییر فونت
• تنظیم فرمت حاشیه

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

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

دکمه "قالب بندی مشروط" در نوار ابزار، در برگه "صفحه اصلی" قرار دارد:

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

هنگام اعمال قالب بندی شرطی، سیستم باید دو تنظیم را تنظیم کند:

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

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

• در جدول داده، محدوده ای را که می خواهیم برجسته سازی رنگ را برای آن اعمال کنیم، انتخاب کنید:

• به تب Home در نوار ابزار بروید و روی Conditional Formatting کلیک کنید. در لیست کشویی، چندین نوع قالب را برای انتخاب مشاهده خواهید کرد:
  • قوانین انتخاب
  • قوانین انتخاب اولین و آخرین مقادیر
  • هیستوگرام ها
  • مقیاس های رنگی
  • مجموعه آیکون ها
• در مثال ما، می‌خواهیم داده‌هایی را با مقدار منفی برجسته کنیم. برای انجام این کار، نوع “Cell selection rules” => “Less than” را انتخاب کنید:

همچنین شرایط زیر موجود است:

1. مقادیر بزرگتر یا مساوی با مقداری هستند.
2. برجسته کردن متن حاوی حروف یا کلمات خاص؛
3. برجسته کردن موارد تکراری؛
4. تاریخ های خاص را برجسته کنید.

• در پنجره پاپ آپ، در قسمت "Formatcells that are LESS"، مقدار "0" را مشخص کنید، زیرا باید مقادیر منفی را با رنگ برجسته کنیم. در لیست کشویی سمت راست، قالبی را انتخاب کنید که شرایط را داشته باشد:

• برای اختصاص یک فرمت، می توانید از پالت های رنگی از پیش پیکربندی شده استفاده کنید و همچنین پالت خود را ایجاد کنید. برای انجام این کار، روی مورد کلیک کنید:

• در پنجره پاپ آپ فرمت، مشخص کنید:
  • رنگ پر کن
  • رنگ فونت
  • فونت
  • مرزهای سلولی

• پس از انجام تنظیمات، روی دکمه "OK" کلیک کنید.

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

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

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

• محدوده ای از داده ها را انتخاب کنید. در نوار ابزار روی "قالب بندی شرطی" کلیک کنید. در لیست کشویی، مورد "قانون جدید" را انتخاب کنید:

• در پنجره پاپ آپ باید نوع قانون اعمال را انتخاب کنیم. در مثال ما، نوع «فرمت فقط سلول‌هایی که حاوی هستند» برای ما مناسب است. پس از آن، برای انتخاب داده هایی که مقادیر آنها بزرگتر از "57" اما کمتر از "59" باشد، شرطی را تعیین می کنیم:

• همانطور که در مثال بالا انجام دادیم، روی دکمه "Format" کلیک کنید و قالب را تنظیم کنید. روی دکمه "OK" کلیک کنید:

قالب بندی مشروط بر اساس مقدار سلول دیگر

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

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

• اولین سلول را برای اختصاص قانون انتخاب کنید. در نوار ابزار روی "قالب بندی شرطی" کلیک کنید. بیایید شرط «کمتر از» را انتخاب کنیم.
• در پنجره پاپ آپ، پیوند سلولی را که این سلول با آن مقایسه می شود، مشخص کنید. قالب را انتخاب می کنیم. دکمه "OK" را فشار دهید.

• بیایید سلولی را که قالب را به آن اختصاص داده ایم با دکمه سمت چپ ماوس دوباره انتخاب کنیم. روی "قالب بندی شرطی" کلیک کنید. "مدیریت قوانین" را در منوی کشویی انتخاب کنید => روی دکمه "ویرایش قانون" کلیک کنید:

• در قسمت سمت چپ پنجره پاپ آپ، پیوند را از علامت «$» پاک کنید. روی دکمه "OK" و سپس دکمه "اعمال" کلیک کنید.

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

در تصویر زیر، داده ها به صورت رنگی مشخص شده اند که در آن نرخ ارز نسبت به دوره قبل کمتر شده است:

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

اعمال قوانین متعدد در یک سلول امکان پذیر است.

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

برای اعمال چند شرط برای یک سلول واحد، موارد زیر را انجام دهید:

• محدوده ای را با داده هایی که می خواهیم قالب بندی شرطی را اعمال کنیم انتخاب کنیم => روی آیتم "قالب بندی شرطی" در نوار ابزار کلیک کنید => شرط انتخاب "بیش از ..." را انتخاب کنید و شرط اول را مشخص کنید (اگر بیشتر از 10 باشد. ، سپس پر سبز). برای هر یک از شرایط (بیش از 20 و بیشتر از 30) اقدامات مشابه را تکرار می کنیم. علیرغم این واقعیت که ما سه قانون را اعمال کردیم، داده های جدول به رنگ سبز سایه زده می شوند:

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

• فیلدهای داده؛
• پالت رنگ؛
• تنظیم فونت

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

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

ابتدا، بیایید نحوه انتخاب معیارهای قالب بندی مناسب و نحوه تغییر آنها را بررسی کنیم. درک اصل عملکرد آن با یک مثال آماده ساده تر است:

فرض کنید یک ستون شامل محدوده ای از سلول ها با مقادیر عددی است. اگر آنها را با شرایط قالب بندی مناسب تعریف کنید، تمام مقادیر با عدد بزرگتر از 100 به رنگ قرمز نمایش داده می شوند. برای اجرای این کار، این ابزار اکسل مقدار هر سلول از محدوده مشخص شده را مطابق با شرایط معیارها تجزیه و تحلیل می کند. نتایج تجزیه و تحلیل مثبت هستند، به عنوان مثال (A2>100=TRUE)، سپس یک قالب جدید از پیش تعریف شده (رنگ قرمز) اختصاص داده می شود. در نتیجه مخالف (A2>100=FALSE)، قالب سلول ها تغییر نمی کند.

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

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

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

فرض کنید سلول A1 حاوی مقدار عددی 50 است:

شرایط زیر را برای فرمت نمایش مقادیر در A1 تعریف کنید:

1. اگر عدد بزرگتر از 15 باشد، فونت به رنگ سبز نمایش داده می شود.
2. اگر عدد بزرگتر از 30 باشد، فونت به رنگ زرد نمایش داده می شود.
3. اگر عدد بزرگتر از 40 باشد، فونت به رنگ قرمز نمایش داده می شود.

مطمئناً متوجه شدید که مقدار 50 در سلول A1 با تمام شرایط مطابقت دارد (A1>15، A1>30 و A1>40 = TRUE). اکسل با چه رنگ فونتی مقدار عددی 50 را نمایش می دهد؟

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

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

یک قانون دوم ایجاد کنید

مثال دوم فرض کنید باید هزینه های ستون C را به این صورت قالب بندی کنیم:

همه مبالغ بین 300 تا 600 دلار باید دارای پس‌زمینه زرد در سلول‌های خود باشند و مبالغ کمتر از 500 دلار نیز باید دارای رنگ فونت قرمز باشند.

بیایید سعی کنیم این شرایط را بسازیم:

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

با انتشار نسخه جدید نرم افزار کامپیوترمایکروسافت آفیس نیز ویژگی های جدیدی دارد. توسعه دهندگان برخی از مؤلفه ها را بهبود بخشیده اند و کار با برنامه ها را راحت تر کرده اند. ما نمی توانیم اکسل 2010 و ویژگی های جدید اینفوگرافیک آن را نادیده بگیریم. بنابراین در این مقاله از یک مثال برای آموزش نحوه کار با اجزای جدید اکسل 2010 استفاده می کنیم.

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

مشاهده تعداد زیادی از مقادیر و مقایسه آنها با مقادیر برنامه ریزی شده همیشه راحت نیست. فرض کنید میزان درآمد هر مدیر در ماه باید حداقل 100000 روبل باشد. اما لازم نیست شاخص ها را به صورت دستی با مشاهده هر مقدار ارزیابی کنید، اعتماد به مؤلفه داخلی آسان تر است برتری داشتن. بیایید ناحیه داده را انتخاب کنیم. از منوی کشویی به تب "Insert - Conditional Formatting - Icon Set" بروید، الگوی مورد علاقه خود را انتخاب کنید، من چراغ راهنمایی را دوست دارم، زیرا کار با آن بسیار راحت است. پس از انتخاب یک الگو، پنجره "ایجاد قوانین قالب بندی" در مقابل ما ظاهر می شود. در اینجا لازم است داده هایی را در مقابل همین نمادها وارد کنید که در بالای آنها کار کارمند به عنوان: عالی، رضایت بخش و رضایت بخش ارزیابی می شود. داده ها در پارامتر "Value" مقابل هر یک از دایره ها وارد می شوند و فیلد "نوع" در این مورد باید از "درصد" به "اعداد" تغییر کند. من پارامترهای زیر را تنظیم کرده ام: 110، 90. پارامتر سوم به طور خودکار تنظیم می شود، به عنوان تمام مقادیر کمتر از رضایت بخش ارزیابی می شود. ما دکمه "OK" را فشار می دهیم.

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

اما این آخرین راه برای قالب بندی مشروط داده ها نیست. عناصر اینفوگرافیک جدیدی مانند «هیستوگرام» و «مقیاس رنگ» در آن ظاهر شد. بیایید آنها را با جزئیات بیشتری در نظر بگیریم. مقادیر موجود در سلول ها را انتخاب کنید و به "Insert - Conditional Formatting - Bar Charts" بروید. لیستی از الگوها در منوی کشویی ظاهر می شود، هنگامی که ماوس را روی هر یک از آنها نگه دارید، نتیجه پیش نمایش داده می شود. طرح رنگی را که دوست داریم انتخاب می کنیم و می بینیم که سلول ها با ستون های افقی با اندازه های مختلف پر شده اند. آنها مقادیر موجود در سلول ها را به صورت گرافیکی نمایش می دهند. اگر عددی با علامت منفی وارد شود، نمودار در جهت مخالف سلول حرکت می کند و مقادیر منفی را نشان می دهد.

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

برش ها و بیشتر

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

راه دوم با استفاده از عملکرد جدید "Slice" سازماندهی شده است، ابزاری جالب برای تجزیه و تحلیل داده های دیجیتال. بیایید به "درج - برش" برویم. پنجره "Insert Slice" باز می شود؛ در آن، شما باید شاخصی را که با آن مقادیر نمونه برداری می شود، علامت گذاری کنید، یعنی ستونی از جدول که توسط آن می توانید برش های گزارش خود را مشاهده کنید. "Dates" را انتخاب کنید و روی دکمه "OK" کلیک کنید. کاربرگ یک فریم با مقادیر نوشته شده در آن نمایش می دهد.

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

منحنی اطلاعات

راه بعدی برای تجزیه و تحلیل بصری داده ها، منحنی های اطلاعاتی است. یک سلول آزاد را در مقابل ردیف های داده فعال می کنیم. در تب "Insert" بخش "Infocurves" را پیدا می کنیم (در نسخه من به دلایلی آنها را "Sperklines" می نامیدند). ما محدوده داده را انتخاب می کنیم - این خط ما خواهد بود و دکمه "OK" را فشار دهید. می توانید ببینید که چگونه یک نمودار کوچک در سلولی که انتخاب کردیم ساخته شده است، این منحنی اطلاعات است.

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

Infocurves سه نوع هستند: "گراف" - ما فقط آن را در نظر گرفتیم. "ستون" - داده ها را به شکل ستون های کوچک نمایش می دهد و به وضوح حداکثر و حداقل مقادیر را نشان می دهد. "برد / باخت" - سلول به نوعی به دو قسمت تقسیم می شود، مربع هایی با مقادیر منفی در قسمت پایین قرار می گیرند، موارد مثبت در قسمت بالایی، صفر به هیچ وجه نمایش داده نمی شود.

نتیجه

در این مقاله، ما نه تنها یاد گرفتیم که چگونه به سرعت جدول ترسیم کنیم، بلکه تجزیه و تحلیل بصری داده ها را نیز انجام دادیم. ما همچنین با مفاهیمی مانند جدول محوری آشنا شدیم، نحوه فیلتر کردن مقادیر و قالب بندی مشروط مقادیر دیجیتال و ایجاد برش ها را آموختیم. علاوه بر این، ما به وضوح ویژگی جدیدی به نام "Infocurves" را کشف کردیم. لازم به ذکر است که پیشرفت ها در چهره قابل مشاهده است و تقریباً تمام عملکردهای جدید با هدف تسهیل کار یک متخصص و ارائه بصری داده ها انجام می شود. اگر به عملکرد جدید ویرایشگر صفحه گسترده علاقه دارید، می توانید با شرکای 1CSoft تماس بگیرید.

نبرکوتین اسکندر

تمامی حقوق محفوظ است. برای اطلاعات در مورد استفاده از این مقاله لطفا تماس بگیرید مدیران سایت

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

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

ابزار Conditional Formatting در صفحه اصلی در قسمت Styles قرار دارد.

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

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

بیایید یک سری اعداد را در محدوده A1 معرفی کنیم: A11:

محدوده ای از مقادیر را انتخاب کنید. منوی Conditional Formatting را باز کنید. "قوانین انتخاب سلول" را انتخاب کنید. بیایید یک شرط، برای مثال، "بیش از".

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

با فشار دادن دکمه OK از منو خارج شوید.

قالب بندی مشروط بر اساس مقدار سلول دیگر

بیایید مقادیر محدوده A1:A11 را با عدد موجود در سلول B2 مقایسه کنیم. بیایید عدد 20 را قرار دهیم.

محدوده منبع را انتخاب کنید و پنجره ابزار "Conditional Formatting" (در زیر به اختصار "UV") باز کنید. برای این مثال، شرط "کمتر از" را اعمال می کنیم ("قوانین انتخاب سلول" - "کمتر از").

نتیجه قالب بندی بلافاصله در برگه اکسل قابل مشاهده است.

مقادیر محدوده A1:A11 که کمتر از مقدار سلول B2 است، با پس زمینه انتخاب شده پر می شود.

بیایید شرایط قالب بندی را تنظیم کنیم: مقادیر سلول ها را در محدوده های مختلف مقایسه کنید و همان ها را نشان دهید. ستون A1:A11 را با ستون B1:B11 مقایسه می کنیم.

محدوده اولیه (А1:А11) را انتخاب کنید. "UV" - "Cell Selection Rules" - "Equal" را فشار دهید. در حاشیه سمت چپ پیوندی به سلول B1 وجود دارد. لینک باید باشه مختلط یا نسبی!، نه مطلق

هر مقدار در ستون A توسط برنامه با مقدار مربوطه در ستون B مقایسه شد. مقادیر یکسان با رنگ هایلایت شده اند.

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

در مثال ما، سلول A1 هنگام فراخوانی ابزار فعال بود. مرجع $B1. بنابراین، اکسل مقدار سلول A1 را با مقدار B1 مقایسه می کند. اگر ستون را نه از بالا به پایین، بلکه از پایین به بالا انتخاب کنیم، سلول A11 فعال خواهد بود. و این برنامه B1 را با A11 مقایسه می کند.

مقایسه کنید:

برای اینکه ابزار Conditional Formatting وظیفه خود را به درستی انجام دهد، مراقب این لحظه باشید.

می توانید صحت شرط داده شده را به روش زیر بررسی کنید:

1. اولین سلول را در محدوده قالب بندی شرطی انتخاب کنید.
2. منوی ابزار را باز کنید، روی "Manage Rules" کلیک کنید.

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

قالب بندی شرطی - شرایط چندگانه

محدوده اولیه A1:A11 است. لازم است اعدادی که بزرگتر از 6 هستند را با قرمز برجسته کنید. سبز - بزرگتر از 10. زرد - بزرگتر از 20.

طبق شرط اول گزینه های قالب بندی را پر کنید:

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

لطفاً توجه داشته باشید که مقادیر برخی از سلول ها همزمان با دو یا چند شرط مطابقت دارد. اولویت پردازش بستگی به ترتیب فهرست بندی قوانین در "Dispatcher" - "Manage Rules" دارد.

یعنی عدد 24 که همزمان از 6 و 10 و 20 بزرگتر است مشمول شرط "=$A1>20" (نخستین لیست) است.

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

محدوده ای را با تاریخ انتخاب کنید.

بیایید "UV" - "Date" را به آن اعمال کنیم.

لیستی از شرایط موجود (قوانین) در پنجره ای که باز می شود ظاهر می شود:

مورد دلخواه را انتخاب کنید (مثلاً برای 7 روز گذشته) و روی OK کلیک کنید.

سلول های دارای تاریخ هفته گذشته با رنگ قرمز مشخص شده اند (تاریخ نوشتن مقاله 1395/02/02 می باشد).

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

اگر قوانین استاندارد کافی نباشد، کاربر می تواند یک فرمول را اعمال کند. تقریباً همه: امکانات این ابزار بی پایان است. بیایید یک گزینه ساده را در نظر بگیریم.

یک ستون با اعداد وجود دارد. لازم است سلول ها را با اعداد زوج برجسته کنید. ما از فرمول استفاده می کنیم: \u003d OSTAT ($ A1؛ 2) \u003d 0.

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

برای بستن پنجره و نمایش نتیجه - OK.

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

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

جدول نمونه:

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

محدوده ای را با مقادیر جدول انتخاب کنید. روی "UV" - "Create Rule" کلیک کنید. نوع قانون یک فرمول است. بیایید از تابع IF استفاده کنیم.

نحوه پر کردن شرایط قالب بندی "پروژه های تکمیل شده":

به طور مشابه، قوانین قالب بندی را برای پروژه های ناتمام تنظیم می کنیم.

در "Dispatcher" شرایط به این صورت است:

نتیجه را می گیریم:

هنگامی که گزینه های قالب بندی برای کل محدوده تنظیم می شود، این شرط همزمان با پر شدن سلول ها اجرا می شود. به عنوان مثال، بیایید پروژه دیمیتروا را برای 28 ژانویه "تکمیل" کنیم - به جای "R" "Z" را قرار می دهیم.

"رنگ آمیزی" به طور خودکار تغییر می کند. با ابزارهای استاندارد اکسل، رسیدن به چنین نتایجی زمان زیادی می برد.

درس 8

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

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

برگه تراز به شما اجازه می دهد تا تعیین کنید: جایی که متن در سلول قرار می گیرد. فرض کنید متنی را در یک سلول تایپ کرده ایم.

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

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

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

و متن به سمت بالا حرکت خواهد کرد.

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

مشاهده می شود که اندازه عمودی (ردیف ها) در جهت افزایش تغییر کرده است. بنابراین خطوط زیر میز کوچکتر از جایی است که یک جدول وجود دارد. توجه داشته باشید که نوع فونت در هدر متفاوت است، زیرا اندازه (عرض) سلول افزایش می یابد.

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

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

در قسمت Sample می توانید ببینید که متن چگونه خواهد بود.

حالا دوباره هدر جدول را انتخاب می کنیم و تیک پارامتر عرض autofit را بردارید و تصویر زیر را دریافت می کنیم.

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

در پانل ظاهر شده، حالت را انتخاب کنیدتنظیم خودکار عرض متن. ما گرفتیم:

در تب Border، می‌توانید حاشیه‌های اطراف سلول را تعیین کنید. فرض کنید چندین سلول داریم، همانطور که در شکل نشان داده شده است. زیر

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

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

حالا دوباره جدول را انتخاب کنید و دوباره از تب استفاده کنیدمرز.

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

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

ذخیره سبک سلول . فرض کنید هنگام ایجاد جداول زیر از سبک به دست آمده استفاده می کنیم. بنابراین دوباره چهار سلول را انتخاب کرده و روی دکمه کلیک کنیدسبک های سلول در برگه صفحه اصلی.

در حال حاضر استایل هایی در برنامه نصب شده است، اما ما باید سبک خود را ایجاد کنیم. پس بیایید روی آن کلیک کنیمایجاد سبک سلولی.

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

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

گاهی اوقات نیاز به تخصیص اعداد بسته به شرایط خاصی است. بنابراین، اگر جدول داده‌های مقایسه‌ای در مورد دسته‌هایی از جمعیتی که از برخی محصولات سوء استفاده می‌کنند ارائه می‌کند، بهتر است افرادی که مستعد نوشیدنی‌های الکلی هستند، گیاه‌خوارانی که از تخت‌خواب‌های باغ با فونت زیر خط‌دار غذا می‌خورند و کسانی که مصرف می‌کنند برجسته شود. مقادیر غیر قابل کنترل غذا به صورت پررنگ. علاوه بر این، استفاده از فرمت رنگ ممکن است تحت شرایط خاصی دیکته شود. به عنوان مثال، اگر دمای یک آپارتمان در زمستان از 0 بالاتر نمی رود، تعداد این آپارتمان ها باید با رنگ آبی، در دمای 0 - 10 درجه - سبز، در محدوده 10 - 20 درجه - نشان داده شود. در رنگ زرد، و بیش از 30 درجه - در قرمز.

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

پنجره ای ظاهر می شود که در آن مقادیر قوانین را تعیین می کنیم.

بیایید کار را طوری تنظیم کنیم که رنگ پس زمینه سلول ها بسته به مقدار آنها باشد. بیایید قانون بالا را انتخاب کنیم همه سلول ها را قالب بندی کنیدبر اساس مقادیر آنها و بر روی دکمه کلیک کنیدخوب .

اگر رنگ را به آبی تغییر دهیم، جدول زیر به دست می آید.

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

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

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

با استفاده از حالت - مجموعه نمادها می توانید نمادها را در کنار مقادیر تنظیم کنید.

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

در پنجره، تمام مقادیری که بین 25 تا 72 هستند با رنگ قرمز روشن و رنگ قرمز تیره برجسته می شوند. این مقادیر را می توان با وارد کردن آنها از صفحه کلید تغییر داد.

در لیست مقادیری که می توانید قالب را تغییر دهید، یک قالب سفارشی وجود دارد که می توانید نوع فونت، سبک و غیره را تغییر دهید، به عنوان مثال، می توانید استایل را پررنگ کنید.

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