Може ли Excel VLOOKUP да върне всички резултати и да ги присъедини със запетая между тях?
Гледам видео
- Целта е да се обединят всички текстови отговори от VLOOKUP
- Метод на Бил: Използвайте VBA функция, наречена GetAll
- Уникален списък с помощта на Премахване на дубликати
- Метод на Майк:
- Уникален списък с помощта на разширен филтър
- Функция TEXTJOIN добавена в Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Поради функцията IF, формулата изисква Ctrl + Shift + Enter по всяко време, когато редактирате формулата
- Alt AQOR Enter ще стартира отново Разширения филтър!
Видео транскрипция
Епизод 183: Присъединете се към всички мачове на VLOOKUP
Бил Джелен: Хей, добре дошла. Време е за поредния дуелинг подкаст на Excel. Аз съм Бил Джелен от, към мен ще се присъедини Майк Гирвин за Excel Is Fun. Това е нашият епизод 183: Присъединете се към всички мачове на VLOOKUP.
(Музика)
Добре, днешният въпрос от Мат. Може ли 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 в момента е “”, тогава няма да поставим запетая, нали?
Така че сега, когато имаме тази функция тук, гледайте колко лесно е да решите проблема на Мат. Ще дойдем тук и нека вземем неговите идентификатори, Ctrl + C и ще поставим Ctrl + V така. Данни, Премахване на дубликати, щракнете върху OK. Така че има уникален списък с идентификатори и след това искаме да кажем = getall и търсим тази стойност в запетая E2. Разглеждайки този диапазон тук, ще натисна F4. F4 работи точно като обикновена функция. И отново изместваме въпроса на Мат, щракнете двукратно, за да го свалите. Ще работи.
И нека просто опитаме, нека опитаме нещо лудо тук. Нека направим фраза 1 и просто сложим куп от тях като фрази от 1 до 10. Ще подпишем всичко това на 109999. Поставете и след това поставете тук. Копирайте тази формула надолу, редактирайте формулата, така че тя да стигне чак до дъното, разбира се. Мда. И ще върне всички тези фрази. Добре, така че това е моето решение, VBA, малко функция там. Майк, да видим какво имаш.
Майк Гирвин: Благодаря ,. GetAll, това е страхотна функция на VBA. Добре, ще отида на листа точно тук. Вече го преобразувах в таблица на Excel, така че когато добавим записи по-долу, надяваме се нещата да се актуализират.
Сега първото нещо, което ще направя в две части. Тук бих могъл да направя формула за извличане на уникален списък, но искам да разгледам друга опция: Разширеният филтър има опция за извличане на уникален списък и може да бъде актуализиран. Ще подчертая само данните от колоната с идентификатори, до Разширен филтър или ще използвам клавиатурата Alt, A, Q. Сега, филтър списък на място, няма начин. Искам да го копирам на друго място. Той получи само колоната A и тъй като това е таблица на Excel, която ще се разшири по-късно. Нямам никакви критерии, искам да го копирам в D1 и да проверя само Уникални записи. Щракнете върху OK.
Сега ще дойда тук, Всички коментари влизат и ще използвам функция, която работи само в Excel 2016 Office 365: = TEXTJOIN функция. Само тази функция си заслужава да получите най-новата версия на Excel. Това е толкова често срещана задача, която хората искат да свършат, да обединят много неща заедно. Сега нашият разделител в „,“ и най-хубавото в тази функция е, че можем да ѝ кажем да игнорира празни клетки. Сега мога да поставя TRUE, 1 или да го оставя, да го пропусна. И така, ще го оставя, пропусна го. И тук имаме нужда от нашия текст. Ще използваме функцията IF, за да филтрираме и да получим само елементите, които искаме. Ще кажа, че прегледайте цялата тази колона тук: Име на таблицата и след това в () Името на полето, някой от вас = ли е към тази относителна препратка към клетка, това е логичният тест. Ако щях да щракна това и да натисна клавиша F9 за оценка,в момента можете да видите, че имаме само 2 ИСТИНИ, Ctrl + Z, сега въвеждам запетая и с масива от истини и фалши, сега мога да му дам елементите, които да изберете. Така че сега ще изберем само елементите, които имат ИСТИНА тук от този диапазон. Запетая и искам да се уверя, че поставям “” - това ще се покаже като празна клетка по отношение на втория аргумент в TEXTJOIN.
Сега ще затворя скоби и сега функцията IF ще създаде този низ от истини и фалши, действителните елементи от този диапазон ще бъдат взети, ако го види True и всички останали елементи ще имат тази празна клетка. И познай какво? TEXTJOIN напълно ще игнорира всички тези празни клетки и ще върне само елементите, които съответстват на този ID, и след това ще се присъедини към него с този разделител. Сега това определено е формула за масив, която изисква специалното натискане на клавиш Ctrol + Shift + Enter. Аргументът за логически тест съдържа нашата операция Array и този аргумент не може да изчисли правилно тази операция Array, освен ако не използваме клавиатурата Ctrl + Shift + Enter. Сега ще затворя скоби. Всъщност бихме могли да докажем 1 точно тук в Текст 1, ако F9 всичко това, можем да видим, че получаваме 2-те елемента, останалите празни клетки ще бъдат игнорирани. Ctrl + Z. Сега некаs въведете това в клетката с Ctrl + Shift + Enter. Веднага потърсете лентата за формули. Тези къдрави скоби са Excel, който ви казва, че е разбрал и изчислил това като формула на масив. Сега мога да щракна двукратно и да го изпратя. Това изглежда добре.
Отивам до последната клетка и натискам F2, за да проверя дали всички диапазони изглеждат правилно. Сега това, което не искам да правя, е, че не искам да натискам Enter, защото тази формула, след като я поставим в режим на редактиране, ще изчисли правилно само ако използваме Ctrl + Shift + Enter; или, тъй като вече сме въвели формулата, можем просто да използваме клавиша Esc, за да се върнем обратно към каквото и да е в клетката, преди да го поставим в режим на редактиране.
Сега, нека тестваме това. Щраквам в последната клетка тук долу и натискам Tab и след това въвеждам нов ID, Tab, Tab. Друг нов запис, Tab, и вече виждам, че нямах достатъчно работа тук. Аз съм, ще сложим - Perfect и след това Enter. Сега това няма да се актуализира автоматично, ако имаме куп формули, които броим уникални елементи и след това извличаме уникални елементи, но няма проблем. Гледай това. Можем да актуализираме този списък с уникални записи, защото използвахме разширен филтър и няма значение от коя клетка започвате, защото когато се извика разширен филтър, той запаметява диапазона на извличане и диапазоните, които първоначално е гледал. Можете да кликнете върху Разширен филтър или да използвате клавиатурата Alt + A + Q. Трябва да изберем Копиране на друго място, но погледнете това.Той напълно се запомни и разшири до A13 поради функцията на Excel Table. Запомни диапазона на екстракта. Трябва да проверя само уникални записи, но щракнете върху OK.
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.
О, хей, искам да благодаря на всички, че се отбиха. Ще се видим следващия път за поредния подкаст на дуел на Excel от и Excel е забавно.
Свали файл
Изтеглете примерния файл от тук: Duel183.xlsm
