OR 2 Excel 1 2 2.1 Excel.,. 2.2, x mathematical programming optimization problem, OR 1., 1 : f(x) h i (x) = 0, i = 1,..., m, g j (x) 0, j = 1,..., l, f(x) h i (x) = 0, i = 1,..., m, g j (x) 0, j = 1,..., l., f, g j, h i : IR n IR., f (objective function), x (decision variable), x h i (x) = 0, i = 1,..., m, g j (x) 0, j = 1,..., l (constraints) 2.,,, 2 : min{f(x) x S} = max{ f(x) x S}., S := {x IR n h i (x) = 0, i = 1,..., m, g j (x) 0, j = 1,..., l}. f, g j, h i, linear programming; LP. 2.3 Excel.,.,. Excel,, Excel2003 :..., Excel2003 : Excel2007 1. Office, Excel. Excel 1a. 2... 1. 2.
OR 2 Excel 2 3.. 4. OK. 1a: Excel2007 Office. Excel2003 1.. 1b. 2.. 3. OK. 2.,,. ツール アドイン 1b: Excel2003 :,.,.,.,,,.,,. 1. Excel2003.
OR 2 Excel 3 2: 2..,,,. 3... 3: 4..,,,. 2.4,. 1 2 Sheet1, 2 X Y A,B,C,,.,. X Y. : X, Y x, y. 4: Sheet1, f f = 90x + 100y. x, y.
OR 2 Excel 4,. x, y., A g A, X x 0.2kg, Y y 0.3kg, g A = 0.2x + 0.3y kg. A 27kg, g A 27. B, C. 0, x 0, y 0., LP : f = 90x + 100y g A = 0.2x + 0.3y 27 g B = 0.3x + 0.1y 20 g C = 0.1x + 0.4y 30 x 0, y 0., LP Sheet1. 3. 1 1 A,B,C., G5:G7 A,B,C 27, 20, 30. D10 D11 X Y LP x, y. F8 LP f, f : F8 : = D8 * D10 + E8 * E10 =sumproduct(d8:e8,d10:e10 G5, G6, G7, A,B,C LP g A, g B, g C, g A, g B, g C : F5 : = D5 * D10 + E5 * E10 =sumproduct(d5:e5,d10:e10 F6 : = D6 * D10 + E6 * E10 =sumproduct(d6:e6,d10:e10 F7 : = D7 * D10 + E7 * E10 =sumproduct(d7:e7,d10:e10 sumproduct 2. F5 F8 : F5 =sumproduct(d5:e5,d$10:e$10). F5 Ctrl+C, F5 F8.,,, OK. Ctrl+V,..
OR 2 Excel 5 5:,,. Excel2003 :,., F8,.., Sheet1 f F8,.. f,.,.., D10:E10..,, g A F5,, 27, OK.,. 6: 3 3, 7,, 8. 7: 1 1 8:
OR 2 Excel 6 x 0, y 0,,.,,, OK.,,,. 9:, OK,. 10:,, X : 47.14kg Y : 58.57kg, : 10,100. OK,,.,,,.,.,,,.
OR 2 Excel 7 2 2 Sheet2, 2 X Y,,,.,,,. X Y. X Y 300 400 1,000 3 1 5 30 20 70 240 260 2.5 2.4,.,, EXCEL. 3.. 2,450,. 8, 250 350,. 2 Sheet3,.. 2.,...., 2450, 8.. Int. 11:,.,, OK.,.
OR 2 Excel 8,. 4 4. : 2,500 3,000 11 4. 2 250 350, 300. 5,, 3.. 2 Sheet4 250 350 300 20,000 : =MAX(D5:D7)-MIN(D5:D7). 3, 5. 12:.,, OK.,. ショートケーキ 種 類 単 価 250 数 量 24 金 6000 額 チョコケーキ チーズケーキ 350 300 22 21 6300 7700 合 計 金 数 額 量 20000 ばらつき 67 3 目 的 関 数 予 算 金 額 [ 円 ] 20,000 13: 10 4.
OR 2 Excel 9 2.1 : f = x + y + z g 1 = 250 x + 350 y + 300 z 20, 000 g 2 = max{x, y, z} min{x, y, z} 3 x, y, z 5.,,, x, y, z., 2., : f = x + y + z g 1 = 250x + 350y + 300z 20, 000 g 2 = + u + v 3 g 3 = x u 0 g 4 = y u 0 g 5 = z u 0 g 6 = x v 0 g 7 = y v 0 g 8 = z v 0 x, y, z 5, u, v.. 2.6,. 5 2 Sheet5, A B 2 L1 L3 3. A, B 1 60, 40 100. L1 L3,. L1 L3 20, 30, 50. A, B.. 1 L1 L2 L3 A 30 50 40 B 60 30 50 A L1, L2, L3 x A1, x A2, x A3, B L1, L2, L3 x B1, x B2, x B3.,. x A1 x B3, P. P = 30 x A1 + 50 x A2 + 40 x A3 + 60 x B1 + 30 x B2 + 50 x B3
OR 2 Excel 10,,.. x A1 + x A2 + x A3 60, x B1 + x B2 + x B3 40, x A1 + x B1 = 20, x A2 + x B2 = 30, x A3 + x B3 = 50.,. 14: Excel, : A G7 = D7 + E7 + F7 =sum(d7:f7) B G8 = D8 + E8 + F8 =sum(d8:f8) L1 D9 = D7 + D8 =sum(d7:d8) L2 E9 = E7 + E8 =sum(e7:e8) L3 F9 = F7 + F8 =sum(f7:f8) G4:G5, D4:F4 A, D5:F5 B D7:F7, D8:F8, =D4*D7+E4*E7+F4*F7, =D5*D8+E5*E8+F5*F8, =sumproduct(d4:f4,d7:f7) =sumproduct(d5:f5,d8:f8), H4 =G4+G5 =sum(g4:g5). 15. 15:.,., A L1 : 20 L2 : 0 L3 : 40,, 3,600. B L1 : 0 L2 : 30 L3 : 10
OR 2 Excel 11 16:,,,,.,. 2.7,. 6 2 Sheet6, 5 A E L1 L3. 100. 2.. 1 2 3 A 80 20 0 B 30 40 30 C 0 100 0 D 20 20 60 E 50 0 50,, =1, 5. i j 1, 0 0-1 x ij,., 0 x ij 1. LP,.