Chào mừng quay trở lại với một bài hướng dẫn mới trên Google Spreadsheets! Lần này, chúng ta sẽ tìm hiểu cách kết hợp hai bảng trong Google Sheets bằng cách sử dụng công thức.
Các loại kết hợp bảng khác nhau
Chắc hẳn mỗi người đều có những mục đích và cách kết hợp bảng khác nhau. Trong bài hướng dẫn này, tôi đã bao gồm 3 loại kết hợp bảng khác nhau mà bạn có thể sử dụng.
Loại 1:
Trước đây, khi tôi sử dụng Excel, tôi thường phải kết hợp các bảng tài chính kết thúc quý. Bài viết này sẽ hướng dẫn bạn cách kết hợp hai bảng như vậy trong Google Sheets. Bạn có thể tham khảo hình ảnh dưới đây:
Trong ví dụ trên, tôi đã kết hợp hai bảng. Tôi đã loại bỏ các bản sao trong hai cột đầu tiên và thêm cột thứ ba (tổng lương). Các ID duy nhất cũng được thêm vào bảng kết hợp như một hàng mới.
Loại 2:
Đôi khi, bạn có thể muốn kết hợp hai bảng theo cách khác nhau. Hãy xem ví dụ dưới đây:
Ở đây, thay vì thêm số lương (như trong ảnh trên), bạn có thể muốn đặt số lượng đó vào một cột mới như hình ảnh dưới đây:
Loại 3:
Đây là hướng dẫn cuối cùng về cách kết hợp hai bảng trong Google Sheets. Dưới đây là một cách kết hợp độc đáo: tìm kiếm tiêu đề cột và sau đó kết hợp các cột phù hợp. Xem ví dụ:
Cách kết hợp hai bảng trong Google Sheets
Bài viết này sẽ giải thích chi tiết ba loại kết hợp bảng ở trên. Tôi biết rằng việc tạo bảng minh họa mất thời gian, vì vậy tôi đã chia sẻ bảng mẫu của mình với bạn.
Ví dụ Sheet: Merging of Tables
File này được đặt ở chế độ sao chép. Nếu không có hạn chế mức tên miền để nhận một tệp ngoài, bạn có thể sao chép thành công. Nếu không, hãy bỏ qua. Chỉ có một vài dòng dữ liệu mẫu để gõ. Vậy là chúng ta bắt đầu.
Kết hợp dữ liệu dựa trên ID duy nhất và thêm cột tổng (Loại 1)
Trong ví dụ này, tôi sử dụng công thức QUERY (xin đừng để tên công thức làm bạn sợ) để kết hợp hai bảng trong Google Sheets. Bạn cũng có thể sử dụng công thức này để kết hợp nhiều hơn hai bảng.
Bảng 1:
Bảng 2:
Hãy xem hai bảng này. Tôi sẽ kết hợp hai cột đầu tiên và sau đó thêm giá trị của chúng vào cột cuối cùng.
=query({A2:C;E3:G},"SELECT Col1, Col2, SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col1, Col2",1)Công thức trên sẽ kết hợp hai bảng như dưới đây.
Giả sử bạn muốn kết hợp thêm một bảng nữa. Bạn có thể sử dụng bảng thứ ba như bên dưới.
Đây là một công thức chung. Thay thế bảng 1, bảng 2 và bảng 3 bằng phạm vi dữ liệu tương ứng.
=query({table1;table2;table3},"SELECT Col1, Col2, SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col1, Col2",1)Kết hợp dữ liệu dựa trên ID duy nhất và chèn cột số lượng (Loại 2)
Trong ví dụ này, tôi sẽ sử dụng hai bảng trên (bảng 1 và bảng 2). Công thức ở đây hoàn toàn khác biệt.
Ở đây, tôi sẽ kết hợp hai cột đầu tiên chứa tên và ID, sau đó đặt cột thứ ba cạnh nhau.
Tôi có ba công thức. Công thức đầu tiên được đặt trong ô I3 và trả về giá trị trong khoảng I3:J8.
=unique({A3:B7;E3:F7})Công thức trên sẽ kết hợp hai cột đầu tiên trong bảng 1 và bảng 2 và loại bỏ các bản sao. Vì vậy, chúng ta có các Mã Nhân viên và Tên duy nhất.
Công thức thứ hai trong ô K3 trả về các giá trị trong khoảng K3:K7.
=ArrayFormula(IFERROR(vlookup(I3:I8,A3:C7,3,0),0))Công thức này là một công thức mảng Vlookup, sử dụng nhiều khóa tìm kiếm và trả về nhiều giá trị.
=ArrayFormula(iferror(vlookup(I3:I8,E3:G7,3,0),0))Các khóa tìm kiếm là các ID duy nhất trong I3:I8 và bảng tra cứu là bảng 1 (A3:C7). Cột đầu ra là cột 3 (Số tiền) trong bảng tra cứu.
Ở ô L3, tôi đã sử dụng một công thức Vlookup nữa. Lần này, bảng tra cứu là bảng 2 (E3:G7). Khác biệt duy nhất là không có sự khác biệt khác.
=ArrayFormula(iferror(vlookup(I3:I8,E3:G7,3,0),0))Tìm tiêu đề và kết hợp các cột phù hợp (Loại 3)
Đây là một phương pháp hoàn toàn khác so với hai loại kết hợp bảng trước đó.
Ở đây, tôi thực sự kết hợp hai bảng chứ không phải kết hợp. Nhưng sau đó, bạn có thể sử dụng dữ liệu được kết hợp đó và kết hợp bằng cách sử dụng phương pháp Loại 1 ở trên. Tôi sẽ thảo luận về điều đó dưới một tiêu đề riêng phía dưới.
Nếu bạn có hai tập dữ liệu với các cột tương tự và muốn kết hợp chúng, bạn có thể làm theo phương pháp này.
Tôi nghĩ rằng tôi nên cung cấp hình ảnh minh họa cho điều này.
Bảng 1:
Bảng 2:
Tôi có hai báo cáo bán hàng. Hàng đầu của bảng đầu tiên chứa nhãn – “Tháng”, “Nhân viên bán hàng” và “Doanh số bán hàng”. Đây là nhãn cột hoặc chúng ta có thể nói là tiêu đề trường.
Tôi muốn tìm kiếm các nhãn này trong bảng 2 và nếu tìm thấy, kết hợp các cột đó dưới bảng 1.
Vì vậy, công thức phải bắt đầu với;
{A1:C4;Vì chúng ta muốn bảng đầu tiên như nó hiện diện trong đầu ra. Công thức cuối cùng là;
=iferror({A1:C4;
ArrayFormula(hlookup(A1:C1,E1:F4,row(A2:A),0))})Tôi đã sử dụng Hlookup để tìm kiếm trên hàng đầu tiên của bảng 2 cho các nhãn trường trong A1:C1 (phạm vi khóa tìm kiếm).
Phạm vi A1:C1 là hàng đầu tiên của bảng 1, là khóa tìm kiếm trong Hlookup. Công thức mảng Hlookup sẽ trả về các cột phù hợp dưới A1:C4.
Chủ đề của chúng ta là cách kết hợp hai bảng trong Google Sheets. Liên quan đến vấn đề đó, ví dụ loại 3 có đáng để thảo luận không?
Tôi nghĩ rằng nó chỉ thỏa mãn một phần. Thực ra, những gì tôi đã thể hiện là cách tìm kiếm tên cột và kết hợp các cột phù hợp.
Trong ví dụ tiếp theo, tôi sẽ chỉ cho bạn cách kết hợp các hàng tương tự trong dữ liệu được kết hợp đó. Bạn có thể sử dụng nó như một phương pháp tổng hợp dữ liệu đầy đủ trong Google Docs Sheets.
Thực ra đây là sự kết hợp của loại 1 và loại 3.
Cách kết hợp và kết hợp các cột phù hợp trong hai bảng trong Google Sheets
Để đơn giản hóa công thức, ở đây tôi sử dụng các bảng trợ giúp. Tôi sẽ cho bạn biết đó là điều gì. Trước khi làm quen với dữ liệu mẫu, hãy xem nó.
Dữ liệu mẫu bảng 1:
Dữ liệu mẫu bảng 2:
Cả hai bảng đều có các cột phù hợp và không phù hợp. Hãy xem tên cột (tên tháng) để hiểu điều đó.
Làm thế nào để kết hợp đúng hai bảng này trong Google Sheets? Dưới đây là hướng dẫn từng bước.
Để tìm kiếm tên cột và kết hợp hai bảng, chúng ta có thể sử dụng hàm Hlookup.
Bước 1:
Trong Hlookup, chúng ta phải sử dụng các tên cột duy nhất của bảng 1 và 2 làm khóa tìm kiếm. Chúng tôi có thể tạo ra điều này bằng công thức dưới đây.
=transpose(unique(transpose({A1:D1,F1:I1})))Để hiểu công thức này trả về gì, hãy nhập nó vào ô K1. Hãy đảm bảo rằng K1:O1 trống. Hãy đặt công thức này ở đó.
Bước 2:
Chúng ta đã có các khóa tìm kiếm cho Hlookup. Bây giờ chúng ta muốn phạm vi tìm kiếm. Đó là phạm vi bảng 1 A1:D.
Sử dụng công thức mảng Hlookup sau đây trong ô K2.
=ArrayFormula(IFERROR(hlookup(K1:P1,A1:D,row(A2:A),0)))Nó sẽ tạo ra một bảng trợ giúp như bên dưới.
Bước 3:
Sử dụng công thức trên cùng trong ô Q2. Chỉ cần thay đổi phạm vi A1:D bằng phạm vi bảng 2 F1:I.
=ArrayFormula(IFERROR(hlookup(K1:P1,F1:I,row(A2:A),0)))Đó là bảng trợ giúp thứ hai của chúng ta.
Bây giờ hãy sử dụng truy vấn để kết hợp hai bảng này.
Bước 4:
Chỉ cần nhập công thức sau vào ô W1 và xem kết quả!
=query({K1:O4;Q2:U4},"SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4), SUM(Col5) GROUP BY Col1",1)Đó là tất cả. Chúc vui vẻ!
