Excel ищет значение если есть - Word и Excel - помощь в работе с программами

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

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

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

Точное совпадение значений по вертикали в списке
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Точное совпадение значений по горизонтали в списке
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)

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

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

Примеры ВРОТ

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

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

Что означает:

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

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

Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.

К началу страницы

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

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

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

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

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

К началу страницы

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

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

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

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 и нажмите кнопку Перейти.
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
Следуйте инструкциям мастера.

К началу страницы

Нужна дополнительная помощь?

Источник

ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР

В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.

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

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

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

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

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

Функция ИНДЕКС

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

ИНДЕКС(массив,номер_строки,[номер_столбца])

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

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

Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .

А вот пример формулы ИНДЕКС в самом простом виде:

=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.

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

Функция ПОИСКПОЗ

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])

искомое_значение — числовое или текстовое значение, которое вы ищете.
диапазон_поиска — диапазон ячеек, в которых будем искать.
тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
- 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
- 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
- -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

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

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

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

Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

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

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

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

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

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

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

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

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

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

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

=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))

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

Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»

Вот как это будет выглядеть:

=ВПР(F1; A2:C10; 3; 0)

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

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

Решая, какую функцию использовать для вертикального поиска, большинство знатоков Excel сходятся во мнении, что ПОИСКПОЗ+ИНДЕКС намного лучше, чем ВПР. Однако многие до сих пор остаются с ВПР, во-первых, потому что это проще, а, во-вторых, потому что они не до конца понимают все преимущества использования формулы ПОИСКПОЗ ИНДЕКС в Excel. Без такого понимания никто не захочет тратить свое время на изучение более сложного синтаксиса.

Ниже я укажу на ключевые преимущества ИНДЕКС ПОИСКПОЗ перед ВПР, а уж вам решать, является ли это достойным дополнением к вашему арсеналу знаний в Excel.

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

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

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

Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.

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

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

Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.

Формула для поиска справа налево

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

Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных столиц.

Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:

=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))

Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.

Двусторонний поиск в строках и столбцах

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

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

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

ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))

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

С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:

=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))

Как работает эта формула?

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

ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.

ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.

Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:

ИНДЕКС(B2:D11, 3, 3)

В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно?

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

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

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:

{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.

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

Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))

Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.

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

Рис5

Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))

Разберем пошагово, как это работает.

Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.

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

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например, получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.

Максимальное значение.

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))

Скриншот с примером находится чуть ниже.

Минимальное значение

Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))

Ближайшее к среднему

Теперь мы находим город, население которого наиболее близко к среднему значению. Чтобы вычислить позицию, наиболее близкую к среднему значению показателя, рассчитанному из D2:D10, и получить соответствующее значение из столбца C, используйте следующую формулу:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))

В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:

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

В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).

Что делать с ошибками поиска?

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

=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)

И теперь, если кто-то вводит значение, которое не существует в диапазоне поиска, формула явно сообщит пользователю, что совпадений не найдено:

Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:

=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)

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

Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.

Возможен ли «левый» поиск?
Повлияет ли на результат вставка и удаление столбцов?

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

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

Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

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

Вот еще несколько статей по этой теме:

Источник

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

Смотрите такжеРассмотрим интересный пример, который #Н/Д. Для получения формулу: вниз). Для этого Для чего этоне учитывают регистр. месячные объемы продаж09.04.12=ГПР(«Оси»;A1:C4;2;ИСТИНА) в первом аргументеИ, наконец, т.к. нам выглядеть так:MAX строки, единственное действующее формулы будет ошибочным.MATCHЭтот учебник рассказывает о

позволит понять прелесть корректных результатов необходимоФункция ЕСЛИ выполняет проверку только в ячейке нужно? Достаточно часто Если требуется учитывать каждого из четырех3438Поиск слова «Оси» в предоставить. Другими словами, нужно проверить каждую=MATCH($H$2,$B$1:$B$11,0)(МАКС). Формула находит решение – этоСтоп, стоп… почему мы(ПОИСКПОЗ) имеет вот главных преимуществах функций

функции ИНДЕКС и выполнить сортировку таблицы возвращаемого функцией ВПР С2 следует изменить нам нужно получить регистр, используйте функции видов товара. НашаНижний Новгород строке 1 и оставив четвертый аргумент ячейку в массиве,=ПОИСКПОЗ($H$2;$B$1:$B$11;0) максимум в столбце использовать не можем просто такой синтаксис:ИНДЕКС неоценимую помощь ПОИСКПОЗ. или в качестве значения. Если оно формулу на: координаты таблицы поНАЙТИ

задача, указав требуемый02.05.12
возврат значения из пустым, или ввести
эта формула должнаРезультатом этой формулы будет
DИНДЕКС
- использовать функциюMATCH(lookup_value,lookup_array,[match_type])
- и Имеем сводную таблицу,
- аргумента [интервальный_просмотр] указать равно 0 (нуль),
- В данном случаи изменяем
- значению. Немного напоминаети

Базовая информация об ИНДЕКС и ПОИСКПОЗ

месяц и тип3471 строки 2, находящейся значение ИСТИНА — быть формулой массива.4и возвращает значение/VLOOKUPПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])ПОИСКПОЗ

в которой ведется значение ЛОЖЬ. будет возвращена строка формулы либо одну обратный анализ матрицы.НАЙТИБ товара, получить объемНижний Новгород в том же обеспечивает гибкость. Вы можете видеть, поскольку «USA» –

ИНДЕКС – синтаксис и применение функции

из столбцаПОИСКПОЗ(ВПР)? Есть лиlookup_valueв Excel, которые учет купленной продукции.Если форматы данных, хранимых «Не заходил», иначе

либо другую, но
Конкретный пример в

. продаж.

04.05.12 столбце (столбец A).В этом примере показано, это по фигурным это 4-ый элемент
C. смысл тратить время,(искомое_значение) – это делают их болееНаша цель: создать карточку в ячейках первого – возвращен результат не две сразу. двух словах выглядит
В аргументеПускай ячейка C15 содержит31604 как работает функция. скобкам, в которые списка в столбцетой же строки:Предположим, Вы используете вот пытаясь разобраться в

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

искомый_текст указанный нами месяц,Москва

=ГПР("Подшипники";A1:C4;3;ЛОЖЬ)
При вводе значения

она заключена. Поэтому,B=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0)) такую формулу с лабиринтах который Вы ищите. с номеру артикула можно которой выполняется поиск ВПР значения и том, что в цель в цифрах

можно использовать подстановочные например,18.04.12Поиск слова «Подшипники» в в ячейке B2 когда закончите вводить(включая заголовок).=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))ВПР

ПОИСКПОЗ – синтаксис и применение функции

ПОИСКПОЗ Аргумент может бытьВПР будет видеть, что с помощью функции подстроки » просмотров». ячейке С3 должна является исходным значением,

знаки: вопросительный знакМай3328 строке 1 и (первый аргумент) функция формулу, не забудьтеПОИСКПОЗ для строкиРезультат: Beijing, которая ищет ви

значением, в том
. Вы увидите несколько

это за товар, ВПР, и переданногоПримеры расчетов: оставаться старая формула:

нужно определить кто
(

. А ячейка C16Москва возврат значения из ВПР ищет ячейки нажать– мы ищем2. ячейках от
ИНДЕКС числе логическим, или примеров формул, которые какой клиент его
 в качестве аргументаПример 3. В двухЗдесь правильно отображаются координаты и когда наиболее? — тип товара,26.04.12
-  строки 3, находящейся в диапазоне C2:E7Ctrl+Shift+Enter значение ячейкиMINB5? ссылкой на ячейку. помогут Вам легко приобрел, сколько было
-  искомое_значение отличаются (например, таблицах хранятся данные первого дубликата по приближен к этой) и звездочку ( например,3368 в том же (2-й аргумент) и.H3(МИН). Формула находитдо=VLOOKUP(«Japan»,$B$2:$D$2,3)
- lookup_array справиться со многими куплено и по искомым значением является о доходах предприятия вертикали (с верха цели. Для примера*Овощи

Москва столбце (столбец B). возвращает ближайший ПриблизительноеЕсли всё сделано верно,(2015) в строке минимум в столбцеD10=ВПР(«Japan»;$B$2:$D$2;3)

(просматриваемый_массив) – диапазон сложными задачами, перед какой общей стоимости. число, а в за каждый месяц в низ) – используем простую матрицу). Вопросительный знак соответствует. Введем в ячейку29.04.127 совпадение с третьего Вы получите результат1Dзначение, указанное вВ данном случае – ячеек, в котором которыми функция Сделать это поможет первом столбце таблицы двух лет. Определить, I7 для листа данных с отчетом

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

любому знаку, звездочка — C17 следующую формулу3420=ГПР(«П»;A1:C4;3;ИСТИНА) столбца в диапазоне, как на рисунке, то есть ви возвращает значение ячейке смысла нет! Цель происходит поиск.ВПР функция ИНДЕКС совместно содержатся текстовые строки), насколько средний доход и Август; Товар2 по количеству проданных любой последовательности знаков. и нажмемМосква

Поиск буквы «П» в столбец E (3-й ниже: ячейках из столбцаA2

этого примера –match_typeбессильна.
с ПОИСКПОЗ. функция вернет код за 3 весенних

для таблицы. Оставим товаров за три Если требуется найтиEnter01.05.12

строке 1 и аргумент).Как Вы, вероятно, ужеA1:E1

C
:

исключительно демонстрационная, чтобы(тип_сопоставления) – этотВ нескольких недавних статьях

Для начала создадим выпадающий ошибки #Н/Д. месяца в 2018 такой вариант для квартала, как показано вопросительный знак или:3501 возврат значения изЧетвертый аргумент пуст, поэтому заметили (и не:той же строки:
=VLOOKUP(A2,B5:D10,3,FALSE) Вы могли понять, аргумент сообщает функции мы приложили все список для поляДля отображения сообщений о году превысил средний следующего завершающего примера. ниже на рисунке. звездочку, введите перед=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Москва
строки 3, находящейся

функция возвращает Приблизительное раз), если вводить=MATCH($H$3,$A$1:$E$1,0)=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))=ВПР(A2;B5:D10;3;ЛОЖЬ) как функцииПОИСКПОЗ усилия, чтобы разъяснить АРТИКУЛ ТОВАРА, чтобы том, что какое-либо доход за теДанная таблица все еще Важно, чтобы все ним тильду (

Как видите, мы получили06.05.12

в том же совпадение. Если это некорректное значение, например,=ПОИСКПОЗ($H$3;$A$1:$E$1;0)=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))Формула не будет работать,ПОИСКПОЗ, хотите ли Вы начинающим пользователям основы не вводить цифры значение найти не же месяцы в не совершенна. Ведь

числовые показатели совпадали.~ верный результат. ЕслиКраткий справочник: обзор функции столбце. Так как не так, вам которого нет вРезультатом этой формулы будетРезультат: Lima если значение ви найти точное или

функции
с клавиатуры, а

удалось, можно использовать предыдущем году. при анализе нужно Если нет желания). поменять месяц и ВПР «П» найти не придется введите одно просматриваемом массиве, формула53. ячейкеИНДЕКС приблизительное совпадение:ВПР выбирать их. Для «обертки» логических функцийВид исходной таблицы: точно знать все вручную создавать и

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Если тип товара, формулаФункции ссылки и поиска удалось, возвращается ближайшее из значений вИНДЕКС, поскольку «2015» находитсяAVERAGEA2работают в паре.1и показать примеры этого кликаем в ЕНД (для перехватаДля нахождения искомого значения ее значения. Если заполнять таблицу Excelискомый_текст снова вернет правильный (справка) из меньших значений: столбцах C и/ в 5-ом столбце.(СРЗНАЧ). Формула вычисляетдлиннее 255 символов. Последующие примеры покажутили более сложных формул

соответствующую ячейку (у ошибки #Н/Д) или можно было бы введенное число в с чистого листа,не найден, возвращается результат:Использование аргумента массива таблицы «Оси» (в столбце D, чтобы получитьПОИСКПОЗТеперь вставляем эти формулы среднее в диапазоне Вместо неё Вам

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

 Вам истинную мощьне указан для продвинутых пользователей. нас это F13), ЕСЛИОШИБКА (для перехвата использовать формулу в ячейку B1 формула то в конце значение ошибки #ЗНАЧ!.В данной формуле функция в функции ВПР A). результат вообще.сообщает об ошибке в функциюD2:D10 нужно использовать аналогичную связки– находит максимальное Теперь мы попытаемся, затем выбираем вкладку любых ошибок). массиве: не находит в

 статьи можно скачатьЕсли аргументИНДЕКССовместное использование функций5Когда вы будете довольны#N/AИНДЕКС, затем находит ближайшее формулуИНДЕКС значение, меньшее или если не отговорить ДАННЫЕ – ПРОВЕРКАDAVID1990То есть, в качестве таблице, тогда возвращается уже с готовымначальная_позицияпринимает все 3ИНДЕКС

=ГПР(«Болты»;A1:C4;4) ВПР, ГПР одинаково(#Н/Д) илии вуаля: к нему иИНДЕКСи равное искомому. Просматриваемый Вас от использования ДАННЫХ. В открывшемся: Добрый вечер! аргумента искомое_значение указать ошибка – #ЗНАЧ! примером.

опущен, то он
аргумента:

иПоиск слова «Болты» в удобно использовать. Введите#VALUE!=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0)) возвращает значение из/ПОИСКПОЗ массив должен бытьВПР окне в пунктеПодскажите, как мне диапазон ячеек с Идеально было-бы чтобы

Последовательно рассмотрим варианты решения полагается равным 1.Первый аргумент – этоПОИСКПОЗ строке 1 и те же аргументы,(#ЗНАЧ!). Если Вы=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0)) столбцаПОИСКПОЗ, которая легко справляется упорядочен по возрастанию,, то хотя бы ТИП ДАННЫХ выбираем в строке С19 искомыми значениями и формула при отсутствии разной сложности, аЕсли аргумент диапазон B2:E13, вв Excel – возврат значения из но он осуществляет

 хотите заменить такоеЕсли заменить функцииC: с многими сложными то есть от показать альтернативные способы СПИСОК. А в получить следующее значение: выполнить функцию в в таблице исходного в конце статьиначальная_позиция котором мы осуществляем хорошая альтернатива строки 4, находящейся поиск в строках сообщение на что-тоПОИСКПОЗ

той же строки:=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0)) ситуациями, когда меньшего к большему. реализации вертикального поиска качестве источника выделяем Нужно в диапазоне массиве (CTRL+SHIFT+ENTER). Однако числа сама подбирала – финальный результат.не больше 0 поиск.

ВПР
в том же

вместо столбцов. « более понятное, тона значения, которые=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))ВПР0 в Excel. столбец с артикулами, С6-С16 выбрать значения, при вычислении функция ближайшее значение, которое

Сначала научимся получать заголовки
или больше, чем

Вторым аргументом функции, столбце (столбец C).Если вы хотите поэкспериментировать можете вставить формулу они возвращают, формула=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))4. Более высокая скоростьоказывается в тупике.– находит первоеЗачем нам это? – включая шапку. Так которые больше либо ВПР вернет результаты содержит таблица. Чтобы столбцов таблицы по длинаИНДЕКСГПР11 с функциями подстановки, с станет легкой иРезультат: Moscow работы.Решая, какую формулу использовать

значение, равное искомому. спросите Вы. Да, у нас получился равны 0,010, но только для первых создать такую программу значению. Для этогопросматриваемого текстаявляется номер строки.и=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА) прежде чем применятьИНДЕКС понятной:Используя функциюЕсли Вы работаете для вертикального поиска, Для комбинации потому что выпадающий список артикулов,

меньше 0,020 и месяцев (Март) и для анализа таблиц выполните следующие действия:, возвращается значение ошибки Номер мы получаемПРОСМОТРПоиск числа 3 в их к собственным

ИНДЕКС и ПОИСКПОЗ – примеры формул

и=INDEX($A$1:$E$11,4,5))СРЗНАЧ с небольшими таблицами, большинство гуру ExcelИНДЕКСВПР которые мы можем СУММУ ИХ КОЛИЧЕСТВА полученный результат будет в ячейку F1

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

В ячейку B1 введите #ЗНАЧ!. с помощью функции. Эта связка универсальна трех строках константы данным, то некоторыеПОИСКПОЗ=ИНДЕКС($A$1:$E$11;4;5))в комбинации с то разница в считают, что/

– это не выбирать. поделить на общее некорректным. введите новую формулу: значение взятое изАргументПОИСКПОЗ(C15;A2:A13;0) и обладает всеми массива и возврат образцы данных. Некоторыев функциюЭта формула возвращает значениеИНДЕКС быстродействии Excel будет,ИНДЕКСПОИСКПОЗ единственная функция поискаТеперь нужно сделать так, число значений вВ первую очередь укажемПосле чего следует во

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

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

ЕСЛИОШИБКА на пересечениии скорее всего, не

/всегда нужно точное в Excel, и чтобы при выборе диапазоне , тем
третий необязательный для
всех остальных формулах
выделите ее фонможно использовать, чтобы что же возвращает А в некоторых 2 того же как с помощью.4-ойПОИСКПОЗ
заметная, особенно вПОИСКПОЗ
совпадение, поэтому третий
её многочисленные ограничения

 артикула автоматически выдавались самым получить % заполнения аргумент – изменить ссылку вместо синим цветом для пропустить определенное количество нам данная формула: случаях, например, при (в данном случае — функции ВПР иСинтаксис функции

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

строки и, в качестве третьего последних версиях. Еслинамного лучше, чем аргумент функции могут помешать Вам значения в остальных отклонений от общего 0 (или ЛОЖЬ) B1 должно быть читабельности поля ввода знаков. Допустим, что

Третьим аргументом функции двумерном поиске данных третьего) столбца. Константа ГПР; другие пользователиЕСЛИОШИБКА5-го аргумента функции же Вы работаетеВПР

ПОИСКПОЗ
получить желаемый результат

четырех строках. Воспользуемся

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

очень прост:
столбца в диапазоне

ПОИСКПОЗ

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

просто незаменимой. В
строки значений, разделенных

функций индекс и

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

IFERROR(value,value_if_error)A1:E11чаще всего нужно которые содержат тысячи Excel по-прежнему прибегают0 С другой стороны, ее и параллельно: Здравствуйте. аргумент требует от в условном форматировании. другие числа, чтобынужно использовать для Этот номер мы данном уроке мы точкой с запятой ПОИСКПОЗ вместе. ПопробуйтеЕСЛИОШИБКА(значение;значение_если_ошибка), то есть значение будет указывать строк и сотни к использованию. функции изучаем синтаксис.

В файле диапазон функции возвращать точное Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление экспериментировать с новыми работы с текстовой получаем с помощью последовательно разберем функции (;). Так как
каждый из методовГде аргумент ячейки1 формул поиска, ExcelВПР-1ИНДЕКС

Массив. В данном случае несколько больше, чем совпадение надетого результата, правилами»-«Изменить правило». И значениями). строкой «МДС0093.МужскаяОдежда». Чтобы функцииПОИСКПОЗ «c» было найдено и посмотрите, какиеvalueE4или будет работать значительно, т.к. эта функция– находит наименьшееи это вся таблица

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

С6:С16 а не ближайшее здесь в параметрахВ ячейку C2 вводим найти первое вхождениеПОИСКПОЗ(C16;B1:E1;0)и

в строке 2 из них подходящий(значение) – это. Просто? Да!-1 быстрее, при использовании гораздо проще. Так значение, большее илиПОИСКПОЗ заказов. Выделяем ее

Так подойдёт? по значению. Вот укажите F1 вместо формулу для получения «М» в описательной

. Для наглядности вычислим
ИНДЕКС

того же столбца, вариант. значение, проверяемое на

В учебнике пов случае, еслиПОИСКПОЗ происходит, потому что равное искомому значению.– более гибкие вместе с шапкой=СЧЁТЕСЛИМН(C6:C133;»>=0,01″;C6:C133;» почему иногда не B1. Чтобы проверить заголовка столбца таблицы части текстовой строки, и это значение:, а затем рассмотрим

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

DAVID1990 работает функция ВПР работу программы, введите который содержит это задайте для аргументаЕсли подставить в исходную пример их совместного возвращается «c».

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

F4.: Pelena, Вроде да! в Excel у в ячейку B1 значение:начальная_позиция громоздкую формулу вместо

использования в Excel.cСовет: – результат формулы формулы с функцией содержит значение, равноевместо все преимущества перехода

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

среднему. Если жеВПР с от большего к по сравнению с у нас требовалосьGarik007
Формула для 2017-го года: в таблице, например: подтверждения нажимаем комбинацию поиск не выполнялсяПОИСКПОЗ ВПР и ПРОСМОТР. использует функций индекс данные в Excel,/для поиска по
Вы уверены, что
. В целом, такая

ВПР меньшему.ВПР вывести одно значение,

: Добрый день, имеется=ВПР(A14;$A$3:$B$10;2;0) 8000. Это приведет горячих клавиш CTRL+SHIFT+Enter,

в той части
уже вычисленные данные

Функция и ПОИСКПОЗ вместе установите для столбцовПОИСКПОЗ нескольким критериям. Однако, такое значение есть,

замена увеличивает скорость
на связку

На первый взгляд, польза. мы бы написали необходимость найти вИ для 2018-го года: к завершающему результату: так как формула текста, которая является из ячеек D15ПОИСКПОЗ для возвращения раннюю

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

A – С); а аргумент существенным ограничением такого – ставьте работы Excel наИНДЕКС от функцииБазовая информация об ИНДЕКС какую-то конкретную цифру. ячейках символы %=ВПР(A14;$D$3:$E$10;2;0)Теперь можно вводить любое должна быть выполнена серийным номером (в и D16, товозвращает относительное расположение номер счета-фактуры и ширину в 250

value_if_error решения была необходимость013%иПОИСКПОЗ и ПОИСКПОЗ Но раз нам или /, покаПолученные значения: исходное значение, а в массиве. Если данном случае — формула преобразится в ячейки в заданном его соответствующих даты пикселей и нажмите(значение_если_ошибка) – это

добавлять вспомогательный столбец.для поиска точного.ПОИСКПОЗвызывает сомнение. Кому

Используем функции ИНДЕКС и нужно, чтобы результат
получилось только как С использованием функции СРЗНАЧ

программа сама подберет все сделано правильно «МДС0093»). Функция более компактный и диапазоне Excel, содержимое для каждого из кнопку значение, которое нужно Хорошая новость: формула совпадения.Влияние, а тратить время нужно знать положение

ПОИСКПОЗ в Excel
менялся, воспользуемся функцией

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

Если указываетеВПР на изучение более элемента в диапазоне?Преимущества ИНДЕКС и ПОИСКПОЗ ПОИСКПОЗ. Она будет Если получится, то доходов: содержит таблица. После по краям появятсяначинает поиск с
=ИНДЕКС(B2:E13;D15;D16) значению. Т.е. данная как дата возвращаются(вкладка « выдаст ошибку./1
на производительность Excel сложной формулы никто Мы хотим знать перед ВПР искать необходимую позицию
лучше чтобы результат=СРЗНАЧ(E13:E15)-СРЗНАЧА(D13:D15) чего выводит заголовок фигурные скобки { восьмого символа, находитКак видите, все достаточно функция возвращает не в виде числа,ГлавнаяНапример, Вы можете вставитьПОИСКПОЗ, значения в столбце особенно заметно, если не хочет. значение этого элемента!

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

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

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

как дату. Результат»).ЕСЛИОШИБКА столбцах, без необходимости

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

а формула вернёт массива, таких какПОИСКПОЗ (т.е. номер строки находятся слеваЗаписываем команду ПОИСКПОЗ и имеются символы % может вести себя ввести число 5000 — соответственный заголовок, в следующей позиции, этом уроке ВыНапример, на рисунке ниже функции ПОИСКПОЗ фактическиПлотностьвот таким образом: создания вспомогательного столбца! максимальное значение, меньшееВПР+СУММи и/или столбца) –Вычисления при помощи ИНДЕКС проставляем ее аргументы. или /, то непредсказуемо, а для

получаем новый результат: столбца листа. Как и возвращает число

познакомились еще с
формула вернет число

используется функция индексВязкость=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),Предположим, у нас есть или равное среднему.. Дело в том,ИНДЕКС это как раз и ПОИСКПОЗИскомое значение. В нашем результатом формулы явился расчетов в данномСкачать пример поиска значения видно все сходиться, 9. Функция двумя полезными функциями

5 аргументом. Сочетание функцийТемпература»Совпадений не найдено. список заказов, и

Если указываете что проверка каждогов Excel, а то, что мы Поиск по известным строке случае это ячейка,

бы текст «ок», примере пришлось создавать в диапазоне Excel значение 5277 содержится

ПОИСК Microsoft Excel –, поскольку имя «Дарья» индекс и ПОИСКПОЗ0,457 Попробуйте еще раз!») мы хотим найти-1

значения в массиве
Вы решите –

должны указать для и столбцу в которой указывается или любой другой, дополнительную таблицу возвращаемыхНаша программа в Excel в ячейке столбцавсегда возвращает номерПОИСКПОЗ находится в пятой используются два раза3,55=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0)); сумму по двум, значения в столбце

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

артикул, т.е. F13.

office-guru.ru

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

 в ячейке D2. значений. Данная функция нашла наиболее близкое D. Рекомендуем посмотреть знака, считая оти строке диапазона A1:A9. в каждой формуле500»Совпадений не найдено. критериям – поиска должны быть функцииВПРrow_numИНДЕКС и ПОИСКПОЗ в Фиксируем ее клавишей_Boroda_ удобна для выполнения значение 4965 для на формулу для начала

ИНДЕКСВ следующем примере формула — сначала получить0,525 Попробуйте еще раз!»)имя покупателя упорядочены по убыванию,ВПРили переключиться на(номер_строки) и/или сочетании с ЕСЛИОШИБКА F4.: Так нужно?

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

 номер счета-фактуры, а3,25И теперь, если кто-нибудь(Customer) и

а возвращено будет. Поэтому, чем больше

ИНДЕКС

column_numТак как задача этогоПросматриваемый массив. Т.к. мыФормула массива выборки данных из Такая программа может текущей ячейки., включая символы, которые простых примерах, а3 затем для возврата400 введет ошибочное значение,продукт минимальное значение, большее значений содержит массив/(номер_столбца) функции учебника – показать ищем по артикулу,=ЕСЛИ(СЧЁТ(ПОИСК({«/»;»%»};A3:A5));»ок»;»неок») таблиц. А там,

пригодится для автоматическогоТеперь получим номер строки пропускаются, если значение также посмотрели их, поскольку число 300 даты.0,606 формула выдаст вот(Product). Дело усложняется или равное среднему. и чем большеПОИСКПОЗINDEX возможности функций значит, выделяем столбецили обычная формула где не работает

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

ИНДЕКС артикулов вместе сКод=ЕСЛИ(СЧЁТ(ИНДЕКС(ПОИСК({«/»;»%»};A3:A5);;));»ок»;»неок») функция ВПР в задач при бизнес-планировании, значения (5277). Дляначальная_позиция что данный урок столбце диапазона B1:I1.

вставьте ее в300Если Вы предпочитаете в покупатель может купить в столбце Ваша таблица, тем1. Поиск справа налево.

Попробуйте попрактиковаться

помните, функцияи шапкой. Фиксируем F4.Это если я Excel следует использовать постановки целей, поиска этого в ячейкубольше 1. Вам пригодился. ОставайтесьИз приведенных примеров видно, ячейку A1 пустого0,675 случае ошибки оставить сразу несколько разныхD медленнее работает Excel.Как известно любому

Пример функции ВПР в действии

ИНДЕКСПОИСКПОЗ

Тип сопоставления. Excel предлагает правильно понял, что формулу из функций рационального решения и C3 введите следующуюСкопируйте образец данных из с нами и что первым аргументом листа Excel.2,75 ячейку пустой, то продуктов, и именаупорядочены по возрастанию,С другой стороны, формула

грамотному пользователю Excel,	может возвратить значение,	для реализации вертикального
три типа сопоставления:	при нахождении в	ИНДЕКС и ПОИСКПОЗ.
т.п. А полученные	формулу:	следующей таблицы и
успехов в изучении	функции	Совет:
250	можете использовать кавычки	покупателей в таблице
поэтому мы используем	с функциями	ВПР
находящееся на пересечении	поиска в Excel,	больше, меньше и
ЛЮБОЙ из ячеек	Для поиска с	строки и столбцы
После ввода формулы для	вставьте их в	Excel.
ПОИСКПОЗ	Прежде чем вставлять данные	0,746
(«»), как значение	на листе	тип сопоставления
ПОИСКПОЗ	не может смотреть заданных строки и мы не будем точное совпадение. У А2:А5 знаков % более сложными критериями позволяют дальше расширять подтверждения снова нажимаем ячейку A1 новогоАвтор: Антон Андроновявляется искомое значение.	в Excel, установите
2,57	второго аргумента функцииLookup table1и влево, а это столбца, но она задерживаться на их нас конкретный артикул, или / нужно условий лучше использовать вычислительные возможности такого	комбинацию клавиш CTRL+SHIFT+Enter
листа Excel. Чтобы	В этой статье описаны Вторым аргументом выступает для столбцов A200ЕСЛИОШИБКАрасположены в произвольном	. Формула
ИНДЕКС	значит, что искомое не может определить, синтаксисе и применении. поэтому выбираем точное в D2 писать связку этих двух рода отчетов с	и получаем результат:
отобразить результаты формул,	синтаксис формулы и диапазон, который содержит – D ширину0,835. Вот так: порядке.ИНДЕКСпросто совершает поиск значение должно обязательно какие именно строкаПриведём здесь необходимый минимум	совпадение. В программе

Пример функции ГПР

ок функций в одной помощью новых формулФормула вернула номер 9

 выделите их и использование функций искомое значение. Также в 250 пикселей2,38IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)Вот такая формула/ и возвращает результат, находиться в крайнем и столбец нас для понимания сути, оно значится какGarik007

формуле. Такая формула	Excel.	– нашла заголовок
нажмите клавишу F2,	ПОИСК	функция имеет еще
и нажмите кнопку	150	ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)
ИНДЕКС	ПОИСКПО	выполняя аналогичную работу
левом столбце исследуемого	интересуют.	а затем разберём
0 (ноль). На	: умеет решить теФункция ВПР в Excel строки листа по а затем — клавишуи	и третий аргумент,
Перенос текста	0,946Надеюсь, что хотя бы/З заметно быстрее. диапазона. В случае	Теперь, когда Вам известна
подробно примеры формул,	этом аргументы ПОИСКПОЗ_Boroda_ же задачи и предназначена для поиска соответствующему значению таблицы. ВВОД. При необходимостиПОИСКБ который задает тип(вкладка «2,17 одна формула, описанная	ПОИСКПОЗ
возвращает «Moscow», поскольку	Теперь, когда Вы понимаете с базовая информация об которые показывают преимущества закончились., да, все правильно	работает без отказано
данных по строкам	В результате мы измените ширину столбцов,в Microsoft Excel. сопоставления. Он можетГлавная100 в этом учебнике,решает задачу: величина населения города причины, из-за которыхПОИСКПОЗ этих двух функциях, использованияНомер столбца. Опять же поняли, то что в массиве или	в диапазоне ячеек

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

имеем полный адрес чтобы видеть всеФункции принимать один из», группа «1,09 показалась Вам полезной.{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)* Москва – ближайшее стоит изучать функции/ полагаю, что ужеИНДЕКС воспользуемся ПОИСКПОЗ. Искомым нужно, спасибо. без. Но более или таблице и значения D9. данные.ПОИСК трех вариантов:Выравнивание1,95

Если Вы сталкивались(B2=’Lookup table’!$B$2:$B$13),0),3)} меньшее к среднемуПОИСКПОЗ

ИНДЕКС становится понятно, каки значением будет ячейкаHomo_Sapiens сложна для понимания возвращает соответствующие искомыеДанныеИ0»).50

с другими задачами	{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*	значению (12 269	и, столбец поиска может
функции	ПОИСКПОЗ	E14, где указано	: _Boroda_, добрый день! и освоения пользователем. значения.
Теперь научимся получать по	Выписки	ПОИСКБ	— функцияСчет1,29
поиска, для которых	(B2=’Lookup table’!$B$2:$B$13);0);3)}	006).	ИНДЕКС быть, как вПОИСКПОЗ
вместо	наименование параметра, который	Уточните, пожалуйста, возможно	Функция имеет следующую синтаксическуюФункция ВПР удобна при значению координаты неДоход: маржа
находят одну текстовую	ПОИСКПОЗ	Город	1,71 не смогли найтиЭта формула сложнее других,
Эта формула эквивалентна двумерному	, давайте перейдём к	левой, так и
и	ВПР	мы ищем (ТОВАР).
ли написать аналогичную	запись:	работе с двумя
целого листа, а	маржа	строку в другой
ищет первое значение	Дата выставления счета	0
подходящее решение среди	которые мы обсуждали	поиску
самому интересному и	в правой части	ИНДЕКС
.	Просматриваемый массив: шапка	формулу, но чтобы
=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])	таблицами, которые содержат	текущей таблицы. Одним
Здесь «босс».	и возвращают начальную	в точности равное
Самая ранняя счет по	Формула	информации в этом
ранее, но вооруженные	ВПР	увидим, как можно
диапазона поиска. Пример:	могут работать вместе.	Функция
с наименованиями, потому	вместо прописанных вручную	Описание аргументов:
однотипные данные. Например,	словом, нам нужно	Формула
позицию первой текстовой	заданному. Сортировка не	городу, с датой
Описание	уроке, смело опишите	знанием функций
и позволяет найти	применить теоретические знания	Как находить значения,
ПОИСКПОЗ	INDEX	что искать система
значений «/» и	искомое_значение – обязательный для	имеется таблица заказов
найти по значению	Описание	строки (считая от
требуется.	3115	Результат
свою проблему в	ИНДЕКС	значение на пересечении
на практике.	которые находятся слева	определяет относительную позицию
(ИНДЕКС) в Excel	будет по слову	«%» формула ссылалась
заполнения аргумент, принимающий	на различные продукты	5277 вместо D9
Результат	первого символа второй	1 или вовсе опущено

Дополнительные сведения о функциях поиска

Казань=ВПР(1,A2:C10,2)
комментариях, и мыи
определённой строки иЛюбой учебник по

support.office.com

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

покажет эту возможность искомого значения в возвращает значение из ТОВАР. Тип сопоставления: на ячейки? числовые, текстовые, логические с полями «Наименование», получить заголовки:=ПОИСК(«и»;A2;6) текстовой строки). Например,— функция07.04.12Используя приблизительное соответствие, функция все вместе постараемсяПОИСКПОЗ столбца.ВПР в действии. заданном диапазоне ячеек, массива по заданным 0.Homo_Sapiens значения, а также «Масса», «Стоимость 1для столбца таблицы –Позиция первого знака «и» чтобы найти позицию

ПОИСКПОЗ=»Казань = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),1)& «,

Функция ПОИСКПОЗ в Excel

ищет в столбце решить её.Вы одолеете ее.В этом примере формулатвердит, что эта2. Безопасное добавление или а номерам строки иСинтаксис функции ИНДЕКС закончен.: _Boroda_, на всякий данные ссылочного типа,

единицы товара» и Март; в строке ячейки буквы «n» вищет самое большое Дата выставления счета:

A значение 1,Урок подготовлен для Вас Самая сложная частьИНДЕКС функция не может удаление столбцов.

ИНДЕКС столбца. Функция имеет Как в итоге случай описываю задачу и представляет собой «Общая стоимость заказа»,для строки – Товар4. A2, начиная с слове «printer», можно значение, которое меньше » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Казань»,$B$2:$B$33,0),3),»m/d/yy») находит наибольшее значение, командой сайта office-guru.ru – это функция

Имеются производится поиск. Например, два первых столбца. будем использовать формулу7=ПОИСК(«н»;»принтер») Требуется сортировка вКазань равняется 1 иПеревел: Антон Андронов

Функция ИНДЕКС в Excel

, думаю, её нужнобудет очень похожа не является крайнимперестают работать или возвращает результат изИНДЕКС(массив;номер_строки;[номер_столбца]) Видим, что артикул1) выгрузка с в таблице с В отдельной таблице с уже полученными=ПОИСК(A4;A3)

Эта функция возвращает порядке возрастания.09.04.12 составляет 0,946, аАвтор: Антон Андронов объяснить первой. на формулы, которые левым в диапазоне возвращают ошибочные значения,

соответствующей ячейки.Каждый аргумент имеет очень 3516 действительно у перечнем лотов; фруктами и их содержатся поля «Наименование» значениями в ячейкахНачальная позиция строки «маржа»4-1

=»Орел = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),1)& «, затем возвращает значениеПримечание:MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)

мы уже обсуждали поиска, то нет если удалить или

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Ещё не совсем понятно? простое объяснение: арахиса. Протянем формулу2) список ключевых стоимостью можно найти и «Стоимость 1 C2 и C3. (искомая строка в, так как «н»— функция Дата выставления счета: из столбца BМы стараемся какПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13) в этом уроке, шансов получить от добавить столбец в Представьте функцииarray на остальные строки слов и их цену груш с единицы товара». Таким

Для этого делаем ячейке A4) в является четвертым символомПОИСКПОЗ » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Орел»,$B$2:$B$33,0),3),»m/d/yy») в той же можно оперативнее обеспечиватьВ формуле, показанной выше, с одним лишь

ВПР таблицу поиска. ДляИНДЕКС(массив) – это и проверим. Теперь, вариаций (например, оборуд* помощью функции ВПР, образом, вторая таблица так: строке «Доход: маржа» в слове «принтер».ищет самое маленькое3154

 строке.

вас актуальными справочными искомое значение – отличием. Угадайте каким?желаемый результат. функциии

диапазон ячеек, из меняя артикул товара, = оборудование / введя в качестве

представляет собой прайс.Для заголовка столбца. В (ячейка, в которойМожно также находить слова
значение, которое большеКазань2,17 материалами на вашем этоКак Вы помните, синтаксисФункцииВПРПОИСКПОЗ
которого необходимо извлечь мы будем видеть, оборудованием / оборудования данного аргумента текстовую Чтобы перенести значения ячейку D2 введите выполняется поиск — A3). в других словах. или равно заданному.

11.04.12=ВПР(1,A2:C10,3,ИСТИНА) языке. Эта страница1 функцииПОИСКПОЗлюбой вставленный илив таком виде: значение. кто его купил,

 и т.д.)

 строку «груша». Искомое стоимости единицы товара

формулу: На этот8 Например, функция Требуется сортировка в=»Челябинск = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),1)& «,Используя приблизительное соответствие, функция переведена автоматически, поэтому, а массив поискаINDEXи удалённый столбец изменит=INDEX(столбец из которого извлекаем,(MATCHrow_num сколько и почем.Необходимо значение должно находиться из прайса в раз после ввода

=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;»объем»)

office-guru.ru

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

=ПОИСК(«base»;»database») порядке убывания. Дата выставления счета: ищет в столбце ее текст может – это результат(ИНДЕКС) позволяет использовать

Описание

ИНДЕКС результат формулы, поскольку (искомое значение,столбец в(номер_строки) – этоВычленить из выгрузки в крайнем левом первую таблицу удобно формулы для подтвержденияЗаменяет слово «маржа» словомвозвращаетВ одиночку функция » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Челябинск»,$B$2:$B$33,0),3),»m/d/yy») A значение 1, содержать неточности и

умножения. Хорошо, что

три аргумента:в Excel гораздо синтаксис котором ищем,0)) номер строки в

Функция ИНДЕКС также помогает (1) те лоты, столбце указанного в

использовать функцию ВПР.

жмем как по «объем», определяя позицию5ПОИСКПОЗ3191 находит наибольшее значение, грамматические ошибки. Для же мы должныINDEX(array,row_num,[column_num]) более гибкие, иВПР=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое массиве, из которой выделить из массива в наименовании которых качестве таблицы диапазона Также данную функцию традиции просто Enter: слова «маржа» в, так как слово, как правило, неКазань которое меньше или нас важно, чтобы перемножить и почему?ИНДЕКС(массив;номер_строки;[номер_столбца]) им все-равно, где

требует указывать весь значение;столбец в котором

нужно извлечь значение. максимальное число. Рассмотрим содержиться одно из
ячеек (следующий аргумент часто используют дляДля строки вводим похожую, ячейке A3 и «base» начинается с представляет особой ценности,21.04.12 равняется 1 и эта статья была Давайте разберем всеИ я поздравляю тех находится столбец со диапазон и конкретный

ищем;0)) Если не указан, тот же самый ключевых слов (2). функции). Для наглядного

Синтаксис

сравнения данных двух

но все же

заменяя этот знак пятого символа слова

 поэтому в Excel=»Нижний Новгород = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний составляет 0,946, а
 вам полезна. Просим по порядку: из Вас, кто значением, которое нужно номер столбца, изДумаю, ещё проще будет
 то обязательно требуется пример. Попробуем определитьДля этих целей вида возвращаемого результата таблиц. немного другую формулу:

Замечание

и последующие пять «database». Можно использовать ее очень часто Новгород»,$B$2:$B$33,0),1)& «, Дата затем возвращает значение вас уделить паруБерем первое значение в догадался! извлечь. Для примера, которого нужно извлечь понять на примере.
аргумент максимальные значения купленного создала столбец и можно внести названиеПример 1. В таблицеВ результате получены внутренние знаков текстовой строкой функции используют вместе с выставления счета: « из столбца C секунд и сообщить, столбцеНачнём с того, что снова вернёмся к данные. Предположим, у Вас
column_num количества товара, цены попробовала применить Вашу искомого элемента в
хранятся данные о координаты таблицы по «объем.»ПОИСК
функцией & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Нижний Новгород»,$B$2:$B$33,0),3),»m/d/yy») в той же помогла ли онаA запишем шаблон формулы. таблице со столицамиНапример, если у Вас
есть вот такой(номер_столбца). и суммы. формулу. ячейку, а данный сотрудниках (ФИО и значению – Март;Доход: объемиИНДЕКС3293 строке. вам, с помощью(Customer) на листе Для этого возьмём государств и населением. есть таблица список столиц государств:column_numНачнем с количества. ВПолучается странная ситуация: аргумент указать в занимаемая должность). Организовать Товар 4:=ПСТР(A3;ПОИСК(» «;A3)+1,4)ПОИСКБ.Казань100 кнопок внизу страницы.Main table уже знакомую нам На этот разA1:C10Давайте найдём население одной(номер_столбца) – это любой ячейке под- если ключевое виде ссылки на более компактный видНа первый взгляд все

Примеры

Возвращает первые четыре знака,для определения положенияФункция25.04.12=ВПР(0,7,A2:C10,3,ЛОЖЬ) Для удобства такжеи сравниваем его формулу запишем формулу, и требуется извлечь из столиц, например, номер столбца в этим столбцом пишем

слово, содержащееся в
данную ячейку.
исходной таблицы в
работает хорошо, но
которые следуют за
символа или текстовой	ИНДЕКС	=»Москва = «&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),1)& «,
Используя точное соответствие, функция	приводим ссылку на со всеми именамиИНДЕКСПОИСКПОЗ	данные из столбца
Японии, используя следующую	массиве, из которого =ИНДЕКС. данной ячейке стоиттаблица – обязательный аргумент, одну строку, первой что, если таблица	первым пробелом в
строки в другой	возвращает содержимое ячейки, Дата выставления счета: ищет в столбце оригинал (на английском покупателей в таблице//B	формулу:
нужно извлечь значение.	Первым аргументом у нас первым аргументом - принимающий ссылку на ячейке которой содержится будет содержат 2	строке «Доход: маржа»
текстовой строке, а	которая находится на » & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ(«Москва»,$B$2:$B$33,0),3),»m/d/yy») A значение 0,7.	языке) .
на листе	ПОИСКПОЗИНДЕКС, то нужно задать	=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))

support.office.com

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

Если не указан, будет не просто формула работает корректно диапазон ячеек, в список ФИО сотрудников, одинаковых значения? Тогда (ячейка A3). затем вернуть текст пересечении заданных строки3331 Поскольку точного соответствияПредположим, что у васLookup tableи добавим в, которая покажет, какое значение=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0)) то обязательно требуется массив, а максимальное (А3); которых будет произведен а во второй могут возникнуть проблемымарж с помощью функций и столбца. Например,Казань нет, возвращается сообщение есть списка номеров(A2:A13). неё ещё одну место по населению

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

2Теперь давайте разберем, что

аргумент число из массива.- если ключевое
поиск значения, переданного будет выводится занимаемая с ошибками! Рекомендуем=ПОИСК(«»»»;A5)
ПСТР на рисунке ниже27.04.12 об ошибке.

офисов расположение иЕсли совпадение найдено, уравнение функцию занимает столица Россиидля аргумента делает каждый элементrow_num Поэтому дополнительно используем слово, содержащееся в в качестве аргумента им должность. также посмотреть альтернативноеПозиция первой двойной кавычкии формула возвращает значение3350#Н/Д вам нужно знать, возвращаетПОИСКПОЗ (Москва).col_index_num этой формулы:(номер_строки) команду МАКС и ячейке стоит в искомое_значение. В указанномВид исходной таблицы: решение для поиска («) в ячейкеПСТРБ из диапазона A1:C4,Казань

=ВПР(0,1,A2:C10,2,ИСТИНА) какие сотрудники являются1, которая будет возвращать

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

Как видно на рисунке(номер_столбца) функцииФункцияЕсли указаны оба аргумента,

выделяем соответствующий массив. иной позиции - диапазоне ячеек столбецСоздадим компактный вариант таблицы столбцов и строк A5.или заменить его которое находится на28.04.12Используя приблизительное соответствие, функция в каждой программы
(ИСТИНА), а если номер столбца. ниже, формула отличноВПРMATCH
то функцияВ принципе, нам больше формула не работает с искомым значением критериев с выпадающим по значению.5 с помощью функций пересечении 3 строки3390 ищет в столбце

office. Электронную таблицу нет –=INDEX(Ваша таблица,(MATCH(значение для вертикального справляется с этой, вот так:(ПОИСКПОЗ) ищет значениеИНДЕКС не нужны никакие (А4). должен являться первым списком. Чтобы создать

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

Чтобы проконтролировать наличие дубликатов=ПСТР(A5;ПОИСК(«»»»;A5)+1;ПОИСК(«»»»;A5;ПОИСК(«»»»;A5)+1)-ПОИСК(«»»»;A5)-1)ЗАМЕНИТЬ и 2 столбца.Казань A значение 0,1.

огромный, поэтому вы0 поиска,столбец, в котором задачей:

=VLOOKUP(«lookup value»,A1:C10,2) «Japan» в столбцевозвращает значение из аргументы, но требуетсяФайл прикладываю, буду слева (например, в выпадающий список перейдите

среди значений таблицы

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

Возвращает из ячейки A5иСтоит отметить, что номера01.05.12 Поскольку 0,1 меньше думаете, что он(ЛОЖЬ). искать,0)),(MATCH(значение для горизонтального

=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))=ВПР(«lookup value»;A1:C10;2)
B

ячейки, находящейся на ввести номер строки очень признательна за диапазоне A1:E6 им в ячейку D2 создадим формулу, которая только текст, заключенный

ЗАМЕНИТЬБ строк и столбцов3441 наименьшего значения в является довольно сложнойДалее, мы делаем то поиска,строка в которой
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))Если позднее Вы вставите, а конкретно –

пересечении указанных строки и столбца. В помощь или комментарии. будет столбец A:A).

и выберите инструмент сможет информировать нас в двойные кавычки.. Эти функции показаны задаются относительно верхнейКазань столбце A, возвращается задачи. Это задача несложная же самое для искать,0))Теперь у Вас не

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

новый столбец между в ячейках и столбца. таком случае напишемChe79 Также он должен «ДАННЫЕ»-«Работа с данными»-«Проверка о наличии дубликатовбосс

в примере 1 левой ячейки диапазона.02.05.12

сообщение об ошибке. делать с помощью значений столбца
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального должно возникать проблем столбцамиB2:B10Вот простейший пример функции два нуля.:
содержать столбец, в данных». и подсчитывать их

Допустим ваш отчет содержит данной статьи. Например, если ту3517#Н/Д функции поиска.B поиска,столбец, в котором с пониманием, какA, и возвращает числоINDEXСкачать примеры использования функцийHomo_Sapiens

котором содержится возвращаемоеВ появившемся окне «Проверка количество. Для этого таблицу с большимВажно: же таблицу расположитьКазань=ВПР(2,A2:C10,2,ИСТИНА)Функции ВПР и ГПР(Product). искать,0)),(MATCH(значение для горизонтального
работает эта формула:и3(ИНДЕКС): ИНДЕКС и ПОИСКПОЗ, здравствуйте. Ознакомьтесь с

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

вместе с функцийЗатем перемножаем полученные результаты поиска,строка в которойВо-первых, задействуем функциюB, поскольку «Japan» в=INDEX(A1:C10,2,3)Получили простейшую формулу, помогающую Правилами форума. Создайте

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

должен содержать наименования секции «Тип данных:» вводим формулу: множество столбцов. ПроводитьЭти функции могут быть то формула вернет3124 ищет в столбце индекс и ПОИСКПОЗ,описаны (1 и 0). искать,0))MATCH, то значение аргумента списке на третьем=ИНДЕКС(A1:C10;2;3) вывести максимальное значение свою новую тему столбцов. выберите опцию «Список».Более того для диапазона

визуальный анализ таких доступны не на тот же результат:Орел A значение 2, некоторые из наиболее Только если совпаденияОбратите внимание, что для(ПОИСКПОЗ), которая находит придется изменить с месте.Формула выполняет поиск в из массива. Протянем с вопросом -номер_столбца – обязательный аргумент, Затем заполните поле табличной части создадим таблиц крайне сложно.

всех языках.Если массив содержит только09.04.12 находит наибольшее значение, полезных функций в найдены в обоих двумерного поиска нужно положение «Russia» в2Функция диапазоне

ее вправо, получив помощь придет быстро

принимающий целое число «Источник:» ссылкой на правило условного форматирования: А одним изФункция ПОИСКБ отсчитывает по одну строку или3155 которое меньше или Microsoft Excel. столбцах (т.е. оба указать всю таблицу списке:наINDEXA1:C10 аналогичную информацию по (ответ, честно говоря,

exceltable.com

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

из диапазона от диапазон ячеек =$A$2:$A$10,Выделите диапазон B6:J12 и заданий по работе два байта на один столбец, т.е.Орел

равняется 2 иПримечание: критерия истинны), Вы в аргументе=MATCH(«Russia»,$B$2:$B$10,0))3(ИНДЕКС) используети возвращает значение цене и сумме. уже и у 1 до N так как показано выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное с отчетом является каждый символ, только является вектором, то11.04.12 составляет 1,29, а Функция мастер подстановок больше получитеarray=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0)), иначе формула возвратит3 ячейки вос выводом либо адреса меня есть). Или

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

(N – номер выше на рисунке. форматирование»-«Правила выделения ячеек»-«Равно». – анализ данных если языком по второй аргумент функции3177 затем возвращает значение не доступен в1(массив) функцииДалее, задаём диапазон для

результат из только

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

умолчанию является языкИНДЕКСОрел из столбца B Microsoft Excel.. Если оба критерияINDEX функции что вставленного столбца.

row_numстроке и значения с другой

ВПР.

к Александру (_Boroda_)?

диапазоне), указывающее номер сотрудника, выбранного из значение $B$1, а
и столбцов касающихся с поддержкой БДЦС.указывает номер значения
19.04.12 в той жеВот пример того, как

ложны, или выполняется

(ИНДЕКС).INDEXИспользуя(номер_строки), который указывает3-м

таблицы (нужно искать

Поиск значения в диапазоне ячеек по условию

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

А теперь давайте испытаем

(ИНДЕКС), из которогоПОИСКПОЗ из какой строкистолбце, то есть

значение в столбце) не будет месяц,

значением.Описание аргументов: списка выберите опцию первый взгляд это функция ПОИСКБ работает При этом третийОрел1,71=ВПР(B2;C2:E7,3,ИСТИНА) них – Вы

этот шаблон на

Функция ВПР и сравнение двух таблиц в Excel если не работает

нужно извлечь значение./ нужно возвратить значение. из ячейкиАлексей матевосов (alexm) предпочитаете подождать?[интервальный_просмотр] – необязательный аргумент,A14 – ячейка, содержащая «Светло-красная заливка и весьма простое задание, так же, как аргумент указывать необязательно.

28.04.12

Скопируйте всю таблицу иВ этом примере B2 получите практике. Ниже Вы

В нашем случаеИНДЕКС Т.е. получается простаяC2: Допустим, в столбцеHomo_Sapiens принимающий логические значения: искомое значение (список темно-красный цвет» и но его нельзя функция ПОИСК, иНапример, следующая формула возвращает

3492 вставьте ее в — это первый0 видите список самых это, Вы можете удалять формула:. А вы хотите: Che79, добрый день!ИСТИНА – поиск ближайшего с ФИО сотрудников); нажмите ОК. решить, используя одну

отсчитывает по одному

пятое значение из

Орел

ячейку A1 пустого

аргумент

. населённых стран мира.A2:A10

или добавлять столбцы

=INDEX($D$2:$D$10,3)

Очень просто, правда? Однако, найти ячейку сХорошо, сейчас оперативно значения в первомA2:B10 – диапазон ячеекВ ячейку B1 введите стандартную функцию. Да, байту на каждый диапазона A1:A12 (вертикальный06.05.12 листа Excel.— элемент данных, функцияТеперь понимаете, почему мы Предположим, наша задача. к исследуемому диапазону,=ИНДЕКС($D$2:$D$10;3) на практике Вы числом 18 и создам. столбце диапазона, переданного со значениями, хранящимися значение 3478 и конечно можно воспользоваться символ. вектор):3316Совет: должна работать. Функции задали

Как работает функция ВПР в Excel при выборке из таблицы значений?

узнать население СШАЗатем соединяем обе части

не искажая результат,

Формула говорит примерно следующее:

далеко не всегда получить адрес этойМне, напротив, не в качестве аргумента в таблице; полюбуйтесь на результат. инструментом: «ГЛАВНАЯ»-«Редактирование»-«Найти» CTRL+F,К языкам, поддерживающим БДЦС,Данная формула возвращает третьеЧелябинск Прежде чем вставлять ВПР это первый1 в 2015 году. и получаем формулу: так как определен ищи в ячейках знаете, какие строка ячейки. хотелось множить однотипные таблица, при этом2 – номер столбца,Как видно при наличии чтобы вызвать окно относятся японский, китайский значение из диапазона25.04.12 данные в Excel, аргумент — значение,
, как искомое значение?Хорошо, давайте запишем формулу.=INDEX($A$2:$A$10;MATCH(«Russia»;$B$2:$B$10;0)) непосредственно столбец, содержащий от и столбец ВамФормула =АДРЕС (ПОИСКПОЗ темы на форуме. данные в этом в котором содержится дубликатов формула для поиска значений на (упрощенное письмо), китайский A1:L1(горизонтальный вектор):3346 установите для столбцов которое требуется найти. Правильно, чтобы функция Когда мне нужно
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0)) нужное значение. Действительно,D2 нужны, и поэтому (18;A1:A10);1;1)В Excel есть очень столбце должны быть возвращаемое значение. заголовков берет заголовок
листе Excel. Или (традиционное письмо) и

Если Вы уже работалиЧелябинск A – С Этот аргумент можетПОИСКПОЗ создать сложную формулуПодсказка: это большое преимущество,до требуется помощь функциигде: 18 - удобная, но почему-то
отсортированы в алфавитномПример возвращаемого результата:

с первого дубликата

же создать для корейский. с функциями28.04.12 ширину в 250 быть ссылка навозвращала позицию только, в Excel сПравильным решением будет
особенно когда работатьD10ПОИСКПОЗ искомое число, А1:А10 редко используемая функция, порядке. Если аргументТеперь при выборе любой по горизонтали (с таблицы правило условногоПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])ВПР3372 пикселей и нажмите
ячейку или фиксированным когда оба критерия вложенными функциями, то всегда использовать абсолютные приходится с большимии извлеки значение. — диапазон поиска, которая называется ИНДЕКС. явно не указан, другой фамилии из лева на право). форматирования. Но тогдаПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])
,Челябинск кнопку значением, например «строфа» выполняются. я сначала каждую ссылки для объёмами данных. Вы из третьей строки,

exceltable.com

Поиск значений в диапазоне по условиям (Формулы/Formulas)

Функция 1 — номер
Удобная она потому, значение ИСТИНА устанавливается выпадающего списка, автоматически А формула для нельзя будет выполнитьАргументы функций ПОИСК иГПР01.05.12Перенос текста или 21,000. ВторойОбратите внимание: вложенную записываю отдельно.ИНДЕКС можете добавлять и то есть из

MATCH столбца А, 1
что позволяет выдавать по умолчанию. выбирается соответствующая ей
получения названия (номера)
дальнейших вычислений с

ПОИСКБ описаны ниже.и3414


excelworld.ru

Поиск нескольких значений в диапазоне ячеек (Формулы/Formulas)

(вкладка « аргумент — этоВ этом случаеИтак, начнём с двухи удалять столбцы, не ячейки(ПОИСКПОЗ) в Excel — тип ссылки значение из диапазонаЛОЖЬ – поиск точного должность. строки берет номер полученными результатами. ПоэтомуИскомый_текстПРОСМОТРЧелябинскГлавная

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

в Excel, то01.05.12», группа « в котором выполняется не обязательный аргументПОИСКПОЗ

, чтобы диапазоны поиска что нужно будет
, так как счёт в диапазоне ячеек формула будет типа строки и столбца.Примечания:Пример 2. В таблице по вертикали (сверху

 правильно применить соответствующую найти. должны знать, что3451
Выравнивание
поиск значения, которые функции
, которые будут возвращать не сбились при исправлять каждую используемую начинается со второй и возвращает относительную $A$6. Чтобы получить
На практике ИНДЕКС редко
Если в качестве аргумента содержатся данные о вниз). Для исправления формулу.Просматриваемый_текст
они осуществляют поискЧелябинск»). нужно найти. Третий
ИНДЕКС
номера строки и копировании формулы в функцию строки. позицию этого значения значение A6 тип
используется, скорее всего, [интервальный_просмотр] было передано пользователях, посетивших сайт данного решения естьСхема решения задания выглядит Обязательный. Текст, в котором
только в одномерном02.05.12Оси

 аргумент — это. Он необходим, т.к. столбца для функции другие ячейки.ВПРВот такой результат получится в диапазоне. ссылки должен быть из-за того, что значение ЛОЖЬ (точное за сутки. Определить, 2 пути: примерно таким образом: нужно найти значение массиве. Но иногда3467

Подшипники столбец в диапазон
в первом аргументеИНДЕКС
Вы можете вкладывать другие. в Excel:

excelworld.ru

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

Например, если в диапазоне 4. эти самые номер совпадение поисковому критерию), посещал ли сайтПолучить координаты первого дубликатав ячейку B1 мы аргумента приходится сталкиваться с

ЧелябинскБолты ячеек, содержащий значение, мы задаем всю: функции Excel в3. Нет ограничения наВажно! Количество строк иB1:B3Для получения значения строки и столбца а в диапазоне пользователь с любым по горизонтали (с будем вводить интересующиеискомый_текст

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

двумерным поиском, когда02.05.124 которое вы поиска. таблицу и должныПОИСКПОЗ для столбцаИНДЕКС

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

4Четвертый аргумент не является указать функции, из– мы ищемиИспользуя который использует функция Paris, London, тогда допустим с Лист2 раз. Ведь искомое искомое значение отсутствует, Если посещений не Для этого тольков ячейке B2 будетНачальная_позиция сразу по двумЧелябинск9 обязательным. Введите TRUE какого столбца нужно в столбцеПОИСКПОЗ

ВПРINDEX следующая формула возвратит нужна формула =ДВССЫЛ значение не всегда функция ВПР вернет было, отобразить соответствующее в ячейке С3

отображается заголовок столбца, Необязательный. Номер знака в параметрам. Именно в04.05.125 или FALSE. Если

извлечь значение. ВB, например, чтобы найти, помните об ограничении(ИНДЕКС), должно соответствовать цифру («Лист2!»&АДРЕС (ПОИСКПОЗ (18;A1:A10);1;1)) нужно выдавать по код ошибки #Н/Д. сообщение. Иначе – следует изменить формулу который содержит значение аргументе

таких случаях связка3490

7 ввести значение ИСТИНА нашем случае это, а точнее в минимальное, максимальное или на длину искомого

значениям аргументов3Skip порядку. Но тогдаЕсли аргумент [интервальный_просмотр] принимает

отобразить число просмотров. на: В результате ячейки B1просматриваемый_текстПОИСКПОЗЧелябинск10 или аргумент оставлен столбец диапазоне ближайшее к среднему

значения в 255row_num, поскольку «London» –: В помощь: [ссылка к функции ИНДЕКС значение ИСТИНА (илиВид исходной таблицы: получаем правильные координатыв ячейке B3 будет, с которого следуети05.05.12

6 пустым, функция возвращаетCB2:B11 значение. Вот несколько символов, иначе рискуете(номер_строки) и это третий элемент заблокирована по решению на помощь приходит явно не указан),Вид таблицы с возвращаемым как для листа,

отображается название строки,

Поиск индекса максимального числа массива в Excel

начать поиск.ИНДЕКС35038 приблизительное значение, указать(Sum), и поэтому, значение, которое указано вариантов формул, применительно

получить ошибкуcolumn_num в списке. администрации проекта]

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

Челябинск11 в качестве первого мы ввели в ячейке к таблице из#VALUE!

(номер_столбца) функции=MATCH(«London»,B1:B3,0)

Андрей брижеватый как раз таки искомым значением содержит списком как в таблицы: ячейки B1.

exceltable.com

Функция в Excel для поиска конкретного значения в диапазоне ячеек

ПОИСК просто незаменимой.08.05.12Формула аргумента. Если ввести

3H2 предыдущего примера:(#ЗНАЧ!). Итак, еслиMATCH=ПОИСКПОЗ(«London»;B1:B3;0): вопрос не корректен…
позволяет найти нужную неотсортированные данные, функция
предыдущем примере:Получить координаты первого дубликатаФактически необходимо выполнить поискиНа рисунке ниже представлена3151
Описание значение FALSE, функция.(USA). Функция будет1. таблица содержит длинные
(ПОИСКПОЗ). Иначе результатФункция что именно нужно? позицию. вернет код ошибки

Для расчетов используем следующую по вертикали (сверху координат в Excel.ПОИСКБ

 таблица, которая содержитНижний НовгородРезультат

будут соответствовать значение

Источник

Поиск нужных данных в диапазоне

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

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

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

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

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

Ссылки по теме

Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
Улучшенная версия функции ВПР (VLOOKUP)
Многоразовый ВПР

Источник

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

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

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

Базовая информация об ИНДЕКС и ПОИСКПОЗ
Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
ИНДЕКС и ПОИСКПОЗ – примеры формул
- Как находить значения, которые находятся слева
- Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
- Поиск по известным строке и столбцу
- Поиск по нескольким критериям
- ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Содержание

Базовая информация об ИНДЕКС и ПОИСКПОЗ
ИНДЕКС – синтаксис и применение функции
ПОИСКПОЗ – синтаксис и применение функции
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
ИНДЕКС и ПОИСКПОЗ – примеры формул
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

Каждый аргумент имеет очень простое объяснение:

array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

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

Вот простейший пример функции INDEX (ИНДЕКС):

=INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

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

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
- 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
- 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
- -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

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

Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:
=INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

Предположим, Вы используете вот такую формулу с ВПР, которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2:

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом, такая замена увеличивает скорость работы Excel на 13%.

Влияние ВПР на производительность Excel особенно заметно, если рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что проверка каждого значения в массиве требует отдельного вызова функции ВПР. Поэтому, чем больше значений содержит массив и чем больше формул массива содержит Ваша таблица, тем медленнее работает Excel.

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

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

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:
=MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))
Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
Затем соединяем обе части и получаем формулу:
=INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ, в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.

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

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:
=MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).
ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:
=MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)* (B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)* (B2='Lookup table'!$B$2:$B$13);0);3)}

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
Далее, мы делаем то же самое для значений столбца B (Product).
Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

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

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС. Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3.

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

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)), "Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0)); "Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

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

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

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

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

В этой статье

Поиск значений в списке по вертикали по точному совпадению

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

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

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

Примеры ИНДЕКСов и СОВПАДЕНИй

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

Дополнительные сведения можно найти в разделе Функция индекс и функция ПОИСКПОЗ.

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

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

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

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

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

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

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

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

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

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

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

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

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

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

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

Загрузка программы-надстройки «Мастер подстановок»

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

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

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

Выборка значений из таблицы Excel по условию

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

Как сделать выборку в Excel по условию

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

Автоматическую выборку реализует нам формула, которая будет обладать следующей структурой:

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

Выборка соответственного значения с первым наименьшим числом:

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

Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения. Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5. Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы. Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

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

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

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ class=’formula’>

Как в Excel выбрать первое минимальное значение кроме нуля:

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

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

Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.

Пять последних значений в MS EXCEL

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

Удаляем в MS EXCEL пропуски в списке (формулы)

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

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

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

Вывод итогов столбцов по строкам в MS EXCEL

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

Отбор неповторяющихся значений со значениями из соседнего столбца в MS EXCEL

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

Отбор неповторяющихся значений в MS EXCEL

Имея список с повторяющимися значениями, создадим список, состоящий только из значений, которые не повторяются.

Отбор повторяющихся значений в MS EXCEL

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

Динамическое разнесение данных по столбцам

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

Отбор уникальных значений в MS EXCEL с условиями

Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL. Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные значения из первого столбца. При добавлении новых строк в таблицу, список уникальных значений будет автоматически обновляться.

Запрос на выборку данных в MS EXCEL (на основе элементов управления формы)

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

Отбор неповторяющихся значений из двух диапазонов в MS EXCEL

Имея два списка с однотипными повторяющимися значениями, создадим список, состоящий только из неповторяющихся значений.

Отбор уникальных значений из двух диапазонов в MS EXCEL

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

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки

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

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

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

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть3. Поиск с учетом РЕГИСТРА

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

Отбор уникальных значений с сортировкой в MS EXCEL

Из исходной таблицы отберем только уникальные значения и выведем их в отдельный диапазон с сортировкой по возрастанию. Отбор и сортировку сделаем с помощью одной формулой массива. Формула работает как для текстовых (сортировка от А до Я), так и для числовых значений (сортировка от мин до макс).

Поиск ЧИСЛОвых значений и вывод соответствующих значений в отдельный список в MS EXCEL

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

Поиск нескольких значений с максимальной длиной строки в MS EXCEL

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

Отбор повторяющихся значений (дубликатов) со значениями из соседнего столбца в MS EXCEL

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

ВПР с возвратом всех значений

Все, кто сталкивался с функцией ВПР знают, что она умеет возвращать исключительно одно значений — первое найденное. Но что делать, когда надо видеть все значения? К примеру имеется такая таблица:

И из этой таблицы необходимо получить фамилии(поле ФИО) всех сотрудников из отдела Сбыта:

Тут два варианта:

Вариант 1: все значения необходимо получить в одной ячейке через разделитель(скажем запятую). Такое возможно только через использование VBA(на момент написания статьи). Такую функцию я написал давно: Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли

. В результате получим что-то вроде: Александров Иван,Александров Петр,Андреев Алексей .

Вариант 2: каждое значение должно быть в отдельной ячейке. Этот вариант мы и рассмотрим более подробно в данной статье. Причем решать задачу будем без использования макросов — только встроенными функциями

Здесь надо сразу оговориться — при помощи непосредственно ВПР (VLookup) это сделать ну никак не получится. Но это можно сделать при помощи её родственников — связки ИНДЕКС(ПОИСКПОЗ) (INDEX(MATCH)) . Плюс еще пара функций. В сборе функция выглядит так:
=ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1)
=INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1)
где:

$A$12:$G$111 — таблица с ФИО и всеми данными
$I$12 — ячейка с названием отдела
$C$12:$C$111 — столбец с названиями отделов в таблице $A$12:$G$111

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

Tips_All_VlookupAllVals.xls (84,5 KiB, 8 651 скачиваний)

Первое и самое главное — формула вводится в ячейку как формула массива. Это значит, что для приложенного к статье примера необходимо будет выделить ячейку J12 , записать в неё формулу и завершить ввод нажатием сразу трех клавиш: Ctrl+Shift+Enter. После этого скопировать ячейку, выделить диапазон J13:J39 и вставить туда скопированную ячейку.
За отбор ФИО указанного отдела отвечает этот кусок: ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11)
IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11)
Как видно здесь идет сравнение отдела, записанного в $I$12 со всеми отделами в таблице: $C$12:$C$111 . Если отдел совпадает, то функция возвращает номер строки СТРОКА( $C$12:$C$111 ) . Но нам нужен не номер строки на листе, а номер строки внутри таблицы(потому что в формулу ИНДЕКС (INDEX) у нас передан диапазон $A$12:$G$111 и если передать в его второй аргумент( номер_строки ) 14, то получим не Александров Иван , а Андреев Олег ). Поэтому отнимаем от номера строки 11, чтобы было возвращено число 3(а не 14). Т.к. формула записана формулой массива — ЕСЛИ (IF) возвращает массив из номеров строк и логического ЛОЖЬ (FALSE) (если отдел не совпадает):
шаг 1: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<12:13: 14 :15:16: 17 :18:19:20>-11)
шаг 2: ЕСЛИ(<ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ: ИСТИНА :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;<1:2: 3 :4:5: 6 :7:8:9>)
шаг 3:
Т.к. ЕСЛИ (IF) в данном случае возвращает массив значений, включая ненужные нам ЛОЖЬ (FALSE) — применяем НАИМЕНЬШИЙ (SMALL) , которая будет брать только числа (сначала 3, затем 6 и т.д.), а логические значения ЛОЖЬ (FALSE) будет игнорировать:
НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
SMALL(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;ROW(A1))
Функция НАИМЕНЬШИЙ (SMALL) отбирает из указанного массива чисел наименьшее. При этом можно отобрать по рангу — первое наименьшее, второе наименьшее, третье наименьшее и т.д.

с первым аргументом разобрались — это уже отобранные записи, где число — строка в таблице с нужной ФИО и ненужные нам логические ЛОЖЬ (FALSE) .
второй аргумент СТРОКА(A1) для каждой следующей строки будет меняться. В приложенном примере первая ячейка с формулой — J12 . В ней СТРОКА(A1) равна 1, т.е. НАИМЕНЬШИЙ (SMALL) вернет первое наименьшее из массива чисел — 3 . В ячейке J13 это уже будет СТРОКА(A2) и она равна 2, а значит НАИМЕНЬШИЙ (SMALL) вернет второе наименьшее из массива чисел — 6 . И т.д. Когда все числа закончатся — НАИМЕНЬШИЙ (SMALL) вернет значение ошибки #ЧИСЛО! (#NUM!)
ячейка J12
шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A1))
шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;1)
шаг 3: НАИМЕНЬШИЙ( 3 )
ячейка J13
шаг 1: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;СТРОКА(A2))
шаг 2: НАИМЕНЬШИЙ(<ЛОЖЬ:ЛОЖЬ: 3 :ЛОЖЬ:ЛОЖЬ: 6 :ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>;2)
шаг 3: НАИМЕНЬШИЙ( 6 )

И последний штрих — все это передается в функцию ИНДЕКС (INDEX) . Для ячейки J12 это будет значение ячейки, расположенной на пересечении 3-й строки и 1-го столбца диапазона $A$12:$G$111 . На листе это ячейка A14 . Т.е. третья сверху ячейка столбца А в диапазоне $A$12:$G$111 — Александров Иван .

ячейка J12
шаг 1: =ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ( 3 );1)
шаг 2: =ИНДЕКС( $A$12:$G$111 ;3;1)
шаг 3: Александров Иван
ячейка J13
шаг 1: =ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ( 6 );1)
шаг 2: =ИНДЕКС( $A$12:$G$111 ;6;1)
шаг 3: Александров Петр

И в довершение неплохо бы убрать ошибку в случае, если ни одно значение не соответствует критерию или значений больше, чем ячеек с формулами. Более подробно подобное решение я описывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Для всех версий Excel:
=ЕСЛИ(ЕОШ(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1));»»;ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1))
=IF(ISERR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1));»»;INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1))
Для Excel 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС( $A$12:$G$111 ;НАИМЕНЬШИЙ(ЕСЛИ( $I$12 = $C$12:$C$111 ;СТРОКА( $C$12:$C$111 )-11);СТРОКА( A1 ));1);»»)
=IFERROR(INDEX($A$12:$G$111;SMALL(IF($I$12=$C$12:$C$111;ROW($C$12:$C$111)-11);ROW(A1));1);»»)
Плюс к этому в приложенном к статье файле я привел формулу, которая возвращает список всех сотрудников выбранного отдела без повторений и формулу с возможностью просто поменять заголовок в результирующем столбце(в файле это столбец K, ячейка K11 ) на любой из исходной таблицы и данные будут отображены из этого столбца.
Например, если вместо столбца ФИО записать в K11 Адрес — будут отображены все адреса сотрудников выбранного отдела. Если записать Телефон — все телефоны сотрудников выбранного отдела.

Tips_All_VlookupAllVals.xls (84,5 KiB, 8 651 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

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

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

Способ 1. Дополнительный столбец с ключом поиска

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

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

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

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

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Источник

Хитрости »

15 Май 2011 512000 просмотров

Как найти значение в другой таблице или сила ВПР

Задача и её решение при помощи ВПР
Описание аргументов ВПР
Что важно всегда помнить при работе с ВПР
Как избежать ошибки #Н/Д(#N/A) в ВПР?
Как при помощи ВПР искать значение по строке, а не столбцу?
Решение при помощи ПОИСКПОЗ
Работа с критериями длиннее 255 символов

Задача и её решение при помощи ВПР

Если в двух словах, то ВПР позволяет сравнить данные двух таблиц на основании значений из одного столбца.
Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Возьмем две таблицы:

рис.1

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

второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:

=ВПР($A2;Лист1!$A$1:$C$4;3;0)
=VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)

Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы(References & Arrays) функцию ВПР(VLOOKUP) и по отдельности указав нужные критерии. Теперь копируем(

Ctrl

) ячейку с формулой(С2), выделяем все ячейки столбца

до конца данных и вставляем(

Ctrl

Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности.
ВПР ищет заданное нами значение(аргумент искомое_значение) в первом столбце указанного диапазона(аргумент таблица). Поиск значения всегда происходит сверху вниз(собственно, поэтому функция и называется ВПР: Вертикальный ПРосмотр). Как только функция находит заданное значение — поиск прекращается, ВПР берет строку с найденным значением и смотрит на аргумент номер_столбца. Именно из этого столбца берётся значение, которое мы и видим как итог работы функции. Т.е. в нашем конкретном случае, для ячейки С2 второй таблицы, функция берет фамилию «Петров С.А.»(ячейка $A2 второй таблицы) и ищет её в первом столбце указанной таблицы(Лист1!$A$1:$C$4), т.е. в столбце А. Как только находит(это ячейка А3)

ВПР может вернуть только одно значений — первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет ошибка #Н/Д(#N/A). Не надо этого бояться — это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой — как числа. Или наоборот.

Описание аргументов ВПР

Искомое_значение($A2) — это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет Петров С.А.. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках — =ВПР(«Петров С.А»;Лист1!$A$1:$C$4;3;0), либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: «*» и «?». Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью «Петров С.А», а ввести лишь фамилию и знак звездочки — «Петров*». Тогда будет выведена любая запись, которая начинается на «Петров». Если же надо найти запись, в которой в любом месте строки встречается фамилия «Петров», то можно указать так: «*петров*». Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: «Иванов ?.?.».
Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A2 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A2&»*». Эта запись будет равнозначна «Иванов*». В A2 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A2&»*» =>
«Иванов»&»*» =>
«Иванов*»
А полная формула в итоге будет выглядеть так: =ВПР(A2&»*»;Лист1!$A$1:$C$4;3;0)
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: «*»&A1&»*»
Таблица(Лист1!$A$1:$C$4) — указывается диапазон ячеек, в первом столбце которых будет просматриваться аргумент Искомое_значение. Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100. Диапазон в аргументе таблица всегда должен быть «закреплен», т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4).
Номер_столбца(3) — указывается номер столбца в аргументе Таблица, значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия — т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументе Таблица.

если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне: =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ(Лист1!$A$1:$C$4);0). К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе: =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ($A$1:$C$4);0).
Интервальный_просмотр(0) — очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Так же допускается указать 1 или 0. 1 = ИСТИНА, 0 = ЛОЖЬ. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Искомому_значению. Это не имеет никакого отношения к знакам подстановки(«*» и «?»). Если же использовать 1 или ИСТИНА, то…Совсем в двух словах не объяснить. Если вкратце — ВПР будет искать наиболее похожее значение, подходящее под Искомомое_значение. Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр равен ИСТИНА или 1. Если же 0 или ЛОЖЬ — сортировка не нужна. Этот аргумент необходимо использовать осторожно — не стоит указывать 1 или ИСТИНА, если нужно найти точное соответствие и уж тем более не стоит использовать, если не понимаете принцип его работы.
Подробнее про работу ВПР с интервальным просмотром, равным 1 или ИСТИНА можно ознакомиться в статье ВПР и интервальный просмотр(range_lookup)

При работе с ВПР всегда важно помнить три вещи:

Таблица всегда должна начинаться с того столбца, в котором ищем Искомое_значение. Т.е. ВПР не умеет искать значение во втором столбце таблицы, а значение возвращать из первого. В лучшем случае ничего найдено не будет и получим ошибку #Н/Д(#N/A), а в худшем результат будет совсем не тот, который должен быть
аргумент Таблица должен быть «закреплен», т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4). Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки и жмете клавишу F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы из одной ячейки в остальные аргумент Таблица будет «съезжать» и результат может быть совсем не таким, какой ожидался(в лучшем случае получите ошибку #Н/Д(#N/A)
номер_столбца не должен превышать общее кол-во столбцов в аргументе таблица, а сама Таблица соответственно должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере указана Лист1!$A$1:$C$4 — всего 3 столбца(A, B, C). Значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца. Т.е. если мы запишем формулу так: =ВПР($A2;Лист1!$A$1:$C$4;4;0) — мы получим ошибку #ССЫЛКА!(#REF!).
Если аргументом Таблица указан диапазон $B$1:$C$4 и необходимо вернуть данные из столбца С, то правильно будет указать номер столбца 2. Т.к. аргумент Таблица($B$1:$C$4) содержит только два столбца — В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА!(#REF!), т.к. третьего столбца в указанном диапазоне просто нет.

Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца: =ВПР($A2;Лист1!$A$1:$C$4;СТОЛБЕЦ();0). Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.

Как избежать ошибки #Н/Д(#N/A) в ВПР?

Еще частая проблема — многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это можно обойти при помощи специальных функций.
Для пользователей Excel 2003 и старше:

=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$1:$C$4;3;0));»»;ВПР($A2;Лист1!$A$1:$C$4;3;0))
=IF(ISNA(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)),»»,VLOOKUP($A2,Лист1!$A$1:$C$4,3,0))

Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше будет удобнее использовать функцию

ЕСЛИОШИБКА(IFERROR)

=ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$1:$C$4;3;0);»»)
=IFERROR(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0);»»)

Подробнее про различие между использованием ЕСЛИ(ЕНД и ЕСЛИОШИБКА я разбирал в статье: Как в ячейке с формулой вместо ошибки показать 0
Но я бы не рекомендовал использовать

ЕСЛИОШИБКА(IFERROR)

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

искомое значение состоит более чем из 255 символов(решение этой проблемы приведено ниже в этой статье: Работа с критериями длиннее 255 символов)
искомое значение является числом с большим кол-вом знаков после запятой. Excel не может правильно воспринимать такие числа и в итоге ВПР может вернуть ошибку. Правильным решением здесь будет округлить искомое значение хотя бы до 4-х или 5-ти знаков после запятой(конечно, если это допустимо):
=ВПР(ОКРУГЛ($A2;5);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(ROUND($A2,2),Лист1!$A$1:$C$4,3,0)
искомое значение содержит специальные или непечатаемые символы.
В этом случае придется либо избавиться от непечатаемых символов в искомом аргументе:
=ВПР(ПЕЧСИМВ($A2);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(CLEAN($A2),Лист1!$A$1:$C$4,3,0)
либо добавить перед всеми специальными символами(такими как звездочка или вопр.знак) знак тильды(~), чтобы сделать эти знаки просто знаками, а не знаками специального значения(так же работа со специальными(служебными) символами описывалась в статье: Как заменить/удалить/найти звездочку). Добавить символ перед знаком той же тильды можно при помощи функции ПОДСТАВИТЬ(SUBSTITUTE):
=ВПР(ПОДСТАВИТЬ($A2;»~»;»~~»);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(SUBSTITUTE(A2,»~»,»~~»),Лист1!$A$1:$C$4,3,0)
Если необходимо добавить тильду сразу перед несколькими знаками, то делает это обычно так(на примере подстановки одновременно для тильды и звездочки):
=ВПР(ПОДСТАВИТЬ(ПОДСТАВИТЬ($A2;»~»;»~~»);»*»;»~*»);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,»~»,»~~»),»*»,»~*»),Лист1!$A$1:$C$4,3,0)

Как при помощи ВПР искать значение по строке, а не столбцу?

На самом деле ответ будет коротким — ВПР всегда ищет сверху вниз. Слева направо она не умеет. Но зато слева направо умеет искать её сестра ГПР(HLookup) — Горизонтальный

ПР

осмотр.
ГПР ищет заданное значение(аргумент

искомое_значение

) в первой строке указанного диапазона(аргумент

таблица

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

#Н/Д(#N/A)

.
Если надо найти значение «Иванов» в строке 2 и вернуть значение из строки 5 в таблице

A2:H10

, то формула будет выглядеть так:

=ГПР(«Иванов»;$A$2:$H$10;5;0)
=HLOOKUP(«Иванов»,$A$2:$H$10,5,0)

Все правила и синтаксис функции точно такие же, как у ВПР:
-в искомом значении можно применять символы астерикса(*) и вопр.знака(?) — «Иванов*»;
-таблица должна быть закреплена —

$A$2:$H$10

;
-интервальный просмотр работает по тому же принципу(0 или ЛОЖЬ точный просмотр слева-направо, 1 или ИСТИНА — интервальный).

Решение при помощи ПОИСКПОЗ

Общий принцип работы

ПОИСКПОЗ(MATCH)

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

Искомое_значение

в аргументе

Просматриваемый_массив

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

Просматриваемом_массиве

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

=ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0)
=MATCH($A2,Лист1!$A$1:$A$4,0)

Искомое_значение($A2) — непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше — то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки * и ? и ровно в таком же исполнении.
Просматриваемый_массив(Лист1!$A$1:$A$4) — указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искать Искомое_значение. Если попытаться указать более одного столбца, то функция вернет ошибку.Справедливости ради надо отметить, что можно указать либо столбец, либо строку
Тип_сопоставления(0) — то же самое, что и Интервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС(INDEX)(которая возвращает из указанного диапазона значение на пересечении заданных строки и столбца) — то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Массив(Лист1!$A$2:$C$4). В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не обязательно или он всегда будет равен 1(столбец-то всего один). К слову — данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если не преследуются иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0));»»;ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2);»»)

Работа с критериями длиннее 255 символов

Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:

=ВПР(ПСТР($A2;1;255);ПСТР(Лист1!$A$1:$C$4;1;255);3;0)

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

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$1:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$1:$A$4=$A2;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

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

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

Скачать пример

Tips_All_VLookUp.xls (26,0 KiB, 17 435 скачиваний)

Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

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

ВПР (искомое значение; диапазон поиска; номер столбца с входным значением; 0 (ЛОЖЬ) или 1 (ИСТИНА)).

ЛОЖЬ – точное значение, ИСТИНА – приблизительное значение.

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

В ячейке С12 начинаем писать функцию:

B12 – поскольку нам нужен Хепилор, выбираем ячейку с предварительно написанным названием искомого лекарства.
Далее выбираем диапазон данных B3:D10, где функция будет совершать поиск нужного нам значения. Крайний левый столбец диапазона должен содержать в себе искомый критерий, по которому производится поиск значения.
Следующий шаг – указать номер столбца в массиве B3:D10, из которого будет считана информация на одной строке с Хепилором. Столбцы нумеруются слева направо в самом диапазоне, в нашем примере первый столбец – В, но не А, поскольку А лежит вне области диапазона.

Поиск по столбце «Производитель» будет работать точно так же, нужно просто указать последовательность столбца, где находится нужная нам информация – заменяем цифру «3» в формуле (ячейка С27) на цифру «2»:

Есть определённая особенность, связанная со столбцами. Иногда в Excel-файле в таблицах некоторые ячейки объединяют. На картинке ниже в формуле на месте порядкового номера столбца у нас написана цифра «3», но результат – название производителя, а не цена, как в первом примере:

Произошел сдвиг нумерации столбцов как раз из-за наличия объединения ячеек в столбце «Лекарственное средство»: мы объединяли столбцы «H» и «I», зрительно столбец «Лекарственное средство» — это первый столбец, а «Производитель» — второй, НО формула нумерует их следующим образом:

H – первый;
I – второй;
J – третий;
K – четвертый.

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

Использование функции ВПР для работы с несколькими таблицами и другими функциями

В следующем примере рассмотрим, как ещё мы можем использовать функцию для поиска и получения информации по критериям и комбинирование функции с функцией ЕСЛИОШИБКА. Например, мы имеем два отчета – отчет о количестве товара и отчет о цене за единицу товара, которые нам необходимы для подсчета стоимости. Опять же, с небольшим количеством данных это вполне можно сделать вручную, но, когда мы имеем большой объем, справиться с этим скорее и эффективнее нам поможет функция ВПР. В ячейке D3 начинаем писать функцию:

B3 – критерий, по котором проводим поиск данных.
F3:G14 – диапазон, по котором наша функция будет осуществлять поиск совпадения критерия и данных по строке.
Цифра «2» — номер столбца с нужной нам информацией по критерию.
Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.

Таким образом, когда мы задаем формуле искомый критерий, она начинает поиск совпадений с верхней ячейки первого столбца (шаг 1 на картинке). Затем функция «читает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Хепилора, она отсчитает нужное количество столбцов вправо (шаг 3) и выдаст нам искомое значение для критерия – цену 86,90 (шаг 4):

Но сейчас у нас есть данные только по первому критерию. Для того чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию до последнего критерия. Однако, на этом этапе для корректной работы диапазон, где совершается поиск, нужно закрепить, иначе массив данных «съедет» вниз и у нас ничего не получится. Для этого используем абсолютные ссылки для диапазона в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего совершаем копирование формулы до конца таблицы:

В итоге мы получаем необходимый нам результат:

Однако, наш пример базировался на полном соответствии критериев с обеих таблиц – одинаковое количество товаров, одинаковые наименования. Но что, если, например, убрать последние четыре товара с отчета по ценам за упаковку? Тогда у нас будет ошибка #Н/Д в первой таблице в тех позициях, которые находятся на одной строке с искомым критерием:

Если вас не устраивает такое содержание ячеек, можно заменить значение ошибки. Для этого комбинируем функцию ВПР с функцией ЕСЛИОШИБКА. Синтаксис функции ЕСЛИОШИБКА(значение, значение_если_ошибка), таким образом значением у нас будет наша использованная функция ВПР, а значением если ошибка – то, что мы хотим видеть вместо #Н/Д, например, прочерк, но обязательно взятый в кавычки:

В результате мы получим красиво оформленную таблицу с надлежащим видом:

Использование приблизительного значения

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

Как видим, размер премии зависит от того диапазона по системе премирования, куда попал показатель выполнения продаж конкретного сотрудника. Мы видим, что если план выполнен менее, чем на 100% — премия не присваивается, а если на 107% (выше 100%, но меньше 110%), тогда сотрудник получает премию размером 10%. Описанные показатели премии нам нужно вписать с помощью функции ВПР в столбец «Премия» первой таблицы, только на этот раз критерий будет находиться в определённом диапазоне.

Для корректной работы нужно убедиться, что границы диапазонов во второй таблице крайнего левого столбца размещены по возрастанию сверху вниз (шаг 1). Формула берёт выбранный нами критерий и осуществляет поиск в первом столбце второй таблицы (шаг 2), просматривая все значения сверху вниз (шаг 3). Как только функция находит первое значение, которое превышает критерий с первой таблицы, делает «шаг назад» (шаг 4) и считывает значение, которое соответствует найденому критерию (шаг 5). Иными словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Таким образом, наша функция будет выглядеть так:

И результат использования функции ВПР с приблизительным поиском имеет вот такой результат:

Скачать все пошаговые примеры функции ВПР в Excel

Например, сотрудник Ольга имеет премию размером 0%, поскольку она выполнила 76% продаж, тоесть перевыполнила план на 0%. А сотрудник Наталья совершила продажи на 21% выше нормы и была премирована на 20%, что мы и видим, если сравнить самостоятельно данные с двух таблиц.

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

Источник

На чтение 10 мин Просмотров 13.8к. Опубликовано 31.07.2020

Содержание

5 thoughts on “ «ВПР» по частичному совпадению ”
Проверяем условие для полного совпадения текста.
ЕСЛИ + СОВПАД
Использование функции ЕСЛИ с частичным совпадением текста.
ЕСЛИ + ПОИСК
ЕСЛИ + НАЙТИ
Примеры использования функции ПОИСКПОЗ в Excel
Формула для поиска неточного совпадения текста в Excel
Сравнение двух таблиц в Excel на наличие несовпадений значений
Поиск ближайшего большего знания в диапазоне чисел Excel
Особенности использования функции ПОИСКПОЗ в Excel

Спустя катастрофически большой промежуток времени с момента публикации моего последнего поста, решил поделиться супер крутой, на мой взгляд, Excel-формулой, узнав о которой, начинаешь удивляться, как же раньше-то я жил без нее. Но, должен сказать, авторство ее создания не мое, а вероятнее всего принадлежит англоязычному ресурсу, о котором я скажу ниже.
Кто более-менее часто работает с массивами данных в Excel почти наверняка знает про функцию ВПР (см. мою статью) или ИНДЕКС+ПОИСКПОЗ, которые решают достаточно частую задачу по объединению двух наборов данных по каким-либо совпадающим значениям. И действительно, использование этих функций решает задачи по сопоставлению и объединению данных в 90% случаев. Если бы не одно но — данные, по которым производится объединение, действительно должны именно совпадать. Но бывают случаи, когда требуется сопоставление по частичному совпадению. Да, в ВПР есть поиск по приблизительному совпадению, но работает он не совсем прозрачно, а потому предугадать, почему было подобрано одно похожее слово, а не другое, может быть невозможно не просто. Как вы поняли, эту прелюдию я затеял не просто так, а для того, чтобы рассказать, как же решить такую задачу при помощи Excel.
Предположим, у нас есть список товаров, которые надо как-то сгруппировать:

5 thoughts on “ «ВПР» по частичному совпадению ”

На форуме SQL.ru мне подсказали еще одно очень изящное решение этой задачи, посмотреть его можно здесь:
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&t > Спасибо большое, Казанский (автор совета)!

Игорь, спасибо Вам огромное за эту «бронебойную» формулу. Весь интернет «перелопатила» в поиске решения своей задачи и только Вы мне помогли на 100%. Всё работает как часики. Удачи Вам, успешной работы и ещё больше таких гениальных решений.

Ольга, спасибо большое за Ваш комментарий! Справедливости ради надо сказать, что идея этой формулы не моя, а обнаружил я ее на сайте Exceljet

Игорь, добрый день!
Формула прекрасная, но есть ли какая-нибудь ее вариация, которая может находить и подставлять несколько значений сразу?
Например, в строке указаны два производителя холодильников, LG и Samsung
Можно ли вывести их в ячейку через запятую?

Добрый день, Артём!
Спасибо за ваш комментарий и прошу прощения за медленный ответ. Вопрос интересный, но с ходу у меня на него ответа, увы, нет, а по времени довольно сильно ограничен. Если будет свободное время, попробую поломать голову на эту тему

Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст.

Будьте особо внимательны в том случае, если для вас важен регистр, в котором записаны ваши текстовые значения. Функция ЕСЛИ не проверяет регистр – это делают функции, которые вы в ней используете. Поясним на примере.

Проверяем условие для полного совпадения текста.

Проверку выполнения доставки организуем при помощи обычного оператора сравнения «=».

=ЕСЛИ(G2=»выполнено»,ИСТИНА,ЛОЖЬ)

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

Если же вас интересует именно точное совпадение текстовых значений с учетом регистра, то можно рекомендовать вместо оператора «=» использовать функцию СОВПАД(). Она проверяет идентичность двух текстовых значений с учетом регистра отдельных букв.

Вот как это может выглядеть на примере.

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

ЕСЛИ + СОВПАД

В случае, если нас интересует полное совпадение текста с заданным условием, включая и регистр его символов, то оператор «=» нам не сможет помочь.

Но мы можем использовать функцию СОВПАД (английский аналог — EXACT).

Функция СОВПАД сравнивает два текста и возвращает ИСТИНА в случае их полного совпадения, и ЛОЖЬ — если есть хотя бы одно отличие, включая регистр букв. Поясним возможность ее использования на примере.

Формула проверки выполнения заказа в столбце Н может выглядеть следующим образом:

Как видите, варианты «ВЫПОЛНЕНО» и «выполнено» не засчитываются как правильные. Засчитываются только полные совпадения. Будет полезно, если важно точное написание текста — например, в артикулах товаров.

Использование функции ЕСЛИ с частичным совпадением текста.

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

Первое, что приходит на ум – использовать подстановочные знаки «?» и «*» (вопросительный знак и звездочку). Однако, к сожалению, этот простой способ здесь не проходит.

ЕСЛИ + ПОИСК

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

=ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем)

Если третий аргумент не указан, то поиск начинаем с самого начала – с первого символа.

Функция ПОИСК возвращает либо номер позиции, начиная с которой искомые символы встречаются в тексте, либо ошибку.

Но нам для использования в функции ЕСЛИ нужны логические значения.

Здесь нам на помощь приходит еще одна функция EXCEL – ЕЧИСЛО. Если ее аргументом является число, она возвратит логическое значение ИСТИНА. Во всех остальных случаях, в том числе и в случае, если ее аргумент возвращает ошибку, ЕЧИСЛО возвратит ЛОЖЬ.

В итоге наше выражение в ячейке G2 будет выглядеть следующим образом:

Еще одно важное уточнение. Функция ПОИСК не различает регистр символов.

ЕСЛИ + НАЙТИ

В том случае, если для нас важны строчные и прописные буквы, то придется использовать вместо нее функцию НАЙТИ (в английском варианте – FIND).

Синтаксис ее совершенно аналогичен функции ПОИСК: что ищем, где ищем, начиная с какой позиции.

Изменим нашу формулу в ячейке G2

То есть, если регистр символов для вас важен, просто замените ПОИСК на НАЙТИ.

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

Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

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

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

Например, массив <«виноград»;»яблоко»;»груша»;»слива»>содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;<«виноград»;»яблоко»;»груша»;»слива»>;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.

Формула для поиска неточного совпадения текста в Excel

Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

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

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Сравнение двух таблиц в Excel на наличие несовпадений значений

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

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

Как видно, третьи элементы списков не совпадают.

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

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

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

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

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

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

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

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

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

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

Источник

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Функция ВПР()

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Функция ПОИСКПОЗ в Excel

В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel, а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.

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

В Microsoft Excel существует множество различных функций поиска, которые могут помочь найти определенное значение в ряде ячеек, а ПОИСКПОЗ — одна из них. ПОИСКПОЗ на английском MATCH , в основном, идентифицирует относительное положение элемента в диапазоне ячеек. Однако функция ПОИСКПОЗ Excel в сочетании с другими функциями может сделать гораздо больше.

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

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

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

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

Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.

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

1 или отсутствует (по умолчанию) – находит наибольшее значение в массиве, которое меньше или равно значению поиска. Массив поиска должен быть отсортирован в порядке возрастания, от самого маленького до большого или от A до Z.
0 – находит первое значение в массиве, которое в точности равно значению поиска. Сортировка не требуется.
-1 – находит наименьшее значение в массиве, которое больше или равно значению поиска. Массив поиска должен быть отсортирован в порядке убывания, от самого большого до самого маленького или от Z до A.

Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:

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

При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:

Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel

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

Особенности функции ПОИСКПОЗ Excel

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

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

Использование ПОИСКПОЗ в Excel – примеры формул

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

Формула ПОИСКПОЗ Excel с подстановочными знаками

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

Вопросительный знак (?) — заменяет любой символ
Звездочка (*) — заменяет любую последовательность символов

Примечание . Подстановочные знаки могут использоваться только в формулах ПОИСКПОЗ с параметром тип_сопоставления равному 0.

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

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

Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:

=ПОИСКПОЗ(«восток*»; A2:A8; 0)

Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и объединить эту ячейку с подстановочным символом, например:

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

Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с подстановочными знаками

Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:

Вышеуказанная формула будет соответствовать названию « Бейкер » и вернет его относительное положение, которое равно 5.

Формула ПОИСКПОЗ с учетом регистра

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

Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:

Логика этой формулы такова:

Функция СОВПАД сравнивает значение поиска с каждым элементом просматриваемого массива. Если сравниваемые ячейки в точности равны, функция возвращает ИСТИНУ, или ЛОЖЬ в противном случае.
Далее функция ПОИСКПОЗ сравнивает ИСТИНУ (которая является ее искомым_значением) с каждым значением в массиве, возвращаемом СОВПАД, и возвращает позицию первого совпадения

Обратите внимание , что это формула массива, которая требует нажатия Ctrl+Shift+Enter для правильной обработки.

Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)

На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:

Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с учетом регистра

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

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

=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)

Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:

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

Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)

Как вы помните, функция ПОИСКПОЗ Excel сама по себе не учитывает регистр. Чтобы заставить ее различать регистр, вставьте функцию СОВПАД в аргумент просматриваемый_массив и не забудьте нажать Ctrl+Shift+Enter , чтобы завершить эту формулу массива :

=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)

На следующем изображении демонстрируются обе формулы в действии:

Функция ПОИСКПОЗ в Excel – Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в 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 – ИНДЕКС ПОИСКПОЗ пример формулы

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

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

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

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция «Мастер подстановок» больше не доступна в Excel.

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

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «Иванов» или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент необязателен. Введите значение истина или ложь. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ложь, функция будет соответствовать значению, представленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение истина, вы получаете более гибкие возможности.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).

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

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

Использование функций индекс и MATCH вместо функции ВПР

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

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

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

Скопируйте указанные ниже данные в пустой лист.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладка Главная , группа Выравнивание ).

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

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

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

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

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

Вариант 4. Ссылка на столбец / строку

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

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

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

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

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

Функция ИНДЕКС в Excel

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

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Источник

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

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

Примеры ВРОТ

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

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

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

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

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

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

Нужна дополнительная помощь?

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

Функция ИНДЕКС

Функция ПОИСКПОЗ

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

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

Формула для поиска справа налево

Двусторонний поиск в строках и столбцах

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

Максимальное значение.

Минимальное значение

Ближайшее к среднему

Что делать с ошибками поиска?

Возможен ли «левый» поиск?

Повлияет ли на результат вставка и удаление столбцов?

Возможен ли поиск по строкам и столбцам?

Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

Базовая информация об ИНДЕКС и ПОИСКПОЗ

ИНДЕКС – синтаксис и применение функции

ПОИСКПОЗ – синтаксис и применение функции

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

ИНДЕКС и ПОИСКПОЗ – примеры формул

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Попробуйте попрактиковаться

Пример функции ВПР в действии

Пример функции ГПР

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

Дополнительные сведения о функциях поиска

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

Функция ПОИСКПОЗ в Excel

Функция ИНДЕКС в Excel

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Описание

Синтаксис

Замечание

Примеры

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

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

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

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

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

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

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

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

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

Поиск значения в диапазоне ячеек по условию

Функция ВПР и сравнение двух таблиц в Excel если не работает

Как работает функция ВПР в Excel при выборке из таблицы значений?

Поиск значений в диапазоне по условиям (Формулы/Formulas)

Поиск нескольких значений в диапазоне ячеек (Формулы/Formulas)

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

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

Поиск индекса максимального числа массива в Excel

Функция в Excel для поиска конкретного значения в диапазоне ячеек

Поиск нужных данных в диапазоне

Ссылки по теме

Базовая информация об ИНДЕКС и ПОИСКПОЗ

ИНДЕКС – синтаксис и применение функции