Генеричка формула
=MAX(INDEX(data,0,MATCH(column,header,0)))
Резиме
Да бисте преузели максималну вредност у скупу података, где је колона променљива, можете да користите ИНДЕКС и МАТЦХ заједно са функцијом МАКС. У приказаном примеру формула у Ј5 је:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
где су подаци (Б5: Ф15) и заглавље (Б4: Ф4) именовани опсези.
Објашњење
Напомена: Ако сте нови корисник ИНДЕКС-а и МАТЦХ-а, погледајте: Како се користе ИНДЕКС-ови и МАТЦХ-ови
У стандардној конфигурацији функција ИНДЕКС преузима вредност у датом реду и колони. На пример, да бисте добили вредност у реду 2 и колони 3 у датом опсегу:
=INDEX(range,2,3) // get value at row 2, column 3
Међутим, ИНДЕКС има посебан трик - могућност преузимања читавих колона и редова. Синтакса укључује нулу за аргумент „други“. Ако желите целу колону, ред дајете као нулу. Ако желите цео ред, колону наводите као нулу:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
У приказаном примеру желимо да пронађемо максималну вредност у датој колони. Преокрет је у томе што колона мора бити променљива како би се лако могла променити. У Ф5, формула је:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Радећи изнутра, прво користимо функцију МАТЦХ да бисмо добили „индекс“ колоне тражене у ћелији Ј4:
MATCH(J4,header,0) // get column index
Са "Греен" у Ј4, функција МАТЦХ враћа 3, јер је Греен трећа вредност у заглављу именованог опсега . Након што МАТЦХ врати резултат, формула се може поједноставити на ово:
=MAX(INDEX(data,0,3))
Са нулом наведеном као број реда, ИНДЕКС враћа све вредности у колони 3 именованих података опсега . Резултат се враћа у функцију МАКС у низу попут овог:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
И МАКС враћа коначни резултат, 93.
Минимална вредност
Да бисте добили минималну вредност променљивим ступцем, можете једноставно заменити функцију МАКС функцијом МИН. Формула у Ј6 је:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Са ФИЛТЕР
Нова функција ФИЛТЕР такође се може користити за решавање овог проблема, јер ФИЛТЕР може филтрирати податке по реду или колони. Трик је у томе да се направи логички филтер који ће изузети друге колоне. ЦОУНТИФ у овом случају добро функционише, али мора бити конфигурисан „уназад“, са Ј4 као опсегом и заглављем за критеријуме:
=MAX(FILTER(data,COUNTIF(J4,header)))
После ЦОУНТИФ покретања имамо:
=MAX(FILTER(data,(0,0,1,0,0)))
А ФИЛТЕР испоручује трећу колону на МАКС, исто као и горња функција ИНДЕКС.
Као алтернативу ЦОУНТИФ-у уместо тога можете да користите ИСУМБЕР + МАТЦХ:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Функција МАТЦХ се поново поставља „уназад“, тако да добијамо низ са 5 вредности који ће служити као логички филтер. Након ИСНУМБЕР и МАТЦХ трчања имамо:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
И ФИЛТЕР поново испоручује трећу колону на МАКС.
