Vaadake videot - otsige ja tõstke esile andmeid tingimusliku vormindamise abil
Kui töötate suurte andmekogumitega, võib tekkida vajadus luua otsingufunktsioon, mis võimaldab otsitava termini lahtreid/ridu kiiresti esile tõsta.
Kuigi Excelis pole selleks otsest võimalust, saate otsingufunktsioone luua tingimusvormingu abil.
Oletame näiteks, et teil on andmestik, nagu allpool näidatud (pildil). Sellel on veerud toote nime, müügiesindaja ja riigi jaoks.
Nüüd saate kasutada tingimusvormingut märksõna otsimiseks (sisestades selle lahtrisse C2) ja tõsta esile kõik lahtrid, millel on see märksõna.
Midagi, nagu allpool näidatud (kui sisestan lahtrisse B2 üksuse nime ja vajutan sisestusklahvi, tõstetakse kogu rida esile):
Selles õpetuses näitan teile, kuidas seda otsingut Excelis esile tõsta ja esile tõsta.
Hiljem õpetuses läheme natuke kaugemale ja vaatame, kuidas muuta see dünaamiliseks (nii et see tõuseb esile otsingukasti sisestamise ajal).
Näidisfaili allalaadimiseks klõpsake siin ja järgige.
Otsige ja tõstke esile sobivaid lahtreid
Selles jaotises. Näitan teile, kuidas otsida ja esile tõsta ainult andmestiku sobivaid lahtreid.
Midagi, nagu allpool näidatud:
Siin on sammud kõigi lahtrite otsimiseks ja esiletõstmiseks, millel on vastav tekst:
- Valige andmekogum, millele soovite tingimusvormingut rakendada (selles näites A4: F19).
- Klõpsake vahekaarti Avaleht.
- Klõpsake rühmas Stiilid tingimusvormingut.
- Klõpsake rippmenüüst valikut Uus reegel.
- Klõpsake dialoogiboksis „Uus vormindamisreegel” valikut „Kasuta vormingut, et määrata vormindatavad lahtrid”.
- Sisestage järgmine valem: = A4 = $ B $ 1
- Klõpsake nuppu „Vorminda…”.
- Määrake vorming (otsitud märksõnale vastavate lahtrite esiletõstmiseks).
- Klõpsake nuppu OK.
Nüüd tippige lahtrisse B1 kõik ja vajutage sisestusklahvi. See tõstab esile andmekogumis sobivad lahtrid, mis sisaldavad märksõna B1 -s.
Kuidas see töötab?
Tingimuslikku vormingut rakendatakse alati, kui selles määratud valem tagastab väärtuse TRUE.
Ülaltoodud näites kontrollime iga lahtrit valemi abil = A4 = $ B $ 1
Tingimuslik vormindamine kontrollib iga lahtrit ja kinnitab, et lahtri sisu on sama mis lahtris B1. Kui see on sama, tagastab valem tõe ja lahter tõstetakse esile. Kui see pole sama, tagastab valem FALSE ja midagi ei juhtu.
Näidisfaili allalaadimiseks klõpsake siin ja järgige.
Otsige ja tõstke esile vastavate andmetega ridu
Kui soovite ainult sobivate lahtrite asemel esile tõsta kogu rida, saate seda teha, valemit veidi muutes.
Allpool on näide, kus kogu rida tõstetakse esile, kui tooteliik vastab lahtris B1 olevale.
Siin on sammud kogu rea otsimiseks ja esiletõstmiseks.
- Valige andmekogum, millele soovite tingimusvormingut rakendada (selles näites A4: F19).
- Klõpsake vahekaarti Avaleht.
- Klõpsake rühmas Stiilid tingimusvormingut.
- Klõpsake rippmenüüst valikut Uus reegel.
- Klõpsake dialoogiboksis „Uus vormindamisreegel” valikut „Kasuta vormingut, et määrata vormindatavad lahtrid”.
- Sisestage järgmine valem: = $ B4 = $ B $ 1
- Klõpsake nuppu „Vorminda…”.
- Määrake vorming (otsitud märksõnale vastavate lahtrite esiletõstmiseks).
- Klõpsake nuppu OK.
Ülaltoodud sammud otsiksid andmestikust määratud üksust ja kui see leiab sobiva üksuse, tõstab see esile kogu rea.
Pange tähele, et see kontrollib ainult üksuste veergu. Kui sisestate siia müügiesindaja nime, siis see ei tööta. Kui soovite, et see müügiesindaja nime puhul töötaks, peate muutma valemi väärtuseks = $ C4 = $ B $ 1
Märkus. Põhjus, miks see toob esile kogu rea ja mitte ainult sobiva lahtri, on see, et oleme veeruviite eel kasutanud märki $ ($ B4). Nüüd, kui tingimusvorming analüüsib rea lahtreid, kontrollib see, kas selle rea veeru B väärtus on võrdne lahtri B1 väärtusega. Nii et isegi kui see analüüsib A4 või B4 või C4 jne, kontrollib see ainult B4 väärtust (kuna oleme veeru B lukustanud dollarimärgi abil).
Siit saate lugeda absoluutsete, suhteliste ja segatud viidete kohta.
Ridade otsimine ja esiletõstmine (osalise vaste alusel)
Mõnel juhul võiksite osalise vaste alusel read esile tõsta.
Näiteks kui teil on selliseid objekte nagu valge tahvel, roheline tahvel ja hall tahvel ning soovite need kõik esile tõsta sõna Tahvel alusel, saate seda teha funktsiooni OTSI abil.
Midagi, nagu allpool näidatud:
Selleks toimige järgmiselt.
- Valige andmekogum, millele soovite tingimusvormingut rakendada (selles näites A4: F19).
- Klõpsake vahekaarti Avaleht.
- Klõpsake rühmas Stiilid tingimusvormingut.
- Klõpsake rippmenüüst valikut Uus reegel.
- Klõpsake dialoogiboksis „Uus vormindusreegel” valikut „Kasuta vormingut, et määrata, milliseid lahtreid vormindada”.
- Sisestage järgmine valem: = JA ($ B $ 1 ””, ISNUMBER (OTSI ($ B $ 1, $ B4)))
- Klõpsake nuppu „Vorminda…”.
- Määrake vorming (otsitud märksõnale vastavate lahtrite esiletõstmiseks).
- Klõpsake nuppu OK.
Kuidas see töötab?
- Funktsioon SEARCH otsib otsingustringi/märksõna kõigist rea lahtritest. See tagastab vea, kui otsingusõna ei leitud, ja tagastab numbri, kui leiab vaste.
- Funktsioon ISNUMBER teisendab vea väärtuseks FALSE ja arvväärtused TRUE.
- JA funktsioon kontrollib lisatingimust - kas lahter C2 ei tohiks olla tühi.
Nüüd, kui sisestate lahtrisse B1 märksõna ja vajutate sisestusklahvi, tõstab see esile kõik read, millel on selle märksõna sisaldavad lahtrid.
Boonuse näpunäide: Kui soovite otsingurea tõstmise tundlikuks muuta, kasutage otsingu asemel funktsiooni FIND.
Näidisfaili allalaadimiseks klõpsake siin ja järgige.
Dünaamiline otsing ja esiletõstmise funktsioon (tippides tippides)
Kasutades samu ülalkirjeldatud tingimusliku vormindamise nippe, saate ka sammu edasi teha ja muuta selle dünaamiliseks.
Näiteks saate luua otsinguriba, kus vastavad andmed tõstetakse esile otsinguribale kirjutades.
Midagi, nagu allpool näidatud:
Seda saab teha ActiveX -juhtelementide abil ja see võib olla hea funktsioon aruannete või armatuurlaudade loomisel.
Allpool on video, kus näitan, kuidas seda luua:
Kas see õpetus oli teile kasulik? Andke mulle oma mõtted kommentaaride jaotises teada.
Samuti võivad teile meeldida järgmised Exceli õpetused:
- Dünaamiline Exceli filter - ekstraheerib andmed sisestamise ajal.
- Looge otsingusoovitustega ripploend.
- Soojuskaardi loomine Excelis.
- Tõstke Exceli lahtri väärtuse põhjal esile read.
- Tõstke Exceli andmevahemikus esile aktiivne rida ja veerg.
- Kuidas Excelis tühje lahtreid esile tõsta.
