Bendroji formulė
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Santrauka
Norėdami, kad „INDEX“ grąžintų elementų masyvą į kitą funkciją, galite naudoti neaiškią gudrybę, pagrįstą IF ir N funkcijomis. Parodytame pavyzdyje formulė E5 yra:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
kur „duomenys“ yra įvardytas diapazonas B5: B10.
Paaiškinimas
Nenuostabu, kad INDEX grąžina daugiau nei vieną reikšmę kitai funkcijai. Norėdami iliustruoti, šią formulę galima naudoti norint grąžinti pirmuosius tris nurodyto diapazono elementus "duomenys", kai jie įvedami kaip kelių langelių masyvo formulė.
(=INDEX(data,(1,2,3)))
Rezultatus galima pamatyti diapazone D10: F10, kuriame teisingai yra 10, 15 ir 20.
Tačiau jei formulę įvyniosime į funkciją SUM:
=SUM(INDEX(data,(1,2,3)))
Galutinis rezultatas yra 10, o jis turėtų būti 45, net jei jis įvestas kaip masyvo formulė. Problema ta, kad INDEX grąžina funkciją SUM tik į pirmąjį masyvo elementą. Norėdami priversti „INDEX“ grąžinti kelis elementus į SUM, masyvo konstantą galite apvynioti tokiose N ir IF funkcijose:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
kuris pateikia teisingą rezultatą 45. Panašiai ir ši formulė:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
teisingai pateikia 60, 10, 20 ir 30 sumą.
Ši neaiški technika kartais vadinama „nukrypimu“, nes ji neleidžia INDEX tvarkyti rezultatų kaip langelio nuorodų ir vėliau mesti visus masyvo elementus, išskyrus pirmąjį. Vietoj to, INDEX pateikia visą reikšmių masyvą į SUM. Jeffas Weiras turi gerą paaiškinimą apie „stackoverflow“.
Pastaba skaitytojams: nesu tikras, kodėl tai veikia. Jei galite pateikti aiškų paaiškinimą, aš jį atnaujinsiu.
