Žiūrėkite vaizdo įrašą - ieškokite ir paryškinkite duomenis naudodami sąlyginį formatavimą
Jei dirbate su dideliais duomenų rinkiniais, gali prireikti sukurti paieškos funkciją, leidžiančią greitai paryškinti ieškomo termino ląsteles/eilutes.
Nors „Excel“ nėra tiesioginio būdo tai padaryti, galite sukurti paieškos funkciją naudodami sąlyginį formatavimą.
Pavyzdžiui, tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau (paveikslėlyje). Jame yra produkto pavadinimo, pardavimo atstovo ir šalies stulpeliai.
Dabar galite naudoti sąlyginį formatavimą ieškodami raktinio žodžio (įvesdami jį į langelį C2) ir paryškindami visas ląsteles, kuriose yra tas raktinis žodis.
Kažkas, kaip parodyta žemiau (kai įvedu elemento pavadinimą langelyje B2 ir paspaudžiu „Enter“, visa eilutė paryškinama):
Šioje pamokoje parodysiu, kaip sukurti šią paieškos ir paryškinimo funkciją „Excel“.
Vėliau vadovaudamiesi mes šiek tiek pažengsime ir pažiūrėsime, kaip padaryti ją dinamišką (kad ji būtų paryškinta, kai rašote paieškos laukelyje).
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą ir sekite toliau.
Ieškokite ir pažymėkite atitinkančius langelius
Šiame skyriuje. Parodysiu, kaip ieškoti ir paryškinti tik atitinkamas duomenų rinkinio ląsteles.
Kažkas, kaip parodyta žemiau:
Štai veiksmai, skirti ieškoti ir paryškinti visas langelius, kuriuose yra atitinkamas tekstas:
- Pasirinkite duomenų rinkinį, kuriam norite taikyti sąlyginį formatavimą (šiame pavyzdyje A4: F19).
- Spustelėkite skirtuką Pagrindinis.
- Grupėje Stiliai spustelėkite Sąlyginis formatavimas.
- Išskleidžiamajame meniu spustelėkite Nauja taisyklė.
- Dialogo lange „Nauja formatavimo taisyklė“ spustelėkite parinktį „Naudoti formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
- Įveskite šią formulę: = A4 = 1 USD
- Spustelėkite mygtuką „Formatuoti …“.
- Nurodykite formatą (norėdami paryškinti ieškomą raktinį žodį atitinkančius langelius).
- Spustelėkite Gerai.
Dabar įveskite bet ką į langelį B1 ir paspauskite „Enter“. Jis paryškins atitinkamas duomenų rinkinio ląsteles, kuriose yra raktinis žodis B1.
Kaip tai veikia?
Sąlyginis formatavimas taikomas, kai jame nurodyta formulė grąžina TRUE.
Pirmiau pateiktame pavyzdyje mes patikriname kiekvieną langelį naudodami formulę = A4 = 1 USD
Sąlyginis formatavimas patikrina kiekvieną langelį ir patikrina, ar langelio turinys yra toks pat kaip B1 langelyje. Jei jis tas pats, formulė grąžina TRUE ir langelis bus paryškintas. Jei tai nėra tas pats, formulė grąžina FALSE ir nieko neįvyksta.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą ir sekite toliau.
Ieškokite ir paryškinkite eilutes su atitinkančiais duomenimis
Jei norite paryškinti visą eilutę, o ne tik atitinkančius langelius, tai galite padaryti šiek tiek pakoreguodami formulę.
Žemiau pateikiamas pavyzdys, kai visa eilutė paryškinama, jei produkto tipas atitinka B1 langelyje nurodytą.
Štai veiksmai, skirti ieškoti ir paryškinti visą eilutę:
- Pasirinkite duomenų rinkinį, kuriam norite taikyti sąlyginį formatavimą (šiame pavyzdyje A4: F19).
- Spustelėkite skirtuką Pagrindinis.
- Grupėje Stiliai spustelėkite Sąlyginis formatavimas.
- Išskleidžiamajame meniu spustelėkite Nauja taisyklė.
- Dialogo lange „Nauja formatavimo taisyklė“ spustelėkite parinktį „Naudoti formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
- Įveskite šią formulę: = $ B4 = $ B $ 1
- Spustelėkite mygtuką „Formatuoti …“.
- Nurodykite formatą (norėdami paryškinti ieškomą raktinį žodį atitinkančius langelius).
- Spustelėkite Gerai.
Pirmiau minėti veiksmai ieškos nurodyto elemento duomenų rinkinyje, o jei jis ras atitinkantį elementą, jis paryškins visą eilutę.
Atminkite, kad taip bus tikrinamas elementų stulpelis. Jei čia įvesite pardavimo atstovo pavadinimą, jis neveiks. Jei norite, kad jis veiktų pardavimo atstovo pavadinime, turite pakeisti formulę į = $ C4 = $ B $ 1
Pastaba: Priežastis, dėl kurios ji paryškina visą eilutę, o ne tik atitinkantį langelį, yra ta, kad prieš stulpelio nuorodą naudojome $ ženklą ($ B4). Dabar, kai sąlyginis formatavimas analizuoja eilutės langelius, jis patikrina, ar tos eilutės B stulpelio vertė yra lygi B1 langelio vertei. Taigi net tada, kai jis analizuoja A4, B4 arba C4 ir pan., Jis tikrina tik B4 vertę (nes B stulpelį užrakinome naudodami dolerio ženklą).
Daugiau apie absoliučias, santykines ir mišrias nuorodas galite perskaityti čia.
Ieškoti ir paryškinti eilutes (remiantis daline atitiktimi)
Kai kuriais atvejais galite paryškinti eilutes pagal dalinę atitiktį.
Pvz., Jei turite tokių elementų kaip „White Board“, „Green Board“ ir „Grey Board“ ir norite juos visus paryškinti pagal žodį „Board“, tai galite padaryti naudodami funkciją „SEARCH“.
Kažkas, kaip parodyta žemiau:
Štai žingsniai, kaip tai padaryti:
- Pasirinkite duomenų rinkinį, kuriam norite taikyti sąlyginį formatavimą (šiame pavyzdyje A4: F19).
- Spustelėkite skirtuką Pagrindinis.
- Grupėje Stiliai spustelėkite Sąlyginis formatavimas.
- Išskleidžiamajame meniu spustelėkite Nauja taisyklė.
- Dialogo lange „Nauja formatavimo taisyklė“ spustelėkite parinktį „Naudoti formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
- Įveskite šią formulę: = IR ($ B $ 1 ””, ISNUMBER (PAIEŠKA ($ B $ 1, B4 $)))
- Spustelėkite mygtuką „Formatuoti …“.
- Nurodykite formatą (norėdami paryškinti ieškomą raktinį žodį atitinkančius langelius).
- Spustelėkite Gerai.
Kaip tai veikia?
- Funkcija SEARCH ieško paieškos eilutės/raktinio žodžio visuose eilutės langeliuose. Jei paieškos raktinis žodis nerastas, pateikiama klaida, o radus atitiktį - skaičius.
- Funkcija ISNUMBER klaidą paverčia FALSE, o skaitines reikšmes - TRUE.
- IR funkcija patikrina, ar nėra papildomos sąlygos - kad langelis C2 neturėtų būti tuščias.
Taigi dabar, kai įvedate raktinį žodį į langelį B1 ir paspaudžiate „Enter“, jis paryškina visas eilutes, kuriose yra langelių, kuriuose yra tas raktinis žodis.
Premijos patarimas: Jei norite, kad paieškos ir didžiosios raidės būtų jautrios, naudokite FIND funkciją, o ne SEARCH.
Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą ir sekite toliau.
Dinaminė paieška ir paryškinimo funkcija (svarbiausi elementai rašant)
Naudodamiesi tomis pačiomis aukščiau aprašytomis sąlyginio formatavimo gudrybėmis, taip pat galite žengti žingsnį toliau ir padaryti ją dinamišką.
Pavyzdžiui, galite sukurti paieškos juostą, kurioje atitinkami duomenys bus paryškinti, kai rašote paieškos juostoje.
Kažkas, kaip parodyta žemiau:
Tai galima padaryti naudojant „ActiveX“ valdiklius ir tai gali būti gera funkcija kuriant ataskaitas ar informacijos suvestines.
Žemiau yra vaizdo įrašas, kuriame parodau, kaip tai sukurti:
Ar jums pasirodė ši pamoka naudinga? Leiskite man žinoti savo mintis komentarų skiltyje.
Jums taip pat gali patikti šios „Excel“ pamokos:
- Dinaminis „Excel“ filtras - ištraukia duomenis įvedant tekstą.
- Sukurkite išskleidžiamąjį sąrašą su paieškos pasiūlymu.
- Šilumos žemėlapio kūrimas „Excel“.
- Paryškinkite eilutes pagal langelio vertę „Excel“.
- „Excel“ duomenų diapazone paryškinkite aktyvią eilutę ir stulpelį.
- Kaip paryškinti tuščias ląsteles „Excel“.
