Условное форматирование в excel с формулой впр
Обновлено: 06.07.2024
Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный - зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких - серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя - зеленым. И так далее - насколько фантазии хватит.
Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат - Условное форматирование (Format - Conditional formatting) .
В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format) , параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники - желтым, а неуспевающие - красным цветом:
Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях - бесконечно.
Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.
Другой, гораздо более мощный и красивый вариант применения условного форматирования - это возможность проверять не значение выделенных ячеек, а заданную формулу:
Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие.
Выделение цветом всей строки
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляя незафиксированной ссылку на строку - проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:
Выделение максимальных и минимальных значений
Ну, здесь все достаточно очевидно - проверяем, равно ли значение ячейки максимальному или минимальному по диапазону - и заливаем соответствующим цветом:
В англоязычной версии это функции MIN и MAX, соответственно.
Выделение всех значений больше(меньше) среднего
Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:
Скрытие ячеек с ошибками
Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR) , которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:
Скрытие данных при печати
Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати - делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение ("да", "нет"):
Заливка недопустимых значений
Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF) , которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:
Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю - желтым:
Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования - заливку ячеек цветовыми градиентами, миниграфики и значки:
Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью. :)
Функция ВПР полезна в Excel, но когда вы перетаскиваете дескриптор автозаполнения, чтобы заполнить диапазон формулой ВПР, могут появиться некоторые ошибки. Теперь это руководство расскажет вам, как правильно заполнять функцию ВПР в Excel.
Пример
Существует таблица, содержащая оценки и относительные оценки, теперь вы хотите найти оценки в B2: B5 и вернуть относительные оценки в C2: C5, как показано ниже:
Автозаполнение ВПР в Excel с абсолютной ссылкой
Как правило, вы можете использовать формулу ВПР следующим образом = ВПР (B2; F2: G8,2) затем вы перетаскиваете дескриптор автозаполнения в нужный диапазон, вы можете получить неверные результаты, как показано на скриншоте ниже:
Но если вы используете абсолютную ссылку вместо относительной в части формулы, содержащей массив таблиц, результаты автозаполнения будут правильными.
в нужную ячейку и перетащите дескриптор автоматического заполнения в нужный диапазон, вы получите правильные результаты. Смотрите скриншот:
Наконечник:
Синтаксис приведенной выше ВПР: ВПР (lookup_value, table_array, col_index_num), здесь B2 - это значение поиска, диапазон $ F $ 2: $ G $ 8 - это таблица, 2 указывает на возвращаемое значение во втором столбце таблицы.
ПОСМОТРЕТЬ значения на нескольких листах
Автозаполнение ВПР в Excel с именем диапазона
Помимо использования абсолютной ссылки в формуле, вы также можете использовать имя диапазона вместо относительной ссылки в части формулы массива таблицы.
1. Выберите диапазон массива таблиц, затем перейдите к Поле имени (рядом со строкой формул) и введите Метки (или любое другое имя) и нажмите Enter ключ. Смотрите скриншот:
Диапазон массива таблиц - это диапазон, содержащий критерии, которые необходимо использовать в функции ВПР.
2. В ячейке введите эту формулу
затем перетащите дескриптор автозаполнения в диапазон, в котором необходимо применить эту формулу, и результаты будут получены правильно.
Автозаполнение ВПР в Excel с помощью расширенной утилиты Kutools for Excel
Если формула вам не нравится, вы можете попробовать Супер ПОСМОТРЕТЬ группа Kutools for Excel, который содержит несколько расширенных утилит LOOKUP, и все они поддерживают автоматическое заполнение VLOOKUP, вы можете найти ту, которая вам нужна. В этом случае я беру ПОСМОТРЕТЬ на нескольких листах утилита в качестве примера.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Супер ПОСМОТРЕТЬ > ПОСМОТРЕТЬ на нескольких листах.
2. в ПОСМОТРЕТЬ на нескольких листах диалог, пожалуйста, сделайте следующее:
1) Выберите диапазон поиска и диапазон вывода.
2) В разделе Диапазон данных щелкните значок Добавить кнопка чтобы добавить диапазон данных, который вы используете, в список, вы можете указать ключевой столбец и возвращаемый столбец при добавлении.
3. После добавления диапазона данных щелкните OK, появится диалоговое окно с вопросом о сохранении сценария, щелкните Да, чтобы дать сценарию имя, щелкните Нет, чтобы закрыть. Теперь ВПР автоматически заполняется в диапазоне вывода.
Объедините строки на основе другого значения столбца в Excel
Образец файла
Прочие операции (статьи)
Как автозаполнить ВПР в Excel?
Функция ВПР полезна в Excel, но когда вы перетаскиваете дескриптор автозаполнения, чтобы заполнить диапазон формулой ВПР, могут появиться некоторые ошибки. Теперь это руководство расскажет вам, как правильно заполнять функцию ВПР в Excel.
Применить отрицательный vlookup, чтобы вернуть значение слева от ключевого поля в Excel? L
Обычно функция Vlookup может помочь вам вернуть значение в правых столбцах. Если ваши извлеченные данные в левом столбце, вы можете рассмотреть возможность ввода отрицательного номера столбца в формулу: = Vlookup (F2, D2: D13, -3,0), но .
Применить условное форматирование на основе ВПР в Excel
В этой статье вы узнаете, как применить условное форматирование к диапазону на основе результатов ВПР в Excel.
Группировать возраст в диапазоне с помощью ВПР в Excel
В моем листе у меня есть несколько имен и возрастов, а также некоторые возрастные группы, теперь я хочу сгруппировать возрасты на основе данных возрастных групп, как показано на скриншоте ниже. Как мне ее быстро решить?
В этой статье вы узнаете, как применить условное форматирование к диапазону на основе результатов ВПР в Excel.
- Применить условное форматирование на основе ВПР и сравнения результатов
- Применение условного форматирования на основе ВПР и сопоставления результатов
- Применяйте условное форматирование на основе ВПР и сопоставляйте результаты с помощью замечательного инструмента
Применить условное форматирование на основе ВПР и сравнения результатов
Например, вы перечислили все оценки студентов и результаты последнего семестра на двух листах, как показано на скриншоте ниже. Теперь вы хотите сравнить результаты на двух листах и выделить строки в Счет лист, если баллы выше, чем в прошлом семестре. В этой ситуации вы можете применить условное форматирование на основе функции ВПР в Excel.
1. В таблице оценок выберите оценки учащихся, кроме заголовков (в моем случае я выбираю B3: C26), и нажмите Главная > Условное форматирование > Новое правило. Смотрите скриншот:
2. В диалоговом окне «Новое правило форматирования» сделайте следующее:
(1) Щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила список;
(2) В Формат значений, где эта формула истинна поле, введите эту формулу = ВПР ($ B3, «Оценка за последний семестр»! $ B $ 2: $ C $ 26,2, FALSE) <Score! $ C3;
(3) Щелкните значок Формат кнопку.
Примечание: в формуле выше
- $ B3 это первая ячейка с именем студента в Счет лист;
- «Результат последнего семестра»! $ B $ 2: $ C $ 26 таблица результатов за последний семестр в Оценка последнего семестра лист;
- 2 означает поиск значений во втором столбце таблицы результатов за последний семестр.
- Счет! $ C3 это первая ячейка в Счет лист.
3. В диалоговом окне «Формат ячеек» перейдите к Заполнять вкладку, выберите цвет заливки и нажмите OK > OK закрыть два диалога.
Теперь вы увидите, если оценка студента в Счет лист выше, чем в Оценка последнего семестра лист, строка этого учащегося будет выделена автоматически. Смотрите скриншот:
Применение условного форматирования на основе ВПР и сопоставления результатов
Вы также можете применить функцию ВПР для сопоставления значений на двух листах, а затем применить условное форматирование на основе ВПР и результатов сопоставления в Excel. Пожалуйста, сделайте следующее:
Например, у меня есть список победителей в Sheet1, и список студентов в Sheet2 как показано ниже. Теперь я сопоставлю список победителей и список студентов с помощью функции ВПР, а затем выделю сопоставленные строки в списке победителей.
1. Выберите список победителей, кроме заголовков, и нажмите Главная > Условное форматирование > Новое правило.
2. В диалоговом окне «Новое правило форматирования» сделайте следующее:
(1) В Выберите тип правила список, пожалуйста, нажмите, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать.;
(2) В Формат значений, где эта формула истинна поле, введите эту формулу = НЕ (ISNA (ВПР ($ C3; Sheet2! $ B $ 2: $ C $ 24,1; FALSE)));
(3) Щелкните значок Формат кнопку.
Внимание: В формуле выше
- $ C3 - первое имя в списке победителей;
- Sheet2! $ B $ 2: $ C $ 24 список студентов на Sheet2;
- 1 означает поиск совпадающего значения в первом столбце списка студентов.
Если вам нужно выделить несоответствующие значения, вы можете использовать эту формулу = ISNA (ВПР ($ C3; Sheet2! $ B $ 2: $ C $ 24,1; FALSE))
3. В диалоговом окне «Формат ячеек» перейдите к Заполнять вкладку, щелкните, чтобы выбрать цвет заливки, и щелкните OK > OK чтобы закрыть оба диалога.
Теперь вы увидите, совпадает ли имя в списке победителей (или не совпадает) со списком студентов, строка с этим именем будет выделена автоматически.
Применяйте условное форматирование на основе ВПР и сопоставляйте результаты с помощью замечательного инструмента
Если у вас есть Kutools for Excel установлен, вы можете применить его Выберите одинаковые и разные ячейки функция для простого применения условного форматирования на основе ВПР и сопоставления результатов в Excel. Пожалуйста, сделайте следующее:
Kutools for Excel- Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 60-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки для включения этой функции.
2. В диалоговом окне «Выбор одинаковых и разных ячеек» сделайте следующее:
(1) Выберите столбец Имя в списке победителей в Найдите значения в выберите столбец Имя списка студентов в Согласно информации поле, и необязательно проверять У моих данных есть заголовки вариант как вам нужно.
(2) Проверьте Каждый ряд вариант в на основании раздел;
(3) Проверьте Те же значения or Разные ценности вариант, как вам нужно в Найдите раздел;
(4) Проверьте Заполните цвет фона вариант и выберите цвет заливки из раскрывающегося списка ниже;
(5) Проверьте Выбрать целые строки опцию.
3, Нажмите Ok кнопку, чтобы применить эту функцию.
В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.
Как вызвать функцию ВПР. Функция ВПР в Excel
В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.
Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.
Теперь перейдем непосредственно к вариантам применения функции ВПР.
Первый вариант использования функции ВПР.
Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.
Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.
Вызываем функцию ВПР, как описано выше.
Аргументы функции. Функция ВПР в Excel.
Искомое_значение.
Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).
Таблица.
Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.
Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.
В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты
Номер_столбца.
Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.
Интервальный _просмотр.
В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.
Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.
Вот как это выглядит все вместе.
Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.
Второй вариант использования функции ВПР.
У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.
Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.
В диалоговом окне, Аргументы функции прописываем следующие данные:
Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).
Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).
Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.
Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.
Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.
Обратите внимание. Функция ВПР в Excel.
Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.
Это особенность, так же говорит нам о том, что значения, которые мы хотим перенести в нашу таблицу, должны быть справа, от столбца, в котором происходит поиск по заданному критерию.
Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.
Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).
Возможные ошибки.
Читайте также: