Может ли Excel VLOOKUP вернуть все результаты и соединить их запятой между ними?
Смотреть видео
- Цель состоит в том, чтобы объединить все текстовые ответы из ВПР.
- Метод Билла: используйте функцию VBA под названием GetAll
- Уникальный список с использованием удаления дубликатов
- Метод Майка:
- Уникальный список с использованием расширенного фильтра
- Функция TEXTJOIN добавлена в Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Из-за функции ЕСЛИ формула требует Ctrl + Shift + Enter каждый раз, когда вы редактируете формулу.
- Alt AQOR Enter повторно запустит Расширенный фильтр!
Стенограмма видео
Эпизод 183: Присоединяйтесь ко всем матчам VLOOKUP
Билл Джелен: Привет, с возвращением. Пришло время для еще одного подкаста Dueling Excel. Я Билл Джелен из, ко мне присоединится Майк Гирвин из Excel Is Fun. Это наш Эпизод 183: Присоединяйтесь ко всем матчам VLOOKUP.
(Музыка)
Хорошо, сегодня вопрос от Мэтта. Может ли функция ВПР возвращать все результаты и объединять их, разделяя их запятыми. Например, 109876, которые здесь эти два, может ли он вернуть пространство запятой низкого уровня масла Проверено 12/12. И, конечно, если бы их было больше, она бы вернулась еще. Хорошо, поэтому в моем решении будет использоваться VBA. Хорошо, поэтому убедитесь, что он сохранен как xlsm, иначе вы не можете запускать VBA или xlsb, но не xlsx - xlsx - это единственный файл, который не может запускать VBA. Мы нажмем Alt + F11, убедитесь, что вы используете Dual183 или другое название вашей книги. Вставьте модуль в пустой модуль, и мы собираемся вставить этот код, хорошо.
Давайте посмотрим на эту функцию GetAll, и вот идентификационный номер, который мы ищем, а затем диапазон, который мы хотим посмотреть. И мы начнем с того, что мы собираемся вернуть переменную с именем GetAll, поэтому мы начнем с того, что она равна пустому пробелу. Для каждой ячейки в моем диапазоне, если значение ячейки - это то, что мы ищем, мы возьмем GetAll = GetAll & «», а затем Cell.Offset (0 строк, 1 столбец), другими словами, значение это как раз рядом с этим идентификационным номером, потому что в VBA вот этот идентификационный номер. Если мы найдем совпадающий идентификационный номер, мы хотим перейти на 1 столбец. Теперь, что, если вы хотите перейти на 2 столбца поверх или на 3 столбца поверх, ну, тогда вы измените эти 0 строк и 1 столбец на 2. Хорошо, также проверьте, не ставим ли - мы не ставим запятую, если это первый.Итак, если переменная GetAll в настоящее время имеет значение «», мы не будем ставить запятую, хорошо?
Итак, теперь, когда у нас есть эта функция, посмотрите, насколько легко решить проблему Мэтта. Мы собираемся подойти сюда, взять его ID, Ctrl + C и вставить Ctrl + V вот так. Данные, Удалить дубликаты, нажмите ОК. Итак, есть уникальный список идентификаторов, а затем мы хотим сказать = getall и ищем это значение в запятой E2. Просматривая здесь этот диапазон, я нажимаю F4. F4 работает как обычная функция. И снова убирая вопрос Мэтта с дороги, дважды щелкните, чтобы сбить его. Это сработает.
А давай попробуем, попробуем здесь что-нибудь безумное. Давайте возьмем Фразу 1 и просто поместим их кучу, например Фразу с 1 по 10. Мы подпишем все это как 109999. Вставьте, а затем вставьте сюда. Скопируйте эту формулу вниз, отредактируйте формулу так, чтобы, конечно, она доходила до конца. Ага. И он вернет все эти фразы. Хорошо, это мое решение, VBA, небольшая функция. Майк, давай посмотрим, что у тебя есть.
Майк Гирвин: Спасибо. GetAll, это потрясающая функция VBA. Хорошо, я перейду к листу прямо здесь. Я уже преобразовал его в таблицу Excel, поэтому, надеюсь, когда мы добавим записи ниже, все будет обновлено.
Теперь первое, что я собираюсь сделать, это две части. Я мог бы сделать здесь формулу для извлечения уникального списка, но я хочу рассмотреть другой вариант: расширенный фильтр имеет опцию извлечения уникального списка, и его можно обновлять. Я собираюсь выделить только данные столбца идентификатора, перейти к расширенному фильтру, или я собираюсь использовать клавиатуру Alt, A, Q. Теперь, список фильтров на месте, никоим образом. Я хочу скопировать его в другое место. У него есть только столбец A, и потому что это таблица Excel, которая будет расширена позже. У меня нет никаких критериев, я хочу скопировать его в D1 и проверить только уникальные записи. Щелкните ОК.
Теперь я собираюсь подойти сюда, ввести Все комментарии, и я собираюсь использовать функцию, которая работает только в Excel 2016 Office 365: = Функция TEXTJOIN. Только за эту функцию стоит установить последнюю версию Excel. Это такая обычная задача, которую люди хотят делать, объединять многие вещи. Теперь наш разделитель - «,», и самое замечательное в этой функции - мы можем указать ей игнорировать пустые ячейки. Теперь я могу поставить ИСТИНА, 1 или Оставить, пропустить. Итак, я собираюсь оставить это, пропустить. И вот где нам понадобится наш текст. Мы собираемся использовать функцию ЕСЛИ, чтобы отфильтровать и получить только те элементы, которые нам нужны. Я хочу сказать, что просмотрите весь этот столбец здесь: Имя таблицы, а затем в () имя поля, кто из вас = к этой относительной ссылке на ячейку, это логический тест. Если бы я щелкнул по нему и нажал клавишу F9 для оценки,Вы могли видеть, что сейчас у нас есть только 2 ИСТИНА, Ctrl + Z, теперь я ввожу запятую и с массивом Истин и Ложь, теперь я могу дать ему элементы, которые нужно выбрать. Итак, теперь мы выберем из этого диапазона только те элементы, которые имеют значение ИСТИНА. Мы с запятой хотим поставить «» - это будет отображаться как пустая ячейка по отношению ко второму аргументу в TEXTJOIN.
Теперь я собираюсь закрыть круглые скобки, и теперь функция IF создаст эту строку True и Falses, фактические элементы из этого диапазона будут выбраны, если он увидит это True, а все остальные элементы будут иметь эту пустую ячейку. И угадайте, что? TEXTJOIN полностью проигнорирует все эти пустые ячейки и вернет только элементы, соответствующие этому идентификатору, а затем присоединится к нему с этим разделителем. Теперь это определенно формула массива, для которой требуется специальное нажатие клавиш Ctrl + Shift + Enter. Аргумент логического теста содержит нашу операцию с массивом, и этот аргумент не может правильно вычислить эту операцию с массивом, если мы не используем клавиатуру Ctrl + Shift + Enter. Теперь закрою скобки. На самом деле мы могли бы доказать 1 прямо здесь, в Тексте 1, если бы я F9 все это, мы могли бы увидеть, что получили 2 элемента, остальные пустые ячейки будут проигнорированы. Ctrl + Z. А теперь давайs введите это в ячейку с помощью Ctrl + Shift + Enter. Немедленно посмотрите на панель формул. Эти фигурные скобки говорят о том, что Excel понял и вычислил это как формулу массива. Теперь я могу дважды щелкнуть и отправить его вниз. Выглядит хорошо.
Я собираюсь перейти к последней ячейке и нажать F2, чтобы убедиться, что все диапазоны выглядят правильно. Сейчас я не хочу нажимать Enter, потому что эта формула после того, как мы переведем ее в режим редактирования, будет правильно вычисляться только при использовании Ctrl + Shift + Enter; или, поскольку мы уже ввели формулу, мы можем просто использовать клавишу Esc, чтобы вернуться к тому, что находится в ячейке, прежде чем мы переведем ее в режим редактирования.
Теперь давайте проверим это. Я собираюсь щелкнуть здесь последнюю ячейку и нажать Tab, а затем ввести новый идентификатор, Tab, Tab. Еще одна новая пластинка, Tab, и я уже вижу, что мне здесь не хватило работы. Я, мы поставим - Perfect, а затем Enter. Теперь это не будет обновляться автоматически, как если бы у нас была куча формул, по которым мы подсчитываем уникальные элементы, а затем извлекаем уникальные элементы, но это не проблема. Смотри. Мы можем обновить этот список уникальных записей, потому что мы использовали расширенный фильтр, и не имеет значения, с какой ячейки вы начинаете, потому что при вызове расширенного фильтра он запоминает диапазон извлечения и диапазоны, на которые он первоначально смотрел. Вы можете нажать «Расширенный фильтр» или использовать клавиатуру Alt + A + Q. Нам нужно выбрать Копировать в другое место, но посмотрите на это.Он полностью запомнился и расширился до A13 благодаря функции таблицы Excel. Он запомнил диапазон вытяжки. Мне нужно проверить только уникальные записи, но нажмите ОК.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
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.
Эй, я хочу поблагодарить всех за то, что заглянули. Увидимся в следующий раз на еще одном подкасте Dueling Excel от и Excel Is Fun.
Скачать файл
Загрузите образец файла здесь: Duel183.xlsm
