Một trong những khả năng của Microsoft Excel là khả năng so sánh hai danh sách dữ liệu, xác định các điểm trùng khớp giữa các danh sách và xác định mục nào chỉ được tìm thấy trong một danh sách. Điều này rất hữu ích khi so sánh hồ sơ tài chính hoặc kiểm tra xem một tên cụ thể có trong cơ sở dữ liệu hay không. Bạn có thể sử dụng chức năng MATCH để xác định và đánh dấu các bản ghi phù hợp hoặc không khớp hoặc bạn có thể sử dụng định dạng điều hòa với chức năng COUNTIF. Các bước sau đây cho bạn biết cách sử dụng từng loại để khớp với dữ liệu của bạn.
Các bước
Phương pháp 1/2: Xác định hồ sơ bằng hàm MATCH
Bước 1. Sao chép danh sách dữ liệu vào một trang tính
Excel có thể làm việc với nhiều trang tính trong một sổ làm việc hoặc với nhiều sổ làm việc, nhưng bạn sẽ thấy việc so sánh các danh sách dễ dàng hơn nếu bạn sao chép thông tin của chúng vào một trang tính.
Bước 2. Cung cấp cho mỗi mục danh sách một số nhận dạng duy nhất
Nếu hai danh sách của bạn không chia sẻ một cách chung để xác định chúng, bạn có thể cần thêm một cột bổ sung vào mỗi danh sách dữ liệu xác định mục đó vào Excel để nó có thể xem liệu một mục trong danh sách nhất định có liên quan đến một mục hay không. trong danh sách khác. Bản chất của số nhận dạng này sẽ phụ thuộc vào loại dữ liệu bạn đang cố gắng đối sánh. Bạn sẽ cần một số nhận dạng cho mỗi danh sách cột.
- Đối với dữ liệu tài chính liên quan đến một khoảng thời gian nhất định, chẳng hạn như hồ sơ thuế, đây có thể là mô tả về tài sản, ngày tài sản được mua hoặc cả hai. Trong một số trường hợp, một mục nhập có thể được xác định bằng một số mã; tuy nhiên, nếu cùng một hệ thống không được sử dụng cho cả hai danh sách, mã định danh này có thể tạo ra các kết quả phù hợp mà không có hoặc bỏ qua các kết quả phù hợp cần được thực hiện.
- Trong một số trường hợp, bạn có thể lấy các mục từ một danh sách và kết hợp chúng với các mục từ danh sách khác để tạo số nhận dạng, chẳng hạn như mô tả tài sản vật chất và năm nó được mua. Để tạo một số nhận dạng như vậy, bạn nối (thêm, kết hợp) dữ liệu từ hai hoặc nhiều ô bằng cách sử dụng dấu và (&). Để kết hợp mô tả mục trong ô F3 với ngày trong ô G3, được phân tách bằng dấu cách, bạn nhập công thức '= F3 & "" & G3' vào một ô khác trong hàng đó, chẳng hạn như E3. Nếu bạn chỉ muốn bao gồm năm trong số nhận dạng (vì một danh sách sử dụng ngày tháng đầy đủ và danh sách kia chỉ sử dụng năm), bạn sẽ bao gồm hàm YEAR bằng cách nhập '= F3 & "" & YEAR (G3)' vào ô E3. (Không bao gồm các dấu ngoặc kép đơn lẻ; chúng chỉ ở đó để chỉ ra ví dụ.)
- Khi bạn đã tạo công thức, bạn có thể sao chép nó vào tất cả các ô khác của cột định danh bằng cách chọn ô có công thức và kéo chốt điền trên các ô khác của cột mà bạn muốn sao chép công thức. Khi bạn thả nút chuột, mỗi ô bạn kéo qua sẽ được điền công thức, với các tham chiếu ô được điều chỉnh thành các ô thích hợp trong cùng một hàng.
Bước 3. Chuẩn hóa dữ liệu nếu có thể
Trong khi tâm trí nhận ra rằng "Inc." và "Hợp nhất" có nghĩa tương tự, Excel thì không trừ khi bạn định dạng lại từ này hay từ kia. Tương tự như vậy, bạn có thể coi các giá trị như $ 11, 950 và $ 11, 999,95 là đủ gần để khớp, nhưng Excel thì không trừ khi bạn yêu cầu.
- Bạn có thể xử lý một số từ viết tắt, chẳng hạn như "Co" cho "Company" và "Inc" cho "Incorporated bằng cách sử dụng hàm chuỗi LEFT để cắt ngắn các ký tự bổ sung. Các từ viết tắt khác, chẳng hạn như" Assn "cho" Association ", tốt nhất có thể là xử lý bằng cách thiết lập hướng dẫn kiểu nhập dữ liệu và sau đó viết chương trình để tra cứu và sửa các định dạng không phù hợp.
- Đối với chuỗi số, chẳng hạn như mã ZIP trong đó một số mục nhập bao gồm hậu tố ZIP + 4 và những mục khác thì không, bạn có thể sử dụng lại hàm chuỗi LEFT để chỉ nhận dạng và khớp với mã ZIP chính. Để Excel nhận ra các giá trị số gần giống nhau nhưng không giống nhau, bạn có thể sử dụng hàm ROUND để làm tròn các giá trị gần giống nhau và khớp chúng.
- Các dấu cách thừa, chẳng hạn như nhập hai dấu cách giữa các từ thay vì một, có thể được loại bỏ bằng cách sử dụng hàm TRIM.
Bước 4. Tạo các cột cho công thức so sánh
Cũng giống như bạn phải tạo các cột cho số nhận dạng danh sách, bạn sẽ cần tạo các cột cho công thức thực hiện việc so sánh cho bạn. Bạn sẽ cần một cột cho mỗi danh sách.
Bạn sẽ muốn gắn nhãn các cột này bằng một cái gì đó như "Thiếu?"
Bước 5. Nhập công thức so sánh vào mỗi ô
Đối với công thức so sánh, bạn sẽ sử dụng hàm MATCH được lồng bên trong một hàm khác của Excel, ISNA.
- Công thức có dạng "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", trong đó một ô của cột định danh của danh sách đầu tiên được so sánh với từng số nhận dạng trong danh sách thứ hai để xem nó có phù hợp với một trong số chúng không. Nếu nó không khớp, một bản ghi bị thiếu và từ "TRUE" sẽ được hiển thị trong ô đó. Nếu nó khớp, bản ghi sẽ xuất hiện và từ "FALSE" sẽ được hiển thị. (Khi nhập công thức, không bao gồm dấu ngoặc kép kèm theo.)
- Bạn có thể sao chép công thức vào các ô còn lại của cột giống như cách bạn sao chép công thức định danh ô. Trong trường hợp này, chỉ tham chiếu ô cho ô số nhận dạng thay đổi, vì việc đặt các ký hiệu đô la trước các tham chiếu hàng và cột cho các ô đầu tiên và cuối cùng trong danh sách các số nhận dạng ô thứ hai làm cho chúng tham chiếu tuyệt đối.
- Bạn có thể sao chép công thức so sánh cho danh sách đầu tiên vào ô đầu tiên của cột cho danh sách thứ hai. Sau đó, bạn sẽ phải chỉnh sửa tham chiếu ô để "G3" được thay thế bằng tham chiếu cho ô định danh đầu tiên của danh sách thứ hai và "$ L $ 3: $ L $ 14" được thay thế bằng ô định danh đầu tiên và cuối cùng của danh sách thứ hai. (Để nguyên dấu đô la và dấu hai chấm.) Sau đó, bạn có thể sao chép công thức đã chỉnh sửa này vào các ô còn lại trong hàng so sánh của danh sách thứ hai.
Bước 6. Sắp xếp danh sách để xem các giá trị không khớp dễ dàng hơn, nếu cần
Nếu danh sách của bạn lớn, bạn có thể cần phải sắp xếp chúng để đặt tất cả các giá trị không khớp với nhau. Hướng dẫn trong phần phụ bên dưới sẽ chuyển đổi công thức thành giá trị để tránh lỗi tính toán lại và nếu danh sách của bạn lớn, sẽ tránh được thời gian tính toán lại lâu.
- Kéo chuột qua tất cả các ô trong danh sách để chọn nó.
- Chọn Sao chép từ menu Chỉnh sửa trong Excel 2003 hoặc từ nhóm Bảng tạm của dải băng Trang chủ trong Excel 2007 hoặc 2010.
- Chọn Dán Đặc biệt từ menu Chỉnh sửa trong Excel 2003 hoặc từ nút thả xuống Dán trong nhóm Bảng tạm của dải băng Trang chủ Excel 2007 hoặc 2010.
- Chọn "Giá trị" từ danh sách Dán dưới dạng trong hộp thoại Dán Đặc biệt. Bấm OK để đóng hộp thoại.
- Chọn Sắp xếp từ menu Dữ liệu trong Excel 2003 hoặc nhóm Sắp xếp và Lọc của dải băng Dữ liệu trong Excel 2007 hoặc 2010.
- Chọn "Hàng tiêu đề" từ danh sách "Phạm vi dữ liệu của tôi có" trong hộp thoại Sắp xếp Theo, chọn "Thiếu?" (hoặc tên mà bạn thực sự đặt cho tiêu đề cột so sánh) và bấm OK.
- Lặp lại các bước này cho danh sách khác.
Bước 7. So sánh trực quan các mục không khớp để xem tại sao chúng không khớp
Như đã lưu ý trước đây, Excel được thiết kế để tìm kiếm các kết quả phù hợp dữ liệu chính xác trừ khi bạn thiết lập để tìm kiếm các kết quả gần đúng. Sự không khớp của bạn có thể đơn giản như một sự chuyển đổi ngẫu nhiên của các chữ cái hoặc chữ số. Nó cũng có thể là một cái gì đó yêu cầu xác minh độc lập, chẳng hạn như kiểm tra xem liệu các tài sản được liệt kê có cần được báo cáo ngay từ đầu hay không.
Phương pháp 2/2: Định dạng có điều kiện với COUNTIF
Bước 1. Sao chép danh sách dữ liệu vào một trang tính
Bước 2. Quyết định danh sách bạn muốn đánh dấu các bản ghi phù hợp hoặc không phù hợp
Nếu bạn chỉ muốn đánh dấu các bản ghi trong một danh sách, có thể bạn sẽ muốn đánh dấu các bản ghi duy nhất trong danh sách đó; nghĩa là, các bản ghi không khớp với các bản ghi trong danh sách khác. Nếu bạn muốn đánh dấu các bản ghi trong cả hai danh sách, bạn sẽ muốn đánh dấu các bản ghi khớp với nhau. Đối với mục đích của ví dụ này, chúng tôi sẽ giả sử danh sách đầu tiên chiếm các ô từ G3 đến G14 và danh sách thứ hai chiếm các ô từ L3 đến L14.
Bước 3. Chọn các mục trong danh sách mà bạn muốn đánh dấu các mục độc đáo hoặc phù hợp
Nếu bạn muốn đánh dấu các mục phù hợp trong cả hai danh sách, bạn sẽ phải chọn từng danh sách một và áp dụng công thức so sánh (được mô tả trong bước tiếp theo) cho từng danh sách.
Bước 4. Áp dụng công thức so sánh thích hợp
Để làm điều này, bạn sẽ phải truy cập hộp thoại Định dạng có Điều kiện trong phiên bản Excel của bạn. Trong Excel 2003, bạn làm như vậy bằng cách chọn Định dạng có Điều kiện từ menu Định dạng, trong khi trong Excel 2007 và 2010, bạn bấm vào nút Định dạng có Điều kiện trong nhóm Kiểu của dải băng Trang chủ. Chọn loại quy tắc là "Công thức" và nhập công thức của bạn vào trường Chỉnh sửa Mô tả Quy tắc.
- Nếu bạn muốn đánh dấu các bản ghi duy nhất cho danh sách đầu tiên, công thức sẽ là "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", với phạm vi ô của danh sách thứ hai được hiển thị dưới dạng giá trị tuyệt đối và tham chiếu vào ô đầu tiên của danh sách đầu tiên dưới dạng giá trị tương đối. (Không nhập dấu ngoặc kép.)
- Nếu bạn muốn đánh dấu các bản ghi duy nhất cho danh sách thứ hai, công thức sẽ là "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", với phạm vi ô của danh sách đầu tiên được hiển thị dưới dạng giá trị tuyệt đối và tham chiếu vào ô đầu tiên của danh sách thứ hai dưới dạng giá trị tương đối. (Không nhập dấu ngoặc kép.)
- Nếu bạn muốn đánh dấu các bản ghi trong mỗi danh sách được tìm thấy trong danh sách kia, bạn sẽ cần hai công thức, một cho danh sách đầu tiên và một cho danh sách thứ hai. Công thức cho danh sách đầu tiên là "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", trong khi công thức cho danh sách thứ hai là COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Như đã lưu ý trước đó, bạn chọn danh sách đầu tiên để áp dụng công thức của nó và sau đó chọn danh sách thứ hai để áp dụng công thức của nó.
- Áp dụng bất kỳ định dạng nào bạn muốn để đánh dấu các bản ghi đang được gắn cờ. Bấm OK để đóng hộp thoại.
Video - Bằng cách sử dụng dịch vụ này, một số thông tin có thể được chia sẻ với YouTube
Lời khuyên
- Thay vì sử dụng tham chiếu ô với phương pháp định dạng có điều kiện COUNTIF, bạn có thể nhập một giá trị cần tìm kiếm và gắn cờ một hoặc nhiều danh sách cho các trường hợp của giá trị đó.
- Để đơn giản hóa các biểu mẫu so sánh, bạn có thể tạo tên cho danh sách của mình, chẳng hạn như "List1" và "List2". Sau đó, khi viết công thức, các tên danh sách này có thể thay thế cho các phạm vi ô tuyệt đối được sử dụng trong các ví dụ trên.
