CÁCH SỬ DỤNG WHAT IF ANALYSIS TRONG EXCEL 2019 (PHẦN 28): WHAT
Bạn đã gây ra một bí quyết phức tạp phụ thuộc vào vào nhiều trở nên và muốn biết việc chuyển đổi các biến đầu vào đó sẽ khiến kết quả thay đổi như cố nào. Chũm vì soát sổ từng đổi mới riêng lẻ, hãy lập bảng tài liệu What-If Analysis vào Excel và quan cạnh bên nhanh toàn bộ các kết quả hoàn toàn có thể xảy ra.
Bạn đang xem: Cách sử dụng what if analysis trong excel
What-If Analysis vào Excel là gì?
What-If Analysis là 1 tính năng bao gồm sẵn vào Excel, đấy là công cụ hữu dụng để giải việc Nếu - Thì. Sử dụng What-If Analysis, chúng ta cũng có thể sử dụng một vài bộ giá bán trị khác nhau trong một hoặc nhiều cách làm để mày mò tất cả các hiệu quả khác nhau.
Ví dụ: bạn có thể thực hiện What-If Analysis để thi công hai chi phí mà mỗi chi tiêu đều đưa định một mức lợi nhuận nhất định. Hoặc, bạn cũng có thể chỉ định một công dụng mà bạn có nhu cầu một phương pháp tạo ra, tiếp đến xác định bộ giá trị nào sẽ khởi tạo ra công dụng đó. Excel cung ứng một số lao lý khác nhau sẽ giúp bạn tiến hành kiểu phân tích cân xứng với nhu yếu của mình.
Ba loại quy định What-If Analysis đi kèm theo với Excel: Scenarios (kịch bảng), Goal Seek (mục tiêu tìm kiếm), cùng Data Tables (bảng dữ liệu).
Scenarios và Data Tables lấy những tập phù hợp giá trị đầu vào và xác định kết quả rất có thể có.Một Data Tables chỉ vận động với một hoặc hai biến, tuy nhiên nó có thể đồng ý nhiều giá bán trị khác nhau cho các biến đó.Scenarios bao gồm thể có không ít biến, mà lại nó chỉ rất có thể chứa về tối đa 32 giá chỉ trị.Goal Seek chuyển động khác vớiScenarios cùng Data Tables ở chỗ nó lấy một hiệu quả và xác minh các giá trị đầu vào rất có thể tạo ra kết quả đó.
Ngoài bố công chũm này, bạn có thể cài đặt các phần bổ trợ giúp bạn triển khai What-If Analysis, chẳng hạn như Solver add-in. Solver add-in giống như như Goal Seek, mà lại nó tất cả thể chứa nhiều biến hơn. Chúng ta có thể tạo dự báo bằng cách sử dụng fill handle và các lệnh khác nhau được tích phù hợp sẵn vào Excel.
Tuyệt đỉnh Excel - vươn lên là bậc thầy Excel trong 16 giờ
Trong hướng dẫn này, họ sẽ tìm hiểu cách chế tác và áp dụng data table cho
What-If Analysis. Data table đặc trưng hữu ích khi 1 công thức phụ thuộc vào vào một vài giá trị và bạn có nhu cầu thử nghiệm với các phối kết hợp đầu vào không giống nhau và so sánh kết quả.
Cách tạo ra data table cho
What-If Analysis vào Excel
Hiện tại, Excel chỉ hỗ trợ data table một và hai biến. Tuy nhiên bị số lượng giới hạn ở buổi tối đa nhị ô đầu vào khác nhau, data table vẫn có thể chấp nhận được bạn soát sổ nhiều giá trị thay đổi tùy thích.
Cách tạo ra bảng tài liệu một biến chuyển trong Excel
Một bảng dữ liệu biến vào Excel được cho phép kiểm tra một loạt giá chỉ trị cho 1 ô nguồn vào và cho biết thêm các quý hiếm đó tác động như ráng nào đến kết quả của một công thức gồm liên quan.
Để khiến cho bạn hiểu rõ hơn về tính năng này, bọn họ sẽ làm theo một ví dụ rõ ràng thay vì mô tả các bước chung chung.
Giả sử các bạn đang cân nhắc gửi tiền tiết kiệm của bản thân mình vào một ngân hàng, ngân hàng trả lãi suất 5% hàng tháng. Để kiểm tra những tùy chọn khác nhau, chúng ta đã xây dựng phương pháp tính lãi kép tiếp sau đây :
B8 chứa phương pháp FV tính số dư cuối kỳ.B2 là biến bạn có nhu cầu thử nghiệm (đầu tứ ban đầu).
Bây giờ, hãy triển khai một so với What-If dễ dàng để xem khoản tiết kiệm ngân sách của các bạn sẽ là từng nào trong 5 năm tùy trực thuộc vào số tiền đầu tư ban đầu của bạn, xê dịch từ 1.000 đô la mang lại 6.000 đô la.
Dưới trên đây là các bước để tạo bảng tài liệu một biến:
Nhập những giá trị biến đổi trong một cột hoặc trên một hàng. Trong lấy ví dụ này, chúng tôi sẽ tạo nên một bảng dữ liệu hướng cột , vày vậy họ nhập những giá trị vươn lên là của chúng tôi vào một cột (D3: D8) cùng để lại ít nhất một cột trống ở mặt phải cho những kết quả.Nhập công thức của người sử dụng vào ô một mặt hàng ở trên cùng một ô ở bên phải của những giá trị thay đổi (trong trường thích hợp này là E2). Hoặc, links ô này với công thức trong tập dữ liệu thuở đầu của bạn và nhập công thức dễ dàng và đơn giản =B8 vào ô E2
Column input cell (vì giá trị Investment của chúng ta nằm trong một cột) và lựa chọn ô trở thành được tham chiếu trong công thức của bạn. Trong ví dụ như này, chúng ta chọn B3 đựng giá trị đầu tư ban đầu.
Currency).
Bây giờ, bạn cũng có thể xem nhanh bảng tài liệu một vươn lên là của mình, đánh giá số dư có thể có và lựa chọn số chi phí gửi về tối ưu:
Ví dụ trên cho biết thêm cách tùy chỉnh bảng dữ liệu theo chiều dọc hoặc theo phía cột vào Excel. Nếu mình muốn bố viên ngang, hãy có tác dụng theo công việc bên dưới:
Nhập những giá trị phát triển thành trong một hàng, nhằm lại tối thiểu một cột trống ở phía trái (cho công thức) với một sản phẩm trống bên dưới (cho kết quả). Đối với lấy ví dụ như này, shop chúng tôi nhập những giá trị biến trong số ô F3: J3.Nhập phương pháp vào ô bí quyết một cột phía bên trái giá trị biến đầu tiên của bạn và một ô dưới (E4)Tạo bảng dữ liệu như lí giải ở trên, nhưng lại nhập giá trị đầu vào (B3) trong hộp Row input cellNhấn OK , và các bạn sẽ có một data table theo phương ngang.
Cách tạo thành bảng tài liệu hai phát triển thành trong Excel
Một bảng tài liệu hai biến cho thấy cách kết hợp khác nhau của 2 cỗ giá trị biến tác động đến hiệu quả công thức. Nói bí quyết khác, nó cho biết thêm việc biến đổi hai giá trị đầu vào của thuộc một phương pháp sẽ đổi khác kết quả đầu ra như vậy nào.
Các cách để sinh sản bảng tài liệu hai vươn lên là trong Excel về cơ bạn dạng giống như trong ví dụ trên, nước ngoài trừ bài toán bạn nhập nhì phạm vi giá trị đầu , 1 trong những một hàng và một trong một cột. Cách triển khai như sau:
Nhập một cỗ giá trị đầu vào bên dưới công thức, trong và một cột (giá trị đầu tư chi tiêu trong E3: E8).Nhập cỗ giá trị đổi thay khác vào bên bắt buộc công thức, trong và một hàng (số năm trong F2: H2). Tại thời điểm này, bảng dữ liệu hai đổi thay của bạn sẽ trông giống hệt như sau.
Cách chế tạo ra bảng tài liệu để đối chiếu nhiều kết quả
Nếu bạn muốn đánh giá chỉ nhiều phương pháp cùng một lúc, hãy kiến tạo bảng dữ liệu của người tiêu dùng tương tự các bước ở trên, nhưng thêm vào bảng các công thức ở trong phần sau đây.
Xem thêm: Cách Thiết Kế Trang Web Site Chuyên Nghiệp, Hướng Dẫn Tạo Website
Ở bên cần của công thức thứ nhất trong trường đúng theo bảng tài liệu dọc được tổ chức triển khai thành những cộtBên bên dưới công thức thứ nhất trong trường vừa lòng bảng dữ liệu ngang được tổ chức triển khai theo hàng
Để bảng tài liệu "nhiều công thức" hoạt động chính xác, tất cả các công thức nên tham chiếu mang đến cùng một ô đầu vào . Ví dụ, thêm một phương pháp nữa vào bảng tài liệu một biến hóa số của chúng ta để tính lãi và xem nó bị tác động như cố gắng nào vì chưng quy tế bào của khoản đầu tư ban đầu.
Trong ô B10, tính lãi theo bí quyết sau:=B8-B3Sắp xếp tài liệu nguồn của bảng dữ liệu với các giá trị trở thành trong D3: D8 với E2 được link với B8.Thêm một cột nữa vào phạm vi bảng tài liệu (cột F) và links F2 cùng với B10.
Bảng dữ liệubằng bí quyết nhấp vàotab
Data tab > What-If Analysis > Data Table
Tronghộpô
Column input đầu vào cell, cung cấp ô đầu vào (B3) và bấm
OK. Bây giờ bạn cũng có thể quan ngay cạnh tác động của những giá trị biến của công ty trên cả nhì công thức
Cách xóa và chỉnh sửa data table trong đối chiếu What-if Excel
Ở phần trước, các bạn đã khám phá cách tạo nên bảng tài liệu để phân tích
What-if Excel. Trong phần tiếp theo, bọn họ sẽ tìm hiểu kỹ hơn giải pháp sử dụng nhân kiệt này. Để sử dụng kết quả bảng dữ liệu trong Excel, hãy ghi nhớ 3 điều đơn giản sau:
Cách xóa bảng tài liệu trong Excel
Như đang đề cập sinh sống trên, Excel không được cho phép xóa giá chỉ trị trong những ô trơ tráo chứa kết quả. Bất cứ khi nào bạn cố gắng thực hiện vấn đề này, thông tin lỗi "Không thể nuốm đổi 1 phần của bảng dữ liệu" sẽ hiển thị.
Tuy nhiên, bạn có thể dễ dàng xóa cục bộ mảng những giá trị kết quả theo công việc bên dưới.
Tùy ở trong vào yêu cầu của bạn, hãy chọn tất cả các ô của bảng dữ liệu hoặc chỉ các ô có kết quả.Nhấn phím Delete.Cách chỉnh sửa kết quả bảng dữ liệu
Vì không thể cố kỉnh đổi 1 phần của mảng vào Excel, chúng ta không thể chỉnh sửa những ô lẻ tẻ với những giá trị được tính toán. Các bạn chỉ hoàn toàn có thể thay thế tất cả các giá trị đó bằng giá trị của riêng mình bằng phương pháp thực hiện công việc sau:
Chọn toàn bộ các ô kết quả.Xóa cách làm TABLE vào thanh công thức.Nhập giá trị mong ước và thừa nhận Ctrl + Enter. Thao tác này sẽ chèn cùng một giá trị vào toàn bộ các ô đang chọn:
Sau khi phương pháp TABLE thay đổi mất, bảng dữ liệu cũ sẽ vươn lên là một phạm vi thường thì và chúng ta có thể tự vì chỉnh sửa bất kỳ ô cá biệt nào một bí quyết bình thường.
Cách thống kê giám sát lại bảng tài liệu theo phương pháp thủ công
Nếu một bảng tài liệu lớn có rất nhiều giá trị và cách làm biến làm đủng đỉnh Excel của bạn, chúng ta có thể tắt đo lường và tính toán lại auto trong bảng đó và toàn bộ các bảng dữ liệu khác. Để thực hiện, bạn vui lòng làm theo công việc sau:
Chuyển cho tabFormulastab >Calculation
Bấm vào nút
Calculation Options
Sau đó click vào ô
Automatic Except Data Tables. Thao tác làm việc này đang tắt đo lường và tính toán bảng dữ liệu auto và tăng speed độ đo lường lại của tổng thể sổ làm việc.
Để thống kê giám sát lại bảng dữ liệu của công ty theo bí quyết thủ công, hãy lựa chọn các ô tác dụng của nó, tức là các ô tất cả công thức TABLE () với nhấn F9.
Hy vọng qua bài viết này, bạn đã biết cách tạo Data table sử dụng trong so sánh What-If của Excel. Cạnh bên đó, để không bỏ lỡ những mẹo cùng thủ thuật tin học văn phòng có lợi khác., hãy thâm nhập Gitiho ngay hôm nay.
Đôi khi, việc nhập vài tài liệu cho một bảng tính, thi công một không nhiều công thức, với định dạng để làm cho nó rõ ràng, thì chưa đủ. Trong tởm doanh, các bạn thường được yêu cầu tiên đoán một cực hiếm nội tại làm sao đó từ một mớ láo độn các công thức và công dụng trong bảng tính. Nói biện pháp khác, bạn phải phân tích tài liệu để biết và thế chắc được một cụ thể nào đó. Trong Excel, phân tích tài liệu kinh doanh có nghĩa là sử dụng những cơ chế tạo tế bào hình marketing có sẵn. Chương này trình bày với bạn những công núm đó và một trong những kỹ thuật đối chiếu với nhiều tính năng khác nhau của các công nỗ lực này. Các bạn sẽ học cách thực hiện một số phương thức của Excel để phân tíchWhat-if analysis(điều gì đang xảy ra), nắm rõ công cụGoal Seek, với cách tạo thành cácScenario.
1. Sử dụng What-If AnalysisWhat-If Analysis (phân tích điều gì vẫn xảy ra) chắc hẳn rằng là phương thức cơ bản nhất nhằm truy vấn dữ liệu trong bảng tính. What-If Analysis đại khái tất cả nghĩa là: chúng ta tính một phương pháp D từ các biến A, B, C; tiếp đến bạn từ bỏ hỏi:Điều gì sẽ xảy ranếu bạn biến hóa biến A, hoặc đổi khác B, giỏi là C?
Điều gì đang xảy ravới kết quả?Ví dụ, hình 1 minh họa một bảng tính tính quý hiếm tương lai của một khoản chi tiêu (The Future Value of an Investment) nhờ vào 5 biến: lãi suất hằng năm (interest rate), thời hạn (period), số tiền gửi hằng năm (annual deposit), số tiền gửi ban sơ (initital deposit), và cách tính lãi (deposit type). Đây chính là
Hàm FV(), và ô C9 cho chính mình thấy kết quả của nó.
Bây giờ, câu hỏi sẽ là: Điều gì xảy ra nếu như lãi suất là 7% một năm? Điều gì xảy ra nếu tôi gởi thêm vào ngân hàng $8,000 từng năm? Hoặc $12,000 từng năm? Điều gì xẩy ra nếu tôi bớt số tiền nhờ cất hộ ban đầu?Trả lời những thắc mắc này chỉ là 1-1 giản biến hóa các biến thích hợp và quan sát và theo dõi hiệu ứng xảy ra trên kết quả.1.1. Thiết lập bảng dữ liệu một nguồn vào (One-Input Data Table)Trong ví dụ như ở bài xích trên, câu hỏi chỉnh sửa các biến trong bí quyết chỉ cho chính mình mỗi lần một kết quả. Nếu bạn muốn nghiên cứu giúp hiệu ứng xảy ra xuất phát điểm từ 1 dãy cực hiếm đối với công dụng của công thức, bạn cần thiết lập một bảng dữ liệu đầu vào (input data table) và thực hiện What-If Analyisis.Ví dụ, vào bảng tính cơ mà tôi đã trình diễn ở hình 1, bạn muốn thấy quý hiếm tương lai của khoản đầu tư đó sẽ biến hóa thế nào lúc số tiền gửi vào từng năm chạy trong khoảng từ $7,000 mang lại $13,000. Bạn chỉ cần nhập các giá trị này vào một sản phẩm hoặc một cột, và tiếp đến tạo những bí quyết thích hợp.Việc thiết lập cấu hình một bảng tài liệu và thực hiện What-If Analyisis thế nào rất là dễ dàng dàng, bạn chỉ việc theo những điều sau đây:Thêm vào bảng tính những giá trị nguồn vào mà bạn có nhu cầu thử vào công thức. Các bạn có hai chắt lọc cho vị trí của các giá trị này:Nếu bạn có nhu cầu nhập các giá trị vào một hàng, chúng ta hãy bắt đầu tại ô biện pháp ô chứa bí quyết một ô về phía bên đề xuất và trong mặt hàng phía trên. Ví dụ, ô chứa cách làm là ô C9, bạn hãy bắt đầu nhập các giá trị tại ô D8.Nếu bạn muốn nhập những giá trị vào một cột, bạn hãy bước đầu tại ô cách ô chứa phương pháp một ô về phía bên trái và trong mặt hàng phía dưới. Ví dụ, ô chứa bí quyết là ô C9, các bạn hãy bước đầu nhập những giá trị tại ô B10, như minh họa ngơi nghỉ hình 2.
Chọn dãy ô bao hàm các ô đựng giá trị đầu vào và ô chứa cách làm (trong hình 3, tôi lựa chọn dãy B9:C16)ChọnData,What-If Analysis,Data Table. Excel hiển thị hộp thoạiData Table.Bạn nhập các thông số kỹ thuật trong vỏ hộp thoại này thế nào tùy thuộc vào cách các bạn đã thiết lập bảng tài liệu đầu vào ở bước 1:Nếu các bạn đã nhập những giá trị trong một hàng, chúng ta nhập showroom của ô bạn sẽ thay đổi cực hiếm trong text box
Row input đầu vào cell.Nếu chúng ta đã nhập các giá trị vào một cột, chúng ta nhập add của ô bạn sẽ thay đổi quý giá trong text boxColumn input đầu vào cell. Trong minh họa nghỉ ngơi hình 3, vị tôi sẽ biến hóa giá trị của Annual Deposit (số tiền nhờ cất hộ vào hằng năm), cùng đã tùy chỉnh cấu hình các giá bán trị nguồn vào theo cột, bắt buộc tôi lựa chọn ô C4 làmColumn đầu vào cell.
NhấnOK. Excel sẽ đặt sửa chữa từng input Value (giá trị đầu vào) vào trong đầu vào Cell, cùng hiển thị kết quả ra bảng dữ liệu, như các bạn thấy làm việc hình 4 sau đây:
Bạn thấy đó, ứng với từng số tiền gửi vào hằng năm, các bạn sẽ biết trước được giá trị sau này của khoản đầu tư chi tiêu này. Bạn để ý ô B13, có giá trị là $10,000, công dụng tương ứng của nó ở C13 là $172,790, đó cũng chính là kết quả của công thức lúc đầu (ở ô C9).1.2. Bổ sung cập nhật thêm phương pháp cho đầu vào TableBạn không xẩy ra giới hạn với chỉ một cách làm trong bảng dữ liệu. Nếu bạn có nhu cầu thấy của những giá trị đầu có hiệu ứng như thế nào với những cách làm khác nhau, bạn chỉ việc thêm bí quyết vào trong dữ liệu.Ví dụ, trong bảng tính Tính cực hiếm tương lai nhưng tôi đã cần sử dụng làm ví dụ trong số những bài trước, tôi đưa thêm vấn đề lạm phạt (inflation) vào các phép tính nhằm biết số lượng thật sự của khoản đầu tư sẽ tất cả được sau khoản thời gian đã tính khoản lạm phát là bao nhiêu. Hình 5 minh họa bảng tính này đã có được chỉnh sửa, với 1 biến mới ở C7 là Inflation (giả sử mức lạm phát hằng năm là 2%), với một bí quyết tính quý giá tương lai theo lạm phát kinh tế ở ô Today"s Dollars (D9).
Ghi chú:Cú pháp cho phương pháp tính quý giá tương lai theo lạm phát kinh tế là:= Future Value / (1 + Inflation Rate) ^ Period(=Giá trị tương lai/ (1 +Mức mức lạm phát hằng năm) ^Số năm đầu tư)Cụ thể, bí quyết tại D9 là:= C9 / (1 + C7) ^ C3
Để sinh sản Data Table, chúng ta làm theo quá trình đã trình bày ở bài bác trước. Các bạn chỉ cần chắc chắn là rằng, dãy mà chúng ta chọn ở cách 2 bao hàm các giá trị đầu vào (Input value) và cả hai bí quyết (tức là hàng B9:D16 vào hình 5).Hình 6 minh họa tác dụng bạn dành được với công thức mới này:
1.3. Thiết lập cấu hình bảng tài liệu hai đầu vào (Two-Input Data Table)Bạn cũng có thể xác lập Data Table với hai biến đổi đầu vào. Tiếp tục với bảng tính ví dụ từ những bài xích trước, tùy chọn này cho thấy thêm hiệu ứng trên giá trị tương lai của một khoản đầu tư chi tiêu khi bạn biến hóa hai trở thành khác nhau, ví dụ, biến đổi số tiền giữ hộ hằng năm và đổi khác lãi suất.Các bước tiếp sau đây hướng dẫn các bạn cách tùy chỉnh một Data Table với nhì đầu vào:Nhập một tập hợp các giá trị đầu vào trong cột đựng ô công thức, bắt đầu từ ô ngay dưới ô đựng công thức, với tập hợp các giá trị nguồn vào thứ nhị trên và một hàng với ô đựng công thức, ban đầu từ ô tiếp nối ô đựng công thức. Hình 7 minh họa bảng tính Tính quý giá tương lai cho một khoản đầu tư, có một Data Table với nhì đầu vào: Annual Deposit và Interest Rate:
Chọn dãy ô bao hàm các ô cất giá trị đầu vào và ô chứa bí quyết (trong hình 7, tôi lựa chọn dãy B8:G15)ChọnData,What-If Analysis,Data Tableđể hiển thị vỏ hộp thoạiData Table.Nhập địa chỉ cửa hàng của ô các bạn sẽ thay đổi giá trị khớp ứng với các giá trị đầu vào đã nhập vào môt mặt hàng ở text boxRow input cell(trong ví dụ, là biến hóa Interest Rate, ô C2).Nhập địa chỉ của ô bạn sẽ thay đổi giá trị tương xứng với các giá trị nguồn vào đã nhập vào một cột nghỉ ngơi text boxColumn input đầu vào cell(trong ví dụ, là trở thành Annual Deposit, ô C4).NhấnOK. Excel đã đặt sửa chữa thay thế từng đầu vào Value (giá trị đầu vào) vào trong đầu vào Cell, cùng hiển thị công dụng ra bảng dữ liệu, như chúng ta thấy sinh hoạt hình 8 sau đây:
1.4. Sửa một bảng dữ liệuKhi bạn muốn thực hiện nay những biến đổi trong bảng dữ liệu, chúng ta cũng có thể sửa lại phương pháp (hay hồ hết công thức) và các dữ liệu đầu vào (Input value). Tuy nhiên với các công dụng của bảng dữ liệu thì lại là chuyện khác. Khi chúng ta chạy lệnh Data Table (nghĩa là hotline hộp thoại Data Table ra và nhập các thông số), Excel sẽ nhập một cách làm mảng vào bên phía trong của bảng dữ liệu. Cách làm này là 1 trong những hàm TABLE(), một hàm đặc biệt quan trọng chỉ có khi dùng lệnh Data Table, hàm này có cú pháp như sau:=TABLE(row_input_ref,column_input_ref)Ở đây,row_input_refvàcolumn_input_reflà các tham chiếu ô mà các bạn đã nhập vào trong hộp thoại Data Table. Cặp vệt bộc lộ rằng đây là một mảng, nghĩa là chúng ta không thể đổi khác hoặc xóa những bộ phận riêng lẻ vào đó. Nếu bạn có nhu cầu thay đổi các kết quả, bạn phải chọn toàn thể bảng dữ liệu và chạy lại lệnh Data Table. Nếu bạn có nhu cầu xóa những kết quả, bạn phải chọn toàn bộ toàn bộ mảng và tiếp đến xóa nó.
