Semplice tabella pivot per contare i valori unici

Sembra una semplice tabella pivot con cui imparare. Mi piacerebbe fare un conteggio di valori unici per un particolare valore su cui sto raggruppando.

Ad esempio, ho questo:

ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 

Quello che voglio è una tabella pivot che mi mostra questo:

 ABC 1 DEF 2 

La semplice tabella pivot che creo mi dà questo (un conteggio di quante righe):

 ABC 3 DEF 4 

Ma voglio invece il numero di valori unici.

Quello che sto cercando di fare è scoprire quali valori nella prima colonna non hanno lo stesso valore nella seconda colonna per tutte le righe. In altre parole, “ABC” è “buono”, “DEF” è “cattivo”

Sono sicuro che c’è un modo più semplice per farlo, ma ho pensato di dare una tabella pivot alla prova …

Inserire una terza colonna e in C2 cella incollare questa formula

 =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) 

e copialo. Ora crea il tuo pivot basato sulla 1a e 3a colonna. Vedi l’istantanea

inserisci la descrizione dell'immagine qui

AGGIORNAMENTO: è ansible farlo ora automaticamente con Excel 2013. Ho creato questo come una nuova risposta perché la mia risposta precedente in realtà risolve un problema leggermente diverso.

Se hai questa versione, seleziona i tuoi dati per creare una tabella pivot e quando crei la tua tabella, assicurati che l’opzione “Aggiungi questo dato al modello di dati” sia spuntata (vedi sotto).

Seleziona la casella accanto a

Quindi, quando si apre la tabella pivot, crea normalmente righe, colonne e valori. Quindi fai clic sul campo in cui desideri calcolare il conteggio distinto e modifica le Impostazioni valore campo: Modifica le impostazioni del valore del campo

Infine, scorrere fino all’ultima opzione e scegliere ‘Distinct Count.’ Scegli l'opzione 'Distinct Count'

Questo dovrebbe aggiornare i valori della tabella pivot per mostrare i dati che stai cercando.

Mi piacerebbe lanciare nel mix una opzione aggiuntiva che non richiede una formula, ma potrebbe essere utile se è necessario contare valori unici all’interno del set su due colonne diverse. Utilizzando l’esempio originale, non avevo:

 ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 

e voglio che appaia come:

 ABC 1 DEF 2 

Ma qualcosa di più:

 ABC 123 ABC 123 ABC 123 ABC 456 DEF 123 DEF 456 DEF 567 DEF 456 DEF 456 

e volevo che appaia come:

 ABC 123 3 456 1 DEF 123 1 456 3 567 1 

Ho trovato il modo migliore per ottenere i miei dati in questo formato e quindi essere in grado di manipolarli ulteriormente è stato quello di utilizzare il seguente:

inserisci la descrizione dell'immagine qui

Una volta selezionato ‘Running in totale’, quindi scegliere l’intestazione per il set di dati secondario (in questo caso sarebbe l’intestazione o il titolo della colonna del set di dati che include 123, 456 e 567). Questo ti darà un valore massimo con il conteggio totale di elementi in quel set, all’interno del tuo set di dati primari.

Ho quindi copiato questi dati, incollato come valori, quindi inserito in un’altra tabella pivot per manipolarlo più facilmente.

A mio parere, avevo circa un quarto di milione di righe di dati, quindi questo ha funzionato molto meglio di alcuni approcci alla formula, in particolare quelli che cercano di confrontare su due colonne / set di dati perché continuavano a bloccare l’applicazione.

La possibilità di eseguire un “conteggio distinto” fa parte di Excel 2013 ma non è abilitata automaticamente.

Quindi, se esegui una copia di EXCEL 2013, ecco un modo brillante per risolvere ciò senza il fastidio di passare attraverso una funzione: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables- infine-in-excel-2013 /

Vedi gli oggetti unici del conteggio di Debra Dalgleish

inserisci la descrizione dell'immagine qui

Non è necessario che la tabella venga ordinata per la seguente formula per restituire 1 per ciascun valore univoco presente.

supponendo che l’intervallo di tabella per i dati presentati nella domanda sia A1: B7, immettere la seguente formula nella cella C1:

 =IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1)) 

Copia quella formula su tutte le righe e l’ultima riga conterrà:

 =IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7)) 

Ciò si traduce in un 1 che viene restituito la prima volta che viene trovato un record e 0 per tutte le volte successive.

Sommare semplicemente la colonna nella tabella pivot

Ho trovato l’approccio più semplice è quello di utilizzare l’opzione Distinct Count in Value Field Settings (fare clic con il pulsante sinistro del mouse nel riquadro Values ). L’opzione per Distinct Count è in fondo alla lista.

Posizione di dove fare clic

Qui ci sono i precedenti (TOP, Count normale) e dopo (BOTTOM; Distinct Count )

CONTARE

DISTINCT COUNT

Il mio approccio a questo problema era leggermente diverso da quello che vedo qui, quindi condividerò.

  1. (Prima fai una copia dei tuoi dati)
  2. Concatena le colonne
  3. Rimuovere i duplicati sulla colonna concatenata
  4. Ultimo – ruotare sul set risultante

Nota: vorrei includere le immagini per renderlo ancora più facile da capire, ma non posso perché questo è il mio primo post;)

La risposta di Siddharth è magnifica.

Tuttavia , questa tecnica può causare problemi quando si lavora con un ampio set di dati (il mio computer si blocca su 50.000 righe). Alcuni metodi meno intensivi del processore:

Singolo controllo di unicità

  1. Ordina per le due colonne (A, B in questo esempio)
  2. Usa una formula che guarda meno dati

     =IF(SUMPRODUCT(($A2:$A3=A2)*($B2:$B3=B2))>1,0,1) 

Controlli multipli di unicità

Se è necessario verificare l’univocità in colonne diverse, non è ansible fare affidamento su due tipi.

Anziché,

  1. Ordina singola colonna (A)
  2. Aggiungi la formula che copre il numero massimo di record per ciascun raggruppamento. Se ABC potrebbe avere 50 righe, la formula sarà

     =IF(SUMPRODUCT(($A2:$A49=A2)*($B2:$B49=B2))>1,0,1) 

Excel 2013 può fare Count distinti in pivot. Se non si ha accesso al 2013, ed è una quantità minore di dati, faccio due copie dei dati grezzi, e nella copia b, selezionare entrambe le colonne e rimuovere i duplicati. Quindi fai il perno e conta la tua colonna b.

Puoi utilizzare COUNTIFS per più criteri,

= 1 / CONTEGGIO (A: A, A2, B: B, B2) e quindi trascinare verso il basso. Puoi inserire tutti i criteri che desideri, ma richiede molto tempo per l’elaborazione.

Passaggio 1. Aggiungi una colonna

Passaggio 2. Utilizzare la formula = IF(COUNTIF(C2:$C$2410,C2)>1,0,1) nel primo record

Passaggio 3. Trascinarlo su tutti i record

Passaggio 4. Filtro ‘1’ nella colonna con la formula

Puoi creare una colonna aggiuntiva per archiviare l’unicità, quindi sumrla nella tabella pivot.

Quello che voglio dire è che la cella C1 dovrebbe sempre essere 1 . La cella C2 dovrebbe contenere la formula =IF(COUNTIF($A$1:$A1,$A2)*COUNTIF($B$1:$B1,$B2)>0,0,1) . Copia questa formula in modo che la cella C3 contenga =IF(COUNTIF($A$1:$A2,$A3)*COUNTIF($B$1:$B2,$B3)>0,0,1) e così via.

Se hai una cella di intestazione, ti consigliamo di spostarle tutte in una riga e la formula C3 dovrebbe essere =IF(COUNTIF($A$2:$A2,$A3)*COUNTIF($B$2:$B2,$B3)>0,0,1) .

Se i dati sono ordinati, suggerisco di utilizzare la seguente formula

 =IF(OR(A2<>A3,B2<>B3),1,0) 

Questo è più veloce in quanto utilizza meno celle per calcolare.

Di solito ordino i dati per il campo di cui ho bisogno per fare il conteggio distinto di quindi usare IF (A2 = A1,0,1); ottieni quindi un 1 nella prima riga di ogni gruppo di ID. Semplice e non richiede tempo per calcolare su set di dati di grandi dimensioni.

Ho trovato un modo più semplice per farlo. Riferendosi all’esempio di Siddarth Rout, se voglio contare i valori unici nella colonna A:

  • aggiungere una nuova colonna C e riempire C2 con la formula “= 1 / COUNTIF ($ A: $ A, A2)”
  • trascinare la formula fino al resto della colonna
  • ruotare con la colonna A come etichetta di riga e Sum {colonna C) in valori per ottenere il numero di valori univoci nella colonna A