✅ Löydä lähin ottelu Excelistä kaavojen avulla

Sisällysluettelo

Etsi lähin ottelu Excelistä

Excel -toiminnot voivat olla erittäin tehokkaita, jos haluat yhdistellä erilaisia kaavoja. Asiat, jotka saattoivat tuntua mahdottomilta, alkavat yhtäkkiä näyttää lapsen leikiltä.

Yksi esimerkki on löytää hakuarvon lähin vastaavuus Excelin tietojoukosta.

Excelissä on pari hyödyllistä hakutoimintoa (kuten VLOOKUP & INDEX MATCH), jotka voivat löytää lähimmän vastaavuuden muutamissa yksinkertaisissa tapauksissa (kuten näytän alla olevilla esimerkeillä).

Mutta parasta on, että voit yhdistää nämä hakutoiminnot muihin Excel -toimintoihin saadaksesi paljon enemmän aikaan (mukaan lukien etsintäarvon lähimmän vastaavuuden löytäminen lajittelemattomasta luettelosta).

Tässä opetusohjelmassa näytän sinulle, kuinka löydät Excel -haun arvon lähimmän haun hakukaavojen avulla.

Etsi lähin ottelu Excelistä

Voi olla monia erilaisia skenaarioita, joissa sinun on etsittävä lähin vastaavuus (tai lähin vastaava arvo).

Alla on esimerkkejä, joita käsittelen tässä artikkelissa:

Etsi provisioprosentti myynnin perusteella
Löydä paras ehdokas (lähimmän kokemuksen perusteella)
Seuraavan tapahtuman päivämäärän löytäminen

Aloitetaan!

Lataa esimerkkitiedosto napsauttamalla tätä

Etsi provisioprosentti (etsit lähimmän myyntiarvon)

Oletetaan, että sinulla on alla näkyvä tietojoukko, josta haluat löytää kaikkien myyntihenkilöstön provisioprosentit.

Palkkiot jaetaan myyntiarvon perusteella. Ja tämä lasketaan oikealla olevasta taulukosta.

Jos esimerkiksi myyjä tekee 5000 kokonaismyynnin, palkkio on 0% ja jos hän tekee 15000 kokonaismyynnin, komissio on 5%.

Jos haluat saada provisioprosentin, sinun on löydettävä lähin myyntialue, joka on juuri alempi kuin Myynti -arvo. Esimerkiksi 15000 myyntiarvolla provisio olisi 10 000 (mikä on 5%) ja myyntiarvolla 25 000 provisio olisi 20 000 (mikä on 7%).

Löytääksesi lähimmän myyntiarvon ja saadaksesi provisioprosentin, voit käyttää likimääräistä vastaavuutta VLOOKUPissa.

Alla oleva kaava tekisi tämän:

= HAKU (B2, $ E $ 2: $ F $ 6,2,1)

Huomaa, että tässä kaavassa viimeinen argumentti on 1, joka käsittää kaavan käyttämään likimääräistä hakua. Tämä tarkoittaa, että kaava käy läpi sarakkeen E myyntiarvot ja löytää arvon, joka on vain pienempi kuin hakuarvo.

Sitten VLOOKUP -kaava antaa tämän arvon provisioprosentin.

Merkintä: Jotta tämä toimisi, sinun on järjestettävä tiedot nousevaan järjestykseen.

Lataa esimerkkitiedosto napsauttamalla tätä

Löydä paras ehdokas (lähimmän kokemuksen perusteella)

Yllä olevassa esimerkissä tiedot piti lajitella nousevaan järjestykseen. Mutta voi olla tapauksia, joissa tietoja ei lajitella.

Tarkastellaan siis esimerkkiä ja katsotaan, kuinka voimme löytää lähimmän vastaavuuden Excelistä kaavojen yhdistelmän avulla.

Alla on esimerkki tietojoukosta, josta minun on löydettävä työntekijän nimi, jolla on työkokemus, joka on lähinnä haluttua arvoa. Haluttu arvo tässä tapauksessa 2,5 vuodessa.

Huomaa, että tietoja ei ole lajiteltu. Myös lähin kokemus voi olla joko vähemmän tai enemmän kuin antaa kokemus. Esimerkiksi 2 vuotta ja 3 vuotta ovat molemmat yhtä lähellä (ero 0,5 vuotta).

Alla on kaava, joka antaa meille tuloksen:

= INDEKSI ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Tämän kaavan temppu on muuttaa hakutoimintoa ja hakuarvoa, jotta löydetään vähimmäiskokemusero vaadituissa ja todellisissa arvoissa.

Ymmärrämme ensin, miten teet sen manuaalisesti (ja sitten selitän, miten tämä kaava toimii).

Kun teet tämän manuaalisesti, käyt sarakkeen B jokaisen solun läpi ja huomaat, mikä on ero vaaditun ja henkilön kokemuksen välillä. Kun sinulla on kaikki erot, löydät vähimmäismäärän ja haet kyseisen henkilön nimen.

Juuri tätä teemme tällä kaavalla.

Anna minun selittää.

MATCH-kaavan hakuarvo on MIN (ABS (D2-B2: B15)).

Tämä osa antaa sinulle pienimmän erot annetun kokemuksen (joka on 2,5 vuotta) ja kaikkien muiden kokemusten välillä. Tässä esimerkissä se palauttaa 0,3

Huomaa, että olen käyttänyt ABS: ää varmistaakseni, että etsin lähintä (mikä voi olla enemmän tai vähemmän kuin annettu kokemus).

Tästä vähimmäisarvosta tulee nyt hakuarvo.

MATCH-funktion hakutoiminto on ABS (D2- $ B $ 2: $ B $ 15).

Tämä antaa meille joukon numeroita, joista 2,5 (vaadittu kokemus) on vähennetty.

Nyt meillä on hakuarvo (0,3) ja hakutoiminto ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

MATCH -funktio löytää tämän taulukon sijainnin 0.3, joka on myös sen henkilön nimi, jolla on lähin kokemus.

INDEX -funktio käyttää tätä paikkanumeroa palauttaakseen henkilön nimen.

Huomautus: Jos on useita ehdokkaita, joilla on sama vähimmäiskokemus, yllä oleva kaava antaa ensimmäisen vastaavan työntekijän nimen.

Etsi seuraavan tapahtuman päivämäärä

Tämä on toinen esimerkki, jossa voit käyttää hakukaavoja löytääksesi tapahtuman seuraavan päivämäärän kuluvan päivämäärän perusteella.

Alla on tietojoukko, jossa minulla on tapahtumien nimet ja tapahtumien päivämäärät.

Haluan seuraavan tapahtuman nimen ja tämän tulevan tapahtuman päivämäärän.

Alla on kaava, joka antaa tulevan tapahtuman nimen:

= INDEKSI ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Ja alla oleva kaava antaa tulevan tapahtuman päivämäärän:

= INDEKSI ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Selitän kuinka tämä kaava toimii.

Tapahtuman päivämäärän saamiseksi MATCH -toiminto etsii nykyisen päivämäärän sarakkeesta B. Tässä tapauksessa emme etsi tarkkaa vastaavuutta, vaan likimääräistä. Siksi MATCH -funktion viimeinen argumentti on 1 (joka löytää suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo).

Joten MATCH -funktio palauttaa sen solun sijainnin, jonka päivämäärä on vain pienempi tai yhtä suuri kuin nykyinen päivämäärä. Joten seuraava tapahtuma, tässä tapauksessa, olisi seuraavassa solussa (koska luettelo on lajiteltu nousevaan järjestykseen).

Joten saadaksesi tulevan tapahtuman päivämäärän lisäämällä vain yhden MATCH -funktion palauttamaan solun sijaintiin, ja se antaa sinulle seuraavan tapahtumapäivän solun sijainnin.

Tämän arvon antaa sitten INDEX -funktio.

Tapahtuman nimen saamiseksi käytetään samaa kaavaa ja INDEX -funktion alue muutetaan sarakkeesta B sarakkeeseen A.

Lataa esimerkkitiedosto napsauttamalla tätä

Idea tästä esimerkistä tuli mieleeni, kun ystäväni pyysi pyyntöä. Hänellä oli sarakkeessa luettelo kaikista ystävien/sukulaisten syntymäpäivistä ja hän halusi tietää seuraavan syntymäpäivän (ja henkilön nimen).

Nämä ovat kolme esimerkkiä, jotka osoittavat, kuinka löytää lähin vastaava arvo Excelissä hakukaavojen avulla.

Saatat pitää myös seuraavista Excel -vihjeistä/opetusohjelmista