Excel adatok összefésülése
2008-09-13T13:55:48+02:00
2008-09-14T16:53:13+02:00
2022-07-29T23:15:30+02:00
jaky
Sziasztok!

Van egy terméklistám excelben, szerteném egy másik lista alapján az árakat frissíteni benne a termékek kódja alapján.

Az eredeti listában a kódok az "O" oszlopban vannak, az árak az "E" oszlopban.

A másik listából a kódokat beillesztettem az "R" oszlopba, a friss árakat pedig mellé, az "S" oszlopba.

Nálam itt állt meg a tudomány.
Tudna valaki segíteni, h. hogyan tudnám rávenni, hogy az "S" oszlop alapján befrissítse az árakat az "E" oszlopban, úgy, hogy azoknál a termékeknél, ahol nem egyezik v. nincs kód, maradjon meg a régi ár?
Előre is 1000 köszönet a segítségért!
Mutasd a teljes hozzászólást!
Egy új oszlopba:

=ha(vagy(kod0<>kod2 ; ar2=""); ar1;ar2)
(Az oszlopok betüjelzéseit helyettesítsd be)

Majd utána a kapott eredmény oszlopot másolod, és az árak oszlopra Szerkesztés > Irányított beillesztés > Értéket

---

Ha a kódok nem bizutos, hogy olyan sorrendben vannak, a két listában, akkor a régi ár és az új ár egymásmellé rakásához:
=fkeres(termékkod, R:s,2,0)
És az új ár:
=ha(hibás(FkeresEredmény); RégiÁr;ÚjÁr)

Ezt végigmásolod, majd ezzel árral felülírhatod a régi-t. (ld. fent)
Mutasd a teljes hozzászólást!

  • Kedves Micu!

    Köszönöm a segítséget! Érzem én a jószándékot, de ez nekem úgy kínai, ahogy van!
    Mit nevezünk pl. kod0-nak és kod2-nek, stb.
    Meg tudnád adni a képletet az általam használt betűk alapján?

    Sajnos a kódok biztos, h. nem ugyanabban a sorrendben vannak a két listában. Sőt a frissebb listában nincs is meg minden, ami a régiben.

    Köszönöm a türelmedet!

    Üdv.
    Mutasd a teljes hozzászólást!
  • Const UJMUNKALAP = "Sheet1" 'munkalap neve, ahol az uj arak es kodok vannak Const UJAR_OSZLOP = "S" 'az uj arakat tartalmazo oszlop Const UJKOD_OSZLOP = "R" 'az uj kodokat tartalmazo oszlop Const MUNKALAP = "Sheet1" 'munkalap neve, ahol az eredeti arak es kodok vannak Const AR_OSZLOP = "E" 'az eredeti arakat tartalmazo oszlop Const KOD_OSZLOP = "O" 'az eredeti kodokat tartalmazo oszlop Sub cserebere() Dim i As Integer Dim kod As String Dim c As Range i = 2 Do While Worksheets(UJMUNKALAP).Range(UJKOD_OSZLOP & i) <> "" kod = Worksheets(UJMUNKALAP).Range(UJKOD_OSZLOP & i) For Each c In Worksheets(MUNKALAP).Range(KOD_OSZLOP & ":" & KOD_OSZLOP) If c.Text = kod Then Worksheets(MUNKALAP).Range(AR_OSZLOP & c.Row) = _ Worksheets(UJMUNKALAP).Range(UJAR_OSZLOP & i) Exit For End If Next i = i + 1 Loop End Sub

    Sejtem, hogy nem tudod, hogy mi is az a macro.
    Na akkor, Excelben alt + F11, Insert menü | Module és a létrejött lapra másold be ezt a kódot. Ha magyar Exceled van akkor a
    Const UJMUNKALAP = "Sheet1" 'munkalap neve, ahol az uj arak es kodok vannak Const MUNKALAP = "Sheet1" 'munkalap neve, ahol az eredeti arak es kodok vannak
    sorokat módosítsd valahogy így:
    Const UJMUNKALAP = "Munka1" 'munkalap neve, ahol az uj arak es kodok vannak Const MUNKALAP = "Munka1" 'munkalap neve, ahol az eredeti arak es kodok vannak

    Miután kész vagy, a kurzorral állj rá a Sub cserebere() sorra és nyomd meg az F5-öt. Nézd meg a munkalapodat.
    Kellemes hétvéget.

    ---
    A biztoságod kedvéért egy másolaton próbálgasd, ezt ugye nem is kell mondani.
    Mutasd a teljes hozzászólást!
  • Bocs, de ezt ugye nem gondolod komolyan:

    Do While Worksheets(UJMUNKALAP).Range(UJKOD_OSZLOP & i) <> ""
    kod = Worksheets(UJMUNKALAP).Range(UJKOD_OSZLOP & i)
    For Each c In Worksheets(MUNKALAP).Range(KOD_OSZLOP & ":" & KOD_OSZLOP)


    1. Olyat VB-ben megírni, ami függvényt tartalmaz az Excel (Application.vlookup), vagy műveletként (Find) benne van.

    2. Ráadásul dupla ciklussal csinálni, és a belsőben meg 65536 vagy 1 millió cellát vizsgálni.

    3. Olyan feladatra, amit 2 képlettel és 2 másolással meg lehet oldani, ahhoz minek a program?
    Mutasd a teljes hozzászólást!
  • Naaa, csak szeretem a VBA-t!

    ---
    1. Igazad van.
    2. Mondjuk ezt vártam is. De ez csak azért van, mert nem tudom, hogy folytonosan vannak-e az új adatok ( nem derült ki kérdésből ).
    Na, de hogy folytassam:
    3. Mert a prog.hu-n vagyunk.
    Mutasd a teljes hozzászólást!
  • Próbáld csak ki amit írtam! Meglátod, hogy jó lesz. Úgyis egyszerűbb egy gombnyomással elintézni, mint másolgatni ( ahogy Micu mester mondja ), ugye azzal tévedni is lehet.
    A kód amit én írtam, nem téved.
    Mutasd a teljes hozzászólást!
  • 2.
    folytonosan vannak-e az új adatok

    És ha vannak? Feldolgozás közben csak nem jönnek. Feldolgozás elött le kell kérdezni, hány sor van.
    Mutasd a teljes hozzászólást!
  • Feldolgozás közben csak nem jönnek

    Ilyenre gondoltam:
    ertek kod ertek kod <ures sor> ertek kod <ures sor> ertek kod
    Mutasd a teljes hozzászólást!
  • Kipróbáltam pár termékkel, működik. Két gond van vele csak.
    A nagyobbik gond, h. a teljes 25000 soros adatbázissal már órák óta játszik a gépem. A másik, h. az első sorban lévő adatot nem másolja át. Nincs valami egyszerű és gyors megoldás?
    Mindenesetre köszönöm a fáradozásodat!
    Mutasd a teljes hozzászólást!
  • Végétől (65000 vagy 1 millió) felfel megnézed, hogy hol az utolsó.
    Mutasd a teljes hozzászólást!
  • Nem értesz:
    <ures sorok> 16524. sor: adat kod <ures sorok> 20000. sor: adat kod <ures sorok> 32541. sor: adat kod <ures sorok>
    Így mi lesz? Tudom, hogy túlzás, de ilyesmire ( is ) gondoltam. Próbálgattam VLOOKUP-al is és nem gyorsabb azzal sem, mint az én kódommal. Persze a kódomat egy kicsit átalakítottam úgy, hogy nem direkt a cellákon végzem a műveteteket, hanem egy tömbön:
    tomb = Worksheets(MUNKALAP).Range(KOD_OSZLOP & "1:" & KOD_OSZLOP & "25000")
    Persze a többi oszlopot is így olvasom be, majd a végén visszaírom az oszlopba a tömböt.
    Na, így már nagyságrendekkel gyorsabb. 4-5 perc alatt megvan.
    A VLOOKUP gondolom kb. azt csinálja amit én is írtam: sorra veszi a
    cellákat
    amíg megtalálja azt ami kell.
    Mutasd a teljes hozzászólást!
  • Azért a te mintádban se mindegy, 65536 vagy csak 32541-et kell átnézni.

    Az igazi sebesség különbség akkor lesz látható, ha sok olyan tétel van, ami nem található. Mert ha megtalálható akkor mindegy, hogy végigmenne az összes soron, vagy megállna, mert a találatnál úgy is leáll.

    De ha nincs, akkor a oszlop:oszlop vagy 30000 sorral többet kell hogy megnézzen, mintha leállna a végén.

    Ezzel teszteltem (A *1 a képletben azért van benne, mert ahol keresni kell, az szám, míg amit keresni kell az szöveg. Lehetne val() is, de nem mérvadó, hiszen mind a kettőben ugyan az van.):


    Sub tied() Const UJMUNKALAP = "Munka1" 'munkalap neve, ahol az uj arak es kodok vannak Const UJAR_OSZLOP = "S" 'az uj arakat tartalmazo oszlop Const UJKOD_OSZLOP = "R" 'az uj kodokat tartalmazo oszlop Const MUNKALAP = "Munka1" 'munkalap neve, ahol az eredeti arak es kodok vannak Const AR_OSZLOP = "E" 'az eredeti arakat tartalmazo oszlop Const KOD_OSZLOP = "O" 'az eredeti kodokat tartalmazo oszlop k = Time Dim i As Integer Dim kod As String Dim c As Range i = 2 Do While Worksheets(UJMUNKALAP).Range(KOD_OSZLOP & i) <> "" Application.StatusBar = i kod = Worksheets(UJMUNKALAP).Range(KOD_OSZLOP & i) * 1 For Each c In Worksheets(MUNKALAP).Range(UJKOD_OSZLOP & ":" & UJKOD_OSZLOP) If c.Text = kod Then Worksheets(MUNKALAP).Range(AR_OSZLOP & i) = _ Worksheets(UJMUNKALAP).Range(UJAR_OSZLOP & c.Row) Exit For End If Next i = i + 1 Loop v = Time Range("u1") = v - k Range("U1").NumberFormat = "h:mm:ss.00" End Sub Sub enyem() Const UJMUNKALAP = "Munka1" 'munkalap neve, ahol az uj arak es kodok vannak Const UJAR_OSZLOP = "S" 'az uj arakat tartalmazo oszlop Const UJKOD_OSZLOP = "R" 'az uj kodokat tartalmazo oszlop Const MUNKALAP = "Munka1" 'munkalap neve, ahol az eredeti arak es kodok vannak Const AR_OSZLOP = "E" 'az eredeti arakat tartalmazo oszlop Const KOD_OSZLOP = "O" 'az eredeti kodokat tartalmazo oszlop k = Time Set keres = Range(UJKOD_OSZLOP & 1 & ":" & Range(UJAR_OSZLOP & Rows.Count).End(xlUp).Address) i = 2 Do While Worksheets(UJMUNKALAP).Range(KOD_OSZLOP & i) <> "" Application.StatusBar = i kod = Worksheets(UJMUNKALAP).Range(KOD_OSZLOP & i) * 1 e = Application.VLookup(kod, keres, 2, 0) If VarType(e) <> vbError Then Worksheets(MUNKALAP).Range(AR_OSZLOP & i) = e End If i = i + 1 Loop v = Time Range("u2") = v - k Range("U2").NumberFormat = "h:mm:ss.00" End Sub

    "O" (keresendő) oszlop (2500-ig másolva)
    0000030056664
    0000030056671
    0000030059764
    5998172500143
    5998172500150

    Amiben meg kell találni (1 hiányzik a listából):
    30056671
    5998172500143
    30056664
    5998172500143
    30059764

    Majd hogy mind meglegyen beletettem (bár a feladat szerint lehet, hogy nincs minden benne):
    5998172500150

    Amikor minden benne volt (tehát sose nézte át a 65536 sort), akkor a tied 5 mp, az enyém 3 mp alatt futott le. Amikor volt ami nincs meg, akkor a tied nem volt türelmem megvárni

    Tapasztalatom szerint a vlookup kicsit gyosrabb, mint a ciklikus keresés.
    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