Bendroji formulė
=MAX(INDEX(data,0,MATCH(column,header,0)))
Santrauka
Norėdami gauti maksimalią reikšmę duomenų rinkinyje, kai stulpelis yra kintamas, galite naudoti INDEX ir MATCH kartu su MAX funkcija. Parodytame pavyzdyje formulė J5 yra:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
kur duomenys (B5: F15) ir antraštė (B4: F4) yra pavadinti diapazonais.
Paaiškinimas
Pastaba: Jei dar nesinaudojote „INDEX“ ir „MATCH“, žr
Standartinėje konfigūracijoje funkcija INDEX gauna vertę nurodytoje eilutėje ir stulpelyje. Pvz., Norėdami gauti 2 eilutės ir 3 stulpelio vertes nurodytame diapazone:
=INDEX(range,2,3) // get value at row 2, column 3
Tačiau „INDEX“ turi ypatingą triuką - galimybę gauti ištisus stulpelius ir eilutes. Sintaksėje nurodomas nulis argumentui „kitas“. Jei norite viso stulpelio, eilutę pateikite kaip nulį. Jei norite visos eilutės, stulpelį pateikite kaip nulį:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Parodytame pavyzdyje norime rasti didžiausią vertę tam tikrame stulpelyje. Posūkis yra tas, kad stulpelis turi būti kintamas, kad jį būtų galima lengvai pakeisti. F5 formulė yra:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Dirbdami iš vidaus, mes pirmiausia naudojame funkciją MATCH, kad gautume J4 langelyje reikalaujamo stulpelio „indeksą“:
MATCH(J4,header,0) // get column index
Kai J4 yra „žalia“, funkcija MATCH grąžina 3, nes žalia yra trečioji vertė pavadinto diapazono antraštėje . Kai MATCH grąžins rezultatą, formulę galima supaprastinti:
=MAX(INDEX(data,0,3))
Nurodžius nulį kaip eilutės numerį, INDEX grąžina visas reikšmes pavadintų diapazono duomenų 3 stulpelyje . Rezultatas grąžinamas į MAX funkciją tokiu masyvu:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Ir MAX grąžina galutinį rezultatą - 93.
Minimali vertė
Norėdami gauti mažiausią vertę su kintamu stulpeliu, galite tiesiog pakeisti funkciją MAX funkcija MIN funkcija. J6 formulė yra:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Su FILTER
Naują FILTER funkciją taip pat galima naudoti šiai problemai išspręsti, nes FILTER gali filtruoti duomenis pagal eilutes arba stulpelius. Apgaulė yra sukurti loginį filtrą, kuris pašalins kitus stulpelius. Šiuo atveju COUNTIF veikia gerai, tačiau jis turi būti sukonfigūruotas „atgal“, o diapazonas yra J4 ir kriterijų antraštė :
=MAX(FILTER(data,COUNTIF(J4,header)))
Paleidus COUNTIF, turime:
=MAX(FILTER(data,(0,0,1,0,0)))
O „FILTER“ pristato 3-ąjį stulpelį MAX, kaip ir aukščiau esanti INDEX funkcija.
Kaip alternatyvą COUNTIF vietoj to galite naudoti ISNUMBER + MATCH:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Funkcija MATCH vėl nustatoma „atgal“, kad gautume masyvą su 5 reikšmėmis, kuris bus naudojamas kaip loginis filtras. Po ISNUMBER ir MATCH bėgimo turime:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Filtras vėl pateikia 3 stulpelį MAX.
