VBA optimalizálása vagy Acces-ben SQL lekérés

VBA optimalizálása vagy Acces-ben SQL lekérés
2014-04-30T19:00:00+02:00
2014-05-12T19:24:55+02:00
2022-12-01T01:41:51+01:00
*deleted_17225596
Az csatolt fájlban látható, hogy milyen szűrést szeretnék végrehajtani. Ez jól működik, de mivel az Excel nem kifejezetten arra való - most már tapasztalom és írtátok is - Access-ben szeretnék hasonló kérést készíteni.

1. Mint látjátok legalább kétszer végig halad a sorokon, erre van-e más megoldás - próbáltam osztállyal készített "collection"-ba menteni a sorokat, de azon is végig kell sétálni többször, bár gyorsabb valamivel. Az eredeti kódban háromszor ennyi van és 1.4e3-szer több sor, írták hogy szakaszoljam, de az is csak fél megoldás.

2. Lehet SQL-kérést adni, ami hasonlóan működik, mint a "GetNyitas"/"GetMelyik" függvények, azaz lehet-e tömbben keresni SQL-lel?

Bármilyen alternatívára nyitott vagyok, válaszotok előre is köszönöm.
Mutasd a teljes hozzászólást!
Csatolt állomány
Szia!

Az előző hozzászólásomat pontosítom:
Az init eljárásban fel kell venned egy változót, ami azt mutatja, hogy hol jársz az adatállományban.
Én ezt vsor- nak neveztem el.
Ezután az init eljárásban a hívások a kövekezők:
(Figyelem, a sorrendet megfordítottam - nem véletlenül!)
      melyik = GetMelyik(Cikkszam, Nev, vsor)
      nyitas = GetNyitas(Cikkszam, Nev, vsor)

 A GetMelyik függvény így néz ki:

Private Function GetMelyik(ByVal Cikkszam As String, ByVal Nev As String, ByVal k As Single) As String
  Dim LastRow As Single, i As Single
  GetMelyik = "NINCS" ' alapertelmezett ertek megadasa
  With WS ' objektum hozzaferese
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row ' utolso sor keresese
    For i = k To LastRow ' ciklus futtatasa az utolso sorig adott oszlopban
      If (Cikkszam = .Cells(i, 1)) And (Nev = .Cells(i, 2)) Then ' ha a cikkszam es a nev egyezik az argumentumban megadottal
        If (.Cells(i, 4) = "MINDKETTO") Then
          GetMelyik = "EGYIK|MASIK": Exit Function ' visszateresi ertek megadasa
        ElseIf (InStr(.Cells(i, 4), "MASIK") > 0) Then ' ha talalat erteke nagyobb mint 0, akkor van benne
          GetMelyik = "MASIK": Exit Function
        ElseIf (.Cells(i, 4) = "EGYIK") Then
          GetMelyik = "EGYIK": Exit Function
        End If
     Else
        Exit For
      End If
    Next i
  End With

A GetNyitas pedig így:

Private Function GetNyitas(ByVal Cikkszam As String, ByVal Nev As String, ByRef vsor As Single) As String
  Dim LastRow As Single, i As Single
  Dim nyitas As Variant
  ReDim nyitas(0) ' ures tomb letrehozasa
  With WS
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = vsor To LastRow
      If (Cikkszam = .Cells(i, 1)) And (Nev = .Cells(i, 2)) Then
        If Not InArray(nyitas, .Cells(i, 3)) Then AddToArray nyitas, .Cells(i, 3) ' ha nincs a tombben a keresett elem, akkor tegyuk be az adott tombbe
      Else
          Exit For
      End If
    Next i
  End With
  GetNyitas = GetPrimaryNyitas(nyitas)
  Set nyitas = Nothing
  vsor = i
End Function

Üdv.
Mutasd a teljes hozzászólást!

  • Az SQL adatbázisban (táblákban) keres. Semmi köze a tömbökhöz.

    Ha egy rekordot meg akarsz találni, akkor a lekérdezés végére egy
    ... Where mező="érték" and mező2 ="érték2"
    forma kell.

    Ekkor leszűri (kigyűjti) azokat a rekordokat, amik megfelelnek a feltételednek, és ha így végiglépegetsz rajtuk (vagy betöltöd egy tömbbe, vagy cellákba) akkor máris használhatod.
    --
    Az IIF nem már, mint az Excel Ha függvény megfelelője.
    ---

    Excelben miért nem használod az autószűrést és másolást, vagy az irányított szűrést.
    Mutasd a teljes hozzászólást!
  • Gondolhatod, ha inkább írok szkriptet, akkor az előző nem megy - ha meg tudod mutatni hogyan kell és azt egy átlagos felhasználó is tudja alkalmazni, akkor megkaptam a választ, hajlamos vagyok túlkomplikálni a dolgokat.

    Ám optimalizálásra nem tudsz mondani valamit - 147e3 sorról van szó és 20 perc legalább míg lefut?
    Mutasd a teljes hozzászólást!
  • Szerintem próbáld ki a szűrést, és ha gyorsabb, akkor rögzítsd le makróba.

    --
    Az Access lekérdezést meg az Access lekérdezés rácsában kellene összekattintgatni, és ha jó, akkor annak az sql nézetében lévő kódot felhasználni.

    De akkor vagy minden t át kellene tenni Access-be, vagy meg kellene nézned a két alkalmazás közötti kommunikáció lehetőségeit. (Vagy legalább az ADO/DAO programozását)
    Mutasd a teljes hozzászólást!
  • :) azzal indult az egész tavaly, hogy makrókat rögzítgettem, majd belemerültem a VBA-ba.

    Igen ez a lenne a következő ötlet, hogy adatbázisba menteni és csak a szükséges adatokat kérni célzott query-vel.

    Tehát szummázva a dolgot, nem lehet ennél kevesebb futással azt, amit szeretnék.

    Bármire jutok ide is megírom.
    Mutasd a teljes hozzászólást!
  • úúú, valami transzponálás féle jutott eszembe lekérdezésekhez

    cikkszam | nev | ... | EGYIK | MASIK | MINDEGYIK | ...
    1000000 | sdf | ... | 0 | 0 | 1 | ...

    talán ezzel közelebb állok, ez a group by adhatja a megoldást.
    Mutasd a teljes hozzászólást!
  • Szia!

    A mintát átnézve a következők a kérdéseim:

    1.Jól értem-e hogy a NYITAS és a MELYIK mezőknél prioritás van a kiválasztandó értékek között (NYITAS esetén MIND-STAT-EGY, MELYIK esetén MINDKETTO,MASIK,EGYIK,NINCS)? Ez még megoldható lenne a rendezéshez egyéni listát használva!
    2.De a kiválasztásnál viszont nem egy konkrét rekordot választasz, hanem egy újat hozol létre, amely megfelel az 1. pont szerinti feltételeknek az adott ciksszám és szállító rekordjaiból kiválasztva - lásd 10000001 C MIND MASIK - mivel ilyen egyedi rekord nincs?
    Ezt a kiválasztást már valóban vizsgálat alapján lehet megoldani.

    3. Nem egészen értem, hogy cikkszámra és névre sorbarendezett tételeknél miért kezded mindig előlről a NYITAS és MELYIK oszlop vizsgálatát. Így teljesen feleslegesen nézed át az adatállomány 99%-át rengetegszer.Szerintem elég lenne csak azt a kis szeletet átnézni, ami az adott cikkszámhoz tartozik.

    Üdv.
    Mutasd a teljes hozzászólást!
  • Törölve.
    Mutasd a teljes hozzászólást!
  • #3. Nem egészen értem, hogy cikkszámra és névre sorbarendezett tételeknél miért kezded mindig előlről a NYITAS és MELYIK oszlop vizsgálatát. Így teljesen feleslegesen nézed át az adatállomány 99%-át rengetegszer.Szerintem elég lenne csak azt a kis szeletet átnézni, ami az adott cikkszámhoz tartozik.

    Na, erre voltam kíváncsi, hogy mit ronthatok el. Hogyan gondolod?
    Mutasd a teljes hozzászólást!
  • Szia!

    Például így:
    Private Function GetNyitas(ByVal Cikkszam As String, ByVal Nev As String, ByVal k As Single) As String
    és
    For i = k To LastRow
          If (Cikkszam = .Cells(i, 1)) And (Nev = .Cells(i, 2)) Then
            If Not InArray(nyitas, .Cells(i, 3)) Then AddToArray nyitas, .Cells(i, 3) ' ha nincs a tombben a keresett elem, akkor tegyuk be az adott tombbe
          Else
            Exit For
          End If
    next i

    Továbbá megpróbálhatnád a getnyitas és a getmelyik függvényt egybe dolgozni, hiszen ugyanazokból a rekordokból kell dolgozniuk, miért kell 2 x végigmenni??

    Ezen túl én jobbnak látnám a ciklusváltozókat long-nak definiálni (ami 32 bites integer).

    Üdv.
    Mutasd a teljes hozzászólást!
  • Szia!

    Az előző hozzászólásomat pontosítom:
    Az init eljárásban fel kell venned egy változót, ami azt mutatja, hogy hol jársz az adatállományban.
    Én ezt vsor- nak neveztem el.
    Ezután az init eljárásban a hívások a kövekezők:
    (Figyelem, a sorrendet megfordítottam - nem véletlenül!)
          melyik = GetMelyik(Cikkszam, Nev, vsor)
          nyitas = GetNyitas(Cikkszam, Nev, vsor)

     A GetMelyik függvény így néz ki:

    Private Function GetMelyik(ByVal Cikkszam As String, ByVal Nev As String, ByVal k As Single) As String
      Dim LastRow As Single, i As Single
      GetMelyik = "NINCS" ' alapertelmezett ertek megadasa
      With WS ' objektum hozzaferese
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row ' utolso sor keresese
        For i = k To LastRow ' ciklus futtatasa az utolso sorig adott oszlopban
          If (Cikkszam = .Cells(i, 1)) And (Nev = .Cells(i, 2)) Then ' ha a cikkszam es a nev egyezik az argumentumban megadottal
            If (.Cells(i, 4) = "MINDKETTO") Then
              GetMelyik = "EGYIK|MASIK": Exit Function ' visszateresi ertek megadasa
            ElseIf (InStr(.Cells(i, 4), "MASIK") > 0) Then ' ha talalat erteke nagyobb mint 0, akkor van benne
              GetMelyik = "MASIK": Exit Function
            ElseIf (.Cells(i, 4) = "EGYIK") Then
              GetMelyik = "EGYIK": Exit Function
            End If
         Else
            Exit For
          End If
        Next i
      End With

    A GetNyitas pedig így:

    Private Function GetNyitas(ByVal Cikkszam As String, ByVal Nev As String, ByRef vsor As Single) As String
      Dim LastRow As Single, i As Single
      Dim nyitas As Variant
      ReDim nyitas(0) ' ures tomb letrehozasa
      With WS
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = vsor To LastRow
          If (Cikkszam = .Cells(i, 1)) And (Nev = .Cells(i, 2)) Then
            If Not InArray(nyitas, .Cells(i, 3)) Then AddToArray nyitas, .Cells(i, 3) ' ha nincs a tombben a keresett elem, akkor tegyuk be az adott tombbe
          Else
              Exit For
          End If
        Next i
      End With
      GetNyitas = GetPrimaryNyitas(nyitas)
      Set nyitas = Nothing
      vsor = i
    End Function

    Üdv.
    Mutasd a teljes hozzászólást!
  • Szia!

    Szerintem még tudnál gyorsítani az eljárásodon, ha a kiválogatandó adatállományodat is betennéd egy tömbbe.
    Kb. így:
    option base 1
    public adatok()

    Az init eljárásban pedig
    redim adatok(ws.range("A1").currentregion.rows.count,ws.range("A1").currentregion.columns.count)
    adatok=ws.range("A1").currentregion.value

    A függvényeidben/de az init eljárás további részében is/ a .cells(i,3) stb. helyett az adatok(i,3) stb. értékekre kell hivatkozni.

    Viszont az inarray és addtoarray függvényed meghívása előtt az adott tömbértéket egy változóba kell tenni.

    Üdv.
    Mutasd a teljes hozzászólást!
  • Nagyon jokat irtal. Erre lenne szuksegem mindig, hogy el is mondja valaki mi helyes es milyen iranyba kell menni. Erre voltam kivancsi, hogy hogyan tudom beeroltetni a keresbe azt hog ne kelljen tobbszor atnezni mint ahanyszor kell. Tegnap az jutott eszembe hogy a keresett majd megtalalt sorokat torlom - majdnem az amit irtal de attol messze all :) az utobbi irasoddal par kerdes folmerult bennem, ha az elozoket ertelmeztem folteszem neked a kerdeseim.

    Koszonom a korrekt valaszt!
    Mutasd a teljes hozzászólást!
  • A valaszoddal nagyon sok kerdesemre erkezett valasz, olyanra is, amit nem is kerdeztem.

    Am szeretnek meg kerdezni igy alvas elott: utana olvastam a szamtipusoknak, eddig long-ot hasznaltam csak nem ertettem minek nagyobb szamot lefoglalni a memoriaban - azert vannak alapveto hianyossagaim! -, de igy CPU oldalrol mar ertem hogy a 32 bit miert jobb. Kell e a valtozok vegere a tipusjelzo &, !, % stb. illetve erdemes ebb egy sorban dimenzionalni a valtozokat tipusonkent vagy soronkent. Szamit e ez meg az optimalizalasban.
    Jo e egyaltalan, hogy igy szetbontom a metodusokat vagy eleg egy szubrutin es abba belehanyni egyszerre.
    Tenyleg jobb e osztalykent elerni mondjuk a cellak regiojat vagy eleg, ha helyileg tombbol keresem.
    Referenciat erdemes adni vagy createobject metodussal szepen meghivni, ami kell - hasonloan a js-hez.
    Mutasd a teljes hozzászólást!
  • Szia!


    Nem biztos, hogy minden kérdésedre tudom a választ. Ha excel VBA-t használsz, akkor
       - a változók deklarálása akkor kötelező, ha a modul elejére beírod az option explicit utasítást (ami egyébként erősen ajánlott). Ez azt jelenti, hogy minden változót deklarálni kell az első használat előtt.
       - a deklarálás többféle módon történhet, de a változók típusát mindig meg kell adni vagy a típusjelzővel vagy az As kulcsszó után. Ha nem adsz meg tipust a változónak akkor az alapból variant lesz - ami memóriaigény szempontjából nem feltétlenül hatékony.
       - a különböző számtípusok "értelmezési tartománya" is más lsd. integer vs  long. Mindkettő egész, de a long sokkal nagyobb értéket képes felvenni mindkét irányban.
       - a változók deklarálása hány sorban történjen - ez ízlés dolga, kinek hogyan tetszik, mint írtam, minden változóhoz hozzá kell írni a típusát, nincs csoport deklaráció.
       - érdemes a tömbök deklarációját is átnézned a helpben (dinamikus, fix)
    Az én gyakorlatom nem elég az osztályok használatával kapcsolatos kérdéseid megválaszolására, de vannak itt a fórumon tapasztaltabb résztvevők is. Tedd fel szerintem külön topikban az erre vonatkozó kérdést.


    Amit viszont tudok, függvényt akkor érdemes csinálni, ha sokszor kell ugyanazt a feladatot elvégezni (de nyilván a függvény nem feltétlenül osztály).
    Továbbá: a memóriában levő műveletek -akár tömbökkel - mindig összehasonlíthatatlanul gyorsabbak, mint a cellákkal való műveletek!
    Nemrég egy majdnem a tiedhez hasonló feladatban több mint 900 ezer sort - 8 oszlopot kellett átnézni és egyedi szempontok alapján kiválogatni.
    Ugyanazzal a metodikával és logikával végrehajtva a cellákon végzett műveletekkel közel 3 órát futott a program, a tömbökkel pedig 30 másodpercig! Ez azért nem elhanyagolható különbség azt hiszem. Ebben az esetben csak a memória méret korlátozza, hogy mekkora cellatartományt tud hiba nélkül beolvasni.
    Ugyanakkor az excelnek van egy sor olyan eszköze/tudása amit direkt arra a feladatra optimalizáltak és ezért sokkal gyorsabb ha rábízzuk, hogy megcsinálja (pl. sorbarendezés, szűrés, stb.), mintha a magunk algoritmusával próbálkozunk. (Na majd jön valaki és jól megcáfol ebben is...)

    Ha az excelen belül dolgozol, az excel VBA minden eszköze rendelkezésedre áll, nem kell semmilyen más módon hivatkozni rá.

    Remélem, sikerült kielégítően válaszolni a kérdéseidre. Ha valami nem lenne tiszta, írj bátran, reggel frissen, kipihenten lehet érthetőbb leszek.

    Üdv.
    Mutasd a teljes hozzászólást!
  • Jo e egyaltalan, hogy igy szetbontom a metodusokat

    Jó, mert könnyebb tesztelni.


    "Kell e a valtozok vegere a tipusjelzo &, !, %"

    Nem célszerű, inkább szövegesen definiáld a típusát.


    Referenciat erdemes adni vagy createobject metodussal szepen meghivni, ami kell - hasonloan a js-hez.


    Referenciát érdemes megadni, mert akkor kapsz segítséget a gépeléskor, használhatod a beépített állandókat, és (kapásból) használhatod az ott definiált függvényeket

    CreateObject metodussal érdemes meghívni, mert akkor (ha csak nem használsz "új" elemet), akkor verziófüggetlen lesz.

    :)
    Tehát mind a kettőnek van előnye és hátránya is.
    Mutasd a teljes hozzászólást!
  • Nagyon koszonom a segitseget, nehezen erthetoek ezek a leirasok mert ki mit talal jonak, de irtam par hosszu ciklust es bizonyitgattam magamnak.
    Micu: ezer koszonet, sokat jelent ez, mert nem tanultam ezeket es tapasztalataid jol jonnek.
    Fferi: atgondoltam a kiolvasast, hasznaltam a tippjeid es lass csodat a husz/harminc percbol lett egy/ketto. Am meg mindig a leggyorsabb a sajat magam altal irt "distinct" fuggveny, ami cellakon megy vegig, nem ertem de mar nem ez a fontos.
    Hetvegere befejezem es majd privatban elkuldom nektek lassatok mirol van szo, titkokat nem teszek kozze. Amolyan koszonet.
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd