Šī apmācība parādīs, kā izveidot Excel diagrammas ar nosacītu formatējumu visās Excel versijās: 2007, 2010, 2013, 2016 un 2022.
Nosacītā formatēšana ir prakse piešķirt pielāgotu formatējumu Excel šūnām-krāsa, fonts utt., Pamatojoties uz norādītajiem kritērijiem (nosacījumiem). Šī funkcija palīdz analizēt datus, atrast statistiski nozīmīgas vērtības un noteikt modeļus noteiktā datu kopā.
Diemžēl Excel nepiedāvā iebūvētus rīkus nosacītu formatējumu piemērošanai Excel diagrammām.
Tomēr ar pilienu izklājlapu maģijas neviena problēma nav nepārvarama. Šajā apmācībā jūs uzzināsit, kā izveidot dinamisku diagrammu ar nosacītu formatējumu, kur uzzīmētie datu punkti tiek automātiski formatēti, pamatojoties uz to attiecīgajām faktiskajām vērtībām.
Piemēram, apskatiet šo kolonnu diagrammu, sadalot pārdošanas vadītāja sniegumu astoņu mēnešu laikā. Ievērojiet, kā kolonnas krāsa tiek automātiski pielāgota atkarībā no tās pamatvērtības.
Darba sākšana
Apsveriet šo datu kopas paraugu, kas satur pārdošanas rezultātus:
Pirms sākam, pārvietojiet savu datu kopu tā, lai virs tabulas būtu trīs tukšas rindas ar neapstrādātiem datiem. Rindas tiks izmantotas nosacījumu formatēšanas noteikumu iestatīšanai.
1. solis: Sagatavojiet diagrammas datus.
Lai viss darbotos, vienādojumam jāpievieno daži papildu diagrammas dati. Ērtības labad apskatiet, kā diagrammas datiem vajadzētu izskatīties sagatavošanās posma beigās:
Mūsu pirmais solis ir definēt robežvērtības (nosacījumus), kurām faktiskās vērtības jāsadala vairākās kategorijās, ļaujot tās attēlot kā atsevišķas datu sērijas.
Piemēram, no iepriekš redzamā ekrānuzņēmuma var redzēt, ka pārdošanas rādītājiem konkrētajā mēnesī ir jābūt robežās no 90 000 līdz 150 000 USD, lai tos uzskatītu par apmierinošiem.
Lai izveidotu kārtulas, izmantojiet trīs tukšās rindas virs datu kopas:
- 1. rinda (etiķete): Izmantojiet šīs šūnas formatēšanas noteikumu marķēšanai.
- 2. rinda (minimālā vērtība): Šīs rindas vērtības nosaka katra nosacījuma apakšējo robežu.
- 3. rinda (maks. Vērtība): Tāpat šīs vērtības nosaka augšējās robežas.
Izmantojot šo metodi, varat izveidot tik daudz nosacījuma formatēšanas noteikumu, cik vēlaties-tikai pārliecinieties, ka vērtību diapazoni nepārklājas.
Turpinot virzīties uz priekšu, veidojot diagrammu, izveidojiet pielāgotas diagrammas leģendas vienumus, kas nodrošinās diagrammas kontekstu. Ievadiet šo formulu C4 un kopējiet to uz D4 velkot uzpildes rokturi:
| 1 | = C1 & ": no" & TEXT (C2, "$#, ##") & "līdz" & TEXT (C3, "$#, ##") |
Formula apvieno glīti izskatīgu dinamisku etiķeti, pamatojoties uz iepriekš iestatītajiem nosacījuma formatēšanas noteikumiem. Funkcija TEKSTS formatē vērtības kā valūtu. Bet, ja jūsu datu veids atšķiras, izmantojiet šo formulu:
| 1 | = C1 & ": no" & TEXT (C2, "#, ##") & "līdz" & TEXT (C3, "#, ##") |
Vai arī šis, strādājot ar procentiem:
| 1 | = C1 & ": no" & TEXT (C2, "#%") & "līdz" & TEXT (C3, "#%") |
Visbeidzot, atrodiet diagrammas vērtības. Ievadiet šādu formulu C5, nokopējiet to uz C12 un pāri atbilstošajām šūnām kolonna D:
| 1 | = JA (UN (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formulā tiek salīdzināta dotā faktiskā vērtība aile B pretēji norādītajām robežvērtībām, izmantojot funkcijas IF un AND. Ja vērtība ietilpst diapazonā, tā tiek nokopēta šajā slejā. Pretējā gadījumā formula atgriež kļūdu #N/A, lai novērstu to, ka viena un tā pati vērtība tiek attēlota vairākas reizes.
2. solis: iestatiet kolonnu diagrammu.
Apkopojot visus diagrammas datus, kā alternatīvu iestatiet vienkāršu kolonnu diagrammu vai joslu diagrammu:
- Iezīmējiet visus diagrammas datus, izņemot slejas, kurās ir faktiskās vērtības un kārtulas, turot nospiestu Ctrl atslēga (A4: A12 un C4: D12).
- Dodieties uz Ievietot cilni.
- Atlasiet “Ievietojiet kolonnu vai joslu diagrammu.”
- Izvēlieties “Klasteru kolonna/kopu josla.”
Rezultātā jūs iegūsit diagrammu ar datu punktiem, kas uzzīmēti, pamatojoties uz nosacījuma formatēšanas noteikumiem, kā solīts:
3. solis: mainiet pārklāšanās un atstarpes platuma vērtības.
Pirms šīs apmācības slēgšanas novērsiet nepareizi novietoto kolonnu problēmu, ko izraisījušas neredzamās tukšās vērtības (kļūdas #N/A), kas attēlotas diagrammā.
Ar peles labo pogu noklikšķiniet uz jebkuras slejas un izvēlieties “Datu sērijas formāts”Uznirstošajā konteksta izvēlnē.
Uzdevumu rūtī mainiet kolonnu atrašanās vietu un platumu:
- Pārslēdzieties uz Sērijas opcijas cilni.
- Mainīt “Sērijas pārklāšanās"Uz"100%.”
- Iestatiet Gap Width uz "60%.”
4. solis: pielāgojiet krāsu shēmu.
Visbeidzot, pievienojiet pēdējos pieskārienus. Neaizverot Datu sērijas formāts uzdevumrūtī, mainiet diagrammas krāsu shēmu:
- Dodieties uz Fill & Line cilni.
- Zem "Aizpildīt, "Izvēlieties"Ciets pildījums.”
- Noklikšķiniet uz “Aizpildiet krāsu”Ikonu un izvēlieties zaļš no krāsu paletes.
Kad esat tur, pārkrāsojiet citas datu sērijas, mainiet diagrammas nosaukumu, un jūsu pilnībā dinamiskā diagramma ar nosacītu formatējumu ir gatava darbam!
