2 moduri de a schimba fundalul în Excel

În acest articol, veți învăța două moduri simple de a schimba fundalul celulelor pe baza conținutului lor în cele mai recente versiuni de Excel. De asemenea, veți înțelege ce formule să utilizați pentru a schimba nuanța celulelor sau a celulelor în care formulele sunt scrise incorect sau în care nu există deloc informații.

Toată lumea știe că editarea fundalului unei celule simple este o procedură simplă. Faceți clic pe „culoarea de fundal”. Dar ce se întâmplă dacă aveți nevoie de corecție de culoare bazată pe un anumit conținut de celule? Cum pot face ca acest lucru să se întâmple automat? Ceea ce urmează este o serie de informații utile care vă vor permite să găsiți modalitatea corectă de a îndeplini toate aceste sarcini.

Schimbarea dinamică a culorii de fundal a celulei

Sarcină: aveți un tabel sau un set de valori și trebuie să editați culoarea de fundal a celulelor în funcție de numărul introdus acolo. De asemenea, trebuie să vă asigurați că nuanța răspunde la schimbarea valorilor.

Soluţie: Pentru această sarcină, funcția de „formatare condiționată” din Excel este furnizată pentru a colora celulele cu numere mai mari decât X, mai mici decât Y sau între X și Y.

Să presupunem că aveți un set de produse în diferite state cu prețurile lor și trebuie să știți care dintre ele costă mai mult de 3,7 USD. Prin urmare, am decis să evidențiem acele produse care sunt peste această valoare în roșu. Și celulele care au o valoare similară sau mai mare, s-a decis să se coloreze într-o nuanță verde.

Notă: Captura de ecran a fost făcută în versiunea 2010 a programului. Dar acest lucru nu afectează nimic, deoarece succesiunea acțiunilor este aceeași, indiferent de versiunea - cea mai recentă sau nu - o folosește persoana.

Deci, iată ce trebuie să faceți (pas cu pas):

1. Selectați celulele în care nuanța ar trebui editată. De exemplu, intervalul $B$2:$H$10 (numele coloanelor și prima coloană, care listează numele statelor, sunt excluse din eșantion).

2. Faceţi clic pe „Acasă“ in grup "Stil". Va fi un articol "Formatarea condițională". De asemenea, acolo trebuie să selectați „Noua regulă”. În versiunea în limba engleză a Excel, secvența pașilor este următoarea: „Acasă”, „Grup de stiluri”, „Formatare condiționată > Regulă nouă».

3. În fereastra care se deschide, bifați caseta „Formatați numai celulele care conțin” („Formatați numai celulele care conțin” în versiunea în limba engleză).

4. În partea de jos a acestei ferestre sub inscripție „Formatați numai celulele care îndeplinesc următoarea condiție” (Formatați numai celulele cu) puteți atribui reguli după care se va efectua formatarea. Am ales formatul pentru valoarea specificată în celule, care trebuie să fie mai mare de 3.7, după cum puteți vedea din captura de ecran: 

5. Apoi, faceți clic pe butonul "Format". Va apărea o fereastră cu o zonă de selecție a culorii de fundal în stânga. Dar înainte de asta, trebuie să deschideți fila "Completati" ("Completati"). În acest caz, este roșu. După aceea, faceți clic pe butonul „OK”.

6. Apoi veți reveni la fereastră „Noua regulă de formatare”, dar deja în partea de jos a acestei ferestre puteți previzualiza cum va arăta această celulă. Dacă totul este în regulă, trebuie să faceți clic pe butonul „OK”.

Ca rezultat, obțineți ceva de genul acesta:

În continuare, trebuie să mai adăugăm o condiție, adică să schimbăm fundalul celulelor cu valori mai mici de 3.45 în verde. Pentru a efectua această sarcină, trebuie să faceți clic pe „Noua regulă de formatare” și repetați pașii de mai sus, doar condiția trebuie setată ca „mai puțin decât sau echivalent” (în versiunea în limba engleză „mai puțin sau egal cu”, apoi scrieți valoarea. La sfârșit, trebuie să faceți clic pe butonul „OK”.

Acum tabelul este formatat în acest fel.

Afișează cele mai mari și mai mici prețuri la combustibil din diferite state și puteți determina imediat unde situația este cea mai optimistă (în Texas, desigur).

Recomandare: Dacă este necesar, puteți utiliza o metodă de formatare similară, editând nu fundalul, ci fontul. Pentru a face acest lucru, în fereastra de formatare care a apărut în etapa a cincea, trebuie să selectați fila „Font” și urmați instrucțiunile oferite în fereastră. Totul este intuitiv clar și chiar și un începător își poate da seama.

Ca rezultat, veți obține un tabel ca acesta:

Cum să păstrați culoarea celulei aceeași, chiar dacă valoarea se schimbă?

Sarcină: Trebuie să colorați fundalul astfel încât să nu se schimbe niciodată, chiar dacă fundalul se va schimba în viitor.

Soluţie: găsiți toate celulele cu un anumit număr folosind funcția Excel "Găsiți toate" „Găsiți toate” sau supliment „Selectați celule speciale” („Selectați celule speciale”) și apoi editați formatul celulei utilizând funcția "Celule de format" ("Celule de format").

Aceasta este una dintre acele situații rare care nu sunt tratate în manualul Excel și chiar și pe Internet, soluția la această problemă poate fi găsită destul de rar. Ceea ce nu este surprinzător, deoarece această sarcină nu este standard. Dacă doriți să editați fundalul permanent, astfel încât să nu se schimbe niciodată până când nu este ajustat manual de către utilizatorul programului, trebuie să urmați instrucțiunile de mai sus.

Selectați toate celulele care conțin o anumită condiție

Există mai multe metode posibile, în funcție de ce fel de valoare anume se găsește.

Dacă trebuie să desemnați celule cu o anumită valoare cu un fundal special, trebuie să mergeți la fila „Acasă“ Și alegeți „Găsiți și selectați” – „Găsiți”.

Introduceți valorile necesare și faceți clic pe "Găsiți toate".

Ajutor: Puteți face clic pe butonul "Opțiuni" din dreapta pentru a profita de unele setări suplimentare: unde să căutați, cum să vizualizați, dacă să respectați literele mari și mici și așa mai departe. De asemenea, puteți scrie caractere suplimentare, cum ar fi un asterisc (*), pentru a găsi toate liniile care conțin aceste valori. Dacă utilizați un semn de întrebare, puteți găsi orice caracter.

În exemplul nostru anterior, dacă dorim să găsim toate cotațiile de combustibil între 3,7 USD și 3,799 USD, am putea rafina interogarea noastră de căutare.

Acum selectați oricare dintre valorile pe care programul le-a găsit în partea de jos a casetei de dialog și faceți clic pe una dintre ele. După aceea, apăsați combinația de taste „Ctrl-A” pentru a selecta toate rezultatele. Apoi, faceți clic pe butonul „Închidere”. 

Iată cum să selectați toate celulele cu valori specifice utilizând caracteristica Găsește toate. În exemplul nostru, trebuie să găsim toate prețurile combustibilului peste 3,7 USD și, din păcate, Excel nu vă permite să faceți acest lucru folosind funcția Găsiți și înlocuiți.

„Butoiul de miere” iese la iveală aici, deoarece există un alt instrument care va ajuta la sarcini atât de complexe. Se numește Selectați celule speciale. Acest supliment (care trebuie instalat în Excel separat) va ajuta:

• găsiți toate valorile într-un anumit interval, de exemplu între -1 și 45,
• obțineți valoarea maximă sau minimă într-o coloană,
• găsiți un șir sau un interval,
• găsiți celule după culoarea de fundal și multe altele.

După instalarea suplimentului, faceți clic pe butonul „Selectați după valoare” („Selectați după valoare”) și apoi rafinați interogarea de căutare în fereastra de supliment. În exemplul nostru, căutăm numere mai mari de 3,7. presa "Alege" („Selectați”) și într-o secundă veți obține un rezultat ca acesta:

Dacă sunteți interesat de supliment, puteți descărca o versiune de probă de la link-ul.

Schimbarea fundalului celulelor selectate prin fereastra „Format Cells”.

Acum, după ce toate celulele cu o anumită valoare au fost selectate folosind una dintre metodele descrise mai sus, rămâne să specificați culoarea de fundal pentru ele.

Pentru a face acest lucru, trebuie să deschideți o fereastră „Format celulă”apăsând tasta Ctrl + 1 (puteți, de asemenea, să faceți clic dreapta pe celulele selectate și să faceți clic stânga pe elementul „formatare celule”) și să ajustați formatarea de care aveți nevoie.

Vom alege o nuanță portocalie, dar puteți alege oricare alta.

Dacă trebuie să editați culoarea de fundal fără a modifica alți parametri de aspect, puteți pur și simplu să faceți clic pe „umplere de culoare” si alege culoarea care ti se potriveste perfect.

Rezultatul este un tabel ca acesta:

Spre deosebire de tehnica anterioară, aici culoarea celulei nu se va schimba chiar dacă valoarea este editată. Acesta poate fi folosit, de exemplu, pentru a urmări dinamica mărfurilor dintr-un anumit grup de preț. Valoarea lor s-a schimbat, dar culoarea a rămas aceeași.

Editarea culorii de fundal pentru celule speciale (necomplete sau cu erori la scrierea unei formule)

Similar cu exemplul anterior, utilizatorul are capacitatea de a edita culoarea de fundal a celulelor speciale în două moduri. Există opțiuni statice și dinamice.

Aplicarea unei formule pentru a edita un fundal

Aici culoarea celulei va fi editată automat în funcție de valoarea acesteia. Această metodă ajută foarte mult utilizatorii și este solicitată în 99% din situații.

Ca exemplu, puteți folosi tabelul anterior, dar acum unele dintre celule vor fi goale. Trebuie să stabilim care dintre ele nu conțin citiri și să edităm culoarea de fundal.

1. Pe fila „Acasă“ trebuie să faceți clic pe "Formatarea condițională" ->  „Noua regulă” (la fel ca în pasul 2 din prima secțiune „Schimbați dinamic culoarea de fundal”.

2. Apoi, trebuie să selectați elementul „Folosește o formulă pentru a determina…”.

3. Introduceți formula =EsteBlank() (ISBLANK în versiune), dacă doriți să editați fundalul unei celule goale sau =IsError() (ISERROR în versiune), dacă trebuie să găsiți o celulă în care există o formulă scrisă eronat. Deoarece în acest caz trebuie să edităm celulele goale, introducem formula =EsteBlank(), apoi plasați cursorul între paranteze și faceți clic pe butonul de lângă câmpul de introducere a formulei. După aceste manipulări, o serie de celule ar trebui selectată manual. În plus, puteți specifica singur intervalul, de exemplu, =Este necompletat(B2:H12).

4. Faceți clic pe butonul „Format” și selectați culoarea de fundal corespunzătoare și faceți totul așa cum este descris în paragraful 5 al secțiunii „Modificare dinamică a culorii de fundal a celulei”, apoi faceți clic pe „OK”. Acolo puteți vedea și ce culoare va fi celulei. Fereastra va arăta cam așa.

5. Dacă vă place fundalul celulei, trebuie să faceți clic pe butonul „OK”, iar modificările vor fi imediat făcute în tabel.

Schimbarea statică a culorii de fundal a celulelor speciale

În această situație, odată alocată, culoarea de fundal va rămâne așa, indiferent de modul în care se schimbă celula.

Dacă trebuie să schimbați permanent celulele speciale (vide sau care conțin erori), urmați aceste instrucțiuni:

1. Selectați documentul sau mai multe celule și apăsați F5 pentru a deschide fereastra Go To, apoi apăsați butonul "A scoate in evidenta".
2. În caseta de dialog care se deschide, selectați butonul „Alte libere” sau „Celele goale” (în funcție de versiunea programului – sau engleză) pentru a selecta celulele goale.
3. Dacă trebuie să evidențiați celulele care au formule cu erori, ar trebui să selectați elementul „Formule” și lăsați o singură casetă de selectare lângă cuvântul „Erori”. După cum urmează din captura de ecran de mai sus, celulele pot fi selectate în funcție de orice parametri și fiecare dintre setările descrise este disponibilă dacă este necesar.
4. În cele din urmă, trebuie să schimbați culoarea de fundal a celulelor selectate sau să le personalizați în orice alt mod. Pentru a face acest lucru, utilizați metoda descrisă mai sus.

Nu uitați că modificările de formatare efectuate în acest fel vor persista chiar dacă completați goluri sau modificați tipul de celulă special. Desigur, este puțin probabil ca cineva să vrea să folosească această metodă, dar în practică se poate întâmpla orice.

Cum să profitați la maximum de Excel?

În calitate de utilizatori intensi ai Microsoft Excel, ar trebui să știți că acesta conține multe caracteristici. Unele dintre ele le cunoaștem și le iubim, în timp ce altele rămân misterioase pentru utilizatorul obișnuit, iar un număr mare de bloggeri încearcă să arunce măcar puțină lumină asupra lor. Există însă sarcini comune pe care fiecare dintre noi va trebui să le execute, iar Excel nu introduce unele caracteristici sau instrumente pentru automatizarea unor acțiuni complexe.

Iar soluția la această problemă este suplimentele (suplimente). Unele dintre ele sunt distribuite gratuit, altele – pentru bani. Există multe instrumente similare care pot îndeplini diferite funcții. De exemplu, găsiți duplicate în două fișiere fără formule criptice sau macrocomenzi.

Dacă combinați aceste instrumente cu funcționalitatea de bază a Excel, puteți obține rezultate foarte bune. De exemplu, puteți afla ce prețuri la combustibil s-au schimbat și apoi găsiți duplicate în fișierul pentru anul trecut.

Vedem că formatarea condiționată este un instrument la îndemână care vă permite să automatizați lucrul pe tabele fără abilități specifice. Acum știți cum să umpleți celulele în mai multe moduri, în funcție de conținutul lor. Acum rămâne doar să o punem în practică. Mult noroc!