Sou botânico e estou tentando produzir uma tabela Excel que filtre uma lista muito longa de espécies com base na localização e no habitat básico.
Eu criei uma tabela mestre com 200 linhas para cada espécie e ao longo das colunas estão primeiro os locais (por exemplo, coluna b para a Inglaterra, coluna b para a Escócia e assim por diante) e depois da localização as colunas são habitats básicos (ou seja, coluna f para florestas , coluna g para pastagens, coluna h para aquáticos). Todas as células contêm um SIM ou NÃO relevante para cada espécie, mostrando a localização geográfica e o habitat em que a espécie ocorre.
Quero produzir uma tabela que pergunte onde fica meu local e quais habitats ele suporta e use isso para restringir quais espécies provavelmente estarão presentes. Por exemplo, se eu tivesse um local em Londres que sustentasse florestas, pastagens e uma sebe, eu poderia usar os filtros para reduzir a lista de espécies de mais de 200 para, esperançosamente, um punhado de espécies!
Qualquer idéia sobre como produzir isso seria ótima. Anexei uma foto da minha planilha atual.
Responder1
Bastante direto
Você organizou suas informações essencialmente em duas tabelas lado a lado com suas linhas alinhadas. A Tabela 1 é a sua localização e a Tabela 2 é o seu habitat. Basicamente, você deseja gerar uma lista ou números de linha onde haja um sim na coluna de localização correspondente e um sim na coluna de habitat correspondente. Com base nessa lista de números de linha, extraia as espécies correspondentes.
Para fazer isso vamos fazer algumas suposições:
- A planilha onde todos os seus dados estão localizados é chamada SData
- Os dados são apresentados em A1:R200
- A linha 1 é uma linha de cabeçalho
- Colunas C:I são colunas de localização
- As colunas J:R são locais de habitat
- A localização que procura é B1
- O habitat que você procura é D1
- Sua lista será exibida em A2:B200
1) Determine a coluna de localização
=INDEX(SData!C2:I200,0,MATCH($B$1,SData!$C$1:$I$1,0)
2) Determinar a coluna Habitat
=INDEX(SData!J2:R200,0,MATCH($D$1,SData!$J$1:$R$1,0)
3) Determine quais linhas contêm Sim
Tanto Habitat quanto Location precisam conter sim para serem uma linha aceitável. Se cell = yes será True. Nas operações matemáticas, o Excel trata Verdadeiro como 1 e Falso como 0. Portanto, se ambas as células consecutivas forem sim, você terá 1*1=1. Se ambos forem FALSO então você terá 0*0=0. E se uma célula for Sim e a outra Não, o resultado será 1*0=1.
Então, para fazer isso, a função agregada será usada. Ele executará operações semelhantes a array para certas funções como 14 e 15 que serão usadas. Aggregate também pode ser instruído a ignorar erros. Portanto, configuraremos AGGREGATE para dividir o número da linha pela verificação da condição para sim. Como resultado, você terminará com o número da linha ou com um erro de divisão por 0, cujo agregado será revertido e ignorará esses resultados.
=INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($B$1,SData!$J$1:$R$1,0))="yes")),ROW(A1)))
Coloque a fórmula acima em A2 e copie para B200. O problema que prevejo até agora é quando você fica sem linhas que correspondem aos seus dados. Nesta situação ou se você não obtivesse nenhum resultado, um erro seria criado. Para evitar resultados de erro em suas células, você pode agrupar toda a fórmula acima em uma fórmula IFERROR para que "" seja exibido quando o resultado for um erro.
=IFERROR(INDEX(SData!A:A,AGGREGATE(14,6,ROW(SData!$A$2:$A$200)/((INDEX(SData!$C$2:$I$200,0,MATCH($B$1,SData!$C$1:$I$1,0))="yes")*(INDEX(SData!$J$2:$R$200,0,MATCH($D$1,SData!$J$1:$R$1,0))="yes")),ROW(A1))),"")
Exemplo:
Tabela de dados:
Folha de resultados 1:
Folha de resultados 2:
