Med fortsatt bruk Microsoft Excel å spore eller Budsjettering Eller inventar, du vil sannsynligvis lære mye om de tilgjengelige funksjonene og deres formler. Men å oppdage noe nytt kan være forvirrende eller skremmende. Denne veiledningen introduserer noen få nyttige funksjoner og deres formler for når du er klar til å dykke inn i nytt territorium. Her er 5 nyttige avanserte Excel-formler du bør kjenne til.
1. Finn verdiene i regnearket ditt: XLOOKUP
Når du vil søke etter data basert på andre data i et stort regneark, er XLOOKUP din go-to-funksjon. annet enn VLOOKUP , som bare kan søke etter verdier fra venstre til høyre, XLOOKUP kan søke etter verdier fra venstre til høyre eller høyre til venstre, noe som gjør det til et mer fleksibelt verktøy.
Merk: Når dette skrives, er XLOOKUP kun tilgjengelig for Microsoft 365-abonnenter. For andre Excel-versjoner, sjekk ut kombinasjonen av INDEX og MATCH i neste seksjon.
Funksjonsformelen er:
XLOOKUP(oppslagsverdi; oppslagsområde; returområde; ikke_funnet; samsvarsmodus; søkemodus)
Bare de tre første argumentene kreves. Nedenfor er en beskrivelse av hvert argument:
- Oppslagsverdi: Verdien å søke etter.
- Lookup_range: Området som inneholder verdien det skal søkes etter.
- Returområde: Området som inneholder verdien som skal returneres.
- Ikke funnet: Teksten som skal returneres hvis verdien ikke blir funnet. "#N/A" er standard hvis den utelates.
- match_mode: Samsvarstype med "0" for eksakt samsvar og "#N/A" for ikke funnet (standard hvis utelatt), "1" for eksakt samsvar og neste mindre element hvis ingen ble funnet, "-1" for eksakt samsvar og større element Neste hvis ikke funnet, eller '2' for å matche et jokertegn ved hjelp av et spørsmålstegn, stjerne eller tilde.
- Søkemodus: Søkemodus med "1" for å starte ved første element (standard hvis utelatt), "-1" for å starte ved siste element, "2" når "lookup_range" er i stigende rekkefølge, eller "-2" når "lookup_range" er i synkende rekkefølge.
For eksempel ser vi etter salg for segment 2, så vi bruker følgende formel:
=XLOOKUP(2,A19:A24,D19:D24)
Ved å bryte ned formelen er 2 "oppslagsverdien", A19:A24 er "oppslagsområdet", og D19:D24 er argumentet "returområde".
Resultatet, 74000, er den korrekte tilsvarende verdien for sektor 2.
Hvis du vil ha noe mer dynamisk, hvor du ser etter en verdi i en celle i stedet for en konstant, og deretter mottar det oppdaterte resultatet, hver gang du endrer verdien i den cellen, se følgende eksempel:
Vi erstatter "lookup_value" 2 med celle F19:
=XLOOKUP(F19,A19:A24,D19:D24)
Når vi skriver inn 2 i celle F19, får vi resultatet 74000, og hvis vi skriver inn en annen verdi, som 5, får vi automatisk det oppdaterte resultatet, som er 61000.
Du kan inkludere argumentet "ikke funnet" Så du trenger ikke å se på en feilmelding hvis ingen treff ble funnet. Vi legger til "ikke svar" på argumentet.
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
Når en verdi legges inn i celle F19 som ikke samsvarer, vil du se meldingen din i stedet for "#NA".
2. Finn den alternative verdien i et ark: INDEX og MATCH
Mens XLOOKUP gir deg en kraftig måte å slå opp verdier på, er den foreløpig bare tilgjengelig for Microsoft 365 Excel-abonnenter. Hvis du bruker en annen versjon av Excel, kan du bruke INDEX og MATCH for å gjøre det samme.
Med INDEKS-funksjonen returneres verdien i cellen basert på posisjonen du skrev inn i formelen. INDEKS-formelen er:
INDEX(område; radnummer; kolonnenummer)
Med MATCH-funksjonen returneres posisjonen til verdien du skriver inn i formelen. MATCH-formelen er:
MATCH(verdi; område; samsvarstype)
For å kombinere disse to funksjonene og deres formler, sett MATCH-formelen i INDEX-formelen som søkested ("radnummer" og "kolonnenummer").
Ved å bruke de samme dataene som XLOOKUP-eksemplet ovenfor, ønsker vi å se segmentsalget som vi legger inn i celle F19. Formelen er:
=INDEX(D19:D24,MATCH(F19,A19:A24))
For å bryte ned denne formelen starter vi med INDEX og dens "range"-argument, som er D19:D24. Dette er utvalget som inneholder resultatet vi ønsker.
MATCH-formelen bruker F19 som "verdi"-argument og A19:A24 som "område"-argument som inneholder den verdien.
Når vi skriver inn 4 i celle F19 får vi resultatet 75000, som er riktig.
Hvis vi legger inn en annen sektor i celle F19, for eksempel 6, oppdateres formelen automatisk for å gi den riktige verdien på 58000.
3. LEGG TIL ELLER TELLE MED KRITERIER: SUMIF, SUMIFS, COUNTIF, COUNTIFS
SUM- og COUNT-funksjonene i Excel er laget for å legge til tall og telle celler, men de er begrenset til enkle matematiske operasjoner. Variasjoner på disse funksjonene lar deg legge til eller telle ved hjelp av kriterier. Du kan for eksempel bare legge til tall større enn 12 eller telle celler som inneholder navnet Bill.
Med SUMIF og COUNTIFS kan du legge til eller telle med én betingelse, og med SUMIFS og COUNTIFS kan du legge til eller telle med flere betingelser. Nedenfor er syntaksen for hver:
SUMIF
SUM.HVIS(område; kriterier; sum_område)
ANTALL.HVIS
ANTALLHVIS(område; tilstand)
SUMIFS
SUMMER(sum_område; tilstandsområde1; tilstand1; tilstandsområde2; tilstand2;...)
COUNTIFS
ANTALLHVIS(tilstandsområde1; tilstand1; tilstandsområde2; tilstand2;...)
Se nedenfor for et eksempel på hver funksjon.
For å bare legge til tallene i vårt område B2 til B7 for tall større enn 12, bruker vi SUMIF-funksjonen og denne formelen:
=SUM.HVIS(B2:B7;">12")
Resultatet er 31, siden formelen la til 16 og 15, de eneste to tallene som er større enn 12 i datasettet.
I følgende eksempel teller vi celler med verdier mindre enn 12 i samme område, B2 til B7, med følgende formel:
=ANTALLHVIS(B2:B7,"<12")
Resultatet av denne formelen er 4, siden det er antall celler i området med verdier mindre enn 12.
La oss ta det opp med SUMIFS og bruke to betingelser. For å legge til tallene i cellene B2 til B7 kun for de som abonnerer på vårt nyhetsbrev (Y) i cellene C2 til C7 og hvis by er San Diego i cellene D2 til D7, bruker vi følgende formel:
=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")
Resultatet av denne formelen er 19, siden bare to kunder har en Y for nyhetsbrevet og en San Diego for byen. Bill Brown har 11 og Sue Smith har 8 for totalt 19.
For et eksempel som bruker COUNTIFS-funksjonen, teller vi antall celler med de samme to betingelsene beskrevet ovenfor. Vi teller antall kunder som abonnerer på nyhetsbrevet (Y) og byen deres San Diego.
=ANTALL(C2:C7,"Y",D2:D7,"San Diego")
Som forventet var poengsummen 2 for Bill Brown og Sue Smith, San Diegos eneste nyhetsbrevabonnement.
4. Testdata under forskjellige forhold: IFS
Excels IF-funksjon er et kraftig verktøy for å teste data mot forhold. Du kan for eksempel bruke den til å vise en bokstavkarakter for en elevs numeriske poengsum eller "ja" hvis selgeren hentet inn nok inntekter til å tjene bonusen sin og "nei" hvis de ikke gjorde det.
Problemet med IF-funksjonen er at for å teste dataene dine mot flere kriterier, må du kombinere alle IF-setningene sammen. For å rette opp denne gruppeforvirringen, bruk IFS-funksjonen.
I likhet med SUMIFS og COUNTIFS ovenfor, lar IFS deg legge til flere kriterier i formelen din på en klar og lettlest måte.
Syntaksen er IFS(test1, if_test1_true, test2, if_test2_true, ...), hvor du kan teste opptil 127 forskjellige benchmarks.
Ved å bruke et av eksempelscenarioene våre begynte vi ganske enkelt å referere til belønninger. Hvis mengden i celle B2 er større enn 20000 XNUMX, vises "Ja". Hvis ikke, vis "Nei". Formelen blir:
=IFS(B2>20000,"Ja",B2<20000,"Nei")
I den første testen, hvis verdien i B2 er større enn 20000 2, er resultatet "ja". I den andre testen, hvis verdien i B20000 er mindre enn XNUMX XNUMX, er resultatet hvis sant "Nei".
Heldigvis mottar vår selger sin belønning. Ettersom den omsetter for 21000,- er den lagt inn "Ja" i bonuskolonnen.
Kopier formelen hvis du har en liste som vårt eksempel. Dra fyllhåndtaket i nedre høyre hjørne av cellen til de gjenværende cellene til andre selgere. Formelen oppdateres automatisk for å imøtekomme de forskjellige cellereferansene.
For et annet eksempel fra IFS har vi en liste over vaktledere for hver ukedag. Når gjeldende dag vises i celle D2, vises navnet på den tilsvarende lederen i celle E2. Formelen blir:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
Dette sier at hvis verdien i celle D2 er mandag, vis navnet i celle B2, hvis verdien i celle D2 er tirsdag, vis navnet i celle B3, og hvis verdien i celle D2 er onsdag, vis navnet i celle B3 og så videre.
Når du endrer ukedagen i celle D2, vises det tilsvarende navnet på vaktlederen i celle E2.
Med en liten mengde arbeid med å lage formelen på forhånd, kan du høste fruktene av litt automatisering i Excel-arket ditt.
5. Filtrer ved hjelp av flere kriterier: filter
Som Excel-bruker vet du sikkert at applikasjonen kommer med en innebygd filterfunksjon. Men hvis du vil filtrere dataene dine ved å bruke en betingelse eller til og med flere kriterier, må du bruke filterfunksjonen. Denne fleksible funksjonaliteten og dens argumenter kan hjelpe deg med å begrense store datasett på bare noen få minutter.
Syntaksen til funksjonen er FILTER(område, område=kriterier, hvis_tom), der bare de to første argumentene til datasettet pluss kriteriene og dets beholderområde er nødvendig. Det tredje argumentet er nyttig hvis du vil returnere noe spesielt hvis formelen ikke returnerer et nullresultat, f.eks "Det er ikke noe data" أو "ingenting".
Det er viktig å vite hvordan FILTER-funksjonen fungerer med en enkelt betingelse før du legger til flere kriterier. For eksempel kan du filtrere dataene i cellene A2 til C9 av Harold Hill som er i B2 til B9. Formelen for dette er:
=FILTER(A2:C9,B2:B9="Harold Hill")
Vi har to resultater for Harold Hill. lett ikke sant?
La oss legge til flere termer i formelen. Flere kriterier legges til ved å bruke en stjerne (*) for OG og et plusstegn (+) for ELLER.
For eksempel filtrerer vi begge Harold Hill و Elektronikk ved hjelp av følgende formel:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
For å dele formelen er A2:C9 datasettet, B2: B9 = "Harold Hill" er den første betingelsen, og stjernen representerer OG , og A2: A9 = "Elektronikk" er den andre betingelsen.
Vi fikk ett resultat med filteret vårt, der formelen må samsvare med de to betingelsene, Harold Hill og Electronics.
I et annet eksempel kan du filtrere etter klær eller biler:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Ved å dele med denne formelen er A2:C9 datasettet, A2: A9 = "Klær" er den første betingelsen, og plusstegnet representerer OR , og A2: A9 = "Automatisk" er den andre betingelsen.
Denne gangen fikk vi to resultater, der formelen må samsvare med begge betingelsene – ikke begge.
ofte stilte spørsmål
Q1: Hvordan finner jeg formelstrukturen til en funksjon direkte i Excel?
Svar: Excel har et innebygd verktøy for å finne funksjonaliteten du trenger. Med den kan du se syntaksen til funksjonens syntaks.
Velg en tom celle, gå til Formler-fanen og velg Sett inn funksjon på venstre side av båndet. Skriv inn jobben i søkefeltet øverst og klikk på Gå. Når du ser funksjonen du ønsker, velger du den for å se beskrivelsen og syntaksen nederst i boksen.
Q2: Hvordan kan jeg feilsøke en formel som ikke fungerer som den skal?
Svar: Velg formelen du opplever og gå til Formler-fanen. Velg "Formatevaluering" i Formatrevisjon-delen av båndet.
Du vil se formelen din i Evaluer Formula-vinduet med en del av den understreket. Klikk "Evaluer" for å se beregningen av den understrekede delen. Fortsett denne prosessen for å gå gjennom hver del av formelen til resultatet. Dette lar deg se hvordan formelen behandles og hvor feilen kan være.
Q3: Hvordan finner jeg ut hvorfor jeg får en formelfeil?
Svar: Når du ser en feil i en formel du har opprettet, kan du gjøre to ting for å få mer informasjon. Klikk først på feilknappen som vises ved siden av cellen, og velg deretter Hjelp for denne feilen for å få flere detaljer fra Microsoft i sidefeltet som vises.
Alternativt kan du velge Vis beregningstrinn for å åpne formelevalueringsvinduet vist ovenfor. Du vil se resultatet av beregningen og prosessen for å komme dit, noe som bør bidra til å finne årsaken til feilen.
