Trebuie să evidențiați valorile duplicate într-o coloană? Trebuie să selectați primele 5 celule maxime? Trebuie să faceți o scală termică pentru claritate (culoarea se schimbă în funcție de creșterea/scăderea valorii celulelor)? În Excel, evidențierea de culoare a celulelor în funcție de condiții se poate face foarte rapid. În Excel, o funcție specială „Formatare condiționată” este responsabilă pentru evidențierea celulelor cu culoare. Recomand cu incredere! Citiți mai multe mai jos:
Pentru început, pe panglica de activități din meniul principal, găsiți secțiunea Stiluri și faceți clic pe butonul Formatare condiționată,
Când faceți clic, se va deschide un meniu cu diferite opțiuni pentru această editare. După cum puteți vedea, există într-adevăr multe oportunități aici.
Acum mai multe despre cele mai utile:
Evidențierea culorilor Excel a celulelor în funcție de condiții. Condiții simple
Pentru a face acest lucru, accesați elementul Reguli de selecție a celulelor. Dacă, de exemplu, trebuie să selectați toate celulele mai mari de 100, faceți clic pe butonul Mai multe. La fereastră:
În mod implicit, condițiile sunt sugerate să fie evidențiate cu roșu, dar puteți seta formatarea dorită a celulei făcând clic în fereastra din dreapta și selectând opțiunea de care aveți nevoie.
Evidențierea valorilor duplicate, incl. pe mai multe coloane
Pentru a selecta toate valorile duplicate, selectați meniul corespunzător (vezi imaginea de la începutul articolului). În continuare, va apărea din nou o fereastră cu formatare. Personalizați după cum doriți.
Ce să faci dacă trebuie să găsești repetări în două sau mai multe coloane, de exemplu, când numele complet este în coloane diferite? Faceți o altă coloană și concatenați valorile cu formula =, adică. într-o celulă separată veți avea scris IvanovIvanIvanych, apoi puteți selecta valori duplicate pentru această coloană. Este important să înțelegeți că, dacă ordinea cuvintelor este diferită, Excel va considera astfel de linii ca nerepetate.
Evidențierea primelor/ultimelor valori în culoare. Din nou formatare condiționată
Pentru a face acest lucru, accesați Regulile pentru selectarea primului și ultimul element al celulei și selectați elementul necesar. Pe lângă faptul că poți evidenția primele/ultimele valori (inclusiv în procente), poți folosi posibilitatea de a evidenția date peste și sub medie (eu o folosesc și mai des). Foarte la îndemână pentru vizualizarea rezultatelor altele decât cele normale sau medii!
Construirea unei diagrame termice și a histogramei
Caracteristica cool pentru vizualizarea datelor este graficul termic / temperatură. Linia de jos este că, în funcție de mărimea valorii dintr-o coloană sau un rând, celula este evidențiată cu o anumită nuanță de culoare. Tabelele sunt mult mai bine percepute cu ochii, iar luarea deciziilor devine mai ușoară. La urma urmei, unul dintre cei mai buni analizoare este ochiul nostru și, în consecință, creierul nostru, nu o mașină!
Histograma dintr-o celulă (figura de mai jos) este, de asemenea, o funcție extrem de utilă pentru detectarea modificărilor valorilor și compararea acestora.
Evidențierea celulelor care conțin text specific
De foarte multe ori trebuie să găsiți celule care conțin un anumit set de caractere, puteți utiliza desigur funcția =, dar este mai ușor și mai rapid să o aplicați la formatarea condiționată, parcurgeți - Reguli de selecție a celulelor - Textul conține (vezi imaginea 2) .
Foarte util atunci când lucrați cu text. Un exemplu, când într-o coloană aveți numele complete ale angajaților și trebuie să selectați toți colegii lui Ivanov. Selectați celulele, accesați elementul de meniu și selectați textul care conține Ivanov, apoi filtrați tabelul după culoare:
Excel evidențierea cu culoare. Filtrați după culoare
Pe lângă posibilitățile de mai sus, puteți filtra celulele selectate după culoare cu un filtru obișnuit. Spre surprinderea mea, foarte puțini oameni știu despre asta - se pare că există ecouri ale versiunii din 2003 - această posibilitate nu exista.
Verificarea condițiilor de formatare
Pentru a verifica ce formatare condiționată ați setat deja, accesați Principal - Formatare condiționată - Gestionați reguli, aici puteți edita condițiile de evidențiere a celulelor cu culoare, precum și selectați prioritatea formatării specificate (cine este mai mare, mai important, puteți schimba butoanele - săgeți)
Interval de formatare condiționat nevalid
Important! Formatarea condiționată, dacă este utilizată incorect, este adesea cauza unor mari încetiniri în Excel. Acest lucru se întâmplă dacă copiați celule de mai multe ori cu selecția culorilor. Atunci poti avea multe conditii cu culoarea. Eu însumi am văzut mai mult de 3 mii de condiții - dosarul a frânat fără Dumnezeu. De asemenea, poate încetini atunci când intervalul este setat ca în imaginea de mai sus, este mai bine să indicați A: A - pentru întreaga gamă.
Citiți mai multe despre frânele Excel și cauzele acestora. Acest articol a ajutat sute de oameni;)
Mă bucur că am aflat despre evidențierea condiționată a celulelor în Excel.
Distribuie articolul nostru pe rețelele tale de socializare:Într-un tutorial video detaliat. Astăzi am dori să extindem puțin acest articol pentru a rezolva o problemă mai restrânsă. Să presupunem că trebuie să numărați numărul de celule în funcție de culoarea celulelor sau de culoarea textului.
Începând cu Excel 2007, programul are încorporată capacitatea de a sorta celulele după culoare. Astfel, putem filtra culoarea de care avem nevoie, selectam celulele rămase în vizualizare și vedem vizual numărul total de celule. Dar ce se întâmplă dacă trebuie să facem acest lucru des și, în același timp, avem nevoie ca totul să fie numărat și recalculat folosind formule.
În aceste scopuri, trebuie să utilizați o macrocomandă foarte simplă, sau mai degrabă o funcție definită de utilizator, să o numim ColorNom, ne va permite să extragem codul numeric al culorii de umplere și apoi, folosind acest cod, vom număra numărul total de celule folosind tehnicile descrise în articol
Asadar, haideti sa începem. Accesați editorul Visual Basic, pentru aceasta:
v Excel 2003 presa Serviciu, Mai departe Macroși apoi Editor Visual Basic.
v Excel 2007, 2010 și 2013 se face altfel. Accesați secțiunea Dezvoltator, apoi selectați Visual Basic
Atenţie! Secțiunea barei de instrumente Dezvoltator este disponibil implicit în Excel 2007, dar trebuie să fie activat în Excel 2010 și 2013. Acest lucru este util în special pentru acei utilizatori care vor lucra adesea cu macrocomenzi. Pentru a activa bara de instrumente pentru dezvoltatori în Excel 2010 sau 2013, trebuie să rulați Fișier | Parametri | Personalizarea Panglicii după aceea este necesar pe partea dreaptă să puneți o căpușă în fața inscripției Dezvoltator
După ce se deschide editorul Visual Basic, introduceți un modul gol, pentru aceasta selectați meniul Introduceși mai departe Modul
Funcție publică ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
Funcția de sfârșit
Important! Nu veți putea găsi numărul de culoare al celulei cu această funcție când utilizați formatarea condiționată. În plus, atunci când schimbați culoarea unei celule, Excel nu recalculează valorile; trebuie să faceți acest lucru manual apăsând Ctrl + Alt + F9, sau modificările vor avea loc când deschideți din nou fișierul. Acest lucru se datorează faptului că Excel nu consideră o modificare a culorii celulei ca fiind o modificare a formulei. În acest sens, dacă acest lucru este critic, atunci puteți face o modificare a formulei în sine, prin simpla adăugare a unei funcții care este recalculată constant și, în același timp, nu va afecta determinarea culorii celulei. De exemplu, specificați funcția pentru determinarea datei curente înmulțită cu zero.
În cazul nostru, funcția va arăta astfel.
=ColorNom (A1) + Astăzi () * 0
Un exemplu de numărare a numărului de valori după culoarea culorii de umplere a celulelor în Excel
Luați în considerare exemplul de mai sus cu o listă de fructe. Am definit codul celulei și l-am afișat în fața fiecărei celule.
Așa arată argumentele acestei funcții.
COUNTIF (interval, criteriu)
Să scriem formula:
COUNTIF ($ B $ 1: $ B $ 8; E2)
Am indicat intervalul cu semnul dolarului, astfel încât acesta să fie fix și formula să poată fi extinsă. Avem doar trei criterii și acestea sunt indicate în tabelul nostru auxiliar. Să întindem formula și să obținem numărul de celule după culoare.
Descărcați fișierul exemplu: (fișier cu suport macro)
O persoană este atât de structurată încât percepe culorile mai bine decât numerele goale - nu degeaba cele mai importante secțiuni ale diagramelor sau rândurilor unui tabel cărora merită să fii atent sunt de obicei evidențiate cu o culoare contrastantă. Cu toate acestea, marcarea manuală a celulelor „bune” și „rele” ale unei foi de calcul în Excel nu este distractivă. Mi-aș dori ca MS Excel să o facă singur și chiar să schimbe automat aceleași culori în cazul unei schimbări a situației...
Dar el știe deja să o facă perfect - tu și cu mine îl putem ajuta doar puțin!
Să rezolvăm o astfel de problemă aplicată: în tabelul nostru „fructe” este indicată greutatea cutare sau cutare nume în kilograme. Pentru a ne ușura navigarea în ceea ce ne lipsește și ceea ce, dimpotrivă, este în exces, vom colora toate valorile mai mici de 20 în roșu și totul peste 50 în verde. În acest caz, tot ce rămâne în această gamă nu va fi deloc marcat cu culoare. Și pentru a complica sarcina, să mergem și mai departe și să facem dinamică alocarea culorilor - atunci când valoarea din celula corespunzătoare se schimbă, culoarea acesteia se va schimba și ea.
Mai întâi, selectați intervalul de date, adică conținutul celei de-a doua coloane a tabelului MS Excel, apoi accesați fila „ Acasă", Unde în grup" Stiluri "activați instrumentul" Formatarea condițională", Și în lista derulantă, selectați" Creați o regulă«.
În fereastra care apare „Creați o regulă de formatare”, selectați Tipul de regulă: „Formatează numai celulele care conțin”, iar în constructorul de mai jos, setați parametrii: „Valoare celulă”, „Mai puțin” și introduceți manual „marginea” noastră. : numarul 20.
Apăsați butonul „Format” de mai jos, accesați fila „Umplere” și selectați culoarea roșie. Faceți clic pe „Ok”.
Notă: puteți, desigur, să marcați celulele nu cu culoarea de fundal, ci, de exemplu, cu dimensiunea sau culoarea fontului - pentru aceasta, în aceeași fereastră, trebuie să utilizați filele corespunzătoare.
Uită-te la masă - evident că mai avem foarte puține mere și mandarine, este timpul să facem o nouă achiziție!
Super, datele sunt deja evidențiate!
Acum, prin analogie, să creăm o altă regulă - doar că de această dată cu parametrii „Valoarea celulei”, „Mai mare decât”, 20. Vom specifica verde ca culoare de umplere. Gata.
Nu mi s-a părut suficient - textul negru pe fundal roșu și verde nu se poate citi bine, așa că am decis să ne decorez puțin regulile și să înlocuiesc culoarea textului cu alb. Pentru a face acest lucru, deschideți instrumentul Formatare condiționată, dar alegeți nu regulă nouă, ci Managementul regulilor", mai jos.
În fereastra care apare, selectați pe rând ambele reguli și apăsați butonul " Schimbare", După care, în fereastra deja familiară" Format„Mergi la fila” Font„Și schimbați culoarea textului în alb. Iată cu ce am ajuns:
Acum am schimbat nu numai fundalul celulelor tabelului, ci și culoarea fontului
Să încercăm să schimbăm valorile „rele” în valori „bune”? Odată gata - culoarea s-a schimbat automat de îndată ce valorile au apărut în celulele corespunzătoare care intră sub una dintre reguli.
Schimbarea valorilor din tabelul nostru excel... totul funcționează!
V excela există caracteristici utile de care mulți nu sunt conștienți. Una dintre ele este caracteristica de formatare condiționată, care aplică automat un anumit format, de exemplu, vopsește celulele în roșu care se potrivesc unei anumite condiții stabilite de utilizator.
Aici vă vom arăta cum stabiliți condiții în Excel pentru a schimba apoi culoarea celulelor pentru a se potrivi automat.
Condiții pentru completarea celulelor Excel în Windows
Este foarte ușor să pictezi celulele în condiții. Selectați celulele, coloanele sau rândurile a căror culoare doriți să o schimbați. Apoi pe fila Acasă mergi la meniu Stiluriși alegeți Formatarea condițională > Condiții de selecție a celulelor... Alegeți condiția care vi se potrivește. Introduceți valoarea de referință, deschideți meniul derulant și selectați Format personalizat... Faceți clic pe filă Completatiși alegeți o culoare pentru celule. În cele din urmă apăsați O.K.Condiții pentru completarea celulelor în Excel pe Mac
Pe Mac, colorăm selecția de celule în condiții - exact aceleași. Singura diferență este gama mai largă de condiții pentru personalizare.Imagine: © Mika Baumeister - Unsplash.com
Să presupunem că una dintre sarcinile noastre este să introducem informații despre dacă un client a făcut o comandă în luna curentă. După aceea, pe baza informațiilor primite, este necesară evidențierea celulelor color în funcție de condiția: care dintre clienți nu a făcut o singură comandă în ultimele 3 luni. Pentru astfel de clienți, va trebui să retrimiteți oferta.
Desigur, aceasta este o sarcină pentru Excel. Programul ar trebui să găsească automat astfel de contrapărți și să le evidențieze în mod corespunzător. Pentru aceste condiții, vom folosi formatarea condiționată.
Umplerea automată a celulelor cu date
Mai întâi, să pregătim o structură pentru a completa registrul. Mai întâi de toate, să luăm în considerare condiționat un exemplu gata făcut de registru automat, care este prezentat în figura de mai jos:
Utilizatorul trebuie doar să indice dacă clientul a făcut o comandă în luna curentă, apoi valoarea textului „comanda” trebuie introdusă în celula corespunzătoare. Condiția principală pentru evidențiere: dacă în termen de 3 luni contrapartea nu a efectuat o singură comandă, numărul acestuia este evidențiat automat cu roșu.
Soluția prezentată ar trebui să automatizeze unele fluxuri de lucru și să simplifice analiza vizuală a datelor.
Umplerea automată a celulelor cu datele reale
În primul rând, pentru registrul cu numere de clienți, vom crea anteturi de coloane cu culoarea verde și lunile efective, care vor afișa automat perioadele de timp. Pentru a face acest lucru, introduceți următoarea formulă în celula B1:
Cum funcționează formula pentru generarea automată a lunilor ieșite?
În figură, formula returnează perioada timpului de ieșire începând de la data scrierii articolului: 17.09.2017. Primul argument al funcției DATE este o formulă imbricată care returnează întotdeauna anul curent la data de astăzi datorită funcțiilor: AN și AZI. Al doilea argument este numărul lunii (-1). Un număr negativ înseamnă că suntem interesați de ce lună a fost în timpul trecut. Exemplu de condiții pentru al doilea argument cu o valoare:
- 1 - înseamnă prima lună (ianuarie) din anul specificat în primul argument;
- 0 este acum 1 lună;
- -1 este de 2 luni. înapoi de la începutul anului în curs (adică: 10/01/2016).
Ultimul argument este ziua lunii specificată în al doilea argument. Ca rezultat, funcția DATE colectează toți parametrii într-o singură valoare, iar formula returnează data corespunzătoare.
După cum puteți vedea, funcția DATE folosește acum valoarea din celula B1 și crește numărul lunii cu 1 în raport cu celula anterioară. Ca rezultat, obținem 1 - ziua lunii următoare.
Acum copiați această formulă din celula C1 în restul titlurilor coloanelor din intervalul D1: L1.
Selectați intervalul de celule B1: L1 și selectați instrumentul: „HOME” - „Cells” - „Format Cells” sau apăsați doar combinația de taste CTRL + 1. În caseta de dialog care apare, în fila „Număr”, în secțiunea „Formate de numere:”, selectați opțiunea „(toate formatele)”. În câmpul „Tip:”, introduceți valoarea: MMM.YY (litere mari obligatorii). Datorită acestui fapt, vom obține o afișare scurtată a valorii datei în anteturile registrului, ceea ce va simplifica analiza vizuală și o va face mai confortabilă datorită unei mai bune lizibilități.
Notă! Când vine luna ianuarie (D1), formula schimbă automat data de la an la următorul.
Cum să evidențiați o coloană cu culoare în Excel după condiție
Acum trebuie să evidențiați celulele cu o culoare legată de luna curentă. Datorită acestui lucru, putem găsi cu ușurință coloana în care trebuie să introducem datele reale pentru această lună. Pentru asta:
Coloana de sub titlul de registru corespunzător este evidențiată automat cu verde, conform condițiilor noastre:
Cum funcționează formula pentru evidențierea condiționată a unei coloane?
Datorită faptului că, înainte de a crea regula de formatare condiționată, am acoperit întreaga secțiune tabelară pentru introducerea datelor din registru, formatarea va fi activă pentru fiecare celulă din acest interval B2: L15. Referința mixtă din formula B $ 1 (adresă absolută numai pentru rânduri și relativă pentru coloane) indică faptul că formula se va referi întotdeauna la primul rând al fiecărei coloane.
Evidențierea automată a unei coloane în funcție de starea lunii curente
Condiția principală pentru umplerea celulelor cu culoare: dacă intervalul B1: L1 conține aceeași dată ca prima zi a lunii curente, atunci celulele din întreaga coloană își schimbă culoarea în cea specificată în formatarea condiționată.
Notă!În termenii acestei formule, ultimul argument al funcției DATE este specificat ca 1, la fel ca și formulele din definirea datelor pentru anteturile coloanei registrului.
În cazul nostru, aceasta este umplerea verde a celulelor. Dacă ne deschidem registrul luna viitoare, atunci coloana corespunzătoare va fi evidențiată în verde, indiferent de ziua curentă.
Secțiunea tabelară este formatată, acum să o umplem cu valoarea text „comanda” într-o ordine mixtă a clienților pentru lunile curente și anterioare.
Cum să evidențiați condiționat celulele în roșu
Acum trebuie să evidențiem cu roșu celulele cu numărul clienților care nu au făcut o singură comandă de 3 luni. Pentru asta:
Numerele clienților sunt evidențiate cu roșu dacă rândul lor nu conține valoarea „comandă” în ultimele trei celule pentru luna curentă (inclusiv).
Analiza formulei de evidențiere a celulelor după condiție:
Să începem cu partea de mijloc a formulei noastre. Funcția OFFSET returnează o referință la un interval de decalaj față de intervalul de bază cu un număr specificat de rânduri și coloane. Referința returnată poate fi o singură celulă sau un întreg interval de celule. În plus, puteți defini numărul de rânduri și coloane returnate. În exemplul nostru, funcția returnează o referință la un interval de celule pentru ultimele 3 luni.
Partea importantă pentru condiția noastră de evidențiere se află în primul argument al funcției OFFSET. Acesta determină în ce lună începe compensarea. În acest exemplu, aceasta este celula D2, adică începutul anului este ianuarie. Desigur, pentru restul celulelor din coloană, numărul rândului pentru celula de bază va corespunde cu numărul rândului în care se află. Următoarele 2 argumente ale funcției OFFSET determină câte rânduri și coloane ar trebui să fie compensate. Deoarece calculele pentru fiecare client vor fi efectuate pe aceeași linie, specificăm valoarea offset-ului pentru linii –¬ 0.
În același timp, pentru a calcula valoarea celui de-al treilea argument (compensat cu coloane), folosim formula imbricată LUNA (AZI ()), care, în conformitate cu condițiile, returnează numărul lunii curente din anul curent. . Scădem numărul 4 din numărul lunii calculat prin formulă, adică în cazurile lunii noiembrie obținem un offset de 8 coloane. Și, de exemplu, pentru iunie - doar 2 coloane.
Ultimele două argumente ale funcției OFFSET specifică înălțimea (în număr de rânduri) și lățimea (în număr de coloane) intervalului returnat. În exemplul nostru, aceasta este o zonă de celule cu o înălțime de 1 rând și o lățime de 4 coloane. Acest interval acoperă coloanele din ultimele 3 luni și pe cea actuală.
Prima funcție din formula COUNTIF verifică condițiile: de câte ori apare valoarea textului „comanda” în intervalul returnat folosind funcția OFFSET. Dacă funcția returnează valoarea 0, înseamnă că nu a existat o singură comandă de la un client cu acest număr de 3 luni. Și în conformitate cu condițiile noastre, celula cu numărul acestui client este evidențiată cu roșu.
Dacă dorim să înregistrăm datele clienților, Excel este ideal pentru acest scop. Puteți înregistra cu ușurință în categoriile corespunzătoare numărul de produse comandate, precum și datele tranzacțiilor. Problema începe să apară treptat pe măsură ce volumul de date crește.
Dacă sunt atât de multe, încât petrecem câteva minute căutând o anumită poziție de registru și analizând informațiile introduse. În acest caz, merită să adăugați mecanisme la tabelul de înregistrare pentru a automatiza unele dintre procesele de lucru ale utilizatorului. Ceea ce am făcut.
