7.1.2 Таблиці даних для двох змінних

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

Наприклад, необхідно створити таблицю даних, яка обчислює місячні виплати за позикою під заставу в 200 000 грн., але на цей раз потрібно змінювати не тільки відсоткову ставку, але й строк позики. Треба обчислити місячні виплати для відсоткової ставки 6; 6,5; 7; 7,5; 8 та 8,5 відсотків при строках 15, 20, 25 та 30 років (180, 240, 300 та 360 місяців).

Щоб скласти таку таблицю, виконують наступні дії:

1. Вводиться перша множина вхідних значень у стовпчик. Як і раніше, вводимо шість відсоткових ставок в клітинки В3:В8.

2. Вводиться друга множина вхідних значень в рядку вище та правіше на одну клітинку від початку першого діапазону. В даному випадку вводимо строки позики в клітинки С2:F2.

3. Створюється формула для таблиці. Оскільки це таблиця з двома змінними, формула повинна бути введена в клітинку на перетині рядка та стовпчика, що містять дві множини вхідних значень, тобто в клітинку В2. Якщо в таблицю даних для однієї змінної можна включати будь-яку кількість формул, то в таблиці з двома змінними допускається використання лише однієї формули. Для даного прикладу формула буде мати наступний вигляд:

=ППЛАТ (А2/12; В1; 200 000)

Рисунок 7.4. - Клітинка В2 містить формулу для таблиці з двома змінними

На рис. 7.4 представлений результат, отриманий на даний момент. Формула повертає помилкове значення #ДЕЛ/0, оскільки в обчисленнях використовуються дві порожні клітинки, і в результаті отримується дуже велике або дуже мале число, яке Excel не може представити.

4. Виділяється діапазон таблиці даних – мінімальний прямокутний блок, що містить всі вхідні значення і формулу. В даному випадку виділяємо діапазон В2:F8.

5. Вибирається в меню Данные команда Таблица Подстановки та задаються вхідні клітинки. Оскільки це таблиця з двома змінними, задаються дві вхідні клітинки: одна для першої множини вхідних значень і одна для другої. В даному прикладі вводимо посилання для першої вхідної клітинки $B$1 в поле Подставлять значения по столбцам в, а потім вводимо посилання для другої вхідної клітинки $A$2 в поле Подставлять значения по строкам в.

6. Натискається Enter або ОК для обчислення таблиці. Результат представлений на рис. 7.5.

Рисунок 7.5. - Таблиця даних, що обчислює місячні виплати при різних поєднаннях відсоткових ставок та строків.

7.1.3 Редагування таблиць даних

Хоча можна редагувати формули або вхідні значення в лівому стовпчику або у верхньому рядку таблиці, міняти вміст клітинок в діапазоні результатів не дозволяється, оскільки таблиця даних є масивом. Якщо допущена помилка при заданні таблиці даних, то треба виділити всі результати, використати команду Очистить з меню Правка, а потім заново перерахувати таблицю.

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

7.2 Команда Пошук рішення

Команда Пошук рішення використовується для більш зручного розв’язування складних задач. Цей інструмент може використовуватись для розв’язку задач, які включають багато клітинок, що змінюються, і допомагає знайти комбінації змінних, які максимізують або мінімізують значення в цільовій клітинці. Він також дозволяє задати одне або декілька обмежень – умов, які повинні виконуватись при пошуку рішень.

Пошук рішення є надбудовою. Ця команда міститься в меню Сервіс. В якості приклада задачі, яку може розв’язувати пошук рішення, розглянемо складання плану рекламної кампанії нового виробу. Нехай загальний бюджет на друк і розповсюдження реклами складає 12 000 000 грн., загальне число публікацій рекламних оголошень бажано довести до 800 млн. примірників, і розміщення реклами планується в шести виданнях (вид. 1, вид. 2,…, вид. 6). Кожне видання має свою кількість читачів і різну ціну друкованої сторінки. (Щоб зробити цей аналіз більш простим, ми не враховуємо скидку на об’єм випуску). Задача полягяє в тому, щоб досягнути заданого числа читачів, по можливості, з найменшими витратами при наступних додаткових обмежень:

в кожному виданні повинно бути, по крайній мірі, шість оголошень;

не можна тратити більше однієї третьої засобів на одне видання;

загальна вартість розміщення реклами в вид. 3 і вид. 4 не повинна перевищувати 7 500 000 грн.

Рис. 7.6 показує один зі способів розв’язування задачі.

Рисунок. 7.6.- Можна використати Пошук рішень, щоб визначити, скільки оголошень треба помістити в кожному виданні для досягнення своєї мети з найменшими затратами

Можна розв’язувати цю задачу самостійно, підставляючи різні значення в D2:D7, слідкуючи за виконанням обмежень і відмічати вплив цих змін на загальні витрати в Е8. Насправді, Поиск решения так і робить, але він виконує цю роботу значно швидше. При цьому він не буде просто перебирати всі можливості, а використовує деякі аналітичні підходи для оптимального розв’язку.

Щоб почати роботу з цим інструментом, виберіть в меню Сервис команду Поиск решения. Відкривається вікно діалога, показане на рис. 7.7. В цьому вікні діалога слід вказати свою мету (мінімізувати загальні витрати), клітинки, що змінюються (кількість оголошень, які поміщаються в кожному виданні) і обмеження (умови, приведені внизу листка на рис.7.6).

Рисунок 7.7.- Використайте вікно діалога Пошук рішень для постановки своєї задачі