Pogojno oblikovanje v Excelu: navodila in uporaba

S temi navodili ustvarite različne oblike

Pogojno oblikovanje v programu Microsoft Excel je uporabno orodje za povečanje vidnosti tabel, poročil ali celic, označevanje področja ali več stolpcev, analizo podatkov, razjasnitev razlik ali prepoznavanje trendov. Za to se uporabljajo podatkovne vrstice, kompleti simbolov ali barvne lestvice. Odvisno od navedenih pogojev se spremeni prikaz celic ali oblikuje obseg celic. Za številne integrirane pogoje lahko ustvarite svojega.

Pogojno oblikovanje je smiselno in vam pomaga

Pogojno oblikovanje v Excelu je mogoče uporabiti na različne načine, ne glede na to, ali gre za tabelo Excel, določen obseg celic ali poročilo vrtilne tabele. Na voljo so vam različna dejanja in predloge oblik. Odločilna prednost za uporabnike Excela je, da je mogoče z barvnim označevanjem poudariti posebne vrednosti ali rezultate. Če se vrednost spremeni, se oblikovanje samodejno prilagodi - odvisno od pogojev.

info: Vrtilna tabela ("vrtilna tabela") je posebna tabela, s katero je mogoče izračunati, povzeti in analizirati veliko količino podatkov. Poleg tega lahko hitro primerjate in ugotovite trende v podatkih o pregledu.

Pogojno oblikovanje z obstoječimi pravili za številke, besedilno vsebino ali datume

Če formatirate s primerjalnim operaterjem, boste celice lažje našli. Vrednosti lahko naredite bolj vidne, na primer večje ali manjše od definirane vrednosti ali določenih vrednosti, ki ležijo med zgornjo in spodnjo mejo. To pomeni, da so želeni ali iskani podatki prikazani v barvi. Za urejanje sta na voljo hitro oblikovanje ali napredno oblikovanje. S podaljšanim oblikovanjem lahko delate s formulami, na primer za samodejno označevanje stolpca Excel v barvi.

Primeri:

• Skrbniški uslužbenec bi rad označil vse zaloge pod 15 kosi z barvnim ozadjem.
• Vsa mesta z več kot 150.000 prebivalci je treba označiti v tabeli.
• Proizvodno podjetje želi označiti vse blago, proizvedeno med 1. januarjem 2022-2023 in 31. januarjem 2022-2023, rdeče.

Hitro formatiranje:

Izbira ene ali več celic.

Na zavihku kliknite »Start«.

Pojdite na puščico poleg gumba "Pogojno oblikovanje" v "Slog".

Kliknite na "Pravila za označevanje celic".

Izberite želeni ukaz, npr. B. več kot, manj kot, med datumom itd.

Vnesite vrednosti in izberite obliko.

Razširjeno oblikovanje:

1. Izberite eno ali več celic v poročilu tabele ali vrtilne tabele.

2. Pojdite na zavihek »Domov«, v skupini Slogi kliknite puščico poleg »Pogojno oblikovanje«.

3. Kliknite na "Upravljanje pravil": Pojavi se pogovorno okno "Upravitelj pravil pogojnega oblikovanja".

4. Izberite dejanje:

   • Kliknite na "Novo pravilo": Odpre se pogovorno okno "Novo pravilo oblikovanja".
   • Če želite spremeniti obliko, se prepričajte, da je na seznamu "Pokaži pravilo oblikovanja za" izbran pravilen delovni list ali tabela.
   • V polju "Velja za" pojdite na "Strni pogovorno okno"; nato na delovnem listu izberite nov obseg celic.
   • Kliknite gumb »Razširi pogovorno okno«.
   • Izberite pravilo in kliknite »Uredi pravilo«: Odpre se pogovorno okno »Uredi pravilo oblikovanja«.

5. Če želite spremeniti obseg polj v območju vrednosti "Uporabi pravilo za" poročila vrtilne tabele, naredite naslednje:

   • Izbor: Kliknite "Samo te celice"
   • Ustrezno polje: Kliknite na "Vse celice z istimi polji"
   • Polje vrednosti: Kliknite na "Vse celice"

6. Pri »Izberi vrsto pravila« kliknite »Oblikuj samo celice, ki vsebujejo«

Zdaj lahko izvedete dejanja pod "Urejanje opisa pravila" v polju seznama "Oblikuj celice samo z":

Oblika po številki, datumu ali uri:

To naredite tako, da izberete vrednost celice in operater za primerjavo ter vnesete številko, datum ali čas. primer: Za "Med" vnesite 300 in 500 ali izberite "Enako" in vnesite datum.

S formulo lahko vrnete vrednost števila, datuma ali časa.

Oblika po besedilu:

Izberite "Posebno besedilo" in "Relacijski operater".

Vnesite besedilo, npr. B. na "vsebuje" besedo "zlato" ali na "začne se" s predpono, ki jo želite poiskati.

Vnesete lahko največ 255 znakov.

Vrednosti je mogoče vrniti s formulami. Če želite to narediti, se mora formula začeti z (=). Neveljavne formule niso oblikovane. Priporočljivo je, da formulo najprej preizkusite glede napak.

Oblika po datumu:

• Izberite datum in datum primerjave, npr. B. Včeraj, naslednji teden ali ta mesec

Nato: Če želite določiti obliko, kliknite »Oblika«, prikaže se pogovorno okno »Oblikuj celice«. Izberite želeno obliko številke, obliko izpolnitve, okvir ali pisavo, kliknite »V redu«.

Možno je izbrati več formatov. Katere oblike so bile izbrane, si lahko ogledate v polju »Predogled«.

Pogojno oblikovanje z bazami podatkov, barvnimi shemami in nabori simbolov

Vsebine in vrednosti v celicah je mogoče vizualno nadgraditi z bazami podatkov, barvnimi lestvicami ali naborom simbolov ter jih narediti privlačnejše in jasnejše. Če želimo vrednosti v obsegu celic med seboj primerjati, je to lažje s tem pogojnim oblikovanjem.

Oblikujte celice s podatkovnimi vrsticami

Podatkovne vrstice olajšajo primerjavo celic med seboj. Vrednost v celici je označena z dolžino palice. Višja kot je vrednost, daljša je podatkovna vrstica, nižja je vrednost, krajša je vrstica. To omogoča hitro določanje visokih ali nizkih vrednosti.

Hitro formatiranje:

Označite želeno območje (celico, tabelo ali celoten list).

Na zavihku Domov kliknite gumb Pogojno oblikovanje.

Pojdite na "podatkovno vrstico" in izberite polnilo.

Oblikujte celice z barvnimi okvirji

Barvne lestvice pomagajo lažje razvrstiti različne podatkovne podatke in hitro primerjati njihovo vsebino. Odvisno od odtenka barve se prikažejo visoke ali nizke vrednosti. Celice lahko oblikujete z 2 ali 3 barvno lestvico.

2-barvna lestvica

Hitro formatiranje:

Izberite celico, tabelo ali celoten list.

Na zavihku Domov kliknite Pogojno oblikovanje.

Pojdite na "barvno lestvico" in kliknite želeno obliko 2-barvne lestvice. Zgornja barva pomeni višje vrednosti, spodnja barva pa nižje vrednosti.

3-barvna lestvica

Tretja barva omogoča vizualizacijo srednjih vrednosti- poleg barvne predstavitve visokih in nizkih vrednosti kot v 2-barvni lestvici.

Hitro formatiranje:

Izberite celico, tabelo ali celoten list.

Na zavihku Domov kliknite Pogojno oblikovanje.

Pojdite na "barvno lestvico" in kliknite želeno obliko 3-barvne lestvice. Zgornja barva pomeni višje vrednosti, spodnja barva pa nižje vrednosti.

Oblikujte celice z nizi simbolov

Z nizom simbolov si lahko podatke ogledate in jih razvrstite v ločene kategorije. Simbol pomeni določeno območje vrednosti. Pogojno oblikovanje v Excelu ponuja različne barvne simbole: puščice, pike, križe, klicaje in še veliko več.

Hitro formatiranje:

Izberite celico, tabelo ali celoten list.

Na zavihku Domov kliknite Pogojno oblikovanje.

Pojdite na "Nabori simbolov" in izberite želeni nabor simbolov

Ustvarite pravila oblikovanja sami

Če v pogojnem oblikovanju, ki ga ponuja Excel, ne najdete ustrezne možnosti, lahko preprosto ustvarite posamezna pravila za oblikovanje.

Označite obseg celic, ki jih želite pogojno oblikovati.

Pojdite na "Pogojno oblikovanje" na zavihku "Domov".

Izberite "Novo pravilo" in potrdite z "V redu".

Če ne najdete možnosti, ki jo potrebujete za svoje pravilo oblikovanja, vam lahko pomaga logična formula. Na primer, lahko uporabite formulo za samodejno barvanje celic Excel, takoj ko je vsebina na voljo. Nasprotno pa lahko prazne celice pogojno oblikujemo.

Primer: Z uporabo formule lahko primerjate podatke izven izbranega območja. To vključuje tudi podatke, ki so na drugem delovnem listu v istem delovnem zvezku. Lahko pa primerjate vrednosti z rezultatom, ki ga vrne funkcija. Formula mora biti logična in vračati "true" ali "false".

Če celice označite neposredno na delovnem listu, lahko v formulo vnesete sklice na celice. To so absolutne reference celic. Vendar pa lahko Excel tudi prilagodi sklice na celice za vsako celico na izbranem območju. Če želite, izberite relativne reference celic.

Navodila po korakih za ustvarjanje pravila

1. Na zavihku »Domov« v skupini »Slogi« pojdite na puščico poleg »Pogojno oblikovanje« in kliknite »Upravljanje pravil«. Prikaže se pogovorno okno Upravitelj pravil pogojnega oblikovanja.

2. Možni so naslednji ukrepi:

   • Če želite dodati pogojno obliko, pojdite na "Novo pravilo".
   • Spremenite pogojno obliko:
   • • Preverite, ali ste na pravilnem delovnem listu.
     • Najprej v razdelku Velja za kliknite pogovorno okno Zmanjšaj. Nato izberite nov obseg celic in pojdite na "Razširi pogovorno okno".
     • Izberite želeno pravilo, kliknite »Uredi pravilo«. Prikaže se »Uredi pravilo oblikovanja«.

3. Pri »Izberi vrsto pravila« kliknite »Uporabi formulo za določanje celic za oblikovanje«.

   • Pod "Uredi opis pravila" vnesite formulo v "Oblikuj vrednosti, za katere to pravilo velja". Formula se začne z (=) in mora vrniti »true« (1) ali »false« (0).
   • Kliknite Oblika, da se prikaže pogovorno okno Oblikovanje celic.
   • Zdaj izberite številko, pisavo, obliko izpolnitve itd., Ki jih je treba uporabiti, če je vrednost celice resnična, in potrdite z "V redu". Izbrane oblike so prikazane v "Predogled".

Formula COUNTIF na primer vrne logične vrednosti. Če uporabite to formulo, lahko določite podvojene vrednosti ali vsebino, ki se pojavi v določeni frekvenci, označite z barvo. Na ta način lahko najdemo tudi edinstvene predmete in jih barvno označimo. Druge formule, ki jih je mogoče uporabiti za pogojno oblikovanje, so

• REST: tako lahko samodejno nastavite izmenične barve črt
• IDENTIČNO: S to formulo lahko podatke, ki jih iščete, označite v Excelovi tabeli, ki ustreza določeni vrednosti
• ISERROR: samodejno označi prazne celice v Excelovi tabeli ali povzroči, da vrednosti napak izginejo iz delovnega zvezka
• NOT in ISNUMBER: Oblikuje vse celice programa Excel, ki ne vsebujejo številk
• MAX: največja vrednost celice je označena v tabeli
• MIN: omogoča pogojno oblikovanje najmanjše vrednosti na Excelovem seznamu, tako da je minimalno mogoče videti na prvi pogled
• LEVO: glede na niz znakov na začetku se Excelove celice samodejno oblikujejo in dobijo enotno barvo besedila
• LARGE in LEFT: omogočite barvanje celice z novo vsebino
• DESNO: Celice je mogoče oblikovati glede na zadnji znak niza
• DANES: S tem lahko sestanke barvno oblikujete z uporabo semaforja ali pa označite datumske vrednosti, ki bodo kmalu posodobljene
• MESEC ali LETO: omogoča razdelitev Excelovega seznama na mesece ali leta; Z LETO lahko označite tudi datumske vrednosti tekočega leta in označite letne vrednosti
• ALI in TEDEN: barvno označuje vrednosti glede na dan v tednu

Primer ustvarjanja pravila za preglede AND in OR:

V tem primeru se za pogojno oblikovanje uporabljata dve vrednosti. Če prvo pravilo ne velja, se drugo pravilo samodejno uporabi.

Prvo pravilo: Kupec stanovanja želi preveriti, ali je načrtovani nakup nepremičnine v skladu z njegovim proračunom. Lahko vloži do 80.000 evrov lastnih sredstev kot polog in servisira mesečni obrok posojila v višini največ 1.200 evrov. Če tako predujem kot obrok posojila izpolnjujeta te zahteve, so ustrezne celice označene z zeleno.

Drugo pravilo: Če polog ali obrok ne izpolnjujeta zahtev, so ustrezne celice označene z rdečo.

Formula za ustvarjanje prvega pravila z zeleno barvo je naslednja:

= AND (ČE ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

Formula za ustvarjanje drugega pravila z rdečo barvo je naslednja:

= ALI (ČE ($ B $ 4 = 1200,1))

Prenesite in izbrišite pogojno oblikovanje

Pogojno oblikovanje je mogoče kopirati in uporabiti za druge ali nove podatke na delovnem listu ali znova izbrisati.

Prenos pogojnega oblikovanja

Če želite kopirati pogojno oblikovanje in ga prilepiti v druge celice ali obsege celic, uporabite gumb "Prenesi obliko".

Izberite celice s pogojnim oblikovanjem, ki jih želite kopirati in prilepiti v druge celice, in jih kliknite.

Zdaj pojdite na "Start" in nato "Transfer Format". Kazalec se prikaže kot čopič.

Celici ali obsegu celic lahko dodate pogojno oblikovanje s premikanjem čopiča nad celico.

Z ESC prenehate uporabljati krtačo.

Še vedno lahko uporabite čopič za vstavljanje pogojnega oblikovanja, tako da dvokliknete »Prenesi obliko«.

Druga možnost je, da pogojno oblikovanje kopirate z bližnjicami na tipkovnici in prilepite v drugo celico Excel.

Jasno pogojno oblikovanje

Razlikuje se med postopkom brisanja na delovnem listu in postopkom brisanja v vrsti celic.

Izbrišite na delovnem listu ali na celotnem delovnem listu

Pojdite na "Pogojno oblikovanje" na zavihku "Domov".

Nato kliknite »izbriši pravila«

Nato kliknite »Izbriši pravila na celotnem listu«.

Lahko pa tudi izbrišete pogojno oblikovanje v pogovornem oknu »Izberi vsebino«.

Izbriši v obsegu celic

Označite celice, ki vsebujejo želeno pogojno oblikovanje.

Kliknite gumb "Povečevalno steklo za analizo branja" (gumb se nahaja spodaj desno poleg izbranih podatkov).

Pojdite na "Izbriši obliko".

Poiščite in izbrišite iste pogojne oblike na celotnem delovnem listu

Kliknite celico z ustrezno pogojno obliko.

Na zavihku »Domov« kliknite puščico poleg »Poišči in izberi«, nato kliknite »Izberi vsebino«.

Kliknite na "Pogojne oblike".

Pri »Preverjanje podatkov« pojdite na »Enako« (s tem bodo izbrane vse celice z enakim pogojnim oblikovanjem).

Na zavihku »Start« pojdite na »Pogojno oblikovanje«, nato »Izbriši pravila« in »Izbriši pravila v izbranih celicah«.

Pogojno oblikovanje v poročilu vrtilne tabele

S poročilom vrtilne tabele lahko uporabniki hitro povzamejo velike količine podatkov. Obseg celic, Excelova preglednica ali poročilo vrtilne tabele - pogojno oblikovanje v bistvu deluje povsod enako. Samo v poročilu vrtilne tabele je nekaj posebnih funkcij, na katere morate biti pozorni:

• Nekatere pogojne oblike ne delujejo na poljih v razponu vrednosti poročila vrtilne tabele.
• Če na kakršen koli način spremenite postavitev poročila vrtilne tabele, bo pogojno oblikovanje trajalo, dokler se polja v osnovnih podatkih ne odstranijo.
• Pogojno oblikovanje polj v razponu vrednosti lahko temelji na hierarhiji podatkov. Obseg tega pogojnega oblikovanja je določen z naslednjo nižjo ali naslednjo višjo stopnjo hierarhije v vrsticah za enega ali več stolpcev ali v stolpcih za eno ali več vrstic.
• Obseg pogojnega oblikovanja za polja v razponu vrednosti je mogoče določiti s tremi metodami:

• • izbor
  • Ustrezno polje
  • Polje vrednosti

Privzeta metoda je izbira.

Opredelitev območja z izbiro

Pojdite na "Uporabi pravilo oblikovanja za".

Kliknite pogovorno okno Novo pravilo oblikovanja ali pogovorno okno Uredi pravilo oblikovanja.

Ta metoda je primerna za sosednje vrste polj v razponu vrednosti, npr. B. anketni podatki za regijo, pa tudi za nesorazmerno vrsto polj v razponu vrednosti, kot so podatki raziskav za različne regije na različnih ravneh hierarhije podatkov.

Opredelitev območja skozi ustrezno polje

Ta metoda deluje, če imate

• se želite izogniti izbiri številnih nepovezanih elementov
• želite pogojno oblikovati več polj v obsegu vrednosti za raven hierarhije podatkov
• Želite izključiti delne rezultate

Opredelitev območja skozi polje vrednosti

Ta metoda deluje, če imate

• se želite izogniti izbiri številnih nepovezanih elementov
• želite pogojno formatirati več polj v razponu vrednosti za vse ravni hierarhije
• Želite vključiti skupne in delne rezultate.

Zaključek: Pogojno oblikovanje v Excelu olajša spremljanje podatkov

Če želite barvno označiti določene vrednosti podatkov v Excelovi tabeli, je pogojno oblikovanje v Excelu idealno. Za uporabo pogojnega oblikovanja imate dve možnosti. Uporabite pravila, ki jih ponuja Excel, da barvno označite besedilno vsebino, številske vrednosti ali datume. Lahko pa tudi sami določite pravilo z različnimi pogoji, pod katerimi je treba označiti ustrezne vrednosti. Podatke lahko preprosto in učinkovito primerjate med seboj s pomočjo podatkovnih vrstic, barvnih črt in naborov simbolov. Če možnosti oblikovanja niso več potrebne, jih lahko odstranite z nekaj kliki.

Več člankov o pogojnem oblikovanju

• Kako samodejno ustvariti puščice na seznamih Excel
• Prikaz negativnih časovnih vrednosti v Excelovih tabelah
• Negativni odstotki barve rdeča
• Na prvi pogled prepoznajte negativne odstotke
• Oblikujte številke z barvno obliko po meri
• Oblikujte celice programa Excel, ki nimajo podane vrednosti
• Samodejno shrani celice z ustreznimi oblikami valut
• Barvno označite celice s funkcijo makra
• Uporabite pogojno oblikovanje in označite podatke, ki so večji ali enaki tretji največji celici
• Vrednosti obratne resnice