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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

Таблиці даних входять до складу набору команд, іноді званого інструментами аналізу «якщо». Використання таблиць даних — це те саме, що й виконання аналізу «якщо».

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

Різновиди аналізу «якщо»    В Excel існує три різновиди засобів аналізу «якщо»: сценарії, таблиці даних і підбір параметра. Сценарії й таблиці даних на основі наборів вхідних значень визначають можливі результати. Підбір параметра діє інакше: він на основі результату визначає можливі вхідні значення, які давали б цей результат.

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

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

Відомості про аналізу what-if знайти Загальні відомості про аналіз «якщо».

Основні відомості про таблиці даних

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

Таблиці даних з однією змінною    Якщо ви хочете, щоб побачити різні значення з однією змінною в одному або кількох формулах результати цих формул за допомогою до таблиці даних з однією змінною. Наприклад, можна використовувати до таблиці даних з однією змінною побачити різні ставки впливають на розмір щомісячної виплати за допомогою функції 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) натисніть кнопку Аналіз «якщо»та виберіть пункт Таблиця даних.

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

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

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

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

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

Прискорення обчислення аркуша, який містить таблицю даних

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

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

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

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

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

Примітка : У разі вибору цього способу таблиці даних не оброблятимуться при переобчисленні книги. Щоб переобчислити таблицю даних вручну, виділіть її формули та натисніть клавішу F9.

Що далі?

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

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

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

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

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

На початок сторінки

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

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

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

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

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

×