2. Pagpili ng parameter

Kung ang isang formula na naglalaman ng isang link sa parehong cell ay ipinasok sa isang Excel cell (maaaring hindi direkta, ngunit hindi direkta - sa pamamagitan ng isang kadena ng iba pang mga link), pagkatapos ay sinasabi nila na mayroong isang cyclic reference (cycle). Sa pagsasagawa, ang mga paikot na sanggunian ay ginagamit pagdating sa pagpapatupad ng isang umuulit na proseso at mga kalkulasyon gamit ang mga relasyon sa pag-uulit. Sa normal na mode, nakita ng Excel ang isang loop at nagpapakita ng mensahe tungkol sa sitwasyon, na humihiling sa iyong ayusin ito. Ang Excel ay hindi maaaring magsagawa ng mga kalkulasyon dahil ang mga pabilog na sanggunian ay bumubuo ng isang walang katapusang bilang ng mga kalkulasyon. Mayroong dalawang paraan sa labas ng sitwasyong ito: alisin ang mga paikot na sanggunian o payagan ang mga kalkulasyon gamit ang mga formula na may mga paikot na sanggunian (sa ang huling kaso ang bilang ng mga pag-uulit ng loop ay dapat na may hangganan).

Isaalang-alang natin ang problema sa paghahanap ng ugat ng isang equation gamit ang pamamaraan ni Newton gamit ang mga cyclic na sanggunian. Kunin natin bilang isang halimbawa ang quadratic equation: x2 - 5x + 6=0, isang graphical na representasyon na kung saan ay ipinapakita sa Fig. 8. Mahahanap mo ang ugat ng equation na ito (at anumang iba pang) gamit ang isang Excel cell lang.

Upang paganahin ang cyclic calculation mode, sa Tools menu/Options/Calculations tab, paganahin ang Iteration checkbox at, kung kinakailangan, baguhin ang bilang ng mga pag-uulit ng cycle sa Limit number of iteration field at ang katumpakan ng pagkalkula sa Relative error field (sa pamamagitan ng default ang kanilang mga halaga ay 100 at 0.0001, ayon sa pagkakabanggit). Bilang karagdagan sa mga setting na ito, pinipili namin ang opsyon ng pagsasagawa ng mga kalkulasyon: awtomatiko o manu-mano. Gamit ang mga awtomatikong kalkulasyon, ang Excel ay agad na gumagawa ng panghuling resulta sa mga manu-manong kalkulasyon, makikita mo ang resulta ng bawat pag-ulit.

kanin. 8. Graph ng isang function

Pumili tayo ng isang di-makatwirang cell, italaga ito ng isang bagong pangalan, sabihin - X, at ipasok dito ang isang paulit-ulit na formula na tumutukoy sa mga kalkulasyon gamit ang pamamaraan ni Newton:

kung saan tinukoy ng F at F1, ayon sa pagkakabanggit, ang mga expression para sa pagkalkula ng mga halaga ng function at derivative nito. Para sa aming quadratic equation, pagkatapos ipasok ang formula, lalabas ang value 2 sa cell, na tumutugma sa isa sa mga ugat ng equation (Fig. 8). Sa aming kaso, ang paunang pagtatantya ay hindi tinukoy; ang umuulit na proseso ng computational ay nagsimula sa default na halaga na nakaimbak sa cell X at katumbas ng zero. Paano makukuha ang pangalawang ugat? Karaniwang magagawa ito sa pamamagitan ng pagbabago ng paunang hula. Ang problema sa pagtatakda ng mga paunang setting ay maaaring malutas sa iba't ibang paraan sa bawat kaso. Magpapakita kami ng isang pamamaraan batay sa function na IF. Upang madagdagan ang kalinawan ng mga kalkulasyon, ang mga cell ay itinalaga ng mga makabuluhang pangalan (Larawan 9).

Sa cell Khnach (B4) ipinasok namin ang paunang pagtatantya - 5.

Sa cell Hcurrent (C4) isulat ang formula:
=IF(Хcurrent=0;Хnach; Хcurrent-(Хcurrent^2-5*Хcurrent+6)/(2*Хcurrent-5)).

Sa cell D4 inilalagay namin ang isang formula na tumutukoy sa pagkalkula mga halaga ng function sa puntong Hcurrent, na magpapahintulot sa iyo na subaybayan ang proseso ng solusyon.

Pakitandaan na sa unang hakbang ng mga kalkulasyon, ang paunang halaga ay ilalagay sa Xcurrent cell, at pagkatapos ay magsisimula ang pagkalkula gamit ang formula sa mga susunod na hakbang.

Upang baguhin ang paunang pagtatantya, hindi sapat na baguhin ang mga nilalaman ng Hnach cell at simulan ang proseso ng pagkalkula. Sa kasong ito, magpapatuloy ang mga kalkulasyon simula sa huling nakalkula

mga kahulugan. Upang i-reset ang halaga na nakaimbak sa XCurrent cell, kailangan mong muling isulat ang formula doon. Upang gawin ito, piliin lamang ang cell na naglalaman ng formula para sa pag-edit sa pamamagitan ng pag-double click dito (ang mga nilalaman ng cell ay ipapakita sa formula bar). Ang pag-click sa Enter button ay magsisimula sa pagkalkula sa isang bagong paunang hula.

2.2. Pagpili ng parameter

Kapag ang nais na resulta ng isang pagkalkula ng formula ay kilala, ngunit ang mga halaga na kinakailangan upang makuha ang resultang ito ay hindi alam, maaari mong gamitin ang Parameter Selection tool sa pamamagitan ng pagpili sa Parameter Selection command sa Tools menu. Kapag pumili ka ng isang parameter, binabago ng Excel ang halaga sa isang partikular na cell hanggang sa kalkulahin ng formula na tumutukoy sa cell na iyon ang nais na resulta.

Kunin natin bilang isang halimbawa ang parehong quadratic equation x2-5x+6=0. Upang mahanap ang mga ugat ng equation, gawin ang mga sumusunod na hakbang:

Sa cell C3 (Larawan 10) nagpasok kami ng isang formula upang kalkulahin ang halaga ng function,

nakatayo sa equation sa kaliwa ng equal sign. Bilang argumento, gumagamit kami ng link sa cell C2, i.e. =C2^2-5*C2+6.

Sa dialog box na Pumili ng isang parameter (Larawan 10), sa Set field sa cell, magpasok ng link sa cell na may formula, sa Value field - ang inaasahang resulta, sa Changing cell values ​​​​​​- isang link sa cell kung saan iimbak ang halaga ng napiling parameter (ang mga nilalaman ng cell na ito ay hindi maaaring isang formula).

Pagkatapos i-click ang Ok button, magpapakita ang Excel ng dialog box Resulta ng pagpili ng parameter. Kung ang napiling halaga ay kailangang i-save, pagkatapos ay mag-click sa Ok, at ang resulta ay mai-save sa cell na tinukoy nang mas maaga sa field ng Pagbabago ng mga halaga ng cell. Upang ibalik ang halaga na nasa cell C2 bago gamitin ang Select Parameter command, i-click ang Kanselahin.

Kapag pumipili ng parameter, gumagamit ang Excel ng isang umuulit (cyclic) na proseso. Ang bilang ng mga pag-ulit at katumpakan ay nakatakda sa Tools menu/Options/Calculations tab. Kung gumaganap ang Excel ng isang kumplikadong gawain sa pagpili ng parameter, maaari mong i-click ang button na I-pause sa dialog box ng Resulta ng Parameter Selection upang matakpan ang pagkalkula, at pagkatapos ay i-click ang Step button upang isagawa ang susunod na pag-ulit at tingnan ang resulta. Kapag nilulutas ang isang problema sa isang step-by-step na mode, lalabas ang button na Magpatuloy upang bumalik sa normal na mode ng pagpili ng parameter.

Bumalik tayo sa halimbawa. Ang tanong ay lumitaw muli: kung paano makuha ang pangalawang ugat? Tulad ng sa nakaraang kaso, ito ay kinakailangan upang itakda ang paunang approximation. Magagawa ito bilang mga sumusunod (Larawan 11,a):

A

b

kanin. 11. Paghahanap ng pangalawang ugat

Sa cell X (C2) ipinasok namin ang paunang pagtatantya.

Sa cell Xi (C3) nagpasok kami ng isang formula upang kalkulahin ang susunod na approximation sa ugat, i.e. =X-(X^2-5*X+6)/(2*X-5).

Sa cell C4 inilalagay namin ang isang formula na tumutukoy sa pagkalkula ng halaga ng function sa kaliwang bahagi ng orihinal na equation sa puntong Xi.

Pagkatapos nito, piliin ang utos na Piliin ang parameter, kung saan kinukuha namin ang cell C2 bilang cell upang baguhin. Ang resulta ng mga kalkulasyon ay ipinapakita sa Fig. 11, b (sa cell C2 - ang huling halaga, at sa cell C3 - ang nauna).

Gayunpaman, ang lahat ng ito ay maaaring gawin nang medyo mas simple. Upang mahanap ang pangalawang ugat, sapat na upang ilagay ang pare-parehong 5 sa cell C2 bilang paunang pagtatantya (Fig. 10) at pagkatapos ay simulan ang proseso ng Pagpili ng Parameter.

2.3. Paghanap ng solusyon

Ang utos ng Parameter Selection ay maginhawa para sa paglutas ng mga problema sa paghahanap ng isang partikular na target na halaga na nakasalalay sa isang hindi kilalang parameter. Para sa mas kumplikadong mga problema, dapat mong gamitin ang command na Search for a solution (Solver), na na-access sa pamamagitan ng menu item na Tools/Search for a solution.

Ang mga problemang maaaring lutasin gamit ang Search for a solution ay binuo sa pangkalahatan tulad ng sumusunod:

Hanapin:
x1, x2, …, xn
tulad na:
F(x1, x2, …, xn) > (Max; Min; = Value)
na may mga paghihigpit:
G(x1, x2, ... , xn) > (>Halaga;< Value; = Value}

Ang mga variable na iyong hinahanap - ang Excel worksheet cells - ay tinatawag na adjustable cells. Ang layunin ng function na F(x1, x2, ..., xn), kung minsan ay tinatawag na layunin, ay dapat na tukuyin bilang isang formula sa isang cell ng worksheet. Ang formula na ito ay maaaring maglaman ng mga function na tinukoy ng gumagamit at dapat ay depende sa (reference) sa mga cell na inaayos. Sa sandali ng pagtatakda ng problema, natutukoy kung ano ang gagawin sa layunin ng pag-andar. Maaari kang pumili ng isa sa mga opsyon:

hanapin ang maximum ng layunin ng function F(x1, x2, ..., xn);

hanapin ang minimum ng layunin ng function F(x1, x2, ..., xn);

tiyakin na ang layunin ng function na F(x1, x2, … , xn) ay may nakapirming halaga: F(x1, x2, … , xn) = a.

Ang mga function na G(x1, x2, ..., xn) ay tinatawag na mga paghihigpit. Maaari silang tukuyin pareho bilang pagkakapantay-pantay at hindi pagkakapantay-pantay. Ang mga karagdagang paghihigpit ay maaaring ipataw sa mga kinokontrol na mga cell: non-negatibiti at/o integerity, pagkatapos ay ang nais na solusyon ay hinahanap sa lugar ng positibo at/o integer na mga numero.

Sinasaklaw ng formulation na ito ang malawak na hanay ng mga problema sa pag-optimize, kabilang ang solusyon ng iba't ibang equation at sistema ng mga equation, linear at nonlinear na problema sa programming. Ang ganitong mga problema ay kadalasang mas madaling bumalangkas kaysa lutasin. At pagkatapos, upang malutas ang isang partikular na problema sa pag-optimize, kinakailangan ang isang espesyal na idinisenyong paraan. Ang solver ay nasa arsenal nito na makapangyarihang mga tool para sa paglutas ng mga naturang problema: ang pangkalahatang paraan ng gradient, ang simplex na pamamaraan, ang sangay at nakatali na pamamaraan.

Sa itaas, upang mahanap ang mga ugat ng isang quadratic equation, ginamit ang pamamaraan ni Newton gamit ang mga cyclic reference (hakbang 1) at ang Parameter Selection tool (item 2).

Ang layunin ng aralin: Patuloy na bumuo ng mga kasanayan sa spreadsheet.

• pang-edukasyon:
bumuo ng mga kasanayan sa paglikha, pag-edit, pag-format at pagsasagawa ng mga simpleng kalkulasyon sa mga spreadsheet.
• pagbuo:
palawakin ang pag-unawa ng mga mag-aaral sa mga posibleng lugar ng aplikasyon ng mga spreadsheet; bumuo ng analytical na pag-iisip, pagsasalita at mga kasanayan sa atensyon.
• pang-edukasyon:
upang bumuo at linangin ang nagbibigay-malay na interes; itanim ang mga kasanayan ng pagsasarili sa trabaho.

Lesson plan.

1. Oras ng pag-aayos.
2. Pag-update ng kaalaman ng mga mag-aaral.
3. Sinusuri ang takdang-aralin.
4. Pagtugon sa suliranin.
5. Malayang paglutas ng problema.
6. Pagbubuod. Mga rating.
7. Takdang aralin.

Sa panahon ng mga klase

1. Pansamahang sandali.

Ipaalam ang paksa ng aralin, bumalangkas ng mga layunin at layunin ng aralin.

Ngayon ay muli nating bibisitahin ang maliit na higanteng si Vasya sa Fairytale Land. Gaya ng dati, kailangan niya ang iyong tulong, guys.

Matutulungan mo ba si Vasya? Suriin natin ito ngayon!

2. Pag-update ng kaalaman ng mga mag-aaral.

1) Sagutin ang mga tanong nang pasalita.

	A	B	C	D
1	2	1	=A1+3*B1	=A1^2+B1
2	4	6	=A2+3*B2	=A2^2+B2

• Ano ang isang spreadsheet?
• Anong mga pangunahing elemento ng isang spreadsheet ang alam mo?
• Paano itakda ang pangalan ng isang cell (row, column) sa isang spreadsheet?
• Ano ang maaaring nilalaman ng isang cell?
• Ang numero 1 ay nasa hanay..., sa hilera..., sa cell na may address...
• Ang numero 4 ay nasa cell na may address...
• Ano ang mga patakaran para sa pagsulat ng mga formula sa mga cell?
• Ano ang halaga na kinakalkula ng formula sa cell C1?
• Ano ang halaga na kinakalkula ng formula sa cell D2?

2) Anong resulta ang makukuha sa mga cell na may mga formula?

	A	SA
1	25	4
2	2	=A1*B1/2
3

Sagot: 25*4/2=50

	A	B	C	D
1		5	2	1
2		6	8	3
3		8	3	4
4				=SUM(B1:D3)

• Ano ang ibig sabihin ng =SUM(B1:D3)?
• Ilang elemento ang nilalaman ng block B1:D3? Sagot: 9.
• Mga nilalaman ng cell D3? Sagot: 5+2+1+6+8+3+8+3+4= 40

3) Pagsusuri ng takdang-aralin

Mga resulta ng kumpetisyon sa paglangoy

Isang estudyante ang nagkuwento tungkol sa kung paano niya natapos ang kanyang takdang-aralin (sa pamamagitan ng projector).

№	BUONG PANGALAN.	1	2	3	Pinakamahusay na oras	Average na oras		paglihis
1	Lyagushkin	3.23	3.44	3.30
2	Morzhov	3.21	3.22	3.24
3	Akulov	3.17	3.16	3.18
4	Rybin	3.24	3.20	3.18
5	Cherepakhin	3.56	3.44	3.52
	Pinakamataas na paglihis

• Ang average na oras para sa bawat atleta ay matatagpuan bilang arithmetic mean ng kanyang tatlong paglangoy.
• Ang pinakamababang resulta ng 3 paglangoy ay naitala sa cell na "Pinakamahusay na oras".
• Sa cell ay nakasulat ang "Pinakamahusay na resulta ng kumpetisyon". pinakamababang oras mula sa hanay.
• Itinatala ng column na "Deviation" ang pagkakaiba sa pagitan ng pinakamahusay na oras ng atleta at ang pinakamahusay na resulta ng kompetisyon.
• Ang maximum na halaga ng column ay naitala sa "Maximum deviation" cell.

Mga resulta ng kompetisyon sa paglangoy
№	BUONG PANGALAN.	1	2	3	Pinakamahusay na oras	Average na oras	paglihis
1	Lyagushkin	3,23	3,44	3,30	3,23	3,32	0,07
2	Morzhov	3,21	3,22	3,24	3,21	3,22	0,05
3	Akulov	3,17	3,16	3,18	3,16	3,17	0,00
4	Rybin	3,24	3,20	3,18	3,18	3,21	0,02
5	Cherepakhin	3,56	3,44	3,52	3,44	3,51	0,28
	Pinakamahusay na resulta ng kumpetisyon					3,16
	Average na oras ng mga kakumpitensya					3,29
	Pinakamataas na paglihis					0,28

4) Paglutas ng mga simpleng problema.

Nagpasya ang maliit na higanteng si Vasya na ayusin ang bakod sa paligid ng kanyang hardin at hukayin ito para sa pagtatanim ng mga gulay (dumating na ang isa pang tagsibol), at markahan ang mga hugis-parihaba na kama. Upang magtrabaho, kailangan niyang hanapin ang haba ng bakod at ang lugar ng site. Ngunit hindi siya pumasok sa paaralan. Tulungan natin si Vasya.

No. 1. Kalkulahin ang perimeter at lugar ng isang parihaba na may mga gilid:

a) 3 at 5; b) 6 at 8; c) 10 at 7.

Tinatalakay namin ang gawaing ito kasama ng mga bata:

• Paano magdisenyo ng mesa?
• Anong mga formula ang dapat kong gamitin?
• Paano gamitin ang nakasulat na mga formula para sa susunod na parihaba?

Disenyo ng talahanayan - sa pisara at sa mga notebook.

Kasabay nito, isa pang estudyante ang nag-iisa na nilulutas ang susunod na problema at inilalahad ang kanyang solusyon sa mga mag-aaral (sa pamamagitan ng projector).

Ang pagkakaroon ng pag-usapan ang solusyon sa problema No. 2, nagpapatuloy kami sa paglutas sa susunod.

Ang isang mag-aaral ay nagpapakita kung paano gumawa ng mga formula, ang isa pa ay nagpapakita kung paano gamitin ang sum function, format ng numero (pangkalahatan, pera), atbp. (Ang talahanayan ay handa na, ang mga mag-aaral ay kailangang magpasok ng mga formula, gumamit ng pagsusuma at makuha ang sagot).

Hindi. 3. Kalkulahin, gamit ang ET, sapat ba ang 150 rubles para mabili ni Vasya ang lahat ng mga produkto na iniutos ng kanyang ina para sa kanya, at magiging sapat ba ito para sa mga chips para sa 10 rubles? Pinayagan ako ni Nanay na ilagay ang sukli sa alkansya. Ilang rubles ang mapupunta sa alkansya?

Iminungkahing solusyon:

№	Pangalan	Presyo sa rubles	Dami	Presyo
1	Tinapay	9,6	2	=C2*D2
2	kape	2,5	5	=C3*D3
3	Gatas	13,8	2	=C4*D4
4	Dumplings	51,3	1	=C5*D5
				=SUM(E2:E5)
Pagkatapos mamili ay magkakaroon			=150-E6
Pagkatapos bumili ng chips magkakaroon			=D7-10

5) Malayang paglutas ng problema.

Ang maliit na higanteng si Vasya ay madalas na bumisita sa mga residente ng Flower City.

Pagpunta sa beach, nagpasya ang masasayang maliliit na lalaki na mag-stock softdrinks. Kinuha ni Dunno ang 2 litro ng kvass, 1 litro ng soda at 1 litro ng raspberry syrup, Donut - 3 litro ng soda at 2 litro ng raspberry syrup, Toropyzhka - 2 litro ng soda, Doctor Pilyulkin - 1 litro ng kvass at 1 litro ng castor oil.

• Ilang litro ng bawat uri ng inumin ang pinagsama-sama ng lahat ng maliliit na lalaki?
• Ilang litro ng inumin ang dinala ng bawat lalaki sa kanila?
• Ilang litro ng inumin ang pinagsama-sama ng lahat ng maliliit na lalaki?

Idisenyo ang talahanayan ayon sa gusto mo at i-save ito sa iyong personal na folder.

Ang resulta ng trabaho.

Nakakatawang tao.
Mga inumin.	inumin	Ewan	Donut	Toropyzhka	Pilyulkin
Kabuuan	2	0	0	1	3
Kvass, l	1	3	2	0	6
Soda, l	1	2	0	0	9
Syrup, l	0	0	0	1	1
Langis ng castor, l	4	5	2	2	13

KABUUAN:

7) Pagbubuod. Mga rating.

8) Takdang-Aralin.

Pag-isipan at lutasin ang problemang ito kung alam din ang mga sumusunod na dami.

Paano magbabago ang talahanayan? Anong mga formula ang lalabas?

• Ito ay kilala na ang 1 litro ng kvass sa Flower City ay nagkakahalaga ng 1 barya, 1 litro ng soda ay nagkakahalaga ng 3 barya, 1 litro ng raspberry syrup ay nagkakahalaga ng 6 na barya, 1 litro ng castor oil ay nagkakahalaga ng 2 barya.
• Ilang barya ang ginugol ng bawat tao sa pagbili ng inumin?
• Ilang barya ang ginugol sa pagbili ng bawat uri ng inumin?

Gaano karaming pera ang ginugol ng lahat ng mga tao?

1. Panitikan
2. Computer science. Problema sa book-workshop sa 2 tomo /Ed. I.G.Semakina, E.K.Henner - M.: Laboratory of Basic Knowledge, 2010.

Efimova O. Kurso sa teknolohiya ng computer na may mga pangunahing kaalaman sa computer science. – M.: LLC “AST Publishing House”; ABF, 2005. Sa artikulong ito matututunan mo kung paanolutasin ang quadratic equation sa Excel sa. Suriin natin nang detalyado ang solusyon sa isang simpleng problema sa mga larawan.

Pag-unlad ng desisyon

Ilunsad natin ang Microsoft Office Excel. 2007 version ang gamit ko. Una, pagsamahin natin ang mga cell A1:A5 at isulat ang quadratic equation formula sa kanila sa form na ax2+bx+c=0 Susunod, kailangan nating i-square ang x, upang gawin ito kailangan nating gawing superscript ang numero 2. Piliin ang dalawa at i-right click.

Kumuha kami ng formula ng form na ax 2 +bx+c=0

Sa cell A2 ipinasok namin ang text value a=, sa cell A3 b= at sa cell A4 c=, ayon sa pagkakabanggit. Ang mga halagang ito ay ipapasok mula sa keyboard sa mga sumusunod na cell (B2,B3,B4).

Maglagay tayo ng teksto para sa mga halagang kakalkulahin. Sa cell C2 d=, C3 x 1 = C4 x 2 =. Gawin natin ang sublinear spacing para sa x na katulad ng superscript spacing sa x 2

Magpatuloy tayo sa paglalagay ng mga formula para sa solusyon

Ang discriminant ng isang quadratic trinomial ay b 2 -4ac

Sa cell D2, ilagay ang naaangkop na formula para sa pagtaas ng numero sa pangalawang kapangyarihan:

Ang isang quadratic equation ay may dalawang ugat kung ang discriminant ay mas malaki sa zero. Sa cell C3, ilagay ang formula para sa x 1

KUNG(D2>0;(-B3+ROOT(D2))/(2*B2);”Walang ugat”)

Upang kalkulahin ang x2, ipinakilala namin ang isang katulad na formula, ngunit may plus sign

IF(D2>0;(-B3-ROOT(D2))/(2*B2);”Walang ugat”)

Alinsunod dito, kasama ang ipinasok na mga halaga a, b, c, ang diskriminasyon ay unang kinakalkula, kung ang mga halaga nito ay mas mababa sa zero, ang mensahe na "Walang mga ugat" ay ipinapakita, kung hindi man ay nakukuha namin ang mga halaga x 1 at x 2.

Pagprotekta sa isang sheet sa Excel

Kailangan naming protektahan ang sheet kung saan ginawa namin ang mga kalkulasyon. Kung walang proteksyon, kailangan mong umalis sa mga cell kung saan maaari kang pumasok mga halaga a,b,c, ibig sabihin, mga cell B2 B3 B4. Upang gawin ito, piliin ang hanay na ito at pumunta sa format ng cell, pumunta sa tab na Mga Review, Protektahan ang Sheet at alisan ng check ang Protected cell box. I-click ang OK upang kumpirmahin ang mga pagbabagong ginawa.

Ang hanay ng mga cell na ito ay hindi mapoprotektahan kapag ang worksheet ay protektado. Protektahan natin ang sheet upang gawin ito, pumunta sa tab na Review at piliin ang Sheet Protection. Ipasok natin ang password 1234. I-click ang OK.

Ngayon ay maaari nating baguhin ang mga halaga ng mga cell B2,B3,B4. Kung susubukan naming baguhin ang iba pang mga cell, matatanggap namin ang sumusunod na mensahe: "Ang cell o tsart ay protektado mula sa mga pagbabago. At pati na rin ang payo sa pag-alis ng proteksyon.

Maaari ka ring maging interesado sa materyal kung paano i-secure ito.

Ang isang makabuluhang bahagi ng mga problema na nalutas gamit ang mga spreadsheet ay ipinapalagay na ang user ay mayroon nang hindi bababa sa ilang paunang data upang mahanap ang nais na resulta. Gayunpaman, ang Excel 2010 ay may mga kinakailangang tool kung saan maaari mong malutas ang problemang ito sa kabaligtaran - piliin ang kinakailangang data upang makuha ang nais na resulta.

Ang "Maghanap ng solusyon" ay isa sa mga tool na ito, pinaka-maginhawa para sa "mga problema sa pag-optimize." At kung hindi mo pa ito kailangang gamitin noon, ngayon na ang oras para ayusin ito.

Kaya, magsisimula kami sa pamamagitan ng pag-install ng add-in na ito (dahil hindi ito lilitaw sa sarili nitong). Sa kabutihang palad, ngayon ito ay maaaring gawin nang simple at mabilis - buksan ang menu na "Mga Tool", at nasa loob na nito ang "Mga Add-on"

Ang natitira na lang ay ipahiwatig ang "Excel Add-in" sa column na "Pamamahala", at pagkatapos ay i-click ang "Go" na buton.

Pagkatapos ng simpleng pagkilos na ito, ang activation button na "Search for a solution" ay ipapakita sa "Data". Gaya ng ipinapakita sa larawan

Tingnan natin kung paano maayos na gamitin ang paghahanap ng solusyon sa Excel 2010 na may ilang simpleng halimbawa.

Halimbawa ng isa .

Sabihin nating hawak mo ang posisyon ng pinuno ng isang malaking departamento ng produksyon at kailangan mong wastong ipamahagi ang mga bonus sa mga empleyado. Sabihin nating ang kabuuang halaga ng mga bonus ay 100,000 rubles, at kinakailangan na ang mga bonus ay proporsyonal sa mga suweldo.

Ibig sabihin, ngayon kailangan nating piliin ang tamang proportionality coefficient para matukoy ang laki ng bonus na may kaugnayan sa suweldo.

Una sa lahat, kailangan mong mabilis na lumikha (kung wala ka pa) isang talahanayan kung saan maiimbak ang mga paunang formula at data, ayon sa kung saan maaari mong makuha ang nais na resulta. Para sa amin, ang resultang ito ay ang kabuuang halaga ng premium. At ngayon bigyang-pansin - ang target na cell C8 ay dapat na naka-link gamit ang mga formula sa nais na cell na palitan sa address E2. Ito ay kritikal. Sa halimbawa, ikinonekta namin sila gamit ang mga intermediate na formula, na responsable sa pagkalkula ng bonus para sa bawat empleyado (C2:C7).

Ngayon ay maaari mong i-activate ang "Maghanap ng mga solusyon". Magbubukas ang isang bagong window kung saan kailangan nating tukuyin kinakailangang mga parameter.

sa ilalim ng " 1 " ay nagpapahiwatig ng aming target na cell. Isa lamang ang pwede.

« 2 " - Ito posibleng mga opsyon pag-optimize. Mayroong kabuuang "Maximum", "Minimum" o "Specific" na posibleng mga value na mapagpipilian. At kung kailangan mo ng isang tiyak na halaga, kailangan mong ipahiwatig ito sa naaangkop na hanay.

« 3 "—maaaring maraming nababagong mga cell (isang buong hanay o hiwalay na tinukoy na mga address). Pagkatapos ng lahat, gagana ang Excel sa kanila, pag-uuri sa mga pagpipilian upang makuha ang halaga na tinukoy sa target na cell.

« 4 "- Kung kailangan mong magtakda ng mga paghihigpit, dapat mong gamitin ang pindutang "Magdagdag", ngunit titingnan namin ito sa ibang pagkakataon.

« 5 » — isang pindutan para sa paglipat sa mga interactive na kalkulasyon batay sa program na aming tinukoy.

Ngunit ngayon ay bumalik tayo sa kakayahang baguhin ang ating gawain gamit ang "Add" button. Ang yugtong ito ay lubos na mahalaga (hindi bababa sa pagbuo ng mga formula), dahil ito ang limitasyon na nagpapahintulot sa amin na makakuha tamang resulta sa labasan. Narito ang lahat ay ginagawa nang maginhawa hangga't maaari, kaya maaari mong itakda ang mga ito hindi lamang para sa buong saklaw nang sabay-sabay, kundi pati na rin para sa ilang mga cell.

Upang gawin ito, maaari kang gumamit ng ilang partikular (at pamilyar sa lahat ng mga user ng Excel 2010) na mga simbolo na "="", ">=", "<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Ngunit sa aming halimbawa, maaari lamang magkaroon ng isang limitasyon - isang positibong koepisyent. Siyempre, maaari mo itong itakda sa iba't ibang paraan - alinman sa paggamit ng "Magdagdag" (na tinatawag na "hayagang pagtukoy sa hadlang"), o pagsuri lamang sa function na "Gawing aktibo ang mga hindi napigilang variable." Magagawa ito sa add-on na "Solution Search" sa pamamagitan ng pag-click sa "Options" na buton.

Sa pamamagitan ng paraan, pagkatapos kumpirmahin ang mga parameter at ilunsad ang programa (ang "Run") na pindutan), maaari mong tingnan ang resulta sa talahanayan. Pagkatapos ay magpapakita ang programa ng window ng "mga resulta ng paghahanap".

Kung ganap na nababagay sa iyo ang ipinakitang resulta, ang natitira lamang ay kumpirmahin itong muli (ang "OK" na buton), na magre-record ng resulta sa iyong talahanayan. Kung ang isang bagay sa mga kalkulasyon ay hindi angkop sa iyo, pagkatapos ay kailangan mong kanselahin ang resulta (ang "Kanselahin") na pindutan), bumalik sa nakaraang estado ng aming talahanayan at itama ang mga pagkakamali.

Ang tamang solusyon sa halimbawang problema ay dapat magmukhang ganito

Napakahalaga na upang makuha ang tamang resulta kahit na may kaunting pagbabago sa paunang data, kailangan mong i-restart ang "Search for Solutions".

Upang makakuha ng mas detalyadong pagtingin sa kung paano gumagana ang program na ito, tingnan natin ang isa pang halimbawa.

Sabihin nating ikaw ang may-ari ng isang malaking negosyo ng muwebles at kailangan mong ayusin ang produksyon sa paraang makuha ang pinakamataas na posibleng tubo. Gumagawa ka lamang ng mga bookshelf, na may dalawang modelo lamang - "A" at "B", ang produksyon nito ay limitado lamang sa pagkakaroon (o kawalan) ng mga de-kalidad na board, pati na rin ang oras ng makina (pagproseso sa isang makina).

Ang modelong "A" ay nangangailangan ng 3 m 3 ng mga board, at ang modelong "B" ay nangangailangan ng 1 m 3 pa (iyon ay, 4). Mula sa iyong mga supplier nakakatanggap ka ng maximum na 1700 m 3 ng mga board bawat linggo. Sa kasong ito, ang modelong "A" ay nilikha sa loob ng 12 minuto ng pagpapatakbo ng makina, at "B" sa loob ng 30 minuto. Sa kabuuan, ang makina ay maaaring gumana nang hindi hihigit sa 160 oras bawat linggo.

Ang tanong ay: gaano karaming mga produkto (at anong modelo) ang dapat gawin ng isang kumpanya bawat linggo upang makuha ang pinakamataas na posibleng kita, kung ang istante na "A" ay nagbibigay ng 60 rubles ng kita, at "B" - 120?

Dahil alam ang pamamaraan, nagsisimula kaming lumikha ng talahanayan na kailangan namin gamit ang data at mga formula. Ang lokasyon ng mga cell, tulad ng dati, maaari mong itakda sa iyong paghuhusga. O gamitin ang aming

Sa anumang maginhawang paraan, ilulunsad namin ang aming "Search for Solutions", magpasok ng data, at gumawa ng mga setting.

Kaya tingnan natin kung ano ang mayroon tayo. Ang target na cell F7 ay naglalaman ng isang formula na magkalkula ng kita. Itinakda namin ang parameter ng pag-optimize sa maximum. Kabilang sa mga nababagong cell mayroon tayong "F3: G3". Mga Limitasyon - lahat ng natukoy na halaga​​ay dapat na hindi negatibong integer, ang kabuuang halaga ng oras ng makina na ginugol ay hindi lalampas sa 160 (aming cell D9), ang halaga ng mga hilaw na materyales ay hindi lalampas sa 1700 (cell D8).

Siyempre, sa kasong ito posible na hindi ipahiwatig ang mga address ng cell, ngunit direktang ipasok ang mga kinakailangang digital na halaga, ngunit kung gagamitin mo ang mga address, kung gayon ang mga pagbabago sa mga paghihigpit ay maaaring gawin sa talahanayan, na makakatulong sa pagkalkula ng kita ng enterprise na ito sa hinaharap, kapag nagbago ang source data.

Ina-activate namin ang program, at naghahanda ito ng solusyon.

Gayunpaman, hindi lamang ito ang solusyon at maaari kang makakuha ng ibang resulta. Ito ay maaaring mangyari kahit na ang lahat ng data ay naipasok nang tama at wala ring mga error sa mga formula.

Oo. Ito ay maaaring mangyari kahit na sinabi namin sa programa na maghanap buo numero. At kung bigla itong mangyari, kailangan mo lang gumawa ng mga karagdagang pagsasaayos sa "Search for Solutions". Buksan ang window na "Maghanap ng mga solusyon" at ipasok ang "Mga Pagpipilian".

Ang aming nangungunang parameter ay responsable para sa katumpakan. Kung mas maliit ito, mas mataas ang katumpakan, at sa aming kaso ito ay makabuluhang pinatataas ang mga pagkakataong makakuha ng isang integer. Ang pangalawang parameter ("Balewalain ang mga paghihigpit ng integer") ay sumasagot sa tanong kung paano kami nakakuha ng ganoong tugon na may katotohanan na tahasan naming tinukoy ang isang integer sa kahilingan. Binalewala lang ng "Solution Finder" ang limitasyong ito dahil sa katotohanang sinabi ito ng mga advanced na setting.

Kaya maging lubhang maingat sa hinaharap.

Ang ikatlo at marahil ang huling halimbawa. Subukan nating bawasan ang mga gastos ng isang kumpanya ng transportasyon gamit ang paghahanap para sa mga solusyon sa Excel 2010.

Kaya, ang kumpanya ng konstruksiyon ay nagbibigay ng isang order para sa transportasyon ng buhangin, na kinuha mula sa 3 mga supplier (quarries). Kailangan itong maihatid sa 5 magkakaibang mga mamimili (na mga construction site). Ang halaga ng paghahatid ng kargamento ay kasama sa gastos ng pasilidad, kaya ang aming gawain ay tiyakin ang paghahatid ng mga kargamento sa mga lugar ng konstruksiyon na may kaunting gastos.

Mayroon kaming - isang supply ng buhangin sa quarry, ang pangangailangan para sa mga construction site para sa buhangin, ang gastos ng transportasyon "supplier-consumer".

Ito ay kinakailangan upang makahanap ng isang pamamaraan para sa pinakamainam na transportasyon ng mga kargamento (papunta at mula), kung saan ang kabuuang halaga ng transportasyon ay magiging minimal.

Ang mga gray na cell ng aming talahanayan ay naglalaman ng mga formula para sa mga kabuuan ayon sa mga column at row, at ang target na cell ay isang formula para sa pangkalahatang pagkalkula ng halaga ng paghahatid ng mga produkto. Inilunsad namin ang aming "Maghanap ng solusyon" at gawin ang mga kinakailangang setting

Pagkatapos nito, nagsisimula kaming makahanap ng solusyon sa problemang ito.

Gayunpaman, huwag nating kalimutan na madalas na ang mga gawain sa transportasyon ay maaaring kumplikado ng ilang karagdagang mga paghihigpit. Sabihin nating may komplikasyon na lumitaw sa kalsada at ngayon ay teknikal na imposibleng maghatid ng mga kargamento mula sa quarry 2 hanggang construction site 3. Upang isaalang-alang ito, kailangan mo lamang idagdag ang karagdagang hadlang na "$D$13=0". At kung patakbuhin mo ngayon ang programa, iba ang resulta

Sa wakas, ang lahat na nananatiling sasabihin ay tungkol sa pagpili ng paraan ng solusyon. At kung ang problema ay talagang napaka kumplikado, kung gayon upang makuha ang ninanais na resulta, malamang na kakailanganin mong piliin ang kinakailangang paraan ng solusyon.

Iyon lang ang tungkol sa isyung ito.

Naghanap kami ng mga solusyon sa Excel 2010 - para sa paglutas ng mga kumplikadong problema

Karamihan sa mga problemang nalutas gamit ang isang spreadsheet ay kinabibilangan ng paghahanap ng nais na resulta gamit ang kilalang pinagmumulan ng data. Ngunit ang Excel ay may mga tool na nagbibigay-daan sa iyo upang malutas ang kabaligtaran na problema: piliin ang paunang data upang makuha ang nais na resulta.

Ang isa sa gayong kasangkapan ayPaghanap ng solusyon, na lalong maginhawa para sa paglutas ng tinatawag na "mga problema sa pag-optimize".

Kung hindi mo pa ito ginagamit noonPaghanap ng solusyon, pagkatapos ay kakailanganin mong i-install ang naaangkop na add-on.

Magagawa mo ito tulad nito:

para sa mga bersyon na mas luma sa Excel 2007 sa pamamagitan ng command menu Serbisyo -> Mga Add-on;

mula noong Excel 2007 sa pamamagitan ng dialog boxMga Pagpipilian sa Excel

Mula noong Excel 2007pindutan upang magsimulaPaghanap ng solusyon lalabas sa tab Data.

Sa mga bersyon bago ang Excel 2007, may lalabas na katulad na command sa menuSerbisyo

Tingnan natin ang pamamaraan ng trabaho Paghanap ng solusyon gamit ang isang simpleng halimbawa.

Halimbawa 1. Pamamahagi ng bonus

Ipagpalagay natin na ikaw ang pinuno ng isang departamento ng produksyon at kailangan mong pantay na ipamahagi ang isang bonus sa halagang 100,000 rubles. sa pagitan ng mga empleyado ng departamento ayon sa kanilang opisyal na suweldo. Sa madaling salita, kailangan mong pumili ng koepisyent ng proporsyonalidad upang makalkula ang laki ng bonus sa suweldo.

Una sa lahat, lumikha kami ng isang talahanayan na may paunang data at mga formula kung saan dapat makuha ang resulta. Sa aming kaso, ang resulta ay ang kabuuang halaga ng premium. Napakahalaga na ang target na cell (C8) ay naka-link sa pamamagitan ng mga formula sa nais na cell na baguhin (E2). Sa halimbawa, sila ay konektado sa pamamagitan ng mga intermediate na formula na kinakalkula ang halaga ng bonus para sa bawat empleyado (C2:C7).

Ngayon ilunsad natin Paghanap ng solusyon at sa dialog box na bubukas, itakda ang mga kinakailangang parameter. Hitsura Ang mga dialog box ay bahagyang naiiba sa pagitan ng mga bersyon:

Mula noong Excel 2010

Bago ang Excel 2010

Pagkatapos pindutin ang pindutanMaghanap ng solusyon (Run)Makikita mo na ang resultang nakuha sa talahanayan. Kasabay nito, lumilitaw ang isang dialog box sa screenMga resulta ng paghahanap ng solusyon.

Mula noong Excel 2010

Bago ang Excel 2010

Kung ang resulta na nakikita mo sa talahanayan ay nababagay sa iyo, pagkatapos ay sa dialog box Mga resulta ng paghahanap ng solusyon pindutin OK at itala ang resulta sa talahanayan. Kung ang resulta ay hindi angkop sa iyo, pagkatapos ay i-click Kanselahin at bumalik sa dating estado ng talahanayan.

Ang solusyon sa problemang ito ay ganito

Mahalaga: para sa anumang mga pagbabago sa source data upang makakuha ng bagong resulta Paghanap ng solusyon ay tatakbo muli.

Tingnan natin ang isa pang problema sa pag-optimize (pagmaximize ng kita)

Halimbawa 2. Produksyon ng muwebles (pag-maximize ng kita)

Gumagawa ang kumpanya ng dalawang modelo A at B ng mga prefabricated na bookshelf.

Ang kanilang produksyon ay limitado sa pamamagitan ng pagkakaroon ng mga hilaw na materyales (mataas na kalidad na mga board) at oras ng pagproseso ng makina.

Ang bawat modelong A na produkto ay nangangailangan ng 3 m²mga board, at para sa modelo ng produkto B - 4 m². Ang kumpanya ay maaaring makatanggap ng hanggang 1,700 m² ng mga board bawat linggo mula sa mga supplier nito.

Ang bawat modelong Isang produkto ay nangangailangan ng 12 minutong oras ng makina, at para sa modelo ng produkto B - 30 min. Maaaring gamitin ang 160 oras na oras ng makina bawat linggo.

Gaano karaming mga produkto ng bawat modelo ang dapat gawin ng kumpanya bawat linggo upang makamit ang pinakamataas na kita, kung ang bawat produkto ng modelo A ay nagdadala ng 60 rubles. kita, at ang bawat produkto ng modelo B ay nagkakahalaga ng 120 rubles. dumating?

Alam na natin ang procedure.

Una, gumawa kami ng mga talahanayan na may pinagmulang data at mga formula. Ang pag-aayos ng mga cell sa sheet ay maaaring maging ganap na arbitrary, bilang maginhawa para sa may-akda. Halimbawa, tulad ng nasa larawan

Ilunsad natin Paghanap ng solusyonat sa dialog box itakda ang mga kinakailangang parameter

1. Ang target na cell B12 ay naglalaman ng formula para sa pagkalkula ng kita
2. Parameter ng pag-optimize - maximum
3. Binagong mga cell B9:C9
4. Mga paghihigpit: ang mga nahanap na halaga ay dapat na integer, hindi negatibo; ang kabuuang tagal ng oras ng makina ay hindi dapat lumampas sa 160 oras (sanggunian sa cell D16); ang kabuuang dami ng mga hilaw na materyales ay hindi dapat lumampas sa 1700 m² (reference sa cell D15). Dito, sa halip na mga link sa mga cell D15 at D16, maaaring tukuyin ang mga numero, ngunit kapag gumagamit ng mga link, anumang mga pagbabago sa mga paghihigpit ay maaaring direktang gawin sa talahanayan
5. Pindutin ang pindutan Maghanap ng solusyon (Run) at pagkatapos ng kumpirmasyon makuha namin ang resulta

Ngunit kahit na ginawa mo ang mga formula at itakda nang tama ang mga paghihigpit, ang resulta ay maaaring hindi inaasahan. Halimbawa, kapag nilulutas ang problemang ito maaari mong makita ang sumusunod na resulta:

At ito sa kabila ng katotohanan na ang paghihigpit ay itinakda buo. Sa ganitong mga kaso, maaari mong subukang ayusin ang mga setting Paghanap ng solusyon. Upang gawin ito sa window Paghanap ng solusyon pindutin ang pindutan Mga pagpipilian at pumasok kami sa dialog box ng parehong pangalan

Ang una sa mga napiling parameter ay responsable para sa katumpakan ng mga kalkulasyon. Sa pamamagitan ng pagbabawas nito, makakamit mo ang isang mas tumpak na resulta, sa aming kaso - mga halaga ng integer. Ang pangalawa sa mga naka-highlight na opsyon (magagamit simula sa Excel 2010) ay sumasagot sa tanong: paano makukuha ang mga fractional na resulta kapag nililimitahanbuo? Kinalabasan Paghanap ng solusyonang paghihigpit na ito ay binalewala lamang alinsunod sa naka-check na bandila.

Halimbawa 3. Problema sa transportasyon (pagbawas ng gastos)

Sa pagkakasunud-sunod ng isang kumpanya ng konstruksiyon, ang buhangin ay dinadala mula sa tatlong mga supplier (quarry) sa limang mga mamimili (mga lugar ng konstruksyon). Ang halaga ng paghahatid ay kasama sa halaga ng proyekto, kaya ang kumpanya ng konstruksiyon ay interesado na matugunan ang mga pangangailangan ng buhangin ng mga lugar ng pagtatayo nito sa pinakamurang paraan.

Ibinigay: mga reserbang buhangin sa mga quarry; pangangailangan ng buhangin sa lugar ng konstruksiyon; gastos sa transportasyon sa pagitan ng bawat pares ng supplier-consumer.

Kinakailangang maghanap ng pinakamainam na pamamaraan ng transportasyon upang matugunan ang mga pangangailangan (mula saan at saan), kung saan ang kabuuang gastos sa transportasyon ay magiging minimal.

Ang isang halimbawa ng lokasyon ng mga cell na may source data at mga paghihigpit, ang nais na mga cell at ang target na cell ay ipinapakita sa figure

Sa mga gray na cell mayroong mga formula para sa mga kabuuan ayon sa mga hilera at mga haligi, at sa target na cell mayroong isang formula para sa pagkalkula ng kabuuang gastos sa transportasyon.

Inilunsad namin ang Paghahanap para sa isang solusyon at itinakda ang mga kinakailangang parameter (tingnan ang figure)

I-click Maghanap ng solusyon (Run) at makuha ang resulta na ipinapakita sa ibaba

Sa konklusyon, iminumungkahi kong subukan mo ang iyong kamay sa paggamit Paghanap ng solusyon at gamitin ito upang malutas ang isang lumang problema:

Ang isang magsasaka ay bumili ng 100 ulo ng baka sa merkado para sa 100 rubles. Ang isang toro ay nagkakahalaga ng 10 rubles, isang baka 5 rubles, isang guya 50 kopecks. Ilang toro, baka at guya ang binili ng magsasaka?