Вопрос или проблема
Я не уверен, публиковался ли этот тип вопроса здесь раньше, потому что сам не уверен, как его задать. Итак, у меня есть огромный трехмерный набор данных, который выглядит примерно как таблица ниже, но с 10 “Предложениями” и около 40000 строк “Людей” с несколькими сотнями “Компаний”.
| Человек | Компания | Предложение 1 | Предложение 2 | Предложение 3 |
|---|---|---|---|---|
| Человек 1 | Компания 1 | Отклонено | Принято | В ожидании |
| Человек 2 | Компания 1 | Принято | Отклонено | Принято |
| Человек 3 | Компания 1 | В ожидании | В ожидании | Отклонено |
| Человек 4 | Компания 2 | Принято | Отклонено | Принято |
| Человек 5 | Компания 2 | Принято | Отклонено | Отклонено |
| Человек 6 | Компания 2 | В ожидании | Отклонено | В ожидании |
| Человек 7 | Компания 3 | Отклонено | Принято | Отклонено |
| Человек 8 | Компания 3 | Принято | Принято | Принято |
| Человек 9 | Компания 4 | В ожидании | Принято | Принято |
| Человек 10 | Компания 5 | Отклонено | В ожидании | Принято |
| Человек 11 | Компания 5 | Принято | Отклонено | В ожидании |
| Человек 12 | Компания 5 | Принято | Отклонено | В ожидании |
Мне нужна (сводная) таблица, которая показывает, сколько людей из каждой компании приняли предложение. Как бы я ни старался, я получаю это здесь, то есть таблица показывает только тех, кто принял ВСЕ три предложения. Есть ли способ, чтобы столбцы сводной таблицы показывали ИЛИ вместо И? Моим идеальным решением была бы эта таблица здесь. Это возможно? Это из-за того, что в таблице нет числовых данных?
P.S: Я понимаю, что могу использовать COUNTIF, но мне также нужна динамическая таблица (знать, кто на самом деле принял предложение, кликнув по номеру, или добавить еще одну строку под каждой компанией, чтобы узнать, кто эти люди и т.д.
P.P.S: Да, я пробовал использовать “Distinct Count”, и я отфильтровал предложения по “Принято” в диалоговом окне параметров Pivottable.
Надеюсь, вы, супер пользователи, сможете мне помочь? Я буду очень признателен!
Заранее спасибо!
Спасибо.
Пожалуйста, проверьте, полезна ли вам следующая информация.
-
Выберите вашу таблицу и добавьте ее в PowerPivot.
-
Создайте 3 меры для “Предложения 1”, “Предложения 2” и “Предложения 3”.
Предложение 1:=CALCULATE(COUNT(Table1[Offer 1]),FILTER(Table1,Table1[Offer 1]=”Принято”))
Предложение 2:=CALCULATE(COUNT(Table1[Offer 2]),FILTER(Table1,Table1[Offer 2]=”Принято”))
Предложение 3:=CALCULATE(COUNT(Table1[Offer 3]),FILTER(Table1,Table1[Offer 3]=”Принято”))
-
Затем создайте сводную таблицу. Вы можете выбрать таблицу и перейти в
Дизайн > Макет > Промежуточные итоги > Показать все промежуточные итоги вверху группы.
Обратите внимание, вам могут потребоваться некоторые настройки, если макет сводной таблицы не соответствует вашим требованиям. Перейдите в
Дизайн > Макет > Макет отчета, измените его макет.
Ответ или решение
Создание сводной таблицы в MS Excel для трехмерных данных без числовых значений и с использованием логики "ИЛИ" может быть сложной задачей, но с правильным подходом эта задача решаема. В этом ответе я объясню, как можно достичь поставленной цели, и дам практические рекомендации по созданию динамичных и адаптивных сводных таблиц.
Теория
Сводные таблицы в Excel обычно используются для обобщения, анализа и представления данных в формате, который легко читать и понимать. Работая с данными, не содержащими числовые значения и не имеющими выраженных ключей, сводная таблица может столкнуться с ограничениями. Стандартная логика сводных таблиц основана на агрегировании числовых данных с использованием функций, таких как сумма, среднее, минимальное или максимальное значение. Однако, при работе с текстовыми данными, которые должны обрабатывать логическими операциями "ИЛИ", это может стать задачей.
Ключ к решению проблемы заключается в использовании возможностей PowerPivot и моделировании данных с помощью мощных функций DAX (Data Analysis Expressions), которые расширяют функциональные возможности Excel.
Пример
Рассмотрим набор данных, в котором у нас есть множества предложений для различных лиц по компаниям. Нас интересуют те случаи, когда хотя бы одно из предложений было принято.
Сначала необходимо импортировать данные в PowerPivot:
-
Выберите ваш набор данных и добавьте его в PowerPivot. Это можно сделать, выбрав "PivotTable" и затем "Add to Data Model".
-
В PowerPivot создайте три меры для каждого предложения, используя следующие формулы:
- Для "Offer1":
Offer1 := CALCULATE(COUNT(Table1[Offer 1]), FILTER(Table1, Table1[Offer 1] = "Accepted")) - Для "Offer2":
Offer2 := CALCULATE(COUNT(Table1[Offer 2]), FILTER(Table1, Table1[Offer 2] = "Accepted")) - Для "Offer3":
Offer3 := CALCULATE(COUNT(Table1[Offer 3]), FILTER(Table1, Table1[Offer 3] = "Accepted"))
- Для "Offer1":
Применение
Теперь, создавая сводную таблицу, вы можете добавить созданные меры, чтобы вычислить количество лиц, принявших хотя бы одно предложение. PowerPivot позволяет вам управлять показателями подобным образом, благодаря чему можно гибко манипулировать агрегированным текстом и выполнять сложную фильтрацию.
Чтобы создать сводную таблицу, выполните следующие действия:
- Вставьте новую сводную таблицу, выберите "PowerPivot" и выберите таблицы, которые были добавлены в модель данных.
- Затем, добавьте созданные меры в область значений сводной таблицы.
- Убедитесь, что выбрано отображение промежуточных итогов для каждой компании: это можно сделать в меню "Design > Subtotals > Show all subtotals at top of group".
Теперь у вас будет динамическая таблица, которая позволяет легко видеть количество людей, принявших одно или несколько предложений от каждой компании. Вы также можете щелкнуть на количество для детализации данных, чтобы увидеть список людей.
Заключение
Используя PowerPivot и DAX, вы расширяете возможности стандартных функций Excel, позволяя обрабатывать и анализировать сложные, многомерные данные. Вы можете индексировать текстом, применяя логику "ИЛИ", что делает обработку данных более гибкой и удобной для анализа. В данном контексте, знание этих инструментов является критически важным для вашего успеха в анализе данных, особенно когда традиционные методы Excel достигают своих пределов. Таким образом, вы сможете создавать настраиваемые и мощные аналитические отчеты, резюмирующие информацию наиболее информативным образом.