Вопрос или проблема
Это довольно странная проблема для объяснения.
В сущности, мои данные находятся в “длинном” формате. Несколько строк соответствуют одной точке данных. Я прикрепил фотографию моего power query, чтобы помочь вам понять:
Мой рабочий лист связан с формой Microsoft, где могут быть внесены новые диагнозы, услуги и ADF. Поэтому я настроил свои столбцы так, чтобы они динамически обновлялись, добавляя новые для новых вводов (например, если кто-то написал BPD и у меня еще не было столбца для этого, он создаст новый).
Однако я развернул столбцы, потому что в своей сводной таблице мне нужно иметь возможность фильтровать по диагнозам, услугам, ADF и т. д., и наличие фильтра для каждого отдельного диагноза заняло бы слишком много места. Поэтому я хотел иметь возможность фильтровать диагнозы (и т. д.) под одним заголовком.
МОЯ ПРОБЛЕМА:
Эти развернутые столбцы создают кучу новых строк для одной точки данных. Например, на картинке у LEO теперь 6 строк для его единственного ответа в 9 месяце. Мне нужно, чтобы сводная таблица, которую я затем создаю, не учитывала все 6 строк при расчете[вставьте код сюда][1] – так как это проблема, когда я пытаюсь суммировать некоторые значения, это значительно искажает цифры.
Я слышал, что люди используют “отличный” или “уникальный” подсчет для таких ситуаций – но я обеспокоен тем, что люди с одинаковым ID будут появляться несколько раз независимо от моих развернутых столбцов, так как данные являются продольными, поэтому LEO снова ответит с новой точкой данных в 12 месяце, например.
Надеюсь, это имеет смысл! – Готов ответить на любые уточняющие вопросы!
Спасибо за вашу помощь!
Ответ или решение
Решение проблемы с многострочными данными в сводной таблице Excel
Введение
Вам необходимо организовать данные, полученные из Microsoft Forms, так, чтобы они эффективно обрабатывались в сводной таблице. Поскольку ваша информация находится в «длинном» формате, с несколькими строками для каждой точки данных, необходимо найти способ обрабатывать эти множественные записи без искажения итоговых значений.
Проблематика
Проблема заключается в том, что после «разворачивания» (unpivot) ваших данных, ваша сводная таблица считает каждую запись за отдельную точку данных. Например, если у одного и того же клиента есть несколько диагнозов, это приводит к многократному суммированию значений, что искажает результаты анализа.
Предложенное решение
Для правильного учета данных в сводной таблице предлагаю следующие шаги:
-
Создание вспомогательных столбцов: Ваша задача — назначить уникальный идентификатор для каждой точки данных. Это можно сделать путем добавления нового столбца с формулой, которая будет объединять уникальные идентификаторы клиента и месяц. Например, используйте формулу:
=A2 & "-" & MONTH(B2) ; где A2 — ID клиента, B2 — дата. -
Использование функции "DISTINCT" в Power Pivot: Если ваши данные подключены через Power Pivot, вы можете использовать функцию
DISTINCTCOUNTна созданном вами столбце. Это позволит сводной таблице считать только уникальные комбинации идентификатора клиента и периода, что предотвратит множественное суммирование. -
Настройка сводной таблицы: В вашем сводном отчете используйте новый вспомогательный столбец в качестве строки или фильтра, что позволит понять, сколько клиентов или точек данных у вас на определенный период, без искажения значений за счет дублирования строк.
-
Правильное использование фильтров: Фильтруйте данные по диагнозам, услугам и другим переменным, используя сводные фильтры, чтобы избежать переполнения пространств за счет множества строк.
-
Проверка корректности данных: После применения этих изменений обязательно проверьте, что ваши итоговые суммы и средние значения корректны. Один из способов сделать это — создать отдельный расчетный столбец, который будет суммировать уникальные значения, чтобы убедиться, что ваши выводы верны.
Заключение
Работа с данными в длинном формате требует внимательности, особенно когда нужно учитывать множественные записи. Используя вспомогательные столбцы и функции агрегирования, мы можем добиться корректного отображения данных в сводной таблице. Следуйте вышеуказанным шагам для оптимизации вашего анализа, и ваше руководство по работе с данными станет более эффективным и точным.