2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Транспонированием таблицы в Excel называется замена столбцов строками и наоборот. Иными словами – это поворот в двух плоскостях: горизонтальной и вертикальной. Транспонировать таблицы можно тремя способами.

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

Самый простой и универсальный путь. Рассмотрим сразу на примере. Имеем таблицу с ценой некоего товара за штуку и определенным его количеством. Шапка таблицы расположена горизонтально, а данные расположены вертикально соответственно. Стоимость рассчитана по формуле: цена*количество. Для наглядности примера подсветим шапку таблицы зеленым цветом.

Вертикальная таблица.

Нам нужно расположить данные таблицы горизонтально относительно вертикального расположения ее шапки.

Чтобы транспонировать таблицу, будем использовать команду СПЕЦИАЛЬНАЯ ВСТАВКА. Действуем по шагам:

  1. Выделяем всю таблицу и копируем ее (CTRL+C).
  2. Ставим курсор в любом месте листа Excel и правой кнопкой вызываем меню.
  3. Кликаем по команде СПЕЦИАЛЬНАЯ ВСТАВКА.
  4. В появившемся окне ставим галочку возле пункта ТРАНСПОНИРОВАТЬ. Остальное оставляем как есть и жмем ОК.

В результате получили ту же таблицу, но с другим расположением строк и столбцов. Причем, заметим, что зеленым подсвечены ячейки с тем же содержанием. Формула стоимости тоже скопировалась и посчитала произведение цены и количества, но уже с учетом других ячеек. Теперь шапка таблицы расположена вертикально (что хорошо видно благодаря зеленому цвету шапки), а данные соответственно расположились горизонтально.

Пример.

Аналогично можно транспонировать только значения, без наименований строк и столбцов. Для этого нужно выделить только массив со значениями и проделать те же действия с командой СПЕЦИАЛЬНАЯ ВСТАВКА.

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

С появлением СПЕЦИАЛЬНОЙ ВСТАВКИ транспонирование таблицы при помощи команды ТРАНСП почти не используется по причине сложности и большего времени на операцию. Но функция ТРАНСП все же присутствует в Excel, поэтому научимся ею пользоваться.

Снова действует по этапам:

  1. Правильно выделяем диапазон для транспонирования таблицы. В данном примере исходной таблицы имеется 4 столбца и 6 строк. Соответственно мы должны выделить диапазон ячеек в котором будет 6 столбцов и 4 строки. Как показано на рисунке: Диапазон.
  2. Сразу заполняем активную ячейку так чтобы не снять выделенную область. Вводим следующую формулу:=ТРАНСП(A1:D6)
  3. Нажимаем CTRL+SHIFT+ENTER. Внимание! Функия ТРАНСП()работает тилько в массиве. Поэтому после ее ввода нужно обязательно нажать комбинацию горячих клавиш CTRL+SHIFT+ENTER для выполнения функции в массиве, а не просто ENTER.

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

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

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

  1. Создадим сводную таблицу. Для этого выделим исходную таблицу и откроем пункт ВСТАВКА – СВОДНАЯ ТАБЛИЦА. СВОДНАЯ ТАБЛИЦА.
  2. Местом, где будет создана сводная таблица, выбираем новый лист.
  3. В получившемся макете сводных таблиц можем выбрать необходимые пункты и перенести их в нужные поля. Перенесем «продукт» в НАЗВАНИЯ СТОЛБЦОВ, а «цена за шт» в ЗНАЧЕНИЯ. Пример1.
  4. Получили сводную таблицу по нужному нам полю. Также команда автоматически подсчитала общий итог.
  5. Можно убрать галочку у ЦЕНА ЗА ШТ и поставить галочку рядом с ОБЩАЯ СТОИМОСТЬ. И тогда получим сводную таблицу по стоимости товаров, а также опять общий итог.
Читайте так же:
Как вызвать консоль в Google Chrome

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

Как строить таблицы в Excel

Работа в Microsoft Excel во многом состоит из построения, оформления и вычисление таблиц.

Практически каждый, кто открывает Excel сталкивается с задачами создания, оформления, преобразования, сортировки и печати таблиц.

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

Как нарисовать таблицу в эксель

Воспользоваться сеткой.

Применить все границы.

Если в Вашей таблице уже заполнены данные, то можно сделать просто обрамление таблицы excel границами.

Для этого выделите диапазон Вашей таблицы, потом зайдите на вкладке «Главная» в «Границы» и там выберете «Все границы»:

Как отформатировать таблицу.

Пожалуй, самый эффектный способ оформить таблицу в excel – это сделать форматирование таблицы.

Для этого нужно поместить курсор в любое место Вашей таблицы и на вкладке «Главная» выбрать «Форматировать как таблицу», далее применить любую из готовых цветовых схем:

Как продолжить таблицу в эксель вниз.

Как растянуть таблицу в excel.

Как отменить умную таблицу в excel.

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

Для этого на любом месте сводной таблицы нужно будет нажать правой клавишей мыши, зайти в раздет «Таблица», а там выбрать «Преобразовать в диапазон».

Как перевернуть таблицу в excel.

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

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

Для этого нужно выделить ячейку в которую нужно перевернуть таблицу горизонтально, нажать на «Правую клавишу мыши», выбрать «Специальная вставка», в правом нижнем углу диалогового окна специальной вставки отметить флажок «Транспонировать», нажать «Ok».

Таким образом в пару кликов можно развернуть таблицу в excel на 90 градусов.

Как сортировать таблицу в excel.

Чтобы отсортировать таблицу в excel по возрастанию достаточно поместить курсор внутрь столбца по которому требуется упорядочить таблицу в excel, и на вкладке «Данные» в разделе «Сортировка и фильтры» выбрать порядок сортировки:

3 способа транспонирования данных в Excel

пример транспонирования +в excelЧасто в работе у нас возникает потребность перевернуть данные — из строчного представления сделать данные столбцом, из столбца перевернуть данные в строку. Поворот данных из столбцов в строки или наоборот называется транспонированием.

Транспонировать данные в Excel можно с помощью:

  • «Специальной» вставки с галочкой «Транспонировать»;
  • Функции Excel =трансп();
  • и с помощью сводной таблицы.
Читайте так же:
Как установить драйверы на материнскую плату
Рассмотрим данные способы транспонирования на примерах.

У нас есть таблица с данными, и мы хотим из табличного представления данных сделать строчное — для этого транспонируем данные.

транспонирование пример

1-ый способ транспонирования — специальная вставка

Транспонируем данные с помощью специальной вставки с галочкой «транспонировать».
Для этого выделяем необходимые данные и копируем их с помощью кнопки «копировать» или с помощью сочетания клавиш ctrl+c.

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

транспонированиеtransponirovanie v excel

После нажатия откроется диалоговое окно, в котором ставим галочку «транспонировать» и нажимаем ОК

транспонирование в excel

Данные переворачиваются, и мы получаем нужное нам строчное представление:

транспонирование пример в excel

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

2-ой способ транспонирования функция Excel =ТРАНСП()

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

операция транспонирования

Итак, как с помощью формулы =ТРАНСП перевернуть данные?

1. Вводим формулу (как в картинке выше) и передаем в неё ссылку на весь диапазон, который хотим перевернуть:

данные для транспонирования

2. Выделяем диапазон в листе с формулой =ТРАНСП, равный массиву исходных данных (т.е равное количество строк и столбцов в перевернутом виде).
Чтобы легче было выделить массив нужного размера, рекомендую перевести стиль ссылок в вид R1C1, чтобы столбцы и строки стали номерами. Это делается в параметрах Excel -> в разделе «Формулы» -> поставить галочку «Стиль ссылок R1C1».

пример транспонирования в excel

транспонирование пример

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

транспонирование матрицы

Теперь дату преобразуем в формат «Дата», а продажи в «руб». В итоге получаем диапазон, аналогичный исходным данным, только с перевернутыми значениями и, что важно, связанный с исходными данными. Если мы будем изменять исходные данные, то автоматически они будут меняться на листе с транспонированными данными.

Аналогичным образом диапазон переворачивается и в обратную сторону.

3-ий способ транспонирования — сводная таблица

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

Сначала необходимо сделать сводную таблицу. Выделяем исходные данные:

транспонирование сводная таблица

Заходим в меню «Вставка» и выбираем пункт «Сводная таблица», и в диалоговом окне сводной нажимаем «ОК»:

транспонирование в excel

В новом листе создается сводная таблица:

сводная таблица и транспонирование

В названия столбцов перетаскиваем поля «дата» и «товар», в значения перетаскиваем «объем продаж».

В меню «Конструктор» отключаем общие и промежуточные итоги. Меню «Конструктор» появляется в Excel при постановке курсора на сводную таблицу.

Читайте так же:
Что почитать на карантине: бесплатные книги от «ЛитРес»

транспонирование сводной в Excel

И получаем перевернутые данные:

транспонирование пример в excel

Для удобства поле «Товары» мы можем перенести в область строк, и получить более рабочий вид кросс таблицы:

сводная транспонирование

Мы рассмотрели 3 способа транспонирования данных.

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

Воспользовавшись Forecast4AC PRO в листе «3-й способ», мы построили график, на который выведена «Модель прогноза», «Исходные данные», «Границы прогноза», и «Тренд».

Точных Вам прогнозов и хорошего настроения!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Как транспонировать данные Excel

Узнайте, как переворачивать ячейки и переключать строки в столбцы или столбцы в строки

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

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

Эти инструкции применяются к Microsoft Excel 2010, 1013, 2016, 2019 и Excel для Office 365.

Как перевернуть ячейки столбца в Excel

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

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

Нажмите правой кнопкой мыши столбец A и выберите Вставить , чтобы добавить новый столбец слева от столбца продавца, который вы хотите отсортировать.

Введите 1 в первой ячейке (строка 2). Введите 2 во вторую ячейку (строка 3).

Удерживая нажатой клавишу Cntrl , поместите угол мыши в нижний правый угол ячейки с цифрой 2. Курсор изменится на две горизонтальные линии. Дважды щелкните левой кнопкой мыши, чтобы автоматически заполнить оставшуюся часть столбца до имени последнего продавца. Это автоматически заполнит оставшуюся часть столбца числами от 3 до 8.

Выделите всю таблицу.

В меню Главная выберите Сортировать и фильтровать на ленте. Выберите Пользовательская сортировка .

Установите для Сортировать по значение Столбец А , для Сортировать по значение Значения ячеек и Порядок . От самого большого до самого маленького . Нажмите ОК .

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

Теперь вы можете щелкнуть правой кнопкой мыши столбец A, чтобы выбрать его, щелкнуть правой кнопкой мыши столбец A и выбрать Удалить, чтобы удалить пронумерованный столбец.

Читайте так же:
Офисные программы для Андроид

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

Как поменять ячейки строк в Excel

Что делать, если вы хотите видеть данные о продажах с декабря по январь, а не с января по декабрь?

На самом деле вы можете использовать тот же подход, что и выше, для переворачивания столбцов с небольшим изменением.

Сначала добавьте новую строку прямо под заголовком и пронумеруйте эти ячейки от 1 (до января) до 12 (до декабря).

Теперь выполните те же шаги для сортировки таблицы, но на этот раз вы будете сортировать по строке, а не по столбцу.

Выделите только столбцы от A до M.

В меню Главная выберите Сортировать и фильтровать на ленте. Выберите Пользовательская сортировка .

Нажмите Параметры и выберите Сортировать слева направо .

Установите для Сортировать по значение Строка 2 , для Сортировать по значение Значения ячеек и Порядок . От самого большого до самого маленького . Нажмите ОК .

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

Теперь щелкните левой кнопкой мыши на строке 2 и удалите пронумерованную строку.

Поменяйте местами только две колонки или строки

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

В этом примере электронной таблицы вы можете поменять строку Джули Мур на строку Джона Хадсона всего несколькими щелчками мыши.

Вот как вы это делаете:

Выделите весь ряд с Джули Мур в столбце А.

Удерживая нажатой клавишу Shift , переместите курсор мыши к верхнему краю ячейки Джули Мур. Курсор мыши изменится на перекрестие.

Удерживая нажатой клавишу Shift , перетащите мышь к верхнему краю ячейки Джона Хадсона, пока линия чуть выше этой строки не будет выделена темной линией.

Когда вы отпустите левую кнопку мыши, две строки поменяются местами.

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

Единственное отличие состоит в том, что вы перетаскиваете курсор мыши в сторону, пока строка после столбца, которую вы хотите поменять местами с темными линиями.

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

Как поменять местами столбцы и строки

Что если вы хотите поменять местами весь столбец заголовка со всем столбцом продавца, при этом сохраняя целостность данных в электронной таблице?

Большинство людей предпочитают делать это вручную, даже не подозревая, что в Excel есть встроенная функция «transpose», которая сделает это за вас.

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

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

Читайте так же:
Как обновить плагины для Яндекс.Браузера

Нажмите на новый лист, который вы создаете. Щелкните правой кнопкой мыши вызов A1 и выберите параметр Транспонировать в разделе Параметры вставки .

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

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

Используйте VBA, чтобы поменять строки или столбцы (Дополнительно)

VBA – это мощная функция в Excel. На самом деле вы можете выполнить каждую из перечисленных выше задач с помощью очень простого кода VBA.

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

Перевернуть столбцы или строки

Чтобы реорганизовать всю строку слева направо или столбец сверху вниз, вы можете создать для этого функцию Flip_Columns () или Flip_Rows ().

Чтобы создать этот код, выберите меню Разработчик и выберите Просмотреть код .

Если Разработчик не указан в меню, вы можете добавить его. Нажмите Файл , нажмите Параметры и выберите Настроить ленту . В этом окне найдите Разработчик на левой панели и добавьте его справа. Нажмите ОК , и появится пункт меню «Разработчик».

Чтобы перевернуть строки выбранного столбца на вашем листе, вы можете использовать следующий код VBA:

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

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

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

Поменяйте местами две колонки или строки

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

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

Транспонировать весь диапазон

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

Для этого даже есть метод в VBA:

Однако сделать это в VBA – гораздо больше работы, чем стоит, поскольку, как вы видели ранее в этой статье, транспонирование всего диапазона – это не более, чем копирование и вставка этого диапазона всего несколькими щелчками мыши.

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector