Skatieties video - meklējiet un iezīmējiet datus, izmantojot nosacītu formatējumu
Ja strādājat ar lielām datu kopām, var būt nepieciešams izveidot meklēšanas funkcionalitāti, kas ļauj ātri izcelt meklētā vienuma šūnas/rindas.
Lai gan programmā Excel nav tieša veida, kā to izdarīt, varat izveidot meklēšanas funkcionalitāti, izmantojot nosacīto formatējumu.
Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk (attēlā). Tajā ir slejas produkta nosaukumam, tirdzniecības pārstāvim un valstij.
Tagad varat izmantot nosacījuma formatējumu, lai meklētu atslēgvārdu (ievadot to šūnā C2) un iezīmētu visas šūnas, kurās ir šis atslēgvārds.
Kaut kas, kā parādīts zemāk (kur šūnā B2 ievadu vienuma nosaukumu un nospiedu taustiņu Enter, visa rinda tiek izcelta):
Šajā apmācībā es parādīšu, kā izveidot šo meklēšanas un izcelt funkciju programmā Excel.
Vēlāk apmācībā mēs nedaudz pavirzīsimies uz priekšu un redzēsim, kā to padarīt dinamisku (lai tas izceltos, kamēr rakstāt meklēšanas lodziņā).
Noklikšķiniet šeit, lai lejupielādētu parauga failu un seko līdzi.
Meklējiet un iezīmējiet atbilstošās šūnas
Šajā sadaļā. Es parādīšu, kā meklēt un izcelt tikai atbilstošās šūnas datu kopā.
Kaut kas, kā parādīts zemāk:
Tālāk ir norādītas darbības, lai meklētu un iezīmētu visas šūnas, kurām ir atbilstošais teksts.
- Atlasiet datu kopu, kurai vēlaties lietot nosacīto formatējumu (šajā piemērā A4: F19).
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
- Nolaižamajās opcijās noklikšķiniet uz Jauns noteikums.
- Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz opcijas “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
- Ievadiet šādu formulu: = A4 = 1 ASV dolārs
- Noklikšķiniet uz pogas “Formatēt…”.
- Norādiet formatējumu (lai izceltu šūnas, kas atbilst meklētajam atslēgvārdam).
- Noklikšķiniet uz Labi.
Tagad ierakstiet jebko šūnā B1 un nospiediet enter. Tas iezīmēs atbilstošās šūnas datu kopā, kas satur atslēgvārdu B1.
Kā tas darbojas?
Nosacītā formatēšana tiek lietota ikreiz, kad tajā norādītā formula atgriež vērtību TRUE.
Iepriekš minētajā piemērā mēs pārbaudām katru šūnu, izmantojot formulu = A4 = $ B $ 1
Nosacījuma formatēšana pārbauda katru šūnu un pārbauda, vai šūnas saturs ir tāds pats kā šūnā B1. Ja tas ir vienāds, formula atgriež TRUE un šūna tiek izcelta. Ja tas nav tas pats, formula atgriež FALSE un nekas nenotiek.
Noklikšķiniet šeit, lai lejupielādētu parauga failu un seko līdzi.
Meklēt un izcelt rindas ar atbilstošiem datiem
Ja vēlaties izcelt visu rindu, nevis tikai atbilstošās šūnas, varat to izdarīt, nedaudz pielāgojot formulu.
Tālāk ir sniegts piemērs, kurā tiek izcelta visa rinda, ja produkta veids atbilst šūnā B1 norādītajam.
Tālāk ir norādītas visas rindas meklēšanas un izcelšanas darbības.
- Atlasiet datu kopu, kurai vēlaties lietot nosacīto formatējumu (šajā piemērā A4: F19).
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
- Nolaižamajās opcijās noklikšķiniet uz Jauns noteikums.
- Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz opcijas “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
- Ievadiet šādu formulu: = $ B4 = $ B $ 1
- Noklikšķiniet uz pogas “Formatēt…”.
- Norādiet formatējumu (lai izceltu šūnas, kas atbilst meklētajam atslēgvārdam).
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības meklētu norādīto vienumu datu kopā, un, ja tas atrod atbilstošo vienumu, tas izceļ visu rindu.
Ņemiet vērā, ka tas pārbaudīs tikai vienumu sleju. Ja šeit ievadāt tirdzniecības pārstāvja vārdu, tas nedarbosies. Ja vēlaties, lai tas darbotos pārdošanas pārstāvja vārdā, formula jāmaina uz = $ C4 = $ B $ 1
Piezīme. Iemesls, kāpēc tiek izcelta visa rinda, nevis tikai atbilstošā šūna, ir tas, ka pirms kolonnas atsauces esam izmantojuši zīmi $ ($ B4). Tagad, kad nosacījuma formatēšana analizē šūnas pēc kārtas, tā pārbauda, vai šīs rindas B kolonnas vērtība ir vienāda ar vērtību šūnā B1. Tātad, pat analizējot A4 vai B4 vai C4 un tā tālāk, tā pārbauda tikai B4 vērtību (jo mēs esam bloķējuši kolonnu B, izmantojot dolāra zīmi).
Vairāk par absolūtām, relatīvām un jauktajām atsaucēm varat lasīt šeit.
Meklēt un izcelt rindas (pamatojoties uz daļēju atbilstību)
Dažos gadījumos, iespējams, vēlēsities izcelt rindas, pamatojoties uz daļēju atbilstību.
Piemēram, ja jums ir tādi elementi kā baltā tāfele, zaļā tāfele un pelēkā tāfele, un jūs vēlaties tos visus izcelt, pamatojoties uz vārdu tāfele, varat to izdarīt, izmantojot funkciju MEKLĒT.
Kaut kas, kā parādīts zemāk:
Lai to izdarītu, veiciet tālāk norādītās darbības.
- Atlasiet datu kopu, kurai vēlaties lietot nosacīto formatējumu (šajā piemērā A4: F19).
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
- Nolaižamajās opcijās noklikšķiniet uz Jauns noteikums.
- Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz opcijas “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
- Ievadiet šādu formulu: = UN ($ B $ 1 ””, ISNUMBER (MEKLĒT ($ B $ 1, B4 $)))
- Noklikšķiniet uz pogas “Formatēt…”.
- Norādiet formatējumu (lai izceltu šūnas, kas atbilst meklētajam atslēgvārdam).
- Noklikšķiniet uz Labi.
Kā tas darbojas?
- Funkcija SEARCH meklē meklēšanas virkni/atslēgvārdu visās šūnās pēc kārtas. Tas atgriež kļūdu, ja meklēšanas atslēgvārds netiek atrasts, un atgriež skaitli, ja tas atrod atbilstību.
- Funkcija ISNUMBER pārvērš kļūdu par FALSE un skaitliskās vērtības par TRUE.
- UN funkcija pārbauda papildu nosacījumu - vai šūna C2 nedrīkst būt tukša.
Tāpēc tagad, kad šūnā B1 ierakstāt atslēgvārdu un nospiediet taustiņu Enter, tas izceļ visas rindas, kurās ir šūnas, kurās ir šis atslēgvārds.
Bonusa padoms: Ja vēlaties, lai meklēšanas burtu reģistrjutība būtu jūtīga, MEKLĒŠANAS vietā izmantojiet funkciju MEKLĒT.
Noklikšķiniet šeit, lai lejupielādētu parauga failu un seko līdzi.
Dinamiska meklēšana un izcelšanas funkcionalitāte (izceļ, rakstot)
Izmantojot tos pašus nosacījumu formatēšanas trikus, kas aprakstīti iepriekš, jūs varat arī spert soli tālāk un padarīt to dinamisku.
Piemēram, varat izveidot meklēšanas joslu, kurā, ierakstot meklēšanas joslā, tiek izcelti atbilstošie dati.
Kaut kas, kā parādīts zemāk:
To var izdarīt, izmantojot ActiveX vadīklas, un tā var būt laba funkcionalitāte, ko izmantot, veidojot atskaites vai informācijas paneļus.
Zemāk ir video, kurā parādīts, kā to izveidot:
Vai šī apmācība jums šķita noderīga? Ļaujiet man zināt savas domas komentāru sadaļā.
Jums varētu patikt arī šādas Excel apmācības:
- Dinamiskais Excel filtrs - ieraksta datus, rakstot.
- Izveidojiet nolaižamo sarakstu ar meklēšanas ieteikumiem.
- Siltuma kartes izveide programmā Excel.
- Iezīmējiet rindas, pamatojoties uz šūnas vērtību programmā Excel.
- Iezīmējiet aktīvo rindu un kolonnu datu diapazonā programmā Excel.
- Kā izcelt tukšās šūnas programmā Excel.
