Сегодняшний вопрос от Квентина, который был на моем семинаре по Power Excel в Атланте. Квентин должен создать в Excel 7 одинаковых вопросов для каждого из более чем 1000 клиентов.
Как вы можете видеть на этом рисунке, клиенты находятся в A. Вопросы, которые нужно повторить, находятся в столбце D.
Вы можете решить эту проблему с помощью VBA или формул, но это неделя Power Query, поэтому я собираюсь использовать классный трюк в Power Query.
Если вам нужна пустая строка между опросами, добавьте порядковый номер и цифру 7 после последнего вопроса.
Нажмите Ctrl + T из обоих наборов данных. Назовите второй набор данных именем, которое вы можете запомнить, например, Вопросы или Опрос.
Из второго набора данных используйте Данные, Из таблицы.
Откроется редактор Power Query. На вкладке «Главная» выберите раскрывающийся список «Закрыть и загрузить» и выберите «Закрыть и загрузить в…». В следующем диалоговом окне выберите Only Create a Connection.
Вы вернулись в Excel. Выберите любую ячейку в таблице клиентов в столбце A. Данные, из таблицы. После открытия редактора запросов щелкните вкладку «Добавить столбец» на ленте и выберите «Пользовательский столбец». Формула =#"Questions"(включая # и кавычки).
В редакторе появится новый столбец, в каждой строке которого будет повторяться таблица значений. Щелкните значок «Развернуть» в заголовке столбца.
Выберите оба поля в таблице. На вкладке «Главная» выберите «Закрыть и загрузить».
Появится новый рабочий лист с 7 вопросами, повторенными для каждого из более чем 1000 клиентов.
Смотреть видео
Стенограмма видео
Учитесь Excel из эпизода подкаста 2205: Взрывное исследование ссуд.
Привет, добро пожаловать обратно в netcast, я Билл Джелен. Так вот, буквально вчера в эпизоде 2204, именно Кейли из Нэшвилла пришлось провести взрыв ВПР - для каждого элемента здесь, в столбце D, у нас была соответствующая группа элементов в столбце G, и нам нужно было взорвать их. Итак, если бы во дворце C было 8 элементов, мы бы получили 8 строк.
Итак, сегодня у нас есть Квентин. Квентин был на моем семинаре в Атланте, но на самом деле он из Флориды, и у Квентина здесь почти 1000 клиентов - ну, более 1000 клиентов - в столбце A, и для каждого клиента ему нужно создать этот опрос - - это опрос из 1, 2, 3, 4, 5, 6 вопросов. И что я собираюсь сделать здесь, я собираюсь добавить порядковый номер только с числами от 1 до 7, чтобы таким образом я мог создать красивую пустую строку между ними. Я собираюсь превратить оба этих набора данных в таблицу; Итак, мы пытаемся развернуть эти 7 строк для каждой из этих 1000 клиентов. Это цель.
Теперь я могу сделать это с помощью VPA; Я могу делать это с помощью формул; но здесь это что-то вроде «недели Power Query», мы запускаем это наш третий пример Power Query подряд, поэтому я собираюсь использовать Power Query. Я собираюсь превратить этот левый в таблицу. Я буду очень осторожен и назову ее, а не Таблицу 1. Я дам ей имя. Позже нам придется повторно использовать это имя, поэтому я назову его Вопросы - вот так. И тогда это будет Таблица 2, но я собираюсь переименовать ее в «Клиенты» - не так важно, чтобы я переименовал эту, потому что это вторая, у которой должно быть имя. Итак, мы собираемся выбрать это; Данные; и мы собираемся сказать From Table / Range. Получение и преобразование данных - это называется Power Query. Он встроен в Excel 2016. Если у вас 2010 или 2013, в Windowsни Mac, ни iOS, ни Android, вы можете бесплатно скачать Power Query от Microsoft.
Итак, мы собираемся получить данные из таблицы / диапазона; вот наша таблица - мы не собираемся ничего делать с ней, только Close и Load; Закрыть и загрузить в; только создать соединение; Хорошо, и видите, имя этого запроса - Вопросы. Он использует то же имя, что и здесь. И затем мы вернемся к этому, и к данным; Из таблицы / диапазона; Итак, есть список наших 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 продолжает меня удивлять. Прочтите книгу M для Data Monkey, чтобы узнать больше о Power Query.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«С AGGREGATE вы можете делать все, что угодно, кроме понимания».
Лиам Бастик
