Содержание
Трансформация столбца в таблицу
Представьте, что опосля выгрузки из какой-нибудь корпоративной 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 выполняется аналогично.
ФИО | Год рождения | Возраст на 2005 год | Место жительства | Домашний телефон | Цвет волос | Цвет глаз |
Леонов А.Д. | Химки | 503-76-11 | блондин | коричневый | ||
Хвесюк С. Р. | Зеленоград | 531-88-33 | блондин | коричневый | ||
Птицына О. Т. | Солнечногорск | 46-28 | блондин | коричневый |
Рис. 228 Перечень блондинов с коричневыми глазами
Рис. 229 Фамилии с сочетанием "ов"
Рис. 230 Четвёртое задание
В 5-ом задании итоговая таблица не обязана содержать все начальные столбцы. Для этого нужно заготовить заблаговременно заглавия столбцов для новейшей таблицы:
Рис. 231 Для 5-ого задания
Результат выполнения 5-ого задания представлен на Рис. 232:
Рис. 232 Готовое 5-ое задание
Готовое шестое задание представлено на Рис. 233:
Рис. 233 Готовое шестое задание и условие отбора для него
Преобразуйте сводную кросс-таблицу в тонкий перечень быстро и точно
(Внимание: видео может не отражать крайние обновления. Используйте аннотацию ниже.)
Кросс-таблицы также именуют сводными, двумерными (2D) либо таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и приятной матрице с заголовками столбцов и строк. Но такое представление данных не подступают для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в посторонние системы, т.д. Потому перед анализом данных так принципиально аккуратненько преобразовать сводные таблицы в «плоский» список.
Инструмент «Редизайн таблицы» точно преобразует сводные таблицы в тонкий перечень без написания макросов:
-
Редизайн сводной таблицы в перечень в секунды
-
Преобразование сложных таблиц с многоуровневыми заголовками
-
Корректный редизайн таблиц с объединёнными либо пустыми ячейками
-
Сохранение заголовков столбцов
-
Сохранение форматирования ячеек
Перед началом работы добавьте «Редизайн таблицы» в Excel
«Редизайн таблицы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, декстоп Office 365.

– пробный период дает 14 дней полного доступа ко всем инструментам.
Как преобразовать сводную таблицу Excel в тонкий список
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools
Раскроется диалоговое окно.
-
Выделите сводную таблицу, включая заголовки.Совет:нажмите на всякую ячейку таблицы, и вся таблица будет выделена автоматически.
-
Укажите размер заголовков:
В обычной таблице: Больших строк = 1, Больших столбцов = 1
-
Укажите, следует ли поместить итог на новейший либо на имеющийся лист.
Чтобы вставить тонкий перечень на имеющийся лист, укажите исходную ячейку (верхняя левая).
-
Нажмите ОК
Готово. Надстройка автоматом подберёт ширину столбцов для плоского списка.
Как преобразовать сложную сводную таблицу с многоуровневыми заголовками
Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заглавия. Их также можно сделать плоскими с помощью XLTools:
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools
Раскроется диалоговое окно.
-
Выделите сводную таблицу, включая заголовки.Совет:нажмите на всякую ячейку таблицы, и вся таблица будет выделена автоматически.
-
Укажите размер заголовков:
-
Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
-
Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
-
-
Укажите, следует ли поместить итог на новейший либо на имеющийся лист.
-
Нажмите ОК
Готово. Надстройка автоматом подберёт ширину столбцов для плоского списка.
Как выполнить редизайн таблицы с пустыми ячейками
Если в вашей сводной таблице имеются пустые ячейки, то и в соотвествующие ячейки плоского перечня также будут пустыми. При этом пустые значения в плоском перечне не несут важной инфы для анализа. Потому мы советуем следующее:
-
Если пустые ячейки находятся в заголовке: перед редизайном заполните ячейки заголовков.
-
Если пустые ячейки находятся в теле таблицы: вы сможете пропустить надлежащие строчки в плоском списке:
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools.
-
Выделите сводную таблицу, включая заголовки.
-
Укажите размер заголовков.
-
Отметьте флажкомПропустить пустые значения.
-
Укажите, куда поместить результат.
-
Нажмите ОК
Готово.
Как выполнить редизайн таблицы с объединёнными ячейками
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools.
-
Выделите сводную таблицу, включая заголовки.
-
Укажите размер заголовков.
-
Отметьте флажкомДублировать значение в объединённых ячейках:
-
Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответственной строке плоского списка.
-
Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
-
-
Укажите, куда поместить результат.
-
Нажмите ОК
Готово.
Как выполнить редизайн таблицы с сохранением заголовков
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools.
-
Выделите сводную таблицу, включая заголовки.
-
Укажите размер заголовков.
-
Отметьте флажкомСохранить заголовки:
-
Где это может быть, надстройка продублирует заглавия из сводной таблицы.
-
Категориям таблицы будет автоматом присвоен заголовок «Категория».
-
Переменным значениям таблицы будет автоматом присвоен заголовок «Значение».
-
-
Укажите, куда поместить результат.
-
Нажмите ОК
Готово.
Как выполнить редизайн таблицы с сохранением формата ячеек
-
Нажмите кнопкуРедизайн таблицына вкладке XLTools.
-
Выделите сводную таблицу, включая заголовки.
-
Укажите размер заголовков.
-
Отметьте флажкомСохранить формат ячеек:
Каждая ячейка сохранит своё форматирование в результирующем плоском перечне, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
-
Укажите, куда поместить результат.
-
Нажмите ОК