3 skøre Excel-formler, der gør fantastiske ting

• Produktivitet

Reklame

Excel-formler kan gøre næsten alt. I denne artikel lærer du, hvor kraftfulde Microsoft Excel-formler og betinget formatering kan være, med tre nyttige eksempler.

Lås op for snydarket "Væsentlige Excel-formler"!

Dette vil tilmelde dig vores nyhedsbrev

Udgravning i Microsoft Excel

Vi har dækket en række forskellige måder til bedre at udnytte Excel, f.eks. At bruge det til at oprette din egen kalenderskabelon eller bruge den som et projektstyringsværktøj.

Meget af kraften ligger bag Excel-formlerne og reglerne, som du kan skrive for automatisk at manipulere data og information, uanset hvilke data, du indsætter i regnearket.

Lad os grave i, hvordan du kan bruge formler og andre værktøjer til at udnytte Microsoft Excel bedre.

Betinget formatering med Excel-formler

Et af de værktøjer, som folk ikke bruger ofte nok, er Betinget formatering. Hvis du leder efter mere avanceret information om betinget formatering i Microsoft Excel, skal du sørge for at tjekke Sandys artikel om formateringsdata i Microsoft Excel med betinget formatering.

Ved hjælp af Excel-formler, regler eller bare et par rigtig enkle indstillinger kan du omdanne et regneark til et automatiseret betjeningspanel.

For at komme til betinget formatering skal du blot klikke på fanen Hjem og klikke på ikonet Værktøjslinje betinget formatering .

Under betinget formatering er der mange muligheder. De fleste af disse er uden for denne specifikke artikels rækkevidde, men størstedelen af ​​dem handler om at fremhæve, farve eller skygge celler baseret på dataene i den celle.

Dette er sandsynligvis den mest almindelige brug af betinget formatering - ting som at gøre en celle rød ved at bruge mindre end eller større end formler. Lær mere om, hvordan du bruger IF-sætninger i Excel.

Et af de mindre brugte betingede formateringsværktøjer er indstillingen Ikonindstillinger, der tilbyder et stort sæt ikoner, du kan bruge til at omdanne en Excel-datacelle til et ikon for instrumentbræt.

Når du klikker på Administrer regler, fører det dig til den betingede formateringsregler Manager .

Afhængigt af de data, du valgte, før du valgte ikonsættet, vil du se den celle, der er angivet i Manager-vinduet med det ikonsæt, du lige har valgt.

Når du klikker på Rediger regel, ser du dialogen, hvor magien sker.

Det er her du kan oprette den logiske formel og ligninger, der viser det ønskede betjeningspanelikon.

Dette eksempel på dashboard viser tid brugt på forskellige opgaver kontra budgetteret tid. Hvis du går over halvdelen af ​​budgettet, vises et gult lys. Hvis du er over budget, bliver det rødt.

Som du kan se, viser dette dashboard, at tidsbudgettering ikke er vellykket.

Næsten halvdelen af ​​tiden bruges meget over de budgetterede beløb.

Tid til at fokusere igen og bedre styre din tid!

1. Brug af VLookup-funktionen

Hvis du gerne vil bruge mere avancerede Microsoft Excel-funktioner, er her en anden til dig.

Du er sandsynligvis bekendt med VLookup-funktionen, som giver dig mulighed for at søge gennem en liste efter et bestemt emne i en kolonne og returnere dataene fra en anden kolonne i samme række som det punkt.

Desværre kræver funktionen, at det element, du søger efter på listen, er i venstre kolonne, og de data, du leder efter, er til højre, men hvad nu hvis de skiftes?

Hvad i nedenstående eksempel, hvad hvis jeg vil finde den opgave, som jeg udførte den 25/25/2018 ud fra følgende data?

I dette tilfælde søger du gennem værdier til højre, og du vil returnere den tilsvarende værdi til venstre - modsat den måde, VLookup normalt fungerer.

Hvis du læser Microsoft Excel-pro-brugerfora, finder du mange mennesker, der siger, at dette ikke er muligt med VLookup, og at du er nødt til at bruge en kombination af indeks- og matchfunktioner for at gøre dette. Det er ikke helt sandt.

Du kan få VLookup til at arbejde på denne måde ved at indlejre en VÆLG-funktion i den. I dette tilfælde ser Excel-formlen sådan ud:

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

Hvad denne funktion betyder er, at du vil finde datoen 6/25/2013 i opslagslisten og derefter returnere den tilsvarende værdi fra kolonneindekset.

I dette tilfælde vil du bemærke, at kolonneindekset er "2", men som du kan se, er kolonnen i tabellen ovenfor faktisk 1, ikke?

Det er sandt, men hvad du laver med funktionen "VÆLG" er at manipulere de to felter.

Du tildeler referencenumre-indeksnumre til dataområder - tildeles datoer til indeksnummer 1 og opgaverne til indeksnummer 2.

Så når du skriver "2" i VLookup-funktionen, henviser du faktisk til indeks nummer 2 i CHOOSE-funktionen. Sej, ikke?

Så nu bruger VLookup kolonnen Dato og returnerer dataene fra kolonnen Opgave, selvom opgaven er til venstre.

Nu hvor du kender denne lille spids, så forestil dig hvad du ellers kan gøre!

Hvis du prøver at udføre andre avancerede dataopslag, skal du huske at tjekke Danns fulde artikel om at finde data i Excel ved hjælp af opslagsfunktioner.

2. Indlejrede formler til parse strenge

Her er en mere skør Excel-formel til dig.

Der kan være tilfælde, hvor du enten importerer data til Microsoft Excel fra en ekstern kilde, der består af en streng afgrænset data.

Når du har hentet dataene, ønsker du at analysere disse data i de enkelte komponenter. Her er et eksempel på navn, adresse og telefonnummeroplysninger afgrænset af “;” tegnet.

Her er, hvordan du kan analysere disse oplysninger ved hjælp af en Excel-formel (se om du mentalt kan følge med denne sindssyge):

For at udpakke det venstre felt (personens navn) i det første felt, ville du blot bruge en VENSTRE funktion i formlen.

 "=LEFT(A2, FIND(";", A2, 1)-1)" 

Sådan fungerer denne logik:

• Søger i tekststrengen fra A2
• Finder symbolet “;” for afgrænsning
• Trækker en for den korrekte placering af slutningen af ​​det strenge afsnit
• Grib den venstre tekst til det punkt

I dette tilfælde er den venstre tekst "Ryan". Mission fuldført.

3. Indlejret formel i Excel

Men hvad med de andre sektioner?

Der kan være lettere måder at gøre dette på, men da vi vil forsøge at skabe den mest sprækede nestede Excel-formel (der faktisk fungerer), bruger vi en unik tilgang.

For at udtrække dele til højre skal du indlejre flere HØJRE funktioner for at få fat i tekstsektionen indtil det første “;” -symbol og udføre VENSTRE funktion på det igen. Her er hvordan det ser ud til at udtrække gadenummerdelen af ​​adressen.

 "=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" 

Det ser skønt ud, men det er ikke svært at gå sammen. Alt hvad jeg gjorde var at tage denne funktion:

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

Og indsat det på alle steder i VENSTRE funktion ovenfor, hvor der er en "A2".

Dette udtrækker den anden sektion af strengen korrekt.

Hver efterfølgende sektion af strengen har brug for et andet reden oprettet. Så nu skal du bare tage den skøre "HØJRE" ligning, du havde oprettet til det sidste afsnit, og derefter overføre den til en ny HØJRE formel med den forrige RETTE formel indsat i sig selv, uanset hvor du ser "A2". Her er hvordan det ser ud.

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

Derefter tager du DEN formel og placerer den i den originale VENSTRE formel, uanset hvor der er en "A2".

Den endelige mind-bending formel ser sådan ud:

 "=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)" 

Denne formel udtrækker korrekt "Portland, ME 04076" ud af den originale streng.

Gentag ovennævnte proces igen for at udpakke det næste afsnit.

Dine Excel-formler kan blive rigtig luske, men alt hvad du laver er at skære og indsætte lange formler i sig selv, laver lange rede, der faktisk fungerer.

Ja, dette opfylder kravet til ”skør”. Men lad os være ærlige, der er en meget enklere måde at udføre den samme ting med én funktion.

Vælg blot kolonnen med de afgrænsede data, og vælg derefter Tekst til kolonner under menupunktet Data .

Dette åbner et vindue, hvor du kan opdele strengen med en hvilken som helst afgrænser.

I et par klik kan du gøre det samme som den skøre formel ovenfor ... men hvor er det sjove i det?

Bliv skør af Microsoft Excel-formler

Så der har du det. Ovenstående formler beviser, hvor over-the-top en person kan få, når man opretter Microsoft Excel-formler for at udføre visse opgaver.

Undertiden er disse Excel-formler faktisk ikke den nemmeste (eller bedste) måde at udføre ting på. De fleste programmerere vil fortælle dig at holde det enkelt, og det er lige så sandt med Excel-formler som noget andet. Du kan endda bruge indbyggede funktioner som Power Query.

Hvis du virkelig ønsker at blive seriøs med at bruge Excel, skal du læse gennem vores begyndervejledning til brug af Microsoft Excel Begyndervejledningen til Microsoft Excel Begyndervejledningen til Microsoft Excel Brug denne begyndervejledning til at starte din oplevelse med Microsoft Excel. De grundlæggende tips til regnearket her hjælper dig med at lære Excel at lære. Læs mere . Det har alt hvad du har brug for for at begynde at øge din produktivitet med Excel. Derefter skal du sørge for at læse vores væsentlige Excel-funktioner snyderark De væsentlige Microsoft Excel-formler og -funktioner Cheat Sheet De væsentlige Microsoft Excel-formler og -funktioner Cheat Sheet Download dette Excel-formel snyderark for at tage en genvej gennem verdens foretrukne regnearksprogram. Læs mere .

Udforsk mere om: Microsoft Excel, Microsoft Office 2016, regneark.