Да ли Екцел ВЛООКУП може вратити све резултате и придружити им се зарезом између?
Погледајте видео
- Циљ је повезати све текстуалне одговоре из ВЛООКУП-а
- Биллов метод: Користите ВБА функцију која се зове ГетАлл
- Јединствена листа помоћу Уклањање дупликата
- Мајков метод:
- Јединствена листа помоћу напредног филтера
- У Оффице 365 додата функција ТЕКСТЈОИН
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Због функције ИФ, формула захтева Цтрл + Схифт + Ентер сваки пут када уређујете формулу
- Алт АКОР Ентер ће поново покренути напредни филтер!
Видео транскрипт
Епизода 183: Придружите се свим ВЛООКУП мечевима
Билл Јелен: Хеј, добродошао назад. Време је за још један Дуел Екцел подцаст. Ја сам Билл Јелен из, придружит ће ми се Мике Гирвин за Екцел Ис Фун. Ово је наша Епизода 183: Придружите се свим ВЛООКУП мечевима.
(Музика)
У реду, данашње питање од Матта. Може ли ВЛООКУП вратити све резултате и придружити их заједно са размаком зареза између њих. На пример, 109876 која су ово двоје овде, може ли да врати простор са зарезом са ниским уљем Проверено 12/12. И наравно да их је било више, вратило би се више. Добро, овде ће моје решење бити коришћење неке ВБА. Па, уверите се да је сачуван као клсм или да не можете покренути ВБА или клсб, али не и клск - клск је она датотека која не може да покреће ВБА. Притисните тастере Алт + Ф11, уверите се да користите Дуал183 или како год да се зове ваша радна свеска. Уметните модул у празан модул и ми ћемо залепити овај код, у реду.
Погледајмо ову функцију ГетАлл, а овде је ИД број који тражимо, а затим опсег који желимо да тражимо. И започињемо, враћаћемо променљиву која се зове ГетАлл, па започињемо са тим да је једнако празном празном. За сваку ћелију у мом опсегу, ако је вредност ћелије оно што тражимо, узећемо ГетАлл = ГетАлл & “”, а затим Целл.Оффсет (0 редова, 1 колона), другим речима вредност то је само поред тог матичног броја, јер је у ВБА овде ИД број. Ако пронађемо одговарајући ИД број, желимо да пређемо за 1 колону. Е, шта ако желите да пређете 2 колоне или 3 колоне, па онда промените 0 редова и 1 колону у 2. У реду, такође проверите да ли ставимо размак зарезом ако је ово Први.Дакле, ако је променљива ГетАлл тренутно „“, тада нећемо стављати размак зарезима, у реду?
Дакле, сада када имамо ову функцију, припазите како је ово лако решити Маттов проблем. Доћи ћемо овде и узмимо његове личне карте, Цтрл + Ц и залепимо Цтрл + В тако. Дата, Ремове Дуплицатес, кликните ОК. Дакле, постоји јединствена листа ИД-ова, а затим желимо да кажемо = геталл и тражимо ту вредност у Е2 запети. Гледајући кроз овај опсег овде, притисните Ф4. Ф4 ради баш као и редовна функција. И поново померите Матт-ово питање с пута, двапут кликните да бисте га оборили. Успеће.
И хајде само да покушамо, пробајмо нешто лудо овде. Направимо фразу 1 и ставимо гомилу њих попут фраза од 1 до 10. Све ћемо то потписати на 109999. Налепите, а затим овде залепите. Копирајте ту формулу, уредите формулу тако да иде све до дна, наравно. Аха. И вратиће све те фразе. У реду, то је моје решење, ВБА, мала функција тамо. Мике, да видимо шта имаш.
Мике Гирвин: Хвала. ГетАлл, то је сјајна ВБА функција. У реду, прећи ћу на лист овде. Већ сам га претворио у Екцел табелу, па ће се надамо да ће се ствари додати када додамо записе у наставку.
Сада ћу прво да урадим ово из два дела. Овде бих могао да направим формулу за издвајање јединствене листе, али желим да погледам другу опцију: Напредни филтер има опцију извлачења јединствене листе и може се ажурирати. Истакнућу само податке из колоне ИД, па све до Напредног филтера или ћу користити тастатуру Алт, А, К. Сада, филтер листа на месту, нема шансе. Желим да га копирам на друго место. Добио је само колону А и зато што је то Екцел табела која ће се касније проширити. Немам ниједан критеријум, желим да га копирам у Д1 и да проверим само Јединствене записе. Кликните ОК.
Сада ћу доћи овде, ући Сви коментари и користићу функцију која ради само у програму Екцел 2016 Оффице 365: = ТЕКСТЈОИН функција. Само ова функција вреди набавити најновију верзију програма Екцел. Ово је тако уобичајен задатак који људи желе да ураде, да споје многе ствари. Сада је наш граничник у „,“, и сјајна ствар у вези са овом функцијом је то што јој можемо рећи да занемарује празне ћелије. Сада могу да ставим ТРУЕ, 1 или Леаве ит, Имит. Дакле, Оставит ћу то, изоставити. И ево где нам треба наш текст. Користићемо функцију ИФ да бисмо филтрирали и добили само ставке које желимо. Рећи ћу да погледате целу ову колону овде: Име табеле, а затим () име поља, да ли је неко од вас = на ову релативну референцу ћелије, то је логички тест. Ако бих кликнуо на ово и притиснуо тастер Ф9 да бих проценио,могли сте да видите тренутно имамо само 2 ИСТИНЕ, Цтрл + З, сада куцам зарез и помоћу низа тачних и нетачних, сада могу да му дам предмете да их одабере. Дакле, сада ћемо из овог распона одабрати само предмете који овде имају ИСТИНУ. Зарез и желим да обавезно ставимо „“ - то ће се приказати као празна ћелија у односу на други аргумент у ТЕКСТЈОИН.
Now, I'm going to close parenthesis and now the IF function will create that string of Trues and Falses, the actual items from this range will be picked up if it sees it True and all the other items will have that empty cell. And guess what? TEXTJOIN will totally ignore all of those empty cells and return just the items that match this ID, and then join it with that delimiter. Now this is definitely an Array formula that requires the special keystroke Ctrol+Shift+Enter. The logical test argument holds our Array operation and that argument cannot calculate this Array operation correctly unless we use the keyboard Ctrl+Shift+Enter. Now I'm going to close parentheses. Actually we could prove 1 right here in Text 1 if I F9 all this, we could see we get the 2 items, the rest of those empty cells will be ignored. Ctrl+Z. Now, let's enter this into the cell with Ctrl+Shift+Enter. Immediately look up to the Formula Bar. Those curly brackets are Excel telling you it understood and calculated this as an Array formula. Now I can double-click and send it down. That is looking good.
I'm going to go to the last cell and hit F2 to verify that all the ranges are looking correctly. Now what I don't want to do is I don't want to hit Enter because that formula after we put it in Edit Mode will only calculate correctly if we use Ctrl+Shift+Enter; or, because we already entered the formula, we can just use the Esc key to revert back to whatever's in the cell before we put it in Edit Mode.
Now, let's test this. I'm going to click in the last cell down here and hit Tab and then type a new ID, Tab, Tab. Another new record, Tab, and I can already see I didn't have enough work here. I am, we're going to put - Perfect and then Enter. Now, this isn't going to automatically update like if we have a bunch of formulas that we’re counting unique items and then extracting unique items, but no problem. Watch this. We can update this list of unique records because we used Advanced Filter and it doesn't matter what cell you start from either because when Advanced Filter is invoked, it memorizes the extract range and the ranges it was originally looking at. You can click on Advanced Filter or use the keyboard Alt+A+Q. We do have to select Copy to another location, but look at that. It totally remembered and expanded to A13 because of the Excel Table feature. It remembered the extract range. I do have to check Unique records only but click 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.
О, хеј, желим да захвалим свима што су свратили. Видимо се следећи пут за још један Дуцаст Екцел подцаст од и Екцел Ис Фун.
Скини докуменат
Преузмите датотеку узорка овде: Дуел183.клсм
