Как преобразовать диапазон в таблицу. Трансформация столбца в таблицу

Трансформация столбца в таблицу

Представьте, что опосля выгрузки из какой-нибудь корпоративной 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 данные фактически из всех источников и трансформировать их позже хоть каким хотимым образом.

Читайте также  Как в google chrome добавить вкладки. Как добавить вкладку в "Гугл Хром": советы

Для начала, превратим наш спектр в “умную таблицу” – для этого данные необходимо выделить и надавить сочетание кнопок 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):

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

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

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

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

Преобразование таблицы Excel в спектр данных

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

  1. Щелкните в любом месте таблицы, а потом перейдите в раздел работа с таблицами > конструктор на ленте.

  2. В группе Сервис выберите команду преобразовать в диапазон.

    -ИЛИ-

    Щелкните таблицу правой клавишей мыши, а потом в контекстном меню выберите пункт таблица > преобразовать в диапазон.

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

  1. Щелкните в любом месте таблицы, а потом откройте вкладку Таблица .

  2. Нажмите клавишу преобразовать в диапазон.

  3. Нажмите клавишу Да , чтоб подтвердить действие.

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

Щелкните таблицу правой клавишей мыши, а потом в контекстном меню выберите пункт таблица > преобразовать в диапазон.

Читайте также  Программа для перепрошивки планшета. LiveSuite - программа для прошивки Андроид

Примечание: Функции таблицы станут недосягаемы опосля ее преобразования в спектр. К примеру, заглавия строк больше не включают стрелки “Сортировка и фильтр”, а вкладка ” Конструктор таблиц ” исчезает.

Дополнительные сведения

Вы постоянно сможете задать вопросец спецу Excel Tech Community, попросить помощи в обществе Answers community, а также предложить новейшую функцию либо улучшение на сайте Excel User Voice.

См. также

Видео: создание и форматирование таблиц

Примечание:  Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на британском языке).

Оставьте комментарий