Превращение строк в столбцы и обратно

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

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:

transpose1.gif

Способ 1. Специальная вставка

Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special). В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.

transpose2.gif

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

Плюсы: в транспонированной таблице сохраняется исходное форматирование ячеек.

Способ 2. Функция ТРАНСП

Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):

transpose3.gif

После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива. Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.

Плюсы: между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.

Минусы: не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.

Способ 3. Формируем адрес сами

Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:

  • Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
  • Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
  • Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.

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

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:

transpose4.gif

Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.

Плюсы: сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.

Минусы: форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

 



22.03.2013 17:38:10
А как это сделать если у меня эти таблицы на разных листах и необходимо чтобы данные, добавляемые в основной таблице автоматически транспонировались во вторую?
Третий способ не работает, второй тоже пока не хочет, первый не устраивает.
07.08.2013 16:10:13
Третий вариант работает.
Мне больше всего он пригодился.
Не сразу получилось его сделать, проблема была в том что таблица, которую необходимо было перевернуть находилась в середине листа. Не стал парится, сделал новый лист, поместил туда копию нужной мне таблички, добавил связь на исходную. Ниже сделал "переворот" третьим способ, всё работает.
Так что всё работает, даже если делать таблицы на разных листах. Спасибо Николаю, за то что собрал столько полезной информации на одном сайте, и доходчиво всё изложил!
25.12.2013 14:50:38
Здравствуйте! А как можно привести исходную таблицу к такому виду?
ИмяКварталЗначение
Саша1 квартал5
Саша2 квартал10
Саша3 квартал15
Саша4 квартал33
Маша1 квартал44
Маша2 квартал66

25.12.2013 19:21:07
Да, с помощью вот такого макроса, например.
26.12.2013 07:02:11
Спасибо, Николай!
А Вы случайно не знаете как эту задачу решить с помощью сводных таблиц? Одна женщина тоже ведет курсы по Excel и говорит что такую задачу можно решить с помощью сводных таблиц "в 2-3 клика", но чтобы узнать как именно нужно идти на ее курсы.
26.12.2013 16:10:03
Строите сводную, куда помещаете все столбцы с данными, а потом делаете двойной щелчок левой по общему итогу. Excel выведет на новый лист детализацию по той ячейке, куда вы щелкнули, т.е. таблицу, очень похожую на ту, что вам нужно. Правда, этот способ работает не всегда - все зависит от исходных данных.
26.12.2013 16:24:48
понял, спасибо. Макрос конечно более универсальный способ.
26.02.2014 17:36:56
Добрый день.
Мне очень понравился 3-й способ, но мне не удалось повторить то же самое применительно к ячейкам расположенным не в первых строках и столбце, а в произвольном месте листа. Возможно надо модифицировать формулу, но не соображу как. Не подскажете?
скажите ,как быть в таком случае: таблица в таком виде
код    1.01.2014   2.01.2014
123    3                         6
234    4                         8
456    7                         7
768    9                          8

а нужна в таком:
дата                   код       кол-во
1.01.2014             123         3

1.01.2014              234        4

и т д по остальным датам.
17.05.2016 11:13:24
Добрый день! Очень нужна помощь! Подскажите как развернуть столбцы с датами,чтобы дата присвоилась в строку, сохранив в правильном порядке остальные строки описания и значения
тип операциипредприятие странаянв16фев16март16
производствокомпанияРоссия324
производсвтокомпанияРоссия589
производствокомпанияРБ845
22.04.2017 10:33:14
Вам нужен макрос для редизайна кросс-таблицы в плоскую. Либо использовать для этого надстройку Power Query.
14.04.2017 16:06:44
Мне на практике «Способ 2» очень пригодился при разработке метода: «Проектирование хранилищ данных DWH (Data Warehouse) в Microsoft Excel». Даже как-то статью на эту тему написал: http://biweb.ru/bi-dwh-and-excel.html
19.10.2017 15:07:38
есть таблица такого вида:
https://ibb.co/jNL4MR

как мне привести ее в горизонтальный вид
https://ibb.co/ibUg1R
15.03.2018 17:54:16
А если необходимо транспорировать горизонтальный динамический диапазон заданный с помощью функции СМЕЩ в вертикальный?
16.12.2018 13:38:13
Я бы это делал через Power Query - проще в разы :)
08.10.2018 20:47:04
Все три способа работают прекрасно! Спасибо, Николай!
29.10.2018 09:24:33
А если исходная таблица будет начинаться не с A1, а, например, c В9, то какова будет формула
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
29.10.2018 09:48:07
А вот и ответ:
=ДВССЫЛ(АДРЕС((СТОЛБЕЦ(A68)+67);(СТРОКА(A68)-67)))
07.12.2018 10:31:18
Спасибо за отличный ресурс по Excel, часто пользуюсь им.

В описании Способа 3 есть неверная формулировка:
Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(A3) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3
=СТРОКА(A3) выдаст 3 (третья строка), а СТОЛБЕЦ(A3) выдаст 1 (первый столбец)
16.12.2018 13:37:38
Спасибо, Антон! Подправил опечатку.
Наверх