Bạn có thể tìm và đánh dấu các mục trùng lặp giữa hai cột bằng cách sử dụng tính năng Định dạng có Điều kiện trong Google Trang tính.
Khi làm việc trong Google Trang tính với các tập dữ liệu lớn, bạn có thể gặp phải sự cố khi phải xử lý nhiều giá trị trùng lặp. Trong khi một số mục nhập trùng lặp được đặt có chủ ý trong khi những mục khác là sai lầm. Điều này đặc biệt đúng khi bạn đang cộng tác trên cùng một trang tính với một nhóm.
Khi nói đến việc phân tích dữ liệu trên Google Trang tính, việc có thể lọc ra các dữ liệu trùng lặp có thể là điều cần thiết và thuận tiện. Mặc dù Google Trang tính không có bất kỳ hỗ trợ gốc nào để tìm các bản sao trong trang tính, nhưng nó cung cấp một số cách để so sánh, xác định và loại bỏ dữ liệu trùng lặp trong các ô.
Đôi khi, bạn muốn so sánh từng giá trị trong một cột với một cột khác và tìm xem có bất kỳ giá trị nào trùng lặp trong đó không và ngược lại. Trong Google Trang tính, bạn có thể dễ dàng tìm thấy các bản sao giữa hai cột với sự trợ giúp của tính năng định dạng có điều kiện. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách so sánh hai cột trong Google Trang tính và tìm các điểm trùng lặp giữa chúng.
Tìm mục nhập trùng lặp giữa hai cột bằng cách sử dụng định dạng có điều kiện
Định dạng có điều kiện là một tính năng trong Google Trang tính cho phép người dùng áp dụng các định dạng cụ thể như màu phông chữ, biểu tượng và thanh dữ liệu cho một ô hoặc phạm vi ô dựa trên các điều kiện nhất định.
Bạn có thể sử dụng định dạng có điều kiện này để đánh dấu các mục trùng lặp giữa hai cột, bằng cách tô màu các ô hoặc thay đổi màu văn bản. Bạn cần so sánh từng giá trị trong một cột với một cột khác và tìm xem có giá trị nào được lặp lại hay không. Để điều này hoạt động, bạn phải áp dụng định dạng có điều kiện cho từng cột riêng biệt. Làm theo các bước sau để làm điều đó:
Mở bảng tính bạn muốn kiểm tra các bản sao trong Google Trang tính. Đầu tiên, chọn cột đầu tiên (A) để kiểm tra với cột B. Bạn có thể đánh dấu toàn bộ cột bằng cách nhấp vào ký tự cột phía trên nó.
Sau đó, nhấp vào trình đơn ‘Định dạng’ từ thanh trình đơn và chọn ‘Định dạng có điều kiện’.
Trình đơn Định dạng có Điều kiện mở ra ở phía bên phải của trang tính google. Bạn có thể xác nhận phạm vi ô là những gì bạn đã chọn trong tùy chọn 'Áp dụng cho phạm vi'. Nếu bạn muốn thay đổi phạm vi, hãy nhấp vào 'biểu tượng phạm vi' và chọn một phạm vi khác.
Sau đó, nhấp vào menu thả xuống trong 'Quy tắc định dạng' và chọn tùy chọn "Công thức tùy chỉnh là".
Bây giờ, bạn cần nhập công thức tùy chỉnh vào hộp "Giá trị hoặc công thức".
Nếu bạn đã chọn toàn bộ một cột (B: B), hãy nhập công thức COUNTIF sau vào hộp "Giá trị hoặc công thức" trong Quy tắc định dạng:
= countif ($ B: $ B, $ A2)> 0Hoặc là,
Nếu bạn đã chọn một dải ô trong một cột (giả sử một trăm ô, A2: A30), hãy sử dụng công thức này:
= COUNTIF ($ B $ 2: $ B $ 30, $ A2)> 0Khi bạn đang nhập công thức, hãy đảm bảo thay thế tất cả các trường hợp của chữ ‘B’ trong công thức bằng chữ cái của cột bạn đã đánh dấu. Chúng tôi đang thêm dấu ‘$’ trước các tham chiếu ô để biến chúng thành phạm vi tuyệt đối, vì vậy, nó không thay đổi, chúng tôi áp dụng công thức.
Trong phần Kiểu định dạng, bạn có thể chọn kiểu định dạng để đánh dấu các mục trùng lặp. Theo mặc định, nó sẽ sử dụng màu tô xanh lục.
Bạn có thể chọn một trong các kiểu định dạng đặt trước bằng cách nhấp vào ‘Mặc định’ trong tùy chọn ‘Kiểu định dạng’, sau đó chọn một trong các kiểu định dạng.
Hoặc, bạn có thể sử dụng bất kỳ công cụ định dạng nào trong số bảy công cụ định dạng (In đậm, Nghiêng, Gạch chân, Gạch ngang, Màu văn bản, Màu tô) trong phần ‘Kiểu định dạng’ để đánh dấu các bản sao.
Ở đây, chúng tôi chọn màu tô cho các ô trùng lặp bằng cách nhấp vào biểu tượng ‘Màu tô’ và chọn màu ‘vàng’.
Khi bạn đã chọn định dạng, hãy nhấp vào 'Hoàn tất' để đánh dấu các ô.
Hàm COUNTIF đếm số lần mỗi giá trị ô trong ‘Cột A’ xuất hiện trong ‘Cột B’. Vì vậy, nếu một mục xuất hiện thậm chí một lần trong cột B, công thức sẽ trả về TRUE. Sau đó, mục đó sẽ được đánh dấu trong 'Cột A' dựa trên định dạng bạn đã chọn.
Điều này không làm nổi bật các mục trùng lặp mà nó làm nổi bật các mục có trùng lặp trong Cột B. Điều đó có nghĩa là mỗi mục được đánh dấu màu vàng có các mục trùng lặp trong Cột B.
Bây giờ, chúng ta phải áp dụng định dạng có điều kiện cho Cột B bằng cách sử dụng cùng một công thức. Để làm điều đó, hãy chọn cột thứ hai (B2: B30), chuyển đến menu ‘Định dạng’ và chọn ‘Định dạng có điều kiện’.
Ngoài ra, hãy nhấp vào nút "Thêm quy tắc khác" trong ngăn "Quy tắc định dạng có điều kiện".
Tiếp theo, xác nhận phạm vi (B2: B30) trong hộp 'Áp dụng cho phạm vi'.
Sau đó, đặt tùy chọn ‘Định dạng ô nếu ..’ thành ‘Công thức tùy chỉnh là’ và nhập công thức bên dưới vào hộp công thức:
= COUNTIF ($ A $ 2: $ A $ 30, $ B2)> 0Ở đây, chúng tôi đang sử dụng phạm vi cột A ($ A $ 2: $ A $ 30) trong đối số đầu tiên và ‘$ B2’ trong đối số thứ hai. Công thức này sẽ kiểm tra giá trị ô trong ‘cột B’ so với mọi ô trong cột A. Nếu tìm thấy một kết quả phù hợp (trùng lặp), thì định dạng có điều kiện sẽ chọn mục đó trong ‘cột B’
Sau đó, chỉ định định dạng trong các tùy chọn ‘Kiểu định dạng’ và nhấp vào ‘Hoàn tất’. Ở đây, chúng tôi đang chọn màu cam cho cột B.
Thao tác này sẽ đánh dấu các mục trong cột B có các mục trùng lặp trong cột A. Bây giờ, bạn đã tìm thấy và đánh dấu các mục trùng lặp giữa hai cột.
Bạn có thể nhận thấy, mặc dù có một bản sao cho ‘Arcelia’ trong cột A, nó không được đánh dấu. Đó là bởi vì giá trị trùng lặp chỉ nằm trong một cột (A) không nằm giữa các cột. Do đó, nó không được đánh dấu.
Đánh dấu các điểm trùng lặp giữa hai cột trong cùng một hàng
Bạn cũng có thể đánh dấu các hàng có cùng giá trị (trùng lặp) giữa hai cột bằng cách sử dụng định dạng có điều kiện. Quy tắc định dạng có điều kiện có thể kiểm tra từng hàng và đánh dấu các hàng có dữ liệu khớp trong cả hai cột. Đây là cách bạn thực hiện việc này:
Trước tiên, hãy chọn cả hai cột mà bạn muốn so sánh, sau đó chuyển đến menu ‘Định dạng’ và chọn ‘Định dạng có điều kiện’.
Trong ngăn Quy tắc định dạng có điều kiện, xác nhận phạm vi trong hộp ‘Áp dụng cho phạm vi’ và chọn ‘Công thức tùy chỉnh là’ từ menu thả xuống ‘Ô công thức nếu ..’.
Sau đó, nhập công thức bên dưới vào hộp "Giá trị hoặc công thức":
= $ A2 = $ B2Công thức này sẽ so sánh hai cột theo từng hàng và đánh dấu các hàng có giá trị giống hệt nhau (trùng lặp). Như bạn có thể thấy, công thức được nhập ở đây chỉ dành cho hàng đầu tiên của phạm vi đã chọn, nhưng công thức sẽ được tự động áp dụng cho tất cả các hàng trong phạm vi đã chọn bằng tính năng định dạng có điều kiện.
Sau đó, chỉ định định dạng từ các tùy chọn ‘Kiểu định dạng’ và nhấp vào ‘Hoàn tất’.
Như bạn có thể thấy, chỉ những hàng có dữ liệu trùng khớp (trùng lặp) giữa hai cột mới được đánh dấu và tất cả các dòng trùng lặp khác sẽ bị bỏ qua.
Đánh dấu các ô trùng lặp trong nhiều cột
Khi làm việc với các bảng tính lớn hơn có nhiều cột, bạn có thể muốn đánh dấu tất cả các bản sao xuất hiện trên nhiều cột thay vì chỉ một hoặc hai cột. Bạn vẫn có thể sử dụng định dạng có điều kiện để đánh dấu bản sao trong nhiều cột.
Đầu tiên, hãy chọn phạm vi của tất cả các cột và hàng bạn muốn tìm kiếm các bản sao thay vì chỉ một hoặc hai cột. Bạn có thể chọn toàn bộ cột bằng cách giữ phím Ctrl, sau đó nhấp vào ký tự ở đầu mỗi cột. Ngoài ra, bạn cũng có thể nhấp vào ô đầu tiên và ô cuối cùng trong phạm vi của mình đồng thời giữ phím Shift để chọn nhiều cột cùng một lúc.
Trong ví dụ, chúng tôi đang chọn A2: C30.
Sau đó, nhấp vào tùy chọn ‘Định dạng’ trong menu và chọn ‘Định dạng có điều kiện’.
Trong quy tắc Định dạng có điều kiện, hãy đặt Quy tắc định dạng thành "Công thức tùy chỉnh là", sau đó nhập công thức sau vào hộp "Giá trị hoặc Công thức":
= countif ($ A $ 2: $ C $ 30, A2)>Chúng tôi đang thêm dấu ‘$’ trước các tham chiếu ô để biến chúng thành các cột tuyệt đối, vì vậy, nó không thay đổi, chúng tôi áp dụng công thức. Bạn cũng có thể nhập công thức mà không có dấu ‘$’, nó hoạt động theo cả hai cách.
Sau đó, chọn định dạng mà bạn muốn đánh dấu các ô trùng lặp bằng cách sử dụng tùy chọn 'Định dạng kiểu'. Ở đây, chúng tôi đang chọn màu tô 'Vàng'. Sau đó, nhấp vào ‘Hoàn tất’.
Điều này sẽ làm nổi bật các bản sao trên tất cả các cột bạn đã chọn, như được hiển thị bên dưới.
Sau khi áp dụng định dạng có điều kiện, bạn có thể chỉnh sửa hoặc xóa quy tắc định dạng có điều kiện bất cứ lúc nào bạn muốn.
Nếu bạn muốn chỉnh sửa quy tắc định dạng có điều kiện hiện tại, hãy chọn bất kỳ ô nào có định dạng có điều kiện, đi tới ‘Định dạng’ trên menu và chọn ‘Định dạng có điều kiện’.
Thao tác này sẽ mở ngăn ‘Quy tắc định dạng có điều kiện’ ở bên phải với danh sách các quy tắc định dạng được áp dụng cho lựa chọn hiện tại. Khi đưa chuột vào quy tắc, nó sẽ hiện ra nút xóa, bạn bấm vào nút xóa để xóa quy tắc. Hoặc, nếu bạn muốn chỉnh sửa quy tắc hiện đang hiển thị, hãy nhấp vào chính quy tắc đó.
Nếu bạn muốn thêm một định dạng có điều kiện khác vào quy tắc hiện tại, hãy nhấp vào nút "Thêm quy tắc khác".
Đếm số trùng lặp giữa hai cột
Đôi khi, bạn muốn đếm số lần một giá trị trong một cột lặp lại trong một cột khác. Nó có thể được thực hiện dễ dàng bằng cách sử dụng cùng một hàm COUNTIF.
Để tìm số lần một giá trị trong cột A tồn tại trong cột B, hãy nhập công thức sau vào một ô trong cột khác:
= COUNTIF ($ B $ 2: $ B $ 30, $ A2)Nhập công thức này vào ô C2. Công thức này đếm số lần giá trị trong ô A2 tồn tại trong cột (B2: B30) và trả về số lượng trong ô C2.
Khi bạn gõ công thức và nhấn Enter, tính năng Tự động điền sẽ xuất hiện, hãy nhấp vào ‘Dấu đánh dấu’ để tự động điền công thức này vào các ô còn lại (C3: C30).
Nếu tính năng tự động điền không xuất hiện, hãy nhấp vào hình vuông màu xanh lam ở góc dưới cùng bên phải của ô C2 và kéo nó xuống để sao chép công thức trong ô C2 sang các ô C3: C30.
Cột 'So sánh 1' (C) bây giờ sẽ hiển thị cho bạn số lần mỗi giá trị tương ứng trong cột A xuất hiện trong cột B. Ví dụ: giá trị của A2 hoặc "Franklyn" không được tìm thấy trong cột B, do đó, Hàm COUNTIF trả về “0”. Và giá trị “Loreta” (A5) được tìm thấy hai lần trong cột B, do đó, nó trả về “2”.
Bây giờ, chúng ta phải lặp lại các bước tương tự để tìm số lượng trùng lặp của cột B. Để làm điều đó, hãy nhập công thức sau vào ô D2 trong cột D (So sánh 2):
= COUNTIF ($ A $ 2: $ A $ 30, $ B2)Trong công thức này, hãy thay thế phạm vi từ ‘$ B $ 2: $ B $ 30’ thành ‘$ A $ 2: $ A $ 30’ và ‘$ B2’ thành ‘$ A2’. Hàm đếm số lần giá trị trong ô B2 tồn tại trong cột A (A2: A30) và trả về số lần giá trị trong ô D2.
Sau đó, tự động điền công thức vào phần còn lại của các ô (D3: D30) trong cột D. Bây giờ, 'So sánh 2' sẽ hiển thị cho bạn số lần mỗi giá trị tương ứng trong cột B xuất hiện trong cột A. Ví dụ: , giá trị của B2 hoặc “Stark” được tìm thấy hai lần trong cột A, do đó, hàm COUNTIF trả về “2”.
Ghi chú: Nếu bạn muốn đếm các bản sao trên tất cả các cột hoặc nhiều cột, bạn chỉ cần thay đổi phạm vi trong đối số đầu tiên của hàm COUNTIF thành nhiều cột thay vì chỉ một cột. Ví dụ: thay đổi phạm vi từ A2: A30 thành A2: B30, sẽ đếm tất cả các bản sao trong hai cột thay vì chỉ một.
Đó là nó.