Як створити таблицю даних з однією змінною в Excel 2016

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

На наступному малюнку показано електронну таблицю прогнозів продажів на 2017 рік, для якої потрібно створити таблицю даних з однією змінною. У цьому робочому аркуші прогнозована сума продажів у клітинці B5 розраховується шляхом додавання минулорічної суми продажів у клітинці B2 до суми, яка, як очікується, зросте у 2017 році (розраховується шляхом множення загальної суми минулого року в клітинці B2 на відсоток зростання в клітинці B3 ), даючи вам формулу

Електронна таблиця прогнозу продажів зі стовпцем можливих відсотків зростання для підключення до таблиці даних з однією змінною.

=B2+(B2*B3)

Оскільки ви натиснули командну кнопку «Створити з вибору» на вкладці «Формули» стрічки після того, як зробили вибірку A2:B5 і прийняли прапорець «Лівий стовпець» за замовчуванням, формула використовує заголовки рядків у стовпці A і звучить так:

=Продаж_2016+(Продаж_2016*Ріст_2017)

Як ви можете бачити на цьому малюнку, стовпець можливих темпів зростання від 1% до 5,0% був введений у стовпець B у діапазоні B8:B24.

Електронна таблиця прогнозу продажів після створення таблиці даних з однією змінною в діапазоні C8:C24.

Щоб створити таблицю даних з однією змінною, яка підключає кожне з цих значень у формулу зростання продажів, виконайте такі прості кроки:

Скопіюйте вихідну формулу, введену в клітинку B5, у клітинку C7, ввівши = (дорівнює), а потім клацнувши клітинку B5, щоб створити формулу =Projected_Sales_2017.

Копія вихідної формули (щоб замінити серію різних темпів зростання в B8:B24) тепер є заголовком стовпця для таблиці даних з однією змінною.

Виберіть діапазон комірок B7:C24.

Діапазон таблиці даних включає формулу разом із різними темпами зростання.

Натисніть Дані→ Аналіз "Що, якщо"→ Таблиця даних на стрічці.

Excel відкриває діалогове вікно Таблиця даних.

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

Excel вставляє абсолютну адресу комірки, $B$3, у текстове поле Комірка введення стовпця.

Натисніть OK, щоб закрити діалогове вікно Таблиця даних.

Щойно ви натискаєте кнопку OK, Excel створює таблицю даних у діапазоні C8:C24, вводячи формулу за допомогою функції TABLE в цей діапазон. Кожна копія цієї формули в таблиці даних використовує відсоток темпів зростання в тому ж рядку в стовпці B для визначення можливого результату.

Клацніть клітинку C7, потім клацніть командну кнопку Format Painter у групі «Буфер обміну» на вкладці «Головна» та перетягніть діапазон комірок C8:C24.

Excel копіює формат числа облікових записів у діапазон можливих результатів, розрахованих за цією таблицею даних.

Кілька важливих речей про таблицю даних з однією змінною, створену в цій електронній таблиці:

• Якщо ви зміните будь-які відсотки темпів зростання в діапазоні комірок B8:B24, Excel негайно оновить пов’язаний прогнозований результат продажів у таблиці даних. Щоб заборонити Excel оновлювати таблицю даних, доки ви не натиснете командну кнопку «Обчислити зараз» (F9) або «Обчислити аркуш» (Shift+F9) на вкладці «Формули», натисніть кнопку «Параметри обчислення» на вкладці «Формули», а потім клацніть «Автоматично, крім таблиць даних». опція (Alt+MXE).

• Якщо ви спробуєте видалити будь-яку формулу TABLE в діапазоні комірок C8:C24, Excel відображає сповіщення «Не вдається змінити частину таблиці даних». Ви повинні вибрати весь діапазон формул (у цьому випадку C8:C24), перш ніж натиснути Delete або натиснути кнопку Clear або Delete на вкладці Home.