Mặc dù danh sách dài các hàm của Excel là một trong những tính năng hấp dẫn nhất của ứng dụng bảng tính của Microsoft, nhưng có một vài viên ngọc chưa được sử dụng đầy đủ để nâng cao các hàm này. Một công cụ thường bị bỏ qua là Phân tích Điều gì sẽ xảy ra.

Công cụ Phân tích Điều gì xảy ra^{(What-If Analysis)} của Excel được chia thành ba thành phần chính. Phần được thảo luận ở đây là tính năng Tìm kiếm Mục tiêu^{(Goal Seek)} mạnh mẽ cho phép bạn làm việc ngược lại từ một hàm và xác định các đầu vào cần thiết để có được đầu ra mong muốn từ một công thức trong một ô. Đọc tiếp để tìm hiểu cách sử dụng công cụ Tìm kiếm mục tiêu phân tích nếu xảy ra^{(What-If Analysis Goal Seek)} trong Excel .

Ví dụ về công cụ tìm kiếm mục tiêu của Excel

Giả sử bạn muốn vay thế chấp để mua nhà và bạn lo lắng không biết lãi suất của khoản vay sẽ ảnh hưởng như thế nào đến số tiền phải trả hàng năm. Số tiền thế chấp là 100.000 đô la và bạn sẽ trả lại khoản vay trong vòng 30 năm.

Sử dụng hàm PMT^(PMT) của Excel , bạn có thể dễ dàng tìm ra các khoản thanh toán hàng năm nếu lãi suất là 0%. Bảng tính có thể trông giống như sau:

Ô ở A2 thể hiện lãi suất hàng năm, ô ở B2 là thời hạn của khoản vay tính theo năm và ô ở C2 là số tiền của khoản vay cầm cố. Công thức trong D2 là:

=PMT(A2,B2,C2)

và đại diện cho các khoản thanh toán hàng năm của khoản thế chấp $ 100.000 trong 30 năm với lãi suất 0%. Lưu ý^(Notice) rằng con số trong D2 là số âm vì Excel giả định rằng các khoản thanh toán là một dòng tiền âm từ tình hình tài chính của bạn.

Thật không may, không có công ty cho vay thế chấp nào sẽ cho bạn vay 100.000 đô la với lãi suất 0%. Giả sử^(Suppose) bạn thực hiện một số hình dung và phát hiện ra rằng bạn có thể đủ khả năng trả lại 6.000 đô la mỗi năm cho khoản thanh toán thế chấp. Bây giờ bạn đang tự hỏi mức lãi suất cao nhất bạn có thể trả cho khoản vay là bao nhiêu để đảm bảo rằng bạn không phải trả nhiều hơn 6.000 đô la mỗi năm.

Nhiều người trong trường hợp này chỉ cần bắt đầu nhập các số trong ô A2 cho đến khi con số trong D2 đạt xấp xỉ 6.000 đô la. Tuy nhiên, bạn có thể làm cho Excel thực hiện công việc cho bạn bằng cách sử dụng công cụ Tìm kiếm mục tiêu phân tích điều gì sẽ xảy ra. ^{(Analysis Goal Seek)}Về cơ bản, bạn sẽ làm cho Excel hoạt động ngược lại từ kết quả trong D4 cho đến khi nó đạt mức lãi suất thỏa mãn khoản thanh toán tối đa của bạn là 6.000 đô la.

Bắt đầu bằng cách nhấp vào tab Dữ liệu trên ^(Data)Ruy-băng^(Ribbon) và tìm nút Phân tích Điều gì xảy ra trong phần ^{(What-If Analysis)}Công cụ Dữ liệu^{(Data Tools)} . Nhấp vào nút Phân tích điều gì xảy ra^{(What-If Analysis)} và chọn Tìm kiếm mục tiêu^{(Goal Seek)} từ menu.

Excel mở ra một cửa sổ nhỏ và yêu cầu bạn chỉ nhập ba biến. Biến Set Cell phải là một ô có chứa công thức. Trong ví dụ của chúng tôi ở đây, nó là D2 . Biến To Value là số tiền bạn muốn ô ở D2 nằm ở cuối phân tích.

Đối với chúng tôi, nó là -6,000 . Hãy nhớ rằng Excel coi các khoản thanh toán là một dòng tiền âm. Biến By Change Cell^{(By Changing Cell)} là lãi suất bạn muốn Excel tìm cho bạn để khoản thế chấp 100.000 đô la sẽ khiến bạn chỉ mất 6.000 đô la mỗi năm. Vì vậy, hãy sử dụng ô A2 .

Nhấp vào nút OK và bạn có thể nhận thấy rằng Excel sẽ nhấp nháy một loạt các số trong các ô tương ứng cho đến khi các lần lặp cuối cùng hội tụ về một số cuối cùng. Trong trường hợp của chúng tôi, ô ở A2 bây giờ sẽ đọc khoảng 4,31%.

Phân tích này cho chúng ta biết rằng để không chi tiêu hơn 6.000 đô la mỗi năm cho khoản thế chấp 100.000 đô la trong 30 năm, bạn cần đảm bảo khoản vay không quá 4,31%. Nếu bạn muốn tiếp tục thực hiện phân tích điều gì xảy ra, bạn có thể thử các kết hợp số và biến số khác nhau để khám phá các lựa chọn bạn có khi cố gắng đảm bảo một mức lãi suất tốt cho một khoản thế chấp.

Công cụ Tìm kiếm Mục tiêu Phân tích Điều gì xảy ra^{(What-If Analysis Goal Seek)} trong Excel là một bổ sung mạnh mẽ cho các hàm và công thức khác nhau được tìm thấy trong bảng tính điển hình. Bằng cách làm ngược lại kết quả của một công thức trong một ô, bạn có thể khám phá các biến khác nhau trong tính toán của mình một cách rõ ràng hơn.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long liѕt of functionѕ is one of the mоst enticing features оf Microѕoft’s spreadsheet application, there a few υnderutilized gems that enhance these functions. One often-overlooked tool iѕ the Whаt-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Cách sử dụng Phân tích Điều gì xảy ra trong Excel

• Cách sử dụng Tính năng Nói ô trong Excel

• 2 cách sử dụng hàm Transpose của Excel

• Làm thế nào để hiểu phân tích điều gì xảy ra trong Microsoft Excel

• Cách xóa dòng trống trong Excel

• Cách sửa lỗi # N / A trong công thức Excel như VLOOKUP

• Cách tùy chỉnh chức năng dán của Word

• Cách loại bỏ các hàng trùng lặp trong Excel

• Cách theo dõi người phụ thuộc trong Excel

• Sắp xếp dữ liệu một cột và nhiều cột cơ bản trong bảng tính Excel

• Cách sắp xếp theo ngày trong Excel

• Sử dụng tên dải động trong Excel để thả xuống linh hoạt

• Cách tạo nhiều danh sách thả xuống được liên kết trong Excel

• Kết nối Excel với MySQL

• 3 cách để tách một ô trong Excel

• 4 cách chuyển đổi Excel sang Google Trang tính

• Sử dụng bàn phím để thay đổi chiều cao hàng và chiều rộng cột trong Excel

• Căn giữa dữ liệu trang tính của bạn trong Excel để in

• Cách hợp nhất dữ liệu trong nhiều tệp Excel

• 4 cách sử dụng dấu kiểm trong Excel