Giả sử bạn có tài liệu Google Sheet với nhiều trang tính có cấu trúc giống nhau (số lượng và tên các cột). Bạn cần kết hợp dữ liệu từ các trang tính này thành một tổng quan chính. Trong Google Sheet, có các tùy chọn linh hoạt để tự động hóa quy trình và không cần phải làm thủ công. Trong hướng dẫn này, chúng tôi sẽ giới thiệu một số chức năng, cũng như giải pháp không có công thức, để hợp nhất các trang tính thành một.
Xem nhanh
Kết hợp dữ liệu từ hai hoặc nhiều trang tính trong một tài liệu Google Sheet
Có một tài liệu Google Sheet với hai trang tính: Invoices 2019 và Invoices 2020. Mỗi trang tính này có tám cột ( A:H) cùng tên. Hàng đầu tiên chứa tiêu đề cột. Nhiệm vụ của chúng ta là hợp nhất dữ liệu theo chiều dọc từ các trang tính này thành một.
Kết hợp các trang tính thành một bằng FILTER
FILTER là một chức năng của Google Sheet để lọc ra các tập hợp con dữ liệu từ một phạm vi dữ liệu được chỉ định theo một điều kiện đã cho.
Để kết hợp các trang tính bằng FILTER, hãy áp dụng công thức sau:
={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0);
FILTER({sheet#2-range},LEN({sheet#2-range-first-column})>0);...}
{sheet#1-range}– phạm vi dữ liệu từ trang tính đầu tiên bao gồm hàng tiêu đề.{sheet#2-range}– phạm vi dữ liệu từ trang tính thứ hai không có hàng tiêu đề.{sheet#1-range-first-column}– cột đầu tiên của phạm vi dữ liệu từ trang tính đầu tiên.{sheet#2-range-first-column}– cột đầu tiên (không có hàng tiêu đề) của phạm vi dữ liệu từ trang tính thứ hai.
Điều kiện LEN (LEN({sheet#1-range-first-column})>0) trong công thức FILTER là bắt buộc để bỏ qua các hàng trống trong phạm vi. Nếu không, công thức cũng sẽ thêm các hàng trống khi hợp nhất các hàng với dữ liệu.
Trong trường hợp này, công thức sẽ như sau:
={ FILTER(‘Invoices 2019’!A1:H, LEN(‘Invoices 2019’!A1:A) > 0); FILTER(‘Invoices 2020’!A2:H, LEN(‘Invoices 2020’!A2:A) > 0)}
Bằng cách này, bạn có thể hợp nhất nhiều hơn hai trang tính với nhau. Tất cả những gì bạn cần là thêm các trang tính có liên quan và phạm vi của chúng trong công thức.
Lưu ý: Hãy chắc chắn để xác định phạm vi dữ liệu từ bảng thứ hai (và những người tiếp theo) mà không có hàng tiêu đề như A2: H thay vì A1: H . Nếu không, hàng tiêu đề cũng sẽ được nhập. Ví dụ,
={ FILTER(‘Invoices 2019’!A1:H, LEN(‘Invoices 2019’!A1:A) > 0); FILTER(‘Invoices 2020’!A1:H, LEN(‘Invoices 2020’!A1:A) > 0)}
Kết hợp các trang tính thành một với QUERY
QUERY là một chức năng của Google Sheet để tìm nạp dữ liệu dựa trên các tiêu chí cụ thể. Ngoài ra, bạn có thể sửa đổi format, thay đổi thứ tự của các cột và thực hiện các thao tác khác với dữ liệu đã nhập.
Để kết hợp các trang tính bằng QUERY, hãy áp dụng công thức sau:
=QUERY({{sheet#1-range};{sheet#2-range};...,"Select * where Col1 is not null"){sheet#1-range}– phạm vi dữ liệu từ trang tính đầu tiên bao gồm hàng tiêu đề.{sheet#2-range}– phạm vi dữ liệu từ trang tính thứ hai không có hàng tiêu đề.
Trong trường hợp của chúng tôi, công thức sẽ như sau: =query({‘Invoices 2019′!A1:H;’Invoices 2020’!A2:H},”Select * where Col1 is not null”)
Bạn có thể hợp nhất nhiều hơn hai trang tính với nhau bằng QUERY nếu bạn thêm các trang tính có liên quan và phạm vi của chúng trong công thức. Đừng quên rằng phạm vi từ trang thứ hai và các trang tiếp theo phải được chỉ định mà không có hàng tiêu đề giống như với hàm FILTER ở trên.
Lưu ý rằng các công thức QUERY và FILTER được đề cập chỉ hợp nhất các trang tính với cùng một số cột.
Kết hợp các trang tính thành một và hợp nhất dữ liệu với QUERY
Chúng ta đã kết hợp thành công trang tính với invoices data. Tuy nhiên, sẽ thật tuyệt nếu chúng ta không chỉ có thể hợp nhất mà còn hợp nhất dữ liệu cụ thể từ các trang tính đó. Ví dụ: số tiền hóa đơn của công ty Abatz vào năm 2020 là 1778 đô la và 2864 đô la vào năm 2019. Tổng số tiền trên hóa đơn của Abatz là 4642 đô la.
Mục tiêu của chúng ta là tổng hợp số lượng hóa đơn cho tất cả các công ty có hồ sơ trong cả hai trang tính. Đối với điều này, chúng ta đã sửa đổi công thức QUERY ở trên để có được như sau: =query({‘Invoices 2019′!A1:H;’Invoices 2020’!A2:H},”Select Col2,sum(Col8) where Col1 is not null group by Col2″)
Vì company_name là tham số lặp duy nhất đang hợp nhất dữ liệu, chúng ta không cần truy vấn các cột khác từ trang tính.
Hợp nhất các trang tính thành một trang theo lịch trình tùy chỉnh
Nếu bạn cần kết hợp nhiều trang tính thành một trang tự động theo lịch trình tùy chỉnh hoặc bạn không muốn xử lý các công thức, hãy sử dụng trình Google Sheets importer. Nó cho phép bạn nhập một dải dữ liệu từ nhiều trang tính và hợp nhất chúng lại với nhau.
Trình nhập Google Sheet là một phần của Coupler.io, một sản phẩm để nhập dữ liệu từ các nguồn dữ liệu khác nhau vào Google Sheet, Excel hoặc BigQuery.
Nguồn
- Chọn Google Sheet làm ứng dụng nguồn từ danh sách.
- Kết nối tài khoản Google của bạn hoặc chọn một tài khoản từ danh sách thả xuống nếu bạn đã kết nối một số tài khoản.
- Chọn file Google Sheet trên Google Drive của bạn để chuyển dữ liệu từ đó.
- Chọn trang tính để hợp nhất.
Thứ tự của các trang tính được chỉ định không ảnh hưởng đến thứ tự của dữ liệu đã hợp nhất. Nếu bạn cần hợp nhất dữ liệu theo một thứ tự cụ thể (ví dụ: trước tiên Invoices 2020, sau đó Invoices 2019), hãy đảm bảo sắp xếp các trang tính trong bảng tính của bạn theo thứ tự này.
Hợp nhất các trang tính với một tên theo khuôn mẫu
Bạn có thể hợp nhất nhiều trang tính có tên theo khuôn mẫu! Giả sử bạn có các trang tính có hóa đơn trong 10 năm qua. Thay vì gõ vào tất cả chúng từng cái một (Invoices 2010,Invoices 2011,Invoices 2012,…), sử dụng các mô hình sau:{sheet-name}.+
Trong trường hợp của chúng tôi, nó sẽ trông như sau:Invoices 20.+
Bằng cách này, tên sheet sẽ được kết hợp bởi hoa văn và nếu tờ mới được thêm vào (Invoices 2021,Invoices 2022…), Dữ liệu từ chúng sẽ được hợp nhất tự động mà không cần thay đổi các tham số của trình nhập.
Tính năng này hoạt động tương tự với bất kỳ tên trang tính nào bạn có.
- Nhấp vào Jump to Destination Settings. Theo tùy chọn, bạn có thể chọn một phạm vi cụ thể trong bảng tính của mình để hợp nhất. Để thực hiện việc này, hãy nhấp vào Continue và chỉ định phạm vi trong trường tùy chọn.
Điểm đến
- Chọn Google Sheet làm ứng dụng đích mà bạn muốn lưu dữ liệu.
- Kết nối với tài khoản Google của bạn.
- Chọn file Google Sheet, file này sẽ nhận các trang tính được kết hợp.
- Chỉ định ô đầu tiên mà bạn muốn nhập phạm vi dữ liệu của mình. Ô A1 được đặt theo mặc định.
- Chọn chế độ nhập cho dữ liệu của bạn: bạn có thể thay thế thông tin trước đó của mình hoặc nối các hàng mới bên dưới các mục đã nhập cuối cùng.
- Bật tính năng Last updated column, nếu bạn muốn thêm một cột vào bảng tính với thông tin về ngày và giờ làm mới gần đây nhất.
Bạn có thể chạy quá trình nhập ngay lập tức nếu bạn nhấp vào Save and Run. Nếu bạn muốn tự động nhập dữ liệu theo lịch biểu, hãy bật tính năng Automatic data refresh và tùy chỉnh lịch biểu.
Lịch trình
- Chọn Interval (từ 15 phút đến hàng tháng)
- Chọn Days of the week
- Chọn Time preferences
- Lên lịch Time zone
Khi bạn đã sẵn sàng, hãy nhấp vào Save & Run để lưu các tham số và bắt đầu nhập dữ liệu đầu tiên và kết hợp các trang tính đã chỉ định.
Trình nhập thêm cột Sheet Name, vì vậy bạn có thể dễ dàng điều hướng dữ liệu đã hợp nhất.
Cách hợp nhất các trang tính từ một tài liệu Google Sheet khác
Chúng ta đã biết cách kết hợp các trang tính trong tài liệu Google Sheet. Bây giờ chúng ta hãy khám phá cách bạn có thể nhập hai hoặc nhiều trang tính từ một bảng tính khác và hợp nhất chúng thành một.
Hợp nhất các trang tính thành một trang tính từ một bảng tính khác mà không cần công thức
Trình nhập Google Sheet của nó là cách dễ nhất để nhập và hợp nhất dữ liệu từ tài liệu Google Sheet. Tất cả những gì bạn cần làm là:
- Chọn Google Sheet làm ứng dụng nguồn từ danh sách.
- Kết nối tài khoản Google của bạn hoặc chọn một tài khoản từ danh sách thả xuống nếu bạn đã kết nối một số tài khoản.
- Chọn một file trên Google Drive của bạn để chuyển dữ liệu
- Chọn trang tính để hợp nhất. (
Invoices 2019vàInvoices 2020)
- Chọn Google Sheet làm ứng dụng đích mà bạn muốn lưu dữ liệu.
- Kết nối với tài khoản Google của bạn.
- Chọn một file và một trang tính để chuyển dữ liệu đã hợp nhất sang.
Bây giờ, trình nhập Google Sheet sẽ nhập và hợp nhất các trang tính một cách trơn tru.
Bạn có thể làm tương tự bằng cách sử dụng các công thức QUERY hoặc FILTER được lồng vào IMPORTRANGE. Đây là một chức năng cho phép bạn nhập phạm vi dữ liệu từ tài liệu Google Sheet này sang tài liệu Google Sheet khác.
Hợp nhất các trang tính từ một bảng tính khác thành một trang tính bằng FILTER + IMPORTRANGE
Cú pháp công thức FILTER + IMPORTRANGE để kết hợp hai hoặc nhiều trang tính từ một bảng tính khác như sau:
={FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-first-column})>0);
FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-first-column})>0);...}
{spreadsheet-ID}– ID hoặc URL của tài liệu Google Sheet, bạn đang nhập dữ liệu{sheet#1-name}– tên của trang tính đầu tiên{sheet#1-name}– tên của trang tính thứ hai{sheet#2-range}– phạm vi dữ liệu từ trang tính đầu tiên bao gồm hàng tiêu đề.{sheet#2-range}– phạm vi dữ liệu từ trang tính thứ hai không có hàng tiêu đề.{sheet#1-first-column}– cột đầu tiên của phạm vi dữ liệu từ trang tính đầu tiên.{sheet#2-first-column}– cột đầu tiên (không có hàng tiêu đề) của phạm vi dữ liệu từ trang tính thứ hai.
Đây là cách công thức trông như thế nào cho trường hợp sử dụng của chúng ta:
={ FILTER(importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2019!A1:H”), LEN(importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2019!A1:A”)) > 0); FILTER(importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2020!A2:H”), LEN(importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2020!A2:A”)) > 0)}
Hợp nhất các trang tính từ một bảng tính khác thành một trang tính bằng QUERY + IMPORTRANGE
Cú pháp công thức QUERY + IMPORTRANGE để kết hợp hai hoặc nhiều trang tính từ một bảng tính khác ngắn hơn:
=QUERY({IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}");IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}");...,"Select * where Col1 is not null"){spreadsheet-ID}– ID hoặc URL của tài liệu Google Sheet, bạn đang nhập dữ liệu{sheet#1-name}– tên của trang tính đầu tiên{sheet#2-name}– tên của trang tính thứ hai{sheet#1-range}– phạm vi dữ liệu từ trang tính đầu tiên bao gồm hàng tiêu đề.{sheet#2-range}– phạm vi dữ liệu từ trang tính thứ hai không có hàng tiêu đề.
Trong trường hợp này, công thức sẽ như sau: =query({importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2019!A1:H”);importrange(“1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck”, “Invoices 2020!A2:H”)},”Select * where Col1 is not null”)
IMPORTRANGE #REF! Bạn cần kết nối các trang tính này
Sẽ ổn nếu bạn nhận được cảnh báo này trong lần chạy công thức FILTER + IMPORTRANGE hoặc QUERY + IMPORTRANGE đầu tiên. Nhấp vào Allow access để kết nối bảng tính nguồn và bảng tính mục tiêu. Sau đó, công thức sẽ nhập và hợp nhất các trang tính.
Cách hợp nhất các trang tính từ các tài liệu Google Sheet khác nhau
Một trường hợp sử dụng khác là khi bạn cần hợp nhất một trang tính từ một tài liệu Google Sheet và một trang tính từ một tài liệu Google Sheet khác. Bạn có thể dễ dàng xử lý điều này bằng cách sử dụng FILTER + IMPORTRANGE hoặc QUERY + IMPORTRANGE. Sự khác biệt là bạn sẽ phải chỉ định các ID bảng tính khác nhau trong các tham số tương ứng của công thức IMPORTRANGE.
Trình nhập QUERY, FILTER hoặc Google Sheets – chọn cách nào?
Trình nhập Google Sheet
Nếu bạn không muốn mất thời gian vào việc viết công thức và kiểm tra cú pháp của chúng, hãy sử dụng trình nhập Google Sheet. Nó dễ sử dụng và cho phép bạn lập lịch nhập và hợp nhất dữ liệu. Trình nhập đặc biệt hoạt động nếu bạn cần hợp nhất nhiều trang tính từ một tài liệu Google Sheet khác. Trong trường hợp này, đây là một giải pháp thay thế nâng cao cho hàm IMPORTRANGE.
FILTER
Chức năng FILTER rất tốt khi bạn cần hợp nhất các trang tính trong một bảng tính. Nó đơn giản và không yêu cầu bất kỳ kiến thức nâng cao nào. Đồng thời, cú pháp của FILTER lồng vào IMPORTRANGE khá phức tạp, vì vậy bạn nên tránh sử dụng FILTER để hợp nhất các trang tính từ các bảng tính bên ngoài.
QUERY
Sự kết hợp giữa QUERY và IMPORTRANGE là lựa chọn tốt nhất để hợp nhất các trang tính từ một bảng tính khác, cũng như các bảng tính khác nhau. Cú pháp của nó rất dễ hiểu và không cồng kềnh như với FILTER.
