Med fortsatt användning Microsoft Excel Att spåra eller Budgetering Eller inventering, du kan lära dig mycket om tillgängliga funktioner och deras formler. Men att upptäcka något nytt kan vara förvirrande eller skrämmande. Den här guiden introducerar några användbara funktioner och deras formler när du är redo att ge dig ut i nya territorier. Här är 5 användbara avancerade Excel-formler som du bör känna till.
1. Hitta värden i ditt kalkylblad: XLOOKUP
När du vill slå upp data baserat på annan data i ett stort kalkylblad är XLOOKUP din bästa funktion. Till skillnad från VLOOKUP Till skillnad från ett kalkylblad som bara kan söka efter värden från vänster till höger, kan XLOOKUP söka efter värden från vänster till höger eller från höger till vänster, vilket gör det till ett mer flexibelt verktyg.
Obs! I skrivande stund är XLOOKUP endast tillgängligt för Microsoft 365-prenumeranter. För andra versioner av Excel, kontrollera kombinationen INDEX och MATCH i nästa avsnitt.
Funktionsformeln är:
LETARUT(sökvärde, sökintervall, returintervall, hittades inte, matchningsläge, sökläge)
Endast de tre första argumenten krävs. En beskrivning av varje argument finns nedan:
- Lookup_value: Värdet att söka efter.
- Sökintervall: Det område som innehåller det värde som ska sökas efter.
- Returintervall: Intervallet som innehåller värdet som ska returneras.
- Inte_hittat: Texten som ska returneras om värdet inte hittas. "#N/A" är standardvärdet om det utelämnas.
- Matchningsläge: Matchningstyp med "0" för exakt matchning och "#N/A" för hittades inte (standard om utelämnad), "1" för exakt matchning och nästa mindre objekt om ingen hittas, "-1" för exakt matchning och nästa större objekt om ingen hittas, eller "2" för jokerteckenmatchning med frågetecken, asterisk eller tilde.
- Sökläge: Sökläge med "1" för att börja från det första objektet (standard om utelämnat), "-1" för att börja från det sista objektet, "2" när "lookup_range" är i stigande ordning, eller "-2" när "lookup_range" är i fallande ordning.
Till exempel letar vi efter försäljning för sektor 2, så vi använder följande formel:
=XLOOKUP(2,A19:A24,D19:D24)
Om man bryter ner formeln är 2 "sökvärde", A19:A24 är "sökintervall" och D19:D24 är argumentet "returintervall".
Resultatet, 74000, är det korrekta motsvarande värdet för sektor 2.
Om du vill ha något mer dynamiskt, där du slår upp ett värde i en cell snarare än en konstant, och sedan får det uppdaterade resultatet varje gång du ändrar värdet i den cellen, se följande exempel:
Vi ersätter "lookup_value" 2 med cell F19:
=XLOOKUP(F19,A19:A24,D19:D24)
När vi skriver in 2 i cell F19 får vi resultatet 74000, och om vi skriver in ett annat värde, till exempel 5, får vi automatiskt det uppdaterade resultatet, vilket är 61000.
Du kan inkludera medlaren. "icke_hittad" Så att du inte behöver titta på ett felmeddelande om ingen matchning hittas, lägger vi till "svara inte" i argumentet.
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
När ett värde som inte matchar anges i cell F19, visas ditt meddelande istället för "#NA".
2. Hitta det alternativa värdet i ett ark: INDEX och MATCH
Även om XLOOKUP erbjuder ett kraftfullt sätt att slå upp värden, är det för närvarande endast tillgängligt för Microsoft 365 Excel-prenumeranter. Om du använder en annan version av Excel kan du använda INDEX och MATCH för att göra samma sak.
Med funktionen INDEX returneras värdet i en cell baserat på den position du anger i formeln. INDEX-formeln är:
INDEX(intervall, radnummer, kolumnnummer)
Med funktionen MATCH returneras positionen för det värde du anger i en formel. MATCH-formeln är:
MATCH(värde, intervall, matchningstyp)
För att kombinera dessa två funktioner och deras formler, placera MATCH-formeln i INDEX-formeln som sökplats ("radnummer" och "kolumnnummer").
Med samma data som i XLOOKUP-exemplet ovan vill vi se segmentförsäljningen som vi anger i cell F19. Formeln är:
=INDEX(D19:D24,MATCH(F19,A19:A24))
För att dela upp formeln börjar vi med INDEX och dess "intervall"-argument, vilket är D19:D24. Detta är intervallet som innehåller det resultat vi vill ha.
MATCH-formeln använder F19 som argumentet "värde" och A19:A24 som argumentet "intervall" som innehåller det värdet.
När vi skriver in 4 i cell F19 får vi resultatet 75000, vilket är korrekt.
Om vi anger en annan sektor i cell F19, till exempel 6, uppdateras formeln automatiskt för att ge det korrekta värdet 58000.
3. Addera eller räkna efter kriterier: SUMMA.OM, SUMMA.OM, ANTAL.OM och ANTAL.OM
Funktionerna SUMMA och ANTAL i Excel är utformade för att addera tal och räkna celler, men de är begränsade till enkla aritmetiska operationer. Varianter av dessa funktioner låter dig addera eller räkna med hjälp av kriterier. Du kan till exempel bara addera tal större än 12 eller räkna celler som innehåller namnet "Bill".
Med SUMMA.OM och ANTAL.OM kan du addera eller räkna med ett enda villkor, och med SUMMA.OM och ANTAL.OM kan du addera eller räkna med flera villkor. Nedan listas syntaxerna för varje villkor:
SUMIF
SUMMAOM(intervall; kriterier; summaintervall)
COUNTIF
ANTALOM(intervall, villkor)
SUMMA.OMF
SUMMAOMFÄRD(summaintervall, villkorsområde1, villkor1, villkorsområde2, villkor2,…)
ANTAL.OMF
ANTALOMFÄRDNINGAR(villkorsområde1, villkor1, villkorsområde2, villkor2,…)
Se nedan för ett exempel på varje funktion.
För att addera talen i vårt intervall från B2 till B7 endast för tal större än 12, använder vi funktionen SUMIF och denna formel:
=SUMMA.OM(B2:B7;">12")
Resultatet är 31, eftersom formeln adderade 16 och 15, de enda två talen större än 12 i datamängden.
I följande exempel räknar vi celler med värden mindre än 12 i samma intervall, från B2 till B7, med följande formel:
=ANTALOM(B2:B7,"<12")
Resultatet av denna formel är 4, eftersom detta är antalet celler i området med värden mindre än 12.
Låt oss titta på SUMIFS och använda två villkor. För att addera talen i cellerna B2 till B7 för endast de som prenumererar på vårt nyhetsbrev (Y) i cellerna C2 till C7 och vars stad är San Diego i cellerna D2 till D7, använder vi följande formel:
=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")
Resultatet av denna formel är 19, eftersom det bara finns två klienter med Y för nyhetsbrev och San Diego för stad. Bill Brown har 11 artiklar och Sue Smith har 8 artiklar, totalt 19 artiklar.
I ett exempel med funktionen ANTAL.OMF räknar vi antalet celler med samma två villkor som beskrivs ovan. Vi räknar antalet kunder som registrerat sig för nyhetsbrevet (Y) och deras stad är San Diego.
=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")
Som väntat blev resultatet 2 för Bill Brown och Sue Smith, den enda nyhetsbrevsprenumerationen i San Diego.
4. Testdata under olika förhållanden: IFS
Funktionen OM i Excel är ett kraftfullt verktyg för att testa data mot villkor. Du kan till exempel använda den för att visa ett bokstavsbetyg för en students numeriska poäng, eller ett "ja" om en säljare genererar tillräckligt med intäkter för att tjäna sin bonus, och ett "nej" om de inte gör det.
Problemet med OM-funktionen är att för att testa dina data mot flera kriterier måste du kombinera alla OM-satser. För att åtgärda denna förvirring kan du använda OM-funktionen.
I likhet med SUMIFS och COUNTIFS ovan, låter IFS dig lägga till flera kriterier i din formel på ett tydligt och lättläst sätt.
Syntaxen är IFS(test1, if_test1_true, test2, if_test2_true, …), där du kan testa upp till 127 olika kriterier.
Med hjälp av ett av våra exempelscenarier började vi helt enkelt med att ange belöningarna. Om beloppet i cell B2 är högre än 20000 XNUMX, visa "Ja". Om inte, visa "inga". Formeln blir:
=OMS(B2>20000,"Ja",B2<20000,"Nej")
I det första testet, om värdet i B2 är större än 20000, blir resultatet om det är sant "ja". I det andra testet, om värdet i B2 är mindre än 20000, blir resultatet om det är sant "inga".
Som tur är får vår säljare sin bonus. Eftersom hans försäljning är 21000 XNUMX, "Ja" I bonuskolumnen.
Kopiera formeln om du har en lista som vårt exempel. Dra fyllningshandtaget i cellens nedre högra hörn till de återstående cellerna för de andra säljarna. Formeln uppdateras automatiskt för att inkludera de olika cellreferenserna.
För ett annat exempel från IFS har vi en lista över jourhavande chefer för varje veckodag. När den aktuella dagen visas i cell D2 visas motsvarande chefs namn i cell E2. Formeln blir:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
Detta visar att om värdet i cell D2 är måndag, visas namnet i cell B2, om värdet i cell D2 är tisdag, visas namnet i cell B3, om värdet i cell D2 är onsdag, visas namnet i cell B3, och så vidare.
När du ändrar veckodag i cell D2 visas motsvarande namn på jourhavande chef i cell E2.
Med lite arbete för att skapa formeln i förväg kan du dra nytta av viss automatisering i ditt Excel-ark.
5. Filtrera med hjälp av flera kriterier: Filtrera
Som Excel-användare vet du förmodligen att programmet har en inbyggd filtreringsfunktion. Men om du vill filtrera dina data med hjälp av ett villkor eller till och med flera kriterier måste du använda filterfunktionen. Denna flexibla funktion och dess argument kan hjälpa dig att begränsa stora datamängder på bara några minuter.
Funktionens syntax är FILTER(intervall, intervall=kriterier, om_tom), där endast de två första argumenten för datamängden plus kriterierna och dess containerintervall krävs. Det tredje argumentet är användbart om du vill returnera något specifikt om formeln inte returnerar noll resultat, till exempel "Inga uppgifter" أو "ingenting".
Det är viktigt att förstå hur FILTER-funktionen fungerar med ett enda villkor innan du lägger till flera kriterier. Du kan till exempel filtrera data i cellerna A2 till C9 efter Harold Hill, som finns i cellerna B2 till B9. Formeln för detta är:
=FILTER(A2:C9,B2:B9="Harold Hill")
Vi har två resultat för Harold Hill. Enkelt, eller hur?
Låt oss lägga till fler villkor i formeln. Flera kriterier läggs till med en asterisk (*) för OCH och ett plustecken (+) för ELLER.
Till exempel filtrerar vi bort båda: Harold Hill و Elektronik Med hjälp av följande formel:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
För att dela formeln är A2:C9 datamängden, B2:B9 = “Harold Hill” Det är det första villkoret, och asterisken representerar OCH, och A2: A9 = “Elektronik” Det är det andra villkoret.
Vi fick ett resultat med vårt filter, där formeln ska matcha båda villkoren, Harold Hill och Elektronik.
I ett annat exempel kan du filtrera efter kläder eller bilar:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Genom att dividera denna formel får man A2:C9 som datamängd, A2:A9 = ”Kläder” Det är det första villkoret, och plustecknet representerar ELLER, och A2:A9 = “Fordon” Det är det andra villkoret.
Den här gången får vi två resultat, eftersom formeln ska matcha endera villkoret – inte båda.
Vanliga frågor
F1: Hur kan jag hitta formelstrukturen för en funktion direkt i Excel?
Svar: Excel har ett inbyggt verktyg för att hitta den funktion du behöver. Med hjälp av det kan du se syntaxen för funktionens formel.
Markera en tom cell, gå till fliken Formler och välj Infoga funktion till vänster i menyfliksområdet. Skriv in funktionen i sökrutan högst upp och klicka på Gå. När du ser den funktion du vill använda markerar du den för att se dess beskrivning och syntax längst ner i rutan.
F2: Hur kan jag korrigera en formel som inte fungerar korrekt?
Svar: Markera formeln du har problem med och gå till fliken Formler. Välj Utvärdera formel i avsnittet Formelgranskning i menyfliksområdet.
Du ser din formel i fönstret Utvärdera formel, med en del av den understruken. Klicka på Utvärdera för att se beräkningen av den understrukna delen. Fortsätt med den här processen för att stega igenom varje del av formeln till resultatet. Detta låter dig se hur formeln bearbetas och var fel kan ha uppstått.
F3: Hur kan jag ta reda på varför jag fick ett formelfel?
Svar: När du ser ett fel i en formel du har skapat kan du göra två saker för att få mer information. Klicka först på felknappen som visas bredvid cellen och välj sedan "Hjälp med det här felet" för att få mer information från Microsoft i sidofältet som visas.
Alternativt kan du välja "Visa beräkningssteg" för att öppna fönstret Formelutvärdering som visas ovan. Du ser resultatet av beräkningen och den process som används, vilket bör hjälpa till att identifiera orsaken till felet.
