3 Crazy Excel-formler som gjør fantastiske ting

• Produktivitet

Annonse

Excel-formler kan gjøre nesten hva som helst. I denne artikkelen lærer du hvor kraftige Microsoft Excel-formler og betinget formatering kan være, med tre nyttige eksempler.

Lås opp juksearket "Essential Excel Formulas" nå!

Dette vil registrere deg på vårt nyhetsbrev

Å grave i Microsoft Excel

Vi har dekket en rekke forskjellige måter å utnytte Excel bedre på, for eksempel å bruke det til å lage din egen kalendermal eller bruke den som et prosjektstyringsverktøy.

Mye av kraften ligger bak Excel-formlene og reglene som du kan skrive for å manipulere data og informasjon automatisk, uavhengig av hvilke data du setter inn i regnearket.

La oss grave i hvordan du kan bruke formler og andre verktøy for å utnytte Microsoft Excel bedre.

Betinget formatering med Excel-formler

Et av verktøyene som folk 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 formateringsdata i Microsoft Excel med betinget formatering.

Med bruk av Excel-formler, regler eller bare noen få enkle innstillinger, kan du transformere et regneark til et automatisk dashbord.

For å komme til betinget formatering, klikker du bare på Hjem- fanen og klikker på verktøylinjeikonet for betinget formatering .

Under betinget formatering er det mange alternativer. De fleste av disse er utenfor omfanget av denne bestemte artikkelen, men de fleste av dem handler om å utheve, fargelegge eller skyggelegge celler basert på dataene i den cellen.

Dette er sannsynligvis den vanligste bruken av betinget formatering - ting som å gjøre en celle rød ved å bruke mindre enn eller større enn formler. Lær mer om hvordan du bruker IF-setninger i Excel.

Et av de mindre brukte betingede formateringsverktøyene er alternativet Ikonoppsett, som tilbyr et flott sett med ikoner du kan bruke til å gjøre en Excel-datacelle om til et skjermikon for dashbordet.

Når du klikker på Administrer regler, tar den deg til betingelsesformateringsregler .

Avhengig av dataene du valgte før du valgte ikonsettet, vil du se cellen som er angitt i Manager-vinduet, med ikonet du nettopp valgte.

Når du klikker på Rediger regel, vil du se dialogen der magien skjer.

Det er her du kan lage den logiske formelen og ligningene som viser det instrumentpanelikonet du ønsker.

Dette eksempelpanelet viser tidsbruk på forskjellige oppgaver kontra budsjettert tid. Hvis du går over halve budsjettet, vises et gult lys. Hvis du er over budsjettet, blir det rødt.

Som du ser, viser dette instrumentbordet at tidsbudsjettering ikke er vellykket.

Nesten halvparten av tiden brukes mye over de budsjetterte beløpene.

På tide å fokusere på nytt og styre tiden din bedre!

1. Bruke VLookup-funksjonen

Hvis du ønsker å bruke mer avanserte Microsoft Excel-funksjoner, er her en annen for deg.

Du er sannsynligvis kjent med VLookup-funksjonen, som lar deg søke gjennom en liste etter et bestemt element i en kolonne, og returnere dataene fra en annen kolonne i samme rad som det elementet.

Dessverre krever funksjonen at elementet du søker etter i listen er i venstre kolonne, og dataene du leter etter er til høyre, men hva om de byttes?

Hva i eksemplet nedenfor, hvis jeg vil finne oppgaven jeg utførte den 25/25/2018 fra følgende data?

I dette tilfellet søker du gjennom verdiene til høyre, og du vil returnere den tilsvarende verdien til venstre - motsatt av hvordan VLookup normalt fungerer.

Hvis du leser Microsoft Excel pro-brukerfora, vil du finne mange som sier at dette ikke er mulig med VLookup, og at du må bruke en kombinasjon av indeks- og matchfunksjoner for å gjøre dette. Det er ikke helt sant.

Du kan få VLookup til å jobbe på denne måten ved å hekke en VELG-funksjon i den. I dette tilfellet ser Excel-formelen slik ut:

 "=VLOOKUP(DATE(2018, 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 er kolonnen i tabellen over faktisk 1, ikke sant?

Det er sant, men det du gjør med "VELG" -funksjonen er å manipulere de to feltene.

Du tilordner referanse-indeksnumre til dataområder - tilordner datoene til indeksnummer 1 og oppgavene til indeksnummer 2.

Så når du skriver “2” i VLookup-funksjonen, henviser du faktisk til Indeks nummer 2 i VELG-funksjonen. Kult, ikke sant?

Nå bruker VLookup Date- kolonnen og returnerer dataene fra oppgavekolonnen, selv om oppgaven er til venstre.

Nå som du kjenner til denne lille godbit, bare forestill deg hva annet du kan gjøre!

Hvis du prøver å gjøre andre avanserte dataoppslag, må du huske å sjekke Danns fulle artikkel om å finne data i Excel ved hjelp av oppslagsfunksjoner.

2. Nested Formula to Parse Strings

Her er en mer gal Excel-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 streng avgrenset data.

Når du har hentet inn dataene, vil du analysere dataene inn i de enkelte komponentene. Her er et eksempel på navn, adresse og telefonnummerinformasjon avgrenset av tegnet “;”.

Slik kan du analysere denne informasjonen ved hjelp av en Excel-formel (se om du mentalt kan følge med denne galskapen):

For det første feltet, for å trekke ut det venstre elementet (personens navn), vil du ganske enkelt bruke en VENSTRE funksjon i formelen.

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

Slik fungerer denne logikken:

• Søker i tekststrengen fra A2
• Finner symbolet ";"
• Trekker fra en for riktig plassering av slutten av den strenge delen
• Tar tak i den venstre teksten til det punktet

I dette tilfellet er den venstre teksten “Ryan”. Oppdrag utført.

3. Nested Formula i Excel

Men hva med de andre seksjonene?

Det kan være enklere måter å gjøre dette på, men siden vi vil prøve å lage den sprøeste Nested Excel-formelen som mulig (som faktisk fungerer), kommer vi til å bruke en unik tilnærming.

For å trekke ut delene til høyre, må du hekke flere HØYRE funksjoner for å hente tekstdelen opp til det første “;” -symbolet og utføre VENSTRE-funksjonen på den igjen. Slik ser det ut for å trekke ut gatenummerdelen av adressen.

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

Det ser gale ut, men det er ikke vanskelig å stykke sammen. Alt jeg gjorde var å ta denne funksjonen:

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

Og satte den inn på alle steder i VENSTRE-funksjonen over der det er en "A2".

Dette trekker ut den andre delen av strengen riktig.

Hver påfølgende del av strengen trenger et nytt reir som er opprettet. Så nå tar du bare den vanvittige "HØYRE" ligningen du hadde laget for den siste delen, og deretter overfører den til en ny HØYRE formel med den forrige HØYRE formelen limt inn i seg selv hvor du ser "A2". Slik ser det ut.

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

Så tar du DEN formelen, og plasserer den i den originale VENSTRE formelen uansett hvor det er en "A2".

Den endelige 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)" 

Denne formelen trekker riktig ut “Portland, ME 04076” ut av den opprinnelige strengen.

For å trekke ut neste avsnitt, gjenta prosessen ovenfor på nytt.

Excel-formlene dine kan bli skikkelig sløyfe, men alt du gjør er å kutte og lime inn lange formler i seg selv, lage lange reir som faktisk fungerer.

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.

Bare velg kolonnen med de avgrensede dataene, og velg deretter Tekst til kolonner under Data- menyelementet.

Dette vil få opp et vindu der du kan dele strengen etter hvilken som helst avgrenser.

Med et par klikk kan du gjøre det samme som den vanvittige formelen over ... men hvor er moroa i det?

Blir gal av Microsoft Excel-formler

Så der har du det. Ovennevnte formler viser bare hvor over-the-top en person kan få når du oppretter Microsoft Excel-formler for å utføre visse oppgaver.

Noen ganger er ikke Excel-formlene faktisk den enkleste (eller beste) måten å oppnå ting på. De fleste programmerere vil fortelle deg å holde det enkelt, og det er like sant med Excel-formler som noe annet. Du kan til og med bruke innebygde funksjoner som Power Query.

Hvis du virkelig ønsker å bli seriøs med å bruke Excel, vil du lese gjennom vår nybegynnerveiledning for bruk av Microsoft Excel Begynnerveiledningen til Microsoft Excel Begynnerveiledningen til Microsoft Excel Bruk denne nybegynnerguiden for å starte opplevelsen din med Microsoft Excel. De grunnleggende regnearkstipsene her vil hjelpe deg å begynne å lære Excel på egen hånd. Les mer . Den har alt du trenger for å øke produktiviteten din med Excel. Deretter må du huske å konsultere de essensielle Excel-funksjonene jukseark. De essensielle Microsoft Excel-formlene og -funksjonene Jukseark De essensielle Microsoft Excel-formlene og funksjonene Cheat Sheet Last ned dette Excel-formlene jukseark for å ta en snarvei gjennom verdens favoritt regnearksprogram. Les mer .

Utforsk mer om: Microsoft Excel, Microsoft Office 2016, Regneark.