Транспонирование в Excel

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

В этой статье вы найдете несколько способов преобразования строк в столбцы (или столбцов в строки) в Excel. Эти решения работают во всех версиях Excel (2013, 2010, 2007 и более ранних), охватывают множество возможных сценариев, объясняют наиболее распространенные ошибки и показывают хорошие примеры использования формул транспонирования в Excel.

• Преобразование строк в столбцы с помощью Копировать > Вставить
• Транспонируйте таблицу, скопировав/вставив ссылки на исходные данные
• Транспонировать данные в Excel с помощью формул

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

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

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

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

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

      

     

      

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

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

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

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

Как транспонировать таблицу, ссылающуюся на исходные данные

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

1. Скопируйте строки, которые вы хотите преобразовать в столбцы (или столбцы, которые вы хотите преобразовать в строки).
2. Выберите пустую ячейку на том же или другом листе.
3. Откройте диалоговое окно «Специальная вставка», как показано в примере выше, и нажмите «Вставить ссылку» в левом нижнем углу диалогового окна.

    

   

    

   Результат будет примерно таким:

    

   

    

4. Выберите новые данные и откройте диалоговое окно Excel — найти и заменить. Вы также можете нажать Ctrl+H, чтобы сразу перейти на вкладку «Заменить).
5. Замените все символы, равные «=», на «xxx». Фактически, вы можете заменить их любым другим символом, не входящим в рассматриваемый диапазон.

    

   

    

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

    

   

    

6. Скопируйте таблицу с символами «xxx» и запустите команду «Специальная вставка» > «Транспонировать», чтобы преобразовать столбцы в строки (или строки в столбцы).
7. Снова откройте диалоговое окно «Найти и заменить», чтобы заменить все символы «xxx» на «=», то есть восстановить исходные ссылки на ячейки. Готовый!

    

   

    

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

Транспонируем данные в Excel при помощи формул

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

Преобразуем в Excel строки в столбцы при помощи функции TRANSPOSE (ТРАНСП)

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

В этом примере мы преобразуем таблицу, в которой перечислены штаты США с данными о населении:

Сначала нужно определить точное количество строк и столбцов в таблице. В нашем примере это 7 столбцов и 6 строк, включая заголовки. Давайте запомним эти числа и начнем преобразовывать строки в столбцы.

1. Подсчитайте ячейки, которые вы хотите транспонировать. В нашем случае это 7 столбцов и 6 строк.
2. Выберите пустой диапазон ячеек. Поскольку функция ТРАНСП (TRANSPOSE) изменяет вертикальный размер диапазона на горизонтальный, это означает, что вы должны выбрать столько строк и столбцов, сколько столбцов и строк в вашей исходной таблице соответственно. В нашем примере мы выбираем 6 столбцов и 7 строк.
3. Нажмите F2, чтобы войти в режим редактирования.
4. Запишите функцию ТРАНСП (TRANSPOSE) и укажите в скобках диапазон данных, которые вы хотите транспонировать. В нашем случае это будет выглядеть так:

   =ТРАНСП($A$1:$G$6)
   
   =ТРАНСП($A$1:$G$6)

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

1. Нажмите Ctrl+Shift+Enter.

Примечание. Обязательно нажмите Ctrl+Shift+Enter, так как это формула массива. Если вы просто нажмете Enter, чтобы ввести формулу, она не сработает.

Готовый! Наши данные транспонируются, 7 столбцов становятся 6 столбцами, что мы и хотели получить.

Преимущества функции TRANSPOSE (ТРАНСП)

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

Недостатки функции TRANSPOSE (ТРАНСП)

• Форматирование исходной таблицы не будет сохранено в преобразованной таблице, как показано на рисунке выше.
• Если какие-то ячейки исходной таблицы были пусты, то в транспонированных ячейках будут нули.
• Вы не сможете редактировать ячейки в преобразованной таблице, потому что функция ТРАНСП сильно зависит от источника данных. Если вы попытаетесь изменить любую ячейку, вы получите предупреждающее сообщение: вы не можете изменить часть массива).

Наконец, какой бы красивой и простой ни была функция ТРАНСП, ей не хватает гибкости, и поэтому она не является лучшим решением во многих ситуациях.

Преобразовываем строку в столбец или столбец в строку при помощи функции INDIRECT (ДВССЫЛ)

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

В этом примере мы будем использовать небольшую таблицу, чтобы вы могли сосредоточиться на процессе транспонирования, а не на данных в таблице. Мы будем использовать несколько сложную комбинацию функций ДВССЫЛ и АДРЕС).

Предположим, у вас есть данные в 4 столбцах (A — D) и 5 ​​строках (1 — 5):

1. Введите формулу, показанную ниже, в верхнюю левую ячейку диапазона назначения (допустим, это ячейка A7) и нажмите Enter:

   =ДВССЫЛ(АДРЕС(СТОЛБЦ(A1),СТРОКА(A1)))
   
   =ДВССЫЛ(АДРЕС(СТОЛБЦ(A1),СТРОКА(A1)))

   Если данные не начинаются со строки номер 1 или столбца A, то требуется немного более сложная формула:

   =ДВССЫЛ(АДРЕС(СТОЛБЦ(A1) — СТОЛБЦ($A$1) + СТРОКА($A$1), СТРОКА(A1) — СТРОКА($A$1) + СТОЛБЦ($A$1)))
   
   =ДВССЫЛ(АДРЕС(СТОЛБЦ(A1) — СТОЛБЦ($A$1) + СТРОКА($A$1), СТРОКА(A1) — СТРОКА($A$1) + СТОЛБЦ($A$1)))

   Где A1 — верхняя левая ячейка исходной таблицы. Также обратите внимание на использование абсолютных и относительных ссылок.

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

    

   

    

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

Но не волнуйтесь, это совсем не проблема. Вот как вы можете легко восстановить исходный формат:

• Скопируйте исходную таблицу.
• Выберите транспонированную таблицу.
• Щелкните по выделенной области правой кнопкой мыши и в разделе «Параметры вставки» (Insert Options) выберите «Формат» (Format).

   

  

   

Короче говоря, метод INDIRECT кажется более сложным решением проблемы преобразования строк в столбцы, чем метод TRANSPOSE).

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

Недостатки: На самом деле я вижу только один: не сохраняется формат исходных данных. Однако вы можете легко восстановить его, как показано выше.

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

Функции INDIRECT (ДВССЫЛ) и ADDRESS (АДРЕС) – синтаксис и логика

Функция ДВССЫЛ используется для создания косвенной ссылки на ячейку. Например, если вы хотите поместить значение ячейки B1 в ячейку A8, вы должны ввести следующую формулу в ячейку A8:

= ДВССЫЛ («B1»)

= ДВССЫЛ («B1»)

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

Я думаю, вы помните, что в формуле мы использовали еще 3 функции: ADDRESS (АДРЕС), COLUMN (СТОЛБЦ) и ROW (СТРОКА).

Функция ADDRESS используется для получения адреса ячейки из указанных вами номеров строк и столбцов. Помните: сначала строка, затем столбец. Например, ADDRESS(8,1) вернет $A$8.

Функция COLUMN(COLUMN) возвращает номер столбца данной ссылки на ячейку. Например, COLUMN(A3) вернет значение 1, поскольку столбец A является первым столбцом. Как вы могли догадаться, функция ROW работает точно так же, только для строк.

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

=ДВССЫЛ(АДРЕС(СТОЛБЦ(A1),СТРОКА(A1)))

=ДВССЫЛ(АДРЕС(СТОЛБЦ(A1),СТРОКА(A1)))

Как видите, в функции ADDRESS мы сначала устанавливаем столбец, а затем строку, и вот в чем хитрость! Помните, что функция АДРЕС принимает первое число как номер строки, а второе число как номер столбца. Другими словами, функция АДРЕС берет номер столбца, возвращаемый функцией СТОЛБЦ (COLUMN), и преобразует его в номер строки, затем берет номер строки и преобразует его в номер столбца, то есть меняет местами строки и столбцы.

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

• функции COLUMN (СТОЛБЦ) и ROW (СТРОКА) возвращают номера столбца и строки соответственно;
• функция ADDRESS меняет местами строку и столбец;
• функция ДВССЫЛ отправляет обратные данные в другую ячейку электронной таблицы Excel.

Совсем не страшно, правда?

Транспонируем данные при помощи макроса VBA

Написать скрипт, транспонирующий строки в столбцы, не составит труда, если вы хорошо владеете VBA. Если нет, то в интернете можно найти множество готовых макросов. Но если вы работаете с большими таблицами, помните, что метод Transpose в VBA ограничен 65536 элементами. Если ваш массив превысит этот лимит, все дополнительные данные будут автоматически потеряны.

Урок подготовила для вас команда сайта office-guru.ru

Источник: https://www.ablebits.com/office-addins-blog/2014/03/06/transpose-excel-rows-columns/

Перевод: Антон Андронов

Правила перепечатки

Дополнительные учебники по Microsoft Excel