Jak zrychlit práci/výkon excelu

Programy pro práci v kanceláři (Word, Excel, Access…=>Office)

Moderátor: Mods_senior

Uživatelský avatar
elninoslov
Level 2
Level 2
Příspěvky: 211
Registrován: červen 13
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod elninoslov » 13 srp 2019 14:51

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...
Nemáte oprávnění prohlížet přiložené soubory.



Reklama
VOM
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 13 srp 2019 15:53

Děkuji za rady a přikládám zmenšený soubor s diskutovanými procedurami
Prosím o připomínky.
M.
Nemáte oprávnění prohlížet přiložené soubory.

Uživatelský avatar
elninoslov
Level 2
Level 2
Příspěvky: 211
Registrován: červen 13
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod elninoslov » 19 srp 2019 11:09

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
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 20 srp 2019 15:06

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

Uživatelský avatar
Grimm
Level 1
Level 1
Příspěvky: 80
Registrován: září 17
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod Grimm » 20 srp 2019 16:06

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.
Nemáte oprávnění prohlížet přiložené soubory.

VOM
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 21 srp 2019 16:19

Díky, ale bez účinku.

Uživatelský avatar
elninoslov
Level 2
Level 2
Příspěvky: 211
Registrován: červen 13
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod elninoslov » 21 srp 2019 18:18

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
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 22 srp 2019 07:54

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
Nemáte oprávnění prohlížet přiložené soubory.

VOM
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 04 říj 2019 14:40

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

Uživatelský avatar
atari
Level 5
Level 5
Příspěvky: 2079
Registrován: říjen 08
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod atari » 04 říj 2019 14:53

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

VOM
Level 1.5
Level 1.5
Příspěvky: 110
Registrován: srpen 10
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod VOM » 10 říj 2019 07:35

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.

Uživatelský avatar
atari
Level 5
Level 5
Příspěvky: 2079
Registrován: říjen 08
Pohlaví: Muž

Re: Jak zrychlit práci/výkon excelu

Příspěvekod atari » 10 říj 2019 08:49

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.


  • Mohlo by vás zajímat
    Odpovědi
    Zobrazení
    Poslední příspěvek
  • Nízký výkon Ryzen5-2600 v Excelu
    od bugrpartak » 18 úno 2019 21:24 » v Problémy s hardwarem
    6
    610
    od Zivan
    19 úno 2019 22:02
  • výkon jako když není výkon hry chybuji
    od czkof15 » 25 bře 2019 21:25 » v Hry
    31
    3001
    od czkof15
    19 dub 2019 14:03
  • Jak zrychlit extrakci nebo komprimování z winraru nebo do winraru?
    od Neznalec911 » 31 kvě 2019 21:48 » v Rady s výběrem hardwaru a sestavením PC
    3
    392
    od ITCrowd
    01 čer 2019 10:36
  • WiFi v práci
    od Vampirus » 24 dub 2019 19:44 » v Sítě - hardware
    3
    678
    od mmmartin
    25 dub 2019 08:59
  • Výběr monitoru k práci
    od Edom » 13 pro 2018 16:18 » v Vše ostatní (hw)
    31
    1694
    od vuLva
    18 pro 2018 19:19

Zpět na “Kancelářské balíky”

Kdo je online

Uživatelé prohlížející si toto fórum: CommonCrawl [Bot] a 1 host