Sąlyginis formatavimas „Excel“: instrukcijos ir naudojimas

Naudokite šias instrukcijas, kad sukurtumėte įvairius formatus

Sąlyginis „Microsoft Excel“ formatavimas yra naudingas įrankis, padedantis labiau išryškinti lenteles, ataskaitas ar langelius, paryškinti sritį ar kelis stulpelius, analizuoti duomenis, išsiaiškinti skirtumus ar nustatyti tendencijas. Tam naudojamos duomenų juostos, simbolių rinkiniai ar spalvotos skalės. Atsižvelgiant į nurodytas sąlygas, keičiamas langelių rodymas arba formuojamas langelių diapazonas. Daugeliui integruotų sąlygų galite sukurti savo.

Sąlyginis formatavimas yra prasmingas ir jums padeda

Sąlyginis „Excel“ formatavimas gali būti taikomas įvairiais būdais, nesvarbu, ar tai būtų „Excel“ lentelė, ar tam tikras langelių diapazonas, ar „PivotTable“ ataskaita. Jums prieinami įvairūs veiksmai ir formatų šablonai. Lemiamas „Excel“ naudotojų pranašumas yra tas, kad specialios vertės ar rezultatai gali būti paryškinti koduojant spalvas. Jei vertė pasikeičia, formatavimas automatiškai koreguojamas - atsižvelgiant į sąlygas.

info: „PivotTable“ („Pivot Table“) yra speciali lentelė, su kuria galima apskaičiuoti, apibendrinti ir išanalizuoti daug duomenų. Be to, galite greitai atlikti palyginimus ir nustatyti tyrimo duomenų tendencijas.

Sąlyginis formatavimas pagal esamas skaičių, teksto turinio ar datų taisykles

Jei formatuosite naudodami palyginimo operatorių, langelius bus lengviau rasti. Galite padaryti vertes labiau matomas, pavyzdžiui, didesnes ar mažesnes už apibrėžtą reikšmę arba tam tikras vertes, esančias tarp viršutinės ir apatinės ribos. Tai reiškia, kad norimi arba ieškomi duomenys rodomi spalvotai. Galima redaguoti greitą arba išplėstinį formatavimą. Naudodami išplėstinį formatavimą galite dirbti su formulėmis, pavyzdžiui, automatiškai pažymėti „Excel“ stulpelį spalva.

Pavyzdžiai:

• Sandėlio tarnautojas norėtų paryškinti visas atsargas iki 15 vienetų spalvotu fonu.
• Visi miestai, kuriuose gyvena daugiau nei 150 000 gyventojų, turi būti pažymėti lentelėje.
• Gamybos įmonė nori raudonai paryškinti visas prekes, pagamintas nuo 2022-2023–2022 m. Sausio 1 d. Iki 2022-2023–2022 m. Sausio 31 d.

Greitas formatas:

Vienos ar kelių langelių pasirinkimas.

Skirtuke spustelėkite „Pradėti“.

Eikite į rodyklę šalia „Stiliaus“ mygtuko „Sąlyginis formatavimas“.

Spustelėkite „Ląstelių paryškinimo taisyklės“.

Pasirinkite norimą komandą, pvz. B. didesnis nei, mažesnis nei, tarp datos ir kt.

Įveskite reikšmes ir pasirinkite formatą.

Išplėstinis formatavimas:

1. Pasirinkite vieną ar daugiau langelių lentelėje arba „PivotTable“ ataskaitoje.

2. Eikite į skirtuką „Pagrindinis“, grupėje „Stiliai“ spustelėkite rodyklę šalia „Sąlyginis formatavimas“.

3. Spustelėkite „Tvarkyti taisykles“: pasirodys dialogo langas „Sąlyginio formatavimo taisyklių tvarkyklė“.

4. Pasirinkite veiksmą:

   • Spustelėkite „Nauja taisyklė“: pasirodys dialogo langas „Nauja formatavimo taisyklė“.
   • Norėdami pakeisti formatą, įsitikinkite, kad sąrašo laukelyje „Rodyti formatavimo taisyklę“ pasirinktas tinkamas darbalapis arba lentelė.
   • Lauke „Taikoma“ eikite į „Sutraukti dialogo langą“; tada darbalapyje pasirinkite naują langelių diapazoną.
   • Spustelėkite mygtuką „Išplėsti dialogo langą“.
   • Pasirinkite taisyklę ir spustelėkite „Redaguoti taisyklę“: pasirodys dialogo langas „Redaguoti formatavimo taisyklę“.

5. Norėdami pakeisti „PivotTable“ ataskaitos reikšmių srities „Taikyti taisyklę“ laukų diapazoną, atlikite šiuos veiksmus:

   • Pasirinkimas: Spustelėkite „Tik šios ląstelės“
   • Atitinkamas laukas: Spustelėkite „Visos ląstelės su tais pačiais laukais“
   • Vertės laukas: Spustelėkite „Visos ląstelės“

6. Skiltyje „Pasirinkti taisyklės tipą“ spustelėkite „Formatuoti tik langelius, kuriuose yra“

Dabar galite atlikti veiksmus sąrašo lauke „Redaguoti taisyklės aprašą“ sąrašo lauke „Tik formatuoti langelius su“:

Formatuoti pagal skaičių, datą ar laiką:

Norėdami tai padaryti, pasirinkite langelio vertės ir palyginimo operatorių ir įveskite skaičių, datą arba laiką. pavyzdys: „Tarp“ įveskite 300 ir 500 arba pasirinkite „Lygus“ ir įveskite datą.

Naudodami formulę galite atgauti skaičiaus, datos ar laiko vertę.

Formatuoti pagal tekstą:

Pasirinkite „Konkretus tekstas“ ir „Santykių operatorius“.

Įveskite tekstą, pvz. B. ant "yra" žodis "auksas" arba "prasideda" su priešdėliu, kurio norite ieškoti.

Galima įvesti ne daugiau kaip 255 simbolius.

Vertes galima grąžinti naudojant formules. Norėdami tai padaryti, formulė turi prasidėti (=). Netinkamos formulės nėra suformatuotos. Patartina pirmiausia išbandyti klaidų formulę.

Formatuoti pagal datą:

• Pasirinkite datą ir palyginimo datą, pvz. B. Vakar, kitą savaitę arba šį mėnesį

Tada: norėdami nurodyti formatą, spustelėkite „Formatuoti“, pasirodys dialogo langas „Formatuoti langelius“. Pasirinkite norimą skaičių formatą, užpildymo formatą, rėmelį ar šriftą, spustelėkite „Gerai“.

Galima pasirinkti kelis formatus. Kokius formatus pasirinkote, galite pamatyti lauke „Peržiūra“.

Sąlyginis formatavimas naudojant duomenų bazes, spalvų schemas ir simbolių rinkinius

Langelių turinį ir vertes galima vizualiai patobulinti naudojant duomenų bazes, spalvų skalę ar simbolių rinkinius ir padaryti juos patrauklesnius ir aiškesnius. Jei langelių diapazono vertes reikia palyginti viena su kita, tai lengviau atlikti naudojant šį sąlyginį formatavimą.

Formatuokite langelius naudodami duomenų juostas

Duomenų juostos palengvina langelių palyginimą. Langelio reikšmę nurodo juostos ilgis. Kuo didesnė vertė, tuo ilgesnė duomenų juosta, tuo mažesnė vertė, tuo trumpesnė juosta. Tai leidžia greitai nustatyti aukštas arba žemas vertes.

Greitas formatas:

Pažymėkite norimą sritį (langelį, lentelę ar visą lapą).

Skirtuke Pagrindinis spustelėkite mygtuką Sąlyginis formatavimas.

Eikite į „duomenų juostą“ ir pasirinkite užpildą.

Formatuokite langelius spalvotais rėmeliais

Spalvų skalės padeda lengviau klasifikuoti skirtingą duomenų informaciją ir greitai palyginti jų turinį. Atsižvelgiant į spalvų atspalvį, rodomos didelės arba mažos vertės. Langelius galite formatuoti 2 arba 3 spalvų skalėje.

2 spalvų skalė

Greitas formatas:

Pasirinkite langelį, lentelę arba visą lapą.

Skirtuke Pagrindinis spustelėkite Sąlyginis formatavimas.

Eikite į „spalvų skalę“ ir spustelėkite norimą 2 spalvų skalės formatą. Viršutinė spalva reiškia aukštesnes vertes, apatinė - mažesnes.

3 spalvų skalė

Trečioji spalva leidžia vizualizuoti vidutines vertes- be aukštų ir žemų spalvų atvaizdavimo, kaip 2 spalvų skalėje.

Greitas formatas:

Pasirinkite langelį, lentelę arba visą lapą.

Skirtuke Pagrindinis spustelėkite Sąlyginis formatavimas.

Eikite į „spalvų skalę“ ir spustelėkite norimą 3 spalvų skalės formatą. Viršutinė spalva reiškia aukštesnes vertes, apatinė - mažesnes.

Formatuokite langelius simbolių rinkiniais

Naudojant simbolių rinkinį, duomenis galima peržiūrėti ir suskirstyti į atskiras kategorijas. Simbolis reiškia tam tikrą verčių diapazoną. Sąlyginis „Excel“ formatavimas siūlo įvairių spalvų simbolius: rodykles, taškus, kryžius, šauktukus ir daug daugiau.

Greitas formatas:

Pasirinkite langelį, lentelę arba visą lapą.

Skirtuke Pagrindinis spustelėkite Sąlyginis formatavimas.

Eikite į „Simbolių rinkinius“ ir pasirinkite norimą simbolių rinkinį

Formavimo taisykles sukurkite patys

Jei „Excel“ siūlomame sąlyginiame formatavime nerandate tinkamos parinkties, galite lengvai sukurti atskiras formatavimo taisykles.

Pažymėkite langelių, kuriuos reikia formatuoti, diapazoną.

Skirtuke „Pagrindinis“ eikite į „Sąlyginis formatavimas“.

Pasirinkite „Nauja taisyklė“ ir patvirtinkite „Gerai“.

Jei nerandate reikiamos parinkties savo formatavimo taisyklei, gali padėti logiška formulė. Pavyzdžiui, galite naudoti formulę, kad automatiškai nuspalvintumėte „Excel“ langelius, kai tik turinys bus pasiekiamas. Priešingai, tuščios ląstelės gali būti suformatuotos sąlyginai.

Pavyzdys: Taikydami formulę galite palyginti duomenis, esančius už pasirinkto diapazono ribų. Tai netgi apima duomenis, esančius kitame tos pačios darbaknygės darbalapyje. Arba galite palyginti vertes su rezultatu, kurį grąžina funkcija. Formulė turi būti logiška ir grąžinti „tiesa“ arba „klaidinga“.

Pažymėdami langelius tiesiai darbalapyje, galite įvesti ląstelių nuorodas į formulę. Tai yra absoliučios ląstelių nuorodos. Tačiau „Excel“ taip pat gali koreguoti kiekvienos pasirinktos srities ląstelės nuorodas. Jei norite, pasirinkite santykines langelių nuorodas.

Žingsnis po žingsnio taisyklės kūrimo instrukcijos

1. Skirtuko „Pagrindinis“ grupėje „Stiliai“ eikite į rodyklę šalia „Sąlyginis formatavimas“ ir spustelėkite „Tvarkyti taisykles“. Pasirodo dialogo langas Sąlyginio formatavimo taisyklių tvarkyklė.

2. Galimi šie veiksmai:

   • Jei norite pridėti sąlyginį formatą, eikite į „Nauja taisyklė“.
   • Pakeiskite sąlyginį formatą:
   • • Patikrinkite, ar esate tinkamame darbalapyje.
     • Pirma, Taikoma, spustelėkite Sutraukti dialogo langą. Tada pasirinkite naują langelių diapazoną ir eikite į „Išplėsti dialogo langą“.
     • Pasirinkite norimą taisyklę, spustelėkite „Redaguoti taisyklę“. Pasirodo „Redaguoti formatavimo taisyklę“.

3. Skiltyje „Pasirinkti taisyklės tipą“ spustelėkite „Naudoti formulę, kad nustatytumėte langelius, kuriuos norite formatuoti“.

   • Skiltyje „Redaguoti taisyklės aprašą“ įveskite formulę „Formato vertės, kurioms ši taisyklė teisinga“. Formulė prasideda (=) ir turi grąžinti „true“ (1) arba „false“ (0).
   • Spustelėkite Formatuoti, kad būtų rodomas dialogo langas Langelių formatavimas.
   • Dabar pasirinkite skaičių, šriftą, užpildymo formatą ir tt, kuris turėtų būti naudojamas, jei langelio vertė yra teisinga, ir patvirtinkite „Gerai“. Pasirinkti formatai rodomi skiltyje „Peržiūra“.

Pavyzdžiui, COUNTIF formulė grąžina logines reikšmes. Jei naudojama ši formulė, galima nustatyti pasikartojančias reikšmes arba spalvotai paryškinti turinį, kuris vyksta tam tikru dažniu. Tokiu būdu unikalius daiktus taip pat galima rasti ir paryškinti spalvomis. Kitos formulės, kurias galima naudoti sąlyginiam formatavimui, yra

• POILSIS: todėl kintamos linijų spalvos gali būti nustatytos automatiškai
• IDENTICAL: Naudojant šią formulę, jūsų ieškomi duomenys gali būti pažymėti „Excel“ lentelėje, atitinkančioje nurodytą vertę
• KLAIDA: automatiškai pažymi tuščius langelius „Excel“ lentelėje arba pašalina klaidų reikšmes iš darbaknygės
• NE ir ISUMAS: Formatuoja visas „Excel“ langelius, kuriuose nėra skaičių
• MAX: didžiausia langelio reikšmė paryškinta lentelėje
• MIN: leidžia sąlyginį formatuoti mažiausią „Excel“ sąrašo reikšmę, kad iš pirmo žvilgsnio būtų galima pamatyti mažiausią reikšmę
• Kairė: priklausomai nuo simbolių eilutės pradžioje, „Excel“ langeliai automatiškai suformatuojami ir jiems suteikiama vienoda teksto spalva
• LARGE ir LEFT: įgalinkite naujo turinio langelio spalvinimą
• Dešinė: langelius galima formatuoti pagal paskutinį eilutės simbolį
• ŠIANDIEN: Naudojant tai, susitikimai gali būti suformatuoti spalvotai naudojant šviesoforo metodą arba gali būti paryškintos datos reikšmės, kurios netrukus bus atnaujintos
• MĖNESIS arba METAI: leidžia suskirstyti „Excel“ sąrašą į mėnesius ar metus; Naudojant YEAR, taip pat galima pažymėti einamųjų metų datos vertes ir paryškinti metines vertes
• ARBA ir SAVAITGALIS: spalvos paryškina spalvas pagal savaitės dieną

AND ir OR patikrinimų taisyklės kūrimo pavyzdys:

Šiame pavyzdyje sąlyginei formatavimui naudojamos dvi reikšmės. Jei pirmoji taisyklė negalioja, antroji taisyklė taikoma automatiškai.

Pirma taisyklė: Namo pirkėjas nori patikrinti, ar planuojamas turto pirkimas atitinka jo biudžetą. Jis gali įnešti iki 80 000 eurų savo lėšų kaip pradinę įmoką ir aptarnauti mėnesinę paskolos įmoką, ne daugiau kaip 1200 eurų. Jei ir pradinė įmoka, ir paskolos įmoka atitinka šiuos reikalavimus, atitinkami langeliai pažymėti žalia spalva.

Antra taisyklė: Jei pirmoji įmoka ar įmoka neatitinka reikalavimų, atitinkami langeliai pažymėti raudona spalva.

Formulė, kaip sukurti pirmąją taisyklę naudojant žalią spalvą, yra tokia:

= IR (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Antrosios taisyklės sukūrimo naudojant raudoną spalvą formulė yra tokia:

= ARBA (JEI ($ B $ 4 = 1200,1))

Perkelkite ir ištrinkite sąlyginį formatavimą

Sąlyginis formatavimas gali būti nukopijuotas ir pritaikytas kitiems ar naujiems darbalapio duomenims arba dar kartą ištrintas.

Perkelti sąlyginį formatavimą

Norėdami nukopijuoti sąlyginį formatavimą ir įklijuoti jį į kitas ląsteles ar langelių diapazonus, naudokite mygtuką „Perkelti formatą“.

Pasirinkite langelius su sąlyginiu formatu, kuriuos norite nukopijuoti ir įklijuoti į kitus langelius, ir spustelėkite juos.

Dabar eikite į „Pradėti“, tada - „Perkėlimo formatas“. Žymeklis rodomas kaip teptukas.

Galite pridėti sąlyginį formatavimą langeliui ar langelių diapazonui, perkeldami šepetį virš langelio.

Naudodami ESC, baigiate teptuko naudojimą.

Jūs vis dar galite naudoti šepetėlį, norėdami įterpti sąlyginį formatavimą, dukart spustelėdami „Transfer Format“.

Arba sąlyginį formatavimą galima nukopijuoti naudojant sparčiuosius klavišus ir įklijuoti į kitą „Excel“ langelį.

Išvalyti sąlyginį formatavimą

Skiriamas trynimo procesas darbalapyje ir ištrynimo procesas įvairiuose langeliuose.

Ištrinkite darbalapyje arba visame darbalapyje

Skirtuke „Pagrindinis“ eikite į „Sąlyginis formatavimas“.

Tada spustelėkite „ištrinti taisykles“

Tada spustelėkite „Ištrinti taisykles iš viso lapo“.

Arba galite ištrinti sąlyginį formatavimą naudodami dialogo langą „Pasirinkti turinį“.

Ištrinti langelių diapazone

Pažymėkite langelius, kuriuose yra norimas sąlyginis formatavimas.

Spustelėkite mygtuką „Skaitymo analizės didinamasis stiklas“ (mygtukas yra apačioje dešinėje, šalia pasirinktų duomenų).

Eikite į „Ištrinti formatą“.

Visame darbalapyje raskite ir ištrinkite tuos pačius sąlyginius formatus

Spustelėkite langelį su tinkamu sąlyginiu formatu.

Skirtuke „Pagrindinis“ spustelėkite rodyklę šalia „Rasti ir pasirinkti“, tada spustelėkite „Pasirinkti turinį“.

Spustelėkite „Sąlyginiai formatai“.

Skiltyje „Duomenų tikrinimas“ eikite į „Tas pats“ (bus pasirinktos visos ląstelės su vienodu sąlyginiu formatu).

Skirtuke „Pradėti“ eikite į „Sąlyginis formatavimas“, tada „Ištrinti taisykles“ ir „Ištrinti taisykles pasirinktuose langeliuose“.

Sąlyginis formatavimas „PivotTable“ ataskaitoje

Naudodami „PivotTable“ ataskaitą, vartotojai gali greitai apibendrinti didelius duomenų kiekius. Langelių diapazonas, „Excel“ skaičiuoklė arba „PivotTable“ ataskaita - sąlyginis formatavimas iš esmės visur veikia vienodai. Tik „PivotTable“ ataskaitoje yra keletas specialių funkcijų, kurias turėtumėte žinoti:

• Kai kurie sąlyginiai formatai neveikia „PivotTable“ ataskaitos reikšmių diapazono laukuose.
• Jei bet kokiu būdu pakeisite „PivotTable“ ataskaitos išdėstymą, sąlyginis formatavimas išliks tol, kol bus pašalinti pagrindinių duomenų laukai.
• Sąlyginis verčių diapazono laukų formatavimas gali būti pagrįstas duomenų hierarchija. Šio sąlyginio formatavimo diapazoną lemia kitas žemesnis arba aukštesnis hierarchijos lygis vieno ar kelių stulpelių eilutėse arba vienos ar kelių eilučių stulpeliuose.
• Sąlyginio formatavimo diapazonas reikšmių diapazono laukams gali būti apibrėžtas trimis metodais:

• • pasirinkimas
  • Atitinkamas laukas
  • Vertės laukas

Numatytasis metodas yra pasirinkimas.

Srities apibrėžimas pagal pasirinkimą

Eikite į „Taikyti formatavimo taisyklę“.

Spustelėkite dialogo langą Nauja formatavimo taisyklė arba Redaguoti formatavimo taisyklės dialogo langą.

Šis metodas tinka gretimai reikšmių diapazono laukų serijai, pvz. B. regiono apklausos duomenys, taip pat vertybių diapazono nenutrūkstamų laukų serija, pvz., Skirtingų regionų, esančių skirtinguose duomenų hierarchijos lygmenyse, apklausos duomenys.

Srities apibrėžimas per atitinkamą lauką

Šis metodas veikia, jei turite

• norite išvengti daugybės nesusijusių elementų pasirinkimo
• norite sąlygiškai suformatuoti kelis duomenų hierarchijos lygio verčių diapazono laukus
• Norite neįtraukti dalinių rezultatų

Srities apibrėžimas per vertės lauką

Šis metodas veikia, jei turite

• norite išvengti daugybės nesusijusių elementų pasirinkimo
• norite sąlygiškai suformatuoti kelis laukus visų diapazonų reikšmių diapazone
• Norite įtraukti sumas ir dalinius rezultatus.

Išvada: Sąlyginis „Excel“ formatavimas leidžia lengviau sekti duomenis

Jei norite „Excel“ lentelėje spalvotai paryškinti tam tikras duomenų reikšmes, sąlyginis „Excel“ formatavimas yra idealus. Turite dvi galimybes taikyti sąlyginį formatavimą. Arba naudokite „Excel“ pateiktas taisykles, kad paryškintumėte teksto turinį, skaitines reikšmes ar datas. Arba galite patys apibrėžti taisyklę su įvairiomis sąlygomis, kuriomis turi būti pažymėtos atitinkamos vertės. Galite lengvai ir efektyviai palyginti duomenis tarpusavyje naudodami duomenų juostas, spalvų juostas ir simbolių rinkinius. Jei dizaino parinkčių nebereikia, galite jas pašalinti keliais paspaudimais.

Daugiau straipsnių apie sąlyginį formatavimą

• Kaip automatiškai generuoti rodykles „Excel“ sąrašuose
• Rodyti neigiamas laiko reikšmes „Excel“ lentelėse
• Neigiamos spalvos procentas raudonas
• Iš pirmo žvilgsnio atpažinkite neigiamus procentus
• Formatuokite skaičius pasirinktu formatu spalvomis
• Formatuokite „Excel“ langelius, kurie neturi nurodytos vertės
• Automatiškai išsaugokite langelius atitinkamais valiutos formatais
• Pažymėkite langelius spalva naudodami makro funkciją
• Naudokite sąlyginį formatavimą ir paryškinkite duomenis, kurie yra didesni arba lygūs trečiam pagal dydį langeliui
• Atvirkštinės tiesos vertybės