Excel

Kuinka etsiä ensimmäinen ja viimeinen ottelu

How Lookup First

Yksi Excelin hakutoimintojen hämmentävimmistä näkökohdista on ymmärtää, kuinka saada ensimmäinen tai viimeinen ottelu useista vastaavista tietojoukoista. Tämä johtuu siitä, että Excelin toiminta muuttuu kahden tekijän mukaan: suoritatko tarkan tai likimääräisen haun ja onko tiedot lajiteltu vai ei.





Oletetaan esimerkiksi, että haemme 'vihreän' hinnan alla olevista tiedoista VLOOKUPilla. Minkä hinnan saamme?

VLOOKUP minkä ottelun saamme?





Lue vastaus ja lisää mielenkiintoisia esimerkkejä.

Huomautuksia:



Tarkka ottelu = ensimmäinen

Kun teet tarkan ottelun, saat aina ensimmäisen osuman, piste. Ei ole väliä onko tiedot lajiteltu vai ei. Alla olevassa näytössä E5: n hakuarvo on 'punainen'. The VLOOKUP -toiminto , tarkan haun tilassa, palauttaa ensimmäisen ottelun hinnan:

 
= VLOOKUP (E5,data,2,FALSE)

VLOOKUP tarkka haku löytää ensimmäisen osuman

Huomaa, että VLOOKUPin viimeinen argumentti on EPÄTOSI pakottaakseen täsmällisen vastaavuuden.

Arvioitu ottelu = viimeinen

Jos teet likimääräisen ottelun, ja tiedot lajitellaan hakuarvon mukaan , saat viimeisen ottelun. Miksi? Koska likimääräisen ottelun aikana Excel skannaa arvoja, kunnes hakuarvoa suurempi arvo löytyy, niin se astuu takaisin edelliseen arvoon.

Alla olevassa näytössä VLOOKUP on asetettu likimääräiseen hakutilaan ja värit lajitellaan. VLOOKUP palauttaa viimeisen vihreän hinnan:

Excelin kertasumman tulevaisuuden arvo
 
= VLOOKUP (E5,data,2,TRUE)

VLOOKUP likimääräinen ottelu löytää viimeisen osuman

Huomaa, että VLOOKUPin viimeinen argumentti on TOSI likimääräisen vastaavuuden osalta.

Arvioitu ottelu + lajittelemattomat tiedot = vaara

Normaaleilla likimääräisillä hauilla, tiedot on lajiteltava hakuarvon mukaan . Lajittelemattomien tietojen kanssa saatat nähdä normaalin näköisiä tuloksia, jotka ovat täysin vääriä. Tämä ongelma on todennäköisempi VLOOKUPin kanssa, koska VLOOKUP on oletusarvoisesti likimääräinen ottelu kun neljättä argumenttia ei esitetä.

Tämän ongelman havainnollistamiseksi katso alla oleva esimerkki. Data on lajittelematon ja VLOOKUP, ilman neljättä argumenttia, oletuksena on likimääräinen ottelu. Huomaa, ettei 'punaista' ole hintaan 17,00 dollaria, mutta VLOOKUP palauttaa onnellisesti tämän virheellisen tuloksen:

 
= VLOOKUP (E5,data,2)

Esimerkki VLOOKUPin likimääräisestä osumasta väärä tulos

Tästä syystä suosittelen aina asettamaan viimeisen argumentin VLOOKUPille nimenomaisesti: TOSI = likimääräinen vastaavuus, EPÄTOSI = tarkka vastaavuus. Argumentti On valinnainen, mutta arvon antaminen saa sinut ajattelemaan sitä ja muistuttaa visuaalisesti tulevaisuudessa.

Seuraavassa kerromme, miten voitamme edellisen ottelun ja lajittelemattoman datan ongelman.

'Normaali' likimääräinen ottelu

Tässä vaiheessa saatat tuntea olosi hieman hämmentyneeksi ja hämmentyneeksi ajatuksesta, että likimääräinen osuma voi joissakin tapauksissa palauttaa viimeisen ottelun. Jos näin on, älä huoli. Arvioidun vastaavuuden käyttäminen viimeisen osuman saamiseksi ei ole 'normaali' tapaus. Yleensä näet likimääräisen osuman, jota käytetään arvojen määrittämiseen jonkin asteikon mukaan. Klassinen esimerkki on VLOOKUPin käyttäminen likimääräisessä ottelutilassa arvosanojen määrittämiseen, mikä toimii kauniisti:

kuinka valita tiedot kaavioon Excelissä
 
= VLOOKUP (E5,key,2,TRUE)

VLOOKUP likimääräinen ottelu arvosanojen antamiseksi

Tällaisissa tapauksissa hakutaulukko tarkoituksella ei sisällä päällekkäisiä arvoja, joten koko ajatus 'viimeisestä vastaavasta arvosta' on merkityksetön. Lisätietoja tästä kaavasta täältä .

Yllä olevien tietojen tarkoituksena on tarjota taustaa ja kontekstia siitä, miten täsmäytys toimii Excelissä, jotta alla kuvatut lähestymistavat ovat järkeviä.

Käytännön sovellukset

Miten voimme käyttää edellä kuvattua käyttäytymistä käytännön tilanteessa? Yksi yleinen skenaario on etsiä kohteen viimeisin tai viimeinen merkintä. Esimerkiksi alla käytämme VLOOKUPia likimääräisessä ottelutilassa löytääksemme Sandaalien uusimman hinnan. Ilmoitustiedot lajitellaan kohteen, sitten päivämäärän mukaan, joten tietyn tuotteen viimeisin hinta näkyy viimeisenä:

 
= VLOOKUP (F5,data,3,TRUE)

VLOOKUP likimääräinen ottelu + lajiteltu data = viimeisin hinta

INDEKSI ja MATCH

Myös muita hakutoimintoja voidaan käyttää tällä tavalla. Alla käytämme vastaavaa INDEKSI ja OTTELU kaava löytää uusimman hinnan samoilla tiedoilla. Huomaa, että MATCH on määritetty vastaamaan laskevaa järjestystä lajiteltujen kohteiden likimääräistä vastaavuutta asettamalla kolmannen argumentin arvoksi 1:

 
= INDEX (price, MATCH (F5,item,1))

INDEX ja MATCH arvioivat viimeisimmän hinnan

LOOKUP -toiminto

The LOOKUP -toiminto voidaan käyttää myös tässä tapauksessa. KATSO YLÖS aina suorittaa likimääräisen ottelun, joten se toimii hyvin 'viimeisen ottelun' skenaarioissa. Kaava on melko yksinkertainen:

 
= LOOKUP (F5,item,price)

HAKU -toiminto löytää uusimman hinnan

Viimeinen ottelu lajittelemattomalla datalla

Entä jos haluat viimeisen osuman, mutta tietoja ei lajitella hakuarvon mukaan? Toisin sanoen, haluat käyttää ehtoja löytääksesi vastaavuuden ja haluat vain datasi viimeisen kohteen, joka vastaa kriteerejäsi? Tämä on itse asiassa tapaus, jossa LOOKUP -toiminto loistaa, koska LOOKUP pystyy käsittelemään matriisitoimintoja luonnollisesti ilman control + shift + enter. Tämä tarkoittaa, että voimme rakentaa dynaamisesti hakuryhmän haluamamme datan löytämiseksi yksinkertaisten loogisten lausekkeiden avulla.

Katso esimerkiksi alla olevaa kaavaa:

 
= LOOKUP (2,1/(item=F5),price)

LOOKUP -toiminto löytää viimeisen ottelun lajittelemattomien tietojen kanssa

Tämä kaava löytää uusimman hinnan Sandaalit vuonna lajittelematon tiedot.

Et ehkä ole nähnyt tällaista kaavaa ennen, joten hajotetaan se vaiheittain. Työskentelemme sisältä ulospäin ja käytämme ensin kriteereitä yksinkertaisella loogisella lausekkeella:

 
item=F5

Tästä seuraa matriisi arvoista TOSI ja EPÄTOSI, jossa TOSI vastaa kohteita, jotka ovat sandaaleja, ja EPÄTOSI vastaa kaikkia muita arvoja:

 
{FALSETRUEFALSETRUEFALSEFALSETRUEFALSE}

Seuraavaksi jaamme luvun 1 tällä taulukolla. Jaon aikana TOSI tulee 1 ja EPÄTOSI nollaksi, joten sinun pitäisi visualisoida operaatio näin:

 
1/{01010010}

Yksi jaettuna yhdellä on yksi ja yksi jaettuna nollalla on #DIV/0, joten tuloksena on toinen taulukko, joka sisältää vain 1s ja #DIV/0 virheitä:

 
{#DIV/0!1#DIV/0!1#DIV/0!#DIV/0!1#DIV/0!}

Älä huoli, tähän hulluuteen on olemassa menetelmä :)

excel summa soluja, joissa on tietty teksti

Nyt olet ehkä huomannut, että hakuarvo on numero 2. Tämä voi tuntua hämmentävältä. Kuinka LOOKUP löytää koskaan numeron 2 taulukosta, joka sisältää vain 1: t ja virheet? Ei tule. Käytämme 2 -arvoa hakuarvoksi pakottaaksemme LOOKUPin skannaamaan kohteeseen tietojen lopussa .

LOOKUP -toiminto jättää automaattisesti huomiotta virheet, joten ainoa jäljellä oleva asia on 1s. Se skannaa 1: n läpi etsien kahta, jota ei koskaan löydy. Kun se saavuttaa taulukon loppuun, se 'astuu taaksepäin' viimeiseen kelvolliseen arvoon - viimeiseen 1 - joka vastaa viimeistä vastaavuutta annettujen ehtojen perusteella.

INDEX- ja MATCH -matriisiversio

Kauneus LOOKUP -toiminto pystyykö se käsittelemään yllä kuvattua matriisitoimintoa luontaisesti ilman, että sinun tarvitsee syöttää taulukon kaava Ctrl + Vaihto + Enter. Voit kuitenkin varmasti käyttää taulukkokaavaa, jos haluat. Tässä on vastaava INDEX- ja MATCH -kaava, joka on annettava näppäimillä control + shift + enter:

 
{= INDEX (price, MATCH (2,1/(item=F5),1))}

Viimeinen ei-tyhjä solu

Yllä oleva lähestymistapa osoittautuu todella hyödylliseksi. Esimerkiksi hienosäätämällä logiikkaa hiukan voimme löytää esimerkiksi sarakkeen viimeisen tyhjän solun:

 
= LOOKUP (2,1/(B:B''),B:B)

Etsi viimeisen ei-tyhjän solun LOOKUP-näppäimellä

Tämä kaava on kuvattu tarkemmin täällä .

Etsi n. Ottelu? Kaikki ottelut?

Jos olet päässyt tähän asti, saatat ihmetellä, miten löydät toisen tai kolmannen ottelun tai miten haet kaikki ottelut? Tässä muutamia linkkejä sinulle:

Huomaat, että tällaiset kaavat muuttuvat monimutkaisiksi. Vuonna 2019 Exceliin tulee uusia hienoja toimintoja, jotka tekevät näistä ratkaisuista paljon yksinkertaisempia. Pysy kanavalla. Sillä välin, älä unohda sitä Pivot -taulukot ovat loistava tapa tutkia tietoja ilman kaavoja .

Mitä seuraavaksi?

Alla on oppaita saadaksesi lisätietoja Excel -kaavoista. Tarjoamme myös online -videokoulutus .

  • Kaavan perusteet - jos olet vasta aloittamassa
  • 500 kaavan esimerkkiä täydellisin selityksin
  • 101 tärkeää Excel -toimintoa
  • Opas kaikkiin Excel -toimintoihin (Työn alla)
  • Kaavaehdot - 50 esimerkkiä
  • Ehdollisen muotoilun kaavat
Kirjailija Dave Bruns


^