Hàm VLOOKUP chỉ trả về một kết quả khớp duy nhất. Và nếu một giá trị tra cứu có nhiều kết quả phù hợp trong một phạm vi thì sao?
Mục lục
Cách VLOOKUP cho nhiều kết quả trùng khớp
Chúng tôi đã nhập tập dữ liệu từ Google Trang tính sang Excel bằng Coupler. io, giải pháp xuất dữ liệu tự động từ nhiều ứng dụng và nguồn. Bây giờ chúng ta cần tra cứu địa chỉ email của người dùng có tên “Lynn“. Công thức VLOOKUP chỉ trả về một kết quả khớp, trong khi có một số
Đọc thêm về tích hợp Microsoft Excel để xuất dữ liệu theo lịch biểu
Cách tốt nhất để tra cứu tất cả các kết quả trùng khớp là hàm FILTER, sẵn dùng cho Excel Online và Excel for Microsoft 365
Những người dùng Excel khác sẽ phải sử dụng một giải pháp thay thế để trả lại tất cả kết quả phù hợp, nhưng vẫn có thể
Hàm LỌC Excel
cú pháp
=FILTER[array,include,[if_empty]]array– phạm vi hoặc mảng để lọcinclude– tiêu chí lọcif_empty– tham số tùy chọn để trả về khi không có kết quả nào đáp ứng tiêu chí
Trong ví dụ của chúng ta, công thức FILTER để trả về nhiều giá trị sẽ như sau
=FILTER[D2:D100,B2:B100=H5]
Nếu vì bất kỳ lý do gì mà bạn không thể hoặc không muốn sử dụng Excel Online hoặc Excel 365, thì bạn cần tìm hiểu cách giải quyết sau.
Trong bài đăng này, chúng ta sẽ thảo luận về cách mô phỏng sử dụng VLOOKUP để trả về nhiều hàng và/hoặc cột phù hợp. Ý bạn là gì khi "mô phỏng" Jeff? . Tức là, VLOOKUP quét xuống phạm vi tra cứu và dừng lại ở hàng phù hợp đầu tiên… bỏ qua mọi hàng phù hợp bổ sung. Khi VLOOKUP tìm thấy một hàng phù hợp, nó sẽ bắn sang bên phải để truy xuất giá trị liên quan từ một cột. Nhưng giả sử chúng ta muốn trả về các giá trị từ nhiều cột và tất cả các hàng phù hợp. Chà, đây là lúc VLOOKUP bị hỏng. Vì vậy, chúng tôi sẽ mô phỏng chức năng này bằng cách chuyển sang Power Query. Bắt đầu nào… Tôi có một video và tường thuật đầy đủ bằng văn bản bên dưới
Băng hình
Chuyện kể
Trước khi chúng ta đi quá xa, hãy làm rõ mục tiêu của chúng ta. Giả sử chúng ta có một danh sách hóa đơn và danh sách của chúng ta chứa thông tin tóm tắt như InvoiceID và Khách hàng… đại loại như thế này [Bảng tóm tắt]
Chúng tôi cũng có một danh sách chi tiết hóa đơn, trong đó có chi tiết mục hàng cho từng hóa đơn. Nó chứa InvoiceID, Item, Amount, và có lẽ thông tin khác. Một cái gì đó như thế này [Bảng chi tiết]
Bây giờ, chúng tôi muốn đi qua từng mục trong bảng tóm tắt [từng hóa đơn] và lấy các giá trị liên quan từ bảng chi tiết. Bạn sẽ nhận thấy có nhiều hàng phù hợp trong bảng chi tiết cho mỗi hóa đơn. Ngoài ra, chúng tôi muốn truy xuất nhiều cột từ bảng chi tiết, ví dụ: cột Khoản mục và Số tiền
Khi chúng tôi hoàn thành, chúng tôi muốn kết hợp nhiều hàng và cột phù hợp như thế này
Vì VLOOKUP bị giới hạn trong việc truy xuất một giá trị ô từ hàng khớp đầu tiên và cột trả về được chỉ định, nên chúng tôi sẽ sử dụng Power Query thay thế
Chúng tôi sẽ thực hiện nó trong ba bước
- hợp nhất truy vấn
- Chỉ định nhiều cột
- Kết hợp nhiều hàng
Vì vậy, hay thực hiện ngay bây giơ
Ghi chú. nếu đây là dự án một lần, bạn có thể sử dụng VLOOKUP để truy xuất CustID từ bảng tóm tắt vào bảng chi tiết nếu bạn sử dụng đối sánh gần đúng và sắp xếp tóm tắt theo thứ tự tăng dần theo cột tra cứu. Sau đó viết một công thức cho mỗi giá trị cột mà bạn muốn trả về. Nhưng trên thực tế, Power Query sẽ tạo ra một giải pháp dễ dàng làm mới trong các giai đoạn sau khi bảng dữ liệu thay đổi.
hợp nhất truy vấn
Trong bước đầu tiên này, chúng tôi sẽ tải từng bảng vào Power Query rồi hợp nhất chúng. Vì vậy, đầu tiên, chúng ta chọn một ô bất kỳ trong bảng Summary và chọn lệnh Data > From Table. Bảng được tải vào Power Query
Sau đó, chúng ta nhấn chọn Home > Close & Load To… và chọn Only Create Connection như hình bên dưới
Ta làm lại các bước như trên để load bảng Detail vào Power Query
Bây giờ, chúng ta cần hợp nhất hai bảng này với Power Query. Một cách để thực hiện việc này từ bên trong trình soạn thảo Power Query là chọn lệnh Trang đầu > Hợp nhất Truy vấn. Một cách để làm điều này từ bên trong Excel là chọn lệnh Data > Get Data > Combine Queries > Merge. Dù bằng cách nào, bạn sẽ thấy hộp thoại Hợp nhất nơi bạn xác định bảng Tóm tắt trước và sau đó là bảng Chi tiết. Sau đó, bạn xác định cột tra cứu bằng cách chọn cột InvoiceID ở cả 2 bảng, như hình bên dưới
Ghi chú. Loại liên kết được đặt bên ngoài vì chúng tôi đã chọn bảng tóm tắt trước rồi mới đến bảng chi tiết. Nếu bạn đã chọn Chi tiết trước và sau đó là Tóm tắt, bạn sẽ muốn thay đổi Loại liên kết thành Bên ngoài bên phải. Tùy thuộc vào những gì bạn đang làm, các tùy chọn Loại tham gia khác có thể hữu ích, vì vậy hãy kiểm tra chúng.
Bây giờ, chúng tôi nhấp vào OK và chúng tôi thấy mình đang ở trong trình chỉnh sửa Power Query
Với các truy vấn của chúng tôi được hợp nhất, đã đến lúc thực hiện bước tiếp theo của chúng tôi
Chỉ định nhiều cột
Nhớ lại rằng một điều chúng tôi muốn thực hiện là trả về nhiều giá trị cột. VLOOKUP có thể trả về một giá trị từ một cột, nhưng chúng ta có thể dễ dàng trả về nhiều giá trị cột với Power Query
Để làm như vậy, chỉ cần nhấp vào biểu tượng Mở rộng ở bên phải của tiêu đề cột Chi tiết hoặc lệnh Chuyển đổi > Cột có cấu trúc > Mở rộng. Bạn sẽ có thể chọn một hoặc nhiều cột để trả về từ bảng chi tiết
Ở đây, chúng tôi muốn trả về các cột Mục và Số tiền. Chúng tôi nhấp vào OK và bam … chúng tôi đã trả lại nhiều cột
Bây giờ chúng tôi đã trả về nhiều cột, hãy giải quyết các hàng
Chỉ định nhiều hàng
Vào cuối ngày, chúng tôi muốn có một hàng cho mỗi InvoiceID, vì vậy, chúng tôi chọn cột InvoiceID rồi chọn lệnh Transform > Group By. Vì chúng tôi muốn hiển thị nhiều cột khác nhau cho mỗi InvoiceID, chúng tôi chọn Nâng cao trong hộp thoại Nhóm theo kết quả
Bây giờ, chúng tôi xác định từng cột bổ sung mong muốn. Trong trường hợp của chúng tôi, chúng tôi muốn có 3 cột
- Cột mới có tên là Số tiền, tính Tổng của cột Số tiền hiện tại
- Cột mới có tên Đếm, sẽ Đếm Hàng
- Cột mới có tên Mục, bao gồm Tất cả các Hàng
Hộp thoại được cập nhật ở đây
Chúng tôi nhấp vào OK và
Và, chúng tôi gần như ở đó. Bước cuối cùng là chuyển đổi cột Mục thành danh sách các giá trị mục thực được phân tách bằng dấu phẩy trong bảng chi tiết
Để làm điều này, chúng ta sẽ sử dụng một mẹo mà Ken và Miguel đã dạy tôi trong hội thảo của họ [nhân tiện, hãy xem cuốn sách M dành cho Data Monkey tuyệt vời của họ]
Chúng tôi nhấp vào Thêm cột > Cột tùy chỉnh. Trong hộp thoại Cột tùy chỉnh kết quả, chúng tôi nhập tên cột mong muốn, chẳng hạn như ItemList và viết công thức hiển thị bên dưới
Ghi chú. đối số [Mục] tham chiếu tên bảng và "Mục" tham chiếu tên cột
Nhấp vào OK kết quả trong một cột ItemList mới, như thế này
Bây giờ, chúng ta cần cho Power Query biết rằng chúng ta muốn chuyển đổi từng danh sách trong cột ItemList thành danh sách các giá trị được phân tách bằng dấu phẩy. Để làm điều này, chúng tôi chọn nút Mở rộng ở bên phải của tiêu đề ItemList và chọn Trích xuất giá trị…. Trong hộp thoại Trích xuất giá trị từ danh sách kết quả, chúng tôi chọn dấu phân cách ưa thích của mình. Ở đây, chúng ta sẽ sử dụng Dấu phẩy
Nhấp vào OK và bam
Chúng tôi có thể xóa cột Mục, sau đó nhấp vào Trang chủ > Đóng & Tải vào… và gửi nó tới một Bảng. Kết quả xuất hiện trong Excel như thế này
Và, đó chính xác là những gì chúng tôi đặt ra để làm
Bây giờ, phải mất khá nhiều bước để đến đây, nhưng bây giờ là tin tốt nhất. Khi điều này được thiết lập, tất cả những gì chúng ta cần làm trong các giai đoạn sau là nhấp chuột phải vào bảng kết quả và chọn Làm mới. Các hàng mới trong bảng tóm tắt hoặc bảng chi tiết sẽ tự động được đưa vào. Điều này có nghĩa là việc đầu tư thời gian để thiết lập điều này sẽ trả cổ tức hiệu quả cho mỗi giai đoạn tiếp theo
Nếu bạn có bất kỳ mẹo Power Query nào khác hoặc cách để cải thiện quy trình này, vui lòng chia sẻ bằng cách đăng nhận xét bên dưới… xin cảm ơn