Home-theater-designers
Microsoft Excel -formler kan göra nästan vad som helst. I den här artikeln lär du 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, t.ex. var hittar du bra nedladdningsbara Excel -mallar , och hur man använder Excel som ett projekthanteringsverktyg .
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 människor inte använder tillräckligt ofta är villkorlig formatering. Med hjälp av Excel -formler, regler eller bara några riktigt enkla inställningar kan du omvandla 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ältikonen.
Under Villkorlig formatering finns det många alternativ. De flesta av dessa ligger utanför ramen för den här artikeln, men majoriteten av dem handlar om att markera, färga eller skugga celler baserat på data i den cellen.
Detta är förmodligen den vanligaste användningen av villkorlig formatering-saker som att göra en cell röd med formler som är mindre än eller större än. Läs mer om hur du använder IF -uttalanden i Excel.
Ett av de mindre använda villkorliga formateringsverktygen är Ikonuppsättningar alternativ, som erbjuder en fantastisk 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 Villkorlig formateringsregelhanterare .
bästa linux distro för webbutveckling
Beroende på vilken data du valde innan du valde ikonuppsättningen ser du cellen som anges i Manager -fönstret med den ikonuppsättning du just valde.
När du klickar på Redigera regel , ser du 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.
Detta exempel på instrumentpanelen visar tid som används åt olika uppgifter jämfört med budgeterad tid. Om du går över halva budgeten visas ett gult ljus. Om du har överskridit budgeten blir det rött.
Som du kan se visar denna instrumentpanel att tidsbudgetering inte lyckas.
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, här är några för dig att prova.
Du är förmodligen bekant med VLookup -funktionen, som låter dig söka igenom en lista efter ett visst objekt i en kolumn och returnera data från en annan kolumn på 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 data som du letar efter finns till höger, men vad händer om de byts?
I exemplet nedan, vad händer om jag vill hitta uppgiften som jag utförde den 25/06/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 hittar du många som säger att detta inte är möjligt med VLookup. Du måste använda en kombination av index- och matchningsfunktioner för att göra detta. Det är inte helt sant.
Du kan få VLookup att fungera på detta sätt genom att häcka en VÄLJ -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 2006-06-25 i söklistan och sedan returnera motsvarande värde från kolumnindexet.
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 är att manipulera de två fälten.
Du tilldelar referens 'index' nummer till dataområden - tilldelar datumen till index nummer 1 och uppgifterna till index nummer 2.
Så när du skriver '2' i VLookup -funktionen hänvisar du faktiskt till indexnummer 2 i funktionen VÄLJ. Coolt, eller hur?
VLookup använder nu Datum kolumn och returnerar data från Uppgift kolumn, även om uppgiften är till vänster.
hur man skapar en e -postgrupp i outlook
Nu när du känner till den här lilla grejen, tänk dig vad mer du kan göra!
Om du försöker utföra andra avancerade datasökningsuppgifter, kolla in den här artikeln om 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 rad avgränsade data.
När du väl har lagt in data vill du analysera dessa data i 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 denna information med hjälp av en Excel -formel (se om du mentalt kan följa med denna galenskap):
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 textsträngen från A2
- Hittar ';' avgränsare symbol
- Subtraherar en för rätt plats för slutet av den 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, 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 haka flera RIGHT -funktioner för att få tag i textdelen tills det första ';' symbolen och utföra 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 slå ihop. Allt jag gjorde är att ta den här funktionen:
RIGHT(A2,LEN(A2)-FIND(';',A2))Och infogade det på varje plats i VÄNSTER funktion ovanför där det finns en 'A2'.
Detta extraherar korrekt den andra delen av strängen.
Windows 10 file explorer mörkt läge
Varje efterföljande avsnitt av strängen behöver ett annat bo skapat. Allt du behöver göra är att ta ' HÖGER 'ekvation som du skapade i det sista avsnittet och klistra in den i en ny RIGHT -formel med den tidigare RIGHT -formeln klistrad in 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))))))Därefter måste du ta DEN formeln och placera den i den ursprungliga VÄNSTER -formeln varhelst det finns en 'A2'.
Den slutliga tankbö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 om igen.
Dina Excel -formler kan bli riktigt loopiga, 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 uppnå samma sak med en funktion.
Välj bara kolumnen med avgränsade data och sedan under Data menyalternativ, välj Text till kolumner .
Detta öppnar ett fönster där du kan dela strängen med vilken avgränsare du vill. Skriv bara in ' ; 'och du ser att förhandsgranskningen av din valda data ändras i enlighet därmed.
Med ett par klick kan du göra samma sak som den galna formeln ovan ... men var är det roliga i det?
Bli galen med Microsoft Excel -formler
Så där har du det. Ovanstående formler bevisar hur överdriven en person kan bli när han 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 säger att du ska hålla det enkelt, och det är lika sant med Excel -formler som 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 öka produktiviteten med Excel. Efter det, se till att konsultera våra viktiga Excel -funktioner fuskblad för mer vägledning.
Bildkredit: kues/Depositphotos
Dela med sig Dela med sig Tweet E-post De 7 bästa ekonomiska funktionerna i ExcelOavsett om du är en revisor eller en finansproffs, bör du känna till dessa Excel -formler.
Läs Nästa Relaterade ämnen- Produktivitet
- Tips för kalkylblad
- Microsoft excel
- Microsoft Office 2016
- Microsoft Office -tips
- Microsoft Office 2019
Ryan har en kandidatexamen i elektroteknik. Han har arbetat 13 år inom automationsteknik, 5 år inom IT och är nu Apps Engineer. Han var tidigare chefredaktör för MakeUseOf och talade på nationella konferenser om datavisualisering och har presenterats på nationell TV och radio.
Mer från Ryan DubePrenumerera på vårt nyhetsbrev
Gå med i vårt nyhetsbrev för tekniska tips, recensioner, gratis e -böcker och exklusiva erbjudanden!
Klicka här för att prenumerera