Gabriel Brooks
0 
2995
437
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.
Udgravning i Microsoft Excel
Vi har dækket en række forskellige måder, hvorpå du kan udnytte Excel bedre, såsom at bruge det til at oprette din egen kalenderskabelon eller bruge det som et projektstyringsværktøj.
Meget af kraften ligger bag Excel-formler og regler, 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 bare klikke på Hjem fanen, og klik på Betinget formatering ikon på værktøjslinjen.
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 Ikon sæt , 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, det fører dig til Betinget 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 element 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?
I eksemplet nedenfor, hvad nu 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), VÆLG (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, højre?
Det er sandt, men hvad du laver med “VÆLGE” funktion manipulerer de to felter.
Du tildeler reference “indeks” numre til dataintervigelser - tildeling af datoer til indeks nummer 1 og opgaverne til indeks nummer 2.
Så når du skriver “2” i VLookup-funktionen henviser du faktisk til indeks nummer 2 i CHOOSE-funktionen. Sej, ret?
Så nu bruger VLookup Dato kolonne og returnerer dataene fra Opgave kolonne, 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 “;” Karakter.
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 “;” afgrænser symbol
- 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 RETTE funktioner for at få fat i tekstsektionen indtil det første “;” symbol, og udfør VENSTRE funktion på det igen. Her er hvordan det ser ud til at udtrække gadenummerdelen af adressen.
"= LEFT ((HØJRE (A2, LEN (A2) -Find (";", A2))), FIND ( ";", (HØJRE (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 indsatte det på hvert sted 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 tager du bare det vanvittige “RET” ligning, du havde oprettet til det sidste afsnit, og send den derefter videre til en ny HÆJRE formel med den forrige RIGHT formel indsat i sig selv, uanset hvor du ser “A2”. Her er hvordan det ser ud.
(RIGHT ((HØJRE (A2, LEN (A2) -Find ( ";", A2))), LEN ((HØJRE (A2, LEN (A2) -Find ( ";", A2)))) - FIND ( "", (HØJRE (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 ((HØJRE (A2, LEN (A2) -Find (";", A2))), LEN ((HØJRE (A2, LEN (A2) -Find ( ";", A2))) ) -Find ( ";", (HØJRE (A2, LEN (A2) -Find ( ";", A2)))))), FIND ( ";", (RIGHT ((HØJRE (A2, LEN (A2) -Find ( ";", A2))), LEN ((HØJRE (A2, LEN (A2) -Find ( ";", A2)))) - FIND ( ";", (HØJRE (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 rent faktisk fungerer.
Ja, dette opfylder kravet til “helt vildt”. Men lad os være ærlige, der er en meget enklere måde at udføre den samme ting med én funktion.
Vælg bare kolonnen med de afgrænsede data og derefter under Data menupunkt, vælg Tekst til kolonner.
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.
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. . Det har alt hvad du har brug for for at begynde at øge din produktivitet med Excel. Derefter skal du sørge for at konsultere vores væsentlige Excel-funktioner snydeark De væsentlige Microsoft Excel-formler og -funktioner Snydeark De væsentlige Microsoft Excel-formler og -funktioner Cheat Sheet Download denne Excel-formler snyderark for at tage en genvej gennem verdens foretrukne regnearksprogram. .