Aseară la emisiunea de radio Computer America a lui Craig Crossman, Joe din Boston a avut o întrebare:
Am o coloană cu numere de factură. Cum pot folosi Excel pentru a marca duplicatele?
Am sugerat să folosesc formate condiționale și formula COUNTIF. Iată detaliile despre cum să faci acest lucru să funcționeze.
Vrem să configurăm formatarea condiționată pentru întreaga gamă, dar este mai ușor să configurați un format condițional pentru prima celulă din interval și apoi să copiați acel format condițional. În cazul nostru, celula A1 are un antet al numărului de factură, așa că voi selecta celula A2 și din meniu, selectez Format> Formatare condiționată. Caseta de dialog Formatare condițională începe cu meniul derulant inițial care spune „Valoarea celulei este”. Dacă atingeți săgeata de lângă aceasta, puteți alege „Formula este”.
După selectarea „Formula Is”, caseta de dialog schimbă aspectul. În loc de casete pentru „Între x și y”, există acum o singură casetă de formulă. Această casetă de formulă este incredibil de puternică. Puteți introduce orice formulă pe care o puteți visa, atâta timp cât această formulă va fi evaluată la TRUE sau FALSE.
În cazul nostru, trebuie să folosim o formulă COUNTIF. Formula de introdus în casetă este
=COUNTIF(A:A,A2)>1
În limba engleză, aceasta spune, „căutați în întregul interval al coloanei A. Numărați câte celule din acel interval au aceeași valoare cu ceea ce este în A2. (Este foarte important ca„ A2 ”din formulă să indice celula curentă - celula în care setați formatarea condițională. Deci - dacă datele dvs. se află în coloana E și setați prima formatare condițională în E5, formula ar fi =COUNTIF(E:E,E5)>0). Apoi, comparăm pentru a vedea dacă acest număr este> 1. În mod ideal, fără duplicate, numărul va fi întotdeauna 1 - deoarece celula A2 este în interval - ar trebui să găsim exact o celulă în coloana A care conține aceeași valoare ca A2.
Faceți clic pe butonul Format …
Acum este timpul să selectați un format neplăcut. Există trei file în partea de sus a acestui dialog Formatare celule. Fila Font este de obicei prima, așa că ați putea selecta un font roșu, îndrăzneț, dar îmi place ceva mai neplăcut. De obicei dau clic pe fila Modele și aleg fie roșu aprins, fie galben strălucitor. Alegeți culoarea, apoi faceți clic pe OK pentru a închide caseta de dialog Formatare celule.
Veți vedea formatul selectat în caseta „Previzualizarea formatului de utilizat”. Faceți clic pe OK pentru a închide caseta de dialog Formatare condiționată …
… și nu se întâmplă nimic. Wow. Dacă este prima dată când configurați formatarea condiționată, ar fi foarte bine să primiți feedback aici că a funcționat. Dar, dacă nu aveți norocul că 1098 din celula A2 este un duplicat al altei celule, condiția nu este adevărată și se pare că nu s-a întâmplat nimic.
Trebuie să copiați formatarea condițională din A2 în jos în celelalte celule din zona dvs. Cu pervazul cursorului în A2, faceți Editare> Copiere. Apăsați Ctrl + Bara de spațiu pentru a selecta întreaga coloană. Editați> Lipiți special. În dialogul Lipire specială, faceți clic pe Formate. Faceți clic pe OK.
Aceasta va copia formatarea condițională în toate celulele din coloană. Acum - în sfârșit - vedeți câteva celule cu formatul roșu, indicând că aveți un duplicat.
Este informativ să mergeți la celula A3 și să priviți formatul condițional după copiere. Selectați A3, apăsați od pentru a afișa formatarea condiționată. Formula din caseta Formula este modificată pentru a calcula de câte ori apare A3 în coloana A: A.
Note
În întrebarea lui Joe, el avea doar 1700 de facturi în interval. Am configurat 65536 celule cu formatare condiționată și fiecare celulă compară celula actuală cu alte 65536 celule. În Excel 2005 - cu mai multe rânduri - problema va fi și mai gravă. Din punct de vedere tehnic, formula din primul pas ar fi putut fi:=COUNTIF($A$2:$A$1751,A2)>1
De asemenea, atunci când copiați formatul condițional pe întreaga coloană, ați fi putut selecta doar rândurile cu date înainte de a efectua lipirea formatelor speciale.
Mai mult
Cealaltă problemă pe care am descris-o după întrebare este că într-adevăr nu puteți sorta o coloană pe baza unui format condițional. Dacă trebuie să sortați aceste date astfel încât duplicatele să se afle într-o zonă, urmați acești pași. Mai întâi, adăugați un titlu la B1 numit „Duplicat?”. Introduceți această formulă în B2: =COUNTIF(A:A,A2)>1.
Cu indicatorul de celulă în B2, faceți clic pe mânerul de completare automată (micul pătrat din colțul din dreapta jos al celulei) pentru a copia formula până la capăt.
Acum puteți sorta după coloana B descrescătoare și A crescătoare pentru a avea facturile cu probleme în partea de sus a intervalului.
Această soluție presupune că doriți să evidențiați ambele facturi duplicate, astfel încât să puteți afla manual pe care să le ștergeți sau să le corectați. Dacă nu doriți să marcheze prima apariție a duplicatului, puteți ajusta cu formula pentru a fi: =COUNTIF($A$2:$A2,A2)>1. Este important să introduceți semnele de dolar exact așa cum se arată. Aceasta va privi toate celulele din celula curentă doar în sus, căutând intrări duplicate.
Mulțumesc lui Joe din Boston pentru întrebare!
