Excel. Транспонирование строк в столбцы (или столбцов в строки) с помощью формулы массива

Рубрика: 7. Полезняшки Excel

Ранее я описал, как транспонировать столбцы в строки с помощью функции ДВССЫЛ.

Напомню, что, если нужно «переделать» таблицу, ориентированную по строкам, в таблицу, ориентированную по столбцам, можно скопировать строки в буфер, и вклеить на тот же или новый лист с помощью специальной вставки (рис. 1).

Рис. 1. Транспонирование с помощью специальной вставки.

Скачать заметку в формате Word, примеры в формате Excel

Это самый простой и удобный способ… если только не требуется сохранить связь новых данных со старыми (рис. 2).

Рис. 2. Результат транспонирования. Обратите внимание, что в строке формул отражается значение ячейки D1 – 1 (единица), константа.

Если же нужно сохранить связь новой области со старой, то можно использовать функцию ДВССЫЛ, но… гораздо проще и изящнее воспользоваться функцией =ТРАНСП(массив). Если вы ранее не сталкивались с функциями массива, рекомендую для начала посмотреть здесь. Что нужно сделать?

  1. В любой ячейке набрать =ТРАНСП(A1:B10); область A1:B10 можно выделить курсором, а не набирать с клавиатуры;
  2. Вырезать это значение в буфер обмена;
  3. Выделить область того же размера, что и исходная, то есть 2х10 ячеек, но ориентированную иначе: в исходной области 2 столбца и 10 строк, а в новой области 2 строки и 10 столбцов;
  4. Вставить содержимое буфера в строку формул (рис. 3);
  5. Одновременно нажать Ctrl + Shift + Enter, чтобы ввести формулу на лист Excel.

Рис. 3. Использование функции ТРАНСП

Получили новую область, связанную с исходной (рис. 4). Обратите внимание, что в ячейках D4:M5 одинаковые формулы массива {=ТРАНСП(A1:B10)}. Попробуйте изменить какое-либо значение в области A1:B10. Соответствующее изменение произойдет и в области D4:M5.

Рис. 4. Транспонирование с помощью функции массива

Не пытайтесь ввести фигурные скобки с клавиатуры – формула массива не заработает. Если требуется отредактировать формулу массива, выделите весь массив, отредактируйте формулу и нажмите Ctrl + Shift + Enter. Не пытайтесь вставить строки или столбцы внутрь нового массива! Не редактируйте формулу в отдельно взятой ячейке нового массива.

Успехов вам в овладении формулами массива! 🙂

Комментарии: 4 комментария

[…] […]

как готовую таблиц эксель развернуть на 90 градусов, чтобы не посбивался текст?

[…] […]

Статья очень помогла!!!!!!!!!!!!!

Таблица была сложная, и теперь можно анализировать данные через фильтры (проще)

Огромная благодарность Автору

Поклон


Прокомментировать