Ðåøåíèå ýêîíîìè÷åñêèõ çàäà÷ ñ ïîìîùüþ VBA

ÌÈÍÈÑÒÅÐÑÒÂÎ ÎÁÐÀÇÎÂÀÍÈß ÓÊÐÀÈÍÛ

ÊÀÔÅÄÐÀ ÏÐÎÃÐÀÌÍÎÃÎ ÎÁÅÑÏÅ×ÅÍÈß ÝÂÌ

ÐÅØÅÍÈÅ ÝÊÎÍÎÌÈ×ÅÑÊÈÕ ÇÀÄÀ× Ñ ÏÎÌÎÙÜÞ VBA

Êóðñîâàÿ ðàáîòà

ïî äèñöèïëèíå «Ìàòåì. ìîäåëèðîâàíèå èíôîðìàöèîííûõ ñèñòåì »

Ïîÿñíèòåëüíàÿ çàïèñêà

ÐÅÔÅÐÀÒ

Äàííàÿ êóðñîâàÿ ðàáîòà íîñèò íàçâàíèå “Ðåøåíèå ýêîíîìè÷åñêèõ çàäà÷ ñ ïîìîùüþ Visual Basic for Application”.

Ïðè íàïèñàíèè êóðñîâîé ðàáîòû ïðåñëåäîâàëàñü

öåëü ïîëó÷åíèÿ ïðàêòè÷åñêèõ íàâûêîâ ðåøåíèÿ ýêîíîìè÷åñêèõ çàäà÷ ñïîìîùüþ VBA. Åñëè ãîâîðèòü ïîäðîáíåå òî áûë îïèñàíû ñïîñîáû ðåøåíèÿ ñëåäóþùèõ çàäà÷: íà÷èñëåíèå ïðåìèè â âèäå êîììèñèîííûõ è äîïîëíèòåëüíîé îïëàòû, íà÷èñëåíèå ïðåìèàëüíûõ ïî îïðåäåëåííûì óñëîâèÿì, ìîäåëü óïðàâëåíèÿ çàïàñàìè, çàäà÷à îá îïòèìàëüíûõ êàïèòàëîâëîæåíèÿõ, çàäà÷è íà ïîñòðîåíèå ìàòðèöû è âû÷èñëåíèå ñóììû, íàõîæäåíèå áóìàæíîãî ðàñêðîÿ è áàçà äàííûõ.

Ïîÿñíèòåëüíàÿ çàïèñêà ê êóðñîâîé ðàáîòå ñîñòîèò èç ëèñòîâ, è ðàçäåëÿåòñÿ íà 4 îñíîâíûå ÷àñòè:

1) òåîðåòè÷åñêèå ñâåäåíèÿ â ïðèìåíåíèè ê ðåøåíèþ 8 ïîñòàâëåííûõ çàäà÷ êóðñîâîé ðàáîòû

2) îïèñàíèå àëãîðèòìîâ ðåøåíèÿ ïîñòàâäåííûõ çàäà÷

3) ïðîãðàììà ñîçäàííàÿ â ñðåäå Microsoft Excel 97 SR-1 â ìàêðîñàõ íà îáüåêòíî-îðèåíòèðîâàíîì ÿçûêå âûñîêîãî óðîâíÿ Microsoft Visual Basic

4)ïîäðîáíîå îïèñàíèå ìîäóëåé è ìàêðîñîâ ñîñòîâëÿþùèõ â ñîâîêóïíîñòè åäèíûé äîêóìåíò Kurs ðàçìåðîì 202,101 áàéò.

ÑÎÄÅÐÆÀÍÈÅ

Ââåäåíèå . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1. Òåîðåòè÷åñêèå ñâåäåíèÿ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1.1 Ôóíêöèÿ ïîëüçîâàòåëÿ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1.2 Ïåðåìåííûå è ïîñòîÿííûå . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1.3 Ñòàíäàðòíûå ô-öèè ïîëüçîâàòåëÿ äëÿ ðàáîòû ñ ìàññèâàìè è ìàòðèöàìè

1.4 Îáüåêòû, ñâ-âà è ìåòîäû VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1.5 Îïåðàòîðû öèêëà . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2. Îïèñàíèå ïðîãðàììû . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.1 Îïèñàíèå ïåðåìåííûõ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.2 Âõîäíûå è âûõîäíûå äàííûå . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.3 Ïîäðîáíîå îïèñàíèå çàäà÷ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.3.1 Íà÷èñëåíèå ïðåìèè â âèäå êîììèñèîííûõ è äîïîëíèòåëüíîé

îïëàòû . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.3.2 Íà÷èñëåíèå ïðåìèàëüíûõ ïî îïðåäåëåííûì óñëîâèÿì . . . . . . . . . . . .

2.3.3 Ñîñòàâëåíèå âåäîìîñòè ðàñ÷åòà ïðèáûëè òî òîâàðà . . . . . . . . . . . . . . .

2.3.4 Ìîäåëü óïðàâëåíèÿ çàïàñàìè . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.3.5 Îïðåäåëåíèå îïòèìàëüíûõ êàïèòàëîâëîæåíèåé . . . . . . . . . . . . . . . .

2.3.6 Íàõîæäåíèå îïòèìàëüíîãî ðàñêðîÿ . . . . . . . . . . . . . . . . . . . . . . . . . . .

2.3.7 Áàçà äàííûõ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Ñïèñîê èñïîëüçóåìûõ èñòî÷íèêîâ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

ÏÐÈËÎÆÅÍÈÅ 1: Ïðîãðàììà íà ÿçûêå Microsoft Visual Basic . . . . . . . . . . .

ÂÂÅÄÅÍÈÅ

Çàäà÷åé äàííîé êóðñîâîé ðàáîòû ÿâëÿåòñÿ ñîñòàâëåíèå äîêóìåíòà â ñðåäå Microsoft Excel âêëþ÷àþùåãî â ñåáÿ ïðîãðàììû íàïèñàííûå íà ÿçûêå Microsoft Visual Basic ðåøàþùèå 8 ïîñòàâëåííûõ çàäà÷, à èìåííî íà÷èñëåíèå ïðåìèè â âèäå êîììèñèîííûõ è äîïîëíèòåëüíîé îïëàòû, íà÷èñëåíèå ïðåìèàëüíûõ ïî îïðåäåëåííûì óñëîâèÿì, ìîäåëü óïðàâëåíèÿ çàïàñàìè, çàäà÷à îá îïòèìàëüíûõ êàïèòàëîâëîæåíèÿõ, çàäà÷è íà ïîñòðîåíèå ìàòðèöû è âû÷èñëåíèå ñóììû, íàõîæäåíèå áóìàæíîãî ðàñêðîÿ è áàçà äàííûõ.

 ïîÿñíèòåëüíîé çàïèñêå îïèñàíû àëãîðèòìû ðåøåíèÿ çàäà÷, ïîäðîáíîå îïèñàíèå âñåõ ìàêðîñîâ è ìîäóëåé èñïîëüçóåìûõ â ïðîãðàììå, äðóãèìè ñëîâàìè ïðèâåäåíû îïèñàíèÿ âõîäíûõ è âûõîäíûõ äàííûõ äîêóìåíòà, îïèñàíèå íàèáîëåå âàæíûõ ïåðåìåííûõ èñïîëüçóåìûõ â ïðîãðàììå.

Åñëè ãîâîðèòü î ñðåäå íàïèñàíèÿ ïðîãðàìì äëÿ ðåøåíèÿ ïîñòàâëåííûõ çàäà÷ - Microsoft Visual Basic for Application – òî ýòî ñî÷åòàíèå îäíîãî èç ñàìûõ ïðîñòûõ ÿçûêîâ ïðîãðàììèðîâàíèÿ è âñåõ âû÷èñëèòåëüíûõ âîçìîæíîñòåé òàêîé ìíîãîãðàííîé ñèñòåìû êàê Excel. Ñ ïîìîùüþ VBA ìîæíî ëåãêî è áûñòðî ñîçäàâàòü ðàçíîîáðàçíûå ïðèëîæåíèÿ äàæå íå ÿâëÿÿñü ñïåöèàëèñòîì â îáëàñòè ïðîãðàììèðîâàíèÿ. VBA ñîäåðæèò îòíîñèòåëüíî ìîùíóþ ãðàôè÷åñêóþ ñðåäó, ïîçâîëÿþùóþ íàãëÿäíî êîíñòðóèðîâàòü ýêðàííûå ôîðìû è óïðàâëÿþùèå ýë-òû.  îáùåì Visual Basic for Application ïîçâîëÿåò ñ ëåãêîñòüþ ðåøàòü ìíîãèå çàäà÷è.

1. ÒÅÎÐÅÒÈ×ÅÑÊÈÅ ÑÂÅÄÅÍÈß Â ÏÐÈÌÅÍÅÍÈÈ Ê ÐÅØÅÍÈÞ

ÏÎÑÒÀÂËÅÍÍÛÕ ÇÀÄÀ×

1.1 ôóíêöèÿ ïîëüçîâàòåëÿ

Ôóíêöèÿ ïîëüçîâàòåëÿ ñîçäàåòñÿ íà ðàáî÷åì ëèñòå ñ íàçâàíèåì ìîäóëü VBA, ïîñëå ÷åãî ñ íèìè ìîæíî ðàáîòàòü ñ ïîìîùüþ ìàñòåðà ôóíêöèé. Äëÿ ñîçäàíèÿ ìîäóëÿ VBA íåîáõîäèìî ùåëêíóòü ïðàâîé êíîïêîé ìûøè íà ÿðëûê ëèñòà, ïåðåä êîòîðûì íóæíî ðàçìåñòèòü äàííûé ìîäóëü, è èç ðàñêðûâàþùåãîñÿ êîíòåêñòíîãî ìåíþ âûáðàòü êîìàíäó “Âñòàâêà” (Insert). Íà âêëàäêå “Îáùèå”(General) íåîáõîäèìî âûáðàòü òèï ëèñòà – Ìîäóëü (Module). Ïîñëå íàæàòèÿ êíîïêè OK ïðîèçîéäåò âñòàâêà ëèñòà ìîäóëÿ. Òåïåðü íà ëèñòå ìîäóëÿ ìîæíî ââåñòè òåêñò ïðîãðàììû

Åñëè íóæíî ñîçäàòü ìàêðîñ â ïðîãðàììå íóæíî çàéòè â ñðåäó Microsoft Visual Basic ïî ñðåäñòâàì íàæàòèÿ êîìáèíàöèè êëàâèø Alt+F11, çàòåì çàéòè â ìåíþ “Ñåðâèñ”, äàëåå â ïîäìåíþ “Ìàêðîñû” è ââåñòè èìÿ ìàêðîñà, åñëè âî âðåìÿ âûïîëíåíèÿ áûë âûäåëåí êàêîé-òî ìîäóëü òî ïðîãðàììûé êîä ñîçäàííîãî ìàêðîñà ïîÿâèòñÿ èìåííî â íåì

Sub New_Macros()

; in Module 1

End Sub

1.2 Ïåðååííûå è ïîñòîÿííûå

Âñå ïåðåìåííûå â VBA èìåþò òèï. Òèï óêàçûâàåò ÷òî ìîæåò õðàíèòü ïåðåìåííàÿ: öåëîå ÷èñëî, ñòðîêó, äàòó è ò.ä.

1.3 Ñòàíäàðòíûå ô-öèè ïîëüçîâàòåëÿ äëÿ ðàáîòû ñ ìàññèâàìè è ìàòðèöàìè

Èñïîëüçóþòñÿ ñëåäóþùèå ô-öèè ðàáî÷åãî ëèñòà äëÿ ðàáîòû ñ ìàññèâàìè:

Count Êîë-âî ÷èñåë â ìàññèâå

CountA Êîë-âî ýë-òîâ ìàññèâà

Sum Ñóììà ýë-òîâ ìàññèâà

SumProduct Ñóììà ïðîèçâåäåíèé ýë-òîâ ìàññèâîâ

SumSQ Ñóììà êâàäðàòîâ ýë-òîâ ìàññèâà

SumVmY2 Ñóììà êâàäðàòîâ ðàçíîñòåé ýë-òîâ 2-õ

ìàññèâîâ

SumX2mY2 Ñóììà ðàçíîñòåé êâàäðàòîâ ýë-òîâ 2-õ

ìàññèâîâ

Èñïîëüçóþòñÿ ñëåäóþùèå ô-öèè ðàáî÷åãî ëèñòà äëÿ ðàáîòû ñ ìàòðèöàìè:

Mmult – ïðîèçâåäåíèå 2-õ ìàòðèö

Minverse – îáðàòíàÿ ìàòðèöà

Transpose – òðàíñïîíèðîâàííàÿ ìàòðèöà

MdeTerm – îïðåäåëèòåëü ìàòðèöû

1.4 Îáüåêòû, ñâ-âà è ìåòîäû VBA

Îäíèì èç îñíîâíûõ ïîíÿòèé VBA ÿâëÿåòñÿ – îáüåêò.

Îáüåêò ñîäåðæèò ñïèñîê ìåòîäîâ êîòîðûå ê íåìó ïðèìåíèìû, à ìåòîäû – ýòî òî , ÷òî ìîæíî ñ îáüåêòîì äåëàòü, òàêèì îáðàçîì îáüåêò – ýòî ïðîãðàììíûé ýë-ò

êîòîðûé èìååò ñâîå îòîáðàæåíèå íà ýêðàíå, ñîäåðæèò íåêîòîðûå ïåðåìåííûå, îïðåäåëÿþùèå åãî ñâ-âà, è íåêîòîðûå ìåòîäû äëÿ óïðàâëåíèÿ îáüåêòîì. Â VBA ñóùåñòâóåò ìíîæåñòâî âñòðîåííûõ îáüåêòîâ, íàïðèìåð:

Range – äèàïàçîí ÿ÷ååê (ìîæåò âêëþ÷àòü òîëüêî 1 ÿ÷åéêó)

Cells – êîîðäèíàòû ÿ÷åéêè

Sheet – ëèñò

WorkSheets – ðàáî÷èé ëèñò

DialogSheets – äèàëîãîâîå îêíî

Áîëüøèíñòâî îáüåêòîâ ïðèíàäëíæèò ê ãðóïïå ïîäîáíûõ îáüåêòîâ. Ýòè ãðóïïû íàçûâàþòñÿ íàáîðàìè. Íàïðèìåð, âñå ðàáî÷èå ëèñòû ðàáî÷åé êíèãè îáðàçóþò íàáîð íàçûâàåìûé WorkSheets

Ñèíòàêñèñ óñòàíîâêè çíà÷åíèÿ ñâ-âà îáüåêòà èìååò ñëåäóþùèé âèä:

Îáüåêò.Ñâîéñòâî=Âûðàæåíèå

Íàïðèìåð,

WorkSheets(”List1”).Range(“W1”).Value=999

1.5 Îïåðàòîðû öèêëà

1) Äëÿ ìíîãîêðàòíîãî âûïîëíåíèÿ îäíîãî îïåðàòîðà èëè ãðóïïû îïåðàòîðîâ ñëóæèò îïåðàòîð öèêëà FOR..NEXT

Ñèíòàêñèñ:

For Ñ÷åò÷èê = Íà÷. çíà÷. TO êîíå÷íîå çíà÷. STEP

. . .

NEXT Ñ÷åò÷èê

2) Öèêë Do..While

Do While óñëîâèå

<Áëîê îïåðàòîðîâ>

Loop

3) Öèêë Do..Until

Do

<Áëîê îïåðàòîðîâ>

Loop Until óñëîâèå

2. ÎÏÈÑÀÍÈÅ ÏÐÎÃÐÀÌÌÛ

2.1 Îïèñàíèå ïåðåìåííûõ

1) Çàäàíèÿ íà âû÷èñëåíèå êîììèñèîííûõ, èïîëó÷åíèÿ

ïðåìèé (1-å è 2-å çàäàíèÿ)

MAS1(3) – ìàññèâ èç 3-õ ýë-òîâ â êîòîðûé çàíîñÿòñÿ çíà÷åíèÿ äîõîäîâ ìàãàçèíîâ çà óêàçàííûå ìåñÿöû

MAS2(3) – ìàññèâ â êîòîðûé çàíîñÿòñÿ äîõîäû òîëüêî òåõ ìàãàçèíîâ äîõîä êîòîðûõ ïðåâûøàåò 1490,00 ãðâ.

MAS_I1(3) – ìàññèâ èíäåêñîâ

B,C,D - âñïîìãàòåëüíûå ïåðåìåííûå

I – ïåðåìåííàÿ öèêëà

Max – ïåðåìåííàÿ ïðè ïîìîùè êîòîðîé íàõîäèòñÿ ìàêñèìàëüíîå çíà÷åíèå â ìàññèâå äîõîäîâ(ñíà÷àëà ýòîé ïåðåìåííîé ñïåöèàëüíî ïðèñâàèâàåòñÿ î÷åíü ìàëåíüêîå çíà÷åíèå)

Indm, Indm2, Indm3 – èíäåêñû ýë-òîâ ìàññèâà êîòîðûå ÿâëÿþòñÿ ìàêñèìóìàìè åñëè èñêëþ÷èòü ïðåäûäóùèé íàéäåííûé ìàêñèìóì, íàïðèìåð ñíà÷àëà ìàêñèìóìîì ÿâëÿåòñÿ Indm, çàòåì íàõîäèòñÿ ìàêñèìóì èç îñòàâøèõñÿ è ò.ä.

2)Ñîñòàâëåíèå âåäîìîñòè ðàñ÷åòà ïðèáûëè îò

òîâàðà.

Äëÿ îïðåäåëåíèÿ ìàêñèìóìîâ äëÿ êàæäîãî âàðèàíòà ñòîèìîñòè òîâàðà ïîëó÷åííûå ñóììû íàõîäÿùèåñÿ â ÿ÷åéêàõ (b3:f11) çàíîñÿòñÿ â ìàññèâ ÀÀ_2(5), äëÿ îòîáðàæåíèÿ â âåäîìîñòè ìàêñèìàëüíîé è ìèíèìàëüíîé öåíû íà òîâàð.

Ìàññèâû MM_1(9) .. MM_6(9) èñïîëüçóþòñÿ äëÿ îòîáðàæåíèÿ ìèíèìàëüíûõ öåí íà êàæäûé òîâàð ïî âñåì âàðèàíòàì ïðîäàæ.

3) Ìîäåëü óïðàâëåíèÿ çàïàñàìè

Ô-öèÿ ïîëüçîâàòåëÿ CALC âû÷èñëÿþùàÿ ôèíàíñîâûå èñõîäû ïðè âñåâîçìîæíûõ âàðèàíòàõ ñîáûòèé ïîêóïêè æóðíàëîâ è èõ ðåàëèçàöèè.  ô-öèè èñïîëüçóþòñÿ ñëåäóþùèå ïåðåìåííûå: Öåíà_ïîêóïêè, Öåíà_ïðîäàæè, Öåíà_Âîçâðàòà; i,j –ïåðåìåííûå öèêëîâ. NROWS,ROWS – ïåðåìåííûå òèïà Integer. Res() – ìàññèâ òîãî æå òèïà ïåðåìåííîé äëèíû.

4) Çàäà÷à îá îïòèìàëüíûõ êàïèòàëîâëîæåíèÿõ

Ê – ïåðåìåííàÿ òèïà Integer, îòâå÷àåò çà êîëè÷åñòâî êàïèòàëîâëîæåíèé â ìëí. ãðâ.

R() – ìàññèâ ïåðåìåííîé äëèíû, â ïðîãðàììå èñïîëüçóåòñÿ êàê ïðèáûëü îò âëîæåíèÿ i ìëð. â j-òûé ôèëèàë, ãäå â R[i,j] - i(1..7), à j(1..6)

I,j,p – ïåðåìåííûå öèêëîâ.

5) Çàäàíèå íà íàõîæäåíèå îïòèìàëüíîãî ðàñêðîÿ

Äëÿ ïîñòðîåíèÿ ìàòåìàòè÷åñêîé ìîäåëè â ïðîãðàììå ïðîèçâîäèòñÿ ïåðåáîð âñåâîçìîæíûõ âàðèàíòîâ ðàñêðîÿ ðóëîíà ñòàíäàðòíîé äëèíû íà ðóëîíû òðåáóåìîé äëÿ ýòîãî â ïðîãðàììå èñïîëüçóþòñÿ ñëåäóþùèå ïåðåìåííûå:

i1,i2,i3,i4 – ïåðåìåííûå öèêëîâ

a1,a2,a3,a4 – ïåðåìåííûå êîòîðûì ïðèñâàèâàþòñÿ çíà÷åíèÿ çàêàçàííûõ äëèí ðóëîíîâ.

t – ïåðåìåííàÿ êîòîðîé ô-öèÿ Floor âîçâðàùàåò íàèáîëüøåå öåëîå ÷èñëî, íå ïðåâûøàþùåå äàííîå.

M – ìèíèìóì ñðåäè çàêàçàííûõ äëèí

6) Áàçà äàííûõ

i – ïåðåìåííàÿ öèêëà èñïîëüçóåòñÿ â äîáàâëåíèè çàïèñåé â ÁÄ ïðè ïîèñêå ïóñòîãî ïîëÿ

l1 – ìåòêà íà êîòîðóþ ññûëàåòñÿ îïåðàòîð Goto

 ïðîãðàììå ïðîèñõîäèò ïåðåõîä íà ìåòêó â òîì ñëó÷àå åñëè ïðè çàïîëíåíèè äàííûõ ïîëüçîâàòåëü çàáûë ââåñòè ôàìèëèþ ïî êîòîðîé â ïðîãðàììå îïðåäåëÿåòñÿ ïóñòà çàïèñü èëè íåò.

2.2 Âõîäíûå è âûõîäíûå äàííûå

1) Çàäàíèÿ íà âû÷èñëåíèå êîììèñèîííûõ, èïîëó÷åíèÿ

ïðåìèé (1-å è 2-å çàäàíèÿ)

Âõîäíûìè äàííûìè â ýòîì òèïå çàäàíèé ÿâëÿþòñÿ èñõîäíûå òàáëèöû ñ äàííûìè î äîõîäàõ ìàãàçèíîâ çà óêàçàííûå ìåñÿöû, à òàêæå ñóììà äîõîäîâ ïî ìåñÿöàì.  ïðîãðàììå ýòè çíà÷åíèÿ ïðèñâàèâàþòñÿ ðàçëè÷íûì ïåðåìåííûì ïðè ïîìîùè îáüåêòà

WorkSheets(“<Ëèñò>”).Range(“<ß÷åéêà>”).Value

Âûõîäíûìè äàííûìè ÿâëÿþòñÿ ïðåìèàëüíûå ïîëó÷åííûå

â ðåçóëüòàòå âû÷èñëåíèé â ñîîòâåòñòâèè ñ óñëîâèåì çàäàíèÿ.

2)Ñîñòàâëåíèå âåäîìîñòè ðàñ÷åòà ïðèáûëè îò

òîâàðà.

Âõîäíûìè äàííûìè ÿâëÿþòñÿ 9 ðàçëè÷íûõ âèäîâ öåí íà 5 êîìïëåêòóþùèõ â ñîîòâåòñòâèè ñ óñëîâèåì, à òàêæå çíà÷åíèÿ öåí íà òîâàð äî êîìïëåêòàöèè.

Âûõîäíûìè äàííûìè ÿâëÿþòñÿ çíà÷åíèÿ ñóìì öåí êîìïëåêòóþùèõ, ò.å. öåíû íà òîâàð, à òàêæå îïðåäåëåíèå ìàêñèìàëüíîé è ìèíèìàëüíîé öåíû íà òîâàð îïðåäåëÿåìîãî íå òîëüêî ïî ñòîèìîñòè íà òîâàð â öåëîì, íî è ïî ñòîèìîñòè êîìïëåêòóþùèõ ïî îòäåëüíîñòè.

3) Ìîäåëü óïðàâëåíèÿ çàïàñàìè

Âõîäíûìè äàííûìè â çàäà÷å ÿâëÿþòñÿ öåíà ïðîäàæè æóðíàëîâ, öåíà ïîêóïêè è èçäàòåëüñòâà ïðîäàâöîì, è

öåíà âîçâðàòà â ñëó÷àå íåðåàëèçàöèè òîâàðà ïðîäàâöîì, òàêæå èçâåñòíî êîëè÷åñòâî ðåàëèçóåìûõ çà ðàç ïðîäàâöîì ïà÷åê, è ÷èñëî ñîáûòèé çà îò÷åòíûé ïåðèîä âðåìåíè.

Âûõîäíûìè äàííûìè â çàäà÷å ÿâëÿþòñÿ çíà÷åíèÿ ìàêñèìàëüíîé ïðèáûëè è îïòèìàëüíîãî îáüåìà ïðîäàæ, êîòîðûå â ïðîãðåììå âûâîäÿòñÿ ïðè ïîìîùè äèàëîãîâîãî îêíà.

4) Çàäà÷à îá îïòèìàëüíûõ êàïèòàëîâëîæåíèÿõ

 ýòîé çàäà÷å âõîäíûìè äàííûìè ÿâëÿþòñÿ çíà÷åíèÿ ìàò. îæèäàíèåé ïðèáûëè êàê ô-öèé êàïèòàëîâëîæåíèé, â ñîñòâ. ñ óñëîâèåì 6 ôèëèàëîâ è 7 ìëí. ãðâ.

Âûõîäíûìè äàííûìè ÿâëÿþòñÿ òàáëèöà ãäå ïðîèçâîäèòñÿ: îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. âêëàäûâàþòñÿ â 1-é è 2-é ôèëèàëû âìåñëå, îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. âêëàäûâàþòñÿ â 1-é,2-é è 3-é ôèëèàëû âìåñëå, îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. âêëàäûâàþòñÿ â 1-é,2-é,3-é,4-é ôèëèàëû âìåñëå,è îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. âêëàäûâàþòñÿ â 1-é,2-é,3-é,4-é è 5-é ôèëèàëû âìåñëå è 1-é,2-é,3-é,4-é,5-é è 6-é.

Èç ýòîé òàáëèöû íàõîäèòñÿ ìàêñèìàëüíàÿ îæèäàåìàÿ ïðèáûëü â äàííîì ñëó÷àå ðàâíàÿ 1,01 ìëí. è îïòèìàëüíûå êàïèòàëîâëîæåíèÿ, èç òàáëèöû âèäíî, ñêîëüêî ìëí. è â êàêèå ôèëèàëû íóæíî âëîæèòü:

6 ôèëèàë – 2 ìëí.

5 ôèëèàë – 1 ìëí.

6 ôèëèàë – 1 ìëí.

6 ôèëèàë – 1 ìëí.

6 ôèëèàë – 1 ìëí.

6 ôèëèàë – 1 ìëí.

5) Çàäàíèå íà íàõîæäåíèå îïòèìàëüíîãî ðàñêðîÿ

Âõîäíûìè äàííûìè ÿâëÿþòñÿ çíà÷åíèÿ çàêàçàííûõ äëèí è èõ êîë-âà, à òàêæå çíà÷åíèå ñòàíäàðòíîé äëèíû.

Âûõîäíûìè äàííûìè áóäóò çíà÷åíèÿ îòõîäîâ ïðè âñåõ âàðèàíòàõ ðàñêðîÿ ðàññìîòðåííûõ ïðè ðåøåíèè çàäà÷è, è ñóììàðíîå êîë-âî îòõîäîâ êîòîðûå ïî óñëîâèþ çàäà÷è íåîáõîäèìî ìèíèìèçèðîâàòü.

6) Áàçà äàííûõ

Ñäåñü âõîäíûå äàííûå ÿâëÿþòñÿ âûõîäíûìè, ò.å ÷òî ïîëüçîâàòåëü ââåë òî è îòîáðàçèòñÿ â òàáëèöå. Áàçà äàííûõ ñîäåðæèò ñëåæóþùèå ïîëÿ:

2 ïîëÿ ââîäà: Ôàìèëèÿ, èìÿ

3 ðàñêðûâàþùèõñÿ ñïèñêà: Ðàáîòà,Ñòàæ,Ðàáî÷èé äåíü (÷àñ)

2 Ãðóïïû ïî 2 ôëàæêà: Êðåäèòíàÿ êàðòî÷êà, çàãðàí. Ïàñïîðò

2 ãðóïïû ïî 2 ïåðåêëþ÷àòåëÿ: Ïîë,Ñåìåéíîå ïîëîæåíèå

3 ïîëÿ ââîäà ñî ñ÷åò÷èêàìè: Âîçâðàñò,Îêëàä,îòïóñê

2.3 Ïîäðîáíîå îïèñàíèå çàäà÷

2.3.1 Íà÷èñëåíèå ïðåìèè â âèäå êîììèñèîííûõ è äîïîëíèòåëüíîé îïëàòû.

Ñîçäàåì òàáëèöó íà÷èñëåíèÿ ïðåìèé, â ÿ÷åéêè B4:D10 çàíîñèì çíà÷åíèÿ äîõîäîâ ìàãàçèíîâ çà óêàçàííûå ìåñÿöû, ñáîêó â ÿ÷åéêàõ A4:A10 áóäóò ðàñïîëàãàòüñÿ íàçâàíèÿ ìåñÿöåâ, ñîãëàñíî âàðèàíòó – ñ íîÿáðÿ ïî ìàé, à â B3:D3 – ìàãàçèíû, òàêèì îáðàçîì íà ïåðåñå÷åíèè áóäåò ïîêàçàíà âåëè÷èíà äîõîäà ìàãèçèíà êîòîðûé íàõîäèòñÿ â ýòîì ñòîëáöå è ìåñÿöà êîòîðûé ðàñïîëîæåí â ýòîé ñòðîêå.

 ÿ÷åéêå B11 ñ÷èòàåì äîõîä 1-ãî ìàãàçèíà çà âñå ìåñÿöû ïî ôîðìóëå =ÑÓÌÌ(B4:B10), è ðàñòÿãèâàåì ìàðêåð ÷åðòåæà äî ÿ÷åéêè D10, òàêèì îáðàçîì ïðîèçâîäèòñÿ ïîäñ÷åò äîõîäîâ âñåõ ìàãàçèíîâ çà âñå ìåñÿöû.

Îïðåäåëÿåì êàêèå æå èç äîõîäîâ ìàãàçèíîâ ïðåâûøàþò 1490.00 ãðâ. , äëÿ êîòîðûõ âõîäÿò â ýòî ÷èñëî ïðåìèàëüíûå áóäóò ñîñòàâëÿòü â ñîîòâåòñòâèè ñ óñëîâèåì 2% îò äîõîäà çà ýòè ìåñÿöû, îñòàëüíûå ýë-òû â ìàññèâå ñïåöèàëüíî çàïîëíÿþòñÿ íóëÿìè.

Do

k = mas1(i)

If k > 1490 Then mas2(i) = mas1(i) Else mas2(i) = 0

i = i + 1

Loop Until i = 4

 ýòîì öèêëå â ìàññèâ çàíîñÿòñÿ òîëüêî òå çíà÷åíèÿ êîòîðûå ïðåâûøàþò çàäàííîå ïî óñëîâèþ çíà÷åíèå äîïóñòèìîñòè, â äàííîì ñëó÷àå ýòî 1490,00 ðóá.

Òåïåðü äîõîäû ñðåäè îñòàâøèõñÿ ìàãàçèíîâ íóæíî ðàñïðåäåëèòü ïî óáûâàíèþ, äëÿ òîãî, ÷òîáû â ñîîòâåòñòâèè ñ óñëîâèåì íà÷èñëèòü ìàãàçèíàì äîïîëíèòåëüíûå ïðåìèàëüíûå çà 1-å, 2-å, 3-å ìåñòî. Äëÿ ýòîãî âûáèðàåì ñëåäóþùèé àëãîðèòì: íàõîäèì ìàêñèìóì ñðåäè ýòèõ äîõîäîâ è íàçíà÷àåì ýòîìó ìàãàçèíó ñîîòâåòñòâóþùóþ 1-ìó ìåñòó ïðåìèþ, çàìåì âûáèðàåì ìàêñèìóì èç äîõîäîâ íå ó÷èòûâàÿ óæå ñóæåñòâóþùèé (ò.å. íå ó÷èòûâàÿ ïåðâûé ìàêñèìóì), è íàçíà÷àåò ýòîìó ìàãàçèíó ñîîòâ. 2-ìó ìåñòó ïðåìèþ è ò.ä.

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Ñêëàäûâàåì ïîëó÷åííûå 2% ñ òåìè ÷òî íà÷èñëÿþòñÿ äîïîëíèòåëüíî çà 1,2,3 è ò.ä ìåñòà, è çàíîñèì ðåçåëüòàòû â òàáëèöó â ñòðîêó “Ïðåìèàëüíûå”

Worksheets([ëèñò]).Cells([êîîðäèíàòû ÿ÷ååê]).Value = Max * 0.02 + Max * 0.04

2.3.2 Íà÷èñëåíèå ïðåìèè ïî îïðåäåëåííûì óñëîâèÿì

Ñîçäàåì òàáëèöó íà÷èñëåíèÿ ïðåìèé, çàïîëíÿåì åå âåëè÷èíàìè äîõîäîâ çà óêàçàííûå ìåñÿöû, è ñ÷èòàåì ñóììó äîõîäîâ çà âñå ìåñÿöû. Ïîäðîáíîå îïèñàíèå êàê ñîçäàâàòü òàáëèöó è çàïîëíÿòü åå çíà÷åíèÿìè ïðèâîëèòñÿ â ïðåäûäóùåì ïóíêòå.

Îïðåäåëÿåì êàêèå èç ïîëó÷åííûõ ñóìì äîõîäîâ ëåæàò â êàêîé èç 4-õ óêàçàííûõ â óñëîâèè îáëàñòåé è çàíîñèì ðåç-òû â òàáëèöó â ÿ÷åéêè B12:D12 êîòîðûå îòîáðàæàþò ïðåìèàëüíûå

Do

i = i + 1

If AA_1(i) < 700 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.01

If AA_1(i) >= 700 And AA_1(i) < 1400 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.015

If AA_1(i) >= 1400 And AA_1(i) < 2800 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.023

If AA_1(i) >= 2800 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.025

Loop Until i = 3

Ïîëó÷åííàÿ òàáëèöà âûãëÿäèò ñëåäóþùèì îáðàçîì:

2.3.3 Ñîñòàâëåíèå âåäîìîñòè ðàñ÷åòà ïðèáûëè îò òîâàðà

Çàïîëíÿåì òàáëèöó çíà÷åíèÿìè, êàê óêàçàíî â óñëîâèè ò.å 5 ðàçíîâèäíîñòåé êîìïëåêòóþùèõ ðàñïîëîæåííûõ â ÿ÷åéêàõ B2:F2, è 9 âàðèàíòîâ ñòîèìîñòåé êîìïëåêòóþùèõ â ÿ÷åéêàõ A3:A11.  ÿ÷åéêàõ B3:F12 áóäåò ðàñïîëàãàòüñÿ çíà÷åíèÿ ñòîèìîñòåé êîìïëåêòóþùèõ è ñòîèìîñòè ðàáîòû äî êîìïëåêòàöèè.

 ÿ÷åéêàõ G3 ïî ôîðìóëå =ÑÓÌÌ(B3:F3) ñ÷èòàåòñÿ îáùàÿ ñòîèìîñòü âñåõ êîìïëåêòóþùèõ, ðàñòÿãèâàåì ìàðêåð ÿ÷åéêè G3 äî ÿ÷åéêè G11, è ïîëó÷àåì ñòîèìîñòü âñåõ êîìïëåêòóþùèõ äëÿ âñåõ âàðèàíòîâ ñòîèìîñòåé.

 ïðîãðàììå îïðåäåëÿåòñÿ êàêàÿ äåòàëü â êàêîì ìåñòå ñàìàÿ äåøîâàÿ, åñëè íå ó÷èòûâàòü òðàíñïîðòíûå çàòðàòû è çàäàòüñÿ öåëüþ êóïèòü äåòàëè ïî ìèíèìàëüíûì öåíàì. Äëÿ ýòîãî â ïðîãðàììå îïðåäåëÿþòñÿ ìèíèìàëüíûå ñòîèìîñòè ïî 5-òè äåòàëÿì.

Ïîëó÷åííàÿ âåäîìîñòü áóäåò âûãëÿäåòü ñëåäóþùèì îáðàçîì:

2.3.4 Ìîäåëü óïðàâëåíèÿ çàïàñàìè

Ââîäèì èñõîäíûå çíà÷åíèÿ , ò.å. çíà÷åíèÿ ïîêóïêè ïðîäàâöîì æóðíàëîâ, ïðîäàæè ýòèõ æóðíàëîâ è âîçâðàòà â òèïîãðàôèþ â ñëó÷àå íå ðåàëèçàöèè òîâàðà. Ââîä âñåãî ýòîãî ïðîèçâîäèòñÿ â äèàëîãîâîì îêíå, êîòîðîå ñîçäàåòñÿ êàê UserForm ñî ñïåöèàëüíûìè êíîïêàìè è ïîëÿìè ââîäà ïîêóïêè æóðíàëîâ, ïðîäàæè, è âîçâðàòà ê òèïîãðàôèþ. Îêíî ââîäà âûãëÿäèò òàê:

Ñîñòàâëÿåì òàáëèöó ñîñòîÿùóþ èç îáüåìà ðåàëèçàöèè, ÷èñëà ñîáûòèé, è âåðîÿòíîñòè ýòèõ ñîáûòèé, ïåðâûå äâà íàì äàíû ïî óñëîâèþ à âåðîÿòíîñòü ýòèõ ñîáûòèé íóæíî ïîñ÷èòàòü. Ââîäèì â ÿ÷åéêó D7 ñëåäóþùóþ ôîðìóëó âû÷èñëåíèÿ âåðîÿòíîñòåé {=D6/ÑÓÌÌ($D$5:$I$5)}

è ðàñòÿãèâàåì ìàðêåð äî ÿ÷åéêè I7.

 ÿ÷åéêàõ C10:H15 ñïîìîùüþ ô-öèè ïîëüçîâàòåëÿ CALC Âû÷èñëÿåì ôèíàíñîâûå èñõîäû ïðè âñåâîçìîæíûõ âàðèàíòûõ ñîáûòèé ïîêóïêè æóðíàëîâ è èõ ðåàëèçàöèè

Function CALC(buy As Variant) As Variant

Dim Öåíà_ïðîäàæû, Öåíà_ïîêóïêè, Öåíà_âîçâðàòà, NRows, i, j As Integer, Result() As Integer

NRows = buy.Rows.Count

Öåíà_ïðîäàæû = Range("a2").Value

Öåíà_ïîêóïêè = Range("b2").Value

Öåíà_âîçâðàòà = Range("c2").Value

ReDim Result(NRows, NRows)

For i = 1 To NRows

For j = 1 To NRows

If i <= j Then Result(i, j) = buy(i) * (Öåíà_ïðîäàæû - Öåíà_ïîêóïêè)

If i > j Then Result(i, j) = buy(j) * (Öåíà_ïðîäàæû - Öåíà_ïîêóïêè) - (buy(i) - buy(j)) * (Öåíà_ïîêóïêè - Öåíà_âîçâðàòà)

Next j

Next i

CALC = Result

End Function

 ÿ÷åéêàõ J11:J16 ñ ïîìîùüþ ôîðìóëû {=ÌÓÌÍÎÆ(C10:H15;ÒÐÀÍÑÏ(D7:I7))} íàõîäèì îæèäàåìóþ ïðèáûëü, ñîîòâåòñââóþùóþ ðàçëè÷íûì âàðèàíòàì ïîêóïêè æóðíàëîâ.

 ÿ÷åéêå F16 ñïîìîùüþ ôîðìóëû =ÍÀÈÁÎËÜØÈÉ(J11:J16;1)

âû÷èñëÿåì ìàêñèìàëüíóþ ïðèáûëü . Åå òàêæå ìîæíî íàéòè âîñïîëüçîâàâøèñü ô-öèåé ÌÀÊÑ, íàõîäÿùåé ìàêñèìàëüíûé ýë-ò èç ñïèñêà

=Ìàêñ(J11:J16)

 ÿ÷åéêå F17 ïî ôîðìóëå =(ÏÎÈÑÊÏÎÇ(ÍÀÈÁÎËÜØÈÉ(J11:J16;1);J11:J16;0)-1)*5

ñîîòâåòñòâóþùèé îïòèìàëüíûé îáüåì ïîêóïîê ãàçåò. Çàòåì ôóíêöèÿ CALC âûâîäèò ýòè îïòèìàëüíûå çíà÷åíèÿ â îêíå ñîîáùåíèé.

Ô-öèÿ íàèáîëüøèé âîçâðàùàåò Ê-å íàèáîëüøåå çíà÷åíèå èç ìíîæåñòâà äàííûõ . Ýòà ô-öèÿ èñïîëüçóåòñÿ äëÿ òîãî ÷òîáû âûáðàòü çíà÷åíèå ïî åãî îòíîñèòåëüíîìó ìåñòîïîëîæåíèþ. Íàïðèìåð, ôóíóöèþ ÍÀÈÁÎËÜØÈÉ ìîæíî èñïîëüçîâàòü äëÿ òîãî ÷òîáû îïðåäåëèòü íàèëó÷øèé, âòîðîé, òðåòèé ðåçóëüòàò â áàëàõ, ïîêàçàííûé ïðè òåñòèðîâàíèè. Ñèñòàêñèñ ïðîãðàììû òàêîé:

ÍÀÈÁÎËÜØÈÉ(ìàññèâ;Ê) ãäå Ìàññèâ – ýòî ìàññèâ èëè äèàïàçîí ÿ÷ååê ãäå îïðåäåëÿåòñÿ íàèáîëüøåå çíà÷åíèå, ê – ïîçèöèÿ (íà÷èíàÿ ñ íàèáîëüøåé) â ìàññèâå èëè äèàïàçîíå.

Âñå ðåçóëüòàòû çàíåñåííûå â òàáëèöó áóäóò âûãëÿäåòü ñëåäóþùèì îáðàçîì:

2.3.5 Îïðåäåëåíèå îïòèìàëüíûõ êàïèòàëîâëîæåíèé

Ñîçäà¸ì èñõîäíóþ òàáëèöó è çàïîëíÿåì åå ìàò. îæèäàíèÿìè ïðèáûëåé â ñîñòâåòñòâèè ñ óñëîâèåì.

Äëÿ äàëüíåéøåãî ðåøåíèÿ çàäà÷è, ââîäèì ñëåäóþùèå îáîçíà÷åíèÿ:

Ïóñòü R(i,j) – ïðèáûëü ïîëó÷àåìàÿ îò âëîæåíèÿ i ìëí. ãðâ.  j-òûé ôèëèàë, ãäå â ñîîòâ. Ñ âàðèàíòîì i îò (0,7), à j îò (0,6)

F(A,1,2) – îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. ãðâ. âêëàäûâàþòñÿ â 1,2 ôèëèàëû âìåñòå

F(A,1,2,3) – îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. ãðâ. âêëàäûâàþòñÿ â 1,2,3 ôèëèàëû âìåñòå

F(A,1,2,3,4) – îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. ãðâ. âêëàäûâàþòñÿ â 1,2,3,4 ôèëèàëû âìåñòå.

F(A,1,2,3,4,5) – îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. ãðâ. âêëàäûâàþòñÿ â 1,2,3,4,5 ôèëèàëû âìåñòå.

F(A,1,2,3,4,5,6) – îïòèìàëüíîå ðàñïðåäåëåíèå ñðåäñòâ, êîãäà À ìëí. ãðâ. âêëàäûâàþòñÿ â 1,2,3,4,5 ôèëèàëû âìåñòå.

Çíà÷åíèÿ I ïðè êîòîðûõ äîñòèãàåòñÿ ìàêñèìóì îïðåäåëÿþò îïòèìàëüíûå êàïèòàëîâëîæåíèÿ â ôèëèàëû.

Ìàêñèìàëüíûå çíà÷åíèÿ îæèäàåìûõ ïðèáûëåé âû÷èñëÿåòñÿ â ïðîãðàììå è çàíîñèòñÿ â ÿ÷åéêè H4:L11 è áóäåò âûãëÿäåòü ñëåäóþùèì îáðàçîì:

 ïðîãðàììå ïåðåìåííîé Ê – ïðèñâàèâàåì çíà÷åíèå ðàâíîå îáüåìó êàïèòàëîâëîæåíèé.  ìàññèâ R ñ ðàáî÷åãî ëèñòà êàïèòàëîâëîæåíèÿ ââîäèì îæèäàåìóþ ïðèáûëü , ðàñïðåäåëåííóþ ïî ôèëèàëàì.

 äèàïàçîí ÿ÷ååê (B14:K22) âûâîäèòñÿ îïòèìàëüíîå ðàñïðåäåëåíèå êàïèòàëîâëîæåíèé ïî ôèëèàëàì. Ïîñëå âû÷èñëåíèé ìîæíî óâèäåòü ÷òî ìàêñèìàëüíûÿ îæèäàåìàÿ ïðèáûëü ñîñòàâëÿåò 1,01 ìëí. ãðâ. , èç òàáëèöû âèäíû ñëåäóþùèå ðåç-òû:

6 ôèëèàë – 2 ìëí.

5 ôèëèàë – 1 ìëí.

4 ôèëèàë – 1 ìëí.

3 ôèëèàë – 1 ìëí.

2 ôèëèàë – 1 ìëí.

1 ôèëèàë – 1 ìëí.

Ñàìà òàáëèöà âûãëÿäèò ñëåäóþùèì îáðàçîì:

2.3.6 Çàäàíèå íà íàõîæäåíèå îïòèìàëüíîãî ðàñêðîÿ

Ñîñòàâëÿåì òàáëèöó â êîòîðîé áóäóò ïðèâåäåíû îñòàòêè îò ðàñêðîÿ íà çàêàç ïðè ðàçëè÷íûõ âàðèàíòàõ ðàñêðîÿ.

Íàïðèìåð ïî óñëîâèþ â ñîîòâåòñòâèè ñ âàðèàíòîì ñòàíäàðòíàÿ äëèíà ðàñêðîÿ ðàâíà 28 ìåòðîâ,

ò.å. ïåðâûé âàðèàíò ðàñêðîÿ áóäåò ñîñîòàâëÿòü 0 ðóëîí äëÿíîé 4 ì, 0 ðóëîíîâ äëèíîé 6ì è 4 ðóëîíà äëèíîé 9 ì, ðóëîíîâ äëèíîé 11 ì. íå áóäåò, ÷òî â ñóììå äàñò 27, ñëåäîâàòåëüíî îòõîäû áóäóò ñîñòàâëÿòü 1 ìåòð. Âòîðîé âàðèàíò êîãäà 1 ðóëîí ïî 6 ì è äâà ïî 11 ì, â ýòîì ñëó÷àå îñòàòêîâ íå áóäåò è ò.ä. Âñåãî ïîëó÷àåòñÿ 19 âàðèàíòîâ ðàñêðîÿ.

 ïðîãðàììå ýòî áóäåò âûãëÿäåòü òàêèì îáðàçîì:

l = 28

a1 = 4: a2 = 6

a3 = 9: a4 = 11

r = 4

m = Application.Min(a1, a2, a3, a4)

t = Application.Floor(l / m, 1)

For i1 = 0 To t

For i2 = 0 To t

For i3 = 0 To t

For i4 = 0 To t

s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4

If s >= 0 And s < m Then

Cells(r, 1).Value = r - 3

Cells(r, 2).Value = i1

Cells(r, 3).Value = i2

Cells(r, 4).Value = i3

Cells(r, 5).Value = i4

Cells(r, 6).Value = s

r = r + 1

End If

Next i4

Next i3

Next i2

Next i1

Íà ëèñòå ýòî áóäåò âûãëÿäåòü òàê:

Ïóñòü Xj – êîë-âî ñòàíäàðòíûõ ðóëîíîâ, ðàçðåçàííûõ ïî âàðèàíòó j, ãäå j[1..19]. Îãðàíè÷åíèÿ íàëàãàåìûå íà ïåðåìåííûå Xj ñâÿçàíû ñ òðåáîâàíèåì îáåñïå÷èòü èçãîòîâëåíèå çàêàçàííîãî êîë-âà íåñòàíäàðòíûõ ðóëîíîâ. Ô-öèÿ öåëè ó÷èòûâàåò ñóììàðíûå îòõîäû, ïîëó÷àåìûå ïðè âûïîëíåíèè çàêàçà. Òàêèì îáðàçîì èìååì ñëåäóþùóþ ìàò. ìîäåëü:

Ìèíèìèçèðîâàòü:

Z=x1+2x3+x4+2x5+x7+3x8+2x11+2x12+2x13+x14+x15+3x16+

+2x18 + 4(x5+x6+x7+x8+x9+2x10+2x11+2x12+2x13+3x14+4x15+4x16+4x17+5÷18+7x19-220)+ 6(...-210)+9(...-350)+

+11(...-380)

Îòâåäåì äèàïàçîí ÿ÷ååê (i4:i22) ïîä ïåðåìåííûå . Ââåäåì â äèàïàçîí ÿ÷ååê (j3:m3) ëåâûå ÷àñòè îãðàíè÷åíèé, îïðåäåëåííûå ñëåæóþùèìè ôîðìóëàìè:

=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;B4:B22)

=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;c4:c22)

=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;d4:d22)

=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;e4:e22)

 ÿ÷åéêó N4 ââåäåì ô-öèþ öåëè:

=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;F4:F22)+B3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;B4:B22)-J3)+C3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;C4:C22)-K3)+D3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;D4:D22)-L3)+E3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$22;E4:E22)-M3)

ãäå â ÿ÷åéêè B3:E3 ââåäåíû äëèíû, à â ÿ÷åéêè J3:M3 – êîë-âà çàêàçàííûõ ðóëîíîâ

Âûáåðåì êîìàíäó ñåðâèñ – Ïîèñê ðåøåíèÿ è çàïîëíèì îòêðûâøååñÿ äèàëîãîâîå îêíî Ïîèñê ðåøåíèÿ (Solver):

- Óñòàíîâèì öåëåâóþ ÿ÷åéêó – N4

- Èçìåíÿÿ ÿ÷åéêè I4:I22

- Îãðàíè÷åíèÿ $I$4:$I$22=öåëîå

$I$4:$I$22>=0

$j$4:$m$4>=$j$3:$m$3

- Ô-öèÿ = ìèíèìèçàöèÿ

2.3.7 Áàçà äàííûõ

Ñîçäàäèì ïîëÿ áàçû äàííûõ, è çàíåñåì èõ â òàáëèöó. Áàçà äàííûõ áóäåò çàïîëíÿòüñÿ ïðîãðàììîé, ïðîãðàììå íå òðåáóþòñÿ íàçâàíèÿ ïîëåé, íî äëÿ îáëåã÷åíèÿ îðèåíòàöèè â ïåðâîé ñòðîêå ââåäåì äàííûå ñîîòâåòñòâóþùèå ïîëÿì ÁÄ

Ñîçäàäèì êíîïêó “Äîáàâëåíèå” äëÿ äîáàâëåíèÿ çàïèñåé â ÁÄ, äåëàåòñÿ ýòî òàê: Âûçûâàåì ïàíåëü èíñòðóìåíòîâ íà êîòîðîé ðàñïîëîæåíû ïðèìèòèâû, ò.å. îêíà ââîäà, êíîïêè è ò.ä. Ñîçäàåì íà ôîðìå êíîïêó, è ñïîìîùüþ ñâ-âà Caption ïðèñâàèâàåì åé íàçâàíèå “Äîáàâëåíèå”

Ñîçäàäèì ìàêðîñ êîòîðûé áóäåò îòâå÷àòü çà îáðàáîòêó ñîáûòèé ïî íàæàòèþ ýòîé êíîïêè. Ïåðåéäåì â ñðåäó Visual Basic for Application è â ìåíþ «Âñòàâêà» âûáåðåì UserForm, íà ýòó ôîðìó è ïîìåñòèì âñå îáüåêòû îãîâîðåííûå â óñëîâèè(m ðàñêðûâàþùèõñÿ ñïèñêîâ, n ïîëåé ââîäà, ...).

 ìàêðîñå îòâå÷àþùåì çà ñîáûòèå êíîïêè «Äîáàâëåíèå» ââåäåì ïðîöåäóðó êîòîðàÿ áóäåò àêòèâèçèðîâàòü ôîðìó UserForm1, è çàíîñèòü âñå äàííûå èç îêíà ââîäà â ÿ÷åéêè ëèñòà A4:L4, A5:L5 è ò.ä.

Ïî íàæàòèþ êíîïêè “OK” âûïîëíèòñÿ ñëåäóþùèé êîä ïðîãðàììû:

Îêíî ââîäà âûãëÿäèò ñëåäóþùèì îáðàçîì:

ÑÏÈÑÎÊ ÈÑÏÎËÜÇÎÂÀÍÍÛÕ ÈÑÒÎ×ÍÈÊÎÂ

1. À.Ãàðíàåâ. Èñïîëüçîâàíèå MS Excel è VBA â ýêîíîìèêå è ôèíàíñàõ

2. Ñ. Áðàóí, Visual Basic 5.0 ñ ñàìîãî íà÷àëà, Ìîñêâà 1999, èçäàòåëüñòâî “Ïèòåð”

3. Microsoft Visual Basic – on-Line HELP

ÏÐÈËÎÆÅÍÈÅ 1

ÏÐÎÃÐÀÌÌÀ ÍÀ ßÇÛÊÅ MICROSOFT VISUAL BASIC

Ìîäóëü 1:

Sub Return_To_MainMenu()

Worksheets("Ñîäåðæàíèå").Activate

End Sub

Ìîäóëü 2:

Sub Task1()

Worksheets("Çàäàíèå1").Activate

End Sub

Sub Task2()

Worksheets("Çàäàíèå2").Activate

End Sub

Sub Task3()

Worksheets("Çàäàíèå3").Activate

End Sub

Sub Task4()

Worksheets("Çàäàíèå4").Activate

End Sub

Sub Task1_Evrica()

Dim mas1(3) As Integer

Dim mas2(3) As Integer

Dim Mas_I1(3) As Integer

B = Worksheets("Çàäàíèå1").Range("B11").Value

c = Worksheets("Çàäàíèå1").Range("C11").Value

в = Worksheets("Çàäàíèå1").Range("D11").Value

mas1(1) = B

mas1(2) = c

mas1(3) = D

i = 1

l = 0

Do

k = mas1(i)

''''' Çàíåñåíèå â ìàññèâ Mas2 ýë-òîâ >1490

If k > 1490 Then mas2(i) = mas1(i) Else mas2(i) = 0

i = i + 1

Loop Until i = 4

Max = -1

i = 0

Do

i = i + 1

If mas2(i) > Max Then

Max = mas2(i)

indm = i

End If

Loop Until i = 3

Worksheets("Çàäàíèå1").Cells(12, indm + 1).Value = Max * 0.02 + Max * 0.04

'Worksheets("Çàäàíèå1").Range("f15").Value = r

'GoTo l

''''' Íàõîäèì MAx ýë-ò èç îñòàâøèõñÿ,

''''' è çàïîìèíàåì åãî èíäåñê

Max = -1

i = 0

Do

i = i + 1

If i <> indm And mas2(i) > Max Then

Max = mas2(i)

indm2 = i

End If

Loop Until i = 3

Worksheets("Çàäàíèå1").Cells(12, indm2 + 1).Value = Max * 0.02 + Max * 0.02

''''' Íàõîäèì MAx ýë-ò èç îñòàâøèõñÿ,

''''' è çàïîìèíàåì åãî èíäåñê

Max = -1

i = 0

Do

i = i + 1

If mas2(i) > Max And i <> indm2 And i <> indm Then

Max = mas2(i)

indm3 = i

End If

Loop Until i = 3

Worksheets("Çàäàíèå1").Cells(12, indm3 + 1).Value = Max * 0.02 + Max * 0.01

End Sub

Sub Task2_Evrica()

Dim AA_1(3) As Integer

B = Worksheets("Çàäàíèå2").Range("B11").Value

c = Worksheets("Çàäàíèå2").Range("C11").Value

D = Worksheets("Çàäàíèå2").Range("D11").Value

AA_1(1) = B

AA_1(2) = c

AA_1(3) = D

i = 0

Do

i = i + 1

If AA_1(i) < 700 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.01

If AA_1(i) >= 700 And AA_1(i) < 1400 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.015

If AA_1(i) >= 1400 And AA_1(i) < 2800 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.023

If AA_1(i) >= 2800 Then Worksheets("Çàäàíèå2").Cells(12, i + 1).Value = Worksheets("Çàäàíèå2").Cells(11, i + 1).Value * 0.025

Loop Until i = 3

End Sub

Sub Task3_Evrica()

Dim AA_2(10) As Integer

Dim MM_1(10) As Integer

Dim MM_2(10) As Integer

Dim MM_3(10) As Integer

Dim MM_4(10) As Integer

Dim MM_5(10) As Integer

Worksheets("Çàäàíèå3").Range("I3:I12").Clear

Worksheets("Çàäàíèå3").Range("b3:h12").Font.Bold = False

Worksheets("Çàäàíèå3").Range("b3:h12").Font.Size = 10

Worksheets("Çàäàíèå3").Range("b3:h12").Font.Italic = False

i = 0

Do

i = i + 1

AA_2(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 7).Value

Loop Until i = 9

Max = -1

i = 0

Do

i = i + 1

If AA_2(i) > Max Then

Max = AA_2(i)

mm = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(mm + 2, 8).Value = "Ìàêñ. Öåíà íà òîâàð"

Min = 100000

i = 0

Do

i = i + 1

If AA_2(i) < Min Then

Min = AA_2(i)

mm2 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(mm2 + 2, 8).Value = "Ìèíèì. Öåíà íà òîâàð"

'''''''''''''''''''''''''''''

i = 0

Do

i = i + 1

MM_1(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 2).Value

MM_2(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 3).Value

MM_3(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 4).Value

MM_4(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 5).Value

MM_5(i) = Worksheets("Çàäàíèå3").Cells(i + 2, 6).Value

Loop Until i = 9

'''' 1

Min = 100000

i = 0

Do

i = i + 1

If MM_1(i) < Min Then

Min = MM_1(i)

x1 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(x1 + 2, 2).Font.Bold = True

Worksheets("Çàäàíèå3").Cells(x1 + 2, 2).Font.Size = 11

Worksheets("Çàäàíèå3").Cells(x1 + 2, 2).Font.Italic = True

'''' 2

Min = 100000

i = 0

Do

i = i + 1

If MM_2(i) < Min Then

Min = MM_2(i)

x2 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(x2 + 2, 3).Font.Bold = True

Worksheets("Çàäàíèå3").Cells(x2 + 2, 3).Font.Size = 11

Worksheets("Çàäàíèå3").Cells(x2 + 2, 3).Font.Italic = True

'''' 3

Min = 100000

i = 0

Do

i = i + 1

If MM_3(i) < Min Then

Min = MM_3(i)

x3 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(x3 + 2, 4).Font.Bold = True

Worksheets("Çàäàíèå3").Cells(x3 + 2, 4).Font.Size = 11

Worksheets("Çàäàíèå3").Cells(x3 + 2, 4).Font.Italic = True

'''' 4

Min = 100000

i = 0

Do

i = i + 1

If MM_4(i) < Min Then

Min = MM_4(i)

x4 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(x4 + 2, 5).Font.Bold = True

Worksheets("Çàäàíèå3").Cells(x4 + 2, 5).Font.Size = 11

Worksheets("Çàäàíèå3").Cells(x4 + 2, 5).Font.Italic = True

'''' 5

Min = 100000

i = 0

Do

i = i + 1

If MM_5(i) < Min Then

Min = MM_5(i)

x5 = i

End If

Loop Until i = 9

Worksheets("Çàäàíèå3").Cells(x5 + 2, 6).Font.Bold = True

Worksheets("Çàäàíèå3").Cells(x5 + 2, 6).Font.Size = 11

Worksheets("Çàäàíèå3").Cells(x5 + 2, 6).Font.Italic = True

'''' 6

End Sub

Sub Task5()

Worksheets("Çàäàíèå5").Activate

End Sub

Sub Task6()

Worksheets("Çàäàíèå5").Activate

End Sub

Sub Task5_Evrica()

Dim G(4, 4)

Dim c(4)

c(1) = Worksheets("Çàäàíèå5").Range("a1")

c(2) = Worksheets("Çàäàíèå5").Range("b1")

c(3) = Worksheets("Çàäàíèå5").Range("c1")

c(4) = Worksheets("Çàäàíèå5").Range("d1")

Worksheets("Çàäàíèå5").Range("a3:d6").Value = ""

For i = 1 To 4

For j = 1 To 4

If i <= j + 1 Then G(i, j) = c(i) * (Cos(c(j))) ^ 2

If i > j + 1 Then G(i, j) = Abs(c(i - j) ^ 3 - c(i))

Next

Next

For i = 1 To 4

For j = 1 To 4

Worksheets("Çàäàíèå5").Cells(i + 2, j).Value = G(i, j)

Next

Next

End Sub

Sub Task6_Evrica()

Dim X(4)

Dim Y(4)

X(1) = Worksheets("Çàäàíèå5").Range("a12")

X(2) = Worksheets("Çàäàíèå5").Range("a13")

X(3) = Worksheets("Çàäàíèå5").Range("a14")

X(4) = Worksheets("Çàäàíèå5").Range("a15")

Y(1) = Worksheets("Çàäàíèå5").Range("b12")

Y(2) = Worksheets("Çàäàíèå5").Range("b13")

Y(3) = Worksheets("Çàäàíèå5").Range("b14")

Y(4) = Worksheets("Çàäàíèå5").Range("b15")

s1 = 0

s2 = 0

s3 = 0

m = 4

For i = 1 To m

s1 = s1 + X(i)

s2 = s2 + X(i) * Y(i)

s3 = s3 + X(i) * X(i)

Next

s = (2 * s1 + s2) * (2 - s1) + 3 + s3

Worksheets("Çàäàíèå5").Range("D15").Value = s

End Sub

Sub Task7()

Worksheets("Ðàñêðîé").Activate

End Sub

Sub Task7_DB()

UserForm1.ComboBox1.Clear

UserForm1.ComboBox2.Clear

UserForm1.ComboBox3.Clear

UserForm1.ComboBox1.AddItem ("Äèðåêòîð")

UserForm1.ComboBox1.AddItem ("Çàì. äèðåêòîðà")

UserForm1.ComboBox1.AddItem ("Ìåíåäæåð")

UserForm1.ComboBox1.AddItem ("Ñåêòåòàðü")

UserForm1.ComboBox1.AddItem ("Àäìèíèñòðàòîð")

UserForm1.ComboBox1.AddItem ("Îõðàíà")

UserForm1.ComboBox1.AddItem ("Âîäèòåëü")

UserForm1.ComboBox1.AddItem ("Ñòîðîæ")

UserForm1.ComboBox1.AddItem ("Óáîðùèê")

UserForm1.ComboBox2.AddItem ("10 ëåò.")

UserForm1.ComboBox2.AddItem ("9 ëåò.")

UserForm1.ComboBox2.AddItem ("8 ëåò.")

UserForm1.ComboBox2.AddItem ("3 ãîäà.")

UserForm1.ComboBox2.AddItem ("2 ãîäà.")

UserForm1.ComboBox2.AddItem ("1 ãîä.")

UserForm1.ComboBox2.AddItem ("ìåíüøå ãîäà.")

UserForm1.ComboBox3.AddItem ("5 ÷àñîâ")

UserForm1.ComboBox3.AddItem ("6 ÷àñîâ")

UserForm1.ComboBox3.AddItem ("7 ÷àñîâ")

UserForm1.ComboBox3.AddItem ("8 ÷àñîâ")

UserForm1.Show

End Sub

Sub Task7_List()

Worksheets("ÁÄ").Activate

End Sub

Sub Model_of_storekeeping()

UserForm2.Show

End Sub

Ìîäóëü 3:

Option Explicit

'ÌÎÄÅËÜ ÓÏÐÀÂËÅÍÈß ÇÀÏÀÑÀÌÈ

Function CALC(buy As Variant) As Variant

Dim Öåíà_ïðîäàæû, Öåíà_ïîêóïêè, Öåíà_âîçâðàòà, NRows, i, j As Integer, Result() As Integer

NRows = buy.Rows.Count

Öåíà_ïðîäàæû = Range("a2").Value

Öåíà_ïîêóïêè = Range("b2").Value

Öåíà_âîçâðàòà = Range("c2").Value

ReDim Result(NRows, NRows)

For i = 1 To NRows

For j = 1 To NRows

If i <= j Then Result(i, j) = buy(i) * (Öåíà_ïðîäàæû - Öåíà_ïîêóïêè)

If i > j Then Result(i, j) = buy(j) * (Öåíà_ïðîäàæû - Öåíà_ïîêóïêè) - (buy(i) - buy(j)) * (Öåíà_ïîêóïêè - Öåíà_âîçâðàòà)

Next j

Next i

CALC = Result

End Function

Sub Begin()

Worksheets("Ñîäåðæàíèå").Activate

End Sub

Sub Optimum_capital_investmentsEVR()

Dim i, j, k, n, p, l, t As Integer

Dim m, r(), A() As Double

k = 7

ReDim r(k + 1, 6), A(k + 1)

For i = 1 To k + 1

For j = 2 To 7

r(i, j - 1) = Cells(i + 3, j).Value

Next j

Next i

t = 2

For p = 2 To 6

If p = 2 Then

For j = 1 To k + 1

A(j) = Cells(j + 3, 2).Value

Next j

End If

If p > 2 Then

For j = 1 To k + 1

A(j) = Cells(j + 3, p + 5).Value

Next j

End If

For n = 1 To k + 1

m = -1

For j = 1 To n

If m < A(j) + r(n + 1 - j, p) Then

m = A(j) + r(n + 1 - j, p)

End If

Next j

Cells(n + 3, 6 + p).Value = m

l = t

For j = 1 To n

If m = A(j) + r(n + 1 - j, p) Then

Cells(n + 6 + k, l).Value = j - 1

Cells(n + 6 + k, l + 1).Value = n - j

l = l + 2

End If

Next j

Next n

t = l

Next p

End Sub

Ìîäóëü 4:

Sub Ðàñêðîé()

Dim r, i1, i2, i3, i4, s, t As Integer

Dim l, a1, a2, a3, a4, a5, m As Integer

'Dim F, TT, SS, ZZ As String

l = 28

a1 = 4: a2 = 6

a3 = 9: a4 = 11

r = 4

m = Application.Min(a1, a2, a3, a4)

t = Application.Floor(l / m, 1)

For i1 = 0 To t

For i2 = 0 To t

For i3 = 0 To t

For i4 = 0 To t

s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4

If s >= 0 And s < m Then

Cells(r, 1).Value = r - 3

Cells(r, 2).Value = i1

Cells(r, 3).Value = i2

Cells(r, 4).Value = i3

Cells(r, 5).Value = i4

Cells(r, 6).Value = s

r = r + 1

End If

Next i4

Next i3

Next i2

Next i1

Range("J4").FormulaLocal = "=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")"

Range("K4").FormulaLocal = "=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")"

Range("L4").FormulaLocal = "=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")"

Range("M4").FormulaLocal = "=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")"

Range("N4").FormulaLocal = "=ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";F4:F" & r - 1 & ")+B3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";B4:B" & r - 1 & ")-J3)+C3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";C4:C" & r - 1 & ")-K3)+D3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";D4:D" & r - 1 & ")-L3)+E3*(ÑÓÌÌÏÐÎÈÇÂ($I$4:$I$" & r - 1 & ";E4:E" & r - 1 & ")-M3)"

End Sub

Sub Optimum_capital_investments()

Worksheets("Îïò.êàïèòàë").Activate

End Sub

UserFORM1

Îáðàáîò÷èê ñîáûòèÿ êíîïêè <OK>

Private Sub CommandButton1_Click()

If UserForm1.TextBox1.Text = "" Then GoTo ll

i = 0

Do

i = i + 1

Loop Until Worksheets("ÁÄ").Cells(i, 1) = ""

Worksheets("ÁÄ").Cells(i, 1) = UserForm1.TextBox1.Text

Worksheets("ÁÄ").Cells(i, 2) = UserForm1.TextBox3.Text

If UserForm1.CheckBox2 = True Then

Worksheets("ÁÄ").Cells(i, 6) = "Åñòü"

Else

Worksheets("ÁÄ").Cells(i, 6) = "Íåò"

End If

If UserForm1.CheckBox1 = True Then

Worksheets("ÁÄ").Cells(i, 7) = "Åñòü"

Else

Worksheets("ÁÄ").Cells(i, 7) = "Íåò"

End If

Worksheets("ÁÄ").Cells(i, 8) = UserForm1.TextBox5.Text + " ãðâ."

Worksheets("ÁÄ").Cells(i, 9) = UserForm1.TextBox2.Text

Worksheets("ÁÄ").Cells(i, 10) = UserForm1.TextBox6.Text + " ìåñ."

If UserForm1.OptionButton3 = True Then Worksheets("ÁÄ").Cells(i, 11).Value = "Åñòü ñåìüÿ"

If UserForm1.OptionButton4 = True Then Worksheets("ÁÄ").Cells(i, 11).Value = "Íåò ñåìüè"

If UserForm1.OptionButton5 = True Then Worksheets("ÁÄ").Cells(i, 12).Value = " M "

If UserForm1.OptionButton6 = True Then Worksheets("ÁÄ").Cells(i, 12).Value = " Æ "

Worksheets("ÁÄ").Cells(i, 3).Value = ComboBox1.Value

Worksheets("ÁÄ").Cells(i, 4).Value = ComboBox2.Value

Worksheets("ÁÄ").Cells(i, 5).Value = ComboBox3.Value

ll:

UserForm1.Hide

Worksheets("ÁÄ").Activate

End Sub

Îáðàáîò÷èê ñîáûòèÿ êíîïêè <Cancel>

Private Sub CommandButton2_Click()

UserForm1.Hide

Worksheets("ÁÄ").Activate

End Sub

UserForm2

Îáðàáîò÷èê ñîáûòèÿ êíîïêè <OK>

Private Sub CommandButton1_Click()

Worksheets("Çàäàíèå4").Range("c10:h15").Value = ""

Worksheets("Çàäàíèå4").Range("j11:j16").Value = ""

Worksheets("Çàäàíèå4").Range("b2").Value = UserForm2.TextBox1

Worksheets("Çàäàíèå4").Range("a2").Value = UserForm2.TextBox2

Worksheets("Çàäàíèå4").Range("c2").Value = UserForm2.TextBox3

UserForm2.Hide

Range("C10:H15").FormulaArray = "=Ìîäóëü3.CALC(I11:I16)"

Range("J11:J16").FormulaArray = "=MMULT((C10:H15),TRANSPOSE(d7:i7))"

Range("f16").Select

ActiveCell.FormulaR1C1 = "=large(r[-5]c[4]:rc[4],1)"

Range("f17").Select

ActiveCell.FormulaR1C1 = "=(match(large(r[-6]c[4]:r[-1]c[4],1),r[-6]c[4]:r[-1]c[4],0)-1)*5"

r = Range("f16").Value

v = Range("f17").Value

UserForm3.Label3.Caption = Worksheets("Çàäàíèå4").Range("f16")

UserForm3.Label4.Caption = Worksheets("Çàäàíèå4").Range("f17")

UserForm3.Show

End Sub

Îáðàáîò÷èê ñîáûòèÿ êíîïêè <Cancel>

Private Sub CommandButton2_Click()

UserForm2.Hide

End Sub

UserForm3

Private Sub CommandButton1_Click()

UserForm3.Hide

End Sub