اگر به استفاده از نسخه های قدیمی اکسل^(Excel) عادت دارید ، گزینه های قالب بندی شرطی در اکسل 2007^{(Excel 2007)} ، 2010 و 2013 شما را شگفت زده خواهد کرد. پس چرا می خواهید با استفاده از قالب بندی شرطی زحمت بکشید؟ خوب، در اینجا چند دلیل وجود دارد که چرا من عاشق استفاده از این ویژگی اکسل^(Excel) هستم :

1. برای اینکه داده های شما از نظر بصری جذاب تر شود.

2. برای اینکه صفحات گسترده خود را در یک نگاه ساده تر درک کنید.

3. شناسایی انواع خاصی از اعداد برای کمک به حل مسئله^{(problem solving)} .

4. برای کمک به شما در نتیجه گیری از داده های خود.

5. برای نمایش بصری به کاربر آنچه "خوب" یا "بد" با استفاده از سبز و قرمز است.

اکنون، می توانید از قالب بندی شرطی برای قالب بندی هر سلول در یک محدوده بر اساس معیارهای خود استفاده کنید (و گزینه های قالب بندی زیادی برای انتخاب وجود دارد). به عنوان مثال، اگر یک برگه سود^{(profit sheet)} دارید و می خواهید همه سودهای بیشتر از 200 دلار را به رنگ سبز و همه سودهای کمتر از 200 دلار را به رنگ زرد و همه ضررها را قرمز رنگ کنید، می توانید از قالب بندی شرطی استفاده کنید تا به سرعت همه کارها را برای شما انجام دهد. .

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

قالب بندی مشروط به شما امکان می دهد حجم قابل توجهی از داده ها را به سرعت و به راحتی قالب بندی کنید - در حالی که همچنان می توانید انواع مختلف داده ها را تشخیص دهید. می توانید قوانینی را برای گزینه های قالب بندی ایجاد کنید که به مایکروسافت اکسل^{(Microsoft Excel)} اجازه می دهد تا برای شما قالب بندی خودکار را انجام دهد. شما واقعاً فقط باید سه مرحله ساده را دنبال کنید.

مرحله 1:^{(Step 1:)} سلول هایی را که می خواهید قالب بندی کنید انتخاب کنید.

مرحله 2:^{(Step 2:)} روی دکمه Conditional Formatting در منوی Home^{(Home menu)} ، بخش Styles^{(Styles section)} کلیک کنید .

مرحله 3:^{(Step 3:)} قوانین خود را انتخاب کنید. قوانین برجسته سلول ها^{(Highlight Cells Rules)} و قوانین Top/Bottom Rules  در بالا وجود دارد که به شما امکان مقایسه با مقادیر را می دهد. برای این مثال، ما سه قانون را وضع کردیم. اولین مورد این بود که هر مقدار بیشتر از 200 دلار سبز بود.

شایان ذکر است که فقط از بخش Highlight Cells Rules^{(Highlight Cells Rules section)} نیز می توان برای مقایسه یک مجموعه داده با مجموعه داده دیگری استفاده کرد. هر چیز دیگری فقط از یک مجموعه داده ای که برجسته کرده اید استفاده می کند و مقادیر را با یکدیگر مقایسه می کند. به عنوان مثال، هنگام استفاده از قانون Greater Than^{(Greater Than rule)} ، می توانم مقادیر A1 تا A20 را با یک عدد خاص مقایسه کنم یا می توانم A1 تا A20 را با B1 تا B20 مقایسه کنم .

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

اگر این گزینه‌های قالب‌بندی را دوست ندارید، اکسل^(Excel) بسیاری از گزینه‌های جدید قالب‌بندی مشروط^{(Conditional Formatting)} را دارد که می‌توانید از آنها استفاده کنید. به عنوان مثال، می‌توانید نمادهایی مانند فلش‌های رنگی ( مجموعه‌های آیکون^{(Icon Sets)} )، نمودارهای میله‌ای مانند مثال دوم ( نوارهای داده^{(Data Bars)} )، یا حتی طیفی از رنگ‌های انتخاب شده به‌طور خودکار مانند نمونه آخر ( مقیاس‌های رنگ^{(Color Scales)} ) را وارد کنید. این سه گزینه فقط مقادیر یک مجموعه داده را با هم مقایسه می کنند. اگر A1 تا A20 را انتخاب کنید ، فقط آن مقادیر را با یکدیگر مقایسه می کند.

اگر بعداً تصمیم گرفتید که نمی خواهید سلول های شما به صورت مشروط قالب بندی شوند، تنها کاری که باید انجام دهید این است که قالب بندی را پاک کنید. برای انجام این کار، دکمه Conditional Formatting را انتخاب کرده و ^{(Conditional Formatting button and select)} Clear Rules را انتخاب کنید . سپس، انتخاب کنید که آیا می خواهید قوانین را فقط از سلول های انتخاب شده پاک کنید یا از کل کاربرگ.

همچنین، اگر چندین قانون ایجاد کرده باشید، ممکن است فراموش کنید که چه قوانینی را برای چه سلول هایی اعمال کرده اید. از آنجایی که می‌توانید قوانین زیادی را برای یک مجموعه از سلول‌ها اعمال کنید، به خصوص اگر شخص دیگری صفحه‌گسترده را ایجاد کند، می‌تواند بسیار گیج‌کننده باشد. برای مشاهده تمامی قوانین، روی دکمه Conditional Formatting^{(Conditional Formatting button)} و سپس روی Manage Rules کلیک کنید.^{(Manage Rules.)}

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

همچنین یک چک باکس به نام Stop If True وجود دارد که در اینجا به جزئیات آن نمی پردازم زیرا کاملاً پیچیده است. با این حال، می توانید این پست مایکروسافت^(Microsoft) را بخوانید که آن را با جزئیات بسیار توضیح می دهد.

گزینه های قالب بندی مشروط جدید Excel 2010^{(New Conditional Formatting Options Excel 2010)}

تقریباً همه چیز در اکسل 2010^{(Excel 2010)} یکسان است در مورد قالب بندی شرطی^{(Conditional Formatting)} که در اکسل 2007^{(Excel 2007)} گنجانده شده بود . با این حال، یک ویژگی جدید وجود دارد که واقعا آن را بسیار قدرتمندتر می کند.

قبلاً اشاره کرده بودم که بخش Highlight Cells Rules به شما امکان می دهد یک مجموعه از داده ها را با مجموعه دیگری از داده ها در همان صفحه گسترده مقایسه کنید. در سال 2010، اکنون می توانید به کاربرگ دیگری در همان کتاب کار مراجعه کنید. اگر سعی کنید این کار را در اکسل 2007^{(Excel 2007)} انجام دهید، به شما امکان می‌دهد داده‌ها را از کاربرگ دیگری انتخاب کنید، اما زمانی که می‌خواهید در پایان روی OK کلیک کنید، یک پیغام خطا به شما می‌دهد.^{(error message)}

در اکسل 2010^{(Excel 2010)} ، اکنون می توانید این کار را انجام دهید، اما کمی مشکل است، بنابراین می خواهم گام به گام آن را توضیح دهم. فرض کنید من دو کاربرگ دارم و در هر برگه اطلاعاتی از B2 تا B12 برای چیزی شبیه سود دارم. اگر بخواهم ببینم کدام مقادیر در B2 تا B12 از برگه 1^{(sheet 1)} بزرگتر از مقادیر B2 تا B12 در ^(B12)صفحه 2^{(sheet 2)} است، ابتدا مقادیر B2 تا B12 را در برگه 1^{(sheet 1)} انتخاب می کنم و سپس روی Great Than در قسمت Highlight Cells Rules کلیک می کنم.

حالا روی دکمه مرجع سلولی^{(cell reference button)} که در بالا نشان داده ام کلیک کنید. کادر تغییر می کند و نماد مکان نما^{(cursor icon)} به یک صلیب سفید تبدیل می شود. حالا ادامه دهید و روی صفحه 2^{(sheet 2)} کلیک کنید و  ONLY  سلول B2 را انتخاب کنید.  کل محدوده B2 تا B12 ^(NOT)را^(B12) انتخاب نکنید .

خواهید دید که کادر اکنون دارای مقدار =Sheet2!$B$2 است. ما باید این را به =Sheet2!$B2 تغییر دهیم . اساسا، فقط از شر $  ی که قبل از 2 می آید خلاص شوید. این ستون را ثابت نگه می دارد، اما اجازه می دهد شماره ردیف^{(row number)} به طور خودکار تغییر کند. به هر دلیلی، فقط به شما اجازه نمی دهد کل محدوده را انتخاب کنید.

دوباره روی^(Click) دکمه مرجع سلول کلیک^{(cell reference button)} کنید و سپس روی OK کلیک کنید. اکنون مقادیر موجود در برگه 1^{(sheet 1)} که بزرگتر از صفحه 2^{(sheet 2)} هستند، مطابق با گزینه های قالب بندی که انتخاب کرده اید قالب بندی می شوند.

امیدوارم^(Hopefully) همه اینها منطقی باشد! وقتی به اکسل 2013^{(Excel 2013)} نگاه می کنیم، به نظر نمی رسد که ویژگی جدیدی در مورد قالب بندی شرطی وجود داشته باشد. به عنوان آخرین نکته، اگر احساس می‌کنید که قوانین پیش‌فرض با آنچه که می‌خواهید انجام دهید مطابقت ندارد، می‌توانید روی  گزینه New Rule کلیک ^{(New Rule)} کنید و^{(option and start)} از ابتدا شروع کنید. چیزی که در مورد ایجاد یک قانون جدید عالی است این است که می توانید از فرمولی برای تعیین فرمت سلول ها استفاده کنید که بسیار قدرتمند است.

اگرچه قالب بندی شرطی در ظاهر نسبتاً آسان و ساده به نظر می رسد، بسته به داده ها و نیازهای شما می تواند بسیار پیچیده شود. اگر سوالی دارید، در نظر بگیرید. لذت بردن!

Format Cells using Conditional Formatting in Excel

Іf you are used to using older versions of Excel, the conditional formatting options in Excel 2007, 2010, and 2013 will amazе you. So why would you wаnt to bother using conditional formatting? Well, here are a couple of reasons why I love using this featυre of Excel:

1. To make your data more visually appealing.

2. To make your spreadsheets easier to understand at a glance.

3. To identify certain types of numbers for help in problem solving.

4. To assist you in drawing conclusions from your data.

5. To visually display to the user what is “good” or “bad” by using green and red.

Now, you can use conditional formatting to format every cell in a range based on your own criteria (and there are a lot of formatting options to choose from). For example, if you have a profit sheet and you want to color code all profits greater than $200 as green and all profits less than $200 as yellow and all losses as red, then you can use conditional formatting to quickly do all the work for you.

Conditional Formatting in Excel

Conditional formatting enables you to format significant amounts of data quickly and easily – while still being able to distinguish different types of data. You can create rules for the formatting options that will allow Microsoft Excel to auto-format for you. You really only have to follow three simple steps.

Step 1: Select the cells you want to format.

Step 2: Click the Conditional Formatting button under the Home menu, Styles section.

Step 3: Select your rules. There are Highlight Cells Rules and Top/Bottom Rules at the top that let you do comparisons with values. For this example, we imposed three rules. The first was that any value greater than $200 was green.

It’s worth noting that only the Highlight Cells Rules section can also be used to compare a dataset to another dataset. Everything else will just use the one dataset that you have highlighted and compare the values against each other. For example, when using the Greater Than rule, I can compare values from A1 to A20 against a specific number or I can compare A1 to A20 against B1 to B20.

The same logic was applied to the second and third rules. The second rule was that anything between $0 and $200 was formatted yellow. The third rule was that anything less than $0 was formatted red. Here is what a portion of the finished spreadsheet looks like.

If you do not like these formatting options, Excel has many different new Conditional Formatting options that you can use from. For example, you can insert icons like colored arrows (Icon Sets), bar charts like in the second example (Data Bars), or even a range of automatically selected colors like in the last example (Color Scales). These three options only compare values from the same dataset. If you select A1 to A20, it’ll only compare those values against each other.

If you later decide that you don’t want your cells to be conditionally formatted, all you have to do is clear the formatting. To do this, select the Conditional Formatting button and select Clear Rules. Then, select whether you want to clear the rules from only the selected cells or from the entire worksheet.

Also, if you have created several rules, you might forget what rules you have applied to what cells. Since you can apply many rules to the same set of cells, it can become quite confusing especially if someone else created the spreadsheet. To see all the rules, click on the Conditional Formatting button and then click on Manage Rules.

When you have more than one rule applied to the same range of cells, the rules are evaluated in order from higher precedence to lower precedence. By default, the newest rule added will have the higher precedence. You can change that by clicking on the rule and then using the up and down arrow buttons to change the order. Also, you can click the dropdown at the very top and see the rules for only the current selection or for each sheet in the workbook.

There is also a checkbox called Stop If True, which I won’t go into detail here because it’s quite complicated. However, you can read this post from Microsoft that explain it in great detail.

New Conditional Formatting Options Excel 2010

Just about everything is the same in Excel 2010 when it comes to Conditional Formatting that was included in Excel 2007. However, there is one new feature that really makes it much more powerful.

Earlier I had mentioned that the Highlight Cells Rules section lets you compare one set of data to another set of data on the same spreadsheet. In 2010, you can now reference another worksheet in the same workbook. If you try to do this in Excel 2007, it will let you select the data from another worksheet, but will give you an error message when you try to click OK at the end.

In Excel 2010, you can now do this, but it’s a bit tricky so I’m going to explain it step by step. Let’s say I have two worksheets and on each sheet I have data from B2 to B12 for something like profit. If I want to see which values in B2 to B12 from sheet 1 are greater than the B2 to B12 values of sheet 2, I would first select the B2 to B12 values in sheet 1 and then click on Great Than under Highlight Cells Rules.

Now click on the cell reference button that I have shown above. The box will change and the cursor icon will become a white cross. Now go ahead and click on sheet 2 and select ONLY cell B2. Do NOT select the entire range of B2 to B12.

You’ll see that the box now has a value of =Sheet2!$B$2. We’re going to need to change this to =Sheet2!$B2. Basically, just get rid of the $ that comes before the 2. This will keep the column fixed, but allow the row number to change automatically. For whatever reason, it won’t just let you select the entire range.

Click the cell reference button again and then click OK. Now the values in sheet 1 that are greater than sheet 2 will be formatted according to the formatting options you chose.

Hopefully, that all makes sense! When looking at Excel 2013, there doesn’t seem to be any new features when it comes to conditional formatting. As a last tip, if you feel that the default rules don’t match what you are trying to accomplish, you can click the New Rule option and start from scratch. What’s great about creating a new rule is that you can use a formula to determine which cells to format, which is very powerful.

Even though conditional formatting looks relatively easy and simple on the surface, it can become quite complex depending on your data and your needs. If you have any questions, feel free to post a comment. Enjoy!

Related posts

• پیوند سلول ها بین برگه ها و کتاب های کاری در اکسل

• از پنجره تماشای اکسل برای نظارت بر سلول های مهم در یک کتاب کار استفاده کنید

• نحوه مخفی کردن برگه ها، سلول ها، ستون ها و فرمول ها در اکسل

• از Format Painter در Word برای کپی و چسباندن قالب بندی استفاده کنید

• اضافه کردن Regression Trendline خطی به Excel Scatter Plot

• نحوه باز کردن چندین نمونه اکسل

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

• نحوه پیدا کردن و محاسبه محدوده در اکسل

• نمودار داده های اکسل شما

• نحوه اضافه کردن و چاپ تصاویر پس زمینه اکسل

• چگونه به ایجاد Gantt Charts در Microsoft Excel

• تنظیم خودکار عرض ستون و ارتفاع ردیف در اکسل

• نحوه حذف، شکستن یا شکستن رمز عبور فراموش شده اکسل XLS

• نحوه جدا کردن نام و نام خانوادگی در اکسل

• از عملکرد جدید Excel Mobile "Insert Data From Picture" استفاده کنید

• چگونه به ایجاد یک Drop Down List در Excel

• نحوه جابه‌جایی بین کاربرگ‌ها در اکسل

• نحوه ردیابی تغییرات در اکسل

• راهنمای همه پسوندهای فایل اکسل و معنای آنها

• نحوه کم کردن تاریخ در اکسل