Příspěvky uživatele

=SUMPRODUCT((COUNTIF(OFFSET(A2:A15;;;ROW(A2:A15)-1);A2:A15)=1)*C2:C15)
=SOUČIN.SKALÁRNÍ((COUNTIF(POSUN(A2:A15;;;ŘÁDEK(A2:A15)-1);A2:A15)=1)*C2:C15)

Nezrozumiteľné. Nájsť cez VLOOKUP s parametrom TRUE správne rozmedzie nie je problém. Čo má spoločné tá spodná "akože tabuľka" s príkladovymi dátami? Iné roky, iné tony, iné rozdelenie (23t nespadá do rovnakej kategórie ako 25t a 26t). Uveďte normálny súbor Excelu, kde máte príkladové dáta, a presne aký má vzniknúť výsledok z tých konkrétnych dát. Teda nie raz také roky, potom iné, nesediace zaradenie ton objednávok do ton cenníkových kategórií, raz oddeľovač "," potom ";", záhadná pomlčka za 23t ... A chýba cena pod 20t.

@lubo: čas som nameral na rovnakej vzorke súborov 229 s (00:03:49). Vedel by ste ako identifikovať súbory, pri ktorých niekde nastane chyba prevodu na číslo?

@jaks.dalibor: Skutočne potrebujete načítať až toľko súborov? Nemôžete ich trochu eliminovať, napr. dátumom?

Ako pretransformovať funkciu na dotaz a naopak ...
No to je ťažké Vám vysvetliť, keď neviete, čo je funkcia. Funkcia sa používa na vrátenie nejakého výsledku (ale nemusí to tak byť vždy). Opakovane, na väčší počet zmien parametrov. A to je to. Do funkcie (väčšinou) vstupuje nejaký parameter, ktorý sa mení, funkcia vyráta čo má, a vráti adekvátny výsledok. V tomto prípade ako parameter funkcie je súbor, a funkcia vráti jeho hodnoty. Teda nemôžete spustiť funkciu bez parametru. To je presný opak dotazu (teraz vynecháme úvahy o inom druhu parametrov vstupujúcich aj do dotazu napr. v tomto príklade Cesta z pomocnej Tabuľky, nazvime ich radšej premenné), do ktorého nevstupujú parametre ako do funkcie. Teda Vy keď chcete urobiť nejakú funkciu na načítanie údajov zo súborov, urobíte si najskôr normálny dotaz, kde si do premennej dáte pevnú cestu k jednému súboru, nastavíte potrebné kroky, ktoré vidíte čo robia. Keď je to funkčné, zmeníte dotaz na funkciu, stačí napísať na začiatok dotazu
(NázovParametru as TypParametru) =>
a nahradiť ten predtým pevne stanovený skúšobný súbor tým parametrom. A máte z dotazu funkciu z ktorej dostanete výsledok ak ju zavoláte
NázovFunkcie(NázovParametru)
Ale funkciu už nemôžete krokovať - ona je krok v obslužnom dotaze. Takže, ak ju chcete upraviť a krokovať, tak si vytvorte prázdny dotaz, vložte tam kód funkcie, odstráňte
(NázovParametru as TypParametru) =>
a zmeňte ten variabilný NázovParametru na pevný súbor. Lenže tu sa treba zamyslieť nad tým typom parametru. V tomto prípade do funkcie vstupuje parameter typu Binary teda Content - obsah súboru, nie iba názov súboru. Takže ten parameter NázovParametru vymeníte za niečo čo urobí obsah (Content) toho súboru teda
File.Contents("E:\Download\test2\PRE_9-A _02_024_22A221011.TXT")

Je to proste ťažké vysvetliť, keď nepoznáte rozdiel medzi dotazom a funkciou či procedúrou a funkciou, a ako v nich pracujú parametre/premenné.

Ak je dotaz ako funkcia, nedá sa krokovať a pozrieť medzivýsledky alebo ladiť. Ja to robím tak, že si ladím na normálnom dotaze, a ten po overení funkčnosti s pevným parametrom prevediem na funkciu. Takže ak si chcete pozrieť ako táto funkcia pracuje so súborom, prerobte si ju opäť na dotaz s pevným parametrom:
let
Soubor = File.Contents("E:\Download\test2\PRE_9-A _02_024_34A221014.TXT"),
Zdroj = Csv.Document(Soubor,2,{2,23},null,1250),
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),
#"Rozdeliť stĺpec podľa oddeľovača" = Table.SplitColumn(#"Nahradená hodnota", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Teplota nastavená", "T_Eff", "Teplota", "Vlhkost", "Vlhkost nastavená", "Door"}),
#"Zmenený typ pomocou miestneho nastavenia" = Table.TransformColumnTypes(#"Rozdeliť stĺpec podľa oddeľovača", {{"Column1", type datetime}, {"Teplota nastavená", type number}, {"T_Eff", type number}, {"Teplota", type number}, {"Vlhkost", type number}, {"Vlhkost nastavená", type number}}, "en-GB"),
#"Filtrované riadky" = Table.SelectRows(#"Zmenený typ pomocou miestneho nastavenia", each [Door] <> null),
#"Pridané vlastné" = Table.AddColumn(#"Filtrované riadky", "Tolerance teploty MIN", each [Teplota nastavená]-5),
#"Pridané vlastné1" = Table.AddColumn(#"Pridané vlastné", "Tolerance teploty MAX", each [Teplota nastavená]+5),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Pridané vlastné1", "Time in spec start", each if [Teplota] >= [Tolerance teploty MIN] then [Column1] else null),
PosledniTeplota = Table.Last(#"Pridaný podmienený stĺpec")[Teplota],
PosledniVlhkost = Table.Last(#"Pridaný podmienený stĺpec")[Vlhkost],
#"Seskupené řádky" = Table.Group(#"Pridaný podmienený stĺpec", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX"}, {{"Teplota MIN", each List.Min([Teplota]), type number}, {"Teplota MAX", each List.Max([Teplota]), type number}, {"Vlhkost MIN", each List.Min([Vlhkost]), type number}, {"Vlhkost MAX", each List.Max([Vlhkost]), type number}, {"Začátek předehřevu", each List.Min([Column1]), type datetime}, {"Konec předehřevu", each List.Max([Column1]), type datetime}, {"Teplota in spec start", each List.Min([Time in spec start]), type nullable datetime}}),
#"Přidané: Vlastní2" = Table.AddColumn(#"Seskupené řádky", "Total time", each [Konec předehřevu]-[Začátek předehřevu]),
#"Přidané: Vlastní3" = Table.AddColumn(#"Přidané: Vlastní2", "Time in Spec", each [Konec předehřevu]-[Teplota in spec start]),
#"Přidané: Vlastní4" = Table.AddColumn(#"Přidané: Vlastní3", "Náběh teploty", each [Teplota in spec start]-[Začátek předehřevu]),
#"Přidané: Vlastní5" = Table.AddColumn(#"Přidané: Vlastní4", "Poslední teplota", each PosledniTeplota),
#"Přidané: Vlastní6" = Table.AddColumn(#"Přidané: Vlastní5", "Poslední vlhkost", each PosledniVlhkost)
in
#"Přidané: Vlastní6"
Vymeňte si len názov súboru a môžete krokovať. Tie posledné hodnoty sa uložia do premenných vo funkcii Table.Last. a použijú sa následne po zlúčení riadkov. Je to tam pekne vidieť.

No a čo sa týka tej chyby. Skúsim vymyslieť, ako odhaliť, ktorý súbor robí neplechu. Musíme ho nájsť, aby sme ošetrili aj takú eventualitu. Alebo spustite to makro, a keď makro spadne, tak prečítajte hodnotu Files(i). Tam je názov súboru.

EDIT:
POZOR! Fórum maže viac ako 1 medzeru za sebou. Takže v tomto riadku
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),za Zdroj sú v prvých úvodzovkách 2 medzery, v druhých úvodzovkách 1 medzera !!!

Urobil som pokus. Vaše súbory som si zduplikoval tak, aby bolo načítaných 25000 súborov (38750 je to aj so súbormi Default). Musí sa na to pozrieť odborník na PQ, lebo to trvá strašne dlho. Urobil som na pokus aj makro, posúďte sám:

PowerQuery : 150 s
Makro : 43 s

No ja som posldné hodnoty riešil v tej funkcii fncGetSoubor:
(Soubor as binary) => let
Zdroj = Csv.Document(Soubor,2,{2,23},null,1250),
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),
#"Rozdeliť stĺpec podľa oddeľovača" = Table.SplitColumn(#"Nahradená hodnota", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Teplota nastavená", "T_Eff", "Teplota", "Vlhkost", "Vlhkost nastavená", "Door"}),
#"Zmenený typ pomocou miestneho nastavenia" = Table.TransformColumnTypes(#"Rozdeliť stĺpec podľa oddeľovača", {{"Column1", type datetime}, {"Teplota nastavená", type number}, {"T_Eff", type number}, {"Teplota", type number}, {"Vlhkost", type number}, {"Vlhkost nastavená", type number}}, "en-GB"),
#"Filtrované riadky" = Table.SelectRows(#"Zmenený typ pomocou miestneho nastavenia", each [Door] <> null),
#"Pridané vlastné" = Table.AddColumn(#"Filtrované riadky", "Tolerance teploty MIN", each [Teplota nastavená]-5),
#"Pridané vlastné1" = Table.AddColumn(#"Pridané vlastné", "Tolerance teploty MAX", each [Teplota nastavená]+5),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Pridané vlastné1", "Time in spec start", each if [Teplota] >= [Tolerance teploty MIN] then [Column1] else null),
PosledniTeplota = Table.Last(#"Pridaný podmienený stĺpec")[Teplota],
PosledniVlhkost = Table.Last(#"Pridaný podmienený stĺpec")[Vlhkost],
#"Seskupené řádky" = Table.Group(#"Pridaný podmienený stĺpec", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX"}, {{"Teplota MIN", each List.Min([Teplota]), type number}, {"Teplota MAX", each List.Max([Teplota]), type number}, {"Vlhkost MIN", each List.Min([Vlhkost]), type number}, {"Vlhkost MAX", each List.Max([Vlhkost]), type number}, {"Začátek předehřevu", each List.Min([Column1]), type datetime}, {"Konec předehřevu", each List.Max([Column1]), type datetime}, {"Teplota in spec start", each List.Min([Time in spec start]), type nullable datetime}}),
#"Přidané: Vlastní2" = Table.AddColumn(#"Seskupené řádky", "Total time", each [Konec předehřevu]-[Začátek předehřevu]),
#"Přidané: Vlastní3" = Table.AddColumn(#"Přidané: Vlastní2", "Time in Spec", each [Konec předehřevu]-[Teplota in spec start]),
#"Přidané: Vlastní4" = Table.AddColumn(#"Přidané: Vlastní3", "Náběh teploty", each [Teplota in spec start]-[Začátek předehřevu]),
#"Přidané: Vlastní5" = Table.AddColumn(#"Přidané: Vlastní4", "Poslední teplota", each PosledniTeplota),
#"Přidané: Vlastní6" = Table.AddColumn(#"Přidané: Vlastní5", "Poslední vlhkost", each PosledniVlhkost)
in
#"Přidané: Vlastní6"
No a dotaz test iba vyvoláva funkciu pre každý súbor:
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", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"prefix", "Name"}),
#"Rozdělit sloupec oddělovačem1" = Table.SplitColumn(#"Rozdělit sloupec oddělovačem", "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"předehřev", "Name.2", "Nastavený čas", "Batch"}),
#"Nahradená hodnota" = Table.ReplaceValue(#"Rozdělit sloupec oddělovačem1",".TXT","",Replacer.ReplaceText,{"Batch"}),
#"Vyvolat vlastní funkci1" = Table.AddColumn(#"Nahradená hodnota", "Extrakce", each fncGetSoubor([Content])),
#"Odebrané sloupce" = Table.RemoveColumns(#"Vyvolat vlastní funkci1",{"Content", "prefix", "Name.2", "Date accessed", "Date created", "Extension", "Attributes", "Folder Path"}),
#"Rozbalené Extrakce" = Table.ExpandTableColumn(#"Odebrané sloupce", "Extrakce", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX", "Teplota MIN", "Teplota MAX", "Vlhkost MIN", "Vlhkost MAX", "Začátek předehřevu", "Konec předehřevu", "Total time", "Time in Spec", "Náběh teploty", "Poslední teplota", "Poslední vlhkost"}),
#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Extrakce",{{"Date modified", "Datum modifikace"}}),
#"Stĺpce so zmeneným poradím" = Table.ReorderColumns(#"Přejmenované sloupce",{"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 MAX", "Vlhkost MIN", "Tolerance teploty MIN", "Tolerance teploty MAX"}),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Stĺpce so zmeneným poradím", "Pravda/Nepravda", each DateTime.Date([Datum modifikace]) = DateTime.Date([Konec předehřevu]))
in
#"Pridaný podmienený stĺpec"

EDIT:
Priložte ten súbor, v ktorom nejde previesť tie čísla a hádže chyby.

POZOR! Fórum maže viac ako 1 medzeru za sebou. Takže v tomto riadku
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),za Zdroj sú v prvých úvodzovkách 2 medzery, v druhých úvodzovkách 1 medzera !!!

Skúsim aj ja kluci. Riešenie od veny som nevidel, tak dúfam, že som sa nezamotal :)
Musím s tým PQ robiť viac...

Maticový vzorec (Ctrl+Shift+Enter), príklad pre Tabuľku:
=TEXTJOIN(", ";TRUE;IF(SUBTOTAL(3;OFFSET(Tabuľka1[Stĺpec1];ROW(Tabuľka1[Stĺpec1])-MIN(ROW(Tabuľka1[Stĺpec1]));;1));Tabuľka1[Stĺpec1];""))
=TEXTJOIN(", ";PRAVDA;KDYŽ(SUBTOTAL(3;POSUN(Tabuľka1[Stĺpec1];ŘÁDEK(Tabuľka1[Stĺpec1])-MIN(ŘÁDEK(Tabuľka1[Stĺpec1]));;1));Tabuľka1[Stĺpec1];""))

Neviem, či správne rozumiem.
Vy potrebujete systém 1 súbor = 1 riadok?
Pomocou zlúčenia zistím Min/Max hodnoty teplôt, vlhkosti, času. Nastavenie teploty a vlhkosti je naprieč súborom rovnaké?
Vy potrebujete kontrolovať každý riadok poľa [Teplota] voči minimu a maximu z tohto poľa [Teplota]+-tolerancia 5?.
Identifikátor Batch je jedinečný naprieč súbormi? Teda nikdy nie je rovnaké Batch v názvoch viacerých súborov?

EDIT:
Sorry, nereloadlo mi stránku, inak by som zistil, že veny už pridal riešenie.

A ktorý z tých stĺpcov obsahuje rozhodujúci maximálny dátumočas ? (Začátek předehřevu / Konec předehřevu / Datum modifikace / Trplota in spec start)

Výborný nápad. Premýšľal som ako sa zbaviť toho jedného hľadania...

Napr. (to sa dá nakombinovať viacero textových funkcií)
=REPLACE(A1;SEARCH(" ";A1);LEN(A1);MID(A1;SEARCH(" ";A1)+1;2)&"Test_Q")
=NAHRADIT(A1;HLEDAT(" ";A1);DÉLKA(A1);ČÁST(A1;HLEDAT(" ";A1)+1;2)&"Test_Q")

Pr.
Private Sub CommandButton1_Click()
On Error Resume Next
With Worksheets("List1")
Intersect(.UsedRange, .Range("B:C").Resize(Rows.Count - 3).Offset(3, 0)).SpecialCells(xlCellTypeVisible).Select
If Err.Number <> 0 Then MsgBox "Nic.", vbInformation
End With
On Error GoTo 0
End Sub

Ak by ste pre e-shop menil napr. počet vlastností DATA produktu, teda umiestnenie stĺpcov, tak by akákoľvek verzia vzorca počítajúca s číslom stĺpca absolútne (či už COLUMN()=4 ale ISEVEN()/ISODD(), ktorá zisťuje párnosť/nepárnosť aktuálneho stĺpca) prestala okamžite fungovať. Preto som to prerobil tak, že párnosť/nepárnosť sa neurčuje z aktuálneho stĺpca, ale z rozdielu medzi aktuálnym stĺpcom a referenčným stĺpcom. Musíte dbať len na jedinú vec. Aby tie počítané stĺpce išli hneď za referenčným stĺpcom. Teda teraz je referenčný stĺpec napr. U. Tak všade vo vzorci bude $U2. ak aj pridáte napr. 3 stĺpce pred stĺpec U, všetko ostane fungovať, lebo sa počíta rozdiel medzi aktuálnym stĺpcom a referenčným, a referenčný sa automaticky zmení po pridaní sám z U na X. Že sa zmení nepárny na párny nehrá úlohu.
=IF($U2="";"";IF(ISODD(COLUMN()-COLUMN($U2));$E2&", "&IF(COLUMN()-COLUMN($U2)=1;"";TRUNC((COLUMN()-COLUMN($U2)+1)/2)&" ")&"produktový obrázek.";REPLACE($U2;LEN($U2)-3;1;TEXT(TRUNC((COLUMN()-COLUMN($U2)+2)/2);"-00."))))
=KDYŽ($U2="";"";KDYŽ(ISODD(SLOUPEC()-SLOUPEC($U2));$E2&", "&KDYŽ(SLOUPEC()-SLOUPEC($U2)=1;"";USEKNOUT((SLOUPEC()-SLOUPEC($U2)+1)/2)&" ")&"produktový obrázek.";NAHRADIT($U2;DÉLKA($U2)-3;1;HODNOTA.NA.TEXT(USEKNOUT((SLOUPEC()-SLOUPEC($U2)+2)/2);"-00."))))

To je zase niečo iné, ak má byť každý druhý stĺpec iný výsledok. Každopádne takýto "všemocný" vzorec to rieši:
=IF($C2="";"";IF(ISEVEN(COLUMN());$B2&", "&IF(COLUMN()=4;"";TRUNC((COLUMN()-2)/2)&" ")&"produktový obrázek.";REPLACE($C2;LEN($C2)-3;1;TEXT(TRUNC((COLUMN()-1)/2);"-00."))))
=KDYŽ($C2="";"";KDYŽ(ISEVEN(SLOUPEC());$B2&", "&KDYŽ(SLOUPEC()=4;"";USEKNOUT((SLOUPEC()-2)/2)&" ")&"produktový obrázek.";NAHRADIT($C2;DÉLKA($C2)-3;1;HODNOTA.NA.TEXT(USEKNOUT((SLOUPEC()-1)/2);"-00."))))

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje