Kaley Nashvillestä työskentelee lippujen laskentataulukon parissa. Hän valitsee jokaiselle tapahtumalle lipunmyyntisuunnitelman. Lipunmyyntisuunnitelma voisi ilmoittaa missä tahansa 4-16 lipun tyyppiä tapahtumaan. Kaley haluaa kaavan, joka menee hakutaulukkoon ja palauttaa * kaikki * vastaavuudet, lisäämällä uusia rivejä tarpeen mukaan.
Vaikka minulla ei ole VLOOKUP-ratkaisua, joka pystyisi ratkaisemaan tämän, Excel 2016: een rakennetut uudet Power Query -työkalut voivat ratkaista sen.
Huomautus
Jos sinulla on Excel 2010: n tai Excel 2013: n Windows-versio, voit ladata Power Queryn ilmaiseksi Microsoftilta. Valitettavasti Power Query ei ole vielä saatavilla Excel for Androidille, Excel for iOS: lle tai Excel for Macille.
Tavoitteen havainnollistamiseksi: Mike McCann ja Mechanics esiintyvät Allen-teatterissa lippusuunnitelman C. Koska hakutaulukossa on neljä vastaavaa riviä, Kaley haluaa neljä riviä, joissa lukee Mike McCann ja Mechanics, joista jokaisella on erilainen ottelu kuin hakutaulukko.
Valitse solu alkuperäisestä taulukosta. Paina Ctrl + T merkitäksesi tiedot taulukkona. Nimeä taulukko Taulukon työkalut -välilehdessä Taulukko 1: sta Näytä. Toista haku taulukolle kutsumalla sitä lipuiksi.
Valitse solu Näyttelyt-taulukosta. Valitse Tiedot-välilehdeltä Taulukosta / alueelta.
Kun Power Query -editori avautuu, avaa avattava Sulje ja lataa -valikko ja valitse Sulje ja lataa ….
Valitse Tuo tiedot -valintaikkunassa Luo vain yhteys.
Siirry Lipputaulukkoon. Toista vaiheet Luo yhteys vain lippuihin. Sinun pitäisi nähdä molemmat yhteydet Kyselyt-ruudussa:
Valitse mikä tahansa tyhjä solu. Valitse Tiedot, Hae tietoja, Yhdistä kyselyt, Yhdistä.
Yhdistä-valintaikkunassa on kuusi vaihetta. Kolmas ja neljäs eivät vaikuta minusta intuitiivisilta.
- Valitse ylhäältä avattavasta valikosta Näyttelyt
- Valitse Liput toisesta avattavasta valikosta.
- Napsauta ylhäällä olevaa lippusuunnitelman otsikkoa valitaksesi kyseisen sarakkeen ulkomaiseksi avaimeksi Näyttelyt-taulukossa.
- Napsauta alareunassa olevaa lippusuunnitelman otsikkoa valitaksesi kyseisen sarakkeen hakukentän avainkentäksi.
- Avaa Liity-tyyppi ja valitse Sisempi (vain vastaavat rivit).
- Napsauta OK
Tulokset ovat aluksi pettymyksiä. Näet kaikki taulukon 1 kentät ja sarakkeen, jossa lukee Taulukko, Taulukko, Taulukko.
Napsauta Laajenna-kuvaketta Liput-sarakkeen yläosassa.
Poista lippusuunnitelman valinta, koska sinulla on jo kyseinen kenttä. Jäljellä olevan kentän nimi on Tickets.Ticket Type, ellet poista valintaa Use Original Name as Prefix.
Menestys! Kunkin esityksen kukin rivi räjähtää useiksi riveiksi.
En ole erityisen tyytyväinen tietojen lajitteluun. Lajittelu päivämäärän mukaan saa liputyypit lajittelemaan parittomalla tavalla.
Katso video
Nykyisessä tapauksessa video ammuttiin artikkelin kirjoittamisen jälkeen. Ehdotan, että lisätään järjestyssarake Lipputyyppeihin lajittelujärjestyksen hallitsemiseksi.
Videon transkriptio
Opi Excel Podcastista, Jakso 2204: Palauta kaikki VLOOKUPit.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän kysymys Nashville Music Citystä. Olin siellä Nashvillessä, joku on vastuussa lippujen lataamisen aikataulutuksesta lippujärjestelmään, joten tässä on mitä meillä on: Meillä on luettelo tapahtumista - tulevista tapahtumista - meillä on päivämäärä, paikka ja lippusuunnitelma. Joten, vaikka jotain pidetään palatsissa, saattaa olla erilaisia lippusuunnitelmia - esimerkiksi, ehkä lattia on konfiguroitu, tiedätkö, istuimilla tai ehkä se on vain seisova huone, eikö?
Joten, riippuen minkä tyyppisestä lippusuunnitelmasta, sinun on tultava tänne hakupöydälle ja löydettävä kaikki vastaavat tapahtumat, ja lähinnä aiomme tehdä sen, mitä kutsun VLOOKUP-räjähdykseksi. Joten jos jotain on Hannah C: ssä, he menevät alas Hannah C: hen, ja jos Hannah C: ssä on 1, 2, 3, 4, 5, 6-7 tuotetta, meillä on palauttaa seitsemän riviä - mikä tarkoittaa, että joudut lisäämään vielä kuusi riviä ja kopioimaan nämä tiedot alas. Hyvä on.
Emme aio tehdä tätä lainkaan VLOOKUPilla, mutta saat käsitteen - teemme VLOOKUPin ja palautamme kaikki vastaukset uusina riveinä. Selvä, siis aion ottaa nämä molemmat pöydät ja tehdä niistä oikean pöydän Ctrl + T: n avulla. Ensimmäisiä kutsutaan taulukoksi 1 - kamala nimi, kutsumme tätä tapahtumiksi tai näyttelyiksi, kutsumme sitä näyttelyiksi, ja toiset, nyt, hei, tässä on mitä opin, koska harjoittelin tätä - meillä on oltava sekvenssikenttä tässä. Joten = RIVI (A1), kaksoisnapsauta ja kopioi se alaspäin ja kopioi ja liitä sitten erikoisarvot. Hyvä on. Nyt teemme niin, että siitä tulee taulukko - Ctrl + T, ja kutsumme sitä yhdeksi lipuksi.
Hyvä on. Joten meillä on esityksiä, meillä on lippuja. Aion mennä Data-välilehdelle, ja olen täällä show-jutussa, haluan sanoa, että haluan saada tietoni taulukosta tai alueelta - tämä on Power Query, muuten. Jos olet taas Excel 2010: ssä tai 2013: ssä, voit ladata tämän ilmaiseksi Microsoftilta, lataa Power Query -työkalu. Jos käytät Macia, iOS: ää tai Androidia, anteeksi, ei virtakyselyä sinulle. Selvä, joten taulukosta tai alueelta … etsi joku, jolla on - etsi ystävä, jolla on Windows-tietokone, ja anna heidän asettaa tämä. Hyvä on. Tässä on taulukko, emme aio tehdä mitään tälle, vain Sulje ja lataa, Sulje ja lataa ja sano sitten "Luo vain yhteys", täydellinen. Tulemme tänne toiseen taulukkoon: Hae tietoja, taulukosta tai alueelta, emme tee mitään tälle, Sulje ja lataa,Sulje ja lataa kohtaan "Luo vain yhteys", OK. Joten mitä meillä on nyt, onko meillä yhteys ensimmäiseen ja toiseen taulukkoon. Emme aio yhdistää näitä kahta, mikä olennaisesti on kuin VLOOKUPin tekeminen, tai tietokantayhteys, luulen, on todella millaista. Yhdistä kyselyt, olemme yhdistämässä. Hyvä on.
Nyt seitsemän asiaa, jotka sinun on tehtävä tässä valintaikkunassa - ja se on hieman hämmentävää - valitsemme Näyttelyt ensimmäiseksi taulukoksi; valitse Liput toiseksi taulukoksi; valitse, mikä kenttä heillä on yhteistä, ja tämä voi olla useita kenttiä - voit hallita ja napsauttaa - mutta tässä tapauksessa lippuja on vain yksi; ja sitten lippusuunnitelma; ja sitten aiomme muuttaa Join-tyypin Inner-liittymäksi "vain vastaavat rivit". Hyvä on. Napsautat OK ja luulet, että koko ongelmasi ratkaistaan, mutta olet vain murskattu, koska tässä on kaikki A: n tiedot - he eivät ole lisänneet yhtään uutta riviä - ja täällä, vain tylsä tyhmä kenttä nimeltä Tickets, jossa on vain pöytä, pöytä, pöytä, hah.
Mutta onneksi sen yläosassa on Laajenna-kuvake, ja laajennamme sitä - minun ei tarvitse tehdä suunnitelmaa, minulla on jo se - Lipputyyppi ja -järjestys. En halua, että sitä kutsutaan nimellä Tickets.TicketType, minkä Power Query haluaa tehdä - joten poistan tämän valintaruudun valinnan. Hyvä on. Tällä hetkellä meillä on 17 tietoriviä; kun napsautan OK, BAM! Siellä on räjähdys. Joten, Michael Seeley ja Tähtien sytytin esiintyvät kaikilla erilaisilla lipputyypeillä, kuten tämä. Selvä, ja katso, että nämä lipputyypit näkyvät peräkkäin, se on hienoa. Mutta Michael Seeley ei ole seuraava näyttely, seuraava näyttely on 5. kesäkuuta. Joten kun yritän lajitella tämän päivämäärän mukaan - se ajaa minut hulluksi, en voi selittää tätä. Lajittele päivämäärän mukaan, ja Mike Man and the Mechanics nousee 65: een, mutta sitten liput ovat kaikki kiinni. Ne'uudelleen väärässä järjestyksessä, ja sitten minun piti tehdä tämä jakso - tuntuu siltä. Voin lajitella jakson mukaan. Joten nyt, 6, 5, kaunis, ja sitten sisällä, liput ovat oikeita. Ja itse asiassa tässä vaiheessa emme enää tarvitse tätä saraketta. Joten voin napsauttaa hiiren kakkospainikkeella ja poistaa, ja sitten Sulje ja lataa - tällä kertaa aion todella Sulje ja lataa, en sulje ja lataa - ja meillä on tulos. Hyvä on.
Joten menimme tapahtumaluettelosta tähän koko suureen luetteloon, mutta tässä on mahtava osa: Kierrin tämän, Mike Man and Mechanics ei ole Palace B, sen Palace C. Joten palaan oikean yläkulman alkuperäiseen. -nurkka lisätietoja kirjasta.
Hyvä on. Tämän jakson aiheet: Kaleyn Nashvillessä on tehtävä VLOOKUP palauttaakseen kaikki ottelut, yleensä lisäämällä uusia rivejä. Ja se on lipputietokanta, okei? Joten aion kutsua tätä VLOOKUP-räjähdykseksi, koska jokainen esitys räjähtää jopa 16 riviin. Aiomme käyttää Power Queryä tämän ratkaisemiseksi, ja olen oppinut, että Päivämäärä näkyy väärässä järjestyksessä, ellemme lisää Sequence-kenttää lipputyyppiin. Tee molemmista sarjoista taulukko painamalla Ctrl + T; eenimeä ne näyttelyiksi ja lipuiksi; ja sitten jokaisesta taulukosta, Hae tietoja, Taulukosta, Sulje ja lataa, vain Luo yhteys; toista toista taulukkoa varten; sitten Data, Hae tietoja, Yhdistä kyselyt, Yhdistä; ja sitten valintaikkuna on minulle melko sekava - valitse Tapahtumat, valitse Liput, napsauta molemmissa Lipputyyppi, vaihda liitos sisäiseksi liitokseksi,Napsauta OK ja saat sen kauhistuttavan pettymyksen, jossa se on vain sarake, jossa lukee Taulukko, Taulukko, Taulukko, Taulukko; napsauta sen yläosassa olevaa Laajenna-kuvaketta; valitse Lippusarja-kenttä; älä etuliitetä taulukon nimeä; ja voit lajitella päivämäärän, lajittelun järjestyksen mukaan; Sulje ja lataa laskentataulukko. Kaunis asia on, että jos taustalla olevat tiedot muuttuvat, päivitä vain ja saat tulokset.
Hei, lataa käytetty työkirja tämän päivän videosta käymällä URL-osoitteen alla YouTube-kuvauksessa. Myös luettelo tulevista seminaareista - haluaisin nähdä sinut yhdessä Power Excel -seminaareissani.
Haluan kiittää Kaleyä siitä, että hän esiintyi Nashvillessä ja antoi minulle suuren kysymyksen. Haluan sinun pysähtyvän. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: return-all-vlookups.xlsx
Power Query hämmästyttää minua edelleen. Tämä on toinen kolmen päivän sarjasta, jossa vastaus on Power Query:
- Tiistai: Muunna päivämäärä / aika-sarake vain päivämääräksi
- Tänään: Palauta kaikki VLOOKUPit
- Torstai: Luo kysely jokaiselle 1100 tuotteelle
Minulla on koko YouTube-soittolista asioista, jotka päädyin ratkaisemaan Power Queryllä.
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Kun olet epäilyssä, käytä PYÖRISTÄ-toimintoa!"
Mike Girvin
