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