Hàm VLOOKUP trong Excel có thể trả về tất cả các kết quả và nối chúng bằng dấu phẩy ở giữa không?
Xem video
- Mục tiêu là nối tất cả các câu trả lời văn bản từ một hàm VLOOKUP
- Phương pháp của Bill: Sử dụng một hàm VBA được gọi là GetAll
- Danh sách duy nhất sử dụng Xóa bản sao
- Phương pháp của Mike:
- Danh sách duy nhất sử dụng Bộ lọc nâng cao
- Đã thêm chức năng TEXTJOIN trong Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Do hàm IF, công thức yêu cầu Ctrl + Shift + Enter bất kỳ lúc nào bạn chỉnh sửa công thức
- Alt AQOR Enter sẽ chạy lại Bộ lọc nâng cao!
Bản ghi video
Tập 183: Tham gia tất cả các trận đấu VLOOKUP
Bill Jelen: Này, chào mừng trở lại. Đã đến lúc cho một Dueling Excel Podcast khác. Tôi là Bill Jelen đến từ, tôi sẽ tham gia cùng với Mike Girvin cho Excel Is Fun. Đây là Tập 183 của chúng tôi: Tham gia tất cả các trận đấu VLOOKUP.
(Âm nhạc)
Được rồi, câu hỏi hôm nay từ Matt. VLOOKUP có thể trả về tất cả các kết quả và nối chúng lại với nhau bằng dấu cách dấu phẩy giữa mỗi kết quả không. Ví dụ: 109876 là hai ở đây, nó có thể trả về khoảng trống dấu phẩy Low Oil Đã kiểm tra vào ngày 12 tháng 12 không. Và tất nhiên nếu có nhiều hơn, nó sẽ trở lại nhiều hơn. Được rồi, vì vậy giải pháp của tôi ở đây là sử dụng một số VBA. Được rồi, vì vậy hãy đảm bảo rằng nó được lưu dưới dạng xlsm, nếu không bạn không thể chạy VBA hoặc xlsb, nhưng không phải xlsx - xlsx là một tệp không thể chạy VBA. Chúng tôi sẽ nhấn Alt + F11, đảm bảo rằng bạn đang sử dụng Dual183 hoặc bất kể tên sổ làm việc của bạn là gì. Chèn Mô-đun vào mô-đun trống và chúng tôi sẽ dán mã này, được rồi.
Hãy xem chức năng này GetAll, và đây là số ID mà chúng tôi đang tìm kiếm và sau đó là phạm vi mà chúng tôi muốn tìm. Và chúng ta bắt đầu, chúng ta sẽ trả về một biến có tên là GetAll, vì vậy chúng ta bắt đầu với biến đó bằng với giá trị trống. Đối với mỗi ô trong phạm vi của tôi, nếu Giá trị ô là những gì chúng ta đang tìm kiếm thì chúng ta sẽ lấy GetAll = GetAll & “” và sau đó là Cell.Offset (0 hàng, 1 cột), nói cách khác là giá trị đó là ngay bên cạnh số ID đó vì trong VBA, đây là số ID. Nếu chúng tôi tìm thấy số ID phù hợp, chúng tôi muốn chuyển qua 1 cột. Bây giờ, điều gì sẽ xảy ra nếu bạn muốn chuyển 2 cột qua hoặc 3 cột, thì bạn thay đổi 0 hàng và 1 cột này thành 2. Được rồi, cũng hãy kiểm tra xem - chúng tôi không đặt dấu phẩy nếu đây là cái đầu tiên.Vì vậy, nếu biến GetAll hiện là “” thì chúng ta sẽ không đặt dấu phẩy, được chứ?
Vì vậy, bây giờ chúng ta có chức năng này ở đây, hãy xem nó dễ dàng như thế nào để giải quyết vấn đề của Matt. Chúng ta sẽ đến đây và lấy ID của anh ấy, Ctrl + C và dán Ctrl + V như vậy. Dữ liệu, Loại bỏ các bản sao, bấm OK. Vì vậy, có một danh sách ID duy nhất và sau đó chúng tôi muốn nói = getall và chúng tôi đang tìm kiếm giá trị đó trong dấu phẩy E2. Nhìn qua phạm vi này ở đây, tôi sẽ nhấn F4. F4 hoạt động giống như một hàm thông thường. Và một lần nữa chuyển câu hỏi của Matt ra, hãy nhấp đúp để bắn hạ câu hỏi đó. Nó sẽ hoạt động.
Và chúng ta hãy thử, hãy thử một cái gì đó điên rồ ở đây. Hãy làm một Cụm từ 1 và chỉ cần đặt một loạt chúng như Cụm từ 1 đến 10. Chúng tôi sẽ ký tất cả những điều này đến 109999. Dán, và sau đó dán vào đây. Sao chép công thức đó xuống, chỉnh sửa công thức để nó đi hết cỡ xuống dưới cùng, tất nhiên. Đúng vậy. Và nó sẽ trả về tất cả các Cụm từ đó. Được rồi, đó là giải pháp của tôi, VBA, một chức năng nhỏ ở đó. Mike, hãy xem bạn có gì.
Mike Girvin: Cảm ơn ,. GetAll, đó là một chức năng VBA tuyệt vời. Được rồi, tôi sẽ chuyển đến trang tính ngay đây. Tôi đã chuyển đổi nó thành một bảng Excel để khi chúng tôi thêm các bản ghi bên dưới, hy vọng mọi thứ sẽ cập nhật.
Bây giờ, điều đầu tiên tôi sẽ làm điều này thành hai phần. Tôi có thể làm một công thức ở đây để trích xuất danh sách duy nhất nhưng tôi muốn xem xét một tùy chọn khác: Bộ lọc nâng cao có tùy chọn trích xuất danh sách duy nhất và nó có thể được cập nhật. Tôi sẽ chỉ đánh dấu dữ liệu cột ID, chuyển sang Bộ lọc nâng cao hoặc tôi sẽ sử dụng bàn phím Alt, A, Q. Bây giờ, danh sách Bộ lọc tại chỗ, không thể nào. Tôi muốn sao chép nó vào một vị trí khác. Nó chỉ có cột A và vì đó là một bảng Excel sẽ mở rộng sau này. Tôi không có bất kỳ tiêu chí nào, tôi muốn Sao chép nó vào D1 và chỉ kiểm tra các bản ghi Duy nhất. Bấm OK.
Bây giờ, tôi sẽ đến đây, Tất cả nhận xét nhập và tôi sẽ sử dụng một hàm chỉ hoạt động trong Excel 2016 hàm Office 365: = TEXTJOIN. Chỉ riêng chức năng này đã đáng để tải phiên bản Excel mới nhất. Đây là một nhiệm vụ chung mà mọi người muốn làm, tham gia nhiều việc cùng nhau. Bây giờ dấu phân tách của chúng ta trong “,” và điều tuyệt vời về hàm này là chúng ta có thể yêu cầu nó Bỏ qua các ô trống. Bây giờ, tôi có thể đặt TRUE, 1 hoặc Bỏ qua, Bỏ qua. Vì vậy, tôi sẽ Để nó, Bỏ qua nó. Và đây là nơi chúng tôi cần văn bản của mình. Chúng ta sẽ sử dụng hàm IF để lọc ra và chỉ lấy các mục chúng ta muốn. Tôi sẽ nói rằng hãy xem qua toàn bộ cột này ở đây: Tên Bảng và sau đó trong () tên Trường, có phải bất kỳ ai trong số bạn = tham chiếu ô tương đối này không, đó là bài kiểm tra logic. Nếu tôi nhấp vào đây và nhấn phím F9 để đánh giá,bạn có thể thấy ngay bây giờ chúng ta chỉ có 2 TRUES, Ctrl + Z bây giờ tôi gõ dấu phẩy và với mảng Trues và Fals, bây giờ tôi có thể cung cấp cho nó các mục để chọn. Vì vậy, bây giờ, chúng tôi sẽ chỉ chọn các mục có ĐÚNG ở đây từ phạm vi này. Dấu phẩy và tôi muốn đảm bảo đặt “” - sẽ hiển thị dưới dạng ô trống liên quan đến đối số thứ hai trong TEXTJOIN.
Bây giờ, tôi sẽ đóng dấu ngoặc và bây giờ hàm IF sẽ tạo chuỗi Trues và Falses, các mục thực tế từ phạm vi này sẽ được chọn nếu nó thấy nó Đúng và tất cả các mục khác sẽ có ô trống đó. Và đoán xem? TEXTJOIN sẽ hoàn toàn bỏ qua tất cả các ô trống đó và chỉ trả lại các mục phù hợp với ID này, sau đó nối nó với dấu phân cách đó. Bây giờ đây chắc chắn là một công thức Mảng yêu cầu tổ hợp phím đặc biệt Ctrol + Shift + Enter. Đối số kiểm tra logic giữ hoạt động Mảng của chúng ta và đối số đó không thể tính toán chính xác hoạt động Mảng này trừ khi chúng ta sử dụng bàn phím Ctrl + Shift + Enter. Bây giờ tôi sẽ đóng dấu ngoặc đơn. Trên thực tế, chúng tôi có thể chứng minh 1 ngay tại đây trong Văn bản 1 nếu tôi F9 tất cả điều này, chúng tôi có thể thấy chúng tôi nhận được 2 mục, phần còn lại của các ô trống đó sẽ bị bỏ qua. Ctrl + Z. Bây giờ, hãy 's nhập giá trị này vào ô bằng Ctrl + Shift + Enter. Ngay lập tức nhìn lên Thanh công thức. Những dấu ngoặc nhọn đó là Excel cho bạn biết rằng nó đã hiểu và tính nó như một công thức Mảng. Bây giờ tôi có thể nhấp đúp và gửi nó xuống. Điều đó có vẻ tốt.
Tôi sẽ đi đến ô cuối cùng và nhấn F2 để xác minh rằng tất cả các phạm vi đang tìm kiếm chính xác. Bây giờ điều tôi không muốn làm là tôi không muốn nhấn Enter vì công thức đó sau khi chúng tôi đặt nó ở Chế độ chỉnh sửa sẽ chỉ tính toán chính xác nếu chúng tôi sử dụng Ctrl + Shift + Enter; hoặc, bởi vì chúng tôi đã nhập công thức, chúng tôi chỉ có thể sử dụng phím Esc để hoàn nguyên về bất kỳ thứ gì trong ô trước khi chúng tôi đặt nó ở Chế độ chỉnh sửa.
Bây giờ, hãy kiểm tra điều này. Tôi sẽ nhấp vào ô cuối cùng ở đây và nhấn Tab và sau đó nhập ID mới, Tab, Tab. Một bản ghi mới khác, Tab, và tôi có thể thấy rằng tôi không có đủ công việc ở đây. Đúng vậy, chúng ta sẽ đặt - Hoàn hảo và sau đó Nhập. Bây giờ, điều này sẽ không tự động cập nhật giống như nếu chúng ta có một loạt công thức mà chúng ta đang đếm các mục duy nhất và sau đó trích xuất các mục duy nhất, nhưng không vấn đề gì. Hay xem nay. Chúng tôi có thể cập nhật danh sách các bản ghi duy nhất này vì chúng tôi đã sử dụng Bộ lọc nâng cao và bạn bắt đầu từ ô nào không quan trọng vì khi Bộ lọc nâng cao được gọi, nó sẽ ghi nhớ phạm vi trích xuất và phạm vi mà nó đã xem xét ban đầu. Bạn có thể nhấp vào Bộ lọc nâng cao hoặc sử dụng bàn phím Alt + A + Q. Chúng tôi phải chọn Sao chép sang vị trí khác, nhưng hãy nhìn vào điều đó.Nó hoàn toàn được ghi nhớ và mở rộng thành A13 nhờ tính năng Bảng Excel. Nó đã ghi nhớ phạm vi giải nén. Tôi chỉ phải kiểm tra Bản ghi duy nhất nhưng nhấp vào OK.
Bây giờ, tôi phải quay lại và sao chép công thức này xuống. Và bạn đã hoàn thành, sử dụng Bộ lọc nâng cao và chức năng TEXTJOIN tuyệt vời với, trong hoạt động Mảng để chỉ lấy các mục phù hợp. Được rồi, quay lại.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Ồ, tôi muốn cảm ơn mọi người đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần tới với một podcast khác của Dueling Excel và Excel Is Fun.
Tải tập tin
Tải file mẫu tại đây: Duel183.xlsm
