✅ Як швидко транспонувати дані в Excel (Покроковий посібник)

Зміст

Транспонування даних за допомогою спеціальної вставки
Транспонувати дані за допомогою спеціальної вставки та Знайти та замінити
Транспонування даних за допомогою функції TRANSPOSE Excel
Транспонування даних за допомогою Power Query

Переглянути відео - Як транспонувати дані в Excel

Якщо у вас є набір даних і ви хочете транспонувати його в Excel (що означає перетворення рядків у стовпці, а стовпці у рядки), робити це вручну - це повне НІ!

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

Спеціальна вставка може зробити багато дивовижних речей, і однією з таких є транспонування даних у Excel.

Припустимо, у вас є набір даних, як показано нижче:

Ці дані містять регіони у стовпці та чверті поспіль.

Тепер з якоїсь причини, якщо вам потрібно транспонувати ці дані, ось як це можна зробити за допомогою спеціальної вставки:

Виберіть набір даних (у цьому випадку A1: E5).
Скопіюйте набір даних (Control + C) або клацніть правою кнопкою миші та виберіть копіювати.
Тепер ви можете вставити транспоновані дані в нове місце. У цьому прикладі я хочу скопіювати в G1: K5, тому клацніть правою кнопкою миші клітинку G1 і виберіть особливу вставку.
У спеціальному діалоговому вікні "Вставити" перевірте опцію транспонування внизу праворуч.
Натисніть OK.

Це дозволить миттєво скопіювати та вставити дані, але таким чином, щоб вони були транспоновані. Нижче наведено демо -версію, яка показує весь процес.

Наведені вище кроки копіюють значення, формулу (якщо є), а також формат. Якщо ви хочете лише скопіювати значення, виберіть "значення" у спеціальному діалоговому вікні "Вставити".

Зауважте, що скопійовані дані є статичними, і якщо ви внесете будь -які зміни до вихідного набору даних, ці зміни не відобразяться у транспонованих даних.

Якщо ви хочете, щоб ці транспоновані клітинки були пов’язані з вихідними клітинками, ви можете об’єднати можливості Find & Replace із Special Paste.

Транспонувати дані за допомогою спеціальної вставки та Знайти та замінити

Використання лише спеціальної вставки дає статичні дані. Це означає, що якщо ваші вихідні дані змінюються, і ви хочете оновити транспоновані дані, вам потрібно знову використати спеціальну вставку, щоб транспонувати їх.

Ось класна хитрість, яку можна використати для транспонування даних і збереження їх зв’язку з вихідними клітинками.

Припустимо, у вас є набір даних, як показано нижче:

Ось кроки для транспонування даних, але збереження посилань недоторканими:

Виберіть набір даних (A1: E5).
Скопіюйте його (Control + C або клацніть правою кнопкою миші та виберіть копіювати).
Тепер ви можете вставити транспоновані дані в нове місце. У цьому прикладі я хочу скопіювати в G1: K5, тому клацніть правою кнопкою миші клітинку G1 і виберіть особливу вставку.
У діалоговому вікні Спеціальна вставка натисніть кнопку Вставити посилання. Це дасть вам той самий набір даних, але тут клітинки пов'язані з вихідним набором даних (наприклад, G1 пов'язаний з A1, а G2 з A2 тощо).
Вибравши ці нові скопійовані дані, натисніть Control + H (або перейдіть до Домашня сторінка -> Редагування -> Знайти та вибрати -> Замінити). Відкриється діалогове вікно «Знайти та замінити».
У діалоговому вікні «Знайти та замінити» скористайтеся наступним:
- В Знайти що: =
- В Замінити на:!@# (Зверніть увагу, що я використовую!@#, Оскільки це унікальна комбінація символів, яка навряд чи буде частиною ваших даних. Ви можете використовувати будь -який такий унікальний набір символів).
Натисніть Замінити все. Це замінить прирівняне до з формули, і ви отримаєте!@#, А потім посилання на клітинку в кожній клітинці.
Клацніть правою кнопкою миші та скопіюйте набір даних (або скористайтесь Control + C).
Виберіть нове місце розташування, клацніть правою кнопкою миші та виберіть особливу вставку. У цьому прикладі я вставляю його в комірку G7. Ви можете вставити його куди завгодно.
У діалоговому вікні Спеціальна вставка виберіть Транспонування та натисніть OK.
Скопіюйте вставку цих новостворених транспонованих даних до тієї, з якої вони створені.
Тепер знову відкрийте діалогове вікно Знайти та замінити та замініть!@# На =.

Це дасть вам пов'язані дані, які були транспоновані. Якщо ви внесете будь -які зміни до вихідного набору даних, транспоновані дані автоматично оновляться.

Примітка: Оскільки в наших вихідних даних A1 порожнє, вам потрібно буде вручну видалити 0 у G1. 0 відображається, коли ми вставляємо посилання, оскільки посилання на порожню клітинку все одно повертатиме 0. Також вам потрібно буде відформатувати новий набір даних (ви можете просто скопіювати вставити формат лише з вихідного набору даних).

Транспонування даних за допомогою функції TRANSPOSE Excel

Функцію Excel TRANSPOSE - як випливає з назви - можна використовувати для транспонування даних у Excel.

Припустимо, у вас є набір даних, як показано нижче:

Нижче наведено кроки для його транспонування.

Виберіть клітинки, куди потрібно транспонувати набір даних. Зауважте, що вам потрібно вибрати точну кількість клітинок як вихідні дані. Так, наприклад, якщо у вас є 2 рядки та 3 стовпці, вам потрібно вибрати 3 рядки та 2 стовпці клітинок, де ви хочете транспонувати дані. У цьому випадку, оскільки існує 5 рядків і 5 стовпців, вам потрібно вибрати 5 рядків і 5 стовпців.
Введіть = TRANSPOSE (A1: E5) в активну клітинку (яка має бути у верхній лівій комірці виділення та натисніть Control Shift Enter.

Це дозволить транспонувати набір даних.

Ось кілька речей, які вам потрібно знати про функцію TRANSPOSE:

Це функція масиву, тому вам потрібно використовувати Control-Shift-Enter, а не лише Enter.
Ви не можете видалити частину результату. Вам потрібно видалити весь масив значень, які повертає функція TRANSPOSE.
Функція транспонування лише копіює значення, а не форматування.

Транспонування даних за допомогою Power Query

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

Power Query є частиною Excel 2016 (Отримати та перетворити на вкладці Дані), але якщо ви використовуєте Excel 2013 або 2010, то вам потрібно встановити його як надбудову.

Припустимо, у вас є набір даних, як показано нижче:

Ось кроки для транспонування цих даних за допомогою Power Query:

В Excel 2016

Виберіть дані та перейдіть до Дані -> Отримати та перетворити -> З таблиці.
У діалоговому вікні «Створити таблицю» переконайтеся, що діапазон правильний, і натисніть «ОК». Відкриється діалогове вікно Редактор запитів.
У діалоговому вікні Редактор запитів виберіть вкладку «Перетворити».
На вкладці Перетворення перейдіть до Таблиця -> Використовувати перший рядок як заголовки -> Використовувати заголовки як перший рядок. Це гарантує, що перший рядок (який містить заголовки: Q1, Q2, Q3 та Q4) також обробляється як дані та транспонується.
Натисніть Транспонувати. Це дозволить миттєво транспонувати дані.
Натисніть Використовувати перший рядок як заголовки. Тепер це зробить перший рядок транспонованих даних заголовками.
Перейдіть до Файл -> Закрити та завантажити. Це закриє вікно Power Editor і створить новий аркуш, що містить транспоновані дані.

Зауважте, що оскільки верхня ліва комірка нашого набору даних порожня, вона отримує загальну назву Column1 (як показано нижче). Ви можете видалити цю клітинку з транспонованих даних.