Příspěvky uživatele
< návrat zpět
Skúste na kópii dát.
Ak by išlo o viac listov (desiatky), stálo by za zváženie uložiť ich na pozadí v novej inštancii Excelu, ktorá sa síce na začiatku dlhšie otvára (ako nový zošit), no je skrytá, a rýchlejšia. A mohol by Vám bežať aj progres v StatusBar-e.
Podľa mňa je takýto postup celý zle. Skúste kopírovať celý list, nie riadky. Potom stačí hodnotu použitých buniek nahradiť vlastnou hodnotou. Pr.:
Sub pokus()
Dim wb As Workbook, wbNew As Workbook, ws As Worksheet, wsNew As Worksheet
Dim cesta As String, soubor As String
Set wb = ThisWorkbook
Set ws = wb.Worksheets("AAA")
cesta = "D:\pokus\2020\"
soubor = "pokus_ " & ws.Name & " .xlsx"
Application.ScreenUpdating = False
ws.Copy
Set wbNew = ActiveWorkbook
With wbNew
With .Sheets("AAA").UsedRange
.Value = .Value
End With
On Error Resume Next
.SaveAs Filename:=cesta & soubor
If Err.Number = 0 Then .Close False
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
Chcete aby som Vám to upravil nech to spraví všetky listy ?
Priložte prílohu (súbor Excelu) a určo to pôjde ošéfovať.
Ten prevod na hodnoty by sa dal urobiť aj inak (Transpose funguje správne iba do 32767 riadkov):
Sub pokus()
Dim D() As String, V(), r As Long, rv As Long, Riadkov As Long
With List1
Riadkov = .Cells(Rows.Count, 1).End(xlUp).Row - 1
D = Split(Replace(Join(Application.Transpose(.Cells(2, 1).Resize(Riadkov).Value)), " ", ""), ";")
ReDim V(1 To Riadkov, 1 To 2)
On Error Resume Next
For r = 0 To UBound(D) Step 2
rv = rv + 1
V(rv, 1) = Val(D(r)): V(rv, 2) = Val(D(r + 1))
Next r
.Cells(2, 4).Resize(Riadkov, 2).Value = V
End With
End Sub
Bez prílohy je to katastrofa. Ale pokus:
=SUMIF(INDIRECT("'"&D$1&"'!$A$5:$A$50");$A8;INDIRECT("'"&D$1&"'!$C$5:$C$50"))/INDIRECT("'"&D$1&"'!$C$2")*D$3
=SUMIF(NEPŘÍMÝ.ODKAZ("'"&D$1&"'!$A$5:$A$50");$A8;NEPŘÍMÝ.ODKAZ("'"&D$1&"'!$C$5:$C$50"))/NEPŘÍMÝ.ODKAZ("'"&D$1&"'!$C$2")*D$3
A nemusíte použiť na to makro.
EDIT: Aj keď myslím, že v receptoch bude ingrediencia uvedená iba raz, a teda netreba SUMIF, ale stačí VLOOKUP/SVYHLEDAT.
=VLOOKUP($A8;INDIRECT("'"&D$1&"'!$A$5:$C$50");3;FALSE)/INDIRECT("'"&D$1&"'!$C$2")*D$3
=SVYHLEDAT($A8;NEPŘÍMÝ.ODKAZ("'"&D$1&"'!$A$5:$C$50");3;NEPRAVDA)/NEPŘÍMÝ.ODKAZ("'"&D$1&"'!$C$2")*D$3
Prečítal som si 3x Váš "popis" a ani raz som neporozumel. Napr.:
-Kam sa má vypísať výsledok porovnania "OK"/"" ?
-Čo znamená "porovnat dva listy" ? Ktoré z dát v riadkoch majú byť rovnaké ? Iba to jedno číslo ?
-Dovysvetlite presnejšie na konkrétnom príklade. Napr.
• Zoberiem všetky čísla v 'Kontrola s SŘHV'!G6 - {905688,905689,905690}
• Každé z týchto 3 (inak neznámy počet) čísel vyhľadám v 'zdroj dat'!A:A
• A zistím, či zároveň je v stĺpci 'zdroj dat'!AC:AC niekde rovnaká hodnota ako 'Kontrola s SŘHV'!A6 ?
=SUMPRODUCT(COUNTIF(OFFSET($A$5:$A$10;(ROW($1:$31)-1)*11;);L4))
=SOUČIN.SKALÁRNÍ(COUNTIF(POSUN($A$5:$A$10;(ŘÁDEK($1:$31)-1)*11;);L4))
Predpokladám, že je 31 tých tabuliek, preto "$1:$31"
Prikladám príklad, aj s výpisom jedinečných mien pomocou maticového vzorca.
EDIT: A vlastne počet stačí aj takto:
=IF(L4="";"";COUNTIFS($A$5:$A$350;L4))
=KDYŽ(L4="";"";COUNTIFS($A$5:$A$350;L4))
alebo...
=SUBSTITUTE(RIGHT(A1;LEN(A1)-FIND(" - ";A1)-2);" - ";", ")
=DOSADIT(ZPRAVA(A1;DÉLKA(A1)-NAJÍT(" - ";A1)-2);" - ";", ")
=SUBSTITUTE(REPLACE(A1;1;FIND(" - ";A1)+2;"");" - ";", ")
=DOSADIT(NAHRADIT(A1;1;NAJÍT(" - ";A1)+2;"");" - ";", ")
EDIT: 27.11.2019 8:16 - výmena prílohy aj kódu - doplnený podpriečinok "Pracovné"
Pr.Sub Tlac_do_PDF()
Dim Nazov As String, Cesta As String, Podpriecinok As String
With ThisWorkbook
.Save
With Worksheets("List1")
Nazov = "ABC"
Podpriecinok = .Range("A1").Value
Cesta = CreateObject("WScript.Shell").SpecialFolders("mydocuments") & "\Pracovné\"
If Len(Dir(Cesta, vbDirectory)) = 0 Then MkDir Cesta
Cesta = Cesta & IIf(Podpriecinok = "", "", Podpriecinok & "\")
If Len(Dir(Cesta, vbDirectory)) = 0 Then MkDir Cesta
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Cesta & Nazov & ".pdf", _
Quality:=xlQualityMaximum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End With
End With
End Sub
Takže aj podľa mailu, to bude chyba v adresácii.
=IFERROR(MAX(OFFSET('zdroj dat'!$AB$3;MATCH($A3;'zdroj dat'!$A$4:$A$10000;0);;COUNTIF('zdroj dat'!$A$4:$A$10000;$A3)));"")
=IFERROR(MAX(POSUN('zdroj dat'!$AB$3;POZVYHLEDAT($A3;'zdroj dat'!$A$4:$A$10000;0);;COUNTIF('zdroj dat'!$A$4:$A$10000;$A3)));"")
No kým ja som si nakreslil tabuľku .....
Každopádne to sem už dám, Robil som to ale pomocou obyč IF
Kľudne pošlite, mail je v mojom konte, a hore som Vám dal vyskúšanú a funkčnú verziu na variant keď idú po sebe. Ja by som sa ale asi zbavil toho INDIRECT/NEPŘÍMÝ.ODKAZ a dal tam asi OFFSET/POSUN.
=MAX(OFFSET('zdroj dat (2)'!$AB32;MATCH($A35;'zdroj dat (2)'!$A33:$A10029;0);;COUNTIF('zdroj dat (2)'!$A33:$A10029;$A35)-1))
=MAX(POSUN('zdroj dat (2)'!$AB32;POZVYHLEDAT($A35;'zdroj dat (2)'!$A33:$A10029;0);;COUNTIF('zdroj dat (2)'!$A33:$A10029;$A35)-1))
Ak sú pomiešané (nejdú po sebe), ale aj keď idú po sebe, dá sa použiť (maticovo):
=MAX(IF('zdroj dat (2)'!$A33:$A10029=$A35;'zdroj dat (2)'!$AB33:$AB10029))
=MAX(KDYŽ('zdroj dat (2)'!$A33:$A10029=$A35;'zdroj dat (2)'!$AB33:$AB10029))
alebo ak máte najnovší Office tak ten má novú funkciu MAXIFS (nematicovo)
=MAXIFS('zdroj dat (2)'!$AB33:$AB10029;'zdroj dat (2)'!$A33:$A10029;$A35)
Príklady v prílohe.
Ten vzorec ste si asi nekrokovala, čo ?
"+3" - znamená to, že sú bunky zlúčené po 3 riadkoch, alebo že oblasti posunuté o 3? Ak posunuté (čo sú), tak nie o 3, ale oni predsa začínajú na 33 riadku, takže +32. Ak to znamená že sú zlúčené tak nielen +3, ale aj +COUNTIF()*3.
To "COUNTIF" znamená, že idú kontrakty vždy po sebe a niesú pomiešané ?
Zadávate to ako maticový vzorec ?
Príloha by bola lepšia, nech si to nemusíme domýšľať a vytvárať...
=MAX(INDIRECT("'zdroj dat (2)'!$AB"&MATCH($A35;'zdroj dat (2)'!$A33:$A10029;0)+32&":$AB"&MATCH($A35;'zdroj dat (2)'!$A33:$A10029;0)+32+COUNTIF('zdroj dat (2)'!$A33:$A10029;$A35)-1))
=MAX(NEPŘÍMÝ.ODKAZ("'zdroj dat (2)'!$AB"&POZVYHLEDAT($A35;'zdroj dat (2)'!$A33:$A10029;0)+32&":$AB"&POZVYHLEDAT($A35;'zdroj dat (2)'!$A33:$A10029;0)+32+COUNTIF('zdroj dat (2)'!$A33:$A10029;$A35)-1))
Manuálne vytriedenie jedinečných hodnôt môžete obísť maticovým vzorcom (dostatočne natiahnutým), alebo Kontingenčnou tabuľkou (stačí dať iba Obnoviť).
Tých zmien je veľa. Už som aj pozabúdal čo som chcel, tak len čiastočne:
-Rok sa mení výhradne makrom, a výhradne v jedinej bunke NEMAZAT!C2
-Zoznam sviatkov sa mení podľa roku (Veľká Noc tiež)
-PF fialové je podľa sviatkov daného roku
-PF víkendov je zjednodušené
-Zoznamy mien sú už iba na jedinom mieste NEMAZAT!A1:A21
-Zoznamy mesiacov to isté NEMAZAT!F2:F13
-Zmenený spôsob zobrazenia dňa v týždni
-Brala ste rok do mesiacov cez VLOOKUP ??? - opravené
-Určite má byť v mesiacoch v súčtoch C41 a D41 rovnaký vzorec ? Asi nie - opravené.
-Neviem už kde to bolo, bol neviem prečo vynechaný stĺpec H dochádzkovej oblasti - opravené.
-V dochádzkových bunkách C4:H34 je možnosť vybrať služby ZV, NV, 1/2. Nikde v súčtoch mesačných ani v ročnom sa s týmto nepočíta. Je to správne ?
-Ak je už možnosť výberu mena (alebo typ služby ZV,NV,1/2) v A37:A40, a teda meniť poradie, prečo nieje taká možnosť v ročnom sumáre B3:E3, ... B21:E21? Mala ste tam navyše odkazy napevno na riadky, a neviem už v ktorých ročných súčtoch odkazovali na zlé riadky. Dal som to zatiaľ cez SUMIF() {dá sa prípadne cez VLOOKUP}, aby to našlo aj na inej pozícii, keď už sa dá meniť.
-V sumároch polrokov, riadok 17 - 18, to zrátavate po bunke - opravené na SUM
Makrá
-makro na ukladanie s daným rokom, navýšenie roku, premazanie, znovuuloženie s novým rokom, už bez problémov funguje.
-prerobené prefarbovanie, ktoré pristupuje o niečo menej systémom "po jednej bunke". Dal by sa ešte urobiť inak, aby bola vytvorená kolekcia s farbami, a polia z prípadných viacnásobných oblastí zmeny. Otázne je či by to nebolo vzhľadom na malý počet menených buniek naraz (väčšinou asi jedna) zbytočné. Do makra vstupu iba oblasť prieniku zmena vs oblasť služieb.
-makro na vymazanie oblasti nechápem. Má mazať označenú oblasť na liste ??? A čo keď označíte omylom niečo iné? Dal som 2 spôsoby, vyberte si ktorý to má akože byť, či mazanie prieniku výberu vs oblasti služieb, alebo vždy celej oblasti služieb.
-Listy som vo VBA pomenoval.
... a na ostatné veci už som asi aj zabudol :)
Uživatelské menu
\nMenu
On-line nástroje
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
RSS nejnovější články