Кейли от Нашвил работи върху електронна таблица за билети. За всяко събитие тя избира план за билети. Този план за билети може да посочва от 4 до 16 вида билети за събитието. Kaley иска формула, която ще отиде в таблицата за търсене и ще върне * всички * съвпадения, като вмъкне нови редове според случая.
Въпреки че нямам VLOOKUP, който може да реши това, новите инструменти за Power Query, вградени в Excel 2016, могат да го решат.
Забележка
Ако имате Windows версия на Excel 2010 или Excel 2013, можете да изтеглите Power Query безплатно от Microsoft. За съжаление Power Query все още не е налице за Excel за Android, Excel за ios или Excel за Mac.
За да илюстрира целта: Майк Маккан и Механиката се появява в театър Алън с план за билети C. Тъй като в таблицата за търсене има четири съответстващи реда, Кейли иска четири реда с надписи Майк Маккан и Механиката, всеки с различно съвпадение от справочната таблица.
Изберете клетка в оригиналната таблица. Натиснете Ctrl + T, за да маркирате тези данни като таблица. В раздела Инструменти за таблици преименувайте таблицата от Таблица1 на Шоу. Повторете за справочната таблица, като я наречете Tickets.
Изберете клетка в таблицата Показва. От раздела Данни изберете От таблица / диапазон.
След като се отвори редакторът на Power Query, отворете падащото меню Close & Load и изберете Close and Load To….
В диалоговия прозорец Импортиране на данни изберете Само Създаване на връзка.
Отидете до таблицата с билети. Повторете стъпките, за да създадете само връзка към билети. Трябва да видите и двете връзки в екрана Заявки:
Изберете всяка празна клетка. Изберете Данни, Вземете данни, Комбинирайте заявки, Обединете.
В диалоговия прозорец Merge има шест стъпки. 3-ти и 4-ти не ми изглеждат интуитивни.
- Изберете Показва от падащото меню отгоре
- Изберете Билети от второто падащо меню.
- Кликнете върху заглавието за Билетен план в горната част, за да изберете тази колона като външен ключ в таблицата Шоу.
- Щракнете върху заглавието за Билетен план в долната част, за да изберете тази колона като ключово поле в справочната таблица.
- Отворете типа Join и изберете Inner (само съответстващи редове).
- Щракнете върху OK
Резултатите първоначално са разочароващи. Виждате всички полета от таблица 1 и колона, в която пише Таблица, Таблица, Таблица.
Щракнете върху иконата Expand в горната част на колоната Tickets.
Премахнете избора на билетен план, тъй като вече имате това поле. Останалото поле ще се нарича Tickets.Ticket Type, освен ако не премахнете отметката от Use Original Name като Prefix.
Успех! Всеки ред за всяко шоу се взривява на няколко реда.
Не съм особено доволен от сортирането на данните. Сортирането по дата кара типовете билети да се сортират по странен начин.
Гледам видео
В днешния случай видеоклипът е заснет след написването на статията. Предлагам да добавите колона с последователност към типовете билети, за да контролирате реда на сортиране.
Видео транскрипция
Научете Excel от Подкаст, Епизод 2204: Върнете всички VLOOKUPs.
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос от Нешвил Мюзик Сити. Бях там долу в Нешвил, някой е отговорен за планирането на зареждането на билети в билетна система и ето какво имаме: Имаме списък със събития - предстоящи събития - имаме дата, място и план за билети. Така че, въпреки че нещо се провежда в двореца, може да има различни планове за билети - например, може би етажът е конфигуриран, знаете ли, със седалки или може би това е само стояща стая, нали?
Така че, в зависимост от какъв тип план за билет, трябва да дойдете тук до таблицата за търсене и да намерите всички съвпадащи събития и по същество ще направим това, което наричам експлозия на VLOOKUP. Така че, ако нещо е в Hannah C, те ще слязат в Hannah C и ако има - 1, 2, 3, 4, 5, 6 - 7 елемента в Hannah C, ще имаме за да върнете седем реда - което означава, че ще трябва да вмъкнете още шест реда и да копирате тези данни надолу. Добре.
Сега изобщо няма да правим това с VLOOKUP, но вие разбирате концепцията - правим VLOOKUP и връщаме всички отговори като нови редове. Добре, така че ще взема и двете таблици и ще ги превърна в истинска таблица с Ctrl + T. Първите, наречени Таблица 1 - ужасно име, нека наречем това Събития или Шоу, нека го наречем Шоу, така - а второто, ето, ето какво научих, защото практикувах това - трябва да имаме поле за последователност тук. Така че = ROW (A1), щракнете двукратно и копирайте това надолу и след това копирайте и поставете специални стойности. Добре. Сега правим това ще превърне това в таблица - Ctrl + T и ще наречем тази Tickets.
Добре. Така че имаме представления, имаме билети. Ще отида в раздела Данни и аз съм тук в шоуто, искам да кажа, че искам да получа данните си от таблица или диапазон - между другото това е Power Query. Ако сте отново в Excel 2010 или 2013, можете да изтеглите това безплатно от Microsoft, изтеглете инструмента Power Query. Ако сте на Mac или iOS или Android, съжалявам, няма Power Query за вас. Добре, така че от таблица или диапазон … намерете някой, който има - намерете приятел, който има - компютър с Windows и ги накарайте да настроят това. Добре. Ето таблица, няма да направим нещо по този въпрос, просто Затваряне и зареждане, Затваряне и зареждане до и след това казваме „Само създаване на връзка“, перфектно. Ще дойдем тук до втората ни таблица: Вземете данни, от таблица или диапазон, ние не правим нищо за тази, Close & Load,Затворете и заредете в „Само създаване на връзка“, OK. И така, това, което имаме сега, е, че имаме връзка с първата таблица и връзка с втората таблица. Няма да обединяваме тези две, което по същество е все едно да правим VLOOKUP, или съвместна база данни, предполагам, е наистина това, което е. Комбинирайте заявки, ние ще обединим. Добре.
Сега, седем неща, които трябва да направите в този диалогов прозорец - и това е малко объркващо - ние ще изберем Shows като първата таблица; изберете билети като втора маса; изберете кое поле е общото между тях и това може да бъде множество полета - можете да щракнете с щракване - но в този случай има само един план за билети; и след това план за билети; и след това ще променим типа на присъединяване към вътрешно присъединяване със "само съответстващите редове". Добре. Сега щракнете OK и си мислите, че целият ви проблем ще бъде решен, но просто сте смачкани, защото тук са всички данни от A - изобщо не са вмъкнали нови редове - и тук, просто скучно глупаво поле, наречено Билети, което просто има Таблица, Таблица, Таблица, ха.
Но, за щастие, в горната част на това има икона за разгъване и ние ще разширим това - не е нужно да взема план, вече го имам - тип и последователност на билетите. Не искам да се нарича Tickets.TicketType, което Power Query иска да направи - затова премахвам отметката от това квадратче. Добре. В момента имаме 17 реда данни; когато щракна ОК, БАМ! Ето експлозията. И така, Майкъл Сийли и Starlighter's се появяват с всички различни видове билети, като този. Добре, и вижте тези видове билети да се появяват последователно, това е страхотно. Но Майкъл Сийли не е следващото шоу, следващото шоу е на 5 юни. Така че, когато се опитам да сортирам това по Дата - това ме влудява, не мога да обясня това. Сортирай по дата и Mike Man and the Mechanics достига до 65, но след това билетите са объркани. Те'отново на грешната последователност, и тогава затова трябваше да направя тази последователност - чувства се така. Мога да сортирам по последователност. Така че сега, 6, 5, красиво, а след това в рамките на това, билетите са правилни. И всъщност към този момент вече нямаме нужда от тази колона. Така че мога да щракнете с десния бутон на мишката и да премахнете, а след това Close & Load - този път всъщност ще затворя & Load, а не Close & Load to-- и имаме нашия резултат. Добре.
Така че преминахме от списък със събития към целия този голям списък, но ето страхотната част: обърках това, Майк Ман и Механика не е Дворец Б, негов Дворец С. Така че се връщам към оригинала горе вдясно -ръчен ъгъл за повече информация за книгата.
Добре. Теми в този епизод: Кейли в Нешвил трябва да направи VLOOKUP, за да върне всички съвпадения, като обикновено вмъква нови редове. И това е база данни за билети, нали? Така че ще нарека това VLOOKUP Explosion, защото всяко шоу ще се взриви до 16 реда. Ще използваме Power Query, за да решим това, и научих, че датата ще се появи в грешната последователност, освен ако не добавим поле Sequence към типа билет. Направете и двата набора в таблица с Ctrl + T; eename да бъдат Шоу и билети; и след това от всяка таблица, Получаване на данни, От таблица, Затваряне и зареждане, за да създадете само връзка; повторете за другата таблица; след това Data, Get Data, Combine Queries, Merge; и след това този диалогов прозорец, това е доста объркващо за мен - изберете Събития, изберете Билети, щракнете върху Тип билет и в двете, променете съединението във вътрешно съединение,щракнете върху OK и след това получавате онзи ужасно разочароващ резултат, когато това е просто колона, която казва Таблица, Таблица, Таблица, Таблица; щракнете върху иконата Expand в горната част на това; изберете поле Последователност на билети; не добавяйте префикс с името на таблицата; и можете да сортирате по дата, сортирайте по последователност; Затворете и заредете в електронната таблица. Красивото е, че ако основните данни се променят - просто опреснете и имате вашите резултати.
Сега, хей, за да изтеглите работната книга, използвана от днешното видео, посетете URL адреса долу в описанието на YouTube. Също списък там с предстоящите семинари - Бих се радвал да се видим на един от моите семинари на Power Excel.
Искам да благодаря на Кейли, че се появи в Нешвил и ми зададе този страхотен въпрос. Искам да се отбиете. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: return-all-vlookups.xlsx
Power Query продължава да ме изумява. Това е втората от тридневна поредица, където отговорът е Power Query:
- Вторник: Преобразувайте колона от Дата / Час в само дата
- Днес: Върнете всички VLOOKUPs
- Четвъртък: Създайте проучване за всеки от 1100 елемента
Имам цял плейлист в YouTube на неща, които в крайна сметка реших с Power Query.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Когато се съмнявате, използвайте КРЪГЛА функция!“
Майк Гирвин
