Xu Hướng 9/2023 # Sử Dụng Goal Seek Và Solver Trong Excel Để Giải Quyết Các Biến Không Xác Định # Top 9 Xem Nhiều

Bạn đang xem bài viết Sử Dụng Goal Seek Và Solver Trong Excel Để Giải Quyết Các Biến Không Xác Định được cập nhật mới nhất tháng 9 năm 2023 trên website Utly.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

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 sử 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.

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

Goal Seek đã được tích hợp sẵn trong Excel, diú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ả.

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 ô 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.

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.

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.

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ó.

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.

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ử.

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

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

Để 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:

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.

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.

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.

Hy vọng qua bài viết này, bạn đã biết cách sử dụng Goal Seek và Solve 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.

Sử Dụng Goal Seek Trong Excel Để Tìm Mục Tiêu

Bài toán 1: Sử dụng Goal Seek để tìm kiếm mục tiêu đạt điểm vượt qua lớp.

Giả sử bạn có bảng điểm như hình bên dưới:

Điểm trung bình hiện có là 5.1 và bạn cần ít nhất 5.5 để đạt điểm vượt qua lớp. Điều này sẽ phụ thuộc vào điểm của môn Vật lý, vậy bạn cần đạt điểm môn Vật lý tối thiểu là bao nhiêu để vượt qua lớp, Goal Seek sẽ giúp bạn giải quyết vấn đề này.

2. Hộp thoại Goal Seek xuất hiện: Trong đó:

Set cell: Ô chứa công thức tính toán của bài toán, ví dụ công thức tính điểm vượt qua lớp trong ô C11.

To value: nhập giá trị kết quả mong muốn, ví dụ điểm để vượt qua lớp là 5.5.

By changing cell: Đây là ô nơi Goal Seek sẽ đưa ra giá trị cần để đạt được kết quả vừa nhập vào ô To value, ví dụ điểm môn Vật lý cần đạt được trong ô C10.

3. Khi bạn đã hoàn tất, nhấp OK. 4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, môn Vật Lý cần đạt được 8.5 điểm để vượt qua lớp. Bài toán 2: Sử dụng Goal Seek để tìm thời hạn vay cụ thể với khoản trả góp hàng tháng là 3,500,000 đồng.

Giả sử bạn định vay một khoản 500,000,000 đồng để mua nhà, trong thời hạn 120 tháng, với lãi suất 7.5%/năm. Sử dụng công thức =PMT(Lãi suất vay/12, số tháng trả góp, số tiền vay), bạn tính được số tiền trả góp hàng tháng là -5,935,088.46 đồng (số âm thể hiện khoản phải trả).

Tuy nhiên, bạn chỉ có khả năng trả góp 3,500,000 đồng/tháng, nên cần phải vay trong thời hạn dài hơn. Bạn có thể sử dụng Goal Seek để tìm ra số tháng vay cụ thể với khoản trả góp hàng tháng là 3,500,000 đồng.

2. Hộp thoại Goal Seek xuất hiện: Trong đó:

Set cell: Đây là ô chứa công thức tính toán của bài toán, ví dụ công thức tính số tiền trả góp hàng tháng trong ô B6.

To value: Đây là khoản trả góp hàng tháng mà bạn có thể chi trả được, ví dụ khoản trả góp ở đây là 3,500,000 đồng/tháng.

By changing cell: Đây là ô nơi Goal Seek sẽ đưa ra được số tháng vay cụ thể với khoản trả góp hàng tháng vừa được nhập vào ô To value, trong ví dụ này là ô B3.

3. Khi bạn đã hoàn tất, nhấp OK. 4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng. Chú ý:

Trong bài toán 2, giá trị nhập vào ô To value là -3,500,000 (số âm thể hiện khoản phải trả), nếu bạn nhập số dương thì Goal Seek sẽ thông báo không tìm được dữ liệu theo yêu cầu.

Trong quá trình phân tích mô hình dữ liệu, Goal Seek sẽ thực hiện số lần lặp tối đa là 100 để tìm giá trị yêu cầu. Nếu Goal Seek không tìm được giá trị cuối cùng, có nghĩa là mô hình dữ liệu của bạn không hoạt động. Hãy điều chỉnh lại mô hình để đạt được kết quả như ý muốn.

Bạn có thể sử dụng Goal Seek để giải quyết nhiều bài toán thực tế khác nữa, chỉ cần bạn đưa ra được kết quả mong muốn và Goal Seek sẽ giúp bạn tìm ra các mục tiêu.

Hướng Dẫn Sử Dụng Goal Seek Trong Excel

Nếu như chúng ta biết kết quả mà ta muốn có được từ một công thức, vậy thì chỉ cần sử dụng Goal Seek trong Excel để tìm ra được giá trị đầu vào của công thức để cho ra kết quả mà ta muốn tính.

Ví dụ trong thực tế, chúng ta có giá bán đôi giày là 200 nghìn đồng thì thu nhập là 1.639.890.000đ. Muốn thu nhập là 2 tỷ thì giá bán phải là bao nhiêu. Hoặc nếu không muốn tăng giá bán thì ta phải tăng số lượng đôi giày thì số giày bán ra phải là bao nhiêu. Bằng việc sử dụng Goal Seek trên Excel chúng ta sẽ dễ dàng giải được bài toán này. (Kết quả được tính ra cho tình huống này là giá bán phải tăng thành 214 nghìn đồng hoặc phải tăng số lượng bán ra đạt 33,232 đôi để đạt được doanh thu 2 tỷ đồng).

Trong bài này chúng tôi sẽ hướng dẫn bạn cách sử dụng Goal Seek trong Excel bằng minh họa chi tiết.

Ví dụ 1: Sử dụng Goal Seek để tìm ra điểm số cần đạt

Sử dụng Goal Seek trong Excel để tìm ra điểm số cần đạt được trong bài kiểm tra thứ 4 để có được điểm trung bình cuối kỳ là 70.

Bước 1: Nhập công tính trung bình điểm cuối kỳ.

Chọn ô trống cần trả kết quả là điểm trung bình cuối kỳ (trong ví dụ minh họa này là ô B7), nhập công thức tính trung bình sau và nhấn phím Enter để lấy kết quả.

Bước 2: Điểm của bài thi thứ 4 là ô dữ liệu đầu vào (trong minh họa này của chúng ta là ô B5).

Bước 3: Trên tab Data, trong nhóm Forecast, nhấn chuột chọn What-If Analysis.

Bước 4: Nhấn chuột chọn Goal Seek.

Lúc này hộp thoại Goal Seek sẽ xuất hiện.

Bước 5: Nhấn chọn ô B7.

Bước 6: Nhấn vào hộp To value và gõ số 70.

Bước 7: Nhấn vào hộp By changing cell và nhấn chọn ô B5.

Bước 8: Nhấn chọn nút OK.

Lúc này chúng ta đã thu được kết quả cần tìm. Với bài kiểm tra thứ tư đạt 90 điểm thì điểm trung bình cuối kỳ ta đạt được là 70.

Ví dụ 2: Sử dụng Goal Seek để tính ra số tiền cho vay

Sử dụng Goal Seek trên Exel để tính ra số tiền cho vay để mỗi tháng được trả 1500 đô la.

Bước 1. Công thức ở trong ô B5 dùng để tính số tiền trả hàng tháng.

=PMT(B1/12,B2*12,B3)

Chú thích: Hàm PMT dùng để tính khoản thanh toán cho một khoản cho vay. Số tiền vay càng cao thì số tiền phải trả hàng tháng càng cao. Nếu như mỗi tháng chỉ có thể chi trả 1500 đô la thì có thể vay tối đa là bao nhiêu tiền.

Bước 2. Khoản tiền cho vay được để trong ô B3, đây cũng là ô dữ liệu đầu vào.

Bước 3. Trên tab Data, tìm nhóm Forecasst, nhấn chọn What-If Analysis.

Bước 4. Nhấn chọn Goal Seek.

Bước 5. Nhấn chọn ô B5.

Bước 6. Nhấn vào hộp To value và gõ -1500 (dấu trừ ở đây có nghĩa là ta đang trả tiền).

Bước 7. Nhấn vào hộp By changing cell và chọn ô B3.

Bước 8. Nhấn chọn OK.

Tính chính xác của Goal Seek

Sử dụng Goal Seek trả về cho chúng ta đáp án gần đúng. Bạn hoàn toàn có thể thay đổi thiết lập lặp lại trong Excel để thu được đáp án chính xác hơn.

Bước 2. Sử dụng Goal Seek để tìm ra giá trị đầu vào để có được kết quả bình phương là 25.

Tới đây chúng ta được trả kết quả là giá trị gần đúng.

Bước 3. Trên tab File, nhấn chọn Options, chọn Formulas.

Bước 4. Dưới mục Calculation options, ta giảm giá trị của Maximum Change bằng cách thêm vào vài số 0. So với giá trị mặc định vốn có là 0,001.

Bước 5. Nhấn chọn OK.

Bước 6. Sử dụng Goal Seek lần nữa. Excel sẽ trả cho chúng ta đáp án chính xác.

Có thể bạn chưa biết

Có khá nhiều vấn đề mà Goal Seek không thể giải quyết được. Ví dụ như Goal Seek cho phép một dữ liệu đầu vào là ô đơn lẻ và một ô dữ liệu đầu ra cũng là ô đơn lẻ. Do đó nếu ta có dữ liệu đầu vào là nhiều ô thì phải giải quyết như thế nào.

Trong tình huống này ta có thể sử dụng Solver trong Excel để giải quyết vấn đề nếu chúng ta có nhiều giá trị đầu vào. Hoặc thỉnh thoảng ta cũng cần nhập giá trị đầu vào khác để tìm ra đáp án.

Bước 2. Sử dụng Goal Seek để tìm ra giá trị đầu vào để trả kết quả từ công thức là +0.25.

Tới đây ta thu được kết quả là Excel không thể tìm ra đáp án.

Bước 3. Nhấn Cancel.

Bước 4. Bắt đầu với giá trị đầu vào lớn hơn 8.

Bước 5. Sử dụng Goal Seek một lần nữa và Excel sẽ trả cho chúng ta đáp án.

Có thể bạn sẽ cần: Học Excel cấp tốc

Dùng Goal Seek Để Tìm Mục Tiêu Trong Excel

Bài toán 1: Sử dụng Goal Seek để tìm kiếm mục tiêu đạt điểm vượt qua lớp.

Giả sử bạn có bảng điểm như hình bên dưới:

Set cell: Ô chứa công thức tính toán của bài toán, ví dụ công thức tính điểm vượt qua lớp trong ô C11.

To value: nhập giá trị kết quả mong muốn, ví dụ điểm để vượt qua lớp là 5.5.

3. Khi bạn đã hoàn tất, nhấp OK.

4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, môn Vật Lý cần đạt được 8.5 điểm để vượt qua lớp.

Set cell: Đây là ô chứa công thức tính toán của bài toán, ví dụ công thức tính số tiền trả góp hàng tháng trong ô B6.

To value: Đây là khoản trả góp hàng tháng mà bạn có thể chi trả được, ví dụ khoản trả góp ở đây là 3,500,000 đồng/tháng.

3. Khi bạn đã hoàn tất, nhấp OK.

4. Goal Seek sẽ thực hiện tính toán trên mô hình dữ liệu và thông báo kết quả phân tích. Trong ví dụ này, với khả năng trả góp 3,500,000 đồng/tháng, bạn có thể chọn thời hạn vay là 358 tháng.

Hướng Dẫn Cách Sử Dụng Goal Seek Trên Excel

+ hàm goal seek trong excel 2013

+ bài tập về hàm goal seek

+ bài tập sử dụng goal seek

+ giải phương trình bằng goal seek

+ goal seek trong excel 2023

Trong ví dụ của chúng tôi, phiếu bầu CÓ là đa số, nhưng nhút nhát sự chấp thuận cần thiết 2/3 để thắng cuộc bầu cử. Mọi người nhanh chóng nhận ra họ đã gần gũi, nhưng mục nào họ thay đổi để tìm ra cách gần gũi. Điều gì sẽ tạo nên sự khác biệt?

Sử dụng Goal Seek, chúng tôi có thể thay đổi giá trị của các ô khác nhau để xem kết quả thay đổi như thế nào. Điều này sẽ cho phép bạn trả lời các loại câu hỏi này.

Có bao nhiêu cử tri “KHÔNG” cần được chuyển đổi thành CÓ để thắng cuộc bầu cử?

Cần thêm bao nhiêu phiếu do nhóm CÓ để giành chiến thắng trong cuộc bầu cử?

Nếu có thêm 500 người bình chọn thì đội YES có thắng không?

Tạo bảng tính trong Excel có dữ liệu của bạn.

Nhấp vào ô bạn muốn thay đổi. Điều này được gọi là ” Đặt ô “. Trong ví dụ của tôi, đây sẽ là D4.

Từ dữ liệu tab, chọn gì nếu phân tích … nút

Chọn Goal Seek .. từ trình đơn thả xuống

Trong hộp thoại Tìm kiếm mục tiêu, hãy nhập số tiền “what if” mới vào hộp văn bản To value . (Nhớ thêm dấu phần trăm nếu bạn có.)

Trong ví dụ này, chúng tôi yêu cầu Excel thay thế nội dung của ô D4 là 63,90% với 66,67%. Đây là phần trăm cần thiết để thắng cuộc bầu cử.

bài tập sử dụng goal seek

Tôi có thể nghe thấy một số độc giả hét lên “chờ đã…. không chấp nhận giá trị “. Trên thực tế, mọi người có thể la hét vì hai lý do.

Nếu bạn nhìn kỹ vào hộp Trạng thái goal seek trong excel, bạn sẽ thấy giá trị Mục tiêu: lần đọc: 0,6667 đến từ 66,67% của chúng tôi. Nhưng giá trị hiện tại: hiển thị là 66,64%. Excel được làm tròn xuống và giá trị YES đã sửa đổi sẽ không thắng cuộc bầu cử từ 66,64% nhỏ hơn 66,67%.

Một nhóm khác có thể hỏi tại sao Excel không chỉ mất 66,67% trong tổng số TOTAL phiếu bầu là 7008. Và điều đó dẫn chúng ta đến một yêu cầu của ý nghĩa hàm goal seek – công thức. Ô Đặt ở Bước 2 phải chứa công thức . Trong ví dụ của chúng tôi, 63,90% ban đầu là công thức sử dụng giá trị YES (C4) chia cho TOTAL giá trị (C6). Vì vậy, chúng tôi đã đáp ứng yêu cầu đó.

hàm goal seek trong excel đặt ô tìm kiếm cần một công thức

Nếu tôi chỉ cần gõ giá trị 63,90% và cố gắng sử dụng mục tiêu hàm goal seek trong excel, tôi sẽ thấy một lỗi Excel nêu rõ ” ô phải chứa một công thức “.

Cũng như tôi đã sử dụng công thức trong ô đã đặt của mình, tôi cũng đã sử dụng công thức cho TOTAL trong B6. Đây là tổng số phiếu bầu YES và NO của tôi. Vì ô TOTAL này là công thức, tổng số TOTAL của tôi sẽ tự động được điều chỉnh khi goal seek trong excel thay đổi giá trị ô YES. Nếu tôi gõ “7008” trong ô C6, chỉ giá trị ô YES mới thay đổi.

Yêu cầu khác là ô bạn thay đổi trong Bước 6 không thể chứa công thức . Nó phải là một giá trị đã nhập.

Như ví dụ này cho thấy, goal seek excel là một công cụ tốt đẹp có thể nhanh chóng tìm thấy câu trả lời cho các tình huống khác nhau. Mặc dù đây là một ví dụ đơn giản, bạn có thể sử dụng cùng một công cụ cho các bảng tính phức tạp có chứa nhiều biến và công thức. Chỉ cần nhớ, bạn có thể không phải lúc nào cũng thích kết quả.

Tags:

Giangblog: Toàn Tập Về Hàm Goal Seek Trong Excel

Goal seek (Hàm mục tiêu) thường áp dụng trong các bài toán như tính doanh thu hòa vốn, thay đổi một chỉ tiêu chi phí nào đó để có được lợi nhuận như mong muốn, tính tổng chi phí cho tổng chi phí ròng phải trả…

Goal seek để giải các phương trình một ẩn; ngoài hàm Goal Seek còn có 1 công cụ khác là Solver – Solver để giải hệ phương trình và các bài toán tối ưu (Sẽ giải thích ở bài khác)

Khi bạn thiết lập một bảng tính để sử dụng Goal Seek, bạn thường có một công thức trong một ô, và các biến cho công thức này (với một giá trị ban đầu) trong những ô khác. Công thức có thể có nhiều biến, nhưng Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến mà thôi.

Goal Seek hoạt động bằng cách sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek sẽ thử giá trị ban đầu của biến để xem nó có tạo ra kết quả mà bạn muốn hay không. Nếu không, Goal Seek sẽ thử tiếp với những giá trị khác nhau, cho đến khi nó tìm ra lời giải, hay nói cách khác, cho đến khi nào kết quả mà nó tìm được gần giống với kết quả của bạn muốn nhất.

Trước khi chạy Goal Seek, bạn cần thiết lập bảng tính theo một mẫu nào đó, mà cụ thể là nên thực hiện 3 điều sau đây:

* Thiết lập một ô làm ô thay đổi (changing cell). Đây là giá trị mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng đạt được kết quả gần đúng nhất. Rồi nhập một giá trị ban đầu trong ô này, cho nó bằng 0 chẳng hạn.

* Thiết lập các giá trị nhập liệu khác cho công thức và đặt cho chúng những giá trị thích hợp.

* Tạo một công thức để Goal Seek sử dụng trong quá trình cố gắng đạt được mục tiêu.

VD1: Có số liệu của dự án đầu tư khai thác

Sử dụng Goal Seek để tính lãi suất vay vốn sao cho có thể hoàn vốn trong 10 năm

Bạn đang ước mơ sắm được một thiết bị có trị giá $50,000, và muốn là trong vòng 5 năm tính từ hôm nay, ước mơ sẽ thành hiện thực. Giả sử rằng, tiền gửi ngân hàng có lãi suất là 5% một năm, vậy bạn cần phải gửi vào ngân hàng mỗi năm tối thiểu là bao nhiêu để đạt được ước mơ của mình? Hình 15.9 minh họa một bảng tính đã được thiết lập để sử dụng Goal Seek cho ước mơ này:

Trước hết nhập mô hình bài toán, khai báo biến cần tìm, trong ví dụ này là lãi xuất vay vốn tại ô B15

Lên Tool, gọi Goal Seek, xuất hiện hộp hội thoại Goal Seek, điền các thông số: Set cell: ô chứa hàm mục tiêu To value: hàm mục tiêu bằng By changing cell: ô chứa biến

VD2:

Hình 15.9

Trong hình trên:

Ô C6 là ô thay đổi (changing cell): số tiền tối thiểu phải gửi vào ngân hàng mỗi năm (với giá trị ban đầu là không có đồng nào cả).

Các ô C4 và C5 được sử dụng làm các hằng cho hàm FV() ở ô C8.

Ô C8 chứa hàm FV(), là một hàm chuyên dùng để tính một giá trị tương lai cho một khoản đầu tư. Và bạn muốn rằng kết quả ở đây sẽ là $50,000.

1) Chọn Data, What-If Analysis, Goal Seek. Excel hiển thị hộp thoại Goal Seek.

2) Nhập tham chiếu đến ô chứa công thức trong hộp Set Cell. Trong trường hợp này, là $C$8.

3) Nhập giá trị mà bạn muốn sẽ là kết quả của công thức trong hộp To Value. Trong trường hợp này, bạn nhập vào 50000.

4) Nhập theo chiếu đến ô thay đổi, hay nói cách khác, để đạt được giá trị ở To Value cho công thức ở Set Cell, thì thay đổi giá trị ở đây: By changing cell (là ô C6 trong hình 15.9).

Hình 15.10 minh họa bảng tính cùng với hộp thoại Goal Seek đã điền đầy đủ:

Hình 15.10

Có nhiều doanh nghiệp sử dụng lợi nhuận từ sản phẩm như là thước đo cho tình hình tài chính của mình. Một mức lợi nhuận mạnh, có nghĩa là các chi phí đang được kiểm soát tốt, và cho thấy thị trường hài lòng với giá cả của sản phẩm. Dĩ nhiên, lợi nhuận còn phụ thuộc vào nhiều yếu tố khác nữa, và bạn có thể sử dụng Goal Seek để tìm ra mức lợi nhuận tối ưu dựa vào một trong những yếu tố này.

Ví dụ, bạn muốn đưa ra một dòng sản phẩm mới, và bạn muốn thu được 30% lợi nhuận từ nó trong năm đầu tiên. Và giả sử rằng bạn có những giả định sau đây: Từ những thông tin trên, chúng ta sẽ tìm ra mức giá bán hợp lý nhất (Price per Unit) cho sản phẩm để kiếm được 30% lợi nhuận (Margin).

Hình 15.12 minh họa một bảng tính được thiết lập để giải bài toán này. Một giá trị ban đầu là $1.00 được nhập ở ô Price Per Unit (giá bán cho mỗi sản phẩm, ô C4). Với mức giá này, nếu bán hết 100,000 sản phẩm, và sau khi chiết khấu 40% cho đại lý, chúng ta sẽ có doanh thu là $60,000. Chi phí cho 100,000 sản phẩm này cộng với chi phí cố định sẽ là $2,013,000. Như vậy, nếu như bán với giá $1.00 một sản phẩm, chúng ta sẽ lỗ $1,953,000, hay tương đương với -3255% (!)

Để tìm ra giá bán cho sản phẩm (giá trị ở C4) mà kiếm được 30% lợi nhuận (ở C14), tôi thiết lập các tham số trong hộp thoại Goal Seek như sau:

Trong năm đầu tiên này sẽ bán được 100,000 sản phẩm (Unit Sold).

Mức chiết khấu trung bình (Average Discount) cho các đại lý là 40%

Tổng chi phí cố định (Fixed Costs) là $750,000, và chi phí riêng cho mỗi sản phẩm (Costs per Unit) là $12.63

Sau khi chạy Goal Seek, nó đưa ra một giá bán đề nghị là $47.87 để đạt được mức lợi nhuận gần bằng mức lợi nhuận chúng ta muốn (29,92%), như minh họa ở hình 15.13:

Ghi chú về những giá trị xấp xỉ của Goal Seek

Ở hình 15.13 của bài trên, bạn thấy rằng Goal Seek đưa ra một con số xấp xỉ: 29.92%, chứ không phải là 30% như chúng ta mong muốn. Giá trị đó khá gần (lệch chỉ 0.008) nhưng không chính xác. Tại sao Goal Seek không tìm ra lời giải chính xác?

Câu trả lời nằm ở một trong những tùy chọn mà Excel sử dụng để điều khiển các phép tính lặp đi lặp lại (iterative calculations). Đôi khi, việc lặp đi lặp lại có thể phải mất một thời gian cực kỳ dài để tìm ra được lời giải chính xác, do đó, Excel đã hòa hợp bằng việc xác lập những giới hạn nhất định trong quá trình lặp lại.

Để thấy những giới hạn này, bạn mở Excel Options ra rồi nhấn vào ngăn Formulas (xem hình 15.14). Trong đó có hai tùy chọn xác lập cho việc lặp lại:

Maximum Iterations – Giá trị trong text box này quy định số lần lặp lại tối đa. Trong Goal Seek, chính là số giá trị tối đa mà Excel đưa vào ô thay đổi (changing cell) để thử.

Maximum Change – Giá trị trong text box này là giới hạn mà Excel sử dụng để quyết định xem nó có hội tụ đến một lời giải hay không. Nếu hiệu số giữa lời giải hiện hành và mục tiêu muốn đạt được nhỏ hơn hoặc bằng giá trị này, Excel sẽ ngừng lại.

Hình 15.12

Chính giá trị Maximum Change = 0.001 (mặc định) đã ngăn Excel đưa ra lời giải chính xác cho phép tính lợi nhuận ở bài trước. Trong một lần lặp lại nào đó, Goal Seek đã tìm ra đáp số là 0.2992 (tương đương 29.92%), nhưng bởi vì 0.008 (chênh lệch giữa 0.3 và 0.2992) thì nhỏ hơn giá trị mặc định 0.001 trong text box Maximum Change, nên Excel đã ngưng quá trịnh lặp lại và đưa ra lời giải.

Để đạt được lời giải chính xác = 30%, hay 0.3, bạn chỉ cần sửa lại con số trong Maximum Change là 0.0001.

Bạn được yêu cầu tìm số sản phẩm tối thiểu phải bán được để làm cho lợi nhuận bằng 0 (nghĩa là tổng chi phí cho sản phẩm bằng doanh thu từ sản phẩm), hay nói cách khác, phải bán được ít nhất là bao nhiêu sản phẩm thì mới hòa vốn? Thiết lập một phương trình lợi nhuận có kết quả là 0 bằng cách thay đổi số sản phẩm sẽ bán ra, là một trong những thế mạnh của Goal Seek.

Để thử làm điều đó, chúng ta quay lại với bài toán “Tối ưu lợi nhuận sản phẩm”. Nhưng trong trường hợp này, tôi sẽ giả sử rằng giá bán của sản phẩm đã được xác định là $47.95 (lời giải của bài toán trên). Để tìm ra số sản phẩm tối thiểu phải bán được để có lợi nhuận bằng 0, tôi thiết lập các tham số cho Goal Seek như sau (xem hình 15.15):

Tham chiếu cho Set Cell là C13, là ô tính lợi nhuận theo số tiền.

Giá trị cho To Value là 0 (tức là mục tiêu của lợi nhuận, trong trường hợp này)

Tham chiếu cho By Changing Cell là C5 (số sản phẩm được bán)

Hình 15.15

Và lời giải cho chúng ta được Goal Seek tìm ra như minh họa ở hình 15.16: Phải bán được ít nhất là 46,468 sản phẩm, thì chúng ta mới hòa vốn.

Hình 15.16

VD 5: Giải phương trình đại số

Hình 15.17

Hình 15.18 minh họa kết quả, với giá trị tại ô A2 là lời giải cho nghiệm x của phương trình. Bạn chú ý, kết quả của phương trình (ô B2) không chính xác bằng 1. Như đã nói ở bài 15.2.4, nếu bạn cần kết quả chính xác hơn, bạn phải thay đổi giới hạn hội tụ (Maximum Change) của Excel. Ví dụ, bạn có thể thiết lập cho Maximum Change là 0.000001.

Hình 15.18

Cập nhật thông tin chi tiết về Sử Dụng Goal Seek Và Solver Trong Excel Để Giải Quyết Các Biến Không Xác Định trên website Utly.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!