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

Příspěvky uživatele

@elninoslov díky za překlad.

Kdyby hans66 zmínil verzi, tak možná ty vzorce by byly výrazně jednosušší.

Je to běžná úloha. Power query pro ni není vhodné. Lepší je datový model (power pivot).

Popis je hodně obecný, a ne moc srozumitelný, z toho moc nevymyslím. Tak k obecnému dotazu aspoň pár obecných poznámek:

Kód akce v tabulce deník s relacemi do dvou tabulek není dobrý nápad (jde to, ale je to nepřehledné).

Relací M:N se zbavte. Buď lepším lepším návrhem datového modelu nebo pomocnou tabulku (můstkem), kde budou unikátní kódy akce (to se dá pomocí power query). Pak půjde nastavit relace.

nebo:
=IFERROR(SVYHLEDAT(C4-B4;$E$4:$G$10;3)*A4;0)
Pokud by vadily hodnoty na 14 - 15 des. místě, je nutné se dohodnout na způsobu vyhodnocení (např korekce o 1e-14, zaokrouhlení na 2 des. místa, ...)

např.:
=IFERROR(INDEX($G$4:$G$10;POZVYHLEDAT(ZAOKROUHLIT(C4-B4;3);ZAOKROUHLIT($E$4:$E$10;3)))*A4;0)
(maticově)

Co používáš???? Není excel jako excel.
Jak ti tam ty data tečou (je výsledek tabulka?????)

Pokud máš něco z posledního roku, a data jsou v tabulce, stačí na listu dát do buňky na listu 2 odkaz na sloupec tabulky. (Do jedné buňky a vybrat sloupec!!! pak enter) Někdy se po importu místo tabulky deklaruje pojmenovaná oblast. Pak místo na tabulku se odkaž na tuto oblast a třeba pomocí funkce index vyber potřebný sloupec (stačí v jedné buňce, excel data "rozleje" podle potřeby sám.).

Pokud tam tabulka není, jsou další možnosti. Např. lze použít funkci filter (a tím i odebrat případné nuly).

Často je také možné připojení směrovat do více oblastí (na oba listy).

Bez informací můžeš používat tak akorát postupy staré cca 15 let.

Přesun do posledního použitého sloupce v listu: end enter

Odstranění: shift + end enter
pravý klik, odstranit sloupce

PQ má podobné problémy s mapováním adresy.

Těch "onedrive..." má MS víc podle typu onedrivu (soukromý, korporátní, ...). Obecné řešení bude nejspíš komplikovanější. Pár řešení je na https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive. Něco jsem testoval, je tam dobrý základ.

Jak naznačil elnino, v džungli MS je složité lokálně najít obecnější řešení.

excel pro adresu souboru podsunuje url úložiště místo lokální adresy. Toto by mělo jít vypnout zákazem synchronizace onedrivu z prostředí mso: ikona onedrive - nastavení - office - "soubory, které otevřu, ...". Nezkoušel jsem to, nerad používám řešení závislá na lokálním nastavení (https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-use-the-physical-addres-of-a-workbook/8d4ba24c-f2aa-4118-81b8-47e4e35d400c)

Tohle je pro powerquery:

Nástřel dotazu:


// dotaz
let
Zdroj = Excel.CurrentWorkbook(){[Name="data"]}[Content],
#"Seskupené řádky" = Table.Group(Zdroj, {"Column1"}, {{"Tab", each _, type table}}, GroupKind.Local,(x,y)=>if x[Column1] = "Material" and y[Column1] = null then 0 else 1),
#"Filtrované řádky" = Table.SelectRows(#"Seskupené řádky", each ([Column1] = "Material")),
konverze = (Tab as table) =>
let
Material = Table.SelectRows(Tab, each ([Column1] = "Material")),
Vlastní1 = Table.RemoveColumns(Material, List.Select(Table.ColumnNames(Material), each List.NonNullCount(Table.Column(Material, _)) = 0)),
#"Odebrané sloupce" = Table.RemoveColumns(Vlastní1,{"Column1", "Column9"}),
#"Leva strana" = Table.RenameColumns(#"Odebrané sloupce",List.Zip({Table.ColumnNames(#"Odebrané sloupce"), List.Transform({1 .. 4}, each "Sl. " & Text.From(_) ) } ) ),
Data = Table.SelectRows(Tab, each ([Column1] = null)),
#"Odebrané prázdné sloupce" = Table.RemoveColumns(Data, List.Select(Table.ColumnNames(Data), each List.NonNullCount(Table.Column(Data, _)) = 0)),
#"Odebrané prázdné řádky" = Table.SelectRows(#"Odebrané prázdné sloupce", each ([Column2] <> null)),
#"Prava strana" = Table.RenameColumns(#"Odebrané prázdné řádky",List.Zip({Table.ColumnNames(#"Odebrané prázdné řádky"), List.Transform({5 .. 10}, each "Sl. " & Text.From(_) ) } ) ),
#"Přidané: Vlastní" = Table.AddColumn(#"Leva strana", "Vlastní", each #"Prava strana"),
#"Rozbalené Vlastní" = Table.ExpandTableColumn(#"Přidané: Vlastní", "Vlastní", Table.ColumnNames(#"Přidané: Vlastní"[Vlastní]{0}))
in
#"Rozbalené Vlastní",

KonverzeTabulky = Table.TransformColumns(#"Filtrované řádky", {"Tab", konverze} ),
#"Rozbalené Tab" = Table.ExpandTableColumn(KonverzeTabulky, "Tab", Table.ColumnNames(KonverzeTabulky[Tab]{0})),
#"Odebrané sloupce" = Table.RemoveColumns(#"Rozbalené Tab",{"Column1"})
in #"Odebrané sloupce"


Poznámky:

Dotaz čte data z oblasti "Data". (Pokud by se četlo z jiného sešitu, lze číst list).

Table.Group vyhodnocuje data lokálně, to umožňuje rozdělit tabulku na oblasti se stejným materiálem.

Funkce pro konverzi je deklarována v dotazu. (Podobné funkce je většinou snažší napřed naklikat pro jeden řádek, pak změnit na funkci.)

Doplňte si vhodná jména sloupců.

Použijte vlastnosti dokumentu. (je to stejné i v excelu, powerpointu, ...)

Soubor/informace/vlastnosti/upřesnit vlastnosti

Vyberte vhodné k použití, případně vytvořte vlastní. Přes propojení do ovládacích prvků se dají aktualizovat i z dokumentu.

Potom není nutné žádné vyhledávaní, struktura souboru pro tento účel není důležitá, dá se snadno vyhledávat i z průzkumníka ... Makro si jednoduše "sahne" do vlastností a vypíše potřebné.

Stačí

=SUMA(SOUČIN.MATIC(--(A2:E5="M");TRANSPOZICE(A1:E1)))

Poznámku o relacích M:N v příspěvku jsem zaznamenal.

Tak ještě jednou možnosti:

a) Upravit strukturu dat (třeba v PQ) a relací M:N se zbavit.

b) Nebo použít virtuální relace pomocí měřítek. Tento přístup je univerzální, jen trochu pracnější. Umožní zohlednit více vazeb. Příklad je uveden výše.

c) A/nebo použít funkci CROSSFILTER k vytvoření relace M:N. V excelu (na rozdíl od jiných implementací) nelze takovou relaci do datového modelu zadat.
Funkce ale umožňuje takovou relaci ve vzorci vytvořit. Je to v běžných případech často jednodušší, než vytváření univerzálního filtru.

Vše se týká datového modelu a výstupu do KT.

Má to snad být něco takového:

=SUMA(List1:List5!A1)

???

Jinak, pokud funkce nevyžaduje odkaz na pole, lze rozsahy i z různých listů skládat do jednoho pole.

např.
=ZVOLIT({1\2}; List2!C1:C6;List3!C1:C6)

složí dva sloupce vedle sebe do jednoho pole.

Možná se dá také použít KT nebo vytvářet souhrn pomocí powerquery.

"skvělý příklad"...
Ty tabulky mají pro spojení je pole Id a ani jednu společnou hodnotu.

Tak tedy obecně.

1. Tabulky převést na tabulky
2. vytvořit kt z jedné tabulky, zatrhhnout "přidat do datového modelu.
3. na data z následujících tabulek je možné se potom odkazovat pomocí virtuálních relací.

Např. pokud budu předpokládat, že pole Id tabulky spojuje, pak např. na prodej se lze dotázat měřítkem:


prodej_rel:=var akt_id = distinct(Tabulka1[id])
return calculate(sum(Tabulka2[prodej]);CONTAINSROW(akt_id;Tabulka2[id] ))

Vhodnější je (např. pomocí power query) dát tabulkám aspoň náznak struktury databáze.

Nechce se mi to zkoumat. Makro je zbytečné.

Podmíněný formát:

Vzorec: =max(C2:C4)>1
formát: červené pozadí

Spíš bych přidal druhý sloupec "datum" a vložil bych filtr "časovou osu". (analýza KT, filtr, vložit časovou osu).
(první datum se v kt automaticky převádí na text, proto ta kopie)

Add manipulace s časem v ose - makro, převést čas na desetinné číslo a vložit do osy.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse