7 cách so sánh hai tập dữ liệu trong Excel cực đơn giản

Khi làm việc với bảng dữ liệu Excel, rất nhiều trường hợp chúng ta cần so sánh hai danh sách hoặc hai tập dữ liệu để tìm các mục bị thiếu hoặc trùng nhau. Đối với Excel, luôn có nhiều cách để thực hiện mọi việc, bao gồm cả việc so sánh dữ liệu. Từ sử dụng các hàm, định dạng có điều kiện cho đến Power Query đều có nhiều tùy chọn để bạn sử dụng. 

Trong bài học Excel online này, Gitiho sẽ cùng với các bạn xem xét một số cách để so sánh hai danh sách trong Excel và chúng ta cũng sẽ xem xét cách so sánh toàn bộ các hàng của một tập dữ liệu.

Để các bạn có thể hiểu đơn giản hơn về việc so sánh này, Gitiho sẽ hướng dẫn chi tiết các cách để so sánh hai bảng dữ liệu Excel. Ví dụ để so sánh Danh sách 1 (List 1) với Danh sách 2 (List 2) như hình dưới đây.

Xem thêm: 

7 cách so sánh hai tập dữ liệu trong Excel

Sử dụng định dạng có điều kiện để so sánh hai cột dữ liệu

Định dạng có điều kiện (Conditional Formatting) sẽ cho phép bạn đánh dấu một ô hoặc dải ô dựa trên các tiêu chí được xác định trước. Cách nhanh nhất và đơn giản nhất để so sánh trực quan hai cột này là sử dụng quy tắc giá trị trùng lặp đánh dấu được xác định trước. 

Cách tạo định dạng có điều kiện để so sánh hai cột dữ liệu

Bước 1: Chọn hai cột dữ liệu. 

Bước 2: Truy cập vào thẻ Home, sau đó bấm nút Conditional Formatting. Trong menu xổ xuống chọn tùy chọn Highlight Cells Rules. Tiếp theo chọn Duplicate values.

Bước 3: Trong hộp thoại Duplicate Values mở ra, bạn có thể xác định định dạng và chọn giữa các giá trị trùng lặp (Duplicate) hoặc duy nhất (Unique values).

Nếu chọn tùy chọn trùng lặp (Duplicate) thì tất cả các mục lặp lại của danh sách 1 và danh sách 2  sẽ được hiển thị theo định dạng màu sắc đã chọn. Từ đây bạn cũng có thể nhanh chóng xem các mục trong danh sách 2 không có trong danh sách 1 vì những mục này không được áp dụng định dạng, giống như hình dưới đây.

Tuy nhiên, bạn cũng có thể chọn định dạng duy nhất (Unique values) chỉ bằng cách chọn tùy chọn Unique trong hộp thoại Duplicate Values.

Như trong ví dụ này, Gitiho đã áp dụng hai định dạng có điều kiện khác nhau. Màu đỏ cho biết các mục trùng lặp và màu xanh lá cây cho biết các mục duy nhất có trong bảng dữ liệu cần so sánh.

Lưu ý: Ví dụ trên không chỉ lấy các ô có dữ liệu mà còn lấy tất cả các cột từ A đến C. Cột B không có dữ liệu, vì vậy nó không thể ảnh hưởng đến kết quả. Tuy nhiên, những ô này có chứa định dạng có điều kiện được áp dụng, vì vậy, cách tốt hơn là chỉ chọn những ô bạn cần.

Xem thêm: Công thức Conditional Formatting trong Excel để tô màu ngày chủ nhật

Cách xóa định dạng có điều kiện

Sau khi áp dụng định dạng có điều kiện để so sánh hai danh sách trên, bạn có thể xóa tất cả định dạng có điều kiện để trả về mặc định ban đầu. Cách thực hiện như sau:

Bước 1: Trước tiên hãy chọn ô hoặc phạm vi. 

Bước 2: Truy cập vào thẻ Home, sau đó bấm nút Conditional Formatting. Trong menu xổ xuống chọn tùy chọn Clear Rules. Cuối cùng chọn Clear Rules from Selected Cells.

Bước 3: Nếu bạn áp dụng nhiều định dạng có điều kiện cùng một lúc và chỉ muốn xóa một trong những định dạng này, hãy chọn tùy chọn Manage Rules từ trình đơn Conditional Formatting thả xuống. Chọn quy tắc bạn muốn xóa trong danh sách rồi chọn Delete Rule.

Bước 4: Bằng cách nhấn nút OK, quy tắc sẽ bị xóa khỏi Manage Rules và các ô sẽ không còn chứa định dạng nữa.

Như vậy đây là cách cơ bản nhất để bạn có thể so sánh hai danh sách trong Excel. Nhanh chóng, đơn giản và hiệu quả. Bạn cũng có thể áp dụng định dạng có điều kiện dựa trên công thức mà chúng ta sẽ xem xét ở phần sau của bài viết này.

So sánh trùng lặp dữ liệu trong Excel bằng hàm MATCH

Có nhiều hàm tra cứu mà bạn có thể sử dụng để so sánh hai phạm vi hoặc danh sách trong Excel. Đầu tiên chúng ta sẽ xem xét hàm MATCH.

Hàm MATCH trả về vị trí tương đối trong danh sách. Một số dựa trên vị trí của nó, nếu được tìm thấy, trong mảng tra cứu.

Cú pháp cho hàm MATCH là:

= MATCH (lookup value, Lookup array, Match type)

Trong đó:

• Lookup value: Là giá trị tra cứu mà bạn muốn tìm đối sánh. 
• Lookup array: Mảng tra cứu là danh sách mà bạn đang tìm kiếm đối sánh. 
• Match type: Kiểu kết hợp cho phép bạn chọn giữa kết hợp chính xác hoặc gần đúng.

Bây giờ để viết một công thức so sánh xem các mục trong danh sách 2 có nằm trong danh sách 1 hay không bằng hàm MATCH bạn thực hiện như sau: Trong ô E3, chúng ta có thể nhập công thức

= MATCH(C2, $A$2:$A$21,0)

Bằng cách điền vào công thức này, đối với các giá trị mà Excel tìm thấy trùng khớp thì vị trí trùng khớp đó sẽ được trả về. Trường hợp không trùng khớp, giá trị trả về sẽ là #N/A.

Thông thường, vị trí tương đối hoặc #N/A không có giá trị đối với chúng ta và chúng ta cần chuyển các giá trị này thành True hoặc False để có thể dễ dàng xác định được lỗi.

Để làm điều này, chúng ta có thể dễ dàng mở rộng công thức Match của mình bằng cách sử dụng một hàm logic. Khi hàm Match trả về một số, chúng ta có thể sử dụng hàm ISNUMBER

= ISNUMBER(MATCH(C2, $A$2:$A$21,0))

Trong trường hợp này, hàng nào trả về giá trị là True có nghĩa là có giá trị trùng khớp và ngược lại.

Xem thêm: Cách dùng hàm VLOOKUP lọc trùng dữ liệu trong Excel nhanh chóng

Sử dụng hàm MATCH hoặc XMATCH trong Excel 365 dưới dạng Dynamic Array 

Nếu đang sử dụng Excel 365, bạn sẽ có các lựa chọn thay thế khác khi sử dụng hàm MATCH để so sánh hai danh sách hoặc dữ liệu. Như với Excel 365 chúng ta sử dụng hàm mảng động Dynamic Array để chuyển một mảng thành giá trị tra cứu của MATCH và kết quả của chúng ta sẽ được mở rộng khi bảng dữ liệu mở rộng. 

Điều này cũng sẽ ngăn chặn việc sao chép công thức và chỉ với 1 công thức, bảng tính của bạn sẽ ít bị lỗi và gọn gàng hơn.

Sử dụng MATCH và Dynamic Array để so sánh 2 danh sách

Dynamic Array – Hàm mảng động trong Excel là một cách mới để lập mô hình bảng tính Excel của bạn. Thay đổi duy nhất đối với công thức đối sánh là thay vì chọn ô C2 làm giá trị tra cứu của chúng tôi, chúng tôi sẽ chọn phạm vi C2:12.

= ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))

Sử dụng hàm XMATCH trong Excel 365 để so sánh hai danh sách

Excel 365 cũng giới thiệu một hàm mới là XMATCH. Cũng giống như hàm MATCH, XMATCH trả về một vị trí tương đối trong danh sách. Điều này xuất hiện dưới dạng các điều kiện mới trong cú pháp công thức, chẳng hạn như chế độ tìm kiếm và các loại đối sánh. Tất nhiên XMATCH cũng được bổ sung những tính năng này so với MATCH tiền nhiệm.

Cú pháp cho XMATCH là:

XMATCH (Lookup Value, Lookup Array, [Match Mode],[Search Mode])

Trong đó:

• Lookup Value: Giá trị tra cứu là giá trị bạn đang tìm kiếm để tìm vị trí tương đối
• Lookup Array: Mảng tra cứu là hàng hoặc cột chứa giá trị tra cứu
• [Match Mode]: Chế độ đối sánh là tùy chọn. Không giống như hàm MATCH cũ, mặc định là một kết hợp chính xác. Bạn cũng có thể chọn giữa các tùy chọn gồm: Đối sánh chính xác hoặc đối sánh nhỏ nhất tiếp theo; Đối sánh chính xác hoặc đối sánh lớn nhất tiếp theo và Đối sánh ký tự đại diện
• [Search Mode]: Chế độ tìm kiếm cũng là tùy chọn. Tùy chọn mặc định (và duy nhất trong hàm MATCH cũ) là nhìn từ trên xuống. Bạn cũng có thể chọn tìm kiếm cuối cùng đến tìm kiếm đầu tiên và tìm kiếm nhị phân. Nếu bạn đang làm việc với các tìm kiếm nhị phân. Tùy chọn đối sánh ký tự đại diện sẽ không hoạt động.

Với hàm XMATCH, chúng ta có thể sử dụng Dynamic Array hoặc tham chiếu ô để tạo công thức, giống như chúng ta đã xem xét với hàm MATCH. Đối với ví dụ này, Gitiho sẽ sử dụng Dynamic Array. 

Công thức sẽ giống với những gì chúng ta đã sử dụng với hàm MATCH ở trên; ngoại trừ không phải chọn 0 cho một kết hợp chính xác như trong XMATCH và đây là cài đặt mặc định. 

Bây giờ chúng ta hãy thay đổi một chút bằng việc tìm kiếm các mục trong Danh sách 2 chứ không phải trong danh sách 1.

Trong trường hợp này, chúng ta có thể sử dụng công thức

= NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))

Xem thêm: Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Tablet - So sánh danh sách trong Excel trong đó phạm vi có thể thay đổi

Trong mỗi công thức mà Gitiho đã xem xét cho đến nay, chúng tôi đã chọn một dải ô trong hàm Match với đối sánh không động. Điều đó có nghĩa là nếu thêm dữ liệu mới vào một trong các danh sách, bạn sẽ phải thực hiện thêm một bước thủ công để cập nhật công thức để bao gồm dữ liệu mới.

Để tránh mất thời gian vì phải cập nhật lại công thức bạn có thể sử dụng cách sau. Đầu tiên bạn sẽ phải chuyển đổi danh sách thành bảng (Tablet), hãy chọn một trong các danh sách và nhấn phím CTRL. Đây là phím tắt để chuyển đổi cột thành bảng, nếu bạn đã chọn cả tiêu đề trong phạm vi ô, hãy đảm bảo tích vào tùy chọn My tablet has headers, rồi bấm nút OK.
 

Bản chất của Tablet là sử dụng cách đặt tên có cấu trúc. Do đó, khi bạn nhập một công thức và chọn một cột từ một bảng, nó sẽ không hiển thị các tham chiếu ô mà là tên cột.

Như công thức dưới đây Gitiho sử dụng hàm XMATCH để tìm các mục trong Danh sách 2 không có trong Danh sách 1, trong đó đã viết công thức này bằng cách sử dụng tham chiếu bảng vừa tạo.

= NOT (ISNUMBER(XMATCH(list2[List 2],list1[List 1])))

Bây giờ, khi chúng ta đã sử dụng Tablet, nếu thêm một hàng mới vào một trong hai bảng, phạm vi xử lý mở rộng cũng sẽ tăng lên để bao gồm dữ liệu mới.

Tìm sự khác biệt bằng định dạng có điều kiện tùy chỉnh

Trước đó Gitiho đã hướng dẫn các bạn một cách rất nhanh để so sánh hai danh sách này bằng cách sử dụng quy tắc được xác định trước cho các bản sao. Tuy nhiên, bạn cũng có thể sử dụng định dạng có điều kiện tùy chỉnh (Custom Conditional Formatting) để thực hiện. 

Chúng ta cần xem xét hai cách tiếp cận khác nhau ở đây để làm nổi bật sự khác biệt. Khi không sử dụng Tablet và đã tạo công thức True/False để xác định sự khác biệt, chúng ta có thể lấy một bản sao của công thức và thêm công thức này vào định dạng tùy chỉnh. Tuy nhiên, với Tablet, chúng ta phải sử dụng tham chiếu ô.

Sao chép công thức sang định dạng có điều kiện tùy chỉnh

Bắt đầu bằng cách sao chép công thức. Khi kiểm tra công thức trong bảng tính, bạn có thể thấy rằng nó hoạt động trước khi sử dụng ở định dạng có điều kiện. Đây là phương pháp hay nhất vì rất thường xuyên với các tham chiếu ô tương đối và tuyệt đối, rất khó để có được công thức chính xác.

Chọn các ô mà bạn muốn áp dụng định dạng tùy chỉnh. Sau đó truy cập vào thẻ Home trên giao diện ribbon, rồi bấm nút Conditional Formatting, trong menu xổ xuống chọn tùy chọn New Rule.

Hộp thoại New Formatting Rule sẽ mở ra và bạn chọn tùy chọn Use Formula to determine which cells to format. Sau đó, dán công thức vào ô trống dưới ô Format values where this formulas is true. 

Tiếp theo bấm nút Format … để chọn kiểu định dạng, rồi bấm nút OK để hoàn tất. Như vậy tất cả các ô trong cả hai danh sách đáp ứng điều kiện trên đều sẽ được áp dụng định dạng theo định dạng bạn đã chọn.

Hãy nhớ rằng trong ví dụ này chúng ta đã chọn một dải ô để áp dụng định dạng có điều kiện và nó không phải là ô động. Nếu chúng ta sử dụng Tablet, điều này sẽ cập nhật mà không cần phải thay đổi bất cứ điều gì.

Các hàm khác để so sánh hai danh sách trong Excel

Có nhiều hàm khác trong Excel mà bạn có thể sử dụng để so sánh hai danh sách trong Excel. Ở phần trên của bài viết này chúng ta đã cùng xem xét hàm MATCH và XMATCH, tiếp theo chúng ta sẽ xem xét thêm một số hàm khác nữa. 

Sử dụng hàm VLOOKUP để so sánh hai danh sách trong Excel

Nói một cách đơn giản, hàm VLOOKUP sẽ trả về giá trị tương ứng từ một ô, nếu không có giá trị tương ứng, nó sẽ trả thông báo lỗi #N/A khi dùng VLOOKUP. Trong ví dụ này, chúng ta đang làm việc với giá trị là văn bản. 

Vì vậy có thể thực hiện một hàm VLOOKUP và kiểm tra xem nó có trả về văn bản hay không. Nếu sử dụng số thì chúng ta có thể thay thế hàm ISTEXT bằng ISNUMBER.

Chúng ta có thể sử dụng hàm = ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))

Hoặc nếu đang sử dụng mảng động Dynamic arrays trong Excel 365, chúng ta có thể sử dụng hàm

= ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))

Sử dụng hàm XLOOKUP để so sánh hai danh sách trong Excel

XLOOKUP đã được giới thiệu trong Excel 365 và giống như hàm VLOOKUP, XLOOKUP sẽ trả về giá trị tương ứng từ một ô và bạn có thể xác định kết quả nếu không tìm thấy giá trị đó. Sử dụng mảng động Dynamic arrays, hàm sẽ là

= ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))

Xem thêm: HƯỚNG DẪN SỬ DỤNG HÀM XLOOKUP THAY THẾ HÀM VLOOKUP và HLOOKUP

Sử dụng hàm COUNTIF để so sánh hai danh sách trong Excel

Hàm COUNTIF sẽ đếm số lần một giá trị hoặc văn bản có trong một phạm vi. Nếu giá trị không được tìm thấy, 0 được trả về. Chúng ta có thể kết hợp điều này với một câu lệnh IF để trả về giá trị True và False.

= IF(COUNTIF (A2:A21,C2:C12)<>0,”True”, “False”)

Cách so sánh 2 tập dữ liệu trong Excel

Như vậy việc so sánh hai danh sách trong một bảng Excel là khá dễ dàng và bây giờ chúng ta sẽ so sánh hai tập dữ liệu và điều này có thể khó hơn một chút.

Chúng ta hãy xem xét một ví dụ với hai bảng dữ liệu như hình dưới đây, mỗi bảng chứa các tiêu đề cột giống nhau. Nhưng để kết hợp hai bảng này sẽ yêu cầu tìm kiếm nhiều hơn một cột.

Khi bạn cần xem xét nhiều hơn một cột, giải pháp sẽ là tạo một cột tổng hợp kết hợp dữ liệu thành một cột. Thao tác này sẽ tạo một cột duy nhất cho mỗi hàng mà sau đó chúng ta có thể sử dụng làm cột phù hợp

Trong ví dụ này, chúng ta có thể kết hợp Name và DoB để cung cấp cho mỗi bảng một mã định danh duy nhất Có nhiều cách để nối nội dung của một ô, trong trường hợp này chúng ta sẽ thực hiện một phép nối đơn giản. Khi chúng ta đang sử dụng bảng, công thức sẽ định dạng đặt tên bảng sẽ là.

=[@Name] &”-“&[@DoB]

Lặp lại các bước trên bảng thứ hai.

Bây giờ chúng ta có thể sử dụng bất kỳ ví dụ nào ở trên để đối sánh hai cột dữ liệu mới này để xem vị trí chúng khớp với nhau và cũng là trùng khớp với toàn bộ hàng.

So sánh danh sách hoặc tập dữ liệu bằng Power Query

Bạn cũng có thể so sánh danh sách và tập dữ liệu bằng cách sử dụng Power Query. Bằng cách kết nối với các bảng và sau đó hợp nhất các bảng, sử dụng các kiểu nối khác nhau, chúng ta có thể so sánh cả hai danh sách.

Xem thêm: Top khóa học tin học văn phòng giúp bạn gia tăng hiệu suất làm việc

Kết luận

Trên đây Gitiho vừa giới thiệu với các bạn một số cách để so sánh hai danh sách trong Excel và chúng ta cũng sẽ xem xét cách so sánh toàn bộ các hàng của một tập dữ liệu. Có thể cách mà bạn sử dụng thường xuyên không có dưới đây. 

Trong trường hợp đó, bạn hãy để lại comment ở phía dưới và chia sẻ với chúng tôi về cách bạn muốn so sánh hai danh sách hoặc tập dữ liệu trong Excel nhé. Chúc các bạn thành công.