Posso usar a função Correspondência do Excel para corresponder a mais de um tipo de valor

Question

Em outra pergunta que fiz recentemente, mostramos como usar a função Correspondência do Excel para consultar meus dados para produzir uma tabela de tipo de resumo:

Criando uma fórmula que consulta dados na Coluna B com base em uma seleção da Coluna A no Microsoft Excel

Fui muito gentilmente fornecido com essa fórmula que funciona muito bem:

=IF(ISERROR(MATCH($A$1:$A$2&"Not Fed", Animals!A$2:A$7&Animals!B$2:B$7, 0)), "All Animals Fed", "All Animals Not Fed")

Agora estou olhando para ver se existe uma maneira de estender essa fórmula para Corresponder em uma coluna que contenha mais de um tipo de valor (potencialmente até 3 ou 4 valores diferentes).

Por exemplo:

Asaídaquegostariadevercombasenoacimaé

Eu tentei algo como:

=IF(ISERROR(MATCH($A$1:$A$2&(OR("Fed","Partly Fed","Somewhat Fed")) Animals!A$2:A$7&Animals!B$2:B$7, 0)), "All Animals Fed", "All Animals Not Fed")

... mas isso não funcionou e, logicamente, provavelmente nem é o caminho certo baseado em como eu quero que isso se comporte! Essencialmente, desde que o intervalo B1: B7 para o AnimalType fornecido contenha apenas os valores permitidos (em qualquer quantidade / mix), eu quero que a função retorne true.

    

Answer 1

O que você está tentando fazer é uma tabulação cruzada (também conhecida como crosstab ). A ferramenta para fazer isso no Excel é chamada de Tabela Dinâmica .

Infelizmente, as tabelas dinâmicas não são compatíveis com dados qualitativos, portanto, para que isso funcione, você precisará adicionar uma coluna, que representa seus dados alimentados / não alimentados como um valor numérico.

Por exemplo, se você usar =IF(B2="Not Fed",1,0) , você receberá uma coluna na qual, se algum animal for "Não alimentado", você receberá um. (Note que você não especificou o que você quer que aconteça se o campo Fed? estiver vazio para uma linha, ou tiver outro valor surpreendente. Você não disse o que quer que aconteça no caso que "birds" -> {"not fed", "partially fed", "fed"} . A declaração IF precisa que você usa depende de suas respostas a estas perguntas.)

Em seguida, você seleciona o intervalo inteiro com as três colunas (ou coloca a nova coluna entre essas duas, e seleciona apenas os animais e sua coluna calculada, e seleciona "Relatório de tabela dinâmica" no menu Dados.

O assistente irá guiá-lo através de uma série de diálogos. No primeiro, seus dados estão em "lista ou banco de dados do Microsoft Excel". No segundo, se você selecionou seus dados antes de iniciar o assistente, deve padronizar o seu intervalo de dados corretamente.

Na terceira caixa de diálogo, não acesse Finish imediatamente. Você tem várias opções que precisa definir. Primeiro, nesse diálogo, você poderá escolher onde colocar a nova tabela dinâmica. Eu recomendo colocar sua tabela dinâmica em uma nova planilha. Em seguida, clique em "Layout ...". Arraste a caixa "Animais" para a coluna da esquerda ("Linha"). Arraste o título para sua nova coluna no Área "Dados" no meio; quando você fizer isso, ele aparecerá como "Soma de nameoffield". Dê um duplo clique nele, mude o nome para algo como "Algum animal não está alimentado?" e mude o tipo de resumo para "Max", e clique em "Ok", depois clique em "OK" novamente para finalizar com o diálogo Layout. Você ainda está no passo 3 da caixa de diálogo do assistente. Clique em "Opções ...". Desmarque "Total geral de colunas" e verifique se "Total geral de linhas" está ativado. Ligue "For Error Values Show" (muito importante para isso) e defina-o como 0, e clique em "Ok". Então, finalmente, você pode clicar em "Concluir" o assistente.

Você deve acabar com uma nova planilha que diz:

Are any animals unfed?  
Animals Total
Cat     0
Dog     1

Agora, você pode entrar na coluna logo após, =IF(B3=1, "Some Animals Not Fed", "All Animals Fed") e assim por diante na coluna, e se algum animal em um tipo for "Não alimentado", ele será "Alguns animais não alimentados", caso contrário "Todos os animais alimentados"). Agora você pode ocultar a coluna B e ver apenas:

Are any animals unfed?  
Animals 
Cat     All Animals Fed
Dog     Some Animals Not Fed

    

Answer 2

Eu criaria uma tabela dinâmica (faixa de opções Insert) do intervalo. Eu arrastaria o campo AnimalType para a zona Row Labels e o Fed? Campo para a zona de rótulos de coluna. Eu arrastaria o campo AnimalType para a zona Values.

Isso fornecerá uma contagem de Animais por tipo e status de Fed, com totais de linha e coluna. Eles podem responder a todas as perguntas que você está tentando responder, sem codificar nenhuma fórmula, em uma estrutura que pode ser facilmente estendida para incluir outras colunas e valores.