Формула транспонирования в excel

Формула транспонирования в 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. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.

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

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

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

ТРАНСП ( массив )

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

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

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

  • выделим строку длиной 5 ячеек;
  • в Строке формул введем =ТРАНСП(B2:B6) ;
  • нажмем CTRL+SHIFT+ENTER .

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

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

  • выделим столбец длиной 5 ячеек;
  • в Строке формул введем =ТРАНСП(B10:F10) ;
  • Нажмем CTRL+SHIFT+ENTER .
Читайте также:  Вывести первую цифру числа

Транспонирование диапазонов ячеек

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

СОВЕТ: Транспонирование можно осуществить и обычными формулами: см. статью Транспонирование таблиц .

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

Также транспонирование диапазонов значений можно осуществить с помощью Специальной вставки ( Главная/ Буфер обмена/ Вставить/ Транспонировать ). Для этого выделите исходный диапазон.

Выделите левую верхнюю ячейку диапазона, в который нужно поместить транспонированный диапазон. Выберите пункт меню Главная/ Буфер обмена/ Вставить/ Транспонировать . В результате получим транспонированный диапазон.

Вчера в марафоне 30 функций Excel за 30 дней мы считали количество столбцов в диапазоне, используя функцию COLUMNS (ЧИСЛСТОЛБ), а теперь пришло время для чего-то более востребованного.

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

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

Функция 13: TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) возвращает горизонтальный диапазон ячеек как вертикальный или наоборот.

Как можно использовать функцию TRANSPOSE (ТРАНСП)?

Функция TRANSPOSE (ТРАНСП) может изменять ориентацию данных, а также работать совместно с другими функциями:

  • Изменить горизонтальное расположение данных на вертикальное.
  • Показать лучший общий объём заработной платы за последние годы.

Чтобы изменить ориентацию данных, не создавая ссылок на исходные данные:

  • Используйте Paste Special (Специальная вставка) >Transpose (Транспонировать).

Синтаксис TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) имеет следующий синтаксис:

TRANSPOSE(array)
ТРАНСП(массив)

  • array (массив) – это массив или диапазон ячеек, которые нужно транспонировать.

Ловушки TRANSPOSE (ТРАНСП)

  • Функция TRANSPOSE (ТРАНСП) должна быть введена как формула массива, нажатием Ctrl+Shift+Enter.
  • Диапазон, который получится в результате преобразования функцией TRANSPOSE (ТРАНСП), должен иметь то же количество строк и столбцов, сколько столбцов и строк соответственно имеет исходный диапазон.

Пример 1: Превращаем горизонтальные данные в вертикальные

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

Читайте также:  Можно ли вернуть покупку в стиме

Чтобы транспонировать горизонтальный диапазон 2х4 в вертикальный диапазон 4х2:

  1. Выделите 8 ячеек, куда Вы хотите разместить полученный вертикальный диапазон. В нашем примере это будут ячейки B4:C7.
  2. Введите следующую формулу и превратите её в формулу массива нажатием Ctrl+Shift+Enter.

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

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

Пример 2: Изменяем ориентацию без ссылок

Если Вы просто хотите изменить ориентацию Ваших данных, не сохраняя ссылку на изначальные данные, Вы можете использовать специальную вставку:

  1. Выберите исходные данные и скопируйте их.
  2. Выберите верхнюю левую ячейку области, куда необходимо поместить результат.
  3. На вкладке Home (Главная) нажмите на выпадающее меню команды Paste (Вставить).
  4. Выберите Transpose (Транспонировать).
  5. Удалите исходные данные (по желанию).

Пример 3: Лучший общий объём заработной платы за прошедшие годы

Функция TRANSPOSE (ТРАНСП) может использоваться в сочетании с другими функциями, например, как в этой сногсшибательной формуле. Она была опубликована Харланом Гроувом в новостном блоке Excel в обсуждении вопроса о подсчёте лучшей общей суммы заработной платы за 5 прошедших лет (подряд!).

=MAX(MMULT(A8:J8, —(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; —(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)

Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Number и в этом примере число 4 введено, как значение для количества лет.

Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.

Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!

Ссылка на основную публикацию
Троттлинг видеокарты как отключить
«Автотормоз» — это бытовое название интеллектуальной защиты от зависания графического процессора в драйвере NVIDIA, начиная с версии 40.ХХ. Защита основана...
Темы для майкрософт повер поинт
Мы собрали для Вас самые лучшие шаблоны презентаций с четко продуманной структурой и отличным дизайном. Наши шаблоны подойдут всем тем,...
Троттлинг видеокарты как отключить
«Автотормоз» — это бытовое название интеллектуальной защиты от зависания графического процессора в драйвере NVIDIA, начиная с версии 40.ХХ. Защита основана...
Формула транспонирования в excel
Понятие «транспонировать» почти не встречается в работе пользователей ПК. Но тем, кто работает с массивами, будь то матрицы в высшей...
Adblock detector