• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

Příspěvky uživatele

Taky jde použít průměr:

=PRŮMĚR(FILTER(A4:A2884;--(A4:A2884>0,5)*(--(B4:B2884=E2) + (B4:B2884=F2))*(C4:C2884=G2)))

Pokud tedy máte novější verzi a nechcete pokaždé tabulku řadit, lze použít třeba:

=INDEX(SORT(FILTER(D2:G7;D2:D7=E11);4;-1);1;{3\2})

Matice jsou automatické. Předpokladem je datum-čas ve sloupci G (stačí ve sloupci G nahradit "-" za nic).

V čem to chcete dělat? (verze)

Jen text:

countif(oblast;"*")

2.jak označit v makru více řádků, když v nich kdekoliv mám označeno více buňek.


'zapamatuju si vybrané buňky
dim vybrano as range
Set vybrano = Selection

' vybarvim řádek, kde je nějaká vybraná buňka
vybrano.EntireRow.Interior.Color = vbRed


kopírovat celé tyto řádky/2,5,7,10/, nebo třeba do jiného listu

Dim i As Long
Dim area As Range
Dim list2 As Worksheet

'každý řádek zkopíruje do List2
set list2 = Worksheets("List2")
i = 1
For Each area In vybrano.EntireRow.Areas
With area
list2.Cells(i, 1).Resize(.Rows.CountLarge, .Columns.CountLarge).Value = .Value
i = i + .Rows.CountLarge
End With
Next area

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.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse