Formula generica
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Sommario
Excel contiene una preimpostazione incorporata per evidenziare i valori duplicati con la formattazione condizionale, ma funziona solo a livello di cella. Se desideri evidenziare intere righe che sono duplicati, dovrai utilizzare la tua formula, come spiegato di seguito.
Se desideri evidenziare righe duplicate in un insieme di dati non ordinato e non desideri aggiungere una colonna helper, puoi utilizzare una formula che utilizza la funzione CONTA.SE per contare i valori duplicati in ciascuna colonna dei dati.
Ad esempio, se hai valori nelle celle B4: D11 e desideri evidenziare intere righe duplicate, puoi usare una formula piuttosto brutta:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Intervalli denominati per una sintassi più pulita
Il motivo per cui la formula sopra è così brutta è che dobbiamo bloccare completamente ogni intervallo di colonne, quindi utilizzare un riferimento misto per testare ogni cella in ogni colonna. Se crei intervalli denominati per ogni colonna nei dati: col_a, col_b e col_c, la formula può essere scritta con una sintassi molto più pulita:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Spiegazione
Nella formula, CONTA.SE conta il numero di volte in cui ogni valore in una cella appare nella sua colonna "genitore". Per definizione, ogni valore deve apparire almeno una volta, quindi quando il conteggio> 1, il valore deve essere un duplicato. I riferimenti sono accuratamente bloccati in modo che la formula restituisca true solo quando tutte e 3 le celle di una riga appaiono più di una volta nelle rispettive colonne.
L'opzione della colonna helper "imbroglia" combinando tutti i valori in una riga insieme in una singola cella usando la concatenazione. Quindi COUNTIF conta semplicemente il numero di volte in cui questo valore concatenato appare nella colonna D.
Colonna helper + concatenazione
Se non ti dispiace aggiungere una colonna helper ai tuoi dati, puoi semplificare un po 'la formula di formattazione condizionale. In una colonna helper, concatena i valori da tutte le colonne. Ad esempio, aggiungi una formula nella colonna E simile a questa:
=B4&C4&D4
Quindi utilizzare la seguente formula nella regola di formattazione condizionale:
=COUNTIF($E$4:$E$11,$E4)>1
Questa è una regola molto più semplice e puoi nascondere la colonna helper, se lo desideri.
Se si dispone di un numero molto elevato di colonne, è possibile utilizzare la funzione TEXTJOIN (Excel 2016365) per eseguire la concatenazione utilizzando un intervallo:
=TEXTJOIN(",",TRUE,A1:Z1)
È quindi possibile utilizzare COUNTIF come sopra.
SUMPRODUCT
Se stai usando una versione di Excel prima del 2007, puoi usare SUMPRODUCT in questo modo:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
