Microsoft Excel-formler kan göra nästan vad som helst. I den här artikeln får du lära dig hur kraftfulla Microsoft Excel-formler och villkorlig formatering kan vara, med tre användbara exempel.
Lär känna Microsoft Excel
Vi har täckt ett antal olika sätt att bättre använda Excel, till exempel var man kan hitta bra nedladdningsbara Excel-mallar och hur man använder Excel som ett projektledningsverktyg.
Mycket av Excel-kraften ligger bakom Excel-formlerna och -reglerna som hjälper dig att manipulera data och information automatiskt, oavsett vilken data du infogar i kalkylarket.
Låt oss gräva i hur du kan använda formler och andra verktyg för att bättre använda Microsoft Excel.
Villkorlig formatering med Excel-formler
Ett av verktygen som folk inte använder tillräckligt ofta är villkorlig formatering. Med hjälp av Excel-formler, regler eller bara några få riktigt enkla inställningar kan du förvandla ett kalkylblad till en automatiserad instrumentpanel.
För att komma till villkorlig formatering klickar du bara på Hem fliken och klicka på Villkorlig formatering verktygsfältsikon.
Under villkorlig formatering finns det många alternativ. De flesta av dessa ligger utanför den här artikeln, men majoriteten av dem handlar om att markera, färglägga eller skugga celler baserat på data i den cellen.
Det här är förmodligen den vanligaste användningen av villkorlig formatering – saker som att göra en cell röd med mindre-än eller större-än-formler. Läs mer om hur du använder IF-satser i Excel.
Ett av de mindre använda verktygen för villkorlig formatering är Ikonuppsättningar alternativet, som erbjuder en stor uppsättning ikoner som du kan använda för att förvandla en Excel-datacell till en instrumentpanelsikon.
När du klickar på Hantera regler, det tar dig till Hantera regler för villkorlig formatering.
Beroende på vilken data du valde innan du valde ikonuppsättningen, kommer du att se cellen indikerad i fönstret Manager med den ikonuppsättning du just valde.
När du klickar på Redigera regel, kommer du att se dialogrutan där magin händer.
Det är här du kan skapa den logiska formeln och ekvationerna som visar den instrumentpanelsikon du vill ha.
Den här exemplet visar den tid som spenderas på olika uppgifter kontra budgeterad tid. Om du går över halva budgeten visas ett gult ljus. Om du är helt över budget blir den röd.
Som du kan se visar den här instrumentpanelen att tidsbudgetering inte är framgångsrik.
Nästan hälften av tiden går långt över de budgeterade beloppen.
Dags att fokusera om och hantera din tid bättre!
1. Använda VLookup-funktionen
Om du vill använda mer avancerade Microsoft Excel-funktioner, så här är ett par som du kan prova.
Du är förmodligen bekant med funktionen VLookup, som låter dig söka igenom en lista efter ett visst objekt i en kolumn och returnera data från en annan kolumn i samma rad som det objektet.
Tyvärr kräver funktionen att objektet du söker efter i listan finns i den vänstra kolumnen, och att data du letar efter finns till höger, men vad händer om de ändras?
I exemplet nedan, vad händer om jag vill hitta uppgiften som jag utförde den 25/6/2018 från följande data?
I det här fallet söker du igenom värden till höger och du vill returnera motsvarande värde till vänster.
Om du läser Microsoft Excel-pro-användarforum, kommer du att hitta många som säger att detta inte är möjligt med VLookup. Du måste använda en kombination av Index och Match funktioner för att göra detta. Det är inte helt sant.
Du kan få VLookup att fungera på detta sätt genom att kapsla in en CHOOSE-funktion i den. I det här fallet skulle Excel-formeln se ut så här:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"
Denna funktion innebär att du vill hitta datumet 2013-06-25 i uppslagslistan och sedan returnera motsvarande värde från kolumnindex.
I det här fallet kommer du att märka att kolumnindexet är “2”, men som du kan se är kolumnen i tabellen ovan faktiskt 1, eller hur?
Det är sant, men vad du gör med “VÄLJA“-funktionen manipulerar de två fälten.
Du tilldelar referens-“index”-nummer till dataintervall – tilldelar datumen till indexnummer 1 och uppgifterna till indexnummer 2.
Så när du skriver “2” i VLookup-funktionen hänvisar du faktiskt till Index nummer 2 i VÄLJ-funktionen. Coolt, eller hur?
VLookup använder nu Datum kolumn och returnerar data från Uppgift kolumn, även om Uppgift finns till vänster.
Nu när du känner till den här lilla godbiten, föreställ dig bara vad du mer kan göra!
Om du försöker göra andra avancerade uppgifter för datasökning, kolla in den här artikeln om att hitta data i Excel med hjälp av uppslagsfunktioner.
2. Kapslad formel för att analysera strängar
Här är ytterligare en galen Excel-formel för dig! Det kan finnas fall där du antingen importerar data till Microsoft Excel från en extern källa som består av en sträng av avgränsade data.
När du väl har tagit in data, vill du analysera den data till de enskilda komponenterna. Här är ett exempel på information om namn, adress och telefonnummer som avgränsas av “;” karaktär.
Så här kan du analysera den här informationen med hjälp av en Excel-formel (se om du mentalt kan följa med detta vansinne):
För det första fältet, för att extrahera objektet längst till vänster (personens namn), skulle du helt enkelt använda en VÄNSTER funktion i formeln.
"=LEFT(A2,FIND(";",A2,1)-1)"
Så här fungerar den här logiken:
Söker i textsträngen från A2 Hittar “;” avgränsningssymbol Subtraherar en för den korrekta platsen för slutet av strängsektionen Tar tag i texten längst till vänster till den punkten
I det här fallet är texten längst till vänster “Ryan”. Uppdrag slutfört.
3. Kapslad formel i Excel
Men hur är det med de andra avsnitten?
Det kan finnas enklare sätt att göra detta på, men eftersom vi vill försöka skapa den galnaste Nested Excel-formeln som är möjlig (som faktiskt fungerar), kommer vi att använda ett unikt tillvägagångssätt.
För att extrahera delarna till höger måste du kapsla flera HÖGER-funktioner för att ta tag i textavsnittet fram till det första “;” symbolen och utför VÄNSTER-funktionen på den igen. Så här ser det ut för att extrahera gatunummerdelen av adressen.
"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"
Det ser galet ut, men det är inte svårt att få ihop det. Allt jag gjorde var att ta den här funktionen:
RIGHT(A2,LEN(A2)-FIND(";",A2))
Och infogade den på varje plats i VÄNSTER funktion ovan där det finns en “A2”.
Detta extraherar den andra delen av strängen korrekt.
Varje efterföljande sektion av strängen behöver skapas ett annat bo. Nu behöver du bara ta “HÖGER” ekvation som du skapade i det förra avsnittet, och klistra in den i en ny RIGHT-formel med den tidigare RIGHT-formeln inklistrad i den där du ser “A2”. Så här ser det ut.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))
Sedan måste du ta DEN formeln och placera den i den ursprungliga VÄNSTER-formeln varhelst det finns en “A2”.
Den sista sinnesböjande formeln ser ut så här:
"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"
Den formeln extraherar korrekt “Portland, ME 04076” ur den ursprungliga strängen.
För att extrahera nästa avsnitt, upprepa ovanstående process igen.
Dina Excel-formler kan bli riktigt slingriga, men allt du gör är att klippa och klistra in långa formler i sig själva, vilket gör långa bon som fortfarande fungerar.
Ja, detta uppfyller kravet på “galen”. Men låt oss vara ärliga, det finns ett mycket enklare sätt att åstadkomma samma sak med en funktion.
Välj bara kolumnen med de avgränsade uppgifterna och sedan under Data menyalternativ, välj Text till kolumner.
Detta kommer att få upp ett fönster där du kan dela strängen med vilken avgränsare du vill. Skriv bara in ‘;‘ och du kommer att se att förhandsgranskningen av din valda data ändras i enlighet med detta.
Med ett par klick kan du göra samma sak som den där galna formeln ovan… men var är det roliga med det?
Blir galen med Microsoft Excel-formler
Så där har du det. Ovanstående formler bevisar hur överdriven en person kan bli när man skapar Microsoft Excel-formler för att utföra vissa uppgifter.
Ibland är dessa Excel-formler faktiskt inte det enklaste (eller bästa) sättet att åstadkomma saker. De flesta programmerare kommer att säga åt dig att hålla det enkelt, och det är lika sant med Excel-formler som det är med allt annat.
Om du verkligen vill bli seriös med att använda Excel, vill du läsa igenom vår nybörjarguide för att använda Microsoft Excel. Den har allt du behöver för att börja öka din produktivitet med Excel. Efter det, se till att konsultera vårt fuskblad för grundläggande Excel-funktioner för mer vägledning.
Bildkredit: kues/Depositphotos
Om författaren
Ryan Dube (936 publicerade artiklar)
Ryan har en kandidatexamen i elektroteknik. Han har arbetat 13 år inom automationsteknik, 5 år inom IT och är nu Apps-ingenjör. Han var tidigare chefredaktör för MakeUseOf och har talat vid nationella konferenser om datavisualisering och har varit med i nationell TV och radio.
Mer från Ryan Dube
Prenumerera på vårt nyhetsbrev
Gå med i vårt nyhetsbrev för tekniska tips, recensioner, free e-böcker och exklusiva erbjudanden!
Klicka här för att prenumerera
