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

Зміст

• Застосування» розумної " таблиці
  • Створення» розумної " таблиці
  • Найменування
  • Розтягується діапазон
  • Автозаповнення формулами
  • Рядок підсумків
  • Сортування та фільтрація
  • Перетворення таблиці в звичайний діапазон
• Питання та відповіді

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

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

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

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

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

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

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

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

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

   

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

   

   Але найшвидший варіант &8212; це після виділення комірки або масиву використовувати натискання гарячих клавіш 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.

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