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

Створюючи таблицю даних з однією змінною в Excel 2013, ви призначаєте одну клітинку на аркуші, яка служить або як комірка для введення рядка (якщо ви ввели ряд можливих значень у стовпці одного рядка), або як вхідні дані для стовпців. Комірка (якщо ви ввели ряд можливих значень у рядках одного стовпця).

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

=B2+(B2*B3)

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

=Продаж_2013+(Продаж_2013*Ріст_2014)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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