Excel преобразовать строку в диапазон
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Если у вас есть таблица с данными в столбцах, которые необходимо повернуть для переупорядочивать их по строкам, используйте функцию Транспонировать. С его помощью можно быстро переключать данные из столбцов в строки и наоборот.
Например, если данные выглядят так: "Регионы продаж" в заголовках столбцов и "Кварталы" с левой стороны:
Функция Транспонировать переупомешет таблицу, в которой столбцы "Кварталы" отображаются в заголовках столбцов, а слева будут показаны регионы продаж, например:
Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.
Вот как это сделать:
Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.
Примечание: Убедитесь, что для этого нужно скопировать данные, так как не получится использовать команду Вырезать или CTRL+X.
Выберите новое место на том месте на компьютере, куда вы хотите ввести транспонную таблицу, чтобы вместить данные в достаточном месте. В новой таблице будут полностью переоформатироваться все данные и форматирование, которые уже есть.
Щелкните правой кнопкой мыши левую верхнюю ячейку, в которой нужно ввести транспонировать таблицу, и выберите транспонировать .
После успешного поворота данных можно удалить исходную таблицу, и данные в новой таблице останутся без изменений.
Советы по транспонированию данных
Если данные содержат формулы, Excel автоматически обновляет их в соответствие с новым расположением. Убедитесь, что в этих формулах используются абсолютные ссылки. Если они не используются, перед поворотом данных можно переключаться между относительными, абсолютными и смешанными ссылками.
Если вы хотите часто поворачивать данные для их просмотра под разными углами, создайте с помощью нее с помощью перетаскиванием полей из области строк в область столбцов (или наоборот)в списке полей.
Вы можете ввести в книгу данные в качестве транспон данных. Транспонировать: переупочевание содержимого скопированные ячейки при копировании. Данные строк будут вставлены в столбцы, и наоборот.
Вот как можно транспоннять содержимое ячейки:
Скопируйте диапазон ячеев.
Вы выберите пустые ячейки, в которые вы хотите ввести транспонировать данные.
На вкладке Главная щелкните значок Ввести и выберите Ввести транспонировать.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Примечание: Типы данных, демонстрируемые на ленте в этом видео, могут отличаться от того, что вы видите в Excel.
Теперь очень легко получать надежные данные в книге Excel по таким темам, как города, еда, животные, созвездия и т. д. С помощью связанных типовданных можно вставлять надежные данные из надежных интернет-источников и работать с ними. Корпорация Майкрософт вместе с Wolfram предоставлять интересные факты и данные по многочисленным темам, которые можно использовать и обновлять, не выходя из Excel.
Примечание: Связанные типы данных на основе Wolfram в настоящее время доступны только для пользователей с подпиской на Microsoft 365 и английским языком редактирования, добавленным в языковые параметры Office. Если вы соответствуете этим требованиям, но по-прежнему не видите эту функцию, может потребоваться обновить Office. Дополнительные сведения см. в дополнительных сведениях в этой области.
Проверьте, как это работает!
В Excel введите определенные термины в отдельные ячейки. Рекомендуется группировать темы вместе в одном столбце со строкой заголовков.
Совет: Для типа данных "Продукты" можно добавить в текст модификаторы, такие как количество или размер подачи, чтобы получить более конкретные результаты. Например: "3 яйца" или "100 г муки".
Выделите ячейки и выберите Вставка > Таблица, чтобы преобразовать их в таблицу. Установите флажок Таблица с заголовками, если вы хотите добавить заголовки.
Выделите в таблице текст, который нужно преобразовать. Откройте вкладку Данные и в группе Типы данных выберите Автоматически. Если в ячейке отображается , вам потребуется использовать мастер выбора данных, чтобы указать нужный тип данных.
Совет: Если вы уже знаете, какую группу типов данных использовать, вы можете выбрать ее из коллекции. Это позволит Excel лучше сопоставить ваш текст и извлечь нужные данные. Узнайте, какие связанные типы данных доступны в 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) во всех столбцах, кроме двух последних командой Заполнить вниз на вкладке Преобразовать (Transform - Fill Down) :
Удалить ненужные столбцы (первый и последний) и отфильтровать лишние строки (убрать галочку Null в фильтре по столбцу 5). Заголовки столбцов можно переименовать двойным щелчком для пущей приятности, а также настроить подходящие форматы данных с помощью иконок в шапке:
Готовую таблицу выгружаем на новый или любой из имеющихся листов с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить как (Home - Close&Load - Close&Load to) :
В будущем, если исходная таблица с данными поменяется в размерах или в содержимом, достаточно будет просто обновить наш запрос, щелкнув по финальной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) .
Способ 3. Макрос из надстройки PLEX
Если у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще быстрее и проще. Выделяем исходные данные и жмем на вкладке PLEX - Трансформация - Изменить размеры (Transform - Resize) :
Читайте также: