Многоразовый ВПР (VLOOKUP)
Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:
Замечательная функция ВПР (VLOOKUP) в такой ситуации поможет только частично, т.к. умеет вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))
Ее надо ввести следующим образом:
- выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)
- ввести (скопировать формулу в первую ячейку) диапазона
- нажать Ctrl + Shift + Enter
Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5
Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:
=ЕСЛИ(ЕОШ(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)));"";ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)))
В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА - она позволяет решить задачу более компактно:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5));"")
P.S.
В англоязычной версии Excel эти функции будут выглядеть так:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))),"",INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5)),"")
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для нахождения данных в таблице
- Улучшенный вариант функции ВПР (VLOOKUP2), который умеет искать в любом столбце и не только первое значение
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
- Что такое формулы массива и с чем их едят
1) выделять строку вместо столбца перед вставкой формулы массива
2) изменить формулу из примера в части "СТРОКА()-5" соответственно на "СТОЛБЕЦ()-Х", где Х=номер первого столбца в списке минус 1
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")
Формулу протянуть вниз на несколько ячеек.
Не я придумал, из уроков Mike Girvin
А не могли бы вы подробнее разобрать данную формулу
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")
Подставить её легко можно изменив на нужные диапазоны, но нет понимания откуда что берётся.
Заранее спасибо!
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/(($E$2=$A$2:$A$16)+($E$3=$A$2:$A$16)+($D$10=$A$2:$A$16));СТРОКА()-5));"";)
Добавляем через + в скобочках а так же не забываем общие скобки
Так же ссылку можно заменить на текст
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/(("Вася"=$A$2:$A$16)+("петя"=$A$2:$A$16)+("маша"=$A$2:$A$16));СТРОКА()-5));"";)
Как то так
Заранее спасибо
Чтобы увидеть что происходит в какой-либо части функции нужно выделить ее, нажать F9. Возврат в прежнее состояние Ctrl+Z или Esc. Как происходит вычисление функции (формулы): панель ФОРМУЛЫ -->ВЫЧИСЛИТЬ ФОРМУЛУ.
пример СУПЕРСКИЙ-РЕСПЕКТ АВТОРУ. Мне облегчит жизнь очень и очень.
Пользуюсь excel-ем не первый день, но никогда не приходилось через панель "ФОРМУЛЫ -->ВЫЧИСЛИТЬ ФОРМУЛУ" смотреть, что и как вычисляется. Вчера пытался разобраться, но не понял сути (может есть инфа где почитнуть можно что бы разобраться?).
ПОЖАЛУЙСТА, РАЗЖУЙТЕ ДАННЫЙ КУСОЧЕК ФОРМУЛЫ. Поскольку применить для своих данных не получается.
НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"";);СТРОКА()-5))
Массив=$B$2:$B$16, Номер_строки=НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)
НАИМЕНЬШИЙ(Массив;k), отсюда
Массив=ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;""), k=СТРОКА()-5)
ЕСЛИ(лог_выраж.;знач._если_истина;знач._если_ложь), отсюда
если значение $E$2 равно значению из диапазона A2:A16, если ИСТИНА, то СТРОКА(В2:В16)-1, иначе пусто "".
Здесь СТРОКА(В2:В16) получает массив строк ={2:3:4:5:6:7:8:9:10:11:12:13:14:15:16}, а вычитаем 1 для уменьшения массива до = {1:2:3:4:5:6:7:8:9:10:11:12:13:14:15}.
То же самое и с k:
Функция СТРОКА() в ячейке D6 (как в примере) получает {6}, т.е. номер строки, вычитаем 5, получаем {1}
В ячейке D7 получаем {7}, вычитаем 5, получаем {2}
Если бы диапазон "В заказ входят" начинался бы к примеру не с D6, а с D3, то мы бы отняли ......
смекайте......
правильно! Два
Как мог...
Я бы прикрепил пример но как это тут сделать не понятно??
С Вашей помощью удалось создать компактный проект. И на этом волшебство этой формулы не заканчивается: часто формулы возвращают нули, текст нулевой длины или просто есть пустые ячейки. Их можно удалять с помощью "Многоразового ВПР".
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")
Выделенная область является условием, что бы сделать больше условий необходимо так же через знак дроби (/) их прописать дальше. Единственное не получилось сделать условие с неопределенными типа A2&"*" в таком случае результат не выдавался.
Все получилось)
У меня не работает доп. условие через дробь, выдает ошибку #ЧИСЛО (условие ищется в том же диапазоне что и первое)
Вас не затруднит прописать на примере выше доп условие через "/" с произвольными данными просто для понимания структуры формулы.
Заранее большое спасибо!
Нигде не смог найти подобное, вся надежда на Вас!
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/(($E$2=$A$2:$A$16)+($E$3=$A$2:$A$16)+($D$10=$A$2:$A$16));СТРОКА()-5));"";)
Добавляем через + в скобочках а так же не забываем общие скобки
Так же ссылку можно заменить на текст
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/(("Вася"=$A$2:$A$16)+("петя"=$A$2:$A$16)+("маша"=$A$2:$A$16));СТРОКА()-5));"";)
Чтобы данные выводились в строку, можно сделать так. На листе с примером выделите диапазон F2:I2
и введите в него как формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($A2=$A$2:$A$16;СТРОКА($B$2:$B$16)-1;"");СТОЛБЕЦ()-5));"")
Затем формулу можно "протянуть" вниз.
В строках диапазона F2:I2 напротив номера заказа по столбцам будут разнесены товары, которые в него входят. Затем формулой их можно сцепить в одну ячейку.
Спасибо, если поняли.
можно ли исключить дубликаты?
т.е. если номер заказа и товар совпадает несколько раз, то в итоговую таблицу попадало только одно значение?
заранее спасибо!
воспользовался "Способ 3. Выборка уникальных записей формулой"
здравствуйте, помогите с решением проблемы.
суть такова.
есть файл с двумя листами (лист1 и лист2) на листе1 выгрузка из программы (таблица excel) очень много строк и столбцов с данными (даты, суммы, двадцатизначные номера и т.д.)
на листе2 в ячейке А2 выпадающий список со уникальными значениями из столбца А2:А7000 листа 1. в ячейку В2 заведена заведена формула =ЕСЛИ(ЕНД(ВПР...... либо (=ИНДЕКС(ПОИСКПОЗ....) с поиском и подстановкой всех имеющихся значений из листа 1 (т.е. грубо говоря лист 2 пустой в нем выпадающий список и формула, протянутая на весь массив данных как ы листе1.
лист 2 заполняется по мере поступления заказа в обработку.
проблема 1: искать значения в выпадающем списке долго (их более 7000), заводить вручную значения из выпадающего списка тоже долго (значения двадцатизначные, можно ошибиться) какую формулу либо макрос можно применить, чтобы через фильтр искать нужные значения в выпадающем списке допустим по первым пяти введенным знакам иили более и выбирать уже из фильтра.
проблема2: при вводе значения на листе 2 вручную или через выпадающий список, значения листа 1 либо вводят сведения других срок, либо указывают на ссылку. причем если значения выпадающего списка вводятся по порядку, то бпроблем нет а если выборочно, то сведения в ячейках листа 2 не соответствуют сведениям ячеек листа 1.
Подскажите, пж, а как сделать так. чтобы в массиве выбиралось по значениям меньше и больше.
Например, имеем три столбца для подбора оборудования, в первом столбце значение объема помещения min, во втором - max, а в третьем наименование. Задан объем комнаты, например, 2,4 m3 и надо, что бы показало два котла (котел 1 и котел 1.1)
Т.к. данные в искомом диапазоне представленны в двух вариантах ссылкой, и частью ссылки, для этого использованна "MID" функция.
ячейка пустая, но когда смотришь формулу подробно в развернутом варианте, значение формула определяет верно.
Помогите, пожалуйста, разобраться почему выводится пустое значение и как получить искомое?
В моем случае заработало не с первого раза: в диапазоне ячеек была одна со значением #Н/Д
Главное не просто Enter нажимать, а Ctrl + Shift + Enter!
...и тогда все будет работать!
Не подскажите,как и что нужно добавить чтобы поиск был по 2-м переменным?
То есть в данном примере идет поиск по Е2 (№ заказа) ,а что если мне нужно добавить еще одно условие?
Например эта таблица будет иметь еще колонку с датами.Тогда мне нужно искать и по №заказа и по дате.И вывести все,что будет соответствовать этим двум параметрам.Заранее спасибо!
Примерно так: =IF(AND(A2=$P$1;G2=$Q$1);ROW(C2)-1) - это конкретно из моего отчета.
В столбце C (на видео) прописываем двойное условие, и заводим ещё одну ячейку с нужным параметром ( как Е2), к примеру в F2.
Обе эти ячейки я сделал выпадающим списком: при пересечении двух условий выводится нужный результат. (Работаю с клиентской базой в отделе продаж). Условия в моем случае это маршрут продаж и день недели, при "пересечении" которых выпадает список клиентов.
Ещё раз СПАСИБО! Для меня это большой шаг в собственном развитии!
Подскажите пож., как можно сделать так, чтобы из этого результата получить выпадающий список, без того чтобы они записывались на лист?
Пробовал эту формулу вставить в "источник списка" он ничего не находит(
Добавьте пожалуйста в описание.
Подскажите , пожалуйста, возможно ли применить многоразовый ВПР для нескольких страниц?
Я применяла функцию =ИНДЕКС($L$2:$L$35;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$L$2:$L$35&"'!D4: D200";D3)>0;0))
Данная функция работает, но отбирает только лишь один показатель по критерию отбора , а их допустим три на всех имеющихся листах.
Подскажите пожалуйста формулу в случае если оставить в исходной задаче все без изменения кроме того, что за место цифр в столбце "номер заказа" подставить слова и искать не по точному совпадению, а по наличию слова в ячейке. Например нужно вытащить все номера изделий, напротив, которых в столбце "цвет краски" имеется слово "синий":
Кажется, у меня есть решение Вашей задачи, если, конечно, решение еще не найдено.
Помогите, пожалуйста, с такими трудностями:
1.Как вывести результаты по заданных критериях в виде списка в одном столбце подряд?
Т.е. если произошла выборка всех результатов согласно первого критерия, то приступить ко второму и выдать результаты согласно второго критерия под результатами первого...
Возможно ли это прописать формулой?
Если исключительно макросом, то подскажите, пожалуйста, где искать и на что опираться.
Очень нужно!
Заранее спасибо!
К тому же я любитель!
Вот что произошло с предлагаемой вами формулой. Задачи одинаковые поменялись лишь аргументы ( номера столбцов и строк,- диапазоны)
Ваша формула
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))
Мой аналог
=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),ROW()-5)) не работает: менял оба смещения подбирал.
=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),1)) с этим изменением работает.
Вопросов много. Нужен диалог
Вот одна подсказка:
"Думаю да. Во всяком случае с ВПР у меня такое работало.
Как-то так попробуйте:
=ЕСЛИ(ЕНД(A1);"Мама";"Рама") "
Пробую
И еще мне нужно чтобы информация , собиралась не по одному столбцу, а по 2-3.
Помогите пожалуйста
как улушить мульти ВПР чтоб он искал по выбранным столбцам?
Задача: таблица с названием магазинов в 6 торговых центрах, мне необходимо создать обновляемую таблицу где можно будит из выпадающего списка выбрать название торгового центра и будут автоматически появяться все магазины которые есть в нем.