Днешният въпрос от Куентин, който беше на моя семинар в Атланта Power Excel. Куентин трябва да генерира същите 7 въпроса за проучване за всеки от 1000+ клиенти в Excel.
Както можете да видите на тази фигура, клиентите са в А. Въпросите, които трябва да се повторят, са в колона D.
Можете да разрешите това с VBA или формули, но това е Power Query седмица в, така че ще използвам страхотен трик в Power Query.
Ако искате празен ред между всяка анкета, добавете пореден номер и добавете числото 7 след последния въпрос.
Натиснете Ctrl + T и от двата набора от данни. Назовете втория набор от данни с име, което можете да запомните, нещо като Въпроси или Проучване.
От втория набор от данни използвайте Data, From Table.
Отваря се редакторът на Power Query. От раздела Начало изберете падащото меню Затваряне и зареждане и изберете Затваряне и зареждане в …. В следващия диалогов прозорец изберете Само Създаване на връзка.
Сега сте отново в Excel. Изберете която и да е клетка в таблицата за клиенти в колона А. Данни, от таблица. След като се отвори редакторът на заявки, щракнете върху раздела Добавяне на колона в лентата и след това изберете Персонализирана колона. Формулата е =#"Questions"(включително # и кавичките).
В редактора се появява нова колона със стойността Таблица, повтаряща се във всеки ред. Щракнете върху иконата Разгъване в заглавката на колоната.
Изберете и двете полета в таблицата. От раздела Начало изберете Затвори и зареди.
Ще се появи нов работен лист със 7 повторени въпроса за всеки от 1000+ клиенти.
Гледам видео
Видео транскрипция
Научете Excel от подкаст епизод 2205: Експлозия на анкета за заем.
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Точно вчера в епизод 2204 именно Кейли от Нашвил трябваше да направи експлозия VLOOKUP - за всеки елемент тук в колона D имахме съвпадащ куп елементи в колона G и трябваше да ги взривим. Така че, ако Дворец С имаше 8 елемента, щяхме да получим 8 реда.
Сега, днес имаме Куентин. Сега Куентин беше на семинара ми в Атланта, но всъщност той е от Флорида и Куентин има почти 1000 клиенти тук - добре, повече от 1000 клиенти - в колона А и за всеки клиент той трябва да създаде това проучване - - това проучване на 1, 2, 3, 4, 5, 6 въпроса. И това, което ще направя тук, е да добавя номер на последователност само с числата от 1 до 7, така че по този начин мога да създам хубав празен ред между тях. Ще направя и двата набора от данни в таблица; така че ние се опитваме тези 7 реда да се взривят за всеки един от тези 1000 клиенти. Това е целта.
Сега мога да направя това с VPA; Мога да направя това с формули; но това е нещо като "Power Query Week" тук, ние сме в ход на това е нашият трети пореден пример на Power Query, така че ще използвам Power Query. Ще направя това ляво в маса. Ще бъда много внимателен, за да назова това не Таблица 1. Ще му дам име. По-късно ще трябва да използваме повторно това име, така че ще го нарека Въпроси - така. И тогава това ще бъде Таблица 2, но ще преименувам това на Клиенти - не толкова важно, че да го преименувам, защото това е второто, което трябва да има името. И така, ще изберем това; Данни; и ще кажем От таблица / диапазон. Получаване и трансформиране на данни - това е известно като Power Query. Вграден е в Excel 2016. Ако имате 2010 или 2013, под Windows,не Mac, не iOS, не Android, можете да изтеглите Power Query безплатно от Microsoft.
И така, ще получим данни от таблица / диапазон; ето нашата таблица - няма да направим нищо, просто затворете и заредете; Затваряне и зареждане към; само Създаване на връзка; добре, и вижте, името на тази заявка е Въпроси. Използва същото име като тук. И тогава се връщаме към този, и, Данни; От таблица / диапазон; така че, има списък с нашите 1000 или повече клиенти.
Ей сега, ето вик на Мигел Ескобар, приятелю, който е съавтор на M Is For (DATA) MONKEY). Ще поставя линк към това във видеото - страхотна книга за Power Query - помогна ми с това. Ще поставим чисто нова персонализирана колона, а формулата за персонализирана колона е точно тук: = # "името на заявката". Никога не бих разбрал това без Мигел, така че благодаря на Мигел за това.
И когато щракна OK, да, изглежда не работи - просто получаваме маса, маса, маса, но точно това имахме вчера с Кейли и билетите. И всичко, което трябва да направя, е да разширя това и всъщност ще кажа, че вероятно нямам нужда от последователността … е, нека я сложим за всеки случай. Можем да го извадим, след като го видим. В момента имаме 1000 реда, а сега имаме 7000 реда - красиви. Сега виждам, че се появява в Sequence, така че нямам нужда от това. Щраквам с десния бутон и премахвам само тази колона. И тогава мога да се прибера вкъщи; Затваряне и зареждане; и BAM! - сега трябва да имаме повече от 7000 реда с 6 въпроса и празно място за всеки клиент. Куентин беше развълнуван от този в семинара. Готин, готин трик - избягва VBA, избягва цял куп формули, използвайки Index,и подобни неща - чудесен начин.
Хей, днес, позволете ми да ви изпратя с M Is For (DATA) MONKEY. Кен Пулс и Мигел Ескобар написаха най-великата книга за Power Query. Обичам тази книга; след 2 часа ще станете професионалист с тази книга.
Добре, приключване днес - Куентин трябва да генерира идентично проучване за 1000 различни клиенти. Има 6 или 7 или 8 въпроса за всеки клиент. Сега бихме могли да направим това с VBA или макро, но тъй като тук работим с Power Query, нека направим Power Query. Добавих допълнителен празен въпрос към Въпросите; Добавих пореден номер, за да съм сигурен, че празното ще остане там; направете клиентите в маса; направете въпросите в таблица; наистина е важно да назовете Въпроси нещо, което можете да запомните - аз го нарекох „Въпроси“. Добавете въпросите към Power Query, Само като връзка; и след това, докато добавяте клиентите към Power Query, създайте нова персонализирана колона, където формулата е: # "името на първата заявка" и след това разгънете тази колона в редактора на Power Query; Близо &Заредете обратно в електронната таблица и готово. Невероятен трик - обожавам Power Query - най-великото нещо, което се случва с Excel за 20 години.
Искам да благодаря на Куентин, че се появи на моя семинар. Бил е на моя семинар няколко пъти преди това - страхотен човек. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: заем-проучване-експлозия.xlsx
Power Query продължава да ме изумява. Вижте книгата M is for Data Monkey, за да научите повече Power Query.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Можете да правите всичко с АГРЕГАТ, освен да го разберете.“
Лиъм Бастик
