Contagem de Excel do número de vezes que um valor único foi selecionado

Question

Eu tenho um arquivo longo com três colunas, a primeira coluna contém o nome das instituições, a segunda coluna contém o nome dos clientes em cada instituição e as terceiras colunas contêm um campo Sim / Não para indicar se um cliente específico em um instituição específica foi selecionada

Inst.   Cust.   Selected
INST_1  CUST_1  Yes
INST_1  CUST_2  Yes
INST_1  CUST_3  
INST_1  CUST_4  
INST_2  CUST_5  Yes
INST_2  CUST_6  
INST_2  CUST_7  
INST_3  CUST_8  Yes
INST_3  CUST_9  
INST_3  CUST_10 
INST_3  CUST_11 
INST_3  CUST_12 Yes
INST_3  CUST_13 
INST_3  CUST_14 
INST_4  CUST_15 
INST_4  CUST_16 Yes
INST_4  CUST_17 Yes
INST_4  CUST_18 Yes

Estou interessado em calcular o número de instituições nas quais um, dois ou mais de dois clientes foram selecionados.

Estou familiarizado com soluções usando tabelas dinâmicas, mas no meu caso, seria mais prático ter o resultado gerado por uma fórmula. No exemplo, a saída desejada é:

number of institutions with one selected customer: 1

number of institutions with two selected customers: 2

number of institutions with three or more selected customer: 1

    

Answer 1

Eu usaria a função de codificação marco no plugin VBA. Não darei o código exato porque ele muda de vez em quando. Mas aqui está uma codificação pseudo básica.

1. "GET" os dados da célula para procurar primeiro. Depois de descobrir como o seu Excel obtém dados, imagine como fazer o loop.
2. Configure a fórmula para análise e, em seguida, "PUT" em uma célula de dados
3. Avance para a próxima célula ou aumente para a próxima célula. note: Em vez de GET e PUT, você poderia usar o READ e o WRITE. Acredito que a diferença entre os dois seja um, leia o valor binário e o outro, os valores da string. A maioria dos programadores usa valores de strings para armazenar valores e os processa em float ou int.

Answer 2

Passei mais tempo e o problema e encontrei uma solução alavancando a abordagem ilustrada no post Conte valores numéricos exclusivos com critérios

Para implementar a fórmula, adicionei uma coluna adicional transformando a Inst. coluna em uma coluna contendo apenas IDs numéricos. Isso é necessário para poder usar a função FREQUENCY .

Inst.   Inst_ID Cust.   Selected
INST_1     1    CUST_1  Yes
INST_1     1    CUST_2  Yes
INST_1     1    CUST_3  
INST_1     1    CUST_4  
INST_2     2    CUST_5  Yes
INST_2     2    CUST_6  
INST_2     2    CUST_7  
INST_3     3    CUST_8  Yes
INST_3     3    CUST_9  
INST_3     3    CUST_10 
INST_3     3    CUST_11 
INST_3     3    CUST_12 Yes
INST_3     3    CUST_13 
INST_3     3    CUST_14 
INST_4     4    CUST_15 
INST_4     4    CUST_16 Yes
INST_4     4    CUST_17 Yes
INST_4     4    CUST_18 Yes

O número de instituições que foram selecionadas apenas uma vez agora pode ser calculado como SUM(1*(FREQUENCY(IF(Selected="Yes",Inst_ID),Inst_ID)=1)) . Os casos em que as instituições foram seleccionadas duas ou mais vezes podem ser calculados de forma semelhante.

A matriz bin na função FREQUENCY também deve ser apontada para uma lista da Inst_ID exclusiva.