Kompleksne formule v Excelu (kazalo)
- Uvod v zapletene formule v Excelu
- Izberite Funkcija Služi kot funkcija iskanja
- VLOOKUP Z IZBEREM
- ČE S in & ALI funkcijo
Uvod v zapletene formule v Excelu
Excel ponuja številne uporabne formule, imamo kategorijo finančnih, logičnih, besedilnih, datumskih in časovnih obdobij, iskanje in napotki ter matematike in preizkušanja. " Vsake posamezne formule so dovolj dobre za vmesne uporabnike, kjer se srečujejo s situacijo naravnost naprej, toda ko napredujete na naslednjo stopnjo, morate spretnost prevzeti zvišanje, zato lahko uporaba kompleksnejših formul v excelu ključno vlogo pri reševanju zapletenih situacij. Upoštevajoč ta članek vas bomo popeljali skozi nekatere pomembne in prepotrebne zapletene formule. Beri naprej.
Izberite Funkcija Služi kot funkcija iskanja
Nihče od vas ni uporabljal funkcije IZBIRA, vendar je to zelo koristno, da deluje tudi kot funkcija iskanja.
Predlogo Excelove predloge za zapletene formule lahko prenesete tukaj - Zapletene predloge Excelove predloge Na primer poglejte spodnje podatke.
Pod tabelo imamo mesečno podatke o kupcih, imamo vsako tabelo kupcev. V tej tabeli sem v celici B13 ustvaril spustni seznam meseca, tako da bi ob spremembi tega spustnega seznama dobili celoten izbrani mesec.
Ustvari eno tabelo, kot je spodaj.
V celico D13 nanesite VLOOKUP.
Zdaj odprite funkcijo IZBERI v celici B14.
Izberite INDEX številko kot celico VLOOKUP in jo absolutno navedite.
Zdaj za vsak argument izberite mesečne prodajne številke.
Zaprite oklepaj in pritisnite tipko za vnos.
Zdaj kopirajte in prilepite formulo tudi pod spodnje celice.
Zdaj IZBERITE formula bo pokazala številke izbranega meseca.
VLOOKUP s funkcijo IZBIRA
VLOOKUP smo že videli, vendar ga lahko kombiniramo tudi s funkcijo IZBERI. Na primer poglejte spodnje podatke.
V celicah A1 do B11 imamo prodajno tabelo in v celici D2 sem ustvaril spustni seznam mesecev, v celici E2 pa moramo uporabiti VLOOKUP in pridobiti izbrano mesečno prodajno vrednost. Toda težava pri uporabi VLOOKUP je struktura podatkov, kjer je vrednost iskanja na desni strani stolpca z rezultati, zato VLOOKUP ne more pridobiti podatkov od desne proti levi, v tem primeru lahko uporabimo funkcijo CHOOSE.
Za izbiro matrične tabele moramo tukaj odpreti funkcijo IZBERI.
V argumentu številke INDEX v cvetju oklepaji vpišejo 1 in 2. Zdaj izberite prvi obseg kot stolpec Mesec.
Kot drugi obseg izberite stolpec Prodaja.
Navedite številko indeksa stolpcev kot 2 in iskanje obsega kot 0.
Tako smo iz tabele dobili rezultat za mesec april. Torej, kadar nimate natančne strukture podatkov, ki jo lahko izberemo, IZBERITE funkcijo.
ČE S in & ALI funkcijo
Logične funkcije so polne logike za izračun stvari. Oglejte si spodnje primere podatkov.
Iz zgornje tabele moramo izračunati bonus na podlagi spodnjih pogojev.
- Če je storitev več kot 1, 5 leta in če je oddelek prodaja ali podpora, potem je bonus 1000.
- Če še kaj, potem Bonus 3000.
Za izračun bonusa moramo uporabiti IF, AND in OR skupaj. Odprite stanje IF v celici D2.
Testirati moramo več pogojev, odpreti in delovati.
Zdaj je naslednja logika bodisi prodaja bodisi podpora, torej za ta odprti ALI pogoj.
Preverite, ali je naslov Prodaja ali podpora.
Če so vsi priloženi pogoji TRUE, bi moral biti bonus 10000.
Če so priloženi logični testi FALSE, je bonus 3000.
Zaprite oklepaj in pritisnite tipko Enter, da dobimo rezultat.
Kopirajte in prilepite formulo v spodnje celice.
Torej glede na stanje smo dobili rezultat. Če je storitev> 1, 5 leta in je naslov Prodaja ali podpora, bomo dobili 10000 ali pa bomo dobili 3000.
Kompleksna formula z nadomestnimi znaki
VLOOKUP potrebuje natančno določeno vrednost iskanja za pridobitev podatkov. To je tradicionalni slogan, vendar podatke še vedno lahko pridobimo z vrednostjo iskanja delcev. Na primer, če je vrednost iskanja "VIVO" in v glavni tabeli, če je "VIVO Mobile", se lahko še vedno ujemamo z uporabo nadomestnih znakov. Zdaj bomo videli enega od primerov, spodaj so primeri podatkov.
V stolpcu A. imamo iskalno tabelo. V stolpcu C imamo vrednosti iskanja, te vrednosti iskanja niso popolnoma enake kot vrednosti tabele iskanja. Tako bomo videli, kako uporabiti VLOOKUP z uporabo divjih kartic.
Najprej odprite funkcijo VLOOKUP v celici D1.
Prvi argument je vrednost za iskanje . Ena od težav pri tej vrednosti iskanja je, da nimamo natančnega ujemanja, zato moramo to iskalno vrednost priložiti z zvezdico pred in po vrednosti iskanja.
Tukaj smo uporabili dva znaka zvezdice "*" & C2 & "*". Zvezdica v tem primeru označuje, da se mora vmes med nadomestnim znakom izenačiti in vrniti ustrezen rezultat.
Čeprav smo imeli samo znak „Infosys“, je zvezdica ustrezala vrednosti v iskalni tabeli in vrnila natančen rezultat kot „Infosys Ltd“.
Podobno smo v celici D6 dobili vrednost napake kot # VALUE! ker v iskalni tabeli ni besede „Mintra“.
Priporočeni članki
To je vodnik za zapletene formule v Excelu. Tukaj razpravljamo o nekaterih pomembnih in prepotrebnih zapletenih formulah s primeri in predlogo excela. Če želite izvedeti več, lahko preberete tudi druge naše predlagane članke -
- ISKANJE Formula v Excelu (Namigi in triki)
- Vadnice o funkciji Excel NORMSINV
- Imenik in njegove uporabe v Excelu
- Povežite strune v Excelu