Příspěvky uživatele
< návrat zpět
@elninoslov
Gabča mohla zmínit, proč to tak chce. Pokud bude brouzdat po dlouhé tabulce, pak je jedno jak se to udělá, hlavně že to funguje.
Pokud to chce nějak zesumarizovat, pak se nabízí datový model (maximum možností, ale je to jiný level) nebo aspoň kontingenční tabulka. Pro každou tuto variantu lze zpravidla vytvořit vhodnější strukturu dat, než je prosté spojení tabulek.
Vyplňování dat z listů pomocí vzorců, navíc s nepřímým dotazem spotřebovává hodně prostoru i času při přepočtu.
Použij např. powerquery k načtení dat, tam můžeš navíc zavést časové korekce pro každý loger.
K nalezení dynamické oblasti je zpravidla jednodušší (a snad i přehlednější) použití funkce XLOOKUP, pokud je k dispozici). Podobně, při hledání sloupců/řádků oblasti, které splňují podmínku je užitečná funkce součin.matic:
I2:
=LET(
data;XLOOKUP(B2+B1;Vyhodnocení!A4:A1000;Vyhodnocení!M4:M1000;Vyhodnocení!M4;-1):Vyhodnocení!AS1000;
TEXTJOIN(" / ";NEPRAVDA;MIN(data) - nejistotaT;MAX(data) + nejistotaT))
(XLOOKUP vrací odkaz)
J2:
=LET(
data;XLOOKUP(B2+B1;Vyhodnocení!A4:A1000;Vyhodnocení!M4:M1000;Vyhodnocení!M4;-1):Vyhodnocení!AS1000;
logery;Vyhodnocení!M3:AS3;
Sl;SEQUENCE(1;ŘÁDKY(data);1;0);
minL;TEXTJOIN(",";;FILTER(logery;SOUČIN.MATIC(Sl;--(data=MIN(data)))));
maxL;TEXTJOIN(",";;FILTER(logery;SOUČIN.MATIC(Sl;--(data=MAX(data)))));
TEXTJOIN(" / ";;minL;maxL))
CMM-Team napsal/a:
funkce filter hlásí chybu Přesah dat.
Funkce nemá prostor pro vypsání nalezených hodnot. Někde v listu pod buňkou s funkcí jsou data neb tuo vzorce. Vložte vzorec někam, kde je místo.
CMM-Team napsal/a:
vzorec:
=INDEX(Seznamy!$E$3:$E$200; POROVNAT(D7; Seznamy!$F$3:$F$200; 0))
Který by měl dohledat jméno z listu seznamy
Místo podobných konstrukcí raději používejte normální vyhledávací funkce. V tomto případě se nabízí XLOOKUP.
Než ms dotáhne ideu, tak to nějakou dobu trvá.
Do e3 jsem vložil filter:
=FILTER(Seznamy!$B$3:$B$200; Seznamy!$B$3:$B$200<>"")
upravil jsem odkaz v definici Zarizeni na:
=Seznamy!$E$3#
a funguje to...
Ten křížek v odkazu je důležitý, znamená, že odkaz je na celou dynamickou oblast, která začíná na Seznamy!$E$3.
Pište vždy, co máte za verzi. V novějších excelech samotný enter stačí.
@elninoslov
Používám:
= Table.TransformColumnNames(Zdroj, each Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({" ", "#(00A0)"})(_), each _ <> ""), " "))
Přesněji - potýkám se i s dalšími kódy mezer, tak to přepisuji cca tak:
Mezery = (_ as nullable text) as text => Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({" ", "#(00A0)"})(_), each _ <> ""), " "),
Vlastní1 = Table.TransformColumnNames(Zdroj, Mezery)
Ve funkci Mezery je dost "_". Tak to možná bude čitelnější:
Mezery = (_ as nullable text) as text => Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({" ", "#(00A0)"})(_), (item) => item <> ""), " "),
Funkce Mezery může být deklarována samostatně.
Pokud je u funkce deklarován typ, tak mi to nemění typ sloupce při transformaci sloupce.
Mimochodem, v pq jde (jako všude jinde) řešit věci mnoha způsoby.
Jedním z problémů je nalezení vhodné funkce/funkcí a jejich kombinací.
Nezavíráte náhodou sešit ve kterém je kód? Před zavřením kopie otevřete vzor.
Po načtení je zpravidla v dotazu nejprve Zdroj.
Za něj přidat následující
= Table.TransformColumnNames(Zdroj, each Text.Combine(Splitter.SplitTextByAnyDelimiter({" ", "#(00A0)"})(_), ""))
Maže to normální mezery a pevné mezery (kód "#(00A0)").
Pokud tam bude jiná mezera, tak si ji přidejte po vzoru pevné mezery: "#(unicode kód, 4 nebo 8 znaků)"
Kód text rozdělí na místě zadaných znaků (smaže je) a pak zbytek opět spojí bez mezery.
(mimo záznam:
funkce Splitter.SplitTextByAnyDelimiter vygeneruje funkci, která dělí text podle libovolného oddělovače v parametru.
Splitter.SplitTextByAnyDelimiter({" ", "#(00A0)"})(_)
dělí každou hlavičku sloupce, ...
)
Někdy je ještě něco takového:
= Table.PromoteHeaders(Zdroj, [PromoteAllScalars=true])
tj. z prvního řádku to udělá hlavičku. Potom příkaz patří o řádek níž.
Alfan napsal/a:
Jen ještě tyto dotazy:
1) Po načtení dat je užitečné maximálně zmenšit velikost souboru, tj. výmazy nepotřebných sloupců a řádků provádějte co nejdříve. (Potažením myší okně nastavení dotazů).
Co to je přesně to Potažením myší? Já myslel, že ty sloupce označím a pak smažu?
Pokud tvoříte nový skript, tak ano, ale podle zaslané ukázky to tak neděláte.
Pokud to chcete srovnat v hotovém skriptu, tak klik na příkaz, stisknout levé tlačítko a příkaz můžete přesunout na jiné místo. Odkazy na tabulky se přizpůsobí.
Alfan napsal/a:
a) Výmaz podle kódu:
...
Jak ho dostanu do řádku v Nastavení dotazů? Vím, blbej dotaz, ale fakt netuším. Ten dotaz, kde bych ho vkládal se jmenuje "denik".
Kliknete v okně s příkazy na příkaz, za který chcete vložit filtr.
Ve sloupci "Kód akce" kliknete na šipku v hlavičce sloupce, kliknete na "Filtry textu" a vyberete "Nezačíná na..."
V menu klik na upřesnit a zadejte všechny podmínky. V řádku vzorců se objeví
= Table.SelectRows(#"předchozí něco...", each not Text.StartsWith([Kód akce], "PR") or not Text.StartsWith([Kód akce], "ESB") or not Text.StartsWith([Kód akce], "ESP"))
Na všechny vzorce výše v diskusi zapomeňte.
@ Alfan
Na doplnění do přiloženého skriptu si netroufám. Z prostředí PQ je to nesrovnatelně pohodlnější.
Pár poznámek.
1) Po načtení dat je užitečné maximálně zmenšit velikost souboru, tj. výmazy nepotřebných sloupců a řádků provádějte co nejdříve. (Potažením myší okně nastavení dotazů).
a) Výmaz podle kódu:
na vhodné místě naklikejte výmaz podle jedné podmínky.
Potom na řádku vzorců (pokud není vidět - Soubor/Možnosti a nastavení/Možnosti dotazu/Editor PQ/Zobrazit řádek vzorců) nahraďte část vzorce za each ...
b) Mazání sloupců - mažte jedním příkazem (Vybrat všechny nepotřebné vzorce a odebrat sloupce, pokud zůstanete na příkazu tak můžete odebírat i dalši a pq to dá do jednoho řádku.
2) Přetypování:
Poněkud komplikované téma. Typy jsou všude na různých úrovních a typ je v pq také proměnná s typem type ...
Zjednodušeně:
a) Typ sloupce slouží k nastavení vhodného menu, pokud se rozklikne šipka v hlavičce sloupce.
b) Typ sloupce u finální tabulky slouží k předání informace do excelu, ...
c) Typ sloupce a typ hodnoty v buňce spolu nesouvisí.
d) Změnit typ v kontextové nabídce provede konverzi jednotlivých buněk a nastaví také typ sloupce. Chyby (např. pokud hodnotu nelze na požadovaný typ zkonvertovat) se nehlásí. Chyba se projeví až když se konkrétní hodnota potřebuje, často až při exportu do excelu.
d) Při řadě operací s tabulku se typ "ztratí".
Tj. časté přetypování všeho možného není účelné. Pro nastavení typu sloupce má řada funkcí (Table.AddColumn, ...) samostatný parametr, pokud se hodnota ve sloupci počítá funkcí, přebírá se často typ z deklarace funkce.
Často stačí doladit typy včetně přetypování hodnot na konci skriptu.
3) Nahrazování (různé). Lze vybrat více sloupců.
ps. Pokud chcete vědět, co v modelu zabírá místo, existuje řada nástrojů. Celkem šikovný je doplněk PowerPivot utilites. (Verze co mám sice při startu hlásí chyby, ale stačí se vším souhlasit...)
Něco takového? Netestovali jsem to. Pokud chci mazat řádky podle více podmínek jsou dvě možnosti: mazat opakovaně - při větším počtu podmínek a velké tabulce mohou vzniknout problémy s pamětí nebo testovat všechny podmínky najednou. Hledáme podmínky pro výmaz, proto je při použití Tablet.SelectRows nutné výslednou podmínku negovat.
Table.SelectRows(#"tabulka XXX",
each not List.MatchesAny({"PR","ESB","ESP"},
(t) => Text.StarsWith([Kód akce],t)))
V Excelu 1 = 1.
Problém je v tom, že se nepracuje s desítkovou soustavou . Velká a desetinná čísla se ukládají (v desítkovém ekvivalentu na cca 18 číslic, přitom pokud se číslo převede z interního vyjádření do desítkové soustavy, tak se často s původním číslem neshodují. Někdy je toto číslo větší, někdy menší. Pokud má číslo jen desetinnou část, chyba se projeví zpravidla až za 15-tým desetinným místem. Vše co je v čísle před desetinnou čárkou, posouvá nepřesnosti taky doprava. Pokud je tedy číslo cca 45000, posune se chyba o 5 pozic na cca 10. desetinné místo.
Normálně lze zobrazit 15 desetinných míst. Excel při výpočtech často tyto chyby koriguje.
Stručně.
A) Použijte vzorec elnina, případně některou z dalších variant.
B) Pokuste se pochopit, jak Excel funguje a jak fungují použité vzorce (zvlášť pokud je někde najdete nebo jsou výsledkem techniky pokus-omyl).
Ne, nemýlíte se.
Problém je v rozsahu hodnot ve sloupci V. Tím, že zde jsou čísla cca 50000, jsou číslice od cca 10-té pozice jen přibližné.
Problém vzniká, pokud je uložená hodnota menší než skutečná a proces končí na konci dne.
Konkrétně
Mod(45104,2;1) je 0,199999999997090, tj. trochu méně než 0,2.
Počet dnů pro workday je potom 7,999999999997090, po useknutí zůstane jen 7.
Vaše řešení potom do výsledku vrátí chybějící hodnotu 0,999999999997089.
Další možnosti jsou:
=WORKDAY(V4;F4+MOD(V4;1) +1e-9;svátky!$I$1:$I$13)+MOD(F4+V4;1)
=WORKDAY(V4;ZAOKROUHLIT(F4+MOD(V4;1);1);svátky!$I$1:$I$13)+MOD(F4+V4;1)
Asi i jiné.
Něco takového?:
=LET(
zacatek;V3;
zacatek_hod;MOD(zacatek;1);
doba;F3 + zacatek_hod;
doba_hod;MOD(doba;1);
WORKDAY(od;doba)+doba_hod)
Nevím, jak se má počítat s desetinami - jsou to zlomky dne nebo zlomky pracovní doby nebo směs?
Podle odpovědi je nutné vhodně upravit zlomky (buď sumu nebo dobu trvání).
tj. Nejprve spočítat celkovou dobu od počátku prvního dne, spočítat workday a pak vrátit zbývající hodiny.
Bez let to nakonec vypadá snažší (bez úprav v počítání desetin samozřejmě):
=WORKDAY(V3;F3+MOD(V3;1))+MOD(V3+F3;1)
Zdroj ponechat v excelu poslat na gs - jen tabulky.
Aktualizace jde nastavit i bez maker (typ souboru bude vhodný pro export)
Nebo úplně vynechat Excel. Data poslat do gs + použít funkci filter. Gs ji má hodně dlouho.
Případná synchronizace se změnami je vždy problém, zdrojová data jsou v jiném systému. Já bych to (s dostupnými informacemi) ignoroval.
Uživatelské menu
\nMenu
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
Helios iNuvio
Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.
RSS nejnovější články