La scorsa notte, nel programma radiofonico Computer America di Craig Crossman, Joe di Boston aveva una domanda:
Ho una colonna di numeri di fattura. Come posso utilizzare Excel per contrassegnare i duplicati?
Ho suggerito di utilizzare formati condizionali e la formula CONTA.SE. Ecco i dettagli su come farlo funzionare.
Vogliamo impostare la formattazione condizionale per l'intero intervallo, ma è più semplice impostare un formato condizionale per la prima cella dell'intervallo e quindi copiare quel formato condizionale. Nel nostro caso, la cella A1 ha un'intestazione del numero di fattura, quindi selezionerò la cella A2 e dal menu selezionerò Formato> Formattazione condizionale. La finestra di dialogo Formattazione condizionale inizia con il menu a discesa iniziale che dice "Il valore della cella è". Se tocchi la freccia accanto a questo, puoi scegliere "Formula Is".
Dopo aver selezionato "La formula è", la finestra di dialogo cambia aspetto. Invece delle caselle per "Tra x e y", ora è presente un'unica casella formula. Questa scatola della formula è incredibilmente potente. Puoi digitare qualsiasi formula che puoi immaginare, purché quella formula valuterà VERO o FALSO.
Nel nostro caso, dobbiamo usare una formula CONTA.SE. La formula da digitare nella casella è
=COUNTIF(A:A,A2)>1
In inglese, questo dice: "guarda attraverso l'intero intervallo della colonna A. Conta quante celle in quell'intervallo hanno lo stesso valore di quello che è in A2. (È molto importante che" A2 "nella formula punti al cella corrente - la cella in cui stai impostando la formattazione condizionale. Quindi, se i tuoi dati sono nella colonna E e stai impostando la prima formattazione condizionale in E5, la formula sarebbe =COUNTIF(E:E,E5)>0). Quindi, confrontiamo per vedere se questo conta è> 1. Idealmente, senza duplicati, il conteggio sarà sempre 1 - poiché la cella A2 è nell'intervallo - dovremmo trovare esattamente una cella nella colonna A che contiene lo stesso valore di A2.
Fare clic sul pulsante Formato…
Ora è il momento di selezionare un formato odioso. Ci sono tre schede nella parte superiore di questa finestra di dialogo Formato celle. La scheda Carattere è solitamente la prima, quindi puoi selezionare un carattere grassetto e rosso, ma mi piace qualcosa di più odioso. Di solito faccio clic sulla scheda Motivi e scelgo rosso brillante o giallo brillante. Scegli il colore, quindi fai clic su OK per chiudere la finestra di dialogo Formato celle.
Vedrai il formato selezionato nella casella "Anteprima del formato da utilizzare". Fare clic su OK per chiudere la finestra di dialogo Formattazione condizionale …
… e non succede niente. Wow. Se è la prima volta che imposti la formattazione condizionale, sarebbe davvero bello ricevere un feedback sul fatto che ha funzionato. Ma, a meno che tu non sia abbastanza fortunato che il 1098 nella cella A2 sia un duplicato di qualche altra cella, la condizione non è vera e sembra che non sia successo nulla.
È necessario copiare la formattazione condizionale da A2 alle altre celle nell'intervallo. Con il bordo del cursore in A2, fai Modifica> Copia. Premi Ctrl + Barra spaziatrice per selezionare l'intera colonna. Fai Modifica> Incolla speciale. Nella finestra di dialogo Incolla speciale, fare clic su Formati. Fare clic su OK.
Questo copierà la formattazione condizionale su tutte le celle della colonna. Ora - finalmente - vedi alcune celle con la formattazione rossa, a indicare che hai un duplicato.
È informativo andare alla cella A3 e guardare il formato condizionale dopo la copia. Seleziona A3, premi od per visualizzare la formattazione condizionale. La formula nella casella Formula è modificata per contare quante volte A3 appare nella colonna A: A.
Appunti
Nella domanda di Joe, aveva solo 1700 fatture nella gamma. Ho impostato 65536 celle con formattazione condizionale e ogni cella confronta la cella corrente con altre 65536 celle. In Excel 2005, con più righe, il problema sarà anche peggiore. Tecnicamente, la formula nel primo passaggio avrebbe potuto essere:=COUNTIF($A$2:$A$1751,A2)>1
Inoltre, durante la copia del formato condizionale sull'intera colonna, avresti potuto selezionare solo le righe con i dati prima di eseguire Incolla formati speciali.
Di più
L'altro problema che ho descritto dopo la domanda è che non puoi davvero ordinare una colonna sulla base di un formato condizionale. Se è necessario ordinare questi dati in modo che i duplicati si trovino in un'area, seguire questi passaggi. Innanzitutto, aggiungi un'intestazione a B1 chiamata "Duplicate?". Digitare questa formula in B2: =COUNTIF(A:A,A2)>1.
Con il puntatore della cella in B2, fai clic sul quadratino di riempimento automatico (il quadratino nell'angolo in basso a destra della cella) per copiare la formula fino in fondo all'intervallo.
È ora possibile ordinare per colonna B decrescente e A crescente per avere le fatture problematiche nella parte superiore dell'intervallo.
Questa soluzione presuppone che si desideri evidenziare ENTRAMBE le fatture duplicate in modo da poter capire manualmente quale eliminare o correggere. Se non si desidera contrassegnare la prima occorrenza del duplicato, è possibile regolare la formula per essere: =COUNTIF($A$2:$A2,A2)>1. È importante inserire i segni del dollaro esattamente come mostrato. Questo esaminerà solo tutte le celle dalla cella corrente in su, cercando voci duplicate.
Grazie a Joe da Boston per la domanda!
