Excel er et fantastisk værktøj til rapportering, analyse, organisering og automatisering af data. Excel -funktionerne hjælper meget med at arbejde med data. Funktionerne som COUNTIFS, SUMIFS, VLOOKUP osv. Er de mest kraftfulde og ofte anvendte funktioner siden starten i Excel -verden.
Selvom de tilgængelige funktioner i Excel 2016 og ældre er nok til at udregne enhver form for beregning og automatisering, men nogle gange bliver formlerne vanskelige. Hvis du f.eks. Ikke finder den maksimale værdi med nogle betingelser, skal du bruge nogle tricks i 2016 ældre Excel -version. Disse slags mindre, men vigtige ting løses i Excel 2019 og 365.
Der er 10+ nye funktioner i Excel 2019 og 365, der reducerer den menneskelige indsats og kompleksitet af formlerne.
1. MAXIFS -funktionen
I excel 2016 og ældre, hvis du vil have den maksimale værdi i et område, når en eller flere betingelser matcher, skal du bruge MAX med IF med nogle tricks. Det er ikke meget svært, men tid at tage og forvirre for nogle.
Excel 2019 introducerer en ny funktion med navnet MAXIFS -funktion. Denne funktion returnerer den maksimale værdi fra et array, når alle de givne betingelser matches.
Funktionens syntaks er:
| = MAXIFS (max_range, criteria_range1, criteria1, criteria_range2, criteria2…) |
Max_range1: Det er det numeriske område, der indeholder den maksimale værdi.
Criteria_range1: Det er kriterieområdet, du vil filtrere, før du får maksimal værdi.
Kriterier 1: Det er kriterierne eller filteret, du vil sætte på criteria_range, før du får Max -værdien.
Antag, at du skal få de maksimale karakterer fra klasse 3, så er formlen
| = MAXIFS (karakterer, klasse, 3) |
Her er mærker det navngivne område, der indeholder mærkerne, og klassen er det navngivne område, der indeholder klassen.
Læs mere om MAXIFS -funktionen her.
2. MINIFS -funktionen
Samme som MAXIFS -funktion MINIFS -funktionen bruges til at få minimumsværdien fra det givne område, når alle de givne betingelser er opfyldt.
Funktionens syntaks er:
| = MINIFS (min_område, kriterier_område1, kriterier1, kriterier_område2, kriterier2 … |
Min_range1: Det er det numeriske område, der indeholder minimumsværdien.
Criteria_range1: Det er kriterieområdet, du vil filtrere, før du får minimumsværdi.
Kriterier 1: Det er kriterierne eller filteret, du vil sætte på criteria_range, før du får minimumsværdien.
Antag, at du skal få minimumskaraktererne fra klasse 3, så er formlen
| = MINIFS (karakterer, klasse, 3) |
Her er "mærker" det navngivne område, der indeholder mærkerne, og "klasse" er det navngivne område, der indeholder klassen.
Læs mere om MAXIFS -funktionen her.
For at finde minimumsværdi fra inden for rækkevidde med betingelser i excel 2016 og ældre, læs dette.
3. IFS -funktionen
Da de indlejrede Ifs har en særlig plads i vores daglige arbejdsliv, kan vi rigtig godt lide det. Men for nogle nye elever er det komplekst. De indlejrede ifs lader os kontrollere flere betingelser og returnere en anden værdi, når nogen af betingelserne er opfyldt. Formlerne bliver komplekse med mere og mere IF i funktion.
Excel 2019 og Excel 365 bruger nu IFS -funktionen. Det kan kontrollere flere betingelser og returnere forskellige værdier for hver tilstand.
Syntaks for IFS -funktion:
| = IFS (condition1, Value1_If_True, [condition2, Value2_If_True],…) |
Tilstand 1:Den første betingelse.
Value1_If_True: Værdi, hvis den første betingelse er sand.
[Betingelse2]: Dette er valgfrit. Den anden betingelse, hvis du har nogen.
[Value1_If_True]: Værdi, hvis den anden betingelse er sand.
Du kan have så mange kombinationer af betingelser og værdier, du ønsker. Der er en grænse, men du behøver aldrig at nå den grænse.
Lad os sige, at du skal give karaktererne til eleverne ved at markere dem. For mærker mere end 80, karakter A, B for mere end 60, C for mere end 40 og F for mindre end eller lig med 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
En detaljeret forklaring af IFS -funktionen kan findes her.
4. SWITCH -funktionen
Omskifterfunktionen returnerer forskellige værdier afhængigt af resultaterne af et udtryk. Lyder det som IFS? Det er sådan set. Faktisk er denne funktion til at erstatte en anden form for indlejrede IF -formler.
I modsætning til IFS -funktionen, der returnerer værdier baseret på SAND, FALSK; SWITCH -funktionens returværdier baseret på VÆRDIER returneret af udtrykket.
| = SWITCH (udtryk, værdi1, resultat1, [standard eller værdi2, resultat2], …) |
Udtryk: Dette kan være ethvert gyldigt udtryk, der returnerer nogle værdier. En cellereference, en formel eller statisk værdi.
Værdi1, resultat1: Værdien og resultatet er parret. Hvis værdien returneres afudtryk er værdi1, returneres resultatet1.
[Standard eller værdi2, resultat2]: Hvis du vil returnere en standardværdi, skal du definere den her. Ellers definer værdien2 og resultatet2. Det er valgfrit.
For eksempel, hvis du har en formel, der returnerer navnene på dyr. Nu, afhængigt af dyrets returnerede navn, vil du returnere signaturlyden fra det pågældende dyr.
| = SWITCH (A1, "Dog", "Bow Wow", "Cat", "Meow", "Speaks") |
Jeg har forklaret SWITCH -funktionen i detaljer her.
5. FILTER -funktionen
FILTER -funktionen bruges til at filtrere data baseret på nogle kriterier. Vi har brugt filterindstillingen fra fanen Hjem i Excel. FILTER -funktionen fungerer på samme måde som filterindstillingen. Det returnerer bare de filtrerede data ved hjælp af en funktion. Disse filtrerede data kan bruges som datakilde til andre formler.
Syntaksen for FILTER -funktionen er:
| = FILTER (array, inkluder, [if_empty]) |
Array: Dette er den matrix, du vil filtrere. Det kan være en eller todimensionel.
Omfatte:Det er det filter, du vil sætte på Array. Ligesom farver = "rød".
[if_empty]:Dette er valgfrit. Definer enhver tekst eller udtryk, hvis filteret ikke returnerer noget.
Nedenstående formel returnerer alle de frugter, hvis farve er rød.
| = FILTER (frugt, farve = "rød", "ingen frugt fundet") |
Her er frugt og farve navngivet intervaller, der indeholder navne på henholdsvis frugterne og deres farver.
Du kan læse mere om FILTER -funktionen her.
6. SORT -funktionen
I Excel 2016 og ældre var det virkelig vanskeligt at få et sorteret array ved hjælp af en formel. Denne proces er forenklet i Excel 2019 og 365.
Excel 2019 introducerer funktionen SORT. SORT -funktionen sorterer det givne array i stigende eller faldende rækkefølge efter den givne kolonne/række.
Syntaksen for SORT -funktionen er:
| = SORT (array, [sort_indeks], [sorteringsorden], [efter_kol]) |
Array:Det er referencen til array eller område, du vil sortere.
[sort_indeks]:Kolonnetallet i det todimensionale array, som du vil sortere området efter. Som standard er det 1.
[sorteringsrækkefølge]:Den rækkefølge, du vil sortere arrayet efter. For stigende er det 1 og for faldende er det -1. Som standard er det 1.
[af_col]:Indstil det True (1), hvis du vil sortere et vandret array. Som standard er den Falsk (0) for lodrette data.
Lad os sige, hvis du vil sortere værdier i området A2: A11 stigende. så vil formlen være.
| = SORT (A2: A11) |
Jeg har forklaret SORT -funktionen i detaljer her.
7. SORTBY -funktionen
SORTBY -funktionen ligner SORT -funktionen. Den eneste forskel er, at sorteringsarrayet ikke behøver at være en del af det sorterede array i SORTBY -funktionen.
| = SORTBY (array, sorting_array1, [rækkefølge], …) |
Array:Dette er den matrix, du vil sortere.
Sorting_array1:Dette er arrayet, som du vil sortere arrayet efter. Dimensionen af denne matrix skal være kompatibel med array.
[bestille]:Valgfri. Indstil den til -1, hvis du vil have, at ordren skal falde. Som standard er den stigende (1).
Lad os sige, hvis du vil sortere området A2: A11 efter område B2: B11, i faldende rækkefølge. Så vil formlen i excel 2019 eller 365 være:
| = SORTBY (A2: A11, B2: B11, -1) |
Jeg har forklaret SORTBY -funktionen her i detaljer.
8. Den UNIKE funktion
I Excel 2016 og ældre brugte vi en række funktioner i kombination for at få alle de unikke værdier fra den givne liste. Den anvendte formel er ret kompleks og svær at forstå.
Excel 2019 og 365 introducerer en simpel UNIK funktion, der returnerer alle de unikke værdier fra et givet array.
Syntaksen for UNIQUE -funktionen er:
| = UNIQUE (array, [by_col], [exact_once]) |
Array: Matrixen, hvorfra du vil udtrække unikke værdier:
[af_col]: Indstil det SANDT (1), hvis arrayet er vandret. Som standard er det FALSKT for lodrette data.
[lige præcis]: indstil det SANDT (1), hvis du vil udtrække værdier, der kun forekommer én gang i matrixen. Som standard er det FALSKT (0) at udtrække alle unikke værdier.
Lad os sige, at jeg kun vil have en forekomst af hver værdi fra område A2: A11, så vil formlen være:
| = UNIK (A2: A11) |
For at læse mere om den UNIKE funktion i detaljer kan du klikke her.
9. SEQUENCE -funktionen
For at få en række med tal i excel 2016 og ældre bruger vi en kombination af funktioner. Løsningen virker, men den er kompleks.
Excel 2019 og 365 giver løsningen i form af SEQUENCE -funktionen. Sekvensfunktionen returnerer ganske enkelt serien af tallet.
Syntaksen for funktionen SEQUENCE er:
| = SEQUENCE (rækker, [kolonner], [start], [trin]) |
Rækker:Antallet af rækker, som du vil spilde sekvensen til.
[kolonne]:Antallet af kolonner, som du vil spilde sekvensen til. Tallene udfylder først kolonnerne og derefter rækker. Kolonnen er valgfri. Som standard er det 1.
[Start]:Valgfri. Sekvensens startnummer. Som standard er det 1.
[trin]:Dette er stigningsnummeret for det næste nummer. Som standard er det 1.
Det enkle eksempel får en serie på 1 til 10. Formlen vil være:
| = SEKVENS (10) |
Læs denne for at forstå SEQUENCE -funktionen i Excel 365 i detaljer.
10. RANDARRAY -funktionen
Dette er en anden dynamisk matrixformel, der returnerer en række tilfældige tal. Det er en kombination af RAND og RANDBETWEEN funktion. Du kan få brøkdele tilfældige tal eller hele tal. Du kan angive det antal tilfældige tal, du ønsker. Selv rækker og kolonner, hvor du vil fordele disse tal.
Syntaksen for RANDARRAY -funktionen er:
| = RANDARRAY ([rækker], [kolonner], [min], [maks], [heltal]) |
Alle argumenterne i denne funktion er valgfri. Som standard fungerer den som RAND -funktionen.
[rækker]:Antallet af tal, du vil have lodret (antal rækker, du vil udfylde).
[kolonner]:Det antal tal, du vil have vandret (antal kolonner, du vil udfylde).
[min]:Startnummeret eller minimumsværdien af tilfældige tal/s.
[maks]:Det maksimale område af nummeret.
[heltal]:Indstil det sandt, hvis du vil have tilfældige tal til at være hele tal. Som standard er det falsk og returnerer brøkdelte tilfældige tal.
Nedenstående funktion returnerer fem tilfældige brøkdele rækkefuldt:
| = RANDARRAY (5) |
Læs mere om RANDARRAY -funktionen her.
11. CONCAT -funktionen
I Excel 2016 og ældre er det ikke let at sammenkoble mere end en celle eller et område ved hjælp af en formel.
Excel 2019 og 365 er problemet løst med funktionen CONCAT. Funktionen kan tage flere celler, områder som argumenter.
Syntaksen for CONCAT -funktionen er:
| = CONCAT (tekst1, [tekst2], …) |
Tekst1: Teksten1 kan være enhver tekst eller rækkevidde, du vil sammenkæde.
[tekst2]: Dette er valgfrit. Dette kan også være enhver tekst eller rækkevidde.
Lad os sige, at hvis du vil sammenkæde hver celle i område A2: A11, så vil formlen være
| = CONCAT (A2: A11) |
For at udforske CONCAT -funktionen i detaljer klik her.
12. TEXTJOIN -funktionen
Ovenstående funktion sammenkæder alle cellerne i et område, men den sammenkæder ikke cellerne med en bestemt afgrænsning. Lad os sige, at hvis du forbereder en fil til CSV -formater, skal du sammenkæde cellerne med komma. I så fald vil CONCATENATE- og CONCAT -funktionen mislykkes.
Her fungerer TEXTJOIN -funktionen undren og sammenkæder de givne tekster med den givne afgrænser.
| = TEKSTJOIN (afgrænser, ignorere_fritaget_celler, tekst1, [tekst2], …) |
Afgrænser:Dette er den afgrænser, at du vil bruge en separator mellem individuelle tekster. Det kan være et komma (,), semikolon (;) eller noget, endda ingenting.
Ignore_empty_cells:Dette er en binær variabel. Indstil den til SAND, hvis du vil ignorere tomme celler i områder, ellers indstil til FALSK for at inkludere de tomme celler.
Tekst1:Dette er den tekst, du vil slutte dig til. Det kan være individuelle tekster, celler eller hele områder.
Lad os sige, at jeg vil sammenkæde område A2: A11 med et komma, idet vi ignorerer de tomme celler.
| = TEKSTJOIN (",", 1, A2: A11) |
For at forstå denne funktion i detaljer, klik her.
Denne artikel var kun en introduktion til den nye funktion i Excel 365 og 2019. Jeg har forklaret disse funktioner detaljeret i separate artikler. Du kan klikke på de tilgængelige links på hver funktion i artiklen for at forstå funktionen fuldt ud. Der er andre funktioner som XLOOKUP, der ikke er frigivet endnu.
Hvis du er i tvivl om Excel- eller VBA -emner, så spørg i kommentarfeltet herunder. Fortæl os, hvordan vi kan forbedre os. Vi værdsætter dit forslag og glæder os til at høre fra dig.
Opret VBA -funktion for at returnere matrix | For at returnere et array fra brugerdefineret funktion skal vi erklære det, når vi navngiver UDF.
Arrays i Excel Formul | Lær, hvad arrays er i excel.
Sådan oprettes brugerdefineret funktion via VBA | Lær, hvordan du opretter brugerdefinerede funktioner i Excel
Brug af en brugerdefineret funktion (UDF) fra en anden projektmappe ved hjælp af VBA i Microsoft Excel | Brug den brugerdefinerede funktion i en anden Excel-projektmappe
Returner fejlværdier fra brugerdefinerede funktioner ved hjælp af VBA i Microsoft Excel | Lær, hvordan du kan returnere fejlværdier fra en brugerdefineret funktion
Populære artikler:
Opdel Excel -ark i flere filer baseret på kolonne ved hjælp af VBA | Denne VBA -kode deler excelarkbase på unikke værdier i en specificeret kolonne. Download arbejdsfilen.
Slå advarselsmeddelelser fra ved hjælp af VBA i Microsoft Excel 2016 | For at deaktivere advarselsmeddelelser, der afbryder den kørende VBA -kode, bruger vi applikationsklassen.
Tilføj og gem ny arbejdsmappe ved hjælp af VBA i Microsoft Excel 2016 | For at tilføje og gemme projektmapper ved hjælp af VBA bruger vi Workbooks -klassen. Workbooks.Add tilføjer dog let en ny projektmappe …