Як використовувати пошук цілі в Excel 2016

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

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

Наведені нижче цифри ілюструють, як можна використовувати функцію пошуку цілі, щоб дізнатися, на скільки обсяг продажів повинен збільшитися, щоб отримати чистий прибуток за перший квартал у розмірі 525 000 доларів США (враховуючи певне зростання, вартість проданих товарів та припущення щодо витрат) у таблиці прогнозу продажів.

Щоб дізнатися, на скільки обсяги продажів повинні збільшитися, щоб отримати чистий прибуток у розмірі 525 000 доларів США в першому кварталі, виберіть клітинку B7, яка містить формулу для обчислення прогнозу на перший квартал 2017 року, перш ніж натиснути «Дані» → «Аналіз що, якщо» → «Пошук цілі». на стрічці або натисніть Alt+AWG.

Ця дія відкриває діалогове вікно пошуку цілі, подібне до показаного. Оскільки клітинка B7 є активною коміркою, коли ви відкриваєте це діалогове вікно, текстове поле Set Cell вже містить посилання на клітинку B7. Потім клацніть у текстовому полі До значення та введіть 525000 як ціль. Потім ви клацніть текстове поле «Змінивши клітинку» та клацніть клітинку B3 на робочому аркуші (комірка, яка містить продажі за перший квартал), щоб ввести абсолютну адресу комірки, $B$3, у це текстове поле.

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

На наступному малюнку показано діалогове вікно «Статус пошуку цілі», яке з’являється, коли ви натискаєте кнопку «ОК» у діалоговому вікні «Пошук цілі», щоб Excel продовжив і налаштував показник продажів, щоб досягти бажаного показника доходу. Як показує цей малюнок, Excel збільшує продажі в клітинці B3 з 450 000 доларів США до 772 058,82 доларів США, що, у свою чергу, повертає 525 000 доларів США як дохід у клітинці B7.

Електронна таблиця прогнозу, що показує рішення для пошуку цілі.

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

Якщо ви хочете зберегти значення, введені в аркуші в результаті пошуку цілі, натисніть кнопку OK, щоб закрити діалогове вікно «Статус пошуку цілі». Якщо ви хочете повернутися до вихідних значень, замість цього натисніть кнопку Скасувати.

Щоб переключатися між значеннями «після» та «до», коли ви закрили діалогове вікно «Статус пошуку цілі», натисніть кнопку «Скасувати» або натисніть Ctrl+Z, щоб відобразити вихідні значення перед пошуком цілі, і натисніть кнопку «Повторити» або натисніть Ctrl+ Y, щоб відобразити значення, породжені рішенням пошуку цілі.