2. Parametrų pasirinkimas

Jei formulė įvedama į Excel langelį, kuriame yra nuoroda į tą patį langelį (galbūt ne tiesiogiai, bet netiesiogiai per kitų nuorodų grandinę), tada jie sako, kad atsiranda ciklinė nuoroda (ciklas). Praktikoje ciklinės nuorodos naudojamos, kai reikia įgyvendinti iteracinį procesą ir skaičiavimus naudojant pasikartojimo ryšius. Įprastu režimu „Excel“ aptinka kilpą ir parodo pranešimą apie situaciją, prašydama ją ištaisyti. „Excel“ negali atlikti skaičiavimų, nes žiedinės nuorodos generuoja begalinį skaičių skaičiavimų. Yra du išeitys iš šios situacijos: panaikinti ciklines nuorodas arba leisti skaičiavimus naudojant formules su ciklinėmis nuorodomis (į pastarasis atvejis kilpos pakartojimų skaičius turi būti baigtinis).

Panagrinėkime lygties šaknies radimo, naudojant Niutono metodą, naudojant ciklines nuorodas, problemą. Paimkime kaip pavyzdį kvadratinę lygtį: x2 - 5x + 6=0, kurios grafinis vaizdas parodytas fig. 8. Šios (ir bet kurios kitos) lygties šaknį galite rasti naudodami tik vieną Excel langelį.

Norėdami įjungti ciklinio skaičiavimo režimą, meniu Įrankiai/Parinktys/Skaičiavimai įjunkite žymės langelį Iteracijos ir, jei reikia, pakeiskite ciklo pakartojimų skaičių lauke Limit iterations, o skaičiavimo tikslumą lauke Santykinė klaida (pagal pagal numatytuosius nustatymus jų reikšmės yra atitinkamai 100 ir 0,0001). Be šių nustatymų, pasirenkame skaičiavimų atlikimo parinktį: automatiškai arba rankiniu būdu. Naudodami automatinius skaičiavimus, „Excel“ iš karto pateikia galutinį rezultatą su rankiniu skaičiavimu, galite pamatyti kiekvienos iteracijos rezultatą.

Ryžiai. 8. Funkcijos grafikas

Pasirinkime savavališką langelį, priskirkime jam naują pavadinimą, tarkime - X, ir įveskime į jį pasikartojančią formulę, nurodančią skaičiavimus naudojant Niutono metodą:

kur F ir F1 atitinkamai apibrėžia funkcijos ir jos išvestinės reikšmės apskaičiavimo išraiškas. Mūsų kvadratinei lygčiai, įvedus formulę, langelyje atsiras reikšmė 2, atitinkanti vieną iš lygties šaknų (8 pav.). Mūsų atveju, pradinis aproksimavimas nebuvo nurodytas, kartotinis skaičiavimo procesas prasidėjo nuo numatytosios reikšmės, saugomos X langelyje ir lygia nuliui. Kaip gauti antrą šaknį? Paprastai tai galima padaryti pakeitus pradinį spėjimą. Pradinių nustatymų nustatymo problema kiekvienu atveju gali būti išspręsta įvairiais būdais. Mes parodysime vieną techniką, pagrįstą IF funkcija. Siekiant padidinti skaičiavimų aiškumą, ląstelėms buvo suteikti prasmingi pavadinimai (9 pav.).

Ląstelėje Khnach (B4) įvedame pradinį aproksimaciją - 5.

Langelyje Hcurrent (C4) parašykite formulę:
=IF(Srovė=0;Хnach; Хsrovė-(Хsrovė^2-5*Srovė+6)/(2*Srovė-5)).

D4 langelyje dedame formulę, kuri nurodo skaičiavimą funkcijų reikšmės taške Hcurrent, kuris leis stebėti sprendimo procesą.

Atkreipkite dėmesį, kad pirmajame skaičiavimo etape pradinė reikšmė bus įdėta į Xcurrent langelį, o tada skaičiavimas prasidės naudojant formulę tolesniuose etapuose.

Norint pakeisti pradinį aproksimaciją, neužtenka pakeisti Hnach langelio turinį ir pradėti skaičiavimo procesą. Tokiu atveju skaičiavimai bus tęsiami nuo paskutinio skaičiavimo

reikšmės. Norėdami iš naujo nustatyti XCurrent langelyje saugomą reikšmę, turite iš naujo parašyti formulę ten. Norėdami tai padaryti, tiesiog pasirinkite langelį, kuriame yra redagavimo formulė, dukart spustelėdami jį (ląstelės turinys bus rodomas formulės juostoje). Spustelėjus mygtuką Enter, bus pradėtas skaičiavimas su nauju pradiniu spėjimu.

2.2. Parametrų pasirinkimas

Kai žinomas norimas formulės skaičiavimo rezultatas, bet nežinomos reikšmės, reikalingos šiam rezultatui gauti, galite naudoti Parametrų pasirinkimo įrankį, meniu Įrankiai pasirinkę komandą Parametrų pasirinkimas. Kai pasirenkate parametrą, „Excel“ keičia reikšmę viename konkrečiame langelyje, kol formulė, nurodanti tą langelį, apskaičiuos norimą rezultatą.

Paimkime kaip pavyzdį tą pačią kvadratinę lygtį x2-5x+6=0. Norėdami rasti lygties šaknis, atlikite šiuos veiksmus:

C3 langelyje (10 pav.) įvedame formulę funkcijos reikšmei apskaičiuoti,

stovinčios lygybės kairėje nuo lygybės ženklo. Kaip argumentą naudojame nuorodą į langelį C2, t.y. =C2^2-5*C2+6.

Dialogo lange Pasirinkti parametrą (10 pav.) langelyje esančiame laukelyje Nustatyti įveskite nuorodą į langelį su formule, lauke Reikšmė - laukiamą rezultatą, lauke Keisti langelių reikšmes - nuoroda į langelį, kuriame bus saugoma pasirinkto parametro reikšmė (šio langelio turinys negali būti formulė).

Spustelėjus mygtuką Gerai, „Excel“ parodys dialogo langą Parametrų pasirinkimo rezultatas. Jei pasirinktą reikšmę reikia išsaugoti, spustelėkite Gerai ir rezultatas bus išsaugotas langelyje, nurodytame anksčiau lauke Keisti langelių vertes. Norėdami atkurti reikšmę, kuri buvo langelyje C2 prieš naudojant komandą Pasirinkti parametrą, spustelėkite Atšaukti.

Pasirinkdama parametrą, „Excel“ naudoja kartotinį (ciklinį) procesą. Pakartojimų skaičius ir tikslumas nustatomi meniu Įrankiai / Parinktys / Skaičiavimai skirtuke. Jei „Excel“ atlieka sudėtingą parametrų parinkimo užduotį, dialogo lange Parametrų pasirinkimo rezultatas galite spustelėti mygtuką Pristabdyti, kad nutrauktumėte skaičiavimą, tada spustelėkite mygtuką „Žingsnis“, kad atliktumėte kitą iteraciją ir peržiūrėtumėte rezultatą. Sprendžiant problemą žingsnis po žingsnio režimu, pasirodo mygtukas Tęsti, kad grįžtumėte į įprastą parametrų pasirinkimo režimą.

Grįžkime prie pavyzdžio. Vėl kyla klausimas: kaip gauti antrą šaknį? Kaip ir ankstesniu atveju, būtina nustatyti pradinį aproksimaciją. Tai galima padaryti taip (11 pav., a):

A

b

Ryžiai. 11. Antrosios šaknies radimas

X langelyje (C2) įvedame pradinį aproksimaciją.

Ląstelėje Xi (C3) įvedame formulę sekančiai šaknies aproksimacijai apskaičiuoti, t.y. =X-(X^2-5*X+6)/(2*X-5).

C4 langelyje dedame formulę, kuri nurodo funkcijos reikšmės apskaičiavimą kairėje pradinės lygties pusėje taške Xi.

Po to pasirinkite komandą Pasirinkti parametrą, kur keičiamą langelį paimame C2. Skaičiavimo rezultatas parodytas fig. 11, b (ląstelėje C2 - galutinė reikšmė, o langelyje C3 - ankstesnė).

Tačiau visa tai galima padaryti kiek paprasčiau. Norint rasti antrąją šaknį, pakanka į langelį C2 kaip pradinį aproksimaciją įdėti konstantą 5 (10 pav.) ir tada pradėti parametrų pasirinkimo procesą.

2.3. Sprendimo paieška

Parametrų pasirinkimo komanda yra patogi sprendžiant konkrečios tikslinės reikšmės, priklausančios nuo vieno nežinomo parametro, paieškos problemas. Sudėtingesnėms problemoms spręsti turėtumėte naudoti komandą Ieškoti sprendimo (sprendimo), kurią galite pasiekti per meniu punktą Įrankiai / Sprendimo paieška.

Problemos, kurias galima išspręsti naudojant sprendimo paiešką, paprastai formuluojamos taip:

Rasti:
x1, x2, …, xn
tokia, kad:
F(x1, x2, …, xn) > (maks.; min.; = reikšmė)
su apribojimais:
G(x1, x2, ... , xn) > (>Vertė;< Value; = Value}

Kintamieji, kurių ieškote – Excel darbalapio langeliai – vadinami reguliuojamais langeliais. Tikslo funkcija F(x1, x2, ..., xn), kartais vadinama tiesiog tikslu, turėtų būti nurodyta kaip formulė darbalapio langelyje. Šioje formulėje gali būti vartotojo apibrėžtų funkcijų ir ji turi priklausyti nuo koreguojamų langelių (nuorodos). Problemos nustatymo momentu nustatoma, ką daryti su tikslo funkcija. Galite pasirinkti vieną iš parinkčių:

rasti tikslo funkcijos F(x1, x2, ..., xn) maksimumą;

rasti tikslo funkcijos F(x1, x2, ..., xn) minimumą;

užtikrinti, kad tikslo funkcija F(x1, x2, … , xn) turėtų fiksuotą reikšmę: F(x1, x2, … , xn) = a.

Funkcijos G(x1, x2, ..., xn) vadinamos apribojimais. Jas galima nurodyti ir kaip lygybes, ir kaip nelygybes. Kontroliuojamoms ląstelėms gali būti taikomi papildomi apribojimai: neneigiamumas ir/ar sveikumas, tada ieškomas norimas sprendimas teigiamų ir/ar sveikųjų skaičių srityje.

Ši formuluotė apima platų optimizavimo problemų spektrą, įskaitant įvairių lygčių ir lygčių sistemų sprendimą, tiesinio ir netiesinio programavimo uždavinius. Tokias problemas paprastai lengviau suformuluoti nei išspręsti. Ir tada, norint išspręsti konkrečią optimizavimo problemą, reikalingas specialiai sukurtas metodas. Spręstiklis savo arsenale turi galingų įrankių tokioms problemoms spręsti: apibendrintą gradiento metodą, simpleksinį metodą, šakinį ir susietąjį metodą.

Aukščiau, norint rasti kvadratinės lygties šaknis, buvo naudojamas Niutono metodas, naudojant ciklines nuorodas (1 veiksmas) ir parametrų pasirinkimo įrankis (2 punktas).

Pamokos tikslas: toliau lavinkite skaičiuoklės įgūdžius.

• edukacinis:
ugdyti gebėjimus kurti, redaguoti, formatuoti ir atlikti nesudėtingus skaičiavimus skaičiuoklėse.
• kuriant:
plėsti mokinių supratimą apie galimas skaičiuoklių taikymo sritis; ugdyti analitinį mąstymą, kalbos ir dėmesio įgūdžius.
• edukacinis:
formuoti ir ugdyti pažintinį susidomėjimą; diegti savarankiškumo darbe įgūdžius.

Pamokos planas.

1. Laiko organizavimas.
2. Mokinių žinių atnaujinimas.
3. Namų darbų tikrinimas.
4. Problemų sprendimas.
5. Savarankiškas problemų sprendimas.
6. Apibendrinant. Įvertinimai.
7. Namų darbai.

Per užsiėmimus

1. Organizacinis momentas.

Informuokite pamokos temą, suformuluokite pamokos tikslus ir uždavinius.

Šiandien mes vėl aplankysime mažą milžiną Vasiją Pasakų žemėje. Kaip visada, jam reikia jūsų pagalbos, vaikinai.

Ar galite padėti Vasijai? Dabar patikrinkime!

2. Mokinių žinių atnaujinimas.

1) Atsakykite į klausimus žodžiu.

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

• Kas yra skaičiuoklė?
• Kokius pagrindinius skaičiuoklės elementus žinote?
• Kaip nustatyti langelio (eilutės, stulpelio) pavadinimą skaičiuoklėje?
• Koks gali būti ląstelės turinys?
• Skaičius 1 yra stulpelyje..., eilutėje..., langelyje su adresu...
• Skaičius 4 yra langelyje su adresu...
• Kokios yra formulių rašymo ląstelėse taisyklės?
• Kokia reikšmė apskaičiuojama pagal formulę langelyje C1?
• Kokia reikšmė apskaičiuojama pagal formulę langelyje D2?

2) Koks rezultatas bus gautas ląstelėse su formulėmis?

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

Atsakymas: 25*4/2=50

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

• Ką reiškia =SUM(B1:D3)?
• Kiek elementų yra bloke B1:D3? Atsakymas: 9.
• D3 langelio turinys? Atsakymas: 5+2+1+6+8+3+8+3+4= 40

3) Namų darbų tikrinimas

Plaukimo varžybų rezultatai

Vienas mokinys pasakoja, kaip atliko namų darbus (per projektorių).

№	PILNAS VARDAS.	1	2	3	Geriausias laikas	Vidutinis laikas		Nukrypimas
1	Liaguškinas	3.23	3.44	3.30
2	Moržovas	3.21	3.22	3.24
3	Akulovas	3.17	3.16	3.18
4	Rybinas	3.24	3.20	3.18
5	Čerepakhinas	3.56	3.44	3.52
	Maksimalus nuokrypis

• Vidutinis kiekvieno sportininko laikas randamas kaip jo trijų plaukimų aritmetinis vidurkis.
• „Geriausio laiko“ langelyje įrašomas minimalus 3 plaukimų rezultatas.
• Langelyje rašoma „Geriausias konkurso rezultatas“. minimalus laikas iš kolonos.
• Stulpelyje „Nukrypimas“ įrašomas skirtumas tarp geriausio sportininko laiko ir geriausio varžybų rezultato.
• Didžiausia stulpelio reikšmė įrašoma langelyje „Didžiausias nuokrypis“.

Plaukimo varžybų rezultatai
№	PILNAS VARDAS.	1	2	3	Geriausias laikas	Vidutinis laikas	Nukrypimas
1	Liaguškinas	3,23	3,44	3,30	3,23	3,32	0,07
2	Moržovas	3,21	3,22	3,24	3,21	3,22	0,05
3	Akulovas	3,17	3,16	3,18	3,16	3,17	0,00
4	Rybinas	3,24	3,20	3,18	3,18	3,21	0,02
5	Čerepakhinas	3,56	3,44	3,52	3,44	3,51	0,28
	Geriausias konkurso rezultatas					3,16
	Vidutinis konkurentų laikas					3,29
	Maksimalus nuokrypis					0,28

4) Paprastų uždavinių sprendimas.

Mažasis milžinas Vasja nusprendė suremontuoti tvorą aplink savo sodą ir iškasti ją daržovėms sodinti (atėjo dar vienas pavasaris) ir pažymėti stačiakampes lysves. Norėdami dirbti, jam reikėjo rasti tvoros ilgį ir sklypo plotą. Bet jis niekada nelankė mokyklos. Padėkime Vasijai.

Nr. 1. Apskaičiuokite stačiakampio su kraštinėmis perimetrą ir plotą:

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

Kartu su vaikais aptariame šią užduotį:

• Kaip suprojektuoti stalą?
• Kokias formules turėčiau naudoti?
• Kaip naudoti jau parašytas formules kitam stačiakampiui?

Stalo dizainas – ant lentos ir sąsiuviniuose.

Tuo pat metu kitas studentas savarankiškai sprendžia kitą problemą ir savo sprendimą pristato mokiniams (projektorių pagalba).

Aptarę problemos Nr. 2 sprendimą, pereiname prie kitos problemos sprendimo.

Vienas mokinys parodo, kaip dirbti su formulėmis, kitas – kaip naudotis sumos funkcija, skaičių formatu (bendrasis, piniginis) ir kt. (Lentelė jau paruošta, mokiniai turės įvesti formules, naudoti sumavimą ir gauti atsakymą).

Nr. 3. Apskaičiuokite, naudodami ET, ar užtenka 150 rublių, kad Vasja nupirktų visus produktus, kuriuos jam užsakė mama, ir ar užteks traškučių už 10 rublių? Mama leido man įdėti pinigus į taupyklę. Kiek rublių pateks į taupyklę?

Siūlomas sprendimas:

№	vardas	Kaina rubliais	Kiekis	Kaina
1	duona	9,6	2	=C2*D2
2	Kava	2,5	5	=C3*D3
3	Pienas	13,8	2	=C4*D4
4	Koldūnai	51,3	1	=C5*D5
				=SUM(E2:E5)
Po apsipirkimo bus			=150-E6
Nusipirkus traškučių bus			=D7-10

5) Savarankiškas problemų sprendimas.

Mažasis milžinas Vasja dažnai lankydavosi pas Gėlių miesto gyventojus.

Eidami į paplūdimį linksmi žmogeliukai nusprendė apsirūpinti atsargomis gaivieji gėrimai. Dunno su savimi pasiėmė 2 litrus giros, 1 litrą sodos ir 1 litrą aviečių sirupo, Donut - 3 litrus sodos ir 2 litrus aviečių sirupo, Toropyzhka - 2 litrus sodos, daktarą Pilyulkiną - 1 litrą giros ir 1 litrą. ricinos aliejaus.

• Kiek litrų kiekvienos rūšies gėrimo išgėrė visi maži žmogeliukai kartu?
• Kiek litrų gėrimų pasiėmė kiekvienas iš vyrų?
• Kiek litrų gėrimų išgėrė visi mažieji žmogeliukai kartu?

Suprojektuokite lentelę taip, kaip norite, ir išsaugokite ją savo asmeniniame aplanke.

Darbo rezultatas.

Linksmi žmonės. Gėrimai.
Gerti	Nežinau	Spurga	Toropyžka	Pilyulkinas	Iš viso
Gira, l	2	0	0	1	3
Soda, l	1	3	2	0	6
Sirupas, l	1	2	0	0	9
Ricinos aliejus, l	0	0	0	1	1
IŠ VISO:	4	5	2	2	13

7) Apibendrinimas. Įvertinimai.

8) Namų darbai.

Pagalvokite ir išspręskite šią problemą, jei žinomi ir šie dydžiai.

Kaip keisis lentelė? Kokios formulės pasirodys?

Žinoma, kad 1 litras giros Gėlių mieste kainuoja 1 monetą, 1 litras sodos – 3 monetas, 1 litras aviečių sirupo – 6 monetas, 1 litras ricinos aliejaus – 2 monetas.

• Kiek monetų kiekvienas išleido pirkdamas gėrimus?
• Kiek monetų buvo išleista perkant kiekvienos rūšies gėrimus?
• Kiek pinigų visi žmonės išleido kartu?

Literatūra

1. Informatika. Probleminė knyga-dirbtuvės 2 tomuose /Red. I.G.Semakina, E.K.Henner - M.: Pagrindinių žinių laboratorija, 2010 m.
2. Efimova O. Kompiuterinių technologijų kursas su informatikos pagrindais. – M.: UAB „AST leidykla“; ABF, 2005 m.

Šiame straipsnyje sužinosite, kaip Išspręskite kvadratinę lygtįExcelįjungta konkretus pavyzdys. Išsamiai išanalizuosime paprastos problemos su paveikslėliais sprendimą.

Sprendimo eiga

Paleiskite „Microsoft Office Excel“. Aš naudoju 2007 m. versiją. Pirmiausia sujungkime langelius A1:A5 ir į juos parašykime kvadratinės lygties formulę forma ax2+bx+c=0. Toliau reikia kvadratuoti x, kad tai padarytume skaičių 2 viršutiniu indeksu. Pasirinkite du ir spustelėkite dešiniuoju pelės mygtuku.

Gauname ax 2 +bx+c=0 formos formulę

A2 langelyje atitinkamai įvedame teksto reikšmę a=, langelyje A3 b= ir langelyje A4 c=. Šios reikšmės bus įvedamos iš klaviatūros šiuose langeliuose (B2, B3, B4).

Įveskime verčių, kurios bus skaičiuojamos, tekstą. C2 langelyje d=, C3 x 1 = C4 x 2 =. Padarykite, kad x subtiesinis tarpas būtų panašus į viršutinio indekso tarpus x 2

Pereikime prie sprendimo formulių įvedimo

Kvadratinio trinalio diskriminantas yra b 2 -4ac

D2 langelyje įveskite atitinkamą formulę, kaip skaičių pakelti į antrą laipsnį:

Kvadratinė lygtis turi dvi šaknis, jei diskriminantas yra didesnis už nulį. C3 langelyje įveskite x 1 formulę

IF(D2>0;(-B3+ROOT(D2))/(2*B2);"Nėra šaknų"

Norėdami apskaičiuoti x2, pateikiame panašią formulę, bet su pliuso ženklu

IF(D2>0;(-B3-ROOT(D2))/(2*B2);"Nėra šaknų"

Atitinkamai su įvestomis reikšmėmis a, b, c pirmiausia apskaičiuojamas diskriminantas, jei jo reikšmės yra mažesnės už nulį, rodomas pranešimas „Nėra šaknų“, kitaip gauname reikšmes x 1 ir x 2.

Lapo apsauga programoje Excel

Turime apsaugoti lapą, kuriame atlikome skaičiavimus. Be apsaugos turite palikti langelius, į kuriuos galite patekti reikšmės a,b,c, tai yra ląstelės B2 B3 B4. Norėdami tai padaryti, pasirinkite šį diapazoną ir eikite į langelio formatą, eikite į skirtuką Apžvalgos, Apsaugokite lapą ir atžymėkite laukelį Apsaugotas langelis. Spustelėkite Gerai, kad patvirtintumėte atliktus pakeitimus.

Šis langelių diapazonas nebus apsaugotas, kai darbalapis bus apsaugotas. Apsaugokime lapą, kad tai padarytumėte, eikite į skirtuką „Peržiūra“ ir pasirinkite „Sheet Protection“. Įveskime slaptažodį 1234. Spustelėkite Gerai.

Dabar galime pakeisti langelių B2, B3, B4 reikšmes. Kai bandysime pakeisti kitus langelius, gausime tokį pranešimą: „Ląstelė arba diagrama apsaugota nuo pakeitimų. Taip pat patarimai dėl apsaugos pašalinimo.

Galbūt jus taip pat domina medžiaga, kaip ją apsaugoti.

Nemaža dalis problemų, kurios išsprendžiamos naudojant skaičiuokles, daro prielaidą, kad vartotojas jau turi bent kai kuriuos pradinius duomenis norimam rezultatui rasti. Tačiau „Excel 2010“ turi reikiamus įrankius, su kuriais šią problemą galite išspręsti atvirkščiai – pasirinkite reikiamus duomenis, kad gautumėte norimą rezultatą.

„Sprendimo paieška“ yra viena iš šių įrankių, patogiausia „optimizavimo problemoms“ spręsti. Ir jei jums nereikėjo jo naudoti anksčiau, dabar pats laikas tai pataisyti.

Taigi, mes pradedame diegdami šį priedą (nes jis nepasirodys pats). Laimei, dabar tai galima padaryti gana paprastai ir greitai - atidarykite meniu „Įrankiai“ ir jau jame „Priedai“

Belieka stulpelyje „Valdymas“ nurodyti „Excel“ priedus, tada spustelėkite mygtuką „Eiti“.

Atlikus šį paprastą veiksmą, „Duomenys“ bus rodomas aktyvinimo mygtukas „Ieškoti sprendimo“. Kaip parodyta paveikslėlyje

Pažiūrėkime, kaip tinkamai naudoti sprendimų paiešką programoje „Excel 2010“ su keliais paprastais pavyzdžiais.

Vienas pavyzdys .

Tarkime, jūs užimate didelio gamybos skyriaus vadovo pareigas ir turite teisingai paskirstyti priedus darbuotojams. Tarkime, bendra premijų suma yra 100 000 rublių, ir būtina, kad priedai būtų proporcingi atlyginimams.

Tai yra, dabar turime pasirinkti teisingą proporcingumo koeficientą, kad nustatytume premijos dydį, palyginti su atlyginimu.

Visų pirma reikia greitai susikurti (jei tokios dar neturite) lentelę, kurioje bus saugomos pradinės formulės ir duomenys, pagal kuriuos galima gauti norimą rezultatą. Mums šis rezultatas yra bendra priemokos suma. O dabar atkreipkite dėmesį – tikslinė ląstelė C8 turi būti susieta naudojant formules su norima keisti langeliu adresu E2. Tai labai svarbu. Pavyzdyje juos sujungiame naudodami tarpines formules, kurios yra atsakingos už kiekvieno darbuotojo premijos apskaičiavimą (C2:C7).

Dabar galite suaktyvinti „Sprendimų paieška“. Atsidarys naujas langas, kuriame turėsime nurodyti reikiamus parametrus.

Pagal " 1 “ nurodo mūsų tikslinę ląstelę. Gali būti tik vienas.

« 2 “ – tai galimi variantai optimizavimas. Galima rinktis iš viso „Maksimalumo“, „Minimalumo“ arba „Konkrečios“ vertės. Ir jei jums reikia konkrečios vertės, turite ją nurodyti atitinkamame stulpelyje.

« 3 "-gali būti keli keičiami langeliai (visas diapazonas arba atskirai nurodyti adresai). Galų gale, „Excel“ dirbs su jais, rūšiuodama parinktis taip, kad būtų gauta tikslinėje ląstelėje nurodyta reikšmė.

« 4 "- Jei jums reikia nustatyti apribojimus, turėtumėte naudoti mygtuką "Pridėti", bet mes pažvelgsime į tai šiek tiek vėliau.

« 5 » — mygtukas, skirtas perjungti į interaktyvius skaičiavimus pagal mūsų nurodytą programą.

Bet dabar grįžkime prie galimybės pakeisti savo užduotį naudodami mygtuką „Pridėti“. Šis etapas yra gana svarbus (ne mažiau nei formulių sudarymas), nes tai yra apribojimas, kuris leidžia mums gauti teisingas rezultatas prie išėjimo. Čia viskas daroma kuo patogiau, todėl galite juos nustatyti ne tik visam diapazonui vienu metu, bet ir tam tikroms ląstelėms.

Norėdami tai padaryti, galite naudoti daugybę specifinių (ir visiems „Excel 2010“ naudotojams žinomų) simbolių „=“, „>=“, „<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Tačiau mūsų pavyzdyje gali būti tik vienas apribojimas – teigiamas koeficientas. Žinoma, galite jį nustatyti keliais būdais – naudodami „Pridėti“ (tai vadinama „aiškiai nurodant apribojimą“) arba tiesiog pažymėdami funkciją „Padaryti neapribotus kintamuosius neneigiamus“ aktyvią. Tai galima padaryti „Sprendimų paieškos“ priede spustelėjus mygtuką „Parinktys“.

Beje, patvirtinę parametrus ir paleidę programą (mygtukas „Vykdyti“), rezultatą galite peržiūrėti lentelėje. Tada programa parodys „paieškos rezultatų“ langą.

Jei parodytas rezultatas jums visiškai tinka, belieka jį patvirtinti dar kartą (mygtukas „OK“), kuris įrašys rezultatą į jūsų lentelę. Jei kažkas skaičiavimuose jums netinka, turite atšaukti rezultatą (mygtukas „Atšaukti“), grįžti į ankstesnę lentelės būseną ir ištaisyti klaidas.

Teisingas pavyzdinės problemos sprendimas turėtų atrodyti taip

Labai svarbu, kad norint gauti teisingą rezultatą net ir pakeitus pradinius duomenis, reikia iš naujo paleisti „Sprendimų paiešką“.

Norėdami išsamiau pažvelgti į šios programos veikimą, pažvelkime į kitą pavyzdį.

Tarkime, esate didelės baldų įmonės savininkas ir turite organizuoti gamybą taip, kad gautumėte kuo didesnį pelną. Gaminate tik knygų lentynas, turinčias tik du modelius - „A“ ir „B“, kurių gamybą riboja tik aukštos kokybės lentų prieinamumas (arba nebuvimas), taip pat mašinos laikas (apdorojimas mašina).

Modeliui „A“ reikia 3 m 3 lentų, o modeliui „B“ – dar 1 m 3 (tai yra 4). Iš tiekėjų per savaitę gausite daugiausiai 1700 m 3 lentų. Šiuo atveju modelis „A“ sukuriamas per 12 mašinos veikimo minučių, o „B“ – per 30 minučių. Iš viso mašina gali dirbti ne daugiau kaip 160 valandų per savaitę.

Kyla klausimas: kiek produktų (ir kokį modelį) įmonė turėtų pagaminti per savaitę, kad gautų didžiausią įmanomą pelną, jei lentyna „A“ duoda 60 rublių pelno, o „B“ - 120?

Kadangi procedūra žinoma, pradedame kurti mums reikalingą lentelę su duomenimis ir formulėmis. Ląstelių vietą, kaip ir anksčiau, galite nustatyti savo nuožiūra. Arba naudokite mūsų

Bet kokiu patogiu būdu paleidžiame „Sprendimų paiešką“, įvedame duomenis ir atliekame nustatymus.

Taigi pažiūrėkime, ką turime. Tikslinėje ląstelėje F7 yra formulė, kuri apskaičiuos pelną. Optimizavimo parametrą nustatome maksimaliai. Tarp keičiamų langelių turime „F3: G3“. Apribojimai – visos aptiktos reikšmės turi būti neneigiami sveikieji skaičiai, bendras mašinos laikas neviršija 160 (mūsų langelis D9), žaliavų kiekis neviršija 1700 (ląstelė D8).

Žinoma, šiuo atveju buvo galima nurodyti ne langelių adresus, o tiesiogiai įvesti reikiamas skaitmenines reikšmes, tačiau jei naudojate adresus, tada lentelėje galima atlikti apribojimų pakeitimus, kurie padės apskaičiuoti pelną. šios įmonės ateityje, kai pasikeis pirminiai duomenys.

Mes aktyvuojame programą, ir ji paruošia sprendimą.

Tačiau tai nėra vienintelis sprendimas ir galite gauti kitokį rezultatą. Taip gali nutikti net jei visi duomenys buvo įvesti teisingai, o formulėse taip pat nebuvo klaidų.

Taip. Taip gali nutikti net jei programai liepėme ieškoti visas numerį. Ir jei tai staiga atsitiks, tereikia atlikti papildomus „Sprendimų paieškos“ pakeitimus. Atidarykite langą „Ieškoti sprendimų“ ir įveskite „Parinktys“.

Mūsų pagrindinis parametras yra atsakingas už tikslumą. Kuo jis mažesnis, tuo tikslumas didesnis, o mūsų atveju tai žymiai padidina tikimybę gauti sveikąjį skaičių. Antrasis parametras („Ignoruoti sveikųjų skaičių apribojimus“) atsako į klausimą, kaip galėjome gauti tokį atsakymą, nes užklausoje aiškiai nurodėme sveikąjį skaičių. „Sprendimų ieškiklis“ tiesiog ignoravo šį apribojimą, nes tai nurodė išplėstiniai nustatymai.

Taigi būkite labai atsargūs ateityje.

Trečias ir galbūt paskutinis pavyzdys. Pabandykime kuo labiau sumažinti transporto įmonės išlaidas, naudodamiesi sprendimų paieška programoje Excel 2010.

Taigi, statybų įmonė duoda užsakymą pervežti smėlį, kuris paimamas iš 3 tiekėjų (karjerų). Jį reikia pristatyti 5 skirtingiems vartotojams (kurie yra statybvietės). Krovinio pristatymo kaina yra įtraukta į objekto kainą, todėl mūsų užduotis yra užtikrinti krovinių pristatymą į statybvietes su minimaliomis išlaidomis.

Turime - smėlio pasiūlą karjere, statybos aikštelių poreikį smėliui, transportavimo išlaidas "tiekėjas-vartotojas".

Būtina rasti optimalaus krovinio pervežimo (į ir iš) schemą, kurioje bendra pervežimo kaina būtų minimali.

Pilkuose mūsų lentelės langeliuose yra sumų pagal stulpelius ir eilutes formulės, o tikslinis langelis yra formulė, skirta bendrai prekių pristatymo sąnaudoms apskaičiuoti. Paleidžiame „Sprendimo paieška“ ir atliekame reikiamus nustatymus

Po to mes pradedame ieškoti šios problemos sprendimo.

Tačiau nepamirškime, kad neretai transporto užduotis gali apsunkinti ir kai kurie papildomi apribojimai. Tarkime, kelyje iškilo komplikacija ir dabar tiesiog techniškai neįmanoma pristatyti krovinio iš karjero 2 į statybvietę 3. Kad į tai būtų atsižvelgta, tereikia pridėti papildomą apribojimą „$D$13=0“. Ir jei dabar paleisite programą, rezultatas bus kitoks

Galiausiai belieka pasakyti apie sprendimo metodo pasirinkimą. Ir jei problema tikrai labai sudėtinga, tada norint gauti norimą rezultatą, greičiausiai turėsite pasirinkti reikiamą sprendimo būdą.

Tai viskas šiuo klausimu.

Sprendimų ieškojome programoje Excel 2010 – sudėtingoms problemoms spręsti

Dauguma problemų, išspręstų naudojant skaičiuoklę, apima norimo rezultato radimą naudojant žinomus šaltinio duomenis. Tačiau „Excel“ turi įrankių, leidžiančių išspręsti atvirkštinę problemą: pasirinkite pradinius duomenis, kad gautumėte norimą rezultatą.

Viena iš tokių priemonių yraSprendimo paieška, kuris ypač patogus sprendžiant vadinamąsias „optimizavimo problemas“.

Jei anksčiau jo nenaudojoteSprendimo paieška, tada turėsite įdiegti atitinkamą priedą.

Tai galite padaryti taip:

senesnėms nei „Excel 2007“ versijoms naudodami komandą Meniu Paslauga --> Priedai;

nuo Excel 2007 per dialogo langą„Excel“ parinktys

Nuo Excel 2007 mmygtuką pradėtiSprendimo paieška bus rodomas skirtuke Duomenys.

Ankstesnėse „Excel 2007“ versijose panaši komanda bus rodoma meniuAptarnavimas

Pažvelkime į darbo tvarką Sprendimo paieška naudojant paprastą pavyzdį.

1 pavyzdys. Premijos paskirstymas

Tarkime, kad esate gamybos skyriaus vadovas ir turite teisingai paskirstyti 100 000 rublių premiją. tarp skyriaus darbuotojų proporcingai jų pareiginiams atlyginimams. Kitaip tariant, norint apskaičiuoti priedo prie atlyginimo dydį, reikia pasirinkti proporcingumo koeficientą.

Pirmiausia sukuriame lentelę su pradiniais duomenimis ir formulėmis, su kuriomis turėtų būti gautas rezultatas. Mūsų atveju rezultatas yra bendra priemokos suma. Labai svarbu, kad tikslinė ląstelė (C8) būtų susieta per formules su norima keisti ląstele (E2). Pavyzdyje jie sujungiami per tarpines formules, pagal kurias apskaičiuojama premijos suma kiekvienam darbuotojui (C2:C7).

Dabar paleiskite Sprendimo paieška ir atsidariusiame dialogo lange nustatykite reikiamus parametrus. Išvaizda Dialogo langai skirtingose ​​versijose šiek tiek skiriasi:

Nuo Excel 2010

Prieš Excel 2010

Paspaudus mygtukąRasti sprendimą (paleisti)Gautą rezultatą jau galite pamatyti lentelėje. Tuo pačiu metu ekrane pasirodo dialogo langasSprendimo paieškos rezultatai.

Nuo Excel 2010

Prieš Excel 2010

Jei rezultatas, kurį matote lentelėje, jums tinka, tada dialogo lange Sprendimo paieškos rezultatai paspauskite Gerai ir įrašykite rezultatą į lentelę. Jei rezultatas jums netinka, spustelėkite Atšaukti ir grįžti į ankstesnę lentelės būseną.

Šios problemos sprendimas atrodo taip

Svarbu: bet kokiems šaltinio duomenų pakeitimams gauti naują rezultatą Sprendimo paieška vėl teks bėgti.

Pažvelkime į kitą optimizavimo problemą (maksimalizuojant pelną)

2 pavyzdys. Baldų gamyba (pelno didinimas)

Įmonė gamina dviejų modelių A ir B surenkamas knygų lentynas.

Jų gamybą riboja žaliavų (aukštos kokybės plokščių) prieinamumas ir mašinų apdorojimo laikas.

Kiekvienam modelio A gaminiui reikia 3 m²lentos, o gaminio modeliui B - 4 m². Per savaitę įmonė iš tiekėjų gali gauti iki 1700 m² lentų.

Kiekvienam modelio A gaminiui reikia 12 minučių mašinos laiko, o gaminio modeliui B – 30 min. Per savaitę mašina gali būti naudojama 160 valandų.

Kiek kiekvieno modelio gaminių įmonė turėtų pagaminti per savaitę, kad pasiektų maksimalų pelną, jei kiekvienas A modelio gaminys atneša 60 rublių. pelno, o kiekvienas B modelio gaminys kainuoja 120 rublių. atvyko?

Mes jau žinome procedūrą.

Pirmiausia sukuriame lenteles su šaltinio duomenimis ir formulėmis. Ląstelių išdėstymas lape gali būti visiškai savavališkas, kaip patogu autoriui. Pavyzdžiui, kaip nuotraukoje

Paleidžiame Sprendimo paieškair dialogo lange nustatykite reikiamus parametrus

1. Tikslinėje langelyje B12 yra pelno apskaičiavimo formulė
2. Optimizavimo parametras – maksimalus
3. Modifikuoti langeliai B9:C9
4. Apribojimai: rastos reikšmės turi būti sveikosios, ne neigiamos; bendras kompiuterio darbo laikas neturi viršyti 160 valandų (nuoroda į langelį D16); bendras žaliavų kiekis neturi viršyti 1700 m² (atskaitos langelis D15). Čia vietoj nuorodų į langelius D15 ir D16 galima nurodyti skaičius, tačiau naudojant nuorodas bet kokius apribojimų pakeitimus galima atlikti tiesiogiai lentelėje
5. Paspausk mygtuką Rasti sprendimą (paleisti) ir po patvirtinimo gauname rezultatą

Bet net jei teisingai sukūrėte formules ir nustatėte apribojimus, rezultatas gali būti netikėtas. Pavyzdžiui, spręsdami šią problemą galite pamatyti tokį rezultatą:

Ir tai nepaisant to, kad apribojimas buvo nustatytas visas. Tokiais atvejais galite pabandyti pakoreguoti nustatymus Sprendimo paieška. Norėdami tai padaryti lange Sprendimo paieška Paspausk mygtuką Galimybės ir patenkame į to paties pavadinimo dialogo langą

Pirmasis iš pasirinktų parametrų yra atsakingas už skaičiavimų tikslumą. Sumažinus jį, galima pasiekti tikslesnį rezultatą, mūsų atveju – sveikųjų skaičių. Antroji iš paryškintų parinkčių (galima nuo Excel 2010) atsako į klausimą: kaip būtų galima gauti trupmeninius rezultatus ribojantvisas? Pasirodo Sprendimo paieškašis apribojimas buvo tiesiog ignoruojamas pagal pažymėtą vėliavėlę.

3 pavyzdys. Transporto problema (išlaidų sumažinimas)

Statybos įmonės užsakymu smėlis vežamas iš trijų tiekėjų (karjerų) iki penkių vartotojų (statybų). Pristatymo kaina yra įskaičiuota į projekto kainą, todėl statybų įmonė suinteresuota, kad savo statybų aikštelių smėlio poreikius patenkintų pigiausiu būdu.

Duota: smėlio atsargos karjeruose; statybvietės smėlio poreikiai; transportavimo išlaidos tarp kiekvienos tiekėjo ir vartotojo poros.

Būtina rasti optimalią pervežimo schemą poreikiams tenkinti (iš kur ir į kur), kurioje bendros transportavimo išlaidos būtų minimalios.

Ląstelių su šaltinio duomenimis ir apribojimais, norimų langelių ir tikslinės ląstelės vietos pavyzdys parodytas paveikslėlyje

Pilkuose langeliuose yra sumų pagal eilutes ir stulpelius formulės, o tiksliniame langelyje yra formulė bendroms transportavimo sąnaudoms apskaičiuoti..

Paleidžiame sprendimo paiešką ir nustatome reikiamus parametrus (žr. pav.)

Spustelėkite Rasti sprendimą (paleisti) ir gaukite žemiau pateiktą rezultatą

Baigdamas siūlau išbandyti savo jėgas naudojant Sprendimo paieška ir naudokite ją senai problemai išspręsti:

Valstietis turguje už 100 rublių nusipirko 100 galvijų. Jautis kainuoja 10 rub., karvė – 5 rub., veršelis – 50 kapeikų. Kiek bulių, karvių ir veršelių nusipirko valstietis?