Сьогоднішнє запитання від Квентіна, який був на моєму семінарі в Атланті Power Excel. Квентін повинен створити ті самі 7 запитань для кожного з 1000+ клієнтів в Excel.
Як ви можете бачити на цьому малюнку, клієнти знаходяться в А. Питання, які потрібно повторити, знаходяться в графі D.
Ви можете вирішити це за допомогою VBA або формул, але це тиждень Power Query, тому я збираюся скористатися крутим трюком у Power Query.
Якщо ви хочете порожній рядок між кожним опитуванням, додайте порядковий номер і додайте число 7 після останнього питання.
Натисніть Ctrl + T в обох наборах даних. Назвіть другий набір даних іменем, яке ви пам’ятаєте, щось на зразок запитань чи опитування.
З другого набору даних використовуйте Дані, З таблиці.
Відкриється редактор Power Query. На вкладці Домашня сторінка виберіть спадне меню Закрити та завантажити та виберіть Закрити та завантажити в…. У наступному діалоговому вікні виберіть Тільки створити підключення.
Ви повернулися в Excel. Виберіть будь-яку комірку таблиці клієнтів у стовпці А. Дані з таблиці. Після відкриття редактора запитів натисніть на стрічці вкладку Додати стовпець, а потім виберіть Власний стовпець. Формула така =#"Questions"(включаючи # та лапки).
У редакторі з’являється новий стовпець із значенням Таблиця, що повторюється в кожному рядку. Клацніть піктограму Розгорнути у заголовку стовпця.
Виберіть обидва поля в таблиці. На вкладці Домашня сторінка виберіть Закрити та завантажити.
З’явиться новий аркуш із 7 запитаннями, що повторюються для кожного з 1000+ клієнтів.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту Епізод 2205: Вибух опитування позики.
Привіт, ласкаво просимо до мережі, я Білл Джелен. Щойно вчора в епізоді 2204, саме Кейлі з Нешвілу довелося здійснити вибух VLOOKUP - для кожного предмета тут у колонці D у нас була відповідна купа предметів у колонці G, і вони мали вибухнути. Отже, якби в Палаці С було 8 предметів, ми отримали б 8 рядків.
Зараз, сьогодні, у нас є Квентін. Зараз Квентін був на моєму семінарі в Атланті, але насправді він із Флориди, і у Квентіна тут майже 1000 клієнтів - ну, більше 1000 клієнтів - у графі A, і для кожного клієнта йому потрібно створити це опитування - - це опитування з 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 - допомогла мені в цьому. Ми збираємось вставити новеньку спеціальну колонку, а формула спеціальної колонки - ось тут: = # "назва запиту". Я ніколи б цього не зрозумів без Мігеля, тож дякую Мігелю за це.
І коли я натискаю кнопку ОК, так, схоже, це не спрацювало - ми просто отримуємо стіл, стіл, стіл, але це саме те, що ми мали вчора з Кейлі та квитками. І все, що мені потрібно зробити, це розширити це, і я фактично збираюся сказати, що мені, мабуть, не потрібна Послідовність … ну, давайте покладемо її про всяк випадок. Ми можемо вийняти його після того, як побачимо. Зараз у нас 1000 рядків, а зараз 7000 рядків - красиво. Зараз я бачу, що він з’являється у послідовності, тому мені це не потрібно. Я клацну правою кнопкою миші та видалю лише одну колонку. І тоді я можу додому; Закрити та завантажити; і БАМ! - тепер у нас повинно бути більше 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 продовжує дивувати мене. Ознайомтесь із книгою «М для мавпи даних», щоб дізнатися більше про Power Query.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"З AGGREGATE ви можете робити що завгодно, крім як зрозуміти це."
Ліам Бастік
