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

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

Lúc 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 những mục bị thiếu hoặc trùng nhau. Đối với Excel, luôn với 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 những hàm, định dạng với điều kiện cho tới Power Query đều với nhiều tùy chọn để bạn sử dụng. 

Trong bài viết này, Gitiho sẽ cùng với những 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ộ những hàng của một tập dữ liệu.

Để những bạn với thể hiểu thuần tuý hơn về việc so sánh này, Gitiho sẽ hướng dẫn chi tiết những 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.

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

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

Định dạng với đ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 những tiêu chí được xác định trước. Cách nhanh nhất và thuần tuý nhất để so sánh trực quan hai cột này là sử dụng quy tắc trị giá trùng lặp đánh dấu được xác định trước. 

Cách tạo định dạng với đ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 thực đơn 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 với thể xác định định dạng và chọn giữa những trị giá 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ả những mục lặp lại của danh sách Một 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 với thể nhanh chóng xem những mục trong danh sách Hai ko với trong danh sách Một vì những mục này ko được ứng dụng định dạng, giống như hình dưới đây.

Tuy nhiên, bạn cũng với 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 đã ứng dụng hai định dạng với điều kiện khác nhau. Màu đỏ cho biết những mục trùng lặp và màu xanh lá cây cho biết những mục duy nhất với trong bảng dữ liệu cần so sánh.

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

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

Sau lúc ứng dụng định dạng với điều kiện để so sánh hai danh sách trên, bạn với thể xóa tất cả định dạng với điều kiện để trả về mặc định ban sơ. 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 thực đơn 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 ứng dụng nhiều định dạng với đ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à những ô sẽ ko còn chứa định dạng nữa.

Tương tự đây là cách cơ bản nhất để bạn với thể so sánh hai danh sách trong Excel. Nhanh chóng, thuần tuý và hiệu quả. Bạn cũng với thể ứng dụng định dạng với đ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

Với nhiều hàm tra cứu mà bạn với thể sử dụng để so sánh hai phạm vi hoặc danh sách trong Excel. Trước hết 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à trị giá 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 chuẩn xác hoặc sắp đúng.

Ngày nay để viết một công thức so sánh xem những mục trong danh sách Hai với nằm trong danh sách Một hay ko bằng hàm MATCH bạn thực hiện như sau: Trong ô E3, chúng ta với 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 những trị giá 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 ko trùng khớp, trị giá trả về sẽ là #N/A.

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

Để làm điều này, chúng ta với thể thuận lợi mở rộng công thức Match của mình bằng cách sử dụng một hàm logic. Lúc hàm Match trả về một số, chúng ta với 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ề trị giá là True với tức thị với trị giá trùng khớp và trái lại.

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ẽ với những lựa chọn thay thế khác lúc 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 trị giá tra cứu của MATCH và kết quả của chúng ta sẽ được mở rộng lúc 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 Một công thức, bảng tính của bạn sẽ ít bị lỗi và gọn ghẽ hơn.

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

Dynamic Array – Hàm mảng động trong Excel là một cách mới để lập mô phỏng 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 trị giá 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 những đ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à những 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: Trị giá tra cứu là trị giá 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 trị giá tra cứu
  • [Match Mode]: Chế độ đối sánh là tùy chọn. Ko giống như hàm MATCH cũ, mặc định là một kết hợp chuẩn xác. Bạn cũng với thể chọn giữa những tùy chọn gồm: Đối sánh chuẩn xác hoặc đối sánh nhỏ nhất tiếp theo; Đối sánh chuẩn xác hoặc đối sánh to 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 với thể chọn tìm kiếm cuối cùng tới tìm kiếm trước hết và tìm kiếm nhị phân. Nếu bạn đang làm việc với những tìm kiếm nhị phân. Tùy chọn đối sánh ký tự đại diện sẽ ko hoạt động.

Với hàm XMATCH, chúng ta với 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ừ ko phải chọn 0 cho một kết hợp chuẩn xác như trong XMATCH và đây là cài đặt mặc định. 

Ngày nay chúng ta hãy thay đổi một tí bằng việc tìm kiếm những mục trong Danh sách Hai chứ ko phải trong danh sách 1.

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

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

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

Trong mỗi công thức mà Gitiho đã xem xét cho tới nay, chúng tôi đã chọn một dải ô trong hàm Match với đối sánh ko động. Điều đó với tức thị nếu thêm dữ liệu mới vào một trong những 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.

Article post on: nongdanmo.com

Để tránh mất thời kì vì phải cập nhật lại công thức bạn với thể sử dụng cách sau. Trước hết bạn sẽ phải chuyển đổi danh sách thành bảng (Tablet), hãy chọn một trong những 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 tính của Tablet là sử dụng cách đặt tên với cấu trúc. Do đó, lúc bạn nhập một công thức và chọn một cột từ một bảng, nó sẽ ko hiển thị những 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 những mục trong Danh sách Hai ko với 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])))

Ngày nay, lúc 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 với điều kiện tùy chỉnh

Trước đó Gitiho đã hướng dẫn những 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 những bản sao. Tuy nhiên, bạn cũng với thể sử dụng định dạng với đ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 vượt bậc sự khác biệt. Lúc ko sử dụng Tablet và đã tạo công thức True/False để xác định sự khác biệt, chúng ta với 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 với điều kiện tùy chỉnh

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

Chọn những ô mà bạn muốn ứng 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 thực đơn 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 thành. Tương tự tất cả những ô trong cả hai danh sách đáp ứng điều kiện trên đều sẽ được ứng 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 ô để ứng dụng định dạng với điều kiện và nó ko phải là ô động. Nếu chúng ta sử dụng Tablet, điều này sẽ cập nhật mà ko cần phải thay đổi bất cứ điều gì.

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

Với nhiều hàm khác trong Excel mà bạn với 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ếu bạn chưa quen với VLOOKUP, bạn với thể đọc bài viết: Hướng dẫn cách sử dụng hàm VLOOKUP qua những ví dụ từ cơ bản tới tăng

Source: nongdanmo.com

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

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

Chúng ta với 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 với 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ề trị giá tương ứng từ một ô và bạn với thể xác định kết quả nếu ko tìm thấy trị giá đó. Sử dụng mảng động Dynamic arrays, hàm sẽ là

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

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 trị giá hoặc văn bản với trong một phạm vi. Nếu trị giá ko được tìm thấy, 0 được trả về. Chúng ta với thể kết hợp điều này với một câu lệnh IF để trả về trị giá True và False.

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

Tương tự việc so sánh hai danh sách trong một bảng Excel là khá thuận lợi và hiện giờ chúng ta sẽ so sánh hai tập dữ liệu và điều này với thể khó hơn một 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 những 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.

Lúc 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 với thể sử dụng làm cột thích hợp

Trong ví dụ này, chúng ta với thể kết hợp Name và DoB để phân phối cho mỗi bảng một mã định danh duy nhất Với 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 thuần tuý. Lúc 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 những bước trên bảng thứ hai.

Ngày nay chúng ta với 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.

Via @: nongdanmo.com

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

Bạn cũng với 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 những bảng và sau đó thống nhất những bảng, sử dụng những kiểu nối khác nhau, chúng ta với thể so sánh cả hai danh sách.

Kết luận

Trên đây Gitiho vừa giới thiệu với những 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ộ những hàng của một tập dữ liệu. Với thể cách mà bạn sử dụng thường xuyên ko với dưới đây. 

Trong trường hợp đó, bạn hãy để lại comment ở phía dưới và san 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 những bạn thành công.

Thời đại kỹ thuật 4.0 đang dần đi vào cuộc sống đòi hỏi mọi người phải tự trang bị tri thức tin học cho thích hợp để với thể bắt kịp nhưng thay đổi nhanh chóng này. Chẳng người nào khác ngoài bạn hiểu mình cần trang bị thêm tri thức gì. Hãy tìm hiểu ngay TẠI ĐÂY những khóa học quyến rũ, với tính thực tế cao của Gitiho và đăng ký nhận tư vấn ngay hôm nay. Chi tiết tại Gitiho.com.


--- Cập nhật: 26-01-2023 --- nongdanmo.com tìm được thêm bài viết Hàm so sánh trong Excel - Cách sử dụng hàm so sánh và ví dụ sử dụng hàm so sánh từ website thuthuatphanmem.vn cho từ khoá so sánh khác nhau trong excel.

Với khối lượng dữ liệu to, bạn muốn rà soát dữ liệu bị trùng lặp bằng cách rà soát thông thường thì quả thực rất vất vả. Trong bài viết này giới thiệu tới những bạn những Hàm so sánh trong Excel, với kèm ví dụ thực tế giúp bạn dễ hình dung.

1. Sử dụng hàm Exact để so sánh dữ liệu

Mô tả

Hàm Exact thực hiện so sánh hai chuỗi văn bản, trả về trị giá True nếu hai chuỗi trùng nhau, trả về trị giá False nếu hai chuỗi dữ liệu khác nhau. Chú ý Hàm Exact phân biệt chữ hoa, chữ thường trong quá trình so sánh.

Cú pháp

EXACT(Text1, Text2)

Trong đó: Text1, Text2 là hai chuỗi văn bản cần so sánh, là hai thông số yêu cầu.

Chú ý

- Hàm Exact phân biệt chữ hoa, chữ thường trong quá trình so sánh nhưng nó ko phân biệt định dạng.

Ví dụ

So sánh dữ liệu tương ứng theo hàng giữa cột Một và cột 2. Tại ô cần so sánh nhập công thức: EXACT(D4,E4)

Nhấn Enter kết quả trả về trị giá False tức hai trị giá ko trùng nhau do khác nhau chữ chiếc trước hết in thường và in hoa:

Tương tự sao chép công thức cho những trị giá còn lại được kết quả:

Tuy nhiên với hàm này bạn ko thể so sánh Một trị giá trong cột Một so sánh với tất cả những dữ liệu trong cột Hai để tìm sự trùng lặp. Với hàm này bạn chỉ với thể so sánh tương ứng giữa những hàng với nhau.

2. Sử dụng hàm trung gian Countif để so sánh dữ liệu

Để khắc phục trạng thái ở ví dụ Một bạn với thể sử dụng hàm Countif làm trung gian để so sánh những trị giá trong cột Một so với những trị giá trong cột Hai với trùng hay ko.

Mô tả hàm Countif

- Hàm Countif thực hiện đếm số ô thỏa mãn điều kiện nào đó trong vùng dữ liệu được lựa chọn.

Cú pháp

COUNTIF(Range, Criteria)

Trong đó:

- Range: Vùng dữ liệu chứa dữ liệu cần đếm, là thông số yêu cầu.

- Criteria: Điều kiện sử dụng để đếm dữ liệu, là thông số yêu cầu

Ví dụ

So sánh dữ liệu giữa Hai cột, đánh dấu những trị giá ko với trong cột Một mà với trong cột Hai và trái lại.

Bước 1: Đặt tên cho cho những cột dữ liệu:

Bước 2: Tương tự đặt tên cho cột 2, lúc đó tên Hai cột dữ liệu được hiển thị:

Bước 4: Hộp thoại xuất hiện lựa chọn Use a formula to determine which cells to format:

Bước 5: Nhập công thức =COUNTIF(danhsach2,C3)=0 sau đó kích chọn Format:

Bước 7: Tiếp theo kích chọn OK đóng hộp thoại kết quả loại quả ở danh sách Một ko với trong danh sách Hai được tô màu để phân biệt:

Bước 8: Tương tự với danh sách Hai bạn thực hiện tương tự, nhưng chỉ khác nhập công thức: = COUNTIF(danhsach2,F3)=0 tức sẽ tô màu ô với trị giá bằng 0 (ko trùng nhau)

Kết quả bạn đã so sánh Hai cột dữ liệu và với thể thêm ghi chú cho người đọc dễ hình dung:

Trên đây là hướng dẫn cách sử dụng hàm EXACT, ứng dụng hàm COUNTIF  để so sánh dữ liệu trong Excel. Chúc những bạn thành công!

Article post on: nongdanmo.com

Recommended For You

About the Author: Bảo