Решете функция в Excel. Активиране, използване на случай с екранни снимки

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

Какво е Търсене на решения

В комбинация с различни други опции в Excel има една по-малко популярна, но изключително необходима функция „Търсене на решение“. Въпреки факта, че намирането му не е лесно, опознаването и използването му помага за решаването на много проблеми. Опцията обработва данните и дава оптималното решение от разрешените. Статията описва как директно функционира Търсенето на решение.

Как да включите функцията „Търсене на решение“.

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

1. Отворете „Файл“, като щракнете върху съответното име.
2. Кликнете върху секцията „Настройки“.
3. След това изберете подраздела „Добавки“. Тук ще се покажат всички добавки на програмата, отдолу ще се появи надписът „Управление“. От дясната му страна ще има изскачащо меню, където трябва да изберете „Добавки на Excel“. След това щракнете върху „Отиди“.
   

   

4. На монитора ще се покаже допълнителен прозорец „Добавки“. Поставете отметка в квадратчето до желаната функция и щракнете върху OK.
5. Желаната функция ще се появи на лентата вдясно от секцията „Данни“.

Относно моделите

Тази информация ще бъде изключително полезна за тези, които тепърва се запознават с понятието „модел за оптимизация“. Преди да използвате „Търсене на решение“, се препоръчва да проучите материалите за методите за изграждане на модели:

• разглежданият вариант ще даде възможност да се идентифицира най-добрият метод за разпределяне на средства за инвестиции, зареждане на помещенията, доставка на стоки или други действия, при които е необходимо да се намери най-доброто решение.
• „Оптималният метод“ в такава ситуация би означавал: увеличаване на доходите, намаляване на разходите, подобряване на качеството и т.н.

Типични задачи за оптимизация:

• Определяне на производствен план, при който печалбата от продажбата на освободени стоки ще бъде максимална.
• Определяне на транспортни карти, при които транспортните разходи са сведени до минимум.
• Търсене на дистрибуция на няколко машини за различни видове работа, така че производствените разходи да бъдат намалени.
• Определяне на най-кратък срок за изпълнение на работата.

Важно! За формализиране на задачата е необходимо да се създаде модел, който отразява основните параметри на предметната област. В Excel моделът е набор от формули, които използват променливи. Разглежданият вариант търси такива показатели, че целевата функция да е по-голяма (по-малка) или равна на зададената стойност.

Подготвителен етап

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

Нашата задача ще бъде да изчислим отстъпката, по която се умножават сумите за продажба на различни продукти. Тези 2 елемента са свързани с формула, написана така: =D13*$G$2. Където в D13 е записано общото количество за внедряване, а $G$2 е адреса на желания елемент.

Използване на функцията и нейната настройка

Когато формулата е готова, трябва да използвате директно самата функция:

1. Трябва да преминете към секцията „Данни“ и да щракнете върху „Търсене на решение“.

2. Ще се отвори “Опции”, където са зададени необходимите настройки. В реда „Оптимизиране на целевата функция:“ трябва да посочите клетката, в която се показва сумата на отстъпките. Възможно е да зададете координатите сами или да изберете от документа.

3. След това трябва да отидете на настройките на други параметри. В секцията „До:“ е възможно да зададете максимален и минимален лимит или точно число.

4. След това се попълва полето „Промяна на стойностите на променливите:“. Тук се въвеждат данните от желаната клетка, която съдържа конкретна стойност. Координатите се регистрират независимо или се щраква върху съответната клетка в документа.

5. След това се редактира раздела “Съгласно ограничения:”, където се задават ограниченията върху прилаганите данни. Например десетичните дроби или отрицателните числа са изключени.

6. След това се отваря прозорец, който ви позволява да добавите ограничения в изчисленията. Първоначалният ред съдържа координатите на клетка или цял диапазон. Следвайки условията на задачата, се посочват данните от желаната клетка, където се показва индикаторът за отстъпка. След това се определя знакът за сравнение. Задава се на „по-голямо или равно на“, така че крайната стойност да не е със знак минус. „Ограничението“, зададено в ред 3, е 0 в тази ситуация. Възможно е също така да зададете лимит с „Добавяне“. Следващите стъпки са същите.

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

8. Освен това е възможно да изберете други разширени настройки. В долния десен ъгъл има опция „Опции“, която ви позволява да направите това.

9. В настройките можете да зададете „Точност на ограничението“ и „Граници на разтвора“. В нашата ситуация не е необходимо да се използват тези опции.

10. Когато настройките са завършени, се стартира самата функция – щракнете върху „Намиране на решение“.

11. След като програмата извърши необходимите изчисления и изведе окончателните изчисления в необходимите клетки. След това се отваря прозорец с резултатите, където резултатите се запазват / анулират или параметрите на търсенето се конфигурират според нов. Когато данните отговарят на изискванията, намереното решение се запазва. Ако поставите отметка в полето „Връщане към диалоговия прозорец с опции за търсене на решение“ предварително, ще се отвори прозорец с настройки на функцията.

12. Има вероятност изчисленията да се окажат грешни или е необходимо да се променят първоначалните данни, за да се получат други показатели. В такава ситуация трябва да отворите отново прозореца с настройки и да проверите отново информацията.
13. Когато данните са точни, може да се използва алтернативен метод. За тези цели трябва да кликнете върху текущата опция и да изберете най-подходящия метод от списъка, който се появява:

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

Внимание! Когато горните опции не успяха да се справят със задачата, трябва да проверите отново данните в настройките, тъй като това често е основната грешка в такива задачи.

14. Когато желаната отстъпка бъде получена, остава да я приложите, за да изчислите размера на отстъпките за всеки артикул. За целта се маркира началният елемент на колоната „Размер на отстъпката“, изписва се формулата «=D2*$G$2» и натиснете "Enter". Знаците за долар са поставени така, че когато формулата се разтегне до съседни редове, G2 не се променя.

15. Сега ще бъде получена сумата на отстъпката за първоначалния артикул. След това трябва да преместите курсора над ъгъла на клетката, когато стане „плюс“, LMB се натиска и формулата се разтяга до необходимите редове.
16. След това масата ще бъде окончателно готова.

Опции за зареждане/запазване на търсенето

Тази опция е полезна при прилагане на различни опции за ограничения.

1. В менюто Опции на Finder за решения щракнете върху Зареждане/Запазване.
2. Въведете диапазона за областта на модела и щракнете върху Запазване или Зареждане.

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

Важно! За да запазите последните настройки в менюто с опции за решение, се записва работна книга. Всеки лист в него има свои собствени опции за добавяне на Solver. Освен това е възможно да зададете повече от 1 задача за лист, като щракнете върху бутона „Зареждане или запазване“, за да запазите отделни задачи.

Прост пример за използване на Solver

Необходимо е контейнерът да се зареди с контейнери, така че масата му да е максимална. Резервоарът е с обем 32 куб.м. м. Напълнената кутия е с тегло 20 кг, обемът й е 0,15 куб.м. м. Кашон – 80 кг и 0,5 куб. м. Изисква се общият брой на контейнерите да е минимум 110 бр. Данните са организирани по следния начин:

Променливите на модела са маркирани в зелено. Целевата функция е подчертана в червено. Ограничения: по най-малък брой контейнери (по-голям или равен на 110) и по тегло (=SUMPRODUCT(B8:C8;B6:C6) – общо тегло на тара в контейнера.

По аналогия разглеждаме общия обем: =SUMPRODUCT(B7:C7;B8:C8). Такава формула е необходима, за да се определи лимит на общия обем на контейнерите. След това чрез „Търсене на решение“ се въвеждат връзки към елементи с променливи, формули и самите индикатори (или връзки към конкретни клетки). Разбира се, броят на контейнерите е цяло число (също е ограничение). Натискаме „Намери решение“, в резултат на което намираме такъв брой контейнери, когато общата маса е максимална и всички ограничения са взети предвид.

Търсенето на решение не успя да намери решения

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

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

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

Във всяка ситуация трябва първоначално да проверите модела за липса на конфликт на ограничения. Често това е свързано с неправилен избор на коефициент или ограничителен индикатор.

В горния пример индикаторът за максимален обем е 16 кубически метра. м вместо 32, тъй като такова ограничение противоречи на показателите за минимален брой места, тъй като ще съответства на броя от 16,5 куб.м. м.

Заключение

Въз основа на това опцията „Търсене на решение“ в Excel ще помогне при решаването на конкретни проблеми, които са доста трудни или невъзможни за решаване по обичайните начини. Трудността при прилагането на този метод е, че първоначално тази опция е скрита, поради което повечето потребители не знаят за нейното присъствие. Освен това функцията е доста трудна за научаване и използване, но с правилно проучване ще донесе големи ползи и ще улесни изчисленията.