3 Crazy Excel Formler som gjør fantastiske ting

Microsoft Excel er et av de kraftigste regnearkverktøyene, med en imponerende samling av innebygde verktøy og funksjoner. I denne artikkelen lærer du hvor kraftige Excel-formler og betinget formatering kan være, med tre nyttige eksempler.

Graving til Microsoft Excel

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

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

La oss grave inn i hvordan du kan bruke formler og andre verktøy for å gjøre bedre bruk av Microsoft Excel.

Betinget formatering med 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 formatering av data i Microsoft Excel med betinget formatering.

Med bruk av Excel-formler, regler eller bare noen få veldig enkle innstillinger, kan du forvandle et regneark til et automatisert dashboard.

For å komme til betinget formatering klikker du bare på Hjem kategorien, og klikk på Betinget formatering verktøylinjeikonet.

Under betinget formatering er det mange 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 - ting som å snu en cellerød med 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 Ikoninnstillinger alternativet, som tilbyr et stort sett med ikoner du kan bruke til å slå en Excel-datacelle i et dashbord-displayikon.

Når du klikker på Administrer regler, det tar deg til Forholdsregler for formatering av regler.

Avhengig av dataene du valgte før du valgte ikonet, ser du cellen angitt i Manager-vinduet, med ikonet du valgte bare.

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

Her kan du opprette den logiske formelen og ligninger som vil vise instrumentbrettikonet du vil ha.

Dette eksempelpanelet vil vise tid brukt på forskjellige oppgaver i motsetning til budsjettet tid. Hvis du går over halvparten av budsjettet, vises et gult lys. Hvis du er helt over budsjettet, blir den rød.

Som du kan se, viser dette dashbordet at tidsbudsjettering ikke lykkes.

Nesten halvparten av tiden blir brukt langt over de budsjetterte beløpene.

Tid til å refokusere og bedre styre tiden din!

1. Bruke VLookup-funksjonen

Hvis du vil bruke mer avanserte Microsoft Excel-funksjoner, så er det 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 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 blir byttet?

I eksemplet nedenfor, hva om jeg vil finne oppgaven som jeg utførte den 6/25/2018 fra følgende data?

I dette tilfellet søker du gjennom verdier til høyre, og du vil returnere tilsvarende verdi til venstre - motsatt måten VLookup vanligvis fungerer på..

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 Excel-formelen se slik ut:

"= FINN.RAD (DATE (2018,6,25), VELGER (1,2, E2: E8, A2: A8), 2,0)"

Hva denne funksjonen betyr er at du vil finne datoen 6/25/2013 i oppslagslisten, og returner deretter 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, høyre?

Det er sant, men hva gjør du med “VELGE” funksjonen manipulerer de to feltene.

Du tilordner referanse “index” tall til dataområder - tilordne datoene til indeks nummer 1 og oppgavene til indeks nummer 2.

Så når du skriver inn “2” I VLookup-funksjonen refererer du faktisk til Indeksnummer 2 i VELG-funksjonen. Kul, høyre?

Så, nå bruker VLookup Dato kolonne og returnerer dataene fra Oppgave kolonne, selv om oppgaven er til venstre.

Nå som du vet denne lille tingen, tenk bare på hva du ellers 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.

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 avgrensede data.

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

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 side (personens navn), ville du bare bruke en VENSTRE-funksjon i formelen.

"= VENSTRE (A2, FINN (", "A2,1) -1)"

Slik fungerer denne logikken:

• Søker tekststrengen fra A2
• Finner “;” avgrensingssymbol
• Subtraherer en for riktig plassering av enden av strengeseksjonen
• Griper den venstre teksten til det punktet

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

3. Nestet formel i Excel

Men hva med de andre seksjonene?

Det kan være enklere måter å gjøre dette på, men siden vi vil forsøke å lage den skarpeste Nested Excel-formelen mulig (som faktisk fungerer), skal vi bruke en unik tilnærming.

For å trekke ut delene til høyre, må du nest flere RIGHT-funksjoner for å få tak i seksjonen av tekst frem til det første “;” symbol, og utfør LEFT-funksjonen på den igjen. Her er det som ser ut til å trekke ut gateadressens del av adressen.

"= VENSTRE ((HØYRE (A2, LEN (A2)-Finn (",", A2))), FINN ( "", (HØYRE (A2, LEN (A2)-Finn ( "", A2)) ), 1) -1)"

Det ser galt ut, men det er ikke vanskelig å stykke sammen. Alt jeg gjorde er tok denne funksjonen:

HØYRE (A2, LEN (A2)-Finn ( "", 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 “IKKE SANT” ligning du hadde opprettet for den siste delen, og deretter sende 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.

(HØYRE ((HØYRE (A2, LEN (A2)-Finn ( "", A2))), LEN ((HØYRE (A2, LEN (A2)-Finn ( "", A2)))) - FINN ( ";", (HØYRE (A2, LEN (A2)-Finn ( "", 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:

"= VENSTRE ((HØYRE ((HØYRE (A2, LEN (A2)-Finn (",", A2))), LEN ((HØYRE (A2, LEN (A2)-Finn ( "", A2))) )-Finn ( "", (HØYRE (A2, LEN (A2)-Finn ( "", A2)))))), FINN ( "", (HØYRE ((HØYRE (A2, LEN (A2) -Finn ( "", A2))), LEN ((HØYRE (A2, LEN (A2)-Finn ( "", A2)))) - FINN ( "", (HØYRE (A2, LEN (A2 )-Finn ( "", A2)))))), 1) -1)"

Den formelen utdrages riktig “Portland, ME 04076” ut av den opprinnelige strengen.

Hvis du vil pakke ut neste avsnitt, gjentar du fremgangsmåten ovenfor igjen.

Excel-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 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 deretter under Data menyelement, velg Tekst til kolonner.

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 den galne formelen ovenfor ... men hvor 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 Excel-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 som noe annet.

Hvis du virkelig vil bli seriøs med å bruke Excel, vil du lese gjennom vår nybegynners veiledning for å bruke Microsoft Excel. Begynnerveiledningen til Microsoft Excel Begynnerveiledningen til Microsoft Excel Bruk denne nybegynnersveiledningen for å starte opplevelsen din med Microsoft Excel. De grunnleggende regnearktipsene her vil hjelpe deg å begynne å lære Excel på egen hånd. Les mer . Den har alt du trenger for å begynne å øke produktiviteten din med Excel.

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