Розворот таблиці в Microsoft Excel

Бувають випадки, що після того, як користувач уже заповнив значну частину таблиці або взагалі завершив над нею роботу, він розуміє, що більш наочно буде розгорнути таблицю на 90 або 180 градусів. Звичайно, якщо таблиця виготовляється для власних потреб, а не під замовлення, то навряд чи він стане її переробляти заново, а продовжить роботу над уже існуючими варіантом. Якщо ж перевернути табличну область вимагає роботодавець або замовник, то в цьому випадку доведеться попітніти. Але насправді існує ряд нескладних прийомів, які дозволять відносно легко і швидко зробити розворот табличного діапазону в потрібному напрямку, незалежно від того робиться таблиця для себе або під замовлення. Давайте подивимося, як це здійснити в програмі Excel.

виконання розвороту

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

Спосіб 1: розворот на 90 градусів

Перш за все, з'ясуємо, як поміняти місцями рядки з стовпцями. Зазначена процедура по-іншому називається Транспонированием. Найпростіше її здійснити, застосувавши спеціальну вставку.

1. Відзначаємо табличний масив, який потрібно розгорнути. Кількома по позначеному фрагменту правою кнопкою мишки. У списку, зупиняємося на варіанті «Копіювати».

   Також замість зазначеного вище дії після позначення області можна клікнути по значку, «Копіювати», який розташований у вкладці «Головна» в категорії «Буфер обміну».

   Але найшвидший варіант - це після позначення фрагмента зробити комбіноване натискання клавіш Ctrl + C. В цьому випадку копіювання також буде виконано.

2. Позначаємо будь-яку вільну позицію на аркуші з запасом вільного місця. Даний елемент повинен стати верхній лівій осередком транспоновану діапазону. Кількома по цьому об'єкту правою кнопкою миші. У блоці «Спеціальна вставка» може знаходитися піктограма «Транспонувати». Вибираємо її.

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

   Також є ще один варіант. Згідно з його алгоритму після позначення комірки і виклику контекстного меню потрібно двічі перейти по пунктам «Спеціальна вставка».

   Після цього відкривається вікно спеціальної вставки. Навпаки значення «Транспонувати» встановлюємо прапорець. Більше ніяких маніпуляцій в даному вікні робити не потрібно. Клацають по кнопці «OK».

   Дані дії можна виконати також через кнопку на стрічці. Позначаємо осередок і клацають по трикутнику, який розташований нижче кнопки «Вставити», розміщеної у вкладці «Головна» в розділі «Буфер обміну». Відкривається список. Як бачимо, в ньому присутня і піктограма «Транспонувати», і пункт «Спеціальна вставка ...». Якщо ви оберете піктограму, то транспонування відбудеться моментально. При переході по пункту «Спеціальна вставка» запуститься вікно спеціальної вставки, про який ми вже вели мову вище. Всі подальші дії в ньому точно такі ж.

3. Після завершення будь-якого з цього безлічі варіантів дій результат вийде однаковим: буде сформована табличная область, яка представляє собою розгорнутий на 90 градусів варіант первинного масиву. Тобто, в порівнянні з початковою таблицею у транспонованою області рядки і стовпці поміняються місцями.
4. Ми можемо залишити обидві табличні області на аркуші, а можемо видалити первинну, якщо вона більше не потрібна. Для цього позначаємо весь діапазон, який потрібно видалити вище транспонованою таблиці. Після цього у вкладці «Головна» натискаємо по трикутнику, який розміщений справа від кнопки «Видалити» в розділі «Осередки». У випадаючому списку вибираємо варіант «Видалити рядки з листа».
5. Після цього всі рядки, включаючи первинну табличну область, які розташовані вище транспоновану масиву, будуть видалені.
6. Потім, щоб транспонований діапазон прийняв компактний вид, позначаємо його весь і, перейшовши у вкладку «Головна», натискаємо на кнопку «Формат» в розділі «Осередки». У списку вибираємо варіант «Автоподбор ширини шпальти».
7. Після виконання останньої дії табличний масив прийняв компактний і презентабельний вигляд. Тепер ми чітко бачимо, що в ньому, в порівнянні з початковим діапазоном, рядки і стовпці помінялися місцями.

Крім того, транспонувати табличну область можна за допомогою спеціального оператора Excel, який так і називається - «ТРАНСП». Функція ТРАНСП спеціально призначена для того, щоб перетворювати вертикальний діапазон в горизонтальний і навпаки. Її синтаксис такий:

=ТРАНСП(массив)

«Масив» - єдиний аргумент цієї функції. Він являє собою посилання на діапазон, який слід перевернути.

1. Позначаємо сукупність порожніх осередків на аркуші. Кількість елементів в стовпці позначеного фрагмента має відповідати числу осередків в рядку таблиці, а кількість елементів в рядках порожнього масиву - числу осередків в стовпці табличній області. Потім клацають по піктограмі «Вставити функцію».
2. Відбувається активація Майстра функцій. Переходимо в розділ «Посилання та масиви». Відзначаємо там найменування «ТРАНСП» і клацають по «OK»
3. Відкривається вікно аргументів вищевказаного оператора. Встановлюємо курсор в єдине його поле - «Масив». Затискаємо ліву кнопку мишки і відзначаємо табличну область, яку потрібно розгорнути. При цьому її координати відобразяться в поле. Після цього не поспішаємо тиснути на кнопку «OK», як це зазвичай прийнято. Ми маємо справу з функцією масиву, а тому для того, щоб процедура була правильно виконана, тиснемо поєднання клавіш Ctrl + Shift + Enter.
4. Перевернута таблиця, як бачимо, вставлена ​​в зазначений масив.
5. Як бачимо, недолік цього варіанту в порівнянні з попереднім полягає в тому, що при транспонировании вихідне форматування не збережено. Крім того, при спробі змінити дані в будь-якому осередку транспоновану діапазону з'являється повідомлення, що не можна змінювати частина масиву. До того ж, транспонований масив пов'язаний з первинним діапазоном і при видаленні або зміні исходника теж буде видалений або змінений.
6. Але з останніми двома недоліками впорається досить просто. Відзначаємо весь транспонований діапазон. Клацають по значку «Копіювати», який розміщений на стрічці в категорії «Буфер обміну».
7. Після цього, не знімаючи позначення, натискаємо по транспоновану фрагменту правою кнопкою миші. У контекстному меню в категорії «Параметри вставки» натискаємо на піктограму «Значення». Дана піктограма представлена ​​у вигляді квадрата, в якому знаходяться числа.
8. Після додавання коментарів формула в діапазоні буде перетворена в звичайні значення. Тепер розташовані в ньому дані можна змінювати як завгодно. До того ж, даний масив вже не пов'язаний з вихідною таблицею. Тепер при бажанні вихідну таблицю можна видалити тим же способом, який ми розглядали вище, а перевернутий масив можна відформатувати належним чином, щоб він виглядав інформативно і презентабельно.

урок: Транспонування таблиці в Ексель

Спосіб 2: розворот на 180 градусів

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

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

1. Праворуч від таблиці біля її самої верхньої рядки ставимо цифру «1». Після цього встановлюємо курсор в нижній правий кут комірки, де встановлено вказане число. При цьому курсор трансформується в маркер заповнення. Одночасно затискаємо ліву кнопку миші і клавішу Ctrl. Простягаємо курсор до нижньої межі таблиці.
2. Як бачимо, після цього весь стовпець заповнений числами по порядку.
3. Відзначаємо стовпець з нумерацією. Переходимо у вкладку «Головна» і клацаємо по кнопці «Сортування і фільтр», яка локалізована на стрічці в розділі «Редагування». Із списку, зупиняємо вибір на варіанті «настроюється, сортування».
4. Після цього відкриється діалогове вікно, в якому повідомляється, що виявлені дані поза зазначеного діапазону. За замовчуванням перемикач в цьому вікні знаходиться в положенні «Автоматично розширювати виділений діапазон». Потрібно залишити його в тому ж положенні і клацнути по кнопці «Сортування ...».
5. Запускається віконце настроюється сортування. Простежте, щоб біля пункту «Мої дані містять заголовки» була знята галочка навіть в тому випадку, якщо заголовки дійсно присутні. Інакше вони не будуть опущені вниз, а так і залишаться у верхній частині сторінки. В області «Сортувати по» потрібно вибрати найменування того стовпця, в якому встановлена нумерація по порядку. В області «Сортування» потрібно залишити параметр «Значення», який встановлений за замовчуванням. В області «Порядок» слід встановити параметр «За спаданням '. Після виконання даних вказівок тисніть на кнопку «OK».
6. Після цього табличний масив буде відсортований у зворотному порядку. В результаті цієї сортування він буде перевернуть, тобто, останній рядок стане шапкою, а шапка - останнім рядком.

   Важливе зауваження! Якщо таблиця містила формули, то внаслідок такого сортування їх результат може виводитися некоректно. Тому в цьому випадку потрібно або взагалі відмовитися від перевертання, або попередньо перетворити результати обчислення формул у значення.

7. Тепер можна видалити додатковий стовпець з нумерацією, так як він нам більше не потрібен. Відзначаємо його, клацають правою кнопкою миші по зазначеному фрагменту і вибираємо в списку позицію «Очистити вміст».
8. Тепер роботу над розгортанням таблиці на 180 градусів можна вважати завершеною.

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

1. Відзначаємо комірку праворуч від діапазону, який потрібно перевернути, в його першому рядку. Клацають по кнопці «Вставити функцію».
2. Запускається Майстер функцій. Переміщаємося в розділ «Посилання та масиви» і відзначаємо найменування «зміщений», після чого тиснемо по «OK».
3. Запускається вікно аргументів. Функція зміщений призначена для зміщення діапазонів і має такий синтаксис:

   =СМЕЩ(ссылка;смещение_по_строкам;смещение_по_столбцам;высота;ширина)

   Аргумент «Посилання» являє собою лінк на останню комірку або діапазон зміщується масиву.

   «Зсув по рядках» - це аргумент, який вказує, наскільки таблицю потрібно змістити по рядках;

   «Зсув по стовпцях» - аргумент, який вказує, наскільки таблицю потрібно змістити по стовпцях;

   Аргументи «Висота» і «Ширина» необов'язкові. Вони вказують на висоту і ширину осередків перевернутої таблиці. Якщо опустити дані значення, то вважається, що вони рівні висоті і ширині исходника.

   Отже, встановлюємо курсор в поле «Посилання» і відзначаємо останню комірку діапазону, який потрібно перевернути. При цьому посилання потрібно зробити абсолютної. Для цього відзначаємо її і тиснемо на клавішу F4. Близько координат посилання повинен з'явитися знак долара ($).

   Далі встановлюємо курсор в поле «Зміщення по рядках» і в нашому випадку записуємо такий вираз:

   (СТРОКА()-СТРОКА($A$2))*-1

   Якщо ви все робили так само, як було описано вище, в даному виразі у вас, можливо, буде відрізнятися тільки аргумент другого оператора СТРОКА. Тут потрібно вказати координати першого осередку перевертає діапазону в абсолютному вигляді.

   В поле «Зміщення по стовпцях» ставимо «0».

   Поля «Висота» і «Ширина» залишаємо порожніми. Клацають по «OK».

4. Як бачимо, значення, яке розташовувалося в найнижчій клітинці, тепер відображається у верхній частині нового масиву.
5. Для того, щоб перевернути і інші значення, потрібно формулу з даного осередку скопіювати на весь нижній діапазон. Зробимо це за допомогою маркера заповнення. Встановлюємо курсор в нижній правий край елемента. Чекаємо, коли він перетворюється в маленький хрестик. Затискаємо ліву кнопку миші і тягнемо вниз до межі масиву.
6. Як бачимо, весь діапазон заповнений перевернутими даними.
7. Якщо ми хочемо, щоб в його осередках розташовуються не формули, а значення, то відзначаємо зазначену область і тиснемо на кнопку «Копіювати» на стрічці.
8. Потім клацають по зазначеному фрагменту правою кнопкою миші і в блоці «Параметри вставки» вибираємо іконку «Значення».
9. Тепер дані в перевернутий діапазон вставлені, як значення. Вихідну таблицю можна видаляти, а можна залишити, як є.

Як бачимо, існує кілька абсолютно різних способів розгорнути табличний масив на 90 і 180 градусів. Вибір конкретного варіанту, перш за все, залежить від поставленої перед користувачем завдання.

• ← Перенесення рядка в межах осередку в Microsoft Excel
• Робота з спадаючими списками в Microsoft Excel →