Функція ТРАНСПОЗИЦІЯ Excel для заміни стовпця на рядок за допомогою формули
Смаки не враховуються. Це справедливо і для робітничих звичок. Деякі користувачі Excel воліють розміщувати дані вертикально в стовпцях, тоді як інші вибирають горизонтальне розташування рядків. У випадках, коли потрібно швидко змінити орієнтацію заданого діапазону, слід використовувати функцію TRANSPOSE.
Функція Excel ТРАНСПОЗІЯ - синтаксис
Мета функції ТРАНСПОЗИЦІЯ в Excel - перетворити рядки стовпці, тобто. змінити орієнтацію заданого діапазону з горизонтальною на вертикальну чи навпаки.
Функція приймає лише один аргумент:
Де масив діапазон комірок для транспонування.
Масив перетворюється так: перший рядок вихідного масиву стає першим стовпцем нового масиву, другий рядок стає другим стовпцем і так далі.
Важливе зауваження! Щоб функція TRANSPOSE працювала в Excel 2019 та нижче, ви повинні ввести її як формулу масиву, натиснувши Ctrl+Shift+Enter. У Excel 2021 і Excel 365, які підтримують масиви нативно, її можна ввести як звичайну формулу.
Як використовувати функцію ТРАНСПОЗИЦІЯ в Excel
Синтаксис TRANSPOSE не залишає місця для помилок під час побудови формули. Більше складна частина - правильно ввести її в робочий лист. Якщо у вас немає великого досвіду роботи з формулами Excel в цілому та формулами масивів зокрема, будь ласка, переконайтеся, що ви уважно слідуєте наведеним нижче крокам.
1. підрахуйте кількість стовпців та рядків у вихідній таблиці
Спочатку з'ясуйте, скільки стовпців і рядків містить ваша вихідна таблиця. Ці цифри вам знадобляться на наступному етапі.
У цьому прикладі ми збираємося транспонувати таблицю, в якій показано обсяг експорту свіжих фруктів по округах:
Наша вихідна таблиця має 4 стовпці та 5 рядків. Пам'ятаючи про ці цифри, переходимо до наступного кроку.
2. Виберіть ту саму кількість комірок, але змініть орієнтацію
Ваша нова таблиця міститиме ту саму кількість осередків, але буде повернена з горизонтальної орієнтації у вертикальну або навпаки. Отже, ви вибираєте діапазон порожніх осередків, який займає стільки рядків, скільки стовпців має вихідна таблиця, і стільки ж стовпців, скільки рядків має вихідна таблиця.
У нашому випадку ми вибираємо діапазон з 5 стовпців та 4 рядків:
3. Введіть формулу ТРАНСПОЗИЦІЯ
Виділивши діапазон порожніх осередків, введіть формулу транспонування:
Ось докладні кроки:
Спочатку введіть знак рівності, ім'я функції та дужку: =TRANSPOSE(
Потім виберіть діапазон джерела за допомогою миші або введіть його вручну:
Нарешті, введіть дужку, що закриває, але не натискайте клавішу Enter ! На цьому етапі ваша формула транспонування Excel має виглядати приблизно так:
4. Заповніть формулу ТРАНСПОЗИЦІЇ
Натисніть Ctrl+Shift+Enter, щоб правильно завершити формулу масиву. Для чого це потрібно? Тому що формула застосовуватиметься до кількох осередків, і це саме те, для чого призначені формули масиву.
Після натискання Ctrl + Shift + Enter Excel оточить вашу формулу Transpose , які видно в рядку формул і є візуальною ознакою формули масиву. У жодному разі не набирайте їх вручну, це не спрацює.
Знімок екрана нижче показує, що наша вихідна таблиця була успішно транспонована, і 4 стовпці були перетворені в 4 рядки:
Формула ТРАНСПОЗИЦІЯ в Excel 365
У Dynamic Array Excel (365 та 2021) функція TRANSPOSE неймовірно проста у використанні! Ви просто вводите формулу в ліву верхню комірку діапазону призначення та натискаєте клавішу Enter. І все! Жодного підрахунку рядків і стовпців, жодних формул масиву CSE. Вона просто працює.
В результаті виходить динамічний діапазон розливу, який автоматично розливається на стільки рядків та стовпців, скільки необхідно:
Як транспонувати дані в Excel без нулів для пропусків
Якщо один або кілька осередків у вихідній таблиці порожні, то в транспонованій таблиці ці осередки матимуть нульові значення, як показано на скріншоті нижче:
Якщо ви хочете повертати порожні осередки, вкладіть функцію IF у формулу TRANSPOSE, щоб перевірити, чи осередок є порожнім чи ні. Якщо комірка порожня, IF поверне порожній рядок (""), інакше передасть значення для транспонування:
Введіть формулу, як описано вище (не забудьте натиснути Ctrl+Shift+Enter, щоб правильно завершити формулу масиву), і ви отримаєте результат, подібний до цього:
Поради та нотатки щодо використання TRANSPOSE в Excel
Як ви тільки що переконалися, функція TRANSPOSE має низку особливостей, які можуть заплутати недосвідченого користувача. Нижче наведені поради допоможуть вам уникнути типових помилок.
1. Як редагувати формулу ТРАНСПОЗИЦІЇ
Як функція масиву, TRANSPOSE не дозволяє змінювати частину масиву, що повертається. Щоб змінити формулу Transpose, виділіть весь діапазон, до якого належить формула, внесіть необхідні зміни та натисніть Ctrl+Shift+Enter, щоб зберегти оновлену формулу.
2. Як видалити формулу TRANSPOSE
Щоб видалити формулу транспонування з робочого листа, виділіть весь діапазон, на який посилається формула, та натисніть клавішу Delete.
3.Замініть формулу TRANSPOSE значеннями
Коли ви перевертаєте діапазон за допомогою функції TRANSPOSE, вихідний і вихідний діапазони зв'язуються.
Якщо ви хочете розірвати зв'язок між двома таблицями, замініть формулу значеннями, що обчислюються. Для цього виділіть всі значення, що повертаються вашою формулою, натисніть Ctrl + C, щоб скопіювати їх, клацніть правою кнопкою миші і виберіть Спеціальна вставка > Значення із контекстного меню.
Для отримання додаткової інформації див. розділ Як перетворити формули на значення.
Ось як ви використовуєте функцію ТРАНСПОЗИЦІЯ для повороту даних в Excel.
Michael Brown
Майкл Браун - захоплений технологічний ентузіаст, який прагне спростити складні процеси за допомогою програмних інструментів. ділитися своїми знаннями та досвідом з іншими, надаючи прості поради та навчальні посібники для підвищення продуктивності та ефективності. Чи ви є досвідченим професіоналом або новачком, у блозі Майкла ви знайдете цінну інформацію та практичні поради, які допоможуть вам максимально ефективно використовувати ці важливі програмні інструменти.
Як транспонувати рядки в стовпці Excel (5 корисних методів) -.
Транспонування даних в MS Excel - це дійсно одна з найпоширеніших завдань, що виконуються користувачами Excel. транспонувати рядки у стовпці у MS Excel У цій статті ми покажемо кілька простих та ефективних методів з відповідними прикладами та правильними поясненнями.
Наступне зображення показує, що ми робимо в цій статті.
Завантажити Практичний посібник
Ви можете завантажити підручник з практики за посиланням нижче.
Інверсія рядків у стовпці.
5 корисних методів транспонування рядків у стовпці в Excel
Припустимо, що ми маємо набір даних, в якому представлені результати деяких студентів за роками. Ми хочемо транспонувати рядки цього набору даних до стовпців для кращого представлення. У наступних розділах ми покажемо 5 інструментальних методів excel для виконання цього завдання.
1. Використовуйте спеціальну команду "Вставити" та посилання на
Транспонувати рядки у стовпці
Ми можемо використовувати простий Спеціальна вставка команду двома способами, описаними нижче.
1.1 Використання спеціальної команди Paste
Цей метод швидкий і простий, але він не підходить для процесів, що повторюються. Тим не менш, давайте подивимося, як працює цей метод.
Кроки:
- Перш за все, виділіть діапазон осередків ( B4:I9) ви хочете транспонувати.
- Потім клацніть правою кнопкою миші і Копіювати Ви також можете використовувати комбінацію клавіш Ctrl+C натомість.
- Виберіть місце розташування осередку ( B11) де ви хочете отримати кінцевий результат.
- Перейти до Паста > Спеціальна вставка .
- Сайт Спеціальна вставка Відкриється меню. Позначте Транспонувати прапорець. В якості альтернативи можна також відкрити Спеціальна вставка меню за допомогою клавіш Ctrl+Alt+V .
Отже, ми створили перенесено набір даних із нашого вихідного набору даних.
Докладніше: Транспонування кількох рядків у групі в стовпці в Excel.
1.2 Транспонувати таблицю та зв'язати її з вихідними даними
Недоліком наведеного вище методу є те, що при зміні будь-яких даних у вихідному наборі даних вони не зміняться в транспонованому наборі даних. Таким чином, нам потрібен метод, який зв'яже вихідні дані з вихідним набором даних та відповідним чином оновлюватиметься. Дотримуючись наведених нижче кроків, давайте подивимося, як це зробити.
Кроки:
- Виберіть потрібний діапазон осередків ( B4:I9 )> Ctrl+C > Виберіть потрібне місце ( B11 ).
- Перейти до Спеціальна вставка меню, натиснувши кнопку Ctrl+Alt+V> Натисніть на Вставити посилання .
Новий набір даних тепер знаходиться в діапазоні осередків B11:I16 . Він ще не перенесений. Ми щойно з'єднали його з нашим вихідним набором даних. Водночас ми бачимо, що формат також зник. Ви повинні переробити формат, якщо не хочете, щоб він зник!
- Відкрийте Excel Знайти та замінити діалогове вікно. Або натисніть Ctrl+H натомість і дістатися до Замінити вкладку безпосередньо.
- Замінити все " = "символи з" abc або будь-які літери, яких немає в наборі даних> Натисніть кнопку Замінити все .
Може здатися, що ми втратили наш новий набір даних! Ми відстаємо від бажаного ще на три кроки. транспонована таблиця даних.
- Виберіть наш новий набір даних та натисніть Ctrl+C > Виберіть розташування ( K4 ) для остаточного розміщення висновку.
- Натисніть Ctrl+Alt+V > Відзначити Транспонувати прапорець.
Нарешті ми створили бажаний набір пов'язаних і транспонованих даних.
Слід зазначити, що якщо ви зміните будь-який вхід у вихідний набір даних, транспоновані дані також зміняться відповідним чином. Подивіться скріншот, прикріплений тут.
Докладніше: Ярлик Excel Paste Transpose: 4 простих способу використання
2. Використовуйте функцію ТРАНСПОЗИЦІЯ, щоб
Інвертування рядків у стовпці
Функція ТРАНСПОЗИЦІЯ дуже легко використовувати як динамічний масив в MS Excel 365 Ви можете використовувати його та в інших версіях трохи по-іншому. Ми покажемо це у цьому розділі.
2.1 Використання функції TRANSPOSE як динамічного масиву
Кроки:
- Виберіть розташування ( B11 ), де розмістити бажаний вихід у результаті.
- Введіть таку формулу, вибравши весь набір даних.
Нарешті ми створили транспонований набір даних, але втратили формат нашого набору даних.
Слід зазначити, що TRANSPOSE Функція динамічна, що означає, що якщо ви зміните щось у вихідному наборі даних, це автоматично оновиться в транспонованому висновку.
2.2 Використання функції TRANSPOSE за допомогою Ctrl+Shift+Enter
Якщо у вас немає MS Office 365 і змушені працювати зі старими версіями MS Excel, ви все одно можете працювати з TRANSPOSE функцію. Але цього разу вона буде менш гнучкою для вас.
У цьому випадку спочатку потрібно підрахувати, скільки стовпців та рядків міститься у вихідному наборі даних.
Сайт загальне правило полягає в тому, що якщо набір вхідних даних має X ряди і Y-колони вихідний набір даних буде мати Y ряди і X колони .
В даному випадку у нашому прикладі 8 колон і 6 рядів всього.
Кроки:
- Виберіть випадковий діапазон комірок, що складається з 6 стовпців та 8 рядків.
- Введіть таку формулу:
- Натисніть Ctrl+Shift+Enter або просто натисніть Увійти якщо ви MS 365 Користувач.
Нарешті ми отримали транспонований набір даних. Подивіться рядок формул. Фігурні дужки вказують на те, що це формула масиву.
Докладніше: Як транспонувати масив в Excel (3 простих способи)
Подібні читання
- Як транспонувати всі n рядків у стовпці в Excel (2 простих способи)
- Перетворення стовпців на рядки в Excel за допомогою Power Query
- Як транспонувати рядки, що дублюються, в стовпці в Excel (4 способи)
- Транспонування кількох стовпців в один стовпець в Excel (3 зручні методи)
3. Використовуйте функції INDIRECT & ADDRESS для транспонування рядків у стовпці
Ми також можемо транспонувати рядки у стовпці за два кроки, комбінуючи функції INDIRECT і АДРЕСА функції.
Кроки:
- По-перше, виберіть місце розташування комірки ( B11 ), де ви хочете розмістити висновок.
- Після цього введіть таку формулу:
Таким чином, ми отримали транспонований набір даних, але нам знову доведеться форматувати вихідні дані вручну.
Однією з переваг цього є те, що ви можете редагувати транспоновані дані, не змінюючи весь набір даних.
Докладніше: Транспонування кількох рядків у групі в стовпці в Excel
4. використання інструменту Excel Power Query для транспонування кількох рядків у стовпці
Power Query це справді ефективний інструмент для швидкого транспонування даних в Excel. Цього разу ми покажемо кроки щодо транспонування даних за допомогою функції Power Query інструмент.
Кроки:
- По-перше, виберіть набір даних та перейдіть до розділу Дані вкладка> Отримання та перетворення даних > З таблиці/діапазону .
- Сайт Створити таблицю відкриється діалогове вікно> Позначте пункт У моїй таблиці є заголовки прапорець> потім натисніть OK .
- Сайт Редактор Power Query відкриється діалогове вікно> Виберіть Перетворення вкладка.
- Виберіть Використовуйте заголовки як перший рядок .
- Потім виберіть Використовуйте перший рядок як заголовки .
Сайт Редактор Power Query діалогове вікно буде закрито, і, отже, до робочої книги буде завантажено новий аркуш, що містить транспоновані дані.
Докладніше: Excel Power Query: транспонування рядків у стовпці (покрокове керівництво)
5. використання макросів Excel VBA для перетворення рядків у стовпці
VBA
макроси також допомагають перетворювати рядки на стовпці робочого листа. У цьому розділі ми покажемо вам, як працювати з Макроси VBA для транспонування рядків у стовпці у MS Excel.
Кроки:
- Перш за все, клацніть правою кнопкою миші на імені робочого аркуша " Використання макросів VBA "> Натисніть на Подивитись код.
Модуль "Програми Microsoft Visual Basic Відкриється вікно.
Option Explicit Sub TransposeRowsToColumns() Dim SrcRng As Range Dim DestRng As Range Set SrcRng = Application. = Application.InputBox(Prompt:="Select внизу лівий смужку з рівнем становища", Тип:="Підставляє Ріви до Колумни", Тип:=8) SrcRng.Copy DestRng.SelectSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:= True Application.CutCopyMode = False End Sub
- Потім натисніть F5 для запуску макросів VBA> Виберіть весь набір даних та натисніть кнопку OK .
Нарешті, ми перетворили рядки на стовпці за допомогою функції Макроси VBA .
Докладніше: Excel VBA: транспонувати кілька рядків у групі у стовпці
Висновок
Отже, дорогі читачі, ми розглянули п'ять методів транспонування рядків у стовпці Excel. Сподіваюся, ви знайдете цей підручник корисним.Більше того, ви можете завантажити робочий зошит та практикуватися самостійно. Однак, якщо у вас є питання, коментарі або будь-які відгуки, будь ласка, не соромтеся, дайте мені знати поле для коментарів.
Hugh West
Х'ю Вест - досвідчений тренер та аналітик Excel з більш ніж 10-річним досвідом роботи в галузі. Він має ступінь бакалавра в галузі бухгалтерського обліку та фінансів та ступінь магістра ділового адміністрування. Х'ю пристрасно любить викладати і розробив унікальний підхід до навчання, яке легко слідувати і який легко зрозуміти. Його експертні знання Excel допомогли тисячам студентів та фахівців по всьому світу покращити свої навички та досягти успіху у своїй кар'єрі. У своєму блозі Х'ю ділиться своїми знаннями з усім світом, пропонуючи безкоштовні навчальні посібники з Excel та онлайн-навчання, щоб допомогти окремим особам та компаніям повністю розкрити свій потенціал.