Слова содержащие шен

Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:

vlookup_all1.gif

 

Замечательная функция ВПР (VLOOKUP) в такой ситуации поможет только частично, т.к. умеет вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:

=ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($E=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Ее надо ввести следующим образом:

  1. выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)
  2. ввести (скопировать формулу в первую ячейку) диапазона
  3. нажать Ctrl + Shift + Enter

Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5

Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:

=ЕСЛИ(ЕОШ(ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($E=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)));"";ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($E=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)))

В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА - она позволяет решить задачу более компактно:

=ЕСЛИОШИБКА(ИНДЕКС($B:$B;НАИМЕНЬШИЙ(ЕСЛИ($E=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5));"")

P.S.

В англоязычной версии Excel эти функции будут выглядеть так:

=INDEX($B:$B,SMALL(IF($E=A2:A16,ROW(B2:B16)-1,""),ROW()-5))

=IF(ISERR(INDEX($B:$B,SMALL(IF($E=A2:A16,ROW(B2:B16)-1,""),ROW()-5))),"",INDEX($B:$B,SMALL(IF($E=A2:A16,ROW(B2:B16)-1,""),ROW()-5)))

=IFERROR(INDEX($B:$B,SMALL(IF($E=A2:A16,ROW(B2:B16)-1,""),ROW()-5)),"")

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

 


Источник: http://www.planetaexcel.ru/techniques/2/81/

Закрыть ... [X]

С.И.Ожегов, Н.Ю.Шведова. Толковый словарь русского языка (С-Я) Поздравительные открытки мастер-класс



Слова содержащие шен Многоразовый ВПР (VLOOKUP ) - Планета Excel
Слова содержащие шен Usage Statistics for - February 2004
Слова содержащие шен Live4Fun - приколы: фото, видео приколы, смешные
Слова содержащие шен NKALE :-) В каждой игрушке сердце
Слова содержащие шен Антитренды 2017: что не модно носить в новом году Блог
Слова содержащие шен Ассортиментный код консервированной рыбы
Слова содержащие шен Видео о макияже - видео уроки для начинающих, как правильно
Слова содержащие шен Вино из винограда в домашних условиях. Рецепт виноградного
Выкройка рубашки на мальчика Выращивание пеларгоний, полив, обрезка, подкормка Вязание спицами. Схемы вязания, модели и узоры спицами Игрушки, поделки из фетра Записи в рубрике игрушки Как из подручных средств и бросового материала Как нарисовать сказку о царе Салтане Купить кухонную мойку. Кухонные мойки, цены, Киев. Каталог Осинка Плетение из бисера для начинающих - фото, схемы, видео