Função do Excel trabalhando como grupo SQL por + count (distinct *)?

Question

Suponha que eu tenha uma planilha do Excel com os dados abaixo

 CODE (COL A) | VALUE (COL B)
==============================
  A01         | 10
  A01         | 20
  A01         | 30
  A01         | 10
  B01         | 30
  B01         | 30

Existe uma função do Excel funcionando como ...

SELECT CODE, count (Distinct *) FROM TABLE GROUP BY CODE


 CODE    | Distinct Count of Value
===================================
  A01    | 3
  B01    | 1

ou, melhor ainda, posso ter uma fórmula do Excel colada na coluna C para obter algo assim:

 
 CODE (COL A) | VALUE (COL B) | DISTINCT VALUE COUNT WITH MATCHING CODE (COL C)
===============================================================================
  A01         | 10            | 3
  A01         | 20            | 3
  A01         | 30            | 3
  A01         | 10            | 3
  B01         | 30            | 1
  B01         | 30            | 1

Eu sei que posso usar a tabela dinâmica para obter esse resultado facilmente. No entanto, devido aos requisitos de relatório, tenho que anexar a coluna "contagem distinta" à planilha do Excel, portanto, a tabela dinâmica não é uma opção.

Meu último recurso é usar macros do Excel (que são boas), mas antes disso eu gostaria de saber se as funções do Excel podem realizar esse tipo de tarefa.

    

Answer 1

Digite esta fórmula na célula C2, supondo que você tenha dados nas linhas 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 arraste-o para baixo.

Como funciona:

Quando SUMPRODUCT recebe uma lista de argumentos escalares, ele funciona como SUM , mas será necessário um array como um argumento sem uma entrada de matriz especial.

A matriz é preenchida com zeros para registros que não correspondem ao valor CODE na coluna A . Para aqueles que correspondem, a matriz é preenchida com 1/(the number of records that have the same A and B values as this record) . Portanto, por exemplo, há dois registros com A = A01 e B = 10 , portanto, para esses dois registros, 1/2 (½) é inserido na matriz. Pense nisso como uma espécie de ponderação de valores duplicados. Sempre que esses valores são somados, a soma de cada valor B exclusivo é 1 (no exemplo, os dois registros somariam ½ + ½ = 1). Isso fornece a contagem de registros distintos.

Exemplo completo usando seus dados de exemplo:

Para qualquer registro com A = A01 , a fórmula retornará a soma de {½,1,1,½,0,0} = 3 .
Para qualquer registro com A = B01 , a fórmula retornaria a soma de {0,0,0,0,½,½} = 1 .

    

Answer 2

Aqui está uma abordagem que é um pouco mais fácil de entender do que a do Excellll, mas requer uma coluna extra. Supondo que seus dados estejam nas Linhas 2 a 7 (Colunas A e B), insira isso em C2:

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

e isso em D2:

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

e arraste para baixo.

Como funciona:

COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) conta quantas linhas acima e incluindo a atual tem os mesmos valores de A e B que a linha atual. Isso será 1 na primeira ocorrência de um par de valores (linhas 2, 3, 4 e 6) e mais alto nas linhas que estão repetindo um par de valores que ocorreu acima (isto é, será 2 nas linhas 5 e 7). Testando se 1 produz TRUE na primeira ocorrência de cada par de valores distintos e FALSE em outro lugar. Em seguida, a fórmula na coluna D conta quantos TRUE s existem para o valor atual de A .

Você pode simplificar um pouco as fórmulas:

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

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

e, claro, você pode ocultar a coluna C.

    

Answer 3

Eu usaria o suplemento Power Pivot Excel. Ele come contagens distintas para o café da manhã ...

Primeiro eu adicionaria a tabela do Excel ao Power Pivot usando o botão Criar tabela vinculada na faixa de opções do Power Pivot.

Em seguida, usaria o botão Tabela Dinâmica na faixa Power Pivot para criar uma Tabela Dinâmica, arrastando a coluna Código (Col A) para a zona Rótulos de Linha e a coluna Valor (Col B) para a zona Valores (no Power Lista de Campos Dinâmicos).

Por padrão, o campo Valores será agregado como Soma do Valor (Col B). Eu mudaria isso clicando na entrada Soma de valor (Col B) na zona Valores e escolhendo Resumir por e, em seguida, Contagem distinta.

Aqui está uma captura de tela do resultado