Время от времени пользователю Excel может быть поручено преобразовать диапазон данных, имеющих горизонтальную структуру, в вертикальную. Этот процесс называется транспонированием. Это слово является новым для большинства людей, потому что при нормальной работе с ПК нет необходимости использовать эту операцию. Однако любой, кому приходится работать с большими объемами данных, должен знать, как это делать. Сегодня мы более подробно поговорим о том, как это сделать, с помощью какой функции, а также более подробно рассмотрим некоторые другие методы.
Одним из наиболее интересных и функциональных методов транспонирования таблиц в Excel является функция ТРАНСПОРТИРОВКА. С его помощью можно превратить горизонтальный диапазон данных в вертикальный или выполнить обратную операцию. Давайте узнаем, как с этим работать.
Синтаксис этой функции невероятно прост: TRANSPOSE (массив). То есть нам нужно использовать только один аргумент, который представляет собой набор данных, который необходимо преобразовать в горизонтальный или вертикальный вид, в зависимости от того, каким он был изначально.
Предположим, у нас есть столбец с диапазоном B2: B6. Они могут содержать как готовые значения, так и формулы, возвращающие результат в эти ячейки. Для нас это не так важно, транспозиция возможна в обоих случаях. После использования этой функции длина строки будет равна длине столбца исходного диапазона.
Последовательность шагов для использования этой формулы следующая:
Конечно, в вашем случае вам нужно указать типичный диапазон вашей таблицы.
В принципе, механизм действия практически такой же, как и в предыдущем пункте. Предположим, у нас есть строка с начальной и конечной координатами B10: F10. Он также может содержать как прямые значения, так и формулы. Сделайте из него столбец, который будет по размеру аналогичен исходной строке. Последовательность действий следующая:
Другой возможный вариант транспонирования — использовать функцию «Специальная вставка». Это больше не оператор, который будет использоваться в формулах, но он также является одним из самых популярных методов преобразования столбцов в строки и наоборот.
Эта опция находится на вкладке «Главная». Чтобы получить к нему доступ, вам нужно найти группу «Буфер обмена» и найти там кнопку «Вставить». Затем откройте меню, расположенное под этой опцией, и выберите пункт «Транспонировать». Перед этим вам нужно выбрать диапазон, который вы хотите выбрать. В результате мы получаем тот же диапазон, прямо противоположный зеркалу.
Но на самом деле существует гораздо больше способов превратить столбцы в строки и наоборот. Мы описываем 3 метода, с помощью которых мы можем транспонировать таблицу в Excel. Мы рассмотрели два из них выше, но мы приведем еще несколько примеров, чтобы вы получили более четкое представление о том, как выполнять эту процедуру.
Этот способ самый простой. Просто нажмите пару кнопок, и пользователь получит транспонированную версию таблицы. Для наглядности возьмем небольшой пример. Допустим, у нас есть таблица, содержащая информацию о том, сколько предметов в настоящее время доступно и сколько они стоят в целом. Сама таблица выглядит так.
Видим, что у нас есть заголовок и столбец с номерами товаров. В нашем примере заголовок содержит информацию о том, какой продукт, сколько он стоит, сколько доступно и какова общая стоимость всех доступных товаров, связанных с этим элементом. Мы получаем стоимость по формуле, где стоимость умножается на количество. Чтобы сделать этот пример более понятным, сделаем заголовок зеленым.
Наша задача — сделать так, чтобы информация, содержащаяся в таблице, лежала горизонтально. То есть, чтобы столбцы стали строками. Последовательность действий в нашем случае будет следующая:
После выполнения этих шагов вам необходимо нажать кнопку «Транспонировать». Скорее установите флажок рядом с этим элементом. Остальные настройки мы не меняем, поэтому нажимаем кнопку «ОК».
После выполнения этих действий мы остаемся с той же таблицей, только ее строки и столбцы расположены по-разному. Также обратите внимание, что ячейки, содержащие ту же информацию, выделены зеленым цветом. Вопрос: Что случилось с формулами, которые находились в исходном диапазоне? Их позиция изменилась, но сами они остались. Адреса ячеек просто поменяли на те, которые сформировались после транспонирования.
Более или менее аналогичные действия должны быть предприняты для транспонирования значений, а не формул. В этом случае вы также должны использовать меню «Специальная вставка», но сначала выберите диапазон данных, содержащий значения. Мы видим, что специальное окно вставки можно вызвать двумя способами: через специальное меню на ленте или через контекстное меню.
На самом деле этот метод уже не так активно используется, как в начале этой программы для работы с электронными таблицами. Это связано с тем, что этот метод намного сложнее, чем использование специальных паст. Однако он находит свое применение для автоматизации транспонирования таблиц.
Кроме того, эта функция есть в Excel, поэтому вам обязательно нужно знать ее, даже если она почти не используется. Ранее мы рассмотрели порядок работы с ним. Теперь мы дополним эти знания еще одним примером.
После ввода данных нажимаем клавишу Enter, после чего получаем следующий результат.
Мы видим, что формула не перенесена в новую таблицу. Форматирование тоже пропало. Здесь потому что
все это придется делать вручную. Также помните, что эта таблица связана с исходной. Следовательно, как только некоторая информация изменяется в исходном диапазоне, эти изменения автоматически вносятся в транспонированную таблицу.
Поэтому этот способ подходит в тех случаях, когда необходимо обеспечить привязку транспонированной таблицы к исходной. Если вы воспользуетесь специальной вставкой, такой возможности больше не будет.
Это принципиально новый метод, позволяющий не только транспонировать таблицу, но и выполнять огромное количество действий. Правда, механизм транспозиции будет немного отличаться от предыдущих способов. Последовательность действий следующая:
Правда, при использовании сводных таблиц следует учитывать следующие ограничения:
Кроме того, после создания сводной таблицы у пользователя нет возможности добавлять новую информацию.
