» »

VBA Excel in iskanje enakih vrednosti med listi

VBA Excel in iskanje enakih vrednosti med listi

borzon ::

Pozdravljeni,

Naprosam vas za pomoc pri mojem neznanju.
V Excelu imam list1 in na tem listu imam v stolpcu A mešane datume v formatu 20110101, 20200102, 20300120... itd. Ob tem stolpcu imam se stolpec B z nekimi vrednostimi in ti podatki so vezani na ta datum.
Na listu2 imam v stolpcu A in C ter E ponovno mešane datume (pomešane med seboj v 3 stolpcih) kot na listu1 v stolpcu A.
Na listu2 imam ravno tako ob stolpcu A, stolpec B z nekimi podatki, ki so ravno tako vezani na datum v stolpcu A, ob stolpucu C spet neke podatke...
Zanima me, kako naj napišem kodo, da bi program oziroma vba samodejno prekopiral iz lista2 vrednosti na list1 v stolpec C podatke, ki so vezani na isti datum.
Hocem torej, da exel na obeh listih poisce vse enake datume in pripadajoce podatke ob datumih iz lista 2 prekopira k istemu datumu(v novo kolono) na list 1.

Verjetno sem malo zakompliciral razlago.... upam, da ste razumeli ... vsakrsnakoli pomoč je več kot dobrodošla.

kljuka13 ::

Najenostavnejša rešitev za ta problem je uporaba funkcije VLOOKUP. V celico C1 na Listu 1 zapiši formulo
=VLOOKUP(A1;List2!$A$1:$B$4;2;FALSE)

in nato to kodo z dvoklikom majhen črni kvadratek v desnem spodnjem kotu celice prekopiraj v vse vrstice celice.

 Excel

Excel



Enako nato storiš še za svoje preostale stolpce s podatki, tako da ustrezno prilagodiš območja. Več o funkciji VLOOKUP si lahko prebereš v dokumentaciji.

smetko ::

Kljuka13 ti je podal že uporabno rešitev, dodajam pa še nekaj izboljšav.
1. Če ne želiš gledati #N/A pri vrednostih, katere ne najde, potem ovij lookup z funkcijo IFNA(vlookup(...);"vrne če ne najde").

2. V primeru da si lastiš novejšo verzijo excela, poglej tudi funkcijo XLOOKUP, katera je malo bolj flexibilna in ima že vgrajeno funkcionalnost IFNA funkcije.

Omejitev obeh funkcij je, da vrneta samo prvi podatek, katerega najdeta v območju. Če imaš v datumu dvojnike, se zadeve zakomplicirajo, vendar tudi za to obstajajo rešitve. Tudi vlookup se da zamenjati z parom INDEX MATCH.

Najbrž si že opazil, da se v excelu da veliko stvari narediti brez makrojev. Sam se jih izogibam, če imam možnost rešiti problem na drugačen način. Želim pa ti veliko uspehov pri spoznavanju excela. Pa še mala opomba: zadnji parameter v funkciji vlookup mora biti false, kati če ga izpustiš, vrača čudne rezultate, posebno če nimaš podatkov urejenih po velikosti.
No comment

prtenjam ::

smetko je izjavil:

KPa še mala opomba: zadnji parameter v funkciji vlookup mora biti false, kati če ga izpustiš, vrača čudne rezultate, posebno če nimaš podatkov urejenih po velikosti.

Tule bi samo opozoril, da je stvar povsem definirana in zadnji parameter je privzeto TRUE. Torej če zadnjega parametra ne, podate je privzeto TRUE, kar pa ima neko posledico.

TRUE namreč ne pomeni več natančnega ujemanja, temveč delno ujemanje in spet je res, da je v tem primeru nujno imeti podatke sortirane. Primer uporabe funkcije VLOOKUP s TRUE parametrom si lahko ogledate tukaj.

Reči samo želim, da rezultati niso "čudni" so povsem definirani in da, v konkretnem primeru, uporabnik mora uporabiti FALSE.
Matjaž Prtenjak
https://mnet.si

smetko ::

Se strinjam z Prtenjam, da so definirani, posebno če so urejeni. Sam sem to nekoč uporabil pri preračunavanju na takratne Nemške marke, glede na tečajno listo, katera pa se je dopolnjevala 1 x tedensko. Tudi če podatki niso urejeni, se rezultati predvidljivi. Da so "čudni" sem bolj mislil s stališča uporabnika, ko na to naleti prvič in iščeš vzroke. Potem ko ugotoviš vzrok, tudi za nazaj odkriješ, da je vse logično.
Sem pa v začetni fazi imel kar nekajkrat težave, ko sem pri hitenju na to pozabil. Če pogledamo nadaljnji razvoj funkcij kot so match in xlookup se opazi, da je Microsoft spremenil privzete vrednosti iz približnega ujemanja v natančno ujemanje, kar nakazuje, da je s tem imelo problem kar nekaj uporabnikov.
Vendar to so bolj finese za "Guruje" tvojega kova, kot za začetnike. Uporabil sem ta izraz, ker se mi ni dalo razlagati podrobnosti in brez da bi o tem malo bolj razmislil.
No comment

borzon ::

Pozdravljeni vsi skupaj.
Vsem se iskreno zahvaljujem za vašo požrtvovalnost in pomoč pri moji težavi.

Šele danes sem zaradi pomanjkanja časa preizkusil vaše nasvete. Kot sem videl, se malo narobe zastavil vprašanje, zato vas naprošam za ponovno pomoč. V prvem sporočilu sem napisal, da mora excel sam pregledovat na vsakem listu po en stoplpec in stolpca med seboj primerjati in prekopirati vrednost. No tule sem naredil napako.
Želel bi, da excel na vsakem listu samodejno išče enake zapise v dveh stoplcih.
Na listu 1 v stolpcu A imam datum v formatu 20200101, 20300505, ... v B stolpcu pa uro v formatu 110, 310, 710,... v tretjem C stolpcu pa neke podatke.
Enako je na na drugem listu 2, kjer imam v A stolpcu mečane datume v enakem formatu kot na listu 1, v b stoplpcu ure v enakem formatu kot na slitu 1 v C stolpcu pa podatke ki bi jih moral kopirat na prvi list. Potem imam v E stolpcu spet leta, F stolpcu spet uro in G spet podatke ki bi jih moral kopirat.
Skratka, želel bi, da excel samodejno dobi enake pare datumov in ur (ki sta v 2 različnih stolpcih na istem listu1) in prekopira iz lista 2 nek podatek ob enekem datumu in uri.

Spet sem zakompliciral z razlago ... Se opravičujem in upam da ste razumeli.

hvala in lep dan še naprej.

kuall ::

=INDIRECT("sheet2!"&ADDRESS(MATCH(A1,Sheet2!A:A),3))

kuall ::

to išče samo po ujemanju datuma, za uro boš moral še malo pomigat sam

smetko ::

Najprej malo kritike tvojega početja:
Že v naslovu nakazuješ, kako misliš da se problem reši, nakar ko ti podamo rešitve, povsem spremeniš zahteve.
Hkrati pa pričakuješ, da ti bodo drugi reševali tvoj problem pro-bono. Obrni se raja na profesionalce, kateri vedo kaj delajo. Eden od njih se je oglasil tudi v tej temi.
@kuall: Tvoja rešitev ni najbolj optimalna, ker je funkcija INDIRECT volatile. To pomeni da se preračunava pri vsaki spremembi katerekoli celice. Zato sem sam tudi opustil funkcijo offset, katero bi lahko tudi uporabili za ta namen in sicer v paru offset match. Namesto tega se uporablja index match par, oziroma Xlookup pri novejših excelih. Več si preberi pri mojem prvem odgovoru.
Pa še smer, kako se zadeve lotiti, da ne bo samo pridiga: Ena od možnosti je uporaba Sumproduct .
No comment

kuall ::

Smetko seveda formule preračunavajo, zato pa so formule. Če nočeš računanja sprogramiraš macro in preračuna samo, ko klikneš gumb.

Za macro se da najti rešitev na netu, ker je problem pogost.
ampak po moje op itaq rabi preračunavanje in je to zaželjen feature ne pa buk.

Zgodovina sprememb…

  • spremenilo: kuall ()

prtenjam ::

Če me že 'Smetko' hvali (PS: Hvala za pohvale!), se pač moram še enkrat oglasiti ;)

Ugotovili smo že, da je najprimernejša funkcija za iskanje funkcija VLOOKUP (ali novejša izvedenka XLOOKUP) in zato jo je vedno smiselno uporabiti, če se le da, saj je najpreprostejša.
Malce več lahko postorite z uporabo kombinacije funkcij INDEX in MATCH, vendar je tukaj že potrebno malce več znanja.
Največ pa lahko dosežete z uporabo mrežnih funkcij, vendar tu je potrebnega še več znanja.

Če ostanemo torej raje pri VLOOKUP, ki je trivialna za uporabo. Težava, ki jo imate vi, je v dejstvu, da zna VLOOKUP iskati samo po enem stolpcu! Rešitev: Iz dveh stolpcev naredite enega in iščite po tem stolpcu.

Če imate torej datum v stolpcu B in urov stolpcu C, ju združite v en stolpec
=B2 & "_" & C2

To naredite na vseh tabelah - v vašem primeru na vseh treh listih in potem lahko, za iskanje med tabelami, uporabite najpreprostejšo funkcijo izmed vseh - VLOOKUP

PS: Lahko pa za združevanje tabel uporabite moje brezplačno orodje MExcel, ki sem ga sicer že davno upokojil in ga ne razvijam več; deluje pa seveda še. Omenjenega orodja pa ne razvijam več, saj ima Excel v novih verzijah čudovito orodje za združevanje tabel in še 1000 drugih stvari. Orodje sliši na ime Power Query in je res močno, zahteva pa seveda nekaj učenja ;)
Matjaž Prtenjak
https://mnet.si

smetko ::

@kuall: Že v prvem odgovoru sem nakazal, da se makrojem izogibam, in za to je glavni razlog v tem, da makroji zahtevajo v fazi razvoja veliko več časa in znanja kot funkcije, posebno če želiš dobro testirati. Drugi razlog pa je v tem, da sem pripravljen pomagati, da se vsi kaj naučimo, nisem pa pripravljen podpirati lenobe. Odzval sem se šele po dveh "grehih" in to sta bila: uokvirjanje rešitve ter spreminjanje parametrov potem, ko je rešitev že bila podana. To nakazuje na to, da se je nekdo premalo pripravil oziroma podcenjuje naš čas. Je pa to, na žalost, pogosta napaka v informatiki, saj se premalo časa posveti dizajnu in posledično se projekti podražijo. Iz tvoje podane rešitve tudi sklepam, da kar nekaj veš ter razmišljaš s svojo glavo, vendar ti manjka kilometrina. Sam sem v začetku delal podobne napake, in zato ti poskušam pomagati, da postopoma preideš na višji nivo, kot so meni nekoč pomagali drugi, saj se iz svojih napak največ naučimo.

@prtenjam: Tudi s tem odgovorom dokazuješ, da se ne motim, posebno ker sva že oba videla na tem forumu, da so določene funkcije (kot primer predlagana funkcija z moje strani) pretežke za začetnike. Zato je zanje tvoj pristop primernejši. Vendar tudi tu vidim malo izboljšavo, katera pri samem iskanju ne pripomore veliko, lahko pa pripomore pri nadaljnjem preračunavanju časa. Glede na to, da je prvi stolpec datum drugi pa ura, bi pretvoril vse v številko, katero excel uporablja za prikazovanje oziroma računanje časa. Morda sem tudi v tem delu malo prezahteven za navadne uporabnike, vendar se podobne tehnike shranjevanja časa uporablja na veliko področjih kot so različni programski jeziki, baze podatkov, ... Je pa sama zasnova zelo enostavna: celi del števila predstavlja število dni od nekega datuma ( pri excelu 1 = 1.1.1990), decimalni del pa število sekund od polnoči naprej. To pa potem poenostavi računanje z časovnimi spremenljivkami. Je pa ta razlaga bila namenjena bolj drugim, saj sem skoraj prepričan, da sam to zelo dobro poznaš. Upam, da mi bo uspelo še kdaj te izvati, da podaš svoj pogled, saj s tem bogatiš debato kot tudi kvaliteto tega foruma:).

Vsekakor pa upam, da bo nekoč vsaj eden od teh, katerim sva pomagala do sedaj, prišel do naprednega znanja excela, in da bo tako nagrajeno prizadevanje vseh nas, kateri skušamo prenesti znanje, katerega smo pridobili do sedaj. Žal pa opažam, da je takih zanesenjakov na tem forumu čedalje manj.
No comment

prtenjam ::

@smetko: V to dabato se lahko vključim s podrobnim prikazom kako Excel "vidi" datume in kako "vidi" čas. Moram pa reči, da v tem konkretnem primeru občasni uporabnik Excela ne pridobi veliko če obe polji združi v pravo časovno polje, saj je potrebno vpisati kar zapleteno formulo, da iz celic '20200305' in '1204' dobiš pravo datumsko vrednost (če se nisem kje zatipkal):
=DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2))+TIME(LEFT("0"&B2;2);RIGHT(B2;2); 0)


V glavnem povprečni uporabnik Excela lažje razume formulo
=A2&"_"&B2
. Redki sicer potem vprašajo zakaj je vmes podčrtaj in vem da bodo ti nekoč še razumeli Excel ;). V tem konkretnem primeru je podčrtaj sicer brezvezen, a v realnosti, v podjetjih, uporabniki večkrat združujejo neke nize in brez podčrtaja se sledeči nizi združijo v isti rezultat:
"A" & "AB" ==> "AAB"
"AA" & "B" ==> "AAB"

kar pa v splošnem seveda ni pravilno in zato vmes podčrtaj. Kot rečeno v tem primeru kjer imamo datum (vedno 8 mest) + čas (3 ali 4 mesta) je to brezveze, a je takšna formula bolj splošna, pa naj bo vmes še podčrtaj.
Matjaž Prtenjak
https://mnet.si

smetko ::

@prtenjam: vedno se kaj novega naučim. Pri začetnem datumu excela sem se zatipkal in je prava številka 1900 in ne 1990. Čas pa kot vidim, malo drugače računa, kot sem imel v mislih. Morda sem prehitro sklepal iz tega, kako drugi sistemi uporabljajo čas, oziroma so kaj spreminjali takrat, ko so pri MS spreminjali zapis datuma. Vsekakor priznam, da sem v tem delu učil krivo vero.
Tudi z drugim delom se s teboj strinjam, vendar so me do sedaj izkušnje naučile, da se pogosto zgodi, ko uporabniki imajo časovne spremenljivke, hočejo z njimi kaj počet, kot so iskanje razlik v datumih, prištevanje dni k datumu, oziroma malo bolj napredni celo iskati število delovnih dni med datumi. Vse to pa je lažje, če si že v začetku pripravljen na to. Tudi uporaba "_" je popolnoma smiselna, oziroma namesto njega katerikoli znak, kateri se ne pojavi v besedilu, vendar je "_" poleg praznega prostora (" ") najbolj pregleden. Vsekakor hvala, da si nam razširil obzorja, saj kot sem delaj, se iz svojih napak največ naučimo, in to velja tudi zame.
No comment

prtenjam ::

smetko je izjavil:

Vsekakor priznam, da sem v tem delu učil krivo vero.

Nap, vse je tako kot pišeš

smetko je izjavil:

Je pa sama zasnova zelo enostavna: celi del števila predstavlja število dni od nekega datuma ( pri excelu 1 = 1.1.1990), decimalni del pa število sekund od polnoči naprej.

To je namreč čisto res, razen da si se zmotil v letu, pač 1900 oz. 1904
Matjaž Prtenjak
https://mnet.si

borzon ::

Vsem se iskreno zahvaljujem za vašo pomoč. Z vašo pomočjo mi je dejansko uspelo. Uporabil sem funkcijo vlookup in predhodno zdužil dve celici.
Stvar deluje in to je glavno ... imam pa že novo tezavo in bom kar odprl novo temo.

Hvala vsem še 1x

smetko ::

@prtenjam:
Vsekakor priznam, da sem v tem delu učil krivo vero.
Tu sem mislil na del, kako podaja čas (uro, minuto, sekundo), in je v excelu tako, kot je opisana v tvoji povezavi. Sem zamešal s sistemi, kjer je ta podatek direktno za sekunde oziroma mili sekunde, ..., oziroma če se še prav spomnim na Unix, kjer je celo celi del število sekund po določenim datumom (imajo zato težavo z prestopnimi sekundami).
No comment

prtenjam ::

smetko je izjavil:

@oziroma če se še prav spomnim na Unix, kjer je celo celi del število sekund po določenim datumom (imajo zato težavo z prestopnimi sekundami).

Da, UNIX sistemi imajo datum/čas predstavljen kot pretečeno število sekund od 1.1.1970 in da, pri tem prestopne sekunde niso upoštevane. Vendar za povprečne uporabnike prestopne sekunde sploh niso problem, večji problem je dejstvo, da čas predstavlja 32 bitna spremenljivka in slednja se bo leta 2038 obrnila okoli ;)

Vendar pa moje tudi to sploh ne bo problem, saj tukaj govorimo o 32 bitnih sistemih do leta 38 bomo pač imeli že 64bitne. Zna pa biti problem v kakšnih vgrajenih sistemih, ki nastajajo danes, so 32 bitni in naj bi živeli kakšnih 20 let... naj bi ... ti pa imajo težavo.
Matjaž Prtenjak
https://mnet.si


Vredno ogleda ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

pomoč excel (strani: 1 2 )

Oddelek: Programska oprema
585318 (895) -JM-
»

excel - pomoč

Oddelek: Programiranje
381139 (470) korenje3
»

Pomoč Excel

Oddelek: Programska oprema
135754 (5441) mk766321
»

excell formule

Oddelek: Programska oprema
181672 (1374) ZePe
»

Pomoc pri Excelu

Oddelek: Programska oprema
181619 (1614) jerneju

Več podobnih tem