Phân tích Điều gì xảy ra trong Excel cung cấp Bảng Dữ liệu. Đây là một cái tên tồi. Nó nên được gọi là Phân tích độ nhạy. Nó là mát mẻ. Đọc về nó ở đây.
Tìm kiếm Mục tiêu cho phép bạn tìm tập hợp các đầu vào dẫn đến một kết quả cụ thể. Đôi khi, bạn muốn xem nhiều kết quả khác nhau từ nhiều kết hợp đầu vào khác nhau. Với điều kiện bạn chỉ có hai ô đầu vào để thay đổi, bảng dữ liệu cung cấp một cách nhanh chóng để so sánh các lựa chọn thay thế.
Sử dụng ví dụ thanh toán khoản vay, giả sử rằng bạn muốn tính giá cho nhiều loại số dư gốc và cho nhiều điều khoản khác nhau.
Đảm bảo rằng công thức bạn muốn lập mô hình nằm ở góc trên cùng bên trái của một dải ô. Đặt các giá trị khác nhau cho một biến xuống cột bên trái và các giá trị khác nhau cho một biến khác ở trên cùng.
Từ tab Dữ liệu, chọn Phân tích Điều gì xảy ra, bảng dữ liệu.
Bạn có các giá trị dọc theo hàng trên cùng của bảng đầu vào. Bạn muốn Excel cắm các giá trị đó vào một ô đầu vào nhất định. Chỉ định ô đầu vào đó là Ô Nhập hàng.
Bạn có các giá trị dọc theo cột bên trái. Bạn muốn những thứ đó được cắm vào một ô đầu vào khác. Chỉ định ô đó làm Ô Đầu vào Cột.
Khi bạn bấm OK, Excel sẽ lặp lại công thức ở cột trên cùng bên trái cho tất cả các kết hợp của hàng trên cùng và cột bên trái. Trong hình ảnh bên dưới, bạn thấy 60 khoản thanh toán khoản vay khác nhau dựa trên các kết quả khác nhau.
Lưu ý rằng tôi đã định dạng kết quả bảng để không có số thập phân và sử dụng Trang chủ, Định dạng có điều kiện, Thang màu để thêm bóng đổ màu đỏ / vàng / xanh lá cây.
Đây là phần tuyệt vời: Bảng này là "trực tiếp". Nếu bạn thay đổi các ô nhập dọc theo cột bên trái hoặc hàng trên cùng, các giá trị trong bảng sẽ được tính toán lại. Dưới đây, các giá trị dọc bên trái được tập trung vào phạm vi $ 23K đến $ 24K.
Cảm ơn Owen W. Green đã gợi ý các bảng.
Xem video
- Ba công cụ điều gì xảy ra trong Excel
- Hôm qua - Tìm kiếm mục tiêu
- Hôm nay - một bảng dữ liệu
- Tuyệt vời cho các bài toán hai biến
- Thông tin bên lề: không thể nhập hàm mảng TABLE theo cách thủ công - nó sẽ không hoạt động
- Sử dụng Thang màu để tô màu các câu trả lời
- Điều gì sẽ xảy ra nếu bạn có 3 biến để thay đổi? Các tình huống? Không! Sao chép trang tính
- Các bảng tính toán chậm: chế độ tính toán cho Tất cả các bảng ngoại trừ
- Cảm ơn Owen W. Green đã gợi ý mẹo này
Bản ghi video
Học Excel từ podcast, tập 2034 - What-Ifs với Bảng dữ liệu!
Tôi đang podcasting toàn bộ cuốn sách này, hãy nhấp vào "i" ở góc trên bên phải để truy cập danh sách phát!
Hôm nay chúng ta sẽ nói về công cụ thứ hai trong Phân tích điều gì sẽ xảy ra, hôm qua chúng ta nói về Tìm kiếm mục tiêu, hôm nay chúng ta sẽ đề cập đến Bảng dữ liệu. Vì vậy, chúng ta có một mô hình nhỏ đẹp ở đây, đây là một mô hình nhỏ, 3 ô đầu vào, một công thức. Nhưng mô hình này có thể là hàng trăm ô đầu vào, hàng nghìn hàng, miễn là nó đi đến một câu trả lời cuối cùng và chúng tôi muốn lập mô hình câu trả lời này cho một số giá trị khác nhau của 2-3 (?) Ô đầu vào. Ví dụ: có thể chúng tôi muốn xem xét những chiếc xe khác nhau, vì vậy bất cứ nơi nào từ 20000 trở lên, vì vậy tôi sẽ đặt 20 và 21000 ở đó, lấy chốt điền và kéo, hạ xuống 28000. Trên cùng chúng tôi ' đang xem xét các kỳ hạn khác nhau, vì vậy khoản vay 36 tháng, khoản vay 42 tháng, khoản vay 48 tháng, 54, 60, 66, và thậm chí 72.
Được rồi, bước tiếp theo này là hoàn toàn không bắt buộc, nhưng nó thực sự giúp tôi suy nghĩ về điều này, tôi luôn thay đổi màu của các giá trị ở trên cùng và của các giá trị ở bên trái. Và điều thực sự quan trọng ở đây là ô góc đó, ô góc quan trọng nhất đó, phải là câu trả lời mà chúng tôi đang cố gắng lập mô hình. Vì vậy, bạn phải bắt đầu chọn từ ô góc đó với câu trả lời, sau đó chọn tất cả các hàng và tất cả các cột. Vì vậy, chúng tôi đi vào Dữ liệu, Phân tích điều gì xảy ra và Bảng dữ liệu, và nó yêu cầu hai điều ở đây, và đây là cách bạn nghĩ về nó. Nó nói rằng có một loạt các mục khác nhau dọc theo hàng trên cùng trong bảng, tôi muốn lấy những mục đó, từng mục một và cắm chúng vào mô hình, chúng ta nên nhập ở đâu? Vì vậy, các mục này, đây là các điều khoản, chúng sẽ đi vào ô B2. Và sau đó,có một loạt các mục dọc theo cột bên trái, chúng tôi muốn lấy chúng, từng mục một và cắm chúng vào B1, như vậy, được rồi và chúng tôi nhấp vào OK, BAM, mô hình này chạy đi chạy lại .
Bây giờ chỉ cần dọn dẹp một chút ở đây, tôi luôn vào và làm Trang chủ, và có lẽ là 0 chữ số thập phân, như vậy. Và có thể là một chút Định dạng có điều kiện, Thang màu và hãy đi với các số màu đỏ cho các số lớn và màu xanh lá cây cho các số nhỏ, chỉ để cho tôi, bạn biết đấy, cách theo dõi trực quan. Bây giờ có vẻ như nếu chúng ta quay với giá 425 đô la, thì chúng ta đang ở điểm này hoặc điểm này, hoặc bạn biết đấy, có thể ở đây, tất cả chúng ta sẽ thu về gần 425 đô la. Vì vậy, tôi có thể thấy các tỷ lệ cược khác nhau, các kết hợp khác nhau của chúng tôi là gì, để đưa chúng tôi đến những giá trị đó.
Bây giờ một vài điều, phần này bên trong đây, thực sự là một công thức mảng lớn, vì vậy = TABLE (B2, B1), đầu vào hàng và cột. Điều này gây tò mò, bạn không được phép nhập cái này, bạn chỉ có thể tạo cái này bằng cách sử dụng Dữ liệu, Phân tích Nếu-Nếu-xảy ra, bạn phải sử dụng hộp thoại đó. Nếu bạn thử và gõ công thức đó, nhấn Ctrl + Shift + Enter, nó sẽ không hoạt động, phải không? Vì vậy, đó là một hàm trong Excel, nhưng nếu bạn đủ thông minh để nhập nó, quá tệ, nó sẽ không hoạt động, nhưng nó liên tục tính toán lại. Vì vậy, nếu chúng tôi xác định rằng chúng tôi chỉ xem xét các số hạng từ 48 và chúng tôi muốn xem xét theo nhóm 3 hoặc thứ gì đó tương tự, vì vậy khi tôi thay đổi những con số này, tất cả những điều đó đang được tính toán. Trong trường hợp này, nó chỉ làm một công thức cho mỗi công thức, nhưng hãy tưởng tượng nếu chúng ta đang làm một 100 công thức, điều này sẽ bị chậm lại đáng kể. Vì vậy, ở đây dưới Công thức, ở đó 'thực sự là một tùy chọn Tùy chọn tính toán, Tự động hoặc Thủ công, có một tùy chọn thứ ba cho biết "Vâng, hãy tính toán lại mọi thứ ngoại trừ Bảng dữ liệu, đừng tiếp tục tính toán lại bảng dữ liệu." Bởi vì đây có thể là một lực cản rất lớn về thời gian tính toán.
Được rồi, bảng dữ liệu thật tuyệt vời khi bạn có hai biến để thay đổi, nhưng chúng tôi có ba biến để thay đổi. Điều gì sẽ xảy ra nếu có các mức lãi suất khác nhau, tôi khuyên bạn nên đến gặp Trình quản lý kịch bản? KHÔNG, tôi KHÔNG BAO GIỜ khuyên bạn nên đi đến Trình quản lý kịch bản! Trong trường hợp này, chúng ta có 9x7, đó là 63 kịch bản khác nhau mà chúng tôi đã tính toán ở đây, để tạo ra 63 kịch bản khác nhau của Trình quản lý kịch bản sẽ mất 2 giờ, thật kinh khủng. Tôi không đề cập vấn đề này trong cuốn sách “MrExcel XL”, vì đó là 40 mẹo hay nhất. Điều này có lẽ nằm trong cuốn sách “Power Excel” của tôi với 567 điều bí ẩn trong Excel đã được giải đáp, nhưng tôi chắc rằng tôi đã phàn nàn về việc sử dụng nó quá khốn khổ, bạn sẽ không thấy tôi làm Trình quản lý kịch bản ở đây. Nếu chúng tôi thực sự phải làm điều này với nhiều tỷ lệ khác nhau, điều tốt nhất cần làm là Ctrl-drag, lấy trang tính này, Ctrl-drag, Ctrl-drag,Ctrl-kéo, sau đó thay đổi tỷ lệ trên mỗi trang tính. Vì vậy, nếu chúng ta có thể nhận được 5% hoặc 4,75% hoặc tương tự như vậy, đúng không, không có cách nào dễ dàng để thiết lập điều đó cho 3 biến trong Trình quản lý tình huống. Được rồi, “40 mẹo Excel hay nhất mọi thời đại”, tất cả đều có trong sách này, bạn có thể mua sách, hãy nhấp vào chữ “i” ở góc trên bên phải.
Tóm tắt phần từ hôm nay: Có ba công cụ What-If trong Excel, hôm qua chúng ta đã nói về Goal Seek, hôm nay là Bảng dữ liệu. Thật là tuyệt vời cho các bài toán 2 biến, ngày mai bạn sẽ gặp một bài toán 1 biến. Hàm mảng bảng không thể nhập thủ công, nó sẽ không hoạt động, bạn phải sử dụng Dữ liệu, Phân tích Nếu-Nếu, Bảng Dữ liệu. Tôi đã sử dụng thang màu, Trang chủ, Định dạng có điều kiện, Thang màu, để tô màu cho các câu trả lời. Nếu bạn có 3 biến để thay đổi, bạn thực hiện các kịch bản? Không, chỉ cần tạo bản sao của trang tính hoặc các bản sao của bảng, chúng tính toán chậm, đặc biệt là với một mô hình phức tạp. Có một chế độ tính toán Tự động cho tất cả các bảng ngoại trừ và Owen W. Green đã đề xuất đưa tính năng này vào sách.
Vì vậy, cảm ơn anh ấy và cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp lại bạn vào lần sau với một netcast khác từ!
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2034.xlsx
