Příspěvky uživatele

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

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]

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje