Excel ir lielisks rīks, lai ziņotu, analizētu, kārtotu un automatizētu datus. Excel funkcijas ļoti palīdz strādāt ar datiem. Funkcijas, piemēram, COUNTIFS, SUMIFS, VLOOKUP u.c., ir visspēcīgākās un biežāk izmantotās funkcijas kopš tās izveidošanas Excel pasaulē.
Lai gan Excel 2016 un vecākās versijās pieejamās funkcijas ir pietiekamas, lai izstrādātu jebkāda veida aprēķinus un automatizāciju, bet dažreiz formulas kļūst sarežģītas. Piemēram, ja jūs neatradīsit maksimālo vērtību ar dažiem nosacījumiem, jums ir jāizmanto daži triki 2016. gada vecākajā Excel versijā. Šāda veida nelielas, bet svarīgas lietas tiek atrisinātas programmā Excel 2019 un 365.
Programmā Excel 2019 un 365 ir pieejamas vairāk nekā 10 jaunas funkcijas, kas samazina cilvēku piepūli un formulu sarežģītību.
1. MAXIFS funkcija
Programmā Excel 2016 un vecākos gadījumos, ja vēlaties iegūt maksimālo vērtību diapazonā, kad viens vai vairāki nosacījumi sakrīt, jums ir jāizmanto MAX ar IF ar dažiem trikiem. Tas nav īpaši grūti, bet dažiem tas prasa laiku un rada neskaidrības.
Programmā Excel 2019 ir ieviesta jauna funkcija ar nosaukumu MAXIFS. Šī funkcija atgriež masīva maksimālo vērtību, kad tiek saskaņoti visi norādītie nosacījumi.
Funkcijas sintakse ir šāda:
| = MAXIFS (maks. Diapazons, kritēriju diapazons1, kritērijs1, kritēriju diapazons2, kritērijs2 …) |
Maksimālais diapazons1: Maksimālo vērtību satur skaitliskais diapazons.
Kritēriju_diapazons1: Pirms maksimālās vērtības iegūšanas vēlaties filtrēt kritēriju diapazonu.
1. kritērijs: Tieši kritērijus vai filtru vēlaties uzlikt kritērija_diapazons pirms maksimālās vērtības iegūšanas.
Pieņemsim, ka jums ir jāiegūst maksimālās atzīmes no 3. klases, tad formula būs
| = MAXIFS (atzīmes, klase, 3) |
Šeit zīmes ir nosauktais diapazons, kurā ir atzīmes, un klase ir nosauktais diapazons, kurā ir klase.
Sīkāk par MAXIFS funkciju lasiet šeit.
2. MINIFS funkcija
Līdzīgi kā MAXIFS funkcija, funkcija MINIFS tiek izmantota, lai no noteiktā diapazona iegūtu minimālo vērtību, ja ir izpildīti visi norādītie nosacījumi.
Funkcijas sintakse ir šāda:
| = MINIFS (min_diapazons, kritēriju_diapazons1, kritērijs1, kritēriju_diapazons2, kritērijs2… |
Minimālais diapazons1: Minimālo vērtību satur skaitliskais diapazons.
Kritēriju_diapazons1: Tas ir kritēriju diapazons, kuru vēlaties filtrēt pirms minimālās vērtības iegūšanas.
1. kritērijs: Tieši kritērijus vai filtru vēlaties ievietot kritēriju diapazonā pirms minimālās vērtības iegūšanas.
Pieņemsim, ka jums ir jāiegūst minimālās atzīmes no 3. klases, tad formula būs
| = MINIFS (atzīmes, klase, 3) |
Šeit "zīmes" ir nosauktais diapazons, kurā ir atzīmes, un "klase" ir nosauktais diapazons, kurā ir klase.
Sīkāk par MAXIFS funkciju lasiet šeit.
Lai atrastu minimālo vērtību diapazonā ar nosacījumiem programmā Excel 2016 un vecākiem, izlasiet šo.
3. IFS funkcija
Tā kā ligzdotajam Ifs ir īpaša vieta mūsu ikdienas darba dzīvē, mums tas ļoti patīk. Bet dažiem jauniem studentiem tas ir sarežģīti. Ligzdotie ifs ļauj pārbaudīt vairākus nosacījumus un atgriezt citu vērtību, ja kāds no nosacījumiem ir izpildīts. Formulas kļūst sarežģītas ar arvien vairāk IF funkcijām.
Excel 2019 un Excel 365 tagad izmanto IFS funkciju. Tas var pārbaudīt vairākus nosacījumus un katram nosacījumam atgriezt dažādas vērtības.
IFS funkcijas sintakse:
| = IFS (nosacījums1, Vērtība1_Ja_patiesa, [nosacījums2, Vērtība2_Ja_patiesa],…) |
1. nosacījums:Pirmais nosacījums.
Vērtība1_Ja_patiesība: Vērtība, ja pirmais nosacījums ir patiess.
[2. nosacījums]: Tas nav obligāti. Otrais nosacījums, ja tāds ir.
[Value1_If_True]: Vērtība, ja otrais nosacījums ir patiess.
Jums var būt tik daudz nosacījumu un vērtību kombināciju, kādu vēlaties. Ir ierobežojums, bet jums nekad nevajadzēs to sasniegt.
Teiksim, ka atzīmes studentiem jāsniedz pēc atzīmēm. Ja atzīme ir lielāka par 80, A, B pakāpe ir lielāka par 60, C - vairāk nekā 40 un F - mazāka vai vienāda ar 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Detalizētu IFS funkcijas skaidrojumu var atrast šeit.
4. SWITCH funkcija
Pārslēgšanas funkcija atgriež dažādas vērtības atkarībā no vienas izteiksmes rezultātiem. Izklausās pēc IFS? Tas ir sava veida. Faktiski šī funkcija ir paredzēta cita veida ligzdoto IF formulu aizstāšanai.
Atšķirībā no funkcijas IFS, kas atgriež vērtības, kuru pamatā ir TRUE, FALSE; funkcija SWITCH atgriež vērtības, kuru izteiksme atgriež VĒRTĪBAS.
| = SWITCH (izteiksme, vērtība1, rezultāts1, [noklusējuma vai vērtība2, rezultāts2],…) |
Izteiksme: Tā var būt jebkura derīga izteiksme, kas atgriež dažas vērtības. Šūnas atsauce, formula vai statiska vērtība.
Vērtība1, rezultāts1: Vērtība un rezultāts ir savienoti pārī. Ja vērtību atgriežizteiksme ir vērtība1, tad rezultāts1 tiks atgriezts.
[Noklusējums vai vērtība2, rezultāts2]: Ja vēlaties atgriezt kādu noklusējuma vērtību, definējiet to šeit. Citādi definējiet vērtību2 un rezultātu2. Tas nav obligāts.
Piemēram, ja jums ir formula, kas atgriež dzīvnieku vārdus. Tagad, atkarībā no atgrieztā dzīvnieka vārda, vēlaties atgriezt šī dzīvnieka paraksta skaņu.
| = SWITCH (A1, "Suns", "Bow Wow", "Cat", "Mjau", "Runā") |
Šeit es detalizēti izskaidroju SWITCH funkciju.
5. FILTRA funkcija
Filtra funkcija tiek izmantota, lai filtrētu datus, pamatojoties uz dažiem kritērijiem. Mēs esam izmantojuši filtra opciju Excel cilnē Sākums. FILTRA funkcija darbojas tāpat kā filtra opcija. Tas vienkārši atgriež filtrētos datus, izmantojot funkciju. Šos filtrētos datus var izmantot kā datu avotu citām formulām.
Funkcijas FILTER sintakse ir šāda:
| = FILTER (masīvs, iekļaut, [if_empty]) |
Masīvs: Šis ir masīvs, kuru vēlaties filtrēt. Tas var būt viendimensiju vai divdimensiju.
Iekļaut:Tas ir filtrs, kuru vēlaties ievietot masīvā. Piemēram, krāsas = "sarkana".
[if_empty]:Tas nav obligāti. Definējiet jebkuru tekstu vai izteiksmi, ja filtrs neko neatgriež.
Zemāk esošā formula atgriež visus augļus, kuru krāsa ir sarkana.
| = FILTRS (augļi, krāsa = "sarkans", "augļi nav atrasti") |
Šeit augļi un krāsa ir nosaukti diapazoni, kas satur attiecīgi augļu nosaukumus un to krāsas.
Šeit varat sīkāk izlasīt par FILTER funkciju.
6. Kārtošanas funkcija
Programmā Excel 2016 un vecākās versijās bija patiešām sarežģīti iegūt sakārtotu masīvu, izmantojot formulu. Šis process ir vienkāršots programmā Excel 2019 un 365.
Programmā Excel 2019 ir ieviesta funkcija SORT. Funkcija SORT kārto doto masīvu augošā vai dilstošā secībā pēc dotās kolonnas/rindas.
Funkcijas SORT sintakse ir šāda:
| = KĀRTOT (masīvs, [kārtot_indekss], [kārtot kārtību], [pēc_kr.]) |
Masīvs:Tā ir atsauce uz masīvu vai diapazonu, kuru vēlaties kārtot.
[šķirot_indeksu]:Kolonnas numurs divdimensiju masīvā, pēc kura vēlaties kārtot diapazonu. Pēc noklusējuma tas ir 1.
[šķirošanas secība]:Masīva kārtošanas secība. Augošā tempā tas ir 1, bet lejupceļā --1. Pēc noklusējuma tas ir 1.
[by_col]:Iestatiet True (1), ja vēlaties kārtot horizontālu masīvu. Pēc noklusējuma vertikālajiem datiem tas ir False (0).
Pieņemsim, ka vēlaties augošā secībā kārtot vērtības diapazonā A2: A11. tad formula būs.
| = Kārtot (A2: A11) |
Šeit es detalizēti izskaidroju funkciju Kārtot.
7. SORTBY funkcija
Funkcija SORTBY ir līdzīga funkcijai SORT. Vienīgā atšķirība ir tāda, ka šķirošanas masīvam nav jābūt sakārtotā masīva daļai funkcijā SORTBY.
| = SORTBY (masīvs, šķirošanas_masīvs1, [secība],…) |
Masīvs:Šis ir masīvs, kuru vēlaties kārtot.
Kārtošana_masīvs1:Šis ir masīvs, pēc kura vēlaties kārtot masīvu. Šī masīva izmēram jābūt saderīgam ar masīvs.
[pasūtījums]:Neobligāti. Iestatiet to uz -1, ja vēlaties, lai pasūtījums būtu dilstošs. Pēc noklusējuma tas ir augošs (1).
Pieņemsim, ka vēlaties sakārtot diapazonu A2: A11 pēc diapazona B2: B11 dilstošā secībā. Tad Excel 2019 vai 365 formula būs šāda:
| = SORTBY (A2: A11, B2: B11, -1) |
Šeit es detalizēti izskaidroju funkciju SORTBY.
8. UNIKĀLA funkcija
Programmā Excel 2016 un vecākās versijās mēs izmantojām vairākas funkcijas kombinācijā, lai iegūtu visas unikālās vērtības no dotā saraksta. Izmantotā formula ir diezgan sarežģīta un grūti saprotama.
Programmā Excel 2019 un 365 ir ieviesta viena vienkārša unikāla funkcija, kas atgriež visas unikālās vērtības no noteiktā masīva.
Funkcijas UNIQUE sintakse ir šāda:
| = UNIKĀLS (masīvs, [by_col], [precīzi_vienreiz]) |
Masīvs: Masīvs, no kura vēlaties iegūt unikālas vērtības:
[by_col]: Iestatiet to TRUE (1), ja masīvs ir horizontāls. Pēc noklusējuma vertikālajiem datiem tas ir FALSE.
[tieši_ vienreiz]: iestatiet to TRUE (1), ja vēlaties iegūt vērtības, kas masīvā parādās tikai vienu reizi. Pēc noklusējuma visas unikālas vērtības ir iegūt FALSE (0).
Pieņemsim, ka es vēlos iegūt tikai vienu katras vērtības gadījumu no diapazona A2: A11, tad formula būs šāda:
| = UNIKĀLS (A2: A11) |
Lai detalizēti izlasītu par UNIKĀLO funkciju, noklikšķiniet šeit.
9. SEQUENCE funkcija
Lai iegūtu ciparu secību programmā Excel 2016 un vecākiem, mēs izmantojam funkciju kombināciju. Risinājums darbojas, bet ir sarežģīts.
Excel 2019 un 365 nodrošina risinājumu funkcijas SEQUENCE veidā. Secības funkcija vienkārši atgriež skaitļa sēriju.
Funkcijas SEQUENCE sintakse ir šāda:
| = SEQUENCE (rindas, [kolonnas], [sākums], [solis]) |
Rindas:Rindu skaits, kurām vēlaties izlaist secību.
[sleja]:Kolonnu skaits, kurām vēlaties izlaist secību. Skaitļi vispirms aizpildīs kolonnas un pēc tam rindas. Kolonna nav obligāta. Pēc noklusējuma tas ir 1.
[sākt]:Neobligāti. Secības sākuma numurs. Pēc noklusējuma tas ir 1.
[solis]:Šis ir nākamā skaitļa pieauguma numurs. Pēc noklusējuma tas ir 1.
Vienkāršs piemērs ir sērijas no 1 līdz 10. Formula būs šāda:
| = SEKVA (10) |
Lai detalizēti izprastu Excel 365 funkciju SEQUENCE, izlasiet šo.
10. Funkcija RANDARRAY
Šī ir vēl viena dinamiskā masīva formula, kas atgriež nejaušu skaitļu masīvu. Tā ir RAND un RANDBETWEEN funkcijas kombinācija. Jūs varat iegūt daļējus nejaušus skaitļus vai veselus skaitļus. Jūs varat norādīt vajadzīgo nejaušo skaitļu skaitu. Pat rindas un kolonnas, kurās vēlaties izplatīt šos skaitļus.
Funkcijas RANDARRAY sintakse ir šāda:
| = RANDARRAY ([rindas], [kolonnas], [min], [maks.], [Vesels skaitlis]) |
Visi šīs funkcijas argumenti nav obligāti. Pēc noklusējuma tā darbojas kā funkcija RAND.
[rindas]:Vertikāli vēlamo skaitļu skaits (rindu skaits, ko vēlaties aizpildīt).
[kolonnas]:Horizontāli vajadzīgo skaitļu skaits (kolonnu skaits, kuras vēlaties aizpildīt).
[min]:Sākuma numurs vai nejaušā skaitļa (-u) minimālā vērtība.
[maks.]:Maksimālais skaitļa diapazons.
[vesels skaitlis]:Iestatiet to patiesi, ja vēlaties, lai izlases skaitļi būtu veseli skaitļi. Pēc noklusējuma tas ir nepatiess un atgriež daļējus nejaušus skaitļus.
Tālāk norādītā funkcija pēc kārtas atgriezīs piecus izlases daļskaitļus:
| = RANDARRAY (5) |
Sīkāk par funkciju RANDARRAY lasiet šeit.
11. CONCAT funkcija
Programmā Excel 2016 un vecākās versijās nav viegli savienot vairāk nekā vienu šūnu vai diapazonu, izmantojot vienu formulu.
Excel 2019 un 365 problēma tiek atrisināta, izmantojot funkciju CONCAT. Funkcijai var būt vairākas šūnas, diapazoni kā argumenti.
Funkcijas CONCAT sintakse ir šāda:
| = CONCAT (teksts1, [teksts2],…) |
1. teksts: Teksts1 var būt jebkurš teksts vai diapazons, kuru vēlaties savienot.
[teksts2]: Tas nav obligāti. Tas var būt arī jebkurš teksts vai diapazons.
Teiksim, ja vēlaties savienot katru šūnu diapazonā A2: A11, tad formula būs
| = CONCAT (A2: A11) |
Lai detalizēti izpētītu CONCAT funkciju, noklikšķiniet šeit.
12. TEXTJOIN funkcija
Iepriekš minētā funkcija apvieno visas diapazona šūnas, taču tā nesasaista šūnas ar noteiktu norobežotāju. Pieņemsim, ka, sagatavojot failu CSV formātam, šūnas būs jāsavieno ar komatu. Tādā gadījumā CONCATENATE un CONCAT funkcija neizdosies.
Šeit funkcija TEXTJOIN darbojas brīnumā un saista dotos tekstus ar doto norobežotāju.
| = TEXTJOIN (norobežotājs, ignorēt_ tukšas šūnas, teksts1, [teksts2],…) |
Atdalītājs:Šis ir norobežotājs, kuru vēlaties izmantot atdalītājam starp atsevišķiem tekstiem. Tas var būt komats (,), semikols (;) vai jebkas, pat nekas.
Ignorēt_iztukšotās šūnas:Tas ir binārs mainīgais. Iestatiet to uz TRUE, ja vēlaties ignorēt tukšās šūnas diapazonos, pretējā gadījumā iestatiet FALSE, lai iekļautu tukšās šūnas.
1. teksts:Šis ir teksts, kuram vēlaties pievienoties. Tas var būt atsevišķi teksti, šūnas vai veseli diapazoni.
Pieņemsim, ka es vēlos savienot diapazonu A2: A11 ar komatu, ignorējot tukšās šūnas.
| = TEXTJOIN (",", 1, A2: A11) |
Lai detalizēti izprastu šo funkciju, noklikšķiniet šeit.
Šis raksts bija tikai ievads jaunajai Excel 365 un 2019 funkcijai. Šīs funkcijas esmu detalizēti izskaidrojis atsevišķos rakstos. Lai pilnībā izprastu funkciju, varat noklikšķināt uz saitēm, kas pieejamas katrai raksta funkcijai. Ir arī citas funkcijas, piemēram, XLOOKUP, kas vēl nav izlaistas.
Ja jums ir šaubas par Excel vai VBA tēmām, jautājiet komentāru sadaļā zemāk. Pastāstiet mums, kā mēs varam uzlabot. Mēs novērtējam jūsu ieteikumu un vēlamies dzirdēt no jums.
Izveidojiet VBA funkciju masīva atgriešanai | Lai atgrieztu masīvu no lietotāja definētas funkcijas, mums tas ir jādeklarē, nosaucot UDF.
Masīvi programmā Excel Formul | Uzziniet, kādi masīvi ir pieejami programmā Excel.
Kā izveidot lietotāja definētu funkciju, izmantojot VBA | Uzziniet, kā programmā Excel izveidot lietotāja definētas funkcijas
Lietotāja definētas funkcijas (UDF) izmantošana no citas darbgrāmatas, izmantojot VBA programmā Microsoft Excel | Izmantojiet lietotāja definētu funkciju citā Excel darbgrāmatā
Atgriež kļūdas vērtības no lietotāja definētām funkcijām, izmantojot Microsoft Excel VBA Uzziniet, kā atgriezt kļūdu vērtības no lietotāja definētas funkcijas
Populāri raksti:
Sadaliet Excel lapu vairākos failos, pamatojoties uz kolonnu, izmantojot VBA | Šī VBA koda sadalījuma Excel lapas pamatā ir unikālas vērtības noteiktā slejā. Lejupielādējiet darba failu.
Izslēdziet brīdinājuma ziņojumus, izmantojot VBA programmā Microsoft Excel 2016 | Lai izslēgtu brīdinājuma ziņojumus, kas pārtrauc palaisto VBA kodu, mēs izmantojam lietojumprogrammu klasi.
Pievienojiet un saglabājiet jaunu darbgrāmatu, izmantojot VBA programmā Microsoft Excel 2016 | Lai pievienotu un saglabātu darbgrāmatas, izmantojot VBA, mēs izmantojam darbgrāmatu klasi. Workbooks.Add viegli pievieno jaunu darbgrāmatu, tomēr…