Cách sử dụng Goal Seek và Solver trong Excel để tính toán các biến chưa xác định

Excel có thể giải các biến không xác định bằng Goal Seeker hoặc Solver. Trong bài viết này, chúng ta sẽ cùng tìm hiểu cách cách dùng Goal Seek và Solver để tìm kiếm các biến chưa xác định trong Excel.

Bạn có thể làm tất cả mọi thứ với Excel, với điều kiện bạn có đủ mọi dữ liệu cần thiết cho tính toán của mình. Nhưng nếu không thì sao? Với các biến không xác định, hãy sử dụng Add-on Goal Seek và Solver.

Xem thêm: Hướng dẫn sử dụng Add-ons Power Tools để tìm kiếm nâng cao trên trang tính Google Sheets

 

Xác định mục tiêu trong Excel với Goal Seek 

Goal Seek đã được tích hợp sẵn trong Excel, giúp bạn dễ dàng giải quyết những bài toán phức tạp như xác định giá bán ra tối thiểu để hòa vốn, lãi suất và số tiền phải đóng cho khoản trả góp,... Đây là một kỹ thuật cực kỳ mạnh mẽ và hữu ích trong phân tích dữ liệu... Tiện ích này nằm ở tab Data trong menu What-If Analysis.

Trong ví dụ này, chúng ta sẽ sử dụng một bộ dữ liệu rất đơn giản bao gồm doanh số bán hàng của 3 quý và mục tiêu cần đạt của năm. Bây giờ hãy sử dụng Goal Seek để tìm ra những con số cần có trong Q4 để đạt được mục tiêu.

 

Như bạn thấy, tổng doanh số hiện tại là 114.706 sản phẩm. Nếu chúng ta muốn bán 250.000 sản phẩm vào cuối năm thì cần phải bán bao nhiêu sản phẩm trong Q4? Hãy sử dụng Goal Seek để biết kết quả.

1. Nhấp vào Data > What-If Analysis > Goal Seek. Thao tác này sẽ mở ra một cửa sổ nhỏ:

 

2. Đặt phần "equals" của phương trình trong trường Set Cell. Đây là con số mà Excel sẽ cố gắng tối ưu hóa. Trong ví dụ trên sẽ là tổng số doanh số bán hàng trong ô B5.

3. Nhập giá trị mục tiêu vào trường To value. Chúng ta đang tìm kiếm tổng số 250.000 sản phẩm được bán, vì vậy chúng ta sẽ đặt "250.000" vào trường này.

4. Cho Excel biết biến nào cần giải quyết trong trường By changing cell. Chúng ta muốn xem doanh số bán hàng trong Q4 cần gì. Vì vậy hãy yêu cầu Excel giải quyết ô D2. Ảnh dưới là khi nó sẵn sàng hoạt động:

 

5. Bấm nút OK để giải quyết mục tiêu. Excel sẽ cho bạn biết khi Goal Seek tìm ra giải pháp.

 

6. Bấm nút OK một lần nữa để thấy giá trị giải phương trình trong Excel ở ô mà bạn đã chọn trong trường By changing cell.

 

Kết quả là 135.294 sản phẩm. Tất nhiên là chúng ta có thể tìm ra kết quả bằng cách trừ tổng số đang chạy cho mục tiêu hàng năm. Nhưng Goal Seek cũng được sử dụng trên một ô đã có dữ liệu trong đó. Và điều đó hữu ích hơn.

Lưu ý rằng Excel sẽ ghi đè dữ liệu trước đó của chúng ta. Bạn nên chạy Goal Seek trên bản sao dữ liệu của mình và ghi chú vào dữ liệu đã sao chép rằng dữ liệu được tạo bằng Goal Seek để không nhầm lẫn nó với dữ liệu hiện tại.

Goal Seek là một tính năng Excel hữu ích nhưng không phải tiện ích ấn tượng nhất. Chúng ta hãy xem xét một công cụ thú vị hơn nhiều: đó là Solver add-in.

Cách tải và sử dụng Solver add-in trong Excel

Solver giống như một phiên bản đa biến của Goal Seek, hoạt động bằng cách sử dụng một biến mục tiêu và điều chỉnh một số biến khác cho đến khi nó nhận được câu trả lời.

Solver có thể giải quyết giá trị lớn nhất của một số, giá trị nhỏ nhất của một số hoặc một số chính xác.

Đây là một sự lựa chọn tuyệt vời nếu bạn muốn giải quyết nhiều biến không xác định trong Excel. Nhưng việc tìm kiếm và sử dụng công cụ này không hề đơn giản.

Chúng ta hãy cùng tìm hiểu cách tải và sử dụng Solver add-in trong Excel ngay phần bên dưới nhé. Bên cạnh đó, nếu bạn đang tìm kiếm khóa học Excel nâng cao, hãy xem thêm khóa học Power Pivot, Power Query - Biến Excel thành công cụ Phân tích dữ liệu chuyên sâu để khai thác tốt hơn nữa công cụ hữu ích này.

Cách tải Solver add-in trong Excel

Theo mặc định, Excel không có sẵn tính năng Solver. Đây là một phần bổ trợ nên bạn cần tải xuống giống như các tính năng Excel khác. 

Truy cập vào File > Options > Add-Ins, sau đó nhấp vào Go bên cạnh Manage: Excel Add-Ins.

Nếu menu xổ xuống này không hiển thị "Excel Add-Ins" thì bạn sẽ cần phải thay đổi một chút:

 

Trong cửa sổ mới sẽ có một vài tùy chọn. Đánh dấu vào ô bên cạnh dòng chữ Solver Add-In và bấm nút OK.

 

Bạn có thể tìm thấy nút Solver trong nhóm Analysis của tab Data. Sau khi tải phần bổ trợ, đã đến lúc tìm hiểu cách sử dụng nó.

Cách sử dụng Solver trong Excel 

Solver có ba phần chính: the objective (mục tiêu), the variable cells (các ô biến) và the constraints (các ràng buộc). Chúng ta sẽ tìm hiểu từng bước.

 

1. Nhấp vào Data > Solver. Bạn sẽ thấy cửa sổ Solver Parameters bên dưới. (Nếu bạn không thấy nút solver thì hãy xem lại cách tải Solver add-in ở phần trước).

2. Đặt mục tiêu ô (set objective) và cho Excel biết mục tiêu của bạn. Mục tiêu nằm ở đầu cửa sổ Solver và có hai phần: ô mục tiêu và lựa chọn max, min hoặc một giá trị cụ thể.
 

 

3. Nếu bạn chọn Max, Excel sẽ điều chỉnh các biến để ô mục tiêu có thể đạt được giá trị lớn nhất. Min thì ngược lại: Solver sẽ giảm thiểu số mục tiêu. Value Of cho phép bạn chỉ định một số cụ thể để Solver tìm kiếm.

 

4. Chọn các ô biến mà Excel có thể thay đổi trong trường By Changing Variable Cells. Bấm vào mũi tên bên cạnh trường, sau đó nhấp và kéo để chọn các ô Solver sẽ làm việc. Lưu ý rằng tất cả các ô có thể khác nhau. Nếu bạn không muốn một ô thay đổi thì đừng chọn nó.

5. Đặt ràng buộc trên nhiều biến hoặc một biến riêng lẻ. Đây là nơi Solver khẳng định sức mạnh của mình. Bạn không cần thay đổi bất kỳ ô biến nào thành bất kỳ số nào mà chỉ cần chỉ định các ràng buộc cần được đáp ứng. 

Sau khi thêm tất cả thông tin, hãy bấm nút Solve để nhận kết quả. Excel sẽ cập nhật dữ liệu bao gồm các biến mới (đây là lý do tại sao chúng tôi khuyên bạn nên tạo bản sao dữ liệu của mình trước).

Cách đặt các ràng buộc trong Solver

Để thêm constraints (các ràng buộc), hãy bấm nút Add bên cạnh Constraint list. Thao tác này sẽ mở ra một cửa sổ mới. Chọn ô (hoặc các ô) được giới hạn trong trường Cell Reference, sau đó chọn một toán tử.

Dưới đây là các toán tử có sẵn:

• <= (nhỏ hơn hoặc bằng)
• = (bằng)
• => (lớn hơn hoặc bằng)
• int (phải là một số nguyên)
• bin (phải là 1 hoặc 0)
• AllDifferent

AllDierence có chút khó hiểu. Nó chỉ định mọi ô trong phạm vi bạn chọn cho Cell Reference phải là một số khác. Nhưng nó cũng chỉ định rằng chúng phải nằm trong khoảng từ 1 đến số ô. Vì vậy, nếu bạn có ba ô, bạn sẽ kết thúc bằng các số 1, 2 và 3 (nhưng không nhất thiết phải theo thứ tự đó).

Cuối cùng là thêm giá trị cho ràng buộc này.

Bạn có thể chọn nhiều ô cho Cell Reference. Ví dụ, nếu bạn muốn sáu biến có giá trị trên 10, bạn có thể chọn tất cả chúng và nói với Solver rằng chúng phải lớn hơn hoặc bằng 11. Bạn không cần thêm ràng buộc cho mỗi ô.

 

Bạn cũng có thể sử dụng hộp kiểm trong cửa sổ Solver chính để đảm bảo rằng tất cả các giá trị bạn không chỉ định ràng buộc đều không âm. Nếu bạn muốn các biến của mình trở thành số âm thì hãy bỏ chọn hộp này.

Ví dụ về Solver để giải bài toán tối ưu

Để bạn hiêu hơn về cách sử dụng Solver add-in, chúng ta sẽ  thực hiện bài toán đơn giản sau. Đây là dữ liệu mà chúng ta sử dụng:

 

Giả sử bạn có 5 công việc khác nhau với mức thù lao khác nhau, cùng với số giờ làm việc cho mỗi jobs. Chúng ta sẽ sử dụng Solver add-in để tìm ra cách tối đa hóa tổng số tiền trả trong khi vẫn giữ các biến nhất định trong một số ràng buộc như sau:

• Không có công việc nào làm ít hơn bốn tiếng.
• Jobs 2 phải nhiều hơn tám tiếng.
• Jobs 5 phải ít hơn mười một tiếng.
• Tổng số giờ làm việc phải bằng 40.

Bạn nên viết ra các ràng buộc trước khi sử dụng Solver.

Tiếp theo là thiết lập mọi thứ trong Solver:

 

Trước tiên, chúng ta cần tạo một bản sao của bảng để không ghi đè lên bản gốc và chứa thời gian làm việc hiện tại.

Thứ hai, hãy xem các giá trị trong các ràng buộc lớn hơn so với các ràng buộc là hoặc thấp hơn những gì tôi đã đề cập ở trên. Nguyên nhân là do không có tùy chọn lớn hơn hoặc nhỏ hơn. Chỉ có => (lớn hơn hoặc bằng) và <= (nhỏ hơn hoặc bằng).

Nhấp vào Solve và xem kết quả.

 

Như bạn có thể thấy ở bên trái của cửa sổ trên, tổng thu nhập đã tăng 130 đô la. Và tất cả các ràng buộc đều được đáp ứng.

Để giữ các giá trị mới, hãy chọn Keep Solver Solution và bấm nút OK.

Nếu bạn muốn biết thêm thông tin thì có thể chọn một báo cáo từ phía bên phải của cửa sổ. Chọn tất cả các báo cáo bạn muốn, cho Excel biết bạn muốn chúng được phác thảo và bấm nút OK.

Báo cáo được tạo trên các trang tính mới trong workbook và cung cấp cho bạn thông tin về quá trình Solver add-in đã thực hiện để tìm ra kết quả cho bạn.

Trong trường hợp của ví dụ này, các báo cáo không có nhiều thông tin thú vị. Nhưng nếu bạn chạy một phương trình Solver phức tạp hơn thì sẽ tìm thấy một số thông tin báo cáo hữu ích trong các trang tính mới này. Chỉ cần nhấp vào nút + ở bên cạnh bất kỳ báo cáo nào để biết thêm thông tin.

 

Các tùy chọn Solver nâng cao

Nếu bạn không biết nhiều về thống kê thì có thể bỏ qua các tùy chọn nâng cao của Solver và chỉ sử dụng nó như hiện tại. Nhưng nếu bạn đang chạy các phép tính lớn và phức tạp thì nên xem xét tùy chọn nâng cao như hình bên dưới.

 

Bạn có thể chọn giữa GRG Nonlinear, Simplex LP và Evolution. Excel cung cấp một giải thích đơn giản về thời điểm bạn nên sử dụng từng cái. 

Để điều chỉnh các cài đặt bổ sung, chỉ cần bấm nút Options. Bạn có thể cho Excel biết về tính tối ưu của số nguyên, đặt các giới hạn thời gian tính toán (hữu ích cho các bộ dữ liệu lớn) và điều chỉnh cách các phương pháp giải GRG và Evolution thực hiện các phép tính của chúng.

Tổng kết

Hy vọng qua bài viết này, bạn đã biết cách dùng Goal Seek và Solver trong Excel. Goal Seek giúp bạn tiết kiệm thời gian bằng cách tính toán nhanh hơn. Solver bổ sung một lượng lớn sức mạnh cho khả năng tính toán, đưa Excel lên tầm cao mới.

Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay nhé.

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ