Bạn đã bao giờ thử so sánh nội dung của hai Trang tính Google khác nhau để tìm ra sự khác biệt chưa? Nếu đó là các trang tính nhỏ với một vài hàng và cột dữ liệu, nó có thể không khó như vậy. Tuy nhiên, với lượng lớn dữ liệu – một vấn đề hoàn toàn khác!
May mắn thay, phần mềm xử lý dữ liệu như Google Trang tính được lập trình tốt hơn mắt người và phát hiện ra những điểm khác biệt và tương đồng nhỏ giữa các trang tính.
Mặc dù Google Trang tính không thực sự cung cấp bất kỳ công cụ trực tiếp nào để so sánh hai trang tính, nhưng nó có thể được thực hiện bằng cách sử dụng một số công cụ mà họ cung cấp, cũng như một chút sáng tạo.
Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn cách sử dụng Google Trang tính để so sánh hai trang tính. Để chứng minh những chủ đề này, chúng tôi sẽ sử dụng dữ liệu mẫu sau, bao gồm 5 bản ghi (hàng) mỗi bản ghi:
Mẫu 1:
Mẫu 2:
Giả sử Mẫu 1 nằm trên trang tính có tên Trang tính 1 và Mẫu 2 nằm trên trang tính có tên Trang tính 2.
Cách truy cập các ô từ một trang tính khác trong Google Trang tính
Nếu bạn muốn truy cập các ô từ một trang tính khác, bạn có thể sử dụng tham chiếu ô có tiền tố là tên trang tính và theo sau là dấu chấm than (‘!’).
Vì vậy, nếu bạn muốn truy cập ô A2 của sheet 1 (SHEET 1), bạn có thể sử dụng tham chiếu ô “Sheet1! A2 ”.
Tương tự, đối với một tham chiếu ô tuyệt đối, bạn có thể sử dụng “Sheet1! $ A $ 2 ”.
Cách so sánh hai trang tính trong Google Trang tính để tìm ô không khớp
Nếu bạn có hai trang dữ liệu và bạn muốn biết dữ liệu hoặc nội dung có giống nhau trong cả hai hay không, bạn sẽ chỉ cần sử dụng một công thức và dán nó trên trang thứ ba.
Bạn có thể sử dụng công thức sau:
= IF (Sheet1! A1 Sheet2! A1, Sheet1! A1 & “|” & Sheet2! A1, “”)
Hàm IF (IF) yêu cầu ba tham số:
- điều kiện
- văn bản hoặc công thức được trả về nếu điều kiện là TRUE
- văn bản hoặc công thức được trả về nếu điều kiện là FALSE
Ở đây chúng tôi đã chỉ định điều kiện là: Sheet1! A1 Trang tính2! A1, có nghĩa là “ô A1 từ SHEET1 không bằng ô A1 từ SHEET2”.
Nếu điều kiện là TRUE, chúng tôi đã chỉ định rằng hàm phải trả về giá trị trong SHEET1, ô A1, theo sau là ký tự ống (‘|’), theo sau là giá trị trong SHEET2, ô A1 hoặc “Sheet1! A1 & »| & Sheet2! A1 ”.
Nếu điều kiện là FALSE, hàm sẽ chỉ hiển thị một ô trống (“”).
Khi bạn dán công thức trên vào tất cả các ô của trang tính thứ ba (chúng ta hãy gọi nó là “Sheet3”), nó sẽ hiển thị chính xác các ô nào khác nhau cũng như chúng khác nhau như thế nào, như được hiển thị bên dưới:
Hãy xem bạn cần làm gì nếu muốn áp dụng điều này vào dữ liệu mẫu của chúng tôi:
- Tạo trang tính thứ ba (bằng cách nhấp vào biểu tượng “+” ở cuối cửa sổ. Bạn sẽ thấy một tab mới có tên “Trang tính3”.
- Trong ô đầu tiên của trang tính đó (ô A1), nhập công thức: = IF (Sheet1! A1 Sheet2! A1, Sheet1! A1 & “|” & Sheet2! A1, “”)
- Sao chép công thức bằng cách nhấn CTRL + C trên bàn phím của bạn.
- Chọn tất cả các ô trong Trang tính 3 bằng cách nhấn CTRL + A trên bàn phím của bạn hoặc bằng cách nhấp vào nút Chọn Tất cả ở góc trên cùng bên trái của trang tính.
- Nhấn CTRL + V trên bàn phím của bạn để dán công thức vào tất cả các ô đã chọn.
Bây giờ bạn sẽ thấy rằng tất cả các ô giống nhau trong Sheet1 và Sheet2 đều trống. Tuy nhiên, các ô có nội dung khác nhau phải hiển thị các giá trị của ô đó từ cả hai trang tính, được phân tách bằng ‘|’ tính cách.
Cách so sánh hai trang tính để khớp chuỗi chính xác
Một cách sử dụng rất phổ biến của Google Trang tính là so sánh các trang tính theo từng hàng để xác định hàng (hoặc bản ghi) nào khớp và hàng nào không khớp.
Trong ví dụ của chúng tôi, hãy thử so sánh từng dòng giữa Sheet1 và Sheet2. Chúng tôi sẽ hiển thị kết quả trên trang thứ ba có tên là Sheet3. Nếu một dòng khớp trên cả hai sheet, chúng ta sẽ thấy từ “Match” được hiển thị trong dòng tương ứng của Sheet3. Nếu không, thì chúng ta sẽ thấy từ “Not Matching” (“Không khớp”) ở dòng tương ứng.
Để thực hiện việc này, chúng ta sẽ cần sử dụng công thức bao gồm hàm IF (IF) với AND lồng nhau. Hàm AND nhận hai hoặc nhiều điều kiện làm tham số. Nó trả về TRUE nếu tất cả các điều kiện là TRUE và FALSE nếu ngược lại.
Nếu cả hai Bảng tính Google của bạn đều có 2 cột để so sánh, thì bạn cần thực hiện như sau:
- Tạo trang tính thứ ba (bằng cách nhấp vào biểu tượng “+” ở cuối cửa sổ.
- Trong ô A2 của trang tính này, hãy nhập công thức: = IF (AND (Sheet1! A2 = Sheet2! A2, Sheet1! B2 = Sheet2! B2), “Khớp”, “Không khớp”)
- Sao chép công thức vào phần còn lại của cột bằng cách kéo chốt điền của ô.
Bây giờ bạn sẽ thấy từ “Khớp” nơi các hàng khớp của hai trang tính khớp với nhau và từ “Không khớp” nếu không.
Lưu ý rằng ngay cả khi các tên trong dòng cuối cùng của các mẫu của chúng tôi khớp nhau, vì các phần tử tương ứng trong cùng một dòng không khớp, hàm trả về kết quả “Không khớp”.
Để phương pháp này hoạt động, cả hai trang tính phải có cùng số cột. Nếu trang tính của bạn có nhiều hơn hai cột, bạn có thể mở rộng công thức bằng cách thêm các tham số bổ sung để so sánh từng ô trong cùng một hàng. Vì vậy, nếu bạn có 3 cột, công thức của bạn sẽ giống như sau:
= IF (AND (Sheet1! A2 = Sheet2! A2, Sheet1! B2 = Sheet2! B2, Sheet1! C2 = Sheet2! C2), “Khớp”, “Không khớp”)
Vân vân.
Cách so sánh hai trang tính và đánh dấu các hàng phù hợp
Nếu bạn muốn đánh dấu các hàng phù hợp trong một trong hai trang tính thay vì hiển thị kết quả trong một trang tính riêng biệt, bạn có thể sử dụng tính năng Định dạng có Điều kiện trong Google Trang tính.
Định dạng có điều kiện là một kỹ thuật cho phép bạn định dạng các ô dựa trên một điều kiện. Chúng ta có thể sử dụng công thức tương tự như đã thảo luận trong phần trước.
Tuy nhiên, định dạng có điều kiện trong Google Trang tính không cho phép bạn tham chiếu đến các ô từ một trang tính khác.
Một cách để giải quyết vấn đề này là sử dụng hàm INDIRECT để truy cập một cách gián tiếp vào trang tính khác. Điều này sẽ trở nên rõ ràng hơn khi chúng tôi chứng minh phương pháp từng bước.
Dưới đây là cách định dạng có điều kiện có thể được sử dụng để so sánh hai trang tính và đánh dấu các hàng phù hợp trên một trong số chúng:
Bây giờ bạn sẽ thấy các đường phù hợp được đánh dấu bằng màu đã chọn.
Công thức được sử dụng ở đây so sánh hai cột của mỗi hàng trong Trang tính1 và Trang tính2. Nó sử dụng hàm INDIRECT để truy xuất các tham chiếu ô trong cột A và B của Sheet1. Sau đó, công thức sẽ kiểm tra xem các ô tương ứng với cả hai cột trong mỗi hàng có khớp hay không. Nếu có, thì dòng được đánh dấu.
Ghi chú. Nếu trang tính của bạn có nhiều hơn hai cột, bạn có thể mở rộng công thức bằng cách thêm các tham số bổ sung để so sánh từng cột. Vì vậy, nếu bạn có 3 cột, công thức của bạn sẽ như thế này:
“= AND (A2 = INDIRECT (” Sheet1! A2: A “), B2 = INDIRECT (” Sheet1! B2: B “), C2 = INDIRECT (” Sheet1! C2: C “))”
Vân vân.
Ghi chú. Để chỉ đánh dấu các dòng không khớp, bạn có thể thay thế các ký tự “=” trong công thức bằng “”.
Cách so sánh hai trang tính để tìm dữ liệu trùng lặp
Ba phương pháp đầu tiên về cơ bản đã tìm thấy các chuỗi trên cả hai trang tính khớp chính xác và theo cùng một thứ tự. Tuy nhiên, trong thực tế, các hàng trên cả hai trang tính không phải lúc nào cũng được sắp xếp theo cùng một cách. Vì vậy, hãy xem một tình huống mà chúng ta muốn so sánh hai trang tính và tìm ra hàng nào của Trang tính1 được lặp lại trên Trang tính2, bất kể thứ tự nào.
Nói cách khác, hãy sử dụng định dạng có điều kiện để tìm dữ liệu trùng lặp trong Sheet2. Đây là các bước:
- Chọn cột trống ngay sau cột ngoài cùng bên phải của Trang tính 2. Giả sử chúng tôi đã sử dụng cột C trong ví dụ của chúng tôi.
- Trong hàng thứ hai của cột này, hãy dán công thức: = IFERROR (CELL (“địa chỉ”, INDEX (Sheet1! $ A $ 1: $ A $ 9, MATCH (A2, Sheet1! $ A $ 1: $ A $ 9,0)), 1)), FALSE) .. Công thức này sẽ trả về địa chỉ của ô trên Trang tính 1, cột A, tương ứng với nội dung của ô A2 trên trang tính hiện tại. Nếu ô tương ứng không tồn tại trên Trang tính 1, công thức sẽ trả về FALSE.
- Sao chép công thức này vào các hàng còn lại của cột bằng cách kéo chốt điền. Đây là những gì bạn sẽ thấy:
- Bây giờ chúng ta có thể bắt đầu áp dụng định dạng có điều kiện. Lặp lại các bước để định dạng có điều kiện trong phần trước (từ bước 1 đến bước 10), nhưng thay thế công thức ở bước 7 bằng: “= IF (NOT (C2 = FALSE), IF (OFFSET (INDIRECT (C2), 0,1) = B2, TRUE, FALSE), FALSE) ”
Bây giờ bạn sẽ thấy tất cả các hàng trùng lặp của dữ liệu trong Sheet2 được đánh dấu bằng màu đã chọn.
Ghi chú. Nếu bạn muốn ẩn cột có chứa các địa chỉ phù hợp, bạn có thể chỉ cần nhấp chuột phải vào cột và chọn “Ẩn Cột” từ trình đơn ngữ cảnh kết quả.
Giải thích công thức
Công thức ở bước 4 có vẻ hơi phức tạp, nhưng bạn có thể tìm ra nếu chúng tôi chia nhỏ công thức cho bạn. Hãy phân tích từng lớp công thức, bắt đầu với lớp bên trong và di chuyển ra ngoài:
OFFSET (INDIRECT (C2), 0,1)
Hàm này trả về giá trị trong cột B của Sheet1 tương ứng với địa chỉ nhận được trong ô C2. Hãy nhớ rằng C2 chứa địa chỉ của ô có tên trong Sheet1 khớp với tên trong ô A2. Vì vậy, công thức này sẽ chỉ trả về giá trị phần tử tương ứng với tên phù hợp.
IF (OFFSET (INDIRECT (C2), 0,1) = B2, TRUE, FALSE)
Bây giờ, hàm IF sẽ kiểm tra xem giá trị này trong Trang tính1, Cột B có khớp với giá trị tương ứng trong Trang tính2, Cột B. Nếu đúng như vậy, thì công thức trả về TRUE, nếu không thì trả về FALSE.
IF (NOT (C2 = FALSE), IF (OFFSET (INDIRECT (C2), 0,1) = B2, TRUE, FALSE), FALSE)
Công thức này bây giờ sẽ kiểm tra xem C2 có chứa địa chỉ ô hay giá trị FALSE hay không. Nếu nó chứa địa chỉ ô, điều đó có nghĩa là tên tương ứng đã được tìm thấy trong Sheet1. Trong trường hợp này, công thức trả về TRUE hoặc FALSE tùy thuộc vào kết quả của hàm IF lồng nhau bên trong. Nếu C2 chứa FALSE, công thức cũng trả về FALSE.
Nói cách khác, toàn bộ công thức trước tiên sẽ kiểm tra xem có giá trị Tên trùng lặp trong Sheet1 hay không. Nếu không, FALSE được trả về. Nếu tồn tại một tên trùng lặp, nó sẽ kiểm tra xem giá trị phần tử tương ứng có khớp hay không. Nếu vậy, TRUE được trả về; ngược lại, FALSE.
Ghi chú. Nếu trang tính của bạn có nhiều hơn hai cột, bạn có thể mở rộng công thức bằng cách đưa hàm AND vào hàm IF lồng nhau. Ví dụ: nếu bạn có 3 cột trên mỗi trang tính, bạn có thể thay đổi công thức thành:
IF (NOT (D2 = FALSE), IF (OFFSET (AND (INDIRECT (D2), 0,1) = B2, INDIRECT (D2), 0,2) = C2), TRUE, FALSE), FALSE)
Ở đây chúng tôi giả định rằng địa chỉ của các ô phù hợp nằm trong cột D của Trang 2, không phải cột C.
Cách so sánh hai trang tính để tìm dữ liệu còn thiếu
Cuối cùng, điều gì sẽ xảy ra nếu bạn muốn đánh dấu các dòng bị thiếu thay vì các dòng trùng lặp?
Trong trường hợp này, về cơ bản bạn chỉ cần chọn các hàng đối diện. Vì vậy, bạn sẽ chỉ cần thêm một hàm NOT bên ngoài công thức định dạng có điều kiện đã thảo luận trong phần trước.
Nói cách khác, công thức của bạn sẽ giống như sau:
“= NOT (IF (NOT (C2 = FALSE)), IF (OFFSET (INDIRECT (C2), 0,1) = B2, TRUE, FALSE), FALSE))”
Đây là kết quả bạn sẽ nhận được:
phát hiện
Trong hướng dẫn này, chúng tôi đã thảo luận về nhiều cách khác nhau để so sánh hai trang tính để tìm sự khác biệt. Các công thức chúng tôi đã sử dụng có vẻ hơi phức tạp, nhưng chúng tôi khuyến khích bạn tự mình thử để hiểu rõ hơn về cách hoạt động của chúng.
