Hàm CONSOLIDATE trong Excel: Hướng dẫn 03 cách sử dụng

Khi xử lý lượng lớn dữ liệu trong Excel, việc tổng hợp nhiều bảng hay gộp các dữ liệu trùng nhau có thể mất nhiều thời gian, đặc biệt khi số liệu được phân bổ ở nhiều vùng hoặc nhiều trang tính khác nhau. Hàm CONSOLIDATE trong Excel giúp đơn giản hóa quá trình này bằng cách tổng hợp dữ liệu theo từng nhóm và phép tính phù hợp. Trong bài viết dưới đây, MHG sẽ hướng dẫn cách sử dụng hàm CONSOLIDATE chi tiết, từ thiết lập cơ bản đến gộp dữ liệu trùng nhau và tổng hợp dữ liệu từ nhiều sheet.

Hàm CONSOLIDATE trong Excel là gì? 

Hàm CONSOLIDATE trong Excel là công cụ hỗ trợ tổng hợp dữ liệu từ nhiều vùng, nhiều trang tính hoặc nhiều workbook vào một bảng kết quả chung. Công cụ này đặc biệt hữu ích khi người dùng cần xử lý các bảng dữ liệu có cùng cấu trúc hoặc tổng hợp những dữ liệu có cùng nhãn mà không phải thực hiện thủ công trên từng bảng.

Trong thực tế, CONSOLIDATE thường được sử dụng để tổng hợp doanh thu, số lượng, chi phí hoặc các dữ liệu định kỳ được lưu ở nhiều bảng khác nhau. Ví dụ, doanh nghiệp có thể sử dụng CONSOLIDATE để tổng hợp doanh thu của nhiều chi nhánh hoặc gộp số lượng của cùng một sản phẩm xuất hiện ở nhiều vùng dữ liệu vào một bảng báo cáo duy nhất.

Các tính năng của hàm CONSOLIDATE trong Excel 

Hàm CONSOLIDATE trong Excel hỗ trợ người dùng tổng hợp và xử lý dữ liệu từ nhiều vùng hoặc nhiều trang tính trong cùng một bảng kết quả. Một số tính năng nổi bật của CONSOLIDATE bao gồm tổng hợp dữ liệu từ nhiều vùng và trang tính, tổng hợp dữ liệu theo vị trí hoặc theo nhãn và thực hiện các phép tính khi tổng hợp dữ liệu. 

Tổng hợp dữ liệu từ nhiều vùng và trang tính

Hàm CONSOLIDATE trong Excel hỗ trợ tổng hợp dữ liệu từ nhiều vùng dữ liệu vào một bảng kết quả chung. Các vùng nguồn có thể nằm trên nhiều worksheet trong cùng một workbook hoặc ở các workbook khác nhau.

Tính năng này phù hợp khi doanh nghiệp lưu dữ liệu theo từng phòng ban, chi nhánh hoặc kỳ báo cáo riêng biệt nhưng cần tạo một báo cáo tổng hợp. Chẳng hạn, các bảng chi phí của từng chi nhánh có thể được tổng hợp vào một bảng chi phí chung để thuận tiện cho việc theo dõi và báo cáo.

Tổng hợp dữ liệu theo vị trí hoặc nhãn

Khi sử dụng hàm CONSOLIDATE, Excel hỗ trợ hai phương thức tổng hợp dữ liệu là theo vị trí (position) và theo danh mục/nhãn (category).

Theo vị trí phù hợp khi các vùng dữ liệu nguồn có cùng cách bố trí, cùng thứ tự và sử dụng các nhãn giống nhau. Ngược lại, theo nhãn phù hợp khi các vùng dữ liệu không được sắp xếp theo cùng thứ tự nhưng vẫn sử dụng những nhãn tương ứng.

Ví dụ, nếu cùng một sản phẩm xuất hiện trong các bảng của nhiều chi nhánh, CONSOLIDATE có thể dựa trên nhãn sản phẩm tương ứng để tổng hợp các giá trị vào bảng kết quả.

Thực hiện phép tính khi tổng hợp dữ liệu

Lệnh CONSOLIDATE cho phép người dùng lựa chọn phép tính tổng hợp tại mục Function. Theo Microsoft, SUM là lựa chọn mặc định; người dùng có thể chọn summary function khác tùy mục đích xử lý dữ liệu.

Trong thực tế, các phép tính như SUM, AVERAGE, COUNT, MAX VÀ MIN có thể phục vụ những mục đích thống kê khác nhau: 

Function	Ý nghĩa	Ứng dụng
SUM	Tính tổng	Tổng doanh thu, chi phí, số lượng
AVERAGE	Tính trung bình	Doanh thu hoặc chi phí trung bình
COUNT	Đếm giá trị	Thống kê số lượng giá trị
MAX	Xác định giá trị lớn nhất	Doanh thu cao nhất
MIN	Xác định giá trị nhỏ nhất	Doanh thu hoặc chi phí thấp nhất

Hàm CONSOLIDATE dữ liệu theo vị trí và nhãn khác nhau như thế nào?

Khi sử dụng hàm CONSOLIDATE, Excel có thể tổng hợp dữ liệu theo vị trí hoặc theo nhãn. Hai phương pháp này khác nhau ở cách Excel xác định dữ liệu tương ứng giữa các bảng nguồn.

• Hàm CONSOLIDATE theo vị trí

Hàm CONSOLIDATE trong Excel theo vị trí phù hợp khi các bảng dữ liệu nguồn có cùng cấu trúc và thứ tự sắp xếp. Excel sẽ tổng hợp các giá trị nằm tại những vị trí tương ứng giữa các bảng.

Ví dụ, nếu các sheet đều sắp xếp sản phẩm theo thứ tự Laptop → Chuột → Bàn phím → Tai nghe, người dùng có thể tổng hợp dữ liệu theo vị trí.

• Hàm CONSOLIDATE theo nhãn

Khi sử dụng hàm CONSOLIDATE theo nhãn, các bảng có thể chứa cùng danh mục nhưng khác thứ tự sắp xếp. Excel sẽ dựa vào nhãn tương ứng để xác định và tổng hợp dữ liệu.

Ví dụ, một sheet có thứ tự Laptop → Chuột → Bàn phím, trong khi sheet khác là Chuột → Bàn phím → Laptop. Excel có thể dựa vào tên sản phẩm để tổng hợp các giá trị thuộc cùng danh mục.

Tiêu chí	Theo vị trí	Theo nhãn
Cấu trúc bảng	Cùng bố cục, thứ tự	Có thể khác thứ tự
Căn cứ tổng hợp	Vị trí dữ liệu	Nhãn/danh mục
Phù hợp khi	Các bảng cùng một mẫu	Các bảng có cùng danh mục
Ví dụ	Báo cáo tháng cùng cấu trúc	Danh sách sản phẩm khác thứ tự

Hướng dẫn sử dụng CONSOLIDATE trong Excel từ A-Z

Hàm CONSOLIDATE trong Excel có thể được sử dụng trong nhiều trường hợp khác nhau tùy theo cách tổ chức dữ liệu. Dưới đây, MHG sẽ hướng dẫn cách sử dụng CONSOLIDATE trong Excel từ hợp nhất bảng tính trong 1 trang tính đến nhiều trang tính.

Gộp dữ liệu trùng nhau trong Excel trong 1 trang tính 

Khi một trang tính chứa nhiều vùng dữ liệu có nội dung trùng nhau, hàm CONSOLIDATE trong Excel có thể giúp tổng hợp các giá trị này vào một bảng kết quả chung. Với trường hợp này, bạn sẽ thực hiện từ việc xác định các vùng dữ liệu cần gộp, thêm chúng vào lệnh CONSOLIDATE, thiết lập phép tính và nhãn dữ liệu, sau đó kiểm tra kết quả tổng hợp. 

• Bước 1: Chuẩn bị dữ liệu

  

• Bước 2: Chọn một ô trống nằm ngoài bảng dữ liệu nguồn, ví dụ E1, để tránh vùng nguồn chồng lên vùng kết quả.

• Bước 3: Mở CONSOLIDATE: Chọn Data → Data Tools → CONSOLIDATE.

  

• Bước 4: Thiết lập dữ liệu

Function: chọn Sum.

Reference: chọn toàn bộ bảng A1:B8.

Nhấn Add > Tích Top row và Left column.

• Bước 5: Gộp dữ liệu trùng nhau và kết quả

  

Hợp nhất các bảng tính có thứ tự cột khác nhau trong 1 trang tính 

Khi các bảng có cùng loại dữ liệu nhưng thứ tự cột không giống nhau, người dùng vẫn có thể sử dụng hàm CONSOLIDATE trong Excel để tổng hợp dựa trên tên nhãn thay vì vị trí của từng cột. 

• Bước 1: Hai bảng có thứ tự cột khác nhau

  

• Bước 2: Mở lệnh CONSOLIDATE trong Excel

Sau khi chọn vị trí đặt kết quả, trên thanh Ribbon chọn: Data → Data Tools → CONSOLIDATE

• Bước 3 : Chọn Function và vùng dữ liệu Reference

Đây là bước thiết lập quan trọng nhất khi sử dụng hàm CONSOLIDATE.

1. Function: Chọn phép tính cần thực hiện. Ví dụ chọn SUM để tính tổng
2. Reference: Chọn vùng dữ liệu cần tổng hợp, ví dụ A1:D2.
3. Add: Nhấn Add sau mỗi lần chọn một vùng dữ liệu.
4. All references: Kiểm tra danh sách các vùng dữ liệu đã được thêm.

   

• Bước 4: Thiết lập Top row và Left column

  

• Bước 5: Hoàn tất CONSOLIDATE và kiểm tra kết quả

  

Gộp dữ liệu trùng nhau trong Excel từ nhiều trang tính 

Với hàm CONSOLIDATE trong Excel, người dùng có thể tổng hợp dữ liệu từ nhiều trang tính vào một bảng kết quả chung mà không cần cộng từng bảng thủ công. Để thực hiện, bạn cần chuẩn bị các vùng dữ liệu nguồn, chọn vị trí đặt kết quả, thêm lần lượt từng vùng dữ liệu vào CONSOLIDATE và thiết lập nhãn phù hợp theo các bước dưới đây. 

• Bước 1: Chuẩn bị và kiểm tra nguồn dữ liệu

  

Ví dụ, doanh nghiệp có 3 cửa hàng và muốn tổng hợp số lượng sản phẩm bán ra. Mỗi sheet có thể được thiết lập như sau: 

• Bước 2: Chọn vị trí đặt bảng tổng hợp

Tiếp theo, tạo một sheet mới để chứa kết quả, chẳng hạn đặt tên là TongHop.

• Bước 3: Mở lệnh CONSOLIDATE trong Excel

Sau khi chọn vị trí đặt kết quả, trên thanh Ribbon chọn: Data → Data Tools → CONSOLIDATE.

Excel sẽ mở hộp thoại CONSOLIDATE để người dùng thiết lập cách tổng hợp dữ liệu. Đây là vị trí chính thức của công cụ CONSOLIDATE trong các phiên bản Excel hiện hành được Microsoft hỗ trợ.

• Bước 4: Chọn Function và vùng dữ liệu Reference

Đây là bước thiết lập quan trọng nhất khi sử dụng hàm CONSOLIDATE.

1. Function: Chọn phép tính cần thực hiện. Ví dụ chọn Sum để tính tổng
2. Reference: Chọn vùng dữ liệu cần tổng hợp, ví dụ A1:B5
3. Add: Nhấn Add sau mỗi lần chọn một vùng dữ liệu
4. All references: Kiểm tra danh sách các vùng dữ liệu đã được thêm
5. Nếu có 3 sheet: chọn Sheet 1 → Add → Sheet 2 → Add → Sheet 3 → Add.

   

• Bước 5: Thiết lập Top row và Left column

  

• Bước 6: Hoàn tất CONSOLIDATE và kiểm tra kết quả

  

02 Trường hợp đặc biệt khi sử dụng hàm CONSOLIDATE trong Excel

Trong quá trình sử dụng hàm CONSOLIDATE, dữ liệu nguồn có thể phát sinh thêm sau khi bảng tổng hợp đã được tạo, chẳng hạn bổ sung dữ liệu từ một trang tính mới hoặc từ một workbook khác. Với những trường hợp này, người dùng vẫn có thể mở rộng phạm vi tổng hợp bằng cách thêm vùng tham chiếu mới vào lệnh CONSOLIDATE. Dưới đây là 02 tình huống thường gặp và cách thực hiện cụ thể. 

Trường hợp 1: Thêm dữ liệu từ một sheet mới

Tình huống này xảy ra khi bạn đã tổng hợp dữ liệu từ một số sheet nhưng sau đó workbook xuất hiện thêm một sheet chứa dữ liệu mới. Khi đó, bạn có thể thực hiện CONSOLIDATE lại và bổ sung vùng dữ liệu của sheet mới vào Reference.

• Bước 1: Chuẩn bị dữ liệu trong sheet mới

  

• Bước 2: Mở công cụ CONSOLIDATE

Trên thanh Ribbon, chọn: Data → Data Tools → CONSOLIDATE. Hộp thoại của hàm CONSOLIDATE sẽ xuất hiện.

• Bước 3: Chọn phép tính cần sử dụng

Tại mục Function, chọn phép tính phù hợp với mục đích tổng hợp. Trong ví dụ cộng số lượng sản phẩm, chọn: Sum. 

Tại ô Reference, lần lượt chọn các vùng dữ liệu đã sử dụng trước đó rồi nhấn Add. 

Sau đó chọn thêm vùng dữ liệu của sheet mới: CuaHang_4!$A$1:$B$5

Nhấn Add để đưa vùng này vào mục All references.

• Bước 4 : Hoàn tất và kiểm tra dữ liệu

  

Trường hợp 2: Thêm dữ liệu từ một file Excel khác

Nếu dữ liệu cần tổng hợp nằm trong một workbook khác, hàm CONSOLIDATE trong Excel vẫn cho phép thêm vùng dữ liệu đó vào danh sách tham chiếu. Sau khi chọn vùng dữ liệu từ file Excel mới và thêm vào All references, Excel sẽ sử dụng vùng này cùng các nguồn dữ liệu trước đó để thực hiện tổng hợp.

Ví dụ: Bạn đang tổng hợp số lượng sản phẩm từ file 1, nhưng dữ liệu của một cửa hàng mới lại nằm trong file 2.

• Bước 1: Mở cả hai file Excel

  

• Bước 2: Tạo file để hiển thị kết quả

  

• Bước 3: Mở CONSOLIDATE và thêm vùng dữ liệu mới

Trên thanh công cụ, chọn Data → Data Tools → CONSOLIDATE. Tại hộp thoại xuất hiện:

1. Function: Chọn phép tính cần sử dụng, ví dụ Sum để tính tổng.
2. Reference: Lần lượt chọn các vùng dữ liệu cần tổng hợp và nhấn Add.
3. Với sheet mới, chọn vùng dữ liệu tương ứng, ví dụ CuaHang_2!$A$1:$B$5, sau đó nhấn Add.
4. Kiểm tra All references để đảm bảo tất cả vùng dữ liệu cần thiết đã được thêm đầy đủ.

   

• Bước 4: Kiểm tra kết quả

  

Những lỗi thường gặp khi sử dụng hàm CONSOLIDATE trong Excel 

Trong quá trình sử dụng hàm CONSOLIDATE, người dùng có thể gặp tình trạng dữ liệu không được gộp, kết quả sai hoặc thiếu thông tin. Dưới đây là một số những lỗi phổ biến và cách để xử lý. 

Dữ liệu cùng tên nhưng không được gộp

Khi sử dụng hàm CONSOLIDATE trong Excel, đôi khi các dữ liệu nhìn giống nhau nhưng Excel vẫn không gộp vào cùng một nhóm, khiến kết quả tổng hợp bị tách thành nhiều dòng hoặc cột riêng biệt. 

Nguyên nhân: Nhãn dữ liệu giữa các bảng không hoàn toàn giống nhau, có khoảng trắng thừa hoặc cách nhập tên khác nhau. Ví dụ Laptop và Lap top có thể khiến việc đối chiếu nhãn không chính xác.

Cách khắc phục: Kiểm tra và chuẩn hóa lại nhãn dữ liệu trước khi thực hiện CONSOLIDATE. Bạn nên:

• Xóa khoảng trắng thừa và sửa các lỗi chính tả trong tên dữ liệu.
• Thống nhất cách đặt tên giữa các bảng, ví dụ tất cả đều sử dụng Laptop.
• Kiểm tra lại Top row và Left column trong hộp thoại CONSOLIDATE để Excel nhận diện đúng nhãn.
• Thực hiện lại CONSOLIDATE sau khi dữ liệu nguồn đã được chuẩn hóa.

  

Kết quả tổng hợp không đúng giá trị

Sau khi thực hiện hàm CONSOLIDATE trong Excel, kết quả đôi khi có thể khác với số liệu mong muốn hoặc không khớp với dữ liệu nguồn. Tình trạng này thường liên quan đến phép tính được lựa chọn hoặc phạm vi dữ liệu thiết lập trong CONSOLIDATE. 

Nguyên nhân: Chọn sai phép tính tại Function hoặc sai vùng dữ liệu tại Reference.

Cách khắc phục:

• Kiểm tra Function, chẳng hạn chọn Sum khi cần tính tổng.
• Kiểm tra lại vùng dữ liệu trong Reference.
• Đảm bảo vùng được chọn chứa đúng dữ liệu cần tổng hợp.

  

CONSOLIDATE bị thiếu dữ liệu

Trong một số trường hợp, bảng kết quả sau khi thực hiện hàm CONSOLIDATE trong Excel có thể không hiển thị đầy đủ dữ liệu từ các bảng nguồn. Điều này khiến số liệu tổng hợp bị thiếu và không phản ánh chính xác dữ liệu ban đầu. 

Nguyên nhân: Một hoặc nhiều vùng nguồn chưa được thêm vào All references, hoặc Reference chưa bao phủ hết dữ liệu.

Cách khắc phục:

• Kiểm tra danh sách All references.
• Đảm bảo tất cả vùng dữ liệu cần tổng hợp đã được thêm bằng Add.
• Kiểm tra lại điểm bắt đầu và kết thúc của từng vùng dữ liệu.

  

Dữ liệu nguồn thay đổi nhưng bảng tổng hợp không cập nhật

Nguyên nhân: Khi thực hiện lệnh CONSOLIDATE, người dùng không bật tùy chọn Create links to source data, vì vậy kết quả tổng hợp không được liên kết với dữ liệu nguồn.

Cách khắc phục: Tích Create links to source data khi thiết lập CONSOLIDATE nếu cần duy trì liên kết với dữ liệu nguồn. Lưu ý, Microsoft cho biết không thể tạo các liên kết này khi vùng nguồn và vùng đích nằm trên cùng một worksheet.

Lưu ý quan trọng khi sử dụng hàm CONSOLIDATE trong Excel

Để quá trình tổng hợp dữ liệu chính xác và phù hợp với mục đích sử dụng, người dùng nên lưu ý một số điểm sau:

• Chuẩn hóa dữ liệu trước khi tổng hợp: Thống nhất tên danh mục, tiêu đề và cách đặt nhãn giữa các bảng để CONSOLIDATE nhận diện đúng dữ liệu tương ứng.
• Xác định đúng mục đích xử lý: CONSOLIDATE phù hợp khi cần tổng hợp giá trị như tính tổng, trung bình, đếm, tìm giá trị lớn nhất hoặc nhỏ nhất. Nếu mục tiêu là nối các dòng dữ liệu từ nhiều bảng thành một danh sách dài, nên sử dụng công cụ khác như Power Query.
• Không nhầm CONSOLIDATE với xóa dữ liệu trùng lặp: CONSOLIDATE Excel không phải công cụ Remove Duplicates. “Gộp dữ liệu trùng nhau” ở đây là nhóm những dữ liệu có cùng nhãn và thực hiện phép tính tổng hợp trên các giá trị tương ứng, chứ không đơn thuần xóa các dòng bị trùng.
• Kiểm tra kết quả trước khi sử dụng: Sau khi thực hiện lệnh CONSOLIDATE, nên đối chiếu một số giá trị với dữ liệu nguồn trước khi đưa kết quả vào báo cáo hoặc phân tích.
• Lựa chọn công cụ phù hợp: Hàm CONSOLIDATE trong Excel phù hợp với nhu cầu tổng hợp nhanh dữ liệu theo vị trí hoặc theo nhãn. Với dữ liệu lớn, cần làm sạch, biến đổi hoặc kết hợp nhiều nguồn phức tạp, Power Query thường phù hợp hơn.

>>>Xem thêm về hàm khác tại: Tổng hợp các hàm Excel cơ bản dùng trong kế toán

Câu hỏi thường gặp về CONSOLIDATE Excel

• Tại sao CONSOLIDATE trong Excel không hoạt động?

CONSOLIDATE có thể không tạo được kết quả khi vùng Reference được chọn sai, dữ liệu nguồn không phù hợp hoặc vùng đặt kết quả chồng lên vùng nguồn. Hãy kiểm tra lại vị trí ô đích và toàn bộ vùng dữ liệu trong All references.

• Tại sao CONSOLIDATE không gộp các dữ liệu cùng tên?

Nguyên nhân thường gặp là các nhãn không hoàn toàn giống nhau, chẳng hạn có khoảng trắng hoặc cách viết khác nhau. Khi sử dụng hàm CONSOLIDATE theo nhãn, nên chuẩn hóa tên danh mục trước khi tổng hợp.

• Tại sao Excel báo “Source reference overlaps destination area”?

Lỗi này xuất hiện khi vùng dữ liệu nguồn và vùng đặt kết quả bị chồng lấn. Cách xử lý là chọn một ô đích nằm ngoài vùng nguồn hoặc đặt bảng kết quả trên một sheet riêng.

• CONSOLIDATE có tự cập nhật khi dữ liệu nguồn thay đổi không?

Nếu chỉ CONSOLIDATE thông thường, bảng kết quả không tự động liên kết với dữ liệu nguồn. Khi cần tạo liên kết, người dùng có thể chọn Create links to source data trong hộp thoại CONSOLIDATE.

• Có thể CONSOLIDATE dữ liệu khi thứ tự các dòng khác nhau không?

Có. Nếu các bảng có cùng nhãn nhưng khác thứ tự, hàm CONSOLIDATE trong Excel có thể tổng hợp theo nhãn. Khi thiết lập, cần lựa chọn Top row, Left column hoặc cả hai tùy cấu trúc dữ liệu.

• Tại sao kết quả CONSOLIDATE bằng 0 hoặc không đúng?

Hãy kiểm tra dữ liệu nguồn có thực sự ở dạng số hay không, đồng thời kiểm tra Function đã chọn đúng phép tính và Reference có bao phủ đúng vùng dữ liệu cần tổng hợp hay chưa.

• CONSOLIDATE có xóa các dòng dữ liệu trùng nhau không?

Không. CONSOLIDATE Excel không phải Remove Duplicates. CONSOLIDATE dùng để tổng hợp giá trị của những dữ liệu tương ứng, chẳng hạn cộng doanh thu của các dòng có cùng tên sản phẩm; nó không đơn thuần xóa những dòng bị trùng.

>>>Xem thêm hàm có liên quan: Cách tính tổng trong Excel có điều kiện: Hàm SUMIF và SUMIFS

Kết luận 

Hàm CONSOLIDATE trong Excel là công cụ hữu ích giúp người dùng tổng hợp và xử lý dữ liệu từ nhiều vùng hoặc nhiều trang tính một cách nhanh chóng. Khi nắm rõ cách sử dụng CONSOLIDATE trong Excel, bạn có thể lựa chọn phép tính phù hợp, tổng hợp dữ liệu theo vị trí hoặc theo nhãn và hạn chế các thao tác thủ công khi làm việc với bảng tính. Đặc biệt, CONSOLIDATE Excel còn hỗ trợ cách dùng CONSOLIDATE gộp dữ liệu trùng nhau trong Excel, giúp việc tổng hợp số liệu trở nên thuận tiện và chính xác hơn. 

Nếu bạn cần tư vấn thêm về Excel hoặc các giải pháp Microsoft 365, hãy liên hệ MHG qua hotline 024.9999.7777 để được hỗ trợ nhanh chóng.