Opdateret af Ryan Dube i oktober 2017.
Jeg har altid troet, at Microsoft Excel er et af de mest kraftfulde softwareværktøjer derude. Det er ikke kun det faktum, at det er en kraftfuld regneark ansøgning. Microsoft Excel selv har en så imponerende samling af indbyggede værktøjer og funktioner. I denne artikel planlægger jeg at vise dig, hvor magtfulde formler og betinget formatering kan være, med tre nyttige eksempler.
Vi har dækket en række forskellige måder til bedre udnyttelse af Excel, som f.eks. At bruge den til at oprette din egen kalenderskabelon. Sådan laver du en kalenderskabelon i Excel Sådan laver du en kalenderskabelon i Excel Du skal altid bruge en kalender. Vi viser dig, hvordan du opretter din egen brugerdefinerede skabelon gratis med Microsoft Excel. Læs mere, brug det som et målstyringsværktøj 10 + Nyttige Excel-skabeloner til projektstyring og -sporing 10 + Nyttige Excel-skabeloner til projektstyring og sporing Skabeloner er at projektledere hvad knive er til professionelle kokke: uundværlige. Vi viser dig, hvordan du kan replikere succesfulde projekter ved at bruge færdige skabeloner i Microsoft Excel og videre. Læs mere, og andre unikke måder, du kan bruge den til at styre dit liv. Bare læs disse artikler, og du kan se, hvor kraftig Microsoft Excel kan være.
Meget af magten ligger virkelig bag de formler og regler, som du kan skrive for at manipulere data og information automatisk, uanset hvilke data du indsætter i regnearket.
Gravering i Microsoft Excel
Med de rigtige oplysninger kan du oprette et system, der automatisk beregner og genberegner resultater og rapporter fra de pågældende rådata. I dag vil jeg gerne grave lidt længere under overfladen og vise dig, hvordan du kan bruge nogle af de underliggende formler og andre værktøjer til bedre udnyttelse af Microsoft Excel.
Cool betinget formatering med formler
Et af de værktøjer, som jeg tror, at folk bare ikke bruger ofte nok, er betinget formatering. Hvis du leder efter mere avancerede oplysninger om betinget formatering i Microsoft Excel, skal du sørge for at tjekke Sandy's artikel om formatering af data i Microsoft Excel med betinget formatering Automatisk formatering af data i Excel-regneark med betinget formatering Formater automatisk data i Excel-regneark med betinget formatering I Excels betingede formateringsfunktion kan du formatere individuelle celler i et Excel-regneark baseret på deres værdi. Vi viser dig, hvordan du bruger dette til forskellige daglige opgaver. Læs mere .
Med brug af formler, regler eller blot nogle få rigtig enkle indstillinger kan du omdanne et regneark til et automatisk dashboard, der viser dig meget om oplysningerne i regnearket ved et øjeblik.
For at komme til betinget formatering klikker du bare på fanen Startside og klikker på ikonet "Betinget formatering".
Under betinget formatering er der en masse valgmuligheder. De fleste af disse er uden for denne artikels anvendelsesområde, men de fleste handler om at fremhæve, farve eller skygge celler baseret på dataene i den pågældende celle. Dette er nok den mest almindelige brug af betinget formatering - gør ting som at dreje en cellerød ved hjælp af logiske mindre end eller større end formler. Brad Jones beskriver hvordan man bruger en masse af dem i sin artikel om at oprette et Excel Dashboard Visualisere dine data og gøre dit regneark brugervenligt med et Excel Dashboard Visualiser dine data og gør dit regneark brugervenligt med et Excel Dashboard Nogle gange er et simpelt regneark format er ikke engagerende nok til at gøre dine data tilgængelige. Et dashboard giver dig mulighed for at præsentere dine vigtigste data i et let at fordøje format. Læs mere - værd at tjekke, om det er dit mål.
Et af de mindre anvendte betingede formateringsværktøjer er ikonopsætningsindstillingen, som tilbyder et stort sæt ikoner, du kan bruge til at slå en Excel-datacelle i et dashboard-displayikon.
Jeg opdagede dette, når jeg opgraderede Microsoft Office 13+ Årsager, du skal opgradere til Microsoft Office 2016 13+ Grunde, du skal opgradere til Microsoft Office 2016 Microsoft Office 2016 er her, og det er på tide, at du træffer en beslutning. Produktivitetsspørgsmålet er - skal du opgradere? Vi giver dig de nye funktioner og de friskere grunde til at hjælpe dig ... Læs mere og brugt betinget formatering i Microsoft Excel for første gang.
Jeg tjekkede ikonerne og så disse kølige LED-indikatorlamper, som jeg kun havde set før i nogle af de fabriksautomatiseringsdisplayer, jeg har programmeret tidligere. Når du klikker på "Administrer regler", tager det dig til betingelsesformateringsregleringsadministratoren. Afhængigt af de data, du valgte, før du valgte ikonindstillingen, vil du se den celle, der er angivet i administrationsvinduet, med ikonindstillingen du lige har valgt.
Når du klikker på "Rediger regel ...", ser du dialogen, hvor magien sker. Her kan du oprette den logiske formel og ligninger, der viser det dashboardikon, du vil have. I mit eksempel overvåger jeg tid på forskellige opgaver i forhold til min budgetterede tid. Hvis jeg har gået over halvdelen af mit budget, vil jeg have et gult lys til visning, og hvis jeg er over budget, vil jeg have den rød.
Som du kan se, budgetterer jeg ikke rigtig meget min tid. Næsten halvdelen af min tid er brugt langt over, hvad jeg har budgetteret.
Tid til at refokusere og bedre styre min tid Brug 80/20 Time Management Rule til at prioritere dine opgaver Brug 80/20 Time Management Rule til at prioritere dine opgaver Hvordan maksimerer du din tid? Hvis du allerede prioriterer, delegerer og stadig kæmper for at få alt gjort, bør du prøve 80/20-reglen, også kendt som Pareto-princippet. Læs mere !
Se efter emner med VLookup-funktionen
Okay, det er måske ikke nok nok til dig. Måske er du ikke så begejstret for simple logiske formler, der tænder og slukker lys. Hvis du kan lide at bruge mere avancerede Microsoft Excel-funktioner, er jeg gået en anden til dig.
Du er formentlig bekendt med VLookup-funktionen, som lader dig søge gennem en liste for et bestemt emne i en kolonne og returnere dataene fra en anden kolonne i samme række som den pågældende post. Desværre kræver funktionen, at varen du søger efter i listen er i den venstre kolonne, og de data, du leder efter, er til højre, men hvad hvis de skiftes?
I eksemplet nedenfor, hvad hvis jeg vil finde den opgave, jeg udførte den 6/25/2013 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 på. Søg Excel-regneark hurtigere: Udskift VLOOKUP med INDEX og MATCH Søg Excel-regneark hurtigere: Udskift VLOOKUP Med INDEX og MATCH Brug stadig VLOOKUP til at søge efter oplysninger i dit regneark? Sådan kan INDEX og MATCH tilbyde en bedre løsning. Læs mere . Hvis du læser Microsoft Excel pro-brugerfora, finder du mange mennesker, der siger, at dette ikke er muligt med VLookup, og at du skal bruge en kombination af Indeks og Match-funktioner til at gøre dette. Det er ikke helt sandt.
Du kan få VLookup til at arbejde på denne måde ved at neste en VÆLG funktion i den. I dette tilfælde ser formlen sådan ud:
"=VLOOKUP(DATE(2013, 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 returner derefter den tilsvarende værdi fra kolonneindekset. I dette tilfælde vil du bemærke, at kolonneindekset er "2", men som du kan se kolonnen i tabellen ovenfor er faktisk 1, ikke?
Det er sandt, men hvad du laver med funktionen "VÆLG" manipulerer de to felter. Du tildeler referencer "indeks" tal til dataområder - tildeling af datoer til indeks nummer 1 og opgaverne til indeks nummer to. Så når du skriver "2" i VLookup-funktionen, refererer du faktisk til Indeksnummer 2 i VÆLG-funktionen. Skør, eh?
Så nu bruger VLookup kolonnen Dato og returnerer data fra kolonnen Opgave, selvom Opgave er til venstre. Nu da du kender denne lille skat, kan du bare forestille dig hvad du kan gøre!
Hvis du forsøger at lave andre avancerede dataopsøgningsopgaver, skal du sørge for at tjekke Danns fulde artikel om at finde data i Excel ved hjælp af opslagsfunktioner. Find noget i dit Excel-regneark med opslagsfunktioner Find noget i dit Excel-regneark med opslagsfunktioner i en kæmpe Excel-regneark, CTRL + F vil kun få dig hidtil. Vær klog og lad formler gøre det hårde arbejde. Opslag formler spare tid og er nemme at anvende. Læs mere .
Insane Nested Formulas til Parse Strings
Som du kan se, forsøger jeg at blive lidt mere crazier som vi går, fordi jeg ved, at der er nogle af jer derude, der tænker, "... det er jo ikke vildt !!" Jeg ved, dine standarder er høje. Jeg forsøger at leve op til dem. Her er en mere skør 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 række afgrænsede data. Når du har indlæst dataene, vil du analysere disse data i de enkelte komponenter. Her er et eksempel på navn, adresse og telefonnummeroplysninger afgrænset af ";" tegn.
Sådan kan du analysere disse oplysninger ved hjælp af en Excel-formel (se om du mentalt kan følge med denne vanvittighed):
For det første felt, for at udtrække det venstre element (personens navn), ville du blot bruge en LEFT-funktion i formlen.
"=LEFT(A2, FIND(";", A2, 1)-1)" Dette søger tekststrengen fra A2, finder symbolet ";" afgrensninger, trækker en for den rigtige placering af slutningen af stringsektionen, og griber derefter den venstre tekst til det punkt. I dette tilfælde er det "Ryan". Mission fuldført.
Nestende Excel Formler
Men hvad med de andre sektioner? Nå, for at udtrække delene til højre, skal du nest flere RIGHT-funktioner for at få fat i sektionen af tekst indtil det første "" "symbol og udføre VENSTRE-funktionen på den igen. Her er hvad der ligner at udvinde gadenummerdelen af adressen.
"=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" Det ser skørt ud, men det er ikke svært at samle sammen. Alt jeg gjorde er tog denne funktion:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" Og indsæt den i hvert sted i den VENSTRE funktion ovenfor, hvor der er en "A2". Dette trækker den anden sektion af strengen korrekt ud.
Hver efterfølgende sektion af tråden har brug for en anden rede skabt. Så nu tager du bare den skøre "RIGHT" ligning, du havde lavet for sidste afsnit, og send det derefter til en ny RIGHT-formel med den forrige RIGHT-formel indsat i sig selv, hvor du ser "A2". Her ser det ud.
"(RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))))))" Så tager du denne formel og placerer den i den originale LEFT-formel, 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æk korrekt "Portland, ME 04076" ud af den oprindelige streng.
For at uddrage næste afsnit gentages ovenstående proces igen. Dine formler kan blive virkelig loopy, men alt hvad du gør er at skære og indsætte lange formler i sig selv, lave lange rede, der virker rigtig godt!
Ja, dette opfylder kravet om "skør", men lad os være ærlige ... der er en meget enklere måde at opnå det samme med en funktion. Vælg blot kolonnen med de afgrænsede data, og vælg derefter Tekst til kolonner under Data- elementet. Dette vil medbringe et vindue, hvor du kan opdele strengen med en hvilken som helst afgrænser, du vil have.
Med et par klik kan du gøre det samme som den ovenstående formel ... men hvad er det sjovt i det?
At blive skør med Microsoft Excel
Så der har du det. Ovennævnte formler viser kun, hvordan over-the-top en person kan få, når du opretter Microsoft Excel-formler for at udføre visse opgaver. Nogle gange er disse formler ikke egentlig den nemmeste (eller bedste) måde at opnå ting på. De fleste programmører vil fortælle dig at holde det enkelt, og det er lige så sandt med Excel-formler 16 Excel-formler, der vil hjælpe dig med at løse virkelige livsproblemer 16 Excel-formler, der hjælper dig med at løse virkelige problemer. Det rigtige værktøj er halvdelen af arbejdet. Excel kan løse beregninger og behandle data hurtigere, end du kan finde din regnemaskine. Vi viser dig centrale Excel-formler og demonstrere, hvordan du bruger dem. Læs mere som noget andet.
Var disse formler og teknikker vild nok? Har du nogle fantastiske formelle tricks, der udgør din Microsoft Excel superbruger værktøjskasse? Del dine input og feedback i kommentarfeltet nedenfor!
Billedkredit: Kues / Depositobilleder