Có những lúc làm Excel, bạn không chỉ cần cộng trừ nhân chia nữa. Bạn cần kiểu:
-
Làm sao lợi nhuận lớn nhất?
-
Làm sao chi phí nhỏ nhất?
-
Làm sao phân bổ nguồn lực cho “vừa đủ” mà vẫn đạt KPI?
Nghe giống bài toán quản trị, vận hành, sản xuất… đúng không? Và nếu bạn từng ngồi thử “tự chỉnh số” bằng tay, chắc bạn hiểu cảm giác đó: chỉnh một tí thì cái khác lại lệch. Thế là xoay vòng vòng, vừa mệt vừa không chắc có tối ưu thật không. 😵💫
Đây chính là lúc Solver trong Excel 2019 phát huy tác dụng. Nó giống như một “bộ não tối ưu hóa” nằm sẵn trong Excel: bạn đưa cho nó mục tiêu, đưa cho nó các biến cần thay đổi và các ràng buộc, rồi để nó tự tìm ra phương án tốt nhất.
Solver là gì? Nói dễ hiểu nhất có thể
Solver là một Add-in (tiện ích bổ sung) giúp Excel giải các bài toán tối ưu hóa. Tối ưu hóa nghĩa là bạn có một mục tiêu và bạn muốn nó:
-
Lớn nhất (Max) – ví dụ lợi nhuận tối đa
-
Nhỏ nhất (Min) – ví dụ chi phí tối thiểu
-
Bằng một giá trị cụ thể (Value Of) – ví dụ đạt doanh thu đúng 1 tỷ
Bằng cách thay đổi một hoặc nhiều “biến quyết định” (decision variables), đồng thời tuân thủ các “ràng buộc” (constraints) như:
-
Ngân sách không vượt quá X
-
Số giờ làm không vượt quá Y
-
Số lượng phải là số nguyên
-
Không được âm
Nói kiểu đời thường: bạn đưa luật chơi, Solver tự “tính đường đi nước bước”.
Bước 0: Bật Solver trong Excel 2019 (nếu bạn chưa thấy)
Nhiều bạn tìm hoài không thấy Solver vì nó chưa bật.
-
Vào File → Options
-
Chọn Add-ins
-
Ở cuối mục Manage, chọn Excel Add-ins → Go…
-
Tick Solver Add-in
-
OK
Sau đó, bạn vào tab Data sẽ thấy nút Solver xuất hiện.
Ví dụ thực tế dễ hiểu: tối đa hóa lợi nhuận (mô hình mini)
Giả sử bạn bán 2 sản phẩm: A và B.
-
Lợi nhuận A: 50k/sp
-
Lợi nhuận B: 30k/sp
Bạn bị giới hạn nguồn lực:
-
Tổng số sản phẩm tối đa: 100
-
Nguyên liệu: A tốn 2 đơn vị, B tốn 1 đơn vị, tổng nguyên liệu tối đa 120
-
Số lượng phải là số nguyên, không được âm
Cách dựng bảng trong Excel (gợi ý)
-
Ô B2: số lượng A (biến)
-
Ô B3: số lượng B (biến)
-
Ô B5: tổng lợi nhuận = 50000B2 + 30000B3
-
Ô B7: tổng số lượng = B2 + B3
-
Ô B8: tổng nguyên liệu = 2B2 + 1B3
Bạn thấy không, quan trọng là tạo các ô công thức để Solver “đọc” được mục tiêu và ràng buộc.
Mở Solver và thiết lập tối ưu hóa
Vào Data → Solver.
Bạn sẽ thấy 3 phần quan trọng:
1) Set Objective (Ô mục tiêu)
Chọn ô tổng lợi nhuận (ví dụ B5).
Rồi chọn:
-
Max (vì muốn lợi nhuận lớn nhất)
2) By Changing Variable Cells (Ô biến)
Chọn ô số lượng A và B (ví dụ B2:B3).
Đây là những ô Solver được phép thay đổi.
3) Subject to the Constraints (Ràng buộc)
Nhấn Add để thêm từng ràng buộc:
-
B7 <= 100 (tổng số lượng không quá 100)
-
B8 <= 120 (nguyên liệu không quá 120)
-
B2 >= 0, B3 >= 0 (không âm)
-
B2, B3 là int (số nguyên)
Xong ràng buộc, nhấn Solve. Solver sẽ tính và trả ra phương án tối ưu.
Chọn phương pháp giải (Solving Method) – chọn sao cho đúng?
Trong Solver có vài phương pháp, nhưng bạn chỉ cần nhớ nhanh thế này:
-
Simplex LP: bài toán tuyến tính (linear), phổ biến nhất, chạy nhanh
-
GRG Nonlinear: bài toán phi tuyến (nonlinear)
-
Evolutionary: bài toán phức tạp, ràng buộc lạ, có thể chạy lâu nhưng linh hoạt
Nếu mô hình của bạn là kiểu “cộng trừ nhân chia tuyến tính” (như ví dụ bên trên), cứ chọn Simplex LP là hợp lý.
Một vài mẹo để dùng Solver “trơn tru” hơn
1) Đặt mọi thứ thành công thức rõ ràng
Solver không đoán hộ bạn. Nó cần các ô mục tiêu, ô biến, ô ràng buộc đều phải có logic rõ ràng.
2) Dùng tên vùng (Named Ranges) nếu mô hình lớn
Ví dụ đặt tên:
-
QtyA,QtyB,ProfitTotal
Nhìn mô hình đỡ rối, sửa cũng nhanh.
3) Nhớ “Make Unconstrained Variables Non-Negative”
Trong Solver, tick mục này để tránh Solver cho ra số âm (nếu bạn quên đặt ràng buộc >=0).
4) Lưu lại kịch bản (Load/Save)
Nếu bạn hay chạy lại tối ưu nhiều lần, hãy lưu lại cấu hình Solver để khỏi nhập lại ràng buộc.
Lỗi hay gặp khi dùng Solver (để khỏi hoang mang)
-
Solver cannot find a feasible solution: ràng buộc quá chặt, không có nghiệm phù hợp
-
Results do not converge: mô hình phi tuyến khó, thử đổi phương pháp hoặc kiểm tra công thức
-
Ra số lẻ trong khi cần số nguyên: bạn quên ràng buộc int/binary
-
Chạy lâu: mô hình lớn hoặc dùng Evolutionary, nên tối giản biến và ràng buộc nếu có thể
Kết lại – Solver giúp bạn “đỡ đoán mò”, làm tối ưu có căn cứ hơn
Điểm mình thích nhất ở Solver trong Excel 2019 là: nó biến những bài toán kiểu “chỉnh tay mãi không ra” thành một quy trình rõ ràng. Bạn đặt mục tiêu, đặt biến, đặt ràng buộc, rồi để Excel tìm phương án tối ưu. Không còn cảm giác mò mẫm nữa, cũng đỡ tranh luận kiểu “phương án này chắc tốt nhất rồi”.
