Trong bài viết này, chúng ta sẽ tìm hiểu cách kết hợp dữ liệu một cách linh hoạt từ nhiều tab trong Google Sheets. Để giải quyết vấn đề này, chúng ta sẽ sử dụng một công thức và một hàm tùy chỉnh để kết hợp dữ liệu theo chiều dọc từ nhiều tab.
Thách thức trong việc kết hợp dữ liệu từ nhiều tab
Google Sheets và các ứng dụng tương tự hỗ trợ tổ chức dữ liệu trong các tab trong một bảng tính, giúp việc tổ chức dữ liệu trở nên dễ dàng hơn. Tuy nhiên, điều đó cũng đặt ra một thách thức.
Ví dụ, nếu bạn có dữ liệu hàng tháng được tổ chức trong các tab khác nhau trong Google Sheets, việc tạo một bảng tổng hợp có thể đòi hỏi bạn phải sao chép và dán dữ liệu này vào một tab chính. Hoặc bạn có thể cần chỉ định từng phạm vi một trong công thức VSTACK như sau:
=VSTACK(Sheet1!A2:C5, Sheet2!A2:C5, Sheet3!A2:C5)Vậy có cách nào để chỉ định phạm vi dưới dạng danh sách và sử dụng nó một cách linh hoạt không?
Để giải quyết việc sao chép dán hoặc chỉ định từng phạm vi dữ liệu, chúng tôi giới thiệu công thức COPY_TO_MASTER_SHEET.
Đặc điểm của công thức kết hợp dữ liệu từ nhiều tab một cách linh hoạt
Công thức (cũng như hàm tùy chỉnh) kết hợp dữ liệu từ nhiều tab trong Google Sheets và cung cấp các đặc điểm sau:
Kết hợp dọc và tuần tự: Công thức sắp xếp dữ liệu dọc và tuần tự, tạo thành một mảng lớn. Nghĩa là dữ liệu của tab thứ hai sẽ nằm dưới dữ liệu của tab thứ nhất, dữ liệu của tab thứ ba sẽ nằm dưới dữ liệu của tab thứ hai, và cứ tiếp tục như vậy.
Bảo tồn các kiểu dữ liệu: Cả hàm COPY_TO_MASTER_SHEET và công thức không sử dụng hàm SPLIT, từ đó giữ nguyên các kiểu dữ liệu trong kết quả.
Khuyến nghị sử dụng phạm vi đóng: Chúng tôi khuyên bạn nên sử dụng phạm vi đóng trong công thức. Ví dụ, hãy sử dụng A1:C1000, không phải A1:C, vì việc kết hợp dữ liệu theo chiều dọc có thể dẫn đến nhiều dòng trống dưới dữ liệu của mỗi tab. Nếu bạn muốn sử dụng phạm vi mở, hãy bao hàm hàm tên hoặc công thức của chúng tôi bằng QUERY hoặc FILTER để loại bỏ các dòng trống.
Yêu cầu tiên quyết
Giả sử bạn muốn kết hợp dữ liệu động từ Sheet1, Sheet2 và Sheet3 vào một tab thứ tư mang tên Master. Công thức yêu cầu tất cả tên tab được liệt kê trong một danh sách trong tab Master.
Bạn có thể nhập tên tab thủ công trong cột A của tab Master hoặc sử dụng một Google Apps Script, mà bạn có thể lấy từ webapps.stackexchange.com.
Để thêm script, làm theo các bước sau:
- Mở menu “Extensions” trong Google Sheets của bạn và chọn “Apps Script”.
- Xóa văn bản mặc định. Sao chép và dán mã Apps Script từ trang được đề cập ở trên vào đó. Sau đó, lưu dự án bằng cách chọn biểu tượng floppy.
Lưu ý: Ngoài ra, bạn có thể nhận được mã Apps Script bằng cách tạo một bản sao của bảng mẫu của tôi được cung cấp một vài đoạn văn phía dưới.
Sử dụng công thức sau trong ô A1 của tab Master để lấy tất cả tên tab.
=sheetnames()Khi sử dụng hàm COPY_TO_MASTER_SHEET hoặc công thức của chúng tôi để kết hợp dữ liệu từ các tab “Sheet1”, “Sheet2” và “Sheet3” vào tab “Master”, đừng chỉ định A1:A. Hãy chỉ định A2:A.
Điều này bởi vì ô A1 sẽ chứa tên “Master”, mà chúng ta không muốn bao gồm trong quá trình kết hợp dữ liệu.
Kết hợp dữ liệu động từ nhiều tab một cách linh hoạt: Công thức
Chúng ta sẽ kết hợp dữ liệu từ Sheet1, Sheet2 và Sheet3 trong phạm vi A2:C5 vào tab Master.
Dưới đây là kết quả dự kiến sau khi kết hợp dữ liệu từ tab 1 đến tab 3 một cách linh hoạt.
Để biết về dữ liệu mẫu, hàm tên và công thức, hãy nhấn vào nút bên dưới để sao chép bảng.
Trong ví dụ trên (vui lòng tham khảo hình ảnh bên trên), tôi đã sử dụng công thức sau trong ô B2:
=REDUCE( TOCOL(, 1), TOCOL(A2:A, 1), LAMBDA(a, v, IFERROR(VSTACK(a, INDIRECT(v&"!"&"A2:C5")))) )Trong đó:
- A2:A là danh sách các tên tab.
- “A2:C5” là phạm vi trong mỗi tab để kết hợp dọc.
Bây giờ, hãy hiểu cách công thức kết hợp động các tab một cách linh hoạt theo chiều dọc trong Google Sheets. Dưới đây là phân tích công thức.
Lưu ý: Khi bạn sử dụng phạm vi mở trong công thức, ví dụ như “A2:C” thay vì “A2:C5”, hãy bao gói công thức bằng QUERY như sau:
=QUERY(..., "SELECT * WHERE Col1 IS NOT NULL", 0)Phân tích công thức
Công thức sử dụng hàm REDUCE để kết hợp dữ liệu từ nhiều tab theo chiều dọc. Dưới đây là cú pháp:
REDUCE(initial_value, array_or_range, lambda)Trong công thức:
- initial_value: TOCOL(, 1) – Hàm TOCOL trả về một ô trống nếu được chỉ định là TOCOL(, 0). Bằng cách thay đổi 0 thành 1, nó loại bỏ ô trống đó, đáp ứng yêu cầu initial_value cho REDUCE. Nó giống như không trả về gì cả.
- array_or_range: TOCOL(A2:A, 1) – Nó trả về danh sách các tên tab, loại bỏ các ô trống.
Đây là phần Lambda function trong công thức:
- a: tên của bộ cộng tác.
- v: từng giá trị (tên tab) từ array_or_range.
Hàm REDUCE lặp qua từng tên tab trong array_or_range, sử dụng hàm INDIRECT(v&”!A2:C5″). Trong mỗi lần lặp, v biểu thị cho các tab Sheet1, Sheet2 và Sheet3.
Kết quả tại mỗi bước được lưu trong bộ cộng tác, sau đó được xếp chồng theo chiều dọc bằng cách sử dụng VSTACK như VSTACK(a, …). Điều này tạo ra quy trình REDUCE để kết hợp dữ liệu từ nhiều tab theo chiều dọc.
Kết hợp dữ liệu động từ nhiều tab một cách linh hoạt: Hàm tùy chỉnh
Công thức trên đã sẵn sàng để được chuyển thành một hàm tùy chỉnh.
Bạn chỉ cần sao chép và dán nó vào phần định nghĩa công thức trong menu Dữ liệu > Các hàm được đặt tên > Thêm các hàm mới. Thay thế các tham chiếu phạm vi ô bằng tên và thêm các tên đó vào placeholder đối số.
Tôi đã làm điều đó cho bạn. Bạn chỉ cần nhập nó vào bảng tính của mình để sử dụng.
Cú pháp:
COPY_TO_MASTER_SHEET(list, array)Đối số:
- list: Ô hoặc phạm vi ô chứa danh sách tab để kết hợp.
- array: Phạm vi dữ liệu để kết hợp được chỉ định trong dấu ngoặc kép.
Ví dụ:
Bạn có thể thay thế công thức trước đó ở Master!B2 bằng hàm COPY_TO_MASTER_SHEET của tôi như sau.
=COPY_TO_MASTER_SHEET(A2:A,"A2:C5")Khi bạn muốn kết hợp nhiều tab động với phạm vi mở, hãy sử dụng công thức dưới đây kèm theo câu lệnh QUERY:
=QUERY(COPY_TO_MASTER_SHEET(A2:A,"A2:C"), "SELECT * WHERE Col1 IS NOT NULL", 0)Với sự giúp đỡ của hàm tên này, bất kỳ ai cũng có thể nhanh chóng kết hợp dữ liệu từ nhiều tab vào một tab chính trong Google Sheets.
Làm thế nào để sao chép hàm tên COPY_TO_MASTER_SHEET
Dưới đây là các bước nhanh chóng.
- Để sử dụng một hàm được đặt tên, hãy nhập nó vào bảng mà bạn muốn nó hoạt động.
- Đầu tiên, hãy sao chép bảng mẫu của tôi ở trên.
- Truy cập File > Cài đặt và trong mục “Locale”, hãy chọn cùng một quốc gia như bảng mà bạn muốn sử dụng hàm của tôi.
- Sau đó, mở bảng mà bạn muốn sử dụng hàm COPY_TO_MASTER_SHEET.
- Mở menu Dữ liệu trong bảng đó. Chọn Các hàm được đặt tên > Nhập hàm.
- Chọn bảng đã được sao chép (bạn có thể tìm kiếm theo tên bảng) và chọn Chèn. Làm theo các hướng dẫn trên màn hình sau đó.
Bạn sẽ tìm thấy mã để nhập.
Tài nguyên hữu ích
Bài viết này đã giới thiệu về một công thức cũng như một hàm tùy chỉnh để kết hợp dữ liệu một cách linh hoạt từ nhiều tab trong một bảng tính Google Sheets. Nếu bạn quan tâm đến việc kết hợp theo chiều ngang, bạn có thể tìm thấy hướng dẫn liên quan và một số mẹo bổ sung từ các nguồn tài nguyên dưới đây:
- Consolidate Data from Multiple Sheets Using a Formula in Google Sheets
- Consolidate Only the Last Row in Multiple Sheets in Google Sheets
- How to Combine Multiple Sheets in Importrange and Control Via Drop-Down
- Dynamically Combine Multiple Sheets Horizontally in Google Sheets
- SUMIF Across Multiple Sheets in Google Sheets
- Vlookup Across Multiple Sheets in Google Sheets
- How to Include Future Sheets in Formulas in Google Sheets
