3 Crazy Excel-formulär som gör fantastiska saker

Microsoft Excel är ett av de kraftfullaste kalkylarkverktygen, med en imponerande samling inbyggda verktyg och funktioner. I den här artikeln lär du dig hur kraftfulla Excel-formler och villkorlig formatering kan vara, med tre användbara exempel.

Grävning i Microsoft Excel

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 eller använda den som ett projekthanteringsverktyg.

Mycket av makten ligger bakom Excel-formlerna och reglerna som du kan skriva för att automatiskt hantera data och information, oavsett vilken data du lägger in i kalkylbladet.

Låt oss gräva hur du kan använda formulär och andra verktyg för att bättre utnyttja Microsoft Excel.

Villkorlig formatering med formler

Ett av de verktyg som människor inte använder tillräckligt ofta är villkorlig formatering. Om du letar efter mer avancerad information om villkorlig formatering i Microsoft Excel, se till att du kolla in Sandys artikel om formatering av data i Microsoft Excel med villkorlig formatering.

Med hjälp av Excel-formler, regler eller bara några få enkla inställningar kan du omvandla ett kalkylblad till en automatisk kontrollpanel.

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 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-saker som att vända en cellröd med mindre än eller högre än formler. 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 bra uppsättning ikoner som du kan använda för att konvertera en Excel-datacell till en ikon för instrumentpanelen.

När du klickar på Hantera regler, det tar dig till Villkor för formatering av regleringsregler.

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 regeln, Du ser 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 det här instrumentpanelen visas tid som spenderas på olika uppgifter jämfört med budgetterad tid. Om du går över hälften av budgeten kommer ett gult ljus att visas. Om du är helt över budgeten blir den röd.

Som du kan se visar den här instrumentpanelen att tidsbudgetering inte lyckas.

Nästan hälften av tiden spenderas långt över de budgeterade beloppen.

Tiden att fokusera på och bättre hantera din tid!

1. Använda VLookup-funktionen

Om du vill använda mer avancerade Microsoft Excel-funktioner, så här är 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 den posten.

Tyvärr kräver funktionen att objektet du söker i listan finns i den vänstra kolumnen, och de data du letar efter är 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/2018 från följande uppgifter?

I det här fallet söker du igenom värden till höger, och du vill returnera motsvarande värde till vänster - motsatt sättet VLookup fungerar normalt.

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 Excel-formuläret ut så här:

"= LETARAD (DATUM (2018,6,25), VÄLJ (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 returnera sedan 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, höger?

Det är sant, men vad gör du med “VÄLJA” funktionen manipulerar de två fälten.

Du tilldelar referens “index” siffror till dataintervall - tilldela datum till indexnummer 1 och uppgifterna till indexnummer 2.

Så när du skriver “2” I VLookup-funktionen refererar du faktiskt till Index Number 2 i CHOOSE-funktionen. Cool, höger?

Så, nu använder VLookup Datum kolumn och returnerar data från Uppgift kolumn, även om uppgiften ä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 datasökningsuppgifter bör du kolla in Danns fullständiga artikel om att hitta data i Excel med hjälp av sökfunktioner.

2. Nested Formula to Parse Strings

Här är en mer 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 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 “;” karaktär.

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.

"= VÄNSTER (A2, FIND ("; "A2,1 -1))"

Så här fungerar logiken:

• Söker textsträngen från A2
• Finns “;” avgränsningssymbol
• Subtraherar en för rätt placering av änden av den strängsektionen
• Tar den vänstra texten till den punkten

I det här fallet är den vänstra texten “Ryan”. Uppdrag slutfört.

3. Nested Formel i Excel

Men hur är det med de andra sektionerna?

Det kan finnas enklare sätt att göra detta, men eftersom vi vill försöka skapa den skrämmaste Nested Excel-formeln möjlig (som faktiskt fungerar) ska vi använda en unik metod.

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 tills dess först “;” symbolen och utför den vänstra funktionen på den igen. Det här ser ut som att extrahera gatunummerdelen av adressen.

"= VÄNSTER ((HÖGER (A2, LEN (A2) -find (";", A2))), FIND ( ";", (HÖGER (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:

HÖGER (A2, LEN (A2) -find ( ";", A2))

Och sätt in det 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 galet “HÖGER” ekvation du skapat för det sista avsnittet och sedan överföra det till en ny RIGHT-formel med den tidigare RIGHT-formeln klistrade in i sig själv vart du ser “A2”. Så här ser det ut.

(HÖGER ((HÖGER (A2, LEN (A2) -find ( ";", A2))), LEN ((HÖGER (A2, LEN (A2) -find ( ";", A2)))) - FIND ( ";", (HÖGER (A2, LEN (A2) -find ( ";", A2))))))

Då tar du den här formuläret och placerar den i den ursprungliga VÄNSTER formuläret varhelst det finns en “A2”.

Den slutliga mind-bendingformeln ser så här ut:

"= VÄNSTER ((RIGHT ((HÖGER (A2, LEN (A2) -find (";", A2))), LEN ((HÖGER (A2, LEN (A2) -find ( ";", A2))) ) -find ( ";", (HÖGER (A2, LEN (A2) -find ( ";", A2)))))), FIND ( ";", (HÖGER ((HÖGER (A2, LEN (A2) -find ( ";", A2))), LEN ((HÖGER (A2, LEN (A2) -find ( ";", A2)))) - FIND ( ";", (HÖGER (A2, LEN (A2 ) -find ( ";", A2)))))), 1 -1))"

Denna formel extraherar korrekt “Portland, ME 04076” ur den ursprungliga strängen.

För att extrahera nästa avsnitt, upprepa ovanstående process igen.

Din Excel-formler kan bli riktigt loopy, men allt du gör är att klippa och klistra in långa formler i sig, gör långa bonar som faktiskt 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 den avgränsade data och sedan under Data menyalternativ, välj Text till kolumner.

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 galna formulären ovan ... men var ä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 Excel-formlerna 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 något annat.

Om du verkligen vill bli allvarlig med att använda Excel, vill du läsa igenom vår nybörjarguide för att använda Microsoft Excel. Nybörjarhandboken till Microsoft Excel. Nybörjarhandboken till Microsoft Excel. Använd den här nybörjarguiden för att starta din erfarenhet med Microsoft Excel. Grundläggande kalkylbladets tips här hjälper dig att börja lära dig Excel på egen hand. Läs mer . Den har allt du behöver för att börja öka din produktivitet med Excel.

Utforska mer om: Microsoft Excel, Microsoft Office 2016, kalkylark.