Cách khớp dữ liệu trong google sheet
và tìm các giá trị duy nhất hoặc trùng lặp bằng cách sử dụng các hàm VLOOKUP, MATCH, INDEX, IF, COUNTIF hoặc Định dạng có điều kiện. Hướng dẫn từng bước này sẽ hỗ trợ người dùng Excel ở mọi cấp độ trong việc so sánh hai cột trong Excel hoặc Google trang tính.
Hình 1. Kết quả cuối cùng. So sánh hai cột trong Excel Show
Dữ liệu để so sánh hai cột trong ExcelDữ 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. Hình 2. Dữ liệu mẫu để so sánh hai cột trong Excel So sánh hai cột trong Excel bằng VLOOKUPVLOOKUP đượ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
Để 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. Hình 3. Excel VLOOKUP trả về giá trị khớp trong hai cột Đố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. hinh 4. VLOOKUP trả về #N/A cho giá trị chưa khớp 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. Hình 5. Sử dụng VLOOKUP để so sánh hai cột trong Excel So sánh hai cột trong Google trang tính bằng VLOOKUPTheo 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.
Kết quả so sánh trên Excel và Google sheet là như nhau như bảng bên dưới Hình 6. Sử dụng VLOOKUP để so sánh hai cột trong Google trang tính So sánh hai cột bằng Excel COUNTIFTrong 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
Để 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 ”, . Hình 7. Excel COUNTIF trả về số đếm cho một giá trị khớp trong hai cột Đố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. Hình 8. COUNTIF trả về "0" cho giá trị chưa khớp The value in D5 is “2” because “19003” is found twice in column C, specifically in C3 and C9. Hình 9. Sử dụng COUNTIF để so sánh hai cột trong Excel Tùy chỉnh kết quả bằng IF và COUNTIFChú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.
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. Hình 10. Sử dụng IF và COUNTIF để tùy chỉnh kết quả So sánh hai cột bằng MATCHKhi 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
Để 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. Hình 11. Sử dụng Excel MATCH để trả về vị trí của giá trị khớp trong hai cột 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à MATCHKế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
Để 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 Hình 12. Sử dụng Excel MATCH và INDEX để trả về các giá trị khớp trong hai cột 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ộtGiả 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. Hình 13. Chọn các ô để đánh dấu các kết quả phù hợp trong hai cột 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. Hình 14. Tạo quy tắc mới trong Định dạng có điều kiện 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 Hình 15. Định dạng các giá trị trùng lặp trong hai cột 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 Hình 16. đầu ra. Quy tắc định dạng được phản ánh trong các giá trị trùng lặp Đánh dấu các giá trị duy nhất trong hai cộtChú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 ”. Hình 17. đầu ra. Quy tắc định dạng được phản ánh trong các giá trị duy nhất 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? |