Příspěvky uživatele

ak sa Vám nechce vypisovať vzorce, tak do F4 toto, a roztiahnuť:
=OFFSET($A$23;ROW(A1);COLUMN(A1)-1)
=POSUN($A$23;ŘÁDEK(A1);SLOUPEC(A1)-1)
alebo použite
=OFFSET($A$23;1;0)
=OFFSET($A$23;1;1)
...
=POSUN($A$23;1;0)
=POSUN($A$23;1;1)
...
alebo
=INDEX(A:A;24)
=INDEX(B:B;24)
...
alebo
=INDIRECT("A24")
=NEPŘÍMÝ.ODKAZ("A24")
...

Tak napríklad takýto maticový vzorec???
=IF(SUM(COUNTIF(A2;"*"&$B$2:$B$4&"*"))=0;"";INDEX(B:B;MAX(IF(COUNTIF(A2;"*"&$B$2:$B$4&"*");ROW($B$2:$B$4);FALSE))))
=KDYŽ(SUMA(COUNTIF(A2;"*"&$B$2:$B$4&"*"))=0;"";INDEX(B:B;MAX(KDYŽ(COUNTIF(A2;"*"&$B$2:$B$4&"*");ŘÁDEK($B$2:$B$4);NEPRAVDA))))

EDIT:
Tak ma napadol podstatne jednoduchší obyč. vzorec:
=IFERROR(LOOKUP(2;1/(COUNTIF(A2;"*"&$B$2:$B$4&"*")>0);$B$2:$B$4);"")
=IFERROR(VYHLEDAT(2;1/(COUNTIF(A2;"*"&$B$2:$B$4&"*")>0);$B$2:$B$4);"")

Získať s pomocou dolovacích vzorcov celý zoznam, nie je problém:
Sub Najdi_V_SuboreX()
Dim Zoznam(), CestaZoznam As String, SuborZoznam As String, ListZoznam As String, Vzorec As String

CestaZoznam = ThisWorkbook.Path & "\"
SuborZoznam = "JedenZoznam.xlsm"
ListZoznam = "List3"

Vzorec = "'" & CestaZoznam & "[" & SuborZoznam & "]" & ListZoznam & "'!"

wsTMP.UsedRange.ClearContents
With wsTMP.Range("A1")
.Formula = "=COUNTA(" & Vzorec & "$B:$B)"
With .Resize(.Value2 - 1, 2)
.Formula = "=" & Vzorec & "B2"
Zoznam = .Value2
.Value2 = Zoznam 'v prípade ak tam majú hodnoty zostať
'.ClearContents 'v prípade ak sa majú zmazať, a výsledky nebudú ťahané vzorcami v bunkách Hľadaj!G:G
End With
End With

'ďalej sa dá pracovať už aj s poľom "Zoznam" - bunky v makre nebude treba
End Sub

Príklad, pre nájdenie 1 hodnoty v zatvorenom súbore. Princíp, zložíte si vzorec s odkazom:
Sub Najdi_V_Subore1()
Dim Co As Variant, CestaZoznam As String, SuborZoznam As String, ListZoznam As String, Vzorec As String

CestaZoznam = ThisWorkbook.Path & "\"
SuborZoznam = "JedenZoznam.xlsm"
ListZoznam = "List3"

Vzorec = "'" & CestaZoznam & "[" & SuborZoznam & "]" & ListZoznam & "'!"

Co = wsHladaj.Range("F1").Value2
With wsHladaj.Range("G1")
.Formula = "=COUNTA(" & Vzorec & "$B:$B)"
.Formula = "=VLOOKUP(" & Co & "," & Vzorec & "$B$2:$C$" & .Value2 & ",2,FALSE)"
.Value2 = .Value2
End With
End Sub

Takže takto vyzerá zdroj - ten zoznam. A Vy teraz v iných zošitoch potrebujete zistiť podľa čísla bytu meno.

1. Kde je tento zoznam uložený? V rovnakej zložke ako tie iné súbory, z ktorých budete k zoznamu pristupovať? V inej zložke, ale na pevne danom mieste? Na variabilnom mieste (+info ako zistíme jeho adresu)?

2. Koľko krát potrebujete k súboru so zoznamom pristupovať? Tá premenná "ria" je z cyklu alebo nájdený riadok?

3. Ten zoznam je nutné uchovať v tých iných súboroch?

4. Nestačí do tých iných súborov dať iba odkazy vzorcom na zoznam v pomocnom skrytom liste, a použiť aj klasický VLOOKUP v cieľovej bunke (bez makra)?

Ak je príloha XLSM, musíte ju zabaliť do ZIP/RAR. Max veľkosť okolo 300 KB.
Inak prečítal som si ten Váš popis ešte raz. Vy máte pevne daný súbor so zoznamom, pevne daný list aj oblasť, všetko uložené v pevnej zložke? Tak si urobte iba linky v bunkách v tých cieľových súboroch. Alebo v nich nemôžu byť všetky tieto dáta?

Isteže, dajú sa získať dáta bez otvorenia. Ale je potrebné priložiť anonymizované prílohy. Je potrebné totiž konkrétne vedieť veľa vecí, názov listu, kde čo je, aký typ dát (zdroj tabuľka či Tabuľka), aplikácie filtrov (inak sa zisťuje posledný zaplnený riadok), odsadenie tabuľky od začiatku, sú iné dáta pod ňou (ovplyvňuje výpočet oblasti, je potrebné vedieť identifikátor začiatku a konca), dokonca je možné použiť aj PQ dotaz na ťahanie dát z iného súboru...

To takto z brucha nejde.

+- trolejbus

-s automatickou aktualizáciou zoznamu listov okrem výnimiek zadaných v makre
-ošetrenie neexistencie listu
-pri aktualizácii uchováva predošlé voľby

Vyberte si z toho čo chcete.

Prvý nástrel. Na viac nemám čas. Možno by bolo dobré, aby na to PQ mrkol nejaký PQ fachman, páč mi to PQ ide strašne pomaly, niečo tam nerobím košér ...
Makro zatiaľ nekontroluje existenciu listu, čo sa dá cez ExecuteExcel4Macro alebo ADO urobiť tiež bez otvárania súboru. Možno keď bude čas.

Ak máte Office 2019, 2021, 365, tak stačí funkcia MAXIFS
=MAXIFS(kalendář!A1:NA1;kalendář!A2:NA2;">"&0)
Ak starší tak
=LOOKUP(2;1/(kalendář!A2:NA2>0);kalendář!A1:NA1)
=VYHLEDAT(2;1/(kalendář!A2:NA2>0);kalendář!A1:NA1)

Tak nejdem zase písať elaborát o PQ. Ide tam vlastne hlavne o to, aby sa ošetrila možnosť, že budete/nebudete chcieť odfiltrovať chyby. Pretože na hromadné odfiltrovanie hodnôt sa použije List (zoznam), ale ten musí byť pre filter iba s jedinečnými hodnotami, a tie sa nedajú v Liste dosiahnuť, ak obsahuje Error. Preto čáry-máry s nahrádzaním za text "CHYBKA". Pre prípad, keď nebudete chcieť odfiltrovať chybu, je tam krok Priprava, ktorý v dátach mení chybu na "CHYBKA" iba v prípade, že odfiltrovávací List obsahuje "CHYBKA".
List.Distinct - jedinečné hodnoty
List.Transform - zmení hodnoty v Liste
each try Mazat{_} otherwise "CHYBKA" - nahradí chybu
List.Contains - test, obsahuje/neobsahuje danú hodnotu
each not List.Contains - vyberie iba riadky neobsahujúce nič z Listu

Tak to určite nie cez otváranie súborov. Extrémne pomalé. Listy, z ktorých budete čerpať údaje, sa volajú vo všetkých súboroch rovnako? Adresa údaju je na každom liste a vo všetkých súboroch rovnaká? Kopírovať treba iba hodnotu, alebo aj formát bunky, orámovanie, farbu a pod?

Priložte prílohu s príkladom. Riešením by bolo použiť "dolovacie vzorce" (čo je fakt rýchle, bez otvárania súborov) alebo PowerQuery (pre Office 2010+, bez makra a bez otvárania súborov).

Riešenie cez PowerQuery. Vypĺňate iba vstupnú Tabuľku na List1 a na liste Nastavenie nastavíte požadované parametre. Na liste PQ dáte iba Obnoviť všetko.

Tak to určite nestačí. Veď je tam niekoľko Definovaných názvov, ktoré to počítajú. Inak by to bol ohromne zložitý vzorec.

Niektoré Addins idú zapnúť/vypnúť takto:
Sub ON_OFF_ADDIN(ONOFF As Boolean)
Dim AD As AddIn
For Each AD In AddIns
Select Case AD.Title
Case "Euro Currency Tools", "niečo iné"
AD.Installed = ONOFF
End Select
Next AD
Set AD = Nothing
End Sub
Sub Zapni()
ON_OFF_ADDIN True
End Sub
Sub Vypni()
ON_OFF_ADDIN False
End Sub

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje