Excel 2020: XLOOKUPin kaksitoista hyötyä - Excel-vinkkejä

Uusi XLOOKUP-toiminto on tulossa Office 365: een marraskuusta 2019 alkaen. Joe McDaid Excel-tiimistä suunnitteli XLOOKUPin yhdistämään VLOOKUPia käyttävät ihmiset ja INDEX / MATCHia käyttävät ihmiset. Tässä osiossa käsitellään XLOOKUPin 12 etua:

  1. Tarkka haku on oletusarvo.
  2. VLOOKUPin kokonaislukupohjainen kolmas argumentti on nyt oikea viite.
  3. IFNA on sisäänrakennettu käsittelemään puuttuvat arvot.
  4. XLOOKUP: lla ei ole ongelmaa mennä vasemmalle.
  5. Etsi seuraava, pienempi tai seuraava suurempi ottelu lajittelematta taulukkoa.
  6. XLOOKUP voi tehdä HLOOKUP.
  7. Etsi viimeinen ottelu etsimällä alhaalta.
  8. Jokerimerkit ovat oletusarvoisesti pois päältä, mutta voit ottaa ne uudelleen käyttöön.
  9. Palauta kaikki 12 kuukautta yhdellä kaavalla.
  10. Voi palauttaa soluviitteen, jos XLOOKUP on kaksoispisteen vieressä, kuten XLOOKUP (); XLOOKUP ()
  11. Voi tehdä kaksisuuntaisen ottelun, kuten INDEX (, MATCH, MATCH).
  12. Voi laskea kaikki haut yhteen kaavaan, kuten LOOKUP voisi tehdä.

Tässä on syntaksi: = XLOOKUP (haun_arvo, haun_palautus, tuloksen_palautus, (jos_ei_löydetty), (vastaavuustila), (hakutapa)).

XLOOKUP-etu 1: Tarkka haku oletusarvoisesti

99% VLOOKUP-kaavoistani päättyy arvoon FALSE tai 0 tarkan vastaavuuden osoittamiseksi. Jos käytät aina VLOOKUPin täsmää vastaavaa versiota, voit alkaa jättää match_mode pois XLOOKUP-toiminnostasi.

Seuraavassa kuvassa etsit W25-6-solua A4. Haluat etsiä kohdetta L8: L35. Kun se löytyy, haluat vastaavan hinnan sarakkeesta N. Ei ole tarpeen määrittää epätosi vastaavuustilaksi, koska XLOOKUP on oletusarvoisesti tarkka haku.

XLOOKUP arvo A4. Katso L8: L35. Palauta vastaava hinta tuotteesta N8: N35.

XLOOKUP-etu 2: Results_Array on viite kokonaisluvun sijaan

Ajattele VLOOKUP-kaavaa, jota käyttäisit ennen XLOOKUPia. Kolmas argumentti olisi ollut 3 osoittamaan, että haluat palauttaa kolmannen sarakkeen. Aina oli vaara, että tietämätön työtoveri olisi lisännyt (tai poistanut) sarakkeen taulukkoon. Jos taulukossa olisi ylimääräinen sarake, hinnan palauttanut VLOOKUP aloittaisi kuvauksen palauttamisen. Koska XLOOKUP osoitti soluviittausta, kaava kirjoittaa itsensä uudelleen osoittamaan hintaa, joka on nyt sarakkeessa O.

Vanha VLOOKUP epäonnistuu, jos joku lisää uuden sarakkeen hakutaulukkoon. XLOOKUP jatkaa toimintaansa.

XLOOKUP Benefit 3: IFNA on sisäänrakennettu valinnaisena argumenttina

Pelätty # N / A -virhe palautetaan, kun hakuarvoasi ei löydy taulukosta. Aikaisemmin, jos haluat korvata # N / A: n jollakin muulla, sinun on käytettävä IFERRORia tai IFNA: ta kääritty VLOOKUPin ympärille.

Kun kohdetta ei löydy, se palauttaa # N / A kohdasta VLOOKUP tai XLOOKUP…

Ricon YouTube-kanavalleni tekemän ehdotuksen ansiosta Excel-tiimi sisälsi valinnaisen neljännen argumentin if_not_found-argumentille. Jos haluat korvata nuo # N / A-virheet nollalla, lisää vain 0 neljäntenä argumentteina. Tai voit käyttää tekstiä, kuten "Arvoa ei löydy".

XLOOKUPin valinnainen neljäs argumentti on "ellei löydy". Laita 0 tai "Ei löydy" sinne.

XLOOKUP-etu 4: Ei ole ongelma katsoa avainkentän vasemmalle puolelle

VLOOKUP ei voi katsoa avainkentän vasemmalle puolelle turvautumatta VLOOKUPiin (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). XLOOKUP: n kanssa ei ole mitään ongelmaa, jos Tulokset_array on Hakutaulukko vasemmalla.

XLOOKUP: n kanssa ei ole mitään ongelmaa palauttaa luokka sarakkeesta F, kun etsit osanumeroita sarakkeesta G. Tämä oli aina VLOOKUPin heikkous: se ei voinut katsoa vasemmalle.

XLOOKUP-etu 5: Seuraava tai pienempi tai seuraava suurempi ottelu ilman lajittelua

VLOOKUPilla oli mahdollisuus etsiä tarkka ottelu tai vain pienempi arvo. Voit joko jättää neljännen argumentin pois VLOOKUPista tai vaihtaa False arvoksi True. Jotta tämä toimisi, hakutaulukko oli lajiteltava nousevassa järjestyksessä.

Esimerkki VLOOKUPin likimääräisestä vastaavuudesta. Jokainen myynti 10 tuhannesta 20 tuhanteen saa 12 dollarin bonuksen.

Mutta VLOOKUPilla ei ollut mahdollisuutta palauttaa tarkkaa ottelua tai seuraavaa suurempaa tuotetta. Tätä varten sinun oli siirryttävä käyttämään MATCH-arvoa -1 vastaavuustilana ja sinun oli oltava varovainen, että hakutaulukko oli järjestetty laskevaan järjestykseen.

XLOOKUPin valinnainen viides argumentti match_mode voi etsiä vain tarkkaa vastaavuutta, yhtä suuri tai vain pienempi, yhtä suuri tai vain suurempi. Huomaa, että XLOOKUPin arvot ovat järkevämpiä kuin MATCH:

  • -1 löytää arvon, joka on yhtä suuri tai vain pienempi
  • 0 löytää tarkan haun
  • 1 löytää arvon, joka on yhtä suuri tai vain suurempi.

Mutta upein osa: hakutaulukkoa ei tarvitse lajitella ja mikä tahansa match_mode toimii.

Alla olevasta vastaavuustilasta -1 löytyy seuraava pienempi kohde.

XLOOKUPin viides argumentti on Match_Mode. 0 on tarkka ottelu. Negatiivista käytetään tarkalle vastaavuudelle tai seuraavalle pienemmälle kohteelle. Positiivinen 1 on tarkka haku tai seuraava suurempi kohde. 2 on jokerimerkille. Jos haluat peilata, mitä VLOOKUP ja True neljännessä argumentissa tekisivät, laita negatiivinen argumentiksi match_mode XLOOKUP: iin.

Täältä ottelutapa 1 etsii mitä ajoneuvoa tarvitaan juhlissa olevien ihmisten lukumäärän mukaan. Huomaa, että hakutaulukkoa ei ole lajiteltu matkustajien mukaan ja ajoneuvon nimi on avaimen vasemmalla puolella.

XLOOKUP voi tehdä jotain, mitä VLOOKUP ei voinut tehdä: löytää tarkka ottelu tai vain suurempi. Tässä tapauksessa matkanjärjestäjällä on luettelo varauksista. Matkustajamäärän perusteella hakutaulukko näyttää, mitä ajoneuvoa tarvitset kyseisille ihmisille.

Taulukossa sanotaan:

  • Bussiin mahtuu 64 henkilöä
  • Autoon mahtuu 4 henkilöä
  • Motorcyle mahtuu 1 henkilö
  • Tour Van mahtuu 12 henkilöä
  • Pakettiautoon mahtuu 6 henkilöä.

Bonuksena tiedot lajitellaan ajoneuvon mukaan (vanhassa ratkaisussa MATCH: n avulla taulukko olisi lajiteltava laskevasti kapasiteetin mukaan. Lisäksi: ajoneuvo on kapasiteetin vasemmalla puolella.

XLOOKUP-etu 6: Sivuttain XLOOKUP korvaa HLOOKUPin

Hakutoiminto ja tulosarvot voivat olla vaakasuoria XLOOKUP: n kanssa, mikä helpottaa HLOOKUPin korvaamista.

Tässä hakutaulukko on vaakasuora. Aikaisemmin tämä vaati HLOOKUPia, mutta XLOOKUP voi käsitellä pöytää, joka menee sivuttain.

XLOOKUP Benefit 7: Hae viimeisintä ottelua alhaalta

Minulla on vanha video YouTubessa vastaamassa brittiläisen hevostilan kysymykseen. Heillä oli ajoneuvokanta. Joka kerta, kun ajoneuvo tuli polttoainetta tai huoltoa varten, he kirjaivat ajoneuvon, päivämäärän ja mittarilukeman taulukkoon. He halusivat löytää uusimman tunnetun mittarilukeman jokaiselle ajoneuvolle. Vaikka Excel-2017-aikakausi MAXIFS saattaa ratkaista tämän tänään, ratkaisu monta vuotta sitten oli arcane-kaava, joka käytti LOOKUPia ja sisälsi nollalla jakamisen.

Tänään XLOOKUPin valinnaisen kuudennen argumentin avulla voit määrittää, että haun tulisi alkaa tietojoukon alareunasta.

Etsi luettelosta viimeinen ottelu.

Huomautus

Vaikka tämä on hieno parannus, sen avulla voit löytää vain ensimmäisen tai viimeisen ottelun. Jotkut ihmiset toivovat tämän antavan sinun löytää toisen tai kolmannen osuman, mutta se ei ole search_mode-argumentin tarkoitus.

Varoitus

Yllä oleva kuva osoittaa, että on olemassa hakutiloja, jotka käyttävät vanhaa binäärihakua. Joe McDaid ei suosittele näiden käyttöä. Ensinnäkin parannettu hakualgoritmi vuodesta 2018 on tarpeeksi nopea, jotta nopeudesta ei ole merkittävää hyötyä. Toiseksi sinulla on vaara, että avulias työtoveri lajittelee hakutaulukon ja tuo vääriä vastauksia.

XLOOKUP-etu 8: Jokerit ovat oletusarvoisesti pois päältä

Useimmat ihmiset eivät tienneet, että VLOOKUP käsittelee tähteä, kysymysmerkkiä ja tildejä jokerimerkkeinä, kuten kohdassa "# 51 Käytä jokerimerkkiä VLOOKUPissa" sivulla 143. on kuvattu. Jos haluat, että XLOOKUP käsittelee näitä merkkejä jokerimerkkinä, käytä merkkiä 2 Match_Mode-muodossa.

Hyvin harvat ihmiset tajusivat, että VLOOKUP käsittelee hakuarvon tähtiä jokerimerkkinä. Oletuksena XLOOKUP ei käytä jokerimerkkejä, mutta voit pakottaa sen toimimaan kuten VLOOKUP, jos käytät Match Mode 2: Wildcard Character Match.

XLOOKUP-etu 9: Palauta kaikki 12 kuukautta yhdessä kaavassa!

Tämä on todella dynaamisten matriisien etu, mutta se on suosikkini syy rakastaa XLOOKUPia. Kun sinun on palautettava kaikki 12 kuukautta haulla, yksi kaava, joka on syötetty B6: een suorakulmaisella palautusarvolla, antaa useita tuloksia. Nämä tulokset vuotavat vierekkäisiin soluihin.

Alla olevassa kuvassa yksi kaavaan B7 syötetty kaava palauttaa kaikki 12 vastausta, jotka on esitetty kohdassa B7: M7.

Yksi XLOOKUP tammikuussarakkeessa palauttaa numerot tammi-joulukuussa. Tämä tehdään määrittämällä tulosarake 12 sarakkeella.

XLOOKUP-etu 10: Voi palauttaa soluviitteen, jos se on kaksoispisteessä

Tämä on monimutkainen, mutta kaunis. Aiemmin oli seitsemän toimintoa, jotka muuttuvat solun arvon palauttamisesta soluviitteen palauttamiseen, jos toiminto kosketti kaksoispistettä. Katso esimerkki kohdasta Käytä A2: INDEX () haihtumattomana POISSA. XLOOKUP on kahdeksan toiminto, joka tarjoaa tämän käyttäytymisen liittymällä VALINTA, JOS, IFS, HAKEMISTO, EPÄSUORA, OFFSET ja KYTKIN.

Harkitse seuraavaa kuvaa. Joku valitsee kirsikan E4: ssä ja kuvan E5: ssä. Haluat kaavan, joka summaa kaikki välillä B6 - B9.

Kuvassa on kaksi XLOOKUP-kaavaa kahdessa solussa. Ensimmäinen palauttaa 15 solusta B6. Toinen uudelleenkäynnistää 30 mallista B9. Mutta sitten kolmannessa solussa on kaava, joka yhdistää kaksi XLOOKUP-kaavaa kaksoispisteellä ja kääri sen sitten SUM-funktioon. Tuloksena on B6: B9: n summa, koska XLOOKUP voi palauttaa soluviitteen, jos toiminto näkyy operaattorin, kuten kaksoispisteen, vieressä. Todistamaan, että tämä toimii, seuraavissa useissa kuvissa näkyy tämä kaava Arvioi kaava -valintaikkunassa.

Yllä olevasta kuvasta näet, että E4: n XLOOKUP palauttaa 15 solusta B6. XLOOKUP E5 palauttaa 30 B9: stä. Jos kuitenkin otat kaksi XLOOKUP-toimintoa soluista D9 ja D10 ja laitat ne yhteen kaksoispisteen väliin, XLOOKUP-käyttäytyminen muuttuu. Palautuksen 15 sijasta ensimmäinen XLOOKUP palauttaa solun osoitteen B6!

Tämän todistamiseksi olen valinnut D7: n ja käyttänyt Kaavat, Arvioi kaava. Kun olet painanut Arvioi kaksi kertaa, seuraava laskettava osa on XLOOKUP ("Kirsikka", A4: A29, B4: B29), kuten tässä on esitetty.

Tämä näyttää Arvioi kaava -valintaikkunan juuri ennen ensimmäisen XLOOKUP-ohjelman arviointia. Tämä XLOOKUP näkyy juuri ennen kaksoispistettä.

Paina Arvioi uudelleen ja hämmästyttävää, että XLOOKUP-kaava palauttaa $ B $ 6 B6: een tallennetun 15 sijasta. Tämä tapahtuu, koska tätä XLOOKUP-kaavaa seuraa välittömästi kaksoispiste.

Napsauta Arvioi ja ensimmäinen XLOOKUP palauttaa $ B $ 6 15: n sijaan.

Paina Arvioi vielä kaksi kertaa, ja väliaikainen kaava on = SUM (B6: B9).

Kun toinen XLOOKUP on arvioitu, välikaava on = SUM (B6: B9).

Tämä on hämmästyttävää käyttäytymistä, josta useimmat ihmiset eivät tiedä. Excelin MVP Charles Williams kertoo minulle, että se voidaan laukaista millä tahansa näistä kolmesta operaattorista XLOOKUP: n vieressä:

  • Kaksoispiste
  • Avaruus (risteysoperaattori)
  • Pilkku (unionin toimija)

XLOOKUP-etu 11: Kaksisuuntainen ottelu kuten INDEX (, MATCH, MATCH)

Kaikille VLOOKUP-ystävilleni INDEX / MATCH-ihmiset ovat odottaneet, pystyykö XLOOKUP käsittelemään kaksisuuntaisen ottelun. Hyvät uutiset: se voi tehdä sen. Huono uutinen: metodologia on hieman erilainen kuin INDEX / MATCH -fanit odottavat. Se saattaa olla hieman heidän päänsä yli. Mutta olen varma, että he voivat löytää tämän menetelmän.

Kaksisuuntaista ottelua varten haluat löytää, mikä rivi sisältää J3: ssa esitetyn tilinumeron A621. Joten, XLOOKUP alkaa tarpeeksi helposti: = XLOOKUP (J3, A5: A15. Mutta sitten sinun on annettava results_array. Voit käyttää samaa temppua kuin XLOOKUP Benefit 9: Palauta kaikki 12 kuukautta yhtenä kaavana yllä, mutta käytä sitä palauttaaksesi vertikaalivektorin. Sisäinen XLOOKUP etsii J4-kuukautta kuukauden otsikoissa kohdassa B4: G4. Palautusarvoksi määritetään B5: G15. Tuloksena on, että sisempi XLOOKUP palauttaa taulukossa I10 esitetyn kaltaisen taulukon. : I20 alla. Koska A621 löytyy hakukentän viidennestä solusta ja 104 löydetään tuloskentän viidennessä solussa, saat oikean vastauksen kaavasta. Alla J6 näyttää vanhan tavan. J7 palauttaa uuden tavan.

XLookup J3 tililuettelossa A5: A15. Käytä tulostaulukossa XLOOKUP (J4, B4: G4, B5: G15). Tässä kaavassa B4: G4 on luettelo kuukausista. B5: G15 on suorakulmainen arvoryhmä kaikille tileille kaikkien kuukausien ajan. Toisessa solussa vain sisempi XLOOKUP näyttää, kuinka se palauttaa koko toukokuun arvosarakkeen.

XLOOKUP-etu 12: Summa kaikki hakuarvot yhteen kaavaan

Muinainen LOOKUP-toiminto tarjosi kaksi outoa temppua. Ensinnäkin, jos yrität selvittää kertyneiden bonuskulujen kokonaismäärän, voit pyytää LOOKUPia etsimään kaikki arvot yhdellä kaavalla. Alla olevassa kuvassa LOOKUP (C4: C14 tekee 11 hakua. Mutta LOOKUP-toiminto ei tarjonnut tarkkaa hakua ja vaati hakutaulukon lajittelua.

Hae 13 arvoa ja summa ne yhteen. Tämä toimi aiemmin LOOKUPin kanssa, mutta se toimii myös XLOOKUP: n kanssa. Määritä kaikki hakuarvot C4: C14 ensimmäiseksi argumentiksi. Kääri XLOOKUP SUM-toimintoon.

XLOOKUPin avulla voit määrittää alueen hakuluvuksi ja XLOOKUP palauttaa kaikki vastaukset. Etu on, että XLOOKUP voi tehdä tarkat ottelut.

Temppu, jonka avulla LOOKUPia käytetään kaikkien hakutulosten yhteenlaskemiseen, toimi vain Lookupin likimääräisen vastaavuuden kanssa. Täällä XLOOKUP tekee tarkan haun kaikille nimissä L4: L14 ja saa kaikki tulokset.

Bonusvinkki: Entä Twisted LOOKUP?

Excel MVP Mike Girvin näyttää usein temppu LOOKUP-toiminnosta, jossa Lookup_Vector on pystysuora ja Result_Vector on vaakasuora. XLOOKUP ei tue luonnollisesti tätä temppua. Mutta jos huijaat hiukan ja kääri tuloksen_kuvaaja TRANSPOSE-toimintoon, voit hallita kierrettyä hakua.

Tässä hakutaulu on pystysuora ja tulosryhmä vaakasuora. Vanha LOOKUP-toiminto pystyy käsittelemään tämän, mutta jos haluat tehdä sen XLOOKUP: n kanssa, joudut käärittämään jommankumman matriisin TRANSPOSE-muotoon.

Mielenkiintoisia artikkeleita...