Excel är ett utmärkt verktyg för att rapportera, analysera, organisera och automatisera data. Excel -funktionerna hjälper mycket med att arbeta med data. Funktionerna som COUNTIFS, SUMIFS, VLOOKUP, etc är de mest kraftfulla och ofta använda funktionerna sedan starten i Excel -världen.
Även om de funktioner som finns i Excel 2016 och äldre är tillräckliga för att räkna ut någon form av beräkning och automatisering, men ibland blir formlerna knepiga. Om du till exempel inte hittar det maximala värdet med vissa villkor måste du använda några knep i 2016 äldre Excel -version. Den här typen av mindre men viktiga saker löses i Excel 2019 och 365.
Det finns 10+ nya funktioner i Excel 2019 och 365 som minskar den mänskliga ansträngningen och komplexiteten i formlerna.
1. MAXIFS -funktionen
I Excel 2016 och äldre, om du vill få det maximala värdet i ett intervall när ett eller flera villkor matchar, måste du använda MAX med IF med några knep. Det är inte mycket svårt men tar tid och förvirrar för vissa.
Excel 2019 introducerar en ny funktion som heter MAXIFS -funktion. Denna funktion returnerar det maximala värdet från en array när alla givna villkor matchas.
Syntaxen för funktionen är:
| = MAXIFS (max_range, criteria_range1, criteria1, criteria_range2, criteria2…) |
Max_range1: Det är det numeriska intervallet som innehåller maxvärdet.
Criteria_range1: Det är kriterieområdet som du vill filtrera innan du får maxvärde.
Kriterier 1: Det är kriterierna eller filtret som du vill sätta på criteria_range innan du får Max -värdet.
Antag att du måste få maximala betyg från klass 3 då är formeln
| = MAXIFS (betyg, klass, 3) |
Här är märken det namngivna intervallet som innehåller märkena och klassen är det namngivna intervallet som innehåller klassen.
Läs mer om MAXIFS -funktionen i detalj här.
2. MINIFS -funktionen
Samma som MAXIFS -funktionen MINIFS -funktionen används för att få minimivärdet från det angivna intervallet när alla givna villkor är uppfyllda.
Syntaxen för funktionen är:
| = MINIFS (min_område, kriterier_område1, kriterier1, kriterier_område2, kriterier2 … |
Min_område1: Det är det numeriska intervallet som innehåller minimivärdet.
Criteria_range1: Det är kriterieområdet som du vill filtrera innan du får minimivärde.
Kriterier 1: Det är kriterierna eller filtret som du vill sätta på criteria_range innan du får minsta värde.
Antag att du måste få minsta betyg från klass 3 då är formeln
| = MINIFS (betyg, klass, 3) |
Här är "märken" det namngivna intervallet som innehåller märkena och "klass" är det namngivna intervallet som innehåller klassen.
Läs mer om MAXIFS -funktionen i detalj här.
För att hitta minimivärde från intervall med förhållanden i excel 2016 och äldre läs detta.
3. IFS -funktionen
Eftersom de inkapslade Ifs har en speciell plats i vårt dagliga arbetsliv, gillar vi det mycket. Men för några nya elever är det komplext. Med kapslade ifs kan vi kontrollera flera villkor och returnera ett annat värde när något av villkoren är uppfyllda. Formlerna blir komplexa med mer och mer IF -funktion.
Excel 2019 och Excel 365 använder nu IFS -funktionen. Den kan kontrollera flera villkor och returnera olika värden för varje villkor.
Syntax för IFS -funktion:
| = IFS (condition1, Value1_If_True, [condition2, Value2_If_True], …) |
Skick 1:Det första villkoret.
Value1_If_True: Värde om det första villkoret är sant.
[Villkor2]: Detta är valfritt. Det andra villkoret, om du har något.
[Value1_If_True]: Värde om det andra villkoret är sant.
Du kan ha så många kombinationer av villkor och värden som du vill. Det finns en gräns, men du behöver aldrig nå den gränsen.
Låt oss säga att du måste ge betygen till eleverna genom att det finns betyg. För betyg mer än 80, betyg A, B för mer än 60, C för mer än 40 och F för mindre än eller lika med 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
En detaljerad förklaring av IFS -funktionen hittar du här.
4. SWITCH -funktionen
Växlingsfunktionen returnerar olika värden beroende på resultatet för ett uttryck. Låter det som IFS? Det är typ. Egentligen är denna funktion för att ersätta en annan typ av kapslade IF -formler.
Till skillnad från IFS -funktionen som returnerar värden baserade på TRUE, FALSE; SWITCH -funktionens returvärden baseras på VÄRDER som returneras av uttrycket.
| = SWITCH (uttryck, värde1, resultat1, [standard eller värde2, resultat2], …) |
Uttryck: Detta kan vara valfritt uttryck som returnerar vissa värden. En cellreferens, en formel eller ett statiskt värde.
Värde1, resultat1: Värdet och resultatet är ihopkopplade. Om värdet returneras avuttryck är värde1 kommer resultatet1 att returneras.
[Standard eller värde2, resultat2]: Om du vill returnera något standardvärde, definiera det här. Annars definiera värdet2 och resultatet2. Det är valfritt.
Om du till exempel har en formel som returnerar namnen på djur. Nu, beroende på djurets returnerade namn, vill du returnera djurets signaturljud.
| = SWITCH (A1, "Dog", "Bow Wow", "Cat", "Meow", "Speaks") |
Jag har förklarat SWITCH -funktionen i detalj här.
5. FILTER -funktionen
FILTER -funktionen används för att filtrera data baserat på vissa kriterier. Vi har använt filteralternativet från fliken Start i Excel. FILTER -funktionen fungerar på samma sätt som filteralternativet. Det returnerar bara filtrerade data med en funktion. Denna filtrerade data kan användas som datakälla till andra formler.
Syntaxen för FILTER -funktionen är:
| = FILTER (matris, inkludera, [if_empty]) |
Array: Detta är matrisen som du vill filtrera. Det kan vara endimensionellt eller tvådimensionellt.
Omfatta:Det är filtret som du vill sätta på matrisen. Som, färger = "rött".
[if_empty]:Detta är valfritt. Definiera text eller uttryck om filtret inte returnerar något.
Nedanstående formel returnerar alla frukter vars färg är röd.
| = FILTER (frukt, färg = "röd", "inga frukter hittades") |
Här är frukt och färg namngivna intervall som innehåller namnen på frukterna respektive deras färger.
Du kan läsa mer om FILTER -funktionen i detalj här.
6. SORT -funktionen
I Excel 2016 och äldre var det riktigt svårt att få en sorterad matris med en formel. Denna process är förenklad i Excel 2019 och 365.
Excel 2019 introducerar funktionen SORT. SORT -funktionen sorterar den angivna matrisen i stigande eller fallande ordning efter den angivna kolumnen/raden.
Syntaxen för SORT -funktionen är:
| = SORT (matris, [sort_index], [sorteringsordning], [efter_kol]) |
Array:Det är referensen till array eller intervall som du vill sortera.
[sort_index]:Kolumnnumret i den tvådimensionella matrisen som du vill sortera intervallet efter. Som standard är det 1.
[sorteringsordning]:Den ordning som du vill sortera gruppen. För stigande är det 1 och för fallande är det -1. Som standard är det 1.
[av_col]:Ange det True (1) om du vill sortera en horisontell array. Som standard är det Falskt (0) för vertikala data.
Låt oss säga om du vill sortera värden i intervallet A2: A11 stigande. då blir formeln.
| = SORT (A2: A11) |
Jag har förklarat SORT -funktionen i detalj här.
7. SORTBY -funktionen
SORTBY -funktionen liknar SORT -funktionen. Den enda skillnaden är att sorteringsmatrisen inte behöver vara en del av den sorterade matrisen i SORTBY -funktionen.
| = SORTBY (array, sorting_array1, [order], …) |
Array:Det här är matrisen som du vill sortera.
Sorting_array1:Det här är matrisen som du vill sortera matrisen efter. Dimensionen av denna matris bör vara kompatibel med array.
[beställa]:Frivillig. Ställ in den till -1 om du vill att ordern ska sjunka. Som standard är det stigande (1).
Låt oss säga om du vill sortera intervallet A2: A11 efter område B2: B11, i fallande ordning. Då kommer formeln i excel 2019 eller 365 att vara:
| = SORTBY (A2: A11, B2: B11, -1) |
Jag har förklarat SORTBY -funktionen här i detalj.
8. Den UNIKA funktionen
I Excel 2016 och äldre använde vi ett antal funktioner i kombination för att få alla unika värden från den givna listan. Formeln som används är ganska komplex och svår att förstå.
Excel 2019 och 365 introducerar en enkel UNIK funktion som returnerar alla unika värden från en given array.
Syntaxen för funktionen UNIK är:
| = UNIK (matris, [by_col], [exakt_on]] |
Array: Matrisen som du vill extrahera unika värden från:
[av_col]: Ställ in det SANT (1) om matrisen är horisontell. Som standard är det FALSKT för vertikala data.
[exakt_ en gång]: ställ in det SANT (1) om du vill extrahera värden som bara inträffar en gång i matrisen. Som standard är det FALSKT (0) att extrahera alla unika värden.
Låt oss säga att jag bara vill få en instans av varje värde från intervall A2: A11, då är formeln:
| = UNIK (A2: A11) |
För att läsa om den UNIKA funktionen i detalj kan du klicka här.
9. SEQUENCE -funktionen
För att få en sekvens av siffror i Excel 2016 och äldre använder vi en kombination av funktioner. Lösningen fungerar men den är komplex.
Excel 2019 och 365 ger lösningen i form av SEQUENCE -funktionen. Sekvensfunktionen returnerar helt enkelt serien med numret.
Syntaxen för SEQUENCE -funktionen är:
| = SEKVENS (rader, [kolumner], [start], [steg]) |
Rader:Antalet rader som du vill spilla sekvensen till.
[kolumn]:Antalet kolumner som du vill spilla sekvensen till. Siffrorna fyller först i kolumnerna och sedan raderna. Kolumnen är valfri. Som standard är det 1.
[Start]:Frivillig. Sekvensens startnummer. Som standard är det 1.
[steg]:Detta är ökningsnumret för nästa nummer. Som standard är det 1.
Det enkla exemplet får en serie med 1 till 10. Formeln kommer att vara:
| = SEKVENS (10) |
För att förstå SEQUENCE -funktionen i Excel 365 i detaljerad läs detta.
10. RANDARRAY -funktionen
Detta är en annan dynamisk matrisformel som returnerar en rad slumpmässiga tal. Det är en kombination av funktionen RAND och RANDBETWEEN. Du kan få fraktionerade slumptal eller heltal. Du kan ange hur många slumpmässiga nummer du vill ha. Även rader och kolumner där du vill fördela dessa nummer.
Syntaxen för RANDARRAY -funktionen är:
| = RANDARRAY ([rader], [kolumner], [min], [max], [heltal]) |
Alla argument i den här funktionen är valfria. Som standard fungerar det som RAND -funktionen.
[rader]:Antalet nummer som du vill ha vertikalt (antal rader du vill fylla).
[kolumner]:Antalet nummer som du vill ha horisontellt (antal kolumner du vill fylla).
[min]:Startnumret eller minimivärdet för slumpmässiga nummer.
[max]:Det maximala intervallet för numret.
[heltal]:Ange det sant om du vill att slumptalen ska vara hela tal. Som standard är det falskt och returnerar bråkdelade slumpmässiga tal.
Funktionen nedan kommer att returnera fem slumpmässiga bråkdelar radvis:
| = RANDARRAY (5) |
Läs mer om RANDARRAY -funktionen i detalj här.
11. CONCAT -funktionen
I Excel 2016 och äldre är det inte lätt att sammanfoga mer än en cell eller ett intervall med en formel.
Excel 2019 och 365 är problemet löst med funktionen CONCAT. Funktionen kan ta flera celler, intervall som argument.
Syntaxen för CONCAT -funktionen är:
| = CONCAT (text1, [text2], …) |
Text1: Text1 kan vara valfri text eller intervall som du vill sammanfoga.
[text2]: Detta är valfritt. Detta kan också vara valfri text eller intervall.
Låt oss säga att om du vill sammanfoga varje cell i intervall A2: A11, så kommer formeln att vara
| = CONCAT (A2: A11) |
För att utforska CONCAT -funktionen i detalj klicka här.
12. Funktionen TEXTJOIN
Ovanstående funktion sammanfogar alla celler i ett område men den sammanfogar inte cellerna med någon specificerad avgränsare. Låt oss säga om du förbereder en fil för CSV -format än du behöver sammanfoga cellerna med komma. I så fall kommer CONCATENATE och CONCAT -funktionen att misslyckas.
Här fungerar TEXTJOIN -funktionen undrar och sammanfogar de givna texterna med den angivna avgränsaren.
| = TEXTJOIN (avgränsare, ignore_empty_cells, text1, [text2], …) |
Avgränsare:Detta är avgränsaren som du vill använda en separator mellan enskilda texter. Det kan vara ett komma (,), semikolon (;) eller vad som helst, till och med ingenting.
Ignore_empty_cells:Detta är en binär variabel. Ställ in den på TRUE om du vill ignorera tomma celler i intervall, annars ställs den till FALSE för att inkludera de tomma cellerna.
Text1:Det här är texten som du vill ansluta. Det kan vara enskilda texter, celler eller hela intervall.
Låt oss säga att jag vill sammanfoga intervall A2: A11 med ett komma, utan att ignorera de tomma cellerna.
| = TEXTJOIN (",", 1, A2: A11) |
För att förstå denna funktion i detalj, klicka här.
Denna artikel var bara en introduktion till den nya funktionen i Excel 365 och 2019. Jag har förklarat dessa funktioner i detalj i separata artiklar. Du kan klicka på länkarna för varje funktion i artikeln för att förstå funktionen fullt ut. Det finns andra funktioner som XLOOKUP som inte har släppts ännu.
Om du har några tvivel angående Excel- eller VBA -ämnen, fråga i kommentarfältet nedan. Berätta hur vi kan förbättra oss. Vi uppskattar ditt förslag och vill gärna höra från dig.
Skapa VBA -funktion för att returnera matris | För att returnera en matris från användardefinierad funktion måste vi deklarera den när vi heter UDF.
Matriser i Excel Formul | Lär dig vilka matriser som finns i excel.
Hur man skapar användardefinierad funktion via VBA | Lär dig hur du skapar användardefinierade funktioner i Excel
Använda en användardefinierad funktion (UDF) från en annan arbetsbok med VBA i Microsoft Excel | Använd den användardefinierade funktionen i en annan arbetsbok i Excel
Returnera felvärden från användardefinierade funktioner med VBA i Microsoft Excel | Lär dig hur du kan returnera felvärden från en användardefinierad funktion
Populära artiklar:
Dela Excel -blad i flera filer baserat på kolumn med VBA | Den här VBA -koden delar Excel -basen på unika värden i en specifik kolumn. Ladda ner arbetsfilen.
Stäng av varningsmeddelanden med VBA i Microsoft Excel 2016 | För att stänga av varningsmeddelanden som avbryter den körande VBA -koden använder vi applikationsklassen.
Lägg till och spara ny arbetsbok med VBA i Microsoft Excel 2016 | För att lägga till och spara arbetsböcker med VBA använder vi Workbooks -klassen. Workbooks.Add lägger till ny arbetsbok enkelt, dock …