Uppdaterad av Ryan Dube i oktober 2017.
Jag har alltid trott att Microsoft Excel är ett av de mest kraftfulla programverktygen där ute. Det är inte bara det faktum att det är en kraftfull kalkylarkansökning. Microsoft Excel själv har en så imponerande samling inbyggda verktyg och funktioner. I den här artikeln planerar jag att visa dig hur kraftfulla formler och villkorlig formatering kan vara, med tre användbara exempel.
Vi har täckt ett antal olika sätt att bättre utnyttja Excel, till exempel att använda den för att skapa en egen kalendermall. Hur man gör en kalendermall i Excel. Hur man gör en kalendermall i Excel. Du behöver alltid en kalender. Vi visar dig hur du skapar din egen anpassade mall gratis med Microsoft Excel. Läs mer, använd det som ett målhanteringsverktyg 10 + Användbara Excel-mallar för projektledning och spårning 10 + Användbara Excel-mallar för projektledning och spårning Mallar är att projektledare vilka knivar är professionella kockar: oumbärliga. Vi visar dig hur du kan replikera framgångsrika projekt genom att använda färdiga mallar i Microsoft Excel och därefter. Läs mer och andra unika sätt som du kan använda för att hantera ditt liv. Läs bara de artiklarna och du får se hur kraftfull Microsoft Excel kan vara.
Mycket av makten ligger verkligen bakom de formler och regler som du kan skriva för att automatiskt hantera data och information, oavsett vilken data du lägger in i kalkylbladet.
Grävning i Microsoft Excel
Med rätt information kan du skapa ett system som automatiskt beräknar och omberäknar resultat och rapporter från den rådata. Idag vill jag gräva lite längre under ytan och visa hur du kan använda några av de underliggande formlerna och andra verktyg för att bättre utnyttja Microsoft Excel.
Cool villkorlig formatering med formler
Ett av de verktyg som jag tycker att människor inte använder ofta ofta är villkorlig formatering. Om du letar efter mer avancerad information om villkorlig formatering i Microsoft Excel ska du kolla in Sandys artikel om formatering av data i Microsoft Excel med villkorlig formatering. Formatera automatiskt data i Excel-kalkylblad med villkorad formatering. Formatera automatiskt data i Excel-kalkylblad med villkorad formatering I Excels villkorliga formateringsfunktion kan du formatera enskilda celler i ett Excel-kalkylblad baserat på deras värde. Vi visar dig hur du använder detta för olika vardagliga uppgifter. Läs mer .
Med hjälp av formler, regler eller bara några få enkla inställningar kan du omvandla ett kalkylblad till en automatiserad instrumentpanel som visar dig mycket om informationen i kalkylbladet vid en blick.
För att komma till villkorlig formatering klickar du bara på fliken Hem och klickar på ikonen för villkorlig formatering.
Under villkorlig formatering finns en massa alternativ. De flesta av dessa ligger utanför ramen för den här artikeln, men de flesta handlar om att markera, färglägga eller skugga celler baserat på data i den cellen. Detta är förmodligen den vanligaste användningen av villkorlig formatering - gör saker som att vrida en cellröd med hjälp av logiska mindre än eller större än formler. Brad Jones beskriver hur man använder mycket av dessa i sin artikel om att skapa en Excel Dashboard Visualisera dina data och göra dina kalkylblad användarvänliga med en Excel Dashboard Visualisera dina data och gör dina kalkylblad användarvänliga med en Excel Dashboard Ibland kan ett enkelt kalkylblad format är inte engagerande för att göra dina data tillgängliga. En instrumentpanel gör att du kan presentera dina viktigaste data i ett lätt att smälta format. Läs mer - väl värt att kolla om det är ditt mål.
Ett av de mindre använda villkorliga formateringsverktygen är ikonuppsättningsalternativet, som erbjuder en stor uppsättning ikoner som du kan använda för att konvertera en Excel-datacell till en ikon för instrumentpanelen.
Jag upptäckte detta när jag uppgraderade Microsoft Office 13+ Skäl du borde uppgradera till Microsoft Office 2016 13+ Skäl du borde uppgradera till Microsoft Office 2016 Microsoft Office 2016 är här och det är dags för dig att fatta ett beslut. Produktivitetsfrågan är - ska du uppgradera? Vi ger dig de nya funktionerna och de fräschare skälen till att hjälpa dig ... Läs mer och använt villkorad formatering i Microsoft Excel för första gången.
Jag kollade ut ikonuppsättningarna och såg dessa svala LED-indikatorlampor som jag bara hade sett tidigare i några av de fabriksautomatiseringsdisplayer som jag har programmerat tidigare. När du klickar på "Hantera regler" tar det dig till villkorlig formateringsregleradministratör. Beroende på de data du valt innan du väljer ikonuppsättningen ser du cellen som anges i fönstret Manager, med den ikonuppsättning du bara har valt.
När du klickar på "Redigera regel ..." ser du dialogrutan där magiken händer. Här kan du skapa den logiska formeln och ekvationer som visar ikonen för instrumentbrädan du vill ha. I mitt exempel övervakar jag tid på olika uppgifter jämfört med min budgeterade tid. Om jag har gått över hälften av min budget vill jag ha ett gult ljus att visa, och om jag är över budgeten vill jag att den ska bli röd.
Som du kan se, budgetar jag inte riktigt min tid för bra. Nästan hälften av min tid spenderas långt över vad jag har budgetat.
Tid att refokusera och bättre hantera min tid Använd 80/20 Time Management Rule för att prioritera dina uppgifter Använd 80/20 Time Management Rule för att prioritera dina uppgifter Hur maximerar du din tid? Om du redan prioriterar, delegerar och fortfarande kämpar för att få allt gjort, borde du prova 80/20-regeln, även känd som Pareto Principle. Läs mer !
Titta på objekt med VLookup-funktionen
Okej, det är kanske inte galen nog för dig. Kanske är du inte så upphetsad om enkla logiska formler som slår på och av ljus. Om du gillar att använda mer avancerade Microsoft Excel-funktioner, har jag gått en annan för dig.
Du är förmodligen bekant med VLookup-funktionen, som låter dig söka igenom en lista för ett visst objekt i en kolumn och returnera data från en annan kolumn i samma rad som det där objektet. Tyvärr kräver funktionen att objektet du söker i listan finns i den vänstra kolumnen, och de data du letar efter finns till höger, men vad händer om de byts?
I exemplet nedan, vad händer om jag vill hitta den uppgift som jag utförde den 6/25/2013 från följande uppgifter?
I det här fallet söker du igenom värdena till höger och du vill returnera motsvarande värde till vänster - motsatsen till VLookup fungerar normalt Sök Excel-kalkylblad Snabbare: Byt VLOOKUP MED INDEX och MATCH Sök Excel Kalkylblad Snabbare: Byt VLOOKUP Med INDEX och MATCH Fortfarande använder du VLOOKUP för att söka efter information i ditt kalkylblad? Så här kan INDEX och MATCH erbjuda en bättre lösning. Läs mer . Om du läser Microsoft Excel pro-användarforum hittar du många människor som säger att det inte är möjligt med VLookup och att 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 arbeta på det här sättet genom att nesta en CHOOSE-funktion i den. I så fall ser formeln ut så här:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Vad den här funktionen betyder är att du vill hitta datumet 6/25/2013 i söklistan och sedan returnera motsvarande värde från kolumnindexet. I det här fallet märker du att kolumnindexet är "2", men som du kan se kolumnen i tabellen ovan är faktiskt 1, eller hur?
Det är sant, men vad du gör med "VÄLJ" -funktionen manipulerar de två fälten. Du tilldelar referensnummer till dataintervall - tilldela datum till indexnummer 1 och uppgifterna till index nummer två. Så när du skriver "2" i VLookup-funktionen refererar du faktiskt till Index nummer 2 i VÄLJ-funktionen. Crazy, va?
Så nu använder VLookup kolumnen Datum och returnerar data från kolumnen Uppgift, trots att Uppgift är till vänster. Nu när du känner till den här lilla tidbiten, föreställ dig bara vad du kan göra!
Om du försöker göra andra avancerade datauppslagningsuppgifter, var noga med att kolla in Danns fullständiga artikel om att hitta data i Excel med hjälp av sökfunktioner. Hitta någonting i ditt Excel-kalkylblad med Lookup-funktioner. Hitta någonting i ditt Excel-kalkylblad med Lookup-funktioner i en jätte Excel-kalkylblad, CTRL + F kommer bara att få dig så långt. Var smart och låt formler göra det hårda arbetet. Lookup formler sparar tid och är lätta att tillämpa. Läs mer .
Insane Nested Formulas att Parse Strings
Som du kan se försöker jag bli lite snällare när vi går, för jag vet att det finns några av er ute och tänker, "... det är inte galet alls !!" Jag vet, dina standarder är höga. Jag försöker leva upp till dem. Här är en mer galen 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 avgränsad data. När du har tagit in data, vill du analysera den data i de enskilda komponenterna. Här är ett exempel på namn, adress och telefonnummer information avgränsad av ";" tecknet.
Så här kan du analysera den här informationen med hjälp av en Excel-formel (se om du psykiskt kan följa med denna vansinne):
För det första fältet, för att extrahera det vänstra objektet (personens namn), skulle du helt enkelt använda en VÄNSTER funktion i formeln.
"=LEFT(A2, FIND(";", A2, 1)-1)" Detta söker efter textsträngen från A2, finner symbolen ";" avgränsar, subtraherar en för den korrekta platsen för slutet av strängdelen och tar sedan den vänstra texten till den punkten. I det här fallet är det "Ryan". Uppdrag slutfört.
Nesting Excel-formulär
Men hur är det med de andra sektionerna? Tja, för att extrahera delarna till höger måste du näsa flera RIGHT-funktioner för att ta tag i sektionen av texten fram till den första "" "-symbolen och utföra VÄNSTER-funktionen på den igen. Det här ser ut som 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 bita ihop. Allt jag gjorde är den här funktionen:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" Och sätt in den på alla ställen i den vänstra funktionen ovanför där det finns en "A2". Detta extraherar korrekt strängens andra avsnitt.
Varje efterföljande sektion av strängen behöver en annan byggnad skapad. Så nu tar du bara den galna "RIGHT" ekvationen du skapade för det sista avsnittet och skickar sedan in den till en ny RIGHT-formel med den tidigare RIGHT-formeln klistrad in i sig själv vart 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 tar du den här formuläret och placerar den i den ursprungliga VÄNSTER formuläret där det finns en "A2". Den slutliga mind-bendingformeln ser så här ut:
"=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 "Portland, ME 04076" ur den ursprungliga strängen.
För att extrahera nästa avsnitt, upprepa ovanstående process igen. Dina formler kan bli riktigt loopy, men allt du gör är att skära och klistra in långa formler i sig, gör långa bon som fungerar riktigt bra!
Ja, det här uppfyller kravet på "galet", men låt oss vara ärliga ... det är ett mycket enklare sätt att åstadkomma samma sak med en funktion. Markera bara kolumnen med den avgränsade data och välj sedan Text till kolumner under menyn Data . Detta kommer att föra upp ett fönster där du kan dela upp strängen med någon avgränsare du vill ha.
Med ett par klick kan du göra samma sak som den här formeln ovan ... men vad är kul i det?
Bli galen med Microsoft Excel
Så där har du det. Ovanstående formler visar bara hur över-topp en person kan få när man skapar Microsoft Excel-formler för att utföra vissa uppgifter. Ibland är dessa formler inte egentligen det enklaste (eller bästa) sättet att uppnå saker. De flesta programmerare kommer att berätta för dig att det är enkelt och det är lika sant med Excel-formulär 16 Excel-formulär som hjälper dig att lösa verkliga problem 16 Excel-formulär som hjälper dig att lösa verkliga problem Det rätta verktyget är hälften av arbetet. Excel kan lösa beräkningar och bearbeta data snabbare än du kan hitta din räknare. Vi visar dig viktiga Excel-formulär och visar hur du använder dem. Läs mer som något annat.
Var dessa formler och tekniker vilda nog? Har du några fantastiska formverktyg som utgör din Microsoft Excel-verktygsverktyg? Dela din inmatning och feedback i kommentarfältet nedan!
Bildkredit: Kues / Depositphotos