Funzione Excel che funziona come gruppo SQL da + count (distinzione *)?

Supponiamo di avere un foglio di Excel con i dati sotto

  CODICE (COL A) |  VALORE (COL B)
 ==============================
   A01 |  10
   A01 |  20
   A01 |  30
   A01 |  10
   B01 |  30
   B01 |  30

C'è una function Excel che funziona come ..

 SELECT CODE, count (Distinct *) Dalla tabella GROUP BY CODE


  CODICE |  Conteggio distinto del valore
 ===================================
   A01 |  3
   B01 |  1

o, meglio ancora, posso avere una formula Excel incollata nella colonna C per get qualcosa di simile:

 
  CODICE (COL A) |  VALORE (COL B) |  VALORE DISTINTIVO CON IL CODICE DI COLLEGAMENTO (COL C)
 ================================================== =============================
   A01 |  10 |  3
   A01 |  20 |  3
   A01 |  30 |  3
   A01 |  10 |  3
   B01 |  30 |  1
   B01 |  30 |  1

So che posso usare la tabella pivot per get questo risultato facilmente. Tuttavia, a causa dei requisiti di segnalazione, devo aggiungere la colonna "Distinto conto" al foglio di Excel, quindi la tabella pivot non è un'opzione.

Il mio ultimo ricorso è quello di utilizzare le macro di Excel (che sono belle), ma prima di questo vorrei sapere se le funzioni di Excel possono svolgere questo tipo di attività.

Immettere questa formula nella cella C2, supponendo di avere i dati nelle righe da 2 a 7,

=SUMPRODUCT(($A$2:$A$7=A2) / COUNTIFS($B$2:$B$7, $B$2:$B$7, $A$2:$A$7, $A$2:$A$7)) 

e trascinarlo.

Come funziona:

Quando SUMPRODUCT viene fornito un elenco di argomenti scalari, funziona come SUM , ma occorrerà un arrays come argomento senza una voce di arrays specifica.

L'arrays viene popolato con zero per i record che non corrispondono al valore CODE nella colonna A Per coloro che corrispondono, l'arrays è popolato con 1/(the number of records that have the same A and B values as this record) . Così, per esempio, ci sono due record che hanno A = A01 e B = 10 , quindi per questi due record 1/2 (½) viene inserito nell'arrays. Pensate a questo come un tipo di ponderazione per i valori duplicati. Ogni volta che questi valori sono sumti, la sum per each singolo valore B è 1 (nell'esempio i due record avrebbero sum ½ + ½ = 1). Questo dà il count di record distinti.

Esempio completo utilizzando i dati di esempio:

Per each record con A = A01 , la formula restituirà la sum di {½,1,1,½,0,0} = 3 .
Per each record con A = B01 , la formula restituirà la sum di {0,0,0,0,½,½} = 1 .

Ecco un approccio che è un po 'più facile da capire di quello di Excellll, ma richiede una colonna aggiuntiva. Supponendo che i tuoi dati siano in righe da 2 a 7 (colonne A e B), inserisci questo in C2:

 =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1 

e questo in D2:

 =COUNTIFS($C$2:$C$7, TRUE, $A$2:$A$7, $A2) 

e trascina verso il basso.

Come funziona:

COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) conta quante righe sopra e compresa la corrente hanno gli stessi valori A e B della row corrente. Ciò sarà 1 alla prima occorrenza di una coppia di valori (Righe 2, 3, 4 e 6) e più in alto sulle righe che ripetono una coppia di valori sopra accaduta (vale a dire, sarà 2 in Righe 5 e 7). Testare se è 1 resa TRUE alla prima occorrenza di ciascuna coppia di valori distinta e FALSE altrove. Quindi la formula nella colonna D conta quanti TRUE s sono presenti per il valore corrente di A

Puoi semplificare le formule un po ':

 C: =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) D: =COUNTIFS($C$2:$C$7, 1, $A$2:$A$7, $A2) 

e ovviamente puoi hide la colonna C.

Vorrei usare il Power Pivot Excel Add-In. Mangia conteggi distinti per la colazione …

Innanzitutto vorrei aggiungere la tabella di Excel a Power Pivot utilizzando il button Crea tabella collegata sul nastro Power Pivot.

Quindi utilizzare il button PivotTable sul nastro Power Pivot per creare una tabella pivot, trascinando la colonna Code (Col A) nella zona delle etichette di row e la colonna Value (Col B) nella zona Values ​​(nell'elenco Power Pivot Field ).

Per impostazione predefinita, il field Valori verrà aggregato come sum del valore (Col B). Vorrei cambiare questo clic facendo clic sulla sum del valore (Col B) nella zona Valori e scegliendo Riepilogo da, quindi Distinct Count.

Ecco uno screenshot del risultato

esempio di conteggio distinto