Если у вас есть таблица с данными в столбцах, которые необходимо повернуть для переупорядочивать их по строкам, используйте функцию Транспонировать. С его помощью можно быстро переключать данные из столбцов в строки и наоборот.
Например, если данные выглядят так: «Регионы продаж» в заголовках столбцов и «Кварталы» с левой стороны:
Функция Транспонировать переупомешет таблицу, в которой столбцы «Кварталы» отображаются в заголовках столбцов, а слева будут показаны регионы продаж, например:
Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.
Вот как это сделать:
-
Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.
Примечание: Убедитесь, что для этого нужно скопировать данные, так как не получится использовать команду Вырезать или CTRL+X.
-
Выберите новое место на том месте на компьютере, куда вы хотите ввести транспонную таблицу, чтобы вместить данные в достаточном месте. В новой таблице будут полностью переоформатироваться все данные и форматирование, которые уже есть.
Щелкните правой кнопкой мыши левую верхнюю ячейку, в которой нужно ввести транспонировать таблицу, и выберите транспонировать
.
-
После успешного поворота данных можно удалить исходную таблицу, и данные в новой таблице останутся без изменений.
.
Советы по транспонированию данных
-
Если данные содержат формулы, Excel автоматически обновляет их в соответствие с новым расположением. Убедитесь, что в этих формулах используются абсолютные ссылки. Если они не используются, перед поворотом данных можно переключаться между относительными, абсолютными и смешанными ссылками.
-
Если вы хотите часто поворачивать данные для их просмотра под разными углами, создайте с помощью нее с помощью перетаскиванием полей из области строк в область столбцов (или наоборот)в списке полей.
Вы можете ввести в книгу данные в качестве транспон данных. Транспонировать: переупочевание содержимого скопированные ячейки при копировании. Данные строк будут вставлены в столбцы, и наоборот.
Вот как можно транспоннять содержимое ячейки:
-
Скопируйте диапазон ячеев.
-
Вы выберите пустые ячейки, в которые вы хотите ввести транспонировать данные.
-
На вкладке Главная щелкните значок Ввести и выберите Ввести транспонировать.
Бывает, что необходимо какой-то список слов, фраз или других данных, расположенных списком в столбик преобразовать в последовательность строкой через запятую или наоборот из списка через запятую сформировать столбец, где каждая отделенная запятой фраза идет с новой строки. Мне, например, это бывает нужно при работе со списками ключевых слов в SEO-задачах или при работе с системами контекстной рекламы.
Есть, как минимум, три способа выполнить поставленную задачу:
- Преобразовать программе Notepad++
- Cтолбец в строку Excel через запятую
- Использовать Онлайн-скрипт
Содержание
- Преобразование в программе Notepad++ с помощью регулярных выражений
- Преобразование столбца в строку через запятую в Excel
- Столбик через запятую и обратно онлайн
- Список через запятую > в столбик
- Столбик > список через запятую
Преобразование в программе Notepad++ с помощью регулярных выражений
Эта программа известна практически всем, кто занимался редактированием сайтов или работал с кодом. Если у вас ее еще нет, советую скачать и поставить.
Вставляем в программу список для преобразования.
Для примера я взял набор фраз через запятую. Нажимаем комбинацию клавиш Ctrl+H (поиск с заменой), в открывшемся окне ставим метку Режим поиска — Расширенный, Найти: , (запятая и пробел), Заменить на: n Заменить все. Получаем список столбиком.
Если нужно из столбика сделать список через запятую, тогда то же самое, только Найти: rn и Заменить на: , (запятая и пробел).
Если что-то заменилось не так, как надо, делаем отмену Ctrl+Z и пробуем по-другому, например, ищем только n.
Преобразование столбца в строку через запятую в Excel
Используем формулу «лесенка».
Скачать готовый файл можно здесь: 4memo-ru-stolbik-zapyataya.xls
Запятую в самой первой ячейке можно заменить, на что-то другое, если нужно.
Формулу во втором столбце растягиваем вниз, насколько потребуется.
Если полученный список нужно вставить в другой лист эксель, то не забываем избавиться от формул, вставив предварительно данные в блокнот.
Столбик через запятую и обратно онлайн
Этот вариант подойдет для тех, кому нужно срочно, прямо на этой странице, решить задачу конвертации списка — за вас все сделает скрипт.
Список через запятую > в столбик
Разделитель-запятую можно заменить каким-нибудь другим, например, точкой с запятой, только про пробел не забудьте, если он вам нужен.
Столбик > список через запятую
Лично для меня из всех трех способов перестроения списка наиболее удобным является самый первый — через Нотпад, а вы выберите тот, который удобен вам.
Надеюсь, кому-то эта страница облегчит жизнь.
Превращение строк в столбцы и обратно
Постановка задачи
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке — пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши — Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special). В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы: не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы: в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива. Если раньше не сталкивались с формулами массивов, то советую почитать тут — это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы: между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы: не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) — выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) — преобразует текстовую строку, например, «F3» в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) — выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы: сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы: форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Do you want to rotate your data, changing your Excel columns to rows or vice versa?
Switching data from columns to rows or from rows to columns is known as transposing the data. When you convert columns into rows, the dimensions of data and the cell address will also change. Therefore, you should be careful while transposing the data. There are several ways to transpose columns to rows. We list three simple ways you can do this.
Method 1: use the transpose feature in paste drop-down menu
Let’s take the example of regional sales data in a table. Our data include row headers, column headers, and numerical data. Take a look at the images below and follow the steps below to convert the data in columns to row data and vice versa.
Step 1: Select the data set you want to transpose.
Step 2: On the Home tab, in the clipboard group, click Copy. You can also use the shortcut key Ctrl+C (Command+C on the Mac).
Step 3: On the worksheet, select the top-left cell of the destination.
Step 4: On the Home tab, in the clipboard group, click Paste and then click Transpose.
When using this method, there is no link between the source and destination tables. If you change the source data, the transposed data does not change.
Method 2: use the transpose() function to convert excel columns to rows
Another way of doing this is to use the actual Transpose() function.
Step 1: Select the destination area. In this case, as the original is five rows by four columns, this is an area four rows deep and five columns across.
Step 2: Press the F2 function key to enter into edit mode while the target area is still selected, or click in the Formula Bar.
Step 3: Write “=” (without quotes) followed by the Transpose function, and in brackets, select the range you want to transpose.
Step 5: In some versions of Excel (especially versions earlier than Excel 2019), you may need the combination of Ctrl+Shift+Enter keys to save this formula. However, in later versions of Excel, you can press either Enter or Ctrl+Shift+Enter.
When using the Transpose function, if you change the values in the source range, the values in the transposed range automatically update. A disadvantage of using the Transpose function is that this array formula may break when you make changes to the number of source rows or columns. Therefore, you need to be careful if you are handling complex data ranges.
Method 3 – using paste special
If you need more control over your transposition of columns to rows and vice versa, you can use the Paste Link option in the Paste Special drop-down menu.
In the previous method, if the source range changed, the Transpose formula could break. This can be avoided by using Paste Link. This pastes the cell references into each cell. If the values in the source range are changed, the linked cells will also change. Though this function sounds complicated, it’s fairly simple, provided you follow the steps correctly.
Step 1: Copy the data range and go to the top-left of the destination range on the same worksheet.
Step 2: Press the combination of Ctrl+Alt+V (Command+Ctrl+V on the Mac) to get the Paste Special dialogue box. Then press the Paste Link button. The result is that the cell references of the range are copied as formulas.
Step 3: Select the range you just pasted in the destination range and hold down Ctrl+H (or Command+H on the Mac) to invoke the Find and Replace dialogue box.
Step 4: In this box, enter “=” (without quotes) in the “Find what” field and “HS=” (without quotes) in the “Replace with” field, and click the Replace All button. The formulas are converted into text, as you have just replaced the “=” with “HS=.” You can use text other than “HS,” but you must convert the formulas into standard text. We are only using “HS” as a placeholder to help us move values in the spreadsheet.
Step 5: Copy this second range. Select the destination range where you want the transposed range to go. Hold down the Ctrl+Alt+V keys (Command+Ctrl+V on the Mac) to invoke the Paste Special dialogue box again. Check the Transpose option and click OK. This will convert the columns to rows.
Step 6: Now you can convert the transposed data back to formulas. Hold down Ctrl+H again (Command+H on the Mac) to invoke the Replace dialogue box. This time, enter “HS=” (without quotes) in the “Find what” field, “=” (without quotes) in the “Replace with” field, and click the Replace All button.
Now you have the live-transposed range of data without using the Transpose function. You have swapped your Excel columns to rows and vice versa.
Microsoft Excel is an important part of your toolkit, especially if you manage any kind of data. It allows you to store and analyze data easily and with great flexibility. Learning MS Excel can be easy if you put your mind to it. And, of course, our fun and comprehensive course on Excel is always available to help you along.
Would you like to learn more shortcut keys, such as the Ctrl+Alt+V combination shown in this article? If so, read our article on Excel Keyboard Shortcuts.
Frequently Asked Questions
How do I convert multiple columns to rows in Excel?
You cannot transpose entire columns, but smaller ranges of data in multiple columns can be. You need to select the data first. Highlight the first range to be copied, then hold down Ctrl (Command on the Mac) and highlight the other ranges. Copy the data. Then go to where you want the data to be pasted, and go to Home – Paste – Transpose.
What is the shortcut to convert rows to columns in Excel?
The shortcut to convert rows to columns or transpose columns to rows is to press Ctrl+Alt+V (Control+Command+V on the Mac), the letter ‘e,’ then Enter.
How do I switch rows and columns in an Excel chart?
There are three ways to transpose rows and columns in an Excel chart. The first is to go to Chart Design – Switch Row/Column. The second is to right-click in some empty space in the chart, click on “Select Data,” and click on “Switch Row/Column.” Thirdly, you can also go to Chart Design – Select Data – Switch Row/Column.