Как из сводной таблицы сделать обычную
Еще одна типичная проблема для обработки данных с помощью Сводных таблиц — это «нарисованные» руками «Мега-таблицы», которые не поддаются анализу в силу специфики их структуры. В этой статье я расскажу, как быстро преобразовать такое «горе-творчество» в массив для построения сводной таблицы.
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 69 Кб | 2829 |
Итак, имеем такую таблицу, хотя рука не поднимается назвать это таблицей:
Для преобразования данной таблицы нам потребуется надстройка ЁXCEL.
Возвращаемся к нашей таблице и начинаем ее преобразовывать. Для начала убираем итоговые столбцы, переходим в правый конец таблицы, выделяем и удаляем столбцы:
Далее нам необходимо заполнить пустые строки в столбцах «Месяц» и «Менеджер». Для этого возвращаемся в начало таблицы, выделяем первые ячейки с данными в этих столбцах, в нашем случае это ячейки «А3:В3«. В главном меню заходим во вкладку ЁXCEL и нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Заполнить пустоты»:
В открывшемся диалоговом окне нажимаем «ОК»:
Получаем следующий результат:
Встаем курсором в ячейку «А2» и включаем фильтр. Отфильтровываем все строки по столбцу «А«, содержащие слово «Итог» и удаляем:
Сбрасываем фильтр по столбцу «А«. Повторяем операцию для столбца «В«:
Сбрасываем фильтр по столбцу «В» и выделяем всю таблицу. В главном меню во вкладке ЁXCEL нажимаем кнопку «Таблицы» в выпавшем меню выбираем команду «Трансформировать таблицу в массив»:
В открывшемся окне мастера нажимаем кнопку «Далее»:
В следующей вкладке мастера, так же нажимаем кнопку «Далее»:
В следующей вкладке мастера, отвечаем на вопрос «Транспонировать данные ряда?«, в нашем случае выбираем ответ «Нет» и нажимаем кнопку «Далее»:
В следующей вкладке мастера, отвечаем на вопрос «Исключить пустые строки?«, в нашем случае выбираем ответ «Да» и нажимаем кнопку «Трансформировать»:
Ждем. Получаем вот такой массив:
Встаем в ячейку «F1» и пишем название столбца «Наименование»:
Встаем в ячейку «А1» и строим сводную таблицу (Как построить сводную таблицу?):
Преобразование ячеек сводной таблицы в формулы листа
Сводная таблица имеет несколько макетов, предоставляющих предопределенную структуру отчета, но вы не можете настроить эти макеты. Если вам нужна дополнительная гибкость при проектировании макета отчета сводной таблицы, можно преобразовать ячейки в формулы листа, а затем изменить макет этих ячеек, используя все преимущества всех функций, доступных на листе. Можно преобразовать ячейки в формулы, использующие функции Куба, или использовать функцию GETPIVOTDATA. Преобразование ячеек в формулы значительно упрощает процесс создания, обновления и обслуживания этих настраиваемых сводных таблиц.
При преобразовании ячеек в формулы эти формулы имеют доступ к тем же данным, что и сводная таблица, и их можно обновить для просмотра актуальных результатов. Однако, за исключением фильтров отчетов, у вас больше нет доступа к интерактивным функциям сводной таблицы, таким как фильтрация, сортировка, развертывание и свертывание уровней.
Примечание: При преобразовании сводной таблицы OLAP можно продолжать обновлять данные, чтобы получить актуальные значения мер, но нельзя обновить фактические элементы, отображаемые в отчете.
Сведения о распространенных сценариях преобразования сводных таблиц в формулы листа
Ниже приведены типичные примеры того, что можно сделать после преобразования ячеек сводной таблицы в формулы листа для настройки макета преобразованных ячеек.
Изменение порядка и удаление ячеек
Предположим, у вас есть периодический отчет, который необходимо создавать каждый месяц для сотрудников. Вам требуется только подмножество данных отчета, и вы предпочитаете настраивать данные. Вы можете просто перемещать и упорядочивать ячейки в нужном макете, удалять ячейки, которые не нужны для ежемесячного отчета персонала, а затем форматировать ячейки и листы в соответствии с вашими предпочтениями.
Вставка строк и столбцов
Предположим, что вы хотите отобразить сведения о продажах за предыдущие два года, разделенные по регионам и группе продуктов, и вставить расширенный комментарий в дополнительные строки. Просто вставьте строку и введите текст. Кроме того, необходимо добавить столбец, в котором отображаются продажи по регионам и группе продуктов, которые не содержатся в исходной сводной таблице. Просто вставьте столбец, добавьте формулу, чтобы получить нужные результаты, а затем заполните столбец вниз, чтобы получить результаты для каждой строки.
Использование нескольких источников данных
Предположим, вы хотите сравнить результаты между рабочей и тестовой базой данных, чтобы убедиться, что тестовая база данных дает ожидаемые результаты. Можно легко скопировать формулы ячеек, а затем изменить аргумент соединения, чтобы он указывал на тестовую базу данных для сравнения этих двух результатов.
Использование ссылок на ячейки для изменения входных данных пользователя
Предположим, вы хотите, чтобы весь отчет менялся в зависимости от введенных пользователем данных. Можно изменить аргументы в формулах куба на ссылки на ячейки на листе, а затем ввести разные значения в этих ячейках для получения различных результатов.
Создание несоединимого макета строки или столбца (также называемого асимметричными отчетами)
Предположим, что вам нужно создать отчет, содержащий столбец 2008 с именем Actual Sales (Фактические продажи), столбец 2009 с именем Projected Sales (Прогноз продаж), но другие столбцы не нужны. Вы можете создать отчет, содержащий только эти столбцы, в отличие от сводной таблицы, для которой требуется симметричный отчет.
Создание собственных формул куба и многомерных выражений
Предположим, вы хотите создать отчет, в котором отображаются продажи определенного продукта тремя конкретными продавцами за июль. Если вы имеете опыт работы с выражениями многомерных выражений и запросами OLAP, можно ввести формулы Cube самостоятельно. Хотя эти формулы могут стать довольно сложными, вы можете упростить создание и повысить точность этих формул с помощью автозавершения формул. Дополнительные сведения см. в разделе «Использование автозавершения формул».
Преобразование ячеек в формулы, использующие функции куба
Примечание: Эту процедуру можно преобразовать только в сводную таблицу OLAP.
- Чтобы сохранить сводную таблицу для использования в будущем, рекомендуется создать копию книги перед преобразованием сводной таблицы, нажав кнопку » Файл > сохранить как «. Дополнительные сведения см . в разделе «Сохранение файла».
- Подготовьте сводную таблицу, чтобы свести к минимуму изменение порядка ячеек после преобразования, выполнив следующие действия:
- Перейдите к макету, который больше всего похож на нужный макет.
- Взаимодействуйте с отчетом, например фильтрацией, сортировкой и перепроектированием отчета, чтобы получить нужные результаты.
- Щелкните сводную таблицу.
- На вкладке « Параметры » в группе «Сервис» щелкните «Инструменты OLAP» и выберите команду «Преобразовать в формулы». Если фильтры отчетов отсутствуют, операция преобразования завершается. Если существует один или несколько фильтров отчетов, отобразится диалоговое окно «Преобразование в формулы».
- Выберите способ преобразования сводной таблицы: Преобразование всей сводной таблицы
- Установите флажок «Преобразовать фильтры отчетов «. При этом все ячейки преобразуется в формулы листа и удаляется вся сводная таблица. Преобразуйте только метки строк сводной таблицы, метки столбцов и область значений, но сохраните фильтры отчетов.
- Убедитесь, что флажок «Преобразовать фильтры отчетов » снят. (Это значение по умолчанию.) При этом все ячейки с областями меток строк, столбцов и значений преобразуется в формулы листа, а исходная сводная таблица сохраняется, но только с фильтрами отчета, чтобы можно было продолжать фильтрацию с помощью фильтров отчета.
Примечание: Если используется формат сводной таблицы версии 2000–2003 или более ранней, можно преобразовать только всю сводную таблицу.
- Невозможно преобразовать ячейки с фильтрами, примененными к скрытым уровням.
- Невозможно преобразовать ячейки, в которых поля имеют настраиваемое вычисление, созданное на вкладке «Показать значения как» диалогового окна «Параметры поля значений». (На вкладке «Параметры » в группе «Активное поле» щелкните «Активное поле» и выберите пункт «Параметры поля значений».)
- Для преобразованных ячеек форматирование ячеек сохраняется, но стили сводных таблиц удаляются, так как эти стили могут применяться только к сводным таблицам.
Преобразование ячеек с помощью функции GETPIVOTDATA
Функцию GETPIVOTDATA можно использовать в формуле для преобразования ячеек сводной таблицы в формулы листа, если вы хотите работать с источниками данных, не являемыми OLAP, если вы предпочитаете не выполнять обновление до нового формата сводной таблицы версии 2007 сразу или если вы хотите избежать сложности использования функций Куба.
-
Убедитесь, что команда «Создать GETPIVOTDATA » в группе сводной таблицы на вкладке « Параметры» включена.
Примечание: Команда «Создать GETPIVOTDATA» задает или очищает параметр «Использовать функции GETPIVOTTABLE для ссылок на сводную таблицу» в категории «Формулы» раздела «Работа с формулами» диалогового окна «Параметры Excel«.
Примечание: Если удалить из отчета любую из ячеек, указанных в формуле GETPIVOTDATA, формула возвращает #REF!.
Как из сводной таблицы сделать плоскую в Power Query
Когда мы получаем данные из выгрузки или от коллег, часто возникает проблема со структурой таблицы. Встаёт вопрос: «Как привести данные к нужной структуре, чтоб построить удобный и простой отчёт?».
Рассмотрим как должна выглядеть правильная структура таблицы. Правила будут следующими:
- У каждого столбца должен быть заголовок.
- В каждом столбце данные должны быть однородные, т.е. одного типа. Например, если столбец несет под собой значения даты, то в каждой строке в столбце «Дата» должен быть единый тип.
1. В заголовках имеем диапазон по дате или другим категориям
Чтобы исправить такую таблицу необходимо:
- через CTRL выделить все столбцы с диапазоном, в данном случае кварталы;
- перейти во вкладку «Преобразование»;
- найти кнопку «Отменить свертывания столбцов».
Получаем таблицу, с которой можем дальше проводить анализ в Power BI:
2. В одном столбце неоднородные данные
Бывает, что столбец с названием показателя вынесен отдельно:
Выделим нужный столбец и нажмем на кнопку «Столбец сведения», после чего откроется меню настройки.
Во вкладке «Столбец значений» выбираем значения, которые попадут в новые столбцы. Во вкладке «Функция агрегированного значения» выбираем пункт «Не агрегировать».
По окончании проделанных шагов получаем таблицу на рисунке ниже:
3. Сложная комбинация пунктов 1 и 2.
При комбинации случаев 1 и 2 первым делом необходимо избавиться от пустых значений null. Для этого выберем первый столбец и нажмем «Заполнить значения вниз».
Пустые значения первого столбца пропали, а на их месте теперь название филиала, которое было выше.
Далее перевернем таблицу, для этого нажмем на кнопку «Транспонировать», после выбираем «Заполнить значения вниз», как указано на рисунке ниже:
Следующим шагом избавимся от нескольких заголовков. Для этого необходимо объединить столбцы и нажать на кнопку «Объединить столбцы»:
Столбцы склеиваются в один:
Транспонируем таблицу обратно и используем первую строку в качестве заголовка:
Далее действуем как в предыдущих примерах. Выделяем нужный диапазон и нажимаем «Отменить свертывание столбцов»:
Разделим ранее склеенный столбец, чтобы отделить год.
Выделим столбец, где указаны кол-во и сумма, далее нажмем столбец сведения:
В результате получим простую таблицу, с которой удобно работать и которую легко анализировать:
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
Преобразование таблицы Excel в диапазон данных
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
После создания Excel может потребоваться только стиль таблицы без ее функциональных возможностей. Чтобы остановить работу с данными в таблице, не потеряв примененное форматирование стиля таблицы, можно преобразовать таблицу в обычный диапазон данных на этом сайте.
Важно: Для преобразования в диапазон у вас должна быть Excel таблица. Дополнительные сведения см. в Excel таблицы.
- Щелкните в любом месте таблицы, а затем перейдите в >конструктор на ленте.
- В группе Инструменты нажмите кнопку Преобразовать в диапазон. -ИЛИ- Щелкните таблицу правой кнопкой мыши, а затем в ярлыке выберите пункт Таблица > преобразовать в диапазон.
Примечание: Функции таблицы станут недоступны после ее преобразования в диапазон. Например, заголовки строк больше не будут содержать стрелки для сортировки и фильтрации, а использованные в формулах структурированные ссылки (ссылки, которые используют имена таблицы) будут преобразованы в обычные ссылки на ячейки.
- Щелкните в любом месте таблицы и перейдите на вкладку Таблица.
- Нажмите кнопку Преобразовать в диапазон.
- Нажмите кнопку Да, чтобы подтвердить действие.
Примечание: Функции таблицы станут недоступны после ее преобразования в диапазон. Например, заголовки строк больше не будут содержать стрелки для сортировки и фильтрации, а использованные в формулах структурированные ссылки (ссылки, которые используют имена таблицы) будут преобразованы в обычные ссылки на ячейки.
Щелкните таблицу правой кнопкой мыши, а затем в ярлыке выберите пункт Таблица > преобразовать в диапазон.
Примечание: Функции таблицы станут недоступны после ее преобразования в диапазон. Например, в заглавных строках больше нет стрелок сортировки и фильтрации, а вкладка Конструктор таблиц исчезнет.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.