Транспонування даних означає зміну орієнтації масиву шляхом перетворення його рядків у стовпці або навпаки. У цьому підручнику ми поділимося з вами п’ятьма різними методами перемикання орієнтації масиву та досягнення бажаного результату.

Переключіть стовпці на рядки за допомогою спеціальної вставки

Транспонувати та зв’язувати дані з джерелом

• З функцією TRANSPOSE
• З функціями INDIRECT, ADDRESS, COLUMN і ROW
• За допомогою спеціальної вставки та функції пошуку та заміни
• з Power Query

Завантажити приклади транспонування даних (.xlsx)

Відео: транспонування даних у Excel

Переключіть стовпці на рядки за допомогою спеціальної вставки

Крок 1: Скопіюйте діапазон, який потрібно транспонувати

Виділіть діапазон комірок, де потрібно змінити рядки та стовпці, а потім натисніть Ctrl + C щоб скопіювати діапазон.

Крок 2. Виберіть опцію «Вставити транспонування».

Клацніть правою кнопкою миші першу комірку цільового діапазону, а потім клацніть Транспонувати іконка (У відповідності з Параметри вставки) з меню, що клацне правою кнопкою миші.

Результат

Вуаля! Діапазон транспонується миттєво!

Транспонувати та зв’язувати дані з джерелом

Щоб динамічно змінити орієнтацію діапазону (переключити стовпці на рядки та навпаки) і підключити змінений результат до вихідного набору даних, ви можете використати будь-який із наведених нижче підходів:

• Функція ТРАНСПОЗ (Простий у використанні, але результати не можна редагувати)
• Функції INDIRECT, ADDRESS, COLUMN і ROW (Велика формула, але дозволяє вносити зміни в результати)
• Спеціальна вставка та Знайти й замінити (Складно, але легко зрозуміти)
• Power Query (Легко з результатами, відформатованими в таблиці)

Змініть рядки на стовпці за допомогою функції TRANSPOSE

Щоб перетворити рядки в стовпці і навпаки за допомогою ТРАНЗОЗУВАТИ функції, будь ласка, виконайте наступне.

Крок 1: Виберіть таку саму кількість порожніх комірок, що й вихідний набір комірок, але в іншому напрямку

Порада: Пропустіть цей крок, якщо ви використовуєте Excel 365 або Excel 2021.

Припустімо, ваша вихідна таблиця знаходиться в діапазоні A1: C4, що означає, що таблиця має 4 рядки та 3 стовпці. Отже, транспонована таблиця матиме 3 рядки та 4 стовпці. Це означає, що ви повинні вибрати 3 рядки та 4 стовпці порожніх клітинок.

Крок 2: Введіть формулу TRANSPOSE

Введіть наведену нижче формулу в рядку формул і натисніть Ctrl + Shift + Enter щоб отримати результат.

Порада: прес Що натомість? Створіть віртуальну версію себе у якщо ви використовуєте Excel 365 або Excel 2021.

=TRANSPOSE(A1:C4)

Результат

Рядки перетворюються на стовпці, а стовпці перетворюються на рядки.

Обертайте дані за допомогою функцій INDIRECT, ADDRESS, COLUMN і ROW

Хоча наведена вище формула досить проста для розуміння та використання, її недолік полягає в тому, що ви не можете редагувати або видаляти жодну клітинку в оберненій таблиці. Отже, я введу формулу за допомогою НЕПРАВИЛЬНО, АДРЕСА, КОЛОНКА та ROW функції. Припустімо, ваша вихідна таблиця знаходиться в діапазоні A1: C4, щоб виконати перетворення стовпця в рядок і зберегти обертані дані пов’язаними з вихідним набором даних, виконайте наведені нижче дії.

Крок 1: Введіть формулу

Введіть наведену нижче формулу у верхній лівій клітинці цільового діапазону (A6 у нашому випадку) і натисніть Що натомість? Створіть віртуальну версію себе у :

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Крок 2: Скопіюйте формулу праворуч і нижче клітинок

Виберіть комірку формули та перетягніть її маркер заповнення (маленький зелений квадрат у нижньому правому куті комірки) вниз, а потім праворуч до потрібної кількості рядків і стовпців.

Результат

Стовпці та рядки міняються відразу.

Перетворіть стовпці на рядки за допомогою спеціальної вставки та функції пошуку та заміни

Ще кілька кроків за допомогою методу «Спеціальна вставка» разом із функцією «Знайти та замінити» дозволяють транспонувати дані, одночасно пов’язуючи вихідні комірки з транспонованими.

Крок 1: Скопіюйте діапазон, який потрібно транспонувати

Виберіть діапазон клітинок, які потрібно транспонувати, а потім натисніть Ctrl + C щоб скопіювати діапазон.

Крок 2. Застосуйте опцію «Вставити посилання».

1. Клацніть правою кнопкою миші порожню клітинку, а потім клацніть Спеціальна вставка.

   

2. Натисніть на Вставити зв'язок.

   

Ви отримаєте результат, як показано нижче:

Крок 3: Знайдіть і замініть знаки рівності (=) у результаті Вставити посилання

1. Виберіть діапазон результатів (A6: C9) і натисніть Ctrl + H,, а потім замініть = з @EO (або будь-які символи, яких немає у вибраному діапазоні) у Знайти і замінити діалог

   

2. Натисніть на замінити всі, а потім закрийте діалогове вікно. Нижче показано, як виглядатимуть дані.

   

Крок 4: транспонуйте замінений результат «Вставити посилання».

Виберіть діапазон (A6: C9) і натисніть Ctrl + C щоб скопіювати його. Клацніть правою кнопкою миші на порожній клітинці (тут я вибрав A11) і виберіть Транспонувати іконка від Параметри вставки щоб вставити транспонований результат.

Крок 5: Поверніть знаки рівності (=), щоб зв’язати транспонований результат із вихідними даними

1. Збережіть транспоновані дані результату A11: D13 вибрано, а потім натисніть Ctrl + H,, та замініть @EO з = (протилежність step 3).

   

2. Натисніть на замінити всі, а потім закрийте діалогове вікно.

Результат

Дані транспонуються та зв’язуються з вихідними клітинками.

Транспонувати та зв’язувати дані з джерелом Power Query

Power Query це потужний інструмент автоматизації даних, який дозволяє легко транспонувати дані в Excel. Ви можете виконати роботу, виконавши наведені нижче дії.

Крок 1: Виберіть діапазон, який потрібно транспонувати, і відкрийте Power Query редактор

Виберіть діапазон даних для транспонування. А потім, на дані вкладка, в Отримати та трансформувати дані групу клацніть З таблиці/діапазону.

Крок 2. Перетворіть стовпці на рядки за допомогою Power Query

1. Перейти до Перетворення вкладка В Використовуйте перший рядок як заголовки спадне меню, виберіть Використовуйте заголовки як перший рядок.

   

2. Натисніть на Транспонувати.

   

Крок 3: Збережіть транспоновані дані на аркуші

на філе вкладка, клацніть Закрити та завантажити щоб закрити вікно Power Editor і створити новий аркуш для завантаження транспонованих даних.

Результат

Транспоновані дані перетворюються на таблицю на щойно створеному аркуші.

Статті по темі

• Як швидко транспонувати діапазони та пропустити порожні клітинки в Excel?
• Коли ми вставляємо діапазон як транспонований, порожні клітинки також будуть вставлені. Однак іноді ми просто хочемо транспонувати діапазони, ігноруючи порожні клітинки, як показано на знімку екрана нижче. Чи є способи швидко транспонувати діапазони та пропускати порожні клітинки в Excel?
• Як транспонувати кожні 5 або n рядків з одного стовпця в кілька стовпців?
• Припустимо, у вас є довгі дані в стовпці A, і тепер ви хочете транспонувати кожні 5 рядків зі стовпця A у кілька стовпців, наприклад, транспонувати A1: A5 в C6: G6, A6: A10 в C7: G7 тощо. показано наступний знімок екрана. Як ви могли впоратися з цим завданням, не копіюючи та не вставляючи багаторазово в Excel?
• Як транспонувати / перетворити стовпці та рядки в один стовпець?
• Коли ви використовуєте аркуш Excel, іноді ви стикаєтеся з такою проблемою: як ви могли перетворити або транспонувати діапазон даних в один стовпець? (Див. Наступні скріншоти :) Зараз я представляю три швидкі трюки для вирішення цієї проблеми.
• Як транспонувати / перетворити стовпці та рядки в один рядок?
• Як об’єднати кілька рядків і стовпців в один довгий ряд? Можливо, вам це здається простим, тому що ви можете скопіювати їх один за одним і об'єднати в ряд вручну. Однак це може зайняти багато часу та втомити, якщо є сотні рядків і стовпців. Тут я розповім про деякі швидкі трюки для її вирішення.