Dữ liệu để so sánh hai cột trong Excel
Dữ liệu của chúng tôi bao gồm ba cột. Danh sách 1 [cột B], Danh sách 2 [cột C] và So sánh [cột D]. Chúng tôi muốn so sánh mã sản phẩm trong hai cột B và C. Kết quả đối chiếu sẽ được ghi vào cột D.
So sánh hai cột trong Excel bằng VLOOKUP
VLOOKUP được sử dụng khi chúng ta muốn tra cứu một giá trị trong một cột và xác định xem giá trị đó có tồn tại trong cột khác hay không. Có hai loại đối sánh. đối sánh gần đúng hoặc đối sánh chính xác. Khi so sánh các danh sách, đối sánh chính xác được sử dụng phổ biến nhất.
Cú pháp hàm VLOOKUP
=VLOOKUP[lookup_value, table_array, col_index_num, [range_lookup]]
- lookup_value – giá trị mà chúng ta muốn tìm trong table_array
- table_array – phạm vi ô chứa dữ liệu chúng tôi muốn tìm hoặc truy xuất
- col_index_num – số cột trong table_array tương ứng với thông tin chúng tôi muốn truy xuất, liên quan đến lookup_value
- [ range_lookup ] – tùy chọn;
- nếu TRUE hoặc bị bỏ qua, VLOOKUP trả về kết quả khớp chính xác hoặc gần đúng
- nếu FALSE, VLOOKUP sẽ chỉ tìm kết quả khớp chính xác
Để xác định xem một giá trị trong cột đầu tiên [Danh sách 1] có tồn tại trong một cột khác [Danh sách 2] hay không và trả về chính giá trị đó, chúng tôi làm theo các bước sau.
Bước 1 . Nhập công thức vào ô D3. ______1
Bước 2 . Nhấn ENTER
Bước 3 . Sao chép công thức trong ô D3 sang ô D4. D9 bằng cách nhấp vào biểu tượng “+” ở góc dưới cùng bên phải của ô D3 và kéo xuống
lookup_value của chúng tôi là giá trị trong cột B [B3], trong khi table_array của chúng tôi là tập hợp các giá trị trong cột C, $C$3. $C$9. Col_index_num là 1, trả về giá trị trong cột đầu tiên so với table_array nếu giá trị trong cột B được tìm thấy trong cột C. Ngược lại, hàm sẽ trả về giá trị “ #N/A ”. range_lookup là FALSE, có nghĩa là hàm sẽ tìm kiếm một kết quả khớp chính xác.
Kết quả là giá trị trong ô D3 là “ 19001 ”, because the value in column B “19001” is found in column C.
Đối với giá trị tiếp theo trong cột B, “19002”, hàm VLOOKUP trả về “#N/A” trong ô D4 vì không tìm thấy “19002” trong cột C.
Bảng bên dưới hiển thị kết quả cuối cùng sau khi so sánh hai danh sách bằng VLOOKUP. Các giá trị 19001, 19003, 19005 và 19006 có mặt trong hai danh sách.
So sánh hai cột trong Google trang tính bằng VLOOKUP
Theo cách tương tự, chúng ta có thể so sánh hai cột trong Google trang tính bằng cách sử dụng VLOOKUP theo quy trình tương tự ở trên. Ta chèn công thức sau vào D3 và kéo xuống D9.
=VLOOKUP[B3,$C$3:$C$9,1,FALSE]
Kết quả so sánh trên Excel và Google sheet là như nhau như bảng bên dưới
So sánh hai cột bằng Excel COUNTIF
Trong khi làm việc với Excel hoặc Google trang tính và chúng tôi .
Cú pháp của hàm COUNTIF
=COUNTIF[range,criteria]
- phạm vi – phạm vi dữ liệu sẽ được đánh giá bằng tiêu chí
- tiêu chí – tiêu chí hoặc điều kiện xác định ô nào sẽ được tính
Để xác định số lần một giá trị ở cột B tồn tại ở cột C, ta làm theo các bước sau.
Bước 1 . Nhập công thức vào ô D3. ______4
Bước 2 . Nhấn ENTER
Bước 3 . Sao chép công thức trong ô D3 sang ô D4. D9 bằng cách nhấp vào biểu tượng “+” ở góc dưới cùng bên phải của ô D3 và kéo xuống
Phạm vi của chúng tôi là tập hợp các giá trị trong cột C, $C$3. $C$9. Tiêu chí là B3 hoặc giá trị trong cột B. Hàm trả về tần số của B3 trong phạm vi ở cột C. Do đó, giá trị trong ô D3 là “ 1 ”, .
Đối với B4, hoặc “19002”, hàm COUNTIF trả về “0”, vì không tìm thấy “19002” trong cột C.
The value in D5 is “2” because “19003” is found twice in column C, specifically in C3 and C9.
Tùy chỉnh kết quả bằng IF và COUNTIF
Chúng ta có thể tùy chỉnh kết quả khi so sánh hai cột trong Excel bằng cách kết hợp các hàm IF và COUNTIF. Chúng tôi làm theo quy trình tương tự như trong ví dụ trước nhưng chúng tôi sẽ sử dụng công thức này.
=IF[COUNTIF[$C$3:$C$9,B3]>0,"Match found","Not found"]
Thay vì trả về số lượng các giá trị khớp nhau trong hai danh sách, the formula returns “Match found” when the value in column B exists in column C, and returns “Not found” if otherwise.
So sánh hai cột bằng MATCH
Khi chúng tôi so sánh hai cột trong Excel, chúng tôi có thể muốn trả về vị trí của một giá trị trong một cột được chỉ định, thay vì giá trị . Hàm MATCH là giải pháp hoàn hảo cho việc này, vì nó trả về vị trí của một giá trị trong một phạm vi đã chỉ định. Tuy nhiên, lưu ý rằng nó chỉ xem xét trận đấu đầu tiên.
Cú pháp của hàm MATCH
=MATCH[lookup_value, lookup_array, [match_type]]
- lookup_value – giá trị mà chúng tôi muốn tìm trong lookup_array
- lookup_array – phạm vi ô chứa giá trị chúng tôi muốn so khớp
- [match_type] – tùy chọn;
Để xác định xem một giá trị có tồn tại trong hai cột B và C hay không và trả về vị trí của nó thay vì chính giá trị đó, chúng ta làm theo các bước sau.
Bước 1 . Nhập công thức vào ô D3. ______7
Bước 2 . Nhấn ENTER
Bước 3 . Sao chép công thức trong ô D3 sang ô D4. D9 bằng cách nhấp vào biểu tượng “+” ở góc dưới cùng bên phải của ô D3 và kéo xuống
Lookup_value của chúng tôi là giá trị trong cột B, trong khi lookup_array của chúng tôi là tập hợp các giá trị trong cột C, $C$3. $C$9. match_type là “0” để tìm kết quả khớp chính xác.
As a result, the value in cell D3 is “ 4 ”, because the value in column B “19001” is found in the fourth position [C6] in the lookup_array C3. C9. Mặt khác, khi không tìm thấy giá trị, hàm trả về “#N/A”, đây là trường hợp của D4, D6 và D9.
Đối với “19003”, có hai kết quả phù hợp trong cột C, cụ thể là C3 và C9. Tuy nhiên, hàm MATCH chỉ xem xét kết quả khớp đầu tiên và bỏ qua mọi kết quả khớp khác. In D5, the result is “1”, because the first match for “19003” is in the 1st position in the range C3. C9.
So sánh hai cột bằng INDEX và MATCH
Kết hợp các hàm INDEX và MATCH được sử dụng khi chúng tôi muốn so khớp hai cột trong Google trang tính của chúng tôi or in Microsoft Excel. The MATCH function, as discussed above, returns the position of a value in a specified column while INDEX function returns the value of a cell in a range based on a column and row number.
Cú pháp hàm INDEX
________số 8
- mảng – một dải ô mà chúng tôi muốn truy xuất một số dữ liệu
- row_num – hàng trong mảng mà chúng ta muốn truy xuất dữ liệu
- column_num – cột trong mảng mà chúng ta muốn lấy dữ liệu từ đó;
Để so sánh hai cột B và C và trả về giá trị phù hợp bằng cách sử dụng INDEX và MATCH, chúng tôi làm theo các bước sau.
Bước 1 . Nhập công thức vào ô D3. ______9
Bước 2 . Nhấn ENTER
Bước 3 . Sao chép công thức trong ô D3 sang ô D4. D9 bằng cách nhấp vào biểu tượng “+” ở góc dưới cùng bên phải của ô D3 và kéo xuống
Mảng của chúng tôi là tập hợp các giá trị trong cột C, $C$3. $C$9 và số hàng được cung cấp bởi hàm MATCH
Trong ví dụ đầu tiên, hàm MATCH của chúng tôi tra cứu B3 hoặc “19001” trong phạm vi C3. C9 và trả về “4” là vị trí của “19001” trong phạm vi C3. C9. Hàm INDEX sau đó trả về giá trị thứ tư trong mảng C3. C9, là C6 hoặc “19001”. Số cột bị bỏ qua vì chúng tôi chỉ xem xét một cột trong mảng của chúng tôi, cột C.
Cuối cùng, giá trị trong ô D3 là “ 19001 ”. The combination of INDEX and MATCH when used to so sánh hai cột mang lại kết quả giống như khi chúng ta sử dụng VLOOKUP.
So sánh hai cột bằng Định dạng có điều kiện
Định dạng có điều kiện là một hàm tích hợp khác trong Excel hoặc Google trang tính giúp chúng tôi so sánh hai cột dựa trên một bộ quy tắc.
Đánh dấu các giá trị trùng lặp trong hai cột
Giả sử chúng tôi muốn so sánh hai cột trong Excel và đánh dấu các giá trị khớp hoặc trùng lặp các giá trị trong B và C. Chúng tôi làm theo các bước sau.
Bước 1 . Chọn các ô muốn bôi đậm B3. C9.
Bước 2 . Nhấp vào tab Trang chủ, Menu Định dạng có điều kiện và chọn “ Đánh dấu Quy tắc ô ”, sau đó nhấp vào “ Các giá trị trùng lặp ”. The Duplicate Values dialog box will pop up.
Bước 3 . Trong hộp thoại Giá trị trùng lặp, hãy chọn “ Nhân bản ” và “ Điền màu đỏ nhạt với văn bản màu đỏ đậm ” in the drop-down lists
Bước 4 . Nhấn OK để áp dụng quy tắc định dạng.
Kết quả là các giá trị trùng lặp 19001, 19003, 19005 và 19006 ở hai cột B và C được tô sáng
Đánh dấu các giá trị duy nhất trong hai cột
Chúng ta cũng có thể so sánh hai cột trong Excel và đánh dấu sự khác biệt và sử dụng định dạng có điều kiện để chỉ đánh dấu các giá trị duy nhất. Chúng tôi làm theo quy trình tương tự nhưng lần này, chúng tôi chọn “ Duy nhất ” từ danh sách thả xuống và chọn bất kỳ định dạng nào chúng tôi muốn. Trong trường hợp này, chúng tôi chọn kiểu định dạng “ Nền màu vàng với văn bản màu vàng đậm ”.
Do đó, các giá trị duy nhất 19002, 19004 và 19007 trong hai cột B và C được tô sáng
Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn là một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút.
Bạn vẫn đang tìm trợ giúp về hàm IF phải không?