Как применять условное форматирование в google таблицах

Некоторые примеры использования настраиваемых списков

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

• Если у вас есть список, который вам нужно ввести вручную (или скопировать и вставить из другого источника), вы можете создать собственный список и использовать его вместо этого. Например, это могут быть названия отделов в вашей организации, названия продуктов или регионов / стран.
• Если вы учитель, вы можете составить список имен своих учеников. Таким образом, когда вы будете ставить им оценку в следующий раз, вам не нужно будет беспокоиться о том, чтобы ввести имена учеников вручную или скопировать их с другого листа. Это также снижает вероятность ошибок.
• Когда вам нужно отсортировать данные на основе критериев, которые не встроены в Excel. Как описано в предыдущем разделе, вы можете использовать свои собственные критерии сортировки, создав собственный список в Excel.

Это все, что вам нужно знать о создании настраиваемых списков в Excel .

Надеюсь, вы нашли это полезным.

Как создать собственный список в Excel (простое пошаговое руководство)

Как создавать собственные списки в Excel

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

Например, если вы введете «Пн» в одну ячейку «Вт» в соседней ячейке, вы можете использовать маркер заполнения для заполнения остальных дней. Если вы расширите выделение, продолжайте перетаскивать, и он повторится и снова даст вам название дня.

Ниже приведены настраиваемые списки, которые уже встроены в Excel. Как видите, это в основном названия дней и месяцев, поскольку они фиксированы и не изменятся.

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

Ниже приведены шаги по созданию собственного настраиваемого списка в Excel:

• Перейдите на вкладку Файл.
• Щелкните Параметры. Откроется диалоговое окно «Параметры Excel».
• Нажмите на опцию Advanced на левой панели.
• В разделе «Общие» нажмите кнопку «Изменить настраиваемые списки» (возможно, вам придется прокрутить вниз, чтобы перейти к этому параметру).
• В диалоговом окне «Настраиваемые списки» импортируйте список, выбрав диапазон ячеек, содержащих список. Кроме того, вы также можете ввести имя вручную в поле List Entries (через запятую или каждое имя в новой строке)
• Нажмите на Добавить

Как только вы нажмете «Добавить», вы заметите, что ваш список теперь становится частью настраиваемых списков.

Если у вас есть большой список, который вы хотите добавить в Excel, вы также можете использовать параметр «Импорт» в диалоговом окне. (см. как добавить месяцы к дате в Excel)

Теперь, когда у вас есть список в бэкэнде Excel, вы можете использовать его так же, как вы используете числа или названия месяцев с автозаполнением (как показано ниже).

Хотя здорово иметь возможность быстро получить эти пользовательские имена lits в Excel простым перетаскиванием, есть кое-что еще более интересное, что вы можете сделать с настраиваемыми списками (об этом следующий раздел).

А если забыл, где какие правила создавал?

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

Один их простых способов обнаружить такие нестандартные места таблицы – использовать меню Главная – Найти и выделить – …… в последних версиях Excel. Или же Главная – Редактирование – Найти и выделить – … в более ранних версиях.

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

Поэтому лучше всего просто выберите раздел «Управление правилами» — текущий лист. Этот процесс мы уже дважды описывали в предыдущих разделах, поэтому, думаю, проблем здесь не возникнет.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

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

=Отчет

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

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

А теперь о том, как Таблицы облегчают жизнь и работу.

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

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

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

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

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

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

Отформатируйте все ячейки на основе их значений

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

• Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
• Теперь кликните «Условное форматирование», а затем нажмите «Создать правило …».
• В новом окне выберите тип «Форматировать все ячейки на основании их значений».
• Выделите цветом те ячейки, значения которых на единицу больше или меньше среднего значения
• Значения, которые не входят в определенную область, могут быть легко найдены с помощью Excel. Эта функция отмечает цветом ячейки, которые не соответствуют значению.
• Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
  Снова перейдите в «Условное форматирование», а затем в «Создать правило …».
• Если вы теперь выберете «Стандарт», то зададите настройки, по которым ячейки со значениями выше или ниже среднего будут выделены цветом.

Условное форматирование строк или ячеек, если два столбца равны в Excel

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

Метод A Условное форматирование строк или ячеек, если два столбца равны

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

1. Выберите первый список данных, который вы хотите сравнить со вторым, например, A2: A7, затем щелкните Главная > Условное форматирование > Новое правило.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 = $ B2 в текстовое поле под Формат значений, где эта формула истинна.

Чаевые: A2 — первая ячейка в первом списке, а B2 — первая ячейка во втором списке, с которым вы хотите сравнить.

3. Щелкните Форматировать в Новое правило форматирования диалоговом окне, затем выберите цвет фона в Заполнять вкладка во всплывающем окне Формат ячеек Диалог.

4. Нажмите OK > OK чтобы закрыть диалоги. Теперь ячейки закрашены, если два столбца равны.

Внимание: если вы хотите заполнить строки, когда два столбца равны, выберите два списка в качестве диапазона, а затем примените Новое правило форматирования Диалог

Метод B Выделите и выберите ячейки, если два столбца равны

Если вы хотите выделить ячейки, если два столбца равны, и в то же время, чтобы выбрать ячейки, вы можете использовать Сравнить ячейки особенность Kutools for Excel.

Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите первый список, затем выберите второй список, удерживая Ctrl ключ, затем нажмите Kutools > Сравнить ячейки для Сравнить ячейки Диалог.

2. в Сравнить ячейки диалог, проверьте Те же клетки вариант, проверьте Заполните цвет фона or Цвет заливки шрифта установите флажки и выберите нужный цвет.

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

Другие операции (статьи), связанные с форматированием Conditioanl

Подсчет / суммирование ячеек по цветам с условным форматированием в ExcelТеперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.

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

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

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

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

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​ двоеточие прописываем диапазон​ а именно с​ В поле «Имя»​При работе в программе​ нужно два столбца​ просто вписать его​

​ листе. А вот​ которые должны попасть​В Excel 2007/2010 откройте​Данные — Проверка (Data​ столбец с данными​ получилась такая.​ данные столбца А​ устанавливать выпадающий список.​Название этой таблицы можно​. Ещё один способ​ делали ранее с​ ячеек таблицы, данные​ использованием ActiveX. По​ вписываем любое удобное​ Microsoft Excel в​

​ (свойство​ руками с клавиатуры​ дальше начинаются серьезные​ в список​ вкладку​ — Validation)​ отделяет хотя бы​

​=ДВССЫЛ(«Товар»)​ (без названия столбца).​ Мы выделили диапазон​ поменять. Нажимаем на​ сделать раскрывающиеся списки,​ обычными выпадающими списками.​ которой будут формировать​ умолчанию, функции инструментов​ наименование, по которому​ таблицах с повторяющимися​ColumnCount​ (например, Лист2!A1:A5)​

​ отличия от предыдущего​Связь с ячейкой​Разработчик (Developer)​. Из выпадающего списка​ одна пустая строка​Нажимаем «ОК». Получилось так.​ У нас -​ Е1:Е4 (окрашен в​

​ таблицу, заходим на​ смотрите в статье​В первой ячейке создаём​ пункты выпадающего списка.​ разработчика отсутствуют, поэтому​ будем узнавать данный​ данными, очень удобно​=2). Тогда можно получить​LinkedCell​ способа.​- укажите ячейку​. В более ранних​

​Тип данных (Allow)​ или вам нужен​Можно выпадающие списки сделать​ это диапазон А2:А4.​ желтый цвет). Вставляем​ закладку «Конструктор» и​ «Связанные выпадающие списки​ список точно таким​Далее, кликаем по ячейке,​

Создание выпадающего списка с помощью инструментов разработчика

​ нам, прежде всего,​ список. Но, это​ использовать выпадающий список.​ весьма привлекательные результаты,​- связанная ячейка,​Во-первых, созданный выпадающий ActiveX​ куда нужно выводить​ версиях — панель​выберите вариант​ товар, который еще​ на другом листе,​Копируем формулу выделенного​ выпадающий список как​ в разделе «Свойства»​

​ в Excel».​ же образом, как​ и в контекстном​ нужно будет их​ наименование должно начинаться​ С его помощью​

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

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

​ в двух принципиально​ пользователем элемента.​Формы (Forms)​и введите в​

​ вводился выше:​ перед адресом ячейки​ ячейки клавишей «Esc».​ данных», выбираем «Список».​ таблицы. Мы написали​ сейчас рассмотрим удобен​ через проверку данных.​

​ по пунктам «Объект​ переходим во вкладку​ Можно также вписать​ нужные параметры из​ усилия:​

​ элемент​ разных состояниях -​

​Количество строк списка​через меню​ строчку​Выделите ячейки с данными,​ напишем название листа​ Выделяем ячейки, в​ В диалоговом окне​

Связанные списки

​ имя таблицы –​ тем, что при​Во второй ячейке тоже​ ComboBox» и «Edit».​ «Файл» программы Excel,​ примечание, но это​ сформированного меню. Давайте​Способ 1.​ListRows​ режиме отладки, когда​- сколько строк​Вид — Панели инструментов​Источник (Source)​ которые должны попасть​ и поставим восклицательный​ которых будем создавать​ «Проверка вводимых значений»​

​ «Товар».​ добавлении строк или​ запускаем окно проверки​Выпадающий список в Microsoft​ а затем кликаем​ не обязательно. Жмем​

​ выясним, как сделать​Примитивный​- количество отображаемых​ можно настраивать его​ показывать в выпадающем​

​ — Формы (View​знак равенства и​ в выпадающий список​ знак.​ выпадающие списки второго​

​ в строку «Источник»​В этой таблице уже​ столбцов в таблицу,​ данных, но в​ Excel готов.​ по надписи «Параметры».​ на кнопку «OK».​

​ раскрывающийся список различными​

​Способ 2.​ строк​ параметры и свойства,​ списке. По умолчанию​ — Toolbars -​ имя диапазона (т.е.​ (например, наименованиями товаров).​Как еще можно​

​ уровня. У нас​

​ вставляем скопированную формулу​ все столбцы имеют​ все диапазоны в​ графе «Источник» вводим​Чтобы сделать и другие​В открывшемся окне переходим​Переходим во вкладку «Данные»​ способами.​Стандартный​Font​ двигать его по​ — 8, но​ Forms)​

​=Товары​

lumpics.ru>

Условное форматирование ячеек в Excel на основании значений другого диапазона

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

Обратите внимание, что в окне указан абсолютный адрес ячейки ($E$3). В данном случае это допустимо, так как сравнение указывается для фиксированной ячейки

После нажатия «ОК» мы увидим подсветку в тех ячейках, где значение меньше указанного.

Если ввести в ячейку E3 новое значение, то и оформление в таблице Excel изменится.

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

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

В таком случае стиль будет вычисляться на основании сравнения двух парных ячеек.

Вместо чисел можно использовать любые значения: строка, дата и т.д. Например, выделим все автомобили в пункте проката, которые находятся в резерве. Значение «р» — означает, что авто свободно, а «с», что сдано.

Установим зеленый цвет для свободных автомобилей. Нам понадобится условие «Равно».

В качестве значения укажем «р».

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

Чтобы проверить правило, необходимо воспользоваться пунктом «Управление правилами» в меню условного форматирования.

При его выборе откроется окно с перечнем правил.

Вы можете скорректировать правило условного форматирования Excel или установить новое.

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

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

Множество других секретов Excel вы найдете в онлайн курсе.

Форматирование части строки в Excel

Пример условного форматирования части строки на листе Excel в зависимости от содержимого одной или двух ячеек в этой строке.

Условие примера

1. Заливка строки желтым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да».
2. Заливка строки серым фоном, если в четвертой ячейке (столбец «D») этой строки содержится значение «Нет».
3. Заливка строки красным фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да», а в четвертой ячейке (столбец «D») – значение «Нет».
4. Заливка применяется к 5 первым ячейкам любой строки.

Решение примера

1. Выделяем первые 5 столбцов, чтобы задать диапазон, к которому будут применяться создаваемые правила условного форматирования:

2. Создаем первое правило: условие – , цвет заливки – желтый:

3. Создаем второе правило: условие – , цвет заливки – серый:

4. Создаем третье правило: условие – , цвет заливки – красный:

5. Проверяем созданные правила в «Диспетчере правил условного форматирования». Видим, что диапазоны, к которым применяются правила, отобразились верно:

6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строки:

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

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

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

Создание пользовательских форматов для категорий значений

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

1. Положительные числа
2. Отрицательные числа
3. Нулевые значения
4. Текстовый формат

Для этого мы можем в поле Тип указать следующую конструкцию:

Формат положительных значений ; отрицательных ; нулевых ; текстовых 

Соответственно для каждой категории можно применять формат уже описанного нами вида:

«любой текст»КодФормата»любой текст»

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

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

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

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10. Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

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

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник.xlsx), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных, то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

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

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

Использование функций Excel

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

Если вы желаете объявить любую функцию в ячейке (результат обязательно выводится в поле), начните написание со знака «​=», после чего впишите первый символ, обозначающий название формулы

На экране появится список подходящих вариантов, а нажатие клавиши TAB выбирает одну из них и автоматически дописывает оставшиеся символы.

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

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

Это позволит не запутаться в правильном написании значений.

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

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

Формат даты в Excel

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

В выпадающем меню можно увидеть все возможные варианты.

Примечательно то, что при открытии документа значения даты сравниваются с текущим, и если какое-либо правило изменилось (например, перестало быть «Вчера»), то стиль для него изменяется. Это очень удобно при повторной работе с документом.

Например, выбрав условие «В текущем месяце» для ноября мы получим выделение красным только тех строк, которые попадают под это условие.