Поиск слов в excel

Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel

Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.

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

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

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

=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))

Такая формула используется чаще всего для поиска сразу по двум критериям.

Функция ВПР, пошаговая инструкция

Итак, изначально у нас есть две таблицы, которые Вы можете видеть на скриншоте ниже. Общим столбцом для обеих таблиц является «Должность».

Цель: добавить в первую (верхнюю) таблицу в столбец «Зарплата» данные из аналогичного столбца второй (нижней) таблицы.

Если Вы подумали, что это можно сделать и вручную, то это большая ошибка, поскольку строк в таблицах может быть многие тысячи, а порядок их следования в обеих таблицах вовсе не обязан совпадать!

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

Функция ВПР, инструкция

Добавим ВПР в ячейку. Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.

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

Указываем параметр «Таблица». Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12»

Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.

Указываем параметр «Номер столбца». Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).

Указываем параметр «Интервальный просмотр»

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

Обратите внимание на символ «$» перед номерами строк в диапазоне (аргумент ВПР номер 2). Это нужно для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки

В результате для ячейки C4 мы получим следующую формулу: =ВПР(B4; A$10:B$12; 2; 0)

Как работает функция ВПР

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

Поскольку в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в результате то, что указано в колонке «Зарплата».

Итого полученный результат: «40 000»

Это значение и будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше Вы можете видеть, что значение «40 000» вписано в ячейку C4 первой таблицы.

Скачать файл Excel (*.xlsx) с этим примером Вы можете после статьи.

Заполним аналогичными формулами остальные ячейки

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

В данном случае лишь необходимо учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен меняться. Именно поэтому перед номерами строк в диапазоне поставлены символы «$». Для первого аргумента (искомое значение) это, напротив, не нужно, поскольку для каждой строки первой таблицы искомое значение будет в разной ячейке (в нашем примере: A4, A5, A6).

Формула Excel MAX IF с логикой ИЛИ

Чтобы найти максимальное значение при выполнении любого из указанных условий, используйте уже знакомую формулу массива МАКС ЕСЛИ с булевой логикой, но сложите условия, а не перемножайте их.

{=МАКС(ЕСЛИ((критерии_диапазон1знак равнокритерии1) + (критерии_диапазон2знак равнокритерии2), максимальный_диапазон))}

Кроме того, вы можете использовать следующую формулу без массива:

=СУММПРОИЗВ(МАКС(((критерии_диапазон1знак равнокритерии1) + (критерии_диапазон2знак равнокритерии2)) * максимальный_диапазон))

Для примера вычислим лучший результат в раундах 2 и 3

Обратите внимание, что в языке Excel задача формулируется иначе: вернуть максимальное значение, если раунд либо 2, либо 3

С раундами, перечисленными в B2:B10, результатами в C2:C10 и критериями в F1 и H1, формула выглядит следующим образом:

=МАКС(ЕСЛИ((B2:B10=F1) + (B2:B10=H1), C2:C10))

Введите формулу, нажав комбинацию клавиш Ctrl + Shift + Enter, и вы получите такой результат:

Максимальное значение с теми же условиями также можно найти с помощью этой формулы без массива:

=СУММПРОИЗВ(МАКС(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))

Однако в этом случае нам нужно заменить все значения «x» в столбце C нулями, потому что СУММПРОИЗВ МАКС работает только с числовыми данными:

Как работают эти формулы

Формула массива работает точно так же, как за исключением того, что вы соединяете критерии, используя операцию сложения вместо умножения. В формулах массива сложение работает как оператор ИЛИ:

Сложение двух массивов ИСТИНА и ЛОЖЬ (которые получаются в результате проверки значений в B2:B10 по критериям в F1 и H1) дает массив из 1 и 0, где 1 представляет элементы, для которых любое условие является ИСТИННЫМ, а 0 представляет элементы. для которого оба условия ЛОЖНЫ. В результате функция ЕСЛИ «сохраняет» все элементы в C2:C10 (значение_если_истина), для которого любое условие ИСТИННО (1); остальные элементы заменяются на FALSE, потому что значение_если_ложь аргумент не указан.

Формула без массива работает аналогичным образом. Разница в том, что вместо логического теста IF вы умножаете элементы массива 1 и 0 на элементы массива результатов прыжка в длину (C2:C10) в соответствующих позициях. Это аннулирует элементы, которые не соответствуют ни одному условию (имеют 0 в первом массиве), и сохраняет элементы, которые соответствуют одному из условий (имеют 1 в первом массиве).

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

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

Если аргумент «искомое» является текстом и интервальный_просмотр имеет значение ЛОЖЬ, то для обнаружения точного совпадения можно использовать символы подстановки * и ?.

• * (звездочка) – любое количество любых символов (в том числе и их полное отсутствие)
• ? (вопросительный знак) – один любой символ.

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

Они дают вам простой способ создать «ленивый поиск», но они также позволяют ошибиться.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

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

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

• Что_ищем — это значение, которое надо найти
• Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
• Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

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

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

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

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

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Поиск по нескольким критериям в таблицах Excel

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

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

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

Для этой операции подойдёт формула:

В обязательном порядке формулу необходимо вносить не просто так, а как формулу массива, о чём свидетельствуют фигурные скобки. Произвести это возможно с помощью комбинации горячих клавиш Shift+Ctrl+Enter.

«Нагаев А.В.»«Апельсин»«Нагаев А.В.Апельсин»«B2:B6»«C2:C6»«Нагаев А.В.Банан»«Сидоров А.С.Лимон»

Следующим этапом после нахождения функцией ПОИСКПОЗ номера строки с необходимыми условиями, передается функции ИНДЕКС, которая с диапазона «D2:D6» вытянет нужную сумму по указанному адресу номера строки.

Ну, вот результат и готов!

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

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

Не забудьте подкинуть автору на кофе…

Особенности использования формулы ВПР в Excel

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

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

Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий

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

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество. 

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

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

Это позволит удалить сразу все внешние ссылки. 

ИНДЕКС и ПОИСКПОЗ в Excel

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

Синтаксис и использование функции ИНДЕКС

Функция ИНДЕКС Excel возвращает значение в массиве, основанное на указанных вами строках и столбцах. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив; номер_строки; )

Вот очень простое объяснение каждого параметра:

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

Если используются оба параметра номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении указанной строки и столбца.

Вот простейший пример формулы ИНДЕКС:

Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.

Очень легко, не так ли? Однако при работе с реальными данными вы вряд ли знаете, какие строки и столбцы вам нужны, поэтому вам нужна помощь функции ПОИСКПОЗ.

ИНДЕКС ПОИСКПОЗ в Excel пример

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

Функция ПОИСКПОЗ определяет относительное положение значения поиска в указанном диапазоне ячеек. А функция ИНДЕКС принимает это число и возвращает значение в соответствующую ячейку.

Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:

=ИНДЕКС(столбец для возвращения значения, ПОИСКПОЗ (значение поиска, столбец для поиска, 0))

Для лучшего понимания рассмотрим наглядный пример. Предположим, у нас есть список стран с населением, подобных этому:

Функция ПОИСКПОЗ в Excel – Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel

Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ:

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

• Функция ПОИСКПОЗ выполняет поиск значения «Россия» в столбце B, точнее в ячейках B2:B11 и возвращает номер 7, потому что «Россия» находится на седьмом месте в списке.
• Функция ИНДЕКС принимает «7» во втором параметре (номер_строки), который указывает, из какой строки вы хотите вернуть значение, и превращается в простую формулу =ИНДЕКС($C$2:$C$11, 7). То есть формула производит поиск в ячейках C2-C11 и возвращает значение ячейки в седьмой строке, то есть ячейке C8, потому что мы начинаем отсчет со второй строки.

И вот результат, который мы получаем в Excel:

Функция ПОИСКПОЗ в Excel – ИНДЕКС ПОИСКПОЗ пример формулы

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

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; )

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

1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
3. Если аргумент явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.