Üldine valem
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Kokkuvõte
Suur- ja väiketähtede otsimine Excelis
Vaikimisi ei ole Exceli standardsed otsingud tõstutundlikud. Nii VLOOKUP kui ka INDEX / MATCH tagastavad esimese matši lihtsalt juhtumeid eirates.
Selle piirangu otseseks lahendamiseks on massiivi valemi kasutamine, mis põhineb INDEX / MATCH ja EXACT. Kui otsite ainult arvväärtusi, pakub SUMPRODUCT + EXACT ka huvitavat ja paindlikku viisi väiketähtede otsimiseks.
Näites kasutame järgmist valemit
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Kuigi see valem on massiivivalem, ei pea seda sisestama klahvidega Control + Tõst + Enter, kuna SUMPRODUCT käsitab massiive loomulikult.
Selgitus
SUMPRODUCT on loodud töötama massiividega, mida see korrutab, seejärel summeerib.
Sel juhul oleme kaks massiivi, millel on SUMPRODUCT: B3: B8 ja C3: C8. Trikk seisneb selles, et käivitatakse veerus B olevate väärtuste test, seejärel teisendatakse saadud TÕENE / VÄÄR väärtus 1 ja 0 väärtuseks. Me viime testi läbi täpselt nii:
EXACT(E3,B3:B8)
Mis loob selle massiivi:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Pange tähele, et 3. positsiooni tegelik väärtus on meie vaste. Seejärel kasutame topeltnegatiivi (st -, mis on tehniliselt "topeltunar") nende TRUE / FALSE väärtuste sundimiseks 1 ja 0. Tulemuseks on see massiiv:
(0; 0; 1; 0; 0; 0)
Arvutamise hetkel näeb valem SUMPRODUCT välja selline:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Seejärel korrutab SUMPRODUCT iga massiivi elemendid lõpliku massiivi saamiseks:
(0; 0; 775; 0; 0; 0)
Milline SUMPRODUCT siis summeerib ja tagastab 775.
Nii et selle valemi põhisisu on see, et FALSE väärtusi kasutatakse kõigi muude väärtuste tühistamiseks. Ainus väärtus, mis säilib, on need, mis olid tõesed.
Pange tähele, et kuna kasutame SUMPRODUCTi, on selle valemiga ainulaadne keerdkäik: kui vasteid on mitu, tagastab SUMPRODUCT nende vastete summa. See võib olla see, mida soovite, aga ei pruugi olla, nii et olge ettevaatlik, kui eeldate mitu vastet!
Pidage meeles, et see valem töötab ainult arvväärtuste korral, kuna SUMPRODUCT ei käivitu tekstiga. Kui soovite teksti hankida, kasutage INDEX / MATCH + EXACT.
