Функция транспонирования в 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 из-за этого.

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