Se video - Søk og fremhev data ved hjelp av betinget formatering
Hvis du jobber med store datasett, kan det være behov for å opprette en søkefunksjonalitet som lar deg raskt markere celler/rader for det søkte uttrykket.
Selv om det ikke er noen direkte måte å gjøre dette i Excel, kan du opprette søkefunksjonalitet ved hjelp av betinget formatering.
Anta for eksempel at du har et datasett som vist nedenfor (på bildet). Den har kolonner for produktnavn, salgsrepresentant og land.
Nå kan du bruke betinget formatering til å søke etter et nøkkelord (ved å skrive det inn i celle C2) og markere alle cellene som har det søkeordet.
Noe som vist nedenfor (der jeg skriver inn varenavnet i celle B2 og trykker Enter, blir hele raden uthevet):
I denne opplæringen vil jeg vise deg hvordan du oppretter dette søket og fremhever funksjonalitet i Excel.
Senere i opplæringen vil vi gå litt avansert og se hvordan vi gjør det dynamisk (slik at det fremheves mens du skriver i søkeboksen).
Klikk her for å laste ned eksempelfilen og følg med.
Søk og fremhev matchende celler
I denne seksjonen. Jeg vil vise deg hvordan du søker og fremhever bare de matchende cellene i et datasett.
Noe som vist nedenfor:
Her er trinnene for å søke og markere alle cellene som har matchende tekst:
- Velg datasettet du vil bruke betinget formatering på (A4: F19 i dette eksemplet).
- Klikk på kategorien Hjem.
- Klikk på Betinget formatering i gruppen Stiler.
- Klikk på Ny regel i rullegardinmenyen.
- I dialogboksen "Ny formateringsregel" klikker du på alternativet "Bruk en formel for å bestemme hvilke celler som skal formateres".
- Skriv inn følgende formel: = A4 = $ B $ 1
- Klikk på "Format …" -knappen.
- Angi formateringen (for å markere celler som samsvarer med det søkte søkeordet).
- Klikk OK.
Skriv nå alt i celle B1 og trykk enter. Det vil markere de matchende cellene i datasettet som inneholder søkeordet i B1.
Hvordan virker dette?
Betinget formatering brukes når formelen som er angitt i den returnerer SANN.
I eksemplet ovenfor sjekker vi hver celle ved hjelp av formelen = A4 = $ B $ 1
Betinget formatering kontrollerer hver celle og bekrefter at innholdet i cellen er det samme som i celle B1. Hvis det er det samme, returnerer formelen TRUE og cellen blir uthevet. Hvis det ikke er det samme, returnerer formelen FALSK og ingenting skjer.
Klikk her for å laste ned eksempelfilen og følg med.
Søk og fremhev rader med matchende data
Hvis du vil markere hele raden i stedet for bare de matchende cellene, kan du gjøre det ved å justere formelen litt.
Nedenfor er et eksempel der hele raden blir uthevet hvis produkttypen samsvarer med den i celle B1.
Her er trinnene for å søke og markere hele raden:
- Velg datasettet du vil bruke betinget formatering på (A4: F19 i dette eksemplet).
- Klikk på kategorien Hjem.
- Klikk på Betinget formatering i gruppen Stiler.
- Klikk på Ny regel i rullegardinmenyen.
- I dialogboksen "Ny formateringsregel" klikker du på alternativet "Bruk en formel for å bestemme hvilke celler som skal formateres".
- Skriv inn følgende formel: = $ B4 = $ B $ 1
- Klikk på "Format …" -knappen.
- Spesifiser formateringen (for å markere celler som samsvarer med det søkte søkeordet).
- Klikk OK.
Trinnene ovenfor vil søke etter det angitte elementet i datasettet, og hvis det finner det matchende elementet, vil det markere hele raden.
Vær oppmerksom på at dette bare vil se etter varekolonnen. Hvis du skriver inn et salgsrepresentantnavn her, fungerer det ikke. Hvis du vil at det skal fungere for et salgsrepresentantnavn, må du endre formelen til = $ C4 = $ B $ 1
Merk: Grunnen til at den markerer hele raden og ikke bare matchende celle er at vi har brukt et $ -tegn før kolonnehenvisningen ($ B4). Når betinget formatering analyserer celler på rad, sjekker den om verdien i kolonne B i den raden er lik verdien i celle B1. Så selv om den analyserer A4 eller B4 eller C4 og så videre, sjekker den bare B4 -verdi (ettersom vi har låst kolonne B ved å bruke dollartegnet).
Du kan lese mer om absolutte, relative og blandede referanser her.
Søk og uthev rader (basert på delvis samsvar)
I noen tilfeller vil du kanskje markere rader basert på en delvis samsvar.
For eksempel, hvis du har elementer som White Board, Green Board og Gray Board, og du vil markere alle disse basert på ordet Board, kan du gjøre dette ved å bruke SEARCH -funksjonen.
Noe som vist nedenfor:
Her er trinnene for å gjøre dette:
- Velg datasettet du vil bruke betinget formatering på (A4: F19 i dette eksemplet).
- Klikk på kategorien Hjem.
- Klikk på Betinget formatering i gruppen Stiler.
- Klikk på Ny regel i rullegardinmenyen.
- I dialogboksen "Ny formateringsregel" klikker du på alternativet "Bruk en formel for å bestemme hvilke celler som skal formateres".
- Skriv inn følgende formel: = AND ($ B $ 1 ””, ISNUMBER (SØK ($ B $ 1, $ B4)))
- Klikk på "Format …" -knappen.
- Spesifiser formateringen (for å markere celler som samsvarer med det søkte søkeordet).
- Klikk OK.
Hvordan virker dette?
- SEARCH -funksjonen ser etter søkestrengen/søkeordet i alle cellene på rad. Det returnerer en feil hvis søkeordet ikke blir funnet, og returnerer et tall hvis det finner et treff.
- ISNUMBER -funksjonen konverterer feilen til FALSE og de numeriske verdiene til TRUE.
- OG -funksjonen sjekker om det er en tilleggstilstand - at celle C2 ikke skal være tom.
Så nå, når du skriver inn et søkeord i celle B1 og trykker Enter, markerer det alle radene som har cellene som inneholder det søkeordet.
Bonustips: Hvis du vil gjøre søkelysfølsomme, bruker du FIND -funksjonen i stedet for SEARCH.
Klikk her for å laste ned eksempelfilen og følg med.
Dynamisk søk og fremhevingsfunksjonalitet (høydepunkter mens du skriver)
Ved å bruke de samme betingelsene for betinget formatering som er dekket ovenfor, kan du også ta det et skritt videre og gjøre det dynamisk.
For eksempel kan du opprette en søkefelt der matchende data blir markert mens du skriver i søkefeltet.
Noe som vist nedenfor:
Dette kan gjøres ved hjelp av ActiveX -kontroller og kan være en god funksjonalitet å bruke når du oppretter rapporter eller dashbord.
Nedenfor er en video hvor jeg viser hvordan du lager dette:
Synes du denne opplæringen var nyttig? Gi meg beskjed om tankene dine i kommentarfeltet.
Du kan også like følgende Excel -opplæringsprogrammer:
- Dynamisk Excel -filter - trekker ut data mens du skriver.
- Lag en rullegardinliste med forslag til søk.
- Opprette et varmekart i Excel.
- Uthev rader basert på en celleverdi i Excel.
- Marker den aktive raden og kolonnen i et dataområde i Excel.
- Hvordan markere tomme celler i Excel.
