Эффективное использование функции Поиск значения в столбце в Excel

Эффективное использование функции Поиск значения в столбце в Excel

Функция НАЙТИ в Excel (в английском варианте – FIND) используется для получения номера позиции определенного символа или подстроки в тексте.

Синтаксис ее следующий:

НАЙТИ(искомый_текст; просматриваемый_текст; )

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

Искомый_текст — символ или подстрока, которую вы хотите найти.

Просматриваемый_текст — текст, в котором происходит поиск. Обычно это ссылка на ячейку, но вы также можете ввести текст непосредственно в формулу.

Начальная_позиция — необязательный аргумент, указывающий, с какого символа следует начинать поиск. Если этот параметр опущен, поиск начинается с первого.

Параметр Начальная_позиция можно использовать для пропуска указанного количества символов. Например, если функция ПОИСК будет использоваться для работы с текстовой строкой «ТЛВ1500.Телевизоры», установка для параметра Начальная_позиция значения 8 будет гарантировать, что поиск происходит только в описательной части текстовой строки, а не в части с серийным номером (в данном случае «ТЛВ1500»), позволяя найти первое вхождение буквы «Т». Функция ПОИСК начинает поиск с восьмого символа, находит символ, указанный в аргументе Просматриваемый_текст, и возвращает число 9.

Если функция НАЙТИ не находит ничего, то возвращается ошибка #ЗНАЧ! .

Например, формула НАЙТИ("р"; A2) возвратит 3, потому что «р» — третья по счету в слове «Барабан». А вот формула НАЙТИ("А"; A2) выдаст ошибку, поскольку в слове «Барабан» она не находит букву «А» (с учетом регистра).

Функция НАЙТИ в Excel – что нужно помнить!

Чтобы правильно использовать формулу НАЙТИ в Excel, помните следующее:

• Функция НАЙТИ чувствительна к регистру . Поэтому формула =НАЙТИ("б"; A2) возвратит не первую, а пятую позицию в слове, как вы видите на скриншоте выше. Если вы ищете совпадение без учета регистра, используйте функцию ПОИСК.
• Функция НАЙТИ в Excel не позволяет использовать подстановочные знаки.
• Если аргумент Искомый_текст содержит несколько символов, которые вы ищете, функция НАЙТИ возвращает позицию первого из этих символов. Например, формула =НАЙТИ("раб"; A2) возвращает 3, потому что, когда было найдено совпадение, первая буква «р» из слова «раб» была третьей буквой в слове "Барабан".
• Если в тексте содержится несколько вхождений Искомый_текст, то возвращается первое вхождение. Например, формула =НАЙТИ("а"; A2) возвращает 2, потому что первое найденное "а" является второй буквой в слове (см. скриншот выше).
• Если Искомый_текст — пустая строка «», формула НАЙТИ возвращает первый символ из строки поиска.
• Функция Excel НАЙТИ возвращает ошибку #ЗНАЧ!, если происходит любое из следующих событий: 
  • Искомый_текст не найден в Просматриваемый_текст.Начальная_позиция указывает на число, которое больше, чем количество символов в Просматриваемый_текст.
  • Начальная_позиция — 0 (ноль) или отрицательное число.

Связанные вопросы и ответы:

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

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

У нас есть слово «Excel». В этом примере нужно найти положение буквы «Х» в слове. Функция возвратит значение 2, поскольку буква находится по счету на втором месте в искомых данных:

Несмотря на то, что искомая буква «Х» находится в верхнем регистре, функция нашла ее аналог в нижнем регистре и выдала результат. В этом и есть отличие с функцией НАЙТИ – она обращает внимание на соответствие регистров.

Как использовать функцию Поиск значения в столбце в Excel

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

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

Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:

=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1) ; ПОИСКПОЗ(G3;C6:K6;1) )

=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))

Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) - Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) - Интервального просмотра (Range Lookup) . Вообще говоря, возможных значений для него три:

• 1 - поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
• -1 - поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 - до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! :)
• 0 - поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.

Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска - а значит и вся таблица - должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам.Иначе приблизительный поиск корректно работать не будет!

Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.

Какие преимущества дает использование функции Поиск значения в столбце в Excel

Функция ПОИСКПОЗ в Excel ищет указанное значение в массиве и возвращает порядковый номер этого значения.

Рассмотрим пошагово, как составить формулу ПОИСКПОЗ:

ПОИСКПОЗ(искомое_значение; массив_поиска, )

Шаг 1. Искомое_значение (обязательный аргумент) — значение, которое вы хотите найти. Это может быть число, текстовое или логическое значение, а также ссылка на ячейку.

Шаг 2. Массив_поиска (обязательно) — диапазон ячеек для поиска.

Шаг 3. Указываем Тип_совпадения (необязательно) — определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1. Аргумент, установленный в 0, возвращает только точное совпадение, в то время как два других значения допускают приблизительное совпадение.

• 1 или опущено (по умолчанию) — найти наибольшее значение в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от меньшего к большему или от А до Я.
• 0 - найти первое значение в массиве, точно равное искомому значению. Никакой сортировки не требуется.
• -1 — найти наименьшее значение в массиве, которое больше или равно искомому значению. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Я до A.

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

=ПОИСКПОЗ(“Елена”; A2:A8;0)

При желании вы можете поместить искомое значение в какую-либо ячейку (E1 в этом примере) и сослаться на эту ячейку в своей формуле:

=ПОИСКПОЗ(E1; A2:A8;0)

Как вы видите на скриншоте выше, имена расположены не по алфавиту, а потому мы устанавливаем аргументу тип_совпадения значение 0 (точное соответствие). Ведь только этот вариант не требует сортировки в массиве поиска.

Технически формула ПОИСКПОЗ показывает, на какой позиции находится "Елена" в списке. Но поскольку оценки отсортированы от наибольшей суммы к наименьшей, это также говорит нам о том, что Елена занимает пятое место среди 8 учащихся.

4 вещи, которые вы должны помнить

Как вы только что убедились, использовать ПОИСКПОЗ в Excel достаточно просто. Однако, как и в случае с почти любой другой функцией Excel, есть несколько особенностей, о которых вам следует помнить:

1. Функция ПОИСКПОЗ возвращает координаты искомого значения в массиве, а не само значение.
2. Она нечувствител ьна к регистру , то есть не различает строчные и прописные символы при работе с текстовыми значениями.
3. Если искомый массив содержит несколько вхождений значения, то возвращается позиция первого найденного.
4. Если нужное значение не найдено, возвращается ошибка #Н/Д.

Как избежать ошибок при работе с функцией Поиск значения в столбце в Excel

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

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

Формула использует три функции ВПР для считывания 3-х значений с таблицы. В последнем аргументе каждой функции находится логическое значение ИСТИНА. Это значит, что необходимо найти приблизительное значение, а необязательно точное совпадение.

Чтобы любая поисковая функция выборки в Excel: ВПР, ГПР либо ПОИСКПОЗ со значением ИСТИНА в третьем аргументе возвращала правильный результат вычисления, данные в просматриваемом столбце (в данном примере это диапазон ячеек B2:B9) должны быть отсортированы по возрастанию. Функция ВПР поочередно проверяет все значения и закончит поиск, если следующее значение будет больше чем искомое. После чего поиск прекращается. Вот почему нужно сортировать исходный диапазон по возрастанию. Таким способом найдено наибольшее значение, которое одновременно с тем меньше чем искомое.

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

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

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

В выше приведенном примере функция ВПР закончит поиск на второй строке просматриваемого столбца так как число 1023 является наибольшим числом, которое меньше от искомого числа 2003,89. Условно формулу можно разделить на 3, которые последовательно выполняют следующие операции:

Как избежать ошибок при работе с функцией Поиск значения в столбце в Excel

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

1. Сортировка данных

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

2. Использование логического значения ИСТИНА

В последнем аргументе функции ВПР необходимо указать логическое значение ИСТИНА, чтобы найти приблизительное значение, а не точное совпадение.

3. Проверка диапазона данных

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

4. Использование правильной функции

Необходимо использовать правильную функцию для поиска значения в столбце. Например, функция ВПР используется для поиска значения в столбце, а функция ГПР используется для поиска значения в строке.

5. Проверка ошибок

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

Пример использования функции ВПР

Ниже приведен пример использования функции ВПР для поиска значения в столбце:

Зарплата	Налоговая ставка
10000	=ВПР(A2, B:C, 2, ИСТИНА)
20000	=ВПР(A3, B:C, 2, ИСТИНА)
30000	=ВПР(A4, B:C, 2, ИСТИНА)

В этом примере функция ВПР используется для поиска значения в столбце B, исходя из значения в столбце A. Логическое значение ИСТИНА используется для поиска приблизительного значения.

1. Первая функция ВПР возвращает базовую налоговую ставку с третьего столбца таблицы, то есть число 69,80.
2. Следующая функция ВПР ищет тоже самое приблизительное значение для числа 2003,89, но уже по первому столбцу «Зарплата от». После чего найденное приближенное значение вычитаемое от искомого.
3. Третья функция ВПР возвращает процентную ставку с четвертого столбца таблицы. Полученная ставка умножается на чистую зарплату netto – после всех вычетов, а результат прибавляется к базовой ставке.

Как использовать функцию Поиск значения в столбце в Excel для обработки больших объемов данных

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

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

Функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ используются для поиска порядкового наибольшего и наименьшего значения в списке данных. В первом аргументе функции НАИБОЛЬШИЙ, как и в функции МАКС указывается ссылка на исходный диапазон просматриваемых значений. Однако отличие между ними заключается в том, что функция НАИБОЛЬШИЙ имеет еще один обязательный для заполнения аргумент «К» в котором можно указать порядковый номер возвращаемого наибольшего значения (второе наибольшее, третье… и т.д.).

Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.

Как использовать функцию Поиск значения в столбце в Excel для автоматизации задач

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

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

1. Находясь во вкладке «Главная» , кликаем по кнопке «Найти и выделить» , которая расположена на ленте в блоке инструментов «Редактирование» . В появившемся меню выбираем пункт «Найти…» . Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F .
2. После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш» , откроется окно «Найти и заменить» во вкладке «Найти» . Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее» , или на кнопку «Найти всё» .
3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

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

   Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

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

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

Как использовать функцию Поиск значения в столбце в Excel для анализа данных

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

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

Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:

Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))

Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:

• Функция ПОИСКПОЗ ищет искомое значение "Индия" в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
• Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.

Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.