verticaal- of horizontaalzoeken in Excel

 

Verticaal- of horizontaal zoeken in Excel.

Verticaal- of horizontaal zoeken in Excel de unieke functie om gegevens uit andere werkbladen op te halen. Deze functie kunt U onder bepaalde voorwaarden zelfs toepassen op formulieren die U als sjabloon heeft weg geschreven.

Deze functie is o.a. toepasbaar voor:

  • klantgegevens en adressen ophalen uit een klantenbestand.
  • Begin- en eindtijden in een werkrooster.
  • Bestelbon uit een voorraadbestand.

Bij verschillende door mij ontwikkelde Excel rekenmodellen heb ik deze functie toegepast
Voor de uitleg van deze functie maak ik gebruik van het door mij ontwikkelde rekenmodel reiskosten woon- werkverkeer. In dit rekenmodel worden 10 verschillende bestemmingen automatisch opgezocht.

De tabbladen in het werkboek:

de adressenlijst

In de rittenlijst gebruiken we de functie verticaal zoeken om de totaal aantal kms behorende bij de code op te zoeken.

Tabblad januari

 

Voor het invullen van januari gebruikt U de codes uit de adressenlijst. Het totaal aantal kmrs behorende bij de code moeten worden opgezocht in de adressenlijst. Het totaal bedrag aftrekbare kmrs wordt berekend door het aantal Kmrs x de door de belastingdienst aangegeven km vergoeding  (0,19 cnt)

=ALS($G8;VERT.ZOEKEN($G8;adressenlijst!$B$7:$I$16;6);””)

 

adressenlijst!$B$7:$I$16;6

dwz de rittenlijst bevind zich in de cellen B7 tm I16 omdat dit absolute adresgegevens zijn staan de dollartekens er tussen.

Wat wil de formule ongeveer zeggen: Als G8 is ingevuld moet de inhoud verticaal op gezocht worden in de adressenlijst  $B$7:$I$16;6 en moeten de gegevens behorende bij de code opgezocht worden in de 6 kolom.
De “” achteraan de formule wil zeggen als niet aan de voorwaarde wordt voldaan moet er niets worden ingevuld.

In het onderstaande voorbeeld worden de adresgegevens ook opgehaald (= slechts een voorbeeld dit zit niet in het originele bestand)

In de bovenstaande formule wordt verwezen naar de bijpassende gegevens in de 7e kolom van het adressen bestand.

===============================================================

Verticaal zoeken in Ms Excel op 2 voorwaarden.

 

Zoeken op 2 voorwaarden. Standaard heeft MS Excel er geen direct oplossing voor en dan moet je soms een beetje creatief zijn.

In onderstaande productie voorraadlijst van zaden moet gezocht worden op partijnummer en statusnummer van de partij. De 2e regel van onderstaand rekenmodel is een zoek regel. Door in de eerste gele cel het partijnummer te plaatsten en in de 2e cel het statusnummer worden de gegevens van bijbehorende partij opgezocht. Ms Excel kent geen standaard functie voor verticaal zoeken op 2 voorwaarden. Om dit op te lossen heb ik een hulpkolom in het bestand bijgeplaatst. Zie de 2e afbeelding.

zkn2voorwaarden1

Als voorbeeld heb ik het partijnummer uit de groene cel in de eerste gele cel geplaatst. Het statusnummer 2 staat in de 2e gele cel. Gezocht moet worden op de 2 nummers deze heb ik in de cel ervoor samengevoegd tot 1 getal waardoor alle gegevens uit onderstaande database 1250 regel worden gevonden ook wordt de voorraad getoond. Deze stocklijst is gekoppeld aan een lijst met in en uit Mutaties. De samenvoeging van de 2 cellen vindt U bij het volgende plaatje

zkn2voorwaarden2

 

In onderstaande afbeelding ziet U in de eerste kolom het partijnummer staan met direct daarachter het statusnummer.  in Cel B2 staat de volgende formule  =C2&D2.  in cel B6 staat =C6&D6. Deze formule is verder naar beneden gekopieerd. De hulpkolom is een vast onderdeel van de tabel, hij is verder niet nodig en wordt dichtgeschoven. Omdat deze zoekfunctie bedacht was nadat het rekenmodel reed in gebruik was heb ik de kolom er voor gezet. Door tussen voegen zouden op andere tabbladen de cel adressen verschuiven anders had de kolom verborgen gezeten tussen D en E. De complete formule verticaal zoeken  in cel E3 is: =VERT.ZOEKEN($B$2;$B$6:$T$1323;4;ONWAAR)    simpel opgeschreven staat hier dat gezocht moet worden de inhoud van cel B2. in het adressen bestand tussen B6 en T1323. De bijbehorende inhoud moet staan in kolom 4    in F3 staat de zelfde formule maar de bijbehorende inhoud staat in kolom 5

zkn2voorwaarden3

=================================

 

Niet gevonden wat U zocht:

x