Cách so sánh đối soát dữ liệu trên hai trang tính hoặc hai cột trong Google sheets

Trong bài viết này, chúng tôi sẽ chia sẻ cho bạn một số cách nhanh chóng đối soát dữ liệu trong hai bảng tính hoặc hai cột trong Google Sheets. Bắt đầu từ việc so sánh hai bộ dữ liệu, chúng ta sẽ xác định dữ liệu nào đang thiếu và có thể trích suất từ đâu, sau đó tìm hiểu cách hợp nhất hai bộ dữ liệu thành một.

So sánh hai cột hoặc trang tính trong Google Sheets

Một trong những nhiệm vụ bạn có thể phải thường xuyên thực hiện trong công việc hàng ngày là đối soát các cột hoặc trang tính để tìm điểm trùng khớp hoặc khác biệt. Sau đó xác định những sai lệch đó đến từ đâu.

Chúng ta sẽ bắt đầu với việc so sánh hai ô trong Google Sheets. Cách này cho phép bạn quét toàn bộ cột, từng hàng một.

Ví dụ 1. So sánh hai ô trong Google Sheets

Đối với ví dụ đầu tiên này, bạn sẽ cần một cột trợ giúp để nhập công thức vào hàng đầu tiên của dữ liệu để so sánh:

=A2=B2

Nếu các ô khớp nhau, bạn sẽ thấy giá trị TRUE, nếu không sẽ là FALSE. Để kiểm tra tất cả các ô trong một cột, hãy sao chép công thức đơn giản này xuống các hàng khác.

Trong trường hợp cần so sánh các cột từ các tệp khác nhau, bạn chỉ cần sử dụng hàm IMPORTRANGE:

=A2=IMPORTRANGE("spreadsheet_url","Sheet1!A2")

Ví dụ 2. So sánh hai danh sách để tìm các điểm trùng khớp và khác biệt

Giải pháp đơn giản nhất cho ví dụ này là sử dụng hàm IF. Bạn sẽ có thể đặt trạng thái chính xác cho các ô giống nhau và khác nhau :

=IF(A2=B2,"Match","Differ")

Nếu dữ liệu của bạn được viết hoa viết thường khác nhau và bạn cần phần biệt chúng, hãy sử dụng công thức bên dưới.

=IF(EXACT(A2,B2),"Match","Differ")

Trong đó hàm EXACT xem xét trường hợp viết hoa viết thường và tìm kiếm các thông tin nhận dạng hoàn chỉnh.

Nếu bạn cần xác định các hàng có ô trùng lặp, hãy sử dụng công thức sau:

=IF(A2=B2,"Match","")

Để chỉ đánh dấu các hàng có bản ghi duy nhất giữa các ô trong hai cột, hãy sử dụng công thức này:

=IF(A2=B2,"","Differ")

Ví dụ 3. So sánh hai cột trong Google Sheets

Bạn không thíc kéo công thức xuống từng hàng? Không sao, chúng ta sẽ dùng công thức mảng. Đầu tiên, bạn cần tạo công thức mảng IF trong ô đầu tiên trong cột trợ giúp.

=ArrayFormula(IF(A2:A=C2:C,"","Differ"))

Công thức IF này ghép nối mỗi ô của cột A với cùng một hàng trong cột C để đánh dấu những vị trí khác biệt. Điều thú vị về công thức mảng này là nó tự động đánh dấu từng hàng cùng một lúc.

Trong trường hợp bạn muốn đặt tên cho các hàng có các ô giống nhau, hãy điền vào đối số thứ hai của công thức thay vì đối số thứ ba:

=ArrayFormula(IF(A2:A=C2:C,"Match",""))

Ví dụ 4. So sánh hai Trang tính Google để tìm sự khác biệt

Thông thường, bạn cần so sánh hai cột trong Google Sheets nằm trong một bảng tính lớn. Hoặc chúng có thể là các trang tính hoàn toàn khác nhau như báo cáo, bảng giá, ca làm việc mỗi tháng,...  Với những trường hợp này, rõ ràng, bạn không đủ khả năng tạo cột trợ giúp hoặc có thể khá khó quản lý chúng.

Nếu đang phải đối mặt với khó khăn này, đừng lo lắng, bạn vẫn có thể đánh dấu sự khác biệt giữa các trang tính khác nhau

Dưới đây là hai bảng tính với các sản phẩm và giá của chúng. Chúng ta sẽ định vị tất cả các ô có nội dung khác nhau giữa các bảng này

Hãy bắt đầu với việc tạo một trang tính mới và nhập công thức bên dưới vào ô A1:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

Lưu ý, bạn chọn phạm vi bằng kích thước của bảng lớn nhất. Sau đó, bạn sẽ chỉ thấy những ô khác nhau về nội dung. Công thức cũng sẽ kéo các bản ghi từ cả hai bảng và phân tách chúng bằng một ký tự bạn nhập vào công thức:

Nếu các trang tính để so sánh nằm trong các tệp khác nhau, một lần nữa, chỉ cần kết hợp hàm IMPORTRANGE :

=IF(Sheet1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),"")

Cách so sánh dữ liệu trong hai trang tính và tìm nạp các dữ liệu bị thiếu

So sánh hai Google Sheets để sự khác biệt và trùng lập chỉ là một nửa công việc, nhưng còn việc  tìm kiếm dữ liệu bị thiếu thì sao? Có những hàm đặc biệt giúp bạn hoàn thành nhiệm vụ này, ví dụ, VLOOKUP. Hãy xem những gì bạn có thể làm.

Tìm dữ liệu bị thiếu

Ví dụ 1

Hãy tưởng tượng bạn có hai danh sách sản phẩm (trong ví dụ bên dưới là cột A và B, nhưng chúng có thể đơn giản nằm trên các trang tính khác nhau). Bạn cần tìm những thứ được trình bày trong danh sách đầu tiên nhưng không phải trong danh sách thứ hai. Công thức ISERROR này sẽ giúp bạn thực hiện điều đó.

=ISERROR (VLOOKUP (A2,$B:$B,1,0))

Công thức hoạt động như sau:

• VLOOKUP tìm kiếm sản phẩm từ A2 trong danh sách thứ hai.
• Nếu nó ở đó, hàm trả về tên sản phẩm. Hoặc nếu không, bạn sẽ gặp lỗi # N / A nghĩa là không tìm thấy giá trị trong cột B.
• ISERROR kiểm tra hàm VLOOKUP trả về và hiển thị cho bạn TRUE nếu đó là giá trị và FALSE nếu đó là lỗi.
• Do đó, các ô có FALSE là những gì bạn đang tìm kiếm. Sao chép công thức sang các ô khác để kiểm tra từng sản phẩm từ danh sách đầu tiên:

Nếu các cột của bạn nằm trong các trang tính khác nhau, công thức của bạn sẽ tham chiếu đến một trong số chúng:

=ISERROR (VLOOKUP (A2,Sheet2!$B:$B,1,0))

Bạn có thể sử dụng công thức mảng để tự động điền vào tất cả các ô với kết quả:

=ArrayFormula (ISERROR (VLOOKUP (A2:A10,$B:$B,1,0)))

Ví dụ 2

Một giải pháp thông minh khác mà bạn có thể thứ là đếm tất cả các lần xuất hiện của sản phẩm từ A2 trong cột B:

=IF(COUNTIF($B:$B, $A2)=0, "Not found", "")

Nếu hoàn toàn không có gì để đếm, hàm IF sẽ đánh dấu các ô bằng Not found . Các ô khác sẽ vẫn trống:

Ví dụ 3

Ở đâu có VLOOKUP, ở đó có MATCH. Bạn biết điều đó, phải không?

Đây là công thức để so khớp các sản phẩm thay vì đếm:

=IF (ISERROR (MATCH($A2,$B:$B,0)), "Not found", "")

Vui lòng chỉ định phạm vi chính xác của cột thứ hai nếu giá trị cột C không thay đổi:

=IF (ISERROR (MATCH ($A2,$B2:$B28,0)), "Not found", "")

Kéo dữ liệu phù hợp

Ví dụ 1

Nhiệm vụ của bạn có thể phức tạp hơn một chút: bạn có thể cần lấy tất cả thông tin còn thiếu cho các bản ghi chung cho cả hai bảng, ví dụ như cập nhật giá cả. Nếu vậy, bạn sẽ cần đặt hàm MATCH ben trong INDEX :

=INDEX ($E:$E, MATCH ($A2,$D:$D,0))

Công thức so sánh dữ liệu trong cột A với trái cây trong cột D. Đối với mọi thứ tìm thấy, nó kéo giá từ cột E sang cột B.

Ví dụ 2
 

Như bạn có thể đoán, một ví dụ khác sẽ sử dụng hàm VLOOKUP của Google Sheets mà chúng ta đã vô cùng quen thuộc.

Để tham chiếu công thức Vlookup của bạn đến một trang tính khác trong cùng một bảng tính, hãy đặt tên bảng tính theo sau là dấu chấm than (!) Trước tham chiếu phạm vi. Ví dụ:

=VLOOKUP (A2, Sheet4!$A$2:$B$7, 2, false)

Công thức sẽ tìm kiếm giá trị trong A2 trong phạm vi A2: A7 trên Trang tính 4 và trả về giá trị khớp từ cột B ( cột thứ 2 trong phạm vi ).

Nếu tên trang tính bao gồm dấu cách hoặc các ký tự không phải chữ cái, hãy đảm bảo đặt nó trong dấu ngoặc kép. Ví dụ:

=VLOOKUP (A2, 'Lookup table'!$A$2:$B$7, 2, false)

Thay vì nhập tham chiếu đến một trang tính khác theo cách thủ công, bạn có thể yêu cầu Google Sheets tự động chèn nó cho bạn. Đối với điều này, hãy bắt đầu nhập công thức Vlookup của bạn và khi nói đến đối số phạm vi, hãy chuyển sang trang tính tra cứu và chọn phạm vi bằng chuột.

Thao tác này sẽ thêm một tham chiếu phạm vi vào công thức và bạn sẽ chỉ phải thay đổi tham chiếu tương đối (mặc định) thành tham chiếu tuyệt đối. Hãy nhập dấu $ trước ký tự cột và số hàng hoặc chọn tham chiếu và nhấn F4 để chuyển đổi giữa các loại tham chiếu khác nhau.

Sử dụng định dạng có điều kiện để so sánh dữ liệu trong Google Sheets

Có một cách tiêu chuẩn khác mà Google cung cấp cho bạn để so sánh dữ liệu - bằng cách tô màu các kết quả phù hợp và khác biệt thông qua định dạng có điều kiện.

Phương pháp này làm cho tất cả các bản ghi bạn đang tìm kiếm nổi bật ngay lập tức. Công việc của bạn ở đây là tạo một quy tắc với một công thức và áp dụng nó cho một phạm vi dữ liệu chính xác.

Đánh dấu các mục trùng lặp trong hai cột hoặc trang tính khác nhau

Hãy so sánh hai cột trong Google Sheets để tìm các kết quả phù hợp và chỉ tô màu những ô trong cột A đối sánh với các ô trong cùng một hàng trong cột C:

1. Chọn phạm vi có bản ghi để tô màu (A2: A10 đối với tôi).
2. Đi tới Format > Conditional formatting có điều kiện trong menu bảng tính.
3. Nhập một công thức đơn giản cho quy tắc: =A2=C2
4. Chọn màu để đánh dấu các ô.

Đánh dấu các mục trùng lặp trong hai cột trong Google Trang tính.

Nếu các cột của bạn thay đổi kích thước liên tục và bạn muốn quy tắc xem xét tất cả các mục nhập mới, hãy áp dụng quy tắc đó cho toàn bộ cột (A2: A, giả sử dữ liệu để so sánh bắt đầu từ A2) và sửa đổi công thức như sau:

=AND (A2=C2, ISBLANK(A2)=FALSE)

Thao tác này sẽ xử lý toàn bộ các cột và bỏ qua các ô trống.

Lưu ý, để so sánh dữ liệu từ hai trang tính khác nhau, bạn sẽ phải thực hiện các điều chỉnh khác đối với công thức. Bạn thấy đấy, định dạng có điều kiện trong Google sheets không hỗ trợ tham chiếu trang tính chéo. Tuy nhiên, bạn có thể truy cập các trang tính khác một cách gián tiếp:

=A2=INDIRECT("Sheet2!C2:C")

Trong trường hợp này, hãy đảm bảo chỉ định phạm vi áp dụng quy tắc cho  A2: A10.

So sánh hai trang tính và cột của Google Sheets để tìm điểm khác biệt

Để đánh dấu các bản ghi không khớp với các ô trên cùng một hàng trong một cột khác, thao tác thực hiện giống như các bước trên. Bạn cần chọn phạm vi và tạo quy tắc định dạng có điều kiện. Tuy nhiên, công thức ở đây khác một chút:

=A2<>C2

Một lần nữa, hãy sửa đổi công thức để làm cho quy tắc động (có xem xét tất cả các giá trị mới được thêm vào trong các cột này):

=AND(A2=C2,ISBLANK(A2)=FALSE)

Và sử dụng tham chiếu gián tiếp đến một trang tính khác nếu cột để so sánh ở đó:

=A2<>INDIRECT("Sheet1!C2:C")

Đừng quên chỉ định phạm vi áp dụng quy tắc cho A2: A10.

So sánh hai danh sách có nội dung phân tán

Tất nhiên, nhiều khả năng các bản ghi giống nhau trong các cột của bạn sẽ bị phân tán. Giá trị trong A2 trong một cột sẽ không nhất thiết nằm trên hàng thứ hai của cột khác. Trên thực tế, nó có thể xuất hiện muộn hơn nhiều. Rõ ràng, điều này đòi hỏi một phương pháp tìm kiếm các mục khác.

Ví dụ 1. So sánh hai cột trong Google Sheets và đánh dấu sự khác biệt (duy nhất)

Để đánh dấu các giá trị duy nhất trong mỗi danh sách, bạn cần tạo hai quy tắc định dạng có điều kiện cho mỗi cột.

Cột màu A: =COUNTIF($C$2:$C$9,$A2)=0

Cột màu C: =COUNTIF($A$2:$A$10,$C2)=0

Ví dụ 2. Tìm và đánh dấu các mục trùng lặp trong hai cột trong Google Sheets

Bạn có thể tô màu các giá trị chung sau khi sửa đổi một chút trong cả hai công thức từ ví dụ trước. Chỉ cần làm cho công thức đếm mọi thứ lớn hơn 0.

Công thức cho cột A: =COUNTIF($C$2:$C$9,$A2)>0

Công thức cho cột C:=COUNTIF($A$2:$A$10,$C2)>0

Đánh dấu các giá trị xuất hiện trong cả hai cột.

Hy vọng qua bài viết này, bạn đã biết được thêm một số cách thú vị để làm việc với tệp dữ liệu khổng lồ của mình. 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.