Liity kaikkiin VLOOKUP - Excel-vinkkejä

Voiko Excel VLOOKUP palauttaa kaikki tulokset ja liittää ne pilkulla väliin?

Katso video

• Tavoitteena on liittää kaikki tekstivastaukset VLOOKUPista
• Billin menetelmä: Käytä VBA-toimintoa nimeltä GetAll
• Ainutlaatuinen luettelo Poista kopiot -toiminnolla
• Miken menetelmä:
• Ainutlaatuinen luettelo Lisäsuodatinta käyttämällä
• TEXTJOIN-funktio lisätty Office 365: een
• TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))
• IF-funktion takia kaava vaatii Ctrl + Vaihto + Enter aina, kun muokkaat kaavaa
• Alt AQOR Enter suorittaa Lisäsuodatin uudelleen!

Videon transkriptio

Jakso 183: Liity kaikkiin VLOOKUP-otteluihin

Bill Jelen: Hei, tervetuloa takaisin. On aika uudelle Dueling Excel Podcastille. Olen Bill Jelen, minusta tulee. Mike Girvin liittyy Excel Is Fun -sovellukseen. Tämä on jakso 183: Liity kaikkiin VLOOKUP-otteluihin.

(Musiikki)

Selvä, tämän päivän kysymys Mattilta. Voiko VLOOKUP palauttaa kaikki tulokset ja liittää ne yhteen pilkulla välin kunkin välillä. Esimerkiksi 109876, jotka ovat nämä kaksi täällä, voiko se palauttaa vähäisen öljypilkutilan Tarkistettu 12.12. Ja tietysti, jos niitä olisi enemmän, se palauttaisi enemmän. Selvä, joten ratkaisumme tässä käyttää jonkin verran VBA: ta. Selvä, joten varmista, että se on tallennettu nimellä xlsm tai et voi suorittaa VBA: ta tai xlsb: tä, mutta ei xlsx - xlsx on yksi tiedosto, joka ei voi suorittaa VBA: ta. Painamme Alt + F11, varmista, että käytössäsi on Dual183 tai mikä tahansa työkirjan nimi. Lisää moduuli tyhjään moduuliin ja liitämme tämän koodin.

Katsotaanpa tätä funktiota GetAll, ja tässä on etsimämme ID-numero ja sitten alue, jonka haluamme etsiä. Ja aloitamme, palautamme muuttujan nimeltä GetAll, joten aloitamme siten, että se on yhtä suuri kuin tyhjä tyhjä. Jos jokaisen solun alueellani, etsimme soluarvoa, otamme GetAll = GetAll & "" ja sitten Cell.Offset (0 riviä, 1 sarake), toisin sanoen arvon se on vain kyseisen ID-numeron vieressä, koska tässä VBA: ssa, tässä on ID-numero. Jos löydämme vastaavan ID-numeron, haluamme mennä yhden sarakkeen yli. Entä jos haluat siirtyä 2 sarakkeeseen tai 3 sarakkeeseen, niin muutat tämän 0 rivin ja yhden sarakkeen arvoksi 2. Selvä, tarkista myös, emmekö laita pilkkuja, jos tämä on Ensimmäinen.Joten jos GetAll-muuttuja on tällä hetkellä "", emme laita pilkkuja, okei?

Joten nyt, kun meillä on tämä toiminto täällä, katso kuinka helppoa tämä on ratkaista Mattin ongelma. Tulemme tänne ja otamme hänen tunnuksensa, Ctrl + C ja liitä Ctrl + V näin. Tiedot, Poista kopiot ja napsauta OK. Joten on olemassa ainutlaatuinen luettelo tunnuksista ja sitten haluamme sanoa = getall ja etsimme tätä arvoa E2-pilkulla. Tarkastellessani tätä aluetta täältä, painan F4. F4 toimii aivan kuten tavallinen toiminto. Ja jälleen Mattin kysymys pois tieltä, kaksoisnapsauta ampua se alas. Se toimii.

Ja yritetään vain, kokeillaan jotain hullua täällä. Tehdään lause 1 ja laitetaan vain joukko niitä, kuten lauseet 1 - 10. Me allekirjoitamme kaikki nämä numeroon 109999. Liitä ja liitä sitten tänne. Kopioi kaava alas, muokkaa kaavaa niin, että se menee tietysti loppuun asti. Jep. Ja se palauttaa kaikki nuo lauseet. Selvä, joten se on ratkaisuni, VBA, pieni toiminto siellä. Mike, katsotaan mitä sinulla on.

Mike Girvin: Kiitos. GetAll, se on mahtava VBA-toiminto. Selvä, aion mennä täältä. Olen jo muuntanut sen Excel-taulukkoon, joten kun lisätään tietueita alla, toivottavasti asiat päivittyvät.

Nyt teen ensimmäisenä tämän kahdessa osassa. Voisin tehdä tässä kaavan ainutlaatuisen luettelon purkamiseksi, mutta haluan tarkastella toista vaihtoehtoa: Lisäasetussuodattimessa on poimi ainutlaatuinen luettelovaihtoehto ja se voidaan päivittää. Korostan vain ID-sarakkeen tiedot, edistyneelle suodattimelle, tai aion käyttää näppäimistöä Alt, A, Q. Nyt suodatinluettelo paikallaan, ei millään tavalla. Haluan kopioida sen toiseen sijaintiin. Se sai vain A-sarakkeen ja koska se on Excel-taulukko, joka laajenee myöhemmin. Minulla ei ole ehtoja, haluan kopioida sen D1: een ja tarkistaa vain Ainutlaatuiset tietueet. Napsauta OK.

Tulen nyt tänne, kaikki kommentit tulevat ja käytän toimintoa, joka toimii vain Excel 2016 Office 365: = TEXTJOIN -funktiossa. Pelkästään tämä toiminto kannattaa hankkia uusin Excel-versio. Tämä on niin yleinen tehtävä, että ihmiset haluavat tehdä, liittää monia asioita yhteen. Nyt erottimemme merkinnässä “,”, ja hienoa tässä toiminnossa on, että voimme kertoa sen ohittaa tyhjät solut. Voin nyt laittaa TOSI, 1 tai Jätä se, jätä se väliin. Joten aion jättää sen, jättää sen. Ja tässä tarvitaan tekstiä. Aiomme käyttää IF-funktiota suodattamaan ja saamaan vain haluamasi tuotteet. Sanon, että katsokaa koko tämä sarake läpi: Taulukon nimi ja sitten kentässä () kentässä, ovatko kukaan teistä = tähän suhteelliseen soluviittaukseen, se on looginen testi. Jos napsautan tätä ja painan F9-näppäintä arvioidakseni,voit nähdä juuri nyt, että meillä on vain 2 TODISTUSTA, Ctrl + Z kirjoitan nyt pilkun ja joukon Trues and Falses, nyt voin antaa sille valitut kohteet. Joten nyt valitsemme tältä alueelta vain tuotteet, joilla on TOSI. Pilkku ja minä haluamme varmistaa, että laitamme “” - se näkyy tyhjänä soluna TEXTJOINin toisen argumentin suhteen.

Aion sulkea sulkeet ja nyt, jos IF-toiminto luo kyseisen merkkijonon totuus- ja epätosiominaisuuksia, todelliset kohteet tältä alueelta noudetaan, jos se näkee sen tosi ja kaikilla muilla kohteilla on tyhjä solu. Ja arvaa mitä? TEXTJOIN jättää kokonaan huomiotta kaikki tyhjät solut ja palauttaa vain kohteet, jotka vastaavat tätä tunnusta, ja liittää sen sitten siihen erottimeen. Nyt tämä on ehdottomasti Array-kaava, joka vaatii erityisen näppäinpainalluksen Ctrol + Vaihto + Enter. Looginen testiargumentti pitää Array-operaatiotamme ja tämä argumentti ei voi laskea tätä Array-operaatiota oikein, ellemme käytä näppäimistöä Ctrl + Vaihto + Enter. Nyt aion sulkea sulkeet. Itse asiassa voimme todistaa yhden täällä tekstissä 1, jos minä F9 kaiken tämän, voimme nähdä, että saamme 2 kohdetta, loput näistä tyhjistä soluista jätetään huomiotta. Ctrl + Z. Anna nyts syötä tämä soluun painamalla Ctrl + Vaihto + Enter. Katso heti ylös Formula Bar. Nämä kiharat suluet ovat Excel, joka kertoo sinulle ymmärtävän ja laskeneen tämän Array-kaavana. Nyt voin kaksoisnapsauttaa ja lähettää sen alas. Se näyttää hyvältä.

Aion mennä viimeiseen soluun ja painaa F2 tarkistaa, että kaikki alueet näyttävät oikein. En halua tehdä enkä halua painaa Enter-näppäintä, koska kaava sen jälkeen, kun olemme asettaneet sen muokkaustilaan, laskee oikein vain, jos käytämme Ctrl + Vaihto + Enter; tai, koska olemme jo kirjoittaneet kaavan, voimme vain käyttää Esc-näppäintä palataksesi takaisin soluun, ennen kuin laitamme sen muokkaustilaan.

Testataan nyt. Napsautan viimeistä solua täällä ja painan sarkainta ja kirjoitan sitten uuden tunnuksen, välilehden, välilehden. Toinen uusi levy, Tab, ja näen jo, että minulla ei ollut tarpeeksi työtä täällä. Olen, aiomme laittaa - Täydellinen ja sitten Enter. Nyt tämä ei päivity automaattisesti, kuten jos meillä on joukko kaavoja, jotka laskemme yksilöllisiä kohteita ja sitten poimimme ainutlaatuiset kohteet, mutta ei ongelmaa. Katso tämä. Voimme päivittää tämän luettelon ainutlaatuisista tietueista, koska käytimme Edistynyttä suodatinta, eikä sillä ole väliä mistä solusta aloitat, koska kun Edistynyt suodatin käynnistetään, se muistaa aloitusalueen ja alueet, joita se alun perin tarkasteli. Voit napsauttaa Lisäsuodatin-painiketta tai käyttää näppäimistöä Alt + A + Q. Meidän on kuitenkin valittava Kopioi toiseen sijaintiin, mutta katsokaa sitä.Se muisti täysin ja laajeni A13: een Excel-taulukon ominaisuuden vuoksi. Se muisti otevalikoiman. Minun täytyy tarkistaa vain yksilölliset tietueet, mutta napsauta OK.

Minun on tultava tänne ja kopioitava tämä kaava alas. Ja siellä menet, käyttämällä edistynyttä suodatinta ja hämmästyttävää TEXTJOIN-toimintoa Array-toiminnossa saadaksesi vain vastaavat tuotteet. Selvä, heitä takaisin.

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.

Voi hei, haluan kiittää kaikkia pysähtymisestä. Nähdään ensi kerralla uudelle Dueling Excel-podcastille ja Excel on hauskaa.

Lataa tiedosto

Lataa esimerkkitiedosto täältä: Duel183.xlsm