Bạn đang xem: Hàm so sánh số trong excel
Như công ty chúng tôi đã nói ở đoạn mở đầu, thường thì muốn kiểm tra dữ liệu có bị trùng lặp hay không thì bạn có thể thực hiện thủ công bằng cách xem trực tiếp cùng đếm trên tệp tin Excel của mình. Tuy nhiên cách làm này vẫn vất vả mang lại bạn, thỉnh thoảng còn không đúng đắn nữa. Cùng cách rất tốt là bạn sẽ dùng các hàm so sánh tất cả sẵn vào Excel nhằm tính toán, kiểm tra.
Nhấn Enter kết quả trả về cực hiếm False tức hai giá trị không trùng nhau do khác nhau chữ cái thứ nhất in thường với in hoa:
Tương từ bỏ -> bạn sao chép công thức cho các giá trị sót lại sẽ được kết quả như hình dưới:
Bạn lưu ý thêm là với hàm này các bạn không thể đối chiếu 1 giá trị trong cột 1 so sánh với tất cả các tài liệu trong cột 2 nhằm tìm sự trùng lặp. Hàm này chúng ta chỉ có thể so sánh khớp ứng giữa các hàng cùng với nhau.
2. áp dụng hàm trung gian Countif để đối chiếu dữ liệu
Để khắc phục hiện trạng trong Ví dụ 1 chúng ta có thể sử dụng hàm Countif làm trung gian để so sánh những giá trị vào Cột 1 so với các giá trị trong Cột 2 tất cả trùng hay không.
Mô tả hàm Countif
Hàm Countif thực hiện tại đếm số ô thỏa mãn nhu cầu đ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 tài liệu chứa tài liệu cần đếm, là tham số bắt buộc.- Criteria: Điều kiện dùng để đếm dữ liệu, là thông số bắt buộc
Xét lấy ví dụ như 2
So sánh dữ liệu giữa 2 Cột, đánh dấu các giá trị không tồn tại trong Cột 1 mà gồm trong Cột 2 và ngược lại.
Bước 1: Bạn đặt tên cho các cột dữ liệu -> tiếp nối lựa chọn toàn bộ dữ liệu Cột 1 vào thanh add nhập tên danhsach1 -> bạn nhấn Enter.
Bước 2: Tương tự, bạn đặt tên mang lại Cột 2, khi ấy tên 2 cột dữ liệu được hiển thị:
Bước 3: Bạn chọn cục bộ danhsach1 -> kế tiếp vào tab Home -> chọn Conditional Formatting -> lựa chọn New Rule...
Bước 4: Hộp thoại xuất hiện thêm -> chúng ta chọn Use a formula to lớn determine which cells to lớn format:
Bước 5: Bạn nhập bí quyết =COUNTIF(danhsach1,B3)=0 tiếp nối nhấn chọn Format:
Bước 6: Giao diện Format Cells xuất hiện tại -> bạn nhấn lựa chọn tab Fill -> bạn chọn màu lưu lại màu không tồn tại trong danhsach2 -> rồi nhấn chọn OK:
Bước 7: Tiếp theo, các bạn nhấn lựa chọn OK đóng hộp thoại -> hiệu quả loại color danhsach1 không bao gồm trong danhsach2 được sơn màu để phân biệt:
Bước 8: Tương từ với danhsach2 bạn thực hiện tương tự, dẫu vậy chỉ khác nhập công thức: = COUNTIF(danhsach1,D3)=0 tức vẫn tô color ô có mức giá trị bằng 0 (không trùng nhau)
Kết trái là các bạn đã so sánh được 2 cột tài liệu và hoàn toàn có thể thêm ghi chú cho những người khác khi chứng kiến tận mắt sẽ dễ hình dung:
https://thuthuat.taimienphi.vn/ham-so-sanh-trong-excel-trong-excel-43726n.aspx Trên đấy là bài share về phương pháp dùng những Hàm so sánh trong Excel. Để dùng những hàm đối chiếu này thì chúng ta cũng có thể sử dụng hàm EXACT hoặc thực hiện hàm COUNTIF để đối chiếu dữ liệu vào Excel. Chúc chúng ta thành công!
Khi làm việc với bảng dữ liệu Excel, không ít trường hợp bọn họ cần đối chiếu 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 các việc, bao hàm cả việc đối chiếu dữ liệu. Tự sử dụng các hàm, định dạng bao gồm điều kiện cho tới Power Query đều có không ít tùy chọn để các bạn sử dụng.
Trong nội dung bài viết này, wu.edu.vn sẽ thuộc với các bạn xem xét một số cách để so sánh hai list trong Excel và chúng ta cũng vẫn xem xét bí quyết so sánh cục bộ các mặt hàng của một tập dữ liệu.
Để các bạn có thể hiểu dễ dàng và đơn giản hơn về việc đối chiếu này, wu.edu.vn vẫn hướng dẫn chi tiết các cách để so sánh nhị bảng dữ liệu Excel. Lấy 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.
Top khóa huấn luyện Excel chất lượng được học nhiều
G-LEARNING
32138 học viên
4.8 (109 tấn công giá)
499,000đ
799,000đ
G-LEARNING
10592 học tập viên
4.59 (17 đánh giá)
Miễn phí
499,000đ
G-LEARNING
674 học tập viên
4.8 (5 đánh giá)
499,000đ
799,000đ
7 cách so sánh hai tập tài liệu trong Excel
Sử dụng format có điều kiện để đối chiếu hai cột dữ liệu
Định dạng có đk (Conditional Formatting) sẽ cho phép bạn khắc ghi một ô hoặc dải ô dựa trên các tiêu chuẩn được xác minh trước. Cách nhanh nhất có thể và đơn giản và dễ dàng nhất để so sánh trực quan nhị cột này là áp dụng quy tắc quý hiếm trùng lặp khắc ghi được khẳng định trước.
Cách sinh sản định dạng có đk để đối chiếu hai cột dữ liệuBước 1: Chọn nhị cột dữ liệu.
Bước 2: Truy cập vào thẻ Home, tiếp nối bấm nút Conditional Formatting. Trong menu xổ xuống chọn tùy chọn Highlight Cells Rules. Tiếp theo sau chọn Duplicate values.
Bước 3: Trong hộp thoại Duplicate Values mở ra, chúng ta cũng có thể xác định định dạng và chọn giữa các giá trị đụng hàng (Duplicate) hoặc nhất (Unique values).
Nếu lựa 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. Trường đoản cú đây bạn cũng có thể nhanh giường xem các mục trong danh sách 2 không có trong danh sách 1 bởi vì những mục này không được vận dụng định dạng, hệt như hình dưới đây.
Tuy nhiên, bạn cũng có thể chọn format duy độc nhất vô nhị (Unique values) chỉ bằng phương pháp chọn tùy chọn chất lượng trong hộp thoại Duplicate Values.
Như trong lấy một ví dụ này, wu.edu.vn đã vận dụng hai định dạng có đk khác nhau. Màu sắc đỏ cho biết thêm các mục trùng lặp và màu xanh lá cây cho thấy các mục duy nhất có trong bảng tài liệu cần so sánh.
Lưu ý: lấy ví dụ như trên không chỉ là lấy những ô có tài liệu mà còn lấy tất cả các cột trường đoản cú A cho C. Cột B không tồn tại dữ liệu, vì vậy nó ko thể tác động đến kết quả. Tuy nhiên, phần nhiều ô này còn có chứa format có đk được áp dụng, bởi vậy, cách tốt hơn là chỉ chọn các ô các bạn cần.
Cách xóa định dạng tất cả điều kiệnSau khi vận dụng định dạng có đk để so sánh hai list trên, chúng ta cũng có thể xóa tất cả định dạng có đk để trả về mặc định ban đầu. Cách thực hiện như sau:
Bước 1: Trước tiên nên 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 lựa chọn tùy chọn Clear Rules. Sau 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ỉ mong mỏi xóa một trong những định dạng này, nên chọn tùy chọn Manage Rules tự trình đối chọi Conditional Formatting thả xuống. Chọn quy tắc bạn muốn xóa trong list rồi lựa chọn Delete Rule.
Bước 4: bằng cách nhấn nút OK, quy tắc vẫn bị xóa sổ Manage Rules và các ô sẽ không thể chứa format nữa.
Như vậy đó là cách cơ bản nhất để chúng ta có thể so sánh hai list trong Excel. Cấp tốc chóng, đơn giản dễ dàng và hiệu quả. Bạn có thể áp dụng format có điều kiện dựa trên phương pháp mà bọn họ sẽ coi xét tại vị trí sau của bài viết này.
So sánh trùng lặp tài liệu trong Excel bằng hàm MATCH
Có các hàm tra cứu mà bạn có thể sử dụng để đối chiếu hai phạm vi hoặc list trong Excel. Đầu tiên bọn họ sẽ để ý 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ó, giả dụ được tìm kiếm thấy, trong mảng tra cứu.
Cú pháp đến hàm MATCH là:
= MATCH (lookup value, Lookup array, Match type)
Trong đó:
Lookup value: Là giá trị tra cứu mà bạn có nhu cầu tìm đối sánh.Lookup array: Mảng tra cứu là list mà ai đang tìm kiếm đối sánh.Match type: hình dáng kết hợp chất nhận được bạn lựa chọn giữa kết hợp đúng đắn hoặc ngay gần đúng.Bây giờ để viết một công thức so sánh xem các mục trong list 2 tất cả nằm trong danh sách 1 hay là không bằng hàm MATCH bạn triển khai như sau: trong ô E3, chúng ta có thể nhập công thức
= MATCH(C2, $A$2:$A$21,0)
Bằng giải pháp điền vào cách làm này, đối với các giá bán trị nhưng Excel tìm kiếm thấy trùng khớp thì vị trí trùng khớp đó sẽ được trả về. Trường hòa hợp không trùng khớp, giá trị trả về đang là #N/A.
Thông thường, vị trí kha khá hoặc #N/A không có giá trị đối với chúng ta và bọn họ cần chuyển những giá trị này thành True hoặc False để rất có thể dễ dàng xác minh được lỗi.
Để có tác dụng điều này, bạn có thể dễ dàng không ngừng mở rộng công thức Match của mình bằng phương pháp sử dụng một hàm logic. Lúc hàm Match trả về một số, bạn có thể sử dụng hàm ISNUMBER
= ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Trong trường vừa lòng này, sản phẩm nào trả về quý hiếm là True có nghĩa là có giá trị trùng khớp với ngược lại.
Sử dụng hàm MATCH hoặc XMATCH vào Excel 365 bên dưới dạng Dynamic Array
Nếu đang thực hiện Excel 365, bạn sẽ có các lựa chọn thay thế sửa chữa khác khi thực hiện hàm MATCH để đối chiếu hai danh sách hoặc dữ liệu. Như cùng với Excel 365 họ sử dụng hàm mảng cồn Dynamic Array để gửi một mảng thành quý giá tra cứu vãn của MATCH và công dụng của chúng ta sẽ được không ngừng mở rộng khi bảng tài liệu mở rộng.
Điều này cũng trở thành ngăn ngăn việc sao chép công thức và chỉ với cùng 1 công thức, bảng tính của bạn sẽ ít bị lỗi và nhỏ gọn hơn.
Sử dụng MATCH và Dynamic Array để đối chiếu 2 danh sáchDynamic Array – Hàm mảng hễ trong Excel là 1 trong cách bắt đầu để lập mô hình bảng tính Excel của bạn. Thay đổi duy nhất so với công thức đối sánh là thay do chọn ô C2 làm cho giá trị tra cứu vớt của bọn chúng tôi, công ty chúng tôi sẽ chọn phạm vi C2:12.
= ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Excel 365 cũng trình làng một hàm new là XMATCH. Tương tự như hàm MATCH, XMATCH trả về một vị trí kha khá trong danh sách. Điều này mở ra dưới dạng những điều kiện new trong cú pháp công thức, chẳng hạn như chính sách tìm tìm và những loại đối sánh. Tất nhiên XMATCH cũng được bổ sung cập nhật những kỹ năng này so với MATCH tiền nhiệm.
Cú pháp mang đến XMATCH là:
XMATCH (Lookup Value, Lookup Array,
Trong đó:
Lookup Value: Giá trị tra cứu vãn là giá trị ai đang tìm kiếm nhằm tìm địa chỉ tương đốiLookup Array: Mảng tra cứu vớt là hàng hoặc cột chứa giá trị tra cứuVới hàm XMATCH, bạn cũng có thể sử dụng Dynamic Array hoặc tham chiếu ô để chế tạo công thức, kiểu như như họ đã chu đáo với hàm MATCH. Đối với ví dụ này, wu.edu.vn sẽ áp dụng Dynamic Array.
Công thức đã giống với mọi gì họ đã sử dụng với hàm MATCH làm việc trên; ngoại trừ chưa phải chọn 0 cho 1 kết hợp đúng mực như vào XMATCH và đây là cài đặt mặc định.
Bây giờ họ hãy đổi khác một chút bằng việc tìm kiếm những mục trong list 2 chứ chưa hẳn trong danh sách 1.
Trong trường phù hợp này, bạn có thể sử dụng công thức
= NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Trong mỗi công thức mà wu.edu.vn đang xem xét cho tới nay, công ty chúng tôi đã chọn 1 dải ô vào hàm Match với đối sánh tương quan không động. Điều đó có nghĩa là nếu thêm tài liệu mới vào một trong những trong những danh sách, các bạn sẽ phải tiến hành thêm một bước bằng tay thủ công để update công thức để bao gồm dữ liệu mới.
Để né mất thời gian vì phải cập nhật lại công thức bạn cũng có thể sử dụng cách sau. Đầu tiên bạn sẽ phải thay đổi danh sách thành bảng (Tablet), hãy lựa chọn 1 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 đã lựa chọn cả tiêu đề trong phạm vi ô, hãy bảo vệ tích vào tùy chọn My tablet has headers, rồi bấm nút OK.
Bản hóa học của Tablet là thực hiện cách để tên gồm cấu trúc. Vị đó, khi chúng ta nhập một phương pháp và lựa chọn 1 cột xuất phát điểm từ 1 bảng, nó sẽ không còn hiển thị những tham chiếu ô mà là tên cột.
Như công thức tiếp sau đây wu.edu.vn thực hiện hàm XMATCH nhằm tìm các mục trong danh sách 2 không có trong danh sách 1, trong các số đó đã viết bí quyết này bằng phương pháp sử dụng tham chiếu bảng vừa tạo.
= NOT (ISNUMBER(XMATCH(list2,list1
)))
Bây giờ, khi họ đã áp dụng Tablet, ví như thêm một hàng mới vào trong 1 trong hai bảng, phạm vi xử lý mở rộng cũng trở nên tăng lên để bao hàm dữ liệu mới.
Tìm sự biệt lập bằng format có đk tùy chỉnh
Trước kia wu.edu.vn đang hướng dẫn chúng ta một biện pháp rất cấp tốc để so sánh hai danh sách này bằng phương pháp sử dụng luật lệ được xác định trước mang lại các bản sao. Mặc dù nhiên, chúng ta có thể sử dụng định dạng bao gồm điều kiện tùy chỉnh (Custom Conditional Formatting) để thực hiện.
Chúng ta phải xem xét hai bí quyết tiếp cận không giống nhau ở đây để gia công nổi bật sự không giống biệt. Khi không sử dụng Tablet và đã tạo phương pháp True/False để xác định sự khác biệt, chúng ta có thể lấy một phiên bản sao của công thức và thêm công thức này vào định hình tùy chỉnh. Mặc dù nhiên, với Tablet, chúng ta phải thực hiện tham chiếu ô.
Sao chép phương pháp sang định hình có điều kiện tùy chỉnh
Bắt đầu bằng phương pháp sao chép công thức. Lúc kiểm tra cách làm trong bảng tính, chúng ta cũng có thể thấy rằng nó vận động trước khi áp dụng ở định dạng bao gồm điều kiện. Đây là phương pháp hay nhất vì chưng rất liên tục với các tham chiếu ô tương đối và giỏi đối, khôn xiết khó để có được công thức thiết yếu xác.
Chọn các ô mà bạn muốn áp dụng format tùy chỉnh. Tiếp nối 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 lựa chọn New Rule.
Hộp thoại New Formatting Rule sẽ lộ diện và bạn chọn tùy chọn Use Formula to determine which cells to format. Sau đó, dán bí quyết vào ô trống bên dưới ô Format values where this formulas is true.
Tiếp theo bấm nút Format … để lựa chọn kiểu định dạng, rồi bấm nút OK để hoàn tất. Như vậy toàn bộ các ô trong cả hai danh sách thỏa mãn nhu cầu điều kiện trên hồ hết sẽ được áp dụng định dạng theo định dạng chúng ta đã chọn.
Hãy nhớ là trong lấy một ví dụ này chúng ta đã chọn 1 dải ô để áp dụng định dạng có đk và nó chưa phải là ô động. Nếu bọn họ sử dụng Tablet, vấn đề đó sẽ update mà không cần thiết phải thay đổi bất cứ điều gì.
Các hàm không giống để đối chiếu hai danh sách trong Excel
Có những 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 nội dung bài viết này chúng ta đã cùng lưu ý hàm MATCH và XMATCH, tiếp theo chúng ta sẽ lưu ý thêm một số trong những hàm không giống nữa.
Sử dụng hàm VLOOKUP để đối chiếu hai list trong ExcelNếu bạn chưa thân quen với VLOOKUP, bạn cũng có thể đọc bài bác viết: phía dẫn giải pháp dùng hàm VLOOKUP qua các ví dụ tự cơ phiên bản đến nâng cao
Nói một cách 1-1 giản, hàm VLOOKUP đang trả về quý hiếm tương ứng xuất phát điểm từ 1 ô, nếu không có giá trị tương ứng, nó đang trả thông tin lỗi #N/A. Trong lấy ví dụ như này, chúng ta đang thao tác làm việc với cực hiếm là văn bản.
Vì vậy có thể thực hiện nay một hàm VLOOKUP và chất vấn xem nó gồm trả về văn bạn dạng hay không. Nếu áp dụng số thì chúng ta có thể thay nạm hàm ISTEXT bởi ISNUMBER.
Chúng ta rất có thể sử dụng hàm = ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Hoặc nếu như đang sử dụng mảng cồn Dynamic arrays vào Excel 365, chúng ta cũng có thể sử dụng hàm
= ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP sẽ được ra mắt trong Excel 365 và giống như hàm VLOOKUP, XLOOKUP đang trả về cực hiếm tương ứng xuất phát từ một ô và bạn cũng có thể xác định kết quả nếu không tìm kiếm thấy quý giá đó. Thực hiện mảng hễ Dynamic arrays, hàm vẫn là
= ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
Hàm COUNTIF vẫn đếm chu kỳ một quý hiếm hoặc văn bạn dạng có trong một phạm vi. Nếu cực hiếm không được search thấy, 0 được trả về. Bạn cũng có thể kết hợp điều này với một câu lệnh IF để trả về cực hiếm True và False.
= IF(COUNTIF (A2:A21,C2:C12)0,”True”, “False”)
Cách so sánh 2 tập tài 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ễ ợt và hiện thời chúng ta sẽ đối chiếu hai tập dữ liệu và điều này có thể khó rộng một chút.
Chúng ta hãy để mắt tới một lấy ví dụ với nhì 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 lại để kết hợp hai bảng này sẽ yêu cầu tìm kiếm nhiều hơn thế nữa một cột.
Xem thêm: The remix tập 2 - tuyệt đỉnh remix 2
Khi bạn cần xem xét nhiều hơn một cột, phương án sẽ là chế tạo ra một cột tổng hợp kết hợp dữ liệu thành một cột. Làm việc này sẽ khởi tạo một cột duy nhất cho từng hàng nhưng mà sau đó bạn cũng có thể sử dụng làm cột phù hợp
Trong ví dụ này, bạn có thể kết vừa lòng Name với Do
B để cung cấp cho từng bảng một mã định danh duy nhất có nhiều cách để nối văn bản của một ô, vào trường phù hợp này chúng ta sẽ thực hiện một phép nối đối chọi giản. Khi chúng ta đang sử dụng bảng, phương pháp sẽ định dạng đặt tên bảng sẽ là.