Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как преобразовать диапазон в таблицу или наоборот в Excel?

Как сделать из горизонтальной таблицы excel вертикальную?

как сделать из горизонтальной таблицы excel вертикальную

В некоторых ситуациях бывает необходимо инвертировать таблицу, т.е. поменять местами строки и столбцы. Конечно, вы можете перестроить все данные в соответствии со своими потребностями, но это может занять много времени. Не все пользователи Excel знают, что в этой электронной таблице есть функция, которая может помочь автоматизировать эту процедуру. Давайте подробнее рассмотрим, как преобразовать строки в столбцы в Excel.

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

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

Постановка задачи

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

Способ 1. Формулы

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

Добавляем нумерацию

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

Формируем адрес нужной ячейки

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

Читайте так же:
Как распечатать длинную колонку на одной странице в Excel?

Осталось только преобразовать нашу текстовую ссылку в настоящую ссылку. В этом нам поможет функция INDIRECT, которую я уже подробно описал:

Используем ДВССЫЛ

Способ 2. Power Query

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

Во-первых, преобразуйте наш диапазон в «умную таблицу», выделив данные и нажав Ctrl + T или выбрав Главная — Формат как таблица на вкладке Главная. В появившемся окне важно снять флажок Моя таблица имеет заголовки, так как у нас нет «заголовка»:

Преобразуем данные в умную таблицу

После загрузки данные появляются в новом окне Power Query:

Окно Power Query с данными

Теперь добавьте индексный столбец к данным (т.е. фактически смоделируйте нумерацию строк) с помощью вкладки Add Column — Index Column:

Добавляем индекс

А затем добавим колонку для отображения остатка от деления индекса на 7 с помощью команды Добавить колонку — Стандарт — Результат деления (Добавить колонку — Стандарт — Мод) :

Остаток от деления на 7

Теперь давайте свернем нашу таблицу, превратив результирующий столбец в новый заголовок. Для этого выберите команду Info Column на вкладке Transform (Transform — Pivot Column):

Сворачиваем в столбцы

Заполните получившиеся пустоты (нули) на вкладке Transform (Transform — Fill Down), пока все столбцы, кроме двух последних, не будут заполнены нулями:

Заполнение вниз

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

Читайте так же:
Как разделить ячейки и заполнить повторяющиеся значения в Excel?

Убираем лишнее и переименовываем столбцы

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

Выгружаем результаты на лист

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

Способ 3. Макрос из надстройки PLEX

Если у вас установлена моя надстройка PLEX для Excel 2007-2016, это будет еще быстрее и проще. Выберите исходные данные и перейдите на вкладку PLEX — Transform — Resize:

Шаги для преобразования таблицы в текст

Начните с выделения таблицы и перейдите в Table Tools -> Layout -> Data Section. Нажмите кнопку «Преобразовать в текст».

Шаг 2: Появляется диалоговое окно настроек, аналогичное предыдущему. После объединения колонок в текст вам нужно будет решить, как их разделить. Я сохранил те же варианты (и двоеточие).

Отключение темы от Excel

Отсоединив электронную таблицу от Excel, вы разорвете связь между ней и Excel, но данные останутся в MindManager. Тогда изменения данных в MindManager не повлияют на таблицу Excel и наоборот.

Щелкните правой кнопкой мыши значок Microsoft Excel в строке темы письма.

Выберите пункт Выход из Microsoft Excel.

Эти данные заносятся на карту в виде таблицы.

Начало страницы

Способ 3. Сводная таблица

Excel — это многофункциональная электронная таблица, как знают те, кто ею пользуется. И одной из ее особенностей является возможность транспонирования. Тем не менее, оформление будет отличаться от приведенных ранее примеров.

  1. Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА. СВОДНАЯ ТАБЛИЦА.
  2. Местом, где будет создана сводная таблица, выбираем новый лист.
  3. В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем «продукт» в НАЗВАНИЯ СТОЛБЦОВ, а «цена за шт» в ЗНАЧЕНИЯ. Пример1.
  4. Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.
  5. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.
Читайте так же:
Как применить проверку данных, чтобы разрешить числа только в Excel?

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector