Bạn đã phát hành một phương pháp phức tạp nhờ vào vào nhiều trở nên và ao ước biết việc đổi khác các biến nguồn vào đó sẽ khiến cho kết quả đổi khác như chũm nào. Cố gắng vì bình chọn từng biến chuyển 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 dùng hàm table trong excel

What-If Analysis trong Excel là gì?

What-If Analysis là một tính năng gồm sẵn trong Excel, đây là công cụ hữu dụng để giải việc Nếu - Thì. áp dụng What-If Analysis, bạn cũng có thể sử dụng một trong những bộ giá trị không giống nhau trong một hoặc nhiều cách làm để khám phá tất cả các tác dụng khác nhau.

Ví dụ: chúng ta có thể thực hiện nay What-If Analysis để phát hành hai giá thành mà mỗi giá cả đều giả định một mức lệch giá nhất định. Hoặc, chúng ta có thể chỉ định một công dụng mà bạn muốn một bí quyết tạo ra, tiếp nối xác định cỗ giá trị nào sẽ tạo nên ra hiệu quả đó. Excel hỗ trợ một số luật pháp khác nhau sẽ giúp đỡ bạn triển khai kiểu phân tích phù hợp với yêu cầu của mình.

Ba loại phương tiện 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), và Data Tables (bảng dữ liệu).

Scenarios với Data Tables lấy các tập vừa lòng giá trị đầu vào và xác định kết quả hoàn toàn có thể có. Một Data Tables chỉ chuyển động với một hoặc nhị biến, dẫu vậy nó bao gồm thể chấp nhận nhiều giá trị khác biệt cho các biến đó. Scenarios bao gồm thể có không ít biến, nhưng mà nó chỉ rất có thể chứa buổi tối đa 32 giá trị.Goal Seek vận động khác vớiScenarios cùng Data Tables ở chỗ nó đem một công dụng và xác định các cực hiếm đầu vào có thể tạo ra hiệu quả đó.

Ngoài ba công cố này, chúng ta có thể cài đặt các phần hỗ trợ giúp bạn tiến hành What-If Analysis, chẳng hạn như Solver add-in. Solver add-in giống như như Goal Seek, dẫu vậy nó bao gồm thể đựng nhiều biến hơn. Bạn cũng có thể tạo dự báo bằng phương pháp sử dụng fill handle và các lệnh khác nhau được tích hòa hợp sẵn trong Excel.

Trong khuyên bảo này, chúng ta sẽ tìm hiểu cách sinh sản và sử dụng data table choWhat-If Analysis. Data table quan trọng hữu ích lúc 1 công thức phụ thuộc vào vào một số trong những giá trị và bạn có nhu cầu thử nghiệm với các kết hợp đầu vào không giống nhau và so sánh kết quả.

Cách tạo data table choWhat-If Analysis trong Excel

Hiện tại, Excel chỉ cung cấp data table một cùng hai biến. Tuy nhiên bị số lượng giới hạn ở về tối đa hai ô nguồn vào khác nhau, data table vẫn chất nhận được bạn soát sổ nhiều giá chỉ trị phát triển thành tùy thích.

Cách tạo bảng dữ liệu một trở nên trong Excel

Một bảng tài liệu biến trong Excel được cho phép kiểm tra một loạt giá trị cho một ô đầu vào và cho thấy các cực hiếm đó tác động như vắt nào đến công dụng của một công thức tất cả liên quan.

Để khiến cho bạn hiểu rõ hơn về tính năng này, bọn họ sẽ tuân theo một ví dụ cụ thể thay vì chưng mô tả quá trình chung chung.

Giả sử các bạn đang suy nghĩ gửi tiền huyết kiệm của mình vào một ngân hàng, ngân hàng trả lãi suất vay 5% mặt hàng tháng. Để kiểm tra những tùy lựa chọn khác nhau, các bạn đã xây dựng phương pháp tính lãi kép sau đây :

B8 chứa cách làm FV tính số dư cuối kỳ.B2 là biến bạn muốn thử nghiệm (đầu tứ ban đầu).

*

Bây giờ, hãy thực hiện một phân tích What-If đơn giản và dễ dàng để coi khoản tiết kiệm chi phí của bạn sẽ là từng nào trong 5 năm tùy nằm trong vào số chi phí đầu tư lúc đầu của bạn, xấp xỉ từ 1.000 đô la mang lại 6.000 đô la.

Dưới đây là các bước để tạo ra bảng dữ liệu một biến:

Nhập những giá trị trở thành trong một cột hoặc bên trên một hàng. Trong ví dụ như này, chúng tôi sẽ sinh sản một bảng tài liệu hướng cột , bởi vì vậy bọn họ nhập những giá trị trở thành của công ty chúng tôi vào một cột (D3: D8) và để lại ít nhất một cột trống ở mặt phải cho các kết quả.
Nhập công thức của người tiêu dùng vào ô một sản phẩm ở trên cùng một ô ở mặt phải của những giá trị đổi mới (trong trường phù hợp này là E2). Hoặc, link ô này với phương pháp trong tập dữ liệu ban đầu của các bạn và nhập công thức đơn giản =B8 vào ô E2
*
Nếu bạn có nhu cầu kiểm tra tác động của những giá trị biến so với các phương pháp khác tham chiếu đến cùng một ô đầu vào, hãy nhập (các) công thức bổ sung vào bên đề nghị của công thức thứ nhất .Chọn phạm vi bảng dữ liệu, bao gồm công thức của bạn, những ô giá bán trị trở nên và các ô trống cho kết quả (D2: E8).
*

Trong hành lang cửa số hộp thoại Data Table…, hãy nhấp vào ôColumn input cell (vì quý hiếm Investment của bọn họ nằm vào một cột) và chọn ô trở thành được tham chiếu trong phương pháp của bạn. Trong lấy ví dụ này, bọn họ chọn B3 cất giá trị đầu tư ban đầu.
*
Nhấp vào OK , cùng Excel sẽ ngay nhanh chóng điền vào những ô trống với hiệu quả tương ứng với cái giá trị phát triển thành trong và một hàng.Áp dụng format số ước muốn cho hiệu quả ( trong lấy ví dụ này làCurrency).

Bây giờ, chúng ta cũng có thể xem cấp tốc bảng dữ liệu một biến chuyển của mình, soát sổ số dư hoàn toàn có thể có và lựa chọn số tiền gửi tối ưu:

*

Ví dụ trên cho biết thêm cách cấu hình thiết lập bảng tài liệu theo theo hướng dọc hoặc theo hướng cột trong Excel. Nếu bạn thích bố cục ngang, hãy làm cho theo quá trình bên dưới:

Nhập những giá trị thay đổi trong một hàng, nhằm lại ít nhất một cột trống ở phía bên trái (cho công thức) và một mặt hàng trống dưới (cho kết quả). Đối với ví dụ như này, shop chúng tôi nhập các giá trị biến trong những ô F3: J3.Nhập cách làm vào ô cách một cột phía trái giá trị biến chuyển đầu tiên của doanh nghiệp và một ô bên dưới (E4)Tạo bảng tài liệu như hướng dẫn ở trên, tuy thế nhập giá chỉ trị đầu vào (B3) trong hộp Row đầu vào cellNhấn OK , và các bạn sẽ có một data table theo phương ngang.

Xem thêm: Hiện Tượng Sương Mù Quang Hóa Là Gì, Đặc Điểm Và Hậu Quả, Sương Mù Quang Hóa: Nó Là Gì, Đặc Điểm Và Hậu Quả

*

*

Cách chế tác bảng dữ liệu hai trở thành trong Excel

Một bảng tài liệu hai biến cho biết cách phối kết hợp khác nhau của 2 cỗ giá trị biến ảnh hưởng đến kết quả công thức. Nói bí quyết khác, nó cho thấy thêm việc biến hóa hai giá bán trị nguồn vào của thuộc một cách làm sẽ chuyển đổi kết trái đầu ra như vậy nào.

Các bước để chế tác bảng tài liệu hai biến đổi trong Excel về cơ phiên bản giống như trong ví dụ như trên, ngoại trừ việc bạn nhập hai phạm vi giá trị đầu , một trong một sản phẩm và 1 trong các 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ị chi tiêu trong E3: E8).
Nhập cỗ giá trị biến 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 tài liệu hai phát triển thành của các bạn sẽ trông giống hệt như sau.
*
Chọn toàn bộ phạm vi bảng dữ liệu bao gồm công thức, hàng và cột của các giá trị vươn lên là và những ô chứa các giá trị được tính toán sẽ xuất hiện. Chúng ta chọn phạm vi E2: H8.Tạo bảng dữ liệu như thông thường: Data tab > What-If Analysis > Data Table…Trong vỏ hộp Row input cell, nhập tham chiếu mang lại ô đầu vào cho các giá trị vươn lên là trong sản phẩm (trong ví dụ như này, chính là B6 cất giá trị Năm ).Trong ô Column đầu vào cell, hãy nhập tham chiếu đến ô đầu vào cho các giá trị thay đổi trong cột (B3 chứa giá trị Initial Investment).
*
Định dạng kết quả đầu ra nếu đề nghị và triển khai phần tích.

*

Cách tạo thành bảng tài liệu để đối chiếu nhiều kết quả

Nếu bạn có nhu cầu đánh giá bán nhiều phương pháp cùng một lúc, hãy thi công bảng dữ liệu của bạn tương tự các bước ở trên, nhưng cung cấp bảng những công thức tại phần sau đây.

Ở bên phải của công thức trước tiên trong trường thích hợp bảng tài liệu dọc được tổ chức thành các cộtBên dưới công thức thứ nhất trong trường hòa hợp bảng dữ liệu ngang được tổ chức theo hàng

Để bảng tài liệu "nhiều công thức" chuyển động chính xác, toàn bộ các công thức đề nghị tham chiếu mang lại cùng một ô nguồn vào . Ví dụ, thêm một công thức nữa vào bảng tài liệu một thay đổi số của bọn họ để tính lãi với xem nó bị ảnh hưởng như ráng nào bởi quy mô của khoản đầu tư chi tiêu 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 những giá trị đổi mới trong D3: D8 và E2 được liên kết với B8.
*
Chọn phạm vi bảng dữ liệu không ngừng mở rộng (D2: F8).Mởhộp thoạiBảng dữ liệubằng phương pháp nhấp vàotabData tab > What-If Analysis > Data Table
TronghộpôColumn input cell, cung cấp ô nguồn vào (B3) cùng bấmOK. Bây giờ bạn có thể quan giáp tác động của các giá trị biến của doanh nghiệp trên cả nhì công thức
*

Cách xóa và sửa đổi data table trong so với What-if Excel

Ở phần trước, chúng ta đã tò mò cách chế tác bảng tài liệu để phân tíchWhat-if Excel. Vào phần tiếp theo, bọn họ sẽ tìm hiểu kỹ hơn bí quyết sử dụng bản lĩnh này. Để sử dụng kết quả bảng tài liệu trong Excel, hãy ghi ghi nhớ 3 điều dễ dàng sau:

Để một bảng dữ liệu được chế tạo thành công, (các) ô đầu vào phải ở trên và một trang tính với bảng dữ liệu.
Microsoft Excel sử dụng hàm TABLE (row_input_cell, colum_input_cell) nhằm tính toán công dụng bảng dữ liệu:

Như sẽ đề cập sinh hoạt trên, Excel không được cho phép xóa giá chỉ trị trong những ô chơ vơ chứa kết quả. Bất cứ khi nào bạn nỗ lực thực hiện bài toán này, thông tin lỗi "Không thể thế đổi một trong những phần của bảng dữ liệu" đã hiển thị.

Tuy nhiên, bạn có thể dễ dàng xóa toàn cục mảng các giá trị công dụng theo các bước bên dưới.

Tùy nằm trong vào yêu cầu của bạn, hãy chọn tất cả những ô của bảng tài liệu hoặc chỉ các ô gồm kết quả.Nhấn phím Delete.

Cách chỉnh sửa công dụng bảng dữ liệu

Vì ko thể rứa đổi một trong những phần của mảng vào Excel, các bạn không thể chỉnh sửa những ô cô đơn với những giá trị được tính toán. Chúng ta chỉ có thể thay thế toàn bộ các quý giá đó bằng giá trị của riêng mình bằng cách thực hiện các bước sau:

Chọn tất cả các ô kết quả.Xóa bí quyết TABLE vào thanh công thức.Nhập giá chỉ trị mong muốn và dấn Ctrl + Enter. Thao tác này vẫn chèn thuộc một quý giá vào toàn bộ các ô vẫn chọn:

*

Sau khi công thức TABLE biến mất, bảng dữ liệu cũ sẽ trở nên một phạm vi thông thường và chúng ta cũng có thể tự vì chỉnh sửa ngẫu nhiên ô riêng lẻ nào một phương pháp bình thường.

Cách tính toán lại bảng dữ liệu theo biện pháp thủ công

Nếu một bảng dữ liệu lớn có nhiều giá trị và cách làm biến làm chậm chạp Excel của bạn, bạn có thể tắt tính toán lại tự động trong bảng đó và tất cả các bảng tài liệu khác. Để thực hiện, bạn vui vẻ làm theo các bước sau:

Chuyển đến tabFormulastab >CalculationBấm vào nútCalculation OptionsSau kia click vào ôAutomatic Except Data Tables. Làm việc này sẽ tắt thống kê giám sát bảng dữ liệu auto và tăng tốc độ độ tính toán lại của tổng thể sổ có tác dụng việc.

*

Để đo lường lại bảng dữ liệu của người sử dụng theo giải pháp thủ công, nên chọn các ô kết quả của nó, có nghĩa là các ô có công thức TABLE () và nhấn F9.

Hy vọng qua bài viết này, bạn đã hiểu phương pháp tạo Data table áp dụng trong đối chiếu What-If của Excel. ở bên cạnh đó, để không bỏ qua những mẹo và thủ thuật tin học văn phòng có ích khác., hãy tham gia linhthach.vn ngay lập tức hôm nay.