Příspěvky uživatele

Tahle má asi nejmenší režii:

=A2:INDEX(List1!$D:$D;MAX(KDYŽ(List1!$A:$D<>"";ŘÁDEK(List1!$D:$D);1)))

Vzorec je maticový, ake výrazy v názvech se automatiky vyhodnocují maticově.

Tohle už je volatilní:

=List1!$A$2:POSUN(List1!$D$1;MAX(KDYŽ(List1!$A:$D<>"";ŘÁDEK(List1!$D:$D)));0)

nebo rovnou

=POSUN(List1!$A$2;;;MAX(KDYŽ(List1!$A:$D<>"";ŘÁDEK(List1!$D:$D)));4)

ps.

Když budeš něco podobného ladit, dej ctrl-g a vzorec zapisuj do pole odkaz...

Vzorce jsou šílený. Budu předpokládat, že to není recese. Není vidět, jak velká data budeš zpracovávat a kolik výpočtů k tomu budeš potřebovat.

Max a min - na to jsou funkce:
=MAX(DATA!$D$3:$D147)

Datum a čas výskytu maxima:
=HODNOTA.NA.TEXT(SUMA(INDEX(DATA!$B$3:$C147;POZVYHLEDAT(C2;DATA!$D$3:$D147;0);0)); "d.m.rrrr - hh.mm.ss")

Hledáme jednou, sečteme nalezený datum a čas a převedeme na text.

Výskyty jednoduše:
=HODNOTA.NA.TEXT(SUMA(--(DATA!$D$2:$D147=C2));"(\ 0\x\ )")

Jinak pokud už máš funkce sumif, countif, tak je můžeš použít. I v případě zadání celého sloupce je jejich režie poměrně malá (na rozdíl od jiných funkcí -- sum, max, součin.skalární, ..., které zpracovávají všechny buňky)

V opačném případě se vyplatí udržovat odkazy ručně nebo použít šílenosti typu d3:index(d:d;pozvyhledat(1000000000;d3:d65535)).

Všimnul jsem si dvou problémů.

V kódu dotazu má být:
html = Text.Replace(html_maska, "<<Datum>>", Date.ToText(datum, "d.M.yyyy") ),

Tj. ve formátu má být "M" a ne "m". Jinak stránka vrací (zřejmě) poslední data.

Dále je vhodné zakázat aktualizaci dotazu na pozadí:
Pravý klik na na dotaz "Table 0", vlastnosti, zrušit zaškrtnutí u "povolit aktualizaci na pozadí".

Adresa v dotazu je normální text. V PQ se to opraví snadno:

let
datum = #date(2022,2,9),
html_maska = "https://www.kurzy.cz/~nr/kurzy-men/nejlepsi-kurzy/EUR-euro/D-<<Datum>>-Ordb--Ord--Z-3-s-/",
html = Text.Replace(html_maska, "<<Datum>>", Date.ToText(datum, "d.m.yyyy") ),
Zdroj = Web.Page(Web.Contents(html)),
...

Datum je tu natvrdo, nenapsal jsi, kde se má brát. Asi bych ho četl z listu.

ve vba je to podobné:

Dim wb As Workbook
Dim dotaz As String

Set wb = ThisWorkbook

dotaz = wb.Queries("Table 0").Formula

v proměnné dotaz je text dotazu. Musíš jen najít datum, nahradit ho a vrátit opravený dotaz zpět.

Za mne je řešení přes PQ snažší.

Dotazuješ se na jméno souboru v jednom dotazu a soubor čteš v jiném. To vadí ochraně osobních dat. V prostředí PQ jde o hlášení Formula.Firewall.

Když to provedeš v jednom dotazu, tak to projde.

// Zdroj
let
Soubor = Excel.CurrentWorkbook(){[Name="FPath"]}[Content][Column1]{0},
Zdroj = Table.FromColumns({Lines.FromBinary(File.Contents(Soubor), null, null, 1250)})
in
Zdroj

Ochrana se zablokuje v nastavení. Tam se dostaneš z excelu:
Data/rozbalit "Načíst data"/"Možnosti dotazu"

Nebo z prostředí PQ:

Soubor/Možnosti a nastavení/Možnosti dotazu.

V dialogu : Ochrana osobních údajů/Vždycky ignorovat....

Zkuste v nastavení, ochrana osobních údajů, zadat vždy ignorovat.

Problematika typů u pq je dost složitá. Tak jen krátce.

Pokud přidáte sloupec a deklarujete typ, tak je to deklarace jen na úrovni tabulky. Obsahu jednotlivých buněk netýká se to netýká a pokud nějaký typ požadujete, je to nutné ošetřit ve funkci.

Tohle bude fungovat

let
Zdroj = Table.FromList({1 .. 10}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Přidané: Vlastní" = Table.AddColumn(Zdroj, "Vlastní", each "asg", Int64.Type)
in
#"Přidané: Vlastní"

a sloupec "Vlastní" bude deklarován jako celé číslo.

Jinak řečeno: V tabulce se deklarují "typy" sloupců, ale tyto typy nemusí odpovídat typu jednotlivých buněk.

Navíc během výpočtu na deklarovaném typu moc nezáleží, hlídají se hlavně typy předávané funkcím.

Navíc k přepsání deklarovaného typu dochází v procesu zpracování běžně.

Změna typu je něco jiného, tam občas dochází ke skutečné korekci dat.

Typ má ale význam při interakci s okolím. Tj. v případě exportu tabulky do listu, kt, datového modelu je k typu přihlíženo.

Závěr: Nastavení typu je důležité na konci zpracování. V procesu výpočtu je potřebné jen výjimečně.

ps. Přidávání sloupce nebo oprava sloupce. Pokud má tabulka pár řádků je to jedno. Když má pár (desítek) miliónů řádků , tak to jedno není.

Dokud jsem to používal, tak ano. Teď už to nepotřebuju, pbi to má vestavěné.

Hláška "váš dotaz byl vytvořen v jiné verzi" je celkem běžná, pokud se otevírají starší sešity. Obvykle to není problém.

Co se týká nových verzí, je to samostatná diskuse, občas se chování potichu mění.

Bez problémů funguje najít/nahradit.

Vyberte sloupec tabulky (třeba Sloupec1), klikněte na najít, nahradit.
najít: null, nahradit: 0 je to celkem jedno. Jen je třeba kliknout na nahradit celou buňku.

Kontrola: V kódu je Replacer.ReplaceValue. Pokud ne, tak to opravte.

Dál. Parametry v Table.ReplaceValue mohou být funkce.
Pokud je funkcí hledaná nebo nahrazovaná hodnota je parametrem record odpovídající aktuálnímu řádku - stejné jako v addcolumn, ...)

Tj. co se nahrazuje (vygenerováno by mělo být null) nahraďte funkcí, která vrací hodnotu z prohedávaného sloupce. V tomto případě je to each [Sloupec1]. Sloupec dosaďte vlastní. (Je to funkce. Má generovat hodnotu buňky, která se bude nahrazovat. Jak si ji vytvoříte, taková bude.)

Čím nahradit (vygenerováno by mělo bý 0, jinak 3. parametr)
Funkce by měla být stejná, jako v případném addcolumn.
each [Sloupec1] + [další sloupec].

ps. Možnotí je samozřejmě více. Tuhle považuji za nejjednodušší.

Záleží na verzi.

Fungovala finta. V data,připojení si najděte to, které vytváří vaši tabulku v excelu a vytvořte si na některém listu její kopii.

Pak si tuto novou tabulku načtěte do pq, načtěte nová data, ... kontrola překrývajících se dat, ... a spojte je.

Finta je v tom, že pq nesmí o kopii vědět. Někdy bylo nutno vytvořit i druhou kopii, tu předchozí bylo možné smazat.

Asi nečtete odpovědi. eLCHa už odpověděl.
Rozumné řešení v podobě, kterou požadujete zřejmě neexistuje.

Pokud odmítáte power query i vba, pak zkuste navrhout nějaký jiný postup nebo použít jiný sw, např. některý z mnoha etl nástrojů.

@eLCHa
Na jeden problém je vždy více řešení.

Použil {"YY";"XX"}, i když v tomto případě bych tam nejspíš použil odkaz do tabulky, ve které by byly hledané řetězce. Tj. hledané hodnoty pak lze měnit/doplňovat bez změny vzorce + hledané hodnoty vidím v listu.

To je podle mne hlavní rozdíl proti původně použitému samostatnému testování jednotlivých klíčů.

Mimochodem, v případě, že prohledávaná data jsou velká (toto jistě není takový případ), tak ke zjišťování existence hodnoty používám raději funkce typu ...lookup, ...vyhledat,...

Rozdíl je v tom, že countif vždy prohlídne všechna data, vyhledávací funkce po nalezení shody končí. Data lze také často setřídit, a pak je hledání ještě rychlejší. Vyhledávací funkce také neprovádí konverze dat.

Rychlost závisí na počtu nalezených hodnot a na možnosti setřídit prohledávané pole.

Taky jde

=KDYŽ(SOUČIN.SKALÁRNÍ(--JE.ČISLO(HLEDAT({"YY";"XX"};A2)));"OK";"-")

Nechápu, co tím autor zamýšlel.

Tak jen co to dělá:

COUNTIF($PF$9:PF9;[Barva tyče])

prohledá sloupec PF od začátku až do předchozího řádku a spočítá, kolikrát v něm se vyskytuje každá (asi) barva z [Barva tyče]. Vrátí sloupec v rozsahu [Barva tyče].

Pak výsledek prohledá a vypíše, první barvu z [Barva tyče], která zatím nebyla nalezena.

Text je dobrý, ale...
a) Je náchylný na automatickou změnu typu
b) pokud se při zpracování používají funkce jakou sumif,... a číslo slouží jako klíč, tak i když je číslo uloženo jako text, včetně ', tak tyto funkce automaticky takový text převedou na číslo (včetně zaokrouhlení posledních pozic).

Úplně dobré řešení zřejmě není.

Možnosti:
- číslo vhodně rozdělit na 2 části a ty pak zpracovávat samostatně

- k číslu přidat textový znak (na začátek nebo na konec) - ne ' na začátek.

- zpracovávat v power query - ale to také není úplně bezpečné - "formátovat" jako měnu, nebo používat při výpočtech parametr Precission.Decimal. Stejně se to špatně hlídá. Pokud se s číslem neprovádí žádné výpočty, tak načíst jako text, zpracovat a vrátit. Zase pozor na automatické rozpoznání typu...

Příspěvky uživatele

Uživatelské menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje