Iskanje in označevanje podatkov v Excelu (s pogojnim oblikovanjem)

Oglejte si videoposnetek - iskanje in označevanje podatkov z uporabo pogojnega oblikovanja

Če delate z velikimi nabori podatkov, boste morda morali ustvariti iskalno funkcijo, ki vam omogoča, da hitro označite celice/vrstice za iskalni izraz.

Čeprav v Excelu ni neposrednega načina za to, lahko ustvarite iskalno funkcijo z uporabo pogojnega oblikovanja.

Recimo, da imate nabor podatkov, kot je prikazano spodaj (na sliki). Ima stolpce za ime izdelka, prodajnega predstavnika in državo.

Zdaj lahko uporabite pogojno oblikovanje za iskanje ključne besede (tako, da jo vnesete v celico C2) in označite vse celice, ki imajo to ključno besedo.

Nekaj, kot je prikazano spodaj (kjer vnesem ime predmeta v celico B2 in pritisnem Enter, celotna vrstica bo označena):

V tej vadnici vam bom pokazal, kako ustvariti to iskanje in poudariti funkcionalnost v Excelu.

Kasneje v vadnici bomo nekoliko napredovali in videli, kako jo narediti dinamično (tako, da bo poudarjena med tipkanjem v iskalnem polju).

Kliknite tukaj, če želite prenesti primer datoteke in sledite.

Iskanje in označevanje ujemajočih se celic

V tem razdelku. Pokazal vam bom, kako iščete in označite samo ujemajoče se celice v naboru podatkov.

Nekaj, kot je prikazano spodaj:

Tu so koraki za iskanje in označevanje vseh celic z ustreznim besedilom:

1. Izberite nabor podatkov, za katerega želite uporabiti pogojno oblikovanje (A4: F19 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. V spustnih možnostih kliknite Novo pravilo.
   
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite možnost »Uporabi formulo, da določite, katere celice želite formatirati«.
6. Vnesite naslednjo formulo: = A4 = $ B $ 1
7. Kliknite gumb »Oblikuj …«.
8. Določite oblikovanje (za označevanje celic, ki se ujemajo z iskano ključno besedo).
9. Kliknite V redu.

Zdaj vnesite kar koli v celico B1 in pritisnite enter. Poudaril bo ujemajoče se celice v naboru podatkov, ki vsebujejo ključno besedo v B1.

Kako to deluje?

Pogojno oblikovanje se uporabi, kadar formula, navedena v njem, vrne TRUE.

V zgornjem primeru vsako celico preverimo s formulo = A4 = $ B $ 1

Pogojno oblikovanje preveri vsako celico in jo preveri, da je vsebina v celici enaka vsebini v celici B1. Če je enako, formula vrne TRUE in celica se označi. Če ni isto, formula vrne FALSE in nič se ne zgodi.

Kliknite tukaj, če želite prenesti primer datoteke in sledite.

Iščite in označite vrstice z ustreznimi podatki

Če želite označiti celotno vrstico namesto samo ujemajočih se celic, lahko to storite tako, da nekoliko spremenite formulo.

Spodaj je primer, ko je celotna vrstica označena, če se vrsta izdelka ujema z vrsto v celici B1.

Tu so koraki za iskanje in označevanje celotne vrstice:

1. Izberite nabor podatkov, za katerega želite uporabiti pogojno oblikovanje (A4: F19 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. V spustnih možnostih kliknite Novo pravilo.
   
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite možnost »Uporabi formulo, da določite, katere celice želite formatirati«.
6. Vnesite naslednjo formulo: = $ B4 = $ B $ 1
7. Kliknite gumb »Oblikuj …«.
8. Določite oblikovanje (za označevanje celic, ki se ujemajo z iskano ključno besedo).
9. Kliknite V redu.

Zgornji koraki bi iskali določeno postavko v naboru podatkov in če najde ujemajočo se postavko, bo označila celotno vrstico.

Upoštevajte, da bo to preverilo le stolpec postavk. Če tukaj vnesete ime prodajnega predstavnika, to ne bo delovalo. Če želite, da deluje za ime prodajnega predstavnika, morate spremeniti formulo v = $ C4 = $ B $ 1

Opomba: Razlog za poudarjanje celotne vrstice in ne le ujemajoče se celice je ta, da smo pred sklicem na stolpec ($ B4) uporabili znak $. Ko pogojno oblikovanje analizira celice v vrsti, preveri, ali je vrednost v stolpcu B te vrstice enaka vrednosti v celici B1. Torej tudi pri analizi A4 ali B4 ali C4 in tako naprej preverja samo vrednost B4 (saj smo stolpec B zaklenili z znakom za dolar).

Več o absolutnih, relativnih in mešanih referencah lahko preberete tukaj.

Vrstice za iskanje in označevanje (na podlagi delnega ujemanja)

V nekaterih primerih boste morda želeli označiti vrstice na podlagi delnega ujemanja.

Na primer, če imate elemente, kot so bela tabla, zelena tabla in siva tabla, in želite vse te poudariti glede na besedo deska, lahko to storite s funkcijo SEARCH.

Nekaj, kot je prikazano spodaj:

Tu so naslednji koraki:

1. Izberite nabor podatkov, za katerega želite uporabiti pogojno oblikovanje (A4: F19 v tem primeru).
2. Kliknite zavihek Domov.
3. V skupini Slogi kliknite Pogojno oblikovanje.
4. V spustnih možnostih kliknite Novo pravilo.
   
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite možnost »Uporabi formulo, da določite, katere celice želite formatirati«.
6. Vnesite naslednjo formulo: = AND ($ B $ 1 ””, ISNUMBER (ISKANJE ($ B $ 1, $ B4)))
7. Kliknite gumb »Oblikuj …«.
8. Določite oblikovanje (za označevanje celic, ki se ujemajo z iskano ključno besedo).
9. Kliknite V redu.

Kako to deluje?

• Funkcija SEARCH išče iskalni niz/ključno besedo v vseh celicah zapored. Vrne napako, če ključne besede za iskanje ni najdeno, in vrne številko, če najde ujemanje.
• ISNUMBER funkcija pretvori napako v FALSE in številske vrednosti v TRUE.
• Funkcija AND preveri dodatni pogoj - da celica C2 ne sme biti prazna.

Tako zdaj, ko v celico B1 vnesete ključno besedo in pritisnete Enter, označi vse vrstice s celicami, ki vsebujejo to ključno besedo.

Bonus nasvet: Če želite, da je iskanje občutljivo, uporabite funkcijo FIND namesto SEARCH.

Kliknite tukaj, če želite prenesti primer datoteke in sledite.

Funkcija dinamičnega iskanja in označevanja (Poudarki med tipkanjem)

Z istimi zgoraj navedenimi triki pogojnega oblikovanja lahko naredite še korak dlje in ga naredite dinamičnega.

Na primer, lahko ustvarite iskalno vrstico, v kateri se med tipkanjem v iskalno vrstico označijo ujemajoči se podatki.

Nekaj, kot je prikazano spodaj:

To lahko storite s kontrolniki ActiveX in je lahko dobra funkcija za ustvarjanje poročil ali nadzornih plošč.

Spodaj je video, kjer pokažem, kako to ustvariti:

Se vam je ta vadnica zdela uporabna? Sporočite mi svoje misli v oddelku za komentarje.

Morda vam bodo všeč tudi naslednji vaje za Excel:

• Dinamični Excelov filter - izvleče podatke med tipkanjem.
• Ustvarite spustni seznam s predlogom iskanja.
• Ustvarjanje toplotnega zemljevida v Excelu.
• Označite vrstice na podlagi vrednosti celice v Excelu.
• Označite aktivno vrstico in stolpec v obsegu podatkov v Excelu.
• Kako označiti prazne celice v Excelu.