Excel je odlično orodje za poročanje, analizo, organiziranje in avtomatizacijo podatkov. Excelove funkcije v veliko pomoč pri delu s podatki. Funkcije, kot so COUNTIFS, SUMIFS, VLOOKUP itd., So najmočnejše in najpogosteje uporabljene funkcije od svojega začetka v svetu Excel.
Čeprav so funkcije, ki so na voljo v Excelu 2016 in starejše, dovolj za izdelavo kakršnih koli izračunov in avtomatizacijo, včasih postanejo formule zapletene. Če na primer pri nekaterih pogojih ne najdete največje vrednosti, morate v starejši različici Excela za leto 2016 uporabiti nekaj trikov. Te vrste manjših, a pomembnih stvari so rešene v Excelih 2019 in 365.
V Excelu 2019 in 365 je več kot 10 novih funkcij, ki zmanjšujejo človeški trud in zapletenost formul.
1. Funkcija MAXIFS
V Excelu 2016 in starejših, če želite doseči največjo vrednost v razponu, ko se ujema eden ali več pogojev, morate uporabiti MAX z IF z nekaj triki. To ni veliko težko, vendar traja nekaj časa in zmede nekatere.
Excel 2019 uvaja novo funkcijo z imenom funkcija MAXIFS. Ta funkcija vrne največjo vrednost iz matrike, ko se ujemajo vsi dani pogoji.
Sintaksa funkcije je:
| = MAXIFS (največji_razpon, obseg_kriterijev1, merila1, obseg_kriterijev2, merila2 …) |
Max_range1: Številčni obseg vsebuje največjo vrednost.
Obseg_merij1: To je obseg meril, ki ga želite filtrirati, preden dobite največjo vrednost.
Kriterij 1: To je merilo ali filter, ki ga želite postaviti v obseg_kriterija, preden dobite največjo vrednost.
Recimo, da morate dobiti najvišje ocene iz razreda 3, potem bo formula
| = MAXIFS (oznake, razred, 3) |
Tu so oznake imenovani obseg, ki vsebuje oznake, razred pa je poimenovani obseg, ki vsebuje razred.
Podrobno preberite o funkciji MAXIFS tukaj.
2. Funkcija MINIFS
Enako kot funkcija MAXIFS se funkcija MINIFS uporablja za pridobivanje najmanjše vrednosti iz danega območja, ko so izpolnjeni vsi dani pogoji.
Sintaksa funkcije je:
| = MINIFS (min_razpon, obseg_ meril1, merilo1, obseg_ meril2, merilo2 … |
Min_razpon1: Številčni obseg vsebuje najmanjšo vrednost.
Obseg_merij1: To je obseg meril, ki ga želite filtrirati, preden dobite minimalno vrednost.
Kriterij 1: To je merilo ali filter, ki ga želite postaviti v obseg_kriterija, preden dobite minimalno vrednost.
Recimo, da morate iz razreda 3 dobiti minimalne ocene, potem bo formula
| = MINIFS (ocene, razred, 3) |
Tu je "oznake" poimenovani obseg, ki vsebuje oznake, "razred" pa imenovani obseg, ki vsebuje razred.
Podrobno preberite o funkciji MAXIFS tukaj.
Če želite najti najmanjšo vrednost v razponu s pogoji v Excelu 2016 in starejših, preberite to.
3. Funkcija IFS
Ker ima ugnezdeni If v našem vsakdanjem življenju posebno mesto, nam je zelo všeč. Toda za nekatere nove učence je to zapleteno. Vgnezdeni if lahko preverimo več pogojev in vrnemo drugačno vrednost, ko je kateri od pogojev izpolnjen. Formule postajajo kompleksne z vedno več IF v funkciji.
Excel 2019 in Excel 365 zdaj uporabljata funkcijo IFS. Lahko preveri več pogojev in za vsako stanje vrne različne vrednosti.
Sintaksa funkcije IFS:
| = IFS (pogoj1, vrednost1_If_True, [pogoj2, vrednost2_If_True],…) |
Pogoj 1:Prvi pogoj.
Vrednost1_Če je_ resnična: Vrednost, če je prvi pogoj resničen.
[Pogoj 2]: To ni obvezno. Drugi pogoj, če ga imate.
[Value1_If_Tue]: Vrednost, če je drugi pogoj resničen.
Lahko imate poljubno kombinacijo pogojev in vrednosti. Obstaja omejitev, vendar vam te meje nikoli ne bo treba doseči.
Recimo, da morate učencem dati ocene. Za oznake več kot 80, razred A, B za več kot 60, C za več kot 40 in F za manj ali enako 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Podrobno razlago funkcije IFS najdete tukaj.
4. SWITCH funkcija
Funkcija preklopa vrne različne vrednosti, odvisno od rezultatov enega izraza. Sliši se kot IFS? Nekako je tako. Pravzaprav je ta funkcija namenjena zamenjavi druge vrste vnešenih formul IF.
Za razliko od funkcije IFS, ki vrne vrednosti na podlagi TRUE, FALSE; vrne vrednosti funkcije SWITCH na podlagi VALUES, ki jih vrne izraz.
| = SWITCH (izraz, vrednost1, rezultat1, [privzeto ali vrednost2, rezultat2],…) |
Izraz: To je lahko kateri koli veljaven izraz, ki vrne nekatere vrednosti. Referenca celice, formula ali statična vrednost.
Vrednost 1, rezultat 1: Vrednost in rezultat sta seznanjena. Če vrednost vrneizraz je vrednost1, potem bo rezultat1 vrnjen.
[Privzeto ali vrednost2, rezultat2]: Če želite vrniti privzeto vrednost, jo določite tukaj. V nasprotnem primeru določite vrednost2 in rezultat2. Izbirno je.
Na primer, če imate formulo, ki vrača imena živali. Odvisno od vrnjenega imena živali želite vrniti podpis te živali.
| = SWITCH (A1, "Pes", "Bow Wow", "Mačka", "Meow", "Govori") |
Tu sem podrobno razložil funkcijo SWITCH.
5. Funkcija FILTER
Funkcija FILTER se uporablja za filtriranje podatkov na podlagi nekaterih meril. Uporabili smo možnost filtriranja na domačem zavihku v Excelu. Funkcija FILTER deluje enako kot možnost filtra. S funkcijo samo vrne filtrirane podatke. Ti filtrirani podatki se lahko uporabijo kot vir podatkov za druge formule.
Sintaksa funkcije FILTER je:
| = FILTER (matrika, vključi, [če_prazno]) |
Niz: To je matrika, ki jo želite filtrirati. Lahko je enodimenzionalen ali dvodimenzionalen.
Vključuje:To je filter, ki ga želite postaviti na matriko. Na primer, barve = "rdeča".
[if_empty]:To ni obvezno. Določite poljubno besedilo ali izraz, če filter ne vrne nič.
Spodnja formula vrača vse plodove, katerih barva je rdeča.
| = FILTER (sadje, barva = "rdeča", "ni najdenega sadja") |
Tu sta sadje in barva poimenovana območja, ki vsebujeta imena plodov oziroma njihove barve.
Tukaj lahko podrobno preberete o funkciji FILTER.
6. Funkcija SORT
V Excelu 2016 in starejših je bilo res težko dobiti razvrščeno matriko po formuli. Ta postopek je poenostavljen v Excelih 2019 in 365.
Excel 2019 uvaja funkcijo SORT. Funkcija SORT razvrsti dano matriko v naraščajočem ali padajočem vrstnem redu glede na dani stolpec/vrstico.
Sintaksa funkcije SORT je:
| = SORT (matrika, [sort_index], [sort_order], [by_col]) |
Niz:To je referenca matrike ali obsega, ki jo želite razvrstiti.
[sort_index]:Številka stolpca v dvodimenzionalni matriki, po kateri želite razvrstiti obseg. Privzeto je 1.
[sort_order]:Vrstni red, po katerem želite razvrstiti matriko. Za vzpon je 1, za spuščanje pa -1. Privzeto je 1.
[by_col]:Če želite razvrstiti vodoravno matriko, jo nastavite na True (1). Privzeto je False (0) za navpične podatke.
Recimo, če želite vrednosti v območju A2: A11 razvrščati naraščajoče. potem bo formula.
| = RAZVRSTI (A2: A11) |
Tukaj sem podrobno razložil funkcijo SORT.
7. Funkcija SORTBY
Funkcija SORTBY je podobna funkciji SORTBY. Edina razlika je v tem, da matriki za razvrščanje ni treba biti del razvrščene matrike v funkciji SORTBY.
| = SORTBY (matrika, razvrščanje_masi1, [vrstni red],…) |
Niz:To je matrika, ki jo želite razvrstiti.
Razvrstitev_ matrike1:To je matrika, po kateri želite razvrstiti matriko. Dimenzija tega niza mora biti združljiva z matriko.
[naročilo]:Neobvezno. Če želite, da je naročilo padajoče, ga nastavite na -1. Privzeto je naraščajoče (1).
Recimo, če želite razvrstiti obseg A2: A11 po obsegu B2: B11 v padajočem vrstnem redu. Nato bo formula v Excelu 2019 ali 365 naslednja:
| = SORTBY (A2: A11, B2: B11, -1) |
Tu sem podrobno razložil funkcijo SORTBY.
8. Funkcija UNIQUE
V Excelu 2016 in starejših smo uporabili številne funkcije v kombinaciji, da smo dobili vse edinstvene vrednosti s podanega seznama. Uporabljena formula je precej zapletena in težko razumljiva.
Excel 2019 in 365 uvajata eno preprosto funkcijo UNIQUE, ki vrne vse edinstvene vrednosti iz dane matrike.
Sintaksa funkcije UNIQUE je:
| = UNIQUE (niz, [by_col], [točno_ enkrat]) |
Niz: Niz, iz katerega želite izvleči edinstvene vrednosti:
[by_col]: Če je matrika vodoravna, jo nastavite na TRUE (1). Privzeto je za vertikalne podatke FALSE.
[točno_ enkrat]: nastavite na TRUE (1), če želite izvleči vrednosti, ki se pojavijo samo enkrat v matriki. Privzeto je FALSE (0), da izvlečete vse edinstvene vrednosti.
Recimo, da želim dobiti samo en primerek vsake vrednosti iz območja A2: A11, potem bo formula naslednja:
| = ENOTNO (A2: A11) |
Če želite podrobneje prebrati o funkciji UNIQUE, kliknite tukaj.
9. Funkcija SEQUENCE
Za pridobitev zaporedja števil v Excelu 2016 in starejših uporabljamo kombinacijo funkcij. Rešitev sicer deluje, vendar je zapletena.
Excel 2019 in 365 ponujata rešitev v obliki funkcije SEQUENCE. Funkcija zaporedja preprosto vrne niz številke.
Sintaksa funkcije SEQUENCE je:
| = SEQUENCE (vrstice, [stolpci], [začetek], [korak]) |
Vrstice:Število vrstic, v katere želite razdeliti zaporedje.
[stolpec]:Število stolpcev, v katere želite razdeliti zaporedje. Številke bodo najprej zapolnile stolpce in nato vrstice. Stolpec ni obvezen. Privzeto je 1.
[začetek]:Neobvezno. Začetna številka zaporedja. Privzeto je 1.
[korak]:To je prirastna številka za naslednjo številko. Privzeto je 1.
Preprost primer je pridobivanje serije od 1 do 10. Formula bo naslednja:
| = SEKVENCIJA (10) |
Če želite bolje razumeti funkcijo SEQUENCE v Excelu 365, preberite to.
10. Funkcija RANDARRAY
To je še ena formula dinamičnega niza, ki vrne niz naključnih števil. Gre za kombinacijo funkcije RAND in RANDBETWEEN. Dobite lahko delna naključna števila ali cela števila. Določite lahko želeno število naključnih števil. Tudi vrstice in stolpci, v katere želite razporediti te številke.
Sintaksa funkcije RANDARRAY je:
| = RANDARRAY ([vrstice], [stolpci], [min], [max], [celo število]) |
Vsi argumenti v tej funkciji so neobvezni. Privzeto deluje kot funkcija RAND.
[vrstice]:Število številk, ki jih želite navpično (število vrstic, ki jih želite zapolniti).
[stolpci]:Število številk, ki jih želite vodoravno (število stolpcev, ki jih želite zapolniti).
[min]:Začetna številka ali najmanjša vrednost naključnega števila.
[max]:Največji obseg števila.
[celo število]:Če želite, da so naključna števila cela števila, nastavite to vrednost. Privzeto je napačno in vrne delna naključna števila.
Spodnja funkcija po vrstici vrne pet naključnih ulomkov:
| = RANDARRAY (5) |
Podrobno o funkciji RANDARRAY preberite tukaj.
11. Funkcija CONCAT
V Excelu 2016 in starejših ni enostavno združiti več celic ali obsega z eno formulo.
V Excelu 2019 in 365 je problem rešen s funkcijo CONCAT. Funkcija lahko sprejme več celic, obsege kot argumente.
Sintaksa funkcije CONCAT je:
| = CONCAT (besedilo1, [besedilo2],…) |
Besedilo 1: Besedilo1 je lahko katero koli besedilo ali obseg, ki ga želite združiti.
[besedilo2]: To ni obvezno. To je lahko tudi katero koli besedilo ali obseg.
Recimo, če želite združiti vsako celico v območju A2: A11, potem bo formula enaka
| = CONCAT (A2: A11) |
Če želite podrobneje raziskati funkcijo CONCAT, kliknite tukaj.
12. Funkcija TEXTJOIN
Zgornja funkcija združuje vse celice v območju, vendar celic ne poveže z določenim ločevalnikom. Recimo, če pripravljate datoteko za format CSV, potem boste morali celice združiti z vejico. V tem primeru funkciji CONCATENATE in CONCAT ne bosta uspeli.
Tu funkcija TEXTJOIN deluje čudežno in združi dana besedila z dano ločnico.
| = TEXTJOIN (ločilo, ignore_empty_cells, text1, [text2],…) |
Ločilo:To je razmejevalnik, pri katerem želite uporabiti ločilo med posameznimi besedili. Lahko je vejica (,), podpičje (;) ali karkoli, tudi nič.
Prezri_prazne_celice:To je binarna spremenljivka. Če želite prezreti prazne celice v obsegih, nastavite na TRUE, drugače pa nastavite na FALSE, če želite vključiti prazne celice.
Besedilo 1:To je besedilo, ki se mu želite pridružiti. Lahko so posamezna besedila, celice ali celotni obsegi.
Recimo, da želim združiti obseg A2: A11 z vejico, pri čemer ne upoštevam praznih celic.
| = TEXTJOIN (",", 1, A2: A11) |
Če želite podrobneje razumeti to funkcijo, kliknite tukaj.
Ta članek je bil le uvod v novo funkcijo Excelov 365 in 2019. Te funkcije sem podrobno razložil v ločenih člankih. Če želite v celoti razumeti funkcijo, lahko kliknete povezave, ki so na voljo za vsako funkcijo v članku. Obstajajo še druge funkcije, kot je XLOOKUP, ki še niso izdane.
Če dvomite o temah Excela ali VBA, vprašajte v spodnjem razdelku za komentarje. Povejte nam, kako se lahko izboljšamo. Cenimo vaš predlog in vas želimo slišati.
Ustvari funkcijo VBA za vrnitev matrike | Če želimo vrniti matriko iz uporabniško določene funkcije, jo moramo razglasiti, ko poimenujemo UDF.
Nizovi v Excelu Formul | Preberite, kaj so matrike v Excelu.
Kako ustvariti uporabniško določeno funkcijo prek VBA | Naučite se ustvarjati uporabniško določene funkcije v Excelu
Uporaba uporabniško določene funkcije (UDF) iz drugega delovnega zvezka z uporabo VBA v programu Microsoft Excel | Uporabite uporabniško določeno funkcijo v drugem delovnem zvezku programa Excel
Vrnite vrednosti napak iz uporabniško določenih funkcij z uporabo VBA v Microsoft Excelu | Preberite, kako lahko vrnete vrednosti napak iz uporabniško določene funkcije
Priljubljeni članki:
Razdelite list Excel v več datotek na podlagi stolpca z uporabo VBA | Ta koda VBA za razdelitev kode excel na edinstvene vrednosti v določenem stolpcu. Prenesite delovno datoteko.
Izklopite opozorilna sporočila z uporabo VBA v programu Microsoft Excel 2016 | Za izklop opozorilnih sporočil, ki prekinejo delovanje kode VBA, uporabljamo razred Application.
Dodajte in shranite nov delovni zvezek z uporabo VBA v programu Microsoft Excel 2016 | Za dodajanje in shranjevanje delovnih zvezkov z uporabo VBA uporabljamo razred Delovni zvezki. Delovni zvezki.Dodajanje zlahka doda nov delovni zvezek …