Kaley iš Našvilio kuria bilietų skaičiuoklę. Kiekvienam renginiui ji pasirenka bilietų planą. Tame bilietų plane galima nurodyti nuo 4 iki 16 renginio bilietų tipų. Kaley nori formulės, kuri pateks į paieškos lentelę ir grąžins * visas * atitiktis, atitinkamai įterpdama naujas eilutes.
Nors neturiu VLOOKUP, kuris galėtų tai išspręsti, nauji „Power Query“ įrankiai, įdiegti „Excel 2016“, gali tai išspręsti.
Pastaba
Jei turite „Windows 2010“ „Excel 2010“ arba „Excel 2013“ versiją, galite nemokamai atsisiųsti „Power Query“ iš „Microsoft“. Deja, „Power Query“ dar negalima „Excel for Android“, „Excel for ios“ ar „Excel for Mac“.
Tikslui pavaizduoti: Mike'as McCannas ir „Mechanics“ pasirodo Alleno teatre su bilieto planu C. Kadangi paieškos lentelėje yra keturios atitinkančios eilutės, Kaley nori keturių eilučių, kuriose parašyta Mike'as McCannas ir „Mechanics“, kurių kiekviena skiriasi nuo paieškos lentelę.
Pasirinkite langelį pradinėje lentelėje. Paspauskite „Ctrl“ + T, kad pažymėtumėte tuos duomenis kaip lentelę. Skirtuke Lentelės įrankiai pervardykite lentelę iš Lentelė1 į Parodos. Pakartokite paieškos lentelę, pavadindami ją „Bilietais“.
Lentelėje „Parodos“ pasirinkite langelį. Skirtuke Duomenys pasirinkite Iš lentelės / diapazono.
Atsidarius „Power Query“ redaktoriui, atidarykite išskleidžiamąjį meniu „Uždaryti ir įkelti“ ir pasirinkite Uždaryti ir įkelti….
Dialogo lange Duomenų importavimas pasirinkite Tik sukurti ryšį.
Eikite į lentelę „Bilietai“. Pakartokite veiksmus, kad sukurtumėte tik ryšį su bilietais. Užklausų srityje turėtumėte pamatyti abu ryšius:
Pasirinkite bet kurį tuščią langelį. Pasirinkite Duomenys, Gauti duomenis, Sujungti užklausas, Sujungti.
Sujungimo dialogo lange yra šeši veiksmai. 3 ir 4 man neatrodo intuityvus.
- Viršutiniame išskleidžiamajame meniu pasirinkite „Laidos“
- Antrame išskleidžiamajame meniu pasirinkite Bilietai.
- Spustelėkite ant viršaus esančią antraštę „Bilietų planas“, kad lentelėje „Parodymai“ pažymėtumėte tą stulpelį kaip užsienio raktą.
- Norėdami pažymėti tą stulpelį kaip raktinį lauką paieškos lentelėje, spustelėkite apačioje esančią antraštę „Bilietų planas“.
- Atidarykite „Join“ tipą ir pasirinkite „Vidinis“ (tik atitinkančios eilutės).
- Spustelėkite Gerai
Rezultatai iš pradžių nuvilia. Matote visus 1 lentelės laukus ir stulpelį su lentele, lentele, lentele.
Stulpelio Bilietai viršuje spustelėkite piktogramą Išskleisti.
Panaikinkite bilietų plano pasirinkimą, nes jau turite tą lauką. Likęs laukas bus vadinamas Bilietai. Bilieto tipas, nebent atžymėsite žymėjimą Naudoti originalų vardą kaip prefiksą.
Sėkmė! Kiekviena kiekvienos laidos eilutė suskaidoma į kelias eilutes.
Aš nesu ypač patenkinta duomenų rūšiavimu. Rūšiuojant pagal datą, bilietų tipai rūšiuojami keistai.
Žiūrėti video
Šiandienos atveju vaizdo įrašas buvo nufilmuotas parašius straipsnį. Siūlau pridėti eilutės stulpelį prie Bilietų tipai, kad būtų galima valdyti rūšiavimo tvarką.
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2204 serija: grąžinkite visus VLOOKUP.
Ei, sveiki sugrįžę į internetinę transliaciją, aš Billas Jelenas. Šiandienos klausimas iš Našvilio muzikos miesto. Aš buvau ten Nešvilyje, kažkas yra atsakingas už planavimo įkelti bilietus į bilietų sistemą tvarkaraštį, todėl štai ką turime: Mes turime įvykių sąrašą - artėjančius renginius - turime datą, vietą ir bilieto planą. Taigi, nors kažkas vyksta rūmuose, gali būti skirtingi bilietų planai - pavyzdžiui, galbūt grindys sukonfigūruotos, žinote, su sėdynėmis, o gal tai tik stovintysis kambarys, tiesa?
Taigi, priklausomai nuo to, kokio tipo bilietų planas, jūs turite ateiti čia prie „Lookup“ lentelės ir rasti visus atitinkančius įvykius, ir iš esmės mes padarysime tai, ką aš vadinu „VLOOKUP“ sprogimu. Taigi, jei kažkas yra „Hannah C“, jie nusileis į „Hannah C“, o jei Hannah C yra 1, 2, 3, 4, 5, 6–7 daiktai, turėsime jei norite grąžinti septynias eilutes - tai reiškia, kad turėsite įterpti dar šešias eilutes ir nukopijuoti tuos duomenis žemyn. Gerai.
Dabar mes to visiškai nedarysime su VLOOKUP, bet jūs suprantate koncepciją - mes darome VLOOKUP ir visus atsakymus grąžiname kaip naujas eilutes. Gerai, aš paimsiu abi šias lenteles ir padarysiu jas tikra lentele su „Ctrl“ + T. Pirmieji vadinami 1 lentele - siaubingas vardas, pavadinkime tai „Renginiais“ ar „Laidomis“, pavadinkime „Taip“, - „Antrasis“, dabar, ei, štai ką aš išmokau, nes aš tai praktikavau sekos laukas čia. Taigi = EILUTĖ (A1), dukart spustelėkite ir nukopijuokite žemyn, tada nukopijuokite ir įklijuokite specialias reikšmes. Gerai. Dabar mes padarysime, kad tai padarys lentelę - „Ctrl“ + T, ir mes tą pavadinsime „Bilietais“.
Gerai. Taigi mes turime pasirodymus, turime bilietus. Eisiu į skirtuką „Duomenys“ ir čia dalyvauju laidose, noriu pasakyti, kad noriu gauti duomenis iš lentelės ar diapazono - beje, tai yra „Power Query“. Jei vėl naudojatės „Excel 2010“ ar „2013“, galite atsisiųsti tai nemokamai iš „Microsoft“, atsisiųskite „Power Query“ įrankį. Atsiprašome, jei naudojate „Mac“, „iOS“ ar „Android“, jums nereikia „Power Query“. Gerai, taigi iš lentelės ar diapazono … raskite ką nors, kas turi-- raskite draugą, kuris turi „Windows“ kompiuterį, ir paprašykite, kad jis tai nustatytų. Gerai. Čia yra lentelė, mes to nepadarysime, tiesiog uždarykite ir įkelkite, uždarykite ir įkelkite, tada sakykite „Tik sukurti ryšį“. Mes eisime čia prie savo antrosios lentelės: gauti duomenis, iš lentelės ar diapazono, mes nieko nedarome šiam, uždaryti ir įkelti,Uždarykite ir įkelkite į „Tik sukurti ryšį“, Gerai. Taigi, ką mes turime dabar, ar mes turime ryšį su pirmąja lentele ir ryšį su antrąja lentele. Mes neketiname sujungti šių dviejų, o tai iš esmės yra panašus į „VLOOKUP“ atlikimą, arba duomenų bazės jungtį, manau, iš tikrųjų tai yra. Sujunkite užklausas, mes sujungsime. Gerai.
Dabar septyni dalykai, kuriuos turite padaryti šiame dialogo lange - ir tai šiek tiek painu - mes pasirinksime „Parodos“ kaip pirmąją lentelę; pasirinkite Bilietai kaip antrą lentelę; pasirinkite, kokį lauką jie turi bendro, ir tai gali būti keli laukai - galite valdyti ir spustelėti, bet šiuo atveju yra tik vienas bilieto planas; ir tada Bilietų planas; tada pakeisime Prisijungimo tipą į Vidinį sujungimą su „tik atitinkančiomis eilutėmis“. Gerai. Dabar spustelėkite Gerai ir manote, kad visa jūsų problema bus išspręsta, bet jūs tiesiog sutriuškintas, nes čia yra visi duomenys iš A - jie visiškai neįterpė jokių naujų eilučių - ir čia nuobodus kvailas laukas pavadinimu Bilietai, kuriame tiesiog yra stalas, stalas, stalas, hah.
Laimei, viršuje yra „Išskleisti“ piktograma, ir mes ją išplėsime - man nereikia parengti plano, aš tai jau turiu - bilietų tipas ir seka. Nenoriu, kad jis būtų vadinamas „Tickets.TicketType“, ką nori padaryti „Power Query“, todėl atžymiu šį laukelį. Gerai. Šiuo metu turime 17 duomenų eilučių; kai spusteliu Gerai, BAM! Ten sprogimas. Taigi, Michaelas Seeley ir „Starlighter“ pasirodys su visais skirtingais bilietų tipais, kaip šis. Gerai, ir pamatykite, kad šie bilietų tipai rodomi iš eilės. Tačiau Michaelas Seeley nėra kita laida, kita laida bus birželio 5 d. Taigi, kai bandau tai surūšiuoti pagal datą, tai mane išprotėja, negaliu to paaiškinti. Rūšiuoti pagal datą, ir Mike'as Manas bei „Mechanics“ pasieks 65 metus, bet tada visi bilietai bus įsukti. Jievėl neteisinga seka, ir tada aš turėjau atlikti šią seką - jaučiasi taip. Aš galiu rūšiuoti pagal eilę. Taigi dabar, 6, 5, gražūs, o vėliau - bilietai yra teisingi. Tiesą sakant, šiuo metu mums nebereikia šio stulpelio. Taigi galiu spustelėti dešiniuoju pelės mygtuku ir pašalinti, tada uždaryti ir įkelti - šį kartą aš iš tikrųjų uždarysiu ir įkelsiu, o ne uždarysiu ir įkelsiu - ir mes turime savo rezultatą. Gerai.
Taigi iš įvykių sąrašo pereidavome prie viso šio didelio sąrašo, bet čia yra nuostabi dalis: aš tai susukau, „Mike Man and Mechanics“ nėra „B“ rūmai, o jų rūmai. - rankos kampas, kur rasite daugiau informacijos apie knygą.
Gerai. Šios serijos temos: Kaley Našvilyje turi atlikti VLOOKUP, kad grąžintų visas rungtynes, paprastai įterpiant naujas eilutes. O tai bilietų duomenų bazė, gerai? Taigi tai pavadinsiu VLOOKUP sprogimu, nes kiekviena laida sprogs iki 16 eilučių. Norėdami tai išspręsti, naudosime „Power Query“ ir sužinojau, kad Data bus rodoma netinkamoje sekoje, nebent prie bilieto tipo pridėsime lauką „Seka“. Padarykite abu rinkinius į lentelę su „Ctrl“ + T; pavadinkite juos „Laidomis“ ir „Bilietais“; ir tada iš kiekvienos lentelės, Gauti duomenis, Iš lentelės, Uždaryti ir įkelti, kad tik sukurtumėte ryšį; pakartokite kitai lentelei; tada Duomenys, Gauti duomenis, Sujungti užklausas, Sujungti; tada tas dialogo langas man gana painus - pasirinkite „Renginiai“, pasirinkite „Bilietai“, abiejuose spustelėkite „Bilietų tipas“, pakeiskite jungtį į vidinę jungtį,spustelėkite Gerai, tada gausite tą siaubingai nuviliantį rezultatą, kur tai tik stulpelis, kuriame sakoma lentelė, lentelė, lentelė, lentelė; viršuje spustelėkite piktogramą Išskleisti; pasirinkite lauką Bilietų seka; nepriimkite priešdėlio su lentelės pavadinimu; ir jūs galite rūšiuoti pagal datą, rūšiuoti pagal seką; Uždaryti ir įkelti į skaičiuoklę. Gražiausia tai, kad pasikeitus pagrindiniams duomenims, tiesiog atnaujinkite ir turėsite savo rezultatus.
Ei, atsisiųskite naudojamą darbaknygę iš šiandienos vaizdo įrašo, apsilankykite URL, esančiu „YouTube“ apraše. Taip pat būsimų seminarų sąrašas - norėčiau jus pamatyti viename iš mano tiesioginių „Power Excel“ seminarų.
Noriu padėkoti Kaley, kad ji pasirodė Našvilyje ir uždavė man šį puikų klausimą. Aš noriu tavęs, kad užsukai. Pasimatysime kitą kartą kitam internetiniam transliacijai iš.
Atsisiųskite „Excel“ failą
Norėdami atsisiųsti „Excel“ failą: return-all-vlookups.xlsx
„Power Query“ ir toliau mane stebina. Tai yra antroji trijų dienų serija, į kurią atsakoma į „Power Query“:
- Antradienis: konvertuokite datos / laiko stulpelį į tiesiog datą
- Šiandien: grąžinkite visus VLOOKUP
- Ketvirtadienis: sukurkite kiekvieno iš 1100 elementų apklausą
Turiu visą „YouTube“ grojaraštį dalykų, kuriuos galiausiai išsprendžiau naudodamas „Power Query“.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
"Kai kyla abejonių, naudokite ROUND funkciją!"
Mike'as Girvinas
