Refere-se a uma coluna numerada sem usar funções voláteis

Question

Estou tentando usar COUNTIF para contar o número de células em uma coluna que contém uma determinada string. A coluna a ser verificada é especificada por uma célula que contém um número.

Aqui está uma versão simplificada do que tenho no momento.

Onúmero3nacélulaG1indicaqueacolunaaserpesquisadaéaterceiracolunaàdireitadacoluna"Nome", a que tem o cabeçalho "R3" (Linha D). A fórmula na célula G2 conta corretamente o número de células na coluna D que contêm "Y".

=COUNTIF(OFFSET(A:A,0,G1),"Y")

O problema com essa fórmula é que a função OFFSET é volatile o que significa que sempre que abro o livro, a fórmula é recalculada. Se eu tentar fechar a pasta de trabalho sem fazer nenhuma alteração, recebo a caixa de diálogo perguntando se desejo salvar minhas alterações (inexistentes).

Existe uma fórmula alternativa que calcula o número de "Y" em uma coluna sem usar uma função volátil?  Se isso ajudar, os cabeçalhos reais das colunas nas quais estou interessado são de fato R1, R2, R3, etc. Eu poderia converter os dados em uma Tabela se isso ajudasse.

    

Answer 1

Você pode usar uma fórmula de matriz:

=SUM((COLUMN(B1:D1)-1=G1)*(B1:D9="Y"))

Digite esta fórmula com CTRL + SHIFT + ENTER. Onde:

B1: D1 representa os cabeçalhos das colunas "R".
B1: D9 representam os dados das colunas "R".
G1 representa a Rodada para procurar.

Essencialmente, essa fórmula cria um array de 1s onde quer que haja um "Y", mas multiplica por 1 se a coluna corresponder à sua pesquisa ou 0 se não corresponder. Esses itens multiplicados por 0 são efetivamente suprimidos e você fica com apenas aqueles que correspondem ao sinalizador "Y" e ao número da coluna correto.

O benefício de usar este método é muito fácil de expandir e, assumindo que os nomes na coluna A nunca serão "Y", pode até ser implementado em uma estrutura de tabela para expandir automaticamente com a fórmula como:

=SUM((COLUMN(Table1[#Headers])-1=G1)*(Table1="Y"))

    

Answer 2

Esta fórmula não volátil ajudará você a se livrar do problema.

=COUNTIF(CHOOSE($G$2,$B$2:$B$10,$C$2:$C$10,$D$2:$D$10),"Y")

Nota:

• Para evitar referências de celular, você pode usar Named/Dynamic Named Range na fórmula.
• Ajuste as referências de célula na fórmula conforme necessário.

Answer 3

Encontrei uma maneira alternativa de calcular o número. Ele usa um recurso da função INDEX (que não é volátil) que eu perdi. Se você inserir 0 para o argumento de linha e algo (vamos chamá-lo de col ) como o argumento da coluna, INDEX retornará toda a coluna col como uma matriz. O array da coluna pode então ser pesquisado por COUNTIF

=COUNTIF(INDEX($B:$D,0,G1),"Y")

É claro que você também pode inserir 0 para a coluna em vez da linha para obter INDEX para retornar uma linha inteira como uma matriz.