MS Excel

Prosím o pomoc se vzorcem a předem děkuji za případné reakce.
Mám tabulku, ve které se pro jednotlivé dny v měsíci zaznamenávají odpracované hodiny konkrétního pracovníka na jednotlivé zakázky s rozlišením na hodiny řádné (ŘH) a přesčasové (PH). Jde o oblast (X4:BA44). Zakázky nadepisuje a hodiny vyplňuje sám pracovník. Dny tvoří řádky a sloupce představují zakázky s tím, že každá zakázka má vyhrazeny 2 sloupce. Pro zakázky je vyhrazeno 15 míst (maximální uvažovaný počet v měsíci), tedy 15 dvojic sloupců. Ty mohou, ale nemusí být všechny v daném měsíci využity. Vše je patrné z přiloženého sešitu.
Výstupem má být sumarizace spotřebovaných hodin na jednotlivé zakázky s rozlišením na řádné a přesčasové hodiny.
Věc je složitější tím, že jedna zakázka se může nacházet i ve více sloupcích tabulky. Teoreticky by k tomu dojít nemělo, ale prakticky nelze zabránit např. tomu, aby jedna zakázka nebyla nadepsána nedopatřením pro více dvojic sloupců (např. rozkopírováním), nebo tomu, že budou vykázány hodiny ve sloupcích bez nadepsané zakázky (tzv. nespecifikovaná zakázka). Pro účely kontrolních součtů je nutné zajistit součet hodin ze všech sloupců – diferencovaně dle společného identifikačního znaku (konkrétní zakázka nebo bez specifikace), a to i ve zmíněných nestandardních případech.
Řekněme, že mám na jiném místě listu, určeného pro souhrny, již vytvořeny vzorce pro výpis seznamu vyskytujících se zakázek (bez duplicitních hodnot) do jednoho sloupce (A47:A61), včetně jedné položky pro nespecifikované zakázky. Do 2 sloupců vedle vpravo by se měly vypsat celkové počty odpovídajících spotřebovaných řádných a přesčasových hodin.
Doplňující popis
Pod tabulkou je řádek s mezisoučty hodnot jednotlivých sloupců (X44:BA44), tzn., že do podmíněného součtu stačí vybírat hodnoty jen z tohoto řádku. Potřebuji právě pomoct s vytvořením vzorce k podmíněnému sčítání hodnot z této jednořádkové oblasti.
Vlastní číslo zakázky – kód je poměrně složitá kombinace celkem 17 číselných znaků. Excel umí jen 15 ciferné číslo, proto je tento kód podle okolností vyjádřen buď v 6 skupinách čísel - oblast tabulky (X5:BA10), nebo jako textová hodnota. Z praktických důvodů jsou k identifikaci zakázek pro potřeby některých operací ve vzorcích používána jejich pořadová čísla v horním řádku záhlaví tabulky (X4:BA4).
Vpravo od tabulky jsou sloupce s pomocnými výpočty:
• (BC12:BC26) pořadová čísla zakázek, jak jsou postupně zapsána v tabulce
• (BD12:BD26) odpovídající kompletní číselné kódy zakázek v textovém formátu, pokud není původní kód kompletní, je zde nahrazen nulami (nespecifikovaná zakázka)
• (BE12:BE26) kódy zakázek v textovém formátu s oddělovači pro potřebu zpětného vyhledávání
• (BF12:BF26) zakázky seřazeny vzestupně
Pokud to shrnu, tak vzorce v oblasti (F47:G61) by měly umět:
• podle hodnoty (zakázka) v příslušném řádku vlevo (A47:E61) vyhledat všechny stejné hodnoty v oblasti (BE12:BE26),
• vrátit odpovídající pořadová čísla ve sloupci (BC) a
• sečíst jen ty hodnoty v řádku (X44:BA44), které jsou ve sloupcích nadepsaných jednak těmito pořadovými čísly v řádku (X4:BA4), resp. (X1:BA1), a současně odpovídajícím označením druhu spotřebovaných hodin „ŘH“ či „PH“ v řádku (X11:BA11).

Podotýkám, že je třeba vzorce konstruovat pro Excel 2003!

Zaslat odpověď >

eLCHa(25.11.2015 7:46)#028291

Mno - psát složitě umíte, jen co je pravda. Vůbec nevím, jestli jsem Vás pochopil. Přijde mi zbytečné řadit ty zakázky vzestupně či sestupně, takže to tam zatím není - pokud by to bylo nutné, tak to zas není takový problém. Vzorce, které jsem Vám změnil jsou podbarveny černě.

Příloha: zip

28291_evidence_pd-1.zip (52kB, staženo 47x)

citovat

pavelo(25.11.2015 11:04)#028304

Moc děkuji, úkol jste navzdory (nebo možná díky

) mému složitému popisu problematiky pochopil správně. Zobrazuje se to co má a součty také fungují jak mají. Vaším řešením a úpravami jste cestu k cíli výrazně zjednodušil. Na vysvětlenou, pracuji na vývoji formuláře již poměrně dlouho a postupem času a s jednotlivými jeho verzemi se zadání doplňovalo i měnilo, takže tím možná došlo někde i k překombinovaným krokům řešení.
Jen bych ještě poprosil, když už jste do toho proniknul, o to vzestupné seřazení zakázek v souhrnech tak, jak to bylo původně, má to svůj význam.
Také budu muset obejít použití funkce IFERROR, formulář se bude používat i ve verzích Ecelu 2003.
A mám jeden malý dotaz k těm skalárním součinům ve vzorcích pro součty. Uniká mi, proč se ta pole dělí tou jedničkou?citovat

eLCHa(25.11.2015 12:56)#028309

Seřazeno (bylo třeba vložit pomocný řádek - možno skrýt)
IFERROR je můj oblíbený - nahrazeno.
Skalární součin neumí v argumentu převést logickou hodnotu na číslo. Proto je třeba se o to postarat a já to dělám pomocí /1.

Příloha: zip

28309_evidence_pd-1.zip (53kB, staženo 43x)

citovat

Téma: Podmíněné sčítání s proměnným počtem kritérií

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje