Bài viết: Tổng hợp dữ liệu trên nhiều bảng tính

[BNTT]

Tổng hợp dữ liệu trên nhiều bảng tính

Nhiều doanh nghiệp tạo các bảng tính cho một công việc cụ thể rồi sau đó phân phối chúng cho những bộ phận khác nhau. Phổ biến nhất là việc lập ngân sách. Bộ phận kế toán tạo ra một khuôn mẫu ngân sách chung, và mỗi bộ phận trong công ty phải điền vào đó, xong gửi về lại. Những loại bảng tính tương tự như thế này mà bạn thường thấy là những bảng tính về việc kiểm kê, dự đoán doanh số, khảo sát dữ liệu, kết quả thử nghiệm, v.v...

Tạo những bảng tính như vậy, phân phối chúng, và điền vào chúng thì chỉ là những việc đơn giản. Phần khó là khi các bảng tính này được trả về bộ phận ban đầu, và tất cả các dữ liệu mới sẽ được tổng hợp thành một báo cáo, trình bày tổng con số của toàn công ty. Tác vụ này được gọi là tổng hợp dữ liệu và thường không phải là chuyện dễ dàng, nhất là các bảng tính lớn. Tuy nhiên, như bạn sẽ thấy sau đây, Excel có một số tính năng mạnh mẽ nhằm giúp bạn thực hiện công việc khó khăn này.

Excel có thể tổng hợp dữ liệu bằng cách sử dụng một trong những phương pháp sau đây:

• Tổng hợp theo vị trí: Với phương pháp này, Excel tổng hợp dữ liệu từ nhiều bảng tính bằng cách sử dụng các tọa độ dãy giống nhau trên mỗi bảng tính. Bạn sử dụng phương pháp này nếu các bảng tính mà bạn tổng hợp có cùng một một cách trình bày.

• Tổng hợp theo hạng mục: Phương pháp này yêu cầu Excel tổng hợp dữ liệu bằng cách tìm theo các tiêu đề hàng và cột trong mỗi bảng tính. Ví dụ, nếu có một bảng tính liệt kê doanh số Gizmo hằng tháng trong hàng 1, và một bảng tính khác liệt kê doanh số Gizmo hằng tháng trong hàng 5, bạn vẫn có thể tổng hợp dữ liệu miễn là cả hai bảng tính có một tiêu đề "Gizmo" ở đầu những hàng này.

Trong cả hai trường hợp, bạn xác định một hay nhiều dãy nguồn (dãy chứa dữ liệu mà bạn muốn tổng hợp) và một dãy đích (dãy mà bạn sẽ tổng hợp dữ liệu vào đó). Các bài tiếp theo đây sẽ trình bày chi tiết về cả hai phương pháp tổng hợp này.
​

1. Tổng hợp theo vị trí

Nếu các bảng tính mà bạn sẽ dùng để tổng hợp có cùng một khuôn mẫu, việc tổng hợp theo vị trí là lựa chọn dễ thực hiện nhất. Ví dụ, kiểm tra 3 bảng tính ” Division I Budget, Division II Budget, và Division III Budget ” được minh họa trong hình 1. Như bạn thấy, mỗi bảng tính sử dụng các tiêu đề cột và tiêu đề hàng giống như nhau, do đó chúng hoàn toàn thích hợp để tổng hợp theo vị trí.

Hình 1

Hãy bắt đầu bằng cách tạo một bảng tính mới có cùng khuôn mẫu như các bảng tính mà bạng đang tổng hợp. Hình 2 minh họa một bảng tính mới có tên là Consolidation mà bạn dùng để tổng hợp 3 bảng tính ngân sách đã nói ở trên.

Hình 2

Như là một ví dụ, bạn hãy xem mình sẽ bắt đầu tổng hợp các dữ liệu doanh số (sales) trong 3 bảng tính ngân sách minh họa ở hình 1 như thế nào. Bạn bắt đầu làm việc với 3 dãy nguồn:
'[Division I Budget]Details' !B4:M6
'[Division II Budget]Details' !B4:M6
'[Division III Budget]Details' !B4:M6

​

1. Chọn ô trên cùng bên trái của dãy đích. Trong trang tính Consolidate By Position, bạn chọn ô B4.
   ​
2. Chọn Data, Consolidate. Excel sẽ mở hộp thoại Consolidate.
   ​
3. Trong danh sách xổ xuống Function, bạn chọn phép tính sẽ dùng cho việc tổng hợp. Thường thì bạn sẽ dùng hàm Sum (tính tổng), nhưng Excel có 10 phép tính khác nữa cho bạn chọn, bao gồm Count (đếm), Average (trung bình), Max (lớn nhất), Min (nhỏ nhất).
   ​
4. Trong khung Reference, bạn nhập tham chiếu cho một trong các dãy nguồn. Sử dụng một trong các phương pháp sau đây:
   
   ​
   • Nhập bằng tay tọa độ của dãy. Nếu dãy nguồn ở trong một bảng tính khác, bạn phải nhập cả tên bảng tính ở trong một cặp dấu ngoặc vuông. Nếu bảng tính nằm trong một ổ đĩa khác hoặc một thư mục khác, phải bao gồm đường dẫn đầy đủ cho bảng tính đó.
     ​
   • Nếu trang tính (nguồn) đang mở, bạn kích hoạt nó (bằng cách nhấn vào nó hoặc nhấn vào tab View, chọn menu Switch Windows), và rồi dùng chuột để chọn dãy nguồn.
     ​
   • Nếu bảng tính (nguồn) chưa mở sẵn, chọn Browse, chọn file trong hộp thoại Browse, và nhấn OK. Excel sẽ tự động thêm đường dẫn đầy đủ vào trong khungReference. Bạn điền thêm tên trang tính (sheet) và tọa độ dãy nguồn.​
   
   ​
5. Nhấn Add, Excel sẽ thêm dãy nguồn vào trong khung All References (xem hình 3)
   
   

   

   
   Hình 3
   ​
6. Lập lại bước 4 và bước 5 cho tất cả các dãy nguồn.
   ​
7. Nếu bạn muốn số liệu tổng hợp sẽ tự cập nhật khi bạn thay đổi dữ liệu gốc, bạn để nguyên tùy chọn Create Links to Source Data ở trạng thái được kích hoạt.
   ​
8. Nhấn OK. Excel thu thập dữ liệu, tổng hợp chúng, và rồi thêm chúng vào trong dãy đích (xem hình 4.
   
   

   

   
   Hình 4​

Nếu bạn chọn không tạo ra liên kết với dữ liệu nguồn ở bước 7, Excel chỉ điền vào dãy đích những dữ liệu đã tổng hợp. Nhưng nếu bạn chọn tạo liên kết với dữ liệu nguồn, Excel sẽ làm 3 điều sau đây:
​

• Thêm các công thức liên kết với dãy đích vào mỗi ô trong các dãy nguồn mà bạn đã chọn.​

​

• Tổng hợp dữ liệu bằng cách thêm các hàm SUM() (hoặc là phép tính nào mà bạn đã chọn trong danh sách Function) để tính tổng các kết quả của các công thức liên kết.​

​

• Bao quanh các bảng tính tổng hợp vào ẩn đi những công thức liên kết, như bạn đã thấy ở hình 4.​

Nếu bạn hiển thị dữ liệu ở cấp độ 1 (Level 1), bạn sẽ thấy được những công thức liên kết. Ví dụ, hình 5 minh họa chi tiết của doanh số tổng hợp cho Books của January (ô B7). Chi tiết trong các ô B4, B5, và B6 chứa các công thức liên kết đến các ô tương ứng trong 3 bảng tính ngân sách (ví dụ, ='D:\GPE\[Division I Budget.xlsx]Details'!$B$4)

Hình 5

2. Tổng hợp theo hạng mục
Nếu các bảng tính mà bạn sẽ dùng để tổng hợp không có cùng một khuôn mẫu, bạn cần sử dụng phương pháp tổng hợp theo hạng mục. Trong trường hợp này, Excel sẽ kiểm tra từng dãy nguồn và tổng hợp dữ liệu trong những cột và những hàng có cùng chung tiêu đề. Ví dụ, ở hình 6, bạn hãy xem kỹ những hàng trong nhóm Sales của 3 bảng tính.

Hình 6

Như bạn thấy, Division C thì bán books, software, videos và CD-ROMs, Division B thì bán books và CD-ROMs, và Division A thì bán software, books, và videos. Sau đây là cách mà bạn tổng hợp những con số đó:
​

1. Tạo mới hoặc chọn một bảng tính mới để tổng hợp, và chọn góc trái phía trên của dãy đích. Bạn không cần phải nhập các tiêu đề (hàng hay cột) cho các dữ liệu sẽ tổng hợp bởi vì Excel sẽ tự làm điều đó cho bạn. Tuy nhiên, nếu bạn thích sắp xếp các tiêu đề theo một thứ tự cụ thể, bạn có thể tự nhập chúng. (Tuy nhiên, phải bảo đảm rằng bạn nhập chính xác tên các tiêu đề như chúng đã có ở trong các dữ liệu nguồn).
   ​
2. Chọn Data, Consolidate. Excel sẽ mở hộp thoại Consolidate.
   ​
3. Trong danh sách xổ xuống Function, bạn chọn phép tính sẽ dùng cho việc tổng hợp.
   ​
4. Trong khung Reference, bạn nhập tham chiếu cho một trong các dãy nguồn. Với trường hợp này, hãy chắc chắn là bạn có bao gồm cả các tiêu đề hàng và cột của dữ liệu.
   ​
5. Nhấn Add để thêm dãy nguồn vào trong khung All References.
   ​
6. Lập lại bước 4 và bước 5 cho tất cả các dãy nguồn.
   ​
7. Nếu bạn muốn số liệu tổng hợp sẽ tự cập nhật khi bạn thay đổi dữ liệu gốc, bạn để nguyên tùy chọn Create Links to Source Data ở trạng thái được kích hoạt.
   ​
8. Nếu bạn muốn Excel sử dụng tiêu đề dữ liệu ở hàng trên cùng của dãy được chọn, bạn kích hoạt tùy chọn Top Row. Nếu bạn muốn Excel sử dụng tiêu đề dữ liệu ở cột bên trái của dãy được chọn, bạn kích hoạt tùy chọn Left Column. (Hoặc bạn có thể chọn cả hai)
   ​
9. Nhấn OK. Excel thu thập dữ liệu, tổng hợp chúng, và rồi thêm chúng vào trong dãy đích (xem hình 7).
   
   

   

   
   Hình 7​

​

Một số bài viết có liên quan:
1/ Phân tích dữ liệu với Table (phần 1)
2/ Xử lý các lỗi của công thức (phần 4)
3/ Xử lý các lỗi của công thức (phần 3)
4/ Xử lý các lỗi của công thức (phần 2)
5/ Xử lý các lỗi của công thức (phần 1)
6/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 3)
7/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 2)
8/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 1)
9/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
10/ Làm việc với công thức mảng trong Excel