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

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

Потім ви вводите формулу (або її копію) в клітинку, розташовану на перетині цього рядка і стовпця введених значень.

У цій версії електронної таблиці прогнозованих продажів використовуються дві змінні для розрахунку прогнозованих продажів на 2014 рік: темп зростання у відсотках від зростання продажів у минулому році (у клітинці B3 під назвою Growth_2014) і витрати, розраховані як відсоток від продажів минулого року (у клітинка B4 з назвою Витрати_2014). У цьому прикладі вихідна формула, створена в клітинці B5, дещо складніша:

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

Щоб налаштувати таблицю даних із двома змінними, додайте рядок можливих відсотків Expenses_2014 у діапазоні C7:F7 до стовпця можливих відсотків Growth_2014 у діапазоні B8:B17. Потім скопіюйте вихідну формулу Projected_Sales_2014 з клітинки B5 в клітинку B7, клітинка на перетині цього рядка відсотків Expenses_2014 і стовпця Growth_2014 у відсотках за формулою:

=Projected_Sales_2014

За допомогою цих кількох кроків ви можете створити таблицю даних з двома змінними:

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

Цей діапазон комірок містить копію вихідної формули разом із рядком можливих витрат і відсотка темпів зростання.

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

Excel відкриває діалогове вікно «Таблиця даних» з точкою вставки в текстовому полі «Комірка введення рядка».

Клацніть клітинку B4, щоб ввести абсолютну адресу комірки, $B$4, у текстове поле Комірка введення рядка.

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

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

Excel заповнює порожні клітинки таблиці даних формулою ТАБЛИЦЯ, використовуючи B4 як комірку введення рядка та B3 як комірку введення стовпця.

Клацніть клітинку B7, потім клацніть командну кнопку Format Painter у групі «Буфер обміну» на вкладці «Головна» та перетягніть діапазон комірок C8:F17, щоб скопіювати формат числа облікових записів без десяткових знаків у цей діапазон.

Цей формат облікових номерів задовгий для відображення з огляду на поточну ширину стовпців C–F, що позначається символами ######. Оскільки діапазон C8:F17 все ще вибирається з використанням формату Painter, крок 7 вирішує цю проблему.

Натисніть кнопку «Формат» у групі «Комірки» на вкладці «Головна», а потім клацніть «Автопідігнати ширину стовпця» у спадному меню.

Формула масиву {=TABLE(B4,B3)}, яку Excel створює для таблиці даних із двома змінними в цьому прикладі, визначає як аргумент комірки введення рядка (B4), так і аргумент комірки введення стовпця (B3). (Див. сусідню бічну панель «Формули масивів і функція TABLE в таблицях даних».) Оскільки ця єдина формула масиву вводиться у весь діапазон таблиці даних C8:F17, будь-яке редагування обмежено цим діапазоном.