Tämän päivän kysymys Quentiniltä, joka oli Atlanta Power Excel -seminaarissani. Quentinin on luotava samat 7 kyselykysymystä yli 1000 asiakkaalle Excelissä.
Kuten tästä kuvasta näet, asiakkaat ovat A: ssa. Toistettavat kysymykset ovat sarakkeessa D.
Voisit ratkaista tämän VBA: lla tai kaavoilla, mutta tämä on Power Query -viikko, joten aion käyttää hienoa temppua Power Queryssä.
Jos haluat tyhjän rivin jokaisen kyselyn väliin, lisää järjestysnumero ja numero 7 viimeisen kysymyksen jälkeen.
Paina Ctrl + T molemmissa tietojoukoissa. Nimeä toinen tietojoukko nimellä, jonka muistat, esimerkiksi Kysymykset tai Kysely.
Valitse toisesta tietojoukosta Data, From Table.
Power Query -editori avautuu. Valitse Koti-välilehdeltä avattava Sulje ja lataa -valikko ja valitse Sulje ja lataa …. Valitse seuraavassa valintaikkunassa Luo vain yhteys.
Olet nyt palannut Exceliin. Valitse mikä tahansa solu sarakkeessa A olevasta asiakastaulukosta. Tiedot, taulukosta. Kun kyselyeditori avautuu, napsauta Lisää sarake -välilehteä valintanauhassa ja valitse sitten Mukautettu sarake. Kaava on =#"Questions"(mukaan lukien # ja lainausmerkit).
Editoriin ilmestyy uusi sarake, jonka taulukko toistetaan jokaisella rivillä. Napsauta Laajenna-kuvaketta sarakeotsikossa.
Valitse molemmat taulukon kentät. Valitse Koti-välilehdestä Sulje ja lataa.
Näkyviin tulee uusi laskentataulukko, jossa toistetaan seitsemän kysymystä yli 1000 asiakkaalle.
Katso video
Videon transkriptio
Opi Excel Podcast-jaksosta 2205: Lainakyselyn räjähdys.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Juuri eilen jaksossa 2204 Nashvillen Kaylee joutui tekemään VLOOKUP-räjähdyksen - jokaiselle sarakkeessa D olevalle tuotteelle meillä oli vastaava joukko esineitä sarakkeessa G ja tarvitsimme räjähtää ne. Joten jos Palace C: llä olisi 8 tuotetta, saisimme 8 riviä.
Nyt, tänään, meillä on Quentin. Nyt Quentin oli seminaarissani Atlantassa, mutta hän on tosiasiallisesti Floridasta, ja Quentinillä on täällä lähes 1000 asiakasta - no, yli 1000 asiakasta - sarakkeessa A, ja jokaiselle asiakkaalle hänen on luotava tämä kysely - - tämä kysely, jossa oli 1, 2, 3, 4, 5, 6 kysymystä. Ja mitä teen täällä, aion lisätä sekvenssinumeron vain numeroilla 1-7, jotta voin luoda väliin mukavan tyhjän rivin. Aion tehdä nämä molemmat tietojoukot taulukoksi; Joten yritämme saada nämä 7 riviä räjähtämään jokaiselle näistä 1000 asiakkaasta. Se on tavoite.
Nyt voin tehdä tämän VPA: lla; Voin tehdä tämän kaavoilla; mutta se on eräänlainen "Power Query Week" täällä, olemme käynnissä. Tämä on kolmas Power Query -esimerkkimme peräkkäin, joten aion käyttää Power Queryä. Aion tehdä tämän vasemman pöydäksi. Aion olla varovainen nimetä tämä ei taulukko 1. Annan sille nimen. Meidän on käytettävä tätä nimeä myöhemmin uudelleen, joten kutsun sitä kysymyksiksi - tuollaisena. Ja sitten tämä on taulukko 2, mutta aion nimetä sen uudelleen asiakkaiksi - ei niin tärkeää, että nimeän tämän uudelleen, koska se on toinen, jolla on oltava nimi. Joten aiomme valita tämän; Tiedot; ja aiomme sanoa taulukosta / alueelta. Hanki ja muunna tietoja - tämä tunnetaan nimellä Power Query. Se on sisäänrakennettu Excel 2016. Jos sinulla on 2010 tai 2013, Windows,ei Mac, ei iOS, ei Android, voit ladata Power Queryn ilmaiseksi Microsoftilta.
Joten aiomme saada tietoja taulukosta / alueelta; tässä on meidän pöytä - emme aio tehdä sille mitään, vain sulje ja lataa; Sulje ja lataa; vain Luo yhteys; Selvä, ja kyselyn nimi on Kysymyksiä. Se käyttää samaa nimeä kuin täällä. Ja sitten palaamme tähän, ja, Data; Taulukosta / alueelta; Joten, siellä on luettelo yli 1000 asiakkaastamme.
Hei, tässä on huuto ystäväni Miguel Escobarille, joka on M Is For (DATA) MONKEYn apulaisjohtaja. Lisään linkin videoon - upea kirja Power Querystä - auttoi minua tässä. Aiomme laittaa upouuden mukautetun sarakkeen, ja Mukautettu sarake -kaava on tässä: = # "kyselyn nimi". En olisi koskaan ymmärtänyt sitä ilman Miguelia, joten kiitos Miguelille siitä.
Ja kun napsautan OK, joo, se ei näytä toimivan - saamme vain pöydän, pöydän, pöydän, mutta juuri se oli eilen Kayleen ja lippujen kanssa. Ja minun tarvitsee vain laajentaa tätä, ja aion todellakin sanoa, että en todennäköisesti tarvitse sarjaa … No, laitetaan se vain siinä tapauksessa. Voimme ottaa sen pois, kun olemme nähneet sen. Tällä hetkellä meillä on 1000 riviä, ja nyt meillä on 7000 riviä - kaunis. Näen nyt, että se esiintyy jaksossa, joten en tarvitse sitä. Napsautan hiiren kakkospainikkeella ja poistan vain yhden sarakkeen. Ja sitten voin kotiin; Sulje & Lataa; ja BAM! - Meillä pitäisi nyt olla yli 7000 riviä 6 kysymyksellä ja tyhjä tila jokaiselle asiakkaalle. Quentin oli siitä innoissaan seminaarissa. Viileä, siisti temppu - välttää VBA: ta, välttää kokonaisen joukon kaavoja indeksin avulla,ja sellaiset - hyvä tapa edetä.
Mutta, hei, tänään, anna minun lähettää sinut pois M Is For (DATA) MONKEY: lla. Ken Puls ja Miguel Escobar kirjoittivat suurimman kirjan Power Querystä. Rakastan sitä kirjaa; 2 tunnissa sinusta tulee ammattilainen tuon kirjan kanssa.
Selvä, siis, tänään - Quentinin on luotava identtinen kysely 1000 eri asiakkaalle. Jokaiselle asiakkaalle on 6 tai 7 tai 8 kysymystä. Nyt voimme tehdä tämän VBA: n tai makron avulla, mutta koska olemme täällä Power Query -ajossa, teemme Power Query. Lisäsin kysymyksiin ylimääräisen tyhjän kysymyksen; Lisäsin järjestysnumeron varmistaakseni, että tyhjä pysyy siellä; tee asiakkaista pöytä; tee kysymyksistä taulukko; on todella tärkeää, että annat kysymyksille jotain mitä muistat - kutsuin omiani "Kysymyksiksi". Lisää kysymykset Power Query -palveluun, vain yhteytenä; ja kun lisäät asiakkaita Power Queryyn, luo uusi mukautettu sarake, jossa kaava on: # "ensimmäisen kyselyn nimi" ja laajenna sitten sarake Power Query -editorissa; Kiinni &Lataa takaisin laskentataulukkoon ja olet valmis. Hämmästyttävä temppu - rakastan Power Queryä - suurinta, mitä Excelille tapahtuu 20 vuoden aikana.
Haluan kiittää Quentiniä näyttelystäni seminaarissani. Hän on ollut pari kertaa aiemmin seminaarissani - hieno kaveri. Haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: loan-survey-explosion.xlsx
Power Query hämmästyttää minua edelleen. Lisätietoja Power Querysta on kirjassa M on Data Monkey.
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Voit tehdä mitä tahansa AGGREGATE-ohjelmalla paitsi ymmärtää sitä."
Liam Bastick
