Kan Excel VLOOKUP returnere alle resultater og sammenføje dem med et komma imellem?
Se video
- Målet er at sammenkæde alle tekstsvarene fra en VLOOKUP
- Bills metode: Brug en VBA-funktion kaldet GetAll
- Unik liste ved hjælp af Fjern duplikater
- Mike's metode:
- Unik liste ved hjælp af avanceret filter
- TEXTJOIN-funktion tilføjet i Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- På grund af IF-funktionen kræver formlen Ctrl + Shift + Enter, hver gang du redigerer formlen
- Alt AQOR Enter kører det avancerede filter igen!
Videoudskrift
Afsnit 183: Deltag i alle VLOOKUP-kampe
Bill Jelen: Hej, velkommen tilbage. Det er tid til endnu en Dueling Excel Podcast. Jeg er Bill Jelen fra, jeg får følgeskab af Mike Girvin til Excel er sjovt. Dette er vores afsnit 183: Deltag i alle VLOOKUP-kampe.
(Musik)
Okay, dagens spørgsmål fra Matt. Kan VLOOKUP returnere alle resultater og slutte dem sammen med et komma mellem hver. For eksempel kan 109876, som er disse to her, returnere kommatioden med lavt olie Kontrolleret den 12/12. Og selvfølgelig, hvis der var flere, ville det vende tilbage mere. Okay, så min løsning her vil bruge nogle VBA. Okay, så sørg for at det er gemt som xlsm, ellers kan du ikke køre VBA eller xlsb, men ikke xlsx - xlsx er den ene fil, der ikke kan køre VBA. Vi trykker på Alt + F11, sørg for at du er på Dual183 eller hvad navnet på din projektmappe er. Indsæt modul i det tomme modul, og vi indsætter denne kode, okay.
Lad os se på denne funktion GetAll, og her er det ID-nummer, vi leder efter, og derefter det interval, som vi vil se. Og vi starter, vi returnerer en variabel kaldet GetAll, så vi starter med at være lig med blank blank. For hver celle i mit område, hvis celleværdien er det, vi leder efter, tager vi GetAll = GetAll & “” og derefter Cell.Offset (0 rækker, 1 kolonne), med andre ord værdien det er lige ved siden af det ID-nummer, for tilbage i VBA er her ID-nummeret. Hvis vi finder det matchende ID-nummer, vil vi gå 1 kolonne over. Hvad nu hvis du ville gå 2 kolonner over eller 3 kolonner over, ja, så ændrer du denne 0 række og 1 kolonne til at være en 2. Okay, kontroller også for at se om - vi lægger ikke et komma mellemrum hvis dette er den første.Så hvis GetAll-variablen i øjeblikket er "", lægger vi ikke komma-rummet, okay?
Så nu hvor vi har denne funktion her, skal du se, hvor let det er at løse Matts problem. Vi kommer herover og lad os tage hans ID'er, Ctrl + C og indsætte Ctrl + V sådan. Data, Fjern duplikater, klik på OK. Så der er en unik liste over id'er, og så vil vi sige = getall, og vi leder efter den værdi i E2-komma. Når jeg ser igennem dette interval her, skal jeg trykke på F4. F4 fungerer ligesom en almindelig funktion. Og igen flytter Matt's spørgsmål ud af vejen, dobbeltklik for at skyde det ned. Det fungerer.
Og lad os bare prøve, lad os prøve noget skørt her. Lad os lave en sætning 1 og bare sætte en flok af dem som sætning 1 til 10. Vi underskriver alle disse til 109999. Indsæt og indsæt derefter herover. Kopier formlen ned, rediger formlen, så den går selvfølgelig helt til bunden. Jep. Og det vil returnere alle disse sætninger. Okay, så det er min løsning, VBA, en lille funktion der. Mike, lad os se, hvad du har.
Mike Girvin: Tak. GetAll, det er en fantastisk VBA-funktion. Okay, jeg går over til arket lige her. Jeg har allerede konverteret det til en Excel-tabel, så når vi tilføjer poster nedenfor, forhåbentlig ting opdateres.
Nu er den første ting, jeg skal gøre dette i to dele. Jeg kunne lave en formel her for at udtrække en unik liste, men jeg vil se på en anden mulighed: Avanceret filter har en ekstraordinær listeindstilling, og den kan opdateres. Jeg vil kun fremhæve ID-kolonnedataene over til Advanced Filter, eller jeg vil bruge tastaturet Alt, A, Q. Nu, Filterliste på plads, på ingen måde. Jeg vil kopiere det til et andet sted. Det fik bare A-kolonnen, og fordi det er en Excel-tabel, der udvides senere. Jeg har ingen kriterier, jeg vil kopiere det til D1 og kun kontrollere unikke poster. Klik på OK.
Nu skal jeg komme herover, alle kommentarer kommer ind, og jeg vil bruge en funktion, der kun fungerer i Excel 2016 Office 365: = TEXTJOIN-funktion. Denne funktion alene er værd at få den nyeste version af Excel. Dette er en så almindelig opgave, at folk ønsker at gøre, slutte mange ting sammen. Nu er vores afgrænser i “,”, og den store ting ved denne funktion er, at vi kan fortælle det at ignorere tomme celler. Nu kan jeg sætte SAND, 1 eller Lad det være, udelad det. Så jeg vil lade det være, udelade det. Og her har vi brug for vores tekst. Vi bruger IF-funktionen til at filtrere ud og få lige de ting, vi ønsker. Jeg vil sige kig igennem hele denne kolonne her: Tabelnavn og derefter i () feltnavnet, er nogen af jer = til denne relative cellereference, det er den logiske test. Hvis jeg skulle klikke på dette og trykke på F9-tasten for at evaluere,du kunne se lige nu, vi har kun 2 TRUES, Ctrl + Z nu skriver jeg et komma og med arrayet af Trues og Falses, nu kan jeg give det de emner, der skal udvælges. Så nu vælger vi kun de varer, der har en SAND her fra dette interval. Komma og jeg vil sørge for at sætte “” - der vises som en tom celle med hensyn til det andet argument i TEXTJOIN.
Nu skal jeg lukke parentes, og nu vil IF-funktionen skabe den streng af Trues og Falses, de faktiske genstande fra dette interval vil blive afhentet, hvis den ser det sandt, og alle de andre elementer vil have den tomme celle. Og gæt hvad? TEXTJOIN ignorerer fuldstændigt alle disse tomme celler og returnerer kun de emner, der matcher dette ID, og slutter sig derefter til den med denne afgrænser. Nu er dette bestemt en Array-formel, der kræver det specielle tastetryk Ctrol + Shift + Enter. Det logiske testargument holder vores Array-operation, og dette argument kan ikke beregne denne Array-operation korrekt, medmindre vi bruger tastaturet Ctrl + Shift + Enter. Nu skal jeg lukke parenteser. Faktisk kunne vi bevise 1 lige her i Tekst 1, hvis jeg F9 alt dette, kunne vi se, at vi fik de 2 emner, resten af disse tomme celler vil blive ignoreret. Ctrl + Z. Lad os nus indtast dette i cellen med Ctrl + Skift + Enter. Se straks op til Formula Bar. Disse krøllede parenteser er Excel, der fortæller dig, at det forstås og beregnes som en Array-formel. Nu kan jeg dobbeltklikke og sende den ned. Det ser godt ud.
Jeg går til den sidste celle og rammer F2 for at kontrollere, at alle områder ser korrekt ud. Det, jeg ikke vil gøre, er nu, at jeg ikke vil trykke på Enter, fordi den formel, efter at vi har sat den i redigeringstilstand, kun beregner korrekt, hvis vi bruger Ctrl + Shift + Enter; eller fordi vi allerede har indtastet formlen, kan vi bare bruge Esc-tasten til at vende tilbage til hvad der er i cellen, før vi sætter den i redigeringstilstand.
Lad os nu teste dette. Jeg skal klikke i den sidste celle hernede og trykke på Tab og derefter skrive et nyt ID, Tab, Tab. En anden ny post, Tab, og jeg kan allerede se, at jeg ikke havde nok arbejde her. Det er jeg, vi skal sætte - Perfekt og derefter Enter. Nu opdateres dette ikke automatisk, som om vi har en masse formler, som vi tæller unikke emner og derefter udpakker unikke emner, men ikke noget problem. Se dette. Vi kan opdatere denne liste over unikke poster, fordi vi brugte avanceret filter, og det betyder ikke noget, hvilken celle du starter fra, enten fordi når avanceret filter påberåbes, husker det ekstraktområdet og de områder, det oprindeligt så på. Du kan klikke på Advanced Filter eller bruge tastaturet Alt + A + Q. Vi er nødt til at vælge Kopier til en anden placering, men se på det.Det huskedes fuldstændigt og blev udvidet til A13 på grund af Excel Table-funktionen. Det huskede ekstraktområdet. Jeg skal kun kontrollere unikke poster, men klik på OK.
Nu er jeg nødt til at komme over og kopiere denne formel ned. Og der går du ved hjælp af avanceret filter og den fantastiske TEXTJOIN-funktion med i Array-operation for at få netop de emner, der matcher. Okay, kast tilbage til.
Bill Jelen: Hej, Mike, det er fantastisk. Okay, indpakning af denne episode. Jeg brugte VBA-funktionen kaldet GetAll, og min unikke liste blev oprettet af Fjern duplikater, hvilket er langt lettere end Advanced Filter, men problemet er, at det er en engangs ting. Det husker ikke de tidligere indstillinger. Mike oprettede sin unikke liste ved hjælp af Advanced Filter, hvilket betyder, at han senere kunne gentage det Advanced Filter uden at angive inputområdet og ekstraktområdet igen. Og så tilføjede TEXTJOIN, en smuk ny funktion, en Office 365. Mike siger, at det alene er en grund til at få det nyeste Office. Jeg sagde, at TEXTJOIN ville ændre livet. TEXTJOIN er fantastisk, fordi det kan håndtere 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.
Åh hej, jeg vil gerne takke alle for at komme forbi. Vi ses næste gang til en anden Dueling Excel podcast fra og Excel er sjovt.
Download fil
Download prøvefilen her: Duel183.xlsm
