Kaley no Nashville strādā pie biļešu izklājlapas. Katram pasākumam viņa izvēlas biļešu plānu. Šis biļešu plāns varētu norādīt no 4 līdz 16 pasākuma biļešu veidiem. Kalijs vēlas formulu, kas nonāks meklēšanas tabulā un atgriezīs * visas * atbilstības, pēc vajadzības ievietojot jaunas rindas.
Lai gan man nav VLOOKUP, kas to varētu atrisināt, to var atrisināt jaunie Power Query rīki, kas iebūvēti programmā Excel 2016.
Piezīme
Ja jums ir Excel 2010 vai Excel 2013 Windows versija, Power Query varat bez maksas lejupielādēt no Microsoft. Diemžēl Power Query vēl nav pieejams operētājsistēmai Excel for Android, Excel for iOS vai Excel for Mac.
Lai ilustrētu mērķi: Maiks Makkans un Mehāniķis Alena teātrī parādās ar biļešu plānu C. Tā kā uzmeklēšanas tabulā ir četras atbilstošas rindas, Keilijs vēlas četras rindas, kurās rakstīts Maiks Makkans un Mehānika, un katrai no tām ir atšķirīga spēle. uzmeklēšanas tabula.
Sākotnējā tabulā atlasiet šūnu. Nospiediet Ctrl + T, lai atzīmētu šos datus kā tabulu. Cilnē Tabulas rīki pārdēvējiet tabulu no Table1 uz Shows. Atkārtojiet uzmeklēšanas tabulu, nosaucot to par Biļetes.
Rādīšanas tabulā atlasiet šūnu. Cilnē Dati izvēlieties No tabulas / diapazona.
Pēc Power Query redaktora atvēršanas atveriet nolaižamo izvēlni Aizvērt un ielādēt un izvēlieties Aizvērt un ielādēt ….
Dialoglodziņā Datu importēšana izvēlieties Tikai izveidot savienojumu.
Dodieties uz tabulu Biļetes. Atkārtojiet darbības, lai izveidotu tikai savienojumu ar biļetēm. Rūtī Vaicājumi jums vajadzētu redzēt abus savienojumus:
Atlasiet jebkuru tukšu šūnu. Izvēlieties Dati, Iegūt datus, Apvienot vaicājumus, Apvienot.
Sapludināšanas dialoglodziņā ir seši soļi. 3. un 4. man nešķiet intuitīvs.
- Augšējā nolaižamajā izvēlnē izvēlieties Izrādes
- Otrajā nolaižamajā izvēlnē izvēlieties Biļetes.
- Augšdaļā noklikšķiniet uz virsraksta Biļešu plāns, lai tabulā Rādīt šo kolonnu izvēlētos kā ārzemju atslēgu.
- Noklikšķiniet uz virsraksta Biļešu plāns apakšdaļā, lai atlasītu šo kolonnu kā atslēgas lauku uzmeklēšanas tabulā.
- Atveriet veidu Join un izvēlieties Inner (tikai atbilstošās rindas).
- Noklikšķiniet uz Labi
Rezultāti sākotnēji rada vilšanos. Jūs redzat visus 1. tabulas laukus un kolonnu ar tabulu, tabulu, tabulu.
Slejas Biļetes augšdaļā noklikšķiniet uz ikonas Izvērst.
Noņemiet biļešu plāna atlasi, jo jums jau ir šis lauks. Atlikušo lauku sauks Biļetes. Biļetes tips, ja vien neatzīmējat atzīmi Izmantot sākotnējo nosaukumu kā prefiksu.
Panākumi! Katra izrādes katra rinda eksplodē vairākās rindās.
Es neesmu īpaši apmierināts ar datu šķirošanu. Kārtojot pēc datuma, biļešu veidi tiek kārtoti nepāra veidā.
Skatīties video
Mūsdienu gadījumā video tika uzņemts pēc raksta uzrakstīšanas. Lai kontrolētu kārtošanas kārtību, es iesaku biļešu veidiem pievienot secības kolonnu.
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2204. sērija: atgrieziet visus VLOOKUP.
Sveiki, sveicināti atkal netcast ēterā, es esmu Bils Jelens. Šodienas jautājums no Nešvilas mūzikas pilsētas. Es atrados tur Nešvilā, kāds ir atbildīgs par biļešu ielādes plānošanu biļešu sistēmā, un tāpēc mums ir šāds: mums ir notikumu saraksts - gaidāmie notikumi - mums ir datums, norises vieta un biļešu plāns. Tātad, kaut arī kaut kas notiek pilī, var būt dažādi biļešu plāni - piemēram, varbūt grīda ir konfigurēta, jūs zināt, ar sēdvietām vai varbūt tā ir tikai stāvoša istaba, vai ne?
Tātad, atkarībā no tā, kāda veida biļešu plāns jums ir jānāk šeit uz tabulu Meklēt un jāatrod visi atbilstošie notikumi, un būtībā mēs darīsim to, ko es saucu par VLOOKUP sprādzienu. Tātad, ja kaut kas atrodas Hannah C, viņi dodas uz Hannah C un, ja Hannā C ir 1, 2, 3, 4, 5, 6–7 priekšmeti, mums būs lai atgrieztu septiņas rindas - tas nozīmē, ka būs jāievieto vēl sešas rindas un šie dati jākopē uz leju. Labi.
Tagad mēs to nedarīsim vispār ar VLOOKUP, bet jūs saprotat jēdzienu - mēs darām VLOOKUP un visas atbildes tiek atgrieztas kā jaunas rindas. Labi, tāpēc es ņemšu abas šīs tabulas un izveidošu tās īstā tabulā ar Ctrl + T. Pirmie sauca 1. tabulu - briesmīgs nosaukums, sauksim to par notikumiem vai šoviem, sauksim tos par šoviem - un otrais, tagad, hei, šeit es uzzināju, jo es to praktizēju - mums ir jābūt secības lauks šeit. Tātad = ROW (A1), veiciet dubultklikšķi un nokopējiet to uz leju un pēc tam kopējiet un ielīmējiet īpašās vērtības. Labi. Tagad mēs to izveidosim kā tabulu - Ctrl + T, un mēs to sauksim par Biļetes.
Labi. Tātad mums ir izrādes, mums ir biļetes. Es dodos uz cilni Dati, un es esmu šeit ar izrādēm, es gribu teikt, ka es gribu iegūt savus datus no tabulas vai diapazona - starp citu, tas ir Power Query. Ja izmantojat programmu Excel 2010 vai 2013, varat to bez maksas lejupielādēt no Microsoft, lejupielādējiet Power Query rīku. Ja izmantojat Mac, iOS vai Android, atvainojiet, jums nav nepieciešams Power Query. Labi, tāpēc no tabulas vai diapazona … atrodiet kādu, kuram ir-- atrodiet draugu, kuram ir Windows dators, un palūdziet viņiem to iestatīt. Labi. Šeit ir tabula, mēs nedarīsim neko, vienkārši aizveriet un ielādējiet, aizveriet un ielādējiet un pēc tam sakiet "Izveidot tikai savienojumu". Mēs nonāksim šeit pie mūsu otrās tabulas: Iegūt datus, no tabulas vai diapazona, mēs neko nedarām šim, aizvērt un ielādēt,Aizvērt un ielādēt uz "Tikai izveidot savienojumu", Labi. Tātad, kas mums tagad ir, vai mums ir savienojums ar pirmo tabulu un savienojums ar otro tabulu. Mēs negatavosimies apvienot šos divus, kas būtībā ir kā VLOOKUP darīšana, vai datubāzu savienojums, es domāju, patiešām ir tas, kas tas ir. Apvienojiet vaicājumus, mēs apvienosimies. Labi.
Tagad septiņas lietas, kas jums jādara šajā dialoglodziņā - un tas ir nedaudz mulsinoši - mēs izvēlēsimies Izrādes kā pirmo tabulu; izvēlieties Biļetes kā otro tabulu; izvēlieties, kāds lauks viņiem ir kopīgs, un tas var būt vairāki lauki - jūs varat kontrolēt un noklikšķināt, bet šajā gadījumā ir tikai viens biļešu plāns; un pēc tam Biļešu plāns; un tad mēs mainīsim Pievienošanās veidu uz Iekšējo savienojumu ar "tikai atbilstošajām rindām". Labi. Tagad jūs noklikšķiniet uz Labi un domājat, ka visa jūsu problēma tiks atrisināta, bet jūs vienkārši esat saspiests, jo šeit ir visi dati no A - viņi vispār nav ievietojuši jaunas rindas - un šeit garlaicīgs stulbs lauks ar nosaukumu Tickets, kurā vienkārši ir Galds, Galds, Galds, hah.
Bet, par laimi, tās augšpusē ir ikona Izvērst, un mēs to paplašināsim - man nav nepieciešams plāns, man tas jau ir - Biļešu tips un secība. Es negribu, lai to sauc par Tickets.TicketType, ko vēlas darīt Power Query - tāpēc es noņemu atzīmi no šīs izvēles rūtiņas. Labi. Pašlaik mums ir 17 datu rindas; kad noklikšķinu uz Labi, BAM! Tur ir sprādziens. Tātad, Michael Seeley un Starlighter's parādās kopā ar visiem dažādiem biļešu veidiem, piemēram, šis. Labi, un, ja šie biļešu veidi parādās secīgi, tas ir lieliski. Bet Maikls Seilijs nav nākamā izrāde, nākamā izrāde ir 5. jūnijā. Tāpēc, kad es mēģinu to kārtot pēc datuma, tas mani noved pie prāta, es to nevaru izskaidrot. Kārtot pēc datuma, un Maiks Mans un Mehāniķi sasniedz 65, bet tad visas biļetes ir ieskrūvētas. Viņiatkārtoju nepareizu secību, un tad man bija jādara šī secība - tā jūtu. Es varu kārtot pēc secības. Tātad tagad, 6, 5, skaisti, un pēc tam tajā Biļetes ir pareizas. Un faktiski šajā brīdī šī kolonna mums vairs nav vajadzīga. Tāpēc es varu ar peles labo pogu noklikšķināt un noņemt, un pēc tam aizvērt un ielādēt - šoreiz es patiešām aizvēršu un ielādēšu, nevis aizvēršu un ielādēju - un mums ir rezultāts. Labi.
Tātad mēs no notikumu saraksta devāmies uz visu šo lielo sarakstu, bet šeit ir lieliskā daļa: es to ieskrūvēju, Mike Man and Mechanics nav Palace B, tā Palace C. Tāpēc es atgriezos pie oriģināla augšējā labajā stūrī. - rokas stūris, lai iegūtu vairāk informācijas par grāmatu.
Labi. Šīs epizodes tēmas: Keisijam Nešvilā jāveic VLOOKUP, lai atgrieztos visos mačos, parasti ievietojot jaunas rindas. Un tā ir biļešu datu bāze, labi? Tāpēc es to dēvēšu par VLOOKUP Explosion, jo katra izrāde eksplodēs līdz 16 rindām. Mēs to izmantosim Power Query, lai to atrisinātu, un esmu uzzinājis, ka Datums parādīsies nepareizajā secībā, ja vien biļetes tipam nepievienosim lauku Secība. Abas kopas izveidojiet tabulā ar Ctrl + T; nosauciet tos par šoviem un biļetēm; un pēc tam no katras tabulas: Get Data, From Table, Close & Load, lai izveidotu tikai savienojumu; atkārtojiet otrai tabulai; pēc tam Dati, Iegūt datus, Apvienot vaicājumus, Apvienot; un tad dialoglodziņš man ir diezgan mulsinošs - izvēlieties Notikumi, izvēlieties Biļetes, abos noklikšķiniet uz Biļešu tips, mainiet savienojumu uz iekšējo savienojumu,noklikšķiniet uz Labi, un tad iegūstat to briesmīgi pieviljošo rezultātu, kur tā ir tikai kolonna, kurā teikts Table, Table, Table, Table; noklikšķiniet uz paplašināšanas ikonas tā augšdaļā; izvēlieties lauku Biļešu secība; nepievienojiet prefiksu ar tabulas nosaukumu; un jūs varat kārtot pēc datuma, kārtot pēc secības; Aizvērt un ielādēt izklājlapā. Skaisti ir tas, ka, ja mainās pamatā esošie dati, vienkārši atsvaidziniet, un jums ir rezultāti.
Tagad, hei, lai lejupielādētu izmantoto darbgrāmatu no šodienas videoklipa, apmeklējiet vietni URL, kas atrodas YouTube aprakstā. Arī tur gaidāmo semināru saraksts - es labprāt jūs redzētu vienā no maniem tiešraides Power Excel semināriem.
Es gribu pateikties Keilijai, ka viņa parādījās Nešvilā un uzdeva man šo lielisko jautājumu. Es gribu, lai jūs apstāties. Tiksimies nākamreiz, lai skatītu citu netcast no.
Lejupielādējiet Excel failu
Lai lejupielādētu Excel failu: return-all-vlookups.xlsx
Power Query mani turpina pārsteigt. Šī ir otrā no trīs dienu sērijām, kur atbilde ir Power Query:
- Otrdiena: konvertējiet datuma / laika kolonnu tikai datumam
- Šodien: atgrieziet visus VLOOKUP
- Ceturtdiena: izveidojiet aptauju katram no 1100 priekšmetiem
Man ir viss YouTube atskaņošanas saraksts ar lietām, kuras es atrisināju, izmantojot Power Query.
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Kad esat šaubās, izmantojiet funkciju ROUND!"
Maiks Girvins
