EXCEL a funkce SUMIF

[pre.vit]

Mám problém s použitím vnořené funkce sumif.
Tabulka vypadá asi takhle:

1.1._____2.1.____3.1.
pozn.___pozn.___pozn.
x y z___x y z____x y z
...hodnoty pro X, Y a Z...

a na začátku jsou součty, jednotlivých X, pak Y a pak Z.
Na to jsem použil fci =SUMIF(I3:IV3;"X";I4:IV4) pro výběr a součet všech X. Kde řádek 3 je popis XYZ a řádek 4 už jsou hodnoty. Takle to funguje bezvadně, ale já potřebuji dodat ještě jednu podmínku a to na nějaké slovo v poznámce (řádek 2). Jenže když dám =SUMIF(I2:IV2;"*slovo*";SUMIF(I3:IV3;"X";I4:IV4)), tak to už dává chybu ve funkci. Jak použít dvojí podmínku pro součet??

P.S. nevím jak udělat napevno mezeru, aby ten "náhled" tabulky nějak vypadal a tak jsem udělal _

[malli]

Nechceš sem tu tabulku v excelu někam vložit? Ono se v tom takhle špatně orientuje.

[pre.vit]

Nechceš sem tu tabulku v excelu někam vložit? Ono se v tom takhle špatně orientuje.

OK, tady jsem narychlo udělal příkladovou tabulku i s vysvětlivkami...

http://previt.wz.cz/temp/priklad.xls

[D_a_v_i_d]

Jen takovej dotaz - musi to byt nutne vzorcema a SUMIFem? Ja jen ze pokud ne a bylo by mozny na to "vypustit" nejakou VBA procedurku, tak by se to dalo udelat naprosto v pohode a to i tak, ze by nemusely radky a sloupce byt nastaveny "napevno", ale proslo by to celou tabulku krizem krazem a vyhazelo ty soucty...

Pokud chces, tak ti to makro vecer napisu, je to od oka tak na 10-20 radku... Pak by se tam pridelal jen cudlik na prepocet a bylo by to...

[pre.vit]

Jen takovej dotaz - musi to byt nutne vzorcema a SUMIFem? Ja jen ze pokud ne a bylo by mozny na to "vypustit" nejakou VBA procedurku, tak by se to dalo udelat naprosto v pohode a to i tak, ze by nemusely radky a sloupce byt nastaveny "napevno", ale proslo by to celou tabulku krizem krazem a vyhazelo ty soucty...

Pokud chces, tak ti to makro vecer napisu, je to od oka tak na 10-20 radku... Pak by se tam pridelal jen cudlik na prepocet a bylo by to...

No myslej jsem si, že to s tím sumif je nejjednodušší způsob. Prostě jediné co potřebuju zjistit, jak tam do něj dát druhou podmínku. S VBA jsem nikdy nepřišel do styku a netuším co s tím. Btw. ta tabulka na kterou to budu aplikovat je komplexnější, to co jsem tu udělal je hodně zjednodušený příklad...
Ale jestli myslíte, že přes VBA to je nejjednoduší tak se tomu nebráním..

[D_a_v_i_d]

No uprimne, se SUMIFem nevim jak to udelat... Ve VBAcku by to asi problem nebyl, ale zase pokud se s VBAckem nekamaradis, tak pro tebe asi bude slozitejsi si to pak upravit na realny priklad... Nu pockame jeste, jestli nekdo nevymysli nejaky "vzorcovy" postup a pak muzem zkusit nejake to makricko

[malli]

V Excelu 2007 je funkce SUMIFS, kde se dá zadat více kritérií. Odzkoušeno, funguje to v té tvé tabulce bez problémů.

V Excelu 2003 bych to pro začátek řešil tak, že bych do druhého listu udělal "kopii" té tabulky a každá hodnota, by se v případě, že úvodní řádek neobsahuje "slovo", vynásobila číslem 0. Zbyly by tedy jen platné hodnoty a ty bych sečetl funkcí SUMIF.

Zkusím ale vymyslet něco sofistikovanějšího )

EDIT: no, ještě mě napadlo tohle:
Mezi 3. a 4. řádek vlož ještě jeden, kde se bude X, Y, Z "sčítat" s hodnotou "slovo". Takže ti z toho vypadnou hodnoty "X" nebo "Xslovo" atd. No a funkcí SUMIF sečteš jen hodnoty "Xslovo"

[pre.vit]

V Excelu 2007 je funkce SUMIFS, kde se dá zadat více kritérií. Odzkoušeno, funguje to v té tvé tabulce bez problémů.

V Excelu 2003 bych to pro začátek řešil tak, že bych do druhého listu udělal "kopii" té tabulky a každá hodnota, by se v případě, že úvodní řádek neobsahuje "slovo", vynásobila číslem 0. Zbyly by tedy jen platné hodnoty a ty bych sečetl funkcí SUMIF.

Zkusím ale vymyslet něco sofistikovanějšího )

EDIT: no, ještě mě napadlo tohle:
Mezi 3. a 4. řádek vlož ještě jeden, kde se bude X, Y, Z "sčítat" s hodnotou "slovo". Takže ti z toho vypadnou hodnoty "X" nebo "Xslovo" atd. No a funkcí SUMIF sečteš jen hodnoty "Xslovo"

To s tím nápadem vložit skrytý řádek mezi 3. a 4. není špatný. Jenže já ve skutečnosti testuju SUMIFem slovo stylem "*lov*", aby se vybrali všechny buňky se slovem slovo. Protože kolikrát se stane, že tam bude např. "Slovo za slovem" anebo "Slovo a jiné kok**iny" apod.. )
Tak jsem zkusil =KDYŽ(H2="*lov*";"xslov";"x"), ale ejhle fce KDYŽ neumí pracovat s takovýmhle argumentem. Nechápu proč SUMIF tohle zadání bez problémů zkousne a IF ne.. Sakra!!

[peta303]

Na něco podobného kdysi pomohl i průvodce pdomíněným součtem...

[pre.vit]

Na něco podobného kdysi pomohl i průvodce pdomíněným součtem...

Tam je problém s tím, že u každého dne mám sloučené 3 buňky a s tím pracovat neumí..

[malli]

To s tím nápadem vložit skrytý řádek mezi 3. a 4. není špatný. Jenže já ve skutečnosti testuju SUMIFem slovo stylem "*lov*", aby se vybrali všechny buňky se slovem slovo. Protože kolikrát se stane, že tam bude např. "Slovo za slovem" anebo "Slovo a jiné kok**iny" apod.. )
Tak jsem zkusil =KDYŽ(H2="*lov*";"xslov";"x"), ale ejhle fce KDYŽ neumí pracovat s takovýmhle argumentem. Nechápu proč SUMIF tohle zadání bez problémů zkousne a IF ne.. Sakra!!

Hele, nesmíš měnit pravidla během hry!
Když teda akceptuješ tu fintu s tím řádkem, tak bych to rozdělil na 2 kroky:

1) otestuješ, zda v tom řetězci je nebo není ta hledaná část.

např. takto:

=je.chyba(HLEDAT("slov";H2;1))
tahle funkce vyhodí buď PRAVDA nebo NEPRAVDA, podle toho, jestli tvůj řetězec neobsahuje nebo obsahuje "slov"

nebo jinak, když do toho zase vneseš další podmínky

a do skrytého řádku si potom ulož třeba "XSECTI" a "XNESCITEJ"

celý vzorec by pak vydadal asi takhle:

=když(je.chyba(HLEDAT("slov";H2;1))=pravda;"XNESC ITEJ";"XSECTI")

2) a teprve teď začni sčítat pomocí SUMIF

[malli]

Tam je problém s tím, že u každého dne mám sloučené 3 buňky a s tím pracovat neumí..

...případně jestli by ti ten podmíněnej součet pomohl, tak jakej je problém tu sloučenou buňku 3x okopírovat do tří buněk v řádku pod ní?

Takto:

A B C
SLOVO
SLOVO SLOVO SLOVO

[pre.vit]

Hele, nesmíš měnit pravidla během hry!
Když teda akceptuješ tu fintu s tím řádkem, tak bych to rozdělil na 2 kroky:

1) otestuješ, zda v tom řetězci je nebo není ta hledaná část.

např. takto:

=je.chyba(HLEDAT("slov";H2;1))
tahle funkce vyhodí buď PRAVDA nebo NEPRAVDA, podle toho, jestli tvůj řetězec neobsahuje nebo obsahuje "slov"

nebo jinak, když do toho zase vneseš další podmínky

a do skrytého řádku si potom ulož třeba "XSECTI" a "XNESCITEJ"

celý vzorec by pak vydadal asi takhle:

=když(je.chyba(HLEDAT("slov";H2;1))=pravda;"XNESC ITEJ";"XSECTI")

2) a teprve teď začni sčítat pomocí SUMIF

Paráda! Jeden skrytý řádek to napraví.. Dík!

[GooGLi]

nechci zakladat nový vlákno, proto si dovolím to přihodit sem.

Mám tabulku lidí v excelu, kde ke každému jménu je přiřazen výsledek testu. Potřebuju aby mi excel vypsal lidi, kteří dosáhli největšího počtu bodů. Zatím jsem dospěl jen ke vzorci

=INDEX(B5:B29;POZVYHLEDAT(MAX(G5:G29);G5:G29;0))

sloupec B jsou jména a sloupec G jsou výsledky

Tenhle vzorec mi ovšem vyhodí jen první jméno a další už nevypíše, potřeboval bych aby mi vypsal všechna jména která dosáhla nejvyššího výsledku. Např. Výsledku 99 dosáhlo pět lidí, tak všechny vypsat. Díky

[malli]

Pozvyhledat musí mít bohužel vzestupně seřazené hodnoty. Takže před použitím vzorce zkus data setřídit a mělo by to fungovat.
Po setřídění dat už žádný vzoreček nepotřebuješ, protože vítězové budou na konci seznamu
Zkus třeba použít kontingenční tabulku, ta by to měla zvládnout a nebo to obejít přes filtr dat (např. tak, že vzorečkem přiřadíš každému, kdo odpovídá zadání, tedy kdo má max(x:y), třeba jedničku a ostatním nulu). A pak si jedničkáře vyfiltruješ automatickým filtrem. To samé ti ale udělá k.t.

[D_a_v_i_d]

Člověče to si nejsem jistej, jestli to bude "holej" Excel vůbec umět. V podobných případech vždycky zalituju, že nad Excel tabulkama nejde psát něco jako SQL dotazy, to by pak byla pohoda

Koukal jsem sem

http://office.microsoft.com/cs-cz/ex...006252828.aspx

Ale nenašel jsem nic (žádnou funkci), která by vracela vektor/matici nalezených hodnot, což je to, co ty potřebuješ... Dalo by se to asi nějak naprasit buď přes makro nebo kontingenční tabulku.

[GooGLi]

Díky pánové, trošku mě to pomohlo, ale kýženýho výsledku jsem se nedopracoval, páč ten seznam měl bejt mimo tabulku. Ale aspon jsem se ujistil, že to excel neumi .
K+

[D_a_v_i_d]

Pokud to potřebuješ používat častěji a zašleš mi nějaký "vzor" jak vypadá tabulka výsledků, tak ti nějaké VBAčkové makro co ti tohle udělá klidně spíchnu, mělo by bejt celkem easy

[GooGLi]

Díky za nabídku. Nebudeme to zbytečně kvůli takový blbosti komplikovat. Udělám to ručně, až budou finální výsledky. Jakýpakcopak

[D_a_v_i_d]

Jasný, můžeš si tam aspoň nastavit podmíněný formátování, který ti ty jména zvýrazní (bude-li to delší seznam) Já myslel, že je to opakovaná úloha, pokud je to jednorázovka, tak to asi fakt nemá smysl komplikovat