Excel VLOOKUP on tehokas, mutta se ei toimi, kun sinulla on erityisiä tilanteita. Tänään tarkastellaan VLOOKUPin vianmääritystä.
VLOOKUP on suosikkini Excelissä. Jos pystyt tekemään VLOOKUP-sovelluksen, pystyt ratkaisemaan monet ongelmat Excelissä. Mutta on asioita, jotka voivat laukaista VLOOKUPin. Tämä aihe puhuu muutamasta heistä.
Mutta ensin VLOOKUPin perusteet selkeällä englanniksi.
A: C: n tiedot tulivat IT-osastolta. Pyysit myyntiä tuotteen ja päivämäärän mukaan. He antoivat sinulle tuotenumeron. Tarvitset kohteen kuvauksen. Sen sijaan, että odottaisit, että tietotekniikkaosasto suorittaa tiedot uudelleen, löydät taulukon, joka näkyy sarakkeessa F: G.
Haluat, että VLOOKUP löytää kohteen A2: sta, kun se etsii taulukon ensimmäisestä sarakkeesta kohdassa $ F $ 3: $ G $ 30. Kun VLOOKUP löytää osuman F7: stä, haluat, että VLOOKUP palauttaa taulukon toisesta sarakkeesta löytyvän kuvauksen. Jokaisen tarkkaa hakua etsivän VLOOKUPin on päätyttävä väärällä (tai nolla, mikä vastaa väärää). Alla oleva kaava on määritetty oikein.
Huomaa, että käytät F4-näppäintä neljän dollarin merkin lisäämiseen hakutaulukon osoitteeseen. Kun kopioit kaavaa alasarakkeeseen D, tarvitset osoitteen, jonka perusteella hakutaulukko pysyy vakiona. On olemassa kaksi yleistä vaihtoehtoa: Voit määrittää koko sarakkeet F: G hakutaulukoksi. Tai voit nimetä F3: G30 esimerkiksi nimellä ItemTable. Jos käytät =VLOOKUP(A2,ItemTable,2,False), nimetty alue toimii absoluuttisena viitteenä.
Aina kun teet joukon VLOOKUP-tiedostoja, sinun on lajiteltava VLOOKUP-sarake. Lajittele ZA, ja kaikki # N / A-virheet tulevat alkuun. Tässä tapauksessa on yksi. Kohde BG33-9 puuttuu hakutaulukosta. Ehkä se on kirjoitusvirhe. Ehkä se on upouusi tuote. Jos se on uusi, lisää uusi rivi mihin tahansa hakutaulukon keskelle ja lisää uusi kohde.
On melko normaalia, että sinulla on muutama # N / A-virhe. Mutta alla olevassa kuvassa täsmälleen sama kaava ei palauta muuta kuin # N / A. Kun näin tapahtuu, näen pystynkö ratkaisemaan ensimmäisen VLOOKUPin. Etsit A2: sta löytyvää BG33-8: ta. Aloita risteily hakutaulukon ensimmäisen sarakkeen läpi. Kuten näette, vastaava arvo on selvästi F10: ssä. Miksi näet tämän, mutta Excel ei näe sitä?
Siirry kuhunkin soluun ja paina F2-näppäintä. Tässä on F10. Huomaa, että lisäyskohdistin näkyy heti 8: n jälkeen.
Tässä on solu A2 muokkaustilassa. Lisäyskohdistin on muutaman välimatkan päässä 8. Tämä on merkki siitä, että jossain vaiheessa nämä tiedot on tallennettu vanhaan COBOL-tietojoukkoon. Takaisin COBOLissa, jos Tuote-kenttä määritettiin 10 merkiksi ja kirjoitit vain 6 merkkiä, COBOL täytti sen 4 ylimääräisellä välilyönnillä.
Ratkaisu? Sen sijaan, että etsitään A2: ta, etsi TRIM(A2).
TRIM () -toiminto poistaa etu- ja jälkitilat. Jos sanojen välillä on useita välilyöntejä, TRIM muuntaa ne yhdeksi välilyönniksi. Alla olevassa kuvassa on välilyöntejä A1: n molempien nimien edessä ja jälkeen. =TRIM(A1)poistaa kaikki paitsi yhden välilyönnin A3: sta.
Muuten, jos ongelma olisi ollut välilyönnit sarakkeessa F sarakkeen A sijaan? Lisää TRIM () -funktioiden sarake E-sarakkeeseen osoittamalla sarakkeeseen F. Kopioi ne ja liitä arvoina F: ään, jotta hakut alkavat toimia uudelleen.
Toinen hyvin yleinen syy siihen, että VLOOKUP ei toimi, näkyy tässä. Sarakkeella F on reaalilukuja. Sarakkeessa A on tekstiä, joka näyttää numeroilta.
Valitse kaikki sarakkeet A. Paina alt = "" + D, E, F. Tämä tekee oletustekstistä sarakkeet ja muuntaa kaikki tekstiluvut reaalilukuiksi. Hakutoiminto alkaa toimia uudelleen.
Katso video
- VLOOKUP ratkaisee monia ongelmia
- Yleiset VLOOKUP-ongelmat:
- Jos VLOOKUP alkaa toimia, mutta # N / A tulee näkyvämmäksi: unohdit $ hakutaulukkoon
- Muutama # N / A: taulukosta puuttuu kohteita
- Mikään VLOOKUPista ei toimi: tarkista, onko jäljellä olevia välilyöntejä
- Poista takatilat TRIM: llä
- Numerot ja numerot, jotka on tallennettu tekstinä
- Valitse molemmat sarakkeet ja käytä alt = "" + DEF
- Jakso sisältää vitsi, jonka sekä kirjanpitäjät että IT-ihmiset pitävät hauskana, mutta eri syistä
Videon transkriptio
Opi Excel podcastista, jakso 2027 - VLOOKUP-vianmääritys!
Selvä, podcasting koko tätä kirjaa, napsauta "i" oikeassa yläkulmassa päästäksesi soittolistaan!
VLOOKUP on suosikkitoimintoni koko Excelissä, ja kerron tämän vitsi aina yhdessä seminaarissani, ja nauraa riippumatta siitä, oletko IT-henkilö vai et IT-henkilö. Sanon aina: "No, katso, meillä on tämä datajoukko täällä vasemmalla, ja voin tarkastella näitä tietoja ja kertoa, että tiedot tulivat IT-osastolta, koska juuri sitä pyysin, mutta ei oikeastaan sitä, mitä tarvitsin. Pyysin tuotteen päivämäärää ja määrää, ja he antoivat minulle tuotenumeron päivämäärän ja määrän, mutta he eivät vaivautuneet antamaan minulle kuvausta, eikö? ” Ja kirjanpitäjät nauravat aina tätä, koska heille tapahtuu tätä koko ajan, ja IT-kaverit ovat kuin "No, annoin sinulle mitä pyysit, se ei ole minun vikani!" Joten he molemmat pitävät sitä hauskana eri syistä, joten tiedät, ja IT-kaveri on kiireinen, hän ei voi palata kyselyn uudelleenkirjoittamiseen,joten meidän on tehtävä jotain pelastaaksemme tämän itse.
Ja niin tämä pieni taulukko, jonka kopioin tietokoneeltani muualta, selkeällä englanniksi, mitä VLOOKUP tekee, sanoo: "Hei, meillä on tuotenumero W25-6." Meillä on täällä taulukko, haluan risteillä alas taulukon ensimmäisen sarakkeen läpi, kunnes löydän tuotenumeron, ja palauttaa sitten jotain tältä riviltä. Selvä, tässä tapauksessa haluan olla kyseisen sarakkeen toinen sarake. Ja sitten jokaisen VLOOKUPin lopussa meidän on laitettava joko FALSE tai 0. Nyt täällä, kun valitsen alueen, aion painaa F4-näppäintä ja sitten haluan toisen sarakkeen ja sitten FALSE tarkan ottelu. Älä koskaan valitse likimääräistä ottelua, ei koskaan, ei koskaan! Se ei ole arvioitu ottelu, se on hyvin erikoinen asia, se ei toimi koko ajan. Jos haluat palauttaa numerosi Securities and Exchange Commissionille, voit käyttää sitä kaikin keinoin,TOSI tai jätä vain FALSE pois päältä. Mutta jokaisen luomasi VLOOKUPin pitäisi päättyä, FALSE tai, 0, 0 on lyhyempi kuin FALSE, sinun pitäisi laittaa FALSE sinne, mutta 0 on sama asia kuin FALSE.
Selvä, vianetsintä, ensimmäinen asia, kun teet kokonaisen joukon VLOOKUP-tiedostoja, on hyvin normaalia, että sinulla on pari # N / As, eikö? Ja löydän # N / A: n aina siirtymällä Data, ZA, joka tuo # N / As: n alkuun, juuri siellä, BG33-9, joko se on kirjoitusvirhe tai se on upouusi tuote, okei, ja saimme selvittää se. Joten täällä oikealla minulla on uudet tiedot, leikkaan sen ja sitten Alt + IED, lisää ne solut keskelle, sen ei tarvitse olla aakkosjärjestyksessä, tätä taulukkoa ei tarvitse lajitella. Kun käytät FALSE-versiota, taulukko ei ole - voit vain laittaa sen mihin tahansa. En laittanut loppua, koska minun ei tarvinnut kirjoittaa kaavaa uudelleen, haluan vain kaavan työ. Selvä, joten pari # N / A: ta, erittäin, erittäin normaalia, mutta tarkista tämä.
Kun aloitat toimivilla vastauksilla, mutta # N / A: t alkavat näkyä hieman usein, ja lopulta ne ilmestyvät kokonaan alas, se on varma merkki siitä, että et lukinnut taulukon viittausta. Selvä, joten tässä taulukko liikkuu, kun kopioin kaavaa alas, oikeassa, joten minulla on onni lyödä muutamia, jotka olivat luettelon lopussa. Mutta lopulta pääsen pisteeseen, jossa se näyttää täältä täysin tyhjissä soluissa, eikä tietenkään löydy mitään. Selvä, niin se on ensimmäinen asia, saat pari, joka toimii, muutama # N / A, pari muuta, jotka toimivat, ja sitten kaikki # N / A ovat lopputien - varma merkki siitä, ettet laittanut $ sisään.
Palaa vain takaisin, F2 muokkaustilaa varten, valitse kaksoispiste, paina F4, joka tuo kaikki dollarit sisään, kaksoisnapsauttamalla ampua se alas, ja asiat alkavat toimia jälleen, okei. Seuraava, täsmälleen sama kaava, kaava on täydellinen, BG33-8 katso, emme saa mitään oikeutta. Selvä, joten menen katsomaan, BG33-8, hei, siinä se on, se on tuolla, se on punaisella, minun olisi pitänyt nähdä se! Joten pääsen tälle oikealle puolelle, painan F2, ja katson vilkkuvaa lisäyskohtaa, ja katso, se on heti 8: n jälkeen, kuten, ja sitten tulen tänne ja painan F2, siellä on joitain jäljessä olevia tiloja siellä. Tämä on hyvin, hyvin yleistä COBOLin päivinä, he sanoivat: "Tiedätkö, katso, annamme sinulle 10 välilyöntiä tuotenumerolle, ja jos et kirjoita kaikkia 10 välilyöntiä, ne täyttävät tilat . ” Ja niin tämä on hyvin yleistä,ja loistava ratkaisu tässä on päästä eroon niistä etu- ja takatiloista TRIM-toiminnolla. A2: n sijaan käytä TRIM-ohjelmaa (A2), kaksoisnapsauta ampua se alas, okei, silti BG33-9 on taas toisessa taulukossa.
Selvä, vain jotta ymmärrät TRIMin toiminnan, kirjoitin joukon välilyöntejä, John, joukko välilyöntejä, Durran ja joukko välilyöntejä, ja sitten ketjutin * ennen ja jälkeen, jotta voit nähdä miltä se näyttää . Kun pyydän TRIMiä (P4), pääsemme eroon kaikista etutiloista, kaikista takatiloista ja sitten useat sisätilat pienennetään yhteen tilaan. Selvä, joten voit nähdä, tavallaan visualisoi siellä, että he pääsevät eroon etu- ja takatiloista, keskimmäisistä kaksinkertaistetuista tiloista tulee yksi tällainen tila. Joten TRIM, hieno, hieno työkalu arsenaalissasi, okei, tässä on toinen todella yleinen.
Jos se ei ole jäljessä olevia välilyöntejä, niin yleisin asia, jonka olen nähnyt, on se, missä täällä on todellisia lukuja, ja täällä meillä on numeroita, jotka on tallennettu tekstinä. Ja VLOOKUP ei näe sitä otteluna, vaikka 4399, tämä on luku, tämä on teksti, ei toimi. Nopein tapa muuntaa sarake tekstiä numeroiksi, valitsemalla sarake, 3 kirjainta peräkkäin, alt = "" DEF, ja yhtäkkiä VLOOKUP-tiedostomme alkavat toimia uudelleen, loistava, hieno vinkki noin 1500 podcastista sitten. Selvä, joten nämä ovat yleisimpiä VLOOKUP-ongelmia, joko unohdit $, tai sinulla on jäljessä välilyöntejä, tai sinulla on numeroita ja numeroita, jotka on tallennettu tekstinä. Kaikki nämä vinkit ovat tässä kirjassa ”MrExcel XL”, napsauta oikeassa yläkulmassa olevaa ”i” -kuvaketta, voit ostaa kirjan.
Selvä, nopea yhteenveto: VLOOKUP ratkaisee monet ongelmat, jos VLOOKUP alkaa toimia, mutta # N / A! tulee näkyvämmäksi, unohdit laittaa $ hakutaulukkoon. Jos # N / A on muutama, taulukosta puuttuu vain kohteita. Jos mikään VLOOKUP-tiedostoista ei toimi ja se on tekstiä, tarkista, onko välilyöntejä, voit käyttää TRIM-toimintoa. Jos sinulla on numeroita ja numeroita, jotka on tallennettu tekstinä, valitse jompikumpi sarake, se, jolla on teksti, ja tee sitten alt = "" DEF: n kaikille niille takaisin numeroihin.
Hei, hei, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla uudesta netcastista!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2027.xlsx
