Cum se selectează rândurile în Excel după condiție? Selectarea rândurilor cu valori specificate. Completați celulele pe baza valorii din Microsoft Excel

Pentru a plasa corect accentele într-un document realizat în MS Excel, puteți crește dimensiunea celulelor, puteți alege alt font sau dimensiunea literelor sau puteți picta peste pătratele cărora trebuie neapărat să le acordați atenție.

Acum ne vom da seama cum să evidențiem celulele cu culoare în Excel sau să schimbăm culoarea celor care sunt deja completate. Cum să faci o celulă să schimbe culoarea în funcție de o anumită condiție, în funcție de valoarea introdusă în ea și cum să lucrezi cu regulile create.

Umplere simplă în bloc

Colorarea unuia sau mai multor blocuri în Excel nu este dificilă. Mai întâi, selectați-le și, în fila Acasă, faceți clic pe săgeata de lângă găleata de vopsea pentru a extinde lista. Selectați o culoare potrivită de acolo și, dacă nimic nu se potrivește, faceți clic pe „Alte culori”.

Apropo, puteți completa o linie întreagă în acest fel, doar mai întâi faceți clic pe numărul său pentru a-l evidenția. Puteți citi un articol separat despre selectarea celulelor în Excel.

Dacă lucrați cu o masă în care ceva a fost deja pictat, atunci puteți schimba culoarea blocurilor sau o puteți elimina complet astfel. Faceți clic pe el și din culori sau selectați unul nou, sau faceți clic pe opțiunea „Nu”.

În funcție de datele introduse

Acum să ne uităm la cum să facem o celulă să schimbe culoarea pe baza condiție dată. Pentru aceasta este folosită formatarea condiționată, despre care există un articol separat pe site.

Text

Să luăm ca exemplu următorul tabel. Să facem astfel încât roșul să corespundă unui măr, galben unei pere și portocaliu unei portocale.

Selectăm datele cu care vom lucra în cazul meu, acestea sunt numele fructelor. Apoi faceți clic „Formatare condiționată”, pe care îl vom folosi în continuare continuu. Din listă, faceți clic pe „Creează o regulă”.

Se deschide această fereastră. Selectați tipul din partea de sus - „Formatați numai celulele care conțin”, îl vom sărbători și în continuare. Mai jos indicăm condițiile: avem un text care conține anumite cuvinte. În ultimul câmp, fie dați clic pe butonul și selectați o celulă, fie introduceți text.

Diferența este că, prin plasarea unui link către o celulă (=$B$4), condiția se va schimba în funcție de ceea ce este introdus în ea. De exemplu, în loc de măr în B4, indic coacăze, regula se va schimba în consecință, iar blocurile cu același text vor fi completate. Și dacă introduceți măr în câmp, atunci acest cuvânt special va fi căutat și nu va depinde de nimic.

Aici, selectați o culoare de umplere și faceți clic pe OK. Pentru a vedea toate opțiunile, faceți clic pe butonul „Altele”.

Regula a fost creată și o salvăm făcând clic pe butonul „OK”.

Ca rezultat, toate blocurile care conțineau textul specificat au fost vopsite în roșu.

Sunt create reguli și pentru alte fructe, este selectată doar o opțiune de umplere diferită.

Numeric

Acum să ne uităm la datele numerice. Să plasăm numerele din coloana D pe un anumit fundal în funcție de condiția pe care am stabilit-o.

Selectați coloana, creați o regulă și indicați tipul acesteia. Apoi scriem - „Valoare” „mai mare decât” „15”. Puteți fie să introduceți manual ultimul număr, fie să specificați adresa celulei din care vor fi preluate datele. Ne decidem asupra umplerii, facem clic pe „OK”.

Blocurile care conțin numere mai mari decât cel selectat sunt colorate.

Să specificăm mai multe reguli pentru celulele selectate - selectați „Managementul regulilor”.

Aici, selectați totul așa cum am descris mai sus, trebuie doar să schimbați culoarea și să setați condiția „mai mic sau egal cu”.

Când totul este gata, faceți clic pe „Aplicați” și „OK”.

Totul funcționează, valorile sunt egale și sub 15 sunt vopsite în albastru pal.

Aici diferența dintre mărfurile importate și cele vândute ar fi clar vizibilă. Selectați două coloane și faceți clic „Reguli pentru evidențierea celulelor” – „Valori duplicate”. Alegeți o culoare potrivită. După aceasta, dacă două celule lângă cealaltă sunt colorate, înseamnă că fructele care au fost livrate au fost vândute.

Să folosim ultima coloană ca exemplu pentru a arăta cum să schimbi culoarea unei celule în funcție de valoarea specificată. Selectați-le și accesați „Managementul regulilor”.

Creăm unul nou pentru fragmentul curent dacă este necesar, în această listă derulantă puteți selecta fie pentru întreaga foaie, fie pentru alte foi.

Alege punctele necesareîn fereastra care se deschide. Voi completa cu verde închis toate valorile care sunt mai mari decât 90. Deoarece în ultimul câmp am indicat adresa (=$F$15), atunci dacă numărul 90 din celulă se schimbă, de exemplu, la 110, regula se va schimba de asemenea. Salvați modificările făcând clic pe butonul „OK”.

Voi crea o altă regulă, dar în ea voi evidenția cu verde deschis tot ceea ce este mai mic sau egal cu 90. Nu uitați să salvați totul făcând clic pe butoanele din dreapta jos.

Asta sa întâmplat. Din ultima coloană, puteți determina vizual rapid profitul din vânzare a cărui produs a fost mai mare.

De exemplu, dacă prețul se modifică, regulile vor fi și ele revizuite. Dacă valoarea devine mai mare sau mai mică decât valoarea specificată, culoarea celulei se va schimba automat.

Cred că ați observat că regula este creată nu numai pentru text și numere, ci și pentru date și în funcție de completarea și prezența erorilor în celule. Specificați condiția, alegeți o culoare și salvați.

Pentru a vedea ce ați adăugat, selectați intervalul și în fereastră „Managementul regulilor” voinţă lista completa. Folosind butoanele din partea de sus le puteți adăuga, modifica sau șterge.

Dacă valoarea din celulă satisface definit de utilizator condiție, apoi o puteți folosi pentru a selecta această celulă (de exemplu, schimbați fundalul). În acest articol vom merge mai departe - vom selecta întregul rând al tabelului care conține această celulă.

Lasă în rază A6:C16 Există un tabel cu o listă de lucrări, termene limită și stare de finalizare (vezi fișierul exemplu).

Sarcina 1 - valori text

Este necesar să se evidențieze linia care conține lucrarea unui anumit statut. De exemplu, dacă lucrarea nu a început, atunci linia va fi evidențiată cu roșu, dacă lucrarea nu a fost încă finalizată, apoi cu gri și, dacă este finalizată, apoi cu verde. Vom selecta linii folosind reguli.

Soluția 1

Să creăm un mic tabel cu stările jobului în interval E6:E9 .

Selectați un interval de celule A7:C17 , care conține o listă de lucrări și instalați-l prin meniu Acasă/ Culoare de umplere fundalul de umplere este roșu (presupunem că toate lucrările sunt inițial în stare Nu a început).

Asigurați-vă că este selectat intervalul de celule A7:C17 (A7 ar trebui să fie). Apelați comanda de meniu Formatare condiționată / Creați o regulă / Folosiți o formulă pentru a determina ce celule să formatați.

• în câmp" Formatați valori pentru care următoarea formulă este adevărată„trebuie să introduceți =$C7=$E$8 (în celulă E8 se găsește valoarea În curs). Vă rugăm să rețineți utilizarea de ;
• apăsați butonul Format;
• selectați fila Umple;
• alege culoarea gri ;
• Faceți clic pe OK.

ATENŢIE: Încă o dată atrag atenția asupra formulei =$C7=$E$8 . De obicei, utilizatorii intră =$C$7=$E$8 , adică introduce caracter suplimentar dolar.

Trebuie făcut actiuni similare pentru a evidenția lucrările în stare Terminat. Formula în acest caz va arăta ca =$C7=$E$9 și va seta culoarea de umplere la verde.

Ca urmare, tabelul nostru va lua următoarea formă.

Pentru a extinde rapid regulile de formatare condiționată la un nou rând dintr-un tabel, selectați celulele linie nouă (A17:C17 ) și apăsați . Regulile vor fi copiate pe linie 17 mesele.

Sarcina 2 - Date

Să presupunem că se ține un jurnal al vizitelor angajaților conferințe științifice(cm. fisa mostra fisier Date).

Din păcate, coloana Data vizitei nu este sortată și trebuie să evidențiați data primei și ultima vizită fiecare angajat. De exemplu, angajatul Kozlov a mers la conferință pentru prima dată pe 24 iulie 2009 și ultima dată - 18.07.2015.

Mai întâi, să creăm o formulă pentru formatarea condiționată în coloanele B și E. Dacă formula returnează TRUE, atunci rândul corespunzător va fi evidențiat, dacă FALSE, atunci nu va fi evidențiat.

În coloana D creat = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7, care definește data maximă pentru un anumit angajat.

Nota: Dacă trebuie să determinați data maximă indiferent de angajat, atunci formula va fi simplificată semnificativ =$B7=MAX($B$7:$B$16) și nu va fi necesară formula matrice.

Acum să selectăm toate celulele tabelului fără antet și să creăm o regulă. Să copiem formula în regulă (nu trebuie să o introduceți ca formulă matrice!).

Acum să presupunem că coloana cu date a fost sortată și doriți să selectați rânduri ale căror date de vizită se încadrează într-un anumit interval.

Pentru a face acest lucru, utilizați formula =ȘI($B23>$E$22,$B23<$E$23)

Pentru celule E22 Şi E23 cu date limită (evidențiate cu galben) utilizate 22 USD și 23 USD. Deoarece referința la acestea nu ar trebui să se schimbe în regulile UV pentru toate celulele din tabel.

Pentru celulă B22 se utilizează adresarea mixtă $B23, adică. referința la coloana B nu ar trebui să se schimbe (pentru aceasta există un semn $ înainte de B), dar referința la rând ar trebui să se schimbe în funcție de rândul tabelului (în caz contrar, toate valorile datei vor fi comparate cu data de la B23 ).

Astfel, regula UV de exemplu pentru o celulă A27 va arăta ca =ȘI($B27>$E$22,$B27<$E$23) , т.е. A27 va fi evidenţiat deoarece în acest rând data de la B27 se încadrează în intervalul specificat (pentru celulele din coloana A, selecția se va face în continuare în funcție de conținutul coloanei B din același rând - aceasta este „magia” adresei mixte $B23).

Și pentru celulă B31 regula UV va arăta ca =ȘI($B31>$E$22,$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 nu se încadrează în intervalul specificat.

Pentru a găsi și selecta rapid toate celulele care conțin anumite tipuri de date, cum ar fi formule, utilizați comanda Merge. În plus, folosind Du-te la Găsiți numai celule care îndeplinesc anumite criterii, de exemplu, ultima celulă dintr-o foaie de lucru care conține date sau formatare.

Faceți următoarele:

Mai multe informații

Puteți oricând să puneți o întrebare de la Comunitatea Excel Tech, să cereți ajutor în comunitatea Răspunsuri sau să sugerați o nouă funcție sau îmbunătățire a site-ului

Când lucrați cu tabele, valorile afișate în tabel sunt de importanță primordială. Dar o componentă importantă este și designul său. Unii utilizatori consideră acest lucru un factor minor și nu îi acordă prea multă atenție. Dar degeaba, pentru că o masă frumos proiectată este o condiție importantă pentru o mai bună percepție și înțelegere de către utilizatori. Vizualizarea datelor joacă un rol deosebit de important în acest sens. De exemplu, puteți folosi instrumente de vizualizare pentru a colora celulele tabelului în funcție de conținutul lor. Să aflăm cum se poate face acest lucru în Excel.

Desigur, este întotdeauna plăcut să ai un tabel bine conceput în care celulele sunt colorate diferit în funcție de conținut. Dar această caracteristică este relevantă în special pentru tabelele mari care conțin o cantitate semnificativă de date. În acest caz, umplerea celulelor cu culoare va face mult mai ușor pentru utilizatori să navigheze în această cantitate imensă de informații, deoarece, s-ar putea spune, va fi deja structurată.

Puteți încerca să colorați elementele foii manual, dar din nou, dacă masa este mare, acest lucru va dura o perioadă semnificativă de timp. În plus, într-o astfel de matrice de date, factorul uman poate juca un rol și se vor face erori. Ca să nu mai vorbim că tabelul poate fi dinamic, iar datele din el se modifică periodic și masiv. În acest caz, schimbarea manuală a culorii devine nerealistă.

Dar există o cale de ieșire. Pentru celulele care conțin valori dinamice (schimbătoare), se aplică formatarea condiționată, iar pentru datele statistice, puteți utiliza instrumentul „Găsiți și înlocuiți”.

Metoda 1: Formatarea condiționată

Folosind formatarea condiționată, puteți seta anumite limite de valoare la care celulele vor fi colorate într-o culoare sau alta. Colorarea va fi efectuată automat. Dacă valoarea celulei, din cauza unei modificări, depășește marginea, atunci acest element de foaie va fi revopsit automat.

Să vedem cum funcționează această metodă cu un exemplu specific. Avem un tabel cu veniturile întreprinderii în care datele sunt defalcate lunar. Trebuie să evidențiem în diferite culori acele elemente în care valoarea veniturilor este mai mică 400000 ruble, din 400000 la 500000 ruble și depășește 500000 ruble

1. Evidențiem coloana care conține informații despre veniturile companiei. Apoi treceți la filă "Acasă". Faceți clic pe butonul „Formatare condiționată”, care se află pe panglica din cutia de instrumente "Stiluri". În lista care se deschide, selectați elementul „Gestionarea regulilor...”.



2. Se deschide fereastra pentru gestionarea regulilor de formatare condiționată. În câmp „Afișați regulile de formatare pentru” valoarea trebuie setată „Fragmentul actual”. Implicit, este exact ceea ce ar trebui indicat acolo, dar pentru orice eventualitate, verificați și, în caz de discrepanță, modificați setările conform recomandărilor de mai sus. După aceasta, faceți clic pe butonul „Creează o regulă...”.



3. Se deschide fereastra pentru crearea unei reguli de formatare. În lista de tipuri de reguli, selectați o poziție . În blocul de descriere a regulii din primul câmp, comutatorul ar trebui să fie în poziție "Valori". În al doilea câmp, setați comutatorul în poziție "Mai puțin". În al treilea câmp indicăm valoarea elementelor de foaie care conțin o valoare mai mică decât care vor fi vopsite într-o anumită culoare. În cazul nostru, această valoare va fi 400000 . După aceea, faceți clic pe butonul "Format…".



4. Se deschide fereastra Format Cells. Trecerea la fila "Umple". Selectați culoarea de umplere pe care doriți să evidențiați celulele care conțin o valoare mai mică decât 400000 . După aceea, faceți clic pe butonul "BINE"în partea de jos a ferestrei.



5. Ne întoarcem la fereastra pentru crearea unei reguli de formatare și facem și clic pe butonul de acolo "BINE".



6. După această acțiune vom fi din nou redirecționați către Manager de reguli de formatare condiționată. După cum puteți vedea, o regulă a fost deja adăugată, dar trebuie să adăugăm încă două. Așa că apăsăm din nou butonul „Creează o regulă...”.



7. Și din nou ne găsim în fereastra de creare a regulilor. Trecerea la secțiune „Formatați numai celulele care conțin”. În primul câmp al acestei secțiuni lăsăm parametrul „Valoarea celulei”, iar în al doilea setăm comutatorul în poziție "Între". În al treilea câmp trebuie să specificați valoarea inițială a intervalului în care vor fi formatate elementele foii. În cazul nostru acest număr 400000 . În al patrulea indicăm valoarea finală a acestui interval. Se va ridica la 500000 . După aceasta, faceți clic pe butonul "Format…".



8. În fereastra de formatare, treceți din nou la filă "Umple", dar de data aceasta selectăm o culoare diferită, apoi facem clic pe butonul "BINE".



9. După revenirea la fereastra de creare a regulilor, faceți clic și pe butonul "BINE".



10. După cum vedem, în Manager de reguli Am creat deja două reguli. Astfel, rămâne să creăm al treilea. Faceți clic pe butonul „Creează o regulă”.



11. În fereastra de creare a regulilor, treceți din nou la secțiune „Formatați numai celulele care conțin”. În primul câmp lăsăm opțiunea „Valoarea celulei”. În al doilea câmp, setați comutatorul la poliție "Mai mult". În al treilea câmp introducem numărul 500000 . Apoi, ca și în cazurile anterioare, faceți clic pe butonul "Format…".



12. În fereastră „Format celulă” treceți din nou la filă "Umple". De data aceasta alegem o culoare diferită de cele două cazuri anterioare. Faceți clic pe butonul "BINE".



13. În fereastra de creare a regulilor, apăsați din nou butonul "BINE".



14. Se deschide Manager de reguli. După cum puteți vedea, toate cele trei reguli au fost create, așa că faceți clic pe butonul "BINE".



15. Acum elementele tabelului sunt colorate în funcție de condițiile și limitele specificate în setările de formatare condiționată.



16. Dacă modificăm conținutul uneia dintre celule, trecând dincolo de limitele uneia dintre regulile specificate, atunci acest element de foaie își va schimba automat culoarea.

De asemenea, puteți utiliza formatarea condiționată într-un mod ușor diferit pentru a colora elementele foii de lucru.

Metoda 2: Utilizarea instrumentului Găsiți și selectați

Dacă tabelul conține date statice care nu sunt planificate să se modifice în timp, atunci puteți folosi un instrument pentru a schimba culoarea celulelor pe baza conținutului lor, numit „Găsiți și selectați”. Acest instrument vă va permite să găsiți valorile specificate și să schimbați culoarea acestor celule la cea dorită de utilizator. Dar vă rugăm să rețineți că atunci când modificați conținutul elementelor foii, culoarea nu se va schimba automat, ci va rămâne aceeași. Pentru a schimba culoarea în cea actuală, va trebui să repetați procedura din nou. Prin urmare, această metodă nu este optimă pentru tabelele cu conținut dinamic.

Să vedem cum funcționează acest lucru folosind un exemplu specific, pentru care vom lua același tabel cu veniturile întreprinderii.

1. Selectați coloana cu datele care trebuie formatate color. Apoi accesați fila "Acasă"și apăsați butonul „Găsiți și selectați”, care se află pe panglica din cutia de instrumente "Editare". În lista care se deschide, faceți clic pe element "Găsi".



2. Se deschide fereastra „Găsiți și înlocuiți”în filă "Găsi". În primul rând, să găsim valorile până la 400000 ruble Deoarece nu avem o singură celulă care să conţină o valoare mai mică decât 300000 ruble, atunci, de fapt, trebuie să selectăm toate elementele care conțin numere în intervalul de la 300000 la 400000 . Din păcate, nu puteți specifica direct acest interval, ca în cazul utilizării formatării condiționate, în această metodă.

   Dar există o oportunitate de a face lucrurile puțin diferit, ceea ce ne va da același rezultat. Puteți specifica următorul model în bara de căutare "3????". Un semn de întrebare înseamnă orice simbol. Astfel, programul va căuta toate numerele din șase cifre care încep cu cifra "3". Adică, rezultatele căutării vor include valori în interval 300000 – 400000 , care este ceea ce avem nevoie. Dacă tabelul ar avea numere mai mici 300000 sau mai putin 200000 , atunci pentru fiecare interval de o sută de mii căutarea ar trebui efectuată separat.

   Introduceți expresia "3????"în câmp "Găsi"și apăsați butonul „Găsiți totul».



3. După aceea, rezultatele căutării se deschid în partea de jos a ferestrei. Faceți clic stânga pe oricare dintre ele. Apoi introducem combinația de taste Ctrl+A. După aceasta, sunt selectate toate rezultatele căutării și, în același timp, elementele din coloana către care sunt selectate aceste rezultate.



4. După ce elementele din coloană sunt selectate, nu vă grăbiți să închideți fereastra „Găsiți și înlocuiți”. În timp ce în filă "Acasă" la care ne-am mutat mai devreme, mergeți la panglica la blocul de instrumente „Font”. Faceți clic pe triunghiul din dreapta butonului „Umplere culoare”. Se deschide o selecție de culori de umplere diferite. Selectăm culoarea pe care dorim să o aplicăm elementelor de foi care conțin valori mai mici decât 400000 ruble



5. După cum puteți vedea, toate celulele coloanei conțin valori mai mici decât 400000 ruble, evidențiate în culoarea selectată.



6. Acum trebuie să colorăm elementele care conțin valori în intervalul de la 400000 la 500000 ruble Acest interval include numere care se potrivesc cu modelul "4??????". Introduceți-l în câmpul de căutare și faceți clic pe butonul „Găsiți pe toate”, după ce am selectat anterior coloana de care avem nevoie.



7. La fel cu ora anterioară în rezultatele căutării, selectăm întregul rezultat obținut prin apăsarea unei combinații de taste rapide CTRL+A. După aceea, treceți la pictograma de selecție a culorii de umplere. Facem clic pe el și facem clic pe pictograma nuanței de care avem nevoie, care va colora elementele foii, unde valorile sunt în intervalul de la 400000 la 500000 .



8. După cum putem vedea, după această acțiune, toate elementele tabelului cu date în intervalul s 400000 De 500000 evidențiat cu culoarea selectată.



9. Acum trebuie doar să selectăm ultimul interval de valori – mai mult 500000 . Și aici suntem norocoși, deoarece toate numerele sunt mai multe 500000 sunt în intervalul de la 500000 la 600000 . Prin urmare, în câmpul de căutare introducem expresia „5????”și apăsați butonul „Găsiți pe toate”. Dacă ar exista valori mai mari decât 600000 , atunci ar trebui să căutăm suplimentar expresia "6????" etc.



10. Din nou, evidențiați rezultatele căutării folosind combinația Ctrl+A. Apoi, folosind butonul de pe panglică, selectați o nouă culoare pentru a umple intervalul care depășește 500000 prin aceeași analogie ca și mai devreme.



11. După cum puteți vedea, după această acțiune, toate elementele coloanei vor fi completate, conform valorii numerice care este plasată în ele. Acum puteți închide fereastra de căutare făcând clic pe butonul de închidere standard din colțul din dreapta sus al ferestrei, deoarece problema noastră poate fi considerată rezolvată.



12. Dar dacă înlocuim numărul cu altul care depășește limitele stabilite pentru o anumită culoare, atunci culoarea nu se va schimba, așa cum a fost cazul în metoda anterioară. Acest lucru indică faptul că această opțiune va funcționa în mod fiabil numai în acele tabele în care datele nu se modifică.

După cum puteți vedea, există două moduri de a colora celulele în funcție de valorile numerice pe care le conțin: folosind formatarea condiționată și utilizarea „Găsiți și înlocuiți”. Prima metodă este mai progresivă, deoarece vă permite să definiți mai clar condițiile în care vor fi evidențiate elementele foii. În plus, cu formatarea condiționată, culoarea unui element se schimbă automat dacă conținutul acestuia se modifică, ceea ce a doua metodă nu poate face. Cu toate acestea, umplerea celulelor în funcție de valoare folosind instrumentul „Găsiți și înlocuiți” Poate fi folosit și, dar numai în tabele statice.