Sisällys
Käytännössä hyvin usein on tapauksia, joissa sinun ja minun on löydettävä lähin arvo joukosta (taulukosta) suhteessa annettuun numeroon. Se voisi olla esimerkiksi:
- Alennuslaskelma määrästä riippuen.
- Bonusten määrän laskeminen suunnitelman toteutumisesta riippuen.
- Toimituskulujen laskeminen etäisyyden mukaan.
- Sopivien säiliöiden valinta tavaroille jne.
Lisäksi pyöristäminen voi olla tarpeen sekä ylös- että alaspäin tilanteesta riippuen.
On olemassa useita tapoja – ilmeisiä ja ei niin ilmeisiä – ratkaista tällainen ongelma. Katsotaanpa niitä peräkkäin.
Aluksi kuvitellaan toimittaja, joka antaa alennuksia tukkumyynnistä, ja alennuksen prosenttiosuus riippuu ostettujen tavaroiden määrästä. Esimerkiksi yli 5 kappaletta ostettaessa annetaan 2 % alennus ja 20 kappaleesta ostettaessa jo 6 % jne.
Kuinka nopeasti ja kauniisti laskea alennusprosentti syöttäessäsi ostettujen tavaroiden määrää?
Tapa 1: Sisäkkäiset IF:t
Menetelmä sarjasta "mitä siinä on ajatella - sinun täytyy hypätä!". Sisäkkäisten funktioiden käyttäminen IF (JOS) tarkistaaksesi peräkkäin, osuuko solun arvo kuhunkin aikaväliin, ja näyttääksesi alennuksen vastaavalle alueelle. Mutta kaava tässä tapauksessa voi osoittautua erittäin hankalaksi:
Mielestäni on selvää, että tällaisen "hirviönuken" virheenkorjaus tai yrittäminen lisätä siihen pari uutta ehtoa jonkin ajan kuluttua on hauskaa.
Lisäksi Microsoft Excelissä on sisäkkäisrajoitus IF-funktiolle – 7 kertaa vanhemmissa versioissa ja 64 kertaa uudemmissa versioissa. Mitä jos tarvitset lisää?
Menetelmä 2. VLOOKUP intervallinäkymällä
Tämä menetelmä on paljon kompaktimpi. Alennusprosentin laskemiseksi käytä legendaarista funktiota VPR (HAKU) likimääräisessä hakutilassa:
jossa
- B4 – tavaramäärän arvo ensimmäisessä tapahtumassa, josta haemme alennusta
- G$4:$H$8 – linkki alennustaulukkoon – ilman "otsikkoa" ja osoitteet on kiinnitetty $-merkillä.
- 2 — sen alennustaulukon sarakkeen järjestysnumero, josta halutaan saada alennusarvo
- TOSI – tähän "koira" on haudattu. Jos viimeisenä funktion argumenttina VPR eritellä VAHELEMINEN (VÄÄRÄ) tai 0, toiminto etsii tiukka ottelu määrä-sarakkeessa (ja meidän tapauksessamme se antaa #N/A-virheen, koska alennustaulukossa ei ole arvoa 49). Mutta jos sen sijaan VAHELEMINEN kirjoittaa TOSI (TOTTA) tai 1, funktio ei etsi tarkkaa, vaan lähin pienin arvoa ja antaa meille tarvitsemamme alennuksen prosenttiosuuden.
Tämän menetelmän haittapuoli on tarve lajitella alennustaulukko nousevaan järjestykseen ensimmäisen sarakkeen mukaan. Jos tällaista lajittelua ei ole (tai se tehdään käänteisessä järjestyksessä), kaavamme ei toimi:
Näin ollen tätä lähestymistapaa voidaan käyttää vain lähimmän pienimmän arvon löytämiseen. Jos sinun on löydettävä lähin suurin, sinun on käytettävä erilaista lähestymistapaa.
Tapa 3. Lähimmän suurimman etsiminen INDEX- ja MATCH-funktioiden avulla
Katsotaan nyt ongelmaamme toiselta puolelta. Oletetaan, että myymme useita eri tehoisia teollisuuspumppumalleja. Vasemmalla oleva myyntitaulukko näyttää asiakkaan tarvitseman tehon. Meidän on valittava pumppu, jonka teho on lähin suurin tai yhtä suuri, mutta ei pienempi kuin mitä projekti vaatii.
VLOOKUP-toiminto ei auta tässä, joten sinun on käytettävä sen analogia - joukko INDEX-toimintoja (INDEKSI) ja LISÄÄ ALTISTUMISTA (OTTELU):
Tässä MATCH-funktio, jolla on viimeinen argumentti -1, toimii tilassa, jossa etsitään lähin suurin arvo, ja INDEX-funktio poimii sitten tarvitsemamme mallin nimen viereisestä sarakkeesta.
Tapa 4. Uusi toiminto VIEW (XLOOKUP)
Jos sinulla on Office 365:n versio, johon on asennettu kaikki päivitykset, VLOOKUPin sijaan (HAKU) voit käyttää sen analogia - VIEW-toimintoa (XHAKU), joita olen jo analysoinut yksityiskohtaisesti:
Tässä:
- B4 – sen tuotteen määrän alkuarvo, josta haemme alennusta
- $4 G$: $8 – valikoima, josta etsimme otteluita
- $H$4:$H$8 – tulosalue, josta haluat palauttaa alennuksen
- neljäs argumentti (-1) sisältää lähimmän pienimmän haluamamme numeron haun tarkan haun sijaan.
Tämän menetelmän etuja ovat, että alennustaulukkoa ei tarvitse lajitella ja mahdollisuus etsiä tarvittaessa paitsi lähin pienin, myös lähin suurin arvo. Viimeinen argumentti tässä tapauksessa on 1.
Mutta valitettavasti kaikilla ei vielä ole tätä ominaisuutta – vain Office 365:n onnellisilla omistajilla.
Tapa 5. Power Query
Jos et vielä tunne tehokasta ja täysin ilmaista Power Query -apuohjelmaa Excelille, olet täällä. Jos olet jo tuttu, yritämme käyttää sitä ongelmamme ratkaisemiseen.
Tehdään ensin valmistelutöitä:
- Muunnetaan lähdetaulukot dynaamiksi (älykkääksi) pikanäppäimen avulla Ctrl+T tai joukkue Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona).
- Selvyyden vuoksi annetaan heille nimet. Myynti и Alennukset kieleke Rakentaja (Design).
- Lataa kukin taulukko vuorotellen Power Queryyn -painikkeella Taulukosta/alueesta kieleke Päiväys (Tiedot – taulukosta/alueesta). Excelin uusimmissa versioissa tämä painike on nimetty uudelleen muotoon Lehtien kanssa (Arkista).
- Jos taulukoilla on eri sarakkeiden nimet määrien kanssa, kuten esimerkissämme ("Tavaramäärä" ja "Määrä alkaen…"), ne on nimettävä uudelleen Power Queryssä ja nimettävä samalla tavalla.
- Tämän jälkeen voit palata takaisin Exceliin valitsemalla komennon Power Query -editoriikkunassa Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa...) ja sitten vaihtoehto Luo vain yhteys (Luo vain yhteys).
- Sitten alkaa mielenkiintoisin. Jos sinulla on kokemusta Power Querysta, niin oletan, että jatkoajattelun pitäisi olla näiden kahden taulukon yhdistäminen liitoskyselyllä (merge) a la VLOOKUP, kuten edellisessä menetelmässä. Itse asiassa meidän on yhdistettävä lisäystilassa, mikä ei ole ollenkaan ilmeistä ensi silmäyksellä. Valitse Excel-välilehdeltä Tiedot – Hae tiedot – Yhdistä pyynnöt – Lisää (Tiedot - Hae tiedot - Yhdistä kyselyt - Liitä) ja sitten meidän pöydät Myynti и Alennukset näkyviin tulevassa ikkunassa:
- Napsauttamisen jälkeen OK pöytämme liimataan yhdeksi kokonaisuudeksi – toistensa alle. Huomaa, että näiden taulukoiden tavaroiden määrät sisältävät sarakkeet putosivat toistensa alle, koska. niillä on sama nimi:
- Jos myyntitaulukon alkuperäinen rivisarja on sinulle tärkeä, niin jotta voit palauttaa sen kaikkien myöhempien muutosten jälkeen, lisää numeroitu sarake taulukkoomme komennolla Sarakkeen lisääminen – hakemistosarake (Lisää sarake — Hakemistosarake). Jos rivien järjestyksellä ei ole sinulle merkitystä, voit ohittaa tämän vaiheen.
- Käytä nyt taulukon otsikon avattavaa luetteloa ja lajittele se sarakkeen mukaan Määrä Nouseva:
- Ja tärkein temppu: napsauta hiiren kakkospainikkeella sarakkeen otsikkoa Alennus valitse joukkue Täytä (Täytä). Tyhjennä solut nolla automaattisesti täytetään aiemmilla alennusarvoilla:
- On vielä palautettava alkuperäinen rivijärjestys lajittelemalla sarakkeen mukaan indeksi (voit poistaa sen turvallisesti myöhemmin) ja päästä eroon tarpeettomista viivoista suodattimen avulla nolla sarakkeen mukaan Tapahtumakoodi:
- VLOOKUP-toiminnon käyttäminen tietojen etsimiseen ja etsimiseen
- VLOOKUP:n (VLOOKUP) käyttäminen on kirjainkoolla merkitystä
- XNUMXD VLOOKUP (VLOOKUP)