Příspěvky uživatele

Pri počte 100 riadkov je to asi zanedbateľné, ale ak by sa jednalo o veľké množstvo, bolo by asi kratšie nepristupovať na list pri každom 0 riadku, ale hromadne. Zmenený test bunky na 0 nie aj na "" či Empty. Je lepšie odkazovať na nejaký list, zatiaľ je tam ActiveSheet. Proste iba trochu iná alternatíva.
Sub Skryj_Zobraz()
Dim iRow As Integer, r As Range
Application.ScreenUpdating = False
With ActiveSheet
If .Columns(6).EntireColumn.Hidden Then
.Rows("1:100").EntireRow.Hidden = False
Else
For iRow = 1 To 100
If .Cells(iRow, 3).Value = 0 And .Cells(iRow, 3).Value <> "" Then If r Is Nothing Then Set r = .Rows(iRow) Else Set r = Union(r, .Rows(iRow))
Next iRow
If Not r Is Nothing Then r.EntireRow.Hidden = True
End If
.Columns(6).EntireColumn.Hidden = Not .Columns(6).EntireColumn.Hidden
End With
Application.ScreenUpdating = True
End Sub

-Pomocný list "Temp", kde prebieha výcuc dát z Vašej tabuľky, a nachystanie pre rozbaľovacie zoznamy. Tento list potom skryte.
-Vo Vašej tabuľke nemajte medzi dátami v stĺpcoch C a D prázdne riadky.
-Celé to počíta dynamický počet položiek. Nastavte si iba pretiahnutím vzorec v stĺpci A v liste Temp, na požadovaný počet položiek + rezerva (zatiaľ je nastavené 40).
-Pod tabuľku v liste "Data" nevpisujte iné data, na to používajte list "Výber".

-Nevýhoda rozbaľovacích zoznamov je, že sa nevynulujú, ak v nich ostala hodnota z minulosti, ale vzhľadom na zmenu v inom závislom zozname by tam tá hodnota byť nemala. Malá náplasť je že Vám v tomto prípade hodí výsledok "Hodnota" chybu.

Ešte by som mal jedno dôrazné odporúčanie:
Vo Vašom kóde chýba odkazovanie na nejaký list. Odkazujete hneď na Cells, čiže na buňky práve aktívneho listu. Môže sa Vám stať a škaredo vypomstiť, ak sa omylom prepnete na iný list, Excel si zapamätá tento list, a nabudúce súbor otvorí na ňom. A je vymalováno. Riešenie je buď používať v zošitoch len jediný list, alebo .ActiveSheet nahradiť za .Worksheets("meno_listu"), a v každom súbore sa musí upravovaný list volať rovnako, vtedy môžete použiť aj viac listov, aj môže byť ktorýkoľvek aktívny.

Spájanie hodnôt buniek do textového reťazca nieje natívnymi funkciami Excelu možné. Používa sa na to pomocný stĺpec či list
nájdete napr tu :
, alebo ak je to len pár vlastností, tak môžete použiť CONCATENATE+IF.
Potom nieje problém použiť makro.

Ale stále nerozumiem ako to myslíte. Kde uvažujete tie zaškrtávacie políčka ? V každom riadku každej položky a samostatne fungujúce pre každú položku ? No to pri 2000 položkách a 10 vlastnostiach bude záhul. Alebo takto, pre všetky položky naraz?
Ďalej v liste s výsledkom, alebo v liste s datami ?
Bez príloh sa robí vééééľmi ťažko.

Bude potrebné ale vedieť umiestnenia dát, pretože matica musí počítať s číslami riadkov a stĺpcov, a hlavne keď to chcete ob-riadok, tak sa musí nastaviť, ktorý údaj je na párnom riadku.
Ďalej treba nastaviť samozrejme rozsahy tbauľky, a vzorec skopírovať na počet riadkov rovnajúci sa 2-násobku počtu stĺpcom kde je "ano" a "-". Ale to je samozrejmé.

A ak chcete aby Vám "ANO" ukázalo len v tom jedinom riadku kde došlo zmene oproti "očakávanému", tak potrebujete najskôr pomocnú tabuľku, v ktorej bude určené, ktorý servis je pre ktorú značku "očakávaný", a porovnáte údaje s touto tabuľkou. Vyskočí na Vás potom ANO len v tom riadku, kde je nesprávny servis, nie aj v ostatných riadkoch, kde je správny servis. Záleží čo chcete.

Sub SpustiZmenu_Click()
Dim Cesta As String, Subor As String, WB As Workbook, x As Long
Cesta = ThisWorkbook.Path & IIf(Right(ThisWorkbook.Path, 1) = "\", "", "\")
Subor = Dir(Cesta & "*.xlsx", vbNormal)
Application.ScreenUpdating = False
While Subor <> vbNullString
On Error GoTo CHYBA
Set WB = Workbooks.Open(Cesta & Subor)
On Error GoTo 0
With WB
With .ActiveSheet
For x = 1 To .Cells(Rows.Count, 2).End(xlUp).Row
.Cells(x + 1, 1) = .Cells(x + 1, 3).Value & .Cells(x + 1, 4).Value
Next x
End With
.Save
.Close
End With
GoTo POKRACUJ
CHYBA:
MsgBox ("Chyba pri spracovaní súboru :" & vbNewLine & vbNewLine & Cesta & Subor)
POKRACUJ:
Subor = Dir()
Wend
Application.ScreenUpdating = True
End Sub
Vytvorte si v zložke s Vašimi súbormi jeden súbor XLSM v ktorom napr. tlačítkom spustite makro.

PS: Ešte som zabudol dodať, že to otvorí a Vašim spôsobom upraví všetky súbory "*.xlsm", ktoré v danom adresári nájde. Takže pokusy robte na kópii adresára s ostrými dátami.

Chýba Vám nadpis v skrytom stĺpci A. Je potrebný aj kvôli Rozšírenému filtru, aj kvôli zisťovaniu posledného riadku.

Ak chcete, pošlite mi na mail reálne súbory, pozriem sa Vám na to a sprevádzkujem. Ale priložte mi tam aj ručne urobený vzhľad ako to má vyzerať. Stačí s kúska údajov. Ak teda chcete. Zneužitie Vašich dát je vylúčené.

Tu máte 2 verzie. Jedna pre celkový súčet, druhá pre súčty jednotlivých listov.Jednoduché to zrovna nieje. Neznámy počet listov o neznámych názvoch, s neznámym počtom položiek, s neznámymi unikátmi, spojiť z nich všetky hodnoty do jedného poľa čo najrýchlejšie bez cyklov, vyzrieť na Rozšírený filter (ten obsahuje zákernosť, ktorá sa rieši krkolomne) ...

Nechcelo sa mi už ďalej s tým paprať a vymýšľať ako vypísať iba pre každý list iba jeho jedinečné položky, tak som si pomohol z príkladu spoločného súčtu, a v tabuľke všetkých jedinečných z všetkých listov, je proste 0 pri liste kde sa táto hodnota nenachádza. Neviem, či to takto môže byť.
Snáď aspoň niečo pomôže.

V jednom liste máte data do zoznamov, ďalej sú tam pomenované oblasti kategórií, a tie sa cez INDIRECT načítavajú do Overenia údajov.
Nevýhoda je ale tá, že keď zmeníte prvý stĺpec, tak sa neaktualizuje aj druhý, ale musíte si vybrať z už korektných hodnôt podľa prvého. Vyskúšajte a pochopíte.

=IF(ISERROR(C1);"-";C1)
C1 je bunka s hodnotou/chybou. A nemohlo by to byť rovno v tej bunke ? Ak áno, tak to "C1" vo vzorci zamente za to čo je v tej konkrétnej bunke, ten vzorec čo tam v nej je.
teda napr. :
=IF(ISERROR(A1/B1);"-";A1/B1)
kde "A1/B1" je ten vzorec, čo Vám vytvára tú hodnotu/chybu

Maticový vzorec, C1 je tá hranica, A1:A29 je Váš rozsah hodnôt-
=IFERROR(INDEX($A$1:$A$29;SMALL(IF($A$1:$A$29>$C$1;ROW($A$1:$A$29));1));"")
Ak máte rozsah od iného riadku ako 1, tak potom treba upraviť vo vzorci referenčné hodnoty číslovania indexu. To -3 znamená, že sú pred Vašim rozsahom 3 riadky :
=IFERROR(INDEX($A$4:$A$32;SMALL(IF($A$4:$A$32>$C$1;ROW($A$4:$A$32)-3);1));"")
A ak hľadáte nie prvú hodnotu v stĺpci, ktorá presahuje hranicu, ale ak hľadáte prvú najnižšiu hodnotu presahujúcu hranicu :
=IFERROR(MIN(IF($A$4:$A$32>$C$1;$A$4:$A$32));"")

@habi : Veď som to napísal nad Vami, ale opakovanie je matka múdrosti. :)

No ak máte tie farby hodnôt vlastnoručne zafarbené (teda nie Formátom bunky, napr. kladné/záporné čísla), tak:
-Označte rozsah / alebo celý stĺpec
-Ctrl+F
-Formát - tá malá šípka - Vybrať formát bunky - klik na zafarbenú hodnotu
-Nájsť všetky
-klik do poľa s nájdenými a Ctrl+A
-Zavrieť
-A máte ich označené

Alebo
-na karte Data vytvorte Filter pre oblasť alebo stĺpec
-Filtrovať podľa farby - napr. červená
-vykonajte požadované úpravy, a filter zrušte, kliknutím na ikonu Filter

Ale ak to máte farbené pomocou Formátovania bunky/Podmieneného formátovania, tak asi jedine makrom (priradte napr. tlačítku):

Sub SelectColor()
Dim R As Range, C As Long, B As Range, A As Range
On Error GoTo koniec
Set R = Application.InputBox("Oblasť v ktorej chcete hľadať farbu :", "Označenie buniek podľa farby", Selection.Address, , , , , Type:=8)
C = Application.InputBox("Označte bunku s farbou, ktorá sa má hľadať :", "Označenie buniek podľa farby", Selection.Cells(1, 1).Address, , , , , Type:=8).DisplayFormat.Font.ColorIndex
On Error GoTo 0
For Each B In R
If B.DisplayFormat.Font.ColorIndex = C Then If A Is Nothing Then Set A = B Else Set A = Union(A, B)
Next B
A.Select
koniec:
End Sub

Máte asi pravdu, INDIRECT nejde takto použiť. Napadlo ma ExecuteExcel4Macro, ale to sa mi rovnako nedarí sprevádzkovať vo funkcii ale iba v procedúre. Vy ale potrebujete funkciu volanú zo zošitu, čiže posledná možnosť čo ma napadá je ADODB použité v UDF:
Function GetCell(F As String, H As String, C As Range) As String
Dim cnStr As String, rs As ADODB.Recordset, query As String
cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & F & ";Extended Properties=Excel 12.0"
query = "SELECT * FROM [" & H & "$" & C.Address(0, 0) & ":" & C.Address(0, 0) & "]"
Set rs = New ADODB.Recordset
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified
GetCell = rs.Fields(0).Name
rs.Close
End Function
Najskôr pridajte vo VBA v Tools - References - Microsoft ActiveX Data Objects 6.1.
Potom funkciu v zošite budete volať:
=GetCell("z:\Close\ZAKAZKOVE_LISTY.xlsx";$A$12;$N$6)

PS: A ešte ma napadlo použiť skrytý stĺpec/list, a vo funkcii vložiť vzorec s priamym odkazom a prečítať hodnotu, ale to sa mi rovnako vo funkcii nepodarilo, len v procedúre, viď vyššie...

Příspěvky uživatele

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje