Data table trong Excel dùng để làm gì? Bạn đã tạo ra một bí quyết phức tạp dựa vào nhiều biến và muốn được biết việc chỉnh sửa các biến đầu vào đó sẽ khiến kết quả chỉnh sửa ra sao. Thay vì kiểm tra từng biến riêng lẻ, hãy lập bảng dữ liệu What-If Analysis trong Excel và quan sát nhanh toàn bộ các kết quả có thể xảy ra.
Bạn đang xem bài viết: Cách tạo và sử dụng Data Table trong Excel đơn giản nhất 2022
Nội dung bài viết
Data table sử dụng để làm gì?
Data table là một công cụ có khả năng sử dụng để tổng hợp và thống kê, hoặc tính toán sự chỉnh sửa biến động của một giá trị (một biến phụ thuộc) này khi một hoặc hai thành quả nào đó thay đổi (1 hoặc 2 biến độc lập thay đổi).
Ví dụ: chúng ta 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, chúng ta có thể tính Lợi nhuận của doanh nghiệp = Doanh thu – chi phí.
Các kiểu Data Table trong Excel
Data Table trong Excel 1 biến (một biến theo dòng hoặc theo cột)
Các độc giả kỹ yêu cầu của bài toán dưới đây để hiểu về tính năng dự báo độ nhạy của dữ liệu trong Excel (What If Analysis) và Áp dụng trong các tình huống rõ ràng trong công việc
mô tả một bài toán sử dụng What-If Analysis hay Data table để đo đạt dữ liệu
Bài toán tính Lợi Nhuận kinh doanh dựa trên Doanh thu và khoản chi
[LỢI NHUẬN = DOANH THU – CHI PHÍ]
Lập công thức tính ô Lợi Nhuận
vấn đề 1: Hãy lập công thức để tính tự động giá trị của các ô lợi nhuận khi chi phí không chỉnh sửa nhưng Doanh thu thay đổi
1. Chuẩn bị thông tin dữ liệu cần dự báo tự động doanh thu
2. Chọn vùng dữ liệu
3. Chọn Data/ What If Analysis/ Data Table…
4. Chọn nội dung dự báo
5. Nhấn OK để coi kết quả
vấn đề 2: Hãy lập công thức để tính tự động chất lượng của các ô lợi nhuận khi Doanh thu không chỉnh sửa, tuy nhiên chi phí thay đổi
Các bạn thực hiện giống như là vấn đề 1, chỉ chỉnh sửa giá trị của Dòng (Row) Thay cho Cột (Column) trong hộp thoại Data Table…
Data Table trong Excel 2 chiều (2 biến theo dòng và cột)
vấn đề 3: Hãy lập bí quyết để tính tự động giá trị của các ô lợi nhuận khi doanh thu và chi phí đều chỉnh sửa theo các thành quả tương ứng với nhau
1. Chuẩn bị vùng chứa dữ liệu
2. Tô đen vùng dữ liệu
3. Vào Data/ What If Analysis chọn Data Table….
4. Chọn thông tin cho Data Table….
5. Kết quả
Cách tạo bảng trong Data Table trong Excel
Một bảng dữ liệu biến trong Excel cho phép kiểm duyệt một loạt thành quả cho một ô đầu vào và cho biết các giá trị đấy ảnh hưởng như thế nào đến kết quả của một bí quyết xoay quanh.
Để giúp bạn hiểu một cách rõ ràng hơn về tính năng này, con người sẽ thực hiện theo một VD cụ thể thay vì miêu tả các bước chung chung.
Giả sử bạn đang cân nhắc gởi tiền tiết kiệm của mình vào một ngân hàng, tổ chức tài chính trả lãi suất 5% hàng tháng. Để kiểm duyệt các tùy chọn khác nhau, bạn đã tạo ra công thức tính lãi kép sau đây :
- B8 chứa bí quyết FV tính số dư cuối kỳ.
- B2 là biến bạn mong 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 để xem khoản tiết kiệm của chúng ta sẽ là gồm bao nhiêu trong 5 năm tùy thuộc theo vốn đầu tư ban đầu của chúng ta, dao động từ 1.000 đô la đến 6.000 đô la.
dưới đây là các bước để tạo bảng dữ liệu một biến:
- Nhập các thành quả biến trong một cột hoặc trên một hàng. Trong ví dụ này, chúng tôi sẽ tạo một bảng dữ liệu hướng cột , Vì vậy con người nhập các giá trị biến của chúng tôi vào một cột (D3: D8) và để lại ít nhất một cột trống ở bên phải cho các kết quả.
-
Nhập bí quyết của bạn vào ô một hàng ở trên và một ô ở bên phải của các giá trị biến (trong trường hợp này là E2). Hoặc, liên kết ô này với bí quyết trong tập dữ liệu ban đầu của bạn và nhập công thức dễ dàng =B8 vào ô E2
-
- Nếu chúng ta muốn kiểm tra tác động của các thành quả biến đối với các công thức khác tham chiếu đến cùng một ô đầu vào, hãy nhập (các) bí quyết bổ sung vào bên phải của bí quyết đầu tiên .
- Chọn phạm vi bảng dữ liệu, bao gồm công thức của chúng ta, các ô thành quả biến và các ô trống cho kết quả (D2: E8).
-
Chuyển tới Data tab > Data Tools, nhấp vào nút What-If Analysis, và sau đấy nhấp vào Data Table…
-
Trong cửa sổ hộp thoại Data Table…, hãy nhấp vào ô Column Input cell (vì thành quả Investment của chúng ta nằm trong một cột) và chọn ô biến được tham chiếu trong công thức của chúng ta. Trong ví dụ này, con người chọn B3 chứa thành quả đầu tư ban đầu.
- Nhấp vào OK , và Excel sẽ ngaytức thì điền vào các ô trống với kết quả tương ứng với giá trị biến trong cùng một hàng.
- Áp dụng định dạng số ước muốn cho kết quả ( trong VD này là Currency).
Bây giờ, Bạn có thể xem nhanh bảng dữ liệu một biến của mình, kiểm duyệt số dư có thể có và chọn số tiền gửi tối ưu:
ví dụ trên cho thấy cách thiết lập bảng dữ liệu theo chiều dọc hoặc theo hướng cột trong Excel. nếu như bạn thích bố cục ngang, hãy thực hiện theo các bước bên dưới:
- Nhập các giá trị biến trong một hàng, để lại ít nhất một cột trống ở bên trái (cho công thức) và một hàng trống bên dưới (cho kết quả). đối với VD này, chúng tôi nhập các thành quả biến trong các ô F3: J3.
- Nhập bí quyết vào ô cách một cột bên trái thành quả biến trước tiên của bạn và một ô bên dưới (E4)
- Tạo bảng dữ liệu như hướng dẫn ở trên, tuy nhiên nhập thành quả đầu vào (B3) trong hộp Row input cell
- Nhấn OK , và bạn có thể có một data table theo phương ngang.
Cách sử dụng Data Table để tính biến động
Cách dùng Data Table 1 biến để tính biến động
– Khi sử dụng data table chúng ta phải có biết quan điểm các ô như: Ô truy xuất biến số theo hàng, Ô truy tìm biến số theo cột. Ô tham chiếu kết quả.
a. Data table 1 biến phân bổ 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 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 tệp dữ liệu mẫu để rèn luyện 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 tablenhư các thực hành các bướ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 để 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ẽ thu thập thành quả 10 trong ô D8 trong bảng thu nhập chỉnh sửa cho vào ô thu nhập hàng tháng C3, sau đấy thu thập 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. bắt đầu lặp lại công đoạn như trên với các thành quả thu nhập khác.
b. Data table 1 biến phân bổ theo cột
– Bây giờ, Giả sử thu nhập hàng tháng của chúng ta cố định 11 triệu, chí phí hàng tháng chỉnh sửa 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 giống như là trên, trong ô D12 nhập :=C5; và vì khoản chi 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 chỉnh sửa từ 10-14 triệu, khoản chi 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 phân bổ 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 thực hành các bước như hình trên;
Bước 3: một khi điền đầy đủ nội dung 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 đã 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 khoản chi chỉnh sửa. giống như là 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 để tổng hợp và thống kê dữ liệu trong các bảng dữ liệu như giới thiệu dưới đây.
Cách dùng Data table trong Excel để tổng hợp và thống kê dữ liệu
Dùng data table 1 biến để tổng hợp và thống kê dữ liệu
– Cho bảng dữ liệu như hình sau:
– Yêu cầu: tổng hợp và 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 dùng sumif và data table 1 biến để tổng hợp và thống kê, các bước thực hiện như sau, các chúng ta 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: một khi điền 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ể sử dụng hàm Sumif thay thế cho data table 1 biến để tổng hợp và thống kê dữ liệu bằng việc nhập bí quyết trong ô J10: =SUMIF($C$5:$C$12,$I10,$E$5:$E$12) và fill kết quả xuống ta mang lại được kết quả như data table 1 biến:
* giống như là 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 bí quyết trong ô E16: =SUMIF($C$5:$C$12,E15,$G$5:$G$12) ; sau đấy quét vùng dữ liệu E15:G16, chọn thẻ data → what if analysis → data table, vì loại VT phân bổ theo hàng nên ta nhập dữ liệu trong ô Row input cell là E15 như hình sau
Sau đấy nhấn OK kết quả thu được là hình sau:
* Lưu ý: con người hoàn toàn có khả năng dùng hàm Sumif để tổng hợp và 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 bí quyết 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ẽ dùng data table 2 biến Dùng cho bài toán dưới đây; yêu cầu tổng hợp và thống kê số lượng vật tư theo loại VT và Kho;
* Trong Tin học văn phòng, để làm bài này, ta 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.
* Lưu ý: chúng ta hoàn toàn có thể sử dụng lênh Sumifs để thay thế data table 2 biến ở trên bằng việc sử dụng lệnh như sau: =SUMIFS($E$5:$E$12,$B$5:$B$12,$D14,$C$5:$C$12,E$13) sau đấy 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.
Tổng kết
Blog Phần Mềm gửi đến bạn bài chia sẻ về cách tạo và công dụng của Data Table trong Excel. Hy vọng bài viết này sẽ giúp bạn hoàn thành bài tập và công việc hiệu suất tốt hơn trong tương lai.