Se video - Søg og fremhæv data ved hjælp af betinget formatering
Hvis du arbejder med store datasæt, kan der være behov for at oprette en søgefunktionalitet, der giver dig mulighed for hurtigt at fremhæve celler/rækker for det søgte udtryk.
Selvom der ikke er nogen direkte måde at gøre dette på i Excel, kan du oprette søgefunktionalitet ved hjælp af betinget formatering.
Antag f.eks., At du har et datasæt som vist nedenfor (i billedet). Det har kolonner for produktnavn, salgsrepræsentant og land.
Nu kan du bruge betinget formatering til at søge efter et søgeord (ved at indtaste det i celle C2) og fremhæve alle de celler, der har det søgeord.
Noget som vist nedenfor (hvor jeg indtaster varenavnet i celle B2 og trykker på Enter, bliver hele rækken fremhævet):
I denne vejledning viser jeg dig, hvordan du opretter denne søgning og fremhæver funktionalitet i Excel.
Senere i selvstudiet vil vi gå lidt avanceret og se, hvordan vi gør det dynamisk (så det fremhæves, mens du skriver i søgefeltet).
Klik her for at downloade eksempelfilen og følg med.
Søg og fremhæv matchende celler
I dette afsnit. Jeg viser dig, hvordan du søger og fremhæver kun de matchende celler i et datasæt.
Noget som vist herunder:
Her er trinene til at søge og fremhæve alle de celler, der har den matchende tekst:
- Vælg det datasæt, som du vil anvende betinget formatering (A4: F19 i dette eksempel).
- Klik på fanen Hjem.
- Klik på Betinget formatering i gruppen Stilarter.
- Klik på Ny regel i rullemenuerne.
- I dialogboksen 'Ny formateringsregel' skal du klikke på indstillingen 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
- Indtast følgende formel: = A4 = $ B $ 1
- Klik på knappen "Format …".
- Angiv formateringen (for at markere celler, der matcher det søgte søgeord).
- Klik på OK.
Skriv nu alt i celle B1, og tryk på enter. Det fremhæver de matchende celler i datasættet, der indeholder søgeordet i B1.
Hvordan virker det?
Betinget formatering anvendes, når den angivne formel returnerer SAND.
I eksemplet ovenfor kontrollerer vi hver celle ved hjælp af formlen = A4 = $ B $ 1
Betinget formatering kontrollerer hver celle og verificerer, at indholdet i cellen er det samme som i celle B1. Hvis det er det samme, returnerer formlen SAND, og cellen fremhæves. Hvis det ikke er det samme, returnerer formlen FALSK, og der sker ikke noget.
Klik her for at downloade eksempelfilen og følg med.
Søg og fremhæv rækker med matchende data
Hvis du vil markere hele rækken i stedet for kun de matchende celler, kan du gøre det ved at justere formlen lidt.
Nedenfor er et eksempel, hvor hele rækken bliver fremhævet, hvis produkttypen matcher den i celle B1.
Her er trinene til at søge og fremhæve hele rækken:
- Vælg det datasæt, som du vil anvende betinget formatering (A4: F19 i dette eksempel).
- Klik på fanen Hjem.
- Klik på Betinget formatering i gruppen Stilarter.
- Klik på Ny regel i rullemenuerne.
- I dialogboksen 'Ny formateringsregel' skal du klikke på indstillingen 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
- Indtast følgende formel: = $ B4 = $ B $ 1
- Klik på knappen 'Format …'.
- Angiv formateringen (for at markere celler, der matcher det søgte søgeord).
- Klik på OK.
Ovenstående trin søger efter det angivne element i datasættet, og hvis det finder det matchende element, fremhæver det hele rækken.
Bemærk, at dette kun kontrollerer varekolonnen. Hvis du indtaster et salgsrepræsentantnavn her, virker det ikke. Hvis du vil have det til at fungere for salgsrepræsentantens navn, skal du ændre formlen til = $ C4 = $ B $ 1
Bemærk: Årsagen til, at det fremhæver hele rækken og ikke kun den matchende celle, er, at vi har brugt et $ -tegn før kolonnehenvisningen ($ B4). Når betinget formatering nu analyserer celler i en række, kontrollerer det, om værdien i kolonne B i denne række er lig med værdien i celle B1. Så selv når det analyserer A4 eller B4 eller C4 og så videre, tjekker det kun B4 -værdi (da vi har låst kolonne B ved at bruge dollartegnet).
Du kan læse mere om absolutte, relative og blandede referencer her.
Søg og fremhæv rækker (baseret på delvis match)
I nogle tilfælde vil du måske fremhæve rækker baseret på et delvis match.
For eksempel, hvis du har elementer som White Board, Green Board og Gray Board, og du vil fremhæve alle disse baseret på ordet Board, kan du gøre dette ved hjælp af funktionen SØG.
Noget som vist herunder:
Her er trinene til at gøre dette:
- Vælg det datasæt, som du vil anvende betinget formatering (A4: F19 i dette eksempel).
- Klik på fanen Hjem.
- Klik på Betinget formatering i gruppen Stilarter.
- Klik på Ny regel i rullemenuerne.
- I dialogboksen 'Ny formateringsregel' skal du klikke på indstillingen 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
- Indtast følgende formel: = AND ($ B $ 1 ””, ISNUMBER (SØG ($ B $ 1, $ B4)))
- Klik på knappen 'Format …'.
- Angiv formateringen (for at markere celler, der matcher det søgte søgeord).
- Klik på OK.
Hvordan virker det?
- SEARCH -funktion søger efter søgestrengen/søgeordet i alle cellerne i en række. Det returnerer en fejl, hvis søgeordet ikke findes, og returnerer et tal, hvis det finder et match.
- ISNUMBER -funktionen konverterer fejlen til FALSK og de numeriske værdier til SAND.
- OG funktion kontrollerer en yderligere betingelse - at celle C2 ikke må være tom.
Så nu, når du skriver et søgeord i celle B1 og trykker på Enter, fremhæver det alle de rækker, der har cellerne, der indeholder det søgeord.
Bonustip: Hvis du vil gøre søgekriteriet følsomt, skal du bruge FIND -funktionen i stedet for SEARCH.
Klik her for at downloade eksempelfilen og følg med.
Dynamisk søgning og fremhævningsfunktionalitet (højdepunkter, mens du skriver)
Ved hjælp af de samme betingede formateringstriks, der er dækket ovenfor, kan du også tage det et skridt videre og gøre det dynamisk.
For eksempel kan du oprette en søgelinje, hvor de matchende data fremhæves, mens du skriver i søgelinjen.
Noget som vist herunder:
Dette kan gøres ved hjælp af ActiveX -kontroller og kan være en god funktionalitet at bruge, når du opretter rapporter eller dashboards.
Nedenfor er en video, hvor jeg viser, hvordan jeg opretter dette:
Fandt du denne vejledning nyttig? Lad mig vide dine tanker i kommentarfeltet.
Du kan også lide følgende Excel -selvstudier:
- Dynamisk Excel -filter - Udtrækker data, mens du skriver.
- Opret en rulleliste med søgeforslag.
- Oprettelse af et varmekort i Excel.
- Fremhæv rækker baseret på en celleværdi i Excel.
- Fremhæv den aktive række og kolonne i et dataområde i Excel.
- Sådan fremhæves tomme celler i Excel.
