Chào các bạn thân mến,
Hôm nay, tôi sẽ tiết lộ một bí mật quý giá: làm thế nào để kết hợp dữ liệu từ nhiều bảng tính Google vào một bảng tính duy nhất. Hãy tưởng tượng bạn có một tài liệu Google Sheets với nhiều bảng có cùng cấu trúc (số lượng và tên cột). Bạn cần kết hợp dữ liệu từ những bảng này thành một cái nhìn tổng quan duy nhất. Nhưng tại sao phải làm điều này bằng cách sao chép và dán giá trị thủ công? Có nhiều tùy chọn linh hoạt trong Google Sheets để tự động hóa quy trình này và tiết kiệm công sức đáng kể. Trong hướng dẫn này, chúng tôi sẽ giới thiệu cho bạn một số hàm và một giải pháp không sử dụng công thức để kết hợp các bảng.
Phương pháp nhanh nhất để kết hợp các bảng Google Sheets và tự động biến đổi dữ liệu mà không sử dụng công thức
Nếu bạn muốn tự động kết hợp nhiều bảng theo lịch trình tùy chỉnh hoặc đơn giản là không muốn phải loay hoay với các công thức trong Google Sheets để liên kết một bảng với bảng khác, hãy sử dụng Coupler.io cùng với tích hợp Google Sheets của nó.
Coupler.io là một nền tảng tự động hóa dữ liệu và phân tích cho phép bạn nhập khẩu dữ liệu tự động vào Google Sheets (và các mục tiêu khác) từ các ứng dụng khác (Shopify, Airtable, Xero, v.v.) và cơ sở dữ liệu.
Tích hợp với Google Sheets cho phép bạn nhập khẩu một phạm vi dữ liệu từ nhiều bảng và kết hợp các bảng Google Sheets. Trước khi tải dữ liệu nguồn của bạn vào bảng nhận, bạn có thể xem trước và thực hiện các biến đổi linh hoạt như quản lý cột, lọc dữ liệu và sắp xếp. Tất cả điều này có thể được thực hiện dễ dàng trong giao diện của Coupler.io, mà không cần bất kỳ công thức nào. Để cấu hình tích hợp với Google Sheets, hãy đăng ký tài khoản Coupler.io, nhấp vào “Thêm bộ tải” và chọn Google Sheets làm nguồn và đích.
Sau đó, cấu hình các kết nối.
- Kết nối tài khoản Google của bạn và chọn tệp Google Sheets trên Google Drive của bạn mà bạn muốn chuyển dữ liệu từ. Sau đó, chọn các bảng muốn kết hợp.
Thứ tự của các bảng đã chỉ định không ảnh hưởng đến thứ tự của dữ liệu đã được kết hợp. Nếu bạn cần kết hợp dữ liệu theo một thứ tự cụ thể (ví dụ: trước là Sản phẩm 2020, sau đó là Sản phẩm 2021 và Sản phẩm 2022), hãy chắc chắn rằng bạn có các bảng theo thứ tự này trong bảng tính của mình.
Kết hợp bảng với tên được xác định trước
Bạn có thể kết hợp nhiều bảng theo một mô hình! Giả sử bạn có các bảng với hóa đơn trong mười năm qua. Thay vì nhập liệu từng cái một (Hóa đơn 2010, Hóa đơn 2011, Hóa đơn 2012, v.v.), hãy sử dụng mẫu sau:
{tên-bảng}.+Trong trường hợp của chúng ta, nó sẽ trông như thế này: Hóa đơn 20.+
Như vậy, tên bảng sẽ khớp với mẫu này và nếu có bảng mới được thêm (Hóa đơn 2021, Hóa đơn 2022, v.v.), dữ liệu sẽ được tự động kết hợp mà không cần thay đổi cài đặt của bộ tải.
Chức năng này hoạt động tương tự với tất cả các tên bảng bạn có.
Sau đó, bạn có thể xem trước dữ liệu từ các bảng cần kết hợp và áp dụng các biến đổi cần thiết, như ẩn hoặc thêm cột, lọc dữ liệu, sắp xếp, v.v.
Tùy chọn, bạn cũng có thể chọn một phạm vi cụ thể trong các bảng tính của bạn để kết hợp. Để làm điều này, chỉ định phạm vi ô trong trường tùy chọn “Phạm vi”.
- Kết nối tài khoản Google của bạn và chọn một tệp Google Sheets trên Google Drive của bạn, cũng như một bảng sẽ nhận các bảng đã được kết hợp.
Bạn có thể thực hiện việc nhập khẩu ngay lập tức bằng cách nhấp vào “Lưu và chạy”. Để tự động hóa việc nhập khẩu dữ liệu theo lịch trình, hãy bật “Cập nhật dữ liệu tự động” và tùy chỉnh tần suất như sau:
- Chọn khoảng thời gian (từ 15 phút đến hàng tháng).
- Chọn các ngày trong tuần.
- Chọn các ưu tiên thời gian.
- Lên lịch tần suất dựa trên múi giờ.
Khi bạn đã sẵn sàng, hãy nhấp vào “Lưu và chạy” để lưu cài đặt và bắt đầu quá trình nhập khẩu dữ liệu và kết hợp các bảng đã được chỉ định. Đây là kết quả chúng tôi đã nhận được:
Coupler.io thêm cột “Tên bảng”, cho phép bạn dễ dàng điều hướng trong các bảng Google Sheets đã được kết hợp.
Kết nối Sheets với Sheets không phải là tích hợp duy nhất được cung cấp bởi Coupler.io. Nó còn cho phép bạn nhập khẩu dữ liệu từ các tệp CSV, Excel và nhiều ứng dụng khác.
Nếu bạn thích các tùy chọn mặc định để kết hợp các bảng trong Google Sheets, hãy cùng nhau tìm hiểu.
Cách kết hợp dữ liệu từ nhiều bảng trong Google Sheets
Hãy bắt đầu với một nhiệm vụ đơn giản:
Có một bảng tính Google Sheets với hai bảng: Hóa đơn 2019 và Hóa đơn 2020. Mỗi bảng này có tám cột (A: H) có cùng tên. Hàng đầu tiên chứa tiêu đề cột. Nhiệm vụ của chúng tôi là kết hợp dữ liệu theo chiều dọc từ các bảng này thành một bảng duy nhất.
Kết hợp các bảng bằng cách sử dụng hàm FILTER trong Google Sheets
FILTER là một hàm trong Google Sheets cho phép lọc các tập con dữ liệu từ một phạm vi dữ liệu cụ thể dựa trên một điều kiện cụ thể.
Để kết hợp các bảng bằng cách sử dụng FILTER, áp dụng công thức sau:
={FILTER({phạm-vi-bảng#1}, LEN({cột-đầu-tiên-bảng#1})>0); FILTER({phạm-vi-bảng#2}, LEN({cột-đầu-tiên-bảng#2})>0);...}{phạm-vi-bảng#1}: phạm vi dữ liệu của bảng thứ nhất, bao gồm cả hàng đầu tiên.{phạm-vi-bảng#2}: phạm vi dữ liệu của bảng thứ hai không bao gồm hàng đầu tiên.{cột-đầu-tiên-bảng#1}: cột đầu tiên trong phạm vi dữ liệu của bảng thứ nhất.{cột-đầu-tiên-bảng#2}: cột đầu tiên (không có hàng đầu tiên) trong phạm vi dữ liệu của bảng thứ hai.
Điều kiện LEN({cột-đầu-tiên-bảng#1})>0 trong công thức FILTER là cần thiết để loại trừ các hàng trống trong phạm vi. Nếu không, công thức sẽ cũng bao gồm các hàng trống khi kết hợp các hàng chứa dữ liệu.
Trong trường hợp của chúng tôi, công thức sẽ có dạng sau:
={FILTER('Hóa đơn 2019'!A1:H, LEN('Hóa đơn 2019'!A1:A) > 0); FILTER('Hóa đơn 2020'!A2:H, LEN('Hóa đơn 2020'!A2:A) > 0)}
Như vậy, bạn có thể kết hợp nhiều hơn hai bảng cùng nhau. Bạn chỉ cần thêm các bảng và phạm vi của chúng vào công thức.
Lưu ý: Đảm bảo xác định phạm vi dữ liệu của bảng thứ hai (và các bảng tiếp theo) mà không bao gồm hàng đầu tiên, ví dụ: A2:H thay vì A1:H. Nếu không, hàng đầu tiên sẽ được nhập cũng. Ví dụ:
={FILTER('Hóa đơn 2019'!A1:H, LEN('Hóa đơn 2019'!A1:A) > 0); FILTER('Hóa đơn 2020'!A1:H, LEN('Hóa đơn 2020'!A1:A) > 0)}Để biết thêm thông tin về cách hoạt động của hàm FILTER, hãy xem hướng dẫn về hàm FILTER.
Kết hợp các bảng bằng cách sử dụng hàm QUERY trong Google Sheets
QUERY là một hàm trong Google Sheets cho phép trích xuất dữ liệu dựa trên các tiêu chí cụ thể. Bạn cũng có thể thay đổi định dạng, thay đổi thứ tự 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 bảng bằng cách sử dụng QUERY, áp dụng công thức sau:
=QUERY({{phạm-vi-bảng#1};{phạm-vi-bảng#2};...},"Select * where Col1 is not null"){phạm-vi-bảng#1}: phạm vi dữ liệu của bảng thứ nhất, bao gồm cả hàng đầu tiên.{phạm-vi-bảng#2}: phạm vi dữ liệu của bảng thứ hai không bao gồm hàng đầu tiên.
Trong trường hợp của chúng tôi, công thức sẽ có dạng sau:
=QUERY({'Hóa đơn 2019'!A1:H;'Hóa đơn 2020'!A2:H},"Select * where Col1 is not null")
Bạn có thể kết hợp hơn hai bảng với QUERY bằng cách thêm các bảng và phạm vi của chúng vào công thức. Hãy nhớ rằng phạm vi của bảng thứ hai và các bảng sau đó phải được xác định mà không bao gồm hàng đầu tiên, tương tự như với hàm FILTER đã đề cập ở trên.
Lưu ý rằng các công thức QUERY và FILTER đã đề cập chỉ kết hợp các bảng có cùng số lượng cột. Đối với trường hợp khác, hãy xem hướng dẫn của chúng tôi về cách kết hợp các bảng có số lượng cột khác nhau thành một.
Fusionner les feuilles provenant d’une autre feuille de calcul à l’aide de FILTER + IMPORTRANGE
La syntaxe de la formule FILTER + IMPORTRANGE pour fusionner deux ou plusieurs feuilles provenant d’une autre feuille de calcul est la suivante :
={FILTER(IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#1}!{plage-feuille#1}"),LEN(IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#1}!{première-colonne-feuille#1})>0); FILTER(IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#2}!{plage-feuille#2}"),LEN(IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#2}!{première-colonne-feuille#2})>0);...}{ID-feuille-de-calcul}: ID ou URL de la feuille de calcul Google à partir de laquelle vous importez les données.{nom-feuille#1}: le nom de la première feuille.{nom-feuille#2}: le nom de la deuxième feuille.{plage-feuille#1}: la plage de données de la première feuille, y compris la ligne des titres.{plage-feuille#2}: la plage de données de la deuxième feuille sans la ligne des titres.{première-colonne-feuille#1}: la première colonne de la plage de données de la première feuille.{première-colonne-feuille#2}: la première colonne (sans la ligne des titres) de la plage de données de la deuxième feuille.
Voici à quoi ressemble la formule pour notre cas d’utilisation :
={FILTER(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2019!A1:H"), LEN(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2019!A1:A")) > 0); FILTER(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2020!A2:H"), LEN(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2020!A2:A")) > 0)}Fusionner les feuilles d’une autre feuille de calcul en utilisant QUERY + IMPORTRANGE
La syntaxe de la formule QUERY + IMPORTRANGE pour fusionner deux ou plusieurs feuilles d’une autre feuille de calcul est plus courte :
=QUERY({IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#1}!{plage-feuille#1}");IMPORTRANGE("{ID-feuille-de-calcul}", "{nom-feuille#2}!{plage-feuille#2}");...},"Select * where Col1 is not null"){ID-feuille-de-calcul}: l’ID ou l’URL de la feuille de calcul Google à partir de laquelle vous importez les données.{nom-feuille#1}: le nom de la première feuille.{nom-feuille#2}: le nom de la deuxième feuille.{plage-feuille#1}: la plage de données de la première feuille, y compris la ligne des titres.{plage-feuille#2}: la plage de données de la deuxième feuille sans la ligne des titres.
Dans notre cas, la formule sera la suivante :
=query({importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2019!A1:H");importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Factures 2020!A2:H")},"Select * where Col1 is not null")Pour en savoir plus sur la combinaison de QUERY + IMPORTRANGE avec des exemples, consultez la documentation.
ERREUR IMPORTRANGE #REF! Vous devez connecter ces feuilles
Si vous obtenez cet avertissement lors de la première exécution de la formule FILTER + IMPORTRANGE ou QUERY + IMPORTRANGE, cliquez sur “Autoriser l’accès” pour connecter les feuilles source et cible. Après cela, la formule importera et fusionnera les feuilles Google Sheets. Si vous obtenez une autre erreur, consultez notre article de blog “Pourquoi IMPORTRANGE ne fonctionne-t-il pas ? Erreurs et corrections” pour obtenir des solutions.
Comment fusionner des feuilles à partir de différents documents Google Sheets
Un autre cas d’utilisation est lorsque vous devez fusionner une feuille d’un document Google Sheets avec une autre feuille d’un autre document Google Sheets. Vous pouvez facilement gérer cela en utilisant FILTER + IMPORTRANGE ou QUERY + IMPORTRANGE. La différence est que vous devrez spécifier des ID de feuille de calcul différents dans les paramètres respectifs de la formule IMPORTRANGE.
QUERY, FILTER ou Coupler.io – quelles sont les options ?
- L’intégration Google Sheets de Coupler.io
Si vous ne voulez pas perdre de temps à écrire des formules et à vérifier leur syntaxe, optez pour l’intégration Google Sheets de Coupler.io. Elle est facile à utiliser et vous permet de planifier l’importation et la fusion des données. L’intégration est particulièrement fonctionnelle si vous avez besoin de fusionner plusieurs feuilles provenant d’une autre feuille de calcul Google Sheets. Dans ce cas, c’est une alternative avancée à la fonction IMPORTRANGE.
De plus, avec Coupler.io, vous obtenez une liste d’autres importateurs tels que Airtable, CSV, etc. Cela signifie que vous pouvez importer des données à partir de différentes sources dans une seule feuille de calcul pour un traitement ultérieur.
- FILTER
La fonction FILTER est utile lorsque vous avez besoin de fusionner des feuilles au sein d’une même feuille de calcul. Elle est simple et ne nécessite aucune connaissance avancée. Toutefois, la syntaxe de FILTER imbriqué avec IMPORTRANGE est assez volumineuse, il vaut donc mieux éviter d’utiliser FILTER pour fusionner des feuilles provenant de feuilles de calcul externes.
- QUERY
La combinaison de QUERY et IMPORTRANGE est le meilleur choix pour consolider des données dans Google Sheets. Sa syntaxe est facile à comprendre et moins volumineuse que celle de FILTER.
Bien sûr, c’est à vous de décider quelle méthode utiliser, en fonction de ce qui est le mieux pour votre projet. Bonne chance avec vos données !
Accueil
