Existe uma maneira de usar um intervalo de nome como um critério de matriz para uma fórmula de soma (countif ()) no Excel 2010

Question

Coluna de imagens Um total de comentários, como o feedback de uma pergunta da pesquisa. Agora imagine a coluna B com uma fórmula que conta as ocorrências de palavras-chave particulares dentro de cada comentário. No momento, estou usando essa fórmula na coluna B: SUM (COUNTIF (A27, {" LBNL ", " Lawrence Berkeley ", " LBL " , "Lawrence Lab *"}))

Como a lista vai crescer e diminuir ao longo do caminho e porque eu vou acabar com várias fórmulas similares (para diferentes categorias) eu gostaria de controlar a lista em um intervalo nomeado e referenciá-la de lá. / p>

Então, vamos dizer que minha lista é como abaixo e tem um intervalo de nome de search_items1

• LBNL
• Lawrence Berkeley
• LBL
• Laboratório Lawrence

Minha fórmula seria, então, semelhante a SUM (COUNTIF (A27, search_items1)).

Observe o uso de * para curinga que introduz outro desafio, mas não consigo fazer com que a fórmula acima funcione mesmo sem o *. Existe uma maneira de fazer isso funcionar? A solução com o caractere curinga * seria ideal.

Alternativamente, eu poderia fazer referência a uma célula que é concatenada em conjunto a partir do intervalo de nomes e ficaria assim: {" LBNL ", " Lawrence Berkeley ", LBL "," Lawrence Lab * "}. Eu tentei isso, mas a fórmula interpreta como um bloco de texto.

Eu tentei várias variações de sintaxe e inúmeras pesquisas no Google e Superusuário. Por favor ajude.

    

Answer 1

O que sua fórmula faz:

=SUM(COUNTIF(A27,{"LBNL","Lawrence Berkeley","LBL","Lawrence Lab*"}))

é a contagem 1 se a célula A27 for LBNL , ou Lawrence Berkeley ou LBL ou Lawrence Lab* (o * atuando como curinga aqui).

Então, se você tem uma célula sendo LBNL , você terá 1 como resultado. Ter Dr. LBNL resultará em 0.

Se você deseja obter uma contagem de 1 se a célula contiver pelo menos 1 LBNL , será necessário usar curingas nos dois lados do texto, a saber:

=SUM(COUNTIF(A27,{"*LBNL*","*Lawrence Berkeley*","*LBL*","*Lawrence Lab*"}))

Você pode criar um intervalo nomeado e colocá-lo lá, mas isso se tornará uma fórmula de matriz que funcionará apenas com Ctrl + Deslocamento + Enter :

=SUM(COUNTIF(A27,search_items1))

Agora, você pode usar SUMPRODUCT para evitar a necessidade de usar o CSE para usar essa fórmula:

=SUMPRODUCT(COUNTIF(A27,search_items1))

Se, no entanto, você tiver uma célula contendo LBNL LBNL e quiser que o resultado seja 2 , isso será outra questão, pois o trabalho de COUNTIF é concluído quando ele encontrou o que procurava em uma célula e devolve 1 .

Nesse caso, sugiro que a contagem de cada célula seja verificada (porque a função acima pode ser usada para pesquisar em colunas inteiras):

=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,search_items1,"")))/LEN(search_items1))

[Observe agora que você precisa descartar os asteriscos por causa de como SUBSTITUTE funciona.]

    

Answer 2

Nem sempre precisa ser uma fórmula. Pode ser um intervalo também. Isso permite que você mantenha uma planilha limpa.

Você pode definir um array nomeado por meio do gerenciador de nomes. Chame o gerenciador de nomes da faixa de opções da fórmula, crie um novo nome. Dê um nome claro (por exemplo, "BinWithMarks") e cole-o no campo "Refere-se a:":

={100,89,84,79,74,69,64,59,54,49,39,0}

Da mesma forma, você pode adicionar as notas relevantes da seguinte forma, usando as notas como nome:

={"A+","A","A-","B+","B","B-","C+","C","C-","D","E"}

Após confirmar suas escolhas, você pode usar essa matriz para procurar a nota relevante para um aluno usando a combinação de fórmulas de índice e correspondência.

=INDEX(Grades,MATCH(N11,BinWithMarks,-1))