Как создать условие в excel: как сделать функцию – WindowsTips.Ru. Новости и советы

Содержание

Как сделать условие в ячейке excel?

Содержание

  • 1 Синтаксис функции ЕСЛИ с одним условием
  • 2 Функция ЕСЛИ в Excel с несколькими условиями
  • 3 Расширение функционала с помощью операторов «И» и «ИЛИ»
  • 4 Как сравнить данные в двух таблицах
  • 5 Функция ЕСЛИ
    • 5.1 Использование условия «И»
    • 5.2 Использование условия «ИЛИ»
  • 6 СУММЕСЛИ
  • 7 СУММЕСЛИМН
  • 8 СЧЕТЕСЛИ
  • 9 СЧЕТЕСЛИМН
  • 10 Функция ПОИСКПОЗ
  • 11 Использование условий в VBA
  • 12 Заключение
  • 13 Видеоинструкция
  • 14 Как использовать функцию
    • 14.1 Пример №1
    • 14.2 Пример №2
  • 15 Используем несколько условий
  • 16 Другие варианты использования функции
    • 16.1 Находим сумму столбцов или ячеек
    • 16.2 Количество повторений элементов на листе
    • 16.3 Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных
    • 16.4 Поиск нескольких вхождений элемента в списке
    • 16.5 Как сократить количество вложений ЕСЛИ

Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.

Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.

Функция ЕСЛИ в Excel с несколькими условиями

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

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

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

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

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

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

Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

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

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

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

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Microsoft Excel – довольно мощный редактор для работы с таблицами. При этом его возможности не ограничиваются только этим. Работать с текстом можно и в редакторе Word, но в нем нет динамики. Именно поэтому для анализа информации лучше всего использовать Эксель. В данной статье мы рассмотрим функцию ЕСЛИ с несколькими условиями. В Excel стандартных примеров нет, поэтому будем использовать что-то своё.

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

Функция ЕСЛИ

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

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Полное описание можно увидеть в окне «Вставка функции».

  1. Нажмите на иконку
  2. Выберите категорию «Полный алфавитный перечень».
  3. Найдите там пункт «ЕСЛИ».
  4. Сразу после этого вы увидите описание функции.

Далее появится окно, в котором требуется указать «Аргументы функции» (логическое выражение, значение если истина и значение если ложь).

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

Затем необходимо выполнить следующие действия.

  1. Перейдите на первую ячейку. Нажмите на иконку «Fx». Найдите там функцию «ЕСЛИ» (её можно отыскать в категории «Полный алфавитный указатель»). Затем кликните на кнопку «OK».
  1. В результате этого появится следующее окно.
  1. В поле логическое выражение введите следующую формулу.

D3=»Высшая»

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

  1. После подстановки вы увидите, что данное выражение ложно.
  1. Затем указываем значения дли «Истины» и «Лжи». В первом случае какое-то число, а во втором – ноль.
  1. После этого мы увидим, что логический смысл выражения будет ложным.
  1. Для сохранения нажимаем на кнопку «OK».
  1. В результате использования этой функции вы увидите следующее.

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

  1. Наведите курсор в правый нижний угол первой ячейки.
  2. Если вы сделали всё правильно, то он превратится в черный крестик.
  1. Теперь сделайте левый клик мыши и не отпуская палец опуститесь до конца таблицы.
  1. В результате этого вы увидите следующее.

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

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

Использование условия «И»

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

Для этого достаточно выполнить следующие действия.

  1. Кликните на первую ячейку в столбце «Премия».
  2. Затем нажмите на иконку «Fx».
  1. Сразу после этого появится окно с используемой функцией со всеми указанными аргументами. Таким образом редактировать намного проще – непосредственно в ячейке.
  1. В графе логическое выражение укажите следующую формулу. Для сохранения изменений нажмите на кнопку «OK».

И(D3=»Высшая»;E3=»Математика»)

  1. Скопируйте эту формулу во все остальные ячейки.
  1. Теперь премия будет только у одного человека, поскольку только у него высшая категория и он математик.

Использование условия «ИЛИ»

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

  1. Перейдите в первую ячейку.
  2. Кликните на иконку «Fx».
  1. Текущее логическое выражение нас не устраивает.
  1. Нужно будет поменять его на следующее.

ИЛИ(D3=»Первая»;D3=»Вторая»)

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

В результате этого мы увидим следующее.

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

СУММЕСЛИ

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

Затем выполним следующие шаги.

  1. Переходим на соседнюю клетку и вызываем функцию (повторяем описанные выше действия) СУММЕСЛИ. Активной должна быть именно та ячейка, в которой будет выводиться сумма.
  1. Сразу после этого вы увидите окно, в котором нужно указать аргументы функции:
    • диапазон;
    • критерий;
    • диапазон суммирования.
  1. В первое поле нужно вести ссылку на столбец с категорией. Это можно сделать вручную либо просто выделить эти ячейки. В процессе выделения окно свернется, а нужные клетки обозначатся пунктирной рамкой.
  1. Как только вы отпустите палец, всё вернется в прежний вид, а нужный диапазон подставится автоматически.
  1. В графе «Критерий» нужно указать слово «Первая», так как мы считаем зарплату именно этих преподавателей.
  1. Затем нам необходимо выделить графу зарплата. Но перед этим кликаем на последнее поле – нужно, чтобы оно стало активным.
  1. Только после этого можно указывать нужный диапазон.
  1. Как только вы отпустите палец, увидите следующее.
  1. Для сохранения формулы достаточно кликнуть на кнопку «OK».

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

СУММЕСЛИМН

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

Для этого необходимо сделать следующее.

  1. Добавим строчку, где будем производить расчёты.
  1. Переходим в нужную клетку и кликаем на иконку «Fx». Находим нужную функцию и нажимаем на кнопку «OK».
  1. В появившемся окне следует указать нужный диапазон суммирования. В данном случае это графа зарплата.
  1. Для этого достаточно выделить нужные ячейки и в результате этого они подставятся автоматически.
  1. Теперь указываем «Диапазон условия 1». Например, здесь можно указать категорию сотрудников. Перед выделением обязательно кликните на это поле, иначе выделенные ячейки попадут в первую строку. Сразу после этого вы увидите новое поле для ввода.
  1. Введите туда слово «Первая». Сразу после этого вы увидите поля для второго условия.
  1. Делаем клик на поле «Диапазон условия 2». Затем выделяем столбик с предметом.
  1. В строку «Условие 2» напишите нужный вам предмет. В данном случае – «Математика». Для того чтобы увидеть корректность вашей формулы, достаточно будет кликнуть в любое уже ранее указанное поле. В результате этого второе условие окажется в кавычках (согласно правилам) и вы увидите результат данной функции.

Для сохранения формулы необходимо нажать на кнопку «OK».

В итоге вы увидите следующее.

СЧЕТЕСЛИ

В редакторе Эксель для расчётов количества ячеек можно использовать формулу «СЧЕТ». Но как быть, если нужно посчитать не все ячейки? Более того, позиция нужных клеток заранее неизвестна, поскольку таблица может содержать большое количество данных.

В таких случаях нужно использовать СЧЕТЕСЛИ. В качестве примера посчитаем, сколько именно сотрудников имеют высшую категорию. Для этого нужно сделать следующее.

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

СЧЕТЕСЛИМН

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

  1. Добавляем новую строку для расчётов. Кликаем на нужную ячейку и вызываем окно «Вставка функции». Находим нужную и кликаем на кнопку «OK».
  1. В графу «Диапазон условия» указываем поле «Категория». Для этого достаточно выделить нужные ячейки.
  1. После клика в поле «Условие 1» у вас появится строка для второго диапазона.
  1. Введите нужную категорию учителя. В данном случае – «Высшая».
  1. После этого сделайте клик в поле «Диапазон условия 2» и выделите столбец с названием предмета.
  1. Затем в последнее поле указываем слово «Математика». Для сохранения нажимаем на кнопку «OK».
  1. Результат будет следующим.

Расчёт произошел корректно. В нашей таблице всего 1 преподаватель математики с высшей категорией.

Функция ПОИСКПОЗ

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

Для этого нужно сделать следующее.

  1. Перейдите в какую-нибудь ячейку. Вызовите окно вставки функции. После того как найдете нужную, нажмите на кнопку «OK».
  1. В появившемся окне нужно будет указать:
    • искомое значение;
    • просматриваемый массив;
    • тип сопоставления.
  1. В первое поле достаточно написать слово «География», поскольку именно этого преподавателя мы ищем.
  1. Затем нужно выделить столбец с названием предметов (предварительно кликнув на второе поле).
  1. В третьем параметре укажите цифру «0». Для сохранения нажмите на кнопку «OK».
  1. В результате этого мы увидели, что ячейка со словом «География» находится под номером 8 (среди указанного диапазона).

Более подробно про третий параметр можно прочитать на официальном сайте центра поддержки компании Microsoft. Там написано следующее.

Использование условий в VBA

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

Для этого необходимо выполнить следующие шаги.

  1. По умолчанию вкладка с макросами скрыта от пользователей. Её нужно открыть. Нажмите на пункт меню «Файл».
  1. Перейдите в раздел «Параметры».
  1. В появившемся окне выберите категорию «Настроить ленту». Затем поставьте галочку возле пункта «Разработчик». Для сохранения нажмите на кнопку «OK».
  1. Сразу после этого вы увидите, что указанная вкладка появилась на панели инструментов.
  1. Перейдите на неё и нажмите на кнопку «Visual Basic».
  1. Сразу после этого появится окно для написания кода.
  1. В левой части экрана находится список объектов в вашем файле. Выберите ваш текущий лист.
  1. Введите следующий код:

Sub ProverkaPoiskaUchiteley() If = 0 Then MsgBox «Учителя не найдены» End If If > 0 Then MsgBox «Учителя найдены» End If End Sub

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

  1. Закройте этот редактор. Теперь кликните на иконку «Макросы».
  1. В появившемся окне нажмите на кнопку «Выполнить».
  1. В результате этого вы увидите сообщение о том, что учителя найдены, поскольку в ячейке «F17» содержится число больше нуля.
  1. Если вы измените значение этой ячейки на «0», то увидите совсем другой результат.

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

  1. Снова нажмите на иконку «Макросы». В появившемся окне нажмите на кнопку «Параметры».
  1. Сразу после этого вам предложат указать какую-нибудь кнопку и описание к этому макросу.
  1. Сочетания клавиш необязательно должны быть только с клавишей Ctrl. Можно использовать дополнительное сочетание с кнопкой Shift. В качестве примера назначим комбинацию Ctrl+ Shift+ E. Для сохранения нажимаем на «OK».
  1. Закройте это окошко. Теперь нажмите на сочетание клавиш Ctrl+Shift+ E. В результате этого вы увидите сообщение о результате проверки. Так намного удобнее, чем каждый раз заходить в меню.

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

Заключение

В данной статье были рассмотрены основные принципы работы с функциями категории «ЕСЛИ». Каждая из них была детально разобрана на конкретном примере. Если у вас что-то не получается, возможно, вы выделяете не те ячейки или составляете неправильное условие.

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

Видеоинструкция

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

Одна из самых популярных функций в приложении Excel – ЕСЛИ. Это логическое сравнение значения и получаемого результата. Если говорить проще, то функция может звучать так:

ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще

Синтаксис этой функции достаточно прост:

ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА;  значение в случае_ЛОЖЬ)

Разберем подробнее:

  • Логическое_условие – значение, которое будем проверять
  • Значение_в_случае_ИСТИНА – действие, когда требование выполняется
  • Значение в случае_ЛОЖЬ – действие, когда Логическое_условие не выполняется

Как использовать функцию

Пример №1

На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем заданное условие А1>25. ЕСЛИ это требование выполняется, тогда выводим в ячейку «больше 25», иначе «меньше или равно 25».

Пример №2

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

Нам необходимо в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ или НЕ СДАЛ. Критерии у нас будут такими: кто набрал более 45 балов, тот считается сдавшим экзамен, ну а все остальные нет.

Для выполнения задачи необходимо:

  1. Выбрать ту ячейку, в которой будем в водить формулу. У нас это С3.
  2. Затем определяемся с необходимыми параметрами:
    • Логическое_условие – B3>45
    • Значение_в_случае_ИСТИНА – Сдал
    • Значение_в_случае_ЛОЖЬ – не сдал
  3. Таким образом, получаем формулу =ЕСЛИ (В3>45; «Сдал»; «Не сдал»)
  4. Формула будет сравнивать число в ячейке В3 и 45. Если наше требование выполняется и Баллы больше 45, то в ячейке результата мы увидим «Сдал», иначе «Не сдал».
  5. Копируем формулу в каждую ячейку удобным способом, и смотрим результат.

Используем несколько условий

При необходимости, функцию ЕСЛИ можно вложить друг в друга. Таким образом, мы расширим варианты решений.

Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку: Плохо, Хорошо и Отлично. Отлично будем ставить, когда баллы превысят 60. Оценку Хорошо можно будет получить, набрав от 45 до 60 балов. Ну и в остальных случаях ставим Плохо.

  1. Для решения этой задачи составим формулу, включив в нее необходимые критерии оценивания: =ЕСЛИ (В3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»)).
  2. Эта формула использует одновременно два условия. Первая проверка В3>60. Если баллы действительно больше 60, то в поле Результата мы получаем Отлично, и дальнейшая проверка условий не выполняется. Если Баллы меньше 60, то срабатывает вторая часть формулы, и мы проверяем В3>45 или нет. Если все верно, то возвращается значение «Хорошо», иначе «Плохо».
  3. Затем достаточно скопировать формулу во все ячейки столбца и увидеть результат сдачи переаттестации.

Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой.

Другие варианты использования функции

Находим сумму столбцов или ячеек

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

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

Синтаксис: СУММЕСЛИ(диапазон_ячеек; используемое_условие; )

  • Диапазон – обязательный аргумент. Тут задается диапазон, который оценивается на соответствие условию.
  • Используемое_условие – тут указываем, какие именно ячейки необходимо суммировать.
  • Диапазон_суммирования – указываем, какой именно диапазон использовать для суммирования.

Количество повторений элементов на листе

В этом случае мы можем посчитать, сколько раз заданный элемент встречается на листе. Для этого используем функцию СЧЕТЕСЛИ. Она считает количество ячеек, которые совпадают с заданным значением.

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

Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных

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

Поиск нескольких вхождений элемента в списке

Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты, то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР и СЧЕТЕСЛИ.

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

Используем поиск по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов.

Как сократить количество вложений ЕСЛИ

Версии до EXCEL 2003 включительно поддерживали до 7 уровней вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали. Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается.

Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР. Она работает со значениями или действиями из заданного списка по номеру индекса.

Синтаксис: ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; ;…)

Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый»), вернет нам слово «Третий», если мы ее добавим в ячейку.

Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа.

Выделение строк таблицы в EXCEL в зависимости от условия в ячейке. Примеры и описание


Если значение в ячейке удовлетворяет определенному пользователем условию, то с помощью

Условного форматирования

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

Пусть в диапазоне

А6:С16

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

файл примера

).

Задача1 — текстовые значения

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

Условного форматирования

.

Решение1

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

Е6:Е9

.

Выделим диапазон ячеек

А7:С17

, содержащий перечень работ, и установим через меню

Главная/ Цвет заливки

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

Не начата

).

Убедимся, что выделен диапазон ячеек

А7:С17

(

А7

должна быть

активной ячейкой

). Вызовем команду меню

Условное форматирование/ Создать правило / Использовать формулу для определения форматируемых ячеек

.

  • в поле «

    Форматировать значения, для которых следующая формула является истинной

    » нужно ввести

    =$C7=$E$8

    (в ячейке

    Е8

    находится значение

    В работе

    ). Обратите внимание на использоване

    смешанных ссылок

    ;

  • нажать кнопку

    Формат

    ;
  • выбрать вкладку

    Заливка

    ;
  • выбрать серый цвет

    ;
  • Нажать ОК.


ВНИМАНИЕ

: Еще раз обращаю внимание на формулу

=$C7=$E$8

. Обычно пользователи вводят

=$C$7=$E$8

, т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе

Завершена

. Формула в этом случае будет выглядеть как

=$C7=$E$9

, а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.


Примечание

:

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

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

Главная/ Цвет заливки

.

Как это работает?

В

файле примера

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

=$C7=$E$9

из правила

Условного форматирования

для зеленого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и вправо.

Как видно из рисунка, в строках таблицы, которые выделены зеленым цветом, формула возвращает значение ИСТИНА.

В формуле использована

относительная ссылка

на строку ($C7, перед номером строки нет знака $). Отсутствие знака $ перед номером строки приводит к тому, что при копировании формулы вниз на 1 строку она изменяется на

=$C8=$E$9

, затем на

=$C9=$E$9

, потом на

=$C10=$E$9

и т.д. до конца таблицы (см. ячейки

G8

,

G9

,

G10

и т. д.). При копировании формулы вправо или влево по столбцам, изменения формулы не происходит, именно поэтому цветом выделяется вся строка.

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

Условное форматирование в MS EXCEL

.

Прием с дополнительной таблицей можно применять для тестирования любых формул

Условного форматирования

.

Рекомендации

При вводе статуса работ важно не допустить опечатку. Если вместо слово

Завершен

а

, например, пользователь введет

Завершен

о

, то

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

не сработает.

Чтобы исключить некорректный ввод используйте идеи из статьи

Ввод данных из списка значений. Часть1. Выпадающий список

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

Выпадающий список

.

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (

А17:С17

) и нажмите

сочетание клавиш

CTRL+D

. Правила

Условного форматирования

будут скопированы в строку

17

таблицы.

Задача2 — Даты

Предположим, что ведется журнал посещения сотрудниками научных конференций (см.

файл примера лист Даты

).

К сожалению, столбец Дата посещения не отсортирован и необходимо выделить дату первого и последнего посещения каждого сотрудника. Например, сотрудник Козлов первый раз поехал на конференцию 24.07.2009, а последний раз — 18.07.2015.

Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

В столбце D создана

формула массива

=

МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7

, которая определяет максимальную дату для определенного сотрудника.


Примечание:

Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится =

$B7=МАКС($B$7:$B$16)

и формула массива не понадобится.

Теперь выделим все ячейки таблицы без заголовка и создадим правило

Условного форматирования

. Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

Для этого используйте формулу

=И($B23>$E$22;$B23

Для ячеек

Е22

и

Е23

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

абсолютная адресация

$E$22 и

$E$23. Т.к. ссылка на них не должна меняться в правилах УФ для всех ячеек таблицы.

Для ячейки

В22

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

В23

).

Таким образом, правило УФ например для ячейки

А27

будет выглядеть

=И($B27>$E$22;$B27
А27

будет выделена, т.к. в этой строке дата из

В27

попадает в указанный диапазон (для ячеек из столбца А выделение все равно будет производиться в зависимости от содержимого столбца В из той же строки — в этом и состоит «магия» смешанной адресации $B23).

А для ячейки

В31

правило УФ будет выглядеть

=И($B31>$E$22;$B31
В31

не попадает в указанный диапазон.

Excel 2016: условное форматирование

Урок 24: условное форматирование

/en/excel2016/charts/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь.

Посмотрите видео ниже, чтобы узнать больше об условном форматировании в Excel.

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

Условное форматирование позволяет автоматически применять форматирование — например, цветов , значков и гистограмм — к одной или нескольким ячейкам на основе значения ячейки 900. Для этого вам нужно создать правило условного форматирования . Например, правило условного форматирования может быть таким: Если значение меньше 2000 долларов, закрасьте ячейку красным цветом . Применяя это правило, вы сможете быстро увидеть, какие ячейки содержат значения меньше 2000 долларов США.

Чтобы создать правило условного форматирования:

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

  1. Выберите нужных ячеек для правила условного форматирования.
  2. На вкладке Главная щелкните команду Условное форматирование . Появится раскрывающееся меню.
  3. Наведите указатель мыши на нужный тип условного форматирования , затем выберите нужное правило в появившемся меню. В нашем примере мы хотим выделить ячеек , которые на больше, чем 4000 долларов.
  4. Появится диалоговое окно. Введите нужное значение(я) в пустое поле. В нашем примере мы введем 4000 в качестве значения.
  5. Выберите форматирование стиль из раскрывающегося меню. В нашем примере мы выберем Зеленая заливка с темно-зеленым текстом , затем нажмем OK .
  6. Условное форматирование будет применено к выбранным ячейкам. В нашем примере легко увидеть, какие продавцы достигли цели продаж в 4000 долларов за каждый месяц.

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

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

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

  • Панели данных — это горизонтальные полосы, добавляемые к каждой ячейке, очень похожие на гистограмму .
  • Цветовые шкалы меняют цвет каждой ячейки в зависимости от ее значения. Каждая цветовая шкала использует двух- или трехцветный градиент . Например, в Зелено-Желто-Красный Цветовая шкала , самые высокие значения — зеленые, средние значения — желтые, а самые низкие значения — красные.
  • Наборы значков добавляют определенный значок к каждой ячейке в зависимости от ее значения.
Чтобы использовать предустановленное условное форматирование:
  1. Выберите нужных ячеек для правила условного форматирования.
  2. Щелкните команду Условное форматирование . Появится раскрывающееся меню.
  3. Наведите указатель мыши на нужный пресет , затем выберите стиль пресета в появившемся меню.
  4. Условное форматирование будет применено к выбранным ячейкам.

Удаление условного форматирования

Чтобы удалить условное форматирование:
  1. Щелкните команду Условное форматирование . Появится раскрывающееся меню.
  2. Наведите указатель мыши на Clear Rules, и выберите, какие правила вы хотите очистить. В нашем примере мы выберем Удалить правила со всего листа , чтобы удалить все условное форматирование с рабочего листа.
  3. Условное форматирование будет удалено.

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

Вызов!

  1. Откройте нашу рабочую тетрадь.
  2. Щелкните вкладку рабочего листа Challenge в левом нижнем углу рабочей книги.
  3. Выберите ячейки B3:J17 .
  4. Допустим, вы учитель и хотите легко увидеть все оценки ниже проходного. Примените Условное форматирование , чтобы оно Выделяло ячейки , содержащие значения Меньше чем 70 со светло-красной заливкой .
  5. Теперь вы хотите сравнить оценки друг с другом. На вкладке Условное форматирование выберите набор значков под названием 3 Символы (обведены) . Подсказка : Имена наборов значков будут отображаться при наведении на них курсора.
  6. Ваша электронная таблица должна выглядеть следующим образом:
  7. Используя функцию «Управление правилами», удалите светло-красную заливку , но оставьте набор значков .

Продолжать

Предыдущий: Карты

Далее:Отслеживание изменений и комментариев

/en/excel2016/track-changes-and-comments/content/

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

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

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

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

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

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

Как создать правило условного форматирования на основе формулы

Чтобы настроить правило условного форматирования на основе формулы в любой версии Excel 2010 — Excel 365, выполните следующие действия:

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

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

    • Преобразование диапазона ячеек в таблицу ( вкладка «Вставка» > «Таблица »). В этом случае условное форматирование будет автоматически применяться ко всем новым строкам.
    • Выберите несколько пустых строк под вашими данными, скажем, 100 пустых строк.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование > Новое правило…
  3. В окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек .
  4. Введите формулу в соответствующее поле.
  5. Нажмите кнопку Формат … , чтобы выбрать собственный формат.
  6. Переключайтесь между вкладками Font , Border и Fill и экспериментируйте с различными параметрами, такими как стиль шрифта, цвет узора и эффекты заливки, чтобы настроить формат, который лучше всего подходит для вас. Если стандартной палитры недостаточно, нажмите Больше цветов… и выберите любой цвет RGB или HSL по своему вкусу. Когда закончите, нажмите ОК кнопка.
  7. Убедитесь, что в разделе Preview отображается нужный формат, и если это так, нажмите кнопку OK , чтобы сохранить правило. Если вас не устраивает предварительный просмотр формата, снова нажмите кнопку Format… и внесите изменения.

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

Примеры формул условного форматирования Excel

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

Совет. Чтобы ваша формула условного форматирования Excel работала правильно, всегда следуйте этим простым правилам.

Формулы для сравнения значений (числа и текст)

Как вы знаете, Microsoft Excel предоставляет несколько готовых к использованию правил для форматирования ячеек со значениями, большими, меньшими или равными указанному вами значению ( Условное форматирование >Правила выделения ячеек ). Однако эти правила не работают, если вы хотите условно отформатировать определенные столбцы или целые строки 90 244 на основе значения ячейки в другом столбце. В этом случае используются аналогичные формулы:

Состояние Пример формулы
Равен =$B2=10
Не равно =$B2<>10
Больше =$B2>10
Больше или равно =$B2>=10
Менее =$B2<10
Меньше или равно =$B2<=10
Между =И($B2>5, $B2<10)

На снимке экрана ниже показан пример Больше, чем формула , которая выделяет названия продуктов в столбце A, если количество товаров на складе (столбец C) больше 0. Обратите внимание, что формула применима только к столбцу A ($A$2:$A$8). Но если вы выберете всю таблицу (в нашем случае $A$2:$E$8), будут выделены целые строки на основе значения в столбце C.

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

=$A2<$B2 - форматировать ячейки или строки, если значение в столбце A меньше, чем соответствующее значение в столбце B.

=$A2=$B2 - форматировать ячейки или строки, если значения в столбцах A и B совпадают.

=$A2<>$B2 — форматировать ячейки или строки, если значение в столбце A не совпадает со значением в столбце B.

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

Формулы И и ИЛИ

Если вы хотите отформатировать таблицу Excel на основе двух или более условий, используйте функцию =AND или =OR:

Состояние Формула Описание
Если выполняются оба условия =И($B2<$C2, $C2<$D2) Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, и , если значение в столбце C меньше, чем в столбце D.
Если выполняется одно из условий =ИЛИ($B2<$C2, $C2<$D2) Форматирует ячейки, если значение в столбце B меньше, чем в столбце C, или , если значение в столбце C меньше, чем в столбце D.

На снимке экрана ниже мы используем формулу =И($C2>0, $D2="Worldwide") , чтобы изменить цвет фона строк, если количество товаров на складе (столбец C) больше чем 0, и если продукт поставляется по всему миру (столбец D). Обратите внимание, что формула работает с текстовыми значениями , а также с числами .

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

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

Условное форматирование для пустых и непустых ячеек

Я думаю, все знают, как форматировать пустые и не пустые ячейки в Excel - вы просто создаете новое правило типа " Форматировать только те ячейки, которые содержат" и выбираете либо Пробелы , либо Без пробелов .

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

Формула для пробелов : =$B2="" - форматировать выбранные ячейки/строки, если соответствующая ячейка в столбце B пуста.

Формула для непустых : =$B2<>"" - отформатировать выбранные ячейки/строки, если соответствующая ячейка в столбце B не пуста.

Примечание. Приведенные выше формулы будут работать для ячеек, которые «визуально» пусты или не пусты. Если вы используете какую-либо функцию Excel, которая возвращает пустую строку, например. =если(ложь,"ОК","") , и вы не хотите, чтобы такие ячейки считались пустыми, вместо этого используйте следующие формулы =isblank(A1)=true или =isblank(A1)=false для форматирования пустых и непустых ячеек соответственно. .

А вот пример того, как можно использовать приведенные выше формулы на практике. Предположим, у вас есть столбец (B) " Дата продажи " и другой столбец (C) " Доставка ". Эти 2 столбца имеют значение только в том случае, если была совершена продажа и товар доставлен. Итак, вы хотите, чтобы вся строка стала оранжевой, когда вы совершили продажу; и когда товар доставлен, соответствующая строка должна стать зеленой. Для этого необходимо создать 2 правила условного форматирования со следующими формулами:

  • Оранжевые строки (ячейка в столбце B не пуста): =$B2<>""
  • Зеленые строки (ячейки в столбцах B и C не пусты): =AND($B2<>"", $C2<>"")

Еще одна вещь, которую вам нужно сделать, это переместить второе правило вверх и установить флажок Stop if true рядом с этим правилом:

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

Дополнительные сведения см. в разделе Условное форматирование Excel для пустых ячеек.

Формулы Excel для работы с текстовыми значениями

Если вы хотите отформатировать определенный столбец (столбцы), когда другая ячейка в той же строке содержит определенное слово, вы можете использовать формулу, описанную в одном из предыдущих примеров (например, =$D2="Worldwide"). Однако это будет работать только для точного соответствия .

Для частичного совпадения вам нужно будет использовать ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра).

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

=ПОИСК("По всему миру", $D2)>0

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

=ПОИСК("По всему миру", $D2)>1

Формулы Excel для выделения дубликатов

Если ваша задача заключается в условном форматировании ячеек с повторяющимися значениями, вы можете использовать предопределенное правило, доступное в разделе Условное форматирование > Правила выделения ячеек > Повторяющиеся значения… В следующей статье приведены подробные инструкции по использованию этой функции: Как автоматически выделять дубликаты в Excel.

Однако в некоторых случаях данные выглядят лучше, если вы окрашиваете выбранные столбцы или целые строки, когда повторяющиеся значения встречаются в другом столбце. В этом случае вам нужно будет снова использовать формулу условного форматирования Excel, и на этот раз мы будем использовать СЧЁТЕСЛИ формула. Как вы знаете, эта функция Excel подсчитывает количество ячеек в указанном диапазоне, которые соответствуют одному критерию.

Выделить дубликаты, включая 1

st вхождений

=СЧЕТЕСЛИ($A$2:$A$10,$A2)>1 — эта формула находит повторяющиеся значения в указанном диапазоне в столбце A (в нашем случае A2:A10), включая первые вхождения.

Если вы решите применить правило ко всей таблице, все строки будут отформатированы, как показано на снимке экрана ниже. Я решил изменить цвет шрифта в этом правиле, просто для разнообразия 🙂

Выделение дубликатов без 1

st вхождений

Чтобы проигнорировать первое вхождение и выделить только последующие повторяющиеся значения, используйте следующую формулу: =СЧЁТЕСЛИ($A$2:$A2,$A2)>1

Выделите последовательные дубликаты в Excel

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

  • Выберите столбец, в котором вы хотите выделить дубликаты, без заголовка столбца .
  • Создайте правило (правила) условного форматирования, используя следующие простые формулы:
    Правило 1 (синее): =$A1=$A2 — выделяет вхождения 2 и и все последующие вхождения, если они есть.
    Правило 2 (зеленое): =$A2=$A3 — выделяет вхождение 1 st .

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

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

Выделить повторяющиеся строки

Если вы хотите применить условный формат, когда повторяющиеся значения встречаются в двух или более столбцах, вам нужно добавить в таблицу дополнительный столбец, в котором вы объединяете значения из ключевых столбцов, используя простую формулу, подобную этой =A2&B2 . После этого вы применяете правило, используя любую вариацию формулы СЧЁТЕСЛИ для дубликатов (с или без 1 st вхождений). Естественно, вы можете скрыть дополнительный столбец после создания правила.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИМН, которая поддерживает несколько критериев в одной формуле. В этом случае вам не понадобится вспомогательный столбец.

В этом примере, чтобы выделить повторяющиеся строки с первым вхождением , создайте правило со следующей формулой:
=СЧЁТЕСЛИМН($A$2:$A$11, $A2, $B$2:$B$11, $B2)>1

Чтобы выделить повторяющиеся строки без первого вхождения , используйте следующую формулу:
=СЧЁТЕСЛИМН($A$2:$A2, $A2, $B$2:$B2, $B2)>1

Сравните 2 столбца для дубликаты

Одной из наиболее частых задач в Excel является проверка двух столбцов на наличие повторяющихся значений, т. е. поиск и выделение значений, существующих в обоих столбцах. Для этого вам потребуется создать правило условного форматирования Excel для каждого столбца с комбинацией =ЕОШИБКА() и =ПОИСКПОЗ() функции:

Для столбца A: =ЕОШИБКА(ПОИСКПОЗ(A1,$B$1:$B$10000,0))=ЛОЖЬ

Для столбца B: =ЕОШИБКА(ПОИСКПОЗ(B1,$A$1:$A$10000,0))=ЛОЖЬ

Примечание. Чтобы такие условные формулы работали правильно, очень важно, чтобы вы применяли правила ко всем столбцам, например. =$A:$A и =$B:$B .

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

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

Формулы для выделения значений выше или ниже среднего

Когда вы работаете с несколькими наборами числовых данных, функция СРЗНАЧ() может пригодиться для форматирования ячеек, значения которых ниже или выше среднего в столбце.

Например, вы можете использовать формулу =$E2<СРЕДНЕЕ($E$2:$E$8) для условного форматирования строк, в которых количество продаж ниже среднего, как показано на снимке экрана ниже. Если вы ищете противоположное, т. е. чтобы затенить продукты с показателями выше среднего, замените «<» на «>» в ​​формуле: =$E2>СРЕДНИЙ($E$2:$E$8) .

Как выделить ближайшее значение в Excel

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

Пример 1.

Найти ближайшее значение, включая точное совпадение

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

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

= МИН(АБС(B2:D13-(0)))

В приведенной выше формуле B2:D13 — это ваш диапазон ячеек, а 0 — это число, для которого вы хотите найти ближайшее соответствие. Например, если вы ищете значение, ближайшее к 5, формула изменится на: =MIN(ABS(B2:D13-(5)))

Примечание. Это формула массива , поэтому вам нужно нажать Ctrl + Shift + Enter вместо простого штриха Enter, чтобы завершить ее.

Теперь вы создаете правило условного форматирования со следующей формулой, где B3 — это верхняя правая ячейка в вашем диапазоне, а $C$2 — в ячейке с приведенной выше формулой массива:

=ИЛИ(B3=0-$C$2,B3=0+$C$2)

Обратите внимание на использование абсолютных ссылок в адресе ячейки, содержащей формулу массива ($C$2), так как эта ячейка является константой. Кроме того, вам нужно заменить 0 на число, для которого вы хотите выделить ближайшее совпадение. Например, если мы хотим выделить значение, ближайшее к 5, формула изменится на: =OR(B3=5-$C$2,B3=5+$C$2)

Пример 2. Выделите значение, ближайшее к заданному значению, но НЕ точное совпадение 90*(B3:C13=0)))

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

Формула условного форматирования такая же, как в приведенном выше примере:

=ИЛИ(B3=0-$C$2,B3=0+$C$2)

Однако, поскольку наша формула массива в ячейке C2 игнорирует точное совпадение, правило условного форматирования также игнорирует нули и выделяет значение 0,003, которое является ближайшим совпадением.

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

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

  • Как изменить цвет строки на основе значения ячейки
  • Условное форматирование Excel для дат
  • Альтернативные цвета строк и столбцов в Excel
  • Два способа изменить цвет фона на основе значения ячейки
  • Подсчет и суммирование цветных ячеек в Excel

Почему условное форматирование в Excel работает неправильно?

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

  1. Правильно используйте абсолютные и относительные адреса ячеек. Очень сложно вывести общее правило, которое будет работать в 100% случаев. Но чаще всего вы будете использовать абсолютный столбец (с $) и относительную строку (без $) в своих ссылках на ячейки, например. =$A1>1 .

    Имейте в виду, что формулы =A1=1 , =$A$1=1 и =A$1=1 дадут разные результаты. Если вы не уверены, какой из них правильный в вашем случае, вы можете попробовать все 🙂 Для получения дополнительной информации см. Относительные и абсолютные ссылки на ячейки в условном форматировании Excel.

  2. Проверьте применяемый диапазон. Проверьте, применяется ли ваше правило условного форматирования к правильному диапазону ячеек. Эмпирическое правило таково: выберите все ячейки / строки, которые вы хотите отформатировать, но не включайте заголовки столбцов.
  3. Напишите формулу для верхней левой ячейки. В правилах условного форматирования ссылки на ячейки относятся к самой верхней левой ячейке в применяемом диапазоне. Итак, всегда пишите свою формулу условного форматирования для 1-й строки с данными.

    Например, если ваши данные начинаются со строки 2, введите =A$2=10 , чтобы выделить ячейки со значениями, равными 10 во всех строках . Распространенной ошибкой является использование всегда ссылки на первую строку (например, =A$1=10 ). Помните, что вы ссылаетесь на строку 1 в формуле только в том случае, если ваша таблица не имеет заголовков и ваши данные действительно начинаются в строке 1. Наиболее очевидным признаком этого случая является то, что правило работает, но форматирует значения не в тех строках, в которых оно должно .

  4. Проверьте созданное вами правило. Дважды проверьте правило в диспетчере правил условного форматирования. Иногда без всякой причины Microsoft Excel искажает только что созданное правило.

Читайте также: