Příspěvky uživatele

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

64 bit verzi excelu nepoužívám. Detaily např.:

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ee691831(v=office.14)?redirectedfrom=MSDN

Mimo to jsou hlášeny problémy s uvedenými funkcemi:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/94fbf641-3c9a-46e3-a8a0-bba10829e57d/update-vba-code-to-64-bit-from-32-bit?forum=exceldev

Jinak google doručí dost informací na 1. pokus:

https://www.google.com/search?q=win64+SHBrowseForFolderA+excel+vba

Oprav si deklarace api volání.

Nějak tak (netestoval jsem to):

#If VBA7 Then

Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean

Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As LongPtr
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As LongPtr
lParam As LongPtr
iImage As Long
End Type

Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
#Else
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

#End If

ps. Vždy upozorňuji na to, že tupé kopírování kódu není dobrý nápad.

Pokud to chápu, tak
Pokud je pro nějaké datum předchozí pracovní den dnes, tak vybarvit:

=WORKDAY(DATUM(rok;$B$2;C$2);-1;svátky)=DNES()

(vč. svátků - podle stejné logiky)

pokud máte novější verzi, tak to dělá funkce unique.
Jen je nutno nastavit 3. parametr na pravda.

Oba výsledky jsou správné. Záleží ale na tom, co chcete zjistit. Např. firma má více poboček. PRŮMĚR(D3:D123) spočítá průměrnou úspěšnost poboček,
SUMA(C3:C123)/SUMA(B3:B123) spočítá celkovou úspěšnost firmy.

Rozdíl? 100% úspěšnost u malé pobočky a 50% velké pobočky.

Na celkovém výsledku firmy se více projeví velká pobočka a v závislosti na rozdílu ve velikosti se úspěšnost firmy bude blížit k 50%. Průměrná úspěšnost poboček bude ale 75%.

Také lze použít kontingenční tabulku s datovým modelem.

Potřebná míra je např.:

suma1:=SUMX(DISTINCT(SELECTCOLUMNS(Tabulka1;"číslo 1";Tabulka1[číslo 1];"údaj 1";Tabulka1[údaj 1])); [údaj 1])

Zkuste

= Table.Sort(#"Zmenený typ",(a,b) => Comparer.FromCulture("cs-CZ")(a[Meno], b[Meno]))

Výsledek:

Meno Počet
Ábel 2
Bublina 3
Čistý 1
Zvon 5
Žuvačka 4

(Kulturu nastavte podle potřeby.)

Sestupně:
(a,b) => Comparer.FromCulture("cs-CZ")(b[Meno], a[Meno])

Kultura podle prostředí a "správné" třídění bez ohledu na velikost znaků:

Comparer.FromCulture(Culture.Current, true)

ps.1: Jsou i další způsoby.
ps.2: Ano, je to pravda, dokumentace k PQ stojí za ...

Já bych použil jednodušší vzorec:

=TRANSPOZICE(UNIQUE(FILTER(Data<levá hranatá závorka>B<pravá hranatá závorka>;Data[A]=Kriteria[Kriteria])))

ps.
Správná syntaxe nejde vložit

druhá část.

(dotaz na ladění. Funkce je v podstatě stejná.)

Možná se někomu něco může hodit.
Platí, že vše se dá dělat mnoha způsoby.

let
FileName = Excel.CurrentWorkbook(){[Name="tblDirPath"]}[Content]{0}[Directory path] & "\AUT_0032_32211010.TXT",

// Načteme po řádcich a rozdělíme na části
// Lines.FromBinary načte soubor "po řádcích" do seznamu
// Hodí se to při práci s podobnými soubory

File = Lines.FromBinary(File.Contents(FileName), QuoteStyle.None, false, 65001 ),

// Začátky a konce jednotlivých částí
// Připravíme si seznam s počátky oddílů a koncem souboru

#"Oddíl" = List.Generate(
() => [L = {"=", "Sterilisation", "Date", "End of cycle"}, // Texty, které jsou na začátku jednotlivých oddílů (parametry, data, konec souboru)
i=0, // proměnná cyklu
r = 0], // číslo řádku
each <= List.Count([L]), // podmínka pro pokračování cyklu
each [ // Funkce, která realizuje tělo cyklu
L = [L], // Zachová hledané texty
r = List.PositionOf(File, List.FindText(File,[L]{}){0}?), // Zkusí text najít.
i = + 1 // posune řídící proměnnou cyklu
],
each if [r] >= 0 then [r] else List.Count(File) // Vrácená hodnota kroku. Pokud je r < 0, vrátí poslední řádek
),

[code]
// Funkce vrátí seznam {0, 0, 3, 22, 708}

// Připravíme funkci na vybrání odpovídajících řádků oddílu
// Funkce načte jen data, bez hlavičky

DataOddilu = (o) => List.Range(File, #"Oddíl"{o}+1, #"Oddíl"{o+1} - #"Oddíl"{o} - 1),

// Načteme si sloupce ve správném pořadí
// V podstatě zbytečné

Hlavicka = Sloupce[Zkratka],

// -----------------------------------------------------------------------------------------
// první část - batch

Batch = [Batch = Text.AfterDelimiter( File{Oddíl{1}}, "_", {0, RelativePosition.FromEnd})],

// Funkce Table.FromList vyžaduje funkci, která vrátí seznam s buňkou
// each je jen jiný zápis pro deklaraci funkce (_) =>

ParametryData = Table.FromList(DataOddilu(2), each {_}, {"Param"}),

// funkce Splitter.SplitTextByWhitespace při dělení textu bere více mezer za sebou jako jednoho dělitele

#"Rozdělit parametry" = Table.SplitColumn(ParametryData, "Param", Splitter.SplitTextByWhitespace(QuoteStyle.Csv), {"Param.1", "Param", "Value"}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Rozdělit parametry",{"Param.1"}),
#"Změněný typ s národním prostředím" = Table.TransformColumnTypes(#"Odebrané sloupce", {{"Value", type number}}, "en-150"),
#"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(
Table.Transpose(#"Změněný typ s národním prostředím"), [PromoteAllScalars=true]),

// Table.SelectColumns vybere sloupce, které jsou v seznamu Hlavicka a seřadí je.
// V seznamu Hlavička jsou ale další sloupce. Paremetr MissingField.Ignore zajistí, že tyto sloupce budou ignorovné

Parametry = Table.First(Table.SelectColumns(#"Záhlaví se zvýšenou úrovní",Hlavicka, MissingField.Ignore)),

// -----------------------------------------------------------------------------------------
// třetí část
// data mají pevnou pozici

// vybereme data ze 3. oddílu, spojíme je do souvislého textu a předáme funkci Csv.Document ke zpracování.
// Předpokládám pevnou strukturu, data načtem od 2. pozice

FileData = Csv.Document(Lines.ToText(DataOddilu(3)),13,{2,12,21,26,33,39,45,51,57,62,69,75,81},ExtraValues.Ignore,65001),

// Pojmenujeme sloupce

// Výraz List.RemoveNulls(Text.Split(File{#"Oddíl"{3}}, " "))

// Text.Split(File{#"Oddíl"{3}} rozdělí řádek s hlavičkou v načtených datech v každé mezeře a vrátí seznam
// List.RemoveNulls( .. ) tento seznam vyčistí od prázdných znaků, zůstanou nám je jména sloupců
// Funkce List.Zip spojí dva (a více) seznamy do seznamu seznamů, které potřebuje funkce Table.RenameColumns

// a dostaneme
// { {"Column1", "Date"}, {"Column2", "Hour"}, ....}

#"Přejmenované sloupce" = Table.RenameColumns(FileData,
List.Zip(
{
Table.ColumnNames(FileData),
List.RemoveNulls(Text.Split(File{#"Oddíl"{3}}, " "))
})),

#"Odebrané sloupce1" = Table.RemoveColumns(#"Přejmenované sloupce",{"PT04(C)", "PT05(C)", "Operator", "PR01(kPa)", "PT01(C)", "PT03(C)", "Circulator(ON/OFF)"}),
OdebranéPrázdnéŘádky = Table.SelectRows(#"Odebrané sloupce1", each ([Date] <> "")),

// Některé příkazy mění pořadí a z dat původní pořadí nejde určit, přidáme index

#"Přidané: Index" = Table.AddIndexColumn(OdebranéPrázdnéŘádky, "Index", 0, 1, Int64.Type),
DateHour = Table.CombineColumns(#"Přidané: Index",{"Date", "Hour"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateHour"),

// Vyčistíme mezery
// Phase - mohou být jen na začátku a na konci.
// Ostatní čísla - jako o pár řádků výše. Rozdělíme podle mezer, odstraníme null prvky ze seznamu a výsledek zase spojíme
// do textu bez mezer

#"Vyčištěný text" = Table.TransformColumns(DateHour,{
{"Phase", Text.Trim, type text},
{"PR03(kPa)", each Text.Combine(List.RemoveNulls(Text.Split(_, " ")),"")},
{"PT02(C)", each Text.Combine(List.RemoveNulls(Text.Split(_, " ")),"")},
{"Humidity(%)", each Text.Combine(List.RemoveNulls(Text.Split(_, " ")),"")}
}),
#"Změna typu - DateHour" = Table.TransformColumnTypes(#"Vyčištěný text",{{"DateHour", type datetime}}),
Data = Table.TransformColumnTypes(#"Změna typu - DateHour", {{"Phase", type text}, {"PR03(kPa)", type number}, {"PT02(C)", type number}, {"Humidity(%)", type number}}, "en-US"),

// Dále budeme potřebovat souhrny za phase a první řádek každé phase
// Data seskupíme a vyberemo potřebná. V menu Seskupit podle do sloupce Táb zvolíme vybrat všechny řádky.
// Pak si vybereme seskupení podle přání (min, max, průměr, ...)
// Vygenerovaná funkce Table.Group seskupuje podle seznamu, kde je vždy
// jméno výsledného sloupce
// Funkce, která počítá souhrn
// Typ sloupce

// Parametrem funkce počítající souhrn je TABULKA obsahující všechny řádky příslušné skupiny.
// Pokud tedy chceme získat první řádek skupiny stačí vybrat řádek s nejmenší hodnotou indexu.

#"Seskupené řádky" = Table.Group(Data, {"Phase"}, {
{"Tab",
each Table.SelectRows(_, (r) => r[Index] = List.Min([Index])),
type table [DateHour=nullable datetime, Phase=nullable text, #"PR03(kPa)"=nullable number, #"PT02(C)"=nullable number, #"Humidity(%)"=nullable number, Index=number]
},
{"Počet řádků",
each Table.RowCount(_),
Int64.Type},
// Tady přidáme průměry, ...
{"PT02 (Max)",
each List.Max([#"PT02(C)"]),
type number},
{"PT02 (Min)",
each List.Min([#"PT02(C)"]),
type number},
{"PT02 (Prům)",
each List.Average([#"PT02(C)"]),
type number}
}),
#"Rozbalené Tab" = Table.ExpandTableColumn(#"Seskupené řádky", "Tab", {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}, {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}),

// Nalezenou tabulku vložíme do bufferu. Zrychlí to přístup k datům. Nejspíš to v tomto objemu dat není nutné.

GroupData = Table.Buffer(Table.AddKey(#"Rozbalené Tab", {"Phase"}, true)),

// Máme připravené souhrny
// teď výpočty - připravíme funkce
// Výpočty mohou být umístěny přímo do recordu.
// Jen mi to připadlo o něco přehlenější a umožňuje pojmenovat základní hodnoty a ty pak skládat do rozdílů
// Parametrem funkcí je seskupená tabulka GroupData, zase mi použití parametru místo celého jména připadlo čitelnější
//
// Funkce jednoduše vybírají hodnoty z tabulky
// Výraz _{[Phase="2.9"]}? vybere z tabulky řádek, ve kterém je ve sloupci Phase hodnota "2.9". Pokud takový neexistuje, neohlásí chybu, ale vrátí null
// Výraz _[#"PR03(kPa)"]? vybere sloupec tabulky nebo pole recordu se jménem "PR03(kPa)", Otazník zajistí v případě, že sloupec/pole neexistuje, vrácení hodnoty null
//

D1 = (_) => _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D5 = (_) => _{[Phase="3.9"]}?[#"PR03(kPa)"]?, // ? asi an
D8 = (_) => _{[Phase="4.9"]}?[#"PR03(kPa)"]?, // Co tam má být? 4.9 nebo 2.1
D11 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]?,
D13 = (_) => _{[Phase="8.9"]}?[#"PR03(kPa)"]?,

D3 = (_) => _{[Phase="3.9"]}?[#"Humidity(%)"]?,
D6 = (_) => _{[Phase="3.9"]}?[#"PT02(C)"]?, // Co tam má být? 2.9 nebo 3.9

D7 = (_) => _{[Phase="3.9"]}?[#"PR03(kPa)"]? - _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
// nebo lze:
// D7 = (_) => D5(_) - D1(_),
D12 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]? - _{[Phase="4.9"]}?[#"PR03(kPa)"]?,

D2 = (_) => _{[Phase="2.9"]}?[#"DateHour"]? - _{[Phase="2.1"]}?[#"DateHour"]?,
D4 = (_) => _{[Phase="3.9"]}?[#"DateHour"]? - _{[Phase="2.9"]}?[#"DateHour"]?,
D9 = (_) => _{[Phase="4.9"]}?[#"DateHour"]? - _{[Phase="3.9"]}?[#"DateHour"]?,
D10 = (_) => _{[Phase="6.9"]}?[#"DateHour"]? - _{[Phase="4.9"]}?[#"DateHour"]?,

D14 = (_) => _{[Phase="10.9"]}?[#"Počet řádků"]?,
// přidaný vzorec pro průměr, ...
D15 = (_) => _{[Phase="6.9"]}?[#"PT02 (Prům)"]?,
D16 = (_) => _{[Phase="6.9"]}?[#"PT02 (Max)"]?,
D17 = (_) => _{[Phase="6.9"]}?[#"PT02 (Min)"]?,

// Oddělení vzorce a skládání výstupu mi připadalo přehlednější
// Je to ale věc názoru
// složíme record s vypočtenými hodnotami

CalculatedData =
[D1 = D1(GroupData)] &
[D2 = #time(0,0,0) + D2(GroupData)] & // převod na time
// (rozdíl časových údajů má typ duration.
// Proto k času 0 připočteme dobu trvání události a dostame zase time
[D3 = D3(GroupData)] &
[D4 = #time(0,0,0) + D4(GroupData)] & // převod na time
[D5 = D5(GroupData)] &
[D6 = D6(GroupData)] &
[D7 = D7(GroupData)] &
[D8 = D8(GroupData)] &
[D9 = #time(0,0,0) + D9(GroupData)] & // převod na time
[D10 = #time(0,0,0) + D10(GroupData)] & // převod na time
[D11 = D11(GroupData)] &
[D12 = D12(GroupData)] &
[D13 = D13(GroupData)] &
[D14 = D14(GroupData)]&
[D15 = D15(GroupData)]&
[D16 = D16(GroupData)]&
[D17 = D17(GroupData)]
,

// spojíme záznamy

CombineTables = Table.FromRecords({Batch & Parametry & CalculatedData}),

// Pro jistotu seřadíme sloupce a pokud by některý chyběl, tak ho doplníme (s hodnotou null)

#"Seřazení sloupců" = Table.SelectColumns(CombineTables, Hlavicka, MissingField.UseNull),

// Nastavení typu sloupců
// výraz
// Table.ToRows(Sloupce) tabulku Sloupce převede na seznam jehož prvky jsou řádky převedené na seznam
//
// Výraz
// List.Transform(Table.ToRows(Sloupce), each {""&_{0}&"", if _{2} = "text" then type text else if _{2} = "time" then type time else type number }))
// tento seznam převede na jiný seznam:
// {{"Batch", type text}, {....}}
// Typ je zadán ve 3. sloupci v tabulce Sloupce
// Výsledek transformace je použit k nastavení typu sloupců

#"Zmenený typ" = Table.TransformColumnTypes(#"Seřazení sloupců",
List.Transform(Table.ToRows(Sloupce), each {""&_{0}&"", if _{2} = "text" then type text else if _{2} = "time" then type time else type number }))
in
#"Zmenený typ"
[/code]

Co to dělá

GetDataFromFiles:
let

// Načteme adresář a vybereme textové soubory

Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblDirPath"]}[Content]{0}[Directory path]),
#"Filtrované riadky" = Table.SelectRows(Zdroj, each [Extension]=".TXT"),

// Ve sloupci "Content" je binárně obsah načtených souborů
// Zbytek nepotřebujeme (pokud nechceme ponechat datum změny...)

#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Filtrované riadky",{"Content"}),

// Obsah jednotlivých buněk sloupce "Content" transformujeme funkcí fncGetDataFromFile
// pamatujeme, že v každé buňce je binární obsah jednoho souboru
// tento kód vygenerovalo PQ. Proti běžným postupům využívá volitelného parametru, který umožňuje zadat společnou transformaci
// pro všechny sloupce tabulky neuvedené v předchozím seznamu (ten je tady prázdný)

#"Transformovaný sloupec" = Table.TransformColumns(#"Odebrané ostatní sloupce",{},fncGetDataFromFile),

// Pokud jsou v buňkách sloupce tabulky, je sloupci přiřazen typ tabulka ale s konkrétním typem "any",
// tj. s původními typy sloupců se tady nepočítá a typ bude nutné obnovit.
// Lze použít postup na konci funkce fncGetDataFromFile
// Někde jsem kdysi zahlédl zajímavou ideu jak na to jinak
// Funkce Table.TransformColumns umožňuje zadat typ sloupce po transformaci
// Tady transformační funkce vrací původní obsah buňky
// a deklaruje typ sloupce podle tabulky v prvním řádku sloupce.

#"Obnovíme typ" =
Table.TransformColumns(#"Transformovaný sloupec",
{"Content",
each {_}{0},
Value.Type(Table.Column(#"Transformovaný sloupec", "Content"){0})
}),

// Expandujeme sloupec "Content" s tabulkami.
// normálně to generuje kód

// = Table.ExpandTableColumn(#"Obnovíme typ", "Content", {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D17"}, {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D17"})

// nevýhodou je, že pokud se přidá další sloupec, tak se nerozbalí
// Proto je v sešitu tabulka sloupce, ve které je uveden krátký název, název pro report a typ.
// pokud se přidá nová sloupec, stačí ho doplnit v listu excelu.

#"Rozbalené Content" = Table.ExpandTableColumn(#"Obnovíme typ", "Content", Sloupce[Zkratka]),

// Stejnou tabulku použijeme pro přejmenování

#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Content", List.Transform(Table.ToRows(Sloupce), each {_{0}, _{1}} ), MissingField.UseNull)

// Poslední dva příkazy bylo možné zapsat:
//= Table.ExpandTableColumn(#"Obnovíme typ", "Content", Sloupce[Zkratka], Sloupce[Jméno])

in
#"Přejmenované sloupce"

Jak se to píše:

Klikání neumožní vytvářet flexibilní kód. Na druhé straně psát dlouhé konstrukce je taky otrava => základ naklikám a pak ručně doladím.

Na popis funkce potřebuji víc času.

Příspěvky uživatele

Uživatelské menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje