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

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

Таблиця даних – це діапазон клітинок, у яких можна змінити значення в деяких клітинках, а також відповіді на проблеми. Приклад таблиці даних: функція PMT відрізняється від різних сум позики та процентних ставок для обчислення доступної суми на умовах іпотечного кредиту. Експериментуючи з різними значеннями, щоб спостерігати за відповідним варіацією результатів, можна виконати загальні завдання в аналізі даних.

У програмі Microsoft Excel таблиці даних входять до складу набору команд, відомих як засоби аналізу "what-IF". Під час побудови та аналізу таблиць даних ви виконуєте аналіз "what-IF".

Аналіз "what-IF" – це процес змінення значень у клітинках, щоб побачити, як ці зміни впливатимуть на результати формул на аркуші. Наприклад, можна використовувати таблицю даних, щоб змінити відсоткову ставку та тривалість терміну для позики, щоб оцінити потенційні суми щомісячного платежу.

Примітка.: Можна виконувати швидше обчислення за допомогою таблиць даних і Visual Basic для застосунків (VBA). Докладні відомості наведено в статті Excel What-IF (якщо таблиці даних): швидший підрахунок за допомогою VBA.

Типи аналізу "what-IF"    

У програмі Excel існують три типи засобів аналізу "what-IF": сценарії, таблиці данихі мета-пошук. Сценарії та таблиці даних використовують набори значень вхідного для обчислення можливих результатів. Мета-пошук явно відрізняється, він використовує один результат і обчислює можливі вхідні значення, які призводять до результату.

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

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

Докладніше у статті Загальні відомості про аналіз "what-IF".

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

Таблиці даних з однією змінною    

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

На наведеному нижче рисунку, у клітинці D2 міститься формула оплати, = PMT (B3/12, B4,-B5), що посилається на клітинку з вхідними полями B3.

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

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

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

На наведеному нижче рисунку клітинка C2 містить формулу виплат, = PMT (B3/12, B4,-B5), у якому використовуються дві клітинки для входу, B3 і B4.

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

Обчислення таблиці даних    

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

Таблиця даних з однією змінною містить свої вхідні значення в одному стовпці (орієнтований на стовпець) або через рядок (орієнтований на рядки). Будь-яка формула в таблиці даних з однією змінною має посилатися лише на один _ z0z_.

Виконайте такі дії:

  1. Введіть список значень, які потрібно замінити на підставку вводу – вниз на один стовпець або на один рядок. Залишаючи кілька пустих рядків і стовпців по обидві сторони від значень.

  2. Виконайте одну з наведених нижче дій.

    • Якщо таблиця даних орієнтована за стовпцями (значення змінних у стовпці), введіть формулу в один рядок у рядку вище та на одну з них праворуч від стовпця значень. Ця таблиця даних з однією змінною – орієнтована на стовпець, а формула міститься у клітинці D2.




      Якщо потрібно перевірити вплив різних значень на інші формули, у клітинці праворуч від першої формули можна вказати додаткові формули.

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

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

  3. Виділіть діапазон клітинок, які містять формули та значення, які потрібно замінити. На рисунку вище цей діапазон має C2: D5.

  4. На вкладці дані клацніть елемент аналіз "what-IF" _ gt/_ таблиці даних (у групі " Знаряддя даних " або " прогноз " від _ z0z_).

  5. Виконайте одну з наведених нижче дій.

    • Якщо таблиця даних орієнтована за стовпцями, введіть значення _ z0z_ для вводу в полі "поле" у стовпці вводу стовпця . На рисунку вище, вхідний вміст має B3.

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

      Примітка.: Після створення таблиці даних може знадобитися змінити формат клітинок результату. На рисунку клітинки результату форматуються у вигляді грошової одиниці.

Формули, які використовуються в таблиці даних з однією змінною, мають посилатися на одну й ту саму.

Ось як це зробити

  1. Виконайте одну з таких дій:

    • Якщо таблиця даних орієнтована за стовпцями, у верхній рядок таблиці даних у верхньому рядку в пустій області в пусту клітинка вводиться нова формула.

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

  2. Виділіть діапазон клітинок, які містять таблицю даних і нову формулу.

  3. На вкладці дані клацніть елемент аналіз "what-IF " _ gt/_ таблиці даних (у групі " Знаряддя даних " або " прогноз " від _ z0z_).

  4. Виконайте одну з таких дій:

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

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

Таблиця даних із двома змінними використовує формулу, що містить два списки вхідного значення. Формула має посилатися на дві різні вхідні клітинки.

Виконайте такі дії:

  1. У клітинці на аркуші введіть формулу, яка посилається на дві клітинки вводу.

    У наведеному нижче прикладі, у якому значення початкових значень вводяться у клітинки B3, B4 та B5, введіть формулу = PMT (B3/12, B4,-B5) у клітинці C2.

  2. Введіть один список значень вводу в тому самому стовпці, що й під формулою.

    У цьому випадку введіть різні відсоткові ставки у клітинках C3, C4 і C5.

  3. У тому ж рядку, що й формула, можна перейти до другого списку.

    Введіть умови кредитування (в місяцях) у клітинках D2 і E2.

  4. Виділіть діапазон клітинок, які містять формулу (C2), як рядків, так і стовпців значень (C3: C5 і D2: E2), а також клітинки, у яких потрібно обчислити обчислені значення (D3: E5).

    У цьому випадку виберіть діапазон C2: E5.

  5. На вкладці "дані " в групі "Знаряддя даних " або " прогноз " (у _ Z0z_) клацніть елемент " What-IF", а потім – "таблиця даних" (у групі " Знаряддя даних " або " прогноз " від _ z1z_). 

  6. У полі підставляти значення за рядками введіть посилання на клітинку вводу для введених значень у рядку.
    Введіть у полі підставляти поле елемент " вміст ".

  7. У полі Підставляти значення стовпця введіть посилання на клітинку вводу для введених значень у стовпці.
    Введіть B3 у полі підставляти поле вводу у стовпці .

  8. Натисніть кнопку OK.

Приклад таблиці даних із двома змінними

У таблиці даних із двома змінними можна показати, як різні комбінації процентних ставок і умов позики вплинуть на щомісячний іпотечний платіж. На рисунку у клітинці C2 міститься формула оплати, = PMT (B3/12, B4,-B5), у якій використовуються дві клітинки для входу, B3 і B4.

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

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

Щоб покращити швидкодію обчислень, виконайте наведені нижче дії.

  1. Виконайте одну з таких дій:

    • У _ z0z_виберіть пункт Microsoft Office, кнопка_ Z1z_, натисніть кнопку Параметри Excel, а потім виберіть категорію формули .

    • У всіх інших версіях виберіть елемент файл _ gt/Options _ Gt_ формули.

  2. У розділі " варіанти обчислення " в розділі " обчислення" натисніть кнопку "автоматично", крім таблиць даних.

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

Ви можете використовувати кілька інших засобів Excel для виконання аналізу "what-IF", якщо у вас є певні цілі або великі набори змінних даних.

Підбір параметра

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

Розв'язувач Excel

За допомогою надбудови "Пошук розв'язання програми Excel" можна шукати оптимальне значення для набору змінних даних. Розв'язувач працює з групою клітинок (які називаються змінними рішеннями або просто змінними клітинками), які використовуються для обчислення формул у клітинках цілей і обмежень. Надбудова регулює значення у клітинках змінних відповідно до меж у клітинках обмежень і виводить потрібний результат у клітинці цільової функції. Докладні відомості в цій статті: визначення та вирішення проблем за допомогою функції "розв'язувач".

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

У таблиці даних наведено діапазон клітинок B2: D8. Ви можете змінити значення в B4, суму позики, а також щомісячні платежі у стовпці D автоматично оновлювати. За допомогою 3,75% процентна ставка, D2 повертає щомісячний платіж у розмірі $1 042,01 за допомогою цієї формули: = спл (C2/12, $B $3, $B $4).

Діапазон клітинок B2:D8 (таблиця даних)

Можна використовувати одну або дві змінні, залежно від кількості змінних і формул, які потрібно перевірити.

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

У цій живій книзі в клітинці D2 міститься формула оплати =спл (C2/12, $B $3, $B $4). Осередок B3 – це змінна , де можна підключити іншу довжину терміну (кількість щомісячних періодів платежів). У клітинці D2 функція PMT підключається до відсоткової ставки 3,75%/12, 360 місяців і $225 000, а також обчислює щомісячний платіж у розмірі $1 042,01.

Щоб побачити, як різні значення двох змінних у формулі змінюють результати, використовуйте двозмінне тестування. Наприклад, ви можете перевірити різні комбінації процентних ставок і кількість щомісячних періодів виплат, щоб розрахувати платіж за іпотечною ставкою.

У цій живій книзі клітинки C3 містять формулу виплат, =PMT ($B $3/12, $B $2, B4), у якому використано дві клітинки змінних, B2 та B3. У клітинці C2 функція PMT підключається до відсоткової ставки 3.875%/12, 360 місяців і $225 000, а також обчислює щомісячний платіж у розмірі $1 058,03.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×