Excel Solver: Tối ưu nhiều biến với các ràng buộc

Q: Vì sao tôi nhận kết quả khác nhau mỗi lần chạy phương pháp Evolutionary?

Vì nó liên quan đến tính ngẫu nhiên. Giống như tung xúc xắc để tìm đường đi tốt nhất. Nếu bạn muốn kết quả nhất quán, vào Options > Evolutionary và đặt "Random Seed" thành một số cụ thể (ví dụ 1). Như vậy sẽ “tung xúc xắc” theo cùng cách mỗi lần.

Phân tích giả định (What-if) cho phép bạn thử các kịch bản khác nhau để xem chúng ảnh hưởng đến kết quả như thế nào. Đây là không gian an toàn để bạn thử nghiệm với các biến như chi phí hoặc năng lực nhằm tìm ra giải pháp hiệu quả nhất. Cách tiếp cận này rất quan trọng với bất kỳ ai quản lý nguồn lực, lập kế hoạch lịch trình hoặc đưa ra quyết định kinh doanh chiến lược.

Thử-sai thủ công tốn hàng giờ và thường bỏ lỡ đáp án tối ưu. Solver bảo đảm tính tối ưu toán học chỉ trong vài giây. Đó là khác biệt giữa việc phỏng đoán phân bổ nguồn lực và biết chắc bạn đang đưa ra quyết định tốt nhất có thể.

Hướng dẫn này sẽ dẫn bạn qua Excel Solver, tính năng nâng cao được xây dựng cho các bài toán tối ưu hóa như vậy. Tôi sẽ chỉ cho bạn cách thiết lập bảng tính, chạy phân tích và diễn giải kết quả bằng các ví dụ thực tế.

Tìm hiểu Excel Solver: Các khái niệm nền tảng

Trước khi hướng dẫn bạn cách dùng Solver, hãy cùng làm rõ ba khối xây dựng của mọi bài toán tối ưu. Đây không chỉ là thuật ngữ, mà là cốt lõi giúp Solver vận hành.

Ô mục tiêu, biến quyết định và ràng buộc

Hãy coi ô mục tiêu là đích đến của bạn. Đó là công thức bạn muốn tối đa hóa hoặc tối thiểu hóa, như tổng lợi nhuận hoặc tổng chi phí. Ô này phải là một công thức, không chỉ là một con số, vì Solver cần thấy nó thay đổi thế nào khi bạn điều chỉnh các yếu tố.

Biến quyết định là những gì bạn cần tìm. Chúng ta nên làm bao nhiêu chiếc ghế? Nên chi bao nhiêu cho mỗi kênh tiếp thị? Tôi luôn đặt các biến này ban đầu bằng 0 để dễ thấy điều gì đã thay đổi sau khi chạy Solver.

Ràng buộc là các giới hạn và quy tắc. Bạn không thể chi vượt ngân sách. Bạn không thể sản xuất số lượng âm. Một số dự án cần mức đầu tư tối thiểu. Các ràng buộc này biến bài toán toán học thành tình huống thực tế phản ánh đúng hạn chế kinh doanh.

Cách hoạt động là: Solver điều chỉnh các biến quyết định để tối ưu ô mục tiêu đồng thời bảo đảm tất cả ràng buộc được thỏa mãn. Nếu bạn từng học nghiên cứu vận hành, điều này sẽ quen thuộc. Nếu chưa, cứ nghĩ nó là thử-sai thông minh nhưng có bảo đảm.

So sánh với Goal Seek

Tôi thường được hỏi về điều này. Goal Seek đơn giản hơn. Nó thay đổi một ô để đạt một mục tiêu cụ thể. Dùng khi bạn biết chính xác kết quả mong muốn và chỉ cần tìm đầu vào đưa bạn tới đó.

Solver thì khác. Nó tìm câu trả lời tốt nhất khi bạn có nhiều quyết định phải đưa ra và nhiều ràng buộc cần tuân theo. TSolver được dùng nếu bạn cần tối đa hóa hoặc tối thiểu hóa một đại lượng trong khi cân đối nhiều biến.

Quy tắc ngón tay cái của tôi: nếu bạn có thể nói chính xác con số mình muốn, hãy dùng Goal Seek. Nếu bạn đang hỏi "cách tốt nhất để làm việc này là gì?" thì dùng Solver.

Tải và kích hoạt tiện ích bổ sung Solver

Solver đi kèm Excel, nhưng bạn cần bật nó trước. Đừng lo, mất chưa đến một phút.

Các bước cài đặt cho Windows và Mac

Với Windows:

File > Options > Add-ins
Chọn Excel Add-ins từ menu Manage, nhấp Go
Đánh dấu Solver Add-in, nhấp OK
Vào thẻ Data và tìm Solver trong nhóm Analyze

Với Mac:

Tools > Excel Add-ins
Đánh dấu Solver Add-in, nhấp OK

Nếu bạn dùng Excel Online, có tin không vui: hiện không hỗ trợ Solver. Bạn sẽ cần bản desktop (2016 trở lên).

Cấu hình

Sau khi kích hoạt, truy cập Solver qua thẻ Data > nút Solver. Thiết lập mặc định phù hợp cho hầu hết bài toán, nên đừng suy nghĩ quá nhiều phần này.

Tuy vậy, có hai thiết lập đáng chú ý khi gặp sự cố. Precision kiểm soát mức nghiêm ngặt khi thỏa mãn ràng buộc. Tôi siết chặt phần này cho mô hình tài chính nơi từng xu đều quan trọng. Convergence cho Solver biết khi nào dừng tìm cải thiện. Tôi hạ thấp nếu Solver dừng quá sớm với đáp án chưa thật tối ưu.

Bạn có thể tìm cả hai trong hộp thoại Solver Parameters > nút Options, nhưng thực ra, tôi hiếm khi đụng tới.

Thiết lập bài toán: Mô hình hóa trong Excel

Bảng tính gọn gàng giúp cấu hình Solver dễ dàng.

Hãy cùng đi qua một ví dụ: một nhà sản xuất nội thất làm ghế và bàn. Mỗi ghế lãi 60 đô (cần 4 giờ công, 2 đơn vị gỗ). Mỗi bàn lãi 75 đô (cần 6 giờ công, 2 đơn vị gỗ). Ta có 240 giờ công và 100 đơn vị gỗ. Nên làm mỗi loại bao nhiêu?

Xác định bài toán và bố trí bảng tính

Tôi sắp xếp mọi mô hình Solver theo cùng một cách. Luôn có bốn phần:

Dữ liệu đầu vào (thông tin sản phẩm, giới hạn nguồn lực)
Biến quyết định (số lượng cần sản xuất)
Hàm mục tiêu (tổng lợi nhuận)
Tính toán ràng buộc (nguồn lực sử dụng so với khả dụng)

Mô hình Solver hoàn chỉnh với cả bốn phần. Ảnh: Tác giả.

Bố cục này khiến mọi thứ minh bạch. Ai xem mô hình của bạn cũng thấy ngay bạn đang tối ưu cái gì và làm việc trong những giới hạn nào.

Giờ, tôi luôn tạo cái gọi là vùng được đặt tên (named ranges) cho các ô quan trọng. Thay vì viết =B8*B3+B9*B4, tôi có thể viết =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Dễ hiểu hơn rất nhiều sau sáu tháng khi bạn quên ý nghĩa các tham chiếu ô.

Vùng đặt tên giúp công thức dễ đọc. Ảnh: Tác giả.

Cách tạo: chọn ô, nhấp vào Name Box, gõ tên, nhấn Enter. Mất 2 giây, tiết kiệm bao nhiêu nhức đầu về sau.

Biến quyết định và hàm mục tiêu

Tôi khởi tạo biến quyết định bằng 0. Việc này giúp so sánh. Một số người thích bắt đầu với giá trị ước lượng, cũng ổn, nhưng 0 hợp với tôi.

Hàm mục tiêu phải là một công thức. Với ví dụ nội thất, tôi dùng:

=SUMPRODUCT(B8:B9,B3:B4)Được hỗ trợ bởi

Công thức này tính: (Ghế × $60) + (Bàn × $75)

Nhân và cộng đơn giản. Thế là đủ cho hầu hết bài toán kinh doanh.

Ràng buộc

Ràng buộc là nơi bạn chuyển các giới hạn kinh doanh thực thành toán học. Nhà sản xuất nội thất của chúng ta có hai ràng buộc nguồn lực:

Lao động: =SUMPRODUCT(B8:B9,C3:C4) <= 240
Gỗ: =SUMPRODUCT(B8:B9,D3:D4) <= 100

Và đây là điều tôi học được bằng trải nghiệm: luôn thêm ràng buộc không âm (B8:B9 >= 0). Nếu không, Solver có thể đề xuất làm số ghế âm, điều hiển nhiên là không thực tế.

Tôi cũng thêm một cột trạng thái với công thức =IF() như =IF(B16<=C16,"OK","EXCEEDED"). Điều này cho phản hồi trực quan ngay cả trước khi chạy Solver. Đèn xanh là ổn, đèn đỏ nghĩa là thiết lập có vấn đề.

Từng bước: Cách dùng Solver trong Excel

Ổn rồi, mô hình đã xong. Giờ tôi sẽ chỉ bạn cách thực sự chạy Solver. Khi đã quen, phần này chỉ mất khoảng 30 giây.

Dùng hộp thoại Solver Parameters

Mở Solver bằng cách nhấp thẻ Data > nút Solver.

Hộp thoại Solver trước khi cấu hình. Ảnh: Tác giả.

Bạn sẽ điền như sau:

Set Objective: Nhấp vào ô này, rồi nhấp ô B12 (ô Tổng lợi nhuận).

To: Chọn Max vì chúng ta muốn tối đa hóa lợi nhuận.

By Changing Variable Cells: Chọn B8:B9 (các số lượng chúng ta quyết định).

Subject to the Constraints: Nhấp Add cho từng ràng buộc:

Lao động: $B$16 <= $C$16
Gỗ: $B$17 <= $C$17
Không âm: $B$8:$B$9 >= 0

Select a Solving Method: Chọn Simplex LP. Tôi sẽ giải thích các phương pháp khác sau, nhưng với bài toán tuyến tính như thế này, Simplex LP là lựa chọn phù hợp.

Solver đã cấu hình và sẵn sàng giải. Ảnh: Tác giả.

Giải mô hình

Trước khi nhấp Solve, hãy xem nhanh các giá trị hiện tại. Mọi thứ nên đang là 0.

Trạng thái ban đầu với các ô đều bằng 0. Ảnh: Tác giả.

Giờ hãy nhấp Solve. Với bài toán đơn giản như của chúng ta, chỉ mất vài giây.

Thành công! Solver đã tìm ra nghiệm tối ưu. Ảnh: Tác giả.

Khi hộp thoại Solver Results bật lên, nó sẽ báo "Solver found a solution." Đánh dấu Answer và Sensitivity trong phần Reports. Chúng cung cấp phân tích chi tiết mà lát nữa ta sẽ xem. Sau đó nhấp OK.

Nghiệm tối ưu: 30 ghế và 20 bàn. Ảnh: Tác giả.

Vậy là xong. Kết quả cho thấy ta nên làm 30 ghế và 20 bàn để thu lợi nhuận 3.300 đô. Lưu ý cả hai ràng buộc đều hiện "OK", nghĩa là ta dùng nguồn lực hiệu quả mà không vượt giới hạn.

Tóm lược quy trình

Quy trình tôi luôn làm theo:

Xây mô hình (đầu vào, biến, mục tiêu, ràng buộc)
Cấu hình Solver (chọn tối ưu cái gì và điều chỉnh gì)
Nhấp Solve và chờ
Kiểm tra xem nghiệm có hợp lý về mặt kinh doanh không
Xem các báo cáo để có thêm insight

Bước cuối quan trọng. Solver tìm được một đáp án không có nghĩa đó là đáp án phù hợp với doanh nghiệp bạn. Luôn kiểm tra lý tính của kết quả.

Phương pháp giải, tham số và tùy chọn nâng cao của Solver

Solver cung cấp ba thuật toán khác nhau. Chọn đúng giúp cải thiện lớn về tốc độ và độ chính xác.

Tổng quan các phương pháp giải

Ba phương pháp giải cho các loại bài toán khác nhau. Ảnh: Tác giả.

Simplex LP

Simplex LP là lựa chọn tôi dùng 80% thời gian. Dành cho bài toán tuyến tính nơi mọi thứ chỉ là cộng và nhân. Phối hợp sản phẩm? Phân bổ ngân sách? Hoạch định nguồn lực? Simplex LP. Nhanh và đảm bảo bạn nhận được đáp án tốt nhất.

GRG Nonlinear

GRG Nonlinear dùng khi công thức có đường cong, bình phương hoặc hàm mũ. Hãy nghĩ tới mô hình định giá nơi cầu giảm khi giá tăng nhưng không theo đường thẳng. Hoặc tối ưu danh mục với tính phương sai. Nó tìm cực trị địa phương, có thể không phải tốt nhất tuyệt đối, nhưng thường là đủ tốt.

Evolutionary

Evolutionary là lựa chọn “dò lực”. Dùng khi bạn có ràng buộc số nguyên (chỉ sản xuất số nguyên), quyết định nhị phân (có/không), hoặc có hàm =IF() trong công thức. Nó chậm hơn vì về cơ bản thử nhiều nghiệm ngẫu nhiên và giữ lại nghiệm tốt. Nhưng nó hoạt động khi các phương pháp khác thất bại.

Phương pháp	Khi nào dùng	Tốc độ	Ví dụ
Simplex LP	Bài toán tuyến tính (chỉ cộng & nhân)	Nhanh	Phối hợp sản phẩm, phân bổ ngân sách
GRG Nonlinear	Bài toán có đường cong, bình phương, hàm mũ	Trung bình	Mô hình định giá, tối ưu danh mục
Evolutionary	Ràng buộc số nguyên, hàm IF	Chậm	Lập lịch, chọn dự án

Quy tắc lựa chọn của tôi rất đơn giản: bắt đầu với Simplex LP. Nếu công thức có số mũ hoặc tích của các biến, chuyển sang GRG Nonlinear. Nếu cần biến số nguyên hoặc nhị phân, dùng Evolutionary.

Tùy chọn và thiết lập Solver

Nhấp Options trong hộp thoại Solver để xem thiết lập nâng cao. Tôi hiếm khi chỉnh, nhưng đây là những thứ quan trọng:

Giới hạn thời gian chạy bằng Max Time. Mặc định là 100 giây, đủ cho đa số bài toán. Tôi tăng lên với mô hình rất lớn cần thêm thời gian xử lý.

Giới hạn số lần thử của Solver bằng Iterations. Nếu chạm giới hạn mà chưa giải được, hãy tăng lên.

Kiểm soát mức nghiêm ngặt với ràng buộc bằng Precision. Tôi siết chặt cho mô hình tài chính nơi từng xu đều quan trọng, còn lại để mặc định.

Tạo báo cáo và phân tích nhạy cảm

Nhớ lúc tôi bảo bạn đánh dấu các ô báo cáo chứ? Đây là những gì bạn nhận được.

Answer Report ghi lại nghiệm. Ảnh: Tác giả.

Answer Report là hồ sơ truy vết. Nó cho thấy điều gì đã thay đổi: biến quyết định từ 0 thành 30 ghế và 20 bàn, lợi nhuận từ 0 lên 3.300 đô. Nó cũng cho biết ràng buộc nào “ràng buộc chặt” (dùng hết) so với không ràng buộc (còn dư). Ở ví dụ này, cả lao động và gỗ đều ràng buộc chặt, nghĩa là ta dùng hết công suất khả dụng.

Đầu vào có thể thay đổi bao nhiêu trước khi nghiệm thay đổi. Ảnh: Tác giả.

Sensitivity Report mới là phần thú vị. Các cột Allowable Increase/Decrease cho thấy đầu vào có thể thay đổi bao nhiêu trước khi bạn cần một nghiệm khác. Khoảng rộng nghĩa là nghiệm vững. Khoảng hẹp nghĩa là thay đổi nhỏ có thể đảo lộn mọi thứ.

Shadow price thể hiện giá trị của nguồn lực. Ảnh: Tác giả.

Nhưng “vàng” thật sự nằm ở shadow price. Nó cho biết thêm một đơn vị nguồn lực đáng giá bao nhiêu. Nếu lao động có shadow price 7,5 đô, thêm một giờ công tăng 7,5 đô lợi nhuận. Vậy nếu bạn thuê thời vụ với 5 đô/giờ, hãy làm. Nếu 10 đô/giờ, bỏ qua.

Tầm quan trọng của phân tích nhạy cảm

Tôi dùng các báo cáo này để trả lời ba câu hỏi: Nên đầu tư vào nguồn lực nào? Nghiệm của tôi có mong manh không? Nếu dự báo sai thì sao? Các báo cáo đưa ra câu trả lời rõ ràng cho cả ba.

Đầu tiên, tôi xem shadow price để quyết định nên đầu tư vào nguồn lực nào. Shadow price cao nghĩa là giá trị cao.

Tiếp theo, tôi kiểm tra khoảng cho phép để xem nghiệm có mong manh không. Nếu lợi nhuận chỉ cần thay đổi 1 đô là nghiệm đổi, bạn đang ở lằn ranh mỏng. Nếu có thể thay đổi 50 đô, bạn ổn.

Cuối cùng, tôi hỏi nếu dự báo sai thì sao? Khoảng cho phép cho biết bạn có thể sai lệch bao nhiêu trước khi cần tính lại.

Lời khuyên của tôi: luôn kiểm tra shadow price trước khi duyệt ngân sách. Nếu một nguồn lực có shadow price bằng 0, nghĩa là bạn dư công suất. Đầu tư thêm vào đó là lãng phí. Hãy dồn tiền vào nơi shadow price cao nhất.

Xử lý sự cố và khắc phục lỗi

Thông báo lỗi của Solver nghe có vẻ đáng ngại nhưng thường dễ sửa. Đây là những lỗi tôi hay gặp nhất.

Lỗi và thông báo thường gặp

Solver could not find a feasible solution

Điều này nghĩa là các ràng buộc xung đột nhau.

Không có nghiệm thỏa mọi ràng buộc. Ảnh: Tác giả.

Thường xảy ra khi bạn yêu cầu điều bất khả thi, như đặt mục tiêu lợi nhuận vượt quá khả năng nguồn lực. Cách khắc phục là gỡ từng ràng buộc rồi chạy lại để xác định chính xác ràng buộc gây xung đột.

Set Cell values do not converge

Nghĩa là hàm mục tiêu có thể tăng mãi. Bạn đang thiếu một giới hạn trên nào đó. Doanh nghiệp thực luôn có giới hạn, hãy thêm vào.

Solver cannot improve the current solution

Thường gặp ở bài toán phi tuyến. Hãy thử giá trị khởi đầu khác hoặc chuyển sang phương pháp Evolutionary. Đôi khi cực trị địa phương là đủ tốt.

Lỗi trong mô hình hóa

Vòng tham chiếu (Circular references)

Xảy ra khi Ô A phụ thuộc Ô B, còn Ô B phụ thuộc Ô A. Excel sẽ cảnh báo bạn. Dùng thẻ Formulas > Trace Precedents để tìm vòng lặp và phá vỡ nó.

Ràng buộc số nguyên

Thêm ràng buộc số nguyên cho số nguyên dương. Ảnh: Tác giả.

Hạn chế của Solver và lựa chọn thay thế cho bài toán quy mô lớn

Excel Solver rất hữu ích, nhưng có những giới hạn bạn cần biết.

Hiểu các giới hạn của Solver

Bản tiêu chuẩn của Solver tối đa 200 biến quyết định và 100 ràng buộc. Nghe nhiều, nhưng thử lập lịch cho 200 nhân sự qua 50 khung giờ xem. Đó là 10.000 biến, vượt xa khả năng của Solver.

Tôi dùng Solver cho kế hoạch sản xuất hàng tháng, ngân sách hàng quý và lựa chọn dự án. Rất phù hợp cho quyết định mang tính chiến thuật. Nhưng nó không được xây cho các bài toán cấp doanh nghiệp như mạng lưới chuỗi cung ứng với hàng nghìn địa điểm.

Giải pháp thay thế cho bài toán lớn

OpenSolver

OpenSolver là miễn phí và gỡ bỏ những giới hạn đó. Nó xử lý số biến nhiều bằng bộ nhớ máy bạn cho phép, dùng cùng giao diện với Excel Solver, và bạn có thể tải từ OpenSolver.org. Tôi khuyên dùng khi bạn vượt quá giới hạn của Solver tiêu chuẩn.

Frontline Solver Premium

Frontline Solver là trả phí (300–1.500 đô/năm) nhưng hỗ trợ hàng nghìn biến với thuật toán tốt hơn. Đây là lựa chọn tôi sẽ dùng nếu làm việc này toàn thời gian chuyên nghiệp.

Phần mềm độc lập

Các công cụ như CPLEX hoặc Gurobi xử lý hàng triệu biến nhưng cần kiến thức lập trình và đầu tư thực sự. Đó là cấp độ doanh nghiệp.

Theo tôi, với hầu hết nhà phân tích kinh doanh, Excel Solver đáp ứng 95% nhu cầu. Nếu chạm giới hạn của nó, đó thực ra là tín hiệu tốt: công việc của bạn đủ phức tạp để biện minh cho công cụ tốt hơn.

Kết luận

Chúng ta đã đi qua rất nhiều, từ thiết lập cơ bản đến diễn giải shadow price.

Giờ đây, thay vì tranh luận quan điểm trong phòng họp, bạn có thể đưa con số lên màn hình. "Đây là kế hoạch tối ưu về mặt toán học." Điều đó thay đổi cuộc trò chuyện. Bạn ngừng đoán mò và bắt đầu chứng minh.

Hãy thử áp dụng cho vấn đề rối rắm tiếp theo của bạn, xung đột lịch hay bóp chặt ngân sách mà bạn đang né. Có thể đáp án hoàn hảo vẫn luôn ẩn trong bảng tính của bạn bấy lâu.

Và nếu bạn muốn học thêm kỹ thuật để gây ấn tượng với sếp, hãy học khóa Data Analysis in Excel để tiếp tục trau dồi.

Tác giả

Khalid Abdelaty

Tôi là một kỹ sư dữ liệu và người xây dựng cộng đồng, làm việc với pipeline dữ liệu, đám mây và công cụ AI, đồng thời viết các hướng dẫn thực hành, tác động cao cho DataCamp và các nhà phát triển mới nổi.

Câu hỏi thường gặp về Excel Solver

Tôi có thể dùng Solver với macro VBA không?

Chắc chắn rồi. Bạn có thể tự động hóa mọi thứ tôi vừa trình bày. Ghi macro khi chạy Solver để lấy mã cơ bản, rồi tinh chỉnh. Hãy nhớ tham chiếu thư viện Solver trong trình soạn thảo VBA (Tools > References) nếu không mã sẽ không chạy.

Solver có hoạt động trên trang tính được bảo vệ không?

Không, sẽ gặp giới hạn. Solver cần thay đổi giá trị ô để hoạt động. Nếu các ô đó bị khóa, nó sẽ thất bại. Hãy bỏ bảo vệ trang tính trước khi chạy, hoặc viết macro để bỏ bảo vệ, giải, rồi bảo vệ lại.

Solver có xử lý logic "If-Then" mà không cần phương pháp Evolutionary chậm không?

Có, với một mẹo! Thay vì dùng hàm IF (bắt buộc bạn dùng phương pháp Evolutionary chậm), hãy dùng biến nhị phân (0 hoặc 1). Ví dụ, Constraint * BinaryVariable <= Limit. Cách này giữ mô hình tuyến tính để bạn vẫn dùng được Simplex LP nhanh. Có, với một mẹo! Thay vì dùng hàm IF (bắt buộc bạn dùng phương pháp Evolutionary chậm), hãy dùng biến nhị phân (0 hoặc 1). Ví dụ, Constraint * BinaryVariable <= Limit. Cách này giữ mô hình tuyến tính để bạn vẫn dùng được Simplex LP nhanh.

Vì sao tôi nhận kết quả khác nhau mỗi lần chạy phương pháp Evolutionary?