Cách khớp hàng trong Excel
|
Trong nhiều trường hợp, việc hợp nhất dữ liệu trong Excel có thể dễ dàng được thực hiện bằng một INDEX-MATCH (hoặc VLOOKUP) duy nhất. Tuy nhiên, đôi khi, đã đến lúc sử dụng những khẩu súng lớn — INDEX-MATCH-MATCH Show
INDEX-MATCH-MATCH kết hợp hai câu lệnh MATCH vào vị trí hàng và cột trong công thức INDEX. Khi làm như vậy, bạn đang tạo một công thức có thể điền sang trái, phải, lên hoặc xuống khi hợp nhất hoặc tìm dữ liệu Trong hướng dẫn từng bước này, chúng ta sẽ tạo một công thức INDEX-MATCH, sau đó thêm câu lệnh MATCH thứ hai để biến nó thành INDEX-MATCH-MATCH Cách hợp nhất dữ liệu trong Excel hoạt độngCó hai cách chính để hợp nhất dữ liệu trong Excel — VLOOKUP và INDEX-MATCH. Cả hai đều hoạt động giống nhau Với cả VLOOKUP và INDEX-MATCH, bạn có cụm từ tìm kiếm mà bạn muốn tìm trong một cột. Khi Excel tìm thấy cụm từ tìm kiếm, nó sẽ tìm giá trị tương ứng trên cùng một hàng trong một cột khác. Nó giống như tìm một số điện thoại trong danh bạ điện thoại Nếu bạn chưa bao giờ cầm danh bạ điện thoại, điều đó hợp lý. Dưới đây là một hình ảnh dưới đây để tham khảo. Ngày xưa, bạn sẽ mở danh bạ điện thoại, lật qua các trang để tìm họ hoặc doanh nghiệp, sau đó di ngón tay qua một hoặc hai cột để tìm số điện thoại. VLOOKUP và INDEX-MATCH thực hiện chính xác điều tương tự
Cuối cùng, bạn đang cố gắng lập trình tìm thông tin mà bạn không biết với thông tin bạn biết. Đó là ý tưởng hợp nhất dữ liệu. VLOOKUP và INDEX-MATCH hoạt động hơi khác một chút Sự khác biệt giữa VLOOKUP và INDEX-MATCHVới cả hai, bạn đang yêu cầu Excel tìm một giá trị trên một hàng rồi trả về một giá trị trong cột bên cạnh nó. Có một số khác biệt, mặc dù Hướng dẫn lập chỉ mục. Sự khác biệt lớn nhất là VLOOKUP chỉ nhìn đúng. Mảng VLOOKUP bao gồm mảng_lookup cũng như mảng. Nói cách khác, ban đầu bạn phải chọn cả cột tìm kiếm và đầu ra. Lựa chọn bắt đầu với cột tìm kiếm và VLOOKUP chỉ tìm ở bên phải của cột đó. INDEX-MATCH có thể nhìn cả bên trái và bên phải Tham chiếu các ô liền kề. Khi viết công thức VLOOKUP, bạn phải nhập thủ công số cột từ cột tìm kiếm Nhiều cột. Không giống như VLOOKUP, INDEX-MATCH có thể lập chỉ mục nhiều cột cho đầu ra có thể điền. Nói cách khác, mảng có thể có nhiều cột. Khi sử dụng tham chiếu đúng cách, bạn có thể kéo một công thức trên một trang tính và điền vào nhiều cột Khi nào nên sử dụng INDEX-MATCHGiống như bất kỳ hợp nhất dữ liệu nào, INDEX-MATCH hoạt động tốt nhất khi bạn có một mã định danh chung, duy nhất giữa hai nguồn dữ liệu. INDEX-MATCH được sử dụng tốt nhất để hợp nhất dữ liệu với các mã định danh duy nhất, bao gồm
Đây là những ví dụ về khóa chính. Để hợp nhất nhanh, giá trị chung không nhất thiết phải nghiêm ngặt như yêu cầu đối với khóa chính, nhưng ít nhất nó phải là duy nhất. Nếu có các giá trị trùng lặp trong mảng tìm kiếm của bạn, INDEX-MATCH trả về giá trị từ phiên bản đầu tiên, giá trị này có thể không chính xác Các phần của INDEX-MATCH và INDEX-MATCH-MATCHĐể hoàn thành INDEX-MATCH-MATCH, bạn sẽ cần một vài phần bảng tính. Trang tính là tài liệu làm việc của bạn hoặc một phần của trang tính. Trong ví dụ của chúng tôi, chúng tôi sẽ sử dụng một danh sách địa chỉ ngắn. Lưu ý cột mã trạng thái. Đó là cột mà chúng ta sẽ viết công thức INDEX-MATCH-MATCH
GHI CHÚ. Trong nhiều trường hợp, bạn sẽ làm việc trên một trang tính khác với mảng đầu ra. Chúng tôi sẽ không làm điều đó trong ví dụ này. Mọi thứ sẽ nằm trên một tờ giấy mảng tìm kiếm. Đây giống như chìa khóa trả lời. Nó chứa danh sách các giá trị bạn sẽ tìm kiếm và câu trả lời bạn muốn. Trong trường hợp này, nó sẽ là danh sách các tiểu bang và mã tiểu bang của chúng. Nhưng mảng tìm kiếm có thể lớn hơn nhiều
Để đặt hai trang tính này vào ngữ cảnh, đây là kết quả mục tiêu của chúng tôi Tham chiếu tuyệt đối. Trong ví dụ này, chúng tôi sẽ sử dụng tham chiếu tuyệt đối và tương đối. Thông thường, những điều này tương đương với các lựa chọn "khóa" trên một nhóm ô, cột hoặc hàng cụ thể. Đây là một phím nhanh để tham khảo Hàng tuyệt đối, cột tuyệt đối ($A$1. $B$2). Thao tác này sẽ khóa tìm kiếm của bạn trên các ô này. Chúng tôi sẽ khóa cả hàng và cột khi tham chiếu mảng tìm kiếm Cột tuyệt đối, hàng tương đối ($A1. $A20). Thao tác này sẽ khóa cột chứ không khóa hàng. Điều này sẽ hữu ích khi chúng ta viết công thức MATCH đầu tiên Hàng tuyệt đối, cột tương đối (A$1). Thao tác này sẽ khóa hàng chứ không khóa cột. Điều này sẽ hữu ích khi chúng ta viết công thức MATCH thứ hai Công thức INDEX-MATCH-MATCH. Công thức chúng tôi đang sử dụng có ba phần
Đây là cách công thức bị phá vỡ CÔNG THỨC = INDEX(mảng, row_num, [col_num]) mảng. Danh sách các giá trị nằm ở bên trái hoặc bên phải của giá trị tìm kiếm (ví dụ:. mã trạng thái) row_num / col_num. Chỉ mục thường hoạt động trên tọa độ ô (ví dụ:. 2, 2). Chúng ta sẽ thay thế chúng bằng câu lệnh MATCH MATCH(giá_trị_trao_đổi,_mảng_trao_đổi, [loại_so khớp]) tra cứu_array. Danh sách các giá trị bạn muốn tìm kiếm (ví dụ:. cột trạng thái) lookup_value. Thứ bạn muốn tìm mảng tìm kiếm (ví dụ:. ô trạng thái) Loại so khớp. Bạn muốn khớp như thế nào. Chúng tôi sẽ sử dụng "0" để khớp chính xác Trong trường hợp này, chúng ta có "danh sách trạng thái" và "danh sách địa chỉ". Danh sách trạng thái là danh sách các trạng thái và mã trạng thái của chúng. Phần khác là danh sách địa chỉ. Chúng tôi sẽ tham chiếu danh sách trạng thái từ danh sách địa chỉ với INDEX-MATCH Bước 1. Tạo một cột đầu raTrong trang tính của bạn, hãy tạo một cột và đặt tên cho nó giống như mảng đầu ra. Tốt nhất là sao chép và dán hoặc tham chiếu ô để đảm bảo chúng hoàn toàn giống nhau. Điều đó sẽ quan trọng sau này Bước 2. Bắt đầu viết công thức trong cột StateCodeChúng ta sẽ bắt đầu viết công thức của mình trong ô đầu tiên của cột mới. Nếu chúng tôi đã thực hiện tham chiếu đúng cách, chúng tôi sẽ có thể điền vào ô sau
Bước 3. Cho Excel biết nơi bạn muốn tìm kiếmHãy nhớ rằng danh sách tiểu bang ánh xạ các tên thành chữ viết tắt của chúng. Nó giống như chìa khóa trả lời. Mảng là danh sách các giá trị bạn muốn trong cột Mã trạng thái mới của bạn Chúng tôi muốn khóa toàn bộ lựa chọn đó vì nó sẽ không bao giờ thay đổi, vì vậy chúng tôi sẽ sử dụng tham chiếu tuyệt đối. Nếu lựa chọn này là tương đối, thì khi chúng tôi lập chỉ mục cho cột Mã trạng thái mới, mảng cũng sẽ di chuyển và bắt đầu ánh xạ Alabama thành AK, rồi AZ, rồi AR… Chúng tôi không muốn điều đó. Tham chiếu tuyệt đối là quan trọng CÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH(lookup_value, lookup_array, [match_type]),)
Bước 4. Nói với Excel những gì bạn muốn tìmCÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, lookup_array, [match_type]),) Excel bây giờ biết những gì bạn muốn trong ô của bạn. Bây giờ bạn phải nói với nó những gì bạn muốn làm điểm tham chiếu. Đó là lookup_value của công thức MATCH lồng nhau
Lưu ý cột tuyệt đối. Bằng cách khóa cột, bạn sẽ có thể kéo công thức này sang phải, lên hoặc xuống mà không bị mất tham chiếu. Điều đó sẽ quan trọng sau này Bước 5. Cho Excel biết nơi bạn muốn tìm kiếmBạn có cụm từ tìm kiếm của bạn. Bây giờ hãy cho Excel biết nơi tìm thuật ngữ đó. Trong trường hợp này, nó sẽ là danh sách trạng thái — câu trả lời của bạn CÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, $A$1. $A$5, [match_type]),)
Bước 6. Cho Excel biết cách bạn muốn tìm kiếmCÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, $A$1. $A$5, 0),) Giá trị match_type có ba vị trí. -1, 0 và 1 Loại so khớp Mô tả trận đấu -1 So khớp giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value 0 Kết hợp chuẩn xác 1 So khớp giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value Hàm match_type không phân biệt chữ hoa chữ thường và cũng nhận các ký tự đại diện (*). Bạn sẽ biết mình đã làm gì sai nếu hàm trả về #N/A Trong trường hợp này, chúng tôi sẽ sử dụng hàm khớp chính xác vì tên trạng thái rời rạc Bước 7. TRẬN ĐẤU THỨ HAIMặc dù INDEX-MATCH dựa vào tọa độ hàng để lập chỉ mục cột, INDEX-MATCH-MATCH cũng cần một cột để tham khảo. Bạn sẽ nhớ rằng INDEX-MATCH hoạt động tốt nhất với các mã định danh duy nhất. INDEX-MATCH-MATCH cũng không khác. Đảm bảo tiêu đề cột của bạn là duy nhất. Nếu không, Excel sẽ chỉ tham chiếu tiêu đề cột đầu tiên Trong bước này, chúng ta sẽ bắt đầu câu lệnh MATCH thứ hai. Nó hoàn toàn giống với câu lệnh MATCH đầu tiên, nhưng tham chiếu đến cột Trong TRẬN ĐẤU đầu tiên, chúng tôi đã khóa cột bằng tham chiếu tuyệt đối ($C1). Trong trận đấu thứ hai, chúng tôi khóa hàng (D$1) để công thức không di chuyển xuống cùng với công thức. Thay vào đó, nó sẽ di chuyển qua các cột CÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, $A$1. $A$5, 0), MATCH(D$1, lookup_array, [match_type]))
Bước 8. Ánh xạ các cột vào các cột tập dữ liệuGiống như MATCH đầu tiên, chúng ta sẽ cần cho công thức biết nơi tìm cụm từ tìm kiếm. Trong MATCH thứ hai, nó sẽ là ô cột (hoặc các ô) CÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, $A$1. $A$5, 0), MATCH(D$1, $B$8, [match_type]))
Bước 9. Cho Excel biết cách khớp lạiGiống như câu lệnh MATCH đầu tiên, chúng ta sẽ sử dụng đối sánh chính xác — 0. Chúng tôi muốn tìm chính xác đúng cột để làm cho công thức này trở nên chống đạn. Nó có thể không hợp lý vào lúc này, nhưng các bước tiếp theo sẽ cho thấy điều này áp dụng như thế nào đối với các bộ dữ liệu khổng lồ CÔNG THỨC = CHỈ SỐ($B$1. $B$5, MATCH($C1, $A$1. $A$5, 0), TRẬN ĐẤU(A$1, $B$2, 0)) Bước 10. Xem những gì xảy raNhấn enter để thực hiện công thức và bạn sẽ thấy AK trong ô đầu tiên
Đây là sơ đồ về những gì đã xảy ra trong câu lệnh MATCH đầu tiên
Bước 11. Điền công thức của bạn xuốngCó rất nhiều cách để điền vào một cột. Chọn một trong những cảm thấy thoải mái nhất. Phím nóng là nhanh nhất, nhưng những phím khác cũng sẽ thực hiện công việc Nhấn đúp chuột. Bấm đúp vào hình vuông ở góc dưới cùng bên phải của ô phím nóng. Chọn xuống bằng CTRL + mũi tên xuống, sau đó điền xuống bằng CTRL + D Kéo và kéo. Kéo góc dưới cùng bên phải xuống cột Sao chép và dán. Với các công thức Excel được tham chiếu chính xác, bạn chỉ cần sao chép và dán vào một ô mới và công thức sẽ di chuyển cùng với nó
chúc mừng. Bạn đã hoàn thành INDEX-MATCH-MATCH Khi nào nên sử dụng INDEX-MATCH-MATCHINDEX-MATCH là một công thức vô giá để hợp nhất dữ liệu. INDEX-MATCH-MATCH cũng cho phép bạn đối sánh các tiêu đề cột. Nó thực sự hữu ích để chia nhỏ các bộ dữ liệu lớn thành những bộ có thể quản lý được Chẳng hạn, giả sử bạn có 11 cột và 51 hàng U. S. dữ liệu điều tra dân số
Bạn có thể dễ dàng biên dịch dữ liệu cho năm trạng thái liên tiếp theo cách thủ công. Bộ dữ liệu không lớn. Nhưng hãy tưởng tượng bạn có 100 cột và 50.000 hàng dữ liệu về khách hàng, quảng cáo hoặc khoảng không quảng cáo. Đó là công việc cho INDEX-MATCH-MATCH Trong trường hợp này, chúng tôi muốn kéo chọn U. S. Dữ liệu điều tra dân số sang một định dạng thân thiện hơn. Thay vì tìm kiếm trong bảng tính, chúng ta chỉ cần sao chép và dán (hoặc tham chiếu) các tiêu đề vào trang tính mới này. Chúng tôi sẽ sử dụng hai cột không liền kề, không liên tiếp để minh họa cách hoạt động của tính năng này
Nếu bạn đã xây dựng công thức chính xác, thì bạn chỉ cần điền ngay từ cột Mã trạng thái để điền phần còn lại của bảng tính. Với INDEX-MATCH-MATCH, việc các cột hoặc thậm chí các thuật ngữ tra cứu có cùng thứ tự với trang tính hay không không quan trọng
Khi được tham chiếu đúng cách, INDEX-MATCH-MATCH thực hiện rất tốt việc trả về dữ liệu bạn muốn từ tập dữ liệu lớn hoặc dữ liệu hợp nhất Đào tạo Excel từ CBT Nuggets Các công thức rất hữu ích để cắt và phân tích dữ liệu trong Excel, nhưng cũng có một số tính năng tuyệt vời được đưa vào phần mềm. Chẳng hạn, bạn có thể dễ dàng tạo PivotTable hoặc sử dụng tính năng lọc để làm điều tương tự. Huấn luyện viên CBT Nuggets Simona Millham đã đào tạo cho mọi cấp độ kỹ năng - cơ bản đến nâng cao Bắt đầu học Excel ngay hôm nay  Tải xuống Hướng dẫn chứng chỉ mạng cơ bảnHướng dẫn dài 97 trang về mọi chứng chỉ của Cisco, Juniper, F5 và NetApp và cách chúng phù hợp với sự nghiệp của bạn Họ Cần thiết Họ Vai trò công việc của bạn là gì? Gửi cho tôi tin tức và tài nguyên đào tạo CNTT về CBT Nuggets. (không bắt buộc) NộpBằng cách gửi biểu mẫu này, bạn đồng ý rằng bạn đã đọc, hiểu và có thể đồng ý với chính sách bảo mật của chúng tôi |
