Если вы привыкли использовать более старые версии Excel , параметры условного форматирования в Excel 2007 , 2010 и 2013 удивят вас. Так зачем вам использовать условное форматирование? Итак, вот несколько причин, по которым мне нравится использовать эту функцию Excel :

1. Сделать ваши данные более привлекательными.

2. Чтобы упростить понимание ваших электронных таблиц с первого взгляда.

3. Определить определенные типы чисел для помощи в решении задач^{(problem solving)} .

4. Чтобы помочь вам сделать выводы из ваших данных.

5. Визуально показать пользователю, что такое «хорошо» или «плохо», используя зеленый и красный цвета.

Теперь вы можете использовать условное форматирование для форматирования каждой ячейки в диапазоне на основе ваших собственных критериев (и есть много вариантов форматирования на выбор). Например, если у вас есть отчет о прибылях^{(profit sheet)} и убытках, и вы хотите пометить всю прибыль, превышающую 200 долларов США, зеленым цветом, всю прибыль менее 200 долларов США — желтым, а все убытки — красным, вы можете использовать условное форматирование, чтобы быстро выполнить всю работу за вас. .

Условное форматирование в Excel.

Условное форматирование позволяет быстро и легко форматировать значительные объемы данных, сохраняя при этом возможность различать разные типы данных. Вы можете создать правила для параметров форматирования, которые позволят Microsoft Excel автоматически форматировать для вас. Вам действительно нужно выполнить всего три простых шага.

Шаг 1:^{(Step 1:)} Выберите ячейки, которые вы хотите отформатировать.

Шаг 2.^{(Step 2:)} Нажмите кнопку « Условное форматирование^{(Conditional Formatting)} » в главном меню^{(Home menu)} , раздел « Стили»^{(Styles section)} .

Шаг 3:^{(Step 3:)} Выберите правила. Вверху есть правила выделения ячеек^{(Highlight Cells Rules)} и правила Top/Bottom Rules  , которые позволяют сравнивать значения. Для этого примера мы наложили три правила. Во-первых, любое значение, превышающее 200 долларов, было зеленым.

Стоит отметить, что только раздел «Правила выделения ячеек»^{(Highlight Cells Rules section)} также можно использовать для сравнения набора данных с другим набором данных. Все остальное будет просто использовать один набор данных, который вы выделили, и сравнивать значения друг с другом. Например, при использовании правила «больше чем^{(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 button and select)} Очистить правила»^{(Clear Rules)} . Затем выберите, хотите ли вы очистить правила только от выбранных ячеек или от всего листа.

Кроме того, если вы создали несколько правил, вы можете забыть, какие правила к каким ячейкам применялись. Поскольку вы можете применять множество правил к одному и тому же набору ячеек, это может стать довольно запутанным, особенно если электронную таблицу создал кто-то другой. Чтобы просмотреть все правила, нажмите кнопку « Условное форматирование^{(Conditional Formatting button)} », а затем нажмите « Управление правилами».^{(Manage Rules.)}

Если к одному и тому же диапазону ячеек применено несколько правил, правила оцениваются в порядке от более высокого приоритета к более низкому приоритету. По умолчанию самое новое добавленное правило будет иметь более высокий приоритет. Вы можете изменить это, щелкнув правило, а затем используя кнопки со стрелками вверх и вниз, чтобы изменить порядок. Кроме того, вы можете щелкнуть раскрывающийся список в самом верху и просмотреть правила только для текущего выбора или для каждого листа в книге.

Существует также флажок Stop If True , который я не буду здесь подробно описывать, потому что он довольно сложный. Тем не менее, вы можете прочитать этот пост от Microsoft , который объясняет это очень подробно.

Новые параметры условного форматирования Excel 2010^{(New Conditional Formatting Options Excel 2010)}

Почти все то же самое в Excel 2010 , что касается условного форматирования^{(Conditional Formatting)} , которое было включено в Excel 2007 . Тем не менее, есть одна новая функция, которая действительно делает его намного более мощным.

Ранее я упоминал, что раздел « Правила выделения ячеек^{(Highlight Cells Rules)} » позволяет сравнивать один набор данных с другим набором данных в той же электронной таблице. В 2010 году теперь вы можете ссылаться на другой лист в той же книге. Если вы попытаетесь сделать это в Excel 2007 , он позволит вам выбрать данные из другого рабочего листа, но выдаст вам сообщение об ошибке,^{(error message)} когда вы попытаетесь щелкнуть OK в конце.

В 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. НЕ выбирайте^(NOT)  весь диапазон от B2 до B12 .

Вы увидите, что поле теперь имеет значение =Sheet2!$B$2 . Нам нужно изменить это на =Sheet2!$B2 . По сути, просто избавьтесь от $  , который стоит перед 2. Это сохранит фиксированный столбец, но позволит автоматически изменять номер строки . ^{(row number)}По какой-то причине он не позволит вам просто выбрать весь диапазон.

Нажмите кнопку ^(Click)ссылки^{(cell reference button)} на ячейку еще раз, а затем нажмите кнопку ОК. Теперь значения на листе 1^{(sheet 1)} , превышающие значения на листе 2^{(sheet 2)} , будут отформатированы в соответствии с выбранными вами параметрами форматирования.

Надеюсь^(Hopefully) , это все имеет смысл! При взгляде на Excel 2013 не видно никаких новых функций, когда дело доходит до условного форматирования. В качестве последнего совета: если вы чувствуете, что правила по умолчанию не соответствуют тому, чего вы пытаетесь достичь, вы можете щелкнуть параметр «  Новое правило ^{(New Rule)} » и начать^{(option and start)} с нуля. Что хорошего в создании нового правила, так это то, что вы можете использовать формулу, чтобы определить, какие ячейки форматировать, что очень удобно.

Несмотря на то, что условное форматирование выглядит относительно простым и простым на первый взгляд, оно может стать довольно сложным в зависимости от ваших данных и ваших потребностей. Если у вас есть какие-либо вопросы, не стесняйтесь оставлять комментарии. Наслаждаться!

Format Cells using Conditional Formatting in Excel

If you are used to using older versions of Excel, the conditional formatting options in Excel 2007, 2010, and 2013 will amaze you. So why would you want to bother usіng conditional formatting? Well, here arе a couple of reasons why I love using this feature 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

• Связать ячейки между листами и книгами в Excel

• Используйте окно наблюдения Excel для мониторинга важных ячеек в книге

• Как скрыть листы, ячейки, столбцы и формулы в Excel

• Используйте Format Painter в Word для копирования и вставки форматирования

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как записать Macro в Excel

• Используйте новую функцию Excel Mobile «Вставить данные из изображения»

• Используйте функции суммирования для суммирования данных в Excel

• Как разделить имя и фамилию в Excel

• Как отслеживать изменения в Excel

• График ваших данных Excel

• Как рассчитать Variance в Excel

• Как удалить, взломать или взломать забытый пароль Excel XLS

• Как открыть несколько экземпляров Excel

• Как сделать контрольный список в Excel

• Используйте Excel как инструмент для копирования данных из Интернета

• 13 OneNote Tips & Tricks Для лучшего организации ваших заметок лучше

• Используйте Excel, чтобы определить эффективную процентную ставку по номинальной процентной ставке

• Как понять анализ «что, если» в Microsoft Excel

• Как Create Labels в Word от Excel Spreadsheet