Gledajte videozapis - Pretražujte i označite podatke pomoću uvjetnog oblikovanja
Ako radite s velikim skupovima podataka, može se pojaviti potreba za stvaranjem funkcionalnosti pretraživanja koja vam omogućuje brzo označavanje ćelija/redaka za traženi pojam.
Iako ne postoji izravan način za to u Excelu, možete stvoriti funkcionalnost pretraživanja pomoću uvjetnog oblikovanja.
Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku (na slici). Sadrži stupce za naziv proizvoda, prodajnog predstavnika i državu.
Sada možete koristiti uvjetno oblikovanje za pretraživanje ključne riječi (unosom u ćeliju C2) i označavanjem svih ćelija koje imaju tu ključnu riječ.
Nešto kao što je prikazano u nastavku (gdje unosim naziv stavke u ćeliju B2 i pritisnem Enter, cijeli red će biti istaknut):
U ovom vodiču pokazat ću vam kako stvoriti ovu funkciju pretraživanja i istaknuti funkcionalnost u Excelu.
Kasnije u vodiču ići ćemo malo naprednije i vidjeti kako ga učiniti dinamičnim (tako da se istakne dok upisujete u okvir za pretraživanje).
Kliknite ovdje za preuzimanje datoteke primjera i slijedite ga.
Pretraživanje i označavanje ćelija za podudaranje
U ovom odjeljku. Pokazat ću vam kako pretraživati i označavati samo odgovarajuće ćelije u skupu podataka.
Nešto kao što je prikazano ispod:
Evo koraka za pretraživanje i označavanje svih ćelija s odgovarajućim tekstom:
- Odaberite skup podataka na koji želite primijeniti uvjetno oblikovanje (A4: F19 u ovom primjeru).
- Kliknite karticu Početna.
- U grupi Stilovi kliknite Uvjetno oblikovanje.
- U padajućim opcijama kliknite Novo pravilo.
- U dijaloškom okviru "Novo pravilo oblikovanja" kliknite opciju "Koristi formulu za određivanje ćelija koje ćete oblikovati".
- Unesite sljedeću formulu: = A4 = $ B $ 1
- Kliknite gumb "Formatiraj …".
- Navedite oblikovanje (za označavanje ćelija koje odgovaraju traženoj ključnoj riječi).
- Pritisnite U redu.
Sada upišite bilo što u ćeliju B1 i pritisnite enter. Istaknut će podudarne ćelije u skupu podataka koje sadrže ključnu riječ u B1.
Kako ovo radi?
Uvjetno oblikovanje primjenjuje se kad god formula navedena u njemu vrati TRUE.
U gornjem primjeru provjeravamo svaku ćeliju pomoću formule = A4 = $ B $ 1
Uvjetno oblikovanje provjerava svaku ćeliju i provjerava je li sadržaj u ćeliji isti kao i u ćeliji B1. Ako je isto, formula vraća TRUE i ćelija će biti istaknuta. Ako nije isto, formula vraća FALSE i ništa se ne događa.
Kliknite ovdje za preuzimanje datoteke primjera i slijedite ga.
Pretražite i označite redove s podudarajućim podacima
Ako želite označiti cijeli redak umjesto samo odgovarajućih ćelija, to možete učiniti tako da malo prilagodite formulu.
Ispod je primjer gdje se cijeli redak ističe ako vrsta proizvoda odgovara onoj u ćeliji B1.
Evo koraka za pretraživanje i označavanje cijelog retka:
- Odaberite skup podataka na koji želite primijeniti uvjetno oblikovanje (A4: F19 u ovom primjeru).
- Kliknite karticu Početna.
- U grupi Stilovi kliknite Uvjetno oblikovanje.
- U padajućim opcijama kliknite Novo pravilo.
- U dijaloškom okviru "Novo pravilo oblikovanja" kliknite opciju "Koristi formulu za određivanje ćelija koje ćete oblikovati".
- Unesite sljedeću formulu: = $ B4 = $ B $ 1
- Kliknite gumb "Formatiraj …".
- Navedite oblikovanje (za označavanje ćelija koje odgovaraju traženoj ključnoj riječi).
- Pritisnite U redu.
Gore navedeni koraci tražili bi navedenu stavku u skupu podataka, a ako pronađe odgovarajuću stavku, označit će cijeli redak.
Imajte na umu da će ovo provjeriti samo stupac stavke. Ako ovdje unesete naziv prodajnog predstavnika, to neće funkcionirati. Ako želite da radi za naziv prodajnog predstavnika, morate promijeniti formulu u = $ C4 = $ B $ 1
Napomena: Razlog zašto ističe cijeli red, a ne samo odgovarajuću ćeliju je taj što smo koristili znak $ prije referencije stupca ($ B4). Sada, kada uvjetno oblikovanje analizira ćelije u redu, provjerava je li vrijednost u stupcu B tog retka jednaka vrijednosti u ćeliji B1. Dakle, čak i kada analizira A4 ili B4 ili C4 i tako dalje, provjerava samo vrijednost B4 (jer smo stupac B zaključali znakom dolara).
Ovdje možete pročitati više o apsolutnim, relativnim i mješovitim referencama.
Redci za pretraživanje i isticanje (na temelju djelomičnog podudaranja)
U nekim ćete slučajevima možda htjeti istaknuti retke na temelju djelomičnog podudaranja.
Na primjer, ako imate stavke poput bijele ploče, zelene ploče i sive ploče, a sve to želite istaknuti na temelju riječi ploča, to možete učiniti pomoću funkcije TRAGANJE.
Nešto kao što je prikazano ispod:
Evo koraka za to:
- Odaberite skup podataka na koji želite primijeniti uvjetno oblikovanje (A4: F19 u ovom primjeru).
- Kliknite karticu Početna.
- U grupi Stilovi kliknite Uvjetno oblikovanje.
- U padajućim opcijama kliknite Novo pravilo.
- U dijaloškom okviru "Novo pravilo oblikovanja" kliknite opciju "Koristi formulu za određivanje ćelija koje ćete oblikovati".
- Unesite sljedeću formulu: = AND ($ B $ 1 ””, ISNUMBER (TRAŽI ($ B $ 1, $ B4)))
- Kliknite gumb "Formatiraj …".
- Navedite oblikovanje (za označavanje ćelija koje odgovaraju traženoj ključnoj riječi).
- Pritisnite U redu.
Kako ovo radi?
- SEARCH funkcija traži niz za pretraživanje/ključnu riječ u svim ćelijama zaredom. Vraća pogrešku ako ključna riječ za pretraživanje nije pronađena, a vraća broj ako pronađe podudaranje.
- ISNUMBER funkcija pretvara pogrešku u FALSE, a numeričke vrijednosti u TRUE.
- Funkcija AND provjerava postoji li dodatni uvjet - da ćelija C2 ne smije biti prazna.
Zato sada, kad god unesete ključnu riječ u ćeliju B1 i pritisnete Enter, ona ističe sve retke koji sadrže ćelije koje sadrže tu ključnu riječ.
Bonus savjet: Ako želite učiniti pretraživanje osjetljivim, upotrijebite funkciju FIND umjesto SEARCH.
Kliknite ovdje za preuzimanje datoteke primjera i slijedite ga.
Funkcija dinamičkog pretraživanja i isticanja (Istaknuto dok tipkate)
Koristeći gore navedene trikove uvjetnog oblikovanja, možete napraviti i korak dalje i učiniti ga dinamičnim.
Na primjer, možete stvoriti traku za pretraživanje u kojoj se odgovarajući podaci ističu dok upisujete u traku za pretraživanje.
Nešto kao što je prikazano ispod:
To se može učiniti pomoću ActiveX kontrola i može biti dobra funkcionalnost za izradu izvješća ili nadzornih ploča.
Ispod je videozapis u kojem pokazujem kako to stvoriti:
Je li vam ovaj vodič bio koristan? Recite mi svoja razmišljanja u odjeljku za komentare.
Možda će vam se svidjeti i sljedeći vodiči za Excel:
- Dinamički Excel filtar - izdvaja podatke dok tipkate.
- Napravite padajući popis s prijedlogom pretraživanja.
- Izrada toplinske karte u Excelu.
- Istaknite redove na temelju vrijednosti ćelije u Excelu.
- Označite aktivni redak i stupac u rasponu podataka u Excelu.
- Kako označiti prazne ćelije u Excelu.
