Kā izmantot Excel mērķu meklētāju un risinātāju, lai atrisinātu nezināmus mainīgos

Excel ir ļoti spējīgs, ja jums ir visi aprēķiniem nepieciešamie dati.

Bet vai nebūtu jauki, ja varētu atrisināt nezināmus mainīgos ?

Izmantojot mērķu meklēšanu un risinātāja papildinājumu, tas var notikt. Un mēs jums parādīsim, kā. Lasiet tālāk, lai iegūtu pilnu rokasgrāmatu par to, kā atrisināt vienu šūnu ar mērķa meklēšanu vai sarežģītāku vienādojumu ar risinātāju.

Kā programmā Excel izmantot mērķa meklēšanu

Mērķa meklēšana jau ir iebūvēta programmā Excel. Tas atrodas zem Dati cilnē Ko darīt, ja analīze izvēlne:

Šajā piemērā mēs izmantosim ļoti vienkāršu skaitļu kopu. Mums ir trīs ceturtdaļas pārdošanas apjomi un gada mērķis. Mēs varam izmantot mērķa meklēšanu, lai noskaidrotu, kādiem skaitļiem jābūt ceturtajā ceturksnī, lai sasniegtu mērķi.

Kā redzat, pašreizējais pārdošanas apjoms ir 114 706 vienības. Ja mēs vēlamies līdz gada beigām pārdot 250 000, cik mums ir jāpārdod ceturtajā ceturksnī? Excel mērķu meklēšana mums to pateiks.

Tālāk ir norādīts, kā soli pa solim izmantot mērķa meklēšanu.

Klikšķis Dati> Ko darīt, ja analīze> Mērķa meklēšana . Jūs redzēsit šo logu:
Ievietojiet vienādojuma daļu “vienāds” ar Iestatīt šūnu lauks. Šo skaitli Excel mēģinās optimizēt. Mūsu gadījumā tas ir mūsu pārdošanas skaitļu kopējais rādītājs šūnā B5.
Ierakstiet mērķa vērtību Novērtēt lauks. Mēs meklējam kopā pārdotas 250 000 vienības, tāpēc šajā laukā ierakstīsim “250 000”.
Pastāstiet programmai Excel, kurš mainīgais jāatrisina Mainot šūnu lauks. Mēs vēlamies redzēt, kādiem jābūt mūsu pārdošanas apjomiem ceturtajā ceturksnī. Tāpēc mēs teiksim Excel, lai atrisinātu šūnu D2. Kad tas būs gatavs lietošanai, tas izskatīsies šādi:
Sist labi lai atrisinātu savu mērķi. Kad tas izskatās labi, vienkārši sit labi . Excel jums paziņos, kad mērķa meklēšana ir atradusi risinājumu.
Klikšķis labi vēlreiz, un jūs redzēsiet vērtību, kas atrisina jūsu vienādojumu izvēlētajā šūnā Mainot šūnu .

Mūsu gadījumā risinājums ir 135 294 vienības. Protams, mēs to varējām vienkārši konstatēt, no gada mērķa atņemot tekošo kopsummu. Bet mērķa meklēšanu var izmantot arī šūnā, kas tajā jau ir dati . Un tas ir noderīgāk.

Ņemiet vērā, ka programma Excel pārraksta mūsu iepriekšējos datus. Tā ir laba ideja palaidiet mērķa meklēšanu savu datu kopijā . Ieteicams arī atzīmēt savus kopētos datus, ka tie ir ģenerēti, izmantojot mērķi. Jūs nevēlaties to sajaukt ar pašreizējiem, precīziem datiem.

labākais formāts Mac ārējam cietajam diskam

Tātad mērķa meklēšana ir a noderīga Excel funkcija , bet tas viss nav tik iespaidīgi. Apskatīsim rīku, kas ir daudz interesantāks: pievienojumprogramma Solver.

Ko dara Excel risinātājs?

Īsāk sakot, Solver ir kā a Mērķa meklēšanas daudzfaktoru versija . Tas aizņem vienu mērķa mainīgo un pielāgo vairākus citus mainīgos, līdz iegūst vēlamo atbildi.

Tas var atrisināt skaitļa maksimālo vērtību, skaitļa minimālo vērtību vai precīzu skaitli.

Un tas darbojas ierobežojumu ietvaros, tādēļ, ja vienu mainīgo nevar mainīt vai tas var mainīties tikai noteiktā diapazonā, Solver to ņems vērā.

Tas ir lielisks veids, kā atrisināt vairākus nezināmus mainīgos programmā Excel. Bet to atrast un izmantot nav vienkārši.

Apskatīsim pievienojumprogrammas Solver ielādi, pēc tam pāriesim pie risinātāja izmantošanas programmā Excel 2016.

Kā ielādēt risinātāja pievienojumprogrammu

Programmā Excel pēc noklusējuma nav risinātāja. Tā ir pievienojumprogramma, tāpēc, tāpat kā citas jaudīgas Excel funkcijas, tā vispirms ir jāielādē. Par laimi, tas jau ir jūsu datorā.

Doties uz Fails> Iespējas> Papildinājumi . Pēc tam noklikšķiniet uz Iet blakus Pārvaldīt: Excel pievienojumprogrammas .

Ja šajā nolaižamajā izvēlnē ir teikts kas cits, nevis “Excel pievienojumprogrammas”, tas ir jāmaina:

Parādītajā logā redzēsit dažas iespējas. Pārliecinieties, vai lodziņš atrodas blakus Risinātāja pievienojumprogramma tiek pārbaudīts un trāpīts labi .

Tagad jūs redzēsit Risinātājs pogu Analīze grupa Dati cilne:

Ja jūs jau esat izmantojis Datu analīzes rīkkopa , jūs redzēsiet pogu Datu analīze. Ja nē, Solver parādīsies pats.

Tagad, kad esat ielādējis pievienojumprogrammu, apskatīsim, kā to izmantot.

Kā lietot risinātāju programmā Excel

Jebkurai risinātāja darbībai ir trīs daļas: mērķis, mainīgās šūnas un ierobežojumi. Mēs iesim cauri katram no soļiem.

Klikšķis Dati> Risinātājs . Tālāk redzēsit risinātāja parametru logu. (Ja neredzat risinātāja pogu, skatiet iepriekšējo sadaļu par risinātāja pievienojumprogrammas ielādi.)
Iestatiet šūnas mērķi un norādiet programmai Excel savu mērķi. Mērķis atrodas risinātāja loga augšdaļā, un tam ir divas daļas: mērķa šūna un iespēja palielināt, samazināt vai noteikt vērtību. Ja izvēlaties Maks , Excel pielāgos jūsu mainīgos, lai jūsu mērķa šūnā iegūtu pēc iespējas lielāku skaitu. Min ir pretējs: risinātājs samazina mērķa skaitli. Vērtība ļauj norādīt konkrētu risinātāja meklējamo numuru.
Izvēlieties mainīgās šūnas, kuras Excel var mainīt. Mainīgās šūnas ir iestatītas ar Mainot mainīgās šūnas lauks. Noklikšķiniet uz bultiņas blakus laukam, pēc tam noklikšķiniet un velciet, lai atlasītu šūnas, ar kurām risinātājam vajadzētu strādāt. Ņemiet vērā, ka tie ir visas šūnas kas var atšķirties. Ja nevēlaties, lai šūna mainītos, neatlasiet to.
Iestatiet ierobežojumus vairākiem vai atsevišķiem mainīgajiem. Visbeidzot, mēs nonākam pie ierobežojumiem. Šeit Solver ir patiešām spēcīgs. Tā vietā, lai mainītu jebkuru mainīgo šūnu uz jebkuru vajadzīgo skaitli, varat norādīt ierobežojumus, kas jāievēro. Sīkāku informāciju skatiet tālāk sadaļā par ierobežojumu iestatīšanu.
Kad visa šī informācija ir ievietota, nospiediet Atrisiniet lai saņemtu savu atbildi. Programma Excel atjauninās jūsu datus, lai iekļautu jaunos mainīgos (tāpēc mēs iesakām vispirms izveidot savu datu kopiju).

Varat arī ģenerēt pārskatus, kurus mēs īsumā apskatīsim mūsu risinātāja piemērā.

Kā noteikt ierobežojumus risinātājā

Jūs varētu pateikt programmai Excel, ka vienam mainīgajam ir jābūt lielākam par 200. Mēģinot izmantot dažādas mainīgo vērtības, Excel ar šo mainīgo nepārsniegs 201.

Lai pievienotu ierobežojumu, noklikšķiniet uz Pievienot pogu blakus ierobežojumu sarakstam. Jūs iegūsit jaunu logu. Atlasiet šūnu (vai šūnas), kas jāierobežo Šūnu atsauce laukā, pēc tam izvēlieties operatoru.

Šeit ir pieejami operatori:

<= (mazāks vai vienāds ar)
= (vienāds ar)
=> (lielāks vai vienāds ar)
int (jābūt veselam skaitlim)
esmu (jābūt 1 vai 0)
AllDifferent

AllDifferent ir nedaudz mulsinoši. Tas norāda, ka katra jūsu atlasītā diapazona šūna Šūnu atsauce jābūt citam skaitlim. Bet tas arī nosaka, ka tiem jābūt no 1 līdz šūnu skaitam. Tātad, ja jums ir trīs šūnas, jūs galu galā saņemsit skaitļus 1, 2 un 3 (bet ne obligāti šādā secībā)

Visbeidzot, pievienojiet ierobežojuma vērtību.

Ir svarīgi atcerēties, ka jūs varat atlasiet vairākas šūnas šūnu atsaucei. Ja vēlaties, lai, piemēram, sešiem mainīgajiem būtu vērtības virs 10, varat tos visus atlasīt un pateikt risinātājam, ka tiem jābūt lielākiem vai vienādiem ar 11. Katrai šūnai nav jāpievieno ierobežojums.

Varat arī izmantot izvēles rūtiņu galvenajā risinātāja logā, lai pārliecinātos, ka visas vērtības, kurām neesat norādījis ierobežojumus, nav negatīvas. Ja vēlaties, lai mainīgie būtu negatīvi, noņemiet atzīmi no šīs izvēles rūtiņas.

Risinātāja piemērs

Lai redzētu, kā tas viss darbojas, mēs izmantosim risinājumu Solver, lai veiktu ātru aprēķinu. Šeit ir dati, ar kuriem mēs sākam:

Tajā mums ir pieci dažādi darbi, no kuriem katrs maksā atšķirīgu likmi. Mums ir arī stundu skaits, ko teorētiskais darbinieks ir strādājis katrā no šiem darbiem noteiktā nedēļā. Mēs varam izmantot pievienojumprogrammu Solver, lai uzzinātu, kā maksimāli palielināt kopējo samaksu, vienlaikus ievērojot noteiktus mainīgos lielumus noteiktos ierobežojumos.

Šeit ir minētie ierobežojumi:

Nav darba var nokrist zem četrām stundām.
2. darbam jābūt ilgāk par astoņām stundām .
5. darbam jābūt mazāk nekā vienpadsmit stundas .
Kopējam nostrādātajam laikam jābūt vienāds ar 40 .

Pirms risinātāja izmantošanas var būt noderīgi uzrakstīt šādus ierobežojumus.

Lūk, kā mēs to iestatījām Solver:

Pirmkārt, ņemiet vērā, ka Esmu izveidojis tabulas kopiju tāpēc mēs nepārrakstām sākotnējo, kurā ir mūsu pašreizējais darba laiks.

Otrkārt, redziet, ka vērtības ir lielākas par un mazākas par ierobežojumiem vienu augstāku vai zemāku nekā tas, ko es minēju iepriekš. Tas ir tāpēc, ka nav lielākas vai mazākas iespējas. Ir tikai lielāki par vai vienādi un mazāki par vai vienādi ar.

Iesitīsim Atrisiniet un redzēt, kas notiek.

Risinātājs atrada risinājumu! Kā redzat loga kreisajā pusē, mūsu ieņēmumi ir palielinājušies par 130 ASV dolāriem. Un visi ierobežojumi ir izpildīti.

kas ir ierobežota sērija vietnē Netflix

Lai saglabātu jaunās vērtības, pārliecinieties Saglabājiet risinātāja risinājumu tiek pārbaudīts un iesists labi .

Tomēr, ja vēlaties iegūt vairāk informācijas, varat atlasīt pārskatu loga labajā pusē. Atlasiet visus vēlamos pārskatus, pastāstiet programmai Excel, vai vēlaties tos izklāstīt (es to iesaku), un nospiediet labi .

Pārskati tiek ģenerēti jaunās darbgrāmatas lapās, un tie sniedz informāciju par procesu, ko risinātāja pievienojumprogramma veica, lai saņemtu jūsu atbildi.

Mūsu gadījumā ziņojumi nav ļoti aizraujoši, un tur nav daudz interesantas informācijas. Bet, ja palaižat sarežģītāku risinātāja vienādojumu, šajās jaunajās darblapās varat atrast noderīgu pārskatu informāciju. Vienkārši noklikšķiniet uz + poga jebkura pārskata malā, lai iegūtu vairāk informācijas:

Risinātāja papildu opcijas

Ja jūs daudz nezināt par statistiku, varat ignorēt Solver uzlabotās iespējas un vienkārši palaist to tādu, kāda tā ir. Bet, ja veicat lielus un sarežģītus aprēķinus, iespējams, vēlēsities tos izpētīt.

Visredzamākā ir risināšanas metode:

Jūs varat izvēlēties starp GRG nelineāru, Simplex LP un Evolutionary. Excel sniedz vienkāršu skaidrojumu par to, kad jums vajadzētu izmantot katru no tiem. Labākam skaidrojumam ir vajadzīgas zināmas statistikas un regresijas zināšanas.

Lai pielāgotu papildu iestatījumus, vienkārši nospiediet pogu Iespējas pogu. Varat pastāstīt programmai Excel par veselu skaitļu optimitāti, iestatīt aprēķina laika ierobežojumus (noder masīvām datu kopām) un pielāgot to, kā GRG un Evolutionary risinājumu metodes veic aprēķinus.

Atkal, ja jūs nezināt, ko tas nozīmē, neuztraucieties par to. Ja vēlaties uzzināt vairāk par to, kādu risinājuma metodi izmantot, programmā Engineer Excel ir labs raksts, kas to izklāsta jums . Ja vēlaties maksimālu precizitāti, iespējams, Evolutionary ir labs risinājums. Vienkārši ņemiet vērā, ka tas prasīs ilgu laiku.

Mērķa meklētājs un risinātājs: Excel pārcelšana uz nākamo līmeni

Tagad, kad esat apmierināts ar nezināmu mainīgo risināšanas pamatiem programmā Excel, jums ir pieejama pilnīgi jauna izklājlapu aprēķinu pasaule.

Mērķa meklēšana var palīdzēt ietaupīt laiku, ātrāk veicot dažus aprēķinus, un Solver tam pievieno milzīgu jaudu Excel aprēķināšanas spējas .

Tas ir tikai jautājums, kā ar viņiem justies ērti. Jo vairāk jūs tos izmantosit, jo noderīgāki tie kļūs.

Vai izklājlapās izmantojat mērķu meklēšanu vai risinātāju? Kādus citus padomus jūs varat sniegt, lai no tiem iegūtu vislabākās atbildes? Kopīgojiet savas domas zemāk esošajos komentāros!

Kopīgot Kopīgot Čivināt E -pasts 5 padomi, kā uzlādēt VirtualBox Linux iekārtas

Vai esat noguris no virtuālo mašīnu piedāvātās sliktās veiktspējas? Lūk, kas jums jādara, lai uzlabotu VirtualBox veiktspēju.

Lasīt Tālāk Saistītās tēmas

Produktivitāte
Izklājlapa
Microsoft Excel
Microsoft Office padomi

Par autoru Tad Olbraits(Publicēti 506 raksti)

Dann ir satura stratēģijas un mārketinga konsultants, kurš palīdz uzņēmumiem radīt pieprasījumu un potenciālos pirkumus. Viņš arī raksta emuārus par stratēģiju un satura mārketingu vietnē dannalbright.com.

Vairāk no Danna Olbraita

Abonējiet mūsu biļetenu

Pievienojieties mūsu informatīvajam izdevumam, lai iegūtu tehniskus padomus, pārskatus, bezmaksas e -grāmatas un ekskluzīvus piedāvājumus!

Noklikšķiniet šeit, lai abonētu