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

Как использовать функцию ВПР (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
Благодарю за ответ. Честно говоря, перед мной стояла немножко другая задача. Но как раз с помощью этой темы и информации с вашего форума её удалось решить.
Ещё раз спасибо.
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 и т.п).
Страницы: 1  2  
Наверх