Dokumentace/Výpočetní funkce/SUMIFS
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Název funkce:
SUMIFS
Kategorie:
Matematické
Shrnutí:
Vypočítá součet sady čísel v rozsahu buněk, přičemž buňky, které se mají sečíst, se určí pomocí více kritérií. Hlavní rozdíl mezi funkcí SUMIFS a funkcí SUMIF programu Calc spočívá v tom, že tato funkce je omezena na vyhodnocení pouze jednoho kritéria.
Kritéria předávaná službě SUMIFS mohou využívat zástupné znaky nebo regulární výrazy.
Syntaxe:
SUMIFS(SumRange; Range 1; Criterion 1[; Range 2; Criterion 2[; ... [; Range 127; Criterion 127']]])
Vrátí:
Vrací reálné číslo, které je výsledkem sčítání čísel v příslušných buňkách.
Argumenty:
Parametr SumRange
SumRange určuje buňky, které mají být sečteny. SumRange má obvykle jeden z následujících tvarů:
- Odkaz na rozsah buněk (například A1:A25), který nesmí používat operátor spojování odkazů (~).
- Název pojmenovaného rozsahu.
- Název databázového rozsahu.
Argumenty rozsahu
Rozsah 1 určuje množinu buněk, které mají být porovnány s Kritériem 1, a má jeden z tvarů uvedených pro SoučetRozsah. Rozsah 1 by měl mít stejné rozměry jako SumRange.
Rozsah 2, ..., Rozsah 127 mají stejný význam jako Rozsah 1.
Argumenty kritéria
Kritérium 1 je kritérium pro porovnání s buňkami v Rozsahu 1 nebo buňkou obsahující toto kritérium. Kritérium 1 může mít jednu z následujících podob:
- Číslo, například 34,5. Data a logické hodnoty (TRUE nebo FALSE) se považují za čísla.
- Výraz, například 2/3, SQRT($D$1) nebo DATE(2021; 11; 1).
- Textový řetězec, například "golf" nebo "<>10".
SUMIFS hledá buňky v Rozsahu 1', které se rovnají Kritériu 1, pokud Kritérium 1' není textový řetězec začínající komparátorem (>, <, >=, <=, = nebo <>). V druhém případě SUMIFS porovná buňky v rozsahu 1 se zbytkem textového řetězce (pokud možno interpretovaného jako číslo, jinak jako text). Například podmínka ">4,5" testuje, zda je obsah každé buňky větší než číslo 4,5, podmínka "<pes" testuje, zda je obsah každé buňky abecedně před textem "pes", a podmínka "<>2021-11-01" testuje, zda se obsah každé buňky nerovná zadanému datu.
Kritérium 1 podporuje následující konkrétní chování:
- Řetězec "=" odpovídá prázdným buňkám. Například vzorec
=SUMIFS(A1:A10; B1:B10; "=")vrátí součet všech hodnot v rozsahu A1:A10, pokud jsou všechny buňky v rozsahu B1:B10 prázdné. Všimněte si, že "=0" neodpovídá prázdným buňkám. - Řetězec "<>" odpovídá neprázdným buňkám. Například vzorec
=SUMIFS(A1:A10; C1:C10; "<>")vrátí součet všech hodnot v rozsahu A1:A10, pokud v rozsahu C1 nejsou žádné prázdné buňky: C10. - Pokud hodnota za komparátorem <> není prázdná, pak Kritériu 1 odpovídá libovolnému obsahu buňky kromě této hodnoty, včetně prázdných buněk.
Kritérium 2, ..., Kritérium 127 mají stejný význam jako Kritérium 1'.
Chybové stavy
- Pokud některý rozsah buněk předaný jako argument obsahuje operátor spojování odkazů (~), pak SUMIFS ohlásí chybu neplatného argumentu (Chyba:502).
- Všechny rozsahy buněk předané jako argumenty (SumRange a Range 1, ..., Range 127) musí zabírat stejný počet řádků a stejný počet sloupců. Pokud tomu tak není, pak SUMIFS ohlásí chybu neplatného argumentu (Chyba:502).
- Pokud nejsou argumenty Rozsah n a Kritérium n správně spárovány, pak SUMIFS hlásí chybu chybějící proměnné (Chyba:511).
Další detaily:
Podrobnosti specifické pro funkci SUMIFS
- Výchozí porovnávání prováděné funkcí SUMIFS nerozlišuje velká a malá písmena. Při použití regulárního výrazu však lze provést porovnávání podle velikosti písmen tak, že se do regulárního výrazu vloží modifikátor způsobu "(?-i)", jak ukazuje jeden z příkladů níže.
- Chování funkce SUMIFS je ovlivněno několika nastaveními dostupnými v dialogovém okně ▸ ▸ ▸ ( ▸ ▸ ▸ v systému MacOS).
- Pokud je zaškrtnuto políčko Kritéria hledání = a <> musí platit pro celé buňky, pak podmínka "červená" bude odpovídat pouze "červená"; pokud není zaškrtnuta, bude odpovídat "červená", "Fred", "červený sleď".
- Pokud je zaškrtnuto políčko Povolit zástupné znaky ve vzorcích, bude podmínka odpovídat pomocí zástupných znaků - takže například "b?g" bude odpovídat "bag", "beg", "big", "bog" a "bug".
- Pokud je zaškrtnuto políčko Povolit regulární výrazy ve vzorcích, bude podmínka odpovídat pomocí regulárních výrazů - takže například "r.d" bude odpovídat "red", "rid" a "rod", zatímco "red.*" bude odpovídat "red", "redraw" a "redden".
- Nastavení zaškrtávacího políčka Case sensitive nemá žádný vliv na fungování funkce SUMIFS.
Obecné informace o regulárních výrazech v Calcu
 |
Poznámka:
|
- Regulární výraz je řetězec znaků definující vzor textu, který má být porovnán. Podrobnější obecné informace naleznete na stránce Regular expression ve Wikipedii.
- Regulární výrazy se hojně používají v mnoha oblastech a existuje mnoho procesorů regulárních výrazů. Calc využívá open source balíček regulárních výrazů od International Components for Unicode (ICU), další podrobnosti včetně úplné definice syntaxe regulárních výrazů ICU najdete v jejich dokumentaci Regular Expressions.
- Systém nápovědy LibreOffice navíc poskytuje vysokoúrovňový seznam regulárních výrazů.
- Mechanismus regulárních výrazů Calcu podporuje číslované skupiny zachycení, které umožňují identifikovat dílčí rozsahy v rámci shody a použít je v náhradním textu. Závorky se používají k seskupení složek regulárního výrazu a vytvoření číslované skupiny zachycení. Chcete-li do náhradního textu vložit skupinu zachycení, použijte tvar "$n", kde n je číslo skupiny zachycení.
Obecné informace o zástupných znacích Calcu
Zástupné znaky jsou speciální znaky, které lze použít ve vyhledávacích řetězcích předávaných jako argumenty některým funkcím Calcu; lze je také použít k definování kritérií vyhledávání v dialogovém okně Najít a nahradit. Použití zástupných znaků umožňuje definovat pokročilejší parametry hledání pomocí jediného vyhledávacího řetězce.
Calc podporuje jako parametry zástupné znaky nebo regulární výrazy v závislosti na aktuálním nastavení aplikace. Ve výchozím nastavení jsou místo regulárních výrazů podporovány zástupné znaky.
Chcete-li se ujistit, že jsou zástupné znaky podporovány, přejděte do ▸ ▸ ▸ a zkontrolujte, zda je vybrána možnost Povolit zástupné znaky ve vzorcích. Všimněte si, že v tomto dialogovém okně můžete přepnout na regulární výrazy výběrem možnosti Povolit regulární výrazy ve vzorcích nebo zvolit nepodporu zástupných znaků ani regulárních výrazů.
V následující tabulce jsou uvedeny zástupné znaky, které Calc podporuje.
| Zástupný znak | Popis |
|---|---|
| ? (otazník) | Odpovídá libovolnému jednotlivému znaku. Například vyhledávací řetězec „b?g“ odpovídá „bag“ a „beg“, ale nebude odpovídat „boog“ nebo „mug“. Upozorňujeme, že nebude odpovídat ani „bg“ , od té doby "?" musí přesně odpovídat jednému znaku. "?" zástupný znak neodpovídá nulové shodě znaků. |
| * (hvězdička) | Odpovídá libovolné sekvenci znaků, včetně prázdného řetězce. Například hledaný řetězec „*cast“ bude odpovídat „cast“, „forecast“ a „outcast“, ale nebude odpovídat „forecaster“ pomocí výchozího nastavení Calc. Pokud je volba Kritéria vyhledávání = a <> se musí vztahovat na celé buňky je zakázáno v ▸ ▸ ▸ , pak „forecaster" bude shoda pomocí vyhledávacího řetězce „*cast". |
| ~ (tilda) | Zachrání speciální význam otazníku, hvězdičky nebo znaku tilda, který následuje bezprostředně za znakem tilda. Například vyhledávací řetězec "proč~?" odpovídá "proč?", ale nebude odpovídat "proč" ani "proč~". |
Při porovnávání se zástupnými znaky se nerozlišují velká a malá písmena, proto "A?" bude odpovídat jak "A1", tak "a1".
Tyto zástupné znaky jsou podporovány v aplikaci Calc i Microsoft Excel. Pokud tedy potřebujete interoperabilitu mezi oběma aplikacemi, zvolte práci se zástupnými znaky místo regulárních výrazů. Naopak, pokud není interoperabilita nutná, zvažte použití regulárních výrazů pro výkonnější možnosti vyhledávání.
Příklady:
Příklady prodeje tiskovin
Vezměme v úvahu následující tabulku s údaji o tržbách a příjmech malého dodavatele papírenského zboží. Řetězec "N/A" označuje výrobky, které nebyly v období, na které se údaje vztahují, k dispozici pro dodávky.
| A | B | C | |
|---|---|---|---|
| 1 | Výrobek | Prodej | Příjmy |
| 2 | Tužka | 20 | $65 |
| 3 | Pero | 35 | $85 |
| 4 | Notebook | 20 | $190 |
| 5 | Kniha | 17 | $180 |
| 6 | Pouzdro na tužku | N/A | N/A |
Ve všech příkladech založených na této tabulce je třeba poznamenat, že řádek 6 pro penály neobsahuje žádné číselné údaje, a proto se nikdy nepodílí na výsledku SUMIFS, ať už jsou zadána jakákoli kritéria.
| Vzorec | Popis | Vrátí |
|---|---|---|
| =SUMIFS(B2:B6; B2:B6; ">=20") | Zde funkce sečte číselná data Prodej, která jsou větší nebo rovna 20. | 75 |
| =SUMIFS(C2:C6; B2:B6; ">=20"; C2:C6; ">70") | Zde funkce vyhledá v údajích "Prodej" položky větší nebo rovny 20, které mají v údajích "Tržby" také hodnotu větší než 70 USD, a sečte odpovídající číselné položky v údajích "Tržby". | $275 |
| =SUMIFS(C2:C6; B2:B6; ">"&MIN(B2:B6); B2:B6; "<"&MAX(B2:B6)) | Zde funkce vypočítá součet číselných položek v údajích "Tržby", které odpovídají všem hodnotám v údajích "Prodej" kromě minima a maxima. | $255 |
| =SUMIFS(C2:C6; A2:A6; "pen.*"; B2:B6; "<"&MAX(B2:B6)) | Tento příklad bude fungovat, jak je zde popsáno, pouze v případě, že jsou povoleny regulární výrazy. Zde funkce vyhledá položky v datech Produkt, které začínají znaky "pero" a mají hodnotu v datech Prodej, která není maximální, a sečte odpovídající číselné položky v Tržby' data. | $65 |
| =SUMIFS(C2:C6; A2:A6; E2&".*"; B2:B6; "<"&MAX(B2:B6)), kde buňka E2 obsahuje řetězec "pero" (zadaný bez uvozovek). | Tento příklad bude popsaným způsobem fungovat pouze v případě, že jsou povoleny regulární výrazy. Pokud potřebujete kritérium snadno změnit, můžete jej zadat do samostatné buňky a odkaz na tuto buňku použít v podmínce funkce SUMIFS. Zde se odkaz na buňku nahradí jejím obsahem, čímž se získá stejný výsledek jako v předchozím příkladu. | $65 |
Příklady prodeje sportovního vybavení
Příklady v této podkapitole vycházejí z malé databáze údajů o prodeji sportovního vybavení, přičemž údaje jsou uspořádány podle následující tabulky.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Datum | Hodnota tržeb | Kategorie | Oblast | Zaměstnanci |
| 2 | 2021-10-02 | $1,508 | Golf | Východ | Hans |
| 3 | 2021-10-02 | $410 | Tenis | Sever | Kurt |
| 4 | 2021-10-02 | $2,340 | Jachting | Jih | Ute |
| 5 | 2021-10-03 | $4,872 | Tenis | Východ | Brigitte |
| 6 | 2021-10-06 | $3,821 | Tenis | Jih | Fritz |
| 7 | 2021-10-06 | $2,623 | Tenis | Východ | Fritz |
| 8 | 2021-10-07 | $3,739 | Golf | Jih | Fritz |
| 9 | 2021-10-08 | $4,195 | Golf | Západ | Ute |
| 10 | 2021-10-10 | $2,023 | Golf | Východ | Hans |
| Vzorec | Popis | Vrátí |
|---|---|---|
| =SUMIFS(B2:B10; B2:B10; ">=4000") | Zde funkce sečte číselné položky v datech Hodnota prodeje, které jsou větší nebo rovné 4 000 USD. Tento vzorec je ekvivalentní jak =SUMIF(B2:B10; ">=4000"), tak =SUMIF(B2:B10; ">=4000"; B2:B10). |
$9,067 |
| =SUMIFS(B2:B10; E2:E10; "ute") | Zde funkce vyhledá položky pro Ute v datech Zaměstnanec a sečte odpovídající číselné položky v datech Hodnota prodeje. Tento vzorec je ekvivalentní =SUMIF(E2:E10; "ute"; B2:B10) a toto nové uspořádání argumentů zdůrazňuje klíčový rozdíl mezi těmito dvěma funkcemi - SumRange je třetí argument pro SUMIF a první argument pro SUMIFS. |
$6,535 |
| =SUMIFS(B2:B10; CategoryData; "golf"), kde byl vytvořen pojmenovaný rozsah CategoryData, který pokrývá rozsah buněk C2:C10. | Zde funkce vyhledá položky pro Golf v údajích Kategorie a sečte odpovídající číselné položky v údajích SUMIFSHodnota tržeb. | $11,465 |
| =SUMIFS(B2:B10; D2:D10; F1; E2:E10; F2), kde buňky F1 a F2 obsahují textové řetězce ">=south" a "ute" (oba zadané bez uvozovek). | Zde funkce vyhledá položky pro jih a západ v datech Region, které mají také Ute v datech Zaměstnanec, a sečte odpovídající číselné položky v datech Hodnota prodeje. | $6,535 |
| =SUMIFS(B2:B10; A2:A10; DATE(2021; 10; 2); C2:C10; "tennis") | Zde funkce vyhledá položky pro rok 2021-10-02 ve sloupci "Datum", které mají v údaji "Kategorie" také položku Tenis, a sečte odpovídající číselné položky v údaji "Hodnota prodeje". | $410 |
| =SUMIFS(B2:B10; A2:A10; ">="&DATE(2021; 10; 6); E2:E10; "<>"&E8) | Zde funkce vyhledá ve sloupci Datum záznamy s datem 2021-10-06 nebo později, které nemají v údajích Zaměstnanec jméno Fritz, a sečte odpovídající číselné záznamy v údajích Hodnota prodeje. | $6,218 |
| =SUMIFS(B2:B10; C2:C10; "tennis"; D2:D10; "east"; E2:E10; "fritz") | Zde funkce vyhledá záznamy, které mají v údajích Kategorie položku Tenis, v údajích Oblast položku Východ a v údajích Zaměstnanec položku Fritz. Sečte odpovídající číselné položky v údaji "Hodnota prodeje". | $2,623 |
| =SUMIFS(B2:B10; D2:D10; "????"; E2:E10; "*e") | Tento příklad bude fungovat, jak je zde popsáno, pouze pokud jsou povoleny zástupné znaky. Zde funkce vyhledá čtyřmístné položky v datech Region (východ a západ), které mají také položku v datech Zaměstnanec končící písmenem "e" nebo "E" (Brigitte a Ute). Sečte odpovídající číselné položky v datech Hodnota prodeje. | $9,067 |
| =SUMIFS(B2:B10; C2:C10; "^t.*"; D2:D10; ".*h") | Tento příklad bude popsaným způsobem fungovat pouze v případě, že jsou povoleny regulární výrazy. Zde funkce vyhledá záznamy pro Tenis v údajích Kategorie, které začínají písmenem "t" nebo "T" (Tenis), které mají také záznam v údajích Oblast, který končí písmenem "h" nebo "H" (Sever a Jih). Sečte odpovídající číselné položky v údaji "Hodnota prodeje". | $4,231 |
| =SUMIFS(B2:B10; E2:E10; "(?-i)ute") | Tento příklad bude popsaným způsobem fungovat pouze v případě, že jsou povoleny regulární výrazy. Modifikátor režimu "(?-i)" v regulárním výrazu se změní na shodu rozlišující malá a velká písmena, takže v datech Zaměstnanec nebudou nalezeny žádné záznamy. Srovnejte to s druhým příkladem v této tabulce. | $0 |
Další příklady
Pro více příkladů si stáhněte a prohlédněte tento Calc spreadsheet.
Související funkce LibreOffice:
ODF standard:
Související (nebo podobné) funkce Excelu:
SUMIFS