Как да използвате търсенето и решаването на цели на Excel за решаване на неизвестни променливи

Excel е много способен, когато разполагате с всички данни, необходими за вашите изчисления.

Но не би ли било хубаво, ако можеше решаване на неизвестни променливи ?

С Goal Seek и добавката Solver може. И ние ще ви покажем как. Прочетете за пълно ръководство за това как да решите за една клетка с Търсене на цел или по -сложно уравнение с Решител.

Как да използвате търсене на цели в Excel

Търсенето на цели вече е вградено в Excel. Това е под Данни раздел, в Какво-ако анализ меню:

За този пример ще използваме много прост набор от числа. Имаме продажби на стойност три четвърти и годишна цел. Можем да използваме Goal Seek, за да разберем какви числа трябва да бъдат в Q4, за да постигнем целта.

Както можете да видите, текущите продажби са 114 706 единици. Ако искаме да продадем 250 000 до края на годината, колко трябва да продадем през четвъртото тримесечие? Търсенето на цели на Excel ще ни каже.

Ето как да използвате „Търсене на цел“, стъпка по стъпка:

Щракнете Данни> Анализ какво-ако> Търсене на цел . Ще видите този прозорец:
Поставете частта „равно“ на вашето уравнение в Задаване на клетка поле. Това е числото, което Excel ще се опита да оптимизира. В нашия случай това е общият брой на продажбите ни в клетка В5.
Въведете стойността на целта си в Да оценявам поле. Търсим общо 250 000 продадени бройки, така че ще поставим „250 000“ в това поле.
Кажете на Excel за коя променлива да решите в Чрез смяна на клетката поле. Искаме да видим какви трябва да са продажбите ни през четвъртото тримесечие. Така че ще кажем на Excel да реши за клетка D2. Това ще изглежда така, когато е готово за работа:
Удари Добре да решите за целта си. Когато изглежда добре, просто удари Добре . Excel ще ви уведоми, когато Goal Seek е намерил решение.
Щракнете Добре отново и ще видите стойността, която решава уравнението ви в клетката, за която сте избрали Чрез смяна на клетката .

В нашия случай решението е 135 294 единици. Разбира се, можехме просто да открием това, като извадим текущата сума от годишната цел. Но Goal Seek може да се използва и за клетка, която вече има данни в него . И това е по -полезно.

Имайте предвид, че Excel презаписва предишните ни данни. Добра идея е да стартирайте Търсене на цел върху копие на вашите данни . Също така е добра идея да отбележите копираните си данни, че те са генерирани с помощта на Търсене на цел. Не искате да го бъркате за актуални, точни данни.

неща, които да правите, когато ви е скучно онлайн

Така че търсенето на цели е a полезна функция на Excel , но не е толкова впечатляващо. Нека да разгледаме един инструмент, който е много по-интересен: добавката Solver.

Какво прави Solver на Excel?

Накратко, Solver е като a многовариантна версия на Търсене на цел . Тя отнема една целева променлива и коригира редица други променливи, докато получи отговора, който искате.

Той може да реши максимална стойност на число, минимална стойност на число или точно число.

И работи в рамките на ограничения, така че ако една променлива не може да бъде променена или може да варира само в определен диапазон, Solver ще вземе това предвид.

Това е чудесен начин за решаване на множество неизвестни променливи в Excel. Но намирането и използването му не е лесно.

Нека да разгледаме зареждането на добавката Solver, след което да преминем към начина на използване на Solver в Excel 2016.

Как да заредите добавката Solver

Excel няма Solver по подразбиране. Това е добавка, така че подобно на други мощни функции на Excel, първо трябва да я заредите. За щастие вече е на вашия компютър.

Насочете се към Файл> Опции> Добавки . След това кликнете върху Отивам до Управление: Добавки на Excel .

Ако това падащо меню казва нещо различно от „Добавки в Excel“, ще трябва да го промените:

В получения прозорец ще видите няколко опции. Уверете се, че полето до Добавка Solver се проверява и се удря Добре .

Сега ще видите Решител бутон в Анализ групата на Данни раздел:

Ако вече сте използвали Пакет от инструменти за анализ на данни , ще видите бутона Анализ на данните. Ако не, Solver ще се появи сам.

След като сте заредили добавката, нека да разгледаме как да я използваме.

Как да използвате Solver в Excel

Всяко действие на Solver има три части: целта, променливите клетки и ограниченията. Ще преминем през всяка от стъпките.

Щракнете Данни> Решител . Ще видите прозореца Parameter Solver Parameters по -долу. (Ако не виждате бутона за решаване, вижте предишния раздел за това как да заредите добавката Solver.)
Задайте клетъчната си цел и кажете на Excel целта си. Целта е в горната част на прозореца на Solver и има две части: клетката на обектива и избор за увеличаване, минимизиране или конкретна стойност. Ако изберете Макс , Excel ще коригира вашите променливи, за да получи възможно най -голям брой във вашата клетка на целта. Мин е обратното: Solver ще минимизира обективния брой. Стойност на ви позволява да посочите конкретен номер, който Solver да търси.
Изберете променливите клетки, които Excel може да променя. Променливите клетки се задават с Чрез промяна на променливи клетки поле. Щракнете върху стрелката до полето, след това щракнете и плъзнете, за да изберете клетките, с които Solver трябва да работи. Обърнете внимание, че това са всички клетки които могат да варират. Ако не искате клетката да се променя, не я избирайте.
Задайте ограничения за множество или отделни променливи. Накрая стигаме до ограниченията. Тук Solver е наистина мощен. Вместо да променяте някоя от променливите клетки на произволен брой, можете да посочите ограничения, които трябва да бъдат спазени. За подробности вижте раздела как да зададете ограничения по -долу.
След като цялата тази информация е на мястото си, натиснете Решете за да получите отговора си. Excel ще актуализира вашите данни, за да включи новите променливи (затова препоръчваме първо да създадете копие на данните си).

Можете също така да генерирате отчети, които ще разгледаме накратко в нашия пример за Solver по -долу.

Как да зададете ограничения в Solver

Може да кажете на Excel, че една променлива трябва да бъде по -голяма от 200. Когато опитвате различни стойности на променлива, Excel няма да отиде под 201 с тази конкретна променлива.

За да добавите ограничение, щракнете върху Добавяне бутон до списъка с ограничения. Ще получите нов прозорец. Изберете клетката (или клетките), която да бъде ограничена в Справка за клетка поле, след което изберете оператор.

Ето наличните оператори:

<= (по -малко или равно на)
= (равна на)
=> (по -голямо или равно на)
int (трябва да е цяло число)
съм (трябва да бъде 1 или 0)
AllDifferent

AllDifferent е малко объркващо. Той посочва, че всяка клетка в диапазона, за който сте избрали Справка за клетка трябва да е различно число. Но също така уточнява, че те трябва да са между 1 и броя на клетките. Така че, ако имате три клетки, ще завършите с числата 1, 2 и 3 (но не непременно в този ред)

Накрая добавете стойността на ограничението.

Важно е да запомните, че можете изберете няколко клетки за справка за клетка. Ако искате шест променливи да имат стойности над 10, например, можете да ги изберете всички и да кажете на Solver, че те трябва да са по -големи или равни на 11. Не е необходимо да добавяте ограничение за всяка клетка.

Можете също да използвате квадратчето за отметка в главния прозорец на Solver, за да се уверите, че всички стойности, за които не сте посочили ограничения, са отрицателни. Ако искате вашите променливи да станат отрицателни, премахнете отметката от това квадратче.

Пример за решаване

За да видим как работи всичко това, ще използваме добавката Solver, за да направим бързо изчисление. Ето данните, с които започваме:

В него имаме пет различни работни места, всяка от които плаща различна ставка. Имаме и броя часове, които теоретичен работник е работил на всяка от тези работни места за дадена седмица. Можем да използваме добавката Solver, за да разберем как да увеличим максимално общото заплащане, като същевременно поддържаме определени променливи в рамките на някои ограничения.

Ето ограниченията, които ще използваме:

Без работни места може да падне под четири часа.
Работа 2 трябва да бъде повече от осем часа .
Работа 5 трябва да бъде по -малко от единадесет часа .
Общо отработените часове трябва да бъдат равно на 40 .

Може да бъде полезно да изпишете ограниченията си по този начин, преди да използвате Solver.

Ето как бихме го настроили в Solver:

Първо, обърнете внимание, че Създадох копие на таблицата така че не презаписваме оригиналния, който съдържа текущото ни работно време.

И второ, вижте, че стойностите в ограниченията по-голямо и по-малко от са едно по -високо или по -ниско отколкото това, което споменах по -горе. Това е така, защото няма по-големи или по-малки опции. Има само по-голямо или равно на и по-малко от или равно на.

Да ударим Решете и вижте какво ще стане.

Solver намери решение! Както можете да видите вляво на прозореца по -горе, нашите приходи са се увеличили със 130 долара. И всички ограничения са изпълнени.

как да получите интернет на лаптопи

За да запазите новите стойности, уверете се Запазете Solver Solution се проверява и се удря Добре .

Ако обаче искате повече информация, можете да изберете отчет от дясната страна на прозореца. Изберете всички отчети, които искате, кажете на Excel дали искате да бъдат очертани (препоръчвам го) и натиснете Добре .

Отчетите се генерират на нови листове във вашата работна книга и ви дават информация за процеса, през който е преминала добавката Solver, за да получите отговора си.

В нашия случай докладите не са много вълнуващи и там няма много интересна информация. Но ако изпълните по -сложно уравнение на Solver, може да намерите полезна информация за отчитане в тези нови работни листове. Просто щракнете върху + бутон отстрани на всеки отчет, за да получите повече информация:

Разширени опции за решаване

Ако не знаете много за статистиката, можете да игнорирате разширените опции на Solver и просто да я стартирате такава, каквато е. Но ако провеждате големи, сложни изчисления, може да искате да ги разгледате.

Най -очевидният е методът на решаване:

Можете да избирате между GRG Nonlinear, Simplex LP и Evolutionary. Excel предоставя просто обяснение кога трябва да използвате всеки от тях. По -доброто обяснение изисква известни познания по статистика и регресия.

За да коригирате допълнителни настройки, просто натиснете бутона Настроики бутон. Можете да разкажете на Excel за целочислената оптималност, да зададете ограничения за времето на изчисление (полезно за масивни набори от данни) и да коригирате начина, по който методите за решаване на GRG и еволюционното извършват своите изчисления.

Отново, ако не знаете какво означава това, не се притеснявайте. Ако искате да знаете повече за това кой метод за решаване да използвате, Инженер Excel има добра статия, която ви го излага . Ако искате максимална точност, Evolutionary вероятно е добър начин. Просто имайте предвид, че ще отнеме много време.

Търсене и решаване на цели: Извеждане на Excel на следващото ниво

Сега, когато сте запознати с основите на решаването на неизвестни променливи в Excel, за вас е отворен изцяло нов свят на изчисляване на електронни таблици.

Goal Seek може да ви помогне да спестите време, като направите някои изчисления по -бързи, а Solver добавя огромно количество енергия към Изчислителните способности на Excel .

Въпросът е само да се чувствате комфортно с тях. Колкото повече ги използвате, толкова по -полезни ще станат.

Използвате ли търсене на цел или решаване в електронните си таблици? Какви други съвети можете да дадете, за да получите най -добрите отговори от тях? Споделете вашите мисли в коментарите по -долу!

Дял Дял Туит електронна поща 5 съвета за зареждане на вашите VirtualBox Linux машини

Уморени ли сте от лошото представяне на виртуалните машини? Ето какво трябва да направите, за да увеличите производителността на VirtualBox.

Прочетете Напред Свързани теми

Производителност
Електронна таблица
Microsoft Excel
Съвети за Microsoft Office

За автора Тогава Олбрайт(506 статии са публикувани)

Дан е консултант по стратегия за съдържание и маркетинг, който помага на компаниите да генерират търсене и потенциални клиенти. Той също така блогове за стратегия и маркетинг на съдържанието на dannalbright.com.

Още от Дан Олбрайт

Абонирайте се за нашия бюлетин

Присъединете се към нашия бюлетин за технически съвети, рецензии, безплатни електронни книги и изключителни оферти!

Щракнете тук, за да се абонирате