Lọc trùng nhau trong 2 sheet google sheet

Bến Hà Trương
Oct 20 2020

Nội dung chính

• 1 Đánh dấu các mục trùng lặp trong Google Sheets bằng Định dạng có điều kiện
• 1.1 Hàm COUNTIF trong Google Sheets
• 1.2 Đánh dấu toàn bộ hàng có chứa giá trị trùng lặp
• 1.3 So sánh các cột trùng lặp
• 2 Xóa các hàng trùng lặp trong Google Sheets với Google Apps script
• 2.4 Xóa hàng trùng lập bằng tính năng Remove Duplicates có sẵn trong Google Sheets
• 2.5 Xóa các bản sao trong Google Sheets bằng Apps Scripts

Trong bài viết này, chúng ta sẽ tìm hiểu cách nào để đánh dấu nhanh các ô có giá trị trùng lập trong Google Sheets bằng các sử dụng định dạng có điều kiện, sau đó xóa các ô trùng lập bằng Google Script.

Giả sử bạn có danh sách các mail đã gửi, và trong đó nhiều địa chỉ email trùng lặp, bạn cần dọn dẹp dữ liệu trong Google Sheets của mình bằng cách xóa các địa chỉ trùng lặp trước khi gửi email.

Top khóa học Google Sheets được học nhiều

Đánh dấu các mục trùng lặp trong Google Sheets bằng Định dạng có điều kiện

Bạn có thể sử dụng Định dạng có điều kiện trong Google Sheets  kết hợp với  công thức COUNTIF để nhanh chóng đánh dấu các ô chứa giá trị trùng lặp.

Bạn có thể tìm kiếm và đánh dấu các mục trùng lập theo các bước sau.

1. Mở Google Sheets chứa dữ liệu của bạn và chuyển đến menu Format.
2. Chọn Định dạng có Điều kiện (Conditional Formatting) từ menu mở rộng. Điều này sẽ giúp chúng ta thay đổi màu của ô, hàng hoặc cột đáp ứng một tiêu chí cụ thể.
3. Trong hộp nhập Apply to Range, hãy thêm phạm vi ô chứa các bản sao có thể có. Trong ví dụ của chúng ta, địa chỉ email nằm trong cột A nên bạn có thể đặt A:A để chỉ định toàn bộ cột A.
4. Trong phần ‘Format cells if’,  hãy chọn" Công thức tùy chỉnh là “Custom formula is” xuống khi đặt công thức thành = COUNTIF (A:A, A1) > 1

Nhấp vào nút Done và bạn sẽ ngay lập tức nhận thấy rằng tất cả các ô trùng lặp được đánh dấu như hiển thị trong ảnh chụp màn hình bên dưới.

Hàm COUNTIF trong Google Sheets

Hàm COUNTIF trong Google Sheets (và Microsoft Excel) về cơ bản đếm số ô trong phạm vi đáp ứng một tiêu chí cụ thể. Ví dụ: = COUNTIF (A:A, "apple") sẽ đếm số ô có chứa từ apple.

Hàm này cũng có thể chấp nhận các ký tự đại diện, vì vậy = COUNTIF (A:A, "apple?")sẽ đếm các ô có chứa từ apple hoặc apple. Hoặc nói = COUNTIF (A:A, "*@gmail.com") và công thức này sẽ đánh dấu tất cả địa chỉ email kết thúc bằng gmail.

Xin lưu ý rằng hàm COUNTIF không phân biệt chữ hoa chữ thường nên các giá trị như gmail.com và Gmail.com được xem là trùng lặp.

Đánh dấu toàn bộ hàng có chứa giá trị trùng lặp

Nếu bạn quan sát kỹ ảnh chụp màn hình bên trên, chỉ các ô cụ thể có giá trị trùng lặp mới được đánh dấu thông qua định dạng có điều kiện.

Tuy nhiên, nếu bạn muốn Google Sheets làm nổi bật toàn bộ hàng bảng tính có chứa các giá trị trùng lặp, chúng ta cần điều chỉnh một chút quy tắc định dạng.

1. Đi tới hộp nhập Apply to Range và chỉ định toàn bộ phạm vi bảng tính, không chỉ cột chứa các bản sao.
2. Trong công thức tùy chỉnh, hãy sử dụng tham chiếu tuyệt đối cho phạm vi và cũng thay đổi tiêu chí để sử dụng $A1 thay thế A1. Khi chúng ta sử dụng $A1, chúng tôi đang yêu cầu Google Sheets chỉ thay đổi hàng nhưng khóa cột.

Công thức phát hiện trùng lặp mới sẽ là = COUNTIF ($A$1:$C$50, $A1) > 1 

So sánh các cột trùng lặp

Nếu bạn muốn xác định các bản sao bằng cách so sánh dữ liệu trong nhiều cột của Google Sheets, hãy sử dụng COUNTIFS thay thế COUNTIF.

Ví dụ: nếu cột A là Địa chỉ Email và cột B là Tên Công ty và bạn chỉ muốn đánh dấu các ô trùng lặp khi kết hợp các giá trị trong Cột A và B giống hệt nhau, công thức mới có thể được viết là = COUNTIFS (A:A, A1, B:B, B1) >1

Xóa các hàng trùng lặp trong Google Sheets với Google Apps script

Bây giờ chúng ta đã tìm ra một phương pháp đơn giản để đánh dấu các hàng trùng lặp trong Google Sheets, nhiệm vụ tiếp theo là xóa tất cả các hàng trùng lặp.

Có hai cách để thực hiện - sử dụng Google Apps script hoặc sử dụng tính năng tích hợp sẵn của Google sheets để loại bỏ các bản sao.

Xóa hàng trùng lập bằng tính năng Remove Duplicates có sẵn trong Google Sheets

Đầu tiên, bạn hãy đánh dấu toàn bộ cột trong Google Sheets có chứa dữ liệu trùng lặp. Tiếp theo, chuyển đến menu Data và chọn tùy chọn Remove Duplicates.

Chọn cột nào cần bao gồm và phạm vi đã chọn có bất kỳ hàng tiêu đề nào hay không. Nhấp vào Remove duplicates và danh sách của bạn sẽ được dọn sạch chỉ trong một lần. Giống như với hàm COUNTIF, Google Sheets sẽ không quan tâm tới chữ hoa thương và định dạng khi xác định các bản sao.

Xóa các bản sao trong Google Sheets bằng Apps Scripts

Nếu bạn thích tự động hóa, đây là một đoạn mã nhỏ sẽ xóa các bản sao trong Google Sheets đang hoạt động của bạn dựa trên dữ liệu trong cột đầu tiên.

function removeDuplicateRows() {
  SpreadsheetApp.getActiveSpreadsheet()
.getActiveSheet()
.getDataRange()
.removeDuplicates([1]);
}

Hy vọng qua bài viết này, bạn đã biết cách đánh dấu và xóa các hàng trùng lập trong Google Sheets. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.