VLOOKUP е една от най -използваните функции в Excel. Той търси стойност в диапазон и връща съответна стойност в определен номер на колона.
Сега се натъкнах на проблем, при който трябваше да прегледам целия ред и да върна стойностите във всички колони от този ред (вместо да връщам единична стойност).
Ето какво трябваше да направя. В набора от данни по -долу имах имената на търговските представители и продажбите, които те направиха за 4 тримесечия през 2012 г. Имах падащо меню с техните имена и исках да извлека максималните продажби за този търговски представител през тези четири тримесечия.
Бих могъл да измисля 2 различни начина за това - Използване на INDEX или VLOOKUP.
Търсете целия ред / колона, използвайки формулата INDEX
Ето формулата, която създадох, за да направя това с помощта на Index
= ГОЛЯМ (ИНДЕКС ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Как работи:
Нека първо разгледаме функцията INDEX, която е обвита във функцията LARGE.
= ИНДЕКС ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Нека анализираме отблизо аргументите на функцията INDEX:
- Масив - $ B $ 4: $ F $ 1
- Номер на ред - MATCH (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 (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Как работи
- ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Тази формула връща масив {2; 3; 4; 5}. Имайте предвид, че тъй като използва INDIRECT, това прави тази формула променлива.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Ето най -добрата част. Когато ги съберете, той става VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Сега забележете, че вместо един номер на колона, аз съм му дал масив от номера на колони. И VLOOKUP послушно търси стойности във всички тези колони и връща масив.
- Сега просто използвайте функцията LARGE, за да извлечете най -голямата стойност.
Не забравяйте да използвате Control + Shift + Enter, за да използвате тази формула.
Професионален съвет - В VLOOKUP, вместо да използвате единичен номер на колона, ако използвате масив от номера на колони, той ще върне масив от стойности за търсене.
