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

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

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

index1.gif

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

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

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

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

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

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

 


Страницы: 1  2  
Андрей
28.10.2012 23:19:31
а как помкнять задачу?
Например у меня есть таблица и надо найти число по 2 параметрам (к примеру по x и y значениям).
Елена
28.10.2012 23:19:52
Андрей, ваша задача решается аналогично, только после поиска номера строки (x), ищется точно так же номер столбца (y).
=индекс(массив_данных;(поискоз(номер_строки;массив_строк;0));(поискпоз(код_стобца;массив_столбцов;0))).
25.11.2014 13:30:23
здравствуйте у меня тоже проблема по этой тему, дело в том что нужно искать в не большой база данных двух совпадение, Москва в одном ячейке а 002 рядом и ест лист в котором ест тоже в одном Москва а рядом 002,просто там ест много 002 и Москва, но я хочу во первых формула нашла именно ту Москву с которым рядом ест 002 и второй в первом листе рядом Москва и 002 пишется 10 , а втором листе рядом Москва 002 ест 5 надо чтобы формула нашла эти цифры и умножала их очень прошу помогите у меня уже голова болит от этого но ничего не получается
kep
28.10.2012 23:20:30
Оп-па, всё нормально, когда искомое значение найдено,
НО,
если искомое значение не найдено, тогда функция возвращает "#Н/Д"
ВОПРОС: как сделать, чтобы вместо "#Н/Д" значение ячейки равнялось нулю ?
28.10.2012 23:26:41
Используйте, например, функцию ЕСЛИОШИБКА - она перехватывает любые ошибки и выводит вместо них любое нужное вам значение (0).
Галина
28.10.2012 23:21:40
Огромное спасибо, кто сделал данный сайт с помощью него я решила свою задачу.
28.10.2012 23:26:55
Не за что! :)
Леонид
28.10.2012 23:22:36
Очень полезный сайт! Огромный плюс в том, что названия функций даны и на русском, и на английском - хороший выход на сайт с поисковиков.
Сочетание ИНДЕКС+ПОИСКПОЗ можно использовать, если нужно не только выбрать из исходной таблицы одно значение, но и целые строчки (например, из таблицы продаж, в которой также подсчитаны промежуточные итоги по месяцам, перенести в новую таблицу только строчки с промежуточными итогами).
Решение: в функции ИНДЕКС и ПОИСКПОЗ закрепить диапазоны полностью в кажной, а также закрепить ячейку, по которой ищется совпадение так, чтобы по столбцам не смещалась. Пример:
 =ИНДЕКС($A$311:$J$778;ПОИСКПОЗ($A790;$C$311:$C$778;0);2)
Parviz
28.10.2012 23:24:09
Здравствуйте, у меня вопрос по данному примеру:
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
число 2 это что?
28.10.2012 23:27:41
это номер столбца в таблице, откуда мы берем значение, т.е. регион
Дмитрий
28.10.2012 23:25:36
Дополнительное условие к задаче:
Предположим что у артикула товара 8985 не один а два региона. Возможно ли решение при условии что регионы будут записаны в одну ячейку?
28.10.2012 23:28:52
Вытащить все вхождения, а не только первое можно с помощью формулы массива - см. здесь
01.01.2013 01:43:20
Подскажите пожалуйста, а если артикул 6576 повторяется два раза в диапазоне D1:D13, но при этом регионы поставки для него разные. Как лучше решить текущую задачу "определить регион поставки по артикулу товара, набранному в ячейку C16" ?
03.01.2013 00:02:23
Приведенные выше формулы выведут вам первый встретившийся регион.
Если вам нужно вывести все регионы для заданного артикула, то придется использовать более хитрые конструкции - см. Многоразовый ВПР
04.01.2013 02:08:28
Благодарю за ответ. Честно говоря, перед мной стояла немножко другая задача. Но как раз с помощью этой темы и информации с вашего форума её удалось решить.
Ещё раз спасибо.
27.11.2019 13:39:27
Шанс получить ответ ничтожный, но вдруг.. мне также требуется вывести сумму всех значений при вводе нескольких условий, при этом СУММЕСЛИМН не подходит, т.к. значения для суммирования не только в одном столбце. Какую формулу Вам удалось составить?
09.01.2013 14:48:34
А скажите, пожалуйста, можно ли чтобы эта чудесная формула искала бы значения на разных листах? Я поробовала сделать вот так:
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается...
Спасибо.:)
11.01.2013 18:06:51
Без файла сказать трудно. Но у вас с ходу в формуле выделенное красным - это что?
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас - четыре. Что-то лишнее :)
11.04.2013 08:07:19
Конечно можно. У меня была похожая задача и получилось только с INDIRECT. В моем случае
название листов находятся в столбце А. (не смог только разобраться с большим количеством кавычек, но работает)
=INDEX(INDIRECT("'"&$A5&"'!$A$8:$Z$50");MATCH($M$1;INDIRECT("'"&$A5&"'!$B$8:$B$50");0);J$4)
13.01.2013 10:11:19
добрый день. У меня при изменении значения, по которому нужно искать, найденные значения не меняются автоматически, только если нажать на эту ячейку и Enter, либо сохранив файл. Что можно сделать?
13.01.2013 11:53:44
Видимо у вас выключен автоматический пересчет формул. Вкладка Формулы - Вычисления - Автоматически.
08.02.2013 08:36:20
Здравствуйте,Николай. Очень Вас прошу помочь в решении: на одной странице диапазон ячеек в 4 столбика, необходимо задать условие, что если в диапазоне ячеек четвертого столбика встретится 0, то необходимо выбрать значение из левого второго столбика и поставить в определенный диапазон ячеек на другом листе,причем уже значение 100( то есть в 1-листе О, то на 2-м листе 100 и сумма всех этих "100";). Вопрос: какую функцию выбрать.
И ворой вопрос: В интернете я открываю свой киви кошелек и вижу сумму, а можно ли сделать через гиперссылку чтобы программа видела остаток по кошельку на данный момент не заходя в интернет? Заранее спасибо. Ирина
08.02.2013 09:57:17
Ирина, с вопросами не по теме примера лучше на форум. Создайте тему, приложите файл, опишите ситуацию и желаемый результат. Здесь комментарии к примеру. Да и на вопрос ваш, не видя вашего файла, ответить нереально - при всем желании.
11.02.2013 08:40:14
При использовании ПОИСКПОЗ столкнулась с проблемой: мне нужно находить ячейку не по точному значению, а брать ближайшее меньшее значение и ближайшее большее значение. Меньшее значение находить без всяких проблем. При поиске большего выдает #Н/Д. Может вы знаете, почему так?
11.02.2013 10:38:40
При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.
При поиске ближайшего наибольшего - по убыванию.

У вас так?
11.02.2013 11:02:24
Теперь так. Спасибо.
05.04.2013 19:55:31
Скажите пожалуйста, если вместо артикула товаров (в примере) надо  подставлять текст. Попробовал -выдает #Н/Д .
Пробовал задавать диапазон ( артикулов) как текст, все равно выдает ошибку.Что нужно сделать чтобы её исправить?
11.04.2013 08:05:42
Если вы имеете ввиду подставлять вместо обычного диапазона выделенного мышью - имя диапазона текстом, то придется использовать функцию ДВССЫЛ, которая превратит текстовое название диапазона в реальную ссылку на него.
12.05.2013 06:40:07
Спасибо Большое Вам, Николай! Не только за этот пример, а в общем - за весь Сайт!!! :)
20.05.2013 19:14:12
Николай, спасибо за урок!
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.:)
26.05.2013 09:50:32
Спасибо за уточнение, Антон! ПРОСМОТР - тоже вариант в некоторых случаях.
26.05.2013 14:47:05
Добрый день всем!
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!

Ну,  а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
30.05.2013 13:11:27
Указать имя листа и адрес ячейки, откуда брать номер столбца, типа Лист1!A1 - не помогает?
31.08.2014 00:07:53
Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму - или криво офис на комп встал... или одно из двух...то #ссылку возвращает то #н/д.
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало - непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.

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

Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
17.09.2014 12:51:13
хм. таки поборол. отличная функция! так упростила мне работу!!!
ОГРОМНОЕ ВАМ НИКОЛАЙ СПАСИБО!!!
30.05.2013 11:44:06
Приветствую!
Как выполнить поиск в диапазоне одновременно по двум позициям? Т.е. если на примере в теме Поиска данных в диапазоне известен регион и желаемая цена(приблизительно), а нужно найти и вывести в ячейку количество.
ИНДЕКС И ПОИСКПОЗ имеют по одному значению
Заранее благодарю)
30.05.2013 13:07:05
Если искать точно, то можно просто предварительно объединить два столбца в один с помощью функции СЦЕПИТЬ, чтобы получить один столбец для проверки условий.
Если нужно искать приблизительно, то простого решения нет.
02.06.2013 13:54:36
 Благодарю Николая за невероятную комбинацию функций ВПР; СМЕЩ; ПОИСКПОЗ; СЧЁТЕСЛИ,
которая подарила мне уйму свободного времени.:D Очень грамотно.8)
Николай, добрый день!
отличная формула очень часто ее использую
однако столкнулся с небольшой проблемой, подставляемые данные вытаскиваю из другого файла, т.е. в формуле у меня стоит ссылка на другой файл. И очень часто когда открываешь файл с формулой ИНДЕКС(ПОИСКПОЗ...) он покрывается ссылками, лечиться только открытием файла на который стоит ссылка в формуле. Не критично конечно но иногда очень не удобно. Это можно как нибудь вылечить?
09.08.2013 13:42:49
Здравствуйте, Николай! У меня такая задача. В документе три страницы с информацией (3 класса: А, Б, В), в каждом из которых разное количество учащихся. На 4 странице, в протоколе, мне нужно отобразить информацию отдельно о каждом из учеников, который учится в одном из трех классов. Как через функцию "индекс", по номеру учащегося и  классу получить информацию. Т.е. меня интересует второй вариант использования функции "индекс", как производить поиск в нескольких таблицах, как правильно записать формулу. С уважением, Людмила.
18.09.2013 21:48:36
Николай, большое спасибо за Ваши уроки!
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ

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

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

Ещё раз благодарю Вас!
С уважением, Вячеслав!
21.12.2013 09:54:48
Ну да, хорошее решение, чтобы не считать номер колонки вручную :)
11.08.2014 09:44:33
Здравствуйте, а если шапка таблицы многослойная решение есть?
Многослойная шапка - например в строке 2 условия в столбце 2 условия а не по одному.

т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);

а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем

Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!
11.08.2014 10:01:17
Антон, как вам ответить не видя вашего файла?
Создайте лучше тему на форуме, приложите файлик - поможем.
11.08.2014 10:36:48

Вы так быстро ответили , что я таблицу неуспел нарисовать ))

=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
Усл 2qqqhhh
Усл 4xyzxyz
список выбора условия 1kУсл 1Усл 3
список выбора условия 2qjbабвгде
список выбора условия 3jsежзикл
список выбора условия 4jfмнопрс
решениетkbтуфхцч
ksшщъыьэ
kfюя----
а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем
Усл 2qqqhhh
Усл 4xyzxyz
список выбора условия 1kУсл 1Усл 3
список выбора условия 2hjbабвгде
список выбора условия 3sjsежзикл
список выбора условия 4jfмнопрс
решение#ССЫЛ!kbтуфхцч
должно быть ыksшщъыьэ
kfюя----
11.08.2014 16:03:42
Антон, по такой картинке качественно ответить нереально. Я бы склеил условия из шапки попарно с помощью функции СЦЕПИТЬ и получил бы в итоге одно условие, по которому бы и делал обычный поиск. Лучше сделайте тему на форуме и приложите нормальный файл с примером, тогда ответ будет точнее :)
09.12.2013 09:14:03
Здравствуйте! Подскажите пожалуйста, а как мне вывести цену на товар из всплывающего списка, если прайс-лист и его цены у меня находятся на листе1, а вывести надо на лист3?
21.12.2013 09:54:07
Все будет точно так же, как в примере - только диапазоны будете во время ввода формулы выделять на разных листах.
03.04.2014 10:47:52
Подскажите, пожалуйста, если я осуществляю поиск по двум признакам, которые связаны в таблице при помощи функции Связка и у меня находят данные на одно значение выше, чем искомое, то где ошибка?
08.05.2014 10:23:14
Проверьте выделение диапазонов в формуле. Где-то на одну ячейку больше, например, шапку зацепили и т.д.
08.05.2014 12:45:27
Красота! Большое спасибо автору.
ПОИСКПОЗ ищет первое значение слева, а мне нужно ( в строке есть пустые ячейки ) найти крайнее правое. Вопрос: КАК?
08.05.2014 15:03:43
Сам себе и отвечу :D. =ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
(Последний аргумент функции 0 - означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано "";) в строке.
И тут появляется "НО" - если в строке D1:D13 пустые ячейки появляются пару раз (например:
 D1   D2    D3  D4  D5  D6    D7   D8 ....D13
(авс) (авс) (авс) (   ) (   ) (авс) (авс) (   ) ...(авс), то формула  ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) - значение (авс) - ВВОД (Shift+Enter). А как это записать формулой?
14.05.2014 15:01:07
Приблизительное соответствие нужно совсем для другого (округления в нужную сторону при поиске числовых, а не текстовых значений).
если в строке D1:D13
D1:D13 - это столбец, а не строка

Если вы имели ввиду вопрос "как сделать так, чтобы формула находила не первое встретившееся, а последнее значение", то тут проще всего макросом, наверное - писать на VBA функцию аналогичную ВПР.
24.05.2014 11:02:43
Николай, формула ИНДЕКС хорошая, но слева можно найти и с помощью функции ВПР.
В вашем примере, формула =ВПР(C16;ВЫБОР({1;2};$D$2:$D$13;$B$2:$B$13);2;0)
сделает то-же самое.
Может кому пригодится для развития познаний.
22.08.2014 12:19:25
Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы!!!

Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению...?):
яч. E16   =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 - это тоже делает отчет удобнее. Теперь можно "играться" с разными значениями, просто выбирая их из выпадающего списка.

Спасибо!
25.08.2014 14:23:08
Подскажите, можно ли с помощью ВПР в массиве данных получить выборку по дате поступлений по контрагентам. т е есть клиенты, которые платят в течение года, когда 2 раза в месяц, когда раз в три месяца. можно ли получить данные, где будет видно, в какие даты приходит оплата от клиента?
25.11.2014 13:02:57
 здравствуйте  у  меня  тоже  проблема  по  этой  тему,  дело  в  том  что  нужно  искать  в  не  большой  база  данных  двух  совпадение,  Москва  в  одном  ячейке а  002 рядом  и  ест  лист  в  котором  ест  тоже  в  одном  Москва  а  рядом  002,просто  там   ест  много  002  и  Москва,  но  я  хочу  во  первых  формула  нашла  именно  ту  Москву  с  которым  рядом  ест  002  и  второй    в  первом  листе   рядом  Москва  и  002   пишется  10 ,  а  втором  листе  рядом  Москва   002  ест  5   надо  чтобы  формула  нашла  эти  цифры  и  умножала  их  очень  прошу  помогите  у  меня  уже  голова  болит  от  этого  но  ничего  не  получается
Здравствуйте,
у меня следующая проблема: нужно выбрать из таблицы числа с определенным значением и суммировать их в привязке к определенному месяцу, т.е есть несколько строк с одинаковым признаком, но в разные даты и должна получиться одна итоговая сумма по месяцу, но сами числа привязаны к различным датам которые забиты в таблице в кратком формате даты (19.10.2014, 15.11.2014 и т.п).
17.01.2015 16:19:30
Николай, здравствуйте
В очередной раз встал перед задачей предположительно имеющей простое решение, но ...
Как реализовать выбор из таблицы оперируя двумя вводными, т.е. например выбрать значение которое соответствует определённому сочетанию значений из двух других столбцов при условии нахождения всех трёх (двух исходных и искомого) в одной строке
ИНДЕКС(; ПОИСКПОЗ( позволяет оперировать только одним столбцом или есть варианты?
11.03.2015 16:43:02
Николай, здравствуйте.
Подскажите, возможно ли с помощью данных функций осуществить следующее: имеется файл с 13 листами (12 из них имеют название месяцев и там содержаться соответствующие данные за этот месяц), а 13 итоговый с фильтром, при помощи которого можно задать диапазон месяцев (например, с мая по сентябрь или с января по ноябрь). На каждом листе имеются одинаковые по структуре таблицы (например указание объектов в строках и статей расходов в столбцах). В 13 итоговом листе содержится формула суммирования данных с других листов (идентичных по адресу ячеек) с учетом выбранных условий фильтра.
Помогите, пожалуйста, с написанием этой формулы.
12.03.2015 12:56:40
Афтар молодец! Куплю электронную книгу дабы поддержать энтузиазм :)
Добрый день, Николай. Функция ПОИСКПОЗ просматривает массив сверху вниз и, соответственно, возвращает первый порядковый номер аргумента:
ВасяМиша2
Миша
Маша
Жора
Миша
Валя
Пример (скрин) прилагается. Бьюсь несколько дней, но никак не получается, чтобы найти функцию, указывающую последний порядковый номер соответствующего аргумента в массиве. В нашем примере это "5". Подскажите, пожалуйста, функцию для решения этой задачи. Заранее спасибо!
17.10.2015 02:26:39
Подскажите пож-та как функция ПОИСКПОЗ может искать данные которые начинаются с определенных  символов , но эти символы располагаются в определенном столбце  . Т.е. в формулу нужно вбить не сами эти символы с которых  функция ищет , а именно ячейку нужно вбить  в формулу  .  Какой синтаксис в этом случае нужно использовать?
11.11.2015 15:20:24
очень важный урок
вопрос такой:
- есть прайс,
?:  нужно выбрать (найти) соответствие цены товара из диапазона допустимых цен товаров чтобы цену товара обозначить соответствующим именем
Как это сделать?
Страницы: 1  2  
Наверх