10 trinn til et søk i flere kolonner i Excel

Excel tilbyr mange måter å søke, sortere og filtrere data på, og de er enkle å kombinere og automatisere. Du kan for eksempel lage en brukervennlig søkeløsning med flere kolonner ved å kombinere valideringslister og betinget formatering. Det er enkelt å implementere og enkelt å forbedre når du vokser.

Først oppretter du en unik liste over verdier basert på dataene du vil søke. Deretter bruker du datavalideringsfunksjonen til å lage rullegardinlister basert på de unike listene. Når alle brikkene er på plass, legger du til en betinget formateringsregel som drar dem sammen.

Fordi denne teknikken henter lister ved å bruke datavalideringsfunksjonen, kan du lagre denne teknikken for statiske (eller for det meste statiske) data. Du må oppdatere lister og betinget formateringsområde hvis du endrer datoperioden. Selvfølgelig kan du lage dynamiske lister og et dynamisk inputområde for å håndtere hyppige oppdateringer - men det er mer arbeid.

Merk: Du kan laste ned en Excel-demofil som illustrerer denne teknikken.

1: Definer søket

Det første trinnet er et av tankene; må du definere søkekravene. For dette eksemplet oppretter vi et to-kolonne søk basert på den innsendte datoen og statusen i arket vist i figur A. (Dataene er fra tabellen med innkjøpsordre i databaseapplikasjonen Northwind Access. Jeg har endret noen av statusverdiene for å legge til mer kontrast.) Søket vil fremheve innsendte eller godkjente ordrer for en bestemt dato.

Figur A

Bestem søkekravene dine.

2: Gjør plass

Å legge til rullegardinlistene for søk over dataene er mest fornuftig, mesteparten av tiden, men det er ikke et krav. Hvis du vil legge til noen blanke rader over dataområdet, velger du rader 1 til 3. I kategorien Hjem velger du deretter Sett inn arkrader fra rullegardinmenyen Sett inn i Cells-gruppen. I Excel 2003 velger du Rader fra Sett inn-menyen.

3: Lag en unik liste for hver søkekolonne

Denne teknikken bruker en datavalideringsliste basert på naturlige data, så du trenger en liste med unike verdier for hver søkekolonne. Lag først en liste over unike datoer fra kolonnen Submitted Date, som følger:

  1. Velg dataene du vil liste. I dette tilfellet er det D4: D32.
  2. Klikk på fanen Data, og klikk deretter Avansert i gruppen Sorter og filter. I Excel 2003 velger du Filter og deretter Avansert filter fra Data-menyen.
  3. Velg alternativet Kopier til et annet sted.
  4. Angi H4 som kopi til-området
  5. Velg alternativet Unike poster bare, som vist i figur B. (Søk! Er arkenavnsidentifikatoren; jeg brukte klikkmetoden, så Excel viser komplette og absolutte referanser.)
  6. Klikk OK. Figur C viser den resulterende listen ved siden av dataene.

Figur B

Disse innstillingene vil opprette en unik liste over innsendte dataverdier.

Figur C

Den resulterende listen er lett å se ved siden av dataområdet.

Du kan kopiere listen hvor som helst, men for å forenkle eksemplet, kopier den nær dataområdet. Når du bruker dette på ditt eget arbeid, kan det være lurt å kopiere listen til et annet ark, slik at det ikke distraherer brukerne.

Gjenta prosessen for å lage den andre listen, vist i figur D. Denne gangen er listespekteret E4: E32 og Copy To-området er I4. (Grensen mellom datoperioden og de to listene er ikke påkrevd; den skiller bare listene visuelt fra dataområdet.)

Figur D

Du trenger en unik liste for hver søkekolonne.

4: Legg til beskrivende etiketter for hver valideringsliste

Du er klar til å legge til valideringslistene, men først legg til etiketter for å identifisere dem. Bare kopier overskriftsteksten for hver søkekolonne til den tilsvarende cellen i rad 1 (D1 og E1), som vist i figur E.

Figur E

Sett opp valideringslistene.

5: Lag en rullegardinliste for hver søkekolonne

Deretter legger du til en rullegardin for hver søkekolonne. Vi baserer listen over innsendte datoer på listen i kolonne H, som følger:

  1. Velg D2 - det er der du vil vise rullegardinlisten, rett under den beskrivende teksten i D1.
  2. Klikk på fanen Data, og velg deretter Datavalidering fra rullegardinmenyen Datavalidering i gruppen Dataverktøy. I Excel 2003 velger du Validering fra Data-menyen.
  3. I listen Innstillinger (standard) velger du Liste fra rullegardinmenyen Tillat.
  4. Spesifiser = $ H $ 5: $ H $ 11 som kilde, som vist i figur F.
  5. Klikk OK.

Figur F

Spesifiser den unike listen i kolonne H som denne rullegardinens kilde.
Gjenta prosessen for å lage en andre liste i E2, bruk = $ I $ 5: $ I $ 6 som kilde. Figur G viser de resulterende valideringslistene.

Figur G

Du har nettopp opprettet to nedtrekkslister ved å bruke datavalideringsfunksjonen.

6: Legg til betinget formateringsregel

Alle brikkene er på plass. Nå er det på tide å legge til den betingede formateringsregelen:

  1. Velg datoperiode. I dette tilfellet er det A5: G32.
  2. Klikk på kategorien Hjem og velg Ny regel fra rullegardinmenyen Betinget formatering i gruppen Stiler. I Excel 2003 velger du Betinget formatering fra Format-menyen.
  3. Velg alternativet Bruk en formel for å bestemme hvilke celler som skal formateres i toppruten. I Excel 2003 velger du Formel Is fra tilstand 1-kontrollen.
  4. Skriv inn formelen = OG ($ D $ 2 = $ D5, $ E $ 2 = $ E5), som vist i figur H.

Figur H

Denne OG-operatøren kombinerer to forhold.

Ved å bruke operatøren AND, kan du spesifisere mer enn en betingelse. Det første uttrykket, $ D $ 2 = $ D5, returnerer True når en verdi i kolonne D tilsvarer det valgte elementet i rullegardinlisten i D2. Legg merke til at referansen til D5 bruker blandet referanse for å imøtekomme alle verdiene i dataområdet. Det andre uttrykket, $ E $ 2 = $ E5, fungerer på samme måte for rullegardinmenyen E. Når begge uttrykkene er sanne, er betinget formateringsregel sann, og Excel bruker det betingede formatet, som vi vil spesifisere neste.

7: Angi betinget format

For å fortsette, klikk på Format-knappen, og angi en bandingfarge fra Fyll-fanen. Klikk deretter OK for å gå tilbake til den opprinnelige dialogen, vist i figur I, som viser formelen og formatet. Klikk OK for å gå tilbake til arket.

Figur I

Denne regelen vil bruke det spesifiserte formatet når den betingede regelen er sann.

8: Prøv det!

Søkeløsningen er klar til å teste ved å velge et element fra begge listene. Hvis datoen i D2 returnerer en numerisk verdi i stedet for en dato, bruker du kortdatoformatet på D2. Figur J viser resultatet av å velge 3/24/2006 og Godkjent. Som du kan se, fremhever den betingede formateringsregelen postene sendt inn den 24.03.2006 med godkjent status. Det er utrolig hvor mye du får for så liten innsats!

Figur J

Velg et element fra hver liste for å utløse betinget formateringsregel.

På dette tidspunktet kan du tro at du er ferdig - men du kan enkelt utvide denne teknikken til å omfatte flere søkekolonner.

9: Legg til en ny søkekolonne

La oss anta at du vil utvide søket ved å utheve poster for spesifikt personell. I dette tilfellet vil du gjenta trinn 3, 4 og 5 for å generere en unik liste og en tredje rullegardin. Bruk innstillingene vist i figur K for å legge til kolonne C i søkeløsningen. Bruk deretter innstillingene vist i figur L for å legge til en valideringsliste til C2. Figur M viser resultatene av å legge til en tredje søkekolonne til løsningen.

Figur K

Bruk disse innstillingene til å lage en unik liste over verdier fra Opprettet av-kolonnen.

Figur L

Bruk disse innstillingene til å opprette en tredje rullegardin.

Figur M

Legg til en valideringsliste for den nye søkekolonnen.

10: Oppdater regelen om betinget format

Du er omtrent ferdig. Legg til et tredje uttrykk til den betingede formatregelen, som følger:

  1. Velg dataområdet (A5: G32).
  2. Klikk på kategorien Hjem og velg Administrer regler i rullegardinmenyen Betinget formatering i gruppen Stiler. I Excel 2003 velger du Betinget formatering fra Format-menyen.
  3. Velg riktig regel og klikk Rediger regel, som vist i figur N. (Det er ingen manager i Excel 2003, bare rediger regelen på riktig måte.)
  4. Plasser markøren mellom de siste 5 og den lukkende parentesen i formelen.
  5. Legg til et komma og uttrykket $ C $ 2 = $ C5, som vist i figur O.
  6. Klikk OK to ganger.

Figur N

Bruk Policy Manager for å endre en eksisterende regel.

Figur 0

Legg til uttrykket som legger kolonne C-verdier til søket.
Nå kan du søke med tre kolonner samtidig. Figur P viser resultatet av å velge Nancy Freehafer fra den nye listen.

Figur P

Legg til så mange søkekolonner du trenger; generere en unik liste, bygg en rullegardin på den unike listen, og oppdater deretter betingelsesformateringsregelen for å imøtekomme den nye kolonnen.

Topp 10 nyhetsbrev

Vend deg til disse må-lesne primerne for å få den magre på de hotteste teknologiske emner, strategier og analyser. Leveres fredager

Registrer deg i dag

© Copyright 2020 | mobilegn.com