Nas 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 vegetais, mas não na categoria frutas? A saída neste exemplo seria:

zuchini onion

(não conteria tomate, pois também aparece na categoria de frutas). Desde já, obrigado.

Answer 1

Não acho que você consiga 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 a partir 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 precisar de mais ajuda para configurar a conexão de dados.

Answer 2

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

Eu iniciaria uma consulta chamada Fruta e filtraria em categoria = fruta. Então eu iniciaria outra consulta chamada Vege e filtraria em categoria = vege. Então, na consulta Vege, eu adicionaria um comando Merge para ingressar na 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 Fruit, por exemplo, Item. Então eu filtraria essa nova coluna, mantendo apenas as linhas nulas.

Aqui está um começo sobre Power Query Merge and Expand:

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872

Answer 3

Para responder a esta pergunta, primeiro vamos dar uma olhada na tabela dinâmica típica. Há a tabela gerada a partir dos dados e a lista de campos da tabela dinâmica. Para fazer o que você está pedindo, vá para a lista de campos da tabela dinâmica e onde você vê a categoria, coloque o cursor sobre ela e 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, desmarcando efetivamente todas elas, depois procure a que deseja... neste caso, seria clicar na abobrinha e na cebola.

Espero que isso ajude e divirta-se!