Використання «розумних» таблиць в Microsoft Excel

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

Застосування «розумної» таблиці

«Розумна» таблиця являє собою спеціальний вид форматування, після застосування якого до зазначеного діапазону даних, масив осередків набуває певні властивості. Перш за все, після цього програма починає розглядати його не як діапазон комірок, а як цілісний елемент. Дана можливість з'явилася в програмі, починаючи з версії Excel 2007. Якщо зробити запис в будь-який з осередків рядка або стовпця, які знаходяться безпосередньо біля кордонів, то цей рядок або стовпець автоматично включаються в даний табличний діапазон.

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

Але, на жаль, у цій технології є і деякі обмеження. Наприклад, небажано застосування об'єднання осередків. Особливо це стосується шапки. Для неї об'єднання елементів взагалі неприпустимо. Крім того, навіть якщо ви не бажаєте, щоб якесь значення, що знаходиться біля кордонів табличного масиву, було в нього включено (наприклад, примітка), воно все одно буде розцінюватися Excel, як його невід'ємна частина. Тому всі зайві написи потрібно розміщувати хоча б через один порожній діапазон від табличного масиву. Також в ньому не будуть працювати формули масивів і книгу неможливо буде застосовувати для спільного використання. Всі назви стовпців повинні бути унікальними, тобто, не повторюватися.

Створення «розумної» таблиці

Але перш, ніж перейти до опису можливостей «розумної» таблиці, давайте дізнаємося, як її створити.

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

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

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

   Але найшвидший варіант - це після виділення клітинки або масиву використовувати натискання гарячих клавіш Ctrl + T.

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

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

3. Після цього дії діапазон даних буде перетворений в «розумну» таблицю. Це буде виражатися в придбанні деяких додаткових властивостей у даного масиву, а також в зміні його візуального відображення, відповідно до обраного раніше стилю. Про основні можливості, які надають ці властивості, ми і поговоримо далі.

урок: Як зробити таблицю в Excel

Найменування

Після того, як «розумна» таблиця сформована, їй автоматично буде присвоєно ім'я. За замовчуванням це найменування типу «Таблица1», «Таблица2» і т.д.

1. Щоб подивитися, яке ім'я має наш табличний масив, виділяємо будь-якої його елемент і рухаємось у вкладку «Конструктор» блоку вкладок «Робота з таблицями». На стрічці в групі інструментів «Властивості» буде розташовуватися поле «Ім'я таблиці». У ньому якраз і укладено її найменування. У нашому випадку це «Таблица3».
2. При бажанні ім'я можна змінити, просто перебивши з клавіатури назву в зазначеному вище поле.

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

розтягується діапазон

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

1. Виділяємо будь-яку клітинку в першому рядку нижче табличного масиву. Робимо в ній довільну запис.
2. Потім тиснемо на клавішу Enter на клавіатурі. Як бачимо, після цього дії вся рядок, в якій знаходиться тільки що додана запис, була автоматично включена в табличний масив.

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

Аналогічне додавання відбудеться, якщо ми зробимо запис в стовпці, який знаходиться біля кордонів табличного масиву. Він теж буде включений до її складу. Крім того, йому автоматично буде присвоєно найменування. За замовчуванням назва буде «Столбец1», наступна додана колонка - «Столбец2» і т. Д. Але при бажанні їх завжди можна перейменувати стандартним способом.

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

урок: Як додати новий рядок в Excel

Заповнити форму формулами

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

1. Виділяємо першу осередок порожнього стовпчика. Вписуємо туди будь-яку формулу. Робимо це звичайним способом: встановлюємо в клітинку знак «=», після чого клацаємо по тим осередкам, арифметична дія між якими маємо намір виконати. Між адресами осередків з клавіатури проставляем знак математичного дії ( «+», «-», «*», «/» і т.д.). Як бачимо, навіть адресу осередків відображається не так, як в звичайному випадку. Замість координат, що відображаються на горизонтальній і вертикальній панелі у вигляді цифр і латинських букв, в даному випадку у вигляді адреси відображаються найменування колонок на тій мові, на якому вони внесені. Значок «@» означає, що осередок знаходиться в тому ж рядку, в якій розміщується формула. У підсумку замість формули в звичайному випадку

   =C2*D2

   ми отримуємо вираз для «розумної» таблиці:

   =[@Количество]*[@Цена]

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

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

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

рядок підсумків

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

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

   Для активації рядки підсумків замість вищеописаних дій можна також застосувати поєднання гарячих клавіш Ctrl + Shift + T.

2. Після цього в самому низу табличного масиву з'явиться додатковий рядок, яка так і називатиметься - «Підсумок». Як бачимо, сума останнього стовпця вже автоматично підрахована за допомогою вбудованої функції ПРОМЕЖУТОЧНИЕ.ІТОГІ.
3. Але ми можемо підрахувати сумарні значення та для інших стовпців, причому використовувати при цьому абсолютно різні види підсумків. Виділяємо клацанням лівої кнопки миші будь-який елемент рядка «Підсумок». Як бачимо, справа від цього елемента з'являється піктограма у вигляді трикутника. Клацаємо по ній. Перед нами відкривається список різних варіантів підведення підсумків:
   • середнє;
   • кількість;
   • максимум;
   • мінімум;
   • сума;
   • Зміщене відхилення;
   • Зміщена дисперсія.

   Вибираємо той варіант підбиття підсумків, який вважаємо за потрібне.

4. Якщо ми, наприклад, виберемо варіант «Кількість чисел», то в рядку підсумків відобразиться кількість осередків в стовпці, які заповнені числами. Дане значення буде виводитися всі тієї ж функцією ПРОМЕЖУТОЧНИЕ.ІТОГІ.
5. Якщо вам недостатньо тих стандартних можливостей, які надає список інструментів підведення підсумків, описаний нами вище, то тиснемо на пункт «Інші функції ...» в його самому низу.
6. При цьому запускається віконце Майстра функцій, де користувач може вибрати будь-яку функцію Excel, яку вважатиме для себе корисною. Результат її обробки будуть вставлений в відповідному полі рядка «Підсумок».

Читайте також:
Майстер функцій в Ексель
Функція проміжні підсумки в Excel

Сортування і фільтрація

У «розумної» таблиці за замовчуванням при її створенні автоматично підключаються корисні інструменти, які забезпечують виконання сортування та фільтрації даних.

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

   Після цього рядка будуть збудовані в обраному порядку.

   Якщо ви спробуєте впорядкувати значення в колонці, яка містить в собі дані в форматі дати, то вам буде запропоновано на вибір два варіанти сортування «Сортування від старих до нових» і «Сортування від нових до старих».

   Для числового формату теж буде запропоновано два варіанти: «Сортування від мінімального до максимального» і «Сортування від максимального до мінімального».

3. Для того, щоб застосувати фільтр, точно таким же чином викликаємо меню сортування та фільтрації, клацнувши по значку в тому стовпці, по відношенню до даними якого ви збираєтеся задіяти операцію. Після цього в списку знімаємо галочки з тих значень, рядки яких ми хочемо приховати. Після виконання вищевказаних дій не забуваємо натиснути на кнопку «OK» внизу спливаючого меню.
4. Після цього залишаться видно тільки рядки, біля яких в налаштуваннях фільтрації ви залишили галочки. Решта будуть заховані. Що характерно, значення в рядку «Підсумок» теж зміняться. Дані відфільтрованих рядків не будуть враховуватися при підсумовуванні і підведенні інших підсумків.

   Це особливо важливо, враховуючи те, що при застосуванні стандартної функції підсумовування (СУММ), а не оператора ПРОМЕЖУТОЧНИЕ.ІТОГІ, в підрахунку брали б участі навіть приховані значення.

урок: Сортування і фільтрація даних в Ексель

Перетворення таблиці в звичайний діапазон

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

1. Виділяємо будь-який елемент табличного масиву. На стрічці переміщаємося у вкладку «Конструктор». Клацаємо по іконі «Перетворити в діапазон», яка розташована в блоці інструментів «Сервіс».
2. Після цього дії з'явиться діалогове вікно, в якому буде питатися, чи дійсно ми хочемо перетворити табличний формат в звичайний діапазон даних? Якщо користувач впевнений в своїх діях, то слід натиснути на кнопку «Так».
3. Після цього єдиний табличний масив буде перетворений в звичайний діапазон, для якого будуть актуальними загальні властивості і правила Excel.

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

• ← Підсумовування стовпців в Microsoft Excel
• Розрахунок ПДВ в Microsoft Excel →