Как проверить есть ли значение в столбце excel

Как проверить есть ли значения в таблице Эксель

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

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

проверить значения в списке

Проверка с помощью функции СЧЁТЕСЛИ

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

=СЧЁТЕСЛИ( перечень ; товар )>0

проверкас помощью СЧЁТЕСЛИ

Как это работает? Сначала СЧЁТЕСЛИ вычислит количество вхождений каждого товара в прайс. Потом Эксель сравнит эту величину с нулём. Если больше нуля – вернет ИСТИНУ, меньше – ЛОЖЬ. Позиции, для которых формула вывела ЛОЖЬ – отсутствуют у поставщика.

Я не люблю, когда таблица готова наполовину, так что заменим ИСТИНА на строку «В НАЛИЧИИ», а ЛОЖЬ – на «НЕТ». Это легко сделать с помощью функции ЕСЛИ.

получили информативную таблицу

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

Проверка с помощью ВПР

Вероятно, ВПР – одна из наиболее используемых функций Эксель. Применим её и здесь. Я сразу напишу так, чтобы результат хорошо читался:

=ЕСЛИ(ЕНД(ВПР( товар ; перечень ;1;0)); «НЕТ»; «В НАЛИЧИИ»)

проверка с помощью ВПР

  1. Сперва ВПР ищет в прайсе название продукта. Возвращает либо найденное название, либо ошибку #Н/Д, когда такого товара в списке нет
  2. Результат принимает функция ЕНД, которая возвращает ИСТИНА, если ВПР передал ошибку #Н/Д, или ЛОЖЬ в любом другом случае
  3. Далее функция ЕСЛИ обрабатывает результаты предыдущего шага. Когда она получает ИСТИНУ (товар не найден) – выводит в ячейку строку «НЕТ». А когда ЛОЖЬ (найдено) – «В НАЛИЧИИ»

Здесь я так же использовал условное форматирование для подсветки недостающих позиций.

Проверка с помощью функции ПОИСКПОЗ

Работа этого метода очень близка к предыдущему, но вместо ВПР используется связка функций ПОИСКПОЗ и ИНДЕКС:

=ЕСЛИ(ЕНД(ИНДЕКС( перечень ;ПОИСКПОЗ( товар ; перечень ;0))); «НЕТ»; «В НАЛИЧИИ»)

проверка с помощью ПОИСКПОЗ

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

Сегодня мы рассмотрели 3 способа проверить наличие значения в таблице, массиве, списке. Если вам что-то осталось непонятным – спрашивайте в комментариях. А если понятно и полезно – поделитесь ссылкой на статью с другом, буду признателен!

Поиск значений в списке данных

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

Что необходимо сделать

Точное совпадение значений по вертикали в списке

Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.

Примеры ВРОТ

Пример 1 функции ВПР

Пример 2 функции ВПР

Дополнительные сведения см. в этой информации.

Примеры индексов и совпадений

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению "Ольга" (в B7), и возвращает значение в C7 (100),которое является первым значением, которое соответствует значению "Ольга".

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

Для этого используйте функцию ВЛВП.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

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

В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.

Дополнительные сведения см. в этой информации.

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

Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.

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

Пример функций OFFSET и MATCH

C1 — это левые верхние ячейки диапазона (также называемые начальной).

MATCH("Оранжевая";C2:C7;0) ищет "Оранжевые" в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.

1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.

Точное совпадение значений по горизонтали в списке

Для этого используйте функцию ГГПУ. См. пример ниже.

Пример формулы ГВП, которая ищет точное совпадение

Г ПРОСМОТР ищет столбец "Продажи" и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

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

Для этого используйте функцию ГГПУ.

Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.

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

В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.

Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.

Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )

Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).

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

Щелкните ячейку в диапазоне.

На вкладке Формулы в группе Решения нажмите кнопку Под поиск.

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

Загрузка надстройки "Мастер подстройок"

Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.

Ссылка на основную публикацию