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

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. - Таблиця даних, що обчислює місячні виплати при різних поєднаннях відсоткових ставок та строків.

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

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

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

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

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

Пошук рішення є надбудовою. Ця команда міститься в меню Сервіс. В якості приклада задачі, яку може розв’язувати пошук рішення, розглянемо складання плану рекламної кампанії нового виробу. Нехай загальний бюджет на друк і розповсюдження реклами складає 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.- Використайте вікно діалога Пошук рішень для постановки своєї задачі

Задання цілі

В поле Встановити цільову клітинку задається мета, яку повинен досягнути пошук рішення. В даному прикладі треба мінімізувати загальні витрати (значення в клітинці Е8), тому задається мета введенням Е8 в поле Встановити цільову клітинку і встановивши переключатель Рівний в положення Мінімальному значенню.

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

В даному прикладі ми хочемо, щоб Пошук рішення встановив цільову клітинку в найменше можливе значення, тому встановіть переключатель Рівний в положення Мінімальному значенню. В інших задач потрібно дістати в цільовій клітинці найбільше можливе значення. Так, якщо цільова клітинка вміщує значення прибутку, встановіть лічильник Рівний в положення Максимальному значенню.А якщо треба, щоб Пошук рішення знайшов рішення, яке робить цільову клітинку рівною деякому конкретному значенню, треба встановити переключатель Рівний в положення Значенню ввести число (або посилання на клітинку в сусіднє поле).

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

Задання змінних

В наступному кроці слід задати клітинки з змінними. В розглянутому прикладі це буудть клітинки, розміщені в діапазоні D2:D7. Їх значення можуть бути змінені, і вони задають кількість оголошень, розміщених в кожному виданні. Як завжди, можна представити цю інформацію, вказавши координати клітинки або їх імена, чи виділивши клітинки в робочому листі. Якщо змінні знаходяться не в суміжних клітинках, потрібно розділяти змінні клітинки чи діапазони) крапкою з комою. (Для введення посилань на несуміжні клітинки не використовують клавішу Ctrl.) Замість цього можна натиснути клавішу Припустити, і пошук рішень сам запропонує змінюємі клітинки, виходячи із заданої цільовою клітинки.

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

Задання обмежень

Останній крок, заданя обмежень, є необов’язковим. Щоб задати обмеження, слід натиснути в вікні діалога Пошук рішення клавішу Добавити і заповніть вікно діалога Добавлення обмежень. Рис.7.8 показує, як ввести обмеження, при якому загальні витрати на рекламу (значення в клітинці Е8) повинні бути менші або рівні загальному бюджету на рекламну компанію (значення в клітинці G11).

Рис. 7.8.- Натисніть клавішу Добавити в вікні діалога Пошук рішень, щоб добавити обмеження

Як ви можете помітити, обмеження складається із трьох компонентів: посилань на клітинку, оператора порівняння та значення обмежень. Задайте посилання в поле Ссилка на ячейку, виберіть оператор порівняння в списку, що розкривається в середині цього вікна діалога і задайте значення обмеження в полі зправа. Після задання обмежень натисніть клавішу ОК для повернення в вікно діалога Пошук рішення чи нажміть клавішу Добавити для задання наступного обмеження.

Рис. 7.9 показує вікно діалога Пошук рішень після задання всіх обмежень для нашого прикладу. Обмеження перераховуються в алфавітному порядку, який може не співпадати з порядком, в якому їх визначали.

Рисунок 7.9.- Пошук рішень виводить список обмежень в алфавітному порядку і використовує імена клітинок і діапазонів, коли це можливо

Два обмеження мають посилання на діапазони зліва від оператора порівнення. Вираз $D$2:$D$7>=$G$15 говорить про те, що значення в кожній клітинці із діапазону D2:D7 повинно бути більше чи рівне 6, а вираз $F$2:$F$7<=$G$14 говорить про то, що значення в кожній клітинці із діапазону F2:F9 не повинно перевищувати 33,30%. Кожний з цих виразів є коротким записом шести окремих обмежень. Якщо використовується такий спосіб запису, значення границь з правої сторони оператора порівнення повинно представляти собою діапазон такої ж розмірності, яку має діапазон в лівій частині виразу, або повинно бути посиланням на клітинку чи константним значенням.

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

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

Рисунок 7.10- Коли пошук рішень знаходить відповідь на поставлену задачу, він виводить вікно діалога Результати пошуку рішень

Значення, показані на рис. 7.10, говорять про те, що витрати по рекламній компанії можна звести до мінімуму, розмістивши 6 оголошень в Вид. 1,6 – в Вид. 2, 33 – в Вид. 3, 53,4 – в Вид. 4, 34 – в Вид. 5 і 6 – в Вид. 6. При такому варіанті реклама повинна охопити 800 міліонів читачів. Але це значення не може бути точно виконане, тому що неможливо дати три десятих оголошень. Насправді, Вид. 4 не є єдиним виданням, для якого пошук рішень рекомендує неціле число оголошень. Значення для Вид. 3 незначно більше 33, а значення для Вид. 5 дещо нижче 34.

Нецілі результати можна опрацювати одним із двох способів: округлити значення або додавати нові обмеження, при яких результати повинні бути цілими числами. Округлення значення для Вид. 4 до 54 дає число читачів, рівне 803 міліонам при вартості реклами $11 267 347, що є достатньо прийнятним в даному випадку.