V tomto článku se naučíme Jak vyhledat přesné shody pomocí funkce SUMPRODUCT v Excelu.
Scénář:
Jednoduše řečeno, při práci s datovými tabulkami někdy potřebujeme v Excelu vyhledávat hodnoty pomocí malých a velkých písmen. Vyhledávací funkce jako funkce VLOOKUP nebo MATCH nenacházejí přesnou shodu, protože se nejedná o funkce rozlišující malá a velká písmena. Předpokládejme, že pracujeme na datech, kde jsou rozlišována 2 jména na základě rozlišování malých a velkých písmen, tj. Jerry & JERRY jsou dvě různá křestní jména. Úkoly, jako jsou operace, můžete provádět ve více rozsazích pomocí níže uvedeného vzorce.
Jak problém vyřešit?
Pro tento problém budeme muset použít funkci SUMPRODUCT & EXACT. Nyní z funkce vytvoříme vzorec. Zde dostaneme tabulku a v tabulce v aplikaci Excel musíme najít hodnoty odpovídající přesné shodě. Funkce SUMPRODUCT vrací SUM odpovídajících hodnot TRUE (jako 1) a ignoruje hodnoty odpovídající FALSE hodnotám (jako 0) ve vráceném poli
Obecný vzorec:
| = SUMPRODUCT ( - (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: hodnota pro vyhledávání.
lookup_array: vyhledávací pole pro vyhledávací hodnotu.
return_array: pole, vrácená hodnota odpovídající vyhledávacímu poli.
-: Negace (-) znak změní hodnoty, PRAVDA nebo 1 s na NEPRAVDA nebo 0 s a NEPRAVDA nebo 0 s na PRAVDA nebo 1 s.
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu. Zde máme data s množstvím a cenou produktů přijatých k datům. Pokud je jakýkoli produkt zakoupen dvakrát, má 2 názvy. Zde musíme zjistit počet položek pro všechny základní položky. Za tímto účelem jsme jako přijatý seznam přiřadili 2 seznamy a náležitosti požadované ve sloupci pro vzorec. Nyní použijeme níže uvedený vzorec k získání Množství „mléka“ z tabulky.
Použijte vzorec:
| = SUMPRODUKT ( - (PŘESNĚ (F5, B3: B11)), (C3: C11)) |
F6: vyhledejte hodnotu v buňce F6
B3: B11: vyhledejte pole je Items
C3: C11: návratové pole je Množství
-: Negace (-) znak změní hodnoty, PRAVDA nebo 1 s na NEPRAVDA nebo 0 s a NEPRAVDA nebo 0 s na PRAVDA nebo 1 s.
Zde je rozsah uveden jako referenční buňka. Množství získáte stisknutím klávesy Enter.
Jak vidíte, 58 je množství odpovídající hodnotě „mléko“ v buňce B5, nikoli „mléko“ v buňce B9. Pokud potřebujete v buňce C9 množství „54“, budete muset hledat hodnotu „Mléko“.
Cenu odpovídající hodnotě „mléko“ můžete vyhledat pomocí níže uvedeného vzorce.
Použijte vzorec:
| = SUMPRODUKT ( - (PŘESNĚ (F5, B3: B11)), (D3: D11)) |
F6: vyhledejte hodnotu v buňce F6
B3: B11: vyhledejte pole je Items
D3: D11: návratové pole je cena
Tady máme 58 je cena odpovídající hodnotě „mléko“ v buňce B5, nikoli „mléko“ v buňce B9. Pokud potřebujete cenu „15,58“ v buňce D9, budete muset hledat hodnotu „Mléko“.
Jedinečné hodnoty ve vyhledávacím poli
Podobně můžete jedinečné hodnoty najít pomocí vzorce. Zde je jako příklad použita vyhledávací hodnota „VEJCE“.
Na obrázku výše jsme dostali hodnoty upravující stejný vzorec. Problém však nastává, pokud má jedinečnou hodnotu a nehledáte správnou vyhledávací hodnotu. Jako zde pomocí hodnoty "Chléb" ve vzorci k vyhledání v tabulce.
Vzorec vrací 0 jako množství a Cena, ale to neznamená, že množství a Cena chleba je 0. Je to jen to, že vzorec vrací 0 jako hodnotu, když hodnota, kterou hledáte, nebyla nalezena. Použijte tedy tento vzorec pouze k vyhledání přesné shody.
Přesné shody vyhledávání můžete také provádět pomocí funkcí INDEX a MATCH v aplikaci Excel. Další informace o tom, jak provádět vyhledávání rozlišující malá a velká písmena pomocí funkce INDEX & MATCH v aplikaci Excel. Částečné shody můžete také vyhledat pomocí zástupných znaků v aplikaci Excel.
Zde je několik pozorovacích poznámek uvedených níže.
Poznámky:
- Vzorec funguje pouze s vyhledáním přesné shody.
- Funkce SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
- Pole argumentů musí mít stejnou velikost, jinak funkce vrátí chybu.
- Funkce SUMPRODUCT vrací hodnotu odpovídající TRUE hodnotám ve vráceném poli po převzetí jednotlivých produktů v odpovídajícím poli.
- Operátoři mají rádi ( = ), menší než rovno ( <= ), větší než ( > ) nebo se nerovná () lze provést v rámci použitého vzorce, pouze s čísly.
Doufám, že tento článek o tom, jak vyhledat přesnou shodu pomocí funkce SUMPRODUCT v aplikaci Excel, je vysvětlující. Další články o počítání vzorců najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
K vyhledání hodnoty použijte INDEX a MATCH : Vzorec INDEX-MATCH slouží k dynamickému a přesnému vyhledávání hodnoty v dané tabulce. Toto je alternativa k funkci VLOOKUP a překonává nedostatky funkce VLOOKUP.
Součet podle skupin OFFSET v řádcích a sloupcích : Funkci OFFSET lze použít k dynamickému součtu skupiny buněk. Tyto skupiny mohou být kdekoli v listu.
Jak načíst každou N -tu hodnotu v rozsahu v aplikaci Excel: Pomocí funkce OFFSET aplikace Excel můžeme načíst hodnoty ze střídajících se řádků nebo sloupců. Tento vzorec využívá funkci ROW pro střídání řádků a funkci COLUMN pro střídání ve sloupcích.
Jak používat funkci OFFSET v aplikaci Excel : Funkce OFFSET je výkonná funkce aplikace Excel, kterou mnoho uživatelů podceňuje. Odborníci však vědí, jak moc je funkce OFFSET a jak můžeme tuto funkci použít k provádění magických úkolů ve vzorci aplikace Excel. Zde jsou základy funkce OFFSET.
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí aplikace Excel, která se používá k vyhledávání hodnot z různých rozsahů a listů v aplikaci Excel.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot v aplikaci Excel nemusíte svá data filtrovat. Funkce COUNTIF je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty s konkrétními podmínkami.