Căutați și evidențiați date în Excel (cu formatare condiționată)

Vizionați videoclipuri - Căutați și evidențiați datele folosind formatarea condiționată

Dacă lucrați cu seturi de date mari, poate fi necesar să creați o funcționalitate de căutare care să vă permită să evidențiați rapid celulele / rândurile pentru termenul căutat.

Deși nu există o modalitate directă de a face acest lucru în Excel, puteți crea funcționalitatea de căutare utilizând Formatarea condiționată.

De exemplu, să presupunem că aveți un set de date așa cum se arată mai jos (în imagine). Are coloane pentru Nume produs, Reprezentant vânzări și Țară.

Acum puteți utiliza formatarea condiționată pentru a căuta un cuvânt cheie (introducându-l în celula C2) și evidențiați toate celulele care au acel cuvânt cheie.

Ceva așa cum se arată mai jos (unde introduc numele articolului în celula B2 și apăs pe Enter, întregul rând este evidențiat):

În acest tutorial, vă voi arăta cum să creați această căutare și să evidențiați funcționalitatea în Excel.

Mai târziu, în tutorial, vom merge puțin mai avansat și vom vedea cum să-l dinamizăm (astfel încât să fie evidențiat în timp ce tastați în caseta de căutare).

Faceți clic aici pentru a descărca fișierul de exemplu și urmează.

Căutați și evidențiați celulele potrivite

In aceasta sectiune. Vă voi arăta cum să căutați și să evidențiați numai celulele potrivite dintr-un set de date.

Ceva așa cum se arată mai jos:

Iată pașii pentru a căuta și a evidenția toate celulele care au textul potrivit:

1. Selectați setul de date pe care doriți să aplicați Formatarea condiționată (A4: F19 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. În opțiunile drop-down, faceți clic pe Regula nouă.
   
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe opțiunea „Utilizați o formulă pentru a determina ce celule să formatați”.
6. Introduceți următoarea formulă: = A4 = $ B $ 1
7. Faceți clic pe butonul „Format…”.
8. Specificați formatarea (pentru a evidenția celulele care se potrivesc cu cuvântul cheie căutat).
9. Faceți clic pe OK.

Acum introduceți orice în celula B1 și apăsați Enter. Acesta va evidenția celulele potrivite din setul de date care conțin cuvântul cheie în B1.

Cum funcționează asta?

Formatarea condiționată se aplică ori de câte ori formula specificată în ea returnează TRUE.

În exemplul de mai sus, verificăm fiecare celulă folosind formula = A4 = $ B $ 1

Formatarea condiționată verifică fiecare celulă și o verifică, conținutul din celulă este același cu cel din celula B1. Dacă este la fel, formula returnează TRUE și celula este evidențiată. Dacă nu este același, formula returnează FALS și nu se întâmplă nimic.

Faceți clic aici pentru a descărca fișierul de exemplu și urmează.

Căutați și evidențiați rândurile cu date potrivite

Dacă doriți să evidențiați întregul rând în loc doar de celulele potrivite, puteți face acest lucru modificând puțin formula.

Mai jos este un exemplu în care întregul rând este evidențiat dacă tipul de produs se potrivește cu cel din celula B1.

Iată pașii de căutare și evidențiere a întregului rând:

1. Selectați setul de date pe care doriți să aplicați Formatarea condiționată (A4: F19 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. În opțiunile drop-down, faceți clic pe Regula nouă.
   
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe opțiunea „Utilizați o formulă pentru a determina ce celule să formatați”.
6. Introduceți următoarea formulă: = $ B4 = $ B $ 1
7. Faceți clic pe butonul „Format…”.
8. Specificați formatarea (pentru a evidenția celulele care se potrivesc cu cuvântul cheie căutat).
9. Faceți clic pe OK.

Pașii de mai sus ar căuta elementul specificat în setul de date și, dacă va găsi elementul care se potrivește, va evidenția întregul rând.

Rețineți că acest lucru va verifica doar coloana articolului. Dacă introduceți un nume de reprezentant de vânzări aici, acesta nu va funcționa. Dacă doriți să funcționeze pentru numele reprezentantului de vânzări, trebuie să schimbați formula în = $ C4 = $ B $ 1

Notă: Motivul pentru care evidențiază întregul rând și nu doar celula potrivită este că am folosit un semn $ înainte de referința coloanei ($ B4). Acum, când formatarea condițională analizează celulele dintr-un rând, verifică dacă valoarea din coloana B a acelui rând este egală cu valoarea din celula B1. Deci, chiar și atunci când analizează A4 sau B4 sau C4 și așa mai departe, verifică numai valoarea B4 (deoarece am blocat coloana B folosind semnul dolar).

Puteți citi mai multe despre referințe absolute, relative și mixte aici.

Căutați și evidențiați rândurile (pe baza potrivirii parțiale)

În unele cazuri, poate doriți să evidențiați rândurile pe baza unei potriviri parțiale.

De exemplu, dacă aveți elemente precum White Board, Green Board și Gray Board și doriți să evidențiați toate acestea pe baza cuvântului Board, atunci puteți face acest lucru folosind funcția SEARCH.

Ceva așa cum se arată mai jos:

Iată pașii pentru a face acest lucru:

1. Selectați setul de date pe care doriți să aplicați Formatarea condiționată (A4: F19 în acest exemplu).
2. Faceți clic pe fila Acasă.
3. În grupul Stiluri, faceți clic pe Formatare condiționată.
4. În opțiunile drop-down, faceți clic pe Regula nouă.
   
5. În caseta de dialog „Regula de formatare nouă”, faceți clic pe opțiunea „Utilizați o formulă pentru a determina ce celule să formatați”.
6. Introduceți următoarea formulă: = ȘI ($ B $ 1 ””, NUMĂR (CĂUTARE ($ B $ 1, $ B4)))
7. Faceți clic pe butonul „Format…”.
8. Specificați formatarea (pentru a evidenția celulele care se potrivesc cu cuvântul cheie căutat).
9. Faceți clic pe OK.

Cum funcționează asta?

• Funcția CĂUTARE caută șirul de căutare / cuvântul cheie în toate celulele dintr-un rând. Returnează o eroare dacă cuvântul cheie de căutare nu este găsit și returnează un număr dacă găsește o potrivire.
• Funcția ISNUMBER convertește eroarea în FALS și valorile numerice în ADEVĂRAT.
• ȘI funcția verifică o condiție suplimentară - ca celula C2 să nu fie goală.

Deci, acum, ori de câte ori introduceți un cuvânt cheie în celula B1 și apăsați Enter, acesta evidențiază toate rândurile care au celulele care conțin acel cuvânt cheie.

Sfat bonus: Dacă doriți să faceți căutarea sensibilă la majuscule și minuscule, utilizați funcția FIND în loc de SEARCH.

Faceți clic aici pentru a descărca fișierul de exemplu și urmează de-a lungul.

Funcționalitate dinamică de căutare și evidențiere (evidențiază în timp ce tastați)

Folosind aceleași trucuri de formatare condiționată prezentate mai sus, puteți să faceți un pas mai departe și să îl faceți dinamic.

De exemplu, puteți crea o bară de căutare în care datele potrivite sunt evidențiate pe măsură ce tastați în bara de căutare.

Ceva așa cum se arată mai jos:

Acest lucru se poate face folosind controalele ActiveX și poate fi o funcționalitate bună de utilizat atunci când creați rapoarte sau tablouri de bord.

Mai jos este un videoclip în care vă arăt cum să creați acest lucru:

Ați găsit util acest tutorial? Spuneți-mi părerile dvs. în secțiunea de comentarii.

Vă pot plăcea, de asemenea, următoarele tutoriale Excel:

• Filtru Excel dinamic - Extrage date pe măsură ce introduceți text.
• Creați o listă drop-down cu sugestii de căutare.
• Crearea unei hărți de căldură în Excel.
• Evidențiați rândurile pe baza unei valori a celulei în Excel.
• Evidențiați rândul și coloana active într-un interval de date în Excel.
• Cum să evidențiați celulele goale în Excel.