Преобразовать таблицу в excel. Трансформация столбца в таблицу

Представьте, что опосля выгрузки из какой-нибудь корпоративной ERP-системы, базы данных либо программы вы получили данные в виде длинноватого столбца, а для работы для вас из их необходимо скомпоновать нормальную двумерную таблицу:

Разумеется, что вручную сиим заниматься чрезвычайно долго и муторно, потому давайте разберем несколько методов сделать это прекрасно.

Метод 1. Формулы

Ежели пристально приглядеться к начальным данным, то можно увидеть четкую систему – столбец делится на блоки по 7 ячеек, каждый из которых нам необходимо трансформировать в подобающую строку. Чтобы это сделать, необходимо привязаться к номерам строк в начальной таблице. Любая седьмая строчка (1,8,15…) – это дата сделки. На одну строчку ниже – имя менеджера. Еще на одну ниже – город и т.д. Для простоты и наглядности добавим к заготовке будущей таблицы нумерацию слева и сверху:

Числа слева (1,8,15…) – это номера строк, начинающих каждый блок. Числа сверху (0,1,2,3…) – это сдвиг снутри блока. Сейчас, чтоб получить адреса подходящих нам ячеек можно ввести простую формулу, склеивающую буковку столбца с номером строки:

Обратите внимание на корректное закрепление знаками бакса строк и столбцов – это принципиально при копировании ссылки из первой ячейки на весь зеленоватый спектр.

И осталось перевоплотить нашу текстовую ссылку в всеполноценную. В этом нам поможет функция ДВССЫЛ (INDIRECT), о которой я тщательно уже писал:

Вуаля.

Метод 2. Power Query

Power Query – это бесплатная надстройка для Excel, сделанная компанией Microsoft. Для Excel 2010-2013 ее можно совсем свободно скачать и установить (появится вкладка Power Query), а в Excel 2016 она уже встроена по умолчанию и все ее инструменты находятся на вкладке Данные (Data). Эта надстройка умеет импортировать в Excel данные фактически из всех источников и трансформировать их позже хоть каким хотимым образом.

Для начала, превратим наш спектр в “умную таблицу” – для этого данные необходимо выделить и надавить сочетание кнопок Ctrl+T либо выбрать на вкладке Главная команду Форматировать как таблицу (Home – Format as Table). В появившемся потом окне принципиально снять галочку Таблица с заголовками (My table has headers), т.к. “шапки” у нас нет:

Опосля загрузки наши данные покажутся в новеньком окне Power Query:

Сейчас добавляем к данным столбец индекса (т.е., практически, имитируем нумерацию строк) через вкладку Добавление столбца – Столбец индекса (Add Column – Index Column):

А потом добавим столбец, где выведем остаток от деления индекса на 7 с помощью команды Добавление столбца – Обычные – Остаток от деления (Add Column – Standard – Mod):

Сейчас свернем нашу таблицу, конвертируя числа в получившемся столбце в заглавия новейших колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (Transform – Pivot Column):

Осталось заполнить получившиеся пустоты (null) во всех столбцах, не считая 2-ух крайних командой Заполнить вниз на вкладке Преобразовать (Transform – Fill Down):

Удалить ненадобные столбцы (первый и последний) и отфильтровать излишние строчки (убрать галочку Null в фильтре по столбцу 5). Заглавия столбцов можно переименовать двойным щелчком для больщей приятности, а также настроить пригодные форматы данных с помощью иконок в шапке:

Готовую таблицу выгружаем на новейший либо хоть какой из имеющихся листов с помощью команды Главная – Закрыть и загрузить – Закрыть и загрузить как (Home – Close&Load – Close&Load to):

В будущем, ежели начальная таблица с данными обменяется в размерах либо в содержимом, довольно будет просто обновить наш запрос, щелкнув по финальной таблице правой клавишей мыши и выбрав команду Обновить (Refresh).

Метод 3. Макрос из надстройки PLEX

Ежели у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще скорее и проще. Выделяем начальные данные и нажимаем на вкладке PLEX – Трансформация – Поменять размеры (Transform – Resize):

В открывшемся окне настраиваем характеристики преобразования:

Нажимаем ОК и получаем готовый результат:

Скорее уже некуда 🙂

Ссылки по теме

1. Выделить(щелкнуть) любую ячейкуобласти данных;

2. Вкладка Вставка(Insert), команда Таблица(Table);

3. Указать диапазон, надавить OK.

рис. 218

Второй метод преобразованиядиапазона данных в Таблицу – выбрать команду

Форматировать как таблицу (Format as Table) в группе Стили(Styles) на вкладке Главная(Home).

рис. 219

Преимущества использования таблиц

1. Быстрое оформление;

Вкладка Конструктор(Design) дозволяет быстро переключаться меж различными стилями оформления; включать для таблицы Строку итогов(Total Row). Строчка итогов возникает внизу таблицы, даёт возможность для работы с числами каждого поля выбрать из перечня подходящую функцию.

2. Удачный просмотр огромных массивов данных.

При прокручивании таблицы вниз автоматом преобразуются наименования столбцов листа в наименования полей таблицы

3. В «шапке» таблицы – списки фильтрации и сортировки данных;

4. Автоматическое расширение спектра таблицы с копированием формул при вводе новейших строк либо столбцов рядом с данными таблицы.

Для принудительного конфигурации области таблицы нужно в правом нижнем углу протянуть за размерный маркер.

Задание 6. Преобразование спектра в таблицу

Откройте задание "База таблица". Преобразуйте спектр в таблицу:

рис. 220

Примените хоть какой стиль форматирования:

рис. 221

Отсортируйте таблицу по наименованию производителя.

Подсчитайте среднее количество брака:

Таблицу можно снова представить в виде диапазона:

рис. 222

Задание 7. Самостоятельная работа.

Откройте задание "Автофильтр". Ответьте на поставленные вопросы.

рис. 223

Расширенный фильтр.

Возможности расширенного фильтра:

1. Наиболее сложные условия отбора,

2. Размещениеотфильтрованных данных в другом диапазоне,

3. Отбортолько уникальных значений.

Для внедрения расширенного фильтранадо:

1. Построить таблицу условийотбора, отступив от данных на несколько пустых строк либо столбцов.

Вид таблицы условийотбора:

– Заглавие Столбца обязано совпадать с одним из заголовковтаблицы,

– Условия отбора в одной строкеработают как И,

– Условия отбора в различных строкахработают как ИЛИ.

2. Перейти в всякую ячейкуфильтруемой таблицы;

3. На вкладке Данные(Data) в группе Сортировка и фильтр(Sort&Filter)

нажать клавишу Дополнительно(Advanced), в появившемся окне выбрать тип обработки:

рис. 224

Задание 8. Применение Расширенного фильтра

· Откройте задание "Расширенный фильтр".

· Рассчитайте возраст на 2005 год.

· Ответьте на поставленные вопросы.

Рис. 225 Условие отбора

Задание1 выполняется последующим образом: для сотворения перечня служащих старше 18 лет и живущих в г.Зеленограде на листе Таблица заблаговременно заготовлено условие отбора . Для сотворения условия отбора заглавия столбцов были скопированы в ячейки A1:B1. В предстоящем, во избежание ошибок, необходимо воспользоваться конкретно операцией копирования.

Выделяем всякую ячейку начальной таблицы и даём команду Данные – Сортировка и Фильтр – Дополнительно.

Рис. 226 Диалоговое окно расширенного фильтра

Результат работы расширенного фильтра :

Рис. 227 Отобранные записи

Полученную таблицу скопируйте на лист Задание1.

Задание 2 выполняется аналогично.

Рис. 228 Перечень блондинов с коричневыми глазами

Рис. 229 Фамилии с сочетанием "ов"

Рис. 230 Четвёртое задание

В 5-ом задании итоговая таблица не обязана содержать все начальные столбцы. Для этого нужно заготовить заблаговременно заглавия столбцов для новейшей таблицы:

Рис. 231 Для 5-ого задания

Результат выполнения 5-ого задания представлен на Рис. 232:

Рис. 232 Готовое 5-ое задание

Готовое шестое задание представлено на Рис. 233:

Рис. 233 Готовое шестое задание и условие отбора для него

(Внимание: видео может не отражать крайние обновления. Используйте аннотацию ниже.)

Кросс-таблицы также именуют сводными, двумерными (2D) либо таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и приятной матрице с заголовками столбцов и строк. Но такое представление данных не подступают для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в посторонние системы, т.д. Потому перед анализом данных так принципиально аккуратненько преобразовать сводные таблицы в «плоский» список.

Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в тонкий перечень без написания макросов:

«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, декстоп Office 365.

Скачать XLTools для Excel

– пробный период дает 14 дней полного доступа ко всем инструментам.

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools Раскроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.

   

   Совет:нажмите на всякую ячейку таблицы, и вся таблица будет выделена автоматически.

3. Укажите размер заголовков:

   В обычной таблице: Больших строк = 1, Больших столбцов = 1

4. Укажите, следует ли поместить итог на новейший либо на имеющийся лист.

   Чтобы вставить тонкий перечень на имеющийся лист, укажите исходную ячейку (верхняя левая).

5. Нажмите ОК Готово. Надстройка автоматом подберёт ширину столбцов для плоского списка.

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заглавия. Их также можно сделать плоскими с помощью XLTools:

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools Раскроется диалоговое окно.
2. Выделите сводную таблицу, включая заголовки.

   

   Совет:нажмите на всякую ячейку таблицы, и вся таблица будет выделена автоматически.

3. Укажите размер заголовков:

   • Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
   • Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
4. Укажите, следует ли поместить итог на новейший либо на имеющийся лист.
5. Нажмите ОК Готово. Надстройка автоматом подберёт ширину столбцов для плоского списка.

Если в вашей сводной таблице имеются пустые ячейки, то и в соотвествующие ячейки плоского перечня также будут пустыми. При этом пустые значения в плоском перечне не несут важной инфы для анализа. Потому мы советуем следующее:

• Если пустые ячейки находятся в заголовке: перед редизайном заполните ячейки заголовков.
• Если пустые ячейки находятся в теле таблицы: вы сможете пропустить надлежащие строчки в плоском списке:

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.
4. Отметьте флажкомПропустить пустые значения.
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.

4. Отметьте флажкомДублировать значение в объединённых ячейках:

   • Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответственной строке плоского списка.
   • Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.

4. Отметьте флажкомСохранить заголовки:

   • Где это может быть, надстройка продублирует заглавия из сводной таблицы.
   • Категориям таблицы будет автоматом присвоен заголовок «Категория».
   • Переменным значениям таблицы будет автоматом присвоен заголовок «Значение».
5. Укажите, куда поместить результат.
6. Нажмите ОК Готово.

1. Нажмите кнопкуРедизайн таблицына вкладке XLTools.
2. Выделите сводную таблицу, включая заголовки.
3. Укажите размер заголовков.

4. Отметьте флажкомСохранить формат ячеек:

   Каждая ячейка сохранит своё форматирование в результирующем плоском перечне, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.

5. Укажите, куда поместить результат.
6. Нажмите ОК