Vlookup trả về nhiều giá trị trong một ô được phân tách bằng dấu phẩy google sheet
|
Excel có một số công thức tra cứu tuyệt vời, chẳng hạn như VLOOKUP, INDEX/MATCH (và bây giờ là XLOOKUP), nhưng không có công thức nào trong số này cung cấp cách trả về nhiều giá trị khớp. Tất cả những thứ này hoạt động bằng cách xác định trận đấu đầu tiên và trả lại trận đấu đó Show
Vì vậy, tôi đã viết mã VBA một chút để tạo ra một hàm tùy chỉnh (còn được gọi là Hàm do người dùng xác định) trong Excel Cập nhật. Sau khi Excel phát hành mảng động và các hàm tuyệt vời như UNIQUE và TEXTJOIN, giờ đây bạn có thể sử dụng một công thức đơn giản và trả về tất cả các giá trị phù hợp trong một ô (được trình bày trong hướng dẫn này) Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách thực hiện (nếu bạn đang sử dụng phiên bản Excel mới nhất – Microsoft 365 với tất cả các chức năng mới), cũng như cách thực hiện việc này trong trường hợp bạn đang sử dụng các phiên bản cũ hơn ( Vậy hãy bắt đầu Hướng dẫn này bao gồm Tra cứu và trả về nhiều giá trị trong một ô (Sử dụng công thức)Nếu bạn đang sử dụng Excel 2016 hoặc các phiên bản trước đó, hãy chuyển đến phần tiếp theo nơi tôi hướng dẫn cách thực hiện việc này bằng VBA. Với đăng ký Microsoft 365, Excel của bạn hiện có nhiều chức năng và tính năng mạnh mẽ hơn không có trong các phiên bản trước (chẳng hạn như XLOOKUP, Mảng động, hàm ĐỘC ĐÁO/LỌC, v.v. ) Vì vậy, nếu bạn đang sử dụng Microsoft 365 (trước đây gọi là Office 365), bạn có thể sử dụng các phương pháp được đề cập trong phần này để có thể tra cứu và trả về nhiều giá trị trong một ô duy nhất trong Excel Và như bạn sẽ thấy, đó là một công thức thực sự đơn giản Dưới đây tôi có một tập dữ liệu trong đó tôi có tên của những người trong cột A và quá trình đào tạo mà họ đã thực hiện trong cột B Bấm vào đây để tải file ví dụ về và làm theo Đối với mỗi người, tôi muốn biết họ đã hoàn thành khóa đào tạo nào. Trong cột D, tôi có danh sách các tên duy nhất (từ cột A) và tôi muốn nhanh chóng tra cứu và trích xuất tất cả các khóa đào tạo mà mọi người đã thực hiện và lấy những tên này trong một tập hợp duy nhất (được phân tách bằng dấu phẩy) Dưới đây là công thức sẽ làm điều này =TEXTJOIN(", ",TRUE,IF(D2=$A$2:$A$20,$B$2:$B$20,""))Sau khi nhập công thức vào ô E2, hãy sao chép nó cho tất cả các ô mà bạn muốn có kết quả Công thức này hoạt động như thế nào? Hãy để tôi giải cấu trúc công thức này và giải thích từng phần về cách nó kết hợp với nhau sẽ cho chúng ta kết quả Phép thử logic trong công thức IF (D2=$A$2. $A$20) kiểm tra xem ô tên D2 có giống ô tên trong phạm vi A2 không. A20 Nó đi qua từng ô trong phạm vi A2. A20 và kiểm tra xem tên trong ô D2 có giống nhau hay không. nếu trùng tên thì trả về TRUE, ngược lại trả về FALSE Vì vậy, phần này của công thức sẽ cung cấp cho bạn một mảng như hình bên dưới {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} Vì chúng tôi chỉ muốn đào tạo cho Bob (giá trị trong ô D2), nên chúng tôi cần nhận tất cả đào tạo tương ứng cho các ô trả về TRUE trong mảng trên Điều này được thực hiện dễ dàng bằng cách chỉ định một phần [value_if_true] của công thức IF là phạm vi có phần đào tạo. Điều này đảm bảo rằng nếu tên trong ô D2 khớp với tên trong phạm vi A2. A20, công thức IF sẽ trả về tất cả quá trình đào tạo mà người đó đã thực hiện Và bất cứ nơi nào mảng trả về FALSE, chúng tôi đã chỉ định giá trị [value_if_false] là “” (trống), vì vậy nó trả về một khoảng trống Phần IF của công thức trả về mảng như hình bên dưới {“Excel”;””;””;”PowerPoint”;””;””;””;””;””;””;””;””;””;””;”” Nơi có tên của khóa đào tạo mà Bob đã tham gia và để trống những nơi không có tên của Bob Bây giờ, tất cả những gì chúng ta cần làm là kết hợp các tên đào tạo này (được phân tách bằng dấu phẩy) và trả lại nó trong một ô Và điều đó có thể dễ dàng thực hiện bằng cách sử dụng công thức TEXTJOIN mới (sẵn dùng trong Excel 2019 và Excel trong Microsoft 365) Công thức TEXTJOIN có ba đối số
Nếu bạn đang sử dụng Excel trong Microsoft 365 đã có mảng động, bạn chỉ cần nhập công thức trên và nhấn enter. Và nếu bạn đang sử dụng Excel 2019, bạn cần nhập công thức và giữ phím Control và phím Shift rồi nhấn Enter Bấm vào đây để tải file ví dụ về và làm theo Nhận nhiều giá trị tra cứu trong một ô (không lặp lại)Vì công thức ĐỘC ĐÁO chỉ khả dụng từ Excel trong Microsoft 365 nên bạn sẽ không thể sử dụng phương pháp này trong Excel 2019 Trong trường hợp có sự lặp lại trong tập dữ liệu của bạn, như hình dưới đây, bạn cần thay đổi công thức một chút để bạn chỉ nhận được danh sách các giá trị duy nhất trong một ô Trong tập dữ liệu trên, một số người đã tham gia đào tạo nhiều lần. Ví dụ: Bob và Stan đã tham gia khóa đào tạo về Excel hai lần và Betty đã tham gia khóa đào tạo về MS Word hai lần. Nhưng trong kết quả của chúng tôi, chúng tôi không muốn lặp lại tên đào tạo Bạn có thể sử dụng công thức dưới đây để làm điều này =TEXTJOIN(", ",TRUE,UNIQUE(IF(D2=$A$2:$A$20,$B$2:$B$20,"")))Công thức trên hoạt động theo cùng một cách, với một thay đổi nhỏ. chúng tôi đã sử dụng công thức IF trong hàm UNIQUE để trong trường hợp có sự lặp lại trong kết quả của công thức if, hàm UNIQUE sẽ loại bỏ nó Nhấn vào đây để tải về tập tin ví dụ Tra cứu và trả về nhiều giá trị trong một ô (Sử dụng VBA)Nếu bạn đang sử dụng Excel 2016 hoặc các phiên bản trước đó thì bạn sẽ không có quyền truy cập vào công thức TEXTJOIN. Vì vậy, cách tốt nhất để tra cứu và nhận nhiều giá trị phù hợp trong một ô là sử dụng công thức tùy chỉnh mà bạn có thể tạo bằng VBA. Để lấy nhiều giá trị tra cứu trong một ô, chúng ta cần tạo một hàm trong VBA (tương tự như hàm VLOOKUP) để kiểm tra từng ô trong một cột, nếu tìm thấy giá trị tra cứu thì thêm vào kết quả Đây là mã VBA có thể làm điều này 'Code by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells.Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) – 1) End Function Đặt mã này ở đâu?
Công thức này hoạt động như thế nào? Hàm này hoạt động tương tự như hàm VLOOKUP Phải mất 3 đối số làm đầu vào 1. Giá trị tra cứu – Một chuỗi mà chúng ta cần tra cứu trong một dải ô Khi bạn sử dụng công thức này, công thức sẽ kiểm tra từng ô ở cột ngoài cùng bên trái trong phạm vi tra cứu và khi tìm thấy kết quả phù hợp, công thức sẽ thêm vào kết quả trong ô mà bạn đã sử dụng công thức Nhớ. Lưu sổ làm việc dưới dạng sổ làm việc hỗ trợ macro (. xlsm hoặc. xls) để sử dụng lại công thức này. Ngoài ra, chức năng này sẽ chỉ khả dụng trong sổ làm việc này chứ không phải trong tất cả các sổ làm việc Nhấn vào đây để tải về tập tin ví dụ Tìm hiểu cách tự động hóa các tác vụ lặp đi lặp lại nhàm chán bằng VBA trong Excel. Tham gia Khóa học Excel VBA Nhận nhiều giá trị tra cứu trong một ô (không lặp lại)Có khả năng bạn có thể có sự lặp lại trong dữ liệu Nếu bạn sử dụng mã được sử dụng ở trên, nó cũng sẽ cung cấp cho bạn các lần lặp lại trong kết quả Nếu bạn muốn nhận được kết quả không lặp lại, bạn cần sửa đổi mã một chút Đây là mã VBA sẽ cung cấp cho bạn nhiều giá trị tra cứu trong một ô mà không có bất kỳ sự lặp lại nào 'Code by Sumit Bansal (https://trumpexcel.com)
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
For J = 1 To i - 1
If LookupRange.Cells(J, 1) = Lookupvalue Then
If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then
GoTo Skip
End If
End If
Next J
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
Skip:
End If
Next i
MultipleLookupNoRept = Left(Result, Len(Result) - 1)
End FunctionKhi bạn đã đặt mã này vào Trình soạn thảo VB (như đã trình bày ở trên trong phần hướng dẫn), bạn sẽ có thể sử dụng hàm MultipleLookupNoRept Đây là ảnh chụp nhanh về kết quả mà bạn sẽ nhận được với hàm MultipleLookupNoRept này Nhấn vào đây để tải về tập tin ví dụ Trong hướng dẫn này, tôi đã trình bày cách sử dụng công thức và VBA trong Excel để tìm và trả về nhiều giá trị tra cứu trong một ô trong Excel Mặc dù có thể dễ dàng thực hiện bằng một công thức đơn giản nếu bạn đang sử dụng Excel trong đăng ký Microsoft 365, nhưng nếu bạn đang sử dụng các phiên bản trước và không có quyền truy nhập vào các hàm như TEXTJOIN, thì bạn vẫn có thể thực hiện việc này bằng VBA bằng cách tạo Bạn cũng có thể thích các Hướng dẫn Excel sau
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 55 suy nghĩ về "Tra cứu và trả về nhiều giá trị trong một ô trong Excel (Công thức & VBA)"
|
