Merging các hàng trùng lặp trong Google Sheets có thể bao gồm việc hợp nhất các giá trị ô, tóm số, và nối văn bản, ngày tháng hoặc thời gian.
Hợp nhất hàng là quá trình kết hợp hai hoặc nhiều hàng dữ liệu chứa cùng thông tin. Ví dụ: hai hoặc nhiều hàng có cùng tên đầu và tên cuối, hoặc cùng sản phẩm trong hơn một hàng.
Trong trường hợp kết hợp hai hoặc nhiều hàng dữ liệu chứa cùng sản phẩm, bạn có thể cần thêm số lượng hoặc số tiền để tránh mất dữ liệu. Chúng ta nên xem xét tất cả điều này khi viết các công thức.
Nếu bạn đang tìm cách tiếp cận dựa trên công thức mảng mà không sử dụng bất kỳ tiện ích bổ sung nào, giải pháp dưới đây của tôi có thể phù hợp nhất cho bạn.
Không có hàm tích hợp hoặc lệnh menu nào để hợp nhất các hàng trùng lặp trong Google Sheets theo cách mà chúng ta muốn.
Để xử lý điều này, chúng ta sẽ sử dụng ba công thức mảng, mỗi công thức được gán cho một nhiệm vụ cụ thể.
- Công thức mảng đầu tiên sẽ loại bỏ các hàng trùng lặp (hợp nhất ô) trong phạm vi dữ liệu trong các cột đã chỉ định.
- Công thức mảng thứ hai sẽ hoạt động như một phương thức thay thế cho hàm CONCATENATE hoặc JOIN. Nó sẽ nối các giá trị của các hàng đã bị loại bỏ với các hàng hiện có.
- Công thức mảng thứ ba sẽ tổng hợp các cột theo điều kiện. Nó sẽ thêm các giá trị của các hàng đã bị loại bỏ vào các hàng hiện có.
Tất nhiên, bạn có thể hợp nhất các ô (trùng lặp) bằng cách sử dụng hàm UNIQUE. Tuy nhiên, còn việc tổng hợp và kết hợp các giá trị sao?
Thực ra, chúng ta có thể xử lý hầu hết các công việc này bằng chính hàm QUERY. Tuy nhiên, nó không đủ để hợp nhất hoặc nối các giá trị trùng lặp.
Cách hợp nhất các hàng trùng lặp trong Google Sheets với một ví dụ
Trong ví dụ dưới đây, dữ liệu nguồn để hợp nhất các hàng trùng lặp nằm trong phạm vi ô A2:E và kết quả nằm trong phạm vi ô G2:K.
Hình 1
Trong khái niệm hợp nhất các hàng trùng lặp, trước tiên, chúng ta sẽ loại bỏ các bản ghi trùng lặp trong cột “Mặt hàng” (A). Điều này sẽ cho chúng ta các tên trái cây duy nhất (cột G).
Sau đó, chúng ta sẽ tổng hợp Số lượng tương ứng trong cột (B) và Số tiền trong cột (D) riêng biệt. Kết quả được lưu trong cột H và J.
Cột Tỷ lệ (D) chứa nhiều tỷ lệ khác nhau cho cùng một loại trái cây. Chúng ta sẽ nối các tỷ lệ này cho mỗi loại trái cây (cột I). Cột Đánh giá (E) cũng sẽ được nối (kết hợp) tương tự. Kết quả nằm trong cột K.
Chúng ta sẽ sử dụng ba công thức mảng cho mỗi mục đích và lặp lại chúng, nếu cần, cho các cột bổ sung. Điều này cho phép bất kỳ ai điều chỉnh các phạm vi trong công thức để tùy chỉnh công thức cho bộ dữ liệu của mình.
Khi công thức là các công thức mảng, chúng ta có thể sử dụng chúng với dữ liệu bảng tính thông thường, dữ liệu Biểu mẫu Google trong Bảng tính, cũng như dữ liệu IMPORTRANGE.
Trong bài hướng dẫn này, bạn sẽ nhận được các công thức mảng để hợp nhất các hàng trùng lặp trong Google Sheets. Tôi cũng sẽ giải thích cách sử dụng chúng.
Bước 1: Loại bỏ trùng để hợp nhất các ô
Cú pháp: UNIQUE(range, [by_column], [exactly_once])
Trong ví dụ trên về việc hợp nhất các hàng trùng lặp, cột chính để hợp nhất là cột A, chứa các giá trị trùng lặp (tên trái cây). Đôi khi, bạn có thể có nhiều cột mà bạn muốn hợp nhất. Chúng ta sẽ thấy tình huống đó sau này.
Chúng ta có thể sử dụng hàm UNIQUE để làm cho các tên trái cây trong cột A trở nên duy nhất. Tuy nhiên, điều này không đủ. Chúng ta cũng nên bao gồm hàm FILTER bên trong hàm UNIQUE. Điều này là để lọc ra các hàng trống, vì hàm UNIQUE được biết đến với việc trả về ô trống ở cuối.
Công thức sau đây trong ô G2 sẽ làm cho các tên trái cây trong cột A trở nên duy nhất và loại bỏ bất kỳ ô trống nào:
=LET(key,<strong>A2:A</strong>,UNIQUE(FILTER(key,CHOOSECOLS(key,1)<>"")))Khi bạn sử dụng công thức này, chỉ cần thay thế A2:A bằng phạm vi cột tương ứng.
Bước 2: Tổng hợp giá trị trong khi giữ các giá trị trong các hàng đã bị loại bỏ
Hợp nhất các hàng không chỉ đơn giản là loại bỏ các hàng trùng lặp. Chúng ta sẽ loại bỏ các hàng trùng lặp, nhưng chúng ta cũng sẽ giữ lại các giá trị trong chúng với các hàng đã bị loại bỏ. Nếu các giá trị là văn bản, ngày, giờ hoặc thời điểm, chúng ta sẽ kết hợp chúng. Nhưng nếu các giá trị là số, chúng ta có thể tổng hợp hoặc kết hợp chúng, tùy thuộc vào mục đích của chúng ta.
Làm thế nào chúng ta có thể tổng hợp các giá trị tương ứng với các giá trị đã hợp nhất trong bước 1?
Chúng ta có thể sử dụng hàm SUMIF cho việc này. Cú pháp là SUMIF(range, criterion, [sum_range]).
Trong dữ liệu mẫu của chúng ta, sum_range là B2:B, range là A2:A và criterion là các mục duy nhất trong G2:G.
Hình 2
Tuy nhiên, bạn không cần phải lo lắng về các giá trị duy nhất (G2:G) trong công thức của bạn. Chỉ cần chỉ định phạm vi và sum_range, và công thức sẽ đảm nhận phần còn lại.
Công thức H2:
=ARRAYFORMULA(LET(range,<strong>A2:A</strong>,sum_range,<strong>B2:B</strong>,SUMIF(TRANSPOSE(QUERY(TRANSPOSE(range),,9^9)),TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(range,CHOOSECOLS(range,1)<>""))),,9^9)),sum_range)))Chúng ta có thể sử dụng cùng công thức kết quả trong ô J2 để tổng hợp cột Số tiền bằng cách thay thế sum_range B2:B bằng D2:D.
=ARRAYFORMULA(LET(range,<strong>A2:A</strong>,sum_range,<strong>D2:D</strong>,SUMIF(TRANSPOSE(QUERY(TRANSPOSE(range),,9^9)),TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(range,CHOOSECOLS(range,1)<>""))),,9^9)),sum_range)))Vậy là bước thứ hai của việc hợp nhất các hàng trùng lặp và nối các giá trị.
Hình 3
Công thức trên có vẻ phức tạp nhưng đừng lo lắng về điều đó. Chỉ cần chỉ định các phạm vi và bạn đã sẵn sàng.
Tôi đã bao gồm một số linh hoạt trong công thức để tự động điều chỉnh nó khi bạn có nhiều cột hợp nhất trong Bước 1 (công thức G2). Chúng ta sẽ xem một ví dụ về điều đó sau.
Bước 3: Nối các giá trị trong khi giữ các giá trị trong các hàng đã bị loại bỏ
Đây là công thức thứ ba và cuối cùng để hợp nhất các hàng trùng lặp và nối các giá trị.
Trong bước này, chúng ta sẽ nối các Tỷ lệ trong các hàng đã bị loại bỏ với các tỷ lệ hiện có. Điều này liên quan đến hai cột: cột được sử dụng để xác định các hàng duy nhất trong bước 1 và cột tỷ lệ.
Đây là công thức I2:
=LET(merge,<strong>A2:A</strong>,combine,<strong>C2:C</strong>,MAP(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(merge,CHOOSECOLS(merge,1)<>""))),,9^9)),LAMBDA(row,TEXTJOIN(", ",TRUE,FILTER(combine,TRANSPOSE(QUERY(TRANSPOSE(merge),,9^9))=row)))))Để nối cột Đánh giá, sử dụng cùng công thức nhưng thay thế phạm vi C2:C bằng E2:E.
Công thức K2:
=LET(merge,<strong>A2:A</strong>,combine,<strong>E2:E</strong>,MAP(TRANSPOSE(QUERY(TRANSPOSE(UNIQUE(FILTER(merge,CHOOSECOLS(merge,1)<>""))),,9^9)),LAMBDA(row,TEXTJOIN(", ",TRUE,FILTER(combine,TRANSPOSE(QUERY(TRANSPOSE(merge),,9^9))=row)))))
Hình 4
Bạn đã có bộ 3 công thức chính để hợp nhất các hàng trùng lặp trong Google Sheets. Giờ hãy xem một bộ dữ liệu khác nơi chúng ta muốn duy nhất hai cột.
Hợp nhất các hàng trùng lặp và nhiều cột duy nhất trong Google Sheets
Đây là dữ liệu mẫu mới:
Hình 5
Trong ví dụ này, chúng tôi muốn hợp nhất bảng dựa trên tên đầu và tên cuối trong cột A và B. Mark nên được tổng hợp và Chủ đề nên được nối.
Dưới đây là hướng dẫn từng bước để hợp nhất các hàng trùng lặp tên đầu tiên và tên cuối và nối hoặc tổng hợp các cột tương ứng:
-
Di chuyển lên trên và sao chép công thức dưới “Hợp nhất ô duy nhất” (Unique to Merge Duplicate Cells). Thay thế phạm vi A2:A bằng A2:B. Chèn công thức đó vào ô F2.
-
Tương tự, sao chép công thức đầu tiên dưới “Tổng hợp giá trị trong khi giữ các giá trị trong các hàng đã bị loại bỏ” (Aggregating Values while Retaining Removed Rows’ Values). Thay thế A2:A bằng A2:B và B2:B bằng C2:C.
-
Sau đó, sao chép công thức đầu tiên dưới “Nối các giá trị trong khi giữ các giá trị trong các hàng đã bị loại bỏ” (Concatenating Values while Retaining Removed Rows’ Values). Thay thế A2:A bằng A2:B và C2:C bằng D2:D.
Điều đó là tất cả! Bạn có thể sử dụng ba công thức trên để hợp nhất các hàng trùng lặp trong Google Sheets.
