Jak zrychlit práci/výkon excelu

[elninoslov]

Vyhľadanie 10 000 položiek v tých 3 listoch cez kolekciu trvá 0,5 sek. To len na ukážku, že to ide aj inak ako vzorcom. Kód som zatiaľ nepopisoval ani veľmi neladil, no myslím, že aj keď to vo finále urobím, väčšina ľudí mu neporozumie, a bude pre nich preto nepoužiteľný.

Ako písal "lubo.", vzorce treba vkladať hromadne, nie po bunke. Nepoužívať Copy, je to zbytočné. Radšej po vložení vzorcov použiť Value = Value. Tak či tak, počkáme si na prílohu...

[Reklama]

[VOM]

Děkuji za rady a přikládám zmenšený soubor s diskutovanými procedurami
Prosím o připomínky.
M.

[elninoslov]

Makro som prerobil, je zložitejšie o jeden malý cyklus, to nieje problém. Čo nedokážem ale pochopiť, sú podmienky vyhodnotenia. Čo a za akých okolností ide do I:I, J:J, K:K. Čo znamenajú "značky" v Z:Z a AA:AA? Prečo je J:J absolútne nekonzistentný ? Raz sú tam také vzorce, potom inakšie, a potom zadané hodnoty, pôvodné makro do neho taktiež vstupuje. Logika podmienenia výpisu "niečo CHYBÍ" mi tiež nieje celkom jasná. Čo sa týka rýchlosti, čo bola požiadavka, ide to ako blesk - stále 0,5 sek na 11 tisíc riadkov.

Skúste vysvetliť tie podmienky trochu bližšie. Kde má čo byť v akom prípade vypísané (stĺpce I, J, K), a na čo presne sú Z a AA. Prečo sa pri hľadaní v MAT zaokrúhľuje a pri ELE a GEAR nie?

Asi by to šlo aj komplikovanejším vzorcom, bez makra, ale keď som nejak nepobral tie podmienky, tak to bude len ďalšie mrhanie časom pred dovysvetlením.
Dovysvetlite.

Uvádzam len pracovnú verziu makra:

Kód: Vybrat vše

Sub Uprava()
Dim i As Long, r As Long, y As Integer, x As Integer, Radku As Long, Stlp As Byte, Data(), C(), E(), G(), Z(), V1(), V2(), M(8), MM(), Kat() As String, Col As New Collection, Item, bNalez As Boolean, Kurz As Single
   
    Kat = Split("mat,ele,gear", ",")
   
    'Príprava polí
    With wsSpecification
        Radku = .Cells(Rows.Count, 5).End(xlUp).Row - 8
        Select Case Radku
            Case Is < 1: MsgBox "Chybí data ve sloupci E:E.": Exit Sub
            Case 1: ReDim C(1 To 1, 1 To 1): C(1, 1) = .Cells(9, 3).Value
                    ReDim E(1 To 1, 1 To 1): E(1, 1) = .Cells(9, 5).Value
                    ReDim G(1 To 1, 1 To 1): G(1, 1) = .Cells(9, 7).Value
                    ReDim Z(1 To 1, 1 To 1): Z(1, 1) = .Cells(9, 26).Value
            Case Else:  C = .Cells(9, 3).Resize(Radku).Value
                        E = .Cells(9, 5).Resize(Radku).Value
                        G = .Cells(9, 7).Resize(Radku).Value
                        Z = .Cells(9, 26).Resize(Radku).Value
        End Select
        ReDim V1(1 To Radku, 1 To 1)
        ReDim V2(1 To Radku, 1 To 1)
        '.Cells(9, 9).Resize(Radku, 3).ClearContents
        Kurz = .Cells(1, "L").Value
    End With
   
    'Príprava kolekcie
    On Error Resume Next
    With wsMat_costs
        r = .Cells(Rows.Count, 1).End(xlUp).Row
         Data = .Cells(1, 1).Resize(r, 10).Value
        For i = 2 To 10
            M(i - 2) = Data(1, i)
        Next i
        For i = 2 To r
            If Not IsEmpty(Data(i, 1)) Then
                ReDim MM(8)
                For y = 2 To 10
                    MM(y - 2) = Data(i, y)
                Next y
                Col.Add MM, Kat(0) & "•" & Data(i, 1)
            End If
        Next i
    End With
   
    With wsElekdrives
        r = .Cells(Rows.Count, 1).End(xlUp).Row - 6
        Data = .Cells(7, 1).Resize(r, 11).Value
        For i = 1 To r
            If Not IsEmpty(Data(i, 1)) Then Col.Add Data(i, 11), Kat(1) & "•" & Data(i, 1)
        Next i
    End With
   
    With wsGearboxes
        r = .Cells(Rows.Count, 1).End(xlUp).Row - 2
        Data = .Cells(3, 1).Resize(r, 11).Value
        For i = 1 To r
            If Not IsEmpty(Data(i, 1)) Then Col.Add Data(i, 11), Kat(2) & "•" & Data(i, 1)
        Next i
    End With
   
    'Vyhľadanie v kolekcii
    For i = 1 To Radku
        If Not IsEmpty(E(i, 1)) Then
            bNalez = False
           
            For y = 0 To 2
                Item = Col(Kat(y) & "•" & E(i, 1))
                If Err.Number = 0 Then
                    If y = 0 Then   'Pri prvom hľadaní sa ešte dohľadá stĺpec
                        Stlp = 8
                        For x = 0 To 8
                            If MM(x) > G(i, 1) Then Stlp = x - 1: Exit For
                        Next x
                        V1(i, 1) = Round(Item(Stlp) / Kurz, 2)
                    Else
                        V1(i, 1) = Item
                    End If
                   
                    V2(i, 1) = "OK"
                    bNalez = True
                    Exit For
                Else
                    Err.Clear
                End If
            Next y
           
            If Not bNalez Then
                Select Case y
                    Case 0: If G(i, 1) > 0 And IsEmpty(Z(i, 1)) Then V2(i, 1) = Kat(0) & ". CHYBÍ"
                    Case 1: If G(i, 1) > 0 And IsEmpty(Z(i, 1)) And C(i, 1) = "motor type" Then V2(i, 1) = Kat(1) & ". CHYBÍ"
                    Case Else: If G(i, 1) > 0 And IsEmpty(Z(i, 1)) And C(i, 1) = "gearbox type" Then V2(i, 1) = Kat(2) & ". CHYBÍ"
                End Select
            End If
        End If
    Next i
   
    'Zápis výsledku
    wsSpecification.Cells(9, 9).Resize(Radku).Value = V1
    wsSpecification.Cells(9, 11).Resize(Radku).Value = V2
   
    Set Col = Nothing
End Sub

[VOM]

Pěkně zdravím
Protože makru Uprava moc nerozumím chtěl jsem jej otestovat, ale zhavarovalo hned na řádku 30.

Sub Uprava()
Dim i As Long, r As Long, y As Integer, x As Integer, Radku As Long, Stlp As Byte, Data(), C(), E(), G(), Z(), V1(), V2(), M(8), MM(), Kat() As String, Col As New Collection, Item, bNalez As Boolean, Kurz As Single

10 Kat = Split("mat,ele,gear", ",")

'Príprava polí
20 With wsSpecification
30 Radku = .Cells(Rows.Count, 5).End(xlUp).Row - 8
40 Select Case Radku

nyní popis mého makra.
Prohledá sloupec E a podle toho jaká je hmotnost ve sloupci G přiřadí kg cenu z odpovídajícího sloupce listu mat_costs a vloží ji do sloupce I, přitom ji převede z Kč/kg ne EUR/kg.

Potom prohledá sloupec E a vyhledá motor resp. fr. měnič a přiřadí cenu z listu elekdrives
pak to samé s převodovkou.

Hezký den
Milan

[Grimm]

Upřímně se mi to nechce moc studovat, ale dám krk na to, že zádrhel je v pojmenování listu. Elnino používá v kódu codename listu wsSpecification. Ty máš ve svém ukázkovém souboru u listu jménem "specification" codename List2 viz obr. Přejmenuj si list ať souhlasí se jménem použitým v kódu.

[VOM]

Díky, ale bez účinku.

[elninoslov]

Keď si stiahnete tú prílohu čo som dal, a spustíte "Uprava" dá chybu ? Mne nie.
Keď makro aplikujete na iný súbor, dá chybu ? Nemám ako otestovať.

Tie podmienky ste teda vôbec nevysvetlil. Ak sa pozriete do Vašeho makra, vidíte, že
-pri testovaní MAT sa zapisuje do stĺpca I:I nájdená hodnota, a do K:K chyba
-pri testovaní ELE sa zapisuje do stĺpca J:J nájdená hodnota, a do K:K chyba
-pri testovaní GEAR sa zapisuje do stĺpca J:J nájdená hodnota, a do K:K chyba

Výpis chyby je ale podmienený :
Pri MAT:
E<>"" - neprázdna bunka
G>0
Z<>"" - neprázdna bunka

Pri ELE:
E<>"" - neprázdna bunka
G>0
C="motor type"

Pri GEAR:
E<>"" - neprázdna bunka
G>0
C="gearbox type"

Ak nenájde ani v jednej kategórii, akú chybu má vypísať?
Ak nenájde ani v jednej kategórii, ale nebude splnená ani jedna trojica podmienok na výpis chyby, nemá vypísať chybu, aj keď nenašlo? Napr.
E bude hodnota
G bude >0
Z bude 0
C bude niečo iné ako "motor type" alebo "gearbox type"
Nechce sa mi rozmýšľať nad ďalšími eventuálnymi kombináciami.

Ďalej, môže nastať situácia, že sa daná položka nachádza na viac listoch ?

V stĺpci J:J sú vzorce aj hodnoty. Nedokážem identifikovať, čo ovplyvňuje (žeby nejakým záhadným spôsobom stĺpec AA:AA), či do stĺpca J:J treba vložiť hodnotu, alebo vzorec. V stĺpci J:J je niekoľko druhov vzorcov, podľa toho, aký typ údajov je napr. v E:E a D:D. Ak teda zmeníte hodnotu v E:E (rozpočet pre iného zákazníka), ako a kto zaistí, že bude výsledok v J:J správny (aký vzorec, kam, aká hodnota) ?

J61 =SUM(J63:J64)*B61
J64 = 650
J66 =SUM(J68)*B66
J78 =SUMIF($B85:$B125;">0";J85:J125)
J83 =J78-J110-J115

Nieje to náhodou tak, že sa do J:J makrom zapisuje, len ak je v C:C jedna z hodnôt "motor type"/ "gearbox type" / "convertor type" ? Čo ak zmažete/zmeníte toto "XY type", vzorec do J:J sa nedoplní.

A ďalšie a ďalšie veci.
...

Celé to je veľmi zložité a neprehľadné, ale každopádne sa nad tým máte zamyslieť Vy a vysvetliť to. Nie ja alebo ktokoľvek iný, aby sme skúmali čo to asi tak má robiť a za akých podmienok.

[VOM]

Děkuji za připomínky.
Podmínky chybových hlášek jsem opravil. Na řádcích 59, 64, 68 a 71 jsou schválně chyby a hlášky jsou OK.
V příloze soubor, kde v modulu 2 je vaše makro co mi hlásí tu chybu.
Hezký den
Milan

[VOM]

Zdravím a mám pokračující dotaz
Na novém NB (W10) s Excelem 2019 se to počítá cca 120 s.
Na starém NB (W7) s Excelem 2013 běží výpočet cca 20 s. Má někdo podobnou zkušenost prosím?
Milan

[atari]

O které proceduře píšeš?

[VOM]

Zdravím
To podle mě není podstatné. Ptám se zda máte také zkušenost, že nový excel pomalejší než starý nebo bude spíše chyba v HW.
M.

[atari]

Chtěl jsem to vyzkoušet, a potřebuji vědět jakou procedurou to mám otestovat. Já používám "krátké"procedury, takže to vyzkoušené nemám. A vymýšlet testovací proceduru se mi nechce.