Відео
Постановка проблеми
У нас є таблиця, з якою доводиться постійно працювати (сортувати, фільтрувати, щось у ній рахувати) і вміст якої періодично змінюється (додається, видаляється, редагується). Ну хоча б для прикладу – ось так:
Розмір – від кількох десятків до кількох сотень тисяч рядків – значення не має. Завдання - всіляко спростити і полегшити собі життя, перетворивши ці комірки в «розумну» таблицю.
рішення
Виберіть будь-яку комірку в таблиці та на вкладці Головна (Головна) розширити список Відформатувати як таблицю (Відформатувати як таблицю):
У випадаючому списку стилів вибираємо будь-який варіант заливки на свій смак і колір і у вікні підтвердження для вибраного діапазону натискаємо OK і ми отримуємо наступний результат:
У підсумку після такого перетворення асортименту в «розумний» таблиця (з великої літери!) маємо такі радощі (крім гарного дизайну):
- створений таблиця отримує назву Таблиця 1,2,3 тощо, який можна змінити на більш адекватний на вкладці Конструктор (Дизайн). Це ім’я можна використовувати в будь-яких формулах, розкривних списках і функціях, таких як джерело даних для зведеної таблиці або масив пошуку для функції VLOOKUP.
- Створено один раз таблиця автоматично підлаштовується під розмір під час додавання або видалення даних до нього. Якщо додати до таких таблиця нові рядки – розтягнеться нижче, якщо додати нові стовпці – розшириться вшир. У правому нижньому кутку таблиці ви можете побачити автоматично рухомий межовий маркер і, якщо необхідно, відрегулювати його положення за допомогою миші:
- В капелюсі таблиці автоматично Вмикається автофільтр (можна примусово вимкнути на вкладці дані (Дата)).
- При додаванні нових рядків до них автоматично всі формули скопійовані.
- Під час створення нового стовпця з формулою – він буде автоматично скопійований у весь стовпець – не потрібно перетягувати формулу з чорним хрестиком автозаповнення.
- При прокручуванні таблиці вниз заголовки стовпців (A, B, C…) змінено на назви полів, тобто ви вже не можете виправити заголовок діапазону, як раніше (в Excel 2010 також є автофільтр):
- Увімкнувши прапорець Показати загальний рядок (Підсумковий рядок) таб Конструктор (Дизайн) ми отримуємо автоматичний підсумковий рядок у кінці таблиці з можливістю вибору функції (сума, середнє, підрахунок тощо) для кожного стовпця:
- До даних в таблиця можна адресувати використовуючи назви окремих її елементів. Наприклад, щоб підсумувати всі цифри в графі ПДВ, можна скористатися формулою =SUM(Таблиця1[ПДВ]) замість = SUM (F2: F200) а не думати про розмір таблиці, кількість рядків і правильність виділення діапазонів. Також можна використовувати наступні оператори (якщо таблиця має стандартне ім’я Таблиця 1):
- =Таблиця1[#Усі] – посилання на всю таблицю, включаючи заголовки стовпців, дані та підсумковий рядок
- =Таблиця1[#Дані] – посилання лише на дані (без рядка заголовка)
- =Таблиця1[#Заголовки] – посилання лише на перший рядок таблиці із заголовками стовпців
- =Таблиця1[#Усього] – посилання на підсумковий рядок (якщо він є)
- =Таблиця1[#Цей рядок] — посилання на поточний рядок, наприклад, формула =Таблиця1[[#Цей рядок];[VAT]] посилатиметься на значення ПДВ з поточного рядка таблиці.
(В англійській версії ці оператори звучатимуть відповідно як #All, #Data, #Headers, #Totals і #This row).
PS
В Excel 2003 було щось віддалено схоже на такі «розумні» таблиці – воно називалося List і створювалося через меню Дані – Список – Створити список (Дані — Список — Створити список). Але навіть половини нинішнього функціоналу не було взагалі. У старих версіях Excel цього теж не було.