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 số điểm cộng chưa được sử dụng đầy đủ giúp 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ì xảy ra nếu xảy ra.
Công cụ Phân tích Nếu xảy ra trong 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 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 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 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 ý 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ử 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 lãi suất cao nhất mà 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 sẽ chỉ cần bắt đầu nhập các số vào ô 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. 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 là 6.000 đô la của bạn.
Bắt đầu bằng cách nhấp vào Dữ liệu trên ruy-băng và định vị Phân tích điều gì xảy ra trong Công cụ dữ liệu tiết diện. Nhấp vào Phân tích điều gì xảy ra nếu 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. Đặt ô biến 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 . Giá trị biến là số tiền bạn muốn ô tại D2 ở 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. Bằng cách thay đổi ô biến 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 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 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.