Titta på video - Sök och markera data med villkorlig formatering
Om du arbetar med stora datamängder kan det finnas ett behov av att skapa en sökfunktion som gör att du snabbt kan markera celler/rader för den sökte termen.
Det finns inget direkt sätt att göra detta i Excel, men du kan skapa sökfunktioner med villkorlig formatering.
Anta till exempel att du har en dataset som visas nedan (i bilden). Den har kolumner för produktnamn, säljare och land.
Nu kan du använda villkorlig formatering för att söka efter ett nyckelord (genom att ange det i cell C2) och markera alla celler som har det sökordet.
Något som visas nedan (där jag anger objektnamnet i cell B2 och trycker på Enter, blir hela raden markerad):
I den här självstudien visar jag dig hur du skapar den här sökningen och markerar funktionalitet i Excel.
Senare i handledningen kommer vi att gå lite avancerat och se hur vi gör det dynamiskt (så att det markeras medan du skriver i sökrutan).
Klicka här för att ladda ner exempelfilen och följ med.
Sök och markera matchande celler
I denna avdelning. Jag visar dig hur du söker och markerar endast matchande celler i en datamängd.
Något som visas nedan:
Här är stegen för att söka och markera alla celler som har matchande text:
- Välj datauppsättningen som du vill använda villkorlig formatering (A4: F19 i detta exempel).
- Klicka på fliken Hem.
- Klicka på Villkorlig formatering i gruppen Stilar.
- Klicka på Ny regel i rullgardinsmenyn.
- I dialogrutan "Ny formateringsregel" klickar du på alternativet "Använd en formel för att bestämma vilka celler som ska formateras".
- Ange följande formel: = A4 = $ B $ 1
- Klicka på knappen "Format …".
- Ange formateringen (för att markera celler som matchar det sökte sökordet).
- Klicka på OK.
Skriv nu vad som helst i cell B1 och tryck på enter. Det kommer att markera matchande celler i datamängden som innehåller nyckelordet i B1.
Hur fungerar detta?
Villkorlig formatering tillämpas när formeln som anges i den returnerar SANT.
I exemplet ovan kontrollerar vi varje cell med hjälp av formeln = A4 = $ B $ 1
Villkorlig formatering kontrollerar varje cell och verifierar att innehållet i cellen är samma som i cell B1. Om det är samma sak returnerar formeln SANT och cellen markeras. Om det inte är samma sak returnerar formeln FALSKT och ingenting händer.
Klicka här för att ladda ner exempelfilen och följ med.
Sök och markera rader med matchande data
Om du vill markera hela raden istället för bara matchande celler kan du göra det genom att justera formeln lite.
Nedan visas ett exempel där hela raden markeras om produkttypen matchar den i cell B1.
Här är stegen för att söka och markera hela raden:
- Välj datauppsättningen som du vill använda villkorlig formatering (A4: F19 i detta exempel).
- Klicka på fliken Hem.
- Klicka på Villkorlig formatering i gruppen Stilar.
- Klicka på Ny regel i rullgardinsmenyn.
- I dialogrutan "Ny formateringsregel" klickar du på alternativet "Använd en formel för att avgöra vilka celler som ska formateras".
- Ange följande formel: = $ B4 = $ B $ 1
- Klicka på knappen "Format …".
- Ange formateringen (för att markera celler som matchar det sökte sökordet).
- Klicka på OK.
Stegen ovan skulle söka efter det angivna objektet i datamängden, och om det hittar det matchande objektet kommer det att markera hela raden.
Observera att detta endast kommer att kolla på artikelkolumnen. Om du anger ett säljare -namn här fungerar det inte. Om du vill att det ska fungera för säljare namn måste du ändra formeln till = $ C4 = $ B $ 1
Obs! Anledningen till att den markerar hela raden och inte bara matchande cell är att vi har använt ett $ -tecken före kolumnreferensen ($ B4). När villkorlig formatering analyserar celler i rad kontrollerar den om värdet i kolumn B i den raden är lika med värdet i cell B1. Så även när det analyserar A4 eller B4 eller C4 och så vidare, kontrollerar det endast B4 -värde (eftersom vi har låst kolumn B genom att använda dollarstecknet).
Du kan läsa mer om absoluta, relativa och blandade referenser här.
Sök och markera rader (baserat på partiell matchning)
I vissa fall kanske du vill markera rader baserat på en delvis matchning.
Om du till exempel har objekt som White Board, Green Board och Gray Board, och du vill markera alla dessa baserat på ordet Board, kan du göra detta med hjälp av SEARCH -funktionen.
Något som visas nedan:
Här är stegen för att göra detta:
- Välj datauppsättningen som du vill använda villkorlig formatering (A4: F19 i det här exemplet).
- Klicka på fliken Hem.
- Klicka på Villkorlig formatering i gruppen Stilar.
- Klicka på Ny regel i rullgardinsmenyn.
- I dialogrutan "Ny formateringsregel" klickar du på alternativet "Använd en formel för att bestämma vilka celler som ska formateras".
- Ange följande formel: = AND ($ B $ 1 ””, ISNUMBER (SÖK ($ B $ 1, $ B4)))
- Klicka på knappen "Format …".
- Ange formateringen (för att markera celler som matchar det sökte sökordet).
- Klicka på OK.
Hur fungerar detta?
- SEARCH -funktionen letar efter söksträngen/sökordet i alla celler i rad. Det returnerar ett fel om sökordet inte hittas och returnerar ett nummer om det hittar en matchning.
- ISNUMBER -funktionen omvandlar felet till FALSE och de numeriska värdena till TRUE.
- AND -funktionen kontrollerar ett ytterligare villkor - att cell C2 inte ska vara tom.
Så nu, när du skriver ett sökord i cell B1 och trycker på Retur, markerar det alla rader som har cellerna som innehåller det sökordet.
Bonustips: Om du vill göra sök skiftlägeskänslig, använd funktionen HITTA istället för SÖK.
Klicka här för att ladda ner exempelfilen och följ med.
Dynamisk sök- och markeringsfunktionalitet (höjdpunkter när du skriver)
Med samma trick för villkorlig formatering som täcks ovan kan du också ta det ett steg längre och göra det dynamiskt.
Du kan till exempel skapa ett sökfält där matchande data markeras när du skriver i sökfältet.
Något som visas nedan:
Detta kan göras med hjälp av ActiveX -kontroller och kan vara en bra funktionalitet att använda när du skapar rapporter eller instrumentpaneler.
Nedan är en video där jag visar hur jag skapar detta:
Tyckte du att denna handledning var användbar? Låt mig veta dina tankar i kommentarsfältet.
Du kanske också gillar följande Excel -självstudier:
- Dynamiskt Excel -filter - extraherar data medan du skriver.
- Skapa en listruta med sökförslag.
- Skapa en värmekarta i Excel.
- Markera rader baserat på ett cellvärde i Excel.
- Markera den aktiva raden och kolumnen i ett dataområde i Excel.
- Så här markerar du tomma celler i Excel.
