Excel este un instrument excelent pentru raportare, analiză, organizare și automatizare a datelor. Funcțiile Excel ajută foarte mult la lucrul la date. Funcțiile precum COUNTIFS, SUMIFS, VLOOKUP, etc sunt cele mai puternice și frecvent utilizate funcții de la începuturi în lumea Excel.
Deși funcțiile disponibile în Excel 2016 și mai vechi sunt suficiente pentru a rezolva orice fel de calcul și automatizare, dar uneori formulele devin dificile. De exemplu, dacă nu veți găsi valoarea maximă cu anumite condiții, trebuie să utilizați câteva trucuri în versiunea mai veche Excel 2016. Aceste tipuri de lucruri minore, dar importante sunt rezolvate în Excel 2019 și 365.
Există 10+ funcții noi în Excel 2019 și 365 care reduc efortul uman și complexitatea formulelor.
1. Funcția MAXIFS
În Excel 2016 și mai vechi, dacă doriți să obțineți valoarea maximă într-un interval atunci când una sau mai multe condiții se potrivesc, trebuie să utilizați MAX cu IF cu câteva trucuri. Acest lucru nu este foarte dificil, dar este nevoie de timp și confuz pentru unii.
Excel 2019 introduce o nouă funcție numită funcția MAXIFS. Această funcție returnează valoarea maximă dintr-o matrice atunci când toate condițiile date sunt potrivite.
Sintaxa funcției este:
| = MAXIFS (interval_ max, interval_criterii1, criterii1, interval_criterii2, criterii2 …) |
Max_range1: Este intervalul numeric care conține valoarea maximă.
Criteria_range1: Este intervalul de criterii pe care doriți să îl filtrați înainte de a obține valoarea maximă.
Criterii 1: Criteriile sau filtrul pe care doriți să le puneți pe criteriu_interval înainte de a obține valoarea Max.
Să presupunem că trebuie să obțineți notele maxime din clasa 3, atunci formula va fi
| = MAXIFS (note, clasă, 3) |
Aici mărcile sunt intervalul denumit care conține mărcile, iar clasa este intervalul numit care conține clasa.
Citiți despre funcția MAXIFS în detaliu aici.
2. Funcția MINIFS
La fel ca funcția MAXIFS, funcția MINIFS este utilizată pentru a obține valoarea minimă din intervalul dat atunci când toate condițiile date sunt îndeplinite.
Sintaxa funcției este:
| = MINIFS (interval_min, interval_criterii1, criterii1, interval_criterii2, criterii2 … |
Min_range1: Intervalul numeric conține valoarea minimă.
Criteria_range1: Este intervalul de criterii pe care doriți să îl filtrați înainte de a obține valoarea minimă.
Criterii 1: Criteriile sau filtrul pe care doriți să le puneți pe criteriu_interval înainte de a obține valoarea minimă.
Să presupunem că trebuie să obțineți notele minime din clasa 3, atunci formula va fi
| = MINIFS (note, clasă, 3) |
Aici „mărci” este intervalul numit care conține mărcile, iar „clasa” este intervalul numit care conține clasa.
Citiți despre funcția MAXIFS în detaliu aici.
Pentru a găsi valoarea minimă din gama cu condiții în Excel 2016 și mai vechi, citiți acest lucru.
3. Funcția IFS
Întrucât Ifs imbricat are un loc special în viața noastră de zi cu zi, ne place foarte mult. Dar pentru unii noi studenți, este complex. IF-urile imbricate ne permit să verificăm mai multe condiții și să returnăm o valoare diferită atunci când oricare dintre condiții este îndeplinită. Formulele devin complexe cu funcții din ce în ce mai multe IF.
Excel 2019 și Excel 365 utilizează acum funcția IFS. Poate verifica mai multe condiții și poate returna valori diferite pentru fiecare condiție.
Sintaxa funcției IFS:
| = IFS (condiție1, valoare1_If_True, [condiție2, valoare2_If_True], …) |
Starea 1:Prima condiție.
Value1_If_True: Valoare dacă prima condiție este adevărată.
[Condiția2]: Acest lucru este opțional. A doua condiție, dacă aveți.
[Value1_If_True]: Valoare dacă a doua condiție este adevărată.
Puteți avea câte combinații de condiții și valori doriți. Există o limită, dar nu va trebui niciodată să atingi această limită.
Să spunem că trebuie să acordați notele studenților prin notele de acolo. Pentru notele mai mari de 80, gradul A, B pentru mai mult de 60, C pentru mai mult de 40 și F pentru mai puțin sau egal cu 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
O explicație detaliată a funcției IFS poate fi găsită aici.
4. Funcția SWITCH
Funcția de comutare returnează valori diferite în funcție de rezultatele unei expresii. Sună IFS? Este un fel de. De fapt, această funcție este pentru înlocuirea unui alt tip de formule IF imbricate.
Spre deosebire de funcția IFS care returnează valori bazate pe TRUE, FALSE; funcția SWITCH returnează valori bazate pe VALORI returnate de expresie.
| = SWITCH (expresie, valoare1, rezultat1, [implicit sau valoare2, rezultat2], …) |
Expresie: Aceasta poate fi orice expresie validă care returnează unele valori. O referință de celulă, o formulă sau o valoare statică.
Valoare1, rezultat1: Valoarea și rezultatul sunt asociate. Dacă valoarea returnată deexpresie este valoarea 1 atunci rezultatul 1 va fi returnat.
[Valoare implicită sau valoare2, rezultat2]: Dacă doriți să returnați o valoare implicită, definiți-o aici. Altfel definiți valoarea2 și rezultatul2. Este opțional.
De exemplu, dacă aveți o formulă care returnează numele animalelor. Acum, în funcție de numele returnat al animalului, doriți să returnați sunetul semnăturii acelui animal.
| = SWITCH (A1, „Câine”, „Bow Wow”, „Pisică”, „Miau”, „Vorbește”) |
Am explicat în detaliu funcția SWITCH aici.
5. Funcția FILTER
Funcția FILTER este utilizată pentru a filtra datele pe baza unor criterii. Am folosit opțiunea de filtrare din fila Acasă din Excel. Funcția FILTER funcționează la fel ca opțiunea de filtrare. Doar returnează datele filtrate folosind o funcție. Aceste date filtrate pot fi utilizate ca sursă de date către alte formule.
Sintaxa funcției FILTER este:
| = FILTRU (matrice, include, [if_empty]) |
Matrice: Aceasta este matricea pe care doriți să o filtrați. Poate fi unidimensional sau bidimensional.
Include:Este filtrul pe care doriți să îl puneți pe matrice. Ca, culori = "roșu".
[if_ vid]:Acest lucru este opțional. Definiți orice text sau expresie dacă filtrul nu returnează nimic.
Formula de mai jos returnează toate fructele a căror culoare este roșie.
| = FILTRU (fructe, culoare = "roșu", "nu s-au găsit fructe") |
Aici fructele și culoarea sunt denumite game care conțin numele fructelor și respectiv culorile lor.
Puteți citi despre funcția FILTER în detaliu aici.
6. Funcția SORT
În Excel 2016 și mai vechi, a fost foarte dificil să obțineți o matrice sortată folosind o formulă. Acest proces este simplificat în Excel 2019 și 365.
Excel 2019 introduce funcția SORT. Funcția SORT sortează matricea dată în ordine crescătoare sau descendentă după coloana / rândul dat.
Sintaxa funcției SORT este:
| = SORT (matrice, [sort_index], [sort_order], [by_col]) |
Matrice:Este referința matricei sau a gamei pe care doriți să o sortați.
[sort_index]:Numărul coloanei din matricea bidimensională după care doriți să sortați intervalul. În mod implicit, este 1.
[ordinea de sortare]:Ordinea în care doriți să sortați matricea. Pentru ascendent este 1 și pentru descendent este -1. În mod implicit, este 1.
[by_col]:Setați-l True (1) dacă doriți să sortați o matrice orizontală. În mod implicit, este fals (0) pentru datele verticale.
Să presupunem dacă doriți să sortați ascendent valorile din intervalul A2: A11. atunci formula va fi.
| = SORT (A2: A11) |
Am explicat detaliat funcția SORT aici.
7. Funcția SORTBY
Funcția SORTBY este similară cu funcția SORT. Singura diferență este că matricea de sortare nu trebuie să facă parte din matricea sortată în funcția SORTBY.
| = SORTBY (array, sorting_array1, [order], …) |
Matrice:Aceasta este matricea pe care doriți să o sortați.
Sorting_array1:Aceasta este matricea prin care doriți să sortați matricea. Dimensiunea acestei matrice ar trebui să fie compatibilă cu matrice.
[Ordin]:Opțional. Setați-l la -1 dacă doriți ca ordinea să fie descendentă. În mod implicit, este ascendent (1).
Să presupunem dacă doriți să sortați intervalul A2: A11 după intervalul B2: B11, în ordine descrescătoare. Apoi formula în excel 2019 sau 365 va fi:
| = SORTBY (A2: A11, B2: B11, -1) |
Am explicat aici în detaliu funcția SORTBY.
8. Funcția UNICĂ
În Excel 2016 și mai vechi, am folosit o serie de funcții în combinație pentru a obține toate valorile unice din lista dată. Formula utilizată este destul de complexă și greu de înțeles.
Excel 2019 și 365 introduce o funcție UNIQUE simplă care returnează toate valorile unice dintr-o matrice dată.
Sintaxa funcției UNIQUE este:
| = UNIC (matrice, [by_col], [exact_once]) |
Matrice: Matricea din care doriți să extrageți valori unice:
[by_col]: Setați-l TRUE (1) dacă matricea este orizontală. În mod implicit, este FALS pentru datele verticale.
[exact_once]: setați-l TRUE (1) dacă doriți să extrageți valori care apar o singură dată în matrice. În mod implicit, este FALS (0) pentru a extrage toate valorile unice.
Să presupunem că vreau să obțin o singură instanță din fiecare valoare din intervalul A2: A11, atunci formula va fi:
| = UNIC (A2: A11) |
Pentru a citi detaliat despre funcția UNIQUE puteți face clic aici.
9. Funcția SECVENȚĂ
Pentru a obține o secvență de numere în Excel 2016 și mai vechi, folosim o combinație de funcții. Soluția funcționează, dar este complexă.
Excel 2019 și 365 oferă soluția sub forma funcției SEQUENCE. Funcția de secvență returnează pur și simplu seria numărului.
Sintaxa funcției SEQUENCE este:
| = SECVENȚĂ (rânduri, [coloane], [început], [pas]) |
Rânduri:Numărul de rânduri pe care doriți să vărsați secvența.
[coloană]:Numărul de coloane în care doriți să vărsați secvența. Numerele vor completa mai întâi coloanele și apoi rândurile. Coloana este opțională. În mod implicit, este 1.
[start]:Opțional. Numărul inițial al secvenței. În mod implicit, este 1.
[Etapa]:Acesta este numărul incremental pentru următorul număr. În mod implicit, este 1.
Exemplul simplu este obținerea unei serii de la 1 la 10. Formula va fi:
| = SECVENȚĂ (10) |
Pentru a înțelege funcția SEQUENCE în Excel 365, citiți detaliat acest lucru.
10. Funcția RANDARRAY
Aceasta este o altă formulă din matrice dinamică care returnează o matrice de numere aleatorii. Este o combinație de funcții RAND și RANDBETWEEN. Puteți obține numere aleatorii fracționate sau numere întregi. Puteți specifica numărul de numere aleatorii pe care le doriți. Chiar și rânduri și coloane în care doriți să distribuiți aceste numere.
Sintaxa funcției RANDARRAY este:
| = RANDARRAY ([rânduri], [coloane], [min], [max], [întreg] |
Toate argumentele din această funcție sunt opționale. În mod implicit, funcționează ca funcție RAND.
[rânduri]:Numărul de numere pe care le doriți pe verticală (numărul de rânduri pe care doriți să le completați).
[coloane]:Numărul de numere pe care le doriți orizontal (numărul de coloane pe care doriți să le completați).
[min]:Numărul inițial sau valoarea minimă a numărului / numerelor aleatorii.
[max]:Gama maximă a numărului.
[întreg]:Setați-l adevărat, dacă doriți ca numerele aleatoare să fie numere întregi. În mod implicit, este fals și returnează numere aleatorii fracționate.
Funcția de mai jos va returna cinci numere fracționale aleatorii la rând:
| = RANDARRAY (5) |
Citiți despre funcția RANDARRAY în detaliu aici.
11. Funcția CONCAT
În Excel 2016 și mai vechi, nu este ușor să concatenați mai multe celule sau intervale folosind o singură formulă.
Excel 2019 și 365 problema este rezolvată cu funcția CONCAT. Funcția poate lua mai multe celule, intervale ca argumente.
Sintaxa funcției CONCAT este:
| = CONCAT (text1, [text2], …) |
Text1: Textul 1 poate fi orice text sau interval pe care doriți să îl concatenați.
[text2]: Acest lucru este opțional. Acesta poate fi, de asemenea, orice text sau interval.
Să presupunem că dacă doriți să concatenați fiecare celulă din intervalul A2: A11, atunci formula va fi
| = CONCAT (A2: A11) |
Pentru a explora în detaliu funcția CONCAT, faceți clic aici.
12. Funcția TEXTJOIN
Funcția de mai sus concatenează toate celulele dintr-un interval, dar nu concatenează celulele cu un delimitator specificat. Să presupunem că dacă pregătiți un fișier pentru format CSV, va trebui să concatenați celulele cu virgulă. În acest caz funcția CONCATENATE și CONCAT, ambele vor eșua.
Aici funcția TEXTJOIN funcționează minunat și concatenează textele date cu delimitatorul dat.
| = TEXTJOIN (delimitator, ignora_cele_vaciente, text1, [text2], …) |
Delimitator:Acesta este delimitatorul pentru care doriți să utilizați un separator între textele individuale. Poate fi o virgulă (,), punct și virgulă (;) sau orice, chiar și nimic.
Ignore_empty_cells:Aceasta este o variabilă binară. Setați-l la TRUE dacă doriți să ignorați celulele goale din intervale, altfel setați la FALS pentru a include celulele goale.
Text1:Acesta este textul la care doriți să vă alăturați. Poate fi texte individuale, celule sau game întregi.
Să presupunem că vreau să concatenez intervalul A2: A11 cu o virgulă, ignorând celulele goale.
| = TEXTJOIN (",", 1, A2: A11) |
Pentru a înțelege în detaliu această funcție, faceți clic aici.
Acest articol a fost doar o introducere la noua funcție a Excel 365 și 2019. Am explicat aceste funcții în detaliu în articole separate. Puteți face clic pe linkurile disponibile pe fiecare funcție din articol pentru a înțelege complet funcția. Există și alte funcții precum XLOOKUP care nu sunt încă lansate.
Dacă aveți îndoieli cu privire la subiectele Excel sau VBA, întrebați în secțiunea de comentarii de mai jos. Spuneți-ne cum putem îmbunătăți. Apreciem sugestia dvs. și dornici să aflăm de la dvs.
Creați funcția VBA pentru a returna matricea | Pentru a returna o matrice din funcția definită de utilizator, trebuie să o declarăm atunci când numim UDF.
Matrice în Excel Formul | Aflați ce matrice sunt în Excel.
Cum se creează o funcție definită de utilizator prin VBA | Aflați cum să creați funcții definite de utilizator în Excel
Utilizarea unei funcții definite de utilizator (UDF) dintr-un alt registru de lucru utilizând VBA în Microsoft Excel | Utilizați funcția definită de utilizator într-un alt registru de lucru Excel
Returnează valorile de eroare de la funcțiile definite de utilizator utilizând VBA în Microsoft Excel | Aflați cum puteți returna valorile de eroare dintr-o funcție definită de utilizator
Articole populare:
Împărțiți foaia Excel în mai multe fișiere pe baza coloanei folosind VBA | Acest cod VBA împarte baza de foi Excel pe valori unice într-o coloană specificată. Descărcați fișierul de lucru.
Dezactivați mesajele de avertizare folosind VBA în Microsoft Excel 2016 | Pentru a dezactiva mesajele de avertizare care întrerup codul VBA care rulează, folosim clasa Application.
Adăugați și salvați un registru de lucru nou folosind VBA în Microsoft Excel 2016 | Pentru a adăuga și a salva registre de lucru folosind VBA, folosim clasa Workbooks. Workbooks.Add adaugă cu ușurință un nou registru de lucru, totuși …