Excel 2016 for Office 365 -tilaajat Windows- ja Mac-käyttöjärjestelmissä tukee nyt uutta XLOOKUP-toimintoa, jota mainostetaan huomattavasti yksinkertaisempana ja monipuolisempana korvaajana erittäin suositulle (mutta usein pahastuneelle) pystysuoralle hakutoiminnolle VLOOKUP (en tiedä mikä X on XLOOKUPissa tarkoittaa; eXtensive, kenties?).
Niille teistä, jotka eivät vielä tunne VLOOKUP-toimintoa (jota pidetään kolmanneksi eniten käytettynä funktiona heti SUMMA- ja AVERAGE-funktioiden jälkeen), tämä toiminto etsii pystysuoraan rivin mukaan määritetyn hakutaulukon vasemmanpuoleisimman sarakkeen ylhäältä alas, kunnes se löytää arvon hakusarake, joka on merkitty poikkeamanumerolla, joka vastaa tai ylittää etsimääsi lukua. Vaikka VLOOKUP-funktio on erittäin hyödyllinen tiettyjen kohteiden etsimiseen laskentataulukon pitkästä luettelosta tai sarakkeesta, VHAKU-funktiolla on useita rajoituksia, joita tämä uusi hakutoiminto ei jaa, kuten XHAKU:
XLOOKUP-funktiossa on viisi mahdollista argumenttia, joista kolme ensimmäistä ovat pakollisia ja kaksi viimeistä valinnaista, käyttämällä seuraavaa syntaksia:
XHAKU( hakuarvo , hakujono , paluutaulukko ,[ match_mode ],[ search_mode ])
Pakollinen lookup_value- argumentti määrittää arvon tai kohteen, jota etsit. Vaadittu look_up array -argumentti määrittää solualueen, josta tätä hakuarvoa etsitään, ja return_array- argumentti määrittää solualueen, joka sisältää arvon, jonka haluat palauttaa, kun Excel löytää tarkan vastaavuuden.
* Muista, että määrittäessäsi hakutaulukko- ja paluutaulukko-argumentteja XHAKU-funktiossasi molempien alueiden on oltava yhtä pitkiä, muuten Excel palauttaa #ARVO! virhe kaavassasi. Tämä on sitäkin enemmän syytä käyttää tietyn tietotaulukon alueen tai sarakkeen nimiä määritellessään näitä argumentteja sen sijaan, että osoitat niitä tai kirjoittaisit niiden soluviittauksia .
Valinnainen match_mode- argumentti voi sisältää minkä tahansa seuraavista neljästä arvosta:
Valinnainen search_mode- argumentti voi sisältää minkä tahansa seuraavista neljästä arvosta:
Paras tapa ymmärtää uuden XLOOKUP-funktion teho ja monipuolisuus on nähdä se toiminnassa Excel-laskentataulukossa. Seuraavassa kuvassa minulla on laskentataulukko, jossa on yksinkertainen vuoden 2019 myyntitietotaulukko maittain järjestettynä. Jos haluat palauttaa XLOOKUP:n avulla tämän taulukon kokonaismyynnin solussa E4 laskentataulukon soluun D4 syöttämäsi maan perusteella, toimi seuraavasti:
Aseta solukohdistin laskentataulukon soluun E4
Napsauta Kaavat-välilehden Haku ja viittaus -vaihtoehtoa ja sen jälkeen XHAKU avattavan valikon alareunassa avataksesi sen Function Arguments -valintaikkunan.
Napsauta laskentataulukon solua D4 syöttääksesi sen soluviittauksen Lookup_value-argumenttitekstiruutuun.
Paina Sarkainta valitaksesi Lookup_array-argumenttitekstiruutu, napsauta sitten solua A4 ja pidä Vaihto-näppäintä painettuna samalla, kun painat Ctrl-alas-nuolta valitaksesi A4:A8 haettavaksi alueeksi (koska alue A3:B8 on määritelty Excel-tietotaulukoksi, Taulukko1[Maa] näkyy tekstikentässä alueen A4:A8 tilalla).
Valitse Return_array-argumenttitekstiruutu painamalla sarkainta ja napsauta sitten solua B4 ja pidä vaihtonäppäintä painettuna samalla kun painat Ctrl-alasnuolta valitaksesi B4:B8 alueeksi, joka sisältää palautettavat arvot haun tulosten perusteella (joka näkyy Taulukko 1[Kokonaismyynti] tekstikentässä).
Napsauta OK syöttääksesi XLOOKUP-kaavan soluun E4.
Luodaan kaava XLOOKUPilla solussa E4, joka palauttaa myynnin soluun D4 syötetyn maan perusteella.
Excel syöttää XLOOKUP-kaavan laskentataulukon soluun E4 ja palauttaa tuloksena 4900, koska Costa Rica on tällä hetkellä syötetty hakusoluun D4 ja kuten näet vuoden 2019 myyntitaulukosta, tämä on todellakin tämän maan kokonaismyynti.
Koska XLOOKUP toimii oikealta vasemmalle yhtä hyvin kuin vasemmalta oikealle, voit käyttää tätä toimintoa yhtä hyvin palauttaaksesi maan tästä myyntitaulukosta tietyn myyntiluvun perusteella. Seuraava kuva näyttää, kuinka teet tämän. Tällä kertaa luot XHAKU-kaavan soluun D4 ja määrität soluun E4 syötetyn arvon (tässä tapauksessa 11 000) lookup_value-argumentiksi.
Lisäksi syötät match_mode-argumentiksi -1 ohittaaksesi funktion tarkan vastaavuuden oletusarvon, jolloin Excel palauttaa maan, joka vastaa tarkasti hakusoluun E4 syötettyä myyntiarvoa tai sen maan, jonka kokonaismyynti on seuraavaksi pienempi (Meksiko 10 000 dollaria tässä tapauksessa, koska tässä taulukossa ei ole maata, jonka kokonaismyynnistä on 11 000 dollaria). Ilman match_mode-argumentin määrittämistä tälle kaavalle, Excel palauttaisi tuloksena #NA, koska tässä myyntitaulukossa ei ole tarkkaa vastaavuutta 11 000 dollarilla.
Luodaan kaava, jossa on XLOOKUP solussa D4 ja joka palauttaa maan soluun E4 syötetyn myynnin perusteella
Koska XLOOKUP-toiminto on yhtä mukava etsiä vaakasuunnassa sarakkeen mukaan kuin se hakee pystysuunnassa rivin mukaan, voit käyttää sitä luomaan kaavan, joka suorittaa kaksisuuntaisen haun (korvaa tarpeen luoda kaava, joka yhdistää INDEX- ja MATCH-funktiot menneisyydessä). Seuraava kuva, joka sisältää vuoden 2019 tuotantoaikataulutaulukon osanumeroille AB-100 - AB-103 huhtikuusta joulukuuhun, näyttää, kuinka tämä tehdään.
Kaavan luominen sisäkkäisillä XLOOKUP-funktioilla palauttamaan tietyn kuukauden aikana tuotettujen yksiköiden lukumäärän
Solussa B12 loin seuraavan kaavan:
=XHAKU(osan_haku,$A$3:$A$6,XHAKU(päivämäärän_haku,$B$2:$J$2,$B$3:$J$6))
Tämä kaava alkaa määrittämällä XLOOKUP-funktio, joka etsii pystysuorassa rivillä tarkan vastaavuuden osamerkinnälle, joka on tehty soluun nimeltä part_lookup (tässä tapauksessa solu B10) tuotantotaulukon solualueella $A$3:$A$6. . Huomaa kuitenkin, että tämän alkuperäisen HAKU-funktion return_array-argumentti on itsessään toinen XHAKU-funktio.
Tämä toinen, sisäkkäinen XHAKU-funktio etsii solualueelta $B$2:$J$2 vaakasuunnassa sarakkeen mukaan tarkan vastaavuuden päivämäärämerkinnälle, joka on tehty soluun nimeltä date_lookup (tässä tapauksessa solu B11). Tämän toisen sisäkkäisen XLOOKUP-funktion return_array-argumentti on $B$3:$J$6, taulukon kaikkien tuotantoarvojen solualue.
Tämä kaava toimii siten, että Excel laskee ensin toisen, sisäkkäisen XLOOKUP-funktion tuloksen suorittamalla vaakahaun, joka tässä tapauksessa palauttaa taulukon 19. kesäkuuta -sarakkeen solualueella D3:D6 (arvoilla: 438, 153, 306 ja 779) sen tuloksena. Tästä tuloksesta puolestaan tulee alkuperäisen XHAKU-funktion return_array-argumentti, joka suorittaa pystysuoran haun rivin perusteella löytääkseen tarkan vastaavuuden soluun B11 tehtyyn osanumeromerkintään (nimeltään osa_haku). Koska tässä esimerkissä tämä osa_haku-solu sisältää AB-102:n, kaava palauttaa vain kesäkuun 19. päivän tuotantoarvon, 306, toisen, seuraavan XHAKU-funktion tuloksesta.
Siinä se on! Ensimmäinen katsaus XLOOKUPiin, tehokkaaseen, monipuoliseen ja melko helppokäyttöiseen uuteen hakutoimintoon, joka voi tehdä VLOOKUP- ja HLOOKUP-funktioiden suorittamien yhden arvon hakujen lisäksi myös kaksisuuntaisia arvohakuja yhdistämällä Myös INDEX- ja MATCH-toiminnot.
* Valitettavasti XLOOKUP-toiminto ei ole taaksepäin yhteensopiva Microsoft Excelin aiempien versioiden kanssa, jotka tukevat vain VLOOKUP- ja HLOOKUP-toimintoja, tai yhteensopiva sellaisten nykyisten versioiden kanssa, jotka eivät vielä sisällä sitä yhtenä hakutoiminnoistaan, kuten Excel 2019 ja Excel Online . Tämä tarkoittaa, että jos jaat XLOOKUP-kaavoja sisältävän työkirjan työtovereiden tai asiakkaiden kanssa, jotka käyttävät Excelin versiota, joka ei sisällä tätä uutta hakutoimintoa, kaikki nämä kaavat palauttavat #NAME? virhearvot, kun he avaavat sen laskentataulukon.
XLOOKUP-funktio etsii aluetta tai taulukkoa ja palauttaa sitten ensimmäisen löytämänsä osuman vastaavan kohteen. Jos vastaavuutta ei ole, XLOOKUP voi palauttaa lähimmän (likimääräisen) vastaavuuden.
=XHAKU(hakuarvo, hakutaulukko, palautustaulukko, [jos_ei_löydety], [vastaavuustila], [hakutila])
|
Perustelu |
Kuvaus |
|---|---|
|
haku_arvo Edellytetään* |
Haettava arvo |
|
lookup_array Edellytetään |
Hakutaulukko tai alue |
|
return_array Edellytetään |
Palautettava taulukko tai alue |
|
[if_not_found] Valinnainen |
Jos kelvollista vastaavuutta ei löydy, palauta antamasi teksti [if_not_found]. Jos kelvollista vastaavuutta ei löydy ja [if_not_found] puuttuu, #N/A palautetaan. |
|
[match_mode] Valinnainen |
Määritä hakutyyppi: 0 - Tarkka ottelu. Jos yhtään ei löydy, palauta #N/A. Tämä on oletusarvo. -1 - Tarkka ottelu. Jos mitään ei löydy, palauta seuraava pienempi tuote. 1 - Tarkka vastaavuus. Jos mitään ei löydy, palauta seuraava suurempi tuote. 2 – Jokerimerkkiosuma, jossa *, ? ja ~ on erityinen merkitys. |
|
[hakutila] Valinnainen |
Määritä käytettävä hakutila: 1 - Suorita haku alkaen ensimmäisestä kohdasta. Tämä on oletusarvo. -1 - Suorita käänteinen haku alkaen viimeisestä kohdasta. 2 – Suorita binäärihaku, joka perustuu siihen, että lookup_array lajitellaan nousevassa järjestyksessä. Jos niitä ei ole lajiteltu, virheelliset tulokset palautetaan. -2 - Suorita binäärihaku, joka perustuu siihen, että lookup_array lajitellaan laskevassa järjestyksessä. Jos niitä ei ole lajiteltu, virheelliset tulokset palautetaan. |
Esimerkki 1 käyttää XLOOKUP-komentoa maan nimen etsimiseen alueella ja palauttaa sen puhelinnumeron maakoodin. Se sisältää lookup_value (solu F2), lookup_array (alue B2: B11) ja return_array (alue D2:D11) -argumentit. Se ei sisällä match_mode -argumenttia, koska XLOOKUP tuottaa oletuksena tarkan vastaavuuden.
Huomaa: XLOOKUP käyttää hakutaulukkoa ja paluutaulukkoa, kun taas VLOOKUP käyttää yhtä taulukkotaulukkoa, jota seuraa sarakkeen indeksinumero. Vastaava VHAKU-kaava olisi tässä tapauksessa: =VHAKU(F2,B2:D11,3,FALSE)
————————————————————————————
Esimerkki 2 hakee työntekijän tiedot työntekijän tunnusnumeron perusteella. Toisin kuin VLOOKUP, XLOOKUP voi palauttaa taulukon, jossa on useita kohteita, joten yksi kaava voi palauttaa sekä työntekijän nimen että osaston soluista C5:D14.
————————————————————————————
Esimerkki 3 lisää if_not_found argumentin edelliseen esimerkkiin.
————————————————————————————
Esimerkki 4 etsee sarakkeesta C soluun E2 syötetyt henkilökohtaiset tulot ja löytää vastaavan veroprosentin sarakkeesta B. Se asettaa if_not_found argumentti palauttaa 0 (nolla), jos mitään ei löydy. match_mode -argumentin arvoksi on asetettu 1, mikä tarkoittaa, että funktio etsii tarkan vastaavuuden, ja jos se ei löydä sitä, se palauttaa seuraava isompi kohde. Lopuksi search_mode argumentin arvoksi asetetaan 1, mikä tarkoittaa funktiota hakee ensimmäisestä kohdasta viimeiseen.
Huomaa: XARRAYn lookup_array sarake on sarake, kun taas VLOOKUP voi katsoa vain vasemmalta oikealle.return_array
————————————————————————————
Esimerkki 5 käyttää sisäkkäistä XLOOKUP-funktiota sekä pysty- että vaakasuuntaisen vastaavuuden suorittamiseen. Se etsii ensin Bruttovoiton sarakkeesta B ja sen jälkeen Qtr1 taulukon ylimmällä rivillä (väli C5:F5) ja palauttaa lopuksi arvon näiden kahden leikkauspisteessä. Tämä on samanlaista kuin INDEX- ja MATCH-funktioiden käyttäminen yhdessä.
Vinkki: Voit myös korvata HLOOKUP-toiminnon käyttämällä XLOOKUPia.
Huomaa: Kaava soluissa D3:F3 on: =XHAKU(D2,$B6:$B17,XHAKU($C3, $C5:$G5, $C6:$G17)).
————————————————————————————
Esimerkki 6 käyttää SUMMA-funktiota ja kahta sisäkkäistä XLOOKUP-funktiota kaikkien kahden alueen välisten arvojen summaamiseen. Tässä tapauksessa haluamme laskea yhteen rypäleiden, banaanien ja päärynöiden arvot, jotka ovat näiden kahden välillä.
Solun E3 kaava on: =SUMMA(XHAKU(B3,B6:B10,E6:E10):XHAKU(C3,B6:B10,E6:E10))
Miten se toimii? XLOOKUP palauttaa alueen, joten kun se laskee, kaava näyttää tältä: =SUM($E$7:$E$9). Voit nähdä, miten tämä toimii, valitsemalla solun, jolla on samanlainen XLOOKUP-kaava kuin tämä, ja valitsemalla sitten Kaavat > Kaavan tarkastus > Arvioi kaava ja valitse sitten Arvioi käydäksesi laskutoimituksen läpi.
Kuinka estää Microsoft Wordia avaamasta tiedostoja vain luku -tilassa Windowsissa Microsoft Word avaa tiedostot vain luku -tilassa, jolloin niiden muokkaaminen on mahdotonta? Älä huoli, menetelmät ovat alla
Virheiden korjaaminen virheellisten Microsoft Word -asiakirjojen tulostuksessa. Virheet tulostettaessa Word-asiakirjoja, joissa on muuttuneet fontit, sotkuiset kappaleet, puuttuva teksti tai kadonnut sisältö ovat melko yleisiä. Älä kuitenkaan
Jos olet piirtänyt PowerPoint-dioihin esityksen aikana kynää tai korostuskynää, voit tallentaa piirustukset seuraavaa esitystä varten tai poistaa ne, jotta seuraavan kerran näytät ne. Aloitat puhtailla PowerPoint-dioilla. Pyyhi kynä- ja korostuskynäpiirrokset noudattamalla näitä ohjeita: Pyyhi rivit yksi kerrallaan […]
Tyylikirjasto sisältää CSS-tiedostoja, Extensible Stylesheet Language (XSL) -tiedostoja ja kuvia, joita käyttävät ennalta määritetyt sivupohjat, sivuasettelut ja säätimet SharePoint 2010:ssä. CSS-tiedostojen etsiminen julkaisusivuston tyylikirjastosta: Valitse Sivuston toiminnot→ Näytä Kaikki sivuston sisältö. Sivuston sisältö tulee näkyviin. Style-kirjasto sijaitsee […]
Älä hukuta yleisöäsi jättimäisillä numeroilla. Microsoft Excelissä voit parantaa koontinäyttöjesi ja raporttiesi luettavuutta muotoilemalla numerot näyttämään tuhansia tai miljoonia.
Opi käyttämään SharePointin sosiaalisen verkostoitumisen työkaluja, joiden avulla yksilöt ja ryhmät voivat kommunikoida, tehdä yhteistyötä, jakaa ja pitää yhteyttä.
Juliaanisia päivämääriä käytetään usein valmistusympäristöissä aikaleimana ja pikaviitteenä eränumerolle. Tämän tyyppisen päivämääräkoodauksen avulla jälleenmyyjät, kuluttajat ja huoltoedustajat voivat tunnistaa tuotteen valmistusajankohdan ja siten tuotteen iän. Julian-päivämääriä käytetään myös ohjelmoinnissa, armeijassa ja tähtitiedessä. Erilainen […]
Voit luoda verkkosovelluksen Access 2016:ssa. Mikä verkkosovellus sitten on? No, verkko tarkoittaa, että se on verkossa, ja sovellus on vain lyhenne sanoista "sovellus". Mukautettu verkkosovellus on online-tietokantasovellus, jota käytetään pilvestä selaimen avulla. Rakennat ja ylläpidät verkkosovellusta työpöytäversiossa […]
Useimmat SharePoint 2010:n sivut näyttävät navigointilinkkien luettelon pikakäynnistyspalkissa sivun vasemmassa reunassa. Pikakäynnistyspalkki näyttää linkit esitellyn sivuston sisältöön, kuten luetteloihin, kirjastoihin, sivustoihin ja julkaisusivuihin. Pikakäynnistyspalkki sisältää kaksi erittäin tärkeää linkkiä: Koko sivuston sisältö -linkki: […]
Yksinkertaisille ongelmille Excelin Ratkaisija löytää yleensä nopeasti optimaaliset Ratkaisijamuuttujien arvot tavoitefunktiolle. Mutta joissakin tapauksissa Ratkaisijalla on vaikeuksia löytää Solver-muuttujan arvoja, jotka optimoivat tavoitefunktion. Näissä tapauksissa Ratkaisija näyttää yleensä viestin tai virheilmoituksen, joka kuvaa tai käsittelee ongelmaa, jonka […]
