Поиск нужных данных в диапазоне
Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы - загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.
Если же вы знакомы с ВПР, то - вдогон - стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:
Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.
Задача решается при помощи двух функций:
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 - означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.
Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
- Улучшенная версия функции ВПР (VLOOKUP)
- Многоразовый ВПР
Например у меня есть таблица и надо найти число по 2 параметрам (к примеру по x и y значениям).
=индекс(массив_данных;(поискоз(номер_строки;массив_строк;0));(поискпоз(код_стобца;массив_столбцов;0))).
НО,
если искомое значение не найдено, тогда функция возвращает "#Н/Д"
ВОПРОС: как сделать, чтобы вместо "#Н/Д" значение ячейки равнялось нулю ?
Сочетание ИНДЕКС+ПОИСКПОЗ можно использовать, если нужно не только выбрать из исходной таблицы одно значение, но и целые строчки (например, из таблицы продаж, в которой также подсчитаны промежуточные итоги по месяцам, перенести в новую таблицу только строчки с промежуточными итогами).
Решение: в функции ИНДЕКС и ПОИСКПОЗ закрепить диапазоны полностью в кажной, а также закрепить ячейку, по которой ищется совпадение так, чтобы по столбцам не смещалась. Пример:
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
число 2 это что?
Предположим что у артикула товара 8985 не один а два региона. Возможно ли решение при условии что регионы будут записаны в одну ячейку?
Если вам нужно вывести все регионы для заданного артикула, то придется использовать более хитрые конструкции - см.
Ещё раз спасибо.
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0), но ничего не получается...
Спасибо.
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!C700:F900;$C$700:$F$900;ПОИСКПОЗ($A700;$C$700:$C$900;0);1);0)
У функции ИНДЕКС три аргумента, а у вас - четыре. Что-то лишнее
название листов находятся в столбце А. (не смог только разобраться с большим количеством кавычек, но работает)
=INDEX(INDIRECT("'"&$A5&"'!$A$8:$Z$50");MATCH($M$1;INDIRECT("'"&$A5&"'!$B$8:$B$50");0);J$4)
И ворой вопрос: В интернете я открываю свой киви кошелек и вижу сумму, а можно ли сделать через гиперссылку чтобы программа видела остаток по кошельку на данный момент не заходя в интернет? Заранее спасибо. Ирина
При поиске ближайшего наибольшего - по убыванию.
У вас так?
Пробовал задавать диапазон ( артикулов) как текст, все равно выдает ошибку.Что нужно сделать чтобы её исправить?
А не лучше ли сделать то же самое с помощью функции ПРОСМОТР?
=ПРОСМОТР(C16;D2:D13;B2:B13)
Помоему проще и для понимания и для реализации.
Это мой первый комментарий.
В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!
Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.
Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.
Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.)
Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?
Заранее благодарю за помощь!
=ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)
на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало - непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.
Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.
Функция по потенциалу понравилась, но как отрабатывает конкретно у меня - нет.
ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.
Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.
ОГРОМНОЕ ВАМ НИКОЛАЙ СПАСИБО!!!
Как выполнить поиск в диапазоне одновременно по двум позициям? Т.е. если на примере в теме Поиска данных в диапазоне известен регион и желаемая цена(приблизительно), а нужно найти и вывести в ячейку количество.
ИНДЕКС И ПОИСКПОЗ имеют по одному значению
Заранее благодарю)
Если нужно искать приблизительно, то простого решения нет.
которая подарила мне уйму свободного времени.
отличная формула очень часто ее использую
однако столкнулся с небольшой проблемой, подставляемые данные вытаскиваю из другого файла, т.е. в формуле у меня стоит ссылка на другой файл. И очень часто когда открываешь файл с формулой ИНДЕКС(ПОИСКПОЗ...) он покрывается ссылками, лечиться только открытием файла на который стоит ссылка в формуле. Не критично конечно но иногда очень не удобно. Это можно как нибудь вылечить?
Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)
=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);ПОИСКПОЗ(B17;A1:G1;0))
Ещё раз благодарю Вас!
С уважением, Вячеслав!
Многослойная шапка - например в строке 2 условия в столбце 2 условия а не по одному.
т.е.
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
а мне надо еще 2 условия добавить
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
Подскажите пожалуйста как это реализовать в одной формуле.
Спасибо!
Создайте лучше тему на форуме, приложите файлик - поможем.
Вы так быстро ответили , что я таблицу неуспел нарисовать ))
=ИНДЕКС(Диапазон значений таблицы;
ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);
ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);
ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);
ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);
т.е. значение готовое выберется не по 2-м условиям а по четырем
ПОИСКПОЗ ищет первое значение слева, а мне нужно ( в строке есть пустые ячейки ) найти крайнее правое. Вопрос: КАК?
(Последний аргумент функции 0 - означает поиск точного (а не приблизительного) соответствия. )
Искомое значение в ячейке C16 (авс)
0 заменил на 1 и почистил казалось-бы пустые ячейки (раньше формулой было записано "";) в строке.
И тут появляется "НО" - если в строке D1:D13 пустые ячейки появляются пару раз (например:
D1 D2 D3 D4 D5 D6 D7 D8 ....D13
(авс) (авс) (авс) ( ) ( ) (авс) (авс) ( ) ...(авс), то формула ПОИСКПОЗ выдаст значение D7, хотя должно быть D13.
Встречал на каком-то форуме ПОИСК (Ctrl+F) - значение (авс) - ВВОД (Shift+Enter). А как это записать формулой?
Если вы имели ввиду вопрос "как сделать так, чтобы формула находила не первое встретившееся, а последнее значение", то тут проще всего макросом, наверное - писать на VBA функцию аналогичную ВПР.
В вашем примере, формула =ВПР(C16;ВЫБОР({1;2};$D$2:$D$13;$B$2:$B$13);2;0)
сделает то-же самое.
Может кому пригодится для развития познаний.
Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению...?):
яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))
Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 - это тоже делает отчет удобнее. Теперь можно "играться" с разными значениями, просто выбирая их из выпадающего списка.
Спасибо!
у меня следующая проблема: нужно выбрать из таблицы числа с определенным значением и суммировать их в привязке к определенному месяцу, т.е есть несколько строк с одинаковым признаком, но в разные даты и должна получиться одна итоговая сумма по месяцу, но сами числа привязаны к различным датам которые забиты в таблице в кратком формате даты (19.10.2014, 15.11.2014 и т.п).
В очередной раз встал перед задачей предположительно имеющей простое решение, но ...
Как реализовать выбор из таблицы оперируя двумя вводными, т.е. например выбрать значение которое соответствует определённому сочетанию значений из двух других столбцов при условии нахождения всех трёх (двух исходных и искомого) в одной строке
ИНДЕКС(; ПОИСКПОЗ( позволяет оперировать только одним столбцом или есть варианты?
Подскажите, возможно ли с помощью данных функций осуществить следующее: имеется файл с 13 листами (12 из них имеют название месяцев и там содержаться соответствующие данные за этот месяц), а 13 итоговый с фильтром, при помощи которого можно задать диапазон месяцев (например, с мая по сентябрь или с января по ноябрь). На каждом листе имеются одинаковые по структуре таблицы (например указание объектов в строках и статей расходов в столбцах). В 13 итоговом листе содержится формула суммирования данных с других листов (идентичных по адресу ячеек) с учетом выбранных условий фильтра.
Помогите, пожалуйста, с написанием этой формулы.
вопрос такой:
- есть прайс,
?: нужно выбрать (найти) соответствие цены товара из диапазона допустимых цен товаров чтобы цену товара обозначить соответствующим именем
Как это сделать?
Подскажите, пожалуйста, есть ли возможность искать не в диапазоне, а в некоторых ячейках?
Ситуация следующая: у меня есть лист с данными и сводная, которая подтягивает максимальное значение по данным (не сквозной диапазон, а набор ячеек). Теперь мне необходимо понять какое текстовое значение соотносится с этим максимальным значением (соответственно из набора ячеек). Усложняется задача тем, что такое максимальное значение может встречаться не в одной строке...
Соответственно используя данный пример: У меня есть артикул = 15/02/16 - это максимальная дата, которая выбрана из строк 5,7 и 9 одного из столбцов. Далее мне нужно понять какой регион соответствует этой максимальной дате (артикулу), соответственно в этих же строках 5,7 и 9, но другого столбца. Логично предположить, что формула выберет первое значение, которое удовлетворит условию, но если в строках 5 и 7 стоит дата 15/02/16, как прописать, чтобы оба текстовых значения попадали, а строка 9 с датой 08/02 нет...
Спасибо!
есть таблица, из 2-х столбцов: № договора/сумма оплаты. по одному договору бывает несколько оплат.
И есть другая таблица( форма отчета) в которой эти договоры внесены в произвольном порядке.
Необходимо из первой таблицы сделать выборку по договорам, и внести оплаты во 2-ю таблицу. С этим справился с помощью "ВПР", но не знаю как быть когда по одному договору несколько сумм в первой таблице. как их сразу просуммировать?
Подскажите, каким образом можно находить данные по 2м критериям, если один из критериев не точный, а приблизительный (например, если критерий дата +- день)?
Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?
Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?
P/S На функцию еслиошибка реагирует только #Н/Д, а пустую ячейку все равно выдает как 0.1.1900
СПАСИБО:{}
Помогите пожалуйста по стоить формулу.
- есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним
-среднее выводиться с отдельную ячейку С69
задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин
=ИНДЕКС(B3:B61;ПОИСКПОЗ(C69;C3:C61;0))
B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)
Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.
Посмотрите, плиз! Может это у меня только так?
Дайте вашу почту, файл отправлю.
Есть сотрудники и даты. Я сверяю из одной таблицы фамилию сотрудника и дату. и хочу забрать значение на пересечении в другую таблицу на переселении фамилии и даты. т.е. нужно сравнить если дата и фамили совпадает то забираем значение в другую на место такого же совпадение.