Ðåøåíèå ýêîíîìè÷åñêèõ çàäà÷ ñ ïîìîùüþ 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 èìåþò òèï. Òèï óêàçûâàåò ÷òî ìîæåò õðàíèòü ïåðåìåííàÿ: öåëîå ÷èñëî, ñòðîêó, äàòó è ò.ä.
| Òèï äàííûõ
|
Çàíèìàåìûé ðàçìåð ïàìÿòè
|
Äèàïàçîí
|
| Boolean
Integer
Long
Date
Array
Object
String
Currency
Variant
|
2 bytes
2 bytes
4 bytes
8 bytes
Çàâèñèò îò ðàçìåðà è òèïà ýë-òîâ
4
10+äëèíà ñòðîêè
8
Çàâèñèò îò ñîäåðæèìîãî ïåðåìåííîé
|
True/False
-32768 äî32768
-2,147,483,648 äî
2,147,483,648
Îò 1 ÿíâàðÿ äî 31 äàêàáðÿ
Ëþáîé îïðåäåëåííûé
Îáüåêò
Îò 0 äà 2Õ10^9
|
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
| Ìåñÿö
|
Ì à ã à ç è í û
|
| 1
|
2
|
3
|
| Íîÿáðü
|
100
|
100
|
120
|
| Äåêàáðü
|
300
|
150
|
650
|
| ßíâàðü
|
1000
|
130
|
250
|
| Ôåâðàëü
|
1000
|
120
|
50
|
| Ìàðò
|
0
|
100
|
760
|
| Àïðåëü
|
100
|
100
|
0
|
| Ìàé
|
310
|
600
|
500
|
| Âñåãî
|
2810
|
1300
|
2330
|
| Ïðåìèàëüíûå!
|
168,6
|
0
|
93,2
|
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
Ïîëó÷åííàÿ òàáëèöà âûãëÿäèò ñëåäóþùèì îáðàçîì:
| Ìåñÿö
|
Ì à ã à ç è í û
|
| 1
|
2
|
3
|
| Íîÿáðü
|
50
|
100
|
120
|
| Äåêàáðü
|
50
|
150
|
650
|
| ßíâàðü
|
100
|
130
|
250
|
| Ôåâðàëü
|
100
|
120
|
50
|
| Ìàðò
|
120
|
100
|
760
|
| Àïðåëü
|
100
|
100
|
1000
|
| Ìàé
|
50
|
600
|
500
|
| Âñåãî
|
570
|
1300
|
3330
|
| Ïðåìèàëüíûå!
|
5,7
|
19,5
|
83,25
|
2.3.3 Ñîñòàâëåíèå âåäîìîñòè ðàñ÷åòà ïðèáûëè îò òîâàðà
Çàïîëíÿåì òàáëèöó çíà÷åíèÿìè, êàê óêàçàíî â óñëîâèè ò.å 5 ðàçíîâèäíîñòåé êîìïëåêòóþùèõ ðàñïîëîæåííûõ â ÿ÷åéêàõ B2:F2, è 9 âàðèàíòîâ ñòîèìîñòåé êîìïëåêòóþùèõ â ÿ÷åéêàõ A3:A11.  ÿ÷åéêàõ B3:F12 áóäåò ðàñïîëàãàòüñÿ çíà÷åíèÿ ñòîèìîñòåé êîìïëåêòóþùèõ è ñòîèìîñòè ðàáîòû äî êîìïëåêòàöèè.
 ÿ÷åéêàõ G3 ïî ôîðìóëå =ÑÓÌÌ(B3:F3) ñ÷èòàåòñÿ îáùàÿ ñòîèìîñòü âñåõ êîìïëåêòóþùèõ, ðàñòÿãèâàåì ìàðêåð ÿ÷åéêè G3 äî ÿ÷åéêè G11, è ïîëó÷àåì ñòîèìîñòü âñåõ êîìïëåêòóþùèõ äëÿ âñåõ âàðèàíòîâ ñòîèìîñòåé.
 ïðîãðàììå îïðåäåëÿåòñÿ êàêàÿ äåòàëü â êàêîì ìåñòå ñàìàÿ äåøîâàÿ, åñëè íå ó÷èòûâàòü òðàíñïîðòíûå çàòðàòû è çàäàòüñÿ öåëüþ êóïèòü äåòàëè ïî ìèíèìàëüíûì öåíàì. Äëÿ ýòîãî â ïðîãðàììå îïðåäåëÿþòñÿ ìèíèìàëüíûå ñòîèìîñòè ïî 5-òè äåòàëÿì.
Ïîëó÷åííàÿ âåäîìîñòü áóäåò âûãëÿäåòü ñëåäóþùèì îáðàçîì:
| Âàðèàíòû
|
 è ä û ê î ì ï ë å ê ò ó þ ù è õ
|
MIN / MAX
|
| Ñòîèìîñòè
|
1-ÿ äåòàëü
|
2-ÿ äåòàëü
|
3-ÿ äåòàëü
|
4-ÿ äåòàëü
|
5-ÿ äåòàëü
|
Âñåãî
|
| 1-é
|
20
|
90
|
5
|
50
|
60
|
225
|
| 2-é
|
19
|
85
|
4
|
55
|
50
|
213
|
| 3-é
|
20
|
81
|
4
|
50
|
56
|
211
|
Ìèíèì. Öåíà íà òîâàð
|
| 4-é
|
25
|
87
|
8
|
57
|
58
|
235
|
| 5-é
|
29
|
87
|
5
|
55
|
60
|
236
|
| 6-é
|
18
|
88
|
4
|
40
|
61
|
211
|
| 7-é
|
30
|
99
|
9
|
66
|
60
|
264
|
| 8-é
|
30
|
99
|
9
|
66
|
64
|
268
|
Ìàêñ. Öåíà íà òîâàð
|
| 9-é
|
21
|
90
|
6
|
54
|
55
|
226
|
| Äî êîìïëåêòàöèè
|
15
|
75
|
3
|
40
|
50
|
183
|
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 âûâîäèò ýòè îïòèìàëüíûå çíà÷åíèÿ â îêíå ñîîáùåíèé.
Ô-öèÿ íàèáîëüøèé âîçâðàùàåò Ê-å íàèáîëüøåå çíà÷åíèå èç ìíîæåñòâà äàííûõ . Ýòà ô-öèÿ èñïîëüçóåòñÿ äëÿ òîãî ÷òîáû âûáðàòü çíà÷åíèå ïî åãî îòíîñèòåëüíîìó ìåñòîïîëîæåíèþ. Íàïðèìåð, ôóíóöèþ ÍÀÈÁÎËÜØÈÉ ìîæíî èñïîëüçîâàòü äëÿ òîãî ÷òîáû îïðåäåëèòü íàèëó÷øèé, âòîðîé, òðåòèé ðåçóëüòàò â áàëàõ, ïîêàçàííûé ïðè òåñòèðîâàíèè. Ñèñòàêñèñ ïðîãðàììû òàêîé:
ÍÀÈÁÎËÜØÈÉ(ìàññèâ;Ê) ãäå Ìàññèâ – ýòî ìàññèâ èëè äèàïàçîí ÿ÷ååê ãäå îïðåäåëÿåòñÿ íàèáîëüøåå çíà÷åíèå, ê – ïîçèöèÿ (íà÷èíàÿ ñ íàèáîëüøåé) â ìàññèâå èëè äèàïàçîíå.
Âñå ðåçóëüòàòû çàíåñåííûå â òàáëèöó áóäóò âûãëÿäåòü ñëåäóþùèì îáðàçîì:
|
|
|
Ï ð î ä à æ à
|
|
|
|
| Ï
|
|
0
|
4
|
8
|
12
|
14
|
18
|
| î
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
Ïîêóïêà
|
Ïðèáûëü
|
| ê
|
4
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
- ð.
|
| ó
|
8
|
0
|
-20
|
16
|
16
|
16
|
16
|
4
|
- ð.
|
| ï
|
12
|
0
|
-40
|
-4
|
32
|
32
|
32
|
8
|
12,94ð.
|
| ê
|
14
|
0
|
-60
|
-24
|
12
|
48
|
48
|
12
|
16,88ð.
|
| à
|
18
|
0
|
-70
|
-34
|
2
|
38
|
56
|
14
|
9,00ð.
|
| Ìàêñèìàëüíàÿ ïðèáûëü
|
16,88ð.
|
18
|
0,28ð.
|
| Îïòèìàëüíûé îáüåì
|
15
|
2.3.5 Îïðåäåëåíèå îïòèìàëüíûõ êàïèòàëîâëîæåíèé
Ñîçäà¸ì èñõîäíóþ òàáëèöó è çàïîëíÿåì åå ìàò. îæèäàíèÿìè ïðèáûëåé â ñîñòâåòñòâèè ñ óñëîâèåì.
|
|
Ô è ë è à ë û
|
|
| Ìëí. ãðâ
|
1
|
2
|
3
|
4
|
5
|
6
|
| 0
|
0
|
0
|
0
|
0
|
0
|
0
|
| 1
|
0,11
|
0,12
|
0,18
|
0,2
|
0,17
|
0,12
|
| 2
|
0,11
|
0,13
|
0,18
|
0,22
|
0,17
|
0,23
|
| 3
|
0,12
|
0,13
|
0,19
|
0,24
|
0,18
|
0,24
|
| 4
|
0,12
|
0,13
|
0,19
|
0,26
|
0,18
|
0,24
|
| 5
|
0,13
|
0,13
|
0,2
|
0,29
|
0,19
|
0,25
|
| 6
|
0,13
|
0,13
|
0,2
|
0,31
|
0,19
|
0,25
|
| 7
|
0,14
|
0,13
|
0,2
|
0,33
|
0,2
|
0,26
|
Äëÿ äàëüíåéøåãî ðåøåíèÿ çàäà÷è, ââîäèì ñëåäóþùèå îáîçíà÷åíèÿ:
Ïóñòü 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 è áóäåò âûãëÿäåòü ñëåäóþùèì îáðàçîì:
| Ì à ê ñ è ì ó ì û
|
|
|
| 1 è 2
|
1,2 è 3
|
1,2,3 è 4
|
1,2,3,4 è 5
|
1,2,3,4,5 è 6
|
| 0
|
0
|
0
|
0
|
0
|
| 0,12
|
0,18
|
0,2
|
0,2
|
0,2
|
| 0,23
|
0,3
|
0,38
|
0,38
|
0,38
|
| 0,24
|
0,41
|
0,5
|
0,55
|
0,55
|
| 0,24
|
0,42
|
0,61
|
0,67
|
0,67
|
| 0,25
|
0,42
|
0,63
|
0,78
|
0,79
|
| 0,25
|
0,43
|
0,65
|
0,8
|
0,9
|
| 0,26
|
0,43
|
0,67
|
0,82
|
1,01
|
 ïðîãðàììå ïåðåìåííîé Ê – ïðèñâàèâàåì çíà÷åíèå ðàâíîå îáüåìó êàïèòàëîâëîæåíèé.  ìàññèâ R ñ ðàáî÷åãî ëèñòà êàïèòàëîâëîæåíèÿ
ââîäèì îæèäàåìóþ ïðèáûëü , ðàñïðåäåëåííóþ ïî ôèëèàëàì.
 äèàïàçîí ÿ÷ååê (B14:K22) âûâîäèòñÿ îïòèìàëüíîå ðàñïðåäåëåíèå êàïèòàëîâëîæåíèé ïî ôèëèàëàì. Ïîñëå âû÷èñëåíèé ìîæíî óâèäåòü ÷òî ìàêñèìàëüíûÿ îæèäàåìàÿ ïðèáûëü ñîñòàâëÿåò 1,01 ìëí. ãðâ. , èç òàáëèöû âèäíû ñëåäóþùèå ðåç-òû:
6 ôèëèàë – 2 ìëí.
5 ôèëèàë – 1 ìëí.
4 ôèëèàë – 1 ìëí.
3 ôèëèàë – 1 ìëí.
2 ôèëèàë – 1 ìëí.
1 ôèëèàë – 1 ìëí.
Ñàìà òàáëèöà âûãëÿäèò ñëåäóþùèì îáðàçîì:
| Ô è ë è à ë û
|
| 0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
| 1
|
0
|
1
|
0
|
1
|
0
|
1
|
1
|
0
|
1
|
0
|
| 2
|
1
|
1
|
1
|
1
|
1
|
1
|
2
|
0
|
2
|
0
|
| 3
|
1
|
2
|
2
|
1
|
2
|
1
|
2
|
1
|
3
|
0
|
| 4
|
1
|
3
|
3
|
1
|
3
|
1
|
3
|
1
|
3
|
1
|
| 5
|
3
|
2
|
2
|
3
|
3
|
2
|
4
|
1
|
4
|
1
|
| 6
|
3
|
3
|
3
|
3
|
3
|
3
|
5
|
1
|
4
|
2
|
| 7
|
5
|
2
|
2
|
5
|
3
|
4
|
6
|
1
|
5
|
2
|
| Ìëí. ãðâ.
|
1
|
2
|
1,2
|
3
|
1,2,3
|
4
|
1,2,3 è 4
|
5
|
1,2,3,4 è 5
|
6
|
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
Íà ëèñòå ýòî áóäåò âûãëÿäåòü òàê:
| Ä ë è í û ð ó ë î í î â í à ç à ê à ç
|
| Âàðèàíòû
|
Îñòàòîê
|
| ðàñêðîéêè
|
4
|
6
|
9
|
11
|
îò ðàñêòîÿ
|
| 1
|
0
|
0
|
3
|
0
|
1
|
| 2
|
0
|
1
|
0
|
2
|
0
|
| 3
|
0
|
1
|
1
|
1
|
2
|
| 4
|
0
|
3
|
1
|
0
|
1
|
| 5
|
1
|
0
|
0
|
2
|
2
|
| 6
|
1
|
1
|
2
|
0
|
0
|
| 7
|
1
|
2
|
0
|
1
|
1
|
| 8
|
1
|
2
|
1
|
0
|
3
|
| 9
|
1
|
4
|
0
|
0
|
0
|
| 10
|
2
|
0
|
1
|
1
|
0
|
| 11
|
2
|
0
|
2
|
0
|
2
|
| 12
|
2
|
1
|
0
|
1
|
3
|
| 13
|
2
|
3
|
0
|
0
|
2
|
| 14
|
3
|
1
|
1
|
0
|
1
|
| 15
|
4
|
0
|
0
|
1
|
1
|
| 16
|
4
|
0
|
1
|
0
|
3
|
| 17
|
4
|
2
|
0
|
0
|
0
|
| 18
|
5
|
1
|
0
|
0
|
2
|
| 19
|
7
|
0
|
0
|
0
|
0
|
Ïóñòü 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
- Ô-öèÿ = ìèíèìèçàöèÿ
| Ê î ë - â à ç à ê à ç à í í û õ ð ó ë î í î â
|
| 220
|
210
|
350
|
380
|
Îòõîäû
|
| 220
|
210
|
350
|
380
|
49,99996
|
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
D = 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
|
