Excel on suurepärane vahend andmete esitamiseks, analüüsimiseks, korraldamiseks ja automatiseerimiseks. Exceli funktsioonid aitavad andmetega töötamisel palju kaasa. Funktsioonid nagu COUNTIFS, SUMIFS, VLOOKUP jne on kõige võimsamad ja sagedamini kasutatavad funktsioonid alates Exceli maailma loomisest.
Kuigi Excelis 2016 ja vanemates versioonides saadaolevatest funktsioonidest piisab igasuguste arvutuste ja automaatika väljatöötamiseks, kuid mõnikord lähevad valemid keeruliseks. Näiteks kui te ei leia teatud tingimustel maksimaalset väärtust, peate 2016. aasta vanemas Exceli versioonis kasutama mõningaid nippe. Sellised väikesed, kuid olulised asjad lahendatakse Excelis 2019 ja 365.
Excelis 2019 ja 365 on 10+ uut funktsiooni, mis vähendavad inimeste vaeva ja valemite keerukust.
1. Funktsioon MAXIFS
Kui soovite Excelis 2016 ja vanemates versioonides saada maksimaalse väärtuse, kui üks või mitu tingimust sobivad, peate mõne nipiga kasutama MAX koos IF -ga. See pole kuigi raske, kuid mõne jaoks võtab see aega ja tekitab segadust.
Excel 2019 tutvustab uut funktsiooni nimega MAXIFS. See funktsioon tagastab massiivi maksimaalse väärtuse, kui kõik antud tingimused on sobitatud.
Funktsiooni süntaks on järgmine:
| = MAXIFS (max_vahemik, kriteeriumivahemik1, kriteerium1, kriteeriumivahemik2, kriteerium2…) |
Max_vahemik1: See on arvuline vahemik, mis sisaldab maksimaalset väärtust.
Kriteeriumide_vahemik1: See on kriteeriumide vahemik, mida soovite enne maksimaalse väärtuse saamist filtreerida.
Kriteeriumid 1: Need on kriteeriumid või filter, mille soovite kriteeriumi_vahemik enne maksimumväärtuse saamist panna.
Oletame, et peate klassist 3 saama maksimumpunktid, siis on valem järgmine
| = MAXIFS (märgid, klass, 3) |
Siin on märgid nimega vahemik, mis sisaldab märke ja klass on nimega vahemik, mis sisaldab klassi.
Lugege funktsiooni MAXIFS kohta üksikasjalikult siit.
2. Funktsioon MINIFS
Sama mis funktsioon MAXIFS, kasutatakse funktsiooni MINIFS, et saada antud vahemikust minimaalne väärtus, kui kõik antud tingimused on täidetud.
Funktsiooni süntaks on järgmine:
| = MINIFS (min_vahemik, kriteeriumivahemik1, kriteerium1, kriteeriumivahemik2, kriteerium2… |
Min_vahemik1: See on numbriline vahemik, mis sisaldab minimaalset väärtust.
Kriteeriumide_vahemik1: Enne miinimumväärtuse saamist soovite filtreerida kriteeriumivahemikku.
Kriteeriumid 1: Enne miinimumväärtuse saamist soovite kriteeriumide vahemiku panna kriteeriumidele või filtrile.
Oletame, et peate klassist 3 saama miinimumhinded, siis on valem järgmine
| = MINIFS (märgid, klass, 3) |
Siin on "kaubamärgid" nimega vahemik, mis sisaldab märke ja "klass" on nimega vahemik, mis sisaldab klassi.
Siit saate üksikasjalikult lugeda funktsiooni MAXIFS kohta.
Minimaalse väärtuse leidmiseks vahemikus koos tingimustega Excel 2016 ja vanemad lugege seda.
3. IFS -funktsioon
Kuna pesastatud Ifsil on meie igapäevases tööelus eriline koht, meeldib see meile väga. Kuid mõne uue õppija jaoks on see keeruline. Pesastatud if -id võimaldavad meil kontrollida mitut tingimust ja tagastada erineva väärtuse, kui mõni tingimus on täidetud. Valemid muutuvad keerukamaks, kuna nende funktsioonid on üha enam.
Excel 2019 ja Excel 365 kasutavad nüüd IFS -funktsiooni. See võib kontrollida mitut tingimust ja tagastada iga tingimuse jaoks erinevad väärtused.
Funktsiooni IFS süntaks:
| = IFS (tingimus1, väärtus1_f_tõene, [tingimus2, väärtus2_is_tõene],…) |
Tingimus 1:Esimene tingimus.
Value1_If_Tõde: Väärtus, kui esimene tingimus on tõene.
[Tingimus 2]: See on vabatahtlik. Teine tingimus, kui see on olemas.
[Value1_If_True]: Väärtus, kui teine tingimus on tõene.
Teil võib olla nii palju tingimuste ja väärtuste kombinatsioone, kui soovite. Seal on piir, kuid te ei pea kunagi seda piiri ületama.
Ütleme nii, et hinded tuleb anda õpilastele sealsete märkide järgi. Märkide puhul üle 80, klass A, B üle 60, C rohkem kui 40 ja F vähem kui 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
IFS -funktsiooni üksikasjaliku selgituse leiate siit.
4. SWITCH funktsioon
Lülitusfunktsioon tagastab sõltuvalt avaldise tulemustest erinevaid väärtusi. Kõlab nagu IFS? See on omamoodi. Tegelikult on see funktsioon mõeldud teist tüüpi pesastatud IF -valemite asendamiseks.
Erinevalt funktsioonist IFS, mis tagastab väärtused, mis põhinevad tõel, valel; funktsioon SWITCH tagastab väärtused, mis põhinevad avaldise tagastatavatel väärtustel.
| = SWITCH (avaldis, väärtus1, tulemus1, [vaikimisi või väärtus2, tulemus2],…) |
Väljend: See võib olla mis tahes kehtiv avaldis, mis tagastab mõned väärtused. Lahtri viide, valem või staatiline väärtus.
Väärtus1, tulemus1: Väärtus ja tulemus on paaris. Kui väärtus tagastabväljendus on väärtus1, tagastatakse tulemus1.
[Vaikimisi või väärtus2, tulemus2]: Kui soovite tagastada mõne vaikeväärtuse, määrake see siin. Muidu määratlege väärtus2 ja tulemus2. See on vabatahtlik.
Näiteks kui teil on valem, mis tagastab loomade nimed. Sõltuvalt looma tagastatud nimest soovite nüüd tagastada selle looma allkirjaheli.
| = LÜLITUS (A1, "Koer", "Bow Wow", "Cat", "Miau", "Räägib") |
Olen siin SWITCH funktsiooni üksikasjalikult selgitanud.
5. FILTER Funktsioon
Funktsiooni FILTER kasutatakse andmete filtreerimiseks teatud kriteeriumide alusel. Oleme kasutanud Exceli avalehe filtreerimisvalikut. Funktsioon FILTER töötab samamoodi nagu filtri valik. See tagastab lihtsalt funktsiooni abil filtreeritud andmed. Neid filtreeritud andmeid saab kasutada teiste valemite andmeallikana.
Funktsiooni FILTER süntaks on järgmine:
| = FILTER (massiiv, kaasake, [if_empty]) |
Massiiv: See on massiiv, mida soovite filtreerida. See võib olla ühe- või kahemõõtmeline.
Kaasa:See on filter, mille soovite massiivi panna. Nagu, värvid = "punane".
[kui_tühi]:See on vabatahtlik. Määrake mis tahes tekst või avaldis, kui filter ei tagasta midagi.
Allolev valem tagastab kõik viljad, mille värvus on punane.
| = FILTER (puuviljad, värv = "punane", "puuvilju ei leitud") |
Siin nimetatakse puuvilju ja värve vahemikeks, mis sisaldavad vastavalt puuviljade ja nende värvide nimesid.
Funktsiooni FILTER kohta saate üksikasjalikult lugeda siit.
6. Funktsioon SORT
Excel 2016 ja vanemates versioonides oli tõesti keeruline valemi abil sorteeritud massiivi hankida. Seda protsessi lihtsustatakse Excelis 2019 ja 365.
Excel 2019 tutvustab funktsiooni SORT. Funktsioon SORT sorteerib antud massiivi kasvavas või kahanevas järjekorras antud veeru/rea järgi.
Funktsiooni SORT süntaks on järgmine:
| = SORT (massiiv, [sort_index], [sort_order], [by_col]) |
Massiiv:See on massiivi või vahemiku viide, mida soovite sortida.
[sort_index]:Veeru number kahemõõtmelises massiivis, mille järgi soovite vahemikku sortida. Vaikimisi on see 1.
[sort_order]:Massiivi sortimise järjekord. Tõusmise korral on see 1 ja laskumise korral -1. Vaikimisi on see 1.
[by_col]:Kui soovite horisontaalset massiivi sortida, määrake see tõeks (1). Vertikaalsete andmete puhul on see vaikimisi vale (0).
Oletame, et soovite sorteerida väärtusi vahemikus A2: A11 kasvavalt. siis saab valem olema.
| = SORTEERI (A2: A11) |
Olen siin Sorteerimisfunktsiooni üksikasjalikult selgitanud.
7. Funktsioon SORTBY
Funktsioon SORTBY sarnaneb funktsiooniga SORT. Ainus erinevus on see, et sortimismassiiv ei pea funktsioonis SORTBY olema sorteeritud massiivi osa.
| = SORTBY (massiiv, sorteerimismassiiv1, [järjekord],…) |
Massiiv:See on massiiv, mida soovite sortida.
Sorteerimismassi1:See on massiiv, mille järgi soovite massiivi sorteerida. Selle massiivi mõõtmed peaksid ühilduma massiiv.
[tellimus]:Valikuline. Kui soovite, et tellimus langeks, seadke see väärtusele -1. Vaikimisi on see tõusvas joones (1).
Oletame, et soovite sortida vahemikku A2: A11 vahemiku B2: B11 järgi kahanevas järjekorras. Siis on Excel 2019 või 365 valem järgmine:
| = SORTBY (A2: A11, B2: B11, -1) |
Olen siin SORTBY funktsiooni üksikasjalikult selgitanud.
8. UNIKAALNE funktsioon
Rakenduses Excel 2016 ja vanemad kasutasime mitmeid funktsioone koos, et saada antud loendist kõik unikaalsed väärtused. Kasutatav valem on üsna keeruline ja raskesti mõistetav.
Excel 2019 ja 365 tutvustavad ühte lihtsat funktsiooni UNIKAALNE, mis tagastab kõik antud massiivi unikaalsed väärtused.
Funktsiooni UNIQUE süntaks on järgmine:
| = UNIKAALNE (massiiv, [by_col], [täpselt_ekord]) |
Massiiv: Massiiv, millest soovite unikaalseid väärtusi eraldada:
[by_col]: Määrake see TRUE (1), kui massiiv on horisontaalne. Vaikimisi on see vertikaalsete andmete puhul vale.
[täpselt_ kord]: määrake see TRUE (1), kui soovite eraldada väärtusi, mis esinevad massiivis ainult üks kord. Vaikimisi on kõigi unikaalsete väärtuste väljavõtmine VÄÄR (0).
Oletame, et tahan saada vahemikust A2: A11 iga väärtuse kohta ainult ühe eksemplari, siis on valem järgmine:
| = UNIKAALNE (A2: A11) |
Funktsiooni UNIQUE üksikasjalikuks lugemiseks klõpsake siin.
9. SEQUENCE funktsioon
Excel 2016 ja vanemate versioonide numbrite jada saamiseks kasutame funktsioonide kombinatsiooni. Lahendus töötab, kuid on keeruline.
Excel 2019 ja 365 pakuvad lahendust funktsiooni SEQUENCE kujul. Jadafunktsioon tagastab lihtsalt numbri seeria.
Funktsiooni SEQUENCE süntaks on järgmine:
| = SEQUENCE (read, [veerud], [algus], [samm]) |
Ridad:Ridade arv, millele soovite jada edastada.
[veerg]:Veergude arv, kuhu soovite jada edastada. Numbrid täidavad kõigepealt veerud ja seejärel read. Veerg on valikuline. Vaikimisi on see 1.
[algus]:Valikuline. Järjestuse algusnumber. Vaikimisi on see 1.
[samm]:See on järgmise numbri juurdekasvu number. Vaikimisi on see 1.
Lihtne näide on seeria 1 kuni 10. Valem on järgmine:
| = JÄRG (10) |
Excel 365 funktsiooni SEQUENCE üksikasjalikuks mõistmiseks lugege seda.
10. Funktsioon RANDARRAY
See on veel üks dünaamilise massiivi valem, mis tagastab juhuslike arvude massiivi. See on kombinatsioon RAND ja RANDBETWEEN funktsioonidest. Saate murdarvulisi juhuslikke numbreid või täisarvu. Saate määrata soovitud juhuslike numbrite arvu. Isegi read ja veerud, milles soovite neid numbreid levitada.
Funktsiooni RANDARRAY süntaks on järgmine:
| = RANDARRAY ([read], [veerud], [min], [max], [täisarv]) |
Kõik selle funktsiooni argumendid on valikulised. Vaikimisi töötab see funktsioonina RAND.
[read]:Vertikaalselt soovitud numbrite arv (ridade arv, mida soovite täita).
[veerud]:Horisontaalselt soovitud numbrite arv (veergude arv, mida soovite täita).
[min]:Algusnumber või juhusliku arvu/miinimumväärtused.
[max]:Arvude maksimaalne vahemik.
[täisarv]:Kui soovite, et juhuslikud numbrid oleksid täisarvud, määrake see tõeseks. Vaikimisi on see vale ja tagastab juhuslikud murdarvud.
Allolev funktsioon tagastab reas viis juhuslikku murdosa:
| = RANDARRAY (5) |
Lugege üksikasjalikult funktsiooni RANDARRAY kohta siit.
11. Funktsioon CONCAT
Rakenduses Excel 2016 ja vanemates versioonides ei ole ühe valemi abil lihtne ühendada rohkem kui ühte lahtrit või vahemikku.
Excel 2019 ja 365 probleem lahendatakse funktsiooniga CONCAT. Funktsioon võib argumendina võtta mitu lahtrit, vahemikku.
Funktsiooni CONCAT süntaks on järgmine:
| = CONCAT (tekst1, [tekst2],…) |
Tekst1: Tekst1 võib olla mis tahes tekst või vahemik, mille soovite ühendada.
[tekst2]: See on vabatahtlik. See võib olla ka mis tahes tekst või vahemik.
Oletame, et kui soovite ühendada iga lahtri vahemikus A2: A11, siis on see valem
| = CONCAT (A2: A11) |
Funktsiooni CONCAT üksikasjalikuks uurimiseks klõpsake siin.
12. Funktsioon TEXTJOIN
Ülaltoodud funktsioon ühendab kõik vahemiku lahtrid, kuid ei ühenda lahtreid ühegi määratud eraldajaga. Oletame, et kui valmistate CSV -vormingu jaoks faili ette, peate lahtrid komaga ühendama. Sel juhul funktsioon CONCATENATE ja CONCAT ebaõnnestub.
Siin töötab funktsioon TEXTJOIN imeks ja seob antud tekstid etteantud eraldajaga.
| = TEXTJOIN (eraldaja, tühjade lahtrite ignoreerimine, tekst1, [tekst2],…) |
Eraldaja:See on eraldaja, mida soovite kasutada üksikute tekstide vahel eraldajana. See võib olla koma (,), semikoolon (;) või mis tahes, isegi mitte midagi.
Ignoreeri tühje lahtreid:See on binaarne muutuja. Kui soovite vahemikes olevaid tühje lahtreid ignoreerida, seadke see väärtusele TRUE, muul juhul määrake tühjade lahtrite kaasamiseks väärtusele FALSE.
Tekst1:See on tekst, millega soovite liituda. See võib olla üksikuid tekste, lahtreid või terveid vahemikke.
Oletame, et soovin ühendada vahemiku A2: A11 komaga, ignoreerides tühje lahtreid.
| = TEXTJOIN (",", 1, A2: A11) |
Selle funktsiooni üksikasjalikuks mõistmiseks klõpsake siin.
See artikkel oli ainult sissejuhatus uutesse funktsioonidesse Excel 365 ja 2019. Olen neid funktsioone üksikasjalikult selgitanud eraldi artiklites. Funktsiooni täielikuks mõistmiseks võite klõpsata artikli iga funktsiooni jaoks saadaolevatel linkidel. On ka muid funktsioone, nagu XLOOKUP, mida pole veel avaldatud.
Kui teil on Exceli või VBA teemade osas kahtlusi, küsige allpool kommentaaride jaotises. Räägi meile, kuidas saaksime end parandada. Oleme tänulikud teie soovituse eest ja ootame teid kuulda.
Looge massiivi tagastamiseks VBA funktsioon | Massiivi tagastamiseks kasutaja määratud funktsioonist peame selle UDF -i nimetamisel deklareerima.
Massiivid Excel Formulis | Lugege, millised massiivid on Excelis.
Kuidas luua kasutaja määratud funktsiooni VBA kaudu | Siit saate teada, kuidas Excelis kasutaja määratud funktsioone luua
Kasutaja määratud funktsiooni (UDF) kasutamine teisest töövihikust, kasutades Microsoft Excelis VBA -d | Kasutaja määratud funktsiooni saate kasutada mõnes teises Exceli töövihikus
Tagastab vigade väärtused kasutaja määratud funktsioonidest, kasutades Microsoft Excelis VBA-d | Siit saate teada, kuidas saate kasutaja määratud funktsiooni veaväärtusi tagastada
Populaarsed artiklid:
Jagage Exceli leht mitmeks failiks VBA abil | See VBA -kood jagab Exceli lehe baasi unikaalsete väärtuste jaoks määratud veerus. Laadige tööfail alla.
Hoiatusteadete väljalülitamine Microsoft Excel 2016 VBA abil | Jooksva VBA koodi katkestavate hoiatusteadete väljalülitamiseks kasutame rakendusklassi.
Uue töövihiku lisamine ja salvestamine Microsoft Excel 2016 VBA abil | Töövihikute lisamiseks ja salvestamiseks VBA abil kasutame klassi Töövihikud. Töövihikud.Add lisab uue töövihiku lihtsalt, kuid…