Ši pamoka parodys, kaip sukurti „Excel“ diagramas su sąlyginiu formatavimu visose „Excel“ versijose: 2007, 2010, 2013, 2016 ir 2022.
Sąlyginis formatavimas yra praktika, kai pagal nurodytus kriterijus (sąlygas) priskiriamas pasirinktinis formatavimas „Excel“ langeliams-spalva, šriftas ir kt. Ši funkcija padeda analizuoti duomenis, rasti statistiškai reikšmingas vertes ir nustatyti tam tikro duomenų rinkinio modelius.
Deja, „Excel“ nesiūlo jokių integruotų įrankių, skirtų sąlyginiam formatavimui taikyti „Excel“ diagramose.
Tačiau su lašeliu skaičiuoklės magijos jokia problema nėra neįveikiama. Šioje pamokoje sužinosite, kaip sukurti dinaminę diagramą su sąlyginiu formatavimu, kur nubraižyti duomenų taškai automatiškai suformatuoti pagal jų atitinkamas faktines vertes.
Kaip pavyzdį pažiūrėkite į šią stulpelių diagramą, suskirstydami pardavimų vadovo rezultatus per aštuonis mėnesius. Atkreipkite dėmesį, kaip stulpelio spalva automatiškai prisitaiko, atsižvelgiant į jo vertę:
Darbo pradžia
Apsvarstykite šį duomenų rinkinio pavyzdį, kuriame yra pardavimo našumo duomenų:
Prieš pradėdami, perkelkite duomenų rinkinį taip, kad virš lentelės būtų trys tuščios eilutės su neapdorotais duomenimis. Eilutės bus naudojamos sąlyginio formatavimo taisyklėms nustatyti.
1 žingsnis: Paruoškite diagramos duomenis.
Kad viskas veiktų, prie lygties turite pridėti keletą papildomų diagramos duomenų. Kad būtų patogiau, pažiūrėkite, kaip diagramos duomenys turėtų atrodyti pasirengimo etapo pabaigoje:
Pirmasis mūsų žingsnis yra apibrėžti ribines reikšmes (sąlygas), kurios turėtų suskirstyti faktines vertes į kelias kategorijas, kad būtų galima jas pavaizduoti kaip atskiras duomenų eilutes.
Pavyzdžiui, iš aukščiau esančios ekrano kopijos galite matyti, kad tam tikro mėnesio pardavimo skaičiai turi būti 90 000–150 000 USD, kad būtų laikomi patenkinamais.
Norėdami sukurti taisykles, naudokite tris tuščias eilutes virš duomenų rinkinio:
- 1 eilutė (etiketė): Naudokite šiuos langelius žymėdami formatavimo taisykles.
- 2 eilutė (minimali vertė): Šios eilutės reikšmės apibrėžia apatinę kiekvienos sąlygos ribą.
- 3 eilutė (maks. Vertė): Panašiai šios vertės nustato viršutines ribas.
Taikydami šį metodą, galite sukurti tiek daug sąlyginio formatavimo taisyklių, kiek norite-tiesiog įsitikinkite, kad verčių diapazonai nesutampa.
Kurdami diagramą, kurkite tinkintus diagramos legendos elementus, kurie suteiks diagramos kontekstą. Įveskite šią formulę C4 ir nukopijuokite jį į D4 vilkdami užpildymo rankeną:
| 1 | = C1 & ": nuo" & TEXT (C2, "$#, ##") & "iki" & TEXT (C3, "$#, ##") |
Formulė sujungia dailiai atrodančią dinaminę etiketę, pagrįstą anksčiau nustatytomis sąlyginio formatavimo taisyklėmis. Funkcija TEXT formato reikšmes kaip valiutą. Bet jei jūsų duomenų tipas skiriasi, naudokite šią formulę:
| 1 | = C1 & ": nuo" & TEXT (C2, "#, ##") & "iki" & TEXT (C3, "#, ##") |
Arba tai, kai dirbate su procentais:
| 1 | = C1 & ": nuo" & TEXT (C2, "#%") & "iki" & TEXT (C3, "#%") |
Galiausiai raskite diagramos vertes. Įveskite šią formulę C5, nukopijuokite jį į C12 ir į atitinkamas ląsteles D stulpelis:
| 1 | = IF (IR ($ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Formulė lygina tam tikrą faktinę vertę B stulpelis prieš nurodytas ribines vertes, naudojant IF ir AND funkcijas. Jei vertė patenka į diapazoną, ji nukopijuojama į šį stulpelį. Priešingu atveju formulė grąžina klaidą #N/A, kad ta pati vertė nebūtų pavaizduota kelis kartus.
2 žingsnis: nustatykite stulpelių diagramą.
Surinkę visus diagramos duomenis, kaip alternatyvą nustatykite paprastą stulpelinę diagramą arba juostinę diagramą:
- Pažymėkite visus diagramos duomenis, išskyrus stulpelius, kuriuose yra faktinės vertės ir taisyklės, laikydami nuspaudę Ctrl Raktas (A4: A12 ir C4: D12).
- Eikite į Įdėti skirtuką.
- Pasirinkite „Įterpti stulpelį arba juostinę diagramą.”
- Pasirinkite „Sugrupuota stulpelis/grupuota juosta.”
Dėl to gausite diagramą su duomenų taškais, sudarytomis pagal sąlyginio formato taisykles-kaip ir žadėta:
3 veiksmas: pakeiskite sutapimo ir atotrūkio pločio reikšmes.
Prieš uždarydami šią mokymo programą, išspręskite netinkamų stulpelių, atsiradusių dėl nematomų tuščių verčių (klaidų #N/A), problemą diagramoje.
Dešiniuoju pelės mygtuku spustelėkite bet kurį stulpelį ir pasirinkite „Formatuoti duomenų seriją“Iš pasirodžiusio kontekstinio meniu.
Užduočių srityje pakeiskite stulpelių vietą ir plotį:
- Perjunkite į Serijos parinktys skirtuką.
- Keisti “Serijos sutapimas"Iki"100%.”
- Nustatyti Spragos plotis į „60%.”
4 žingsnis: sureguliuokite spalvų schemą.
Galiausiai pridėkite paskutinius akcentus. Neuždarius Formatuoti duomenų seriją užduočių srityje, pakeiskite diagramos spalvų schemą:
- Eikite į Užpildymas ir eilutė skirtuką.
- Pagal "Užpildykite, "Pasirinkti"Tvirtas užpildas.”
- Spustelėkite „Užpildo spalva“Piktogramą ir pasirinkite žalias iš spalvų paletės.
Atvykę perdažykite kitas duomenų serijas, pakeiskite diagramos pavadinimą ir visiškai dinamiška diagrama su sąlyginiu formatavimu yra paruošta naudoti!
