Nosacījuma formatēšana programmā Excel: galīgā rokasgrāmata ar piemēriem

Nosacījuma formatēšana ir viena no vienkāršākajām, bet jaudīgākajām Excel izklājlapu funkcijām.

Kā norāda nosaukums, programmā Excel varat izmantot nosacīto formatējumu, ja vēlaties izcelt šūnas, kas atbilst noteiktam nosacījumam.

Tas ļauj ātri pievienot datu kopai vizuālās analīzes slāni. Izmantojot nosacījuma formatējumu programmā Excel, varat izveidot siltuma kartes, parādīt pieaugošas/samazinošas ikonas, Hārvija burbuļus un daudz ko citu.

Nosacījuma formatēšanas izmantošana programmā Excel (piemēri)

Šajā apmācībā es parādīšu septiņus pārsteidzošus piemērus, kā programmā Excel izmantot nosacīto formatējumu:

• Ātri identificējiet dublikātus, izmantojot Excel nosacīto formatējumu.
• Iezīmējiet šūnas, kuru vērtība ir lielāka/mazāka par skaitli datu kopā.
• Datu kopas augšējo/apakšējo 10 (vai 10%) vērtību izcelšana.
• Kļūdu/tukšu izcelšana, izmantojot Excel nosacīto formatējumu.
• Siltuma karšu izveide, izmantojot nosacīto formatējumu programmā Excel.
• Iezīmējiet katru N rindu/kolonnu, izmantojot nosacīto formatējumu.
• Meklējiet un iezīmējiet, izmantojot Excel nosacīto formatējumu.

1. Ātri identificējiet dublikātus

Nosacījuma formatējumu programmā Excel var izmantot, lai identificētu datu kopas dublikātus.

Tālāk ir norādīts, kā to izdarīt.

• Atlasiet datu kopu, kurā vēlaties izcelt dublikātus.
• Dodieties uz Sākums -> Nosacīta formatēšana -> Šūnu noteikumu izcelšana -> Dublēt vērtības.
• Dialoglodziņā Dublēt vērtības pārliecinieties, vai kreisajā nolaižamajā izvēlnē ir atlasīts Dublikāts. Izmantojot pareizo nolaižamo izvēlni, varat norādīt piemērojamo formātu. Ir daži formāti, kurus varat izmantot vai norādīt savu formātu, izmantojot opciju Pielāgots formāts.
• Noklikšķiniet uz Labi.

Tas uzreiz izceltu visas šūnas, kurām atlasītajā datu kopā ir dublikāts. Jūsu datu kopa var būt vienā kolonnā, vairākās kolonnās vai blakus esošā šūnu diapazonā.

Skatīt arī: Galīgais ceļvedis dublikātu atrašanai un noņemšanai programmā Excel.

2. Iezīmējiet šūnas, kuru vērtība ir lielāka/mazāka par skaitli

Programmā Excel varat izmantot nosacījuma formatējumu, lai ātri iezīmētu šūnas, kurās ir lielākas/mazākas vērtības nekā norādītā vērtība. Piemēram, iezīmējot visas šūnas, kuru pārdošanas vērtība ir mazāka par 100 miljoniem, vai iezīmējot šūnas ar atzīmēm, kas ir mazākas par sliekšņa slieksni.

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

• Atlasiet visu datu kopu.
• Dodieties uz Sākums -> Nosacīta formatēšana -> Šūnu noteikumu izcelšana -> Lielāks par… / Mazāk par…
• Atkarībā no izvēlētās opcijas (lielāka vai mazāka) tiks atvērts dialoglodziņš. Pieņemsim, ka izvēlaties opciju “Lielāks par”. Dialoglodziņā ievadiet numuru laukā pa kreisi. Mērķis ir izcelt šūnas, kuru skaits ir lielāks par šo norādīto.

• Izmantojot nolaižamo izvēlni labajā pusē, norādiet formātu, kas jāpiemēro nosacījumiem atbilstošajām šūnām. Ir daži formāti, kurus varat izmantot vai norādīt savu formātu, izmantojot opciju Pielāgots formāts.
• Noklikšķiniet uz Labi.

Tas uzreiz iezīmētu visas šūnas, kuru vērtības datu kopā ir lielākas par 5.Piezīme. Ja vēlaties izcelt vērtības, kas ir lielākas par 5, vēlreiz izmantojiet nosacījuma formatējumu ar kritēriju “Vienāds ar”.

To pašu procesu var veikt, lai izceltu šūnas, kuru vērtība ir mazāka par norādītajām vērtībām.

3. Iezīmējot augšējo/apakšējo 10 (vai 10%)

Nosacījuma formatēšana programmā Excel var ātri noteikt 10 labākos vienumus vai 10% no datu kopas. Tas varētu būt noderīgi situācijās, kad vēlaties ātri redzēt labākos kandidātus pēc rezultātiem vai lielākajām darījumu vērtībām pārdošanas datos.

Tāpat jūs varat ātri noteikt datu kopas 10 apakšējos vienumus vai 10% apakšējo.

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

• Atlasiet visu datu kopu.
• Dodieties uz Sākums -> Nosacījuma formatēšana -> Augšējie / apakšējie noteikumi -> Top 10 vienumi (vai %) / Apakšējie 10 vienumi (vai %).
• Pamatojoties uz jūsu izvēlēto, tiks atvērts dialoglodziņš. Pieņemsim, ka esat izvēlējies 10 populārākos vienumus, tad tiks atvērts dialoglodziņš, kā parādīts zemāk:
• Izmantojot nolaižamo izvēlni labajā pusē, norādiet formātu, kas jāpiemēro nosacījumiem atbilstošajām šūnām. Ir daži formāti, kurus varat izmantot vai norādīt savu formātu, izmantojot opciju Pielāgots formāts.
• Noklikšķiniet uz Labi.

Tas uzreiz izceltu atlasītās datu kopas 10 populārākos vienumus. Ņemiet vērā, ka tas darbojas tikai šūnām, kurās ir skaitliska vērtība.

Turklāt, ja datu kopā ir mazāk par 10 šūnām un atlasāt opcijas, lai izceltu 10 populārākos vienumus/10 apakšējos vienumus, visas šūnas tiktu iezīmētas.

Šeit ir daži nosacījumu formatēšanas piemēri:

4. Izcelšanas kļūdas/tukšas vietas

Ja programmā Excel strādājat ar daudziem skaitliskiem datiem un aprēķiniem, jūs zināt, cik svarīgi ir identificēt un apstrādāt šūnas, kurās ir kļūdas vai tās ir tukšas. Ja šīs šūnas tiek izmantotas turpmākajos aprēķinos, tas var novest pie kļūdainiem rezultātiem.

Nosacījuma formatēšana programmā Excel var palīdzēt ātri identificēt un izcelt šūnas, kurās ir kļūdas vai tās ir tukšas.

Pieņemsim, ka mums ir datu kopa, kā parādīts zemāk:

Šai datu kopai ir tukša šūna (A4) un kļūdas (A5 un A6).

Tālāk ir norādītas darbības, lai izceltu tukšās vai kļūdainās šūnas.

• Atlasiet datu kopu, kurā vēlaties izcelt tukšās šūnas un šūnas ar kļūdām.
• Dodieties uz Sākums -> Nosacījuma formatēšana -> Jauns noteikums.
• Dialoglodziņā Jauns formatēšanas noteikums atlasiet Izmantot formulu, lai noteiktu, kuras šūnas formatēt.
• Laukā sadaļā “Kārtulas apraksta rediģēšana” ievadiet šādu formulu:
  = VAI (ISBLANK (A1), ISERROR (A1))
  • Iepriekš minētā formula pārbauda visas šūnas, lai konstatētu divus nosacījumus - vai tā ir tukša vai nav un vai tajā ir kļūda. Ja kāds no nosacījumiem ir TRUE, tas atgriež TRUE.
• Iestatiet formātu, kuru vēlaties lietot tukšām vai kļūdainām šūnām. Lai to izdarītu, noklikšķiniet uz pogas Formatēt. Tiks atvērts dialoglodziņš “Formatēt šūnas”, kur varēsit norādīt formātu.
• Noklikšķiniet uz Labi.

Tas uzreiz izceltu visas šūnas, kas ir tukšas vai kurās ir kļūdas.

Piezīme: Formulā nosacījuma formatējumā nav jāizmanto viss diapazons A1: A7. Iepriekš minētā formula izmanto tikai A1. Lietojot šo formulu visam diapazonam, Excel pārbauda vienu šūnu vienlaikus un pielāgo atsauci. Piemēram, pārbaudot A1, tiek izmantota formula = VAI (ISBLANK (A1), ISERROR (A1)). Pārbaudot šūnu A2, tā izmanto formulu = VAI (ISBLANK (A2), ISERROR (A2)). Tas automātiski pielāgo atsauci (jo tās ir relatīvas atsauces) atkarībā no tā, kura šūna tiek analizēta. Tātad jums nav jāraksta atsevišķa formula katrai šūnai. Excel ir pietiekami gudrs, lai pats mainītu šūnu atsauci 🙂

Skatīt arī: IFERROR un ISERROR izmantošana, lai apstrādātu kļūdas programmā Excel.

5. Siltuma karšu izveide

Siltuma karte ir datu vizuāls attēlojums, kur krāsa attēlo šūnas vērtību. Piemēram, varat izveidot siltuma karti, kurā šūna ar visaugstāko vērtību ir iekrāsota zaļā krāsā, un, samazinoties vērtībai, notiek pāreja uz sarkanu krāsu.

Kaut kas, kā parādīts zemāk:

Iepriekš minētajai datu kopai ir vērtības no 1 līdz 100. Šūnas tiek iezīmētas, pamatojoties uz tajā esošo vērtību. 100 iegūst zaļo krāsu, 1 - sarkano.

Tālāk ir norādītas darbības, lai izveidotu siltuma kartes, izmantojot nosacīto formatējumu programmā Excel.

• Atlasiet datu kopu.
• Dodieties uz Sākums -> Nosacītā formatēšana -> Krāsu skalas un izvēlieties vienu no krāsu shēmām.

Tiklīdz jūs noklikšķināt uz siltuma kartes ikonas, tas datu kopai piemēros formatējumu. Ir vairāki krāsu gradienti, no kuriem varat izvēlēties. Ja neesat apmierināts ar esošajām krāsu opcijām, varat atlasīt citus noteikumus un norādīt vēlamo krāsu.

Piezīme. Līdzīgā veidā varat izmantot arī datu bāra un ikonu kopas.

6. Iezīmējiet katru otro rindu/kolonnu

Iespējams, vēlēsities izcelt alternatīvas rindas, lai uzlabotu datu lasāmību.

Tos sauc par zebras līnijām, un tie varētu būt īpaši noderīgi, ja drukājat datus.

Tagad ir divi veidi, kā izveidot šīs zebras līnijas. Ātrākais veids ir pārvērst tabulas datus par Excel tabulu. Tā automātiski pielietoja krāsu alternatīvām rindām. Vairāk par to varat izlasīt šeit.

Vēl viens veids ir izmantot nosacīto formatējumu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Tālāk ir norādītas darbības, lai iezīmētu alternatīvas rindas, izmantojot nosacīto formatējumu programmā Excel.

• Atlasiet datu kopu. Iepriekš minētajā piemērā atlasiet A2: C13 (kas neietver galveni). Ja vēlaties iekļaut arī galveni, atlasiet visu datu kopu.
• Atveriet dialoglodziņu Nosacītā formatēšana (Sākums-> Nosacītā formatēšana-> Jauna kārtula) [Tastatūras saīsne - Alt + O + D].
• Dialoglodziņā atlasiet dialoglodziņu “Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt”.
• Laukā sadaļā “Kārtulas apraksta rediģēšana” ievadiet šādu formulu:
  = ISODD (ROW ())

• Iepriekš minētā formula pārbauda visas šūnas un, ja šūnas ROW numurs ir nepāra, tas atgriež TRUE. Norādītais nosacījuma formāts tiks piemērots visām šūnām, kas atgriež patiesību.
• Iestatiet formātu, kuru vēlaties lietot tukšām vai kļūdainām šūnām. Lai to izdarītu, noklikšķiniet uz pogas Formatēt. Tiks atvērts dialoglodziņš “Formatēt šūnas”, kur varēsit norādīt formātu.
• Noklikšķiniet uz Labi.

Tieši tā! Datu kopas alternatīvās rindas tiks iezīmētas.

Daudzos gadījumos varat izmantot to pašu tehniku. Viss, kas jums jādara, ir izmantot attiecīgo formulu nosacījuma formatējumā. Šeit ir daži piemēri:

• Izcelt alternatīvās pāra rindas: = ISEVEN (ROW ())
• Iezīmējiet alternatīvās pievienošanas rindas: = ISODD (ROW ())
• Iezīmējiet katru trešo rindu: = MOD (ROW (), 3) = 0

7. Meklējiet un iezīmējiet datus, izmantojot nosacīto formatējumu

Tas ir nedaudz uzlabots nosacījumu formatēšanas lietojums. Tas liktu jums izskatīties kā Excel rokzvaigznei.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, ar produktu nosaukumu, tirdzniecības pārstāvi un ģeogrāfiju. Ideja ir ierakstīt virkni šūnā C2, un, ja tā atbilst jebkuras (-u) šūnas (-u) datiem, tad tas ir jāizceļ. Kaut kas, kā parādīts zemāk:

Lai izveidotu šo meklēšanas un izcelšanas funkciju, veiciet tālāk norādītās darbības.

• Atlasiet datu kopu.
• Dodieties uz Sākums -> Nosacījuma formatēšana -> Jauns noteikums (Īsinājumtaustiņš - Alt + O + D).
• Dialoglodziņā Jauns formatēšanas noteikums atlasiet opciju “Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt”.
• Laukā sadaļā “Kārtulas apraksta rediģēšana” ievadiet šādu formulu:
  = UN ($ C $ 2 ””, $ C $ 2 = B5)
• Iestatiet formātu, ko vēlaties lietot tukšām vai kļūdainām šūnām. Lai to izdarītu, noklikšķiniet uz pogas Formatēt. Tiks atvērts dialoglodziņš “Formatēt šūnas”, kur varēsit norādīt formātu.
• Noklikšķiniet uz Labi.

Tieši tā! Tagad, ievadot kaut ko šūnā C2 un nospiežot taustiņu Enter, tas iezīmēs visas atbilstošās šūnas.

Kā tas darbojas?

Nosacījuma formatēšanā izmantotā formula novērtē visas datu kopas šūnas. Pieņemsim, ka ievadāt Japānu šūnā C2. Tagad Excel novērtētu katras šūnas formulu.

Formula šūnai atdotu TRUE, ja ir izpildīti divi nosacījumi:

• Šūna C2 nav tukša.
• Šūnas C2 saturs precīzi sakrīt ar datu kopas šūnas saturu.

Tādējādi tiek izceltas visas šūnas, kurās ir teksts Japāna.

Lejupielādējiet parauga failu

Varat izmantot to pašu loģiku, lai izveidotu šādas variācijas:

• Šūnas vietā iezīmējiet visu rindu.
• Iezīmējiet pat tad, ja ir daļēja atbilstība.
• Rakstīšanas laikā iezīmējiet šūnas/rindas (dinamiska) [Tev patiks šis triks :)].

Kā noņemt nosacīto formatējumu programmā Excel

Pēc nosacījumu formatēšanas tas paliek spēkā, ja vien to nenoņemat manuāli. Labākā prakse ir saglabāt nosacīto formatējumu tikai tām šūnām, kur tas ir nepieciešams.

Tā kā tas ir nestabils, tas var izraisīt lēnu Excel darbgrāmatu.

Lai noņemtu nosacījuma formatējumu:

• Atlasiet šūnas, no kurām vēlaties noņemt nosacījuma formatējumu.
• Dodieties uz Sākums -> Nosacītā formatēšana -> Notīrīt noteikumus -> Notīrīt atlasīto šūnu noteikumus.
  • Ja vēlaties noņemt nosacīto formatējumu no visas darblapas, atlasiet Notīrīt kārtulas no visas lapas.

Svarīgas lietas, kas jāzina par nosacīto formatēšanu programmā Excel

• Nosacījuma formatējums gaistošā. Tas var novest pie lēnas darbgrāmatas. Izmantojiet to tikai tad, kad tas ir nepieciešams.
• Kopējot ielīmēšanas šūnas, kas satur nosacītu formatējumu, tiek kopēta arī nosacītā formatēšana.
• Ja vienai šūnu kopai lietojat vairākas kārtulas, visas kārtulas paliek aktīvas. Jebkuras pārklāšanās gadījumā priekšroka tiek dota pēdējam piemērotajam noteikumam. Tomēr secību var mainīt, mainot secību dialoglodziņā Pārvaldīt kārtulas.