Công thức table trong excel

Chức năng Data Table trong What if analysis là một công cụ khá hay trong excel giúp chúng ta có thể tính toán hoặc thống kê số liệu dựa trên những thay đổi của 1 biến hay 2 biến độc lập

Công cụ What-If Analysis với chức năng Data Table dùng để làm gì? cách sử dụng Data table 1 biến, 2 biến như thế nào? chúng ta cùng tìm hiểu qua bài viết này nhé.

I. Data table dùng để làm gì?

- Data table là công cụ có thể dùng để thống kê, hoặc tính toán sự thay đổi biến động của một giá trị (một biến phụ thuộc) này khi một hoặc hai giá trị nào đó thay đổi (1 hoặc 2 biến độc lập thay đổi).

Ví dụ: Bạn có thể tính toán Số tiền bạn tiết kiệm được (biến phục thuộc) = Thu nhập hàng tháng của bạn (biến độc lập) - Chi phí sinh hoạt hàng tháng (biến độc lập).

Hay, bạn có thể tính Lợi nhuận của doanh nghiệp = Doanh thu - Chi phí.

II. Cách Sử dụng Data Table để tính biến động

1. Cách sử dụng Data Table 1 biến để tính biến động

- Khi sử dụng data table chúng ta cần biết khái niệm các ô như: Ô truy xuất biến số theo hàng, Ô truy xuất biến số theo cột. Ô tham chiếu kết quả.

a. Data table 1 biến sắp xếp theo hàng (theo dòng)

- Giả sử thu nhập hàng tháng của bạn thay đổi từ 10 đến 14 triệu, chí phí hàng tháng bạn sử dụng (cố định) là 6 triệu, tính số tiền bạn tiết kiệm được hàng tháng, như hình dưới đây:

- Các bạn có thể tải file dữ liệu mẫu để thực hành Tại Đây

* Để thực hiện yêu cầu trên, ta thực hiện các bước như sau:

Bước 1: Tạo bảng data table với biến thu nhập sắp xếp theo hàng (D8:H8); nhập kết quả cần biết (số tiền tiết kiệm) vào ô tham chiếu kết quả C9: =C5;

Bước 2: Quét chọn bảng data table từ địa chỉ C8:H9, chọn thẻ Data → vùng Data tools chọn What if analysis → chọn Data table như các thao tác từ 1 đến 3 ở hình sau:

Bước 3: Bảng data table xuất hiện, vì thu nhập thay đổi được sắp xếp theo hàng nên ta chọn truy xuất ở mục Row input cell với địa chỉ ô C3, mục Columns input cell bỏ trống, nhấn OK theo như hình sau.

Sau các thao tác trên ta được kết quả như hình sau:

⇒ Cách xử lý của Data table như sau: Đầu tiên Data table sẽ lấy giá trị 10 trong ô D8 trong bảng thu nhập thay đổi cho vào ô thu nhập hàng tháng C3, sau đó lấy kết quả số tiền tiết kiệm hàng tháng trong ô C5 chép vào ô tham chiếu kết quả C9, sau đó ghi kết quả này vào bảng data table tại ô D9. Tiếp tục lặp lại quá trình như trên với các giá trị thu nhập khác.

b. Data table 1 biến sắp xếp theo cột

- Bây giờ, Giả sử thu nhập hàng tháng của bạn cố định 11 triệu, chí phí hàng tháng thay đổi từ 4 đến 8 triệu, tính số tiền bạn tiết kiệm được hàng tháng.

- Ta thực hiện các bước tương tự như trên, trong ô D12 nhập :=C5; và vì chi phí thay đổi theo cột nên trong bảng data table ta nhập ô Column input cell với địa chỉ ô C4, bỏ trống ô Row input cell như hình sau:

Ta được kết quả như sau:

2. Cách sử dụng Data table 2 biến tính biến động

- Bây giờ giả sử thu nhập thay đổi từ 10-14 triệu, chi phí biến đổi từ 4-8 triệu, vậy số tiền tiết kiệm tương ứng là bao nhiêu?

- Thực hiện các bước như sau:

Bước 1: Tạo bảng data table với biến thu nhập sắp xếp theo hàng (D8:H8); nhập kết quả cần biết (số tiền tiết kiệm) vào ô tham chiếu kết quả C8: =C5;

Bước 2: Quét chọn vùng dữ liệu từ C8:H13, chọn thẻ data → what if analysis → data table. Thực hiện các thao tác như hình trên;

Bước 3: Sau khi nhập đầy đủ thông tin trong cột Row input cell và column input cell, nhấn OK ta được kết quả như bảng sau

* Lưu ý: khi sử dụng data table chúng ta sẽ không xóa được dữ liệu trong từng ô, phải xóa cả bảng

- Ở trên chúng ta đã sử dụng data table để tính toán số tiền tiết kiệm được hàng tháng nếu thu nhập hoặc chi phí thay đổi. Tương tự như vậy các bạn có thể tính lợi nhuận của 1 công ty khi doanh thu và chi phí thay đổi. Ngoài ra, data table còn được sử dụng để thống kê dữ liệu trong các bảng dữ liệu như giới thiệu dưới đây.

III. Cách sử dụng data table để thống kê dữ liệu

1. Sử dụng data table 1 biến để thống kê dữ liệu

- Cho bảng dữ liệu như hình sau:

- Yêu cầu: Thống kê số lượng theo loại VT, và thống kê thành tiền theo loại VT

* Trong bài này, ta sẽ kết hợp sử dụng sumif và data table 1 biến để thống kê, các bước thực hiện như sau, các bạn có thể xem cách dùng hàm sumif Tại đây.

Bước 1: Nhập công thức: =SUMIF($C$5:$C$12,I10,$E$5:$E$12) trong ô J8, nhấn Enter và được kết quả như trên.

Bước 2: Quét chọn vùng dữ liệu từ I10:J12 rồi chọn thẻ data → what if analysis → data table, vì loại VT sắp xếp theo cột nên ta nhập dữ liệu trong ô Column input cell là I10 như hình sau

Bước 3: Sau khi nhập thông tin như hình trên, nhấn OK ta được kết quả như hình sau

* Lưu ý: Trường hợp này chúng ta hoàn toàn có thể dùng hàm Sumif thay thế cho data table 1 biến để thống kê dữ liệu bằng cách nhập công thức trong ô J10: =SUMIF($C$5:$C$12,$I10,$E$5:$E$12) và fill kết quả xuống ta thu được kết quả như data table 1 biến:

* Tương tự như vậy, ta thực hiện thống kê thành tiền theo Loại VT

- Đầu tiên, ta cũng nhập công thức trong ô E16: =SUMIF($C$5:$C$12,E15,$G$5:$G$12) ; sau đó quét vùng dữ liệu E15:G16, chọn thẻ data → what if analysis → data table, vì loại VT sắp xếp theo hàng nên ta nhập dữ liệu trong ô Row input cell là E15 như hình sau

Sau đó nhấn OK kết quả thu được là hình sau:

* Lưu ý: Chúng ta hoàn toàn có thể dùng hàm Sumif để thống kê dữ liệu thay cho data table 1 biến trong trường hợp này, bằng cách nhập công thức sau trong ô E16: =SUMIF($C$5:$C$12,E$15,$G$5:$G$12) rồi fill kết quả theo hàng ta thu được kết quả như data table 1 biến. 

2. Sử dụng data table 2 biến để thống kê dữ liệu

- Bây giờ ta sẽ sử dụng data table 2 biến áp dụng cho bài toán dưới đây; yêu cầu thống kê số lượng vật tư theo loại VT và Kho;

* Trong bài này chúng ta sẽ sử dụng công thức mảng

Bước 1: Nhập 2 ô cột và dòng ở J5 và K5, nhập công thức trong ô I9:  =sum(if(($B$5:$B$12=J5)*($C$5:$C$12=K5),$E$5:$E$12,0)) rồi nhấn Ctrl + Shift + Enter 

Bước 2: Quét vùng dữ liệu từ I9:L12, vào thẻ data → what if analysis → data table rồi chọn như hình sau.

Kết quả thu được sau khi nhấn OK như sau:

* Lưu ý: chúng ta hoàn toàn có thể dùng lênh Sumifs để thay thế data table 2 biến ở trên bằng cách dùng lệnh như sau:  =SUMIFS($E$5:$E$12,$B$5:$B$12,$D14,$C$5:$C$12,E$13) sau đó fill kết quả theo chiều hàng và cột ta được kết quả là hình dưới, tương tự data table 2 biến.

>> bạn có thể xem cách sử dụng hàm sumifs Tại Đây;

Hy vọng rằng với bài hướng dẫn sử dụng data table 1 biến và 2 biến ở trên giúp ích cho các bạn, chúc các bạn vận dụng tốt vào công việc của mình. Nếu bạn thấy bài viết hay, hãy like và chia sẻ bài viết, mọi thắc mắc hãy để lại bình luận dưới bài viết để được hỗ trợ nhé!