зміст
Excel містить багато потужних інструментів для виконання складних математичних обчислень, таких як Що якщо аналіз. Цей інструмент може експериментально знайти рішення ваших вихідних даних, навіть якщо дані неповні. У цьому посібнику ви дізнаєтеся, як використовувати один із інструментів аналіз «що якби». званий Вибір параметрів.
Вибір параметрів
Щоразу, коли ви використовуєте формулу чи функцію в Excel, ви збираєте вихідні значення разом, щоб отримати результат. Вибір параметрів працює навпаки. Він дозволяє, виходячи з кінцевого результату, розрахувати початкове значення, яке дасть такий результат. Нижче ми наведемо кілька прикладів, щоб показати, як це працює. Вибір параметрів.
Як використовувати вибір параметрів (приклад 1):
Уявіть, що ви йдете до якогось навчального закладу. На даний момент ви набрали 65 балів, а щоб пройти відбір, вам потрібно мінімум 70 балів. На щастя, є останнє завдання, яке може збільшити ваші бали. У цій ситуації можна використовувати Вибір параметрівдізнатися, який бал потрібно набрати за останнє завдання, щоб потрапити до навчального закладу.
На зображенні нижче ви бачите, що ваші бали за перші два завдання (тест і письмо) становлять 58, 70, 72 і 60. Хоча ми не знаємо, яким буде ваш бал за останнє завдання (тест 3). , ми можемо написати формулу, яка обчислює середній бал для всіх завдань одночасно. Все, що нам потрібно, це обчислити середнє арифметичне всіх п'яти рейтингів. Для цього введіть вираз =CORE(B2:B6) до комірки B7. Після звернення Вибір параметрів Для вирішення цієї задачі в комірці B6 буде відображено мінімальний бал, який необхідно набрати для вступу до навчального закладу.
- Виберіть комірку, значення якої ви хочете отримати. Кожен раз, коли ви використовуєте інструмент Вибір параметрів, Вам потрібно вибрати клітинку, яка вже містить формулу або функцію. У нашому випадку ми виберемо клітинку B7, оскільки вона містить формулу =CORE(B2:B6).
- На вкладці Додатково дані виберіть команду Що якщо аналіз, а потім у спадному меню натисніть Вибір параметрів.
- З’явиться діалогове вікно з трьома полями:
- ротоновлення в комірці це клітинка, яка містить бажаний результат. У нашому випадку це комірка B7, і ми її вже виділили.
- значення є бажаним результатом, тобто результатом, який має бути в клітинці B7. У нашому прикладі ми введемо 70, тому що вам потрібно набрати мінімум 70, щоб потрапити.
- Зміна значення клітинки – комірка, в якій Excel відобразить результат. У нашому випадку ми виберемо клітинку B6, тому що хочемо знати оцінку, яку хочемо отримати за останнє завдання.
- Після виконання всіх кроків натисніть OK.
- Excel обчислить результат і в діалоговому вікні Результат вибору параметра надати рішення, якщо таке є. Натисніть OK.
- Результат з'явиться у вказаній клітинці. У нашому прикладі Вибір параметрів встановіть, що вам потрібно отримати мінімум 90 балів за останнє завдання, щоб рухатися далі.
Як використовувати вибір параметрів (приклад 2):
Уявімо, що ви плануєте захід і хочете запросити якомога більше гостей, щоб не виходити за бюджет у 500 доларів. Ви можете використовувати Вибір параметріврозрахувати кількість гостей, яких можна запросити. У наступному прикладі клітинка B4 містить формулу =B1+B2*B3, яка підсумовує загальну вартість оренди кімнати та вартість розміщення всіх гостей (ціна за 1 гостя множиться на їх кількість).
- Виберіть клітинку, значення якої потрібно змінити. У нашому випадку ми виберемо клітинку B4.
- На вкладці Додатково дані виберіть команду Що якщо аналіз, а потім у спадному меню натисніть Вибір параметрів.
- З’явиться діалогове вікно з трьома полями:
- Упосадити в камеру це клітинка, яка містить бажаний результат. У нашому прикладі комірка B4 уже виділена.
- значення це бажаний результат. Ми введемо 500, оскільки прийнятно витратити 500 доларів.
- Зміниi значення клітинки – комірка, в якій Excel відобразить результат. Ми виділимо клітинку B3, оскільки нам потрібно розрахувати кількість гостей, яких ми можемо запросити, не перевищивши наш бюджет у 500 доларів США.
- Після виконання всіх кроків натисніть OK.
- Діалогове вікно Результат вибору параметра повідомить вам, якщо рішення знайдено. Натисніть OK.
- Результат з'явиться у вказаній клітинці. У нашому випадку Вибір параметрів підрахував результат 18,62. Оскільки ми підраховуємо кількість гостей, наша остаточна відповідь має бути цілим числом. Ми можемо округлити результат у більшу або меншу сторону. Округлюючи кількість гостей, ми перевищимо заданий бюджет, тобто зупинимося на 18 гостей.
Як ви можете бачити з попереднього прикладу, існують ситуації, які потребують цілого числа як результат. Якщо Вибір параметрів повертає десяткове значення, округляючи його в більшу або меншу сторону відповідно.
Інші типи аналізу «що-якщо».
Інші типи можна використовувати для вирішення більш складних завдань. аналіз «що якби». – Сценарії або таблиці даних. На відміну від Вибір параметрів, який спирається на бажаний результат і працює у зворотному напрямку, ці інструменти дозволяють аналізувати кілька значень і бачити, як змінюється результат.
- Дменеджер сценаріїв дозволяє підставляти значення відразу в декількох клітинках (до 32). Ви можете створити кілька сценаріїв, а потім порівняти їх, не змінюючи значення вручну. У наступному прикладі ми використовуємо сценарії для порівняння кількох різних місць проведення події.
- таблиці дані дозволяють взяти одну з двох змінних у формулі й замінити її будь-якою кількістю значень, а результати звести в таблицю. Цей інструмент має найширші можливості, так як відображає багато результатів одночасно, на відміну від Менеджер сценаріїв or Вибір параметрів. У наступному прикладі показано 24 можливі результати для щомісячних платежів за кредитом:
