MS Excel

Dobrý den,
chtěl požádat o pomoc s vložením sumárního řádku do kontingenční tabulky.
Ve zdrojových datech mám sloupec "doSPL", který mi přiřadí, zda je faktura do splatnosti či po splatnosti.
Já bych potřeboval v kontingenční tabulce toto:
1) buď u každé kategorie sloupce "Rok" v konti tabulce mít souhrn "Do SPL" a "Po SPL"
2) nebo to mít alespoň dole, za celou konti tabulku.
Přičemž bych rád zachoval současný počet zobrazených sloupců konti tabulky.
Děkuji

Zaslat odpověď >

Strana: « předchozí 1 2 3 4

eLCHa(28.5.2015 11:30)#025220

Mno - možná si to zasloužilo vlastní vlákno

Napadá mne možnost pomocí výpočtové položky. Nevýhodou je, že pokud se v datech vyskytne další rok, tento vzorec se musí ručně aktualizovat. Nicméně, pokud aktualizujete kontingenční tabulky pomocí kódu, dá se to samozřejmě automatizovat. Např.:Sub subActualizeFormula()
Const sITEM As String = "Celkový průměr"

Dim bExists As Boolean
Dim sFormula As String
With Worksheets("detail_auta").PivotTables("Kontingenční tabulka 1").PivotFields("ROK")
Dim pi As PivotItem
For Each pi In .PivotItems
If Not pi.Caption = sITEM Then
sFormula = sFormula & "'" & pi.Caption & "'"
Else
bExists = True
End If
Next pi
Set pi = Nothing

sFormula = "=(" & Replace(sFormula, "''", "'+'") & ")/" & (.PivotItems.Count + bExists)

If Not bExists Then
.CalculatedItems.Add sITEM, sFormula
Else
.CalculatedItems(sITEM).StandardFormula = sFormula
End If
End With 'Worksheets("detail_auta").PivotTables("Kontingenční tabulka 1").PivotFields("ROK")
End Sub

Příloha: zip

25220_0_spotreba-komplet_auta-tab_wall1.zip (90kB, staženo 13x)

citovat

Alfan(28.5.2015 11:45)#025222

Pustil jsem makro a hlásí chybu na tomto řádku:
.CalculatedItems(sITEM).StandardFormula = sFormula

A tu výpočtovou položku byste mi mohl, prosím, ukázat?
I když zaslouží editaci, tak bych si to prostě poznamenal.
Děkujicitovat

eLCHa(28.5.2015 12:05)#025223

V předchozím příspěvku jsem aktualizoval kód (v mé příloze byl funkční, protože položka už existovala) - nyní aktualizuje výpočtovou položku a pokud tam není, tak ji i vytvoří.
Samozřejmě musíte vypnout řádkový souhrn - resp. nastavit Zapnuto pouze pro sloupce

Výpočtová položka - umístěte kurzor na pole ROK (E7), dejte možnosti kont. tabulky - Vzorce - Výpočtová položkacitovat

Alfan(28.5.2015 12:17)#025224

add 1) vůbec tomu nerozumím...

add 2) u toho vzorového souboru jsem Počítanou položku přidal, ale u mého mi to hlásí, že buď nedostatek paměti, nebo bylo dosaženo maximálního počtu položek v poli nebo jestliže je prováděno makrem, je nesprávné

vypočtu si to manuálně vedle tabulky, ale štve mě tocitovat

eLCHa(28.5.2015 12:30)#025225

To mne mrzí - čemu nerozumíte?

Mluvím teď o Vaší příloze:
1) v KT kliknete na buňku E7 - ROK
2) Nahoře v ribonu máte Nástroje kontingenční tabulky, vyberete Možnosti, Rozkliknete Vzorce a vyberete Výpočtová položka.

Do pole Název napíšete např.: Celkový průměr
2xkliknete na 2011 zmáčknete +, 2x na 2012 zmáčknete plus.. dáte do závorek a podělíte počtem roků.
Mno prostě vzorec musí být nakonec
=('2011'+ '2012'+ '2013'+ '2014')/4 Dáte OK a přidá se nakonec.

Pak přejdete na návrh KT a celkové součty nastavíte na Zapnuto pouze pro sloupce

Váš originální soubor nevidím, takže nevím, proč to hlásí tu chybu, pokud je to pochopitelné, zkuste vytvořit ručně.

Hledejte na google Pivot Table Calculated Itemcitovat

Alfan(28.5.2015 12:50)#025226

Já tu "Počítanou položku" uměl přidat takto
=PRŮMĚR('2011';'2012';'2013';'2014')
to mi šlo, ale nerozuměl jsem tomu "nastavit Zapnuto pouze pro sloupce".

A to makro se mi stále zastavuje na řádku
.CalculatedItems.Add sITEM, sFormula

"Pokud sestava kontingenční tabulky obsahuje počítané položky, není podporováno více datových polí stejného pole".
Ale já žádnou Počítanou položku zde nemám.citovat

eLCHa(28.5.2015 12:57)#025227

Jestli chcete pošlete mi ten soubor na mail (nebo někde uložte a pošlete odkaz), jinak Vám nepomůžu.citovat

Alfan(28.5.2015 13:55)#025229

@eLCHa
Tak jsem připravoval co nejmenší soubor, abych ho poslal k analýze a jakmile jsem odmazal cca 130 tisíc řádků, tak mi to "dovolilo" vložit "Počítanou položku" Tak si myslím, že jsem měl moc řádků na zdrojovém listu s daty (cca 174 000 řádků) a velikost souboru cca 44 MB.
No nic, jdu dělat něco jiného

Ale každopádně díky.
Tu Počítanou položku zřejmě ještě využiju, stejně jako SUMIF nebo ZÍSKATKONTDATA

citovat

eLCHa(29.5.2015 6:45)#025241

Toto berte jako technickou poznámku, ne jako kritiku, prosím.

Podle dat, která v těch souborech budou zřejmě exportována z nějakého účetního systému. Mám z toho pocit, že si exportujete zbytečně moc dat a pak se Vám špatně pracuje - místo efektivní práce z excelem to na mne působí spíše jako testování maximálních možností.
Já pracuji se SAP (u Vás to asi bude jiný, export do xml tady nevidím) a pokud možno veškerou práci nechávám na něm - tzn nechat si reporty vytvořit přímo v něm - přece jste za něj zaplatili (a tuším i zodpovědné IT oddělení), tak ať maká.
Pokud toto nelze - exportovat pouze relevantní data pro daný problém - vy vytváříte databázi o 300k řádků a pak v KT používáte 2 fitry sestavy (fitrujete nepotřebná data) - není lepší mít v databázi pouze relevantní data a pak se vyhnout problémům? (víz Váš poslední příspěvek)
Pracovat se souborem 44M denně musí být velmi nepraktické.
Pochybuji, ale pokud nelze ta data exportovat jinak - asi bych raději vytvořil jeden soubor čistě s daty a více specifických souborů pro jednotlivé oblasti. Z datového souboru pak v každém z nich tahal pouze relevantní data např. pomocí SQL a teprve z těchto dat bych tvořil výstupy (KT).citovat

Alfan(29.5.2015 7:50)#025244

Máte svým způsobem pravdu a já to respektuji, nicméně já má zase, podle mě několik objektivních příčin, proč takto činím

Nemám k dispozici extra IT oddělení, aby mi něco programovalo a já na to nemám časovou kapacitu.
Účetní SW máme od externí firmy a těm se moc nechce něco dodělávat. A tak například nastavit nějaký export do nějakého "datového skladu" je sci-fi, zatím... Ale například to makro, které čte z toho *.csv souboru používám, teď přesně nevím, kdo ho navrhl a pak dodělal. Je to super a pomáhá mi.
Exportuji, doplňuji ZATÍM, více dat, protože se pořádně seznamuji se systéme, co, která pole obsahují, neb nikdo není schopen (nemá čas) mi to detailně sdělit.
Nevím, na která data přesně a ze kterých polí budu chtít koukat. Nyní to již omezuji a to zejména díky tabulce

Každopádně si myslím, že se nevyhnu počtu řádků, protože potřebuji například ze saldo konta porovnávat 3 roky. Sloupce (pole v účetním SW) omezit mohu a snažím se nyní o to.
Ten soubor je staršího data, cca 1 - 2 měsíce vygenerovaný a proto je tak obsáhlý.
Se SAPem jsem chvilku dělal v UNIPETROLu a tam byl na každý modul, například controling specialista a to se to potom požadovaly sestavy...

Na závěr z mé strany strašně důležitá věc:
VELMI OCEŇUJI POMOC OD KOHOKOLI NA TOMTO NEBO OBDOBNÉM FÓRU!!!
I když někdy jsou naše "lamácké" dotazy pro zkušené "exceláře" skoro až "trapné", pro nás jsou rady a praktická pomoc neocenitelné

citovat

Strana: « předchozí 1 2 3 4

Téma: Kontingenční tabulka - vložení sumárního řádku

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje