ВПР - одна из наиболее часто используемых функций в Excel. Он ищет значение в диапазоне и возвращает соответствующее значение в указанном номере столбца.
Теперь я столкнулся с проблемой, когда мне пришлось искать всю строку и возвращать значения во всех столбцах из этой строки (вместо возврата одного значения).
Итак, вот что мне нужно было сделать. В приведенном ниже наборе данных у меня были имена торговых представителей и их продажи за 4 квартала 2012 года. У меня был раскрывающийся список с их именами, и я хотел извлечь максимальные продажи для этого торгового представителя за эти четыре квартала.
Я мог бы придумать 2 разных способа сделать это - с помощью ИНДЕКСА или ВПР.
Поиск всей строки / столбца с помощью формулы ИНДЕКС
Вот формула, которую я создал для этого с помощью индекса
= НАИБОЛЬШИЙ (ИНДЕКС ($ B $ 4: $ F $ 13; ПОИСКПОЗ (H3; $ B $ 4: $ B $ 13,0); 0); 1)
Как это устроено:
Давайте сначала посмотрим на функцию ИНДЕКС, заключенную в функцию НАИБОЛЬШИЙ.
= ИНДЕКС ($ C $ 4: $ F $ 13; ПОИСКПОЗ (H3; $ B $ 4: $ B $ 13,0); 0)
Давайте внимательно проанализируем аргументы функции ИНДЕКС:
- Массив - $ B $ 4: $ F $ 1
- Номер строки - ПОИСКПОЗ (H3, $ B $ 4: $ B $ 13,0)
- Номер столбца - 0
Обратите внимание, что я использовал номер столбца как 0.
Уловка здесь в том, что когда вы используете номер столбца как 0, он возвращает все значения во всех столбцах. Поэтому, если я выберу Джона в раскрывающемся списке, формула индекса вернет все 4 значения продаж для Джона {91064,71690,67574,25427}.
Теперь я могу использовать функцию Large для извлечения наибольшего значения
Совет от профессионала - используйте номер столбца / строки как 0 в формуле индекса, чтобы вернуть все значения в столбцах / строках.
Поиск всей строки / столбца с помощью формулы ВПР
Хотя формула индекса аккуратна, понятна и надежна, способ ВПР немного сложен. Это также приводит к изменению функции. Однако есть удивительный трюк, которым я бы поделился в этом разделе. Вот формула:
= НАИБОЛЬШИЙ (ВПР (H3; B4: F13; СТРОКА (КОСВЕННО ("2:" & СЧЁТ ($ B $ 4: $ F $ 4))); ЛОЖЬ); 1)
Как это устроено
- ROW (INDIRECT («2:» & COUNTA ($ B $ 4: $ F $ 4))) - эта формула возвращает массив {2; 3; 4; 5}. Обратите внимание: поскольку он использует КОСВЕННО, это делает эту формулу изменчивой.
- ВПР (H3, B4: F13, ROW (INDIRECT («2:» & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - вот лучшая часть. Когда вы складываете их вместе, получается ВПР (H3, B4: F13, {2; 3; 4; 5}, FALSE). Теперь обратите внимание, что вместо одного номера столбца я дал ему массив номеров столбцов. А ВПР послушно ищет значения во всех этих столбцах и возвращает массив.
- Теперь просто используйте функцию НАИБОЛЬШИЙ, чтобы извлечь наибольшее значение.
Не забудьте использовать Control + Shift + Enter, чтобы использовать эту формулу.
Совет от профессионала. В режиме VLOOKUP вместо использования одного номера столбца, если вы используете массив номеров столбцов, он вернет массив значений поиска.
