Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (4)

Сборник формул для условного форматирования

В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:

• Excel 2003 : Формат(Format) -- формула ;
• Excel 2007-2010 : вкладка Главная(Home) -Условное форматирование(Conditional formatting) -Создать правило(New rule) -Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)

Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать

Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона - A4 .

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
= $A1 =МАКС($A$1:$A$20)
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: =B1

ЧИСЛОВЫЕ ЗНАЧЕНИЯ
1. Выделение ячеек с числами:
   =ЕЧИСЛО(A1)
2. Выделение ячеек с числами, но не учитывая нули:
   =И(ЕЧИСЛО(A1); A1 0)
3. Выделение строк со значением больше 0:
   = A1 >0
4. Выделение строк со значением в диапазоне от 3 до 10:
   =И(A1 >=3; A1
5. Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
   = A1 =МАКС($A$1:$A$20)
6. Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
   =И(ЕЧИСЛО(A1); A1 =МИН($A$1:$A$20))
7. Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
   = A1 =НАИБОЛЬШИЙ($A$1:$A$20 ;2)

ТЕКСТОВЫЕ ЗНАЧЕНИЯ

8. Выделение ячеек с любым текстом:
   =ЕТЕКСТ(A1)
9. Выделение ячеек с текстом Итог :
   = A1 ="Итог"
10. Выделение ячеек, содержащих текст Итог :
    =СЧЁТЕСЛИ(A1 ;"*итог*")
    =НЕ(ЕОШ(ПОИСК("итог";A1 )))
11. Выделение ячеек, не содержащих текст Итог :
    =СЧЁТЕСЛИ(A1 ;"*итог*")=0
    =ЕОШ(ПОИСК("итог";A1 ))
12. Выделение ячеек, текст которых начинается со слова Итог :
    =ЛЕВСИМВ(A1 ;4)="Итог"
13. Выделение ячеек, текст которых заканчивается на слово Итог :
    =ПРАВСИМВ(A1 ;4)="Итог"

ДАТА / ВРЕМЯ

14. Выделение текущей даты:
    =A1 =СЕГОДНЯ()
15. Выделение ячейки с датой, больше текущей:
    =A1 >СЕГОДНЯ()
16. Выделение ячейки с датой, которая наступит через неделю:
    =A1 =СЕГОДНЯ()+7
17. Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ(A1 )=МЕСЯЦ(СЕГОДНЯ())
18. Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ(A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД(A1)=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ(A1 ;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ")
19. Выделение ячеек с выходными днями:
    =ДЕНЬНЕД(A1 ;2)>5
20. Выделение ячеек с будними днями:
    =ДЕНЬНЕД(A1 ;2)
21. Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И($A1 >ДАТА(2015;9;1); $A1

    ДРУГИЕ

22. Выделение различий в ячейках по условию:
    =A1 <>$B1
23. Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    =A1 >B1
24. Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
25. Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ($F$1:$H$5000 ;A1 )
26. Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ($F$1:$H$5000 ;A1 )=0
27. Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ($A$1:$A1 ;A1 )=2
28. Выделение ячеек, содержащих ошибки (#ЗНАЧ! ; #Н/Д ; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА(A )
29. Выделение непустых ячеек в столбце A :
    =$A1 ""

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Элен Брэдли объясняет некоторые различия в форматировании сводных таблиц в Excel 2010.

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

Microsoft внесла изменения в порядок применения условного форматирования к сводным таблицам. Теперь у Вас появилось больше возможностей и больше гибкости при использовании условных форматов. В этой статье я покажу Вам, как применять условное форматирование к сводным таблицам и как оседлать всю мощь новых возможностей.

Как работает условное форматирование

В Excel 2007 и 2010, когда условное форматирование применяется к сводной таблице, оно применяется в большей степени к структуре сводной таблицы, а не к самим ячейкам. Поэтому, когда Вы работаете со сводной таблицей, допустим, передвигаете поля с места на место или отображаете данные различными способами, форматирование обновляется вслед за Вашими действиями. Все это, в сочетании с новыми форматами, делает условное форматирование очень удобным инструментом для использования вместе со сводными таблицами.

Как применить условное форматирование к сводной таблице

Мы рассмотрим условное форматирование на примере сводной таблицы, в которой представлен график приема 4-х ветеринаров за год. В таблице отображается число клиентов с разбивкой по кварталам и по месту приема (ферма или хирургия).

Чтобы сделать данные более наглядными, я выбираю значения Farm (Ферма) и Surgery (Хирургия) первого ветеринара с фамилией Browning , т.е. ячейки от B6 до E7 . Выделив этот диапазон, я захожу на вкладку Home (Главная), нажимаю Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выбираю, какой использовать цвет. Эти действия форматируют выбранный диапазон таким образом, что в каждой ячейке появляется гистограмма, которая показывает относительное количество клиентов в каждом квартале и для каждого места приема.

На следующем рисунке мы применяем формат Data Bars (Гистограмма) к первому диапазону данных:

Чтобы применить это же форматирование к аналогичным данным для остальных ветеринаров, нужно выделить ранее отформатированный диапазон, кликнуть иконку Formatting Options (Настройки форматирования), которая появляется в правом нижнем углу диапазона, и выбрать третий из предложенных вариантов (см. рисунок ниже).

Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).

На этом рисунке показано, как применить одинаковое условное форматирование ко всем данным одного типа в нашей сводной таблице:

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

Гистограммы изменяются, когда меняются данные в сводной таблице, длина гистограммы зависит от данных во всех видимых ячейках:

Мы можем пойти еще дальше и сравнить итоговые значения, используя другое условие для форматирования. В данном случае я хочу сравнить итоги по каждому ветеринару с итогами остальных ветеринаров, поэтому выбираю ячейки от B5 до E5 – общее количество клиентов (по кварталам) ветеринара Browning . Создав в этом диапазоне гистограммы другого цвета, я могу сравнивать итоговые данные о клиентах, которых осматривал ветеринар Browning за эти четыре квартала.

Как и в прошлый раз появляется иконка Formatting Options (Параметры форматирования), при помощи которой мы сможем применить это же условное форматирование к итоговым данным по клиентам каждого ветеринара.

Здесь Вы видите гистограммы другого цвета в ячейках с итоговыми значениями, которые можно наглядно сравнить друг с другом:

Другие параметры

В некоторых случаях имеет смысл разделить условное форматирование, как было сделано в прошлом примере, применяя его только к ячейкам, содержащим данные одного уровня, чтобы разделить итоги и общие итоги (Grand Total). Но не всегда имеет смысл это делать.

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

Для этого выделим диапазон B6:E6 , перейдем Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выберем зелёный цвет для гистограмм. Далее в параметрах форматирования выберем второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает, как итоговые, так и общие итоговые (Grand Total) значения, которые, как и все данные в сводной таблице, являются средними значениями. Поэтому не будет нарушением сравнить их одинаковым образом.

Здесь все ячейки содержат средние значения, поэтому допустимо применить одно правило условного форматирования:

Перемещаем данные

Вернёмся к нашей исходной сводной таблице и начнем перемещать данные. Делая это, Вы заметите, что форматирование сохраняется на нужных местах. Мы передвинули поле Office (Место приема) в область Report Filter (Фильтры) и перенесли поле Quarter (Квартал) в область Row Labels (Строки), и при этом все фиолетовые гистограммы остались на своих местах.

Даже если структура таблицы изменена и поля передвинуты, условное форматирование сохраняет свое положение:

Правила форматирования с ограничениями

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

Мы создадим отдельное правило условного форматирования для квартальных итогов каждого ветеринара, то есть нам нужно выделить и применить форматирование к ячейкам D7:D10 (потом D12:D15, потом D17:D19 и так далее). Затем используем правило Conditional Formatting > Top 10 Items (Условное форматирование > Первые 10 элементов), устанавливаем условие для 1 ячейки , формат оставляем предложенный по умолчанию. Вы можете либо скопировать это правило в соседний диапазон, либо создать для каждого индивидуально.

Чтобы скопировать форматирование, выделите одну или несколько ячеек с нужным форматом и нажмите Copy (Копировать). Затем выделите диапазон, в который нужно скопировать форматирование, и на вкладке Home (Главная) выберите Paste Special > Formats (Специальная вставка > Форматы).

В некоторых случаях может потребоваться сравнить данные внутри небольшой области, а не применять правило условного форматирования на всю группу несмежных диапазонов:

Условное форматирование в Excel в сочетании с мощью сводных таблиц позволяет очень тонко настроить форматирование и точно определить, какие данные нужно сравнивать. Вы можете сравнить похожее с похожим, как по всей сводной таблице, так и в рамках одного поля, которое Вас интересует. Знание возможных вариантов и умение пользоваться ими поможет Вам нагляднее сравнить значения и получить желаемый результат.

Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

• Гистограммы;
• Цифровые шкалы;
• Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

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

Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.

При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.

Правила выделения ячеек

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

Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:

• Больше;
• Меньше;
• Равно;
• Между;
• Дата;
• Повторяющиеся значения.

Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».

Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.

В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.

При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.

После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».

Как видим, ячейки выделены, согласно установленному правилу.

По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.

Правило выделения «Текст содержит», главным образом, применяется к ячейкам текстового формата. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться, установленным вами способом.

Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.

Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.

Правила отбора первых и последних значений

Кроме того, в меню условного форматирования имеется ещё один интересный пункт – «Правила отбора первых и последних значений». Тут можно установить выделение только самых больших или самых маленьких значений в диапазоне ячеек. При этом, можно использовать отбор, как по порядковым величинам, так и по процентным. Существуют следующие критерии отбора, которые указаны в соответствующих пунктах меню:

• Первые 10 элементов;
• Первые 10%;
• Последние 10 элементов;
• Последние 10%;
• Выше среднего;
• Ниже среднего.

Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.

Создание правил

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.

Открывается окно, где нужно выбрать один из шести типов правил:

1. Форматировать все ячейки на основании их значений;
2. Форматировать только ячейки, которые содержат;
3. Форматировать только первые и последние значения;
4. Форматировать только значения, которые находятся выше или ниже среднего;
5. Форматировать только уникальные или повторяющиеся значения;
6. Использовать формулу для определения форматируемых ячеек.

Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.

Управление правилами

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

Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.

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

Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.

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

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.

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

Урок 8. Форматирование ячейки

Можно изменить формат ячейки, запомнить его и применить к другой таблице. Сначала рассмотрим возможности по форматированию ячейки. Для этого выделим несколько ячеек, затем щелкнем по ним правой кнопкой и вызовем режим Формат ячеек .

Как видно окно содержит несколько вкладок. Вкладка Число позволяет указать формат данных, находящихся в ячейке. Обычно он редко меняется. Как правило, при вводе данных в ячейку, программа сама определяет формат. В поле Числовые форматы его можно посмотреть.

Вкладка Выравнивание позволяет установить: где будет находиться текст в ячейке. Предположим мы набрали текст в ячейку.

Как видно из рисунка, текст примыкает к левой границе ячейки. Для того чтобы поставить его по центру, нужно установить параметрпо центру в поле по горизонтали .

Интересен режим Ориентация , он позволяет напечатать текст не по горизонтали, а в другом направлении. Предположим, нужно изменить направление к горизонтальной оси на 45 градусов. Для этого установим стрелку в поле Ориентация , как показано на рис. ниже.

Как видно из рисунка направление текста изменилось только в той ячейке, к которой применен режим. Кроме того, размер строки изменился и стал больше. В правой ячейке находится текст, и он находится у нижней границе. Чтобы установить его в другом месте, выделим вторую ячейку и воспользуемся режимом Формат ячеек , вкладкой Выравнивание . Там в поле по вертикали установим значение –по верхнему краю .

И текст передвинется выше.

Интересен режим автоподбор ширины, который позволяет программе автоматически увеличивать размер ячейке (по горизонтали и вертикали), если значение выходит за рамки имеющихся. Например, увеличим размер шрифта до 24 в созданной на прошлых уроках таблицы при работающем параметре автоподбора.

Видно, что размер по вертикали (строк) изменился в сторону увеличения. Так строчки под таблицей имеют меньший размер, чем там, где имеется таблица. Отметим, что вид шрифта в заголовке разный, так как он увеличен по размеру (ширине) ячейки.

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

Установим в показанном выше примере в заголовке шрифт одного стиля (Arial ), установим его полужирным, сделаем подчеркивание и выберем синий цвет. Для этого выделим ячейки заголовка и установим параметры, как это показано ниже.

В поле Образец можно посмотреть, как будет выглядеть текст.

Теперь снова выделим заголовок таблицы, уберем галочку в параметре автоподбор ширины, получим следующую картинку.

Как видно текст налезает на текст других ячеек. Воспользуемся режимом Формат на вкладке Главная .

В появившемся панно выберем режим Автоподбор ширины текста . Получим:

На вкладке Граница можно установить границы вокруг ячейки. Допустим, у нас имеется несколько ячеек, как показано на рис. ниже.

Выделим их и воспользуемся вкладкой Граница .

Мы выбрали цвет – оранжевый, тип линии – двойной и нажали на кнопкувнешние .

Теперь снова выделим таблицу, и снова воспользуемся вкладкой Граница .

Мы выбрали другой цвет, тип линии и нажали на кнопку внутренние. Можно было не выходить из режима установки границ, установить цвет, тип линии, нажать на кнопкувнешние , затем изменить тип линии, цвет и нажать на кнопкувнутренние .

Вкладка Заливка позволяет установить заливку ячеек. Снова выделим предыдущие ячейки и установим цвет. Можно выбрать цвет и тогда ячейки будут закрашены однородным цветом, но мы выбрали Узор и к нему Цвет узора .

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

Здесь уже имеются стили, установленные в программе, но нам нужно создать свой стиль. Поэтому нажмем на надпись Создать стиль ячеек .

На экране появится окно, в котором имеются элементы, для которых создается свой стиль, назовем его Проба . Поставим галочки во всех переключателях и нажмем на кнопку ОК . Теперь новый стиль запомнится в программе и когда вызовем режим Стили ячеек , то он появится в списке Пользовательские .

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

Иногда требуется выделить цифры в зависимости от определенных условий. Так, если в таблице представлены сравнительные данные по категориям населения, злоупотребляющие определенными продуктами, то лиц, склонных к спиртным напиткам, лучше выделить наклонным шрифтом, вегетарианцев, поедающих пищу с грядок – шрифтом с подчеркиванием, а потребляющих бесконтрольно большое количество еды – жирным шрифтом. Кроме того, использование цветового формата может быть продиктовано некоторыми условиями. Например, если температура в квартире зимой не поднимается выше 0, то количество таких квартир лучше показывать синим цветом, при температуре 0 – 10 градусов – зеленым, при диапазоне 10 – 20 градусов – желтым, а свыше 30 градусов – красным цветом.

Вернемся к созданной ранее таблице. Выделим часть таблицы с численными значениями и воспользуемся режимом на вкладке Главная → Условное форматирование . На экране появится окно режима, вид которого показан на рисунке. В этом окне выберем режим Создать правило .

Появится окно, в котором установим значения для правил.

Поставим задачу иметь цвет фона ячеек в зависимости от их значения. Выберем верхнее правилоФорматировать все ячейки на основании их значений и нажмем на кнопку ОК .

Если изменим цвет на синий, то получим следующую таблицу.

Выберем режим в стиле формата – трехцветная шкала и нажмем на кнопку ОК .

В этих режимах можно изменить среднее значение, введя значение при помощи клавиатуры, но можно и указать ячейку, в которой находится это значение, нажав на кнопку -.

Можно установить значениегистограмма . Тогда указанным цветом будет заполнены ячейки в зависимости от их значения.

Можно установить значки около значений при помощи режима – наборы значков.

Можно выполнить условное форматирование не со всей таблицей, а с ее частью. Имеются и другие режимы. Например, Условное форматирование → Правила выделения ячеек → Между .

В окне все значения, которые находятся между 25 и 72 будут выделены светло-красной заливкой и темно-красным цветом. Эти значения можно изменить, введя их с клавиатуры.

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

Отметим, что можно использовать несколько правил для одной таблицы.

Представьте себе монитор, где выведены рабочие узлы атомной электростанции, который отображает стабильность протекания всех процессов. Но вдруг один узел выходит из строя и сигнализирует диспетчеру о сбое, загораясь ярким красным светом. Согласитесь, очень удобно? Похожим целям служит функция условного форматирования в Excel – обеспечение наилучшей наглядности информации.

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

Виды условного форматирования

Форматировать все ячейки на основании их значений

Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

Настройки стиля:

• Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
• Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
• Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
• Направление столбца – определяет способ направленности (слева направо либо наоборот);
• Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
  • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
  • Задать положение оси или одинаковую направленность для всех значений.

Цветовые шкалы

Как и гистограммы, шкалы в условном форматировании заливают цветом ячейку с числовым значением, но отличие заключается в том, что последние заливают ее полностью. Чем выше значение, тем более насыщенная заливка. Также можно использовать несколько цветов, где, например, меньшие числа залиты зеленым, средние желтым, а большие красным.

В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.

• Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
• Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
• 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

Этот вид условного форматирования, в отличие от цвета заливки, использует различные значки в виде фигур, направлений, индикаторов и оценок.

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

Форматировать только ячейки, которые содержат

Этот вид условного форматирования отличается от первого тем, что он создает правило, которое должно соблюдаться, чтобы формат был применен к ячейке.

Рассмотрим правила, которые имеются в этом пункте:

• Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
• Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
• Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
• Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
• Непустые. Противоположное предыдущему правилу.
• Ошибки. Истинно, когда значением ячейки является ошибка.
• Без ошибки. Противоположное предыдущему правилу.

Форматировать только первые и последние значения

Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

Формула в условном форматировании

Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».

Для примера рассмотрим список заказа товаров, который необходимо сравнить с остатком на складе. Всего участвуют 2 таблицы: сам заказ и таблица остатков.

На изображении показан вариант, где уже применено условное форматирование ячеек. Рассмотрим, как его создать.

Используем 2 условия со следующими формулами:

• Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
• Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ)0).

Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.

В функции, в качестве первого аргумента используется ссылка всего на одну ячейку. Вас это не должно смущать, так как приложение «понимает», что ее нужно сместить в соответствии с диапазоном правила. Главное, чтобы она была относительной, т.е. не закреплена символами доллара – $.

Остальные правила

Ничего не было сказано о еще двух видах правил, а именно:

• Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
• Форматирование уникальных или повторяющихся значений.

По ним остается добавить только то, что в первом можно использовать стандартные отклонения. В остальном, они говорят сами за себя.

Управление правилами

Помимо умения создавать правила, условным форматированием также нужно корректно управлять. Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку.

Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.

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

Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.

На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.

Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.

Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.

• < Назад
• Вперёд >

Если материалы сайт Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.