Vispārēja formula
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Kopsavilkums
Reģistrjutīgi uzmeklējumi programmā Excel
Pēc noklusējuma standarta uzmeklēšana programmā Excel nav reģistrjutīga. Gan VLOOKUP, gan INDEX / MATCH vienkārši atgriezīs pirmo maču, neņemot vērā burtus.
Tiešs veids, kā apiet šo ierobežojumu, ir izmantot masīva formulu, kuras pamatā ir INDEX / MATCH ar EXACT. Tomēr, ja meklējat tikai skaitliskās vērtības, SUMPRODUCT + EXACT dod arī interesantu un elastīgu veidu, kā veikt reģistrjutīgu meklēšanu.
Šajā piemērā mēs izmantojam šādu formulu
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Lai gan šī formula ir masīva formula, tā nav jāievada ar Control + Shift + Enter, jo SUMPRODUCT masīvus apstrādā natīvi.
Paskaidrojums
SUMPRODUCT ir paredzēts darbam ar masīviem, kurus tas reizina, pēc tam summē.
Šajā gadījumā mēs esam divi bloki ar SUMPRODUCT: B3: B8 un C3: C8. Viltība ir palaist B kolonnas vērtību pārbaudi, pēc tam iegūtās TRUE / FALSE vērtības konvertēt uz 1 un 0. Pārbaudi veicam ar EXACT, piemēram:
EXACT(E3,B3:B8)
Kas rada šo masīvu:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Ņemiet vērā, ka patiesā vērtība 3. pozīcijā ir mūsu atbilstība. Tad mēs izmantojam dubultnegatīvu (ti, - kas tehniski ir "dubultā unāra"), lai šīs TRUE / FALSE vērtības piespiestu 1 un 0. Rezultāts ir šāds masīvs:
(0; 0; 1; 0; 0; 0)
Šajā aprēķina brīdī formula SUMPRODUCT izskatās šādi:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Pēc tam SUMPRODUCT vienkārši reizina katra masīva vienumus, lai izveidotu galīgo masīvu:
(0; 0; 775; 0; 0; 0)
Kurš SUMPRODUCT tad summējas, un atgriež 775.
Tātad šīs formulas būtība ir tāda, ka FALSE vērtības tiek izmantotas, lai atceltu visas citas vērtības. Izdzīvo tikai tās vērtības, kas bija PATIESAS.
Ņemiet vērā, ka, tā kā mēs izmantojam SUMPRODUCT, šai formulai ir unikāls pagrieziens: ja ir vairākas atbilstības, SUMPRODUCT atgriezīs šo spēļu summu. Tas var būt vai var nebūt tas, ko vēlaties, tāpēc uzmanieties, ja sagaidāt vairākus mačus!
Atcerieties, ka šī formula darbojas tikai skaitliskām vērtībām, jo SUMPRODUCT neapstrādā tekstu. Ja vēlaties izgūt tekstu, izmantojiet INDEX / MATCH + EXACT.
