Как проверить есть ли значения в таблице Эксель
Здравствуйте. В этой статье расскажу, как проверить, есть ли определенное значение в таблице Excel. Вы можете сделать это огромным количеством способов, я расскажу о трёх наиболее удобных. Показываю сразу на примере.
Предположим, у нас есть список товаров, доступных к заказу у поставщика. И есть перечень продукции, которую нужно заказать. Требуется проверить, что из необходимого мы можем заказать у поставщика.
Проверка с помощью функции СЧЁТЕСЛИ
В этом варианте мы посчитаем, сколько раз каждый из товаров встречается в прайсе поставщика. Если СЧЁТЕСЛИ возвращает ноль, значит этой продукции у него нет. Формула такая:
=СЧЁТЕСЛИ( перечень ; товар )>0
Как это работает? Сначала СЧЁТЕСЛИ вычислит количество вхождений каждого товара в прайс. Потом Эксель сравнит эту величину с нулём. Если больше нуля – вернет ИСТИНУ, меньше – ЛОЖЬ. Позиции, для которых формула вывела ЛОЖЬ – отсутствуют у поставщика.
Я не люблю, когда таблица готова наполовину, так что заменим ИСТИНА на строку «В НАЛИЧИИ», а ЛОЖЬ – на «НЕТ». Это легко сделать с помощью функции ЕСЛИ.
Кроме того, я использовал условное форматирование, чтобы автоматически выделить цветом те позиции, которые заказать не удастся. Теперь результат интуитивно понятен и легко читается.
Проверка с помощью ВПР
Вероятно, ВПР – одна из наиболее используемых функций Эксель. Применим её и здесь. Я сразу напишу так, чтобы результат хорошо читался:
=ЕСЛИ(ЕНД(ВПР( товар ; перечень ;1;0)); «НЕТ»; «В НАЛИЧИИ»)
Здесь я так же использовал условное форматирование для подсветки недостающих позиций.
Проверка с помощью функции ПОИСКПОЗ
Работа этого метода очень близка к предыдущему, но вместо ВПР используется связка функций ПОИСКПОЗ и ИНДЕКС:
=ЕСЛИ(ЕНД(ИНДЕКС( перечень ;ПОИСКПОЗ( товар ; перечень ;0))); «НЕТ»; «В НАЛИЧИИ»)
В остальном, порядок работы такой же, но в некоторых случаях лучше использовать именно этот подход, чем с ВПР. Почему – читайте в этой статье.
Сегодня мы рассмотрели 3 способа проверить наличие значения в таблице, массиве, списке. Если вам что-то осталось непонятным – спрашивайте в комментариях. А если понятно и полезно – поделитесь ссылкой на статью с другом, буду признателен!
Как в excel проверить есть ли значение в диапазоне
Как определить, находится ли число или значение в диапазоне в Excel?
Иногда вам нужно проверить или определить, находится ли число или значение в диапазоне. В этом уроке мы покажем вам, как этого добиться.
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Как показано на скриншоте ниже, вы можете определить, выходит ли число «5972» или текст «новый» из диапазона A1: E8 с помощью следующей формулы. Пожалуйста, сделайте следующее.
1. Выберите пустую ячейку для поиска результата. Введите формулу = ЕСЛИ (СЧЁТЕСЛИ (A1: E8; «новый»); «Да»; «Нет») в панель формул, а затем нажмите Enter ключ.
Базовые ноты:
Помимо приведенной выше формулы, вы можете определить, находится ли число или значение в диапазоне с Выбрать определенные ячейки полезности Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон, в котором вы хотите определить, находится ли число или значение, затем щелкните Kutools > Выберите > Выбрать определенные ячейки.
2. в Выбрать определенные ячейки в диалоговом окне выберите Ячейка вариант в Тип выбора раздел, выберите Равно в Конкретный тип раскрывающийся список, затем введите число или значение, которое вы хотите найти в диапазоне, и, наконец, щелкните значок OK кнопка. Смотрите скриншот:
Если число или значение существует в указанном диапазоне, вы получите следующее диалоговое окно. Щелкните значок OK кнопка, то ячейка будет выбрана немедленно.
Но если число или значение не существует в указанном диапазоне, вы получите диалоговое окно, показанное ниже.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Поиск значений в списке данных
Предположим, что вы хотите найти отсрочку телефона сотрудника, используя его эмблему или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнять вычисления или выступать с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
В этой статье
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию В ПРОСМОТР или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ.В.В.
Дополнительные сведения см. в функции ВЛОП.
Примеры индекса и MATCH
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее капусте (в B7), и возвращает значение в C7 (100),которое является первым значением, которое соответствует Капусте.
Дополнительные сведения см. в функциях ИНДЕКС иMATCH.
Приблизительное совпадение значений по вертикали в списке
Для этого используйте функцию ВЛОП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше СРОТ ищет имя учащегося, у которого в диапазоне A2:B7 6 ст. В таблице нет записи для 6 таблиц, поэтому ВРОТ ищет следующее наиболее высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктора, и тем самым возвращает Виктора.
Дополнительные сведения см. в функции ВЛОП.
Подстановка значений по вертикали в списке неизвестных размеров с использованием точного совпадения
Для этой задачи используются функции OFFSET и MATCH.
Примечание: Используйте этот подход, если данные находится в диапазоне внешних данных, который обновляется каждый день. Вы знаете, что цена находится в столбце B, но не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (верхней ячейки, также называемой начальной).
Match(«Апельсины»;C2:C7;0) ищет «Апельсины» в диапазоне C2:C7. Не включаем запускаемую ячейку в диапазон.
1 — это количество столбцов справа от начальной ячейки, в которых должно быть возвращено значение. В нашем примере значение возвращается из столбца D, Sales.
Точный совпадение значений по горизонтали в списке
Для этого воспользуйтесь функцией Г ПРОСМОТР. См. пример ниже.
Г.Г.В.В.ищет столбец «Продажи» и возвращает значение из строки 5 указанного диапазона.
Дополнительные сведения см. в функции ГВ.В.
Примерный совпадение значений по горизонтали в списке
Для этого воспользуйтесь функцией Г ПРОСМОТР.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛУП ищет значение 11000 в строке 3 указанного диапазона. Оно не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в функции ГВ.В.
Создание формулы подпапок с помощью мастера подметок (толькоExcel 2007 )
Примечание: Надстройка «Мастер подстройок» была прекращена в Excel 2010. Эта функция была заменена мастером функций и доступными функциями ссылки и подытогов (справка).
В Excel 2007 данных, которые содержит подписи строк и столбцов, мастер подступок создает формулу подытовки на основе данных на этом сайте. Если вы знаете значение в одном столбце и наоборот, мастер подступов помогает находить другие значения в строке, и наоборот. В формулах, которые он создает, используются формулы ИНДЕКС и MATCH.
Щелкните ячейку в диапазоне.
На вкладке «Формулы» в группе «Решения» нажмите кнопку «Под поиск».
Если команда «Подметка» недоступна, необходимо загрузить надстройка программу.
Загрузка надстройки «Мастер подстройок»
Нажмите кнопку Microsoft Office» 
, выберите «Параметры Excel» и щелкните категорию «Надстройки».
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
В диалоговом окне «Доступные надстройки» щелкните поле «Мастер подстройок» и нажмите кнопку «ОК».
Поиск данных в таблице или диапазоне ячеек с помощью встроенных функций Excel
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив
-или-
Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай
-или-
Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе «образец».
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе «образец».
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе «образец».
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе «образец».
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Поиск значения в диапазоне таблицы Excel по столбцам и строкам
Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.
Поиск значения в массиве Excel
Схема решения задания выглядит примерно таким образом:
Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером.
Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.
Поиск значения в столбце Excel
Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:
Поиск значения в строке Excel
Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:
После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:
Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.
Как получить заголовок столбца и название строки таблицы
Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:
Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:
В результате получены внутренние координаты таблицы по значению – Март; Товар 4:
На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.
Поиск одинаковых значений в диапазоне Excel
Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:
Более того для диапазона табличной части создадим правило условного форматирования:
Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:
В данном случаи изменяем формулы либо одну либо другую, но не две сразу. Стоит напомнить о том, что в ячейке С3 должна оставаться старая формула:
Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.
Поиск ближайшего значения в диапазоне Excel
Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:
Теперь можно вводить любое исходное значение, а программа сама подберет ближайшее число, которое содержит таблица. После чего выводит заголовок столбца и название строки для текущего значения. Например, если ввести число 5000 получаем новый результат:
Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.
