Solver trong Excel là gì?
Solver trong Excel là một Add-in mà Microsoft xây dựng, giúp người dùng phân tích những dữ liệu trong Excel. Sau đó tìm ra giá trị tối ưu (lớn nhất hoặc nhỏ nhất) cho bài toán doanh nghiệp.
Tính tiện dụng mà Add-in Solver mang đến là công cụ này cho phép xác định giá trị lớn nhất hoặc nhỏ nhất của một ô bằng cách thay đổi dữ liệu của những ô khác mà không bị trả về kết quả sai lệch. Hiểu đơn giản là, chúng ta không cần phải tốn thời gian nhập số liệu nhiều lần, mà chỉ thay đổi những số liệu cần thiết.
Ví dụ: Nhân sự có thể thay đổi số lượng ngân sách quảng cáo dự kiến của mình và xem nó ảnh hưởng như thế nào đến số tiền lợi nhuận dự kiến của chiến dịch.
Cách thêm Solver trong Excel
Bước 1: Vào File > Options
Bước 2: Click Add-Ins, trong Manage chọn Excel Add-ins
Bước 3: Click Go
Bước 4: Trong hộp thoại Add-Ins có sẵn, chọn Solver Add-in và Click OK
Bước 5: Sau khi hoàn thành các bước trên, lúc này Solver Add-in đã xuất hiện trong phần Analysis của tab Data
Ví dụ về cách sử dụng Solver trong Excel
Trong ví dụ này ta sẽ tìm giải pháp cho bài toán tối ưu hóa đơn giản sau.
Bài toán: Giả sử, bạn là chủ một thẩm mỹ viện và đang có kế hoạch cung cấp một dịch vụ mới cho khách hàng. Để làm được điều này, cần trang bị một thiết bị mới có giá 40000 USD và cần phải trả góp trong vòng 12 tháng.
Mục tiêu: Tính toán chi phí tối thiểu cho mỗi một lần cung cấp dịch vụ, giúp bạn có thể trả hết chi phí cho thiết bị mới trong khoảng thời gian yêu cầu.
Bước 1: Chạy Excel Solver
Trong Tab Data > Analysis > Solver
Bước 2: Xác định vấn đề
Sau khi khởi động, cửa sổ Solver Parameters sẽ được mở ra và có 3 yếu tố bạn cần xác định:
– Objective cells
– Variable cells
– Constraints cells
Nói một cách chính xác, Excel Solver sẽ tìm giá trị tối ưu cho công thức trong ô Objective cells bằng cách thay đổi giá trị trong Variable cells và tuân theo các giới hạn trong Constraints cells.
Objective Cells
Objective Cells là ô chứa công thức thể hiện mục tiêu của vấn đề. Có thể là tối đa hóa, giảm thiểu hoặc để đạt được giá trị nào đó.
Trong ví dụ này, ô mục tiêu là B7, tính thời hạn thanh toán bằng công thức: =B3/(B4*B5) và kết quả công thức bằng 12.
Variable Cells
Đây là các ô chứa dữ liệu biến đổi, có thể được điều chỉnh để đạt được mục tiêu. Excel Solver cho phép chỉ định tối đa 200 ô biến.
Trong ví dụ này, chúng ta có một số ô có thể thay đổi giá trị:
- Khách hàng dự kiến mỗi tháng (B4) phải nhỏ hơn hoặc bằng 50
- Chi phí mỗi dịch vụ (B5).
Constraints Cells
Các Constraints là những điều kiện phải được đáp ứng. Để thêm Constraints, hãy làm như sau:
– Nhấp vào Add ngay bên phải Subject to the Constraints
– Trong Add Constraint, hãy nhập một điều kiện
– Nhấp Add để thêm
– Tiếp tục nhập thêm các điều kiện khác
– Sau khi đã nhập đến giới hạn cuối cùng, nhấp vào OK để quay lại
Excel Solver cho phép xác định các mối quan hệ sau đây giữa ô được tham chiếu và ràng buộc.
– Nhỏ hơn hoặc bằng, bằng và lớn hơn hoặc bằng. Bạn đặt các mối quan hệ này bằng cách chọn một ô trong các ô tham chiếu, chọn một trong các dấu hiệu sau: <= , =, hoặc > = , sau đó nhập một số, tham chiếu/tên ô hoặc công thức vào hộp Constraint (vui lòng xem ảnh chụp màn hình ở trên).
– Số nguyên. Nếu ô được tham chiếu phải là một số nguyên, hãy chọn int và từ integer sẽ xuất hiện trong hộp Constraint.
– Các giá trị khác nhau. Nếu mỗi ô trong phạm vi được tham chiếu phải chứa một giá trị khác nhau, hãy chọn dif và từ AllDierence sẽ xuất hiện trong hộp Constraint.
– Nhị phân. Nếu bạn muốn giới hạn một ô được tham chiếu là 0 hoặc 1, hãy chọn bin và từ binary sẽ xuất hiện trong hộp Constraint.
Lưu ý: Để chỉnh sửa hoặc xóa một ràng buộc hiện có, hãy làm như sau:
- Trong hộp thoại Solver Parameters, bấm vào Constraint.
- Để sửa đổi ràng buộc đã chọn, hãy nhấp vào Change và thực hiện các thay đổi bạn muốn.
- Để xóa ràng buộc, hãy nhấp vào nút Delete.
Trong ví dụ này, các ràng buộc là:
- B3 = 40000 – chi phí của thiết bị mới là $ 40.000.
- B4 <= 50 – số lượng bệnh nhân dự kiến dưới 50 tuổi mỗi tháng.
Bước 3: Giải quyết vấn đề
Sau khi bạn đã định cấu hình tất cả các tham số, hãy nhấp vào nút Solve ở cuối cửa sổ Solver Parameters để bổ trợ Excel Solver tìm giải pháp tối ưu cho vấn đề của bạn.
Tùy thuộc vào độ phức tạp của mô hình, bộ nhớ máy tính và tốc độ bộ xử lý, có thể mất vài giây, vài phút hoặc thậm chí vài giờ.
Khi Solver xử lý xong, nó sẽ hiển thị cửa sổ hộp thoại Solver Results , tại đây bạn chọn Keep the Solver Solution và bấm OK:
Cửa sổ Solver Result sẽ đóng lại và giải pháp sẽ xuất hiện trên Sheet ngay lập tức.
Trong ví dụ này, $66,67 xuất hiện trong ô B5, là chi phí tối thiểu cho mỗi lần cung cấp dịch vụ tương ứng, cho phép bạn trả hết chi phí cho thiết bị mới trong 12 tháng, miễn là có ít nhất 50 khách hàng mỗi tháng:
Xem thêm
Cách so sánh 2 bảng bằng Power Query trong Excel (có ví dụ)
Các hàm DAX mới trong Excel Data Models và Power Pivot