Bendroji formulė
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Santrauka
Skirtingos didžiosios ir mažosios raidės „Excel“
Pagal numatytuosius nustatymus standartinės „Excel“ peržiūros neskiria didžiosios ir mažosios raidės. Tiek „VLOOKUP“, tiek „INDEX / MATCH“ tiesiog grąžins pirmąsias rungtynes, nepaisydami didžiųjų raidžių.
Tiesioginis būdas apeiti šį apribojimą yra naudoti masyvo formulę, pagrįstą INDEX / MATCH su EXACT. Tačiau, jei ieškote tik skaitinių reikšmių, „SUMPRODUCT + EXACT“ taip pat suteikia įdomų ir lanksčią būdą atlikti didžiųjų ir mažųjų raidžių paiešką.
Pavyzdyje mes naudojame šią formulę
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Nors ši formulė yra masyvo formulė, jos nereikia įvesti paspaudus „Control + Shift“ + „Enter“, nes „SUMPRODUCT“ masyvus tvarko natūraliai.
Paaiškinimas
„SUMPRODUCT“ yra skirtas dirbti su masyvais, kuriuos jis padaugina, tada susumuoja.
Šiuo atveju mes esame du masyvai su SUMPRODUCT: B3: B8 ir C3: C8. Apgaulė yra atlikti B stulpelio verčių testą, tada gautas TRUE / FALSE reikšmes konvertuoti į 1 ir 0. Testą atliekame EXACT būdu taip:
EXACT(E3,B3:B8)
Kuria šią masyvą:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Atkreipkite dėmesį, kad tikroji 3 pozicijos vertė yra mūsų atitiktis. Tada mes naudojame dvigubą neigiamą (ty -, kuris techniškai yra „dvigubas vienarūšis“), kad šias TRUE / FALSE reikšmes priverstume į 1 ir 0. Rezultatas yra šis masyvas:
(0; 0; 1; 0; 0; 0)
Šiuo skaičiavimo momentu SUMPRODUCT formulė atrodo taip:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Tada „SUMPRODUCT“ paprasčiausiai padaugina kiekvieno masyvo elementus, kad gautų galutinį masyvą:
(0; 0; 775; 0; 0; 0)
Kuris SUMPRODUCT tada susumuoja ir grąžina 775.
Taigi šios formulės esmė yra ta, kad FALSE reikšmės yra naudojamos panaikinti visas kitas reikšmes. Išlieka tik tos vertybės, kurios buvo TIKROS.
Atminkite, kad kadangi mes naudojame „SUMPRODUCT“, ši formulė turi unikalų posūkį: jei yra kelios atitiktys, „SUMPRODUCT“ grąžins tų atitikmenų sumą. Tai gali būti ne tai, ko norite, todėl būkite atsargūs, jei tikitės kelių rungtynių!
Atminkite, kad ši formulė tinka tik skaitinėms reikšmėms, nes „SUMPRODUCT“ netvarko teksto. Jei norite gauti tekstą, naudokite INDEX / MATCH + EXACT.
