Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

JPT · 25. 1. 2020 06:53:52

Dalo by se udělat v calcu toto?

Na jednom listu (nebo souboru) by bylo víc sloupců.
Na druhém listu (nebo souboru) by byl v určitém sloupci (ID) seznam číselných hodnot .
Podle těch číselných hodnot (jednu po druhé) by to vyhledalo všechny výskyty ve sloupci Details (z prvního listu) a překopírovalo by to výsledný celý řádek (nebo jen určité sloupce toho řádku) do nového listu (další nálezy by to pak kopírovalo a řadilo už do stejného listu pod sebe).
Každý souhrn výskytů a překopírovaní by byl doplněný o určitou funkci (XIRR).

A nebo by se data z prvního listu nekopírovala a jen by se u zkopírovala ta číselná hodnota ID z druhého listu do nového listu a napozadí by se vypočítalo XIRR a uvedlo by se to vedle té číselné hodnoty. (Screenshot List 4)

Díval jsem se na funkce a přes ty to asi nepujde (nebo jsem nepřišel jak na to, protože se v tom zas tak nevyznám) a přes makra to je na mě moc složité a nejspíš bych se to nikdy nenaučil.

Pokud někdo zvládá makra levou zadní a nudil by se nebo by se mu to chtělo udělat, tak díky.

Tady jsou screnshoty jak vypadají ty sešity a jak bych si to představoval https://uloz.to/file/iOXbSQYjfboU/scree … ice-cz-zip

Editoval JPT (31. 1. 2020 07:59:35)

neutr · 25. 1. 2020 08:51:18

I když to vypadá složitě, technicky je to jednoduchý problém. Jde o to získat z prvního listu jednotný údaj "ID", který ale může mít 2 podoby.

Toho můžeme dosáhnout několika různými způsoby, ale nejsnadnější postup je zřejmě tímto vzorcem do pomocného sloupce v Listu1 (Sheet1):

=IF(LEFT(C2;6)="Půjčka";MID(C2;8;10);RIGHT(C2;10))

Princip je v tom, že ID může mít 2 pozice umístění. Buď na konci textu sloupce C, nebo na začátku hned za výrazem "Půjčka".
Takže dotaz testuje zda prvních 6 znaků je slovo Půjčka - a tehdy vrátí ID které začíná od 8. pozice a je to 10 znaků. V jiném případě vrátí posledních 10 znaků řetězce. Je tedy podstatné, aby tyto parametry byly vždy obsaženy. Pokud by se vyskytla odchylka byla by to chyba. To ale musíte potvrdit, nebo upřesnit. Ze screenů nevidím jiné podoby umístění ID.

Takže takto získáme další sloupec který už obsahuje pouze ID. Podle toho můžete snadno řadit buď automatickým filtrem, nebo vytvořit kontingenční tabulku. Ta umí vyexportovat data do podoby kterou popisujete na obrázku číslo 3. Zbývá aplikovat funkci XIRR ale to už není velký problém.
Doporučoval bych postup s pomocným sloupcem na prvním listu a kontingenční tabulku přímo v tomto listu (stejně tam máte hlavičky autofiltru). Výstupy - setříděné záznamy podle ID + datum nasměrujete do listu 3.
Trošku jiný problém je kontrola obsahů podle listu 2 kde se může asi vyskytnout problém existence položky která se v listu 1 nevyskytuje. Zde bych doporučil každou položku vybavit v pomocném sloupci funkcí COUNTIF která je navedena na pomocný sloupec listu číslo jedna. Součet tohoto sloupce musí dát číslo shodné s počtem řádků v listu 1.

Jde to i jinými způsoby, ale tento je nejsnadnější. Pokud by se data vyskytovala ve více souborech, doporučil bych spíš makro. Myslím že pokud jsou texty ve sloupci "C" prvního listu stejné, není problém mimo toho, že by mohlo být záznamů mnoho. V případě že data zpomalují tabulkový procesor tak bychom také pužili makra s výstupem do jiného sešitu.

Editoval neutr (25. 1. 2020 08:53:17)

JPT · 31. 1. 2020 07:57:18

Udělal jsem si teda ten pomocný sloupec s ID, pak kontingenční tabulku do nového listu, vedle ní tabulku s tím co mě u té půjčky zajímá a nakonec se mi podařilo částečně přes zazanmenání makra a částečně z nalezených řešení na netu, aby to co chci se kopírovalo do dalšího listu. Neni to sice uplně super (je to pomalé a skáčou tam listy jak to makro běží), ale lepší než nic.

neutr · 31. 1. 2020 08:30:55

JPT napsal(a)

Udělal jsem si teda ten pomocný sloupec s ID, pak kontingenční tabulku do nového listu, vedle ní tabulku s tím co mě u té půjčky zajímá a nakonec se mi podařilo částečně přes zazanmenání makra a částečně z nalezených řešení na netu, aby to co chci se kopírovalo do dalšího listu. Neni to sice uplně super (je to pomalé a skáčou tam listy jak to makro běží), ale lepší než nic.

Pokud postnete nahrané makro - přepíšu ho já, nebo někdo jiný tak aby to neskákalo. To skákání je je dáno typen UNO makra. Pokud na listu 2 máte víc nežli několik desítek položek tak i ta hektická práce se zápisem funkce XIRR lze řešit.

Postupy jsou sice navržené na klasické vzorce a postup nůže být jiný - například vše čistě makry. Jedná se ale o to, zda úpravu souboru řešíte jenom 1x za rok, nebo naopak 10x denně. I postup se vzorci lze upravit tak aby manuální zpracování bylo rychlejší nežli nahranými makry.
Pokud nedůvěřujete komtingenční tabulce [KT] (respektive neovládáte plně všechny možnosti) lze to udělat i bez KT prakticky stejně rychle jen vzorci, ale chce to zase rutinu při operacích kopírování, vkládání řádků do vytříděného souboru podle pomocného vzorce a také současné vložení vzorce, změnu vzorců na hodnoty a další "triky".
Pokud si s KT umíte poradit dobře, uděláte si sice "irelevantní" agregační funkci podle možností co nejpodobnější s XIRR. Následně pomocí dialogu "najít a nahradit" změníte "irelevantní" vzorec (například SUM, AVG a pod.) na XIRR. To je takový docela jednoduchý trik, ale pokud umíte pracovat s klávesovými zkratkami, tak se Vám vzorce vyplatí v případech kdy neděláte úpravy každý den. Ale i potom lze celý manuální postup nahrát a pustit na jediné kliknutí.

JPT · 2. 2. 2020 15:05:19

Úprava nebo teda zjišťování stavu NEbude několikrát denně. Spíš jen občas. Nejlepší by bylo, kdyby to makro dokázalo přeskočit záznamy, které už počítalo a počítalo by a přidávalo do seznamu už jen nové. To by se ale dalo řešit tak, že si stáhnu z platformy seznam jen od určitého data.

U kontingeční tabulky nevím, jestli znám všechny možnosti, které nabízí.

Irelevantní vzorce a triky s nima neumím.
Když vytvořím kontingenční tabulku, tak se vypíšou všechny položky a k nim všechny záznamy a nevím jak bych do toho jinak než ručně ke kažné přidal, aby to něco počítalo vzorcem, když každá položka má jiný počet záznamů (List 3 - Kontingenční tabulka 1. podoba.jpg).
Vymyslel jsem to teda tak, že si vyfiltruju položku a mám pevně přiřazené sloupce pro XIRR (List 3 - Kontingenční tabulka 2. podoba + Tabulka.jpg)

Celé to teda funguje tak, že z listu Acount statement se vytvoří kontingenční tabulka do listu Kontingenční tabulka. Z listu Finished investments se zkopíruje hodnota ID (musí tam být ale aktivní buňka jinak to neumím) do buňky F16 na listu Kontingenční tabulka z té buňky si to načte makro a vyfiltruje to v kontingenční tabulce, podle vyfiltrované hodnoty se doplní tabulka vedle kontingeční tabulky (z listů Acount statement a Finished investments) a z toho se nahraným makrem zkopírují data do listu Seznam a to celé se opakuje.

Kdyby to makro dokázalo dělat bez toho skákání a nejradši, kdyby se mě i zeptalo, jaké soubory má použít místo toho, abych jejich obsahy kopírovaly do listů (Acount statement a Finished investments) a kdyby se chod makra ukončil pokud bude prázdná buňka ve sloupci ze kterého to kopíruje (List Finished investments Sloupec B), tak by to bylo super.

Hledal jsem různé návody nebo články jak co udělat, ale asi je to nad moje schopnosti. Asi bych se prva musel naučit programovat nebo aspoň nějaké základy. Ale zatím to moc nechápu.

Screenshoty: https://uloz.to/file/k8Y2TsiGr5Wa/scree … e-cz-2-zip

Makro: https://uloz.to/file/fk7O5je4oisR/makro-txt

Kdyžtak můžu nahrát a dát odkaz přes soukromou zprávu na ten můj soubor.

Editoval JPT (2. 2. 2020 16:00:00)

neutr · 2. 2. 2020 17:47:30

JPT napsal(a)

Kdyžtak můžu nahrát a dát odkaz přes soukromou zprávu na ten můj soubor.

Ano tohle by bylo nejlepší. Já umím udělat simulaci různých sestav, ale tahle Vaše je docela složitá. Na tom bych strávil 10x víc času nežli vlastním programováním. Vidím ale že do listu3 stačí zkopírovat pouze některé sloupce - to můžu nasimulovat dobře.

Ze screenu nevidím ani celý rozsah sloupců, ani řádků. Makro nejde vůbec stáhnout.
Podle všeho exportujete do listu3 pouze sloupce B, D, G (list1). Setřídění_1. sloupce G (ID), setřídění_2. podle sloupce B (datum transakce). Vzorec XIRR se dává pod jednotlivě setříděná ID (nutné vložení řádku). Export jednotlivých výsledků (XIRR) pro každé ID v jediném řádku - do stejného listu4. Lze natagovat jako hyperlink pro list2 (seznam půjček) který skočí z listu4 na list2 pro detail - tedy podle potřeby a pro kontrolu. Toto jsem schopen nasimulovat snadno. Udělám "nástřel" tak aby to chodilo na ostrém souboru.
Mou adresu vidíte na příspěvku a pro jistotu Vám pošlu ještě jiný. Myslím že výše uvedený popis nemusí být správně. Vzor by byl lepší

neutr · 2. 2. 2020 23:26:45

Otestujte Simulace_1.
Asi to není správně, ale podle Vašich screenů jsem se dostal jenom k přípravě tabulek. Podle všecho budu potřebovat vzor jinak úplné tabulky nedám. Jestli Vám to stačí takto, udělal bych to s přístupnou knihovnou.

JPT · 3. 2. 2020 00:31:16

Ty makra jsou zamklé. V tom sešitu to nefunguje a do mého sešitu to nedostanu. Tak nevím jak to mám otestovat ?

neutr · 3. 2. 2020 08:05:47

Omlouvám se udělal jsem chybu. Poslední akcí bylo zavedení ovládání do hlavní nabídky OVLÁDÁNÍ XIRR. Já jsem to nepřesměroval do sešitu "Simulace", ale na všechny sešity Calc. Takže u mne měly všechny sešity OVLÁDÁNÍ XIRR, ale odeslaný nikoliv. Už se mi to párkrát povedlo.

Opravený soubor je zde Simulace_2.

Ještě jednou se omlouvám

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 25. 1. 2020 06:53:52

Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#2 25. 1. 2020 08:51:18

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#3 31. 1. 2020 07:57:18

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#4 31. 1. 2020 08:30:55

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#5 2. 2. 2020 15:05:19

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#6 2. 2. 2020 17:47:30

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#7 2. 2. 2020 23:26:45

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#8 3. 2. 2020 00:31:16

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

#9 3. 2. 2020 08:05:47

Re: Překopírování určitých řádků do nového listu a doplnění funkceVYŘEŠENO

Zápatí