Когато работите с електронни таблици, е много често срещано да знаете желания резултат, но да нямате представа какво число да въведете в клетка, за да го постигнете. Вместо ръчно да опитвате стойности като игра на отгатване, можете да оставите Excel и други електронни таблици да свършат работата вместо вас с инструмента. Търсене на цел.
Тази функция е като да кажете на електронната таблица: „Искам този краен резултат„Вие преценявате каква стойност трябва да има тази клетка, за да постигне това.“ Това е много удобен начин да решаване на уравнения с едно неизвестно приложени към реални случаи: заеми, оценки от изпити, печалби, бюджети или бизнес анализ.
Какво точно е търсенето на цели в електронните таблици?
Инструментът Търсене на цел Това е част от функциите на Анализ „Ами ако...“ които включват Excel и други редактори като ONLYOFFICE. За разлика от обикновените формули, които изчисляват резултат от входни данни, Goal Seek работи по другия начинКазвате му желания резултат и електронната таблица изчислява каква стойност трябва да приеме една от клетките, така че формулата да отговаря на това условие.
С други думи, служи за създаване на формула, която вече съществува приемат специфична стойност чрез автоматично коригиране на една променлива. Това избягва типичния метод на опит и неуспехкъдето променяте число, гледате резултата, променяте го отново и така нататък, докато не го получите правилно.
Ключови понятия: целева клетка и променяща се клетка
За да използвате Goal Seek без да се обърквате, е важно да разберете два основни елемента: целева клетка и клетка, която се променяДоброто разбиране на тези две понятия ще ви спести много главоболия.
La целева клетка е този, който съдържа формула, чийто резултат искате да коригиратеТова не е просто някоя клетка; тя винаги трябва да съдържа формула: средна стойност, финансова функция, SUMPRODUCT, изчисление на печалбата и т.н. Това е мястото, където искате да се появи крайната стойност, която сте решили, като например минимална средна оценка, месечно плащане, желана печалба или специфичен лихвен процент.
La клетка, която се променя е неизвестенКлетката, чиято стойност ще промени инструментът, за да постигне заявения от вас резултат. Тази клетка трябва да е част от формулата в целевата клетка; ако формулата не я препраща, „Търсене на цел“ няма да има начин да повлияе на резултата.
Накрая ще трябва да посочите желаната стойностТова е конкретното число, което искате да постигнете в целевата клетка: крайна оценка 50, средна стойност 70, коефициент -900, печалба от 50 000 и т.н. Вие определяте тази стойност и тя е фокусът на целия анализ.
Как да използвате „Търсене на цели“: подробен преглед
Преди да се впуснем в конкретни примери, си струва да разгледаме общ потокзащото се повтаря почти по един и същ начин във всяка ситуация, както в Excel, така и в ONLYOFFICE и други съвместими редактори.
Най-общо казано, типичното използване на Goal Seek следва следната последователност: Първо настройвате електронната таблица и формулитеСлед това отваряте инструмента, дефинирате целевата клетка, стойността, която трябва да бъде достигната, и клетката, която ще се промени, и оставяте програмата да намери решението.
El стандартен маршрут би се:
- Подгответе даннитеВъвеждате клетките със стойности, които вече знаете (сума на заема, брой месеци, съществуващи бележки, разходи, цени и др.) и ако е необходимо, можете премахване на дубликати.
- Създайте формулата за резултата: В целевата клетка записвате формулата, която изчислява резултата, който искате да контролирате (средна стойност, PAY/PMT, печалба, SUMPRODUCT и др.).
- Отворено търсене на целиОт раздела „Данни“, в групата „Анализ на хипотези“ или „Анализ на резултати“, можете да получите достъп до „Търсене на цел“.
- Попълнете трите параметъраВие посочвате целевата клетка, стойността, която трябва да се постигне, и клетката, която се променя.
- Изпълнявай и приемайПрограмата изчислява последователни приближения, докато не се намери решение, показва ви поле със състоянието на търсенето и можете да приемете, така че новите стойности да останат в листа.
Този модел се повтаря във всички сценарии: единственото нещо, което се променя, е включени формули и коя клетка решите да използвате като неизвестна.
Намиране на цели с оценки: Как да разберете каква оценка трябва да получите
Друго много често срещано използване на търсенето на цел е да изчислете каква степен ви е необходима в предстоящ изпит, за да се постигне определена крайна средна оценка. Това е много поучителен, прост случай, идеален за разбиране как инструментът работи в обратен ред.
Идеята е проста: вече имате някои оценки, пропускате един последен тест и искате да знаете какъв минимален резултат ви е необходим, за да достигне крайната ви средна стойност определена стойност, независимо дали средната стойност е проста или... претеглени.
Средностатистическа стойност на няколко оценки
Представете си, че имате четири теста по даден предмет. Завършили сте три, имате оценките си и остава само финалният изпит. Знаете, че ви е необходим крайна оценка от 50 да вземете курса и искате Excel да ви каже какъв резултат ви е необходим на този последен изпит.
Първо сглобявате дъска с известни оценки и оставете празна клетка за липсващата оценка. В друга клетка изчислявате крайната оценка като осреднена стойност, например с =СРЕДНО(диапазон_на_оценки) или =СРЕДНО, ако използвате функцията English. Тази клетка със средна стойност ще бъде вашата целева клетка.
След това отворете „Търсене на цели“ от „Данни“ > „Какво-ако“ анализ > „Търсене на цели“ и конфигурирайте параметрите. Задаване на клетка Избирате средната клетка (например C6). След това, в Със смелостта Вие пишете целта, която в този случай би била 50. Промяна на клетката Избирате клетката за чакащата бележка, например C5, която Excel ще коригира автоматично.
След като щракнете върху OK, инструментът изчислява оценката от изпита, необходима за средната стойност да бъде точно 50. Excel ще покаже в полето за състояние на търсенето дали е намерено решение и ако е така, ще въведе минималната оценка, от която се нуждаете, в клетката за чакаща оценка, например 60%.
Претеглени оценки с помощта на SUMPRODUCT
Ако искате да отидете още една крачка напред, можете да приложите Goal Seek към система за оценяване. претегленикъдето всеки тест има различна тежест (например, задачи, тестове, финален изпит и др.). В този случай крайната оценка не е проста средна стойност, а среднопретеглена.
Това обикновено се прави с помощта на функцията СУМА ПРОИЗВОДТази формула умножава всяка оценка по нейното тегло и сумира произведенията. Делението на сумата от теглата ви дава крайната среднопретеглена оценка. Това е формулата, която ще въведете в... целева клетка.
Процедурата би била следната: в една колона изброявате различните дейности (тест 1, тест 2, задание, изпит и т.н.), в друга колона оценките, а в трета процентите или теглата. Формулата за крайната оценка може да бъде нещо подобно: =SUMPRODUCT(диапазон_от_степени;диапазон_от_тегла)/SUM(диапазон_от_тегла)Тази клетка със SUMPRODUCT и SUM ще бъде тази, която ще използвате като цел.
Когато отворите „Търсене на цел“, в „Задаване на клетка“ избирате клетката, съдържаща крайната претеглена оценка; в „Със стойност“ въвеждате минималната стойност, която искате да постигнете (например 50); и в „Променяща се клетка“ отново указвате оценка от изпита, което е единствената неизвестна стойност. След като изпълни търсенето, програмата ще ви каже каква оценка трябва да има финалният изпит, например 57,5%така че средната стойност в световен мащаб да достигне 50.
Използване на търсене на цел в ONLYOFFICE Docs 8.0
Excel не е единствената компания, която използва този инструмент; редактори като Документи на ONLYOFFICE Те също така са добавили функцията за търсене на цели към своите електронни таблици, която работи по много подобен начин, идеална, ако работите в съвместна или облачна среда.
Работният процес в ONLYOFFICE е почти същият: първо Въведете вашите данни и формулата, която свързва тези данни с резултата, който искате да контролирате, и можете да използвате помощни функции като автодовършванеНапример, ако искате да изчислите минималната оценка за преминаване, можете да въведете оценките в диапазон и да използвате функцията = СРЕДНА (B2: B5) в клетка с резултат, да речем B7. Тази клетка B7 ще бъде целевата клетка.
След това отивате в раздела Данни и кликваш върху иконата Търсене на целОтваря се диалогов прозорец с три полета, много подобни на тези в Excel. Задаване на клетка Избирате клетката, съдържаща формулата (B7); в доблест Записвате желаната средна стойност (например 70); и в При смяна на клетката Избирате клетката на липсващата бележка (например B5), която е тази, която листът ще промени.
Когато щракнете върху OK, ONLYOFFICE изчислява стойността, която неизвестната оценка трябва да има, за да доведе средната стойност до 70. Прозорецът за състояние на търсенето на цел ще покаже резултата; ако го приемете, засегнатите клетки ще бъдат актуализирани. В дадения пример системата може да ви каже, че се нуждаете от 85 на последния изпит, за да се постигне този минимален среден успех.
Същият механизъм се прилага и за други случаи: амортизация на заеми, инвестиционно планиране, фиксирани бюджети, където искате да коригирате конкретна категория, или анализ на точката на безубыточност в продажбите. Стига да имате една неизвестна променлива и формула, която го свързва с вашата цел, Търсенето на цел е напълно приложимо.
Кога да използвате Goal Seek и кога да преминете към Solver
Въпреки че търсенето на цели е изключително полезно, то има едно съществено ограничение: позволява ви само да коригирате променлива клетка за да постигнете резултата. Ако проблемът ви включва няколко неизвестни или сложни ограничения, той ще бъде неуспешен и ще трябва да прибегнете до по-усъвършенствани инструменти, като например Решител.
Solver е добавка за Excel (и други пакети), която разширява анализа „Какво ако...“, като позволява максимизирайте или минимизирайте целева клетка, модифицирайте няколко клетки едновременно и добавете ограничения (минимуми, максимуми, взаимоотношения между клетките и др.). Идеален е за оптимизиране на печалбата, разпределение на ресурси или линейно програмиране.
Например, ако искате да разберете какви бележки са ви необходими два различни изпита (Английски език и химия) За да получите средна стойност 84, търсенето на цел вече не е полезно, защото имате две променливи. Solver ви позволява да зададете клетката със средна стойност (със стойност 84) като цел, да промените клетките и за двата предмета и да намерите възможна комбинация, например получаване на 87 във всеки.
В бизнес среда можете да използвате Solver, за да максимизиране на печалбата При множество продадени сервизни линии, където едновременно трябва да се спазват минимален брой продадени единици за една услуга, максимален лимит за друга и общ лимит на продажбите. В тези случаи бихте задали общата печалба като целева клетка, избрали продадените количества като променливи и дефинирали ограниченията, оставяйки Solver да намери най-добрата комбинация.
Търсенето на цел е идеално за задачи с една променлива (една променяща се клетка) и относително прости формули, докато Solver поема контрола, когато има множество променливи и едновременни ограничения.
Често срещани проблеми: Защо търсенето на цел може да не работи добре
Въпреки че инструментът обикновено работи много надеждно, има моменти, когато Goal Seek сякаш „се проваля“ или не успява да намери решение. Проблемът обикновено не е в програмата, а в... конфигурация или в самата формула.
Една от първите точки за преглед е корекции на итерациите Excel, например, ограничава максималния брой итерации и максималната допустима промяна между приближенията, за да предотврати безкрайни изчисления. Ако броят на итерациите е твърде нисък или максималната промяна е твърде голяма, инструментът може да спре преждевременно или да не успее да постигне очакваната точност.
В Excel можете да прегледате тези параметри от Файл > Опции > ФормулиТам можете да увеличите максималния брой итерации, за да позволите повече опити за търсене, или да намалите максималната промяна, за да постигнете по-голяма точност. Коригирането на тези стойности често подобрява резултатите при по-деликатни проблеми.
Друга много често срещана грешка е, че формула на целевата клетка Не включвайте клетката, която искате да промените. Ако клетката, която искате да промените, не е част от формулата, независимо колко много електронната таблица я манипулира, това няма да окаже влияние върху резултата. Също така трябва да се уверите, че формулата е добре написана и не генерира кръгови препратки, тъй като това може да блокира изчислението или да доведе до неуспех на търсенето на цел.
Овладяването на търсенето на цели в Excel и други електронни таблици е бърз начин да преминете от догадки към много по-сериозни числени анализи Без да е необходимо да усложнявате нещата с висша математика: просто имайте ясна формула за резултата, решете коя променлива искате да коригирате и използвайте инструмента, за да свърши трудната работа вместо вас, независимо дали става въпрос за личните ви финанси, обучението ви или ежедневното управление на бизнеса ви.
