Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

#1 8. 5. 2017 00:48:48

Jirka_R
Člen
Registrace: 7. 5. 2017
Příspěvků: 2

Automatické doplnování dat na 1listu z dat na 2 listu

Ahoj všen profíkům na LibreOffice Calc, předem se omlouvám za svou tupost ale Calc používám cca 9 dnem (do té doby jsem aktivně používal excel) a lámu si hlavu od pátku sad.

Mám dva listy a potřebuju z druhého listu "Plyn - zdroj" automaticky doplnit data do prvního listu "Plyn" s tím že to má víc podmínek.
"Plyn" - Mám zde několik rolovacích seznamů přes Data > Plantnost.
Rolovací seznamy mám:
1. seznam "Typ odběru" na výběr Domácnost nebo Podnikatel
2. seznam "Kraj" na výběr všechny kraje ČR
3. seznam "Distributor" na výběr EON nebo RWE nebo PRE
4. seznam "Sazba" na výběr 7 možností
5. seznam "Starý tarif" několik set možností

Po zvolení kombice z 5 seznamů by to mělo najít cenu v listu "Plyn - zdroj" a vepsat do listu "Plyn" správnou cenu za 1MWh.

Offline

#2 8. 5. 2017 02:09:49

Jirka_R
Člen
Registrace: 7. 5. 2017
Příspěvků: 2

Re: Automatické doplnování dat na 1listu z dat na 2 listu

Přikládám i vizuální náhled ceny na 1 Listu by se měli doplnit samy (Momentálně dopsany ručně pro představivost)18341850_1460578087312860_3413825491753030523_n.jpg?oh=7c86a60b8f87a049f948395efeddcad9&oe=59BD79E6
18301443_1460578020646200_751109210680771683_n.jpg?oh=2ee327bc5203af5392c8f51d466c82ec&oe=597D90B6

Offline

#3 8. 5. 2017 09:31:17

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Automatické doplnování dat na 1listu z dat na 2 listu

Pokud pro danou kombinaci existuje jen jeden výsledek pak lze použít poměrně snadno funkci sumproduct. Zhruba v tomto tvaru =SUMPRODUCT(list2.A2:A100="Domácnost";list2.B2:B100="Jihočeský";další podmínky;list2.F2:F100)... samozřejmě "Domácnost" nahradit odkazem B3 či kde to máte atd. V případě žádného výsledku vrátí sumprodukt nulu.
Pokud by však existovaly dva či více výsledků pak se ceny sečtou, tzn. výstup je nesmyslný. Pak by možná bylo lepší použít funkci DGET, která sice při zdvojení výsledku vyhledávání nebo žádném výsledku vrátí chybu, ale to se dá ošetřit a aspoň víte že je v databázi nabo v zadání chyba.
Funkce DGET je postavena tak že nejdříve označíte datbázi: list2.A1:I100, pak zadáte název sloupce který chcete mít ve výstupu "Pevná cena za odebraný plyn" (nebo můžeta zadat číslo sloupce tzn. 6), nakonec zadáte odkaz na kriteriální tabulku, kterou si nachystáte bokem. Tam bude v 1. řádku
Typ odběru, Kraj, Distributor ...
a ve druhém řádku ty podmínky dle nichž vybíráte (tedy musí to být přesně naopak než to máte uvedeno na obrázku (tz. ne ve dvou sloupcích ale ve dvou řádcích)
V případě že jsou v databázi dva výsledky tak je možnné funkci ošetřit takto:
=IFERROR(DGET(list2.A1:I100;"Pevná cena za odebraný plyn";F1:J2);"Chyba v databázi")

Editoval ludviktrnka (8. 5. 2017 10:13:24)


LibreOffice 5.4.

Offline

#4 8. 5. 2017 09:52:23

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 3,435

Re: Automatické doplnování dat na 1listu z dat na 2 listu

Já jsem se pokusil dohledat správná data ale Vaší výchozí tabulku (list2) jsem z toho nesestrojil. Navíc nevím jestli lze použít původní názvy distributorů (2016) spolu stěmi novými (2017).
     V každém případě když se to doplní tak na to postačuje kontingenční tabulka. V jiném případě je nutno tabulku z listu 2 naplnit unikátními vyhledávacími daty - která by ale měla být jinde (na jiném listu, nebo na jiném úseku ap. jako součást vyhledávaných dat.)


     Vyhledávací vzorce je můžné seastrojit různě, ale platí že postačuje jen několik zadaných údajů protože například při unikátní sazbě dodavatel nemusí být dohledáván, nebo někdy i opačně. Naopak hrozí chyba ze zadání "AND" všech definovaných pojmů. Myslím zrovna že Pražská distribuční má tarif jen pro domácnosti respektive malé odběry a velkoodběratele nezásobuje - dojde k chybě protože nelze splnit podmínku AND.


     Asi byste měl postnout sešit se vzorem. Postavit správně list na kterém máte data se hned tak nepodaří. Navíc pokud tam máte plně prokombinovaná data tak je vzorec zbytečně složitý - kontingenční tabulka to jistí.
     Předpokládám, že na listu 2 je mnoho řádků na kterých jsou všechny kombinace dat, takže pak by měl stačit filtr který vrátí výsledek do jediného pevně daného řádku (může být na listu 2) a ten se načte do sloupce podle Vašeho požadavku.


     Pokud víte že některé volby vedou na chybu logické funkce AND - musí se větvit na faunkci OR. Z toho ale plyne například několik souběžných možností.


Doporučuji upravit vhodně svúj sešit a postnout ho sem. nemusí tam být všechna data, ale měl by obsahovat všechny různé případy kombinací.


Moje e-mailová adresa
Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte orientaci při vyhledávání řešení JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

#5 8. 5. 2017 13:02:43

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 697

Re: Automatické doplnování dat na 1listu z dat na 2 listu

... ještě by bylo fajn do definece "platnost dat" (menu: Data -> Platnost ...) v políčkách B3 až B7 (nebo tak nějak kde máte ty roletky) vyplnit vzorec závislý na předchozím výběru. Tzn. např. ve výběru Distributora v políčku B5 bude v platnosti dat vyplněn vzorec IF($List2.$B$2:$B$100=B4;$List2.$C$2:$C$100;""). Tím se aspoň částečně omezí výběr na data platná pro předchozí výběr) (což tedy trošku supluje poměrně "dokonalou" kontingeční tabulku, která je však graficky naprosto nemožná)

EDIT: ono tedy samozřejmě ty podmínky lze stavět i za sebe takže ten výběr se zůží dle všech předchozích zvolených podmínek např poslední bude:
IF($List2.$A$2:$A$100=B3;if($List2.$B$2:$B$100=B4;if($List2.$C$2:$C$100=B5;if($List2.$D$2:$D$100=B6;$List2.$E$2:$E$100;"");"");"");"")

Tím zároveň zajistíte platnost zvolené kombinace.

Editoval ludviktrnka (8. 5. 2017 13:11:47)


LibreOffice 5.4.

Offline

#6 8. 5. 2017 13:14:49

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 3,435

Re: Automatické doplnování dat na 1listu z dat na 2 listu

Ještě uvádíte že máte zkušenosti s Excelem. Předpokládám že tam jste tento systém vzorců měl funkční. To by se ale nemělo lišit. V Calcu nefungují makra z Excelu ale vzorce fungují. Ačkoliv na první pohled jsou počeštěné názvy odlišné není to tak.
     Možná je trošku jinak "platnost dat" ale to je zřejmě jenom detail. Domnívám se že to bude v Calcu fungovat stejně jako v Excelu a nejspíš bez úprav.


Moje e-mailová adresa
Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte orientaci při vyhledávání řešení JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

Zápatí