Yleinen kaava
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Yhteenveto
Isot ja pienet kirjaimet Excelissä
Oletuksena Excelin vakiohaut eivät eroa kirjainkokoja. Sekä VLOOKUP että INDEX / MATCH yksinkertaisesti palauttavat ensimmäisen ottelun ohittamatta kirjainkokoa.
Suora tapa kiertää tämä rajoitus on käyttää taulukkoa, joka perustuu INDEX / MATCH ja EXACT. Kuitenkin, jos etsit vain numeerisia arvoja, SUMPRODUCT + EXACT tarjoaa myös mielenkiintoisen ja joustavan tavan tehdä kirjainkoon huomioon ottava haku.
Tässä esimerkissä käytämme seuraavaa kaavaa
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Vaikka tämä kaava on matriisikaava, sitä ei tarvitse syöttää painamalla Control + Vaihto + Enter, koska SUMPRODUCT käsittelee taulukoita natiivisti.
Selitys
SUMPRODUCT on suunniteltu toimimaan matriisien kanssa, jotka se kertoo, sitten summaa.
Tässä tapauksessa olemme kaksi ryhmää SUMPRODUCT: B3: B8 ja C3: C8. Temppu on suorittaa testi sarakkeessa B olevista arvoista ja muuntaa sitten saadut TOSI / EPÄTOSI-arvot arvoksi 1 ja 0. Suoritamme testin EXACT-muodossa seuraavasti:
EXACT(E3,B3:B8)
Mikä tuottaa tämän taulukon:
(EPÄTOSI; EPÄTOSI; TOSI; EPÄTOSI; EPÄTOSI; EPÄTOSI)
Huomaa, että todellinen arvo sijainnissa 3 on osumamme. Sitten käytämme kaksoisnegatiivia (ts. -, joka on teknisesti "kaksinkertainen unary") pakottaaksemme nämä TOSI / EPÄTOSI-arvot osiksi 1 ja 0. Tuloksena on tämä taulukko:
(0; 0; 1; 0; 0; 0)
Laskennan tässä vaiheessa SUMPRODUCT-kaava näyttää tältä:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT kertoo sitten yksinkertaisesti kunkin taulukon kohteet yhdessä lopullisen taulukon tuottamiseksi:
(0; 0; 775; 0; 0; 0)
Mikä SUMPRODUCT sitten summaa ja palauttaa 775.
Joten tämän kaavan ydin on se, että FALSE-arvoja käytetään kaikkien muiden arvojen kumoamiseen. Ainoat arvot, jotka selviävät, ovat TOSIA.
Huomaa, että koska käytämme SUMPRODUCTia, tällä kaavalla on ainutlaatuinen kierre: jos vastaavuuksia on useita, SUMPRODUCT palauttaa vastaavuuksien summan. Tämä voi olla tai ei, mitä haluat, joten ole varovainen, jos odotat useita otteluita!
Muista, että tämä kaava toimii vain numeerisissa arvoissa, koska SUMPRODUCT ei käsittele tekstiä. Jos haluat hakea tekstiä, käytä INDEX / MATCH + EXACT.
