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

Příspěvky uživatele

1) je třeba načíst data do datového modelu (včetně správného typu, pokud jsou čísla formátovaná jako text, tak vzorce nebudou fungovat)
2) vytvořit měřítka - sumy (lze použít autosum):
Sum of km AP:=SUM([km AP])
Sum of used litres:=SUM([used litres])
3) Vytvořit měřítko s výpočtem průměrné spotřeby, např.:
prům. spotřeba:=divide([Sum of used litres];[Sum of km AP])
Vytvořit kont. tabulku.

Funkce REDUCE vloží obsah prvního parametru do akumulátoru a potom postupně prochází hodnoty ve druhém parametru.

V každém kroku volá funkci lambda s parametry a = aktuální hodnoty akumulátoru a n = aktuální hodnota z druhého parametru.

Výsledek volání funkce lambda v každém kroku vloží do akumulátoru.

Po vyčerpání všech proměnných vrátí hodnotu akumulátoru.

Detaily viz nápověda.

Nejlepší 3 ženy:
=VZÍT(SORT(FILTER(ZVOLITSLOUPCE($A$2:$B$16;2;1);$C$2:$C$16="Z");2;-1);3)

Poslední 3 ženy:
=VZÍT(SORT(FILTER(ZVOLITSLOUPCE($A$2:$B$16;2;1);$C$2:$C$16="Z");2;1);3)

Neuvedl jste, co máte.

V A1 je text, v A5:A10 jsou povolené znaky. Vzorec vymaže z textu vše, co je v seznamu.

=REDUCE(A1;A5:A10;LAMBDA(a;n;DOSADIT(a; n; "")))

Ve slovenském excelu se funkce DOSADIT jmenuje SUBSTITUTE.

Pokud nejsou na výstupu potřebné původní hodnoty, tak lze použít nahrazení hodnot:

// all_replace
let
Zdroj = Table.Combine({Data2022, Data2023}),
Table.AddColumn(Zdroj, "Rate", each Table.Column(datarate, "rate")),
#"Extrahované hodnoty" = Table.TransformColumns(#"Přidané: Vlastní", {"Rate", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Změněný typ" = Table.TransformColumnTypes(#"Extrahované hodnoty",{{"Rate", type number}}),
#"Vložené: Sloučený sloupec" = Table.AddColumn(#"Změněný typ", "Period ", each Text.Combine({[Y], [M]}, "-"), type text),
#"Nahrazená hodnota" = Table.ReplaceValue(#"Vložené: Sloučený sloupec",null, null, (x, y, z) as number => x / rate,{"gross salary", "social and health insurance", "net salary", "travel allowance", "allowance", "total company costs", "total net for driver"})
in
#"Nahrazená hodnota"

// rate
let
Zdroj = Excel.CurrentWorkbook(){[Name="datarate"]}[Content][rate]?{0}?
in
Zdroj

Obecně:

Je to pár čísel, na to aby to bylo pomalé.

Vhodnější je poslat tabulku do datového modelu, různé sloupce typu měsíc, rok, ... jsou v tabulce zbytečné. V datovém modelu stačí kliknout na kalendář, vytvořit tabulku a vytvoří se automaticky, ten se spojí relací s původní tabulkou (all).

Pro konverzi je lepší vytvořit samostatné míry.

Efekt: Méně dat, rychlejší aktualizace, flexibilnější výstup.

Pokud chcete sloupce s konverzí přidat, mělo by být rychlejší:

let
Zdroj = Table.Combine({Data2022, Data2023}),
#"Přidané: Vlastní" = Table.AddColumn(Zdroj, "Rate", each rate, type number),
#"Přidané: Vlastní1" = Table.AddColumn(#"Přidané: Vlastní", "Vlastní", each let
rec = [[gross salary],[social and health insurance],[net salary],[travel allowance],[allowance],[total company costs],[total net for driver]],
rec.names = Record.FieldNames(rec),
rec_rate = Record.TransformFields(rec, List.Transform(rec.names, each { _, (f) => f / rate})),
vysl = Record.RenameFields(rec_rate, List.Transform(rec.names, each {_, _ & " EUR"}))
in vysl),
#"Rozbalené Vlastní" = Table.ExpandRecordColumn(#"Přidané: Vlastní1", "Vlastní", Record.FieldNames(#"Přidané: Vlastní1"[Vlastní]{0})),
#"Vložené: Sloučený sloupec" = Table.AddColumn(#"Rozbalené Vlastní", "Period ", each Text.Combine({[Y], [M]}, "-"), type text)
in
#"Vložené: Sloučený sloupec"

Pokud je dotaz na pozici vybrané buňky v tabulce, tak stačí

On Error GoTo nic
With Selection
If .ListObject.Name = "Tabulka" Then MsgBox .Row - .ListObject.Range.Row
End With
nic:

Třeba:

=SUMIFS(XLOOKUP(H2;PROČISTIT(D1:F1);D2:F38);A2:A38;">="&I2;A2:A38;"<="&J2)

Obecně.

*IF* funkce jsou rychlejší, než jejich maticové ekvivalenty.

Pokud máte XLOOKUP, tak kombinaci INDEX/POZVYHLEDAT nepotřebujete (má méně možností a je pomalejší).

Pokud potřebujete hodně různých vzorců (neuvádíte jestli se jedná jen o počet nebo o různé výpočty), tak to řešte v datovém modelu.

Protože buňka hodnotu má: "" a číslo je menší než text

Pokud berete barvu podle podmíněného formátu, pak je vhodnější použít vlastnost Range.DisplayFormat:

FarbaNew = .Cells(R + 1, 2).DisplayFormat.Interior.Color

Tedy pokud je v buňce opravdu podmíněný formát, a ne přebarvení makrem.

@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))

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čí.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse