Note: The other languages of the website are Google-translated. Back to English

Sečíst, pokud buňky obsahují konkrétní text v jiném sloupci

Tento tutoriál vám ukáže, jak sečíst hodnoty, pokud buňky obsahují konkrétní nebo částečný text v jiném sloupci. Vezměte si níže uvedený rozsah dat jako příklad, abyste získali celkové množství produktů obsahujících text „T-Shirt“, s tímto úkolem v Excelu si poradí jak funkce SUMIF, tak funkce SUMPRODUCT.


Sečíst hodnoty, pokud buňka obsahuje konkrétní nebo částečný text s funkcí SUMIF

Chcete-li sečíst hodnoty, pokud buňka obsahuje konkrétní text v jiném sloupci, můžete použít funkci SUMIF se zástupným znakem (*), obecné syntaxe jsou:

Obecný vzorec s pevně zakódovaným textem:

=SUMIF(range,"*text*",sum_range)
  • range: Rozsah dat, který chcete vyhodnotit pomocí kritérií;
  • *text*: Kritéria, na základě kterých chcete sečíst hodnoty. Zde se zástupný znak * používá k nalezení libovolného počtu znaků, aby se porovnaly všechny položky, které obsahují konkrétní text, vložte text mezi dva znaky *. (Upozorňujeme, že text a zástupný znak musíte uzavřít do dvojitých uvozovek.)
  • sum_range: Rozsah buněk se shodnými číselnými hodnotami, které chcete sečíst.

Obecný vzorec s odkazem na buňku:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: Rozsah dat, který chcete vyhodnotit pomocí kritérií;
  • "*"&cell&"*": Kritéria, na základě kterých chcete sečíst hodnoty;
    • *: Zástupný znak, který najde libovolný počet znaků.
    • buňka: Buňka obsahuje konkrétní text, který se má hledat.
    • &: Tento operátor zřetězení (&) se používá ke spojení odkazu na buňku s hvězdičkami.
  • sum_range: Rozsah buněk se shodnými číselnými hodnotami, které chcete sečíst.

Poté, co se seznámíte se základním ovládáním funkce, použijte některý z následujících vzorců, který potřebujete, a poté stiskněte vstoupit klíč k získání výsledku:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                 
 (Use a cell reference)

Poznámka: Tato funkce SUMIF nerozlišuje velká a malá písmena.


Součet hodnot, pokud buňka obsahuje konkrétní nebo částečný text s funkcí SUMPRODUCT

Funkce SUMPRODUCT vám také může pomoci dokončit tuto úlohu bez použití zástupných znaků. Zde byste měli funkce ISNUMBER a SEARCH zabalit do funkce SUMPRODUCT, obecná syntaxe je:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: Rozsah buněk se shodnými číselnými hodnotami, které chcete sečíst;
  • criteria: Kritéria, na základě kterých chcete sečíst hodnoty. Může to být odkaz na buňku nebo konkrétní vámi definovaný text;
  • range: Rozsah dat, který chcete vyhodnotit pomocí kritérií;

Do prázdné buňky použijte některý z níže uvedených vzorců a stiskněte vstoupit klíč k vrácení výsledku:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                   
(Use a cell reference)


Vysvětlení tohoto vzorce:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(HLEDAT("Tričko",$A$2:$A$12))))

  • HLEDAT("Tričko", $A$2:$A$12): Funkce SEARCH vrací umístění konkrétního textu „T-Shirt“ z datového rozsahu A2:A12, takže získáte pole takto: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!;7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Tato funkce ISNUMBER se používá k testování číselných hodnot a vrací nové pole: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: K vynásobení těchto dvou polí matematická operace vynutí hodnoty TRUE a FALSE na 1s a 0s. Násobení těchto dvou polí tedy bude vypadat takto: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Nakonec funkce SUMPRODUCT sečte všechny hodnoty v poli a získá výsledek:1605.

Použitá relativní funkce:

  • SUMIF:
  • Funkce SUMIF může pomoci sečíst buňky na základě jednoho kritéria.
  • SUMPRODUCT:
  • Funkci SUMPRODUCT lze použít k vynásobení dvou nebo více sloupců nebo polí dohromady a následnému získání součtu produktů.
  • ČÍSLO:
  • Funkce Excel ISNUMBER vrací TRUE, pokud buňka obsahuje číslo, a FALSE, pokud ne.
  • VYHLEDÁVÁNÍ:
  • Funkce SEARCH vám pomůže najít pozici konkrétního znaku nebo podřetězce z daného textového řetězce

Další články:

  • Součet nejmenších nebo dolních hodnot N
  • V Excelu je pro nás snadné sečíst rozsah buněk pomocí funkce SUM. Někdy může být nutné sečíst nejmenší nebo dolní 3, 5 nebo n čísel v datovém rozsahu, jak je znázorněno na obrázku níže. V tomto případě vám SUMPRODUCT spolu s funkcí SMALL může pomoci vyřešit tento problém v Excelu.

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel - pomůže vám vyniknout před davem

Rádi byste svou každodenní práci dokončili rychle a dokonale? Kutools pro Excel přináší 300 výkonných pokročilých funkcí (kombinace sešitů, součet podle barvy, rozdělení obsahu buněk, převod data a tak dále...) a ušetří vám 80 % času.

  • Navrženo pro 1500 pracovních scénářů, pomůže vám vyřešit 80 % problémů s Excelem.
  • Omezte každý den tisíce kliknutí na klávesnici a myš a ulehčete svým unaveným očím a rukám.
  • Staňte se odborníkem na Excel za 3 minuty. Už si nemusíte pamatovat žádné bolestivé vzorce a kódy VBA.
  • 30denní neomezená bezplatná zkušební verze. 60denní záruka vrácení peněz. Bezplatný upgrade a podpora po dobu 2 let.
Pás karet aplikace Excel (s nainstalovaným programem Kutools pro Excel)

Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)

  • Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
  • Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
  • Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
  • Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Firefox a Nový Internet Explorer.
Snímek obrazovky aplikace Excel (s nainstalovanou kartou Office)
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL