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

Зміст

• Виконання розвороту
  • Спосіб 1: розворот на 90 градусів
  • Спосіб 2: розворот на 180 градусів
• Питання та відповіді

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

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

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

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

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

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

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

   

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

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

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

   

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

   

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

   

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


3. Після завершення будь-якого з цього безлічі варіантів дій результат вийде однаковим: буде сформована таблична область, яка представляє собою розгорнутий на 90 градусів варіант первинного масиву. Тобто, в порівнянні з початковою таблицею у транспонованої області рядки і стовпці поміняються місцями.

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

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

6. Потім, щоб транспонований діапазон прийняв компактний вигляд, позначаємо його весь і, перейшовши у вкладку»Головна" , натискаємо на кнопку " Формат» у розділі " клітини» . У списку вибираємо варіант " Автопідбір ширини стовпця» .

7. Після виконання останньої дії табличний масив прийняв компактний і презентабельний вигляд. Тепер ми чітко бачимо, що в ньому, в порівнянні з початковим діапазоном, рядки і стовпці помінялися місцями.

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

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

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

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. Запускається вікно аргументів. Функція зміщення призначений для зміщення діапазонів і має такий синтаксис:

   =зсув (посилання; зміщення_по_строкам;зміщення_по_стовпцям;висота; ширина)

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

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

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

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

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

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

   (РЯДОК()-РЯДОК($A$2))*-1

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

   У полі «зміщення стовпців» ставимо «0» .

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


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

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

6. Як бачимо, весь діапазон заповнений перевернутими даними.

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

8. Потім клацаємо по зазначеному фрагменту правою кнопкою миші і в блоці «Параметри вставки» вибираємо іконку " Значення» .

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

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