Oppdatert av Ryan Dube i oktober 2017.
Jeg har alltid trodd at Microsoft Excel er et av de kraftigste programvarene der ute. Det er ikke bare det faktum at det er et kraftig regnearkprogram. Microsoft Excel har en så imponerende samling av innebygde verktøy og funksjoner. I denne artikkelen planlegger jeg å vise deg hvor kraftige formler og betinget formatering kan være, med tre nyttige eksempler.
Vi har dekket en rekke forskjellige måter å gjøre bedre bruk av Excel, for eksempel å bruke den til å lage din egen kalendermal. Hvordan lage en kalendermal i Excel. Hvordan lage en kalendermal i Excel. Du trenger alltid en kalender. Vi viser deg hvordan du lager din egen tilpassede mal gratis med Microsoft Excel. Les mer, bruk det som et målstyringsverktøy 10+ Nyttige Excel-maler for prosjektledelse og sporing 10 + Nyttige Excel-maler for prosjektledelse og sporing Maler er å prosjektledere hvilke kniver som er profesjonelle kokker: uunnværlig. Vi viser deg hvordan du kan kopiere vellykkede prosjekter ved å bruke ferdige maler i Microsoft Excel og utover. Les mer, og andre unike måter du kan bruke den til å styre livet ditt. Bare les de artiklene, og du får se hvor kraftig Microsoft Excel kan være.
Mye av kraften ligger egentlig bak formler og regler som du kan skrive for å manipulere data og informasjon automatisk, uavhengig av hvilke data du legger inn i regnearket.
Graving til Microsoft Excel
Med riktig informasjon kan du opprette et system som automatisk beregner og beregner resultater og rapporter fra disse rådataene. I dag vil jeg gjerne grave litt lenger under overflaten og vise deg hvordan du kan bruke noen av de underliggende formler og andre verktøy for å gjøre bedre bruk av Microsoft Excel.
Cool betinget formatering med formler
Et av verktøyene som jeg tror folk bare ikke bruker ofte nok, er betinget formatering. Hvis du leter etter mer avansert informasjon om betinget formatering i Microsoft Excel, må du sjekke ut Sandys artikkel om formatering av data i Microsoft Excel med betinget formatering. Automatisk formatering av data i Excel-regneark med betinget formatering. Formater automatisk data i Excel-regneark med betinget formatering Excels betingede formateringsfunksjon lar deg formatere individuelle celler i et Excel-regneark basert på deres verdi. Vi viser deg hvordan du bruker dette til ulike daglige oppgaver. Les mer .
Med bruk av formler, regler eller bare noen få veldig enkle innstillinger, kan du forvandle et regneark til et automatisert dashbord som viser mye om informasjonen i regnearket ved et øyeblikk.
For å komme til betinget formatering klikker du bare på Hjem-fanen, og klikker på ikonet "Betinget formatering".
Under betinget formatering er det en rekke alternativer. De fleste av disse er utenfor omfanget av denne artikkelen, men de fleste handler om å markere, fargelegge eller skygge celler basert på dataene i den cellen. Dette er trolig den vanligste bruken av betinget formatering - gjør ting som å snu en cellerød ved å bruke logiske mindre enn eller større enn formler. Brad Jones beskriver hvordan du bruker mange av disse i sin artikkel om å lage et Excel Dashboard Visualisere dataene dine og lage regnearkene dine Brukervennlige med et Excel Dashboard Visualisere dataene dine og lag Regnearkene dine Brukervennlig med et Excel Dashboard Noen ganger kan et enkelt regnearkformat er ikke engasjerende nok til å gjøre dataene dine tilgjengelige. Et dashbord lar deg presentere dine viktigste data i et lett å fordøye format. Les mer - vel verdt å sjekke om det er målet ditt.
Et av de mindre brukte betingede formateringsverktøyene er ikonsettene, som tilbyr et stort sett med ikoner du kan bruke til å slå en Excel-datacelle i et dashboard-displayikon.
Jeg oppdaget dette når jeg oppgraderte Microsoft Office 13+ grunner du bør oppgradere til Microsoft Office 2016 13+ grunner du bør oppgradere til Microsoft Office 2016 Microsoft Office 2016 er her, og det er på tide for deg å ta en beslutning. Produktivitetsspørsmålet er - skal du oppgradere? Vi gir deg de nye funksjonene og de ferskere grunnene til å hjelpe deg ... Les mer og brukt betinget formatering i Microsoft Excel for første gang.
Jeg sjekket ut ikonene og så disse kule LED-indikatorlampene som jeg bare hadde sett før i noen av fabrikkautomatiseringsdisplayene jeg har programmert tidligere. Når du klikker på "Administrer regler", tar det deg til betingelsesformateringsreguleringsbehandling. Avhengig av dataene du valgte før du valgte ikonet, ser du cellen angitt i Manager-vinduet, med ikonet du valgte.
Når du klikker på "Rediger regel ...", ser du dialogen der magien skjer. Her kan du opprette den logiske formelen og ligninger som vil vise instrumentbrettikonet du vil ha. I mitt eksempel overvåker jeg tiden på ulike oppgaver i forhold til min budsjetterte tid. Hvis jeg har gått over halvparten av budsjettet mitt, vil jeg ha et gul lys som skal vises, og hvis jeg er over budsjett, vil jeg at den skal bli rød.
Som du kan se, budger jeg ikke egentlig min tid for godt. Nesten halvparten av tiden min er brukt langt over det jeg har budsjettert.
Tid til å refokusere og bedre håndtere tiden min Bruk 80/20 Time Management Rule til å prioritere oppgavene dine Bruk 80/20 Time Management Rule til å prioritere oppgavene dine Hvordan maksimerer du tiden din? Hvis du allerede prioriterer, delegerer og fremdeles sliter med å få alt gjort, bør du prøve 80/20-regelen, også kjent som Pareto-prinsippet. Les mer !
Se opp elementer med VLookup-funksjonen
Ok, det er kanskje ikke gal nok for deg. Kanskje du ikke er så begeistret for enkle logiske formler som slår lysene av og på. Hvis du liker å bruke mer avanserte Microsoft Excel-funksjoner, har jeg gått en annen for deg.
Du er sikkert kjent med VLookup-funksjonen, som lar deg søke gjennom en liste for et bestemt element i en kolonne, og returnere dataene fra en annen kolonne i samme rad som det aktuelle elementet. Dessverre krever funksjonen at elementet du søker etter i listen er i den venstre kolonnen, og dataene du leter etter er til høyre, men hva om de byttes?
I eksemplet nedenfor, hva om jeg vil finne oppgaven som jeg utførte den 6/25/2013 fra følgende data?
I dette tilfellet søker du gjennom verdier til høyre, og du vil returnere den tilsvarende verdien til venstre - motsatt måten VLookup normalt fungerer på. Søk Excel Spreadsheets Faster: Erstatt VLOOKUP Med INDEX og MATCH Søk Excel Spreadsheets Faster: Erstatt VLOOKUP Med INDEX og MATCH Still bruker du VLOOKUP for å søke etter informasjon i regnearket ditt? Slik kan INDEX og MATCH gi en bedre løsning. Les mer . Hvis du leser Microsoft Excel pro-brukerfora, finner du mange mennesker som sier at dette ikke er mulig med VLookup, og at du må bruke en kombinasjon av Indeks og Match-funksjoner for å gjøre dette. Det er ikke helt sant.
Du kan få VLookup til å jobbe på denne måten ved å nesting en VELG-funksjon i den. I dette tilfellet vil formelen se slik ut:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Hva denne funksjonen betyr er at du vil finne datoen 6/25/2013 i oppslagslisten, og deretter returnere den tilsvarende verdien fra kolonneindeksen. I dette tilfellet vil du legge merke til at kolonneindeksen er "2", men som du kan se kolonnen i tabellen ovenfor er faktisk 1, ikke sant?
Det er sant, men det du gjør med "VELG" -funksjonen, manipulerer de to feltene. Du tilordner referanseindeksnumre til rekkevidde av data - tilordne datoene til indeks nummer 1 og oppgavene til indeks nummer to. Så når du skriver "2" i VLookup-funksjonen, refererer du faktisk til Indeks nummer 2 i VELG-funksjonen. Crazy, eh?
Så nå bruker VLookup kolonnen Dato og returnerer dataene fra kolonnen Oppgave, selv om Oppgave er til venstre. Nå som du vet denne lille tingen, tenk bare hva du kan gjøre!
Hvis du prøver å gjøre andre avanserte dataoppslag, må du sjekke ut Danns fullstendige artikkel om å finne data i Excel ved hjelp av oppslagsfunksjoner. Finn alt i Excel-regnearket med oppslagsfunksjoner. Finn noe i Excel-regnearket med oppslagsfunksjoner i en gigantisk Excel-regneark, CTRL + F vil bare få deg så langt. Vær smart og la formler gjøre det harde arbeidet. Oppslagsmedlemmer sparer tid og er enkle å søke på. Les mer .
Insane Nested Formler å Parse Strings
Som du kan se, prøver jeg å bli litt crazier når vi går, fordi jeg vet at det er noen av dere der ute og tenker, "Vel, det er ikke gal i det hele tatt!" Jeg vet at dine standarder er høye. Jeg prøver å leve opp til dem. Her er en mer gal formel for deg.
Det kan være tilfeller hvor du enten importerer data til Microsoft Excel fra en ekstern kilde som består av en rekke begrensede data. Når du har tatt inn dataene, vil du analysere dataene i de enkelte komponentene. Her er et eksempel på navn, adresse og telefonnummer informasjon avgrenset av tegnet ";".
Slik kan du analysere denne informasjonen ved hjelp av en Excel-formel (se om du kan følgelig følge med denne galskapen):
For det første feltet, for å trekke ut venstre gjenstand (personens navn), ville du bare bruke en VENSTRE-funksjon i formelen.
"=LEFT(A2, FIND(";", A2, 1)-1)" Dette søker tekststrengen fra A2, finner symbolet ";" avgrensninger, trekker en for riktig plassering av slutten av strengeseksjonen, og tar deretter den venstre teksten til det punktet. I dette tilfellet er det "Ryan". Oppdrag utført.
Nestende Excel-formler
Men hva med de andre seksjonene? Vel, for å trekke ut delene til høyre, må du hekke flere HØYRE funksjoner for å få tak i delen av teksten til det første "" "-symbolet, og utfør LEFT-funksjonen på den igjen. Her er det som ser ut til å trekke ut gateadressens del av adressen.
"=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" Det ser galt ut, men det er ikke vanskelig å stykke sammen. Alt jeg gjorde er tok denne funksjonen:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" Og sett den inn på alle steder i VENSTRE-funksjonen over der det er en "A2". Dette trekker riktig ut den andre delen av strengen.
Hver etterfølgende del av strengen trenger en annen rede opprettet. Så nå tar du bare den galne "RIGHT" ligningen du hadde opprettet for den siste delen, og så sender du den inn i en ny RIGHT-formel med den forrige RIGHT-formelen limt inn i seg selv hvor du ser "A2". Her ser det ut som det ser ut.
"(RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))))))" Deretter tar du denne formelen, og legger den inn i den opprinnelige LEFT-formelen hvor det er en "A2". Den siste tankebøyende formelen ser slik ut:
"=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 formelen trekker ut "Portland, ME 04076" ut av den opprinnelige strengen.
Hvis du vil pakke ut neste avsnitt, gjentar du fremgangsmåten ovenfor igjen. Formlene dine kan bli veldig loopy, men alt du gjør er å kutte og lime inn lange formler i seg selv, lage lange reir som fungerer veldig bra!
Ja, dette oppfyller kravet til "gal", men la oss være ærlige ... det er en mye enklere måte å oppnå det samme med en funksjon. Velg bare kolonnen med de avgrensede dataene, og velg deretter Tekst til kolonner under Data- menyelementet. Dette vil hente opp et vindu hvor du kan dele strengen med en hvilken som helst avgrenser du vil ha.
Med et par klikk kan du gjøre det samme som formelen ovenfor ... men hva er det gøy i det?
Bli sprø med Microsoft Excel
Så der har du det. Ovennevnte formler viser bare hvordan over-the-top en person kan få når du lager Microsoft Excel-formler for å utføre visse oppgaver. Noen ganger er disse formlene egentlig ikke den enkleste (eller beste) måten å oppnå ting på. De fleste programmører vil fortelle deg å holde det enkelt, og det er like sant med Excel-formler 16 Excel-formler som vil hjelpe deg med å løse virkelige livsproblemer 16 Excel-formler som vil hjelpe deg med å løse virkelige problemer. Det riktige verktøyet er halvparten av arbeidet. Excel kan løse beregninger og behandle data raskere enn du kan finne kalkulatoren din. Vi viser deg viktige Excel-formler og demonstrerer hvordan du bruker dem. Les mer som noe annet.
Var disse formlene og teknikkene villige nok? Har du noen fantastiske formelle triks som din Microsoft Excel superbruker-verktøykasse? Del innspill og tilbakemelding i kommentarfeltet nedenfor!
Bilde Kreditt: Kues / Depositphotos