Excel: как транспонировать таблицу
Уважаемая редакция! Работая с Excel, мне часто приходится перестраивать таблицу, меняя строки и столбцы местами. Формулами это делать очень неудобно. Подскажите, как можно решить проблему?
В. Суворова, г. Харьков
Николай КАРПЕНКО , канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Операция, в которой строки и столбцы таблицы меняются местами, называется
транспонированием. В Excel такую операцию можно сделать, как минимум, двумя способами. К сожалению, вы не указали версию Excel, которую используете в своей работе, поэтому дать подробную инструкцию по работе с интерфейсом я не могу. Да это, скорее всего, и не нужно. Операция несложная, поэтому можно обойтись и без рисунков.
Транспонирование данных через специальную вставку:
1) выделите блок таблицы, которую хотите транспонировать;
2) скопируйте данные в буфер обмена («
Ctrl+V» или «Ctrl+Ins»);
3) поставьте указатель активной ячейки в начало блока, где должна находиться транспонированная таблица;
4) вызовите меню «
Правка → Специальная вставка» (в Excel 2007 функцию специальной вставки можно вызвать, нажав на маленький треугольник под иконкой «Вставка»). Появится окно параметров специальной вставки;
5) в этом окне включите флажок «
На листе появится транспонированная таблица. Попробуйте изменить данные в исходной таблице. Обратите внимание, что содержимое транспонированной таблицы не изменилось.
Транспонирование таблиц через функцию специальной вставки не устанавливает связь между источником данных и результатом. После специальной вставки обе таблицы будут автономны.
В этом и заключается основной недостаток первого способа. Поэтому я предпочитаю пользоваться формулой-массивом. Вот как это сделать.
Транспонирование данных через формулу-массив:
1) выделите диапазон ячеек на рабочем листе для будущей
транспонированной таблицы;
2) не снимая выделения (!), в первую ячейку диапазона запишите формулу «
3) в качестве параметра «
блок» введите диапазон исходной таблицы. Указать диапазон можно, выделив его прямо на рабочем листе;
4) после того как формула готова, нажмите «
Ctrl+Shift+Enter». Это важно, так как нам нужна не просто формула, а формула-массив.
Все данные из первой таблицы будут автоматически отображаться в новой транспонированной таблице. Откорректируйте исходные данные. Все изменения появятся в транспонированной таблице.
Транспонирование таблиц через функцию «=Трансп()» устанавливает связь между источником данных и результатом.
В завершение темы хочу сделать пару замечаний по второму способу:
1. Вставить функцию «
=Трансп()» вы можете с помощью Мастера функций, выбирать адреса ячеек можно прямо по рабочему листу. Но есть одна тонкость. После того как в окне работы с Мастером формула готова, не нажимайте кнопку «ОК», иначе вы получите обычную формулу, а она для решения задачи не годится. Не закрывая окно Мастера, нажмите «Ctrl+Shift+Enter», и Excel внедрит на рабочий лист формулу-массив.
2. Диапазон ячеек для транспонированной таблицы желательно выбрать с учетом размеров исходной таблицы. Например, если исходные данные занимали 4 строки и 3 колонки, то диапазон для транспонированной таблицы должен занимать 3 колонки и 4 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в лишних ячейках транспонированной таблицы вы увидите значения «
#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.
Стереть значения «#Н/Д» обычным способом (например, клавишей «Del») не удастся. Excel запрещает удалять элементы формулы-массива.
Чтобы устранить это ограничение, сделайте так:
1) выделите транспонированную таблицу;
2) скопируйте ее в буфер обмена («
Ctrl+V» или «Ctrl+Ins»);
3) не снимая выделения, вызовите меню «
Правка → Специальная вставка» (в Excel 2007 это можно сделать, нажав на маленький треугольник под иконкой «/font>Вставка»);
4) в окне параметров специальной вставки включите флажок «
Теперь участок рабочего листа с формулой-массивом превратился в обычные значения. Вы сможете выделить ненужные ячейки и стереть их обычным способом (например, клавишей «
Вот и все. Надеюсь, что этот материал поможет решить проблему.
Жду ваших писем, вопросов, замечаний и предложений на
Excel: Транспонирование
Довольно часто при составлении таблиц встречается такая проблема.
Мы первоначально расставляем отдельные параметры по вертикали, а другие по горизонтали, но постепенно добавляя элементы таблицы, понимаем, что для наглядности представления информации в таблице необходимо поменять местами строки и столбцы.
Делать это вручную, то же самое, что начинать создание таблицы с нуля.
В Excel реализована возможность автоматически поменять столбцы и строки местами, этот процесс называется транспонированием .
- Скопируйте всю таблицу вместе с заголовками , используя кнопку Копировать .
Чтобы скопировать выделенные данные, можно также нажать клавиши CTRL+C.
Примечание. Для перестановки данных можно использовать только команду Копировать. Команда Вырезать для этого случая не подходит.
- Выделите на листе первую ячейку будущей таблицы.
Примечание. Исходная таблица и транспонированная таблица не должны перекрываться. Убедитесь, что выделенная ячейка находится вне области, из которой скопированы данные.
- Далее можно действовать несколькими способами.
На вкладке Главная выберите параметр Вставки — Транспонировать , нажав на значок
Выделив ячейку, кликните правой кнопкой мыши. Откроется меню, в котором необходимо выбрать опцию Транспонировать в Параметрах вставки.
Можно нажать клавиши CTRL+V для вставки данных, а затем нажав на смарт-тег выбрать опцию Транспонировать.
В окне Специальная вставка отметьте опцию транспонировать и нажмите кнопку ОК.
- В результате все заголовки будут расположены по горизонтали.
После того как данные будут успешно транспонированы, останется только удалить первоначальную таблицу.
Функция транспонирования в Excel
Время от времени пользователю Excel может быть поручено преобразовать диапазон данных, имеющих горизонтальную структуру, в вертикальную. Этот процесс называется транспонированием. Это слово является новым для большинства людей, потому что при нормальной работе с ПК нет необходимости использовать эту операцию. Однако любой, кому приходится работать с большими объемами данных, должен знать, как это делать. Сегодня мы более подробно поговорим о том, как это сделать, с помощью какой функции, а также более подробно рассмотрим некоторые другие методы.
- Все статьи (823)
- 1С (2)
- C# (3)
- Data Scientist (1)
- DevOps (1)
- EXCEL (385)
- Frontend-разработка (5)
- Java (6)
- PHP (1)
- Python (24)
- SMM (1)
- Unity (1)
- WORD (124)
- Бесплатные онлайн-курсы (7)
- Бизнес (3)
- Веб программирование (6)
- Гибкие навыки (5)
- Интернет магазины (3)
- Интерьер (93)
- Кибербезопасность (1)
- Маркетинг (13)
- Онлайн сервисы (2)
- Продажи (1)
- Профессии (53)
- Развитие (46)
- Разработка игр (2)
- Разработка приложений (5)
- Тестирование (2)
- Управление (7)
- Учимся рисовать (16)
- Финансы (4)
Показать все категории
Время от времени пользователю Excel может быть поручено преобразовать диапазон данных, имеющих горизонтальную структуру, в вертикальную. Этот процесс называется транспонированием. Это слово является новым для большинства людей, потому что при нормальной работе с ПК нет необходимости использовать эту операцию. Однако любой, кому приходится работать с большими объемами данных, должен знать, как это делать. Сегодня мы более подробно поговорим о том, как это сделать, с помощью какой функции, а также более подробно рассмотрим некоторые другие методы.
Функция ТРАНСП — транспонирование диапазонов ячеек в Excel
Одним из наиболее интересных и функциональных методов транспонирования таблиц в Excel является функция ТРАНСПОРТИРОВКА. С его помощью можно превратить горизонтальный диапазон данных в вертикальный или выполнить обратную операцию. Давайте узнаем, как с этим работать.
Синтаксис функции
Синтаксис этой функции невероятно прост: TRANSPOSE (массив). То есть нам нужно использовать только один аргумент, который представляет собой набор данных, который необходимо преобразовать в горизонтальный или вертикальный вид, в зависимости от того, каким он был изначально.
Транспонирование вертикальных диапазонов ячеек (столбцов)
Предположим, у нас есть столбец с диапазоном B2: B6. Они могут содержать как готовые значения, так и формулы, возвращающие результат в эти ячейки. Для нас это не так важно, транспозиция возможна в обоих случаях. После использования этой функции длина строки будет равна длине столбца исходного диапазона.
Последовательность шагов для использования этой формулы следующая:
- Выберите строку. В нашем случае он имеет длину пять ячеек.
- Затем переместите курсор в строку формул, и там мы вводим формулу = TRANSPOSE (B2: B6).
- Нажмите комбинацию клавиш Ctrl + Shift + Enter.
Конечно, в вашем случае вам нужно указать типичный диапазон вашей таблицы.
Транспонирование горизонтальных диапазонов ячеек (строк)
В принципе, механизм действия практически такой же, как и в предыдущем пункте. Предположим, у нас есть строка с начальной и конечной координатами B10: F10. Он также может содержать как прямые значения, так и формулы. Сделайте из него столбец, который будет по размеру аналогичен исходной строке. Последовательность действий следующая:
- Выделите этот столбец мышью. Вы также можете использовать клавиши клавиатуры Ctrl и стрелку вниз, предварительно щелкнув верхнюю ячейку этого столбца.
- Затем напишите формулу = TRANSPOSE (B10: F10) в строке формул.
- Запишем это как формулу массива с помощью комбинации клавиш Ctrl + Shift + Enter.
Транспонирование с помощью Специальной вставки
Другой возможный вариант транспонирования — использовать функцию «Специальная вставка». Это больше не оператор, который будет использоваться в формулах, но он также является одним из самых популярных методов преобразования столбцов в строки и наоборот.
Эта опция находится на вкладке «Главная». Чтобы получить к нему доступ, вам нужно найти группу «Буфер обмена» и найти там кнопку «Вставить». Затем откройте меню, расположенное под этой опцией, и выберите пункт «Транспонировать». Перед этим вам нужно выбрать диапазон, который вы хотите выбрать. В результате мы получаем тот же диапазон, прямо противоположный зеркалу.
3 способа, как транспонировать таблицу в Excel
Но на самом деле существует гораздо больше способов превратить столбцы в строки и наоборот. Мы описываем 3 метода, с помощью которых мы можем транспонировать таблицу в Excel. Мы рассмотрели два из них выше, но мы приведем еще несколько примеров, чтобы вы получили более четкое представление о том, как выполнять эту процедуру.
Способ 1. Специальная вставка
Этот способ самый простой. Просто нажмите пару кнопок, и пользователь получит транспонированную версию таблицы. Для наглядности возьмем небольшой пример. Допустим, у нас есть таблица, содержащая информацию о том, сколько предметов в настоящее время доступно и сколько они стоят в целом. Сама таблица выглядит так.
Видим, что у нас есть заголовок и столбец с номерами товаров. В нашем примере заголовок содержит информацию о том, какой продукт, сколько он стоит, сколько доступно и какова общая стоимость всех доступных товаров, связанных с этим элементом. Мы получаем стоимость по формуле, где стоимость умножается на количество. Чтобы сделать этот пример более понятным, сделаем заголовок зеленым.
Наша задача — сделать так, чтобы информация, содержащаяся в таблице, лежала горизонтально. То есть, чтобы столбцы стали строками. Последовательность действий в нашем случае будет следующая:
- Выберите диапазон данных, который нам нужно повернуть. Далее мы копируем эти данные.
- Поместите курсор в любое место на листе. Затем щелкаем правой кнопкой мыши и открываем контекстное меню.
- Затем нажмите кнопку «Специальная вставка».
После выполнения этих шагов вам необходимо нажать кнопку «Транспонировать». Скорее установите флажок рядом с этим элементом. Остальные настройки мы не меняем, поэтому нажимаем кнопку «ОК».
После выполнения этих действий мы остаемся с той же таблицей, только ее строки и столбцы расположены по-разному. Также обратите внимание, что ячейки, содержащие ту же информацию, выделены зеленым цветом. Вопрос: Что случилось с формулами, которые находились в исходном диапазоне? Их позиция изменилась, но сами они остались. Адреса ячеек просто поменяли на те, которые сформировались после транспонирования.
Более или менее аналогичные действия должны быть предприняты для транспонирования значений, а не формул. В этом случае вы также должны использовать меню «Специальная вставка», но сначала выберите диапазон данных, содержащий значения. Мы видим, что специальное окно вставки можно вызвать двумя способами: через специальное меню на ленте или через контекстное меню.
Способ 2. Функция ТРАНСП в Excel
На самом деле этот метод уже не так активно используется, как в начале этой программы для работы с электронными таблицами. Это связано с тем, что этот метод намного сложнее, чем использование специальных паст. Однако он находит свое применение для автоматизации транспонирования таблиц.
Кроме того, эта функция есть в Excel, поэтому вам обязательно нужно знать ее, даже если она почти не используется. Ранее мы рассмотрели порядок работы с ним. Теперь мы дополним эти знания еще одним примером.
-
Во-первых, нам нужно выбрать диапазон данных, который будет использоваться для транспонирования таблицы. Только нужно выделить область наоборот. Например, в этом примере у нас 4 столбца и 6 строк. Следовательно, необходимо выбрать область с противоположными характеристиками: 6 столбцов и 4 строки. На картинке это очень хорошо видно.
После ввода данных нажимаем клавишу Enter, после чего получаем следующий результат.
Мы видим, что формула не перенесена в новую таблицу. Форматирование тоже пропало. Здесь потому что
все это придется делать вручную. Также помните, что эта таблица связана с исходной. Следовательно, как только некоторая информация изменяется в исходном диапазоне, эти изменения автоматически вносятся в транспонированную таблицу.
Поэтому этот способ подходит в тех случаях, когда необходимо обеспечить привязку транспонированной таблицы к исходной. Если вы воспользуетесь специальной вставкой, такой возможности больше не будет.
Сводная таблица
Это принципиально новый метод, позволяющий не только транспонировать таблицу, но и выполнять огромное количество действий. Правда, механизм транспозиции будет немного отличаться от предыдущих способов. Последовательность действий следующая:
-
Создайте сводную таблицу. Для этого нам нужно выбрать таблицу, которую нужно транспонировать. Затем перейдите к пункту «Вставить» и найдите там «Сводную таблицу». Появится диалоговое окно, подобное тому, что показано на этом экране.
- Автоматизация. С помощью сводных таблиц вы можете автоматически суммировать данные и произвольно изменять положение столбцов и столбцов. Для этого не нужно выполнять никаких дополнительных действий.
- Интерактивность. Пользователь может изменять информационную структуру сколь угодно часто для выполнения своих задач. Например, вы можете изменить порядок столбцов и каким-либо образом сгруппировать данные. Это можно делать так часто, как захочет пользователь. И это занимает буквально меньше минуты.
- Легко форматировать данные. Сводную таблицу очень легко организовать так, как хочет человек. Для этого достаточно сделать несколько щелчков мышью.
- Получите ценности. Огромное количество формул, используемых для создания отчетов, находятся в непосредственной доступности человека и легко интегрируются в сводную таблицу. Они задаются как сложение, получение среднего арифметического, определение количества ячеек, умножение, нахождение наибольшего и наименьшего значения в указанной выборке.
- Возможность создавать сводные диаграммы. Если сводные таблицы пересчитываются, связанные диаграммы автоматически обновляются. Вы можете создать сколько угодно диаграмм. Все они могут быть отредактированы для определенного действия и не будут связаны между собой.
- Возможность фильтровать данные.
- вы можете создать сводную таблицу на основе нескольких наборов исходной информации. В результате их функциональность станет еще больше.
Правда, при использовании сводных таблиц следует учитывать следующие ограничения:
- Не всю информацию можно использовать для создания сводных таблиц. Перед тем, как использовать их для этой цели, клетки необходимо нормализовать. Проще говоря: организовать правильно. Обязательные требования: наличие строки заголовка, заполненность всех строк, равенство форматов данных.
- Данные необходимо обновлять полуавтоматическим способом. Чтобы получить новую информацию в сводной таблице, нужно нажать на специальную кнопку.
- Сводные таблицы занимают много места. Это может привести к выходу компьютера из строя. Также по этой причине будет сложно отправить файл по электронной почте.
Кроме того, после создания сводной таблицы у пользователя нет возможности добавлять новую информацию.
Транспонирование диапазона с изменяемым количеством элементов
В Excel очень просто транспонировать, обычный диапазон или таблицу. Первый способ, с использованием специальной вставки, подойдет для единичной операции. Если необходимо обновление транспонированных данных, следует воспользоваться функцией ТРАНСП (TRANSPOSE).
Есть и другие случаи, например, преобразования вертикального диапазона в таблицу, когда вверху списка формируется заголовок, а ниже элементы списка, однако, ни один из указанных вариантов не предусматривает случая, когда записи содержат разное количество элементов.
Например, если в списке клиентов у кто-то не указан телефон. Да, можно установить прочерк, а саму запись оставить, и проблема с транспонированием решалась тривиально, однако, если этот список формируется внешним приложением и такой вариант отсутствует.
Постановка задачи
Постановка задачи сводится в преобразовании вертикального диапазона в горизонтальный. Если у записи отдельный элемент отсутствует, то здесь можно оставить поле пустым, сделать прочерк, или пометку об отсутствии данных.
Используемые функции и инструменты
В ходе решения поставленной задачи будут использоваться функции, которые условно можно разделить на две части.
Основные, т.е. непосредственно отвечающие за решение задачи:
| Формула | Применение в рамках данной задачи |
|---|---|
| ВПР (VLOOKUP) | Основа формулы, используется для сопоставления заголовков таблицы и заголовков диапазонов исходного списка |
| ДВССЫЛ (INDIRECT) | Используется для передачи первой ячейки плавающего диапазона в ВПР |
| СМЕЩ (OFFSET) | Используется для определения размера плавающего диапазона, для последующей передачи данных в ВПР |
| СЧЁТЕСЛИ (COUNTIF) | Используется для подсчеты ошибок в каждой строке итоговой таблицы |
Вспомогательные, т.е., без которых можно обойтись:
| Формула | Применение в рамках данной задачи |
|---|---|
| ЕСЛИОШИБКА (IFERROR) | Используется для придания эстетического вида таблице, т.е. замене ошибок на пустые значения ячейки. |
| СЧЁТЗ (COUNTA) | Используется для подсчета столбцов таблицы |
Решение:
В начале составляем заголовок будущей таблицы, количество столбцов будет соответствовать записи с максимальным количеством элементов. Сразу же, с помощью функции СЧЁТЗ, подсчитаем количество записей.
Основой нашей будущей формулы, будет составлять функция ВПР, которая будет искать совпадения в заголовке и исходном диапазоне. Однако, поскольку функция ВПР осуществляет поиск до первого совпадения, нам нужно будет в качестве таблицы выбирать не полный диапазон, а части его (записи на первом рисунке).
Здесь появляются первые сложности. Первая заключается в том, что записи могут содержать разное количество элементов, а вторая заключается в том, что аргумент «таблица», для функции ВПР будет «плавающим», т.е. разным для каждой строки новой таблицы.
Для решения второй сложности пригодится функция ДВССЫЛ (INDIRECT), которая поможет определить первую ячейку каждого «плавающего» диапазона, а с помощью функции СМЕЩ (OFFSET) мы выберем размер каждого «плавающего диапазона».
Размер каждого диапазона зависит от количества элементов в каждой записи. И здесь появляется очень интересный нюанс, дело в том, что количество элементов в записи мы будем определять по формуле:
К-во элементов записи = максимальное к-во элементов – к-во ошибок #НД, возвращаемых функцией ВПР
Другими словами, размер диапазона, который необходимо передать функции ВПР будет зависеть от вычисления этой самой функции ВПР. В Excel такие вычисления называются «итеративными», их еще называют «циклическими», или «циклическими ошибками».
Само по себе «зацикливание» формулы, когда результат ее вычисления напрямую или косвенно зависит от нее самой, не является ошибкой, однако, в большинстве случаев, такое зацикливание случается именно из-за ошибки пользователя, поэтому по умолчанию в Excel отключены итеративные вычисления.
Для включения итеративных вычисления следует перейти в раздел «Формулы» параметров Excel.
Вместе с включением циклических вычислений задается и предельное число итераций (по умолчанию 100), чтобы программа не уходила в бесконечный цикл вычислений, если формула не будет иметь верхнего предела вычисления, например, формула:
A2 = A2+2запросто «повесит» систему, если не ограничить количество циклов. В нашем случае 100 вычислений будет с явным избытком, уменьшим значение до 10.
Формула для подсчета ячеек итоговой таблицы с ошибками будет использовать функцию СЧЁТЕСЛИ:
=СЧЁТЕСЛИ(E2:K2;НД())
Но нам нужно не количество ячеек с ошибками, а количество ячеек без ошибок, поэтому отнимем от максимального количества столбцов данное значение (не забываем зафиксировать ячейку с максимальным количеством столбцов абсолютной ссылкой):
=$D$1-СЧЁТЕСЛИ(E2:K2;НД())Теперь можно записать итоговую формулу преобразования вертикального диапазона в таблицу:
=ВПР(E$1;СМЕЩ(ДВССЫЛ("A"&$D2);0;0;$M2;2);2;ЛОЖЬ)А увидеть, что ячейки с формулами на листе взаимозависимые легче всего на листе Excel:
Или воспользоваться инструментом отображения влияющих и зависимых ячеек:
Т.е. для ячеек из диапазона E2:K2 ячейка M2 является одновременно и влияющей и зависимой, равно как и для ячейки M2 диапазон E2:K2 является и влияющим и зависимым.
Решение задачи на этом не закончилось дело в том, что для функции ВПР, в качестве левого верхнего края аргумента «таблица» мы указали ссылку на ячейку D2 со значением 1, а, растягивая формулу вниз это значение должно смещаться ровно на столько строк, сколько элементов без ошибок было в предыдущей записи итоговой таблице, т.е. значение предыдущей ячейки плюс количество элементов предыдущей записи.
Теперь можно вторую строку с помощью автозаполнения растянуть вниз. Однако, из-за того, что у нас формулы взаимозависимы придется сделать пересчет столбца с расчетом количества элементов в записи. С одной стороны, автоматизация выглядит слабовато, с другой – добавление новых записей корректно обрабатывается формулами.
Данный раздел сугубо косметический и очень простой, если вас смущают ошибки в итоговой таблице, их можно легко обработать с помощью функции ЕСЛИОШИБКА.
Однако лучше это делать не в самой таблице, т.к. перехват ошибок является основой наших вычислений, а вывести результат в новое место, заодно скрыть промежуточные вычисления:
=ЕСЛИОШИБКА(Лист1!E1;"")Казалось бы, промежуточные вычисления можно спрятать в основную формулу, однако, из-за циклических вычисления, мы делали пересчет только для одного столбца, в случае, если поместить вычисление столбцов с ошибками в основную формулу, то такой пересчет придется делать по каждой ячейке формулы.
Более того, опытным путем удалось установить, что расчет количества ошибок необходимо делать справа от самой итоговой таблицы, если поместить колонку с расчетом ошибок слева, то подсчет будет неправильным. Скорее всего, это связано с особенностями вычислений в Excel.
В данном материале был рассмотрен общий случай, когда количество записей может меняться в достаточно широких рамках. Если рассматривать случай, когда в записи будет отсутствовать один или пару элементов, то проверку на наличие/отсутствие можно делать на конкретный столбец и там проблем с пересчетом быть не должно.
Скачать файлы
Ссылка для скачивания рабочего файла.
Также данный файл размещен на форуме поддержки, где можно обсудить материал.