Solver và các bài tập liên quan đến kế hoạch sản xuất

  • Thread starter handung107
  • Ngày gửi
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Solver là một công cụ cao cấp của Excel, nhưng chắc các bạn ít người sử dụng. Thật ra, trong kế toán hàng ngày, với các bài toán quy hoạch tuyến tính đơn giản, bằng nhiều cách khác nhau, ta có thể tự tìm ra những gì cần giải, mà không cần dùng đến Solver, nhưng nếu ta hiểu thêm về Add-in này, chắc chắn sẽ không thừa đâu, các bạn à.

Trên diễn đàn, chưa có bài nào về Solver hoàn chỉnh, hôm nay, tôi sẽ giới thiệu với các bạn bằng những thí dụ cụ thể nhất để các bạn có thể áp dụng vào các bài toán hàng ngày

Bài toán 1 :

- Một xưởng sản xuất làm 3 sản phẩm : TV, máy Cassete, loa. Mỗi sản phẩm được lắp ráp từ những phụ kiện có sẵn trong kho. Có 5 loại vật tư phụ kiện : khung máy, đèn hình, bộ loa, bộ nguồn, bảng mạch điện tử. Mục tiêu là sản xuất đầy đủ các sản phẩm để có lãi nhiều nhất với số vật tư phụ kiện còn tồn trong kho.
Số vật tư tồn đầu kỳ là : 450 khung máy, 250 đèn hình, 800 bộ loa, 450 bộ nguồn và 600 bảng mạch điện tử.

Định mức cho :

- TV : 1 khung, 1 đèn hình, 2 bộ loa, 1 bộ nguồn, 2 bảng mạch điện tử
- Cassette : 1 khung, 2 bộ loa, 1 bộ nguồn, 1 bảng mạch điện tử
- Loa : 1 bộ loa, 1 bảng mạch điện tử

Lãi cho mỗi sản phẩm được dự tính là TV 75đ, Cassette 50đ, loa 35đ

Với đại số, chúng ta sẽ thiết lập các phương trình tuyến tính như sau :

Gọi x1 là số lượng TV, x2 là số lương Cassette, x3 là số lượng loa sản xuất. Ta có :

Tổng số lãi : L = 75x1 + 50x2 + 35 x3
Các điều kiện của bài toán là :

- 1x1 + 1x2 + 0x3 <=400 (khung máy)
- 1x1 + 0x2 + 0x3 <=200 (đèn hình)
- 2x1 + 2x2 +1x3 <=800 (bộ loa)
- 1x1 + 1x2 + 0x3 <=400 (bộ nguồn)
- 2x1 + 1x2 + 1x3 <=600 (bảng mạch điện tử )
Vì số san phẩm sản xuất x1, x2, x3 phải là số dương nên ta cũng có điều kiện là x1 >=0; x2>=0; x3>=0

Các bạn thấy đó, nếu giải bằng đại số...thực sự "phê" đấy. Chúng ta có thể nhờ Excel giải giùm bằng Solver
 
Khóa học Quản trị dòng tiền
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Bây giờ, chúng ta sẽ thiết lập mô hình bảng tính trogn Excel

Cách nhanh nhất (tuy không phải là tốt nhất), bạn sẽ đặt :
- A1 : giá trị của x1, ta gõ số 0 (vì chưa biết nghiệm số)
-A2 : giá trị của x2, cũng gõ 0
- A3 : giá trị của x3, gõ 0
- A4 : tổng số lãi thu được, ta gọi đây là hàm mục tiêu (HMT)
A4 = 75*A1 + 50*A2 + 35*A3

Tiếp theo, từ B1 đến B5, ta nhập các điểu kiện của phương trình

B1 = 1*A1 +1*A2 + 0*A3 (= A1 + A2) (khung máy)
B2 = A2 (đèn hình)
B3 = 2*A1 + 2*A2 + 1*A3 (bộ loa)
B4 = A1 + A2 (bộ nguồn)
B5 = 2*A1 + A2 + A3 (bảng mạch điện tử)

Đối thoại với Solver :

Ta đặt con trỏ ngay Cell A4 (HMT), và mở hộp thoại Solver Parameter bằng cách sử dụng Menu Tools / Solver. Nếu bạn không thấy Solver trong Menu này, bạn phải vào Tools / Add-in, đánh dấu kiểm vào Solver trong hộp thoại liệt kê DS các Add-in đã được cài vào Excel của bạn. Nếu bạn cũng không thấy ở đây, thì hoặc bạn nhấp vào nút Browse, tìm đường dẫn đến thư mục có Add-in này, hoặc bạn đã bỏ qua không cài nó khi cài Office. Trường hợp này, bạn phải tìm dĩa Office để cài thêm thôi

Khi hộp thoại Solver Parameter xuất hiện, bạn sẽ thấy các mục như sau :

- Set Target Cell : tại bảng tính như trên là A4, nếu các bảng khác, nó chính là HMT (hàm mục tiêu ta muốn tìm)
- Lựa chọn Max từ các Option, đúng cho trường hợp này
- Click chuột vào nút Guess, sẽ xuất hiện cửa sổ và bạn sẽ thấy A1:A3 (tức là các Cell liên quan đến A4)
- Bây giờ ta đưa các điểu kiện vào bằng cách nhấp nút Add --> Add Constraint
- Như vậy, tại Cell Reference, các bạn nhập địa chỉ các ô chứa các điều kiện, còn Constraint các bạn nhập điều kiện liên quan, giữa 2 hộp này, bạn sẽ chọn các điều kiện tương ứng như <=, <, =, >=, >
- thí dụ : Cell Reference nhập A1 :A3, nhấp mũi tên chọn >=, Constraint nhập số 0
- Nhấp Add, tiếp tục Cell Refernce nhập B1, Constraint nhập 400, chọn <=
- Tương tự cho đến B5
- Nhấp OK, trở lại hộp thoại Solver, bạn vào Options. Trên hộp thoại này, bạn đánh dấu vào khung kiểm tra "Assume Linear Model" (chuyển sang mô hình tuyến tính)
- Bây giờ, bạn nhấp Solve để Excel tìm các nghiệm vào A1, A2, A3
- Còn lại, các bạn sẽ hiểu cần làm gì rồi...
 
H

handung107

Cao cấp
28/8/04
576
15
0
VN
www.giaiphapexcel.com
Khai báo & Chỉ dịnh biến và các điều kiện

1/ Khai báo nhiều biến trong hộp thoại Solver

Trong hộp thoại "By Changing Cells" để khai báo biến cho mô hình, ta có thể :
- Gõ từ bàn phím tọa độ các vùng, mỗi vùng ngăn cách nhau bằng dấu phẩy
- Nhấp chuột, đồng thời giữ phím Ctrl để chọn các vùng biến

2/ Khai báo điều kiện ràng buộc :

- Với mô hình tuyến tính đơn giản này, vế trái của điều kiện luôn ở "Cell rẻence" như là các ô tham chiếu đơn lẻ, vế phải "Cóntraint" như là các hằng số. Tuy vậy, bạn có thể đưa vế trái là một vùng, một cột, nhưng không cho phép bạn nhiều lựa chọn cùng lúc
- Điều kiện ràng buộc bên vế phải cũng là một vùng phải có kích thước tương đương như vậy
- Với thí dụ trên, bạn có thể đưa vào cả vế trái vùng B1:B5, nhưng bạn phải nhập các số tương đương 400, 600, 800...vào một vùng khác C1:C5 chẳng hạn và đưa vào vế phải

3/ Tránh trùng lặp và xung đột khi khai báo điều kiện :

- Nếu ta khai báo ràng buộc cả cận trên lẫn cận dưới cho cùng một Cell thì Solver sẽ dùng cận chặt hơn. TD : Khi ta khai báo B1<=10 và B1<=5 thì Solver sẽ dùng B1<=5
- Nếu ta dùng ràng buộc cả <= và >= cho cùng một biến quyết định thì Solver sẽ báo lỗi khi ta chạy chương trình
 
Secret_grasses

Secret_grasses

Guest
quochung2005 nói:
chị có thể làm trên ECXELL để cho mọi ngừơi cùng học hỏi, em thấy đề tài này cũng hay đấy mà mọi người cũng ít biết

Đây là file SG làm,bạn xem thử nha.Bạn tham khảo thêm file của SG đã hướng dẫn chi tiết trên file tại đây.
 

Đính kèm

  • Bai tap Solver.rar
    3.7 KB · Lượt xem: 3,210
P

phuong98a

Guest
19/10/09
2
0
0
40
hn
Đây là file SG làm,bạn xem thử nha.Bạn tham khảo thêm file của SG đã hướng dẫn chi tiết trên file tại đây.

vậy cho mình hỏi là làm thế nào để tạo được bản report như trong bài tập của bạn vì khi mình kích vào solver thì ko có lựa chọn tạo bản báo cáo
 
P

phuong98a

Guest
19/10/09
2
0
0
40
hn
@ Serect grass : vậy cho mình hỏi là làm thế nào để tạo được bản report như trong bài tập của bạn vì khi mình kích vào solver thì ko có lựa chọn tạo bản báo cáo
 
T

thuydung2310

Guest
20/11/09
1
0
0
32
TP.HCM
Các anh chị trên diễn đàn cho e hỏi giai bài toán quy hoạch tuyến tính là làm sao.Em mới học Excel căn bản ở trường Đh.Cô cho bài tập cuối kỳ nộp làm bài thu hoạch + làm trên excel mà e không biết phải làm sao:
1/ f = 12x1+9x2+10x3+8x4-->min(max)
với các ràng buộc
3x1-2x2+x3+x4<=15
x1+2x2+2x3+3x4<=10
2x1+x2+2x3+x4>=12
Xi với i = 1,2,3,4:dance2:
làm bằng công cụ slover......E chưa biết phải làm sao và viết bài nộp cần viết thế nào.A chị nào biết giúp em với.Có gì a chị gợi ý rồi gưi cho mail:dungnt1991@gmail.com.Cám ơn a chị..em đang cần gấp:wall:
 
B

boyhung001

Guest
5/10/09
1
0
0
34
tp.hochiminh
choi em hỏi

đây là những bài tóan có 1 đích tới,còn những bài có 2 đích tới như là lợi nhuận và thời gian (tuần..) thì làm sao chị,hlep me....đang rất gấp
 
D

dangvanhuy

Guest
25/4/14
1
0
1
30
ha noi
Ðề: Solver và các bài tập liên quan đến kế hoạch sản xuất

Ban ơi xem hộ mình bài này với . : Cho n túi kẹo, mỗi túi kẹo có Ai viên. Tìm cách chia các túi kẹo trên làm hai phần sao cho sự chênh lệch giữ các phần là ít nhất
có 6 túi kẹo với số kẹo như sau
4 7 1 8 4 7
giai bằng slover nhé thank!
 
S

Sophia30

Guest
12/8/14
1
0
0
Bien Hoa
Ðề: Solver và các bài tập liên quan đến kế hoạch sản xuất

Mình đang làm về hoạch định số sắt để tính cần mua bao nhiêu cây sắt là ít nhất. Điều mình cần làm là như thế này: mình cần cắt 1239 khúc sắt 0.553m và 1239 khúc sắt 0.453 trên cây 6m. Vậy mình phải cắt bao nhiêu cây 6m và cắt như thế nào để được 1239 khúc 0.553m và 1239khuc 0.453. Mình giải theo phép thử thì mình ra cắt 94 cây 6m, mỗi cây 12 khúc 0.453 và 1 khúc 0.553 và 110 cây 6m mỗi cây được 10 khúc 0.553 và 1 khúc 0.453.
Có bạn nào biết cách lập phương trình và giải để ra đáp số tương tự thế này trên solver của excel không? nếu biết thì giúp mình với vì mình phải làm rất nhiều số liệu phức tạp và mất khá nhiều thời gian.
 
M

modeler

Guest
19/9/14
2
0
1
36
Ðề: Solver và các bài tập liên quan đến kế hoạch sản xuất

Mình đang làm về hoạch định số sắt để tính cần mua bao nhiêu cây sắt là ít nhất. Điều mình cần làm là như thế này: mình cần cắt 1239 khúc sắt 0.553m và 1239 khúc sắt 0.453 trên cây 6m. Vậy mình phải cắt bao nhiêu cây 6m và cắt như thế nào để được 1239 khúc 0.553m và 1239khuc 0.453. Mình giải theo phép thử thì mình ra cắt 94 cây 6m, mỗi cây 12 khúc 0.453 và 1 khúc 0.553 và 110 cây 6m mỗi cây được 10 khúc 0.553 và 1 khúc 0.453.
Có bạn nào biết cách lập phương trình và giải để ra đáp số tương tự thế này trên solver của excel không? nếu biết thì giúp mình với vì mình phải làm rất nhiều số liệu phức tạp và mất khá nhiều thời gian.

Chào bạn, bạn có thể làm như mình dưới đây:
Trước tiên bạn phải liệt kê các cách cắt cho một cây 6m (0x0.553m và 13x0.453m ... tới 10x0.553m và 1x0.453m). Giả sử bạn dùng cột B & cột C để ghi lại các cách cắt này, mỗi hàng là một cách cắt. Trên cột A, ứng với từng cách cắt bạn dùng để ghi lại số cây được áp dụng cho cách cắt đó. Ví dụ trong hình của mình (Hàng 4) thì có 89 cây dùng cách cắt 1x0.553m và 12x0.453m. Bạn có thể để trống cột A vì Solver sẽ điền vào cột A cho bạn.

B14, C14: Cuối cột B và cột C, bạn tính tổng số đoạn 0.553m và 0.453m cắt được nếu dùng số lượng cây như cột A cho mỗi cách cách ở cột B và C.

B15, C15: Số đoạn cần cắt mỗi loại là 1239.

B16: Tổng số cây phải dùng, là tổng của cột A.
slv.png

Như vậy, bạn chỉ định cho Solver ô mục tiêu là B16, và động tác là Minimize (tối thiểu số lượng cây 6m phải dùng), bằng cách điểu chỉnh các ô trên cột A, với các điều kiện là: (1) Các ô trên cột A phải là số nguyên, (2) B14 và C14 phải bằng B15 và C15.

Kết quả tối ưu là như trong hình và phải dùng 209 cây 6m.

P/S: Nếu cắt 94 cây 6m (1x0.553 và 12x0.453) và 110 cây 6m (10x0.553 và 1x0.453) thì bạn chỉ cắt được 1194x0.553 và 1238x0.453, không đủ yêu cầu đề ra rồi.
 
M

modeler

Guest
19/9/14
2
0
1
36
Mình chưa hiểu cách giải của bài này lắm, bạn nào có file excel gửi lên cho mình tham khảo với.
Thanks.

Mình format lại bảng tính và thêm giải thích để cho bạn dễ hiểu.

excelshot.png

Mình giả định mục tiêu của bài toán là tối thiểu hóa số cây 6m phải dùng. Nếu như bạn cần tối thiểu hóa số mẩu sắt vụn không xài tới thì có thể đặt thêm cột để tính và đặt mục tiêu cho Solver là tối thiểu hóa tổng của cột này.
 

Đính kèm

  • Book3.xlsx
    12.1 KB · Lượt xem: 415
Sửa lần cuối:
K

ketoan31

Trung cấp
10/5/07
77
1
8
Đồng Nai
Mình format lại bảng tính và thêm giải thích để cho bạn dễ hiểu.

View attachment 15071

Mình giả định mục tiêu của bài toán là tối thiểu hóa số cây 6m phải dùng. Nếu như bạn cần tối thiểu hóa số mẩu sắt vụn không xài tới thì có thể đặt thêm cột để tính và đặt mục tiêu cho Solver là tối thiểu hóa tổng của cột này.

Mình cảm ơn bạn đã giúp đỡ tạo file tham khảo, mình đã hiểu rõ hơn.

Chúc bạn sức khoẻ !
 
  • Like
Reactions: modeler

Xem nhiều

Webketoan Zalo OA