Iezīmējiet rindas, pamatojoties uz šūnas vērtību programmā Excel (nosacīta formatēšana)

Skatieties video - izceliet rindas, pamatojoties uz šūnu vērtībām programmā Excel

Ja vēlaties lasīt rakstiskas instrukcijas, tālāk ir sniegta apmācība.

Nosacītā formatēšana ļauj formatēt šūnu (vai šūnu diapazonu), pamatojoties uz tajā esošo vērtību.

Bet dažreiz tā vietā, lai tikai izceltu šūnu, iespējams, vēlēsities izcelt visu rindu (vai kolonnu), pamatojoties uz vienas šūnas vērtību.

Tālāk ir sniegta datu kopa, kurā esmu izcēlis visas rindas, kurās pārdošanas pārstāvja vārds ir Bobs.

Šajā apmācībā es parādīšu, kā izcelt rindas, pamatojoties uz šūnas vērtību, izmantojot nosacītu formatējumu, izmantojot dažādus kritērijus.

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Iezīmējiet rindas, pamatojoties uz teksta kritērijiem

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties izcelt visus ierakstus, kur pārdošanas pārstāvja vārds ir Bobs.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atlasiet visu datu kopu (šajā piemērā A2: F17).
2. Noklikšķiniet uz cilnes Sākums.
3. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
4. Noklikšķiniet uz “Jauni noteikumi”.
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
6. Formulas laukā ievadiet šādu formulu: = $ C2 = “Bobs”
7. Noklikšķiniet uz pogas "Formatēt".
8. Atvērtajā dialoglodziņā iestatiet krāsu, kādā vēlaties iezīmēt rindu.
9. Noklikšķiniet uz Labi.

Tas iezīmēs visas rindas, kurās tirdzniecības pārstāvja vārds ir “Bob”.

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Kā tas darbojas?

Nosacījuma formatēšana pārbauda katru šūnu, lai pārbaudītu mūsu norādīto nosacījumu, kas ir = $ C2 = “Bobs”

Tātad, analizējot katru A2 rindas šūnu, tā pārbaudīs, vai šūnas C2 nosaukums ir Bobs vai nē. Ja tas notiek, šī šūna tiek izcelta, pretējā gadījumā tā nav.

Ņemiet vērā, ka triks šeit ir izmantot dolāra zīmi ($) pirms kolonnu alfabēta ($ C1). To darot, mēs esam bloķējuši kolonnu, lai tā vienmēr būtu C. Tātad, pat ja šūnai A2 tiek pārbaudīta formula, tā pārbaudīs C2, un, ja A3 tiek pārbaudīts nosacījums, tā pārbaudīs C3.

Tas ļauj mums izcelt visu rindu ar nosacījuma formatējumu.

Iezīmējiet rindas, pamatojoties uz skaitļu kritērijiem

Iepriekš minētajā piemērā mēs redzējām, kā pārbaudīt nosaukumu un izcelt visu rindu.

Mēs varam izmantot to pašu metodi, lai pārbaudītu arī skaitliskās vērtības un iezīmētu rindas, pamatojoties uz nosacījumu.

Pieņemsim, ka man ir tādi paši dati (kā parādīts zemāk), un es vēlos izcelt visas rindas, kurās daudzums ir lielāks par 15.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atlasiet visu datu kopu (šajā piemērā A2: F17).
2. Noklikšķiniet uz cilnes Sākums.
3. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
4. Noklikšķiniet uz “Jauni noteikumi”.
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
6. Formulas laukā ievadiet šādu formulu: = $ D2> = 15
7. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu, kādā vēlaties iezīmēt rindu.
8. Noklikšķiniet uz Labi.

Tas iezīmēs visas rindas, kurās daudzums ir lielāks vai vienāds ar 15.

Līdzīgi mēs to varam izmantot arī, lai noteiktu datuma kritērijus.

Piemēram, ja vēlaties izcelt visas rindas, kurās datums ir pēc 2021. – 2022. Gada 10. jūlija, varat izmantot tālāk norādīto datuma formulu.

= $ A2> DATE (2018,7,10)

Izcelt rindas, pamatojoties uz vairākiem kritērijiem (UN/VAI)

Varat arī izmantot vairākus kritērijus, lai iezīmētu rindas, izmantojot nosacījuma formatējumu.

Piemēram, ja vēlaties izcelt visas rindas, kurās tirdzniecības pārstāvja vārds ir “Bobs” un daudzums ir lielāks par 10, varat to izdarīt, veicot šādas darbības:

1. Atlasiet visu datu kopu (šajā piemērā A2: F17).
2. Noklikšķiniet uz cilnes Sākums.
3. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
4. Noklikšķiniet uz “Jauni noteikumi”.
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
6. Formulas laukā ievadiet šādu formulu: = UN ($ C2 = ”Bob”, $ D2> 10)
7. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu, kādā vēlaties iezīmēt rindu.
8. Noklikšķiniet uz Labi.

Šajā piemērā tiek izceltas tikai tās rindas, kurās ir izpildīti abi nosacījumi (tas tiek darīts, izmantojot formulu UN).

Līdzīgi varat izmantot arī nosacījumu VAI. Piemēram, ja vēlaties izcelt rindas, kurās pārdošanas pārstāvis ir Bobs vai daudzums ir lielāks par 15, varat izmantot šādu formulu:

= VAI ($ C2 = "Bob", $ D2> 15)

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Iezīmējiet rindas dažādās krāsās, pamatojoties uz vairākiem apstākļiem

Dažreiz, iespējams, vēlēsities izcelt rindas krāsā, pamatojoties uz stāvokli.

Piemēram, jūs varat izcelt visas rindas, kurās daudzums ir lielāks par 20, zaļā krāsā un kur daudzums ir lielāks par 15 (bet mazāks par 20) oranžā krāsā.

Lai to izdarītu, jums ir jāizveido divi nosacījuma formatēšanas noteikumi un jāiestata prioritāte.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atlasiet visu datu kopu (šajā piemērā A2: F17).
2. Noklikšķiniet uz cilnes Sākums.
3. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
4. Noklikšķiniet uz “Jauni noteikumi”.
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
6. Formulas laukā ievadiet šādu formulu: = $ D2> 15
7. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu uz Oranža.
8. Noklikšķiniet uz Labi.
9. Dialoglodziņā “Nosacījuma formatēšanas noteikumu pārvaldnieks” noklikšķiniet uz “Jauns noteikums”.
10. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
11. Formulas laukā ievadiet šādu formulu: = $ D2> 20
12. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu uz zaļu.
13. Noklikšķiniet uz Labi.
14. Noklikšķiniet uz Lietot (vai Labi).

Iepriekš minētās darbības padarītu visas rindas ar daudzumu vairāk nekā 20 zaļā krāsā un rindas ar vairāk nekā 15 (bet mazāk par 20 oranžā krāsā).

Izpratne par noteikumu kārtību:

Izmantojot vairākus nosacījumus, ir svarīgi pārliecināties, vai nosacījumu secība ir pareiza.

Iepriekš minētajā piemērā zaļās krāsas stāvoklis ir virs oranžās krāsas nosacījuma.

Ja tas notiek otrādi, visas rindas būtu iekrāsotas tikai oranžā krāsā.

Kāpēc?

Tā kā rinda, kurā daudzums ir lielāks par 20 (teiksim, 23), atbilst abiem mūsu nosacījumiem (= $ D2> 15 un = $ D2> 20). Un tā kā oranžais stāvoklis ir augšpusē, tam tiek dota priekšroka.

Jūs varat mainīt nosacījumu secību, izmantojot pogas Pārvietot uz augšu/uz leju.

Noklikšķiniet šeit lai lejupielādētu parauga failu un sekotu tam.

Iezīmējiet rindas, kur jebkura šūna ir tukša

Ja vēlaties izcelt visas rindas, kurās kāda no šūnām ir tukša, jums jāpārbauda katra šūna, izmantojot nosacīto formatējumu.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atlasiet visu datu kopu (šajā piemērā A2: F17).
2. Noklikšķiniet uz cilnes Sākums.
3. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
4. Noklikšķiniet uz “Jauni noteikumi”.
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
6. Formulas laukā ievadiet šādu formulu: = COUNTIF ($ A2: $ F2, ””)> 0
7. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu uz Oranža.
8. Noklikšķiniet uz Labi.

Iepriekš minētā formula skaita tukšo šūnu skaitu. Ja rezultāts ir lielāks par 0, tas nozīmē, ka šajā rindā ir tukšas šūnas.

Ja kāda no šūnām ir tukša, tā izceļ visu rindu.

Iezīmējiet rindas, pamatojoties uz nolaižamo izvēli

Līdz šim apskatītajos piemēros visi nosacījumi tika norādīti ar nosacījuma formatēšanas dialoglodziņu.

Šajā apmācības daļā es parādīšu, kā to padarīt dinamisku (lai jūs varētu ievadīt nosacījumu šūnā programmā Excel un tā automātiski iezīmēs rindas, pamatojoties uz to).

Tālāk ir sniegts piemērs, kurā nolaižamajā izvēlnē es izvēlos vārdu un visas rindas ar šo nosaukumu tiek izceltas:

Lai to izveidotu, veiciet tālāk norādītās darbības.

1. Izveidojiet nolaižamo sarakstu šūnā A2. Šeit es esmu izmantojis tirdzniecības pārstāvja vārdus, lai izveidotu nolaižamo sarakstu. Šeit ir detalizēts ceļvedis par to, kā programmā Excel izveidot nolaižamo sarakstu.
2. Atlasiet visu datu kopu (šajā piemērā C2: H17).
3. Noklikšķiniet uz cilnes Sākums.
4. Grupā Stili noklikšķiniet uz Nosacījuma formatēšana.
5. Noklikšķiniet uz “Jauni noteikumi”.
6. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
7. Formulas laukā ievadiet šādu formulu: = $ E2 = $ A $ 2
8. Noklikšķiniet uz pogas "Formatēt". Atvērtajā dialoglodziņā iestatiet krāsu uz Oranža.
9. Noklikšķiniet uz Labi.

Tagad, kad nolaižamajā izvēlnē atlasāt jebkuru vārdu, tas automātiski izceļ rindas, kurās nosaukums ir tāds pats kā nolaižamajā izvēlnē.

Vai vēlaties uzzināt vairāk par to, kā meklēt un izcelt programmā Excel? Pārbaudiet zemāk esošos videoklipus.