Příspěvky uživatele

Asi nejsnažší je začít s makrem ve vba v aktualizovaném souboru, které zajistí načtení tabulky z oracle i aktualizaci všech navazujících struktur. Jeho spuštění je vhodné podmínit "správným" uživatelem (strojem, ...). bat (v čemkoliv) zajistí spolupráci s plánovačem úloh vč. nastavení prostředí.

Problémy:
Při aktualizaci v excelu řada kroků probíhá asynchronně, občas se něco předbíhá (typicky aktualizace kt předbíhá čtení dat, ...). To je nutné v makru zohlednit - např. zakázat během aktualizace operace na pozadí, ne vždy to jde.

Velký počet požadavků na aktualizaci tabulek může vyvolat nahodilé chyby (excel prostě spadne).

Rychlost :
Načítání dat z oracle je hodně pomalé (mám pocit, že to ms dělá schválně). Při větším objemu dat je někdy rychlejší vygenerovat csv soubor data čerpat z něj.
Ale v takovém případě je nutné nějak synchronizovat samostatně běžící úlohy.

Počet paralelních aktualizací souborů je často omezením.

Je také třeba myslet na administraci:
Tady je vhodný samostatný systém (tabulka, db, ..) db je asi nejlepší - umožňuje nejlépe monitorovat průběh pro více uživatelů.

Sledovat:
Stav aktualizace souborů z pohledu správce, hlášení o chybách (pád, chybějící data, nevalidní data po aktualizaci, ...), statistika aktualizací (doba trvání aktualizace jednotlivých souborů, počet paralelních úloh, ... - určité pomůže při plánování aktualizací.

Pro uživatele - info o úspěšné aktualizaci, řešení neúspěšné aktualizace (např. ponechání původního souboru + info)

Musí to běžet automaticky. Informace o chybách by se neměly v případě pádu ztratit.

Nepopsal jste ekosystém. Co máte k dispozici, co máte hotovo, objem dat, počet tabulek, proč data nenačitate přímo z db, kolik uživatelů to bude spouštět, z jakého prostředí,...

Tohle řešit přes diskusi je hodně složité - více systémů, často složitá synchronizace kroku...

Zpracování pár tisíc řádků by ve vba nemělo trvat víc jak několik vteřin.

Součástí maker je kumulativní aktualizace listu. To se v power query nedělá snadno, bylo by nutné spojovat dva světy. Úprava maker by nemusela být až tak složitá.

Hlavní? problémy maker, pár příkladů.

1. Zbytečný a časově drahý kód
* filtr čísel ve sloupci K - proč to neudělat v sql dotazu??? Dotaz může data také seřadit.
* sub OznacKArchivaci je zbytečná. Řádky už označeny jsou - v "O" je "Uzavřeno"
* Používání metody Aktivate a Select je v naprosté většině případů zbytečné (a pomalé)
* Nesmyslný kód. Ve funkci Najdi:
For Each Bunka In Selection
jmeno = Bunka.Value 'uloz do promene
bun = Bunka.Address
Next Bunka
Tenhle kód si zapamatuje adresu a obsah poslední buňky ve výběru. Smysl nechápu.
* zase v Najdi:
Vyhoď Application.ScreenUpdating = True
Tady to povolí zobrazení po každém hledání...
Zkontroluj i ostatní funkce. To má být jen tak, kde je to opravdu nutné (na konci obsluhy tlačítka).

2. Pomalé operace
* identifikace objektu "proléza" pokaždé celý strom objektů (pomalé). Místo:
ThisWorkbook.Worksheets("souhrn").Activate
For i = Range ...
If Cells(i, 1) ...
používej With např.:
With ThisWorkbook.Worksheets("souhrn")
For i = .Range("B"...
If .Cells(i, 1 ...
Mimochodem, i takový kód je také dost pomalý.
* procházení buněk pomocí Cell.Offset(1, 0).Select - použij index řádku, nebo metodu .Find, nebo ještě lépe nahraj data sloupce do pole a pak hledej v tomto poli (při dobrém kódu to je v nejhorším případě stejně rychlé, jako Find),

3. Pomalé algoritmy.
* Mazaní, kopírování - doba málo závisí na velikosti bloku, ale hodně na jejich počtu -> je výhodné seskupit archivované řádky do jednoho bloku -> setřídit tabulku tak, aby mazané/kopírované řádky byly v co nejmenším počtu bloků a ty pak vyšejit jediným příkazem.
* Nesmyslný kód:
Set Naj = Sheets("souhrn").Range(sadre).Find(What:=jmeno, SearchOrder:=xlByRows) 'hledá označené HNV
P001 = Sheets("souhrn").Range(Naj.Address).Offset(0, 1).Value 'vytvořeno
P002 = Sheets("souhrn").Range(Naj.Address).Offset(0, 17).Value 'množství

Raději?
Set Naj = Sheets("souhrn").Range(sadre).Find(What:=jmeno, SearchOrder:=xlByRows) 'hledá označené HNV
' + ošetření chyby
With Naj
P001 = .Offset(0, 1).Value 'vytvořeno
P002 = .Offset(0, 17).Value 'množství

nebo ještě rychleji
Dim data() As Variant
' hledej HNV
Set Naj = Sheets("souhrn").Range(sadre).Find(What:=jmeno, SearchOrder:=xlByRows) 'hledá označené HNV
If Naj Is Nothing Then GoTo Konec ' Konec najdi.
data = Naj.Resize(1, 68).Value
P001 = data(1, 2) 'vytvořeno
P002 = data(1, 18) 'množství

1. Pokud vzorec nefunguje po přidání 2. funkce index, tak je chyba tam.

2. AND(něco; něco jiné;...; 0) je buď CHYBA nebo NEPRAVDA

3. Když tvoříš podobné obludy, tak je aspoň slušně formátuj. (řádky/odsazení)

Použijte kontingenční tabulku a seskupte řádky podle roku a měsíce. Excel to udělá automaticky.

Zkuste zaguglit, třeba

https://www.google.com/search?q=longest+common+substring+problem+vba

A něco či vyberte.

Tak jak to popisujete, je to dost obecné. Podobné počítání s velkým objemem dat podmíněnému formátu moc nesvědčí. Vzorec by možná šel, ale při obecném zadání vznikne zase obluda.

Pokud to má rozumně fungovat, je nutné upřesnit zadání. Jinak tu bude zase řada zpráv "... je to skvělý, ... ale ještě drobnost....".

Co třeba použít kontingenční tabulku?

Pokud máte novější verzi excelu, pak
=LET(tab;FILTER($A$1:$A$6;$A$1:$A$6<>0);INDEX(tab;RANDBETWEEN(1;POČET(tab))))

1) Pokud to má být oddělené tak podmínky jsou:
víkend : =WORKDAY(C8-1;1)<>C8
svátek : =WORKDAY.INTL(C8-1;1;"0000000";Tabulka3[Datum])<>C8

=SUMA(SVYHLEDAT(UNIQUE(A2:A15);A2:C15;3;0))

@elninoslov

Čas odpovídá. Hlavní činností je načítání dat.

Tady se každý soubor načítá minimálně 2x. Neměřil jsem to, není to kritické. (V jiném případě jsem viděl i 10 čtení.) Dá se to zrychlit, ale za cenu složitějšího kódu.

Obvykle dávám přednost kratšímu kódu s jednodušší údržbou a čas řeším, až když to trvá opravdu dlouho (např. soubor má několik mil. řádků).

Možná jiná forma zápisu by mohla být čitelnější?

Tohle je načtení jednoho souboru a výpočet příslušné statistiky.
(Zadání je pro mne docela zmatečné, tak jen co je v přikladu nebo v poznámkách.)

// Statistika (funkce)
(Soubor as binary) =>
let
// Načtení dat
Zdroj = Lines.FromBinary(Soubor, null, false,1250),
DataTabulky = List.RemoveLastN(List.RemoveFirstN(Zdroj, each not Text.StartsWith(_, "Date") ), each not Text.StartsWith(_, ">") ),
#"Převedené na tabulku" = Table.FromList({"> " & DataTabulky{0} } & List.Range(DataTabulky,1), Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Rozdělit sloupec oddělovačem" = Table.SplitColumn(#"Převedené na tabulku", "Column1", Splitter.SplitTextByWhitespace(QuoteStyle.Csv)),
#"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(#"Rozdělit sloupec oddělovačem", [PromoteAllScalars=true]),
#"Změněný typ s národním prostředím" = Table.TransformColumnTypes(#"Záhlaví se zvýšenou úrovní", {{"T_Set", type number}, {"T_Rec", type number}, {"HR_Rec", type number}, {"HR_Set", type number}}, "en-US"),
#"Změněný typ" = Table.TransformColumnTypes(#"Změněný typ s národním prostředím",{{"Date", type date}, {"Hour", type time}}),
tab = Table.Buffer(Table.ReplaceValue(#"Změněný typ", each [Date], each [Date] & [Hour],Replacer.ReplaceValue,{"Date"})),
//
// Počítání statistiky (do recordu, pak se to spojí do tabulky)
posledniRadek = Table.Last(tab),
Stat = [
Začátek předehřevu = tab[Date]{0},
// Konec předehřevu = List.Max(tab[Date]),
Konec předehřevu = posledniRadek[Date],
Teplota nastavená = tab[T_Set]{0},
Teplota MIN = List.Min(tab[T_Rec]),
Teplota MAX = List.Max(tab[T_Rec]),

Vlhkost nastavená = tab[HR_Set]{0},
Vlhkost MIN = List.Min(tab[HR_Rec]),
Vlhkost MAX = List.Max(tab[HR_Rec]),

// Teplota in spec start = List.Min(Table.SelectRows(tab[[Date], [T_Rec]], each [T_Rec] >= #"Teplota nastavená" - 5)[Date]),
lim = #"Teplota nastavená" - 5,
Teplota in spec start = List.Min(Table.SelectRows(tab, each [T_Rec] >= lim)[Date]),
Total time = #"Konec předehřevu" - #"Začátek předehřevu",
Time in Spec = #"Konec předehřevu" - #"Teplota in spec start",
Náběh teploty = #"Teplota in spec start" - #"Začátek předehřevu",
Poslední teplota = posledniRadek[T_Rec],
Poslední vlhkost = posledniRadek[HR_Rec],
Počet záznamů v TXT = Table.RowCount(tab), // Co má být velikost??? záznamy, pokud byte, tak je to v info o souboru
Poslední datum = DateTime.Date(#"Konec předehřevu")
]
in
Stat

Všechny soubory se pak vyhodnotí normálně:

let
Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblCesta"]}[Content]{0}[Cesta]),
#"Filtrované řádky" = Table.SelectRows(Zdroj, each [Extension] = ".TXT" and not Text.Contains([Name], "Default") and [Attributes][Hidden] <> true),
#"Rozdělit sloupec oddělovačem" = Table.SplitColumn(#"Filtrované řádky", "Name", each List.Select(Splitter.SplitTextByAnyDelimiter({" ","-", "_", "."}, QuoteStyle.Csv, false)(_), (l) => l <> ""), {"pref.1", "pref.2","Předehřev", "Name.2", "Nastavený čas", "Batch"}),
#"Odebrané sloupce" = Table.SelectColumns(#"Rozdělit sloupec oddělovačem",{"Content", "Batch", "Předehřev", "Nastavený čas", "Date modified"}),
#"Přejmenované sloupce" = Table.RenameColumns(#"Odebrané sloupce",{{"Date modified", "Datum modifikace"}}),
//
// Přidaná statistika suoboru
#"Statistika souboru" = Table.TransformColumns(#"Přejmenované sloupce",{{"Content", Statistika}}),
#"Rozbalené Content" = Table.ExpandRecordColumn(#"Statistika souboru", "Content", {"Začátek předehřevu", "Konec předehřevu", "Teplota nastavená", "Teplota MIN", "Teplota MAX", "Vlhkost nastavená", "Vlhkost MIN", "Vlhkost MAX", "Teplota in spec start", "Total time", "Time in Spec", "Náběh teploty", "Poslední teplota", "Poslední vlhkost", "Počet záznamů v TXT", "Poslední datum"}),
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Rozbalené Content",{"Batch", "Předehřev", "Nastavený čas", "Datum modifikace", "Začátek předehřevu", "Konec předehřevu", "Teplota nastavená", "Teplota MIN", "Teplota MAX", "Vlhkost nastavená", "Vlhkost MIN", "Vlhkost MAX", "Teplota in spec start", "Total time", "Time in Spec", "Náběh teploty", "Poslední teplota", "Poslední vlhkost", "Počet záznamů v TXT", "Poslední datum"}),
// tohle je navíc, v excelu je to triviální vzorec
#"Přidaný sloupec" = Table.AddColumn(#"Odebrané ostatní sloupce", "Pravda/Nepravda", each DateTime.Date([Datum modifikace]) = [Poslední datum], type logical),
#"Změněný typ" = Table.TransformColumnTypes(#"Přidaný sloupec",{{"Začátek předehřevu", type datetime}, {"Konec předehřevu", type datetime}, {"Teplota nastavená", type number}, {"Teplota MIN", type number}, {"Teplota MAX", type number}, {"Vlhkost nastavená", type number}, {"Vlhkost MAX", type number}, {"Vlhkost MIN", type number}})
in
#"Změněný typ"

ctrl-F, zadat frázi, najít vše, ctrl-A, del

Co třeba použít Word (hromadný tisk)?

Příspěvky uživatele

Uživatelské menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje