Forrige tråd
Neste tråd
Print tråd

Excelformler

Excelformler #2043617 22/12/2015 19:44
Registrert: Aug 2006
Innlegg: 2,270
K1neth Offline OP
Ivrig
OP Offline
Ivrig
Registrert: Aug 2006
Innlegg: 2,270
O store forum.

Jeg sitter med to forskjellige xls filer og skulle søkt opp informasjon i fil 1 som skal hentes ut i fil 2.

Test1.xls Test2.xls
Artikelnr Varenavn Artikkelnr Varenavn
11,11 Arne K 11,12
11,12 Per B 11,13
11,13 Finn K 11,11

Det er varenavnet i test2.xls som skal søkes opp i test1.xls
Lar dette seg gjøre ? Det er ca 2500 linjer så en formel som kunne kjøres hele veien ut hadde vært topp.

Redigert av K1neth; 22/12/2015 19:44.
Re: Excelformler [Re: K1neth] #2043633 22/12/2015 21:01
Registrert: Aug 2011
Innlegg: 1,502
E
eirikbk Offline
Ivrig
Offline
Ivrig
E
Registrert: Aug 2011
Innlegg: 1,502
Så lenge du har en verdi som er unik i begge excelfilene kan du bruke vlookup (no: finn.rad) mot disse.
Anbefaler i dette tilfellet at du legger inn hele kolonnen og ikke bare celleområdet i andre ledd av formelen som benevner "table_array" (no: matrise). Dvs f.eks. C:C i stedet for C2:C2500, som er området man skal kjøre lookup i.

Re: Excelformler [Re: K1neth] #2043646 22/12/2015 22:12
Registrert: Aug 2006
Innlegg: 2,270
K1neth Offline OP
Ivrig
OP Offline
Ivrig
Registrert: Aug 2006
Innlegg: 2,270
=Finn.rad(A2;[test2]ark1"$A$1:%E;1;SANN)

Jeg får feilmelding på denne...

Re: Excelformler [Re: K1neth] #2043650 22/12/2015 22:56
Registrert: May 2011
Innlegg: 85
S
Skjervesbu Offline
Medlem
Offline
Medlem
S
Registrert: May 2011
Innlegg: 85
Bruk index & match istedenfor vlookup.

Post et bilde som viser cellene så får du et svar rimelig raskt, tenker jeg.

Re: Excelformler [Re: K1neth] #2043684 23/12/2015 08:15
Registrert: Aug 2013
Innlegg: 86
S
Søgnerider2 Offline
Medlem
Offline
Medlem
S
Registrert: Aug 2013
Innlegg: 86
Slik bruker jeg Finn.rad:

Ark1: lag tabell av listene: CTRL+T, merk av dersom tabellen har overskrifter.

Ark 2:
formel i B2: blir da: Finn.Rad(A2;Tabell1;2;0)

Forsøk på enkel forklaring:
A2(finn verdi tilsvarende A2),
Tabell1(alle tabeller i arbeidsboken får et navn, dette kan du selv endre under utforming. Første tabell får automatisk Tabell1.
2(hent verdi i kolonne to tilsvarende verdi du fant i første kolonne)
0; nøyaktig treff kreves.

I Finn.Rad formel er det viktig at verdien som skal matches i ark1 står i første kolonne.

Håper dette er til litt hjelp.

mvh Morten

Re: Excelformler [Re: K1neth] #2043694 23/12/2015 09:17
Registrert: Aug 2011
Innlegg: 1,502
E
eirikbk Offline
Ivrig
Offline
Ivrig
E
Registrert: Aug 2011
Innlegg: 1,502
Sitat: K1neth
=Finn.rad(A2;[test2]ark1"$A$1:%E;1;SANN)

Jeg får feilmelding på denne...


Ser ut som diverse skriveleifer i formelen. Har du skrevet alt for hånd, eller klikket på aktuelle celler og celleområder?

Prøv å endre til:
=Finn.rad(A2;[test2.xls]ark1!$A:$E;5;USANN)

Jeg må gjøre noen antagelser rundt arkene dine, men formelen over betyr at:
- celle A2 (i test1?) inneholder en ref som er unik og som også finnes i test2 et sted
- kolonne A i test2 inneholder de samme unike referansene som i kolonne A i test1
- den verdien som skal hentes fra test2 befinner seg i kolonne E
- ref forrige punkt, så må man endre til 5, ikke 1, i tredje ledd av formelen. Det betyr at den skal hente fra den femte kolonnen i området du har merket (refererer ikke til kolonne E i seg selv, men til den femte kolonnen i det faktiske merkede området som er benevnet i ledd to)
- USANN fordi det skal være eksakt match
- tok inn .xls til filnavnet. mulig du har .xlsx fil

Noen har nevnt å bruke =MATCH formelen i stedet. Det er muligens metodologisk korrekt, men jeg har benyttet VLOOKUP (finn.rad) en årrekke uten problemer.

Re: Excelformler [Re: Skjervesbu] #2043696 23/12/2015 09:27
Registrert: Sep 2013
Innlegg: 1,898
EHAa Offline
Ivrig
Offline
Ivrig
Registrert: Sep 2013
Innlegg: 1,898
Sitat: Skjervesbu
Bruk index & match istedenfor vlookup.

Post et bilde som viser cellene så får du et svar rimelig raskt, tenker jeg.


Stemmer også for denne. Ha begge regnearkene åpne og prøv følgende:

=Match(cellen som inneholder varenummer1;hele kolonnen i det andre regnearket som inneholder alle varenummer2)

=Index(hele kolonnen i det andre regnearket som inneholder varenavn 2; cellen der du har skrevet inn match-formelen)

PS: Ingen kvalitetssikring på formlene her, bare "hoderegning"...

Re: Excelformler [Re: eirikbk] #2043697 23/12/2015 09:29
Registrert: Sep 2013
Innlegg: 1,898
EHAa Offline
Ivrig
Offline
Ivrig
Registrert: Sep 2013
Innlegg: 1,898
Sitat: eirikbk
Sitat: K1neth
=Finn.rad(A2;[test2]ark1"$A$1:%E;1;SANN)

Jeg får feilmelding på denne...


Ser ut som diverse skriveleifer i formelen. Har du skrevet alt for hånd, eller klikket på aktuelle celler og celleområder?

Prøv å endre til:
=Finn.rad(A2;[test2.xls]ark1!$A:$E;5;USANN)

Jeg må gjøre noen antagelser rundt arkene dine, men formelen over betyr at:
- celle A2 (i test1?) inneholder en ref som er unik og som også finnes i test2 et sted
- kolonne A i test2 inneholder de samme unike referansene som i kolonne A i test1
- den verdien som skal hentes fra test2 befinner seg i kolonne E
- ref forrige punkt, så må man endre til 5, ikke 1, i tredje ledd av formelen. Det betyr at den skal hente fra den femte kolonnen i området du har merket (refererer ikke til kolonne E i seg selv, men til den femte kolonnen i det faktiske merkede området som er benevnet i ledd to)
- USANN fordi det skal være eksakt match
- tok inn .xls til filnavnet. mulig du har .xlsx fil

Noen har nevnt å bruke =MATCH formelen i stedet. Det er muligens metodologisk korrekt, men jeg har benyttet VLOOKUP (finn.rad) en årrekke uten problemer.


Såvidt jeg husker krever vel denne at verdiene er sortert i stigende rekkefølge? Det er ikke alltid at det passer. Ved å bruke match/index slipper man dette kravet..

Redigert av EHAa; 23/12/2015 09:30.
Re: Excelformler [Re: EHAa] #2043705 23/12/2015 09:51
Registrert: May 2011
Innlegg: 85
S
Skjervesbu Offline
Medlem
Offline
Medlem
S
Registrert: May 2011
Innlegg: 85

Re: Excelformler [Re: Skjervesbu] #2043707 23/12/2015 09:59
Registrert: Sep 2013
Innlegg: 1,898
EHAa Offline
Ivrig
Offline
Ivrig
Registrert: Sep 2013
Innlegg: 1,898
Jeg prøvde for enkelhets skyld å holde formlene separat, fordi jeg vet at Kenneth er ei kløne i Excel. Men det blir jo enda mer elegant når man gjør som i eksempelet ditt, og smeller alt inn i 1 operasjon (uten mellomregninger..). Enig i framgangsmåten, ville holdt meg unna lookup, fordi det fort baller seg til når ting ikke er riktig sortert.

Re: Excelformler [Re: EHAa] #2043736 23/12/2015 11:24
Registrert: Aug 2006
Innlegg: 2,270
K1neth Offline OP
Ivrig
OP Offline
Ivrig
Registrert: Aug 2006
Innlegg: 2,270
Et eller annet sted i mellomregningen skjedde det noe galt.

Her er mine celler. Varebeskrivelse skal fra beskrivelse til varenavn i test1

Vedlegg
Skjermbilde 2015-12-23 kl. 12.21.51.png (194.5 KB, 83 nedlastinger)
Redigert av K1neth; 23/12/2015 11:25.
Re: Excelformler [Re: K1neth] #2043761 23/12/2015 12:47
Registrert: Aug 2013
Innlegg: 86
S
Søgnerider2 Offline
Medlem
Offline
Medlem
S
Registrert: Aug 2013
Innlegg: 86
=FINN.RAD(B2;[test2.xlsx]Ark1!$A$1:$E$6;5;0)

Denne formelen får deg dit du vil..

Skrives da inn i kolonne C

for enkelthets skyld, lagde jeg bare 6 rader i test2, og 3 rader i test1, hadde ikke tid til mer. Det betyr at området du skal henvise i $A$1:$E$6 må utvides til å dekke hele datalisten, så lenge du ikke velger å lage tabell av data i test2.

Får du det ikke til, og dersom det ikke er sensitive data, så kan du evt. sende PM om epostadressen min, så kan jeg lage formel rett fila for deg.

mvh Morten

Redigert av Søgnerider2; 23/12/2015 12:54.
Re: Excelformler [Re: K1neth] #2043789 23/12/2015 16:37
Registrert: Feb 2012
Innlegg: 83
K
Kilometerslukern Offline
Medlem
Offline
Medlem
K
Registrert: Feb 2012
Innlegg: 83
Vlookup/finn.rad krever ikke sortering så lenge du bruker eksakt treff (false/usann i siste ledd).

Som tidligere nevnt er det lurt å alltid bruke hele kolonner som matrise. Dvs =FINN.RAD(B2;[test2.xlsx]Ark1!A:E;5;USANN)

Re: Excelformler [Re: Kilometerslukern] #2043790 23/12/2015 16:46
Registrert: Jun 2005
Innlegg: 166
S
SturlaA Offline
Tørr bak øra
Offline
Tørr bak øra
S
Registrert: Jun 2005
Innlegg: 166
Det er "usann" som gjør at du slipper å sortere. Finn.rad løser dette.

Re: Excelformler [Re: SturlaA] #2043804 23/12/2015 18:36
Registrert: Jul 2006
Innlegg: 5,584
erikskon Offline
Søk hjelp!
Offline
Søk hjelp!
Registrert: Jul 2006
Innlegg: 5,584
Vlookup kombinert med usann trikset burde fikse dette. Har i alle fall brukt ulike kombinasjoner av Vlookup på mye større datamengder enn det som er nevnt her. Kan også legge til at det å bruke labels, altså at du navngir de data son skal gjennomsøkes og returneres, kan gjøre det hele enklere.

Re: Excelformler [Re: K1neth] #2043811 23/12/2015 18:44
Registrert: Sep 2013
Innlegg: 1,898
EHAa Offline
Ivrig
Offline
Ivrig
Registrert: Sep 2013
Innlegg: 1,898
Sitat: K1neth
Et eller annet sted i mellomregningen skjedde det noe galt.

Her er mine celler. Varebeskrivelse skal fra beskrivelse til varenavn i test1


Funker for meg..


Moderator  support 

Terrengsykkel utgis av Fri Flyt AS | Postboks 4767 Nydalen, 0421 Oslo | Tlf: 22 04 46 00
Ansvarlig redaktør: Erlend Sande | Redaktør: Kristoffer Kippernes Utstyrsredaktør: Øyvind Aas
Salg og marked: Alexander Hagen | Daglig leder: Audun Holmøy Røhrt
Nettsiden er utviklet av Fri Flyt Byrå