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

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

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

У програмі Microsoft Excel таблиць даних є частиною набору команд відомий як засоби аналізу What-If. Коли ви побудувати й аналізувати дані таблиці, ви робите аналізу what-if.

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

Типи аналізу what-if   

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

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

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

Дізнайтеся більше зі статті, Загальні відомості про аналіз «якщо».

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

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

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

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

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

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

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

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

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

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

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

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

Виконайте наведені нижче кроки.

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

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

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

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

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

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

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

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

  4. На вкладці дані натисніть кнопку аналіз «якщо» > Таблицю даних (у групі Знаряддя даних або прогнозу групу Excel 2016).

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

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

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

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

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

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

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

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

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

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

  3. На вкладці дані натисніть кнопку Аналіз «якщо» > Таблиця даних (у групі Знаряддя даних або прогнозу групу Excel 2016).

  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. На вкладці дані у групі Знаряддя даних або прогнозу (у Excel 2016), натисніть кнопку аналіз «якщо» > Таблицю даних (у групі Знаряддя даних або прогнозу групу Excel 2016).

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

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

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

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

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

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

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

Виконайте ці кроки для покращення продуктивності обчислення.

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

    • У Excel 2007натисніть Кнопку Microsoft Office Зображення кнопки Office , натисніть кнопку Параметри Excelі виберіть категорію формули .

    • В інших версіях, послідовно виберіть елементи файл > Параметри > формули.

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

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

Можна використовувати інші інструменти програми Excel для проведення аналізу «what-if», якщо на те є причина, або потрібно використати більші набори змінних даних.

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

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

Пошук розв'язання Excel

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

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

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

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

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

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

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

×