Excel VERT.ZOEKEN is krachtig, maar het werkt niet in specifieke situaties. Vandaag bekijken we hoe u VERT.ZOEKEN kunt oplossen.
VERT.ZOEKEN is mijn favoriete functie in Excel. Als u VERT.ZOEKEN kunt doen, kunt u veel problemen in Excel oplossen. Maar er zijn dingen die een VERT.ZOEKEN kunnen activeren. In dit onderwerp worden er enkele besproken.
Maar eerst de basisprincipes van VERT.ZOEKEN in gewoon Engels.
De gegevens in A: C waren afkomstig van de IT-afdeling. U vroeg om verkopen op item en datum. Ze gaven je artikelnummer. Je hebt een artikelbeschrijving nodig. In plaats van te wachten tot de IT-afdeling de gegevens opnieuw uitvoert, vindt u de tabel in kolom F: G.
U wilt dat VERT.ZOEKEN het item in A2 zoekt terwijl het door de eerste kolom van de tabel zoekt in $ F $ 3: $ G $ 30. Als VERT.ZOEKEN de overeenkomst vindt in F7, wilt u dat VERT.ZOEKEN de beschrijving in de tweede kolom van de tabel retourneert. Elke VERT.ZOEKEN die naar een exacte match zoekt, moet eindigen op False (of nul, wat gelijk is aan False). De onderstaande formule is correct opgezet.
Merk op dat u F4 gebruikt om vier dollartekens toe te voegen aan het adres voor de opzoektabel. Terwijl u de formule naar kolom D kopieert, moet het adres voor de opzoektabel constant blijven. Er zijn twee veelgebruikte alternatieven: U kunt de volledige kolommen F: G specificeren als de opzoektabel. Of je zou F3: G30 kunnen noemen met een naam zoals ItemTable. Als u gebruikt =VLOOKUP(A2,ItemTable,2,False), fungeert het benoemde bereik als een absolute referentie.
Elke keer dat u een aantal VERT.ZOEKEN uitvoert, moet u de kolom met VERT.ZOEKEN sorteren. Sorteer ZA, en alle # N / A-fouten verschijnen bovenaan. In dit geval is er een. Item BG33-9 ontbreekt in de opzoektabel. Misschien is het een typefout. Misschien is het een gloednieuw item. Als het nieuw is, voegt u ergens in het midden van uw opzoektabel een nieuwe rij in en voegt u het nieuwe item toe.
Het is redelijk normaal om een paar # N / A-fouten te hebben. Maar in de onderstaande afbeelding retourneert precies dezelfde formule niets anders dan # N / A. Als dit gebeurt, kijk ik of ik de eerste VERT.ZOEKEN kan oplossen. U zoekt de BG33-8 op in A2. Begin met cruisen door de eerste kolom van de opzoektabel. Zoals u kunt zien, bevindt de overeenkomende waarde zich duidelijk in F10. Waarom kun je dit zien, maar Excel kan het niet zien?
Ga naar elke cel en druk op de F2-toets. Hier is F10. Merk op dat de invoegcursor direct na de 8 verschijnt.
Hier is cel A2 in de bewerkingsmodus. De invoegcursor is een paar spaties verwijderd van de 8. Dit is een teken dat deze gegevens ooit in een oude COBOL-gegevensset zijn opgeslagen. Terug in COBOL, als het Item-veld was gedefinieerd als 10 tekens en u hebt slechts 6 tekens getypt, zou COBOL het vullen met 4 extra spaties.
De oplossing? In plaats van A2 op te zoeken, zoekt u het TRIM(A2).
De functie TRIM () verwijdert voorloop- en volgspaties. Als u meerdere spaties tussen woorden heeft, converteert TRIM ze naar één spatie. In de onderstaande afbeelding staan er spaties voor en na beide namen in A1. =TRIM(A1)verwijdert op één na alle spatie in A3.
Trouwens, wat als het probleem achterliggende spaties in kolom F was in plaats van kolom A? Voeg een kolom met TRIM () -functies toe aan E, wijzend naar kolom F. Kopieer deze en plak als waarden in F om de zoekacties weer te laten werken.
De andere veel voorkomende reden dat VERT.ZOEKEN niet werkt, wordt hier weergegeven. Kolom F heeft reële getallen. Kolom A heeft tekst die eruitziet als getallen.
Selecteer de hele kolom A. Druk op alt = "" + D, E, F. Dit doet een standaardtekst naar kolommen en converteert alle tekstnummers naar reële getallen. Het opzoeken begint weer te werken.
Bekijk video
- VERT.ZOEKEN lost veel problemen op
- Veelvoorkomende VERT.ZOEKEN-problemen:
- Als VERT.ZOEKEN begint te werken, maar # N / A wordt prominenter: $ vergeten in de opzoektabel
- Een paar # N / A: items ontbreken op de tafel
- Geen van de VERT.ZOEKEN werkt: controleer op volgspaties
- Verwijder volgspaties met TRIM
- Cijfers en cijfers opgeslagen als tekst
- Selecteer beide kolommen en gebruik alt = "" + DEF
- Aflevering bevat een grap die zowel accountants als IT-ers grappig vinden, maar om verschillende redenen
Videotranscriptie
Leer Excel van podcast, aflevering 2027 - Problemen met VERT.ZOEKEN oplossen!
Oké, podcast dit hele boek, klik op de "i" in de rechterbovenhoek om naar de afspeellijst te gaan!
VERT.ZOEKEN Is mijn favoriete functie in heel Excel, en ik vertel deze grap altijd in een van mijn seminars, en het wordt een lachertje of je een IT-persoon bent of geen IT-persoon. Ik zeg altijd: “Kijk, we hebben deze gegevens hier aan de linkerkant, en ik kan naar die gegevens kijken en vertellen dat de gegevens van de IT-afdeling kwamen, want het is precies wat ik vroeg, maar niet echt wat ik nodig had. Ik vroeg om de datum en het aantal van het item, en ze gaven me de datum en het aantal van het itemnummer, maar ze namen niet de moeite om me een beschrijving te geven, toch? " En de accountants lachen hier altijd om, omdat dit hen de hele tijd overkomt, en de IT-jongens zeggen: "Nou, ik heb je gegeven waar je om vroeg, het is niet mijn schuld!" Dus ze denken allebei dat het grappig is om verschillende redenen, dus, weet je, en de IT-man heeft het druk, hij kan niet teruggaan naar het herschrijven van de vraag,dus we moeten iets doen om dit zelf te redden.
En dus dit tafeltje dat ik ergens anders op mijn computer heb gekopieerd, in gewoon Engels, zegt VERT.ZOEKEN: "Hé, we hebben itemnummer W25-6." We hebben hier een tafel, ik wil door de eerste kolom van de tafel gaan tot ik dat itemnummer vind, en dan iets uit die rij teruggeven. Oké, in dit geval wil ik de tweede kolom uit die rij. En dan moeten we aan het einde van elke VERT.ZOEKEN of ONWAAR of 0 zetten. Nu hier, nadat ik het bereik heb gekozen, druk ik op de F4-toets, en dan wil ik de 2e kolom en dan ONWAAR voor een exacte bij elkaar passen. Kies nooit een match bij benadering, nooit, nooit! Het is geen match bij benadering, het is iets heel speciaals, het werkt niet altijd. Als u uw cijfers aan de Securities and Exchange Commission wilt herhalen, kunt u deze gerust gebruiken,TRUE of laat de, FALSE gewoon uit. Maar elke VERT.ZOEKEN die u ooit maakt, moet eindigen op, FALSE of, 0, 0 is korter dan FALSE, u zou daar een FALSE moeten plaatsen, maar een 0 is hetzelfde als FALSE.
Oké, probleemoplossing, ten eerste, als je een hele reeks VERT.ZOEKEN doet, is het heel normaal om een paar # N / A's te hebben, toch? En ik vind altijd de # N / A's door naar Data, ZA te gaan, die de # N / A naar de top brengt, daar, BG33-9, of dat is een typfout of het is een gloednieuw item, oké, en we hebben om het uit te zoeken. Dus hier aan de rechterkant heb ik de nieuwe gegevens, ik zal die knippen, en dan Alt + IED, die cellen in het midden invoegen, het hoeft niet alfabetisch te zijn, deze tabel hoeft niet te worden gesorteerd. Wanneer je de, FALSE-versie gebruikt, is de tabel niet - je kunt het gewoon overal neerzetten waar je maar wilt, ik heb het niet aan het einde gezet omdat ik de formule niet hoefde te herschrijven, ik wil gewoon dat de formule werk. Oké, dus een paar # N / A's, extreem, extreem normaal, maar kijk hier eens naar.
Als je begint met antwoorden die werken, maar dan beginnen de # N / A's enigszins vaak te verschijnen, en uiteindelijk verschijnen ze helemaal naar beneden, dat is een zeker teken dat je de tabelverwijzing niet hebt vergrendeld. Oké, zie je, dus hier beweegt de tafel terwijl ik de formule naar beneden kopieer, juist, en dus heb ik het geluk dat ik er een paar tegenkom die aan het einde van de lijst stonden. Maar uiteindelijk kom ik op het punt dat het hier in volledig lege cellen kijkt, en natuurlijk wordt er niets gevonden. Oké, dus dat is het eerste, je krijgt een paar dat werkt, een paar # N / A's, een paar meer die werken, en dan is alle # N / A de rest van de weg - zeker teken dat je de $ in.
Ga gewoon terug, F2 voor de bewerkingsmodus, selecteer de dubbele punt, druk op F4, dat zet alle $ in, dubbelklik om dat neer te schieten, en alles begint weer te werken, oké. De volgende, exact dezelfde formule, de formule is perfect, BG33-8 zie, we krijgen niets van dat goed. Oké, dus ik ga kijken, BG33-8, hé, daar is het, het is daar, het is in het rood, ik had het moeten zien! Dus ik kom naar deze aan de rechterkant, ik druk op F2, en ik kijk naar het knipperende invoegpunt, en zie, het is direct na de 8, zo, en dan kom ik hier en ik druk op F2, er zijn enkele volgspaties daar. Dit is heel, heel gebruikelijk in de dagen van COBOL, ze zeiden: "Weet je, kijk, we geven je 10 spaties voor het itemnummer, en als je niet alle 10 spaties typt, vullen ze de spaties. . " En dus is dit heel gebruikelijk,en de geweldige oplossing hier is om die voorloop- en volgspaties te verwijderen met de TRIM-functie. Gebruik in plaats van A2 de TRIM (A2), dubbelklik om dat neer te schieten, oké, nog steeds is de BG33-9 terug in de andere tafel.
Oké, zodat je begrijpt hoe TRIM werkt, dus typte ik een aantal spaties, John, een heleboel spaties, Durran en een heleboel spaties, en toen voegde ik een * voor en na samen, zodat je kunt zien hoe het eruit ziet . Als ik om de TRIM (P4) vraag, verwijderen we alle voorloopspaties, alle volgspaties, en vervolgens worden de meerdere binnenruimtes teruggebracht tot één ruimte. Oké, dus je kunt zien, visualiseer je daar, dat ze de voorloop- en volgspaties wegwerken, alle verdubbelde spaties in het midden worden zo een enkele ruimte. Dus TRIM, geweldig, geweldig hulpmiddel in je arsenaal, oké, hier is nog een heel veel voorkomende.
Als het niet de volgspaties zijn, dan is het meest voorkomende dat ik heb gezien, waar we hier echte getallen hebben, en hier hebben we getallen opgeslagen als tekst. En VERT.ZOEKEN ziet dat niet als een overeenkomst, ook al werkt 4399, dit is een getal, dit is tekst, niet. De snelste manier om een kolom met tekst om te zetten in cijfers, de kolom te selecteren, 3 letters achter elkaar, alt = "" DEF, en ineens beginnen onze VERT.ZOEKEN weer te werken, geweldige, geweldige tip van ongeveer 1500 podcasts geleden. Oké, dus dat zijn de meest voorkomende VERT.ZOEKEN-problemen, ofwel ben je de $ vergeten, ofwel heb je spaties aan het einde, of heb je cijfers en cijfers opgeslagen als tekst. Al deze tips staan in dit boek "MrExcel XL", klik op de "i" in de rechterbovenhoek, u kunt het boek kopen.
Oké, korte samenvatting: VERT.ZOEKEN lost veel problemen op, als een VERT.ZOEKEN begint te werken maar # N / A! prominenter wordt, bent u vergeten de $ in de opzoektabel te plaatsen. Als er een paar # N / A's zijn, ontbreken alleen items op de tafel. Als geen van de VERT.ZOEKEN werkt en het is tekst, controleer dan op volgspaties, u kunt de TRIM-functie gebruiken. Als u nummers en nummers als tekst hebt opgeslagen, selecteert u een van de kolommen, de kolom met de tekst, en doet u alt = "" DEF om al die nummers terug te zetten naar cijfers.
Oké nou, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2027.xlsx
