c Excel Excel Excel Excel Microsoft Excel 1. OR Microsoft Excel Excel 1 Excel Excel Excel or 2007 Excel OR Excel Excel LP Excel LP Excel 112 8551 1 13 27 1 Excel Excel Excel 2010 Excel OpenOffice Calc 2. Excel Excel 1 Excel 2010 1 (i) (ii) Excel DB 2 1 LP 0-1 [3] [1] 2012 4 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited. 3 175
DB 2 OK 1 2 Excel DB DB 3. 2 1 3 A B C 4 1 1 c ij 2 u ij a i b j 1 2 3 4 A 4 1 3 3 B 9 2 7 10 C 8 1 10 9 1 2 3 4 A 28 25 21 28 60 B 17 15 15 25 45 C 22 17 19 10 35 35 15 35 45 i {A, B, C} =: I j {1, 2, 3, 4} =: J x ij LP 3 (P1) (x ij ) c ijx ij (1) i I x ij a i, i I (2) x ij = b j, j J (3) i I 0 x ij u ij, i I,j J (4) c ij i j 1 a i i 2 b j j 2 u ij i j 2 Excel x ij 1 Excel 4 1 LP Excel 3 5 3 4 C4:F6 c ij C11:F13 u ij 3 1 H18:H20 a i 1 4 C22:F22 b j (1) (2) 3 (3) 4 (2) (4) x ij,i I,j J (2) i 4 x ij a i i I (3) j 3 i I x ij b j 2 1 Excel 3 (P1) (1) (1) x ij,i I,j J (P1) i j x ij c ijx ij (1) j J (4) i j x ij 0 u ij x ij,i I,j J 4 LP LP [2] 5 176 4 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited.
3 4 DB c ij,u ij,a i,b j x ij x ij 3 C18:F20 x ij G21 cijxij i j =SUMPRODUCT(C4:F6,C18:F20) 6 (2) j xij G18:G20 G18 =SUM(C18:F18) G19 G20 7 (3) C21:F21 C21 =SUM(C18:C20) D21:F21 8 1 DB 4 DB 6 SUMPRODUCT( 1, 2) 1 2 7 G18 [Ctrl]+[C] G19:G20 [Ctrl]+[V] OK G19 =SUM(C19:F19) 8 8 (i) G21 G21 $G$21 (ii) (iii) x ij C18:F20 (iv) (2) (4) DB DB (2) G18:G20 <= H18:H20 DB OK (2) 3 9 (3) (4) (3) 4 C21:F21 = C22:F22 (4) 9 2012 4 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited. 5 177
x ij u ij C18:F20 <= C11:F13 (4) x ij (v) LP 10 DB DB OK C18:F20 G21 669 Excel 11 j 1 0 0-1 x j J := {a,..., r} (P2) (x j ) b jx j (1) c jx j 100 (2) x j {0, 1}, j J (3) (P2) (0-1) x j,j J 0 1 (P1) (P2) Excel 5 5 4. B3:S3 c j B4:S4 Excel b j U3 100 B5:S5 x j T3 (2) =SUMPRODUCT(B3:S3,B$5:S$5) T4 T4 2 a r T4 B5:S5 100 3 18 j a b c d e f g h i j k l m n o p q r LP 1 (2) T3 <= U3 BB B E E E EEEEESSSSSSS x j (3) c j 21313152010123107 581055 9 54 b j 9 3 3 1 10 7 10 0 1 1 6 3 5 0 2 15 3 0 DB B5:S5 10 bin 6 Excel 2010 = 11 Excel SUMIF 0 1 178 6 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited.
LP 6 PC B5:S5 1 73 f ij z ij L11:O13 7 G22 =SUMPRODUCT(L4:O6,L11:O13) (1) fijzij i j G23 =SUM(G21:G22) (1) L18:O20 x ij x ij u ijz ij L18 =L11*C11 L18:O20 5. 5.1 1 3 i j 4 f ij 7 4 f ij 1 2 3 4 A 17 5 25 33 B 18 5 20 12 C 15 20 5 15 (P3) (x ij,z ij ) B 3 x B3 c B3x B3 = 7x B3 f B3 =20 (c ijx ij + f ijz ij) (1) i I x ij a i, i I (2) x ij = b j, j J (3) i I 0 x ij u ijz ij, i I,j J (4) z ij {0, 1}, i I,j J (5) 0-1 i j z ij (4) x ij > 0 z ij =1 1 L4:O6 1 DB (i) G23 (ii) L11:O13 12 (iii) C18:F20 <= C11:F13 DB L18:O20 (iv) L11:O13 bin z ij (5) DB 13 12 C18:F20, L11:O13 C18:F20,L11:O13 $ 13 2012 4 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited. 7 179
816 674 5.2 2 4 E 2 E 5 E 3 2 73? J E E E := {d,..., k} {a,..., r} =: J j E 5 3 E 5 1 0 z j {0, 1} j E (P4) (x j,z j ) c jx j 0.3 c jz j (1) j E b jx j 73 (2) z j x j, j E (3) z j 1 x 5 i, j E (4) i E x j {0, 1}, j J (5) z j {0, 1}, j E (6) (6) z j 0 1 (1) z j =1 j (4) x i =1 i 5 z j =1 14 (P4) 8 2 z j,j E E6:L6 8(a) T5 =SUM(E5:L5)/5 (4) T6 =0.3*SUMPRODUCT(E3:L3,E6:L6) OK DB DB (%) 0 14 (1) 2 U3 =T3-T6 (1) 8(b) 8 2 U3 B5:S5,E6:L6 (i) T4 >= 73 (2) 15 (ii) E6:L6 <= E5:L5 (3) (iii) E6:L6 <= T5 (4) (iv) B5:S5 = (5) (v) E6:L6 = (6) LP 73 87 16 6. Excel 2 4 Excel 0 1 bin int LP 2 4 0-1 15 8(b) U4 73 T4 >= U4 16 100 180 8 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited.
Excel LP LP GRGExcel 2010 2 17 Excel 2010 Excel Excel IF MAX MIN Excel 200 100 18 19 A := {(i, j) :i I,j J} (N,A) (x ij) (i,j) A 9 i ai bj j (P5) (x ij ) c ijx ij (1) x ij x ij = a i,i N (2) (i,j) A j:(i,j) A j:(j,i) A 0 x ij u ij, (i, j) A (3) (N,A) 1 (i, j) A i j (P5) A (i, j) i N (2) (2) j:(i,j) A xij j:(j,i) A xij Excel (i, j) SUMIF 20 10 1 i ai bj B j a B 35 SUMIF Excel 9 1 1 v N := I J 1 a j := b j,j J 17 18 Excel Frontline Systems HP www.solver.com/ 19 [2] 20 10 10 SUMIF 1 VBA Excel VBA 20 SUMIF 2012 4 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited. 9 181
DEA 200 LP VBA for Excel Excel Excel [1] Excel 2001. [2] 1987. [3] Excel OR 2011. 182 10 Copyright c by ORSJ. Unauthorized reproduction of this article is prohibited.