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, có một vài đá quý không được sử dụng để cải thiện 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 Cái gì nếu được phân tích 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 yếu tố cần thiết để có được kết quả mong muốn 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 tiền 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 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 hàm PMT của Excel, bạn có thể dễ dàng tìm ra khoản thanh toán hàng năm sẽ là gì nếu lãi suất là 0 %. Bảng tính có thể trông giống như sau:
Ô tại A2 biểu thị tỷ lệ lãi suất hàng năm, ô tại B2 là độ dài của cho vay trong nhiều năm, và các tế bào tại 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 khoản thanh toán hàng năm của khoản vay thế chấp 30 năm, 100.000 đô la tại 0% lãi suất. Lưu ý rằng con số trong D2 là số âm do Excel giả định rằng các khoản thanh toán là 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 nào cho bạn vay 100.000 đô la với lãi suất 0%. Giả sử bạn làm một số hình dung và tìm ra rằng bạn có thể đủ khả năng để trả lại $ 6.000 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ể thực hiện cho khoản vay là gì để đảm bảo 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 hình 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 what-if. Về cơ bản, bạn sẽ làm cho Excel làm việc ngược trở lại từ kết quả trong D4 cho đến khi nó đạt đến 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ệuRibbon và tìm nút Phân tích gì nếu phân tíchtrong phần Công cụ dữ liệu. Nhấp vào nút Phân tích what-ifvà chọn Tìm mục tiêutừ trình đơn.
Excel mở ra một cửa sổ nhỏ và yêu cầu bạn chỉ nhập ba biến. Biến Đặt ôphải là ô chứa công thức. Trong ví dụ của chúng tôi ở đây, nó là D2. Biến Tới giá trịlà số tiền bạn muốn ô ở D2ở cuối phân tích.
Đối với chúng tôi, nó -6.000. Hãy nhớ rằng Excel thấy các khoản thanh toán dưới dạng dòng tiền âm. Biến Bằng cách thay đổi ôlà lãi suất bạn muốn Excel tìm cho bạn để thế chấp 100.000 đô la bạn sẽ 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 OKvà bạn có thể nhận thấy rằng Excel nhấp nháy một loạt các số trong các ô tương ứng cho đến khi các bước lặp cuối cùng hội tụ một số cuối cùng. Trong trường hợp của chúng ta, ô 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 nhiều hơn $ 6,000 mỗi năm với khoản vay thế chấp 30 năm, 100.000 đô la, bạn cần phải bảo đảm 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 bảo đảm lãi suất tốt trên thế chấp.
Phân tích Điều gì xảy ra nếu Công cụ Tìm mục tiêu là một 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 thông thường. 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 phép tính của bạn rõ ràng hơn.