Розумні таблиці Excel: секрети ефективної роботи та особливості

В останні роки з'явилися нові "розумні" технології: розумні телефони, розумні будинки, холодильники, чохли для телефонів, телевізори і т. д. Не стали винятком і електронні таблиці. Розумні таблиці в Excel були впроваджені починаючи з версії 2010 року.

Поняття про розумних таблицях

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


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

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

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

Випадаючі списки

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


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

Змінюємо стиль розумною таблиці

Якщо слідувати інструкції, то вийде розумна таблиця певного стилю, але в меню вона представляється символічно і не завжди може сподобатися, як вона виглядає на екрані. Тому в разі необхідності стиль можна змінити.
Як зробити розумну таблицю в Excel за новим стилем? Для цього не потрібно виділяти всю таблицю, достатньо помістити курсор у вигляді чорного прямокутника на будь-яку клітинку розумною таблиці. Після цього переміщаємося в таб "Конструктор", група "Стилі таблиць". Якщо не вистачає представлених стилів, клікаємо на "Додаткові параметри" і бачимо всю палітру стилів. Вибираємо вподобаний стиль, який і буде застосований до створеної розумною таблиці. До речі кажучи, якщо жоден стиль не подобається, внизу можна натиснути на посилання "Створити стиль таблиці".

Параметри стилів

Розумні таблиці в Excel 2010 і старше, а також в Excel 2007 можна настроювати під себе, залежно від уподобань і необхідності. Для цього переходимо курсором на будь-яку комірку таблиці. Переходимо на таб "Конструктор", група "Параметри стилів таблиць", і там ставимо/прибираємо галочки з цікавлять нас опцій: "Кнопка фільтра", "Чергуються стовпці або рядки", "Рядок заголовка або підсумків", "Перший або останній стовпець".

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

Формули в розумних таблицях Excel копіювати або протягувати не потрібно. Створення нового стовпця або нового рядка призводить до того, що формули у знову створені елементи таблиці в потрібні комірки самі підтягуються. У разі необхідності створення нового стовпця, в якому потрібно ввести формулу у клітинці даного стовпця вводимо формулу, яка дещо відрізняється від стандартної для даного типу електронної таблиці. Вона має вигляд[@[{Название столбца}]]арифметичні дії.
Тут {Назва стовпця} - конкретна назва стовпця, в якому здійснюється розрахунок, наприклад "Сума, крб.", арифметичні дії - це алгебраїчні вирази, вжиті в формулах Excel і, в разі необхідності, кількості, @ - показує, що дані будуть взяті з того ж рядка. Дані формули можна вводити як зазвичай прийнято в Excel, натискаючи на відповідні поля і тільки вказати необхідні функції, необхідні для формули допоміжні значки в цьому випадку будуть додані автоматично. При натисненні Enter відбудеться перерахунок у даному стовпці у всіх комірках таблиці. У разі необхідності підрахунку в кінці таблиці підсумків на табі "Конструктор" у групі "Параметри стилів таблиць" ставимо галочку на опцію "Рядок підсумків", що дозволить обчислити підсумкові суми по тих стовпців, де були введені формули. Якщо ви зацікавилися даною інформацією, добре володієте іноземними мовами і будете шукати додаткові джерела інформації за кордоном, пам'ятайте про те, що в Росії і за кордоном різні роздільник елементів списку. У них це кома, а у нас - крапка з комою, що необхідно враховувати у формулах.

Автофильтрация

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

Задаємо назву таблиці

Після того як розумна таблиця в Excel створена, додаток присвоює їй назву у відповідності з порядком її проходження (якщо це перша таблиця буде присвоєно назву "Таблиця1" тощо). Для того щоб дізнатися назву таблиці, ставимо курсор у будь-яку її клітинку, переходимо в таб "Конструктор", група "Властивості" і там знаходимо параметр "Ім'я таблиці", який можна редагувати, для чого потрібно помістити на "Таблиця1" курсор, виділити і ввести нову назву, після чого натиснути Enter. Ця назва може бути використано у формулах, вбудованих функціях, випадаючих списках. Назва таблиці може бути використано при застосуванні функцій ВВР, ДПР. Наприклад, ми хочемо знайти, хто у нас був 1229-м покупцем. Для цього в якусь вільну комірку, наприклад K1 вводимо 1229 в клітинку L1 вводимо формулу =ВПР(K1;Таблиця1 (або її змінену назву);1). Тут 1 - номер стовпця, в якому буде здійснюватися пошук. У результаті В комірці L1 з'являться Ф. В. О. покупця під №1229 (якщо, звичайно, він був у першому стовпці).

Адресація в розумних таблицях

Розумні таблиці в Excel дозволяють замість діапазону клітинок з даними застосовувати імена полів. Так, щоб підрахувати суму за значенням C з 1-ї по 21-у комірку можна ввести формулу, загальноприйняту в Excel = СУМ(C1:C21). Припустимо, що у стовпці " у нас занесена інформація по сумі покупки кожним конкретним покупцем, і називається цей стовпчик "Сума, крб.", а таблиця була перейменована в "Покупці". Таким чином, в якості формули може бути введено наступне вираз: = СУМ(Покупці[Сумма, руб.]). Крім цього, можна використовувати спеціальну адресацію: «=Покупці[#Эта строка]» буде посилатися на поточний рядок. «=Покупці[#Данные]» буде посилатися на дані, не враховуючи рядка заголовків. «=Покупці[#Заголовки]» буде посилатися на заголовки стовпців таблиці, розташовані в першому рядку таблиці. «=Покупці[#Итоги]» буде посилатися на підсумковий рядок (у разі її наявності у розумною таблиці). «=Покупці[#Все]» буде посилатися на всю таблицю, включаючи всі перераховані вище формули. Так, наприклад, підрахувати суми покупок можна введенням формули =СУМ(Покупці[#Все]).

Додаткові "фішки" розумних таблиць

Прокрутка стовпців таблиці при її немалий розмірі не вимагає фіксації областей, оскільки назви стовпців таблиці переміщуються в заголовки стовпців конкретного листа конкретної книги. У програмі Excel 2010 з'явилася можливість при роботі з розумними таблицями створювати зрізи. Вони являють собою автофільтр окремого стовпця, представлений у вигляді елемента графіки. Переходимо в таб "Конструктор", група команд "Інструменти", натискаємо "Вставити зріз", в діалоговому вікні вибираємо назви стовпців (або одного стовпця), з яких буде сформований зріз. При необхідності фільтрації таблиці вибираємо потрібну категорію або категорії, затиснувши клавішу Ctrl.

Як видалити розумну таблицю Excel

Іноді може виникнути така ситуація, коли потрібно зберегти дані в звичайному форматі електронної таблиці, видаливши з неї сортування, автоподставляемые формули, адресацію та інші примочки розумних таблиць, тобто залишити таблицю лише в якості бази даних. В Excel перетворити розумну таблицю в звичайну можна. Для цього ставимо курсор в будь-яку комірку розумною таблиці, переходимо в таб "Конструктор", група команд "Сервис", натискаємо на "Перетворити в діапазон". В результаті виникне діалогове вікно, підтверджуємо дію натисненням на кнопку згоди або клавішею Enter. Після цього таблиця буде збережена в звичайному вигляді із збереженням останнього застосовувався стилю і даних.

На закінчення

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