„Excel“ formulė: kelių kriterijų paieška ir perkėlimas -

Turinys

Bendroji formulė

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Santrauka

Norėdami atlikti kelių kriterijų paiešką ir perkelti rezultatus į lentelę, galite naudoti masyvo formulę, pagrįstą INDEX ir MATCH. Parodytame pavyzdyje formulė G5 yra:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Atkreipkite dėmesį, kad ši formulė yra masyvo formulė ir ją reikia įvesti paspaudus „Control + Shift“ + „Enter“.

Šioje formulėje taip pat naudojami trys įvardyti diapazonai: vieta = B5: B13, suma = D5: D13, data = C5: C13

Paaiškinimas

Šios formulės esmė yra INDEX, kuri gauna reikšmę iš pavadinto diapazono „suma“ (B5: B13):

=INDEX(amount,row_num)

kur eilutės_numas yra parengtas naudojant funkciją MATCH ir tam tikrą loginę logiką:

MATCH(1,($F5=location)*(G$4=date),0)

Šiame fragmente vieta F5 palyginama su visomis vietomis, o G4 data - su visomis datomis. Kiekvienu atveju rezultatas yra TRUE ir FALSE reikšmių masyvas. Kai šie masyvai padauginami kartu, matematikos operacija priverčia TRUE ir FALSE reikšmes vienai ir nulinei, kad paieškos masyvas, einantis į MATCH, atrodytų taip:

(1;0;0;0;0;0;0;0;0)

„MATCH“ yra nustatytas taip, kad atitiktų 1 tikslią atitiktį, ir grąžina poziciją į INDEX kaip eilutės numerį. Skaičius 1 veikia pagal paieškos vertę, nes masyve dabar yra tik 1 ir 0, kaip parodyta aukščiau.

F5 ir G4 įrašomi kaip mišrios nuorodos, kad formulę būtų galima nukopijuoti per lentelę be pakeitimų.