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

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

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

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

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

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

Щоб налаштувати таблицю даних із двома змінними, додайте рядок можливих відсотків Expenses_2017 від 10% до 35% у діапазоні C7:H7 до стовпця можливих відсотків Growth_2017 від 1,00% до 5,00% у діапазоні B8:B24. Потім скопіюйте вихідну формулу з назвою Projected_Sales_2017 з клітинки B5 в клітинку B7, клітинку на перетині цього рядка відсотків Expenses_2017 і стовпця Growth_2017 у відсотках з формулою:

=Projected_Sales_2017

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

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

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

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

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

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

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

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

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

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

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

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

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

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