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

Функции поиска данных: разыскивается информация!

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

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

Поиск в таблице Эксель, функции ВПР и ГПР

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

Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

Искомое значение – значение, которое будем искать. Это обязательный аргумент;
Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

Поиск точного совпадения с помощью ВПР

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

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

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

Поиск данных с помощью функции ПРОСМОТР

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :

Искомое значение – данные или ссылка на данные, которые нужно искать;
Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

Искомое значение – обязательный аргумент
Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :

Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Добавить комментарий Отменить ответ

4 комментариев

А как быть, если в поле «Наименование» вперемешку числовые и текстовые значения? У меня эксель числовые значения игнорирует, т.к. они числятся у него, как текстовые. К слову сказать, в таблице 56 тыс. записей и вручную всё пройти довольно сложно.

Не понял Вашего вопроса. Судя по всему, у Вас все данные хранятся, как текст. А что не работает?

А если нужно в просматриваемом массиве найти несколько однотипных искомых значений, а над возвращаемыми данными произвести арифметические вычисления (найти среднеарифметическое значение)?

Здравствуйте. Смотря что Вы называете однотипными данными. Опишите, пожалуйста, подробнее.

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

Пусть в диапазоне А4:В15 имеется таблица с перечнем сотрудников и их зарплат (фамилии сотрудников не повторяются).

Читать еще: Как вставить таблицу из excel в word если таблица не помещается

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

Алгоритм решения задачи следующий:

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

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

Решение

Если несколько значений удовл. критерию

берется первое сверху

= ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2))

берется последнее сверху

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

соответствующие значения суммируются

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

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

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

Совет: Если в диапазон поиска постоянно вводятся новые значения, то для исключения ввода дубликатов следует наложить определенные ограничения (см. статью Ввод неповторяющихся значений). Для визуальной проверки наличия дубликатов можно использовать Условное форматирование (см. статью Выделение повторяющихся значений).

Для организации динамической сортировки пополняемого диапазона поиска можно использовать идеи из статьи Сортированный список.

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

Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F, чтобы вызвать окно поиска значений на листе Excel. Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу.

Поиск значения в массиве Excel

Схема решения задания выглядит примерно таким образом:

в ячейку B1 мы будем вводить интересующие нас данные;
в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

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

Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.

Поиск значения в столбце Excel

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

В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки < >.

В ячейку C2 формула вернула букву D — соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.

Поиск значения в строке Excel

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.

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

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

для столбца таблицы – Март;
для строки – Товар4.

Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:

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

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

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

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

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

Более того для диапазона табличной части создадим правило условного форматирования:

Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

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

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

Читать еще: Выпадающий список в excel в зависимости от значения

Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:

После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1 . Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:

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

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.

Поиск значения в столбце и строке таблицы Excel

Имеем таблицу, в которой записаны объемы продаж определенных товаров в разных месяцах. Необходимо в таблице найти данные, а критерием поиска будут заголовки строк и столбцов. Но поиск должен быть выполнен отдельно по диапазону строки или столбца. То есть будет использоваться только один из критериев. Поэтому здесь нельзя применить функцию ИНДЕКС, а нужна специальная формула.

Поиск значений в таблице Excel

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

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

Поиск значения в строке Excel

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

Чтобы выполнить поиск по столбцам следует:

В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
В ячейку D1 введите следующую формулу:
Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.
В ячейку F1 введите вторую формулу:
Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

Принцип действия формулы поиска значения в строке Excel:

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

Как видно конструкция формулы проста и лаконична. На ее основе можно в похожий способ находить для определенного товара и другие показатели. Например, минимальное или среднее значение объема продаж используя для этого функции МИН или СРЗНАЧ. Вам ни что не препятствует, чтобы приведенный этот скелет формулы применить с использованием более сложных функций для реализации максимально комфортного анализа отчета по продажам.

Как получить заголовки столбцов по зачиню одной ячейки?

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).

Поиск значения в столбце Excel

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
В ячейку D2 введите формулу:
Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.
В ячейку F1 введите вторую формулу:
Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

Принцип действия формулы поиска значения в столбце Excel:

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

Читать еще: Зависимые выпадающие списки в excel как сделать

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!