Příspěvky uživatele

PowerQuery je súčasťou Office 2016/2019/2021/365. Do Office 2010/2013 sa dá doinštalovať oficiálnym doplnkom od MS.

Vzorcami je to zložitejšie. Treba urobiť jedinečné hodnoty, ale vzhľadom aj na to, že môže byť iná jednotka pri rovnakom mene (t/kg, l/ml/kg, kg/bal, ...). Takže som použil zjednocovací skrytý hárok, a odtiaľ sa všetko vzorcami ťahá do výslednej tbl. No tá musí byť nadimenzovaná na maximum možných riadkov + 2. A o zvyšok sa starajú čachre-machre s Podmieneným formátovaním.

No a čo PowerQuery?

V zošite je 10362 definovaných názvov. To ako fakt???
Zošit bez opravy názvov (asi tá čínština) nejde otvoriť.
Stĺpce C a E ("2020 ACT" a "2021 ACT") nie sú súčty, ako píšete, ale indexy. Použite HLOOKUP. Prečo začína oblasť od M, keď mesiace sú od N? Oblasť "2021 YTD Bgt" odkazuje na "2021 Bgt" teda bez YTD ale hlavne iba po september (AI - má to byť AL). Oblasť "2020 ACT" odkazuje na oblasť "2020 YTD". Na čo je to obielené číslo v M?

Keďže predpokladám, že budete dopĺňať roky (stĺpce), tak by som urobil definované oblasti pre zjednodušenie vzorcov s INDEX/MATCH/OFFSET/HLOOKUP ktoré by si samé našli rok aj s prívlastkom, a z danej oblasti našli rozsah mesiacov. Ale to by museli byť tie označenia skupín stĺpcov - roky s prívlastkom jednotné. Viď napr. "2021 YTD Bgt" vs "2021 Bgt", či neexistencia napr "2020 ACT".

Navrhujem aby ste doplnil do tejto tabuľky ešte jeden riadok dát, korektne nazval skupiny, a MANUÁLNE zapísal Vami požadovaný výsledok do buniek, a tieto bunky očervenel.
A pridajte trošku podrobnejší popis, páč MATCH nerobí súčet... a divná je aj tá konštrukcia s pomocou ...MATCH(A7;$M$7:$M$7;0)... ak to má slúžiť na porovnanie stačí "="

EDIT: Nejaký ten príklad...

Čo ak bude Last alebo First 3 a viac ciferné?
Čo ak niečo z toho nebude vôbec číslo?
Čo ak užívateľ zruší InputBox bez zadania?
Čo ak nebude po koniec hárku dosť buniek?

zišlo by sa pár ošetrení...

Ak bude scan do F1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pos
If Not Intersect(Range("F1"), Target) Is Nothing Then
On Error Resume Next
Pos = WorksheetFunction.Match(Range("F1"), Range(Range("B1"), Cells(Rows.Count, "B").End(xlUp)), 0)
If Err.Number = 0 Then Range("A1:B1").Offset(Pos - 1, 0).Interior.Color = vbGreen
On Error GoTo 0
End If
End Sub

V prvých 2 vzorcových príkladoch musíte mať vzorce dostatočne na výšku natiahnuté. KT si to určí sama. No a pridal som ešte možnosť prevodu tabuľky na Tabuľku, kde sa vzorce dopĺňajú samé.

Napr. pomocný stĺpec s maticovým vzorcom (Ctrl+Shift+Enter), prípadne ho skryť, alebo umiestniť na iný list (skrytý?).
Alebo urobiť 2 pomocné stĺpce bez matice, kde bude v jednom iba podmienka a číslo riadku, a v druhom podľa SMALL z prvého pomocného do INDEX dávať číslo prvku. Alebo si urobte KT, ktorú si aktualizujete a obdobne ako je v Overení dát si vypočítate počet prvkov. Ak to teda potrebujete aktualizovať menej často ako každé prepočítanie vzorca. Potom vyššie uvedené príklady...

Ak má CSV stĺpce oddelené bodkočiarkami/středníkmi/";" tak žiadny iný text v CSV NESMIE obsahovať tieto oddeľovače. Odkiaľ by potom import vedel, ktorý ";" je oddeľovač a ktorý text??? Na tie dvojriadky ešte pozriem neskôr...

Sub Kopiruj()
Dim rngCiel As Range, rngZdroj As Range, rngOldSelection As Range

Set rngOldSelection = Selection
Set rngZdroj = Intersect(rngOldSelection, Range("A:A,C:F"))
Set rngCiel = Workbooks("Cieľový zošit.xlsx").Worksheets("Cieľ").Range("B3")

If Not rngZdroj Is Nothing Then rngZdroj.Copy rngCiel
Application.CutCopyMode = False
rngOldSelection.Select
End Sub

Pr.

To pôjde len makrom.
Vy chcete vyberať CSV súbor ale tu je v prílohe XLSX súbor, takže:
-Čím sú v origo CSV oddelené stĺpce?
-Ako v ňom vyzerá desatinné číslo (oddeľovač desatín aj tisícov)?
-Ako vyzerá dátum?
-Aké formáty sú v nezaplnených stĺpcoch?

Najlepšie priložiť taký CSV so spomenutými druhmi dát, bez citlivých údajov, s aspoň 2 riadkami.

Pokus pomocou PQ. Nakoľko sa v XLSX súboroch nenachádza info "Kategorie", tak som musel tak či tak načítať www a potiahnuť to odtiaľ. A keď už som ju mal natiahnutú, tak som odtiaľ zobral aj zvyšok. Problém bol pri tom, že nie všetci športovci majú rovnaký počet Tabuliek vo svojom profile. No snáď to pôjde. Alebo aspoň ako inšpirácia.

Ak sa niekomu chce s tým ešte hrať, prosím mrknite, ako by ste vyriešili napr. tieto veci:

-Nemôžem prepojiť ktAtleti a ktSouteze tak, aby SmartFilter bral mená z ktAtleti (alebo patričného dotazu, či z dátového modelu) a filtroval ktSouteze, kde meno nieje, ale je tam prepojené číslo.

-Ak dám robiť súčet súťaží pre atléta (posledný počítaný stĺpec), skryje mi atléta, ktorý nemá žiadnu súťaž (Filip Litavský) - nežiadúce, lebo chceme vidieť info o atlétovi, aj keď nemá súťaž - nech dá 0.

Už sa mi nechce dumať ...

Pýtam sa preto, lebo premýšľam ako by sa to dalo urobiť cez PowerQuery. PQ si načíta aj tú stránku aj ten XLSX. Na stránke sú objekty Tabuľka. Takže by sa dali vyselektovať nejaké data z nich za predpokladu, že je u každého športovca identická skladba týchto Tabuliek (aj keď sú prázdne). Teda, že Tabuľka2 obsahuje vždy rovnaký typ dát. Obdoba pri tom XLSX, kde sa budú hľadať riadky s určujúcim slovom. To slovo tam musí byť. V PQ by sa to dalo totiž bez nutnosti otvárať stránku alebo súbor XLSX. Len je potrebné dopredu vedieť, ktoré údaje (ktoré Tabuľky a ktoré stĺpce v nich) sa majú zahrnúť do výsledku. No nebude to vytvárať pre každého športovca vlastnú tbl, ale jednu spoločnú, ktorú následne podrobíte analýze.

Ale je to len nápad ... nič žiaľ nemôžem prisľúbiť.

Aké dáta by ste z toho potreboval? Lebo asi len o "jméno" + "rok" + "kategorie" + "počet závodů" nepôjde, či?

Mám to rovnako akurát cez WORKDAY.INTL. Ale ak to bude pre rôzne roky, tak to takto jednoducho a elegantne nepôjde. Bude potrebná buď rozsiahla tabuľka dátumov na všetky dotknuteľné roky, alebo výpočet poľa sviatkov s variabilným rokom. Takže napr. si urobiť súhrnný reťazec sviatkov s dosaditeľným rokom a ten nasekať v matici a previesť cez DATEVALUE. No tu môže nastať problém v lokálnom nastavení PC (EU vs UK/US).
Príloha by neuškodila, možno bude treba zohľadniť aj množstvo dát.

EDIT: Tak príklad, nakoniec som nepoužil spájanie a rozdeľovanie cez text.

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje