Excel - komplikovany PIVOT

Všetko o programoch, problémoch...
POZOR: žiadny WAREZ
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

Zdravim, chcel by som sa popytat, ci by nahodou niekto vedel poradit s nasledujucim problemom:
Otazna znie, ci je mozne v pivote kombinovat filtrovane aj nefiltrovane udaje. Uvediem na priklade: potreboval by som porovnat v tabulke udaje za 1 kalendarny mesiac voci celemu roku (t. j. 12 mesiacov).
Format udajov:
A - typ vyrobku
B - mesiac
C - mnozstvo
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM
Používateľov profilový obrázok
molnart
Pokročilý používateľ
Pokročilý používateľ
Príspevky: 6663
Dátum registrácie: Ut 19. Jún, 2012, 23:03
Bydlisko: Bratislava/Samorin

Re: Excel - komplikovany PIVOT

Príspevok od používateľa molnart »

vytvor v zdrojaku dalsi stlpec kde budes mat sumarne udaje za cely rok
Spoiler: ukázať
PC: CPU: Intel Core i5 12600K with Silentium Fortis 5 ARGB MB: MSI Tomahawk Z690 DDR4 RAM: 2x 16GB G.Skill Ripjaws V 4400-19 DDR4 GPU: GigaByte Eagle GeForce RTX 3060 Ti OC HDD: Samsung 970 1GB GB PSU: Corsair RMx (2018) 650W Case: Fractal Meshify 2 Compact Monitor: Philips 272B7QPJEB OS: Win 11 64-bit
Notebook: HP EliteBook 840 G6 Core i5 8265U, 16 GB RAM, 512 GB SSD
Server: HP Microserver Gen8 Xeon E3-1265Lv2, 16GB ECC DDR3 OS: PVE + OMV + OPNsense
Phone: Samsung Galaxy A52s
Tablet: iPad Pro 11 (2018)
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Re: Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

To by som musel spravit 12 kopii sumarnych udajov - pre kazdy mesiac zvlast, ked v pivote je aktivny filter na mesiac.
Isto, je to riesenie, len teda nie moc efektivne. Obdobne by sa dalo, ak by kazdy mesiac mal svoj stlpec s hodnotami.
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM
Používateľov profilový obrázok
wingo
Moderátor
Moderátor
Príspevky: 5789
Dátum registrácie: St 01. Sep, 2010, 20:36
Bydlisko: Podbrezová

Re: Excel - komplikovany PIVOT

Príspevok od používateľa wingo »

Daj pole Mesiac do stĺpcov, pridaj si do poľa Mesiac vypočítanú položku (Nástroje pre kontingenčné tabuľky - Analyzovať - Polia, položky a množiny - Vypočítavaná položka, pozor musíš mať vybratú bunku v poli Mesiac), ktorá bude súčtom hodnôt za jednotlivé mesiace. Potom odfiltruj mesiace ktoré nechceš vidieť a máš to ;)
Spoiler: ukázať
STEAM: http://steamcommunity.com/id/X-w1n9/

PC1:
Ryzen 5 7600X, Noctua NH-D14+Arctic P14 PWM PST, Gigabyte B650M Gaming X AX, 32GB DDR5 Kingston Fury Renegade RGB @ 6000/28/37/37/30, Gigabyte AORUS RX 6800XT Master 16GB, SSD Kingston Fury Renegade 1TB, Adata XPG Core Reactor 650W, BeQuiet Pure Base 500DX + 3x Arctic P14 PWM PST, LG 42OLEDC21, Gamdias Hermes P3 Brown, Logitech G400, Hifiman HE-4XX + Topping NX4DSD

PC2:
Ryzen 5 5600X, ASUS TUF Gaming B550M-PLUS WiFi, 32GB DDR4 Kingston Predator@3600/16/19/21/36, Sapphire Pulse RX6600 8GB, Toshiba XG3 1TB M.2 NVMe, Seasonic Platinum SS-660XP2, Riotoro Morpheus, ASUS VG279Q, Marvo K605, Rapture ASPIS, Superlux HD-330 + Brainwavz Sheepskin earpads, Edifier WN820NB
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Re: Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

Vdaka, to by pri danej strukture slo. Avsak pribudol dalsi stlpec:
A - typ vyrobku
B - mesiac
C - predane mnozstvo
D - plan/skut.

Zamer je porovnat napr. 6. mes. skutocnost voci planu na cely rok.
Ak by som chcel aplikovat rovnake riesenie, musel by som mat 24 stlpcov (12 pre plan + 12 skut.).

Zdrojove data by som nerad komplikoval, kedze potom aj detail (po rozkliknuti pivotu) je nasledne neprehladny.
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM
Používateľov profilový obrázok
wingo
Moderátor
Moderátor
Príspevky: 5789
Dátum registrácie: St 01. Sep, 2010, 20:36
Bydlisko: Podbrezová

Re: Excel - komplikovany PIVOT

Príspevok od používateľa wingo »

Stĺpec D je plán, či nejaký podiel plánu a skutočnosti? píšeš o pláne ale do popisu si dal plán/skut...

Ja reportujem plán a skutočnosť presne tak, mesiac po mesiaci (12 plán + 12 skutočnosť) s tým že ako sa plní skutočnosť tak vidíš okrem porovnania v rámci mesiaca aj postupné napĺňanie celoročného plánu v súčtovom stĺpci. Ak chceš porovnať 6. mesiac skutočnosti s plánom za celý rok, odfiltruješ všetko okrem 6. mesiaca a súčtového mesiaca (vypočítaná položka) a zostane ti plán za 6. mesiac, skutočnosť za 6. mesiac, plán za rok a skutočnosť za rok a to už vizuálne porovnáš v pohode. Ale ak to chceš čisto dajme tomu 6. mesiac s plánom za celý rok (a nemať tam aj plán na 6. mesiac a skutočnosť za celý rok) sprav si dve kontingenčné vedľa seba (alebo skopíruj odfiltrované údaje do samostatnej statickej tabuľky aby to lepšie vyzeralo), nič lepšie ma nenapadá.

Ďalšia možnosť je nastaviť zobrazovanie položiek ako % z niečoho čo si nastavíš, napr. práve toho plánu, len netuším či to je to čo chceš.
Spoiler: ukázať
STEAM: http://steamcommunity.com/id/X-w1n9/

PC1:
Ryzen 5 7600X, Noctua NH-D14+Arctic P14 PWM PST, Gigabyte B650M Gaming X AX, 32GB DDR5 Kingston Fury Renegade RGB @ 6000/28/37/37/30, Gigabyte AORUS RX 6800XT Master 16GB, SSD Kingston Fury Renegade 1TB, Adata XPG Core Reactor 650W, BeQuiet Pure Base 500DX + 3x Arctic P14 PWM PST, LG 42OLEDC21, Gamdias Hermes P3 Brown, Logitech G400, Hifiman HE-4XX + Topping NX4DSD

PC2:
Ryzen 5 5600X, ASUS TUF Gaming B550M-PLUS WiFi, 32GB DDR4 Kingston Predator@3600/16/19/21/36, Sapphire Pulse RX6600 8GB, Toshiba XG3 1TB M.2 NVMe, Seasonic Platinum SS-660XP2, Riotoro Morpheus, ASUS VG279Q, Marvo K605, Rapture ASPIS, Superlux HD-330 + Brainwavz Sheepskin earpads, Edifier WN820NB
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Re: Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

D ma 2 hodnoty, plan alebo skutocnost ...ja som ten priklad zjednodusil, skutocny report je trosku komplikovanejsi.

Rozumiem, co pises, prave tu narazam na limity pivotu. Potrebujem dynamicky report, aby sa uzivatel vedel pozriet aj na detail (zdrojove udaje), no tie by zas nemali byt moc komplikovane, kedze clovek sa v nich potom straca.
Chcel som dostat do 1 pivotu nasledovne hodnoty:
ACTUAL (M) - aktualny mesiac
PLAN (M) - aktualny mesiac
ACTUAL (YTD) - kumulativ (1-6)
PLAN (YTD) - kumulativ (1-6)
PLAN (Y/E) - cely rok

Uz som to dal cez vypocitave polozky, len skoda, ze treba zakazdym vytvarat novu, resp. upravit (YTD) (napocitat data za novo pridany mesiac) a neviem odstranit z pivotu zbytocnu skutocnost (Y/E), ktora je totozna s (YTD).

Rozdielove a podielove ukazovatele su uz len ceresnicka.

Inak nemas skusenosti s pivotmi z 2 zdrojakov (sheetov), ala datove modely?
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM
Používateľov profilový obrázok
wingo
Moderátor
Moderátor
Príspevky: 5789
Dátum registrácie: St 01. Sep, 2010, 20:36
Bydlisko: Podbrezová

Re: Excel - komplikovany PIVOT

Príspevok od používateľa wingo »

2 zdroje som použil iba keď som spájal 2 CSV súbory ako tabuľky cez ODBC (excel ako zdroj mi nechcelo zobrať), masaker to rozchodiť so slovenskou lokalizáciou :D

Ak chceš skombinovať dáta z dvoch tabuliek (s rovnakým formátom dát, navzájom sa doplňujúce), dá sa to spraviť cez konsolidačné tabuľky, ak ich chceš prepájať ako tabuľky databázy, treba ísť asi iba cez ODBC...
Spoiler: ukázať
STEAM: http://steamcommunity.com/id/X-w1n9/

PC1:
Ryzen 5 7600X, Noctua NH-D14+Arctic P14 PWM PST, Gigabyte B650M Gaming X AX, 32GB DDR5 Kingston Fury Renegade RGB @ 6000/28/37/37/30, Gigabyte AORUS RX 6800XT Master 16GB, SSD Kingston Fury Renegade 1TB, Adata XPG Core Reactor 650W, BeQuiet Pure Base 500DX + 3x Arctic P14 PWM PST, LG 42OLEDC21, Gamdias Hermes P3 Brown, Logitech G400, Hifiman HE-4XX + Topping NX4DSD

PC2:
Ryzen 5 5600X, ASUS TUF Gaming B550M-PLUS WiFi, 32GB DDR4 Kingston Predator@3600/16/19/21/36, Sapphire Pulse RX6600 8GB, Toshiba XG3 1TB M.2 NVMe, Seasonic Platinum SS-660XP2, Riotoro Morpheus, ASUS VG279Q, Marvo K605, Rapture ASPIS, Superlux HD-330 + Brainwavz Sheepskin earpads, Edifier WN820NB
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Re: Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

Nah, az teraz som si vsimol, ze vypocitava polozka ta "nedostane" na zdrojove data :(.
Najlepsie riesenie bude asi nejaky BI system.
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM
Používateľov profilový obrázok
wingo
Moderátor
Moderátor
Príspevky: 5789
Dátum registrácie: St 01. Sep, 2010, 20:36
Bydlisko: Podbrezová

Re: Excel - komplikovany PIVOT

Príspevok od používateľa wingo »

Netuším čo tým myslíš že ťa nedostane na zdrojové dáta... myslíš že tým dvojklikom ti neotvorí zdrojovú tabuľku? Ak mám pravdu povedať pri vypočítanej položke som to neskúšal...

Qlik Sense Desktop je zdarma napríklad, aj keď netuším či v ňom dokážeš spraviť úplne presne to čo chceš
Spoiler: ukázať
STEAM: http://steamcommunity.com/id/X-w1n9/

PC1:
Ryzen 5 7600X, Noctua NH-D14+Arctic P14 PWM PST, Gigabyte B650M Gaming X AX, 32GB DDR5 Kingston Fury Renegade RGB @ 6000/28/37/37/30, Gigabyte AORUS RX 6800XT Master 16GB, SSD Kingston Fury Renegade 1TB, Adata XPG Core Reactor 650W, BeQuiet Pure Base 500DX + 3x Arctic P14 PWM PST, LG 42OLEDC21, Gamdias Hermes P3 Brown, Logitech G400, Hifiman HE-4XX + Topping NX4DSD

PC2:
Ryzen 5 5600X, ASUS TUF Gaming B550M-PLUS WiFi, 32GB DDR4 Kingston Predator@3600/16/19/21/36, Sapphire Pulse RX6600 8GB, Toshiba XG3 1TB M.2 NVMe, Seasonic Platinum SS-660XP2, Riotoro Morpheus, ASUS VG279Q, Marvo K605, Rapture ASPIS, Superlux HD-330 + Brainwavz Sheepskin earpads, Edifier WN820NB
Používateľov profilový obrázok
GASPi
Používateľ
Používateľ
Príspevky: 2614
Dátum registrácie: Pi 01. Jún, 2007, 08:00
Bydlisko: Trnava/Hlohovec

Re: Excel - komplikovany PIVOT

Príspevok od používateľa GASPi »

Ano, neotvori sheet so zdrojovymi datami. Navyse vypocitave polozky su kalkulovane pre kazdu kombinaciu, t. j. pri viac urovnovom pivote ti doplni urovne aj tie, ktore sa rovnaju 0.
Spoiler: ukázať
CPU: Intel Core i5-4670K (3,4GHz@???), CPU cooler: Cooler Master Hyper 212+ EVO, MB: ASUS Z87-A, GPU: MSI GeForce GTX 1080 Ti GAMING X, DDRAM: 2x4GB Kingston DDR3 1600MHz CL9 HyperX X2 Grey Series, SSD: CRUCIAL M4 256GB + SAMSUNG 850 EVO 500GB HDD: SAMSUNG HD501LJ 500GB SATA, DVD-RW: ASUS DRW-24B1ST, SOUND: ASUS Xonar Phoebus + Logitech Z-2300 + Sony MDR-1RBT, CASE: Fractal Design Define R5 Blackout, PSU: EVGA SuperNOVA 750 G2, LCD: 34" Xiaomi Mi Curved Gaming Monitor
BattleTag
STEAM

Návrat na "Programy a problémy s nimi"