Příklad
Potřebuji v tabulce sečíst hodnoty, které odpovídají určitým kritériím. Těch kritérií může být více - pokud je jen jedno, postačuje jednodušší funkce SUMIF.Např. tady potřebuji sečíst ceny všech aut, která jsou červená a mají najeto méně než 150 000 km.
Návod
Použiji funkci SUMIFS s takovouto syntaxí:
- První parametr označuje oblast, ze které se bere součet - v mém případě je to Cena.
- Druhý parametr označuje první oblast, podle které filtruji - v mém případě je to Najeto.
- Třetí parametr označuje filtrovací kritérium, podle kterého filtruji v oblasti, kterou jsem zadal ve druhém parametru
- Čtvrtý parametr označuje další oblast, podle které filtruji, pátý kritérium této oblasti a tak dále - kritérií může být hodně.
- Alternativou k funkci SUMIFS (a obdobným funkcím) jsou databázové funkce typu DSUM (česky DSUMA) a případně funkce SUBTOTAL. Funkce DSDUM má jednu zásadní výhodu - umožňuje zadávat kritéria se vztahem Nebo. Tedy šlo by například sečíst dohromady ceny červených a modrých aut.
Je ta syntax napisna spravne? Akosi mi to nesedi :-(
OdpovědětVymazatUpravil jsem.
OdpovědětVymazatDobrý den
OdpovědětVymazatCo když potřebuju např cena pod 150000 a pro Barvu žlutá a stříbrná?
Jde to řešit jinak než sečíst dva SUMIFS?
Dobrý den, v případě, že bych chtěl sečíst všechna, co nejsou červená, jak by zněl výraz pro "nerovná se červená"? Děkuji.
OdpovědětVymazatNerovná se se zapisuje jako <>
OdpovědětVymazatTo nerovnítko je ale třeba zapsat nějak takhle:
OdpovědětVymazatCONCATENATE("<>";"červená")
Kritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatKritéria jsou vůči sobě ve vztahu logického součinu (AND). Je možno zapsat i kritéria ve vztahu NEBO (OR)?
OdpovědětVymazatAnebo, zda je možno hledat v oblasti kritérií více hodnot, nežli jednu, která vyhovuje.
OdpovědětVymazatTak jsem to řešil. Ve vzorci však mám volbu zaokrouhlení na tisíce či v korunách a volbu od počátku roku či jen konkrétní měsíc. Ten vzorec upravovat v tabulce 52x8 je hrůza. a možných variant s NEBO je až 6.
OdpovědětVymazatDíky za ten odkaz - to funguje.
OdpovědětVymazatJen řetězce kritérií nejdou nahradit odkazem.
Jinak ještě o tom přemýšlím - nejlepší řešení by asi bylo použít databázovou funkci DSUM (DSUMA).
OdpovědětVymazatMěl jsem dovolenou, díky. Asi jsem prve funkci DSUMA nepochopil správně.
OdpovědětVymazatLuboš
Mám ještě jeden dotaz:
OdpovědětVymazatpracuji s externí databází SQL, vytvořím připojení a po spuštění se celá tabulka natáhne do Excelu. Problém je však v tom, že databáze obsahuje téměř milion jedno sto tisíc záznamů. Přitom mi stačí přetáhnout jen poslední rok. Je možno udělat výběr již nad importem dat?
Ideální by bylo udělaz vyfiltrovaný dotaz už přímo v databázi, ale jinak by to mělo jít např. v Power Query.
OdpovědětVymazatBohužel mám verzi MS Office pro podnikatele, hlásí, že pro Power Qwery není vhodná.
OdpovědětVymazatVýběr nad importem jsem vyřešil ve vlastnostech připojení nastavením Typu příkazu na SQL a v Textu příkazu jsem napsal přímo příkaz SQL. Funguje.
OdpovědětVymazatVracím se ke svým dotazům ze srpna. K řešení součtování jsem použil Autofilter a Subtotal (program má vyčíslit součty podle kritérií do jednotlivých políček rozborové tabulky). Řešení je funkční, avšak velmi pomalé. Vrátil jsem se k Vašemu návrhu použít funkci DSUMA. Malá komplikace je v tom, že pro každé počítané políčko v konečné tabulce se mění kritéria pro DSUMA. Nedaří se mi však správně zapsat argumenty funkce.
OdpovědětVymazatTo, co najdu v nápovědě funguje na listu, ve však ve VBA.
Tabulka databáze je vyplněna daty z externí databáze - jmenuje se Helios001_TabDenik.
Luboš
l.pokorny@iex.cz
Dobrý den Luboši,
OdpovědětVymazatnevím jestli teď úplně rozumím Vašemu problému, ale obecně - pokud by mě vadilo, že u DSUMA se musí pro každou hodnotu změnit kritérium, mohlo by pomoci ji zkombinovat s funkcí TABULKA/TABELOVAT:
http://www.excelentnitriky.com/2014/08/funkce-tabelovat-table.html
Díky za reakci na můj komentář. Každý popis funkce DSUMA je jasný a zápis je jednoduchý. Ve VBA mám zápis vzorce např.:SOUČET=WorkSheetFunction.DSUMA(Helios001_TabDenik;"Castka";R1C24:R(X)C28). Vyzkoušel jsem mnoho variant. Jde mi o správný zápis této funkce ve VBA. X udává počítaný rozměr oblasti kritérií.
OdpovědětVymazatLuboš