В сводных таблицах Excel, как показать элементы, которые находятся в одной категории, но не в другой?

Question

Упрощенная версия моих данных:

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

Можно ли настроить сводную таблицу в Excel 2010, чтобы я получил список элементов, которые отображаются в категории овощей, но не в категории фруктов? Вывод в этом примере будет:

zuchini onion

(он не будет содержать помидор, так как это также появляется в категории фруктов). Заранее спасибо.

Answer 1

Я бы использовал Power Query Add-In для этого. Вы можете запустить запрос из таблицы Excel.

Я бы запустил один запрос под названием Fruit и Filter для category = fruit. Затем я бы запустил еще один запрос с именем Vege и Filter на category = vege. Затем в запросе Vege я добавил бы команду Merge для присоединения к запросу Fruit с использованием столбцов Item, сохраняя строки, которые не совпадают. Затем я использовал бы команду Expand, чтобы добавить столбец из запроса Fruit, например Item. Затем я бы отфильтровал этот новый столбец, сохранив только пустые строки.

Вот стартер Power Query Merge и Expand:

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

Answer 2

Я не думаю, что вы сможете сделать это с помощью сводной таблицы, так как фильтрация не является сложной для сгруппированных элементов. Или, по крайней мере, это не так автоматизировано.

Я смог сделать это с помощью запроса SQL. Вы можете запросить Excel из Excel, используя MS Query и подключение для передачи данных (просто укажите источник данных на файл, с которым вы работаете).

Вот окончательный результат:

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'

Дайте мне знать, если вам нужна дополнительная помощь в настройке подключения к данным.

Answer 3

Чтобы ответить на этот вопрос, сначала давайте посмотрим на типичную сводную таблицу. Есть таблица, сгенерированная из данных, и список полей сводной таблицы. Чтобы сделать то, что вы просите, перейдите к списку полей сводной таблицы, и там, где вы видите категорию, поместите курсор на нее, тогда вы увидите маленький перевернутый треугольник справа. Нажмите на треугольник. Это откроет панель с параметрами фильтра. В нижней части панели щелкните в верхнем поле, чтобы снять все флажки, а затем найдите тот, который вам нужен ... в этом случае это будет щелчок по цуккини и луку.

Надеюсь, это поможет и повеселиться!