Вопрос или проблема
Я относительно новичок в сводных таблицах Excel и пытаюсь понять, как именно мне отформатировать свой сырой набор данных из внешнего приложения, чтобы, когда я загружал данные в Excel для анализа, я мог создавать разные сводные таблицы/графики из одного и того же набора данных или минимизировать количество форматирования, которое мне нужно выполнять каждый раз, когда происходит этот сброс данных.
В настоящее время у меня есть список элементов (каждый представлен в строке), которые имеют несколько перечисленных значений для определенных столбцов. Например, столбец цвета может быть “красный” или “красный, синий, черный”, а столбец животных может быть “собака” или “собака, попугай”.
| ID элемента | Цвет | Животное | Штат |
|---|---|---|---|
| 1 | Красный, Синий, Черный | Собака, Попугай | КС |
| 2 | Красный, Черный | Собака, Кошка | НЙ |
| 3 | Синий | Попугай | КС |
И я пытаюсь создать сводную таблицу, в которой разные животные будут строками, разные цвета — столбцами, а значения будут представлять собой количество продуктов (строк в моем оригинальном наборе данных), которые имеют такую комбинацию цвета/животного.
| Красный | Синий | Черный | |
|---|---|---|---|
| Собака | 2 | 1 | 2 |
| Кошка | 1 | 0 | 1 |
| Попугай | 1 | 2 | 1 |
Насколько я могу судить по своим исследованиям, не думаю, что есть способ создать приведенные выше сводные данные, если мой сырой набор данных отформатирован так, как я указал, независимо от используемого разделителя, но, пожалуйста, сообщите, если это неправильно.
Я понимаю, что один из способов отформатировать данные, чтобы мне было удобно проводить этот анализ, — это разбить несколько перечисленных столбцов на отдельные строки, скажем, три разных строки/записи в моем сыром наборе данных для перечислений “красный, синий, черный” в столбце цвета, а затем сделать то же самое с другими много перечисляемыми столбцами.
| ID элемента | Цвет | Животное |
|---|---|---|
| 1 | Красный | Собака |
| 1 | Синий | Собака |
| 1 | Черный | Собака |
| 1 | Красный | Попугай |
| 1 | Синий | Попугай |
| 1 | Черный | Попугай |
Проблема в том, что когда я использую тот же сырой набор данных для генерации сводных таблиц для подсчета свойств, отличных от цвета (например, Штат против Животного), один и тот же элемент, который теперь был разделен на несколько строк, теперь считается несколько раз, чего я не хочу.
Другим решением, которое я рассматривал, является использование одного набора сырых данных, который я имею, и создание разных версий его на разных листах, которые затем будут использоваться разными сводными таблицами. Например, когда я считаю по свойству цвета, я использую таблицу, которая разбивает мою запись “красный, синий, черный” на три разных строки, но когда я не считаю по цвету, я использую таблицу на другом листе, в которой всего одна строка.
Тем не менее, у меня такое ощущение, что я что-то упускаю. Я полагаю, что существует гораздо более элегантное решение, о котором я просто не знаю. Любая помощь будет оценена.
Ответ или решение
Для создания сводных таблиц в Excel с использованием данных, содержащих многократные перечисления, вам потребуется предварительно подготовить данные так, чтобы они могли быть эффективно использованы для анализа. Давайте рассмотрим несколько шагов для достижения этой цели, а также обсудим, как избежать потерь информации и непредвиденных повторений при анализе.
### 1. Подготовка данных
Ваши исходные данные имеют фиксированное количество колонок с многократными значениями. Для выполнения сводного анализа, первым делом нужно преобразовать эти данные в “долгий” формат. Это означает, что каждая строка должна представлять одну комбинацию элемента (например, сочетание цвета и животного).
#### Пример преобразования данных
Исходные данные:
| Element ID | Color | Animal | State |
|————|——————-|—————-|——-|
| 1 | Red, Blue, Black | Dog, Parrot | KS |
| 2 | Red, Black | Dog, Cat | NY |
| 3 | Blue | Parrot | KS |
После преобразования может получиться следующее:
| Element ID | Color | Animal |
|————|——-|——–|
| 1 | Red | Dog |
| 1 | Blue | Dog |
| 1 | Black | Dog |
| 1 | Red | Parrot |
| 1 | Blue | Parrot |
| 1 | Black | Parrot |
| 2 | Red | Dog |
| 2 | Black | Dog |
| 2 | Red | Cat |
| 2 | Black | Cat |
| 3 | Blue | Parrot |
### 2. Использование Power Query для трансформации данных
Если у вас имеется Excel версии 2010 и выше, вы можете использовать Power Query для автоматизации процесса преобразования данных. Это позволит вам каждый раз при загрузке новых данных проводить преобразование по одним и тем же правилам:
1. Импортируйте данные в Power Query.
2. Используйте функцию “Разделить столбец” по запятой для каждого столбца с многократными значениями (Color, Animal).
3. Преобразуйте данные в “долгий” формат, используя функцию “Развернуть”.
4. Закройте и загрузите данные обратно в Excel.
### 3. Создание сводных таблиц
После того как данные подготовлены, вы сможете легко создавать сводные таблицы:
– Перейдите на вкладку “Вставка” -> “Сводная таблица”.
– Выберите ваш подготовленный набор данных.
– В окне сводной таблицы расположите “Animal” в строках и “Color” в столбцах.
– Используйте “Количество” в значениях для подсчета количества уникальных записей.
### 4. Альтернативные подходы
Если ваш анализ требует различных способов представления данных, рассмотрите возможность создания динамических сводных таблиц с использованием фильтров:
– Создайте один набор данных и на его основе экспериментируйте с различными фильтрами и настройками сводных таблиц.
– Также можно создать дашборды, где вы сможете визуализировать данные, комбинируя свойства на одном интерфейсе.
### Заключение
Таким образом, рекомендую использовать подход с преобразованием данных в “долгий” формат, который позволит вам создавать сводные таблицы для различных комбинаций атрибутов, избегая дублирования записей при анализе. Использование Power Query значительно облегчит этот процесс и сделает ваши данные более гибкими для различных запросов анализа. Вы получите актуальные данные для всех нужд анализа, что значительно упростит работу с изменениями в исходном наборе данных.