3 galna Excel-formler som gör fantastiska saker

• Produktivitet

Annons

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ås upp fuskbladet "Essential Excel Formulas" nu!

Detta kommer att registrera dig för vårt nyhetsbrev

Gräva i Microsoft Excel

Vi har behandlat ett antal olika sätt att bättre utnyttja Excel, till exempel att använda det för att skapa din egen kalendermall eller använda den som ett projekthanteringsverktyg.

Mycket av kraften ligger bakom Excel-formlerna och reglerna som du kan skriva för att automatiskt manipulera data och information, oavsett vilken information du sätter in i kalkylarket.

Låt oss gräva in 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. Om du letar efter mer avancerad information om villkorlig formatering i Microsoft Excel ska du kolla in Sandys artikel om formateringsdata i Microsoft Excel med villkorlig formatering.

Med hjälp av Excel-formler, regler eller bara några 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å fliken Hem och klickar på verktygsfältet Ikon för villkorlig formatering .

Under Villkorlig formatering finns det många alternativ. De flesta av dessa är utanför ramen för just den här artikeln, men de flesta av dem 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 göra en cell röd med mindre än eller större än formler. Läs mer om hur du använder IF-uttalanden i Excel.

Ett av de mindre använda villkorade formateringsverktygen är alternativet Ikonuppsättningar, som erbjuder en stor uppsättning ikoner du kan använda för att förvandla en Excel-datacell till en ikon för instrumentpanelsvisning.

När du klickar på Hantera regler, tar du dig till hanteringen av villkorade formateringsregler .

Beroende på vilken information du valt innan du valde ikonuppsättningen ser du cellen som anges i Manager-fönstret med den ikonuppsättning du just har valt.

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 instrumentpanelikon du vill ha.

Det här exemplet på instrumentpanelen visar 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 budgeten blir den röd.

Som ni ser visar denna instrumentpanel att tidsbudgetering inte är framgångsrik.

Nästan hälften av tiden ägnas åt de budgeterade beloppen.

Dags att fokusera och bättre hantera din tid!

1. Använda VLookup-funktionen

Om du vill använda mer avancerade Microsoft Excel-funktioner är här en annan åt dig.

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 i samma rad som det objektet.

Tyvärr kräver funktionen att objektet du letar efter i listan finns i den vänstra kolumnen och att de data du letar efter är till höger, men vad händer om de växlas?

Vad gör jag i exemplet nedan om jag vill hitta den uppgift som jag utförde den 25/25/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 - mittemot hur VLookup normalt fungerar.

Om du läser Microsoft Excel pro-user forum hittar du många som säger att detta 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 fungera på detta sätt genom att bygga in en VÄLJ-funktion i den. I detta fall ser Excel-formeln ut så här:

 "=VLOOKUP(DATE(2018, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" 

Vad den här funktionen innebär är att du vill hitta datum 6/25/2013 i uppslagslistan och sedan returnera motsvarande värde från kolumnindex.

I detta fall 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 det du gör med funktionen ”VÄLJ” är att manipulera de två fälten.

Du tilldelar referensnummer "index" till dataintervall - tilldelar datum till index nummer 1 och uppgifterna till index nummer 2.

Så när du skriver "2" i VLookup-funktionen hänvisar du faktiskt till index nummer 2 i CHOOSE-funktionen. Cool, eller hur?

Så nu använder VLookup kolumnen Datum och returnerar data från kolumnen Uppgift, även om uppgiften är till vänster.

Nu när du känner till den lilla lurviten, föreställ dig vad du annars kan göra!

Om du försöker göra andra avancerade uppsökningar för datainsamling ska du kolla Danns hela artikel om hur du hittar data i Excel med uppslagningsfunktioner.

2. Nested Formula to Parse Strings

Här är en galen Excel-formel till dig.

Det kan förekomma 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 uppgifterna vill du analysera den i de enskilda komponenterna. Här är ett exempel på information om namn, adress och telefonnummer avgränsat av ";" -tecknet.

Så här kan du analysera denna information med hjälp av en Excel-formel (se om du mentalt 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)" 

Så här fungerar den här logiken:

• Söker i textsträngen från A2
• Hitta symbolen ";" för avgränsning
• Subtraherar en för rätt plats för slutet av strängavsnittet
• Tar den längsta texten till den punkten

I det här fallet är den vänstra texten "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 möjligt (som faktiskt fungerar) kommer vi att använda en unik strategi.

För att extrahera delarna till höger måste du häcka flera HÖGER-funktioner för att ta textavsnittet upp till den 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 galen ut, men det är inte svårt att gå ihop. Allt jag gjorde var att ta denna funktion:

 RIGHT(A2, LEN(A2)-FIND(";", A2)) 

Och sätter in den på alla platser i VÄNSTER-funktionen 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 ett nytt bo skapat. Så nu tar du bara den galna "RIGHT" -ekvationen som du skapade för det sista avsnittet och överför den sedan till en ny RIGHT-formel med den föregående RIGHT-formeln klistras in i sig själv oavsett var 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 formeln och placerar den i den ursprungliga Vänsterformeln där det finns en "A2".

Den slutliga mind-bending 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älv, göra långa bon som faktiskt fungerar.

Ja, detta uppfyller kravet för "galna". Men låt oss vara ärliga, det finns ett mycket enklare sätt att utföra samma sak med en funktion.

Välj bara kolumnen med avgränsad data, och välj sedan Text till kolumner under menyalternativet Data .

Detta öppnar ett fönster där du kan dela strängen med vilken avgränsare du vill.

Med ett par klick kan du göra samma sak som den galna formeln ovan ... men var är det roliga i det?

Blir galen med Microsoft Excel-formler

Så där har du det. Ovanstående formler bevisar hur överdrivet 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 göra saker. De flesta programmerare kommer att säga dig att hålla det enkelt, och det är lika sant med Excel-formler som allt annat. Du kan till och med använda inbyggda funktioner som Power Query.

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 Nybörjarguiden till Microsoft Excel Nybörjarguiden till Microsoft Excel Använd denna nybörjarguide för att starta din upplevelse med Microsoft Excel. De grundläggande kalkylarkstips 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. Efter det, se till att du söker våra viktiga Excel-funktioner fuskschema De väsentliga Microsoft Excel-formlerna och -funktioner Fuskblad De väsentliga Microsoft Excel-formlerna och -funktionerna Fuskblad Ladda ner detta Excel-formel fusksblad för att ta en genväg genom världens favorit kalkylbladsprogram. Läs mer .

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