Избор на параметър в Excel, решаваме нерешаващи задачи

Здравейте скъпи читатели! В последната статия научихме как да моделираме резултата с различни входни параметри чрез извършване на анализ какво-ако. Днес ще анализираме обратната задача, не по-малко често срещана, сложна и неотложна. Кажете ни резултата и ние трябва да знаем какви трябва да бъдат входните стойности, за да го получим. Тоест, трябва да намерите решение на проблема. Възможно ли е в Excel? Разбира се, че е възможно, нека разберем!

Програмата ни предоставя два начина за решаване на такъв проблем:

1. Инструмент за търсене
2. Инструмент за решаване

Избор на параметър в Excel

Това е прост инструмент, който наистина помага в много случаи. Той променя стойността в една клетка, за да получи конкретен резултат в друга. Как работи?

Да вземем един прост пример. Планираме да отворим депозит с месечно попълване. Сега имаме 10 хиляди USD на ръка, но след изтичане на срока на депозита, след 12 месеца, искаме да имаме капитал от 20 хиляди. Изисква се да изчислите колко трябва да депозирате на месечна база, за да натрупате сума от 20 хиляди USD за 12 месеца.

Ето нашата електронна таблица:

В жълтата таблица първоначалните данни: първа вноска, месечна вноска, лихвен процент и срок на депозита. В синьото поле е крайният капитал, който изчислихме чрез функцията PV.

Всъщност трябва да вземем такава стойност в клетка B3, така че B7 да стане 20 000. Използваме инструмента „Избор на параметри“:

1. Кликнете върху лентата Данни - Работа с данни - Анализ "какво ако" - избор на параметър;
2. В прозореца, който се отваря, задайте данните за настройките:
3. Задайте в клетка : в товапараметър, посочваме връзка към нашата целева стойност, т.е. "Краен капитал";
4. Стойност : тук трябва да посочите стойността, която трябва да бъде в целевата клетка, т.е. желан резултат от изчислението. В нашия случай това е 20 000;
5. Промяна на стойността на клетка : Посочете препратка към клетката, чиято стойност трябва да бъде променена, за да изберете резултата. В нашия пример това е „Месечна вноска“;

1. Щракнете върху OK, програмата ще търси решение. Когато бъде намерен, Excel ще отчете завършването на селекцията. Щракнете върху OK в прозореца, за да приемете намерената стойност и да я запишете в клетката, или Отказ, за ​​да оставите всичко както си беше.

В нашия пример всичко работи перфектно и научихме, че за да получите капитал от 20 хиляди, трябва да добавяте $736,55 към депозита всеки месец.

Понякога се случва търсенето на решение да не даде резултат, тогава трябва да проверите дали всичко е правилно:

1. Първосе уверете, че целевата клетка зависи от стойността, която променяме. Ако крайната формула не се отнася до стойност на променлива, възстановете тази зависимост и повторете търсенето;
2. Опитвамеда поставим стойност, по-близка до желаната в клетката, която се променя, много често това помага;
3. В Excel броят на итерациите за такова търсене е ограничен. Може би този брой не беше достатъчен, за да се намери решение. Опит заувеличаване на броя повторения. За да направите това, щракнете върху Файл - Опции - Формули и там в групата команди "Опции за изчисление" увеличете ограничения брой повторения.

1. Помислете за изчисленията, които предлагате да направите в програмата.Посочените от вас параметри имат ли точно решение ? Ако не го направят, накарайте ги да се коригират.

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

Инструмент за решаване

Както видяхте, изборът на параметър работи перфектно и безупречно в почти всички случаи. Но има недостатък - манипулира само една стойност, за да промени резултата. Но какво ще стане, ако трябва да изградите по-сложна система от изчисления? След това използваме "Търсене на решение".

Нека отново да разгледаме един пример. Ще планираме производствения процес за един месец, за да увеличим максимално печалбите. Ето нашия работен лист:

Таблицата има следните полета:

1. Минимална партида – минималното количество стоки, които трябва да бъдат произведени за обслужване на съществуващи поръчки;
2. Максимална партида – най-голямото количество стоки, което може да бъде произведено въз основа на запасите от суровини
3. Работни часове – броят човекочасове, необходими за производството на едно изделие;
4. Разходи за работно време - количеството време, което ще бъде изразходвано за производството на всички планирани. Нека имаме 20 работници за 8 часа 22 дни в месеца. Тогава сумата за това поле трябва да бъде 3520 часа.
5. Разходи - разходите за производство на една единица продукт
6. Продажна цена – пазарната стойност на една единица продукция
7. Брутна печалба – печалба, която ще бъде получена от продажбата на произведени стоки.

За простота ще приемем, че търсенето на даден продукт е по-високо от производствените възможности и всичко произведено ще бъде продадено. И така, колко от това, което трябва да произведем, за да получим най-голяма полза, и персоналът е работил точно 3520 часа? Започваме "Търсене на решение":

1. Търсим на лентата Данни - Анализ - Търсенерешения . Щракнете, прозорецът с настройки ще се отвори;
2. В полето "Оптимизиране на целевата функция" задайте връзка към сумата в колона "Брутна печалба";
3. В полето „До“ изберете „Максимум“. В други случаи можете да изберете "минимум" или да зададете конкретна стойност;
4. В списъка „Промяна на променливи клетки“ посочете всички редове от колоната „Производство“
5. След това трябва да направите всички горепосочени ограничения. За да направите това, щракнете върху „Добавяне“ и в прозореца, който се отваря, изберете връзките към клетките и параметрите за тяхното ограничаване:

Въвеждаме всички договорени ограничения, те ще бъдат показани в списъка на прозореца за настройки:

1. Общото прекарано време трябва да бъде 3520 часа;
2. Количеството за производство е по-голямо или равно на минималната партида
3. Произведеното количество е по-малко или равно на максималната партида
4. Количеството, което трябва да бъде произведено, трябва да бъде цяло число

1. Избираме метода на решение в съответствие с препоръките на разработчиците в долната част на прозореца за настройки. Ще изберем линейния метод. Кликнете върху „Намерете решение“, след приключване на търсенето програмата отчита резултата.

В този прозорец можете да потвърдите промените в клетките или да се върнете към оригиналния набор от данни. Освен това чудесно решение би било да запазите резултатите от търсенето като скрипт. По-късно те могат да бъдат възстановени и да бъдат изградени съответните отчети.

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

Като експериментирате с многобройните настройки на инструмента, можете да контролирате процеса на търсене в детайли. Всъщност "Търсене на решение" е много функционален имногостранна добавка, можете да научите всички основни неща на уебсайта на разработчиците: www.solver.com.

Между другото, ако не сте намерили този инструмент на лентата, не се отчайвайте, просто трябва да го свържете. За да направите това, щракнете върху Файл - Опции - Добавки. В долната част от падащия списък Управление изберете Добавки на Excel и щракнете върху Старт. В прозореца, който се отваря, поставете отметка в квадратчето до „Търсене на решение“ и щракнете върху OK. Това е всичко, веднага ще се появи в емисията!

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

---