Excel Advanced Formulas (Kazalo)
- Uvod v napredne formule v Excelu
- Primeri napredne formule v Excelu
Uvod v napredne formule v Excelu
Ko dosežete vmesni nivo v excelu, se morate potruditi, da boste napredovali do napredne ravni. Če želite napredovati na napredno raven, se morate zavedati nekaterih pogosto uporabljenih naprednih formul. V tem članku bom zajel 10 najboljših naprednih formul, ki jih morajo poznati vsi učenci. Za učenje in raziskovanje sledite temu članku.
Primeri napredne formule v Excelu
Poglejmo, kako z naprednimi primeri uporabiti Napredne formule v Excelu.
To napredno predlogo za napredne formule Excel lahko prenesete tukaj - Napredna formula Excelove predlogePrimer # 1 - Delna funkcija VLOOKUP
Verjetno ste naleteli na situacijo, ko VLOOKUP sproži napako, tudi če je pri iskanju vrednosti prišlo do manjšega neuspeha. Če na primer najdete za plačo imena Abhishek Sinha in če imate samo Abhishek, potem VLOOKUP ne more pridobiti podatkov.
Vendar lahko z znakom zvezdic na obeh koncih vrednosti iskanja dobimo podatke za delno vrednost iskanja.
V tabeli 1 imam polno ime in plačo, v tabeli 2 pa samo delna imena in moram poiskati plačo vsakega zaposlenega.
1. korak: Odprite formulo VLOOKUP v celici E3. Pred izbiro vrednosti iskanja postavite zvezdico (*) na obe strani vrednosti iskanja.
Korak 2: Kot ponavadi lahko zdaj izpolnite formulo VLOOKUP in rezultati bodo na voljo.
Po uporabi zgornje formule je izhod prikazan spodaj.
Enaka formula se uporablja tudi v drugih celicah.
Primer # 2 - COUNTIFS s simboli operaterja
Za štetje stvari na seznamu ste morali uporabljati funkcijo COUNTIF in IFS. Lahko računamo tudi na podlagi simbolov operaterja, ki so večji od (>) manjši od (<) in enakega znaka (=).
Zdaj si na primer oglejte spodnje podatke. Če želite šteti skupno število računov za regijo JUŽNO po datumu 10. januarja 2018, kako računate?
Primer # 3 - ČE pogoj s pogoji AND & OR
Logične funkcije so del naših vsakodnevnih dejavnosti. Morate jih obvladati, da boste napredovali na naslednjo stopnjo. Če izračunavate bonus na podlagi več pogojev, morate za izpolnitev opravila vnesti AND ali ALI pogoj s pogojem IF.
Predpostavimo, da morate izračunati znesek bonusa za vsak oddelek na podlagi oddelka in let službe, ki jo potrebujete. Na podlagi spodnjih meril moramo izračunati bonus.
Če je storitev več kot 4 leta in je oddelek bodisi prodaja bodisi podpora 50000 bodisi bonus 25000.
Če želite pridobiti znesek bonusa, uporabite spodnjo formulo.
Po uporabi zgornje formule dobite spodnji izhod.
Enaka formula, ki velja v celici E3 do E9.
Primer # 4 - TEKSTNA funkcija, da vas bo vodila dinamično
Recimo, da vzdržujete dnevno prodajno tabelo in morate tabelo posodabljati vsak dan. Na začetku tabele imate en naslov, ki pravi: »Konsolidirani podatki o prodaji od DD-MM-LLLL do DD-MM-LLLL«. Kot in kdaj se tabela posodobi, morate spremeniti datum naslova. Ali ni frustrirajoča naloga delati isto in znova in znova? Ta naslov lahko naredimo dinamičen z uporabo funkcije TETX, MIN in Max skupaj s konkaniranim simbolom operaterja ampersand (&).
Primer # 5 - INDEX + MATCH + MAX za iskanje najvišje prodajne osebe
Imate seznam prodajnih oseb in prodajnih oseb, ki so jih opravili v nasprotju z njihovim imenom. Kako poveš, kdo je najboljši ali najvišji prodajalec na seznamu? Seveda imamo na voljo več drugih tehnik, s katerimi lahko povemo rezultat, vendar lahko ta funkcija vrne najvišjega prodajalca iz sklopa.
Uporabite spodnjo funkcijo, če želite dobiti najvišje ime prodajalca.
Po uporabi zgornje formule dobite spodnji izhod.
Primer # 6 - Na seznamu dobite število edinstvenih vrednosti
Če imate veliko podvojenih vrednosti in morate povedati, koliko edinstvenih vrednosti je, kako naj poveste? Lahko poveste tako, da odstranite podvojeno vrednost, vendar to ni dinamičen način povevanja števila edinstvenih vrednosti.
S pomočjo te matrične funkcije lahko povemo edinstvene vrednosti iz serije.
Če želite dobiti edinstven seznam vrednosti, uporabite spodnjo formulo.
Primer # 7 - Uporabite imenovani obseg, da naredite padajoči dinamiko
Če pogosto delate s spustnim seznamom in posodabljate spustni seznam, se morate vrniti na obseg seznama virov, če želite izbrisati ali dodati vrednosti. Po tem se morate vrniti na spustne celice in ponovno posodobiti obseg spustnega seznama.
Če pa lahko ustvarite imenovani obseg za spustni seznam, lahko spustni seznam naredite dinamičen in posodobljen.
Ustvarite obseg imen, kot je prikazano na spodnji sliki.
Zdaj pojdite na spustno celico in odprite spustno pogovorno okno.
V viru pritisnite tipko F3, prikaže se vsa definirana imena, izberite ime spustnega seznama.
Ok, spustni seznam je pripravljen in bo samodejno posodobil vrednosti, ko bo prišlo do spremembe obsega padajočega seznama.
Primer # 8 - Znebite se vrednosti napak z uporabo funkcije IFERROR
Prepričan sem, da ste med delom z izračuni VLOOKUP, oddelka doživeli vrednosti napak. Ravnanje s temi vrednostmi napak je dolgočasna naloga. Te vrednosti napak pa se lahko znebimo z uporabo funkcije IFERROR v formuli.
Po uporabi zgornje formule dobite spodnji izhod.
Ista formula, ki se uporablja v drugih celicah.
Primer # 9 - Uporabite funkcijo PMT za ustvarjanje lastne sheme EMI
Danes možnost EMI za nas ni nič čudnega. V Excelu lahko s pomočjo PMT funkcije ocenimo lastno EMI lestvico. Za ustvarjanje lastnega grafikona sledite spodnjim korakom.
V celici B4 nanesite spodnjo formulo, da dobite količino EMI.
Primer # 10 - INDEX + MATCH kot alternativa funkciji VLOOKUP
Upam, da se zavedate omejitve funkcije VLOOKUP. Ena večjih omejitev je VLOOKUP, da lahko podatke pridobiva od leve proti desni, ne od desne proti levi. Podatki niso ves čas dobro organizirani in pripravljeni za uporabo. Pogosto je stolpec vrednosti, ki ga iščemo, na levi strani vrednosti iskanja, zato VLOOKUP v teh primerih ne pomaga.
Kombinacija funkcije INDEX + MATCH služi kot alternativa funkciji VLOOKUP.
Po uporabi zgornje formule je izhod prikazan spodaj.
Enaka formula se uporablja tudi v drugih celicah.
Na podlagi ID-ja izdelka moramo pridobiti prodajne vrednosti. V glavni tabeli ID izdelka je na desni strani stolpca prodaje. Torej VLOOKUP ne more pridobiti podatkov. Za pridobitev podatkov uporabite spodnjo formulo.
Priporočeni članki
To je vodnik za Napredne formule v Excelu. Tukaj smo razpravljali o uporabi naprednih formul v Excelu, skupaj s praktičnimi primeri in naloženo predlogo Excela. Ogledate si lahko tudi druge naše predlagane članke -
- Kako uporabljati Excelovo funkcijo INDEX?
- Napredni Excel | Funkcija baze podatkov
- Primeri formule TRIM v Excelu
- Vodnik po formuli Excela OFFSET