Автор: Дмитрий
Опубликована: 28.11.2011
Популярность= 101 ( +93 / -8 )
 |
Простой способ отзеркалировать по диагонали таблицу в Excel 2003 и Excel 2007. |
Транспонирование таблицы – это замена местами строк и столбцов. Данные в таблице отображаются в обратном порядке, как бы «зеркалируются». Вручную данная операция представляет собой долгий и рутинный процесс, ведь сама таблица может быть достаточно больших размеров, но ее можно выполнить автоматически за несколько секунд в программе Microsoft Excel.
Алгоритм действий транспонирования таблицы в Excel 2003 и Excel 2007 ничем не отличается. Пусть имеется некоторая таблица с количеством товара в разных точках компании по месяцам.
-
Выделяем таблицу полностью вместе с подписями строк и столбцов. На выделенном диапазоне нажмите правой клавишей мыши и в появившемся меню выберите пункт «Копировать».
Установите курсор на пустую ячейку там, где будет левый верхний угол итоговой таблицы, нажмите правой клавишей мыши в контекстном меню выберите пункт «Специальная вставка…».
Откроется окно дополнительных параметров вставки данных из буфера обмена. В самом низу отметьте галочкой параметр «Транспонировать» и нажмите кнопку «ОК».
В результате Вы получите транспонированную таблицу в Microsoft 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.
Скачать пример можно здесь.
Т. Как сделать транспонирование в Экселе? Работая с редактором таблиц Microsoft Office Excel, вам наверное не однократно приходилось создавать формулы и иногда выло совсем не удобно в формуле задавать ряд значений, который находится в строке. Вы наверное думали, что было бы на много удобнее, будь это не строка, а столбец, и приходилось в ручную переносить значение каждой ячейки. Наверное существовали и обратные ситуации, когда данные в столбце нужно было представить рядком. При особо больших количествах значений, вручную это сделать очень сложно, тем более чтобы не допустить ни единой ошибки.
В этой статье, я покажу вам методы, используя которые вы сможете перевести рядки таблиц в столбцы и наоборот.
Всего существует 3 метода для транспонирования таблицы в Excel. Давайте рассмотрим их поподробнее:
1. С применением специальной сводной таблицы.
Этот способ не очень популярен, потому что требует много лишних движений, но у него есть и преимущество над другими в виде возможности фильтрации данных.
Итак, чтобы начать, вам потребуется обычная таблица:
Итак, чтобы создать сводную таблицу вам необходимо выделить данные вашей таблицы и перейти на вкладку "Вставка", после чего в инструментах найдите кнопку "Сводная таблица". После чего Excel выдаст окно в котором вам нужно просто нажать кнопку "ОК", так как все данные мы уже выбрали:
Теперь на новом листе у вас будет находиться сводная таблица. Используя инструкции, в правой части Excel отметьте галками нужные поля данных:
Поигравшись с настройками в блоке "Название строк" и "Название столбцов" вы можете добиться результата транспонирования:
2. При помощи специальной вставки.
Вернемся к нашему первоначальному примеру таблицы. Для использования этого способа, вам потребуется скопировать всю таблицу в буфер обмена. Выделите таблицу целиком и нажмите Ctrl+C или правой кнопкой мыши и пункт "Копировать". Когда таблица скопирована, выделите ячейку куда нужно вставить транспонированную таблицу и на вкладке "Главная" найдите пункт "Вставить", кликните на него и выберите "Транспонировать":
3. Используя специальную функцию =ТРАНСП().
Использование этого метода, сохраняет связь новых данных с исходными, к примеру если вы использовали другие формулы для вычисления, в транспонированной таблице они сохранятся.
Для использования функции, вам необходимо выделить столько ячеек, сколько их в таблице, которую вы хотите транспонировать. В примере у нас была таблица 2х10, то есть 2 столбца и 10 рядков. После транспонирования она будет размером 10х2, так что нам нужно выделить ячейки 10х2:
Теперь, вам нужно перейти к строке данных ячейки, не снимайте выделение ячеек, просто кликните в этой строке:
В этой строке мы запишем нашу функцию ТРАНСП(), но перед ней, нужно ввести знак равенства, чтобы Excel понял, что данные в этих ячейках будут вычисляться. В строку функций, в которой мы теперь находимся, вам необходимо ввести следующее:
В аргументах функции, то есть в круглых скобках, мы задает расположение нашей таблицы. У меня она начинается с элемента А1 и заканчивается элементом В2. Таким образом мы указываем массив значений. После записи этой функции, нажмите сочетание клавиш Ctrl+Shift+Enter, чтобы применить нашу функцию. Вот такой вот результат мы получим:
