Excel-edistyneet kaavat (sisällysluettelo)

  • Johdatus edistyneisiin kaavoihin Excelissä
  • Esimerkkejä edistyneestä kaavasta Excelissä

Johdatus edistyneisiin kaavoihin Excelissä

Kun olet saavuttanut keskitason Excelissä, sinun on työskenneltävä ahkerasti edistyäksesi korkeatasolle. Jotta voit siirtyä edistyneelle tasolle, sinun on oltava tietoinen joistakin usein käytetyistä edistyneistä kaavoista. Tässä artikkelissa kerron 10 parasta edistynyttä kaavaa, jotka kaikkien excel-oppijoiden on tiedettävä. Seuraa tätä artikkelia oppiaksesi ja tutkimaan.

Esimerkkejä edistyneestä kaavasta Excelissä

Ymmärretään, miten Advanced Formulas -sovellusta voidaan käyttää Excelissä, esimerkkejä.

Voit ladata tämän Advanced Formulas Excel -mallin täältä - Advanced Formulas Excel Template

Esimerkki # 1 - Osittainen VLOOKUP-toiminto

Sinun on täytynyt olla havainnut tilanne, jossa VLOOKUP heittää virheen, vaikka hakuarvossa olisi ollut pieni virhe. Jos etsit esimerkiksi Abhishek Sinha -nimen palkkaa ja jos sinulla on vain Abhishek, VLOOKUP ei voi hakea sinulle tietoja.

Tähtimerkkiä käyttämällä hakuarvon kummassakin päässä voimme kuitenkin hakea tiedot osittaiseksi hakuarvoksi.

Taulukossa 1 minulla on koko nimi ja palkka, mutta taulukossa 2 minulla on vain osittaiset nimet ja minun on löydettävä kunkin työntekijän palkka.

Vaihe 1: Avaa VLOOKUP-kaava solussa E3. Ennen hakuarvon valitsemista aseta tähti (*) hakuarvon molemmille puolille.

Vaihe 2: Kuten yleensä, voit suorittaa VLOOKUP-kaavan nyt ja meillä on tulokset.

Yllä olevan kaavan käytön jälkeen tulos näytetään alla.

Samaa kaavaa käytetään muissa soluissa.

HUOMAUTUS: Yksi rajoitus tässä on osittainen VLOOKUP palauttaa saman arvon, jos kyseessä on Abhishek Sinha ja Abhishek Naidu. Koska tässä yhteinen osa-arvo on Abhishek.

Esimerkki 2 - COUNTIFS operaattorisymboleilla

Sinun on oltava käyttänyt COUNTIF & IFS-toimintoa luettelon asioiden laskemiseen. Voimme laskea myös operaattorisymbolien perusteella, kuten suurempi kuin (>) pienempi kuin (<) ja yhtäläisyysmerkki (=).

Nyt tarkastellaan esimerkiksi alla olevia tietoja. Miten haluat laskea eteläisen alueen laskujen kokonaismäärän 10. tammikuuta 2018 jälkeen?

Esimerkki 3 - JOS kunnossa JA & TAI olosuhteissa

Loogiset toiminnot ovat osa päivittäistä toimintaa. Sinun on hallittava ne siirtyäksesi seuraavalle tasolle. Jos lasket bonusta useiden ehtojen perusteella, sinun täytyy pesätä JA tai TAI ehto IF-eholla saadaksesi työtä.

Oletetaan, että sinun on laskettava bonussumma jokaiselta laitokselta sen osan ja palvelusvuoden perusteella, jota tarvitset näitä toimintoja. Alla olevien kriteerien perusteella meidän on laskettava bonus.

Jos palvelu on yli 4 vuotta ja osasto on joko myynti- tai tukipalkkio on 50000 tai bonus on 25000.

Käytä alla olevaa kaavaa saadaksesi bonussumma.

Edellä olevan kaavan käytön jälkeen alla oleva tulos.

Sama kaava sovellettiin solussa E3 - E9.

Esimerkki 4 - TEXT-toiminto, joka tekee sinusta suuntauksen dynaamiseksi

Oletetaan, että ylläpidät päivittäistä myyntitaulukkoa ja sinun on päivitettävä taulukko päivittäin. Taulukon alussa on yksi otsikko, jonka otsikko on ”Konsolidoidut myyntitiedot pp-kk-vvvv - pp-kk-vvvv”. Aina kun taulukko päivittyy, sinun on muutettava otsikkopäivä. Eikö ole turhauttavaa tehdä sama asia uudestaan ​​ja uudestaan? Voimme tehdä tämän otsikon dynaamiseksi käyttämällä TETX-, MIN- ja Max-toimintoja yhdessä ketjutetun operaattorisymbolin kanssa (&).

Esimerkki 5 - INDEX + MATCH + MAX korkeimman myyntihenkilön löytämiseksi

Sinulla on luettelo myyntihenkilöistä ja myynnistä, jota he tekivät heidän nimeään vastaan. Kuinka kertoa kuka on paras tai korkein myyjä luettelossa? Tietysti meillä on useita muita tekniikoita tuloksen kertomiseksi, mutta tämä toiminto voi palauttaa korkeimman myyjän erästä.

Käytä alla-toimintoa saadaksesi korkeimman myyjän nimen.

Edellä olevan kaavan käytön jälkeen alla oleva tulos.

Esimerkki 6 - Hanki luettelo yksilöllisistä arvoista

Jos sinulla on useita päällekkäisiä arvoja ja sinun on kerrottava, kuinka monta ainutlaatuista arvoa on olemassa, kuinka kerrot? Voit kertoa poistamalla kaksoisarvon, mutta tämä ei ole dynaaminen tapa kertoa yksilöllisen arvon määrä.

Tätä taulukkofunktion avulla voimme kertoa erän yksilölliset arvot.

Käytä alla olevaa kaavaa saadaksesi ainutlaatuisen arvoluettelon.

Huomaa: Tämä on taulukkokaava, sinun on suljettava kaava pitämällä Shift + Ctrl-näppäimiä ja painamalla Enter-näppäintä.

Esimerkki 7 - Käytä nimitystä alueesta, jotta pudotus tehdään dynaamiseksi

Jos työskentelet usein avattavan luettelon kanssa ja saat avattavan luettelon ajan tasalle, sinun on palattava lähdeluetteloalueelle poistaaksesi tai lisätä arvoja. Sen jälkeen sinun on palattava avattaviin soluihin ja päivitettävä uudelleen avattavan luettelon alue.

Jos kuitenkin voit luoda nimetyn alueen avattavalle luettelolle, voit tehdä avattavasta luettelosta dynaamisen ja ajan tasalla olevan.

Luo nimialue alla olevan kuvan mukaisesti.

Siirry nyt pudotusvalikkoon ja avaa pudotusvalintaikkuna.

Kun lähde paina F3-näppäintä, se näyttää kaikki määritellyt nimet, valitse avattavan luettelon nimi.

Ok, avattava luettelo on valmis ja se päivittää arvot automaattisesti, kun avattavan luettelon alue muuttuu.

Esimerkki 8 - Päästä eroon virhearvoista käyttämällä IFERROR-toimintoa

Olen varma, että olet kokenut virhearvoja työskennellessäsi VLOOKUP, Division -laskelmien kanssa. Näiden virhearvojen käsittely on työläs tehtävä. Mutta voimme päästä eroon näistä virhearvoista käyttämällä kaavassa IFERROR-toimintoa.

Edellä olevan kaavan käytön jälkeen alla oleva tulos.

Sama kaava, jota käytetään muissa soluissa.

Esimerkki 9 - Luo oma EMI-kaavio PMT-toiminnolla

Nykyään EMI-vaihtoehto ei ole outo asia meille kaikille. Excel voi arvioida oman EMI-kaavion käyttämällä PMT-toimintoa. Luo oma kaavio noudattamalla alla olevia vaiheita.

Sovelletaan kaavan alapuolelle solussa B4 saadaksesi EMI-määrä.

Esimerkki # 10 - INDEX + MATCH vaihtoehtona VLOOKUP-toiminnolle

Toivottavasti tiedät VLOOKUP-toiminnon rajoitukset. Yksi tärkeimmistä rajoituksista on, että VLOOKUP voi hakea tietoja vasemmalta oikealle eikä oikealta vasemmalle. Tiedot eivät aina ole hyvin järjestettyjä ja käyttövalmiita. Usein etsimämme arvo-sarake on hakuarvon vasemmalla puolella, joten VLOOKUP ei auta näissä tapauksissa.

INDEX + MATCH -toiminnon yhdistelmä toimii vaihtoehtona VLOOKUP-toiminnolle.

Yllä olevan kaavan käytön jälkeen tulos näytetään alla.

Samaa kaavaa käytetään muissa soluissa.

Tuotetunnuksen perusteella meidän on poistettava myyntiarvot. Päätaulukossa tuotetunnus on myyntisarakkeen oikealla puolella. Joten VLOOKUP ei voi noutaa tietoja. Käytä alla olevaa kaavaa tietojen noutamiseen.

Suositellut artikkelit

Tämä on opas edistyneisiin kaavoihin Excelissä. Tässä keskustellaan siitä, kuinka Advanced Formulas -sovellusta voidaan käyttää Excelissä, sekä käytännön esimerkkejä ja ladattavaa Excel-mallia. Voit myös käydä läpi muiden ehdottamiemme artikkeleidemme -

  1. Kuinka käyttää Excel INDEX -toimintoa?
  2. Advanced Excel | Tietokannan toiminta
  3. Esimerkkejä Excelin TRIM-kaavasta
  4. Opas Excel OFFSET -kaavaan

Luokka:

Excel-vinkkejä