Многоразовый ВПР (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" функция.
ячейка пустая, но когда смотришь формулу подробно в развернутом варианте, значение формула определяет верно.
Помогите, пожалуйста, разобраться почему выводится пустое значение и как получить искомое?