Как транспонировать массив в excel

Функция транспонирования в Excel

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

Функция ТРАНСП — транспонирование диапазонов ячеек в Excel

Один из самых интересных и функциональных методов транспонирования таблиц в Excel – это функция ТРАНСП. С ее помощью можно превратить горизонтальный диапазон данных в вертикальный или же осуществить обратную операцию. Давайте разберемся в том, как с ней работать.

Синтаксис функции

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

Транспонирование вертикальных диапазонов ячеек (столбцов)

Предположим, у нас есть колонка с диапазоном B2:B6. Они могут содержать как готовые значения, так и формулы, которые возвращают результат в эти ячейки. Нам не столь сильно важно, транспонирование возможно в обоих случаях. После применения этой функции длина строки будет такой же самой, как и длина столбца исходного диапазона.

Последовательность действий для использования этой формулы следующая:

1. Выделяем строку. В нашем случае она имеет длину в пять ячеек.
2. После этого перемещаем курсор на строку формул, и там вводим формулу =ТРАНСП(B2:B6).
3. Нажимаем комбинацию клавиш Ctrl + Shift + Enter.

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

Транспонирование горизонтальных диапазонов ячеек (строк)

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

1. С помощью мыши выделяем эту колонку. Также можно воспользоваться клавишами на клавиатуре Ctrl и стрелочку вниз, предварительно нажав на самую верхнюю ячейку этой колонки.
2. После этого записываем формулу =ТРАНСП(B10:F10) в строку формул.
3. Записываем ее, как формулу массива, с помощью комбинации клавиш Ctrl + Shift + Enter.

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

Еще один возможный вариант транспонирования – использование функции «Специальная вставка». Это уже не оператор, который будет использоваться в формулах, но это также один из популярных методов превращения столбцов в строки и наоборот.

Эта опция находится на вкладке «Главная». Чтобы получить к ней доступ, необходимо найти группу «Буфер обмена», и там найти кнопку «Вставить». После этого открыть меню, которое находится под этой опцией и выбрать пункт «Транспонировать». Перед этим нужно выделить диапазон, который нужно выделить. В результате, мы получим такой же диапазон, только зеркально противоположный.

3 способа, как транспонировать таблицу в Excel

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

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

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

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

Наша задача – сделать так, чтобы информация, содержащаяся в таблице, располагалась горизонтально. То есть, чтобы столбцы стали строками. Последовательность действий в нашем случае будет следующей:

1. Выделяем диапазон данных, который нам нужно повернуть. После этого копируем эти данные.
2. Размещаем курсор в каком-угодно месте листа. Затем нажимаем на правую кнопку мыши и открываем контекстное меню.
3. Потом нажимаем на кнопку «Специальная вставка».

После выполнения этих действий нужно нажать на кнопку «Транспонировать». Вернее, поставить флажок возле этого пункта. Другие настройки не меняем, а потом нажимаем на кнопку «ОК».

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

Почти такие же действия нужно совершать, чтобы транспонировать значения, а не формулы. В этом случае также надо воспользоваться меню «Специальная вставка», но перед этим выделить диапазон данных, содержащих значения. Видим, что можно окно специальной вставки может быть вызвано двумя способами: через специальное меню на ленте или контекстное меню.

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

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

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

1. Сначала нам необходимо выделить тот диапазон данных, который будет использоваться для транспонирования таблицы. Только выделить нужно участок наоборот. Например, в этом примере у нас содержится 4 колонки и 6 рядов. Следовательно, нужно выделить участок с противоположными характеристиками: 6 колонок и 4 ряда. На рисунке очень хорошо это изображено.
2. После этого сразу начинаем заполнять эту ячейку. Важно при этом не снять случайно выделения. Поэтому надо указывать формулу непосредственно в строке формул.
3. Далее нажимаем комбинацию клавиш Ctrl + Shift + Enter. Помним, что это формула массива, поскольку мы работаем сразу с большим набором данных, которые будут переноситься в другой большой набор ячеек.

После того, как мы введем данные, нажимаем клавишу Enter, после чего получаем такой результат.

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

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

Следовательно, этот метод хорошо подходит в случаях, когда нужно обеспечивать привязку транспонированной таблицы к исходной. Если же пользоваться специальной вставкой, такой возможности не будет уже.

Сводная таблица

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

1. Делаем сводную таблицу. Чтобы это сделать, необходимо выделить ту таблицу, которую нам надо транспонировать. После этого переходим в пункт «Вставка» и ищем там «Сводная таблица». Появится такое диалоговое окно, как на этом скриншоте.
2. Здесь можно переназначить диапазон, из которого она будет делаться, а также внести ряд других настроек. Нас сейчас интересует прежде всего место сводной таблицы – на новом листе.
3. После этого будет автоматически создан макет сводной таблицы. В нем необходимо отметить те пункты, которые нами будут использоваться, а затем их надо перенести в правильное место. В нашем случае нам надо пункт «Продукт» перенести в «Названия столбцов», а «Цена за штуку» в «Значения».
4. После этого сводная таблица будет окончательно созданной. Дополнительный бонус – автоматический подсчет итогового значения.
5. Можно менять и другие параметры. Например, снять флажок с пункта «Цена за штуку» и отметить пункт «Общая стоимость». В результате у нас получится таблица, содержащая информацию о том, сколько стоит продукция. Этот метод транспонирования гораздо более функциональный по сравнению с другими. Давайте опишем некоторые преимущества сводных таблиц:

1. Автоматизация. С помощью сводных таблиц можно суммировать данные автоматически, а также менять положение столбцов и колонок произвольно. Для этого не надо выполнять никаких дополнительных действий.
2. Интерактивность. Пользователь может изменять структуру информации столько раз, сколько ему нужно для выполнения его задач. Например, можно изменить порядок колонок, а также группировать данные произвольным образом. Это можно сделать такое количество раз, сколько пользователю нужно. А времени это занимает буквально меньше минуты.
3. Легко форматировать данные. Очень легко оформить сводную таблицу таким образом, каким человеку хочется. Чтобы это сделать, достаточно совершить несколько кликов мыши.
4. Получение значений. Подавляющее число формул, которые применяются для создания отчетов, расположены в непосредственной доступности человека и их легко интегрировать в сводную таблицу. Это такие данные, как суммирование, получение среднего арифметического, определение количества ячеек, умножение, нахождение самого большого и самого маленького значения в указанной выборке.
5. Возможность создания сводных диаграмм. Если сводные таблицы пересчитываются, связанные с ними диаграммы автоматически обновляются. Есть возможность создания такого количества диаграмм, сколько нужно. Все они могут изменяться под конкретную задачу и они не будут взаимосвязаны.
6. Возможность фильтрации данных.
7. Возможно построение сводной таблицы, опираясь на больше, чем одном наборе исходной информации. Следовательно, их функционал станет еще больше.

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

1. Не вся информация может использоваться для генерации сводных таблиц. Перед тем, как их применять с этой целью, ячейки необходимо нормализовать. Простыми словами – оформить правильным образом. Обязательные требования: наличие строки заголовка, заполненность всех строк, равенство форматов данных.
2. Обновлять данные приходится полуавтоматическим методом. Чтобы осуществить получение новой информации в сводной таблице, необходимо нажать на специальную кнопку.
3. Сводные таблицы занимают немало места. Это может приводить к некому нарушению работы компьютера. Также файл будет тяжело отправлять по E-mail из-за этого.

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

Три способа как транспонировать таблицу в Excel

Понятие «транспонировать» почти не встречается в работе пользователей ПК. Но тем, кто работает с массивами, будь то матрицы в высшей математике или таблицы в Excel, приходится сталкиваться с этим явлением.

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

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

Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.

Нам нужно расположить данные таблицы горизонтально относительно вертикального расположения ее шапки.

Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:

1. Выделяем всю таблицу и копируем ее (CTRL+C).
2. Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.
3. Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.
4. В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.

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

Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.

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

С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.

Снова действует по этапам:

1. Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке:
2. Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)
3. Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.

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

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

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

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

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

Формулы массива в Excel

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.

Пример 2. Разрешите Вас. транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем "перевернутый массив" в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

ТРАНСП — Автоматическое транспонироване данных в Excel

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

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

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

Это можно сделать с помощью формулы ТРАНСП. Синтаксис данной функции очень прост

где массив это диапазон данных, которые необходимо преобразовать в вертикальный или горизонтальный вид (транспонировать).

Для реализации данного способа нам необходимо выбрать диапазон в котором автоматически будут транспонироваться данные. В нашем случае это C8:C13

После этого на клавиатуре сразу набираете формулу

В результате у вас должно получить следующее:

Далее, набрав формулу необходимо нажать одновременно (Ctrl+Shift+Enter) для применения формулы массива. В результате формула с двух сторон будет окружена фигурными скобками.

Важно понимать, что если вы просто укажите фигурные скобки, то функция ТРАНСП работать не будет. Кроме того массивы (диапазоны) должны быть одинаковой величины.

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