„Excel“ yra puikus įrankis, skirtas pranešti, analizuoti, tvarkyti ir automatizuoti duomenis. „Excel“ funkcijos labai padeda dirbti su duomenimis. Funkcijos, tokios kaip COUNTIFS, SUMIFS, VLOOKUP ir kt., Yra galingiausios ir dažniausiai naudojamos funkcijos nuo tada, kai jos atsirado „Excel“ pasaulyje.
Nors „Excel 2016“ ir naujesnių versijų funkcijų pakanka bet kokiam skaičiavimui ir automatizavimui, tačiau kartais formulės tampa sudėtingos. Pavyzdžiui, jei su tam tikromis sąlygomis nerasite didžiausios vertės, turite naudoti keletą gudrybių senesnėje 2016 m. „Excel“ versijoje. Tokie smulkūs, bet svarbūs dalykai sprendžiami naudojant „Excel 2019“ ir „365“.
„Excel 2019“ ir „365“ yra daugiau nei 10 naujų funkcijų, kurios sumažina žmonių pastangas ir formulių sudėtingumą.
1. MAXIFS funkcija
„Excel 2016“ ir naujesnėse versijose, jei norite gauti didžiausią diapazono vertę, kai viena ar daugiau sąlygų sutampa, turite naudoti „MAX“ su IF su tam tikrais gudrybėmis. Tai nėra labai sunku, tačiau kai kuriems tai užtrunka ir kelia painiavą.
„Excel 2019“ pristato naują funkciją, pavadintą MAXIFS funkcija. Ši funkcija grąžina didžiausią masyvo vertę, kai visos pateiktos sąlygos yra suderintos.
Funkcijos sintaksė yra tokia:
| = MAXIFS (maks. Diapazonas, kriterijų_diapazonas1, kriterijus1, kriterijų_diapazonas2, kriterijus2 …) |
Maksimalus diapazonas1: Tai yra skaitmeninis diapazonas, kuriame yra maksimali vertė.
Kriterijų_sritis1: Būtent kriterijų diapazoną norite filtruoti prieš gaudami maksimalią vertę.
1 kriterijus: Būtent kriterijus arba filtrą norite įvesti kriterijų_diapazone prieš gaudami didžiausią vertę.
Tarkime, kad jums reikia gauti maksimalius pažymius iš 3 klasės, tada formulė bus
| = MAXIFS (ženklai, klasė, 3) |
Čia ženklai yra pavadintas diapazonas, kuriame yra ženklai, o klasė - pavadintas diapazonas, kuriame yra klasė.
Išsamiai apie MAXIFS funkciją skaitykite čia.
2. MINIFS funkcija
Funkcija MINIFS naudojama taip pat, kaip ir MAXIFS funkcija, kad būtų gauta minimali vertė iš nurodyto diapazono, kai bus įvykdytos visos nurodytos sąlygos.
Funkcijos sintaksė yra tokia:
| = MINIFS (min. Diapazonas, kriterijų diapazonas1, kriterijai1, kriterijų diapazonas2, kriterijai2… |
Min. Sritis1: Tai yra skaitmeninis diapazonas, kuriame yra minimali vertė.
Kriterijų_sritis1: Būtent kriterijų diapazoną norite filtruoti prieš gaudami minimalią vertę.
1 kriterijus: Būtent kriterijus arba filtrą norite įvesti kriterijų_diapazone prieš gaudami minimalią vertę.
Tarkime, kad jums reikia gauti minimalius pažymius iš 3 klasės, tada formulė bus
| = MINIFS (pažymiai, klasė, 3) |
Čia „ženklai“ yra pavadintas diapazonas, kuriame yra ženklai, o „klasė“ yra pavadintas diapazonas, kuriame yra klasė.
Išsamiai apie MAXIFS funkciją skaitykite čia.
Norėdami rasti minimalią vertę iš diapazono su „Excel 2016“ ir senesnėmis sąlygomis, perskaitykite tai.
3. IFS funkcija
Kadangi lizdiniai „Ifs“ užima ypatingą vietą mūsų kasdieniame darbe, mums tai labai patinka. Tačiau kai kuriems naujiems mokiniams tai yra sudėtinga. Įdėti IF leidžia mums patikrinti kelias sąlygas ir grąžinti kitą vertę, kai bet kuri iš sąlygų yra įvykdyta. Formulės tampa sudėtingesnės, kai funkcijų vis daugiau ir daugiau.
„Excel 2019“ ir „Excel 365“ dabar naudoja IFS funkciją. Jis gali patikrinti kelias sąlygas ir grąžinti skirtingas kiekvienos sąlygos vertes.
IFS funkcijos sintaksė:
| = IFS (sąlyga1, reikšmė1_fai tiesa, [sąlyga2, reikšmė2_taiklus],…) |
1 sąlyga:Pirmoji sąlyga.
Value1_If_True: Vertė, jei pirmoji sąlyga yra teisinga.
[2 sąlyga]: Tai neprivaloma. Antroji sąlyga, jei turite.
[Value1_If_True]: Vertė, jei antroji sąlyga yra teisinga.
Galite turėti tiek norimų sąlygų ir vertybių derinių. Yra riba, bet jums niekada nereikės jos pasiekti.
Tarkime, kad pažymius reikia mokiniams duoti pažymiais. Jei pažymiai yra daugiau nei 80, A, B klasė - daugiau nei 60, C - daugiau nei 40, o F - mažesnis arba lygus 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Išsamų IFS funkcijos paaiškinimą rasite čia.
4. SWITCH funkcija
Perjungimo funkcija grąžina skirtingas vertes, priklausomai nuo vienos išraiškos rezultatų. Skamba kaip IFS? Tai savotiška. Tiesą sakant, ši funkcija skirta pakeisti kito tipo įterptas IF formules.
Skirtingai nuo funkcijos IFS, kuri grąžina vertes pagal TRUE, FALSE; funkcijos SWITCH grąžina reikšmes pagal išraiškos grąžintas VERTES.
| = SWITCH (išraiška, vertė1, rezultatas1, [numatytasis arba vertė2, rezultatas2],…) |
Išraiška: Tai gali būti bet kuri tinkama išraiška, kuri grąžina kai kurias vertes. Langelio nuoroda, formulė arba statinė vertė.
Vertė1, rezultatas1: Vertė ir rezultatas yra suporuoti. Jei vertė grąžintaišraiška yra reikšmė1, tada rezultatas1 bus grąžintas.
[Numatytoji arba vertė2, rezultatas2]: Jei norite grąžinti numatytąją vertę, apibrėžkite ją čia. Kitu atveju nustatykite reikšmę2 ir rezultatą2. Tai neprivaloma.
Pavyzdžiui, jei turite formulę, kuri grąžina gyvūnų pavadinimus. Dabar, priklausomai nuo grąžinto gyvūno vardo, norite grąžinti to gyvūno parašo garsą.
| = SWITCH (A1, "Šuo", "Bow Wow", "Cat", "Miau", "Kalbama") |
Čia išsamiai paaiškinau SWITCH funkciją.
5. FILTRO funkcija
Filtro funkcija naudojama duomenims filtruoti pagal kai kuriuos kriterijus. Mes naudojome filtro parinktį „Excel“ pagrindiniame skirtuke. FILTRO funkcija veikia taip pat, kaip ir filtro parinktis. Jis tiesiog grąžina filtruotus duomenis naudodami funkciją. Šie filtruoti duomenys gali būti naudojami kaip duomenų šaltinis kitoms formulėms.
Funkcijos FILTER sintaksė yra tokia:
| = FILTRAS (masyvas, įtraukite, [if_empty]) |
Masyvas: Tai yra masyvas, kurį norite filtruoti. Tai gali būti vieno ar dviejų matmenų.
Įtraukti:Tai filtras, kurį norite įdėti į masyvą. Patinka, spalvos = "raudona".
[if_empty]:Tai neprivaloma. Apibrėžkite bet kokį tekstą ar išraišką, jei filtras nieko negrąžina.
Žemiau pateikta formulė grąžina visus vaisius, kurių spalva yra raudona.
| = FILTRAS (vaisiai, spalva = "raudona", "vaisių nerasta") |
Čia vaisiai ir spalva yra pavadinti diapazonai, kuriuose yra atitinkamai vaisių pavadinimai ir jų spalvos.
Čia galite išsamiai perskaityti apie FILTER funkciją.
6. SORT funkcija
„Excel 2016“ ir naujesnėse versijose buvo tikrai sudėtinga gauti surūšiuotą masyvą naudojant formulę. Šis procesas supaprastintas „Excel 2019“ ir „365“.
„Excel 2019“ pristato funkciją SORT. Funkcija RŪŠIS surūšiuoja masyvą didėjančia arba mažėjančia tvarka pagal nurodytą stulpelį/eilutę.
Funkcijos RŪŠYTI sintaksė yra tokia:
| = RŪŠIUOTI (masyvas, [rūšiuoti_indeksas], [rūšiuoti_pasakymas], [pagal_col]) |
Masyvas:Tai masyvo ar diapazono, kurį norite rūšiuoti, nuoroda.
[rūšiuoti_indeksas]:Dviejų matmenų masyvo stulpelio numeris, pagal kurį norite rūšiuoti diapazoną. Pagal numatytuosius nustatymus tai yra 1.
[Rikiuoti]:Tvarka, pagal kurią norite rūšiuoti masyvą. Kylant jis yra 1, o mažėjant --1. Pagal numatytuosius nustatymus tai yra 1.
[by_col]:Nustatykite „True“ (1), jei norite rūšiuoti horizontalųjį masyvą. Pagal numatytuosius nustatymus vertikaliems duomenims jis yra klaidingas (0).
Tarkime, jei norite rūšiuoti didėjančias vertes diapazone A2: A11. tada formulė bus.
| = RŪŠYTI (A2: A11) |
Čia išsamiai paaiškinau SORT funkciją.
7. SORTBY funkcija
Funkcija SORTBY yra panaši į SORT funkciją. Vienintelis skirtumas yra tas, kad rūšiavimo masyvas neprivalo būti rūšiuoto masyvo dalis funkcijoje SORTBY.
| = SORTBY (masyvas, rūšiavimo_masyvas1, [tvarka],…) |
Masyvas:Tai masyvas, kurį norite rūšiuoti.
Rūšiavimo_masyvas1:Tai yra masyvas, pagal kurį norite rūšiuoti masyvą. Šio masyvo matmenys turėtų būti suderinami su masyvas.
[įsakymas]:Neprivaloma. Jei norite, kad užsakymas mažėtų, nustatykite jį į -1. Pagal numatytuosius nustatymus jis didėja (1).
Tarkime, jei norite rūšiuoti diapazoną A2: A11 pagal diapazoną B2: B11 mažėjančia tvarka. Tada „Excel 2019“ arba „365“ formulė bus tokia:
| = SORTBY (A2: A11, B2: B11, -1) |
Čia išsamiai paaiškinau funkciją SORTBY.
8. UNIKALI funkcija
„Excel 2016“ ir naujesnėse versijose mes naudojome daugybę funkcijų, kad gautume visas unikalias reikšmes iš pateikto sąrašo. Naudojama formulė yra gana sudėtinga ir sunkiai suprantama.
„Excel 2019“ ir „365“ pristato vieną paprastą unikalią funkciją, kuri grąžina visas unikalias masyvo reikšmes.
Funkcijos UNIQUE sintaksė yra tokia:
| = UNIKALUS (masyvas, [by_col], [tiksliai_kartas]) |
Masyvas: Masyvas, iš kurio norite išgauti unikalių verčių:
[by_col]: Nustatykite TRUE (1), jei masyvas yra horizontalus. Pagal numatytuosius nustatymus vertikaliems duomenims ji yra NETEISINGA.
[tiksliai_kart]: nustatykite jį TRUE (1), jei norite išgauti reikšmes, kurios atsiranda tik vieną kartą masyve. Pagal numatytuosius nustatymus yra klaidinga (0) išgauti visas unikalias vertes.
Tarkime, kad noriu gauti tik vieną kiekvienos vertės pavyzdį iš diapazono A2: A11, tada formulė bus tokia:
| = UNIKALUS (A2: A11) |
Norėdami išsamiai perskaityti apie unikalią funkciją, spustelėkite čia.
9. SEQUENCE funkcija
Norėdami gauti skaičių seką „Excel 2016“ ir naujesnėse versijose, naudojame funkcijų derinį. Sprendimas veikia, bet yra sudėtingas.
„Excel 2019“ ir „365“ pateikia sprendimą kaip SEQUENCE funkciją. Sekos funkcija tiesiog grąžina skaičiaus seriją.
Funkcijos SEQUENCE sintaksė yra tokia:
| = SEQUENCE (eilutės, [stulpeliai], [pradžia], [žingsnis]) |
Eilutės:Eilučių, į kurias norite išsiųsti seką, skaičius.
[stulpelis]:Stulpelių, į kuriuos norite išsiųsti seką, skaičius. Skaičiai pirmiausia užpildys stulpelius, o tada eilutes. Stulpelis neprivalomas. Pagal numatytuosius nustatymus tai yra 1.
[pradžia]:Neprivaloma. Sekos pradžios numeris. Pagal numatytuosius nustatymus tai yra 1.
[žingsnis]:Tai kito skaičiaus didinimo skaičius. Pagal numatytuosius nustatymus tai yra 1.
Paprastas pavyzdys yra gauti seriją nuo 1 iki 10. Formulė bus tokia:
| = SEKA (10) |
Norėdami išsamiai suprasti „Excel 365“ funkciją SEQUENCE, perskaitykite tai.
10. Funkcija RANDARRAY
Tai dar viena dinaminio masyvo formulė, kuri grąžina atsitiktinių skaičių masyvą. Tai RAND ir RANDBETWEEN funkcijos derinys. Galite gauti trupmeninius atsitiktinius skaičius arba sveikus skaičius. Galite nurodyti norimą atsitiktinių skaičių skaičių. Net eilutės ir stulpeliai, kuriuose norite paskirstyti šiuos skaičius.
Funkcijos RANDARRAY sintaksė yra tokia:
| = RANDARRAY ([eilutės], [stulpeliai], [min], [maks.], [Sveikas skaičius]) |
Visi šios funkcijos argumentai yra neprivalomi. Pagal numatytuosius nustatymus jis veikia kaip RAND funkcija.
[eilutės]:Vertikaliai norimų skaičių skaičius (norimų užpildyti eilučių skaičius).
[stulpeliai]:Horizontaliai norimų skaičių skaičius (stulpelių, kuriuos norite užpildyti, skaičius).
[min]:Pradinis skaičius arba mažiausia atsitiktinio skaičiaus (-ių) vertė.
[maks.]:Maksimalus skaičiaus diapazonas.
[sveikasis skaičius]:Nustatykite teisybę, jei norite, kad atsitiktiniai skaičiai būtų sveikieji skaičiai. Pagal numatytuosius nustatymus jis yra klaidingas ir grąžina trupmeninius atsitiktinius skaičius.
Žemiau pateikta funkcija grąžins penkis atsitiktinius trupmeninius skaičius iš eilės:
| = RANDARRAY (5) |
Išsamiai apie RANDARRAY funkciją skaitykite čia.
11. CONCAT funkcija
„Excel 2016“ ir naujesnėse versijose nėra lengva sujungti daugiau nei vieną langelį ar diapazoną naudojant vieną formulę.
„Excel 2019“ ir „365“ problema išspręsta naudojant funkciją CONCAT. Funkcija gali turėti kelis langelius, diapazonus kaip argumentus.
Funkcijos CONCAT sintaksė yra tokia:
| = CONCAT (tekstas1, [tekstas2],…) |
1 tekstas: Tekstas1 gali būti bet koks tekstas ar diapazonas, kurį norite sujungti.
[tekstas2]: Tai neprivaloma. Tai taip pat gali būti bet koks tekstas ar diapazonas.
Tarkime, jei norite sujungti kiekvieną A2: A11 diapazono langelį, tada formulė bus tokia
| = CONCAT (A2: A11) |
Norėdami išsamiai ištirti CONCAT funkciją, spustelėkite čia.
12. TEXTJOIN funkcija
Aukščiau pateikta funkcija sujungia visas diapazono ląsteles, tačiau ji nesujungia ląstelių su jokiu nurodytu ribotuvu. Tarkime, jei ruošiate failą CSV formatui, turėsite sujungti langelius kableliu. Tokiu atveju funkcija CONCATENATE ir CONCAT nepavyks.
Čia TEXTJOIN funkcija veikia stebuklu ir sujungia pateiktus tekstus su nurodytu skiriamuoju ženklu.
| = TEXTJOIN (skiriamasis ženklas, ignoruoti tuščius langelius, tekstas1, [tekstas2],…) |
Skyriklis:Tai yra ribotuvas, kurį norite naudoti atskiru tekstu. Tai gali būti kablelis (,), kabliataškis (;) arba bet kas, net nieko.
Ignoruoti tuščias ląsteles:Tai yra dvejetainis kintamasis. Jei norite ignoruoti tuščius langelius diapazonuose, nustatykite jį į TRUE, priešingu atveju nustatykite FALSE, kad įtrauktumėte tuščius langelius.
1 tekstas:Tai tekstas, prie kurio norite prisijungti. Tai gali būti atskiri tekstai, langeliai ar ištisos eilutės.
Tarkime, aš noriu sujungti diapazoną A2: A11 su kableliu, ignoruodamas tuščias ląsteles.
| = TEXTJOIN (",", 1, A2: A11) |
Norėdami išsamiai suprasti šią funkciją, spustelėkite čia.
Šis straipsnis buvo tik įvadas į naują „Excel 365“ ir 2019 funkciją. Šias funkcijas išsamiai paaiškinau atskiruose straipsniuose. Norėdami visiškai suprasti funkciją, galite spustelėti nuorodas, esančias prie kiekvienos straipsnio funkcijos. Yra ir kitų funkcijų, tokių kaip „XLOOKUP“, kurios dar nėra išleistos.
Jei turite kokių nors abejonių dėl „Excel“ ar VBA temų, paklauskite žemiau esančiame komentarų skyriuje. Pasakykite mums, kaip galėtume tobulėti. Mes vertiname jūsų pasiūlymą ir norime išgirsti iš jūsų.
Sukurkite VBA funkciją grąžinti masyvą | Norėdami grąžinti masyvą iš vartotojo apibrėžtos funkcijos, turime jį deklaruoti, kai įvardijame UDF.
Masyvai „Excel Formul“ | Sužinokite, kokie masyvai yra „Excel“.
Kaip sukurti vartotojo apibrėžtą funkciją per VBA | Sužinokite, kaip „Excel“ sukurti vartotojo apibrėžtas funkcijas
Naudotojo apibrėžtos funkcijos (UDF) naudojimas iš kitos darbaknygės naudojant VBA programoje „Microsoft Excel“ | Naudokite vartotojo apibrėžtą funkciją kitoje „Excel“ darbaknygėje
Pateikite klaidų reikšmes iš vartotojo apibrėžtų funkcijų naudodami VBA programoje „Microsoft Excel“ | Sužinokite, kaip galite grąžinti klaidų vertes iš vartotojo nustatytos funkcijos
Populiarūs straipsniai:
Padalinkite „Excel“ lapą į kelis failus pagal stulpelį naudodami VBA | Šis VBA kodas padalija „Excel“ lapą pagal unikalias reikšmes nurodytame stulpelyje. Atsisiųskite darbo failą.
Išjunkite įspėjamuosius pranešimus naudodami VBA programoje „Microsoft Excel 2016“ | Norėdami išjungti įspėjamuosius pranešimus, kurie nutraukia veikiantį VBA kodą, naudojame taikymo klasę.
Pridėkite ir išsaugokite naują darbaknygę naudodami „Microsoft Excel 2016“ VBA | Norėdami pridėti ir išsaugoti darbaknyges naudodami VBA, naudojame darbo knygų klasę. Workbooks.Add lengvai prideda naują darbaknygę, tačiau…