A nashville-i Kaley jegyértékesítő táblázaton dolgozik. Minden eseményhez kiválaszt egy jegykiadási tervet. Ez a jegyértékesítési terv 4–16 jegytípusra utalhat az eseményre. Kaley képletet szeretne, amely a keresőtáblába kerül, és az * összes * találatot adja vissza, adott esetben új sorokat szúr be.
Bár nincs olyan VLOOKUP, amely megoldaná ezt, az Excel 2016-ba beépített új Power Query eszközök képesek megoldani.
jegyzet
Ha rendelkezik az Excel 2010 vagy az Excel 2013 Windows verziójával, akkor ingyenesen letöltheti a Power Query szolgáltatást a Microsoft oldaláról. Sajnos a Power Query még nem érhető el az Android for Excel, az Excel for iOS vagy az Mac for Mac programhoz.
A cél szemléltetése: Mike McCann és a Mechanics az Allen Színházban jelenik meg C jegytervvel. Mivel a keresési táblázatban négy egyező sor található, Kaley négy sorra vágyik, amelyek Mike McCann és a Mechanics feliratúak, és mindegyiküknek más a mérkőzése. a keresőasztal.
Jelöljön ki egy cellát az eredeti táblázatban. Nyomja meg a Ctrl + T billentyűkombinációt az adatok táblázatként történő megjelöléséhez. A Táblaeszközök lapon nevezze át a táblázatot a Táblázat 1-ről Műsorokra. Ismételje meg a keresőtáblát, és hívja Jegyeknek.
Jelöljön ki egy cellát a Műsorok táblában. Az Adatok lapon válassza a Táblázatból / tartományból lehetőséget.
Miután megnyílt a Power Query szerkesztő, nyissa meg a Bezárás és betöltés legördülő menüt, és válassza a Bezárás és a Betöltés… lehetőséget.
Az Adatok importálása párbeszédpanelen válassza a Csak kapcsolat létrehozása lehetőséget.
Lépjen a Jegyek táblához. Ismételje meg a Csak kapcsolat létrehozása a jegyekhez lépéseket. A Lekérdezések ablaktáblán mindkét kapcsolatot látnia kell:
Válasszon ki egy üres cellát. Válassza az Adat, az Adatok lekérése, a Lekérdezések egyesítése, az Egyesítés lehetőséget.
Az Egyesítés párbeszédpanelen hat lépés van. A 3. és a 4. számomra nem tűnik intuitívnak.
- A felső legördülő menüből válassza a Műsorok lehetőséget
- A második legördülő menüből válassza a Jegyek lehetőséget.
- Kattintson a fenti Jegyterv fejlécre, hogy ezt az oszlopot idegen kulcsként válassza ki a Megjelenítések táblázatban.
- Kattintson az alján található Jegyterv fejlécre, hogy az oszlopot válassza a kulcsmezőnek a keresési táblázatban.
- Nyissa meg a Csatlakozás típust, és válassza a Belső elemet (csak az egyező sorok).
- Kattintson az OK gombra
Az eredmények eleinte kiábrándítóak. Megjelenik az 1. táblázat összes mezője és egy oszlop, amely a Tábla, Táblázat, Táblázat feliratot tartalmazza.
Kattintson a Jegyek oszlop tetején található Kibontás ikonra.
Törölje a Jegyterv kijelölését, mivel már rendelkezik ezzel a mezővel. A fennmaradó mező neve Tickets.Ticket Type, hacsak nem törli a jelölést az Eredeti név használata előtagként jelölőnégyzetből.
Siker! Minden műsor minden sora több sorra bomlik.
Nem vagyok különösebben elégedett az adatok rendezésével. A dátum szerinti rendezés hatására a Jegytípusok furcsa módon rendeződnek.
Videót nézni
A mai esetben a videót a cikk megírása után készítették. Javaslom, hogy a rendezési sorrend vezérléséhez adjon hozzá egy szekvencia oszlopot a Jegy típusokhoz.
Videó átirat
Ismerje meg az Excel programot a Podcast 2204-es epizódjából: Az összes VLOOKUP visszaadása.
Hé, üdvözlöm újra a neten, Bill Jelen vagyok. A mai kérdés a Nashville Music City-től. Lent voltam Nashville-ben, valaki felelős a jegyek jegyértékesítési rendszerbe történő beosztásának ütemezéséért, és így áll a rendelkezésünkre: Rendezvények listája van - közelgő események - megvan a dátum, a helyszín és a jegy terv. Tehát, annak ellenére, hogy valamit a palotában tartanak, előfordulhat, hogy különböző jegytervek vannak - például lehet, hogy a padló be van állítva, tudod, ülésekkel vagy csak egy álló helyiség, igaz?
Tehát attól függően, hogy milyen típusú jegytervről van szó, át kell jönnie ide a Lookup asztalhoz, és meg kell találnia az összes megfelelő eseményt, és lényegében azt fogjuk megtenni, amit VLOOKUP-nak nevezek. Tehát ha valami van a Hannah C-ben, akkor lemennek a Hannah C-be, és ha Hannah C-ben van 1, 2, 3, 4, 5, 6-7 elem, akkor hét sor visszaadásához - ami azt jelenti, hogy még hat sort kell beillesztenie, és ezeket az adatokat le kell másolnia. Rendben.
Most ezt egyáltalán nem VLOOKUP-tal fogjuk megtenni, de megkapja a koncepciót - VLOOKUP-ot csinálunk, és az összes választ új sorként adjuk vissza. Rendben, szóval, mindkét táblázatot átveszem, és a Ctrl + T billentyűzettel valódi táblává teszem őket. Az elsőeket 1. táblázatnak hívták - borzalmas név, nevezzük ezt eseményeknek vagy show-knak, nevezzük így show-knak -, a másodikakat, hé, itt van, amit megtanultam, mert ezt gyakoroltam - nekünk meg kell itt egy szekvenciamező. Tehát = ROW (A1), kattintson duplán, és másolja le, majd másolja és illessze be a speciális értékeket. Rendben. Most elkészítjük, hogy ez egy táblává váljon - Ctrl + T, és ezt hívjuk jegynek.
Rendben. Tehát vannak bemutatóink, van jegyünk. Megyek az Adatok fülre, és itt vagyok a műsorokban, azt akarom mondani, hogy egy táblából vagy tartományból szeretném megszerezni az adataimat - ez egyébként a Power Query. Ha visszatér az Excel 2010-re vagy a 2013-ra, letöltheti ezt ingyen a Microsoft-tól, töltse le a Power Query eszközt. Ha Mac, iOS vagy Android rendszert futtat, sajnálom, nincs Power Query az Ön számára. Rendben, tehát egy táblázatból vagy tartományból … keressen valakit, akinek van egy - keressen egy barátot, akinek van egy - Windows PC-je, és állítsa be ezt. Rendben. Itt egy táblázat, ezzel nem fogunk mit kezdeni, csak bezárni és betölteni, bezárni és betölteni, majd azt mondani, hogy "Csak a kapcsolat létrehozása", tökéletes. Ide fogunk térni a második asztalunkhoz: Adatok beolvasása, egy táblából vagy tartományból nem teszünk semmit ezzel a Bezárás és betöltés,Zárja be és töltse be, "Csak kapcsolat létrehozása", OK. Tehát most megvan az a kapcsolatunk az első táblához és a második táblához. Nem fogjuk egyesíteni ezt a kettőt, ami lényegében olyan, mintha a VLOOKUP-ot csinálnánk, vagy azt hiszem, egy adatbázis-együttes valóban olyan, amilyen. Kombinálja a lekérdezéseket, az Egyesítésre megyünk. Rendben.
Hét dolgot kell tenned ebben a párbeszédpanelen - és ez kissé zavaró - első táblázatként a Műsorokat választjuk; válassza a Jegyek lehetőséget második táblának; válaszd ki, hogy melyik mezőjük van közös, és ez több mező lehet - vezérléssel kattintással kattinthat -, de ebben az esetben csak egy jegy terv van; majd Jegyterv; majd a Csatlakozás típust belső csatlakozásra változtatjuk "csak az egyező sorokkal". Rendben. Most kattintson az OK gombra, és úgy gondolja, hogy az egész problémája megoldódik, de csak összetört, mert itt van az összes A-adat - itt egyáltalán nem illesztettek be új sorokat - és ide, csak egy unalmas hülye mező, a Tickets nevű, amiben csak asztal, asztal, asztal van, hah.
De, szerencsére, ennek tetején egy kibontás ikon található, és ezt kibővítjük - nekem nem kell tervet készítenem, már meg is van - a jegy típusa és a sorrend. Nem akarom, hogy Tickets.TicketType legyen, amit a Power Query meg akar tenni - ezért törlöm a jelölést. Rendben. Jelenleg 17 adatsorunk van; amikor az OK gombra kattintok, BAM! Ott a robbanás. Tehát Michael Seeley és a Csillaggyújtó együtt jelenik meg az összes különféle jegytípussal, mint ez. Rendben, és látja, hogy ezek a jegytípusok egymás után jelennek meg, ez nagyszerű. De Michael Seeley nem a következő, a következő show június 5-én lesz. Tehát amikor megpróbálom ezt Dátum szerint rendezni - ez őrületbe kerget, ezt nem tudom megmagyarázni. Rendezés dátum szerint, és Mike Man and the Mechanics feljön 65-re, de akkor a jegyek mind elcsavarodnak. Ők'rossz sorrendben van, és akkor ezért kellett ezt a sorrendet megcsinálnom - úgy érzi. Rendezni tudok Szekvencia szerint. Tehát most, 6, 5, szép, majd azon belül a Jegyek helyesek. És valójában ezen a ponton már nincs szükségünk erre az oszlopra. Tehát jobb egérgombbal kattinthatok és eltávolíthatom, majd bezárhatom és betölthetem - ezúttal valóban bezárni és betölteni fogom, nem pedig bezárni és betölteni - és megvan az eredményünk. Rendben.
Tehát az események listájáról erre az egész nagy listára léptünk, de itt van a félelmetes rész: ezt elcsesztem, a Mike Man and Mechanics nem a Palace B, hanem a Palace C. Tehát visszatérek a jobb felső sarokban lévő eredetihez -kezes sarok további információkért a könyvről.
Rendben. Témák ebben az epizódban: A nashville-i Kaleynek VLOOKUP-ot kell tennie az összes meccs visszaadásához, általában új sorokat illesztve be. És ez egy jegyértékesítési adatbázis, rendben? Tehát ezt VLOOKUP robbanásnak fogom nevezni, mert mindegyik műsor akár 16 sorba is felrobban. A Power Query-t fogjuk használni ennek megoldására, és megtudtam, hogy a Dátum rossz szekvencián fog megjelenni, hacsak nem adunk hozzá egy Szekvencia mezőt a jegy típusához. Készítsen mindkét készletet táblázatba a Ctrl + T billentyűkombinációval; nevezze el őket műsoroknak és jegyeknek; majd az egyes táblákból: Adatok beolvasása, Táblázatból, Bezárás és betöltés - Csak kapcsolat létrehozása; ismételje meg a másik asztalnál; majd Adatok, Adatok lekérése, Lekérdezések egyesítése, Egyesítés; majd ez a párbeszédpanel számomra elég zavaró - válassza az Események lehetőséget, válassza a Jegyek lehetőséget, kattintson a Jegy típusa lehetőségre mindkettőben, változtassa meg a csatlakozást belső csatlakozásra,kattintson az OK gombra, és akkor kapja azt a szörnyen kiábrándító eredményt, ahol csak egy oszlop állítja a Táblázat, Táblázat, Táblázat, Táblázat; kattintson a kibontás ikonra annak tetején; válassza a Jegy szekvencia mezőt; ne előtagozza a táblázat nevét; és rendezhet dátum, rendezés sorrend szerint; Bezárás és betöltés a táblázatba. Az a szép, hogy ha az alapul szolgáló adatok megváltoznak, csak frissítsen, és megkapja az eredményeket.
Hé, ha le szeretné tölteni a mai videóból használt munkafüzetet, keresse fel a lenti URL-t a YouTube leírásában. A közelgő szemináriumok listája is - szívesen látnálak az egyik élő Power Excel szemináriumon.
Szeretnék köszönetet mondani Kaley-nek, hogy megjelent Nashville-ben, és feltette nekem ezt a nagyszerű kérdést. Szeretném, ha betértél volna. Találkozunk legközelebb egy másik netcast-re.
Töltse le az Excel fájlt
Az excel fájl letöltése: return-all-vlookups.xlsx
A Power Query továbbra is meghökkent. Ez a második egy háromnapos sorozatból, ahol a válasz Power Query:
- Kedd: A Dátum / Idő oszlop konvertálása csak dátumra
- Ma: Vissza az összes VLOOKUP-ot
- Csütörtök: Hozzon létre egy felmérést mind a 1100 elemről
Van egy teljes YouTube lejátszási listám azokról a dolgokról, amelyeket végül a Power Query segítségével oldottam meg.
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"Ha kétségei vannak, használja a KEREK funkciót!"
Mike Girvin
