Kan Excel VLOOKUP returnere alle resultatene og bli med dem med et komma mellom?
Se på video
- Målet er å sammenkoble alle tekstsvarene fra en VLOOKUP
- Bills metode: Bruk en VBA-funksjon kalt GetAll
- Unik liste ved hjelp av Fjern duplikater
- Mike's metode:
- Unik liste ved hjelp av avansert filter
- TEXTJOIN-funksjon lagt til i Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- På grunn av IF-funksjonen krever formelen Ctrl + Shift + Enter når du redigerer formelen
- Alt AQOR Enter kjører det avanserte filteret på nytt!
Videoutskrift
Episode 183: Delta i alle VLOOKUP-kamper
Bill Jelen: Hei, velkommen tilbake. Det er på tide med nok en Dueling Excel Podcast. Jeg er Bill Jelen fra, jeg får følge av Mike Girvin for Excel er gøy. Dette er vår episode 183: Bli med på alle VLOOKUP-kamper.
(Musikk)
Greit, dagens spørsmål fra Matt. Kan VLOOKUP returnere alle resultatene og slå dem sammen med et komma mellom hver. For eksempel, 109876 som er disse to her, kan den returnere lavoljekommarom Kontrollert 12/12. Og selvfølgelig, hvis det var flere, ville det returnere mer. Ok, så løsningen min her kommer til å bruke noen VBA. OK, så sørg for at den er lagret som xlsm, eller at du ikke kan kjøre VBA eller xlsb, men ikke xlsx - xlsx er den filen som ikke kan kjøre VBA. Vi trykker på Alt + F11, sørg for at du er på Dual183 eller hva navnet på arbeidsboken din er. Sett inn modulen i den tomme modulen, og vi skal lime inn denne koden, ok.
La oss ta en titt på denne funksjonen GetAll, og her er ID-nummeret vi leter etter, og deretter området vi ønsker å se. Og vi begynner, vi skal returnere en variabel som heter GetAll, så vi begynner med at det er lik blank blank. For hver celle i mitt område, hvis celleverdien er det vi leter etter, skal vi ta GetAll = GetAll & "" og deretter Cell.Offset (0 rader, 1 kolonne), med andre ord verdien det er bare ved siden av ID-nummeret, for tilbake i VBA, her er ID-nummeret. Hvis vi finner samsvarende ID-nummer, vil vi gå 1 kolonne over. Nå, hva om du ville ha 2 kolonner over eller 3 kolonner over, vel, så endrer du denne 0 raden og 1 kolonnen til å være en 2. OK, sjekk også for å se om - vi legger ikke komma mellomrom hvis dette er den første.Så hvis GetAll-variabelen for øyeblikket er "", vil vi ikke sette komma-plass, ok?
Så nå som vi har denne funksjonen her, se hvor enkelt dette er å løse Matts problem. Vi kommer hit og la oss ta ID-ene hans, Ctrl + C og lime inn Ctrl + V slik. Data, Fjern duplikater, klikk OK. Så det er en unik liste over ID-er, og så vil vi si = getall, og vi ser etter den verdien i E2-komma. Når jeg ser gjennom dette området her, trykker jeg på F4. F4 fungerer akkurat som en vanlig funksjon. Og igjen, flytt Matt sitt spørsmål ut av veien, dobbeltklikk for å skyte det ned. Det vil fungere.
Og la oss bare prøve, la oss prøve noe gal her. La oss gjøre en setning 1 og bare legge en haug med dem som setning 1 til 10. Vi signerer alle disse til 109999. Lim inn, og lim deretter inn her. Kopier formelen ned, rediger formelen så den går helt til bunnen, selvfølgelig. Jepp. Og det vil returnere alle disse setningene. Ok, så det er løsningen min, VBA, en liten funksjon der. Mike, la oss se hva du har.
Mike Girvin: Takk. GetAll, det er en fantastisk VBA-funksjon. Greit, jeg skal gå over til arket akkurat her. Jeg har allerede konvertert den til en Excel-tabell, slik at når vi legger til poster nedenfor, forhåpentligvis vil ting oppdateres.
Nå det første jeg skal gjøre dette i to deler. Jeg kan gjøre en formel her for å trekke ut en unik liste, men jeg vil se på et annet alternativ: Avansert filter har et ekstrakt unikt listealternativ, og det kan oppdateres. Jeg skal bare markere ID-kolonnedataene, over til Advanced Filter, eller jeg skal bruke tastaturet Alt, A, Q. Nå, Filterliste på plass, på ingen måte. Jeg vil kopiere den til et annet sted. Den fikk bare A-kolonnen, og fordi den er en Excel-tabell som vil utvides senere. Jeg har ingen kriterier, jeg vil kopiere den til D1 og bare sjekke unike poster. Klikk OK.
Nå skal jeg komme hit, Alle kommentarer kommer inn, og jeg skal bruke en funksjon som bare fungerer i Excel 2016 Office 365: = TEXTJOIN-funksjon. Denne funksjonen alene er verdt å få den nyeste versjonen av Excel. Dette er en så vanlig oppgave at folk vil gjøre, bli med mange ting sammen. Nå er vår skillelinje i “,”, og det som er bra med denne funksjonen, at vi kan fortelle den å ignorere tomme celler. Nå kan jeg sette sant, 1 eller la det være, utelat det. Så jeg skal la det være, utelat det. Og her trenger vi teksten vår. Vi skal bruke IF-funksjonen til å filtrere ut og få akkurat de elementene vi ønsker. Jeg skal si se gjennom hele kolonnen her: Tabellnavn og deretter i () feltnavnet, er noen av dere = til denne relative cellehenvisningen, det er den logiske testen. Hvis jeg klikker på dette og trykker på F9-tasten for å evaluere,du kunne se akkurat nå, vi har bare 2 TRUES, Ctrl + Z, nå skriver jeg inn komma, og med en rekke Trues og Falses, nå kan jeg gi den elementene å plukke ut. Så nå velger vi bare elementene som har SANN her fra dette området. Comma og jeg vil sørge for å sette "" - det vil vises som en tom celle i forhold til det andre argumentet i TEXTJOIN.
Nå skal jeg lukke parentes, og nå vil IF-funksjonen skape den strengen av sannheter og falske ting, de faktiske elementene fra dette området blir plukket opp hvis den ser det sant, og alle de andre elementene vil ha den tomme cellen. Og gjett hva? TEXTJOIN vil fullstendig ignorere alle de tomme cellene og returnere bare elementene som samsvarer med denne ID-en, og deretter bli med den med den avgrenseren. Nå er dette definitivt en matriseformel som krever det spesielle tastetrykket Ctrol + Shift + Enter. Det logiske testargumentet holder Array-operasjonen vår, og argumentet kan ikke beregne denne Array-operasjonen riktig med mindre vi bruker tastaturet Ctrl + Shift + Enter. Nå skal jeg lukke parentes. Egentlig kunne vi bevise 1 her i Tekst 1 hvis jeg F9 alt dette, vi kunne se at vi fikk de to elementene, resten av de tomme cellene vil bli ignorert. Ctrl + Z. La oss nås skriv dette inn i cellen med Ctrl + Shift + Enter. Se umiddelbart opp til Formula Bar. Disse krøllete parentesene er Excel som forteller deg at de forsto og beregnet dette som en matriseformel. Nå kan jeg dobbeltklikke og sende den ned. Det ser bra ut.
Jeg skal gå til den siste cellen og trykke F2 for å bekrefte at alle områdene ser riktig ut. Nå, det jeg ikke vil gjøre er at jeg ikke vil trykke Enter fordi den formelen etter at vi har satt den i redigeringsmodus, vil bare beregne riktig hvis vi bruker Ctrl + Shift + Enter; eller fordi vi allerede har skrevet inn formelen, kan vi bare bruke Esc-tasten for å gå tilbake til det som er i cellen før vi setter den i redigeringsmodus.
La oss nå teste dette. Jeg kommer til å klikke i den siste cellen her nede og trykke Tab og deretter skrive inn en ny ID, Tab, Tab. Nok en ny plate, Tab, og jeg kan allerede se at jeg ikke hadde nok arbeid her. Det er jeg, vi skal sette - Perfekt og deretter Enter. Nå vil dette ikke automatisk oppdateres som om vi har en haug med formler som vi teller unike elementer og deretter trekker ut unike elementer, men ikke noe problem. Se på dette. Vi kan oppdatere denne listen over unike poster fordi vi brukte Advanced Filter, og det spiller ingen rolle hvilken celle du starter fra, fordi når Advanced Advanced blir påkalt, husker det ekstraktområdet og områdene det opprinnelig så på. Du kan klikke på Advanced Filter eller bruke tastaturet Alt + A + Q. Vi må velge Kopier til et annet sted, men se på det.Den ble fullstendig husket og utvidet til A13 på grunn av Excel Table-funksjonen. Den husket ekstraktområdet. Jeg må bare sjekke unike poster, men klikk OK.
Nå må jeg komme bort og kopiere denne formelen ned. Og der går du, ved hjelp av Advanced Filter og den fantastiske TEXTJOIN-funksjonen med, i Array-operasjon for å få akkurat de elementene som samsvarer. Ok, kast tilbake til.
Bill Jelen: Hei, Mike, det er kjempebra. Greit, avslutter denne episoden. Jeg brukte VBA-funksjonen kalt GetAll, og den unike listen min ble opprettet av Fjern duplikater som er langt enklere enn Advanced Filter, men problemet er at det er en engangs ting. Den husker ikke de forrige innstillingene. Mike opprettet sin unike liste ved hjelp av Advanced Filter, noe som betyr at han senere kunne gjøre om det Advanced Filter uten å spesifisere inngangsområdet og ekstraktområdet på nytt. Og så la TEXTJOIN, en vakker ny funksjon, til en Office 365. Mike sier det alene er en grunn til å få det nyeste Office. Jeg sa at TEXTJOIN ville være livsforandrende. TEXTJOIN er kjempebra fordi den 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.
Å hei, jeg vil takke alle for innom. Vi sees neste gang for en annen Dueling Excel-podcast fra og Excel er gøy.
Last ned fil
Last ned eksempelfilen her: Duel183.xlsm
