☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table powerpivot
Как в сводных таблицах Excel отобразить элементы, которые встречаются в одной категории, но не встречаются в другой?

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

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

(в него не войдет томат, так как он также входит в категорию фруктов). Заранее спасибо.

решение1

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

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

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

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'

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

решение2

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

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

Вот начальный курс по Power Query Merge and Expand:

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

решение3

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

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

Связанный контент