Bài Toán Solver Trong Excel: Khám Phá, Ứng Dụng Và Các Bước Thực Hiện Chi Tiết

Có thể bạn quan tâm: Bài Test Excel Nhân Sự: Hướng Dẫn Toàn Diện Đánh Giá Kỹ Năng, Kiểm Tra Năng Lực Và Tối Ưu Quy Trình Quản Lý Nhân Sự

1. Giới thiệu chung về Solver trong Excel

Solver là một trong những công cụ tối ưu hóa mạnh mẽ nhất được tích hợp sẵn trong Microsoft Excel. Với khả năng giải quyết các bài toán tối ưu (linear programming – LP, nonlinear programming – NLP, integer programming – IP) và các mô hình phức tạp, Solver đã trở thành “cây bút” không thể thiếu đối với các nhà phân tích tài chính, nhà quản trị, kỹ sư, nhà nghiên cứu và cả những người dùng cá nhân muốn đưa ra quyết định dựa trên dữ liệu.

1.1. Lịch sử và sự phát triển

• 1990s: Solver xuất hiện như một add‑in của Microsoft, dựa trên công nghệ của Frontline Systems – một công ty chuyên về phần mềm tối ưu.
• 2000s: Microsoft tích hợp Solver vào các phiên bản Excel tiêu chuẩn, mở rộng tính năng hỗ trợ các bài toán nguyên (integer) và phi tuyến (nonlinear).
• Hiện tại: Solver hỗ trợ ba thuật toán chính: GRG Nonlinear, Simplex LP, và Evolutionary. Ngoài ra, người dùng có thể cài đặt thêm Premium Solver Platform để tiếp cận các thuật toán nâng cao như branch‑and‑bound, branch‑and‑cut, và solver dựa trên mô hình Gurobi, CPLEX.

1.2. Tại sao Solver quan trọng?

1. Tiết kiệm thời gian: Thay vì phải viết mã lập trình phức tạp, người dùng chỉ cần thiết lập các ô đầu vào, ràng buộc và mục tiêu, sau đó để Solver tự động tìm nghiệm tối ưu.
2. Độ chính xác cao: Thuật toán nội bộ được tối ưu hoá, cho phép giải các mô hình lớn với độ chính xác đáng tin cậy.
3. Khả năng mô hình hoá đa dạng: Từ bài toán tối ưu lợi nhuận, chi phí, lịch trình sản xuất, phân bổ nguồn lực, tới bài toán tối ưu danh mục đầu tư, thiết kế mạng lưới logistic…
4. Tính linh hoạt: Người dùng có thể thay đổi mục tiêu, ràng buộc, hoặc thậm chí thay đổi thuật toán chỉ trong vài cú click.

Có thể bạn quan tâm: Bài Test Excel Kế Toán Kho: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các loại bài toán Solver có thể giải quyết

2.1. Linear Programming (LP)

• Định nghĩa: Mục tiêu và các ràng buộc đều là hàm tuyến tính.
• Ví dụ thực tiễn: Tối ưu hoá sản xuất (max profit) khi nguồn lực (nguyên liệu, máy móc, thời gian) có giới hạn.
• Thuật toán: Simplex LP (được phát triển bởi George Dantzig).

2.2. Nonlinear Programming (NLP)

• Định nghĩa: Ít nhất một hàm (mục tiêu hoặc ràng buộc) là phi tuyến.
• Ví dụ: Tối ưu hoá hàm lợi nhuận có dạng lũy thừa, hàm logarit, hàm mũ; tối ưu hoá chi phí vận chuyển có công thức chi phí phi tuyến do khoảng cách và khối lượng.
• Thuật toán: GRG (Generalized Reduced Gradient) – phù hợp cho các hàm liên tục và khả vi.

2.3. Integer Programming (IP)

• Định nghĩa: Các biến quyết định phải là số nguyên (thường là 0 hoặc 1 – binary).
• Ví dụ: Lựa chọn dự án (chọn hoặc không chọn); lập lịch công việc (công việc được thực hiện hay không); quyết định mở/đóng nhà máy.
• Thuật toán: Branch‑and‑bound, Branch‑and‑cut (có trong Premium Solver).

2.4. Mixed‑Integer Programming (MIP)

• Định nghĩa: Kết hợp cả biến liên tục và biến nguyên.
• Ví dụ: Tối ưu hoá chi phí vận chuyển (lượng hàng là số thực) đồng thời quyết định mở kho (binary).

2.5. Evolutionary (Thuật toán di truyền)

• Định nghĩa: Thuật toán dựa trên nguyên tắc tiến hoá sinh học, thích hợp cho các mô hình phi tuyến, phi mượt, hoặc có nhiều cực địa phương.
• Ví dụ: Tối ưu hoá cấu hình hệ thống năng lượng, thiết kế mạng lưới logistic phức tạp, tối ưu hoá hàm mục tiêu không khả vi.

Có thể bạn quan tâm: Business Model Canvas Template Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Xây Dựng Mô Hình Kinh Doanh Hiệu Quả

3. Cài đặt và kích hoạt Solver trong Excel

3.1. Kiểm tra sự có mặt của Solver

1. Mở Excel → File → Options → Add‑Ins.
2. Ở phần Manage, chọn Excel Add‑ins và nhấn Go….
3. Trong danh sách, đánh dấu Solver Add‑in và nhấn OK.

Nếu không thấy Solver Add‑in, có thể cần cài đặt từ Microsoft Office Setup hoặc tải bản Premium Solver Platform từ trang Frontline Systems.

3.2. Giao diện Solver

Sau khi bật, một mục Solver sẽ xuất hiện trong tab Data. Khi nhấn vào, cửa sổ Solver Parameters hiện ra, bao gồm:

• Set Objective: ô chứa hàm mục tiêu.
• To: Max, Min hoặc Value Of (đặt giá trị mục tiêu).
• By Changing Variable Cells: các ô quyết định.
• Subject to the Constraints: bảng ràng buộc.
• Select a Solving Method: Simplex LP, GRG Nonlinear, Evolutionary.

4. Quy trình xây dựng mô hình Solver từ A‑Z

4.1. Bước 1: Xác định rõ mục tiêu và biến quyết định

• Mục tiêu: Tối đa hoá lợi nhuận, tối thiểu hoá chi phí, đạt mức lợi nhuận cố định, v.v.
• Biến quyết định: Các ô sẽ được Solver thay đổi để đạt mục tiêu (ví dụ: số lượng sản phẩm, tỷ lệ phân bổ tài sản).

4.2. Bước 2: Thu thập và nhập dữ liệu

• Dữ liệu đầu vào: Giá vốn, giá bán, công suất máy, thời gian làm việc, nhu cầu khách hàng, mức lợi nhuận kỳ vọng.
• Cấu trúc bảng: Nên sắp xếp dữ liệu dưới dạng bảng (Table) để dễ dàng mở rộng và sửa đổi.

4.3. Bước 3: Xây dựng công thức mục tiêu

Ví dụ: Tối đa hoá lợi nhuận
Giả sử có 3 sản phẩm (A, B, C) với lợi nhuận biên lần lượt là 10, 8, 12 (đơn vị: nghìn đồng). Nếu ô B2, B3, B4 chứa số lượng sản xuất, công thức lợi nhuận sẽ là:

=10B2 + 8B3 + 12B4

Đặt công thức này vào ô B6 và chọn ô này làm Set Objective.

4.4. Bước 4: Xây dựng các ràng buộc

4.4.1. Ràng buộc tài nguyên (Linear)

Giả sử:

• Nguyên liệu 1: 2 đơn vị cho A, 1 đơn vị cho B, 3 đơn vị cho C, tổng không vượt quá 100.
• Nguyên liệu 2: 1 đơn vị cho A, 2 đơn vị cho B, 1 đơn vị cho C, tổng không vượt quá 80.

Công thức ràng buộc:

=2B2 + 1B3 + 3B4 <= 100 (nguyên liệu 1)
=1B2 + 2B3 + 1B4 <= 80 (nguyên liệu 2)

4.4.2. Ràng buộc khả năng sản xuất (Integer)

Nếu mỗi lô sản xuất phải là một số nguyên, thêm ràng buộc:

B2, B3, B4 integer

Trong Solver, chọn Add, nhập ô và chọn int.

4.4.3. Ràng buộc phi tuyến (Nonlinear)

Giả sử chi phí vận chuyển theo công thức:

Chi phí = 0.5 (B2^0.8) + 0.4 (B3^0.9) + 0.6 (B4^0.7)

Nếu chi phí không vượt quá 50 nghìn đồng, ràng buộc:

=0.5B2^0.8 + 0.4B3^0.9 + 0.6B4^0.7 <= 50

Với ràng buộc phi tuyến, chọn GRG Nonlinear hoặc Evolutionary tùy vào tính chất hàm.

4.5. Bước 5: Chọn thuật toán phù hợp

4.6. Bước 6: Thiết lập Solver và chạy

1. Mở Solver → Set Objective (ô B6) → Max.
2. By Changing Variable Cells: B2:B4.
3. Add ràng buộc: nhập công thức và chọn ≤, =, ≥ hoặc int cho biến nguyên.
4. Select a Solving Method: Chọn thuật toán.
5. Nhấn Solve → Khi xuất hiện Solver Results, chọn Keep Solver Solution và OK.

4.7. Bước 7: Kiểm tra và phân tích kết quả

• Giá trị mục tiêu: Kiểm tra ô mục tiêu (B6) đã đạt giá trị tối đa hay chưa.
• Kiểm tra ràng buộc: Đảm bảo các ô ràng buộc thỏa mãn (có thể dùng công thức =IF(ràng buộc, "OK", "FAIL") để tự động kiểm tra).
• Phân tích độ nhạy (Sensitivity Analysis): Đối với LP, Solver cung cấp Sensitivity Report (báo cáo độ nhạy) cho phép xem ảnh hưởng của thay đổi hệ số mục tiêu hoặc giới hạn tài nguyên lên nghiệm tối ưu.

5. Ứng dụng thực tiễn của Solver trong các lĩnh vực

5.1. Quản trị tài chính – Tối ưu danh mục đầu tư

Có thể bạn quan tâm: Bài Test Excel Tuyển Dụng: Hướng Dẫn Chi Tiết, Mẫu Câu Hỏi Và Cách Đánh Giá Ứng Viên

• Mục tiêu: Tối đa hoá lợi nhuận kỳ vọng hoặc tối thiểu hoá rủi ro (độ lệch chuẩn) trong khi đáp ứng các ràng buộc như tỷ lệ đầu tư vào từng ngành, mức tối thiểu/ tối đa.
• Biến quyết định: Tỷ lệ phần trăm vốn đầu tư vào từng cổ phiếu.
• Ràng buộc:
• Tổng tỷ lệ = 100%
• Tỷ lệ mỗi cổ phiếu ≤ 20%
• Rủi ro ≤ mức cho phép.
• Thuật toán: GRG Nonlinear (vì hàm rủi ro thường là căn bậc hai của ma trận hiệp phương sai) hoặc Evolutionary nếu hàm phi mượt.

5.2. Sản xuất – Lập kế hoạch sản xuất

• Mục tiêu: Tối đa hoá lợi nhuận hoặc tối thiểu hoá chi phí sản xuất.
• Biến quyết định: Số lượng sản phẩm mỗi loại.
• Ràng buộc: Nguồn nguyên liệu, thời gian máy móc, công suất, tồn kho tối thiểu.
• Thuật toán: Simplex LP (trong trường hợp mọi hàm đều tuyến tính).

5.3. Logistic – Phân phối và vận chuyển

• Mục tiêu: Tối thiểu hoá tổng chi phí vận chuyển.
• Biến quyết định: Số lượng hàng hóa từ mỗi nhà kho tới mỗi khách hàng.
• Ràng buộc: Nhu cầu khách hàng, năng lực nhà kho, giới hạn phương tiện.
• Thuật toán: Simplex LP hoặc MIP nếu quyết định mở/đóng nhà kho (binary).

5.4. Nhân sự – Lập lịch làm việc

• Mục tiêu: Tối thiểu hoá tổng giờ làm thêm hoặc tối đa hoá mức độ hài lòng.
• Biến quyết định: Số ca làm việc cho mỗi nhân viên.
• Ràng buộc: Số giờ tối đa mỗi nhân viên, yêu cầu tối thiểu mỗi ca, luật lao động.
• Thuật toán: Integer Programming.

5.5. Năng lượng – Tối ưu hệ thống năng lượng tái tạo

• Mục tiêu: Tối đa hoá sản lượng điện thu được hoặc tối thiểu hoá chi phí đầu tư.
• Biến quyết định: Số lượng tấm pin, turbine, bộ lưu trữ.
• Ràng buộc: Diện tích đất, ngân sách, yêu cầu năng lượng tối thiểu.
• Thuật toán: Evolutionary hoặc MIP.

6. Những lưu ý quan trọng khi sử dụng Solver

6.1. Độ chính xác vs thời gian tính toán

• LP: Thường cho kết quả trong vài giây.
• NLP: Có thể mất vài phút, đặc biệt khi hàm phức tạp hoặc có nhiều biến.
• Evolutionary: Thời gian có thể kéo dài tới hàng chục phút hoặc hơn, tùy vào số thế hệ (generations) và kích thước quần thể (population size).

6.2. Điều kiện khả vi và liên tục

• GRG Nonlinear yêu cầu hàm mục tiêu và các ràng buộc phải khả vi (có đạo hàm). Nếu hàm chứa IF, OR, AND, hoặc các hàm rời rạc, Solver sẽ báo lỗi hoặc không hội tụ.
• Evolutionary không yêu cầu khả vi, nhưng cần thiết lập đúng bounds (giới hạn) cho các biến để tránh tìm kiếm vô hạn.

6.3. Định dạng dữ liệu

• Đảm bảo không có ô chứa lỗi (#N/A, #DIV/0!) trong vùng tính toán.
• Sử dụng named ranges để dễ quản lý và tránh lỗi khi thay đổi vị trí ô.

6.4. Kiểm tra tính ổn định của nghiệm

• Thực hiện sensitivity analysis (đối với LP) để xem nghiệm có thay đổi lớn khi tham số thay đổi không.
• Đối với NLP, chạy lại Solver với starting points khác nhau để kiểm tra tính ổn định (có thể có nhiều cực địa phương).

6.5. Sử dụng Macro VBA để tự động hoá

Nếu cần chạy nhiều mô hình cùng lúc (ví dụ: phân tích kịch bản), có thể viết macro VBA:

Sub RunSolverMultipleScenarios() Dim i As Integer For i = 1 To 10 ' Đặt giá trị cho các biến đầu vào Range("B2").Value = Worksheets("Scenario").Cells(i, 2).Value Range("B3").Value = Worksheets("Scenario").Cells(i, 3).Value ' Thiết lập Solver SolverReset SolverOk SetCell:=Range("B6"), MaxMinVal:=1, _ ByChange:=Range("B2:B4") SolverAdd CellRef:=Range("C2:C4"), Relation:=1, _ FormulaText:="100" SolverSolve UserFinish:=True ' Lưu kết quả Worksheets("Result").Cells(i, 2).Value = Range("B6").Value Next i
End Sub

Macro này giúp lặp lại quá trình giải cho 10 kịch bản khác nhau và lưu kết quả.

7. Ví dụ chi tiết: Tối ưu hoá danh mục đầu tư bằng Solver

7.1. Dữ liệu mẫu

7.2. Mô hình

• Biến quyết định: xA, xB, xC – tỷ lệ phần trăm đầu tư (tổng = 1).
• Mục tiêu: Tối đa hoá lợi nhuận kỳ vọng: 8xA + 10xB + 7xC.
• Rủi ro (độ lệch chuẩn danh mục):
  σ = SQRT( xA^212^2 + xB^215^2 + xC^210^2 + 2xAxB0.006 + 2xAxC0.004 + 2xBxC0.008 )
• Ràng buộc:
• xA + xB + xC = 1
• σ <= 0.12 (không quá 12%).
• xA, xB, xC >= 0.

7.3. Thực hiện trong Excel

1. Nhập dữ liệu vào bảng (ô B2:B4 cho lợi nhuận, C2:C4 cho độ lệch chuẩn, D2:E4 cho covariance).
2. Tạo ô tính tổng đầu tư: =SUM(F2:F4) (ô F6). Đặt ràng buộc =1.
3. Tạo công thức lợi nhuận: =SUMPRODUCT(B2:B4, F2:F4) (ô G6). Đặt là Set Objective, Max.
4. Tạo công thức độ lệch chuẩn:
5. Tạo ma trận covariance (ô H2:J4).
6. Công thức: =SQRT(MMULT(TRANSPOSE(F2:F4), MMULT(H2:J4, F2:F4))) (ô H6). Ràng buộc <=0.12.
7. Mở Solver:
8. Set Objective: G6 (Max).
9. By Changing Variable Cells: F2:F4.
10. Thêm ràng buộc:
    • F6 = 1
    • H6 <= 0.12
    • F2:F4 >= 0
11. Chọn GRG Nonlinear.
12. Nhấn Solve.

7.4. Kết quả mẫu

• Lợi nhuận kỳ vọng: 80.35 + 100.45 + 70.20 = 9.05%
• Độ lệch chuẩn: 0.118 (11.8%) – thỏa mãn ràng buộc.

7.5. Phân tích độ nhạy

Sau khi Solver trả về Answer Report, ta có:

• Shadow Price cho ràng buộc độ lệch chuẩn: cho biết lợi nhuận sẽ thay đổi bao nhiêu khi tăng giới hạn rủi ro 1%.
• Allowable Increase/Decrease cho lợi nhuận kỳ vọng của mỗi cổ phiếu: cho phép thay đổi mà không làm thay đổi cấu trúc danh mục.

8. Các lỗi thường gặp và cách khắc phục

9. Khi nào nên chuyển sang phần mềm tối ưu chuyên nghiệp

• Số biến > 5000 hoặc ràng buộc > 2000: Excel có giới hạn về bộ nhớ và tốc độ.
• Yêu cầu thời gian thực (ví dụ: tối ưu hoá trong giây trong hệ thống sản xuất tự động).
• Cần tính toán đa mục tiêu (multi‑objective) hoặc stochastic programming (xác suất).
• Yêu cầu tích hợp với hệ thống ERP/SCM: Các phần mềm như IBM ILOG CPLEX, Gurobi, MOSEK cung cấp API, giao diện web và khả năng mở rộng.

Trong những trường hợp này, bạn vẫn có thể export dữ liệu từ Excel sang file .lp hoặc .mps, rồi nhập vào các solver chuyên nghiệp để giải nhanh hơn và có báo cáo chi tiết hơn.

10. Tổng kết

Solver trong Excel không chỉ là một công cụ “đánh giá nhanh” mà còn là một nền tảng tối ưu hoá mạnh mẽ có thể giải quyết hầu hết các bài toán thực tiễn trong kinh doanh, tài chính, sản xuất, logistic và năng lượng. Khi nắm vững các bước:

1. Xác định mục tiêu và biến quyết định.
2. Thu thập dữ liệu và xây dựng công thức.
3. Đặt ràng buộc chính xác.
4. Lựa chọn thuật toán phù hợp.
5. Kiểm tra, phân tích và thực hiện sensitivity analysis.

Bạn sẽ có thể đưa ra quyết định dựa trên dữ liệu một cách khoa học, tối ưu chi phí và tăng lợi nhuận. Đồng thời, việc hiểu rõ các giới hạn của Solver (độ lớn mô hình, tính khả vi, thời gian tính toán) giúp bạn quyết định khi nào nên nâng cấp lên Premium Solver hoặc chuyển sang các giải pháp tối ưu hoá chuyên nghiệp.

Hy vọng bài viết này đã cung cấp cho bạn một hướng dẫn chi tiết, đầy đủ và thực tiễn để triển khai bài toán Solver trong Excel trong mọi môi trường công việc. Chúc bạn thành công trong việc áp dụng Solver để giải quyết những thách thức tối ưu hoá phức tạp!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.