Функция транспонирования в 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. Сводные таблицы занимают много места. Это может привести к некоторому сбою в работе компьютера. Также из-за этого файл будет сложно отправить по электронной почте.

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