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