Mặc dù danh sách dài các chức năng 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 đá quý được sử dụng không đúng cách để tăng cường các chức năng này. Một công cụ thường bị bỏ qua là Phân tích What-If.
Công cụ Phân tích What-If 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 mạnh mẽ cho phép bạn làm việc ngược từ một chức năng 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 What-If của Excel.
Ví dụ về công cụ tìm kiếm mục tiêu của Excel
Giả sử rằng bạn muốn vay một khoản vay thế chấp để mua một ngôi nhà và bạn lo ngại về mức lãi suất của khoản vay sẽ ảnh hưởng đến các khoản thanh toán 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 suốt 30 năm.
Sử dụng chức năng 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 sẽ là bao nhiêu nếu lãi suất là 0%. Bảng tính có thể sẽ trông giống như thế này:
Ô ở A2 đại diện cho lãi suất hàng năm, ô ở B2 là độ dài của khoản vay tính theo năm và ô ở C2 là số tiền của khoản vay thế chấp. 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 30 năm, $ 100.000 với lãi suất 0%. Lưu ý rằng con số trong D2 là âm do Excel giả định rằng các khoản thanh toán là một dòng tiền âm từ vị trí tài chính của bạn.
Thật không may, không có người cho vay thế chấp sẽ cho bạn vay 100.000 đô la với lãi suất 0%. Giả sử 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 trong các 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ể nhận cho khoản vay là gì để đảm bảo cuối cùng bạn không phải trả hơn 6.000 đô la mỗi năm.
Nhiều người trong tình huống này chỉ đơn giản là bắt đầu nhập số trong ô A2 cho đến khi con số trong D2 đạt xấp xỉ 6.000 đô la. Tuy nhiên, bạn có thể khiến Excel thực hiện công việc cho mình bằng cách sử dụng công cụ Tìm kiếm mục tiêu phân tích What-If. Về cơ bản, bạn sẽ làm cho Excel hoạt động ngược từ kết quả trong D4 cho đến khi nó đạt đến 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 Dải băng và định vị nút Phân tích What-If trong phần Công cụ Dữ liệu . Nhấp vào nút Phân tích What-If và chọn Tìm kiếm mục tiêu 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 ô 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 ở cuối phân tích.
Đối với chúng tôi, nó là -6.000 . Hãy nhớ rằng Excel xem các khoản thanh toán là một dòng tiền âm. Biến By Change Cell là mức lãi suất mà bạn muốn Excel tìm cho bạn để khoản thế chấp 100.000 đô la sẽ chỉ tiêu tốn của bạn 6.000 đô la mỗi năm. Vì vậ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ào một số cuối cùng. Trong trường hợp của chúng tôi, ô tại A2 bây giờ sẽ đọc khoảng 4, 31%.
Phân tích này cho chúng tôi biết rằng để không chi quá 6.000 đô la mỗi năm cho khoản thế chấp 30 năm, 100.000 đô la, 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 what-if, bạn có thể thử các kết hợp số và biến khác nhau để khám phá các tùy chọn bạn có khi cố gắng đảm bảo mức lãi suất tốt khi thế chấp.
Công cụ Tìm kiếm mục tiêu phân tích What-If của Excel là sự bổ sung mạnh mẽ cho các chức năng 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 việc ngược từ 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 các tính toán của mình rõ ràng hơn.
