V tomto článku sa naučíme Ako vyhľadávať presné zhody pomocou funkcie SUMPRODUCT v Exceli.
Scenár:
Jednoducho povedané, pri práci s tabuľkami údajov niekedy musíme v programe Excel vyhľadávať hodnoty pomocou malých a veľkých písmen. Funkcie vyhľadávania, ako sú funkcie VLOOKUP alebo MATCH, nenachádzajú presnú zhodu, pretože nejde o funkcie rozlišujúce malé a veľké písmena. Predpokladajme, že pracujeme na dátach, kde sú 2 mená rozlíšené na základe rozlišovania malých a veľkých písmen, t.j. Jerry a JERRY sú dve rôzne krstné mená. Úlohy, ako sú operácie, môžete vykonávať vo viacerých rozsahoch pomocou nižšie uvedeného vzorca.
Ako problém vyriešiť?
Na tento problém budeme musieť použiť funkciu SUMPRODUCT & EXACT. Teraz z funkcie vytvoríme vzorec. Tu je uvedená tabuľka a v tabuľke v programe Excel musíme nájsť hodnoty zodpovedajúce presnej zhode. Funkcia SUMPRODUCT vracia SUM zodpovedajúcich TRUE hodnôt (ako 1) a ignoruje hodnoty zodpovedajúce FALSE hodnotám (ako 0) vo vrátenom poli.
Všeobecný vzorec:
| = SUMPRODUCT ( - (PRESNE (lookup_value, lookup_array)), (return_array)) |
lookup_value: hodnota pre vyhľadávanie.
lookup_array: vyhľadávacie pole pre vyhľadávaciu hodnotu.
return_array: pole, vrátená hodnota zodpovedajúca vyhľadávaciemu poľu.
-: Znak negácie (-) zmení hodnoty, PRAVDA alebo 1 s na NEPRAVDA alebo 0 s a NEPRAVDA alebo 0 s na PRAVDA alebo 1 s.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie. Tu máme údaje s množstvom a cenou produktov prijatých v daný deň. Ak je akýkoľvek výrobok kúpený dvakrát, má 2 názvy. Tu musíme nájsť počet položiek pre všetky nevyhnutné položky. Preto sme ako prijatý zoznam priradili 2 zoznamy a náležitosti potrebné v stĺpci pre vzorec. Teraz použijeme nižšie uvedený vzorec na získanie množstva „mlieka“ z tabuľky.
Použite vzorec:
| = SUMPRODUCT ( - (PRESNE (F5, B3: B11)), (C3: C11)) |
F6: vyhľadajte hodnotu v bunke F6
B3: B11: Vyhľadávacie pole je položky
C3: C11: návratové pole je množstvo
-: Znak negácie (-) zmení hodnoty, PRAVDA alebo 1 s na NEPRAVDA alebo 0 s a NEPRAVDA alebo 0 s na PRAVDA alebo 1 s.
Tu je rozsah uvedený ako referencia bunky. Množstvo získate stlačením klávesu Enter.
Ako vidíte, 58 je množstvo zodpovedajúce hodnote „mlieko“ v bunke B5, nie „mlieku“ v bunke B9. Ak potrebujete množstvo „54“ v bunke C9, budete musieť hľadať hodnotu „Mlieko“.
Cenu zodpovedajúcu hodnote „mlieko“ môžete vyhľadať pomocou nižšie uvedeného vzorca.
Použite vzorec:
| = SUMPRODUCT ( - (PRESNE (F5, B3: B11)), (D3: D11)) |
F6: vyhľadajte hodnotu v bunke F6
B3: B11: Vyhľadávacie pole je položky
D3: D11: návratové pole je cena
Tu máme 58 je cena zodpovedajúca hodnote „mlieko“ v bunke B5, nie „mlieko“ v bunke B9. Ak potrebujete cenu „15,58“ v bunke D9, budete musieť hľadať hodnotu „Mlieko“.
Jedinečné hodnoty v poli vyhľadávania
Podobne môžete jedinečné hodnoty nájsť pomocou vzorca. Ako príklad tu slúži vyhľadávacia hodnota „VAJEC“.
Na obrázku uvedenom vyššie sme dostali hodnoty upravujúce rovnaký vzorec. Problém však nastáva, ak má jedinečnú hodnotu a nevyhľadávate správnu vyhľadávaciu hodnotu. Rovnako ako tu pomocou hodnoty „Chlieb“ vo vzorci na vyhľadanie v tabuľke.
Vzorec vráti 0 ako množstvo a cenu, ale to neznamená, že množstvo a cena chleba je 0. Jednoducho vzorec vráti 0 ako hodnotu, keď sa nenájde hodnota, ktorú hľadáte. Tento vzorec teda použite iba na vyhľadanie presnej zhody.
Presné zhody vyhľadávania môžete vykonávať aj pomocou funkcií INDEX a MATCH v programe Excel. Získajte viac informácií o tom, ako postupovať pri rozlišovaní malých a veľkých písmen pomocou funkcie INDEX & MATCH v programe Excel. Čiastkové zhody môžete vyhľadať aj pomocou zástupných znakov v programe Excel.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s, iba na vyhľadanie presnej zhody.
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú veľkosť, inak funkcia vráti chybu.
- Funkcia SUMPRODUCT vracia hodnotu zodpovedajúcu SKUTOČNÝM hodnotám vo vrátenom poli po prevzatí jednotlivých produktov v zodpovedajúcom poli.
- Operátori sa radi rovnajú ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci použitého vzorca, iba s číslami.
Dúfam, že tento článok o tom, ako vyhľadávať presnú zhodu pomocou funkcie SUMPRODUCT v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o vzorcoch počítania. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvými na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
Na vyhľadanie hodnoty použite INDEX a MATCH : Vzorec INDEX-MATCH sa používa na dynamické a presné vyhľadávanie hodnôt v danej tabuľke. Je to alternatíva k funkcii VLOOKUP a prekonáva nedostatky funkcie VLOOKUP.
Súčet podľa skupín OFFSET v riadkoch a stĺpcoch : Funkciu OFFSET je možné použiť na dynamické sčítanie skupín buniek. Tieto skupiny môžu byť kdekoľvek v hárku.
Ako získať každú n -tú hodnotu v rozsahu v programe Excel: Pomocou funkcie OFFSET v Exceli môžeme získať hodnoty zo striedajúcich sa riadkov alebo stĺpcov. Tento vzorec využíva funkciu ROW na striedanie riadkov a funkciu COLUMN na striedanie v stĺpcoch.
Ako používať funkciu OFFSET v programe Excel : Funkcia OFFSET je výkonná funkcia programu Excel, ktorú mnohí používatelia podceňujú. Odborníci však poznajú silu funkcie OFFSET a ako môžeme túto funkciu použiť na vykonávanie magických úloh vo vzorci programu Excel. Tu sú základy funkcie OFFSET.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov v programe Excel.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt v programe Excel nemusíte filtrovať svoje údaje. Funkcia COUNTIF je potrebná na prípravu hlavného panela.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty s konkrétnymi podmienkami.