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

Второй способ: специальная функция

Второй способ, как перевернуть таблицу в Excel, подразумевает использование функции, которая предназначена для переворота таблицы. Чтобы сделать это, необходимо:

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

Важно проводить выделение за рамками первоначальной таблицы.
Нажать на кнопку “Вставить функцию”.
В появившемся окне, из списка выбрать функцию “ТРАНСП” и нажать “ОК”.
Откроется окно, в котором нужно указать оригинал таблицы. Для этого кликните по полю “Массив”, выделите область в окне программы

Далее нажмите “ОК”.
Теперь установите курсор в конец введенной формулы, нажмите CTRL+SHIFT+ENTER.

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

Второй способ, как перевернуть таблицу в Excel на 90 градусов, не вызовет затруднений у пользователей.

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

​ обуви (столбец №1) повторяется​ ячейке, у которой​Вместо​H19 ​);​ общий итог.​ СВОДНАЯ ТАБЛИЦА.​ тому же все​ должны выделить диапазон​ Для этого нужно​

​ ТРАНСПОНИРОВАТЬ. Остальное оставляем​ таблицы расположена горизонтально,​ встречается в работе​ ниже

Обратите внимание на​Е​ (у нас будет​ размеров иметь актуальную​ столько раз, сколько​ номер строки совпадает​

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

​ENTER​- ссылка на верхнюю левую​скопировать таблицу в Буфер​Скачать примеры транспонирования таблиц​Местом, где будет создана​ исходное форматирование утеряно.​ ячеек в котором​ выделить только массив​ как есть и​ а данные расположены​ пользователей ПК. Но​ использование смешанных ссылок в формуле.​подсчитаем количество размеров​ их 2).​

​ транспонированную таблицу.​ у него имеется​ с номером столбца​нажать​

​ ячейку транспонированной таблицы​ обмена (​ в Excel.​ сводная таблица, выбираем​

1. ​ Придется выравнивать и​ будет 6 столбцов​
2. ​ со значениями и​ жмем ОК.​ вертикально соответственно. Стоимость​ тем, кто работает​
3. ​ При копировании формулы​ каждого наименования: =СЧЁТЕСЛИ(Наименования;D6)​
4. ​Для этого создадим Динамический​Для упрощения формул наложим​ различных размеров (столбец​ (​CTRLSHIFTENTER​

​ (на ту ячейку,​CTRL+C​Это достаточно своеобразный способ​ новый лист.​ подсвечивать снова. Так​ и 4 строки.​ проделать те же​В результате получили ту​ рассчитана по формуле:​ с массивами, будь​ вправо ссылка $E6​Примечание​ диапазон Наименования, чтобы​ ограничения на исходную​ №2). Иногда требуется​А1, B2, C3​.​ в которую Вы​);​

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

​: Не забудьте, что Наименования​

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

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

файл​выделить ячейку ниже таблицы​ заменить столбцы на​ таблиц можем выбрать​ внимание на то​ рисунке:​

1. ​ с другим расположением​ примера подсветим шапку​ высшей математике или​ зафиксирован столбец (зато​ в вышеприведенной формуле​ данных в исходную​ одного наименования не​ таблицу, а только​В​ (​ примера.​ (​
2. ​ строки (наоборот –​ необходимые пункты и​ что транспонированная таблица​Сразу заполняем активную ячейку​
3. ​​ строк и столбцов.​ таблицы зеленым цветом.​ таблицы в Excel,​ F$4 изменяется на​ — это Имя​ таблицу, транспонированная таблица​ может быть больше​ один столбец №2.​

​– это второй​A7​Также см. статью Функция СМЕЩ()​A8​ не получится) и​ перенести их в​ привязанная к исходной.​ так чтобы не​С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ​ Причем, заметим, что​Нам нужно расположить данные​ приходится сталкиваться с​ G$4, затем на​ созданного ранее Вами​ автоматически обновлялась.​ 10 (определяет количество​ Т.е. теперь у​

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

​ столбец,​) ввести формулу =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1))),​ в MS EXCEL​);​ дополнительно узнать общую​ нужные поля. Перенесем​ Измененные значения исходной​ снять выделенную область.​ транспонирование таблицы при​ зеленым подсвечены ячейки​

1. ​ таблицы горизонтально относительно​ этим явлением.​ H$4 и т.д.),​ Динамического диапазона.​Формула для Динамического диапазона: =СМЕЩ(Лист1!$A$6;;;СЧЁТЗ(Лист1!$A$6:$A$19)).​
2. ​ столбцов с размерами​ нас будет столбец​С​
3. ​ где​выделить нужное количество пустых​в меню Вставить (Главная​ сумму по заданному​ «продукт» в НАЗВАНИЯ​ таблице автоматически обновляются​ Вводим следующую формулу:=ТРАНСП(A1:D6)​ помощи команды ТРАНСП​ с тем же​
4. ​ вертикального расположения ее​Транспонированием таблицы в Excel​ а при копировании​В строке 4 (см.​
5. ​Затем создадим перечень уникальных наименований​ в транспонированной таблице);​ наименований обуви (уже​– это третий​А1​ ячеек вне таблицы​ / Буфер обмена)​ полю.​ СТОЛБЦОВ, а «цена​

​ в транспонированной.​Нажимаем CTRL+SHIFT+ENTER

Внимание! Функия​. ​ почти не используется​ содержанием

Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

​ почти не используется​ содержанием. Формула стоимости​ шапки.​ называется замена столбцов​ вниз наоборот ссылка F$4 не​ файл примера) создайте​ в столбце​ таблица отсортирована сначала​

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

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

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

Иногда требуется изменить направление, в котором располагаются ячейки. Это можно сделать путем копирования и вставки и применения команды «Транспонировать». Но в этом случае образуются повторяющиеся данные. Чтобы такого не происходило, можно вместо этого ввести формулу с функцией ТРАНСП. Например, на следующем изображении показано, как расположить горизонтально ячейки с A1 по B4 с помощью формулы =ТРАНСП(A1:B4).

Примечание: Если у вас текущая версия Office 365 , вы можете ввести формулу в левую верхнюю ячейку выходного диапазона, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выбрав диапазон вывода, введите формулу в левую верхнюю ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД . для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

На вкладке «Главная» находим в пункте меню «Абзац», выбираем иконку нижняя граница, кликаем и выбираем строчку «Нет границ»

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

О том, как проделать эту нехитрую операцию и рассказывает наш сегодняшний материал. Выходов тут несколько — и ни одного удобного. Недавно мне позвонили друзья и своим вопросом ошарашали. Разработчики Office посчитали нецелесообразным выводить это действие на правую кнопку мыши после выделения нужного фрагмента текста. Для произвольного выбора градуса переворота текста, на этапе когда область с текстом выделена можно поворачивать область на любой угол кликнув по точке расположенной по середине.

Вопрос был о том, как перевернуть текст в программе Word и сделать это максимально быстро. Просмотрела в 2010 вкладки к таблицам — вроде не сильно от 2007 отличается. Значения строк перейдут в столбцы и наоборот. В первую очередь нужно создать таблицу, для чего нажмите на одноименный пункт и выберите в нем «Нарисовать таблицу». В строке «Применить» поставьте «К выделенному тексту».

Превращение строк в столбцы и обратно

Постановка задачи

Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке – пустить по столбцу и наоборот:

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

Выделяем и копируем исходную таблицу (правой кнопкой мыши – Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.

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

Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.

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

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

После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут – это весьма экзотический, но очень мощный инструмент в Excel.

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

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

Способ 3. Формируем адрес сами

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

• Функция АДРЕС(номер_строки; номер_столбца) – выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
• Функция ДВССЫЛ(ссылка_в_виде_текста) – преобразует текстовую строку, например, “F3” в настоящую ссылку на ячейку F3.
• Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) – выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.

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

в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:

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

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

Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать

3 способа транспонировать в Excel данные

Добрый день уважаемый читатель!

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

Итак, транспонирование — происходит от глагола «транспонировать», далее от немецкого «transponieren», далее из французского «transposer» «переставлять, перемещать», далее из латынского «trānspōnere» — «перекладывать», «переносить», , математическое обозначение, это преобразование матрицы, в результате чего, столбцы становятся строками, а строки превращаются в столбцы. Говоря простым языком, это все значение в строке (горизонталь) перекинуть в столбец (вертикаль).

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

Возможность транспонировать в Excel реализовано 3 способами:

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

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

Для начала создаем таблицу для исходных данных:

Следующим шагом переходим к меню «Вставка» — «Сводная таблица», в диалоговом окне указываем данные для анализа и место где расположится сводная таблица:

Для анализа использована функция транспонировать в Excel, я использовал наглядно столбик «Наименование», которое сделал строкой и шапкой в таблице и столбец «Всего» которое превратилось в строковое значение:

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

Вторым вариантом транспонировать в Excel будет использование функции Excel «=ТРАНСП()», которая разрешит сохранить связь с первоначальными данными:

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

В нужном вам месте вводите формулу «=ТРАНСП()» и передаете в неё ссылку на весь объем диапазона, который необходимо транспонировать в Excel:

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

Транспонировать в Excel с помощью пункта «Меню» — «Специальная вставка» — «Транспонировать». Для использование этого метода вам необходимо скопировать данные которые мы будем транспонировать в Excel, с помощью кнопки меню «Копировать» или же сочетанием на клавиатуре ctrl+c.

Следующий шаг, это установить указатель на то место где вы хотите вставить транспонированные данные. Тут есть 2 варианта, это какой вам будет удобней, либо на панеле меню «Вставить» — «Транспонировать», либо то же самое но через контекстное меню при нажатии контекстного меню.

Ну вот мы и рассмотрели все 3 способа как делается функция транспонирования в Excel. Надеюсь эта информация вам пригодиться и вы будете более продуктивно использовать Excel.

Скачать пример можно здесь.

Excel: как «перевернуть»… таблицу

При выполнении расчетов в MS Excel иногда нужно поменять строки и колонки таблицы местами.

Причем речь идет не только о сугубо математических вычислениях.

При обработке экономических или бухгалтерских отчетов такая задача возникает сплошь и рядом!

Обычными формулами или копированием ячеек это делать очень неудобно.

Возникает вопрос: что можно предпринять в подобной ситуации? Но для начала определимся с терминологией.

Операция, в которой строки и столбцы таблицы меняются местами, называется транспонированием.

В Excel (любой версии!) такую операцию можно сделать, как минимум, двумя способами.

Операция несложная, можно обойтись и без рисунков.

Способ 1. Транспонирование данных через специальную вставку.

1) Выделите участок таблицы, которую хотите транспонировать. 2) Скопируйте данные в буфер (комбинации «Ctrl+C» или «Ctrl+Ins»). 3) Поставьте указатель активной ячейки в начало блока, где должна находиться транспонированная таблица. 4) В программе Excel 2003 вызовите «Правка Специальная вставка» (в MS Excel 2010-2016 щелкните левой кнопкой (ЛКМ) по небольшому треугольнику под иконкой «Вставить»). Откроется окно параметров специальной вставки. 5) В этом окне включите флажок «Транспонировать». 6) Нажмите «ОК».

На листе появится транспонированная таблица. Попробуйте изменить данные в исходной таблице.

Заметьте, что содержимое транспонированной таблицы не изменилось!

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

После специальной вставки обе таблицы будут автономны

После специальной вставки обе таблицы будут автономны.

В этом и заключается основной недостаток первого способа.

Для устранения этого недостатка можно задействовать другой метод решения задачи, – применить формулу-массив. Вот как это сделать.

Способ 2. Транспонирование данных через формулу массив.

1) На свободном участке листа выделите диапазон ячеек для будущей транспонированной таблицы. 2) Не снимая выделения (!) в первую ячейку диапазона запишите формулу «=Трансп(блок)». В качестве параметра «блок» введите диапазон исходной таблицы. Указать диапазон можно, выделив его прямо на рабочем листе. 3) Когда формула готова, нажмите «Ctrl+Shift+Enter»

Это важно, т.к. нам нужно не обычное выражение, а формула-массив

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

Откорректируйте исходные данные

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

Все изменения появятся в транспонированной таблице.

Важно! Транспонирование таблиц через функцию «=Трансп()» устанавливает связь между источником данных и результатом. В завершение темы пара замечаний по второму способу

В завершение темы пара замечаний по второму способу

В завершение темы пара замечаний по второму способу.

1) Вставить функцию «=Трансп()» вы можете с помощью Мастера функций, выбирать адреса ячеек можно прямо по рабочему листу. Но есть одна тонкость. После того, как в окне работы с Мастером формула готова, не нажимайте кнопку «ОК» – иначе вы получите обычную формулу, а она для решения задачи не годится. Не закрывая окно Мастера, нажмите комбинацию «Ctrl+Shift+Enter», программа MS Excel вставит на рабочий лист формулу-массив. 2) Диапазон ячеек для транспонированной таблицы желательно выбрать с учетом размеров исходной таблицы. Например, если исходные данные занимали 4 строки и 3 колонки, то диапазон для транспонированной таблицы должен занимать 3 колонки и 4 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в «лишних» ячейках транспонированной таблицы вы увидите значения «#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.

Важно! Стереть значения «#Н/Д» клавишей «Del» не удастся. Excel запрещает удалять элементы формулы-массива

Чтобы устранить это ограничение, сделайте так

Чтобы устранить это ограничение, сделайте так.

1) Выделите транспонированную таблицу. 2) Скопируйте ее в буфер обмена. 3) Не снимая выделения, вызовите «Правка Специальная вставка» (в MS Excel 2013-2016 щелкните ЛКМ по треугольнику под иконкой «Вставить»). 4) В окне параметров специальной вставки включите флажок «Значения». 5) Нажмите клавишу «ОК».

С этого момента участок рабочего листа с формулой-массивом превратился в обычные значения.

Вы сможете выделить ненужные ячейки и стереть их обычным способом (например, клавишей «Del»).

Преобразуем строки в столбцы в Excel при помощи Специальной вставки

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

1. Выделите строки и столбцы, которые требуется поменять местами. Если Вы хотите развернуть всю таблицу целиком, т.е. все ячейки с данными на листе, нажмите Ctrl+Home, а затем Ctrl+Shift+End.
2. Скопируйте выделенные ячейки. Для этого на выбор:
   • кликните по ним правой кнопкой мыши и выберите Copy (Копировать) в контекстном меню.
   • нажмите Ctrl+C.
   • нажмите кнопку Copy (Копировать) на вкладке Home (Главная) в разделе Clipboard (Буфер обмена).
3. Выделите первую ячейку диапазона, в который Вы собираетесь скопировать транспонированные данные.

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

1. Вставьте скопированные данные при помощи команды Paste Special (Специальная вставка) > Transpose (Транспонировать). Вы можете открыть диалоговое окно Paste Special (Специальная вставка) тремя способами:
   • На вкладке Home (Главная) в разделе Clipboard (Буфер обмена), нажмите маленькую чёрную стрелку под кнопкой Paste (Вставить) и выберите команду Transpose (Транспонировать).
   • Кликните правой кнопкой мыши по ячейке, в которую нужно вставить данные, и в контекстном меню выберите Paste Special (Специальная вставка) > Transpose (Транспонировать).
   • (наконец, самый быстрый способ) Нажмите Ctrl+Alt+V, выберите опцию Transpose (Транспонировать) и кликните ОК.

Замечание: Если Вы транспонируете ячейки, содержащие формулы, убедитесь, что во всех этих ячейках использованы абсолютные ссылки (со знаком $). Это необходимо, поскольку формулы также будут транспонированы, а ссылки на ячейки автоматически обновлены.

Как Вы только что увидели, опция Transpose (Транспонировать) в Excel позволяет преобразовать строки в столбцы (или столбцы в строки) буквально за несколько секунд. Уверен, что этот способ очень полезен, когда требуется транспонировать таблицу один раз. Пользуясь этим методом, Вы сохраняете исходные данные, и это ещё один аргумент в его пользу.

Однако, вышеописанный метод имеет два существенных недостатка, которые не позволяют считать его идеальным решением для транспонирования данных в Excel.

• Данный инструмент не доступен при копировании / вставке полнофункциональной таблицы Excel в отличие от простого диапазона. В этом случае Вам придётся скопировать таблицу без заголовков, либо преобразовать её в диапазон, т.е. кликнуть правой кнопкой мыши по любой ячейке таблицы и в контекстном меню выбрать Table (Таблица) > Convert to Range (Преобразовать в диапазон).
• Более серьёзное ограничение этого метода состоит в том, что он создаёт копию исходных данных, и, если они изменятся, Вам придётся повторять процесс и транспонировать таблицу снова. Никто не захочет тратить своё драгоценное время на то, чтобы преобразовывать одни и те же строки и столбцы по нескольку раз. Согласны?

Преобразуем строки в столбцы в Excel при помощи Специальной вставки

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

1. Выделите строки и столбцы, которые требуется поменять местами. Если Вы хотите развернуть всю таблицу целиком, т.е. все ячейки с данными на листе, нажмите Ctrl+Home, а затем Ctrl+Shift+End.
2. Скопируйте выделенные ячейки. Для этого на выбор:
   • кликните по ним правой кнопкой мыши и выберите Copy (Копировать) в контекстном меню.
   • нажмите Ctrl+C.
   • нажмите кнопку Copy (Копировать) на вкладке Home (Главная) в разделе Clipboard (Буфер обмена).
3. Выделите первую ячейку диапазона, в который Вы собираетесь скопировать транспонированные данные.

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

1. Вставьте скопированные данные при помощи команды Paste Special (Специальная вставка) > Transpose (Транспонировать). Вы можете открыть диалоговое окно Paste Special (Специальная вставка) тремя способами:
   • На вкладке Home (Главная) в разделе Clipboard (Буфер обмена), нажмите маленькую чёрную стрелку под кнопкой Paste (Вставить) и выберите команду Transpose (Транспонировать).
   • Кликните правой кнопкой мыши по ячейке, в которую нужно вставить данные, и в контекстном меню выберите Paste Special (Специальная вставка) > Transpose (Транспонировать).
   • (наконец, самый быстрый способ) Нажмите Ctrl+Alt+V, выберите опцию Transpose (Транспонировать) и кликните ОК.

Замечание: Если Вы транспонируете ячейки, содержащие формулы, убедитесь, что во всех этих ячейках использованы абсолютные ссылки (со знаком $). Это необходимо, поскольку формулы также будут транспонированы, а ссылки на ячейки автоматически обновлены.

Как Вы только что увидели, опция Transpose (Транспонировать) в Excel позволяет преобразовать строки в столбцы (или столбцы в строки) буквально за несколько секунд. Уверен, что этот способ очень полезен, когда требуется транспонировать таблицу один раз. Пользуясь этим методом, Вы сохраняете исходные данные, и это ещё один аргумент в его пользу.

Однако, вышеописанный метод имеет два существенных недостатка, которые не позволяют считать его идеальным решением для транспонирования данных в Excel.

• Данный инструмент не доступен при копировании / вставке полнофункциональной таблицы Excel в отличие от простого диапазона. В этом случае Вам придётся скопировать таблицу без заголовков, либо преобразовать её в диапазон, т.е. кликнуть правой кнопкой мыши по любой ячейке таблицы и в контекстном меню выбрать Table (Таблица) > Convert to Range (Преобразовать в диапазон).
• Более серьёзное ограничение этого метода состоит в том, что он создаёт копию исходных данных, и, если они изменятся, Вам придётся повторять процесс и транспонировать таблицу снова. Никто не захочет тратить своё драгоценное время на то, чтобы преобразовывать одни и те же строки и столбцы по нескольку раз. Согласны?

Способ 2: применение функции

Второй способ переворачивания в Экселе предполагает применения специализированной функции ТРАНСП.

1. Выделяем область на листе, равную по вертикальному и горизонтальному диапазону ячеек исходной таблице. Кликаем по значку «Вставить функцию», размещенному слева от строки формул.
2. Открывается Мастер функций. В списке представленных инструментов ищем наименование «ТРАНСП». После того, как нашли, выделяем и жмем на кнопку «OK».
3. Открывается окно аргументов. У данной функции только один аргумент – «Массив». Ставим курсор в его поле. Вслед за этим, выделяем всю таблицу, которую хотим транспонировать. После того, как адрес выделенного диапазона записан в поле, жмем на кнопку «OK».
4. Ставим курсор в конец строки формул. На клавиатуре набираем сочетание клавиш Ctrl+Shift+Enter. Это действие нужно для того, чтобы данные корректно были преобразованы, так как мы имеем дело не с одной ячейкой, а с целым массивом.
5. После этого программа выполняет процедуру транспонирования, то есть меняет в таблице столбцы и строки местами. Но перенос был сделан без учета форматирования.
6. Форматируем таблицу так, чтобы она имела приемлемый вид.

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

Мы выяснили, как поменять местами столбцы и строки в Excel. Существует два основных способа перевернуть таблицу. Каким из них воспользоваться зависит от того, планируете ли вы применение связанных данных или нет. Если таких планов не имеется, то рекомендуется использовать первый вариант решения поставленной задачи, как более простой. Мы рады, что смогли помочь Вам в решении проблемы.Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ТРАНСПОНИРОВАНИЕ функция в Excel — это один из вариантов изменения способа размещения или ориентации данных на листе; функция перевертывает данные, расположенные в строках по столбцам или от столбцов к строкам. Функция TRANSPOSE может использоваться для транспонирования одной строки или столбца данных или массива нескольких строк или столбцов.

01 из 02