Як перетворити рядки на стовпці в Excel

У цьому посібнику пояснюється, що робити, щоб перетворити рядки на стовпці в Excel. Під час використання Excel часто доводиться міняти місцями рядки та стовпці в таблиці. Наприклад, буває так, що людина склала величезну таблицю, а потім зрозуміла, що читати її набагато легше, якщо її перевернути. 

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

Використання спеціальної функції вставки

Припустімо, у вас є два набори даних. Друге – це те, що ви хочете отримати в кінцевому підсумку. По-перше, це таблиця, яку потрібно транспонувати. У першому варіанті назви країн представлені різними колонками, і читати, а тим більше порівнювати характеристики держав між собою дуже незручно. Тому набагато краще організувати таблицю так, щоб назви країн містилися в різних колонках.

Щоб поміняти місцями рядки та стовпці, виконайте такі дії:

1. Виберіть вихідну таблицю. Якщо ви хочете побачити всю таблицю відразу (якщо вона дуже велика), вам потрібно натиснути комбінацію клавіш Ctrl + Home, а потім – Ctrl + Shift + End.
2. Потім клітинки копіюються. Це можна зробити через контекстне меню або натиснувши комбінацію клавіш Ctrl+C. Рекомендується відразу освоїти себе до останнього варіанту, адже якщо ви навчитеся гарячим клавішам, то зможете виконувати багато завдань буквально за одну секунду.
3. Виберіть першу клітинку в цільовому діапазоні. При цьому він повинен знаходитися за пределами таблиці. Також необхідно підібрати ячейку таким чином, щоб таблиця після транспонування не пересікала з іншими даними таблиці. Наприклад, якщо початкова таблиця має 4 стовпці і 10 рядків, то після виконання всіх цих операцій вона розсунеться на 10 клітинок вниз і на 4 клітинки в сторону. Тому в цьому діапазоні (відраховуючи від цільової комірки) не повинно бути нічого.
4. На цільовій комірці необхідно клацнути правою кнопкою миші і вибрати «Спеціальна вставка», а потім поставити галочку навпроти напису «Транспонувати».

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

Велика перевага опції «Спеціальна вставка» полягає в можливості транспонувати таблицю всього за кілька секунд. І при цьому повністю зберігається форматування, що також дозволяє зекономити кучу часу.

Незважаючи на ці явні плюси, є і ряд серйозних недоліків, які мішають цей метод називатися універсальним:

1. Погано використовувати його для транспонування повноцінних таблиць, не зведених до банального діапазону значень. У цьому випадку функція «Транспонування» буде вимкнена. Щоб вирішити цю проблему, потрібно перетворити таблицю на діапазон.
2. Цей метод добре підходить для одноразового транспонування, оскільки він не пов’язує нову таблицю з вихідними даними. Простіше кажучи, при зміні однієї з таблиць інформація в другій не буде автоматично оновлюватися. Тому транспозицію доведеться повторити. 

Як поміняти місцями рядки та стовпці та прив’язати їх до основної таблиці?

Отже, що можна зробити, щоб параметр «Спеціальна вставка» міг зв’язати таблицю з основними даними та даними, отриманими в результаті транспонування? Адже всі хотіли б, щоб дані оновлювалися автоматично.

1. Скопіюйте таблицю, для якої потрібно транспонування.
2. Виберіть клітинку без даних у порожній області таблиці.
3. Запустіть меню Спеціальна вставка, подібно до попереднього прикладу. Після цього потрібно натиснути на кнопку «Вставити посилання», яка знаходиться внизу ліворуч.
4. Результат буде наступним.
5. Виберіть нову таблицю і запустіть вікно «Найти і замінити», натиснувши комбінацію клавіш Ctrl + H.
6. Замінити всі знаки введення формули (=) на ххх (або будь-яку іншу комбінацію знаків, якої немає в оригінальній таблиці).
7. В результаті вийде щось жахливе, але це обов'язкова проміжна умова для досягнення бажаного результату. Тоді все буде виглядати красиво.
8. Скопіюйте отриману таблицю, а потім використовуйте «Спеціальна вставка», щоб транспонувати її.

Після цього вам просто потрібно знову відкрити діалогове вікно «Знайти та замінити» та використати його, щоб змінити «xxx» у клітинках на «=», щоб усі клітинки були пов’язані з вихідною інформацією.Звичайно, це складніше і довше, але цей спосіб дозволяє обійти відсутність посилання на вихідну таблицю. Але такий підхід має і недолік. Виражається в необхідності самостійного відновлення форматування.

Застосування формул

Є дві функції, які дозволяють гнучко змінювати рядки та стовпці: ТРАНСП і ДВССЫЛ. Тут також є можливість зберегти зв'язок із початковою таблицею, але механіка роботи кілька іна.

функція TRANSP

Насправді ця формула безпосередньо транспонує електронну таблицю. Синтаксис такий:

=TRANSP(масив)

Зараз ми спробуємо застосувати це до таблиці, яка містить інформацію про населення окремих держав.

1. Підрахуйте кількість стовпців і рядків у таблиці та знайдіть на аркуші порожнє місце з такими ж розмірами.
2. Запустіть режим редагування, натиснувши клавішу F2.
3. Напишіть функцію TRANSP з діапазоном даних у дужках. Під час використання цієї функції важливо завжди використовувати абсолютні посилання.
4. Натисніть комбінацію клавіш Ctrl+Shift+Enter. Важливо натискати саме поєднання клавіш, інакше формула відмовиться працювати.

Ось і все, тепер результат виглядає ось так!

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

Но недоліки у них теж є:

1. Форматування, як і у випадку з методом «Знайти та замінити», не буде збережено.
2. Вихідна таблиця повинна містити деякі дані, інакше в деяких комірках будуть нулі.
3. Ізлішня залежність від джерела даних. Крім того, цей метод має протилежний за недоліком – неможливості змінити транспоновану таблицю. Якщо спробувати це зробити, програма скаже, що редагувати частину масиву неможливо.

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

Використання формули INDIRECT

Механіка цього методу дуже схожа на використання формули TRANSP, але при цьому його використання вирішує проблему неможливості редагувати транспоновану таблицю без потері зв'язку з основною інформацією.

Но одной формулы НЕПРАВИЛЬНО недостатньо: вам ще потрібно скористатися функцією АДРЕСА. У цьому прикладі не буде великої таблиці, щоб не перевантажувати вас великою кількістю непотрібної інформації.

Отже, припустимо, у вас є така таблиця, що складається з 4 стовпців і 5 рядків.

Необхідно виконати такі дії:

1. Введіть цю формулу: =НЕПРЯМИЙ(АДРЕСА(СТОВПЦЬ(A1);РЯДОК(A1))) у верхню ліву комірку нової таблиці (у нашому прикладі це A7) і натисніть клавішу enter. Якщо інформація починається не в першому рядку або першому стовпці, то вам доведеться використовувати більш складну формулу: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). У цій формулі A1 означає верхню клітинку таблиці, на основі якої буде формуватися транспонована клітинка.
2. Поширити формулу на всю область, де буде розміщена нова версія таблиці. Для цього перетягніть маркер у правому нижньому кутку першої клітинки на протилежний кінець майбутньої таблиці.
3. все! Таблиця виявилася успішно транспонованою, і її ще можна редагувати. Звичайно, її зовнішній вигляд залишає бажати кращого, але виправити це не складно. Щоб відновити правильне форматування, потрібно скопіювати таблицю, яку ми транспонували (тобто вихідну), а потім вибрати новостворену таблицю. Далі клацніть правою кнопкою миші на вибраному діапазоні, а потім натисніть «Форматування» в параметрах вставки.

Отже, використовуючи функцію НЕПРАВИЛЬНО дозволяє редагувати абсолютно будь-яке значення в підсумковій таблиці, і дані завжди будуть оновлюватися, як тільки вносяться будь-які зміни у вихідну таблицю.

Звичайно, цей спосіб складніше, але якщо відпрацювати його на практиці, то все не так страшно.

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

Як працює комбінація формул INDIRECT і ADDRESS?

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

функція НЕПРАВИЛЬНО у нашій формулі використовується для створення непрямого посилання на клітинку. Наприклад, якщо вам потрібно вказати те саме значення в комірці A8, що й у B1, то ви можете написати формулу

=INDIRECT(“B1”)

Казалось би, зачем це робити? Ведь можна просто написати посилання на ячейку в іншій ячейці. Але перевага цієї функції в тому, що в посиланні можна перетворити абсолютно будь-яку строку, і навіть ту, яка створюється за допомогою іншої формули використання. Собственно, це ми і робимо у формулі.

Поза функціями АДРЕСА також використовується у формулі КОЛОНКА и ЛІНІЯ. Перший повертає адресу комірки на основі вже відомих номерів рядків і стовпців. Тут важливо дотримуватися правильну послідовність. Спочатку вказується рядок, а потім тільки стовпець. Наприклад, функція АДРЕСА(4;2) поверне адресу $B$2.

Наступна використовувана вище функція – це КОЛОНКА. Тут необхідно, щоб формула отримувала номер стовпця з певного посилання. Наприклад, якщо ви використовуєте параметр B2 у дужках цієї функції, вона поверне число 2, оскільки другим стовпцем є стовпець B.

Очевидно, що функція ROW працює так само, вона лише повертає номер рядка. 

І зараз ми будемо використовувати не абстрактні приклади, а цілком конкретну формулу, яка була використана вище:

=НЕПРЯМИЙ(АДРЕСА(СТОВПЦЬ(A1);РЯДОК(A1)))

Тут ви можете побачити це спочатку у функції АДРЕСА вказується стовпець, а вже потім рядок. І саме тут криється секрет працездатності цієї формули. Пам'ятаємо, що ця функція працює дзеркально, і першим аргументом в ній є номер рядка. І виявляється, коли ми пишемо туди адресу стовпця, вона перетворюється на номер рядка і навпаки.

То є, якщо підитожити:

1. Номери стовпців і рядків отримуємо за допомогою відповідних функцій.
2. Використання функції АДРЕСА рядки стають стовпцями і навпаки.
3. функція НЕПРАВИЛЬНО допомагає відобразити дзеркальні дані в комірці.

Ось як все виявляється просто!

Використання макросу для транспонування

Макрос — це невелика програма. Його можна використовувати для автоматизації процесу. Важливо враховувати, що макрос має деякі обмеження. Максимальний метод Transpose дозволяє працювати з 65536 елементами. Якщо цей ліміт буде перевищено, це призведе до втрати даних.

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

Наприклад, можна написати такий код, який буде змінювати місцями рядки та колонки.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange As Range

    Set SourceRange = Application.InputBox(Prompt:=»Please the range to transpose», Title:=»Transpose Rows to Columns», Type:=8)

    Set DestRange = Application.InputBox(Prompt:=»Виберіть верхню ліву клітинку цільового діапазону», Title:=»Transpose Rows to Columns», Type:=8)

    SourceRange.Copy

    DestRange.Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = Неправда

End Sub

Але якщо знати в програмуванні іншого немає, нічого страшного. Можна скористатися описаними вище способами. А потом учиться новому по вимірюванню старого.