Avançado (?) Filtragem do Excel

Question

Primeiro, gostaria de admitir que realmente não sei nada sobre o Excel, mas tentei procurar uma solução para isso em livros do Excel e em Googling.

Veja o que estou tentando fazer:

Eu tenho uma planilha muito longa Há 7 colunas no total, mas apenas duas colunas que me interessam. Aqui está um exemplo de CSV muito mais simples que meu conjunto de dados real, mas a pesquisa / classificação é análoga:

John, Apple
Dave, Apple
Dave, Orange
Steve, Apple
Steve, Orange
Steve, Kiwi
Bob, Apple
Bob, Banana

Estou interessado em extrair as linhas inteiras (todas as colunas) que atendem aos seguintes critérios:

["Apple"] OU ["Apple" e "Orange"] NÃO ["Apple" e "Orange" e Qualquer outra coisa ] NÃO ["Apple" e Qualquer coisa que não seja laranja ]

Portanto, com o CSV acima, eu obteria as linhas inteiras para John e Dave, mas não para Steve e nem para Bob.

Comecei a fazer isso manualmente e provavelmente terminarei quando a pergunta tiver uma resposta, mas gostaria de saber isso para referência futura.

Obrigado!

    

Answer 1

Aqui está um método usando três colunas auxiliares. A ideia é que você possa filtrar as linhas com valor 1 na terceira coluna auxiliar para obter as linhas desejadas.

Primeira coluna auxiliar (coluna C no meu exemplo): fornece a contagem de entradas "Apple" para essa pessoa.

=COUNTIFS($A$1:$A$8,A1,$B$1:$B$8,"Apple")

Segunda coluna auxiliar (coluna D ): fornece a contagem de registros para essa pessoa.

=COUNTIF($A$1:$A$8,A1)

Terceira coluna auxiliar (coluna E ): retorna 1 para pessoas que correspondem aos seus critérios.

=IF(D1=1,IF(C1>0,1,0),IF(AND(D1=2,C1=1),IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8="Orange"))=1,1,0),0))

Em seguida, basta filtrar o 1 s na coluna E .

Você pode compactar essas colunas auxiliares em uma coluna auxiliar, se necessário, mas a fórmula se tornará repetitiva e quase impossível de ser lida.

    

Answer 2

Basicamente, o número de entradas da Apple e da Orange para uma pessoa deve ser igual ao número de todas as entradas para uma pessoa. Portanto, você poderia fazer isso:

1. Adicione uma nova coluna em algum lugar da sua tabela.
2. Coloque na fórmula =A2&"###"&B2 onde A2 é a primeira célula que contém o nome, B2 é a primeira célula que contém a fruta e ### é um separador que não será usado nem no nome nem a coluna de frutas. (Se ### for usado, simplesmente substitua por outra coisa que não seja encontrada nessas colunas.)
3. Arraste esta fórmula pela coluna inteira.
4. Adicione outra coluna em algum lugar da sua tabela.
5. Coloque na fórmula =(COUNTIF($C:$C,$A2&"###Apple") + COUNTIF($C:$C,$A2&"###Orange")) = COUNTIF($A:$A,$A2) onde C é a coluna que você acabou de adicionar na etapa 1, A ainda é a coluna com o nome e ### é o mesmo espaço reservado usado na etapa 2.
6. Arraste esta fórmula pela coluna inteira também.

Resultado: todas as linhas "inválidas" devem conter "FALSE", todas as linhas "válidas" devem conter "TRUE".

Uma nota de aviso: assim que você começar a excluir as entradas, o "FALSE" poderá se transformar em "TRUE" - portanto, talvez você queira criar uma cópia impressa de toda a coluna antes de executar qualquer tipo de edição nos dados.

HTH.