Auteur Topic: Verticaal zoeken (gelezen 9624 keer)

danielle · « **Gepost op:** 18 april 2007, 11:25:36 »

Hallo,

Ik heb een vraagje over verticaal zoeken. In blad 1 heb ik artikelen staan maar zonder leverancier erbij. In blad 2 staan de leveranciers met de artikelnummers. Nu wil ik graag dat de leveranciers ook op blad 1 komen. Ik weet dat dit kan met ver.zoeken, maar ik krijg het niet voor elkaar.

Wie kan mij helpen? Ik heb ook even een voorbeeldje bijgevoegd.

Groetjes,

Danielle

SoftAid · « **Reactie #1 Gepost op:** 18 april 2007, 12:05:50 »

Hallo Danielle,

In blad 2 moet je even de kolommen Leverancier en Artnr. Leverancier van plaats verwisselen.
Dus Leverancier in kolom B en Artnr. Leverancier in kolom A.
Misschien dat het ook kan zonder die verwisseling, maar dan vind ik de juiste formule niet.

Daarna kan je in Blad1!G2 de volgende formule plakken:

=ALS(F2="";"";VERT.ZOEKEN(F2;Blad2!A2:B100;2;ONWAAR))

en deze formule doortrekken in de kolom G, voor het aantal rijen dat je denkt te gaan gebruiken. Je kan later die formule nog verder doortrekken.

VERT.ZOEKEN(F2;Blad2!A2:B100;2;ONWAAR))
=Vertikaal zoeken (Wat zoeken: de tekenreeks die in de eerste kolom van de matrix op Blad2!A2:B100 staat en overeen komt met die in Blad1!F2; waar zoeken: in de matrix op Blad2 bereik A2:B100; Wat moet in Blad1!G2 komen: de tekenreeks die in dezelfde rij als de gevonden tekenreeks (F2) maar in kolom2 van dit bereik staat; WAAR of ONWAAR): bij Onwaar moet de gevonden waarde exact overeen komen.)

Ik heb de formule "=VERT.ZOEKEN" genest in een "=ALS formule", zodat, indien er niets staat in de kolom F, er ook geen foutmelding komt in de bijbehorende rij in kolom G. (bij lege rijen)

Voorbeeld in bijlage: VERT.ZOEKEN.xls

SoftAid

danielle · « **Reactie #2 Gepost op:** 18 april 2007, 12:26:55 »

Oke bedankt.

Maar wat betekend eigenlijk #N/B kan die daar de leverancier niet van vinden, die staan er als het goed is wel bij.

Groetjes,

Danielle

SoftAid · « **Reactie #3 Gepost op:** 18 april 2007, 12:31:01 »

Hallo Danielle,

foutje van deze beginneling

Verander de formule in G2 van

Code: [Selecteer]

=ALS(F2="";"";VERT.ZOEKEN(F2;Blad2!A2:B100;2;ONWAAR))
naar

Code: [Selecteer]

=ALS(F2="";"";VERT.ZOEKEN(F2;Blad2!$A$2:$B$100;2;ONWAAR))
en trek ze dan door. De $-tekens zullen de matrix op A2:B100 houden bij het doortrekken. Nu worden ook die waarden veranderd bij het doortrekken. Sorry.

SoftAid

RedHead · « **Reactie #4 Gepost op:** 18 april 2007, 12:54:17 »

SoftAid,

Citaat

foutje van deze beginneling

Maar wel een hele toffe 'beginneling'...!

Dan over je oplossing.... Ik snap ff niet waarom je zou reageren op een lege cel in kolom F. Je kan in je Vlookup (vert.zoeken) ook beter kiezen voor een complete kolom dan in een voorgedefinieerd bereik. Het maakt je wat flexibeler in het aanvullen van je lookup-tabel. (maar dat is puur mijn persoonlijke mening EN... 'er zijn vele wegen die naar de oplossing leiden').

Danielle, Ik denk dat het beter is om met een IsError (isfout ?) functie te werken. Je test dan het gezochte op aanwezigheid. Is het gezochte niet aanwezig, zal Lookup een fout terug geven (#N/A; #N/B). Deze ondervang je dus met IsError...
Voorbeeldje van de formule in cel G2 (ff in het Engels, want het is weer ff vlug op m'n werk...)

Code: [Selecteer]

=IF(ISERROR(VLOOKUP(F2;Blad2!A:B;2;FALSE));"";VLOOKUP(F2;Blad2!A:B;2;FALSE))of

Code: [Selecteer]

=ALS(ISFOUT(VERT.ZOEKEN(F2;Blad2!A:B;2;ONWAAR));"";VERT.ZOEKEN(F2;Blad2!A:B;2;ONWAAR))Als je de bijlage opent, zal Excel de formule wel weer omzetten naar het Nederlands...

Groet, Leo

danielle · « **Reactie #5 Gepost op:** 18 april 2007, 12:56:53 »

Oke top! bedankt voor jullie hulp! Ik was er zelf nooit uitgekomen.

Groetjes,

Danielle

SoftAid · « **Reactie #6 Gepost op:** 18 april 2007, 13:52:46 »

Hallo Leo,

goede correctie, die hele kolommen opnemen in de matrix. Ik had er ook aan gedacht, maar niet doorgezet.

De isfout formule nesten in een =ALS formule is mijn sterktste punt niet, maar ik heb het nu wel bijgeleerd

SoftAid

spyder · « **Reactie #7 Gepost op:** 18 april 2007, 20:32:15 »

Hoi Leo,

Even off-topic

:

Citaat

beter kiezen voor een complete kolom dan in een voorgedefinieerd bereik

ALs je hele kolommen gebruikt, maakt dat dan niet dat het doorzoeken van die tabel dan veel langer duurt? $:-\$

spyder

RedHead · « **Reactie #8 Gepost op:** 18 april 2007, 20:54:29 »

Spyder,

Citaat

Even off-topic

Niet helemaal off-topic... Het hoort gewoon bij de algemene uitleg (zo, dat is óók weer geregeld

)

Ik doe mijn 'lookups' nooit anders dan op hele kolommen (of het moet net ff zo zijn dat er onder de bewuste lookuptabel nog wat andere info staat...) en heb nog nooit gemerkt dat het iets vertraagd. Ik weet trouwens niet of 'lookup' stopt als er geen gevulde cellen meer worden gevonden. Maar Excel is waarschijnlijk weer 'slimmer' als jij en ik denken...

Groet, Leo

EDIT: Bedankt voor de aanvulling (nederlandse code) in mijn bericht hier boven...

Erik Van Geit · « **Reactie #9 Gepost op:** 18 april 2007, 22:54:29 »

Hallo,

Hoe groter het zoekbereik, hoe trager. Je gaat dit natuurlijk pas merken, wanneer er veel formules van dat type gebruikt worden.
Als de data gesorteerd kunnen worden is VERGELIJKEN (met "criterium typegetal" niet 0, maar wel 1 of -1) ruim sneller (gemiddeld genomen).

even een voorbeeldje, hoe de computer "denkt"
zoek in deze tabel het getal 7
4
8
9
1
3
2
7
5
6
en nu hier
1
2
3
4
5
6
7
8
9

Je computer werkt op dezelfde manier. Als er geen aanwijzing is dat de data gesorteerd zijn, kan je machine ook niet veel aanvangen. Maar als je wel sorteert en het expliciet aan je PC "meedeelt" (via criterium typegetal), gaat het natuurlijk een pak sneller.
Neem een reeks van 1000 gegevens. Als deze gesorteerd zijn, doet de computer het volgende.
1. lees cel 500
2. te groot? lees cel 250, te klein ? lees cel 750
3. nog 250 mogelijke cellen over, weer in 2 te delen
4. nog 125
5. nog 63
6. nog 32
7. nog 16, 8, 4, 2
11. Je systeem heeft slechts een 10-tal cellen moeten checken om tot resultaat te komen.

Als je dus veel formules nodig hebt, doe je er goed aan om "Vergelijken" met dat extra argument (criterium typegetal) te gebruiken.

Om weer korter bij dit topic te komen. Je kan beter je bereik dynamisch definiëren.
https://www.datapigtechnologies.com/flashfiles/dynamicranges.html
In Excel 2003, kan je ook je data in een lijst converteren via menu Data|Lijst|Maak Lijst. Zo zal je nooit gegevens missen.
(NB: deze laatste techniek heb ik zelf nooit gebruikt: 'k heb geen 2003versie)

'k Heb voor de rest eigenlijk deze topic niet gevolgd en zal nu toch eens even naar het voorbeeld kijken...

beste groeten,
Erik

Erik Van Geit · « **Reactie #10 Gepost op:** 18 april 2007, 23:20:31 »

Hier mijn voorstel:

definieer namen
leveranciers
=VERSCHUIVING(Blad2!$A$1;0;0;AANTALARG(Blad2!$A:$A);1)
artikelnummers
=VERSCHUIVING(Blad2!$B$1;0;0;AANTALARG(Blad2!$B:$B);1)
of beter nog
=VERSCHUIVING(leveranciers;0;1)

NB: Hier worden ook volledige kolommen gebruikt, maar dat is slechts 1 keer: alle formules verwijzen naar hier. (De berekening moet slechts 1 keer gebeuren.)
De 2de formule voor de artikelnummers gebruikt simpelweg hetzelfde bereik als de leveranciers, maar dan 1 kolom naar rechts.

Formule in kolom G:
=INDEX(leveranciers;VERGELIJKEN(F2;artikelnummers;0))
Het leuke van deze aanpak is dat je ook vlug ziet wat er met die formule gebeurt: het is bijna Nederlands. Neem een zekere index (zoveelste item) in de leverancierslijst. Om dit itemnummer te weten zoeken we cel F2 op in artikelnummers.

   F                      G
1 Artikelnr. leverancier Leverancier
2 512000                 3M Nederland bv
3 4400014                3M Nederland bv
4 PC 1500                3M Nederland bv
5 8012 BLAUW             3M Nederland bv
6 153504                 Abbott
7 1955                   Abbott
8 9014 N                 Abbott
9 153504                 Abbott
10 31545                  Abbott
11 V474H                  Abbott

Blad1
[Table-It] version 06 by Erik Van Geit

Code: [Selecteer]

RANGE   FORMULA (1st cell)
G2:G11  =INDEX(leveranciers,MATCH(F2,artikelnummers,0))

[Table-It] version 06 by Erik Van Geit

Ik ben geen voorstander van formules als
=Als(ISNB(formule);"";formule)
Zo moet Excel immers 2 keer zoveel werk verrichten. Wat is er op tegen om #N/B in een cel te zien staan? Zo zie je tenminste duidelijk dat er wat aan de hand is, namelijk Niet Bepaald!

(by the way: als je wel deze langere formule wil, gebruik dan ISNB in plaats van ISFOUT: deze laatste uitdrukking heeft veel meer werk, want moet zoeken naar gelijk welke fout)

Als je persé een andere boodschap wil dan #N/B, kan je eigenlijk beter een extra kolom gebruiken.
1. Voeg een kolom in na G.
2. Verberg kolom G.
3. formule in kolom H
=ALS(ISNB(G2);"probleem !";G2)
Nu wordt die "zware formule" slechts 1 keer berekend.

Veel leesgenot en onderzoekplezier :-)
Erik

RedHead · « **Reactie #11 Gepost op:** 19 april 2007, 08:02:48 »

Erik, dat zijn inderdaad mooie methodes (met Offset (verschuiving)).
Ik moet zeggen dat ik zelf het snelste ff werk met (H & V)lookup om het gezochte snel te vinden in een andere tabel. Vervolgens copieeer ik die formules en plak de waarde als 'tekst' weer op dezelde plek terug. (vaak is het toch zo dat zo'n 'zoekactie' op een tabel slechts 1x en niet constant moet gebeuren dus is de extra rekenkracht voor Excel te verwaarlozen..). In het laatste geval lijkt mij het optuigen van 'naam-bereiken' e.d. een hele klus, maar voor een 'blijvende' zoekfuncie uiteraard prima!

Citaat

Wat is er op tegen om #N/B in een cel te zien staan?

Bij het zoeken van tekst is het inderdaad geen probleem. Als je daarintegen getalwaarden zoekt en die moet gebruiken in een sum (som) oid, dan loopt je formule ook uit op een #N/A (#N/B). Dus in die gevallen zou ik toch adviseren om met =Als(ISNB(formule);"";formule) te werken.. (een lege cel beïnvloed de som functie niet!). Wel een handige tip om dat met ISNA (isnb) te doen!

Al met al, weer bedankt voor je prima toevoegingen/lessen.

Groet, Leo

Erik Van Geit · « **Reactie #12 Gepost op:** 19 april 2007, 11:04:22 »

Leo,

Het is allemaal niet zo belangrijk zolang we over "slechts" 2000 cellen spreken. Het is wel goed om erop voorbereid te zijn, wanneer het wel belangrijk is. In het voorbeeld waar we mee bezig zijn, staat de zoekactie in een kolom: we weten natuurlijk niet hoeveel rijen. Het ziet er naar uit dat "besparen" op rekensnelheid een goede zaak is: dat soort mappen staat meestal vol formules. Niet te vergelijken met een eenmalige zoekactie.
Sommige dingen lijken een hele klus, als we ze nog nooit gedaan hebben. Het filmpje een paar keer bekijken, zo de functie "verschuiving" begrijpen, en er een beetje mee spelen, maakt het een vlotte operatie.

Je kan altijd de mogelijke aanwezigheid van fouten opvangen in de "tel"formules (som, gemiddelde, ...) in plaats van op elke regel de berekening 2 keer uit te voeren. #N/A, #N/B, #DEEL/0, ... hoeft dus naar mijn idee nergens een probleem te zijn.

beste groeten,
Erik

Help!

Menu

Hulp bij posten

Zoeken

Recente topics