Аналіз «що-якщо»

Одним з важливих переваг електронних таблиць є можливість швидкої і легкої постановки так званого експерименту «що-якщо» Наприклад, можна змінити функціональне призначення якої-небудь клавіші і відразу ж побачити, до чого це призведе Більш складний варіант: ви вирішили взяти автомобіль в оренду з подальшим викупом або просто купити новий і для аналізу ситуації, що склалася звернулися до Microsoft Excel У програмі вам довелося, насамперед, математично описати обидві фінансові моделі, завдяки чому зявилася можливість провести над ними будь-яку кількість тестів, використовуючи різні параметри і початкові умови: величину орендних платежів, процентну ставку, знижку при виплаті всієї суми відразу і т д Все це дозволило досить точно спрогнозувати загальний обсяг витрат і, виходячи з результату, прийняти те чи інше рішення У поточній розділі мова піде саме про те, як моделювати ситуації і оцінювати отримані результати

Таблиці підстановки

Таблиця підстановки – Це спеціальна таблиця, в якій відображається, як зміна однієї або двох входять у формулу змінних впливає на результат цієї формули Excel передбачає створення таблиць підстановки двох видів: у першому варіанті тестується зміна значення однієї або декількох формул при зміні окремого параметра, а для другого варіанту відображається зміна значення тільки однієї формули, але при варіюванні значень декількох змінних Щоб створити будь-яку з цих таблиць, необхідно в меню Дані (Data) вибрати команду Таблиця підстановки (Table)

Таблиці підстановки для однієї змінної

Припустимо, ви розглядаєте пропозицію щодо купівлі будинку, яке вимагатиме взяття у банку позики у розмірі $ 200 000 з розстрочкою на 30 років Вам необхідно обчислити розмір щомісячних виплат при різних процентних ставках Таблиця підстановки для однієї змінної, зображена на рис 181, надає потрібну інформацію

Рис 181 Побудова таблиці підстановки починається з введення ряду різних процентних ставок і запису формули з функцією ПЛТ

Отже, для вирішення поставленого завдання потрібно виконати наступні дії:

1 Помістити в осередку ВЗ: В9 значення потенційних процентних ставок, як на рис

181 Цей діапазон називається вхідним діапазоном, оскільки він містить ті самі вихідні значення, які потрібно протестувати

2 Ввести величину кредиту в клітинку поза таблиці даних У нашому прикладі ми ввели значення $ 200 000 в осередок С1 Це дозволить потім легко змінювати величину позики при пробі різних сценаріїв

3 Ввести відповідну формулу В даному випадку в комірці С2 запишемо формулу

= ПЛТ (А2/12 360 С1), де А2/12 – місячний відсоток, 360 – термін погашення позики в місяцях, а С1 – посилання на величину суми позики

ПРИМІТКА

Зверніть увагу, що ця формула посилається на клітинку А2, яка зараз порожня При обрахуванні формул Excel присвоює порожнім осередкам значення 0, тому функція в комірці С2 повертає величину щомісячних виплат, розраховану за нульовою процентною ставкою Тобто осередок А2, звана осередком введення, – це фіксована осередок, через яку Excel буде надалі проводити розрахунок, підставляючи в неї значення з вхідного діапазону Насправді, так як зберігається в цьому осередку значення не змінюється, в її якості може виступати будь-яка інша осередок поза діапазону таблиці даних

4 Закінчивши введення вихідних значень і формули, виділіть діапазон таблиці даних – мінімальний прямокутний блок, захоплюючий формулу і весь вхідний діапазон У нашому випадку потрібно виділити діапазон В2: С9 і в меню Дані вибрати команду Таблиця підстановки

5 На екрані зявиться діалогове вікно Таблиця підстановки (Table), представлене на рис 182 Місцезнаходження осередку введення вказується в полі Підставляти значення за стовпцями в (Row Input Cell) або в поле Підставляти значення по рядках в (Column Input Cell) Осередок введення – Це осередок, на яку посилається формула таблиці підстановки, в нашому прикладі нею є осередок А2 Якщо вхідний діапазон є рядком, посилання на вхідну осередок задається в полі Підставляти значення по стовпцях в, якщо ж навпаки – стовпцем (як в даному випадку), використовується поле Підставляти значення по рядках в

Рис 182 У діалоговому вікні Таблиця підстановки задається вхідна осередок

6 Залишилося натиснути кнопку ОК Excel помістить результати обчислення формули для кожного вхідного значення у вільні комірки діапазону таблиці підстановки У нашому прикладі Excel виведе сім значень в діапазон СЗ: С8, як показано на рис 183

Рис 183 Тепер щомісячні виплати за позикою для кожної з величин процентної ставки відображаються в таблиці підстановки

При створенні таблиці підстановки програма занесла формулу масиву

{ТАБЛИЦЯ = ( А2)} в кожну клітинку діапазону результатів, а в цілому в діапазон СЗ: С9 За цією формулою обчислюється значення функції ПЛТ для кожного із значень вхідного діапазону в стовпці В Після побудови таблиці можна в будь-який момент змінювати вихідні значення, і результат обчислень відразу ж буде відображатися на екрані

ПРИМІТКА

Функція ТАБЛИЦЯ є прихованою (internal) функцією Excel Це означає, що її не можна вибрати зі списку діалогового вікна Майстра функцій або ввести вручну

Таблиця підстановки з кількома формулами

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

Розглянемо на прикладі таке важливе питання, як покупка будинку Нехай необхідна для цього сума береться в банку і її розмір – $ 180 000 з терміном погашення 30 років Зараз вам важливо визначити розмір щомісячних платежів по кредиту, а також порівняти його з виплатами для позики в $ 200 000 (рис 183) Тут досить розширити представлену раніше таблицю, включивши в неї обидві формули, в наступному порядку:

1 Занесіть нову формулу = ПЛT (A2/12 360 Dl) в осередок D2 Зверніть увагу, що ця формула так само, як і перша, посилається на клітинку А2

2 Введіть величину позики ($ 180 000) в осередок D1 і виділіть діапазон таблиці B2: D9

3 Виберіть у меню Дані команду Таблиця підстановки і помістіть в поле Підставляти значення по рядках в діалогового вікна Таблиця підстановки посилання на комірку А2 ($ А $ 2) Результат обчислень представлений на рис 184

Рис 184 У цій таблиці підстановки розраховуються розміри щомісячних відрахувань за різними кредитами для різних процентних ставок

Таблиці підстановки для двох змінних

Досі нами обчислювалися значення по одній або декількох формулами при різних значеннях однієї змінної Але, припустимо, потрібно розрахувати розмір щомісячних виплат по кредиту в $ 200 000 не тільки для різних процентних ставок, але й залежно від терміну виплат – потрібно дізнатися, як їх тривалість впливає на розмір місячного платежу

Щоб створити таку таблицю, виконайте такі дії:

1 Як і колись, введіть в діапазон ВЗ: В9 величини процентних ставок Потім заповніть даними другого безліч (тривалість виплат за позикою в місяцях) в рядку, розташованої безпосередньо вище першого осередку діапазону процентних ставок, так, як це показано на рис 185 У нашому випадку другий набір вхідних даних знаходиться в діапазоні C2: F2

2 Після цього вставте значення розміру позики в клітинку поза діапазону таблиці даних, наприклад в комірку 12 Тепер прийшов час самої формули Оскільки ми обумовили, що таблиця буде залежна від двох змінних, формулу слід вводити в клітинку, розташовану на перетині рядка і стовпчика, що містять дві множини вхідних значень, тобто в осередок В2 У таблиці підстановки з двома змінними, на відміну від таблиці з однією змінною, може бути тільки одна формула У нас вона записується так: = ПЛТ (А2/12 В1 12)

3 Формула негайно поверне помилку # СПРАВ / 0, оскільки обидві комірки, А2 і В1, використовувані як аргументи, порожні, і через це виходить або занадто великий, або занадто маленький результат, який програма не в змозі інтерпретувати Але, як ви побачите пізніше, подібний початок ніяк не впливає на виконання подальших обчислень

4 Нарешті, виділяємо діапазон таблиці підстановки – мінімальний блок прямокутної форми, що включає в себе всі вхідні значення і осередок з формулою У нашому випадку – діапазон B2: F9

5 Тепер потрібно вибрати команду Таблиця підстановки в меню Дані та задати комірки вводу Оскільки створюється таблиця з двома змінними, таких осередків повинно бути дві Ми вкажемо в поле Підставляти значення за стовпцями в посилання на комірку В1 ($ В $ 1), а в полі Підставляти значення по рядках в – на клітинку А2

Рис 185 Осередок В2 містить формулу з двома змінними

6 Залишилося натиснути клавішу Enter або кнопку ОК Результат обчислення таблиці даних з двома змінними (з невеликим форматуванням) показаний на рис 186

Рис 186 Ця таблиця підстановки обчислює розмір щомісячних виплат по кредиту при різних процентних ставках і терміни погашення кредиту

ВИРІШЕННЯ ПРОБЛЕМ

Будьте уважні, не переплутайте осередку введення в таблиці з двома змінними Якщо таке раптом станеться, для обчислень залучатимуться вхідні осередки з іншого діапазону, що призведе до невірних результатами Так, у розглянутому випадку замість процентних ставок з діапазону ВЗ: В9 в формулу будуть підставлені тимчасові інтервали з діапазону C2: F2, тобто терміни погашення Навряд чи ви хочете, щоб витрати на будинок становили щомісяця більш ніж 20 млн доларів Щоб не помилитися, ми рекомендуємо при визначенні осередків введення поглядати на формулу У нашому прикладі у формулі

= ПЛТ (А2/12, В1, 12) вміст комірки А2 є першим аргументом, тобто ставкою Процентні ставки розташовуються в стовпці, тому посилання на комірку А2 потрібно ввести в поле Підставляти значення по рядках в

Редагування таблиць підстановки

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

Діапазон-одержувач можна скопіювати в будь-яку іншу частину робочого листа Це зручно, якщо надалі ви хочете змінити вхідні значення або формули розрахунку, зберігши первісний підсумок На рис 187 ми скопіювали значення з блоку C3: F9 в осередку C11: F17 Копії вже не є формулами масиву, це звичайні числові константи – Excel автоматично замінює формули масиву їх числовими значеннями

Рис 187 При копіюванні діапазону результатів формули масиву автоматично заміщуються числами

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*