Aplicarea formatării condiționate într-un tabel pivot poate fi un pic dificil.
Având în vedere că tabelele pivot sunt atât de dinamice, iar datele din backend se pot schimba des, trebuie să cunoașteți modul corect de a utiliza formatarea condiționată într-un tabel pivot în Excel.
Calea greșită de a aplica formatarea condiționată unui tabel pivot
Să vedem mai întâi modul regulat de aplicare a formatării condiționate într-un tabel pivot.
Să presupunem că aveți un tabel pivot așa cum se arată mai jos:
În setul de date de mai sus, data este în rânduri și avem date despre vânzări în coloane.
Iată modul obișnuit de a aplica formatarea condițională oricărui set de date:
- Selectați datele (în acest caz, aplicăm formatarea condițională la B5: D14).
- Accesați Acasă -> Formatare condiționată -> Reguli de sus / de jos -> Peste medie.
- Specificați formatul (folosesc „Green Fill with Dard Green Text”).
- Faceți clic pe OK.
Aceasta va aplica formatarea condiționată așa cum se arată mai jos:
Toate punctele de date care sunt peste media întregului set de date au fost evidențiate.
Problema cu această metodă este că a aplicat formatul condițional unui interval fix de celule (B5: D14). Dacă adăugați date în backend și reîmprospătați acest tabel pivot, formatarea condiționată nu i se va aplica.
De exemplu, mă întorc la setul de date și adaug date pentru încă o dată (11 ianuarie 2015). Aceasta este ceea ce obțin când reîmprospătez tabelul pivot.
După cum puteți vedea în imaginea de mai sus, datele pentru 11 ianuarie 2015 nu sunt evidențiate (deși ar trebui ca valorile pentru Magazinul 1 și Magazinul 3 să fie peste medie).
Motivul, așa cum am menționat mai sus, este că formatarea condițională a fost aplicată pe un interval fix (B5: D14) și nu se extinde la date noi din tabelul pivot.
Modul corect de a aplica formatarea condiționată unui tabel pivot
Iată două metode pentru a vă asigura că formatarea condiționată funcționează chiar și atunci când există date noi în backend.
Metoda 1 - Utilizarea pictogramei de formatare a tabelului pivot
Această metodă utilizează pictograma Opțiuni de formatare a tabelului pivot care apare imediat ce aplicați formatarea condițională într-un tabel pivot.
Iată pașii pentru a face acest lucru:
- Selectați datele pe care doriți să aplicați formatarea condiționată.
- Accesați Acasă -> Formatare condiționată -> Reguli de sus / de jos -> Peste medie.
- Specificați formatul (folosesc „Green Fill with Dard Green Text”).
- Faceți clic pe Ok.
- Când urmați pașii de mai sus, aplică formatarea condițională pe setul de date. În partea dreaptă jos a setului de date, veți vedea pictograma Opțiuni de formatare:
- Faceți clic pe pictogramă. Va afișa trei opțiuni într-o listă derulantă:
- Celule selectate (care ar fi selectate implicit).
- Toate celulele cu valori „Suma veniturilor”.
- Toate celulele cu valori „Suma veniturilor” pentru „Data” și „Magazin”.
- Selectați a treia opțiune - Toate celulele care afișează valorile „Suma veniturilor” pentru „Data” și „Magazin”.
Acum, când adăugați date în partea din spate și reîmprospătați tabelul pivot, datele suplimentare vor fi acoperite automat de formatarea condiționată.
Înțelegerea celor trei opțiuni:
- Celule selectate: Aceasta este opțiunea implicită în care formatarea condițională este aplicată numai pe celulele selectate.
- Toate celulele cu valori „Suma veniturilor”: În această opțiune, ia în considerare toate celulele care afișează valorile Sumei veniturilor (sau orice date aveți în secțiunea valorilor din tabelul pivot).
- Problema cu această opțiune este că va acoperi și valorile Grand Total și îi va aplica formatarea condiționată.
- Toate celulele cu valori „Suma veniturilor” pentru „Data” și „Magazin”: Aceasta este cea mai bună opțiune în acest caz. Aplică formatarea condițională tuturor valorilor (cu excepția totalelor mari) pentru combinația de dată și magazin. Chiar dacă adăugați mai multe date în partea din spate, această opțiune se va ocupa de ele.
Notă:
- Pictograma Opțiuni de formatare este vizibilă imediat după ce aplicați formatarea condiționată pe setul de date. Dacă dispare dacă faceți altceva (editați o celulă sau modificați fontul / alinierea etc.).
- Formatarea condiționată dispare dacă modificați câmpurile rând / coloană. De exemplu, dacă eliminați câmpul Data și îl aplicați din nou, formatarea condiționată s-ar pierde.
Metoda 2 - Utilizarea Managerului de reguli de formatare condiționată
În afară de utilizarea pictogramei Opțiuni de formatare, puteți utiliza și caseta de dialog Manager reguli de formatare condiționată pentru a aplica formatarea condiționată într-un tabel pivot.
Această metodă este utilă atunci când ați aplicat deja formatarea condițională și doriți să modificați regulile.
Iată cum se face:
- Selectați datele pe care doriți să aplicați formatarea condiționată.
- Accesați Acasă -> Formatare condiționată -> Reguli de sus / de jos -> Peste medie.
- Specificați formatul (folosesc „Green Fill with Dard Green Text”).
- Faceți clic pe Ok. Aceasta va aplica formatarea condițională celulelor selectate.
- Accesați Acasă -> Formatare condiționată -> Gestionați regulile.
- În Managerul de reguli de formatare condiționată, selectați regula pe care doriți să o editați și faceți clic pe butonul Editați regula.
- În caseta de dialog Edit Rule, veți vedea aceleași trei opțiuni:
- Celule selectate.
- Toate celulele cu valori „Suma veniturilor”.
- Toate celulele cu valori „Suma veniturilor” pentru „Data” și „Magazin”.
- Selectați a treia opțiune și faceți clic pe OK.
Aceasta va aplica formatarea condițională tuturor celulelor pentru câmpurile „Data” și „Magazin”. Chiar dacă modificați datele de backend (adăugați mai multe date de magazin sau date de date), formatarea condiționată ar fi funcțională.
Notă: Formatarea condiționată dispare dacă modificați câmpurile rând / coloană. De exemplu, dacă eliminați câmpul Data și îl aplicați din nou, formatarea condiționată s-ar pierde.
