Я ввів дані, а потім перетворив діапазон в таблицю за допомогою команди Вставка ► Таблиці ► Таблиця. Зверніть увагу на те, що я не визначав ніяких імен, а область даних в таблиці названа Таблица1 за замовчуванням.
Мал. 160.1. Проста таблиця стремено стовпцями
Робота з підсумковим рядком
Якщо ви хочете обчислити підсумкове прогнозоване і підсумкове фактичне значення продажів, вам навіть не потрібно писати формули. Просто натисніть кнопку додавання рядка підсумкових формул в таблицю.
- Активізуйте будь-яку клітинку в таблиці.
- Виконайте команду Робота з таблицями ► Установки стилів таблиць і встановіть прапорець Рядок підсумків.
- Активізуйте комірку в рядку підсумків і виберіть результуючу формулу в списку (рис. 160.2).
Наприклад, для розрахунку суми значень стовпця Реальний показник виберіть Сума в списку в осередку D15. Excel створить таку формулу:
= ПРОМЕЖУТОЧНИЕ.ІТОГІ (109; [Реальний показник]).
Для функції ПРОМЕЖУТОЧНИЕ.ІТОГІ 109 є перерахованим аргументом, який представляє суму. Другий аргумент функції ПРОМЕЖУТОЧНИЕ.ІТОГІ - це ім'я стовпця в квадратних дужках. Використання назв стовпців в квадратних дужках - спосіб створення структурованих посилань всередині таблиць.
Мал. 160.2. Список, що розкривається дозволяє вам вибрати результуючу формулу для стовпця таблиці
Ви можете вмикати / вимикати відображення підсумкового рядка, встановлюючи / знімаючи прапорець Рядок підсумків в групі Робота з таблицями ► Установки стилів таблиць. Якщо відключити її, то результуючі настройки, які ви вибрали, запам'ятовуються до наступного включення.
Використання формул всередині таблиці
Часто необхідно використовувати формули в таблиці. Наприклад, в таблицю, показану на рис. 160.1, можна додати стовпець, який показує різницю між фактичним і прогнозованим значенням за кожен місяць. Як ви побачите. Excel істотно спрощує цей процес.
- Активізуйте комірку Е2 і введіть Різниця в заголовок стовпчика. Excel автоматично розширить таблицю.
- Перейдіть до комірки Е3 і введіть знак рівності, що позначає початок формули.
- Клацніть на лівій стрілці, вказавши на відповідне значення н стовпці Реальний показник.
- Введіть знак мінус (-), а потім клацніть два рази на лівій стрілці, що вказує на відповідне значення в стовпці За планом.
- Натисніть Enter. щоб завершити формулу (рис. 160.3).
Формула вводиться в інші комірки стовпчика, і рядок формул відображає її:
= [@ [Реальний показник]] - [@ [За планом]]
Мал. 160.3. Стовпець Різниця містить формулу
Хоча формула була введена в перший рядок таблиці, так робити не обов'язково. Всякий раз, коли формула вводиться в порожній стовпець таблиці, вона поширюється на інші осередки в стовпці. Якщо вам потрібно змінити формулу, треба редагувати будь-яку формулу в стовпці, і зміна застосується до інших його осередкам.
Поширення формули на інші комірки стовпчика таблиці - це одна з можливостей автоматичної заміни в Excel. Щоб відключити цю функцію, клацніть на значку, який з'являється, коли ви вводите формулу, і виберіть Не створювати обчислювані стовпці автоматично.
Раніше в цій статті для створення формули застосовувалися імена стовпців. Однак ви можете ввести формулу, використовуючи стандартні посилання на комірки. Наприклад, спробуйте ввести наступну формулу в комірку Е3: = D3-C3. Якщо ввести посилання на комірки, то Excel як і раніше автоматично скопіює формулу в інші комірки стовпчика.
Посилання на дані в таблиці
Формули, які знаходяться за межами таблиці, можуть посилатися на дані всередині таблиці, використовуючи її ім'я і заголовки стовпців. Вам не потрібно створювати назви для цих елементів. Сама таблиця має назву (наприклад, Таблица1), і ви можете посилатися на дані в ній за допомогою заголовків стовпців.
Ви можете, звичайно, використовувати стандартні посилання на комірки, щоб звертатися до даних в таблиці, але посилання на таблицю мають явну перевагу: імена налаштовуються автоматично, якщо розмір таблиці змінюється внаслідок додавання або видалення рядків.
Зверніться до таблиці, показаної на рис. 160.1. Їй було присвоєно ім'я Таблица1 при створенні. Для обчислення суми всіх даних в таблиці вкажіть таку формулу: = СУММ (Таблиця 1). Ця формула завжди повертає суму всіх даних, навіть якщо рядки або стовпці додаються або видаляються. Якщо ж ви зміните назву Таблица1. Excel автоматично відкоригує формули, які посилаються на неї. Наприклад, якщо перейменувати Таблица1 в AnnualData (використовуючи Диспетчер імен), попередня формула зміниться так: = СУММ (Annual Data).
У багатьох випадках потрібно звернутися до конкретного стовпцю в таблиці. Наступна формула повертає суму даних стовпця Реальний показник (але ігнорує підсумковий рядок): = СУММ (Таблиця 1 [Реальний показник]). Зверніть увагу, що ім'я стовпця укладено в квадратні дужки. Знову формула автоматично підлаштовується, якщо ви змінюєте текст в заголовку стовпця.
Що ще краще, Excel надає допомогу при створенні формули, яка посилається на дані в таблиці. На рис. 160.4 продемонстровано роботу функції автоматичного заповнення формули, що допомагає створювати формулу, показуючи список елементів в таблиці.
Мал. 160.4. Формула автозаповнення корисна при створенні формули, яка відноситься до даних в таблиці