Em Tabelas Dinâmicas do Excel, como mostro itens que ocorrem em uma categoria, mas não em outra?

Question

Versão simplificada dos meus dados:

item category number apples fruit 7 orange fruit 2 tomato fruit 3 tomato vege 9 zuchini vege 2 tomato vege 17 onion vege 1

Com uma Tabela Dinâmica no Excel 2010, existe uma maneira de configurá-la para obter uma lista de itens que aparecem na categoria do vege, mas não na categoria de frutas? A saída neste exemplo seria:

zuchini onion

(não conteria tomate, já que também aparece na categoria de fruta). Agradecemos antecipadamente.

    

Answer 1

Eu não acho que você será capaz de fazer isso com uma tabela dinâmica, pois a filtragem não é tão avançada nos itens agrupados. Ou pelo menos não é tão automatizado.

Consegui fazer isso com uma consulta SQL. Você pode consultar o Excel do Excel usando o MS Query e uma conexão de dados (basta apontar a fonte de dados para o arquivo em que você está trabalhando).

Aqui está o resultado final:

A consulta SQL é:

SELECT 'Sheet1$'.category, 'Sheet1$'.item, 'Sheet1$'.number
FROM 'C:\Admin\StackExchange\sql on excel.xlsx'.'Sheet1$' 'Sheet1$'
WHERE 'Sheet1$'.item NOT IN 
(SELECT 'Sheet1$'.item
FROM 'C:\Admin\StackExchange\sql on excel.xlsx'.'Sheet1$' 'Sheet1$' 
WHERE ('Sheet1$'.category<>'vege'))
AND 'Sheet1$'.category='vege'

Deixe-me saber se você precisa de mais ajuda para configurar a conexão de dados.

    

Answer 2

Eu usaria o suplemento de consulta de energia para isso. Você pode iniciar uma consulta a partir de uma tabela do Excel.

Eu começaria uma consulta chamada Fruit e Filter na categoria = fruit. Então eu começaria outra Query chamada Vege e Filter na categoria = vege. Então, no Vege Query eu adicionaria um comando Merge para juntar-se à consulta Fruit usando as colunas Item, mantendo as linhas que não correspondem. Então eu usaria o comando Expandir para adicionar uma coluna da consulta de frutas, por exemplo, Item. Então eu filtraria nessa nova coluna, mantendo apenas as linhas nulas.

Aqui está uma iniciação na Power Query Merge e Expandir:

link

    

Answer 3

Para responder a esta pergunta, primeiro vamos ver a típica tabela dinâmica. Há a tabela gerada a partir dos dados e há a lista de campos da tabela dinâmica. Para fazer o que você está perguntando, vá para a lista de campos da tabela dinâmica e onde você vê a categoria, posicione o cursor sobre ele e então você verá um pequeno triângulo invertido à direita. Clique no triângulo. Isso abrirá um painel com opções de filtro. Na parte inferior do painel, clique na caixa superior efetivamente desmarcando todos eles e, em seguida, procure o que você deseja ... nesse caso, clicando na abobrinha e na cebola.

Espero que isso ajude e divirta-se!