Khi lập kế hoạch, bạn thường biết con số muốn đạt nhưng chưa biết đầu vào phải bằng bao nhiêu, ví dụ lợi nhuận muốn đạt 60 triệu thì doanh số cần bao nhiêu. Bài viết này hướng dẫn bạn dùng Goal Seek và Solver trong Excel để Excel tự tìm con số đó, thay vì bạn thử từng số bằng tay.
Để tìm doanh số cần đạt, bạn chọn ô lợi nhuận B6, vào Data > What-If Analysis > Goal Seek, gõ 60000000 vào ô To value, chọn ô doanh số B2 ở ô By changing cell rồi bấm OK. Excel đổi ô B2 thành 500,000,000, tức là doanh số cần đạt 500 triệu đồng. Khi bài toán có nhiều ô cần tìm và có điều kiện đi kèm, bạn dùng Solver. Bạn bật Solver ở File > Options > Add-ins, sau đó nút Solver hiện ở tab Data. Trong ví dụ kế hoạch sản xuất của bài, Solver tìm ra 740 áo sơ mi, 556 quần âu và 200 áo vest, cho lợi nhuận 158,440,000 đồng.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức, các ô màu xanh nhạt là ô Excel sẽ đổi giá trị. Mỗi bảng ví dụ nằm ở một sheet riêng và bắt đầu từ ô A1. Số trong bài viết theo kiểu Excel trong ảnh: dấu phẩy ngăn hàng nghìn, dấu chấm ngăn phần thập phân, ví dụ 1,500.5. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =PMT(B2/12;B3;-B4).
Goal Seek và Solver khác nhau ở điểm nào?
Goal Seek và Solver đều là công cụ của Excel, không phải hàm, nên bạn không gõ chúng vào ô như =SUM. Cả hai làm ngược với công thức thông thường. Công thức đi từ đầu vào ra kết quả, còn Goal Seek và Solver đi từ kết quả bạn muốn để tìm ngược lại đầu vào.
Điểm so sánh | Goal Seek | Solver |
|---|---|---|
Vị trí | Goal Seek có sẵn ở Data > What-If Analysis. | Solver là add-in đi kèm Excel, bạn phải bật một lần thì nút Solver mới hiện ở tab Data. |
Số ô Excel được đổi | Goal Seek chỉ đổi 1 ô. | Solver đổi được tới 200 ô. |
Mục tiêu | Goal Seek đưa ô công thức về đúng một giá trị. | Solver tìm giá trị lớn nhất, nhỏ nhất hoặc đúng một giá trị. |
Điều kiện kèm theo | Goal Seek không nhận điều kiện. | Solver nhận nhiều ràng buộc, ví dụ không vượt số giờ máy, số lượng phải là số nguyên. |
Khi nào nên dùng | Bạn dùng Goal Seek cho câu hỏi một ẩn: doanh số cần đạt, số tiền vay tối đa, giá bán hòa vốn. | Bạn dùng Solver cho bài toán phân bổ: sản xuất bao nhiêu mỗi loại, chia ngân sách, xếp ca. |
Bạn dùng Goal Seek tìm doanh số cần đạt như thế nào?
Bảng ví dụ là kế hoạch kinh doanh tháng 12. Ô B2 là doanh số dự kiến 400 triệu đồng, ô B3 là tỷ lệ giá vốn 62%, ô B4 là tỷ lệ hoa hồng bán hàng 2% và ô B5 là chi phí cố định 120 triệu đồng. Giám đốc muốn lợi nhuận tháng 12 đạt 60 triệu đồng.
Bước 1: Bạn lập công thức lợi nhuận
Goal Seek chỉ chạy khi ô kết quả là một công thức có dùng tới ô đầu vào. Bạn chọn ô B6, gõ công thức sau rồi nhấn Enter:
=B2*(1-B3-B4)-B5

Với doanh số 400 triệu đồng, lợi nhuận là 400 triệu nhân 36% rồi trừ 120 triệu, bằng 24,000,000 đồng. Con số này còn thiếu 36 triệu đồng so với mục tiêu.
Bước 2: Bạn mở Goal Seek
Bạn chọn ô B6, vào tab Data, bấm What-If Analysis trong nhóm Forecast rồi chọn Goal Seek.

Bước 3: Bạn điền 3 ô trong hộp thoại Goal Seek
Hộp thoại Goal Seek có 3 ô:
Ô | Bạn điền | Ý nghĩa |
|---|---|---|
Set cell |
| Ô này là ô công thức chứa kết quả bạn muốn đạt. Excel tự điền ô đang chọn. |
To value |
| Ô này là con số bạn muốn ô Set cell đạt được. Bạn gõ số, không gõ tham chiếu ô. |
By changing cell |
| Ô này là ô đầu vào Excel được phép đổi. Ô này phải chứa số, không chứa công thức. |

Bạn bấm OK. Hộp Goal Seek Status hiện dòng "Goal Seeking with Cell B6 found a solution", nghĩa là Excel đã tìm ra lời giải. Hộp này cho biết giá trị mục tiêu (Target value) và giá trị hiện tại (Current value) của ô B6.

Bước 4: Bạn đọc kết quả
Sau khi bạn bấm OK, ô B2 đổi thành 500,000,000 và ô B6 đổi thành 60,000,000. Vậy doanh số tháng 12 cần đạt 500 triệu đồng để lợi nhuận đạt 60 triệu đồng.

Goal Seek ghi đè số mới lên ô B2. Nếu bạn cần giữ số 400 triệu cũ để so sánh, bạn chép số cũ sang một ô khác trước khi chạy, hoặc bấm Cancel ở hộp Goal Seek Status.
Bạn tìm doanh số hòa vốn bằng Goal Seek như thế nào?
Doanh số hòa vốn là doanh số làm lợi nhuận bằng 0. Bạn chạy lại Goal Seek với Set cell là B6, To value là 0 và By changing cell là B2.

Ô B2 hiện 333,333,333 nhưng thanh công thức cho thấy số thật là 333333333.333333. Định dạng số của ô chỉ ẩn phần lẻ đi. Nếu ô đầu vào là số lượng sản phẩm hoặc số người, bạn làm tròn lên bằng hàm ROUNDUP để không thiếu so với mục tiêu. Bạn có thể đọc thêm bài hàm làm tròn trong Excel để chọn hàm làm tròn phù hợp.
Bạn dùng Goal Seek tìm số tiền vay tối đa như thế nào?
Bảng ví dụ thứ hai là khoản vay mua xe. Ô B2 là lãi suất năm 9%, ô B3 là số tháng trả 60 và ô B4 là số tiền vay 500,000,000 đồng. Ô B5 tính tiền trả mỗi tháng bằng công thức =PMT(B2/12,B3,-B4). Với số tiền vay 500 triệu đồng, bạn phải trả 10,379,178 đồng mỗi tháng. Bạn chỉ trả được 8 triệu đồng mỗi tháng, nên bạn cần biết số tiền vay tối đa.
Bạn chọn ô B5, mở Data > What-If Analysis > Goal Seek, rồi điền như sau:
Ô Set cell là
B5, ô tiền trả mỗi tháng.Ô To value là
8000000.Ô By changing cell là
B4, ô số tiền vay.


Kết quả là 385,386,988 đồng. Bạn có thể kiểm tra lại bằng công thức =PV(9%/12,60,-8000000), hàm PV cũng trả về 385,386,988. Nếu bạn muốn hiểu hàm PMT và lập lịch trả nợ từng tháng, bạn đọc bài hàm PMT trong Excel tính tiền trả góp hàng tháng.
Bạn cần lưu ý gì khi dùng Goal Seek?
Goal Seek chỉ đổi được 1 ô. Nếu bạn muốn Excel đổi cùng lúc giá bán và số lượng, bạn dùng Solver.
Ô By changing cell phải được công thức ở ô Set cell dùng tới, trực tiếp hoặc qua ô trung gian. Nếu không, Goal Seek vẫn đổi số trong ô đó nhưng ô kết quả không đổi theo, và hộp Goal Seek Status báo không tìm được lời giải.
Goal Seek tìm bằng cách thử dần, nên kết quả có thể lệch mục tiêu một phần rất nhỏ. Ở bảng vay, ô
B5hiện 8,000,000 nhưng giá trị thật là 8000000.000000002.Goal Seek không hợp với công thức bậc thang. Ví dụ, bạn gõ công thức thưởng
=IF(B2>=500000000,5000000,0)vào ôB8của bảng lợi nhuận, rồi chạy Goal Seek với Set cell làB8, To value là5000000và By changing cell làB2. Khi ôB2đang là 400,000,000, Goal Seek trả về 528,000,000 thay vì mốc 500,000,000. Nếu ôB2bắt đầu ở số khác, Goal Seek trả về một số khác, vì công cụ chỉ cần tìm một doanh số bất kỳ làm thưởng đạt 5 triệu. Với công thức bậc thang, bạn đọc mốc ngay trong công thức thay vì dùng Goal Seek.Goal Seek không lưu lại thiết lập. Mỗi lần dữ liệu đổi, bạn phải chạy lại Goal Seek.
Bạn bật Solver trong Excel như thế nào?
Solver là add-in miễn phí cài sẵn cùng Excel nhưng mặc định bị tắt. Nếu tab Data của bạn chưa có nút Solver, bạn bật theo các bước sau.
Bạn bật Solver trên Windows như thế nào?
Bạn bấm File > Options, rồi chọn mục Add-ins ở cột bên trái.
Ở ô Manage dưới cùng, bạn để Excel Add-ins rồi bấm Go.

Trong hộp Add-ins, bạn tích ô Solver Add-in rồi bấm OK.

Bạn mở tab Data. Nút Solver hiện ở nhóm Analyze bên phải.

Nếu Excel báo Solver chưa được cài, bạn bấm Yes để Excel cài thêm. Nếu danh sách không có Solver Add-in, bạn bấm Browse để tìm tệp add-in, theo hướng dẫn của Microsoft.
Bạn bật Solver trên Macbook như thế nào?
Theo hướng dẫn của Microsoft, trên Excel cho Mac bạn vào menu Tools > Excel Add-ins, tích ô Solver Add-In rồi bấm OK. Sau đó nút Solver hiện ở tab Data. Ảnh trong bài chụp trên Windows, nên tên nút trên máy Mac có thể hơi khác.
Bạn tắt Solver như thế nào?
Bạn mở lại hộp Add-ins như trên, bỏ tích ô Solver Add-in rồi bấm OK. Nút Solver biến mất khỏi tab Data. Các bài toán Solver đã lưu trong file vẫn còn, khi bật lại Solver bạn mở ra là thấy.
Bạn dùng Solver lập kế hoạch sản xuất như thế nào?
Một xưởng may làm 3 sản phẩm: áo sơ mi, quần âu và áo vest. Tháng này xưởng có 1,500 giờ may và 2,600 m vải. Xưởng đã nhận đơn tối thiểu 300 áo sơ mi và 200 quần âu, còn áo vest chỉ bán được tối đa 200 chiếc. Kế toán cần biết mỗi sản phẩm nên may bao nhiêu chiếc để tổng lợi nhuận lớn nhất.
Bạn nhập dữ liệu vào vùng A1:D7 đúng như bảng dưới. Mỗi sản phẩm nằm ở một cột: cột B là áo sơ mi, cột C là quần âu, cột D là áo vest.
A | B | C | D | |
|---|---|---|---|---|
1 | Sản phẩm | Áo sơ mi | Quần âu | Áo vest |
2 | Số lượng sản xuất | 300 | 200 | 100 |
3 | Lãi mỗi sản phẩm | 60,000 | 90,000 | 320,000 |
4 | Giờ may mỗi sản phẩm | 0.6 | 1.0 | 2.5 |
5 | Vải mỗi sản phẩm (m) | 1.8 | 1.2 | 3.0 |
6 | Số lượng tối thiểu | 300 | 200 | 0 |
7 | Số lượng tối đa | 1,000 | 800 | 200 |
Bên dưới, bạn lập bảng nguồn lực ở vùng A9:C11. Ô C10 là số giờ may có trong tháng, ô C11 là số mét vải có trong kho. Cột B để trống, bạn sẽ gõ công thức ở Bước 1. Ô A13 ghi "Tổng lợi nhuận", ô B13 cũng để trống.
A | B | C | |
|---|---|---|---|
9 | Nguồn lực | Đã dùng | Có trong tháng |
10 | Giờ may | (công thức) | 1,500.0 |
11 | Vải (m) | (công thức) | 2,600.0 |
Bài toán này có 3 ô cần tìm và nhiều điều kiện, nên Goal Seek không giải được. Bạn dùng Solver.
Bước 1: Bạn lập công thức tính giờ may, vải và lợi nhuận
Hàng 2 là số lượng sản xuất, đang điền tạm kế hoạch 300 áo sơ mi, 200 quần âu và 100 áo vest. Solver sẽ đổi 3 ô B2:D2 này. Bạn chọn ô B10, gõ công thức sau rồi nhấn Enter:
=SUMPRODUCT($B$2:$D$2,B4:D4)

Bạn kéo ô B10 xuống ô B11 để tính số mét vải đã dùng. Dấu $ giữ vùng số lượng $B$2:$D$2 cố định, còn B4:D4 đổi thành B5:D5 là vùng vải mỗi sản phẩm.
Tiếp theo, bạn chọn ô B13, gõ công thức sau rồi nhấn Enter:
=SUMPRODUCT(B2:D2,B3:D3)


Kế hoạch tạm cho lợi nhuận 68,000,000 đồng. Bạn có thể đọc thêm cách dùng hàm này ở bài hàm SUMPRODUCT trong Excel.
Bước 2: Bạn khai báo mục tiêu và ô cần đổi
Bạn chọn ô B13, vào tab Data rồi bấm Solver. Hộp Solver Parameters hiện ra, bạn điền phần trên như sau:
Ô Set Objective là
$B$13, ô tổng lợi nhuận.Ở dòng To, bạn chọn Max vì bạn muốn lợi nhuận lớn nhất.
Ô By Changing Variable Cells là
$B$2:$D$2, 3 ô số lượng sản xuất.
Bước 3: Bạn thêm các ràng buộc
Ràng buộc là điều kiện mà kết quả của Solver phải thỏa. Để thêm một ràng buộc, bạn bấm Add bên phải khung Subject to the Constraints. Hộp Add Constraint có 3 phần: Cell Reference là ô cần giữ điều kiện, ô giữa là dấu so sánh, Constraint là giới hạn.
Bạn thêm lần lượt 4 ràng buộc sau. Sau mỗi ràng buộc, bạn bấm Add để thêm tiếp, tới ràng buộc cuối thì bấm OK.
Ràng buộc | Cell Reference | Dấu | Constraint | Ý nghĩa |
|---|---|---|---|---|
1 |
|
|
| Số lượng mỗi sản phẩm không thấp hơn đơn đã nhận. |
2 |
|
|
| Số lượng mỗi sản phẩm không vượt số bán được. |
3 |
|
| integer | Số lượng phải là số nguyên, vì xưởng không may nửa cái áo. |
4 |
|
|
| Giờ may và vải dùng không vượt số xưởng có. |

Khi bạn chọn dấu int, Excel tự ghi chữ "integer" vào ô Constraint, bạn không phải gõ gì thêm.
Bước 4: Bạn chọn phương pháp giải rồi bấm Solve
Bạn để tích ô Make Unconstrained Variables Non-Negative để số lượng không bị âm. Ở ô Select a Solving Method, bạn chọn Simplex LP, vì lợi nhuận, giờ may và vải đều là phép nhân số lượng với một hằng số rồi cộng lại.

Bạn bấm Solve. Hộp Solver Results báo "Solver found a solution. All Constraints and optimality conditions are satisfied.", nghĩa là Solver đã tìm ra lời giải thỏa mọi ràng buộc. Phần giải thích bên dưới hộp cho biết khi dùng Simplex LP, lời giải này là lời giải tốt nhất của cả bài toán. Bạn để chọn Keep Solver Solution rồi bấm OK để giữ kết quả trên bảng.

Bước 5: Bạn đọc kết quả của Solver

Solver đề xuất may 740 áo sơ mi, 556 quần âu và 200 áo vest. Tổng lợi nhuận là 158,440,000 đồng, gấp hơn 2 lần kế hoạch tạm.
Kết quả | Giải thích |
|---|---|
Áo vest đạt mức tối đa 200 chiếc | Áo vest lãi nhiều nhất trên mỗi giờ may, 128,000 đồng mỗi giờ, nên Solver may hết số bán được. |
Giờ may dùng hết 1,500 giờ, vải dùng gần hết | Áo sơ mi lãi 100,000 đồng mỗi giờ may, cao hơn quần âu 90,000 đồng, nhưng áo sơ mi tốn 1.8 m vải còn quần âu chỉ tốn 1.2 m. Giờ may và vải cùng gần hết, nên Solver chia giữa áo sơ mi và quần âu sao cho dùng vừa đủ cả hai. Nếu may thêm áo sơ mi thay quần âu, xưởng sẽ thiếu vải. |
Vải dùng 2,599.2 m trên 2,600 m | Vải còn dư 0.8 m. Phần dư này không may thêm được chiếc nào vì giờ may đã hết. |
Nếu bỏ ràng buộc số nguyên, Solver trả về khoảng 740.74 áo sơ mi và 555.56 quần âu, lợi nhuận 158,444,444 đồng. Con số đó lớn hơn một chút nhưng không may được trong thực tế, nên bạn giữ ràng buộc int.
Solver lưu toàn bộ thiết lập vào sheet. Tháng sau, khi số giờ may hoặc số vải đổi, bạn sửa số ở cột C rồi mở Solver bấm Solve là có kế hoạch mới.
Bạn chọn phương pháp giải nào trong Solver?
Ô Select a Solving Method có 3 lựa chọn. Theo Microsoft, mỗi phương pháp hợp với một kiểu bài toán:
Phương pháp | Khi nào dùng |
|---|---|
Simplex LP | Bạn dùng Simplex LP khi mọi công thức chỉ cộng, trừ và nhân ô thay đổi với hằng số, ví dụ SUM, SUMPRODUCT. Bài kế hoạch sản xuất thuộc loại này. |
GRG Nonlinear | Bạn dùng GRG Nonlinear khi công thức là đường cong trơn, ví dụ có ô thay đổi nhân với chính nó hoặc nằm trong lũy thừa. |
Evolutionary | Bạn dùng Evolutionary khi công thức có IF, VLOOKUP, CHOOSE phụ thuộc vào ô thay đổi, làm kết quả nhảy bậc. |
Nếu bạn chọn Simplex LP cho công thức có IF, Solver có thể báo "The linearity conditions required by this LP Solver are not satisfied". Khi đó bạn đổi sang GRG Nonlinear hoặc Evolutionary.
Bạn hay gặp lỗi gì khi dùng Goal Seek và Solver trong Excel?
Thông báo hoặc hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|
Goal Seek báo "Cell must contain a formula." | Ô Set cell đang là số gõ tay, không phải công thức. | Bạn chọn đúng ô công thức kết quả, ví dụ ô lợi nhuận |
Goal Seek báo "Cell must contain a value." | Ô By changing cell đang chứa công thức. | Bạn chọn ô đầu vào là số gõ tay, ví dụ ô doanh số |
Goal Seek báo "may not have found a solution" | Mục tiêu không thể đạt được, hoặc ô By changing cell không được công thức dùng tới. | Bạn kiểm tra công thức có dùng ô đầu vào không, rồi bấm Cancel để trả ô về số cũ. |
Tab Data không có nút Solver | Add-in Solver chưa bật. | Bạn bật Solver ở File > Options > Add-ins như phần trên. |
Solver báo "Solver could not find a feasible solution." | Các ràng buộc mâu thuẫn nhau. Ví dụ, kho chỉ còn 700 m vải trong khi đơn tối thiểu đã cần 780 m. | Bạn rà lại từng ràng buộc và số giới hạn ở cột C, hàng 6 và hàng 7. |
Solver báo "The Objective Cell values do not converge." | Bài toán thiếu ràng buộc chặn trên, nên lợi nhuận tăng mãi được. | Bạn thêm ràng buộc về nguồn lực hoặc số lượng tối đa. |
Solver báo "The linearity conditions required by this LP Solver are not satisfied." | Bạn chọn Simplex LP cho công thức không tuyến tính, ví dụ có IF. | Bạn đổi phương pháp sang GRG Nonlinear hoặc Evolutionary. |

Khi gặp thông báo này, bảng đang giữ số thử cuối cùng của Solver. Bạn chọn Restore Original Values rồi bấm OK để bảng quay về số cũ, sau đó sửa ràng buộc hoặc số giới hạn.
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.