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.5
Level 2.5
Příspěvky: 366
Registrován: červen 13
Pohlaví: Muž
Stav:
Offline

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...
Přílohy
Vyhľadávanie v Collection.xlsm
(392.31 KiB) Staženo 54 x

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

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.
Přílohy
Airs TEST 190813.xlsm
(155.48 KiB) Staženo 54 x

Uživatelský avatar
elninoslov
Level 2.5
Level 2.5
Příspěvky: 366
Registrován: červen 13
Pohlaví: Muž
Stav:
Offline

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: 114
Registrován: srpen 10
Pohlaví: Muž
Stav:
Offline

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 2
Level 2
Příspěvky: 162
Registrován: září 17
Pohlaví: Muž
Stav:
Offline

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.
Přílohy
Výstřižek.PNG

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

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.5
Level 2.5
Příspěvky: 366
Registrován: červen 13
Pohlaví: Muž
Stav:
Offline

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: 114
Registrován: srpen 10
Pohlaví: Muž
Stav:
Offline

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
Přílohy
Airs TEST 190813.xlsm
(154.84 KiB) Staženo 56 x

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

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 6
Level 6
Příspěvky: 3195
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

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: 114
Registrován: srpen 10
Pohlaví: Muž
Stav:
Offline

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 6
Level 6
Příspěvky: 3195
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

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
  • Sestava na práci do 18K
    od devak » 16 zář 2023 12:59 » v Rady s výběrem hw a sestavením PC
    1
    1533
    od newwes Zobrazit poslední příspěvek
    16 zář 2023 16:03
  • Notebook na práci do 20k
    od zero » 18 črc 2023 22:08 » v Rady s výběrem hw a sestavením PC
    1
    833
    od zero Zobrazit poslední příspěvek
    19 črc 2023 13:43
  • Výběr myši na práci
    od Edom » 23 lis 2023 23:00 » v Rady s výběrem hw a sestavením PC
    13
    1347
    od nl635 Zobrazit poslední příspěvek
    24 lis 2023 20:43
  • Židle pro hraní her ú práci u PC
    od Elar » 19 dub 2023 11:08 » v Rady s výběrem hw a sestavením PC
    3
    753
    od Pajus Zobrazit poslední příspěvek
    19 dub 2023 13:26
  • Import PDF do excelu Příloha(y)
    od luko02420 » 13 kvě 2023 11:29 » v Kancelářské balíky
    18
    3255
    od luko02420 Zobrazit poslední příspěvek
    16 kvě 2023 11:27

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

Kdo je online

Uživatelé prohlížející si toto fórum: Žádní registrovaní uživatelé a 5 hostů