Nhiều người nghĩ rằng không có sẵn một lệnh hợp nhất dữ liệu Excel trong Google Sheets. Tất nhiên, không có một lệnh như vậy. Nhưng bạn có thể hợp nhất dữ liệu từ nhiều Sheets bằng cách sử dụng một công thức trong Google Sheets.
Đừng xem thường Google Sheets! Có một chức năng mạnh mẽ trong Google Sheets để thực hiện việc hợp nhất dữ liệu một cách tốt hơn! Bạn có biết chức năng/công thức đó là gì không?
Tôi đang nói về công thức Query. Trong công thức này, bạn có thể chỉ định phạm vi dữ liệu trong nhiều Sheets theo hai cách.
Nhập thủ công (Hardcoded): – Sử dụng khi bạn có ít Sheets để kết hợp.
Sử dụng một Hàm được Đặt tên Tùy chỉnh: – Sử dụng khi bạn có nhiều Sheets để kết hợp.
Tôi chỉ sử dụng các chức năng nguyên thủy, không sử dụng Apps Script hoặc bất kỳ plugin bên thứ ba nào, để hợp nhất dữ liệu từ nhiều Sheets.
Việc hợp nhất dữ liệu theo cách này không giới hạn trong Sheets trong một tệp duy nhất. Bạn có thể sử dụng Sheets từ các tệp khác nhau bằng cách nhập chúng.
Hiểu về việc hợp nhất dữ liệu từ nhiều Sheets
Tôi sẽ kết hợp dữ liệu từ nhiều Sheets (nhiều tab trong một workbook hoặc file) thành một Sheet duy nhất và sau đó tóm tắt nó.
Vậy về việc hợp nhất dữ liệu trong hai workbook khác nhau trong Google Sheets?
Nếu đó là lý do, bạn cần sử dụng hàm IMPORTRANGE để trước tiên nhập dữ liệu vào một Sheet chính.
Tôi sẽ bỏ qua phần này trong hướng dẫn này để tránh gây nhầm lẫn không cần thiết.
Ưu điểm của việc sử dụng một công thức (Công thức Query)
Dưới đây là một số lợi ích của việc hợp nhất dữ liệu từ nhiều Sheets bằng một công thức trong Google Sheets:
- Hợp nhất dữ liệu mà không cần bất kỳ Script nào đồng nghĩa với hiệu suất Bảng tính tốt hơn.
- Công thức nhanh hơn và đáng tin cậy hơn nếu chúng ta viết chúng đúng cách.
- Nếu chúng ta sử dụng bất kỳ plugin nào (tôi không biết về bất kỳ plugin nào như vậy khi viết bài này), chúng ta không thể đảm bảo tính sẵn có của plugin đó luôn luôn. Ngay cả khi bạn tìm thấy một plugin, hãy thêm nó sau khi đọc đánh giá từ người dùng.
- Không cần trả tiền thuê một nhà phát triển để viết mã Apps Script cho bạn.
- Khi sử dụng một công thức để hợp nhất dữ liệu, bạn có nhiều linh hoạt hơn. Dữ liệu đã hợp nhất sẽ tự động được cập nhật khi chúng ta thay đổi dữ liệu nguồn.
Cách hợp nhất dữ liệu từ nhiều Sheets bằng một công thức trong Google Sheets
Vui lòng làm theo hướng dẫn từng bước dưới đây.
Dưới đây là bốn Sheets (screenshot). Hai Sheets đầu tiên, ý tôi là tab sheet, chứa dữ liệu mẫu chúng ta sẽ kết hợp trong sheet thứ ba và tóm tắt trong sheet thứ tư.
Chúng ta có thể kết hợp và tóm tắt dữ liệu bằng một công thức duy nhất. Nhưng để đơn giản hóa các bước, tôi sẽ thực hiện nó riêng biệt.
Lưu ý: – Bạn sẽ có chi tiết để kết hợp và tóm tắt dữ liệu bằng một câu truy vấn duy nhất ở phần cuối của hướng dẫn này.
Vậy có hai bước liên quan – hợp nhất dữ liệu và kết hợp. Ta có thể sử dụng sheet thứ ba và thứ tư cho điều đó. Tôi sẽ chia hướng dẫn dưới đây dựa trên điều đó.
Hai sheet đầu tiên chứa một số dữ liệu mẫu. Xem bên dưới.
Tên của sheet đầu tiên của tôi là junesheet, và đây là dữ liệu trong nó.
Bây giờ hãy xem dữ liệu trong Sheet thứ hai có tên julysheet.
Bạn có thể mở tệp mẫu của tôi dưới đây để xem dữ liệu và các công thức của tôi.
Cách kết hợp dữ liệu từ nhiều Sheets bằng một công thức trong Google Sheets
Bây giờ hãy xem cách kết hợp hai Sheets trên thành một Sheet duy nhất trong cùng một tệp Google Sheets. Ý tôi là trang thứ ba được đặt tên là Kết hợp.
Dưới đây là công thức Query để kết hợp dữ liệu từ hai Sheets trên thành một sheet duy nhất.
=query({junesheet!A2:H5;julysheet!A2:H5},"Chọn * từ cột1 không rỗng")
Công thức này dùng cho dữ liệu mẫu của chúng ta ở trên. Bạn có thể sử dụng nó như vậy, nhưng thay đổi phù hợp với dữ liệu trên bảng tính của bạn. Những thay đổi bạn có thể cần thực hiện có thể là như sau.
- Thay đổi phạm vi A2:H5 thành phạm vi dữ liệu thực tế của bạn.
- Thay đổi tên sheet thành tên sheet thực tế của bạn.
- Tôi đã sử dụng hai Sheets để kết hợp. Giả sử có thêm một Sheet khác có tên là augsheet để bao gồm. Hãy đặt một dấu chấm phẩy và nhập tên sheet thứ ba cùng với phạm vi.
Ví dụ:
{junesheet!A2:H5;julysheet!A2:H5;augsheet!A2:H5}Quan trọng: – Chỉ kết hợp các loại dữ liệu tương tự (tránh trộn các loại dữ liệu trong một cột duy nhất). Không bao giờ kết hợp một cột văn bản với một cột ngày hoặc cột số.
Trong bảng tính của bạn, nếu cột A chứa các chuỗi văn bản trong sheet đầu tiên và các số trong sheet thứ hai, đừng kết hợp chúng. Chúng sẽ tạo thành loại dữ liệu kết hợp.
Điều này trái với việc hợp nhất dữ liệu bằng Query. Câu truy vấn sẽ trả về kết quả không chính xác khi có loại dữ liệu pha trộn trong các cột.
Phạm vi vô hạn trong việc hợp nhất
Nếu bạn muốn, bạn có thể sử dụng phạm vi vô hạn như A2: H trong công thức. Sau đó công thức sẽ như sau.
=query({junesheet!A2:H;julysheet!A2:H},"Chọn * từ cột 1 không rỗng")Bạn có nhiều Sheets để kết hợp không? Sau đây là một vài hàm được đặt tên tùy chỉnh để thử.
- REF_SHEET_TABS: Tham chiếu danh sách tên tab trong câu truy vấn trong Google Sheets.
- COPY_TO_MASTER_SHEET: Kết hợp Dữ liệu trong Nhiều Tab trong Google Sheets.
Chúng ta muốn tóm tắt hoặc hợp nhất dữ liệu chúng ta đã kết hợp từ nhiều Sheets. Dưới đây là các bước. Trước tiên, hãy xem dữ liệu đã được hợp nhất bên dưới.
Tóm tắt hoặc hợp nhất dữ liệu từ nhiều Sheets bằng công thức Query trong Google Sheets
Phần dưới đây sử dụng công thức Query. Bạn cũng có thể làm điều này bằng cách sử dụng Pivot Table.
Chỉ bằng cách sử dụng một công thức Query nữa trong Sheet cuối cùng có tên “Consolidate”, chúng ta có thể đạt được kết quả cần thiết.
=query(Combined!A1:H10,"Chọn D, tổng H where A không rỗng nhóm theo D")
Tôi đã sử dụng nhóm Query để thực hiện tổng hợp Sum trong công thức hợp nhất dữ liệu này.
Trong dữ liệu mẫu của chúng tôi, cột D chứa mô tả mục. Tôi đã tóm tắt dữ liệu dựa trên trường mục này.
Khi cùng một mục xuất hiện nhiều lần, công thức này sẽ kết hợp nó thành một hàng duy nhất và cộng tổng các giá trị của nó trong cột H.
Dữ liệu đã hợp nhất sẽ nhìn như bên dưới.
Đó là tất cả. Tôi hy vọng bạn đã thích hướng dẫn của tôi và đã hiểu cách hợp nhất dữ liệu từ nhiều Sheets bằng một công thức trong Google Sheets.
Như tôi đã hứa ở đầu, đây là một công thức duy nhất kết hợp và tóm tắt dữ liệu.
=query({junesheet!A2:H;julysheet!A2:H},"select Col4, sum(Col8) where Col1 is not null group by Col4")Làm gì nếu số cột khác nhau trên mỗi tab?
Thường thì sẽ không xảy ra. Nhưng bất kỳ trường hợp nào, nếu số cột trong phạm vi dữ liệu trên mỗi tab khác nhau, phương pháp trên không hoạt động.
Bạn có thể thử công thức dưới đây để tạo trước sự kết hợp và sau đó hợp nhất nó.
Google Sheets: Kết hợp Hai Bảng có Số Cột Khác nhau trong một Query.
Hàm Query rất tiện ích nếu bạn sử dụng nó một cách đúng đắn. Vì vậy, hãy dành thời gian của bạn và học cách sử dụng hàm Query trên.
