Poate Excel VLOOKUP să returneze toate rezultatele și să le alăture cu o virgulă între ele?
Urmăriți videoclipul
- Scopul este de a concatena toate răspunsurile text dintr-o VLOOKUP
- Metoda lui Bill: utilizați o funcție VBA numită GetAll
- Listă unică utilizând Eliminarea duplicatelor
- Metoda lui Mike:
- Listă unică folosind Advanced Filter
- Funcția TEXTJOIN adăugată în Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Datorită funcției IF, formula necesită Ctrl + Shift + Enter de fiecare dată când editați formula
- Alt AQOR Enter va rula din nou filtrul avansat!
Transcriere video
Episodul 183: Alătură-te tuturor meciurilor VLOOKUP
Bill Jelen: Hei, bine ai revenit. Este timpul pentru un alt podcast Dueling Excel. Sunt Bill Jelen din, mi se va alătura Mike Girvin pentru Excel Is Fun. Acesta este episodul nostru 183: Alăturați-vă tuturor meciurilor VLOOKUP.
(Muzică)
Bine, întrebarea de astăzi de la Matt. Poate VLOOKUP să returneze toate rezultatele și să le unească împreună cu un spațiu virgulă între fiecare. De exemplu, 109876 care sunt aceste două aici, poate întoarce spațiu virgulă Low Oil Verificat la 12/12. Și, desigur, dacă ar fi mai multe, s-ar întoarce mai mult. Bine, deci soluția mea aici va fi utilizarea unor VBA. Bine, deci asigurați-vă că este salvat ca xlsm sau că nu puteți rula VBA sau xlsb, dar nu xlsx - xlsx este singurul fișier care nu poate rula VBA. Vom apăsa Alt + F11, ne asigurăm că sunteți pe Dual183 sau oricare ar fi numele registrului dvs. de lucru. Introduceți modulul în modulul gol și vom lipi acest cod, bine.
Să aruncăm o privire la această funcție GetAll și iată numărul de identificare pe care îl căutăm și apoi intervalul pe care dorim să îl căutăm. Și începem, vom returna o variabilă numită GetAll, deci începem cu faptul că este egal cu golul gol. Pentru fiecare celulă din intervalul meu, dacă valoarea celulei este ceea ce căutăm, vom lua GetAll = GetAll & "” și apoi Cell.Offset (0 rânduri, 1 coloană), cu alte cuvinte valoarea asta este chiar lângă acel număr de identificare, deoarece înapoi în VBA, iată numărul de identificare. Dacă găsim numărul de identificare corespunzător, dorim să trecem cu 1 coloană. Acum, ce se întâmplă dacă doriți să treceți 2 coloane sau 3 coloane peste, atunci schimbați aceste 0 rânduri și 1 coloană pentru a fi 2. Bine, verificați și dacă nu - nu punem spațiu virgulă primul.Deci, dacă variabila GetAll este în prezent „”, atunci nu vom pune spațiul virgulă, bine?
Acum, că avem această funcție aici, urmăriți cât de ușor este să rezolvați problema lui Matt. Vom veni aici și să îi luăm ID-urile, Ctrl + C și să lipim Ctrl + V așa. Date, Eliminați duplicatele, faceți clic pe OK. Deci, există o listă unică de ID-uri și apoi vrem să spunem = getall și căutăm acea valoare în virgula E2. Privind prin acest interval de aici, voi apăsa F4. F4 funcționează la fel ca o funcție obișnuită. Și mutând din nou întrebarea lui Matt, faceți dublu clic pentru a trage asta. O sa mearga.
Și să încercăm, să încercăm ceva nebunesc aici. Să facem o frază 1 și să punem o grămadă de ele, precum fraza 1 până la 10. Vom semna toate acestea la 109999. Lipiți și apoi lipiți aici. Copiați acea formulă în jos, editați formula pentru a merge până la capăt, desigur. Da. Și va returna toate aceste fraze. Bine, deci asta este soluția mea, VBA, o mică funcție acolo. Mike, hai să vedem ce ai.
Mike Girvin: Mulțumesc. GetAll, aceasta este o funcție VBA minunată. În regulă, voi merge la foaie chiar aici. L-am convertit deja într-un tabel Excel, astfel încât atunci când adăugăm înregistrări de mai jos, sperăm că lucrurile se vor actualiza.
Acum, primul lucru pe care îl voi face în două părți. Aș putea face o formulă aici pentru extragerea unei liste unice, dar vreau să mă uit la o altă opțiune: Filtrul avansat are o opțiune de extragere a listei unice și poate fi actualizat. Voi evidenția doar datele din coloana ID, trecând la Advanced Filter sau voi folosi tastatura Alt, A, Q. Acum, lista de filtre în loc, în niciun caz. Vreau să îl copiez în altă locație. A primit doar coloana A și pentru că este un tabel Excel care se va extinde mai târziu. Nu am niciun criteriu, vreau să îl copiez în D1 și să verific numai înregistrările unice. Faceți clic pe OK.
Acum, voi veni aici, toate comentariile intră și voi folosi o funcție care funcționează numai în Excel 2016 Office 365: = funcția TEXTJOIN. Numai această funcție merită să obțineți cea mai recentă versiune de Excel. Aceasta este o sarcină atât de comună pe care oamenii vor să o facă, să unească multe lucruri împreună. Acum, delimitatorul nostru în „,” și cel mai bun lucru despre această funcție este că îi putem spune să ignorăm celulele goale. Acum, pot pune TRUE, 1 sau Leave it, Omite-l. Deci, am să-l las, omite-l. Și aici avem nevoie de textul nostru. Vom folosi funcția IF pentru a filtra și a obține doar articolele dorite. Voi spune că uitați-vă aici la întreaga coloană: Nume tabel și apoi în () numele câmpului, sunteți oricare dintre voi = la această referință relativă a celulei, acesta este testul logic. Dacă ar fi să fac clic pe acest lucru și să apăs tasta F9 pentru a evalua,ați putut vedea chiar acum avem doar 2 ADEVĂRURI, Ctrl + Z acum scriu o virgulă și cu matricea de Adevăruri și Falsuri, acum îi pot da articolele de ales. Deci, acum, vom alege doar articolele care au un ADEVĂRAT aici din această gamă. Eu și comă vrem să ne asigurăm că punem „” - care va apărea ca o celulă goală în ceea ce privește al doilea argument din TEXTJOIN.
Acum, voi închide paranteze și acum funcția IF va crea acel șir de Verități și Falsuri, elementele reale din acest interval vor fi preluate dacă îl vede adevărat și toate celelalte elemente vor avea acea celulă goală. Si ghici ce? TEXTJOIN va ignora în totalitate toate acele celule goale și va returna doar articolele care se potrivesc cu acest ID, apoi îl vor alătura cu acel delimitator. Acum aceasta este cu siguranță o formulă Array care necesită tastarea specială Ctrol + Shift + Enter. Argumentul de test logic deține operațiunea noastră Array și acel argument nu poate calcula corect această operație Array decât dacă folosim tastatura Ctrl + Shift + Enter. Acum voi închide paranteze. De fapt, am putea dovedi 1 chiar aici în Textul 1 dacă am F9 toate acestea, am putea vedea că obținem cele 2 elemente, restul acelor celule goale vor fi ignorate. Ctrl + Z. Acum lasa'introduceți acest lucru în celulă cu Ctrl + Shift + Enter. Uită-te imediat la Formula Bar. Aceste paranteze cretate sunt Excel, spunându-vă că a înțeles și a calculat acest lucru ca o formulă Array. Acum pot face dublu clic și îl pot trimite în jos. Arată bine.
Mă duc la ultima celulă și apăs F2 pentru a verifica dacă toate intervalele arată corect. Acum, ceea ce nu vreau să fac este că nu vreau să apăs pe Enter, deoarece acea formulă după ce o punem în Modul de editare se va calcula corect numai dacă folosim Ctrl + Shift + Enter; sau, pentru că am introdus deja formula, putem folosi doar tasta Esc pentru a reveni la orice se află în celulă înainte de ao pune în Mod Editare.
Acum, să testăm acest lucru. Voi face clic în ultima celulă de aici jos și voi apăsa pe Tab și apoi tastați un nou ID, Tab, Tab. Un alt disc nou, Tab, și văd deja că nu am avut suficientă muncă aici. Sunt, vom pune - Perfect și apoi Enter. Acum, acest lucru nu se va actualiza automat, cum ar fi dacă avem o grămadă de formule pe care le numără elemente unice și apoi extragem elemente unice, dar nu există nicio problemă. Uita-te la asta. Putem actualiza această listă de înregistrări unice, deoarece am folosit filtrul avansat și nu contează din ce celulă începeți, deoarece atunci când este invocat filtrul avansat, memorează intervalul de extragere și intervalele la care se uita inițial. Puteți face clic pe Advanced Filter sau puteți utiliza tastatura Alt + A + Q. Trebuie să selectăm Copiați într-o altă locație, dar uitați-vă la asta.S-a amintit total și s-a extins la A13 datorită caracteristicii Tabel Excel. Își amintea gama de extracte. Trebuie să verific numai înregistrările unice, dar să fac clic pe OK.
Acum, trebuie să vin și să copiez această formulă. Și iată-te, folosind Advanced Filter și uimitoarea funcție TEXTJOIN cu, în operațiunea Array, pentru a obține doar elementele care se potrivesc. În regulă, aruncă-te înapoi.
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.
Oh, hei, vreau să mulțumesc tuturor că au trecut pe aici. Ne vedem data viitoare pentru un alt podcast Dueling Excel de la și Excel Is Fun.
Descărcare fișier
Descărcați exemplarul de fișier aici: Duel183.xlsm
