Cách sử dụng hàm consolidate trong excel để hợp nhất nhiều trang tính

Hàm Consolidate được biết đến là một trong những hàm thống kê rất hiệu quả trên excel. Sử dụng Consolidate bạn có thể dễ dàng tính toán cực nhanh chóng các phép tính tổng (sum), trung bình (evergage), max, min,... trong bảng tính excel. Sau đây, Unica Hướng dẫn cách sử dụng hàm Consolidate trong excel để hợp nhất nhiều trang tính.

Hàm consolidate trong excel là gì?

Consolidate excel là hàm hợp nhất các ô trong excel, trong tiếng anh Consolidate còn có nghĩa là Consolidate meaning tức là củng cố, thống nhất, hợp nhất. Dùng hàm Consolidate bạn có thể hợp nhất các ô, các cột hoặc các trang tính lại với nhau.

Hàm Consolidate thường được ứng dụng nhiều ở trong lĩnh vực kinh doanh, bán hàng, giúp việc xử lý dữ liệu lớn từ phần mềm bán hàng chuyển qua được đơn giản, dễ dàng và hiệu quả hơn.

Hàm consolidate trong excel

Sử dụng Consolidate bạn có thể hợp nhất dữ liệu trong excel, tạo ra một bảng báo cáo tổng hợp tất cả các trường hợp được lựa chọn chỉ bằng vài thao tác rất đơn giản. Excel thường được sử dụng kèm với các hàm khác để việc tính toán của bạn trở nên thuận tiện hơn.

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

Chức năng consolidate trong excel

Hàm Consolidate trong Excel là một hàm giúp bạn hợp nhất dữ liệu từ nhiều trang tính hoặc bảng tính khác nhau vào một trang tính hoặc bảng tính duy nhất. Hàm này có thể được sử dụng để hợp nhất dữ liệu theo nhiều cách khác nhau, bao gồm:

• Hợp nhất dữ liệu theo cột.

• Hợp nhất dữ liệu theo hàng.

• Hợp nhất dữ liệu theo vùng.

• Hợp nhất dữ liệu theo tiêu chí.

>>> Xem thêm: Hướng dẫn cách tạo đường chéo trong ô excel chi tiết nhất

Ví dụ: Nếu bạn đang có một báo cáo lương hàng tháng ở nhiều sheet khác nhau, bạn có thể sử dụng học cách sử dụng hàm Consolidate trong excel để tổng hợp tất cả các file riêng đó lại thành 1 file chung. Điều này giúp việc tính toán lương được dễ dàng và thuận tiện hơn.

Chức năng consolidate trong excel

Hướng dẫn sử dụng Consolidate trong excel

Sau khi đã biết rõ hàm Consolidate trong excel là gì? Chức năng lệnh consolidate như thế nào? Phần tiếp theo bài viết Unica sẽ hướng dẫn cách sử dụng consolidate trong excel, hãy tham khảo nhé:

Hợp nhất các trang tính Excel với một thứ tự các cột khác nhau

Cách sử dụng consolidate để tạo tổng hợp đầu tiên mà Unica muốn chia sẻ với bạn đó là dùng Consolidate hợp nhất các trang tính trong excel với một thứ tự các cột khác nhau. Cách sử dụng hàm Consolidate trong excel để hợp nhất các trang tính theo thứ tự cột vô cùng đơn giản và dễ thực hiện.

Các bước thực hiện: 

Bước 1: Trong Data chọn Combine Sheet.

Chọn Combine Sheet

Bước 2: Và dữ liệu sẽ được kết hợp hoàn hảo bởi các tiêu đề cột:

Kết quả sau thực hiện

>>> Xem thêm: Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ

Cách sử dụng hàm consolidate trong excel để hợp nhất dữ liệu từ nhiều bảng tính trong một bảng tính

Xét ví dụ sau đây: Bạn đang phải làm báo cáo hợp nhất doanh thu từ các văn phòng chi nhánh của công ty. Ảnh dưới đây là bảng cần hợp nhất cấu trúc dữ liệu nhưng số hàng và số cột lại khác nhau.

Bảng có số hàng và số cột khác nhau

Để hợp nhất dữ liệu với nhau bạn thực hiện như sau:

Bước 1: Trong Data chọn Consolidate

Chú ý: Bạn nên hợp nhất dữ liệu vào các trang trống. Nếu bảng tính của bạn đã có dữ liệu, bạn cần chọn sang bảng có dữ liệu trống, đảm bảo có đủ không gian, các hàng và cột trống để chứa dữ liệu được hợp nhất.

Chọn Consolidate

Bước 2: Sau khi hộp thoại Consolidate xuất hiện, bạn tiếp tục thực hiện các thao tác lần lượt như sau: 

Tại hộp Function trên cùng bạn chọn hàm muốn sử dụng để hợp nhất, trong ví dụ này Unica sẽ chọn hàm Sum.

Tiếp theo theo hộp Reference box, bạn nhấp vào biểu tượng thu gọn ở cuối cùng hàng, sau đó chọn vùng ở trang đầu tiên => Chọn nút Add để thêm các vùng khác. Cứ thực hiện bước này cho tất cả các vùng mà bạn muốn hợp nhất.

Thao tác trên hộp thoại Consolidate

Bước 3: Tiếp theo là đến bước update settings. Cũng trong hộp thoại Consolidate, bạn có thể chọn bất kỳ một tuỳ chọn nào trong các tuỳ chọn sau đây.

Nếu bạn muốn sao chép tên hàng và / hoặc tên cột từ các trang tính gốc vào trang hợp nhất bạn có thể chọn Top row và/ hoặc Left column phía dưới Use labels in.

Nếu bạn muốn dữ liệu hợp nhất cập nhật tự động khi dữ liệu nguồn thay đổi thì bạn chọn Create links to source data check. Trong trường hợp này, Excel sẽ tạo liên kết tới bảng tính nguồn của bạn và thể hiện như ảnh sau:

Ảnh thể hiện thao tác tạo liên kết tới bảng tính nguồn

Cách dùng hàm CONSOLIDATE hợp nhất hai trang tính Excel thành một trong các cột chính

Nếu bạn đang tìm kiếm một cách nhanh chóng để khớp và hợp nhất dữ liệu từ hai bảng tính, thì bạn có thể sử dụng hàm Excel VLOOKUP. Chi tiết cách thực hiện như sau:

Bạn sử dụng hàm VLOOKUP theo cú pháp: =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

Trong đó:

• Lookup_value: Đây là giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính đều được.

• Table_array: Đây là bảng giới hạn để dò tìm.

• Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính theo thứ tự từ trái qua phải.

• Range_lookup: Tìm kiếm tương đối hoặc chính xác đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.

Sử dụng hàm CONSOLIDATE để hợp nhất nhiều bảng tính

>>> Xem thêm: Cách sử dụng hàm LOGARIT trong excel có ví dụ minh họa

Ví dụ cách sử dụng Consolidate là gì?

Để hiểu rõ hơn cách sử dụng hàm Consolidate trong excel như thế nào, sau đây Unica sẽ chia sẻ cho bạn một ví dụ cụ thể:

Ví dụ: Bạn cần phải tính tổng số lượng và tổng tiền với từng mã/ từng mặt hàng tương ứng.

Bảng tính có nhiều mã hàng trùng lặp

Cách thực hiện như sau: Đặt con trỏ chuột tại vị trí bạn muốn tạo báo cáo. Sau đó nhấp chuột vào Date => Chọn Consolidate.

Chọn Consolidate

Tại hộp thoại Consolidate xuất hiện bạn làm theo các bước tương ứng gồm: 

• Phần Function chọn Sum để tính tổng.
• Phần Reference gõ vào vùng tham chiếu, chọn phạm vi để Consolidate tính toán bằng cách nhấp chuột vào ô Reference và di chuột để chọn khối dữ liệu..

Kế tiếp, chúng ta nhấn Add đưa địa chỉ tham chiếu vào ô All reference để áp dụng.

Thao tác sử dụng Consolidate trong excel

Chú ý: Bạn đừng quên chọn dấu kiểm tại hai mục Top row và Left column trong vùng Use labels để báo cáo hiện tiêu đề dòng, cột (nếu không chọn dấu kiểm 2 mục này dòng, cột tiêu sẽ bị ẩn mà chỉ có số liệu).

Cuối cùng, nhấn OK để xem báo cáo là hoàn thành. Kết quả Consolidate sẽ cộng gộp chính xác số lượng và thành tiền của các mặt hàng.

Kết quả hoàn thành quá trình sử dụng Consolidate trong excel

Kết luận

Trên đây Unica đã hướng dẫn tới bạn các cách sử dụng hàm Consolidate trong excel đơn giản và nhanh chóng. Bạn hãy tham khảo để biết cách sử dụng nhanh chóng nhất nhé. Chúc các bạn thành công. Bạn hãy khám phá thêm khóa học Excel online của Unica để nâng kiến thức Excel lên một tầm mới.

>> Xem thêm: Trọn bộ tin học văn phòng cơ bản từ giảng viên hàng đầu học phí ưu đãi chỉ từ 99K