3 Skøre Microsoft Excel -formler, der er yderst nyttige

3 Skøre Microsoft Excel -formler, der er yderst nyttige

Microsoft Excel -formler kan næsten alt. I denne artikel lærer du, hvor kraftfulde Microsoft Excel -formler og betinget formatering kan være, med tre nyttige eksempler.





Lær Microsoft Excel at kende

Vi har dækket en række forskellige måder at udnytte Excel bedre på, f.eks hvor finder du flotte Excel -skabeloner, der kan downloades , og hvordan man bruger Excel som et projektstyringsværktøj .



Meget af Excel -kraften ligger bag Excel -formlerne og -reglerne, der hjælper dig med at manipulere data og oplysninger automatisk, uanset hvilke data du indsætter i regnearket.





Lad os grave i, hvordan du kan bruge formler og andre værktøjer til bedre at bruge Microsoft Excel.

Betinget formatering med Excel -formler

Et af de værktøjer, som folk ikke bruger ofte nok, er betinget formatering. Med brug af Excel -formler, regler eller bare et par virkelig enkle indstillinger kan du omdanne et regneark til et automatisk dashboard.



For at komme til betinget formatering klikker du bare på Hjem fanen og klik på Betinget formatering værktøjslinje ikon.

Under Betinget formatering er der mange muligheder. De fleste af disse er uden for omfanget af denne særlige artikel, men størstedelen af ​​dem handler om at fremhæve, farve eller skygge celler baseret på dataene i denne celle.



Dette er sandsynligvis den mest almindelige brug af betinget formatering-ting som f.eks. At gøre en celle rød ved hjælp af formler, der er mindre end eller større. Lær mere om, hvordan du bruger IF -sætninger i Excel.

Et af de mindre brugte betingede formateringsværktøjer er Ikonsæt indstilling, som tilbyder et fantastisk sæt ikoner, du kan bruge til at omdanne en Excel -datacelle til et ikon på instrumentbrættet.



Når du klikker på Administrer regler , det vil tage dig til Conditional Formatting Rules Manager .

disk kører på 99 windows 10

Afhængigt af de data, du har valgt, før du vælger ikonsættet, ser du cellen angivet i vinduet Manager med det ikon -sæt, du lige har valgt.

Når du klikker på Rediger regel , vil du se dialogen, hvor magien sker.

Det er her, du kan oprette den logiske formel og ligninger, der viser det ønskede dashboardikon.

Dette eksempel 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 helt over budgettet, bliver det rødt.

Som du kan se, viser dette dashboard, at tidsbudgettering ikke lykkes.

Næsten halvdelen af ​​tiden bruges langt 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, så er her et par, du kan prøve.

Du kender sikkert VLookup -funktionen, som lader dig søge gennem en liste efter et bestemt element i en kolonne og returnere dataene fra en anden kolonne i samme række som elementet.

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 hvis de skiftes?

Hvad i eksemplet herunder, hvad hvis jeg vil finde den opgave, jeg udførte den 25/06/2018 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.

Hvis du læser Microsoft Excel-pro-brugerfora, finder du mange mennesker, der siger, at dette ikke er muligt med VLookup. Du skal bruge en kombination af indeks- og matchfunktioner for at gøre dette. Det er ikke helt rigtigt.

Du kan få VLookup til at fungere på denne måde ved at indlejre en VÆLG -funktion i den. I dette tilfælde vil Excel -formlen se sådan ud:

'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'

Denne funktion betyder, at du vil finde datoen 25/06/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, ikke?

Det er rigtigt, men hvad du laver med ' VÆLGE 'funktion manipulerer de to felter.

Du tildeler reference 'indeks' numre til dataområder - tildeler datoerne til indeks nummer 1 og opgaverne til indeks nummer 2.

Så når du skriver '2' i VLookup -funktionen, refererer du faktisk til indeksnummer 2 i funktionen VÆLG. Fedt, ikke?

VLookup bruger nu Dato kolonne og returnerer data fra Opgave kolonne, selvom opgaven er til venstre.

kan du se, hvem der ser dit linkin

Nu hvor du kender denne lille smule, kan du forestille dig, hvad du ellers kan gøre!

Hvis du forsøger at udføre andre avancerede datasøgningsopgaver, kan du læse denne artikel om finde data i Excel ved hjælp af opslagsfunktioner .

2. Indlejret formel til parsestrenge

Her er endnu en 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 række afgrænsede data.

Når du har indsendt dataene, vil du analysere disse data i de enkelte komponenter. Her er et eksempel på oplysninger om navn, adresse og telefonnummer, der er afgrænset af ';' Karakter.

Sådan kan du analysere disse oplysninger ved hjælp af en Excel -formel (se om du mentalt kan følge med denne vanvid):

For det første felt, for at udtrække elementet til venstre (personens navn), ville du blot bruge en VENSTRE funktion i formlen.

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

Sådan fungerer denne logik:

  • Søger efter tekststrengen fra A2
  • Finder ';' afgrænsersymbol
  • Fratrækker en for den korrekte placering af enden af ​​den strengsektion
  • Grib den tekst længst til venstre til det punkt

I dette tilfælde er teksten til venstre 'Ryan'. Mission fuldført.

3. Indlejret formel i Excel

Men hvad med de andre afsnit?

Der kan være lettere måder at gøre dette på, men da vi vil prøve at skabe den mest vanvittige indlejrede Excel -formel (det virker faktisk), vil vi bruge en unik tilgang.

For at udtrække delene til højre skal du indlejre flere RIGHT -funktioner for at få fat i tekstdelen indtil det første ';' symbolet, og udfør VENSTRE -funktionen på det igen. Sådan ser det ud for at udtrække 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 sammensætte. Alt jeg gjorde var at tage denne funktion:

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

Og indsatte det hvert sted i VENSTRE funktion ovenfor, hvor der er en 'A2'.

Dette udtrækker korrekt den anden sektion af strengen.

sim -kort ikke leveret mm#2

Hver efterfølgende sektion af strengen har brug for en anden rede. Det eneste du skal gøre er at tage ' RET 'ligning, som du oprettede i det sidste afsnit, og indsæt den i en ny HØJRE formel med den tidligere HØJRE formel indsat i den, hvor du ser' A2 '. Sådan ser det ud.

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

Derefter skal du tage DENNE formel og placere den i den originale VENSTRE formel, hvor der er en 'A2'.

Den sidste mind-bøjende 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 trækker korrekt 'Portland, ME 04076' ud af den originale streng.

For at udtrække det næste afsnit skal du gentage ovenstående proces igen.

Dine Excel -formler kan blive virkelig sløjfe, men alt du laver er at klippe og indsætte lange formler i sig selv og lave lange reder, der stadig virker.

Ja, dette opfylder kravet om 'skør'. Men lad os være ærlige, der er en meget enklere måde at opnå det samme på med en 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 enhver afgrænser, du ønsker. Bare indtast ' ; ', og du vil se, at forhåndsvisningen af ​​dine valgte data ændres i overensstemmelse hermed.

Med et par klik kan du gøre det samme som den skøre formel ovenfor ... men hvor er det sjove i det?

Bliver gal med Microsoft Excel -formler

Så der har du det. Ovenstående formler beviser, hvor over-the-top en person kan få, når han opretter Microsoft Excel-formler til at udføre bestemte opgaver.

Nogle gange er disse Excel -formler faktisk ikke 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 som med alt andet.

Hvis du virkelig vil blive seriøs med at bruge Excel, vil du gerne læse vores begynderguide til brug af Microsoft Excel. Det har alt hvad du behøver for at øge din produktivitet med Excel. Derefter skal du konsultere vores vigtige snedeark i Excel -funktioner for mere vejledning.

Billedkredit: kues/Depositphotos

Del Del Tweet E -mail De 7 bedste økonomiske funktioner i Excel

Uanset om du er en revisor eller en finansiel professionel, bør du kende disse Excel -formler.

Læs Næste Relaterede emner
  • Produktivitet
  • Regnearkstip
  • Microsoft Excel
  • Microsoft Office 2016
  • Microsoft Office -tip
  • Microsoft Office 2019
Om forfatteren Ryan Dube(942 artikler udgivet)

Ryan har en bachelorgrad i elektroteknik. Han har arbejdet 13 år inden for automationsteknik, 5 år inden for IT og er nu Apps Engineer. Tidligere administrerende redaktør for MakeUseOf har han talt på nationale konferencer om datavisualisering og er blevet vist på nationalt tv og radio.

Mere fra Ryan Dube

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere