Печать
PDF

3 способа транспонирования данных в Excel

Автор: Алексей Батурин.

пример транспонирования +в excelЧасто в работе у нас возникает потребность перевернуть данные - из строчного представления сделать данные столбцом, из столбца перевернуть данные в строку. Поворот данных из столбцов в строки или наоборот называется транспонированием

 Транспонировать данные в Excel можно с помощью:

  • "Специальной" вставки с галочкой "Транспонировать";
  • Функции Excel =трансп();
  • и с помощью сводной таблицы.

Рассмотрим данные способы транспонирования на примерах.

У нас есть таблица с данными, и мы хотим из табличного представления данных сделать строчное - для этого транспонируем данные.


 транспонирование пример

1-ый способ транспонирования - специальная вставка


Транспонируем данные с помощью специальной вставки с галочкой "транспонировать".
Для этого выделяем необходимые данные и копируем их с помощью кнопки "копировать" или с помощью сочетания клавиш ctrl+c.

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

транспонирование   transponirovanie v excel

После нажатия откроется диалоговое окно, в котором ставим галочку "транспонировать" и нажимаем ОК

транспонирование в excel

Данные переворачиваются,  и мы получаем нужное нам строчное представление:

транспонирование пример в excel

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

2-ой способ транспонирования функция Excel =ТРАНСП()


Рассмотрим второй способ транспонирования, который, в отличие от первого, позволяет сохранить связь с исходными данными,  — с помощью функции Excel "=ТРАНСП()"

операция транспонирования

Итак, как с помощью формулы =ТРАНСП перевернуть данные?

1. Вводим формулу (как в картинке выше) и передаем в неё ссылку на весь диапазон, который хотим перевернуть:

данные для транспонирования

2. Выделяем диапазон в листе с формулой =ТРАНСП, равный массиву исходных данных (т.е равное количество строк и столбцов в перевернутом виде). 
Чтобы легче было выделить массив нужного размера, рекомендую перевести стиль ссылок в вид R1C1, чтобы столбцы и строки стали номерами. Это делается в параметрах Excel -> в разделе "Формулы" -> поставить галочку "Стиль ссылок R1C1". 

пример транспонирования в excel

транспонирование пример

Например, у нас в исходных данных значения расположены со 2й по 47 строку и 3 столбца, в источнике данных мы выделим 3 строки и 46 столбцов начиная с 1-го. После того, как диапазон выделен, нажимаем на клавиатуре клавишу F2, а затем одновременно Ctrl+Shift+Enter, таким образом мы вводим формулу массива и получаем перевернутые данные с сохраненными ссылками на первоначальный диапазон. 

транспонирование матрицы

Скачать пример транспонирования в Excel

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

Аналогичным образом диапазон переворачивается и в обратную сторону. 

3-ий способ транспонирования - сводная таблица


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

Сначала необходимо сделать сводную таблицу. Выделяем исходные данные:

транспонирование сводная таблица

Заходим в меню "Вставка" и выбираем пункт "Сводная таблица", и в диалоговом окне сводной нажимаем "ОК":

транспонирование в excel

В новом листе создается сводная таблица:

сводная таблица и транспонирование

В названия столбцов перетаскиваем поля "дата" и "товар", в значения перетаскиваем "объем продаж".

В меню "Конструктор" отключаем общие и промежуточные итоги. Меню "Конструктор" появляется в Excel при постановке курсора на сводную таблицу.

 транспонирование сводной в Excel

И получаем перевернутые данные:

транспонирование пример в excel

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

сводная транспонирование

Мы рассмотрели  3 способа транспонирования данных.

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

Воспользовавшись Forecast4AC PRO в листе "3-й способ", мы построили график, на который выведена "Модель прогноза", "Исходные данные", "Границы прогноза", и "Тренд".

Точных Вам прогнозов и хорошего настроения!

Скачать пример транспонирования в Excel

 

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

Статья полезная? Поделитесь с друзьями

Ваши комментарии и вопросы:

Добавить комментарий


Защитный код
Обновить