Excel - suma hodnot a nastavení mezí
Zdravim,
potřeboval bych poradit ohledně nastavení vhodné funkce v MS OFFICE Excel 2007, při práci s tabulkou. Mám tabulku, ve které jsou uvedené měsíce (od 1 do 12 zastupují celý rok, ale není uvedeno vždy 12 měsíců někdy 2,5,10....) a ke každému měsíci jsou vedle v buňce přiřazena data. Potřebuju aby se vždy data za daný rok sečetla pomocí sumy. A hlavně aby nemuselo být prováděno ručně suma pro vybraný data, jedná se o velmi rozsáhlý soubor dat.
Př. Objekt č.1. - měsíce 1 - hodnota 350, měsíc 2 - 222, měsíc 5 - 297, měsíc 12 - 349; Objekt č.2 měsíc 1 - 233, měsíc 2 - 446.....atd. A potřeboval bych za měsíce 1 - 12 -> suma, další objekt měsíce 1-12 -> suma. Rok není ale vždy zastoupen 12 měsíci ale někdy jen 5 nebo 10 atak podobně. Pro názornost přilkládám tabulku, ze které bude lépe patrno. Díky za jakoukoliv radu.
potřeboval bych poradit ohledně nastavení vhodné funkce v MS OFFICE Excel 2007, při práci s tabulkou. Mám tabulku, ve které jsou uvedené měsíce (od 1 do 12 zastupují celý rok, ale není uvedeno vždy 12 měsíců někdy 2,5,10....) a ke každému měsíci jsou vedle v buňce přiřazena data. Potřebuju aby se vždy data za daný rok sečetla pomocí sumy. A hlavně aby nemuselo být prováděno ručně suma pro vybraný data, jedná se o velmi rozsáhlý soubor dat.
Př. Objekt č.1. - měsíce 1 - hodnota 350, měsíc 2 - 222, měsíc 5 - 297, měsíc 12 - 349; Objekt č.2 měsíc 1 - 233, měsíc 2 - 446.....atd. A potřeboval bych za měsíce 1 - 12 -> suma, další objekt měsíce 1-12 -> suma. Rok není ale vždy zastoupen 12 měsíci ale někdy jen 5 nebo 10 atak podobně. Pro názornost přilkládám tabulku, ze které bude lépe patrno. Díky za jakoukoliv radu.
Já se obávám, že největší problém bude v tom, že ve sloupci s čísly měsíců některé hodnoty chybí (je jich tam např. 10 místo 12). Jinak bych tenhle problém vyřešit uměl :cry
Další problém je, že (pokud jsem dobře koukal) třeba ne vžy je poslední měsíc roku prosinec, takže bez jasného určení konce roku (číslování např. 2009, 2010) excel těžko přinutíš dělat sumy za konkrétní roky.
Další problém je, že (pokud jsem dobře koukal) třeba ne vžy je poslední měsíc roku prosinec, takže bez jasného určení konce roku (číslování např. 2009, 2010) excel těžko přinutíš dělat sumy za konkrétní roky.
si tam dej do těch neobsazených měsíců nuly (třeba i neviditelný - bílé) a vzorec sumy budeš mít stejný vždy pro stejný rozsah 1 až 12...
Pokud excel neumi nejaky programovaci jazyk....
Napada me porovnavat hodnoty v bunkach- until porovnavana je mensi, nez predchozi- napis soucet...nekonecny cyklus a od znova pro dalsi bunky.
Jak to ale urobit v excelim jazyku nevim.
Napada me porovnavat hodnoty v bunkach- until porovnavana je mensi, nez predchozi- napis soucet...nekonecny cyklus a od znova pro dalsi bunky.
Jak to ale urobit v excelim jazyku nevim.
Excel umí VisualBasic ne? Ale ten zase tak dobře neznám, dělal jsem v něm jenom něco málo.
Povedlo se mi to udělat v OpenOffice (Excel nemám takže nemůžu zkusit), viz příloha.
Vzorec je následující:
=SUM(INDIRECT(ADDRESS(ROW($B$1) + MATCH(MAX((12-$A$2:$A13)*(ROW($A$2:$A13)+(ROW($A13)*10)));(12-$A$2:$A13)*(ROW($A$2:$A13)+(ROW($A13)*10));0); COLUMN($B$1))):$B13)
a je maticovej (tj. potvrdí se Ctrl+Shift+Enter)
Vypadá to dost drsně, ale není to zase tak hrozný, viz dále (složitě to vypadá proto, že se tam některý části opakujou).
Předpokládá se první měsíc v A2 (v A1 nadpis). Přes program by to bylo asi o dost elegantnější, takhle člověk musí využít toho co má. Zase na druhou stranu, při využití VisualBasic by Excel při otevírání prudil, že soubor obsahuje makra a abych si dal pozor na viry :).
Hlavní problém je v tom, že OO (a asi ani Excel) nemá inverzní lookup zespoda, vždycky se dělá lookup shora (jinak by to šlo pořešit vcelku jednoduchým lookupem).
Vzorec funguje následovně:
- vytvoří pole násobků $A$2:$Axx s pozicí řádku ROW($A$2:$Axx), tj. např.
(12-$A$2:$A13) * ROW($A$2:$A13)
od 12 se odečítá proto, aby se hodnoty invertovaly, tj. vepředu byly větší. Číslem řádku se to násobí proto, aby např. 12, která je víc nahoře, vytvořila menší číslo než 1, která je pod ní (tj. nová oblast od 1 vždycky bude obsahovat vyšší hodnoty než předchozí).
Ale samotný ROW by nestačilo, proto se k němu přičítá ještě pozice posledního ROW($Axx)*10 (aby byla jistota i pro malý hodnoty řádků), tj.
(12-$A$2:$A13) * ( ROW($A$2:$A13) + (ROW($A13)*10) )
dále označeno jako AAA
- pak se v tomhle poli najde maximum, tj. MAX(AAA)
- tohle maximum se pak vyhledá v poli AAA pomocí MATCH(MAX(AAA); AAA; 0) což dá vzdálenost buňky ve který je maximum
dále označeno BBB
-buňka ve který je první položka k sečtení se najde posunem od buňky $B$1 o hodnotu BBB (šlo by posouvat i od první buňky oblasti $B$2, ale pak by bylo potřeba od BBB odečíst 1), tj.
ADDRESS(ROW($B$1)+BBB;COLUMN($B$1))
označeno CCC
- z adresy se získá odkaz na buňku pomocí INDIRECT a vytvoří se oblast k sečtení:
INDIRECT(CCC):$B13
- a pak se to sečte SUM, což už je brnkačka :).
Tak, jak jsou ve vzorci označený absolutní a relativní adresy, by ho mělo jít přímo kopírovat vždycky do cílový buňky pro součet (relativní odkazy se samy patřičně nastaví).
Samozřejmě by to nejspíš šlo udělat i jinak, možná i míň složitě, použil jsem funkce který jsem k tomu našel a u kterých mi to fungovalo (a nemůžu zaručit, že stejný funkce bude poskytovat i Excel).
Povedlo se mi to udělat v OpenOffice (Excel nemám takže nemůžu zkusit), viz příloha.
Vzorec je následující:
=SUM(INDIRECT(ADDRESS(ROW($B$1) + MATCH(MAX((12-$A$2:$A13)*(ROW($A$2:$A13)+(ROW($A13)*10)));(12-$A$2:$A13)*(ROW($A$2:$A13)+(ROW($A13)*10));0); COLUMN($B$1))):$B13)
a je maticovej (tj. potvrdí se Ctrl+Shift+Enter)
Vypadá to dost drsně, ale není to zase tak hrozný, viz dále (složitě to vypadá proto, že se tam některý části opakujou).
Předpokládá se první měsíc v A2 (v A1 nadpis). Přes program by to bylo asi o dost elegantnější, takhle člověk musí využít toho co má. Zase na druhou stranu, při využití VisualBasic by Excel při otevírání prudil, že soubor obsahuje makra a abych si dal pozor na viry :).
Hlavní problém je v tom, že OO (a asi ani Excel) nemá inverzní lookup zespoda, vždycky se dělá lookup shora (jinak by to šlo pořešit vcelku jednoduchým lookupem).
Vzorec funguje následovně:
- vytvoří pole násobků $A$2:$Axx s pozicí řádku ROW($A$2:$Axx), tj. např.
(12-$A$2:$A13) * ROW($A$2:$A13)
od 12 se odečítá proto, aby se hodnoty invertovaly, tj. vepředu byly větší. Číslem řádku se to násobí proto, aby např. 12, která je víc nahoře, vytvořila menší číslo než 1, která je pod ní (tj. nová oblast od 1 vždycky bude obsahovat vyšší hodnoty než předchozí).
Ale samotný ROW by nestačilo, proto se k němu přičítá ještě pozice posledního ROW($Axx)*10 (aby byla jistota i pro malý hodnoty řádků), tj.
(12-$A$2:$A13) * ( ROW($A$2:$A13) + (ROW($A13)*10) )
dále označeno jako AAA
- pak se v tomhle poli najde maximum, tj. MAX(AAA)
- tohle maximum se pak vyhledá v poli AAA pomocí MATCH(MAX(AAA); AAA; 0) což dá vzdálenost buňky ve který je maximum
dále označeno BBB
-buňka ve který je první položka k sečtení se najde posunem od buňky $B$1 o hodnotu BBB (šlo by posouvat i od první buňky oblasti $B$2, ale pak by bylo potřeba od BBB odečíst 1), tj.
ADDRESS(ROW($B$1)+BBB;COLUMN($B$1))
označeno CCC
- z adresy se získá odkaz na buňku pomocí INDIRECT a vytvoří se oblast k sečtení:
INDIRECT(CCC):$B13
- a pak se to sečte SUM, což už je brnkačka :).
Tak, jak jsou ve vzorci označený absolutní a relativní adresy, by ho mělo jít přímo kopírovat vždycky do cílový buňky pro součet (relativní odkazy se samy patřičně nastaví).
Samozřejmě by to nejspíš šlo udělat i jinak, možná i míň složitě, použil jsem funkce který jsem k tomu našel a u kterých mi to fungovalo (a nemůžu zaručit, že stejný funkce bude poskytovat i Excel).
