Không dễ dàng để so sánh tất cả các cột với nhau để tìm các giá trị trùng lặp trong Google Sheets. Điều này là vì không có chức năng nào có sẵn để làm điều này.
Vì vậy, tôi đã viết một hàm tên tự đặt và một hàm con cho việc này. Chúng là COMPARE_ALL_COLUMNS và CAC_CHILD.
Ngoài ra, bạn cũng sẽ tìm thấy các phương pháp sử dụng công thức cho việc này.
Hàm với tên tự đặt sẽ trả về các giá trị trùng lặp trong các cột, trong khi hàm con sẽ trả về các cột để so sánh.
Bạn chỉ cần cung cấp phạm vi tham chiếu, ví dụ như A2:C7.
So sánh sẽ được thực hiện giữa A2:A7 & B2:B7, A2:A7 & C2:C7 và B2:B7 & C2:C7.
Trong ví dụ trên, hàm con trả về các giá trị trong E2:E4 và hàm chính trả về các giá trị trùng lặp, tức là tên các loại trái cây chung, trong F2:H4.
Đây là một ví dụ khác để so sánh hoặc khớp tất cả các cột với nhau và trả về các giá trị trùng lặp trong Google Sheets.
Lần này có năm cột để so sánh.
Phạm vi: A2:E8
Công thức con: G2
Công thức chính: H2
Sử dụng các hàm này trong thực tế, bạn có thể kiểm tra xem các tên giống nhau có được bao gồm trong nhiều đội không.
COMPARE_ALL_COLUMNS và CAC_CHILD: Cú pháp và Đối số
Cú pháp: COMPARE_ALL_COLUMNS(range)
Đối số:
range: Phạm vi tham chiếu
Cú pháp: CAC_CHILD(range)
Đối số:
range: Phạm vi tham chiếu
Bạn có thể nhập các hàm này từ bảng mẫu của tôi dưới đây và sử dụng chúng ngay lập tức để so sánh tất cả các cột với nhau và tìm các giá trị chung.
Hướng dẫn nhập
Sử dụng nút trên để tạo bản sao của bảng của tôi chứa hai hàm đó.
Mở bảng tính mà bạn muốn sử dụng chúng. Trong bảng tính đó, điều hướng đến Dữ liệu > Các hàm có tên > Nhập hàm.
Làm theo hướng dẫn trên màn hình và xong!
Bạn đã sẵn sàng sử dụng hai hàm có tên ở trên của tôi để so sánh tất cả các cột (danh sách) trong phạm vi đã cho và trả về các giá trị trùng lặp/chung.
So sánh tất cả các cột với nhau để tìm các giá trị trùng lặp – Các hàm có tên
Tôi đã đưa ra hai ví dụ ở đầu bài viết này.
Ví dụ đầu tiên chứa tên các loại trái cây trong A2:C7. Dưới đây là các công thức được sử dụng trong E2 và F2.
E2 (Công thức con):
=CAC_CHILD(A2:C7)
F2 (Công thức chính):
=COMPARE_ALL_COLUMNS(A2:C7)
Vui lòng lưu ý rằng chúng ta có thể bao gồm các cột xa trong các hàm có tên này bằng cách cung cấp chúng dưới dạng mảng như {A2:A7, F2:F7, Z2:Z7} trong phạm vi.
Công thức E2 trả về các cột để so sánh.
Công thức F2 được sử dụng để so sánh tất cả các cột với nhau và trả về các giá trị khớp/trùng lặp.
Chúng ta có thể sử dụng một phạm vi mở như A2:C thay vì A2:A7 được không?
Có. Nhưng tôi khuyên bạn nên sử dụng phạm vi đóng để cải thiện hiệu suất của các hàm trong Google Sheets.
Trong ví dụ thứ hai, chúng ta đã so sánh các giá trị trong các cột A2:E8. Nhưng tôi đã sử dụng một phạm vi mở.
G2:
=CAC_CHILD(A2:E)
H2:
=COMPARE_ALL_COLUMNS(A2:E)
Tôi hy vọng các ví dụ trên đủ để hiểu cách sử dụng các hàm có tên COMPARE_ALL_COLUMNS và CAC_CHILD của tôi.
So sánh tất cả các cột với nhau để tìm các giá trị trùng lặp – Công thức
Một số bạn có thể không muốn nhập và sử dụng các hàm tùy chỉnh của tôi cho việc này. Nếu vậy, bạn có thể sử dụng các công thức sau đây.
Công thức sau tương đương với hàm con, tức là CAC_CHILD.
=ArrayFormula(lambda(range,sort(let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),setB,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,c))),filter(setB,setA<=columns(range))))&" and "&let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),filter(setA,setA<=columns(range))))(A2:E))
Thay thế A2:E bằng phạm vi thực tế của bạn.
Còn công thức tương đương với COMPARE_ALL_COLUMNS?
Đây nào!
=lambda(range,map(sort(let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),setB,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,c))),filter(setB,setA<=columns(range)))),let(setA,flatten(makearray(columns(range)-1,columns(range)-1,lambda(r,c,r+c))),filter(setA,setA<=columns(range))),lambda(two,one, transpose(ifna(unique(filter(index(range,0,two),xmatch(index(range,0,two),index(range,0,one),0))))))))(A2:E)
Ở đây, hãy thay thế A2:E bằng phạm vi thực tế của bạn.
Chúng ta có thể dễ dàng sử dụng các công thức này để so sánh/khớp tất cả các cột với nhau để tìm các giá trị trùng lặp nhờ sử dụng LAMBDA.
Các hàm LAMBDA và LET giúp chúng ta sử dụng tham chiếu phạm vi chỉ một lần trong công thức.
Rất dễ dàng để bất kỳ ai thích ứng công thức này vào bảng tính của họ.
Nếu bạn là một người đam mê Google Sheets, bạn có thể muốn biết về logic đằng sau các công thức trên.
Thành thật mà nói, tôi đã viết cả hai dựa trên cùng một logic và đây chính là logic đó.
Cấu trúc của các công thức
Giả sử có năm đội với bảy người chơi trong mỗi đội.
Chúng ta có thể gọi các đội bằng số 1, 2, 3, 4 và 5.
Nếu bạn muốn kiểm tra xem có bất kỳ người chơi nào trong Đội # 1 có mặt trong bất kỳ đội nào khác, bạn có thể sử dụng BYCOL với FILTER và XLOOKUP hoặc COUNTIF.
Việc khớp sẽ diễn ra giữa các cột 1 & 2, 1 & 3 và 1 & 4.
Nhưng mà so sánh tất cả các đội với nhau để tìm tên lặp lại thì sao?
Việc so sánh phải được thực hiện như sau:
Nếu chúng ta có thể tạo hai tập hợp số trên, chúng ta có thể sử dụng hàm INDEX để điều chỉnh phạm vi và so sánh tất cả các cột với nhau.
Quá trình suy nghĩ của tôi về logic này. Tôi có thể tìm được một giải pháp bằng cách sử dụng hàm MAKEARRAY.
Có ba bước mỗi bước để tạo ra hai danh sách số trên.
Hãy bắt đầu với phần “Match With” trước.
Phần “Match With”
Trả về ma trận 4 x 4 (Bước # 1 A).
=makearray(columns(A2:E)-1,columns(A2:E)-1,lambda(r,c,r+c))
Làm phẳng kết quả Bước # 1 A (Bước # 1 B).
=flatten(makearray(columns(A2:E)-1,columns(A2:E)-1,lambda(r,c,r+c)))
Lọc ra các giá trị lớn hơn 5 (tổng số cột trong phạm vi) từ kết quả Bước # 1 B (Bước # 1 C).
=lambda(n_c,let(setA,flatten(makearray(n_c-1,n_c-1,lambda(r,c,r+c))),filter(setA,setA<=n_c)))(5)
Phần “Columns to Match”
Trả về ma trận 4 x 4 (Bước # 2 A).
=lambda(n_c,makearray(n_c-1,n_c-1,lambda(r,c,c)))(5)
Làm phẳng kết quả Bước # 2 A (Bước # 2 B).
=lambda(n_c,flatten(makearray(n_c-1,n_c-1,lambda(r,c,c))))(5)
Lọc ra các giá trị Bước # 2 B nếu giá trị Bước # 1 B lớn hơn 5 (Bước # 2 C).
=lambda(n_c,sort(let(setA,flatten(makearray(columns(A2:E)-1,columns(A2:E)-1,lambda(r,c,r+c))),setB,flatten(makearray(n_c-1,n_c-1,lambda(r,c,c))),filter(setB,setA<=n_c))))(5)
Công thức cuối cùng sử dụng hai danh sách số đã được tạo để so sánh tất cả các cột với nhau và tìm các giá trị trùng lặp.
Hãy cuộn lên và xem bảng mẫu của tôi để hiểu các bước trên cách thức và cách sử dụng chúng để so sánh tất cả các cột với nhau và trả về các tên/giá trị chung.
