Вопрос или проблема
Я пытаюсь создать сводную таблицу, которая суммирует ресурс и количество дней, необходимых на каждую фазу проекта. Каждый ресурс может встречаться в нескольких фазах на разные продолжительности. Однако из-за объема данных, которыми я располагаю, я не могу просто добавить столбец с фазой, а затем вставить новую строку и строку данных для каждой из фаз.
Я могу сделать это, используя countif и sumif, но из-за изменяющегося характера моих данных и желания интегрировать слайсеры для фильтрации данных по определенным критериям, мне нужно, чтобы это было в сводной таблице.
Я думаю, что проблема, вероятно, заключается в том, как я организовал эти данные, пример которых можно увидеть здесь:
Значения не просто 1, 2 и 3 для дней в реальном наборе данных, но суть в том, что если ресурс выше определенной величины (не пустой или 0), то этот человек работает в этой фазе. Моя попытка определить, работает ли кто-то в конкретной фазе, были столбцы фазы, но они не нужны, так как это была просто моя попытка решения.
Пример того, как я представляю себе вывод, приведен ниже:
Я думаю, что проблема, вероятно, заключается в том, как я организовал эти данные.
Это верно, чтобы иметь возможность использовать сводную таблицу в ваших данных, структура ваших данных должна выглядеть как на скриншоте ниже.
В приведенной выше сводной таблице столбец “count of days2” — это ваш “Sum of Resources”.
Найдите образец Excel по следующей ссылке:
https://drive.google.com/open?id=1Szayirw84TXll8y6ASvZBtlxKrkN3nK5
Это было бы проще, если бы вы начинали с макета, который был спроектирован для суммирования, но вы можете сделать это на самом деле в несколько простых шагов. Я воссоздал лишь первые несколько актуальных столбцов данных.
Первый шаг — это добавление итогов по ресурсам и дням в конец каждого столбца. Итог ресурсов в H14:
=COUNTA(H2:H13)
Это просто считает ячейки с записью в столбце. Итог дней в H15:
=SUMIF(H2:H13,H$1,B2:B13)
Это суммирует значения в связанном столбце B, если столбец H содержит значение, которое соответствует заголовку столбца. Если ваша фактическая таблица отличается, просто используйте соответствующее условие теста, которое может быть просто “не пусто”.
После того, как вы ввели первые две формулы, вы можете скопировать или перетащить их, чтобы заполнить другие столбцы.
Чтобы получить результаты в столбцах O по Q, выберите диапазон заголовков с метками столбцов, затем удерживайте клавишу Ctrl, чтобы выбрать и добавить формулы, так как они не все смежные. Скопируйте выделенное (Ctrl+C или значок “Копировать” на панели инструментов).
Я вручную добавил заголовки столбцов в P и Q. Чтобы заполнить значения, щелкните в левом верхнем углу, где будут находиться результаты (в данном случае O2), щелкните правой кнопкой мыши и выберите “Специальная вставка”. Выберите “Значения” и “Транспонировать”, затем нажмите ОК.
Ответ или решение
Создание сводной таблицы в Excel, которая объединяет данные из нескольких столбцов, может представлять собой интересную задачу, особенно если структура данных не подходит для сводного анализа. Однако, с некоторыми изменениями в структуре данных и применением дополнительных приемов Excel, это вполне выполнимая задача. Рассмотрим теоретическую часть, примеры и их применение в данной ситуации.
Теория
Основное требование к любым данным, используемым в сводной таблице — это то, что данные должны соответствовать специфической структуре, известной как нормализованный формат. Это означает, что каждая запись должна быть представлена отдельной строкой, что позволяет легко агрегировать и фильтровать информацию. Когда ваши данные содержат несколько столбцов для одинаковых типов данных (например, фазы проекта или ресурсные дни), правильный подход заключается в преобразовании этих данных в форму, где каждый отдельный элемент данных занимает одну строку или столбец.
Пример
Рассмотрим ситуацию с вашими данными о ресурсах и количестве дней. Допустим, у вас есть таблица, в которой каждая строка посвящена ресурсу, и в ней указаны количество дней, запланированных на различные фазы проекта. Пример структуры данных может выглядеть следующим образом:
| Ресурс | Фаза 1 | Фаза 2 | Фаза 3 |
|---|---|---|---|
| Джон | 5 | 0 | 3 |
| Анна | 2 | 4 | 0 |
Вы хотите создать сводную таблицу, которая будет обобщать данные по ресурсам и количеству дней по каждой фазе.
Применение
Чтобы правильно структурировать ваши данные для сводной таблицы, выполните следующие шаги:
-
Трансформация данных: Преобразуйте текущую структуру данных в формат, который проще агрегировать. Вам потребуется привести данные к следующему виду:
Ресурс Фаза Дни Джон Фаза 1 5 Джон Фаза 3 3 Анна Фаза 1 2 Анна Фаза 2 4 Это потребует создания новых строк для каждого сочетания ресурса и фазы.
-
Использование Excel для преобразования: Используйте функции Excel, такие как
COUNTAиSUMIF, чтобы допомогать в определении общего количества дней и ресурсов. Перенос данных в новый лист вручную может занять некоторое время, но это необходимо для создания соответствующего формата. -
Создание сводной таблицы:
- Выделите всю вашу "новую" таблицу и выберите "Вставка" -> "Сводная таблица".
- Поместите "Ресурс" и "Фаза" в области "Строки", а "Дни" — в область "Значения".
- Обратите внимание, как это легко дает вам доступ к нужной информации и как вы можете легко использовать слайсеры для более детального анализа.
-
Добавление фильтров и слайсеров: Используйте встроенные инструменты Excel для добавления фильтров и слайсеров, чтобы повысить интерактивность вашего анализа. Например, вы можете добавить слайсеры для фильтрации по конкретным фазам или ресурсам, что существенно упростит анализ данных и последующую работу с ними.
Заключение
Создание сводной таблицы в Excel, соответствующей вашим требованиям, возможно при условии соответствующего изменения формата данных. Несмотря на то, что изначальная структура может не подходить для сводного анализа, ее преобразование в нормализованный формат делает возможным использование всех возможностей, предоставляемых сводными таблицами. Это улучшает гибкость анализа данных и позволяет легко адаптировать его под изменяющиеся требования бизнеса.
Эти шаги по переструктурированию ваших данных и созданию сводной таблицы помогут вам в более эффективной организации и анализе вашей информации, что в конечном итоге упростит принятие решений и сделает ваш рабочий процесс более продуктивным.