Кейли из Нашвилла работает над таблицей продажи билетов. Для каждого мероприятия она выбирает план продажи билетов. Этот план продажи билетов может указывать от 4 до 16 типов билетов на мероприятие. Кейли нужна формула, которая перейдет в таблицу поиска и вернет * все * совпадения, вставляя при необходимости новые строки.
Хотя у меня нет VLOOKUP, который мог бы решить эту проблему, новые инструменты Power Query, встроенные в Excel 2016, могут решить эту проблему.
Заметка
Если у вас установлена версия Excel 2010 или Excel 2013 для Windows, вы можете бесплатно загрузить Power Query с сайта Microsoft. К сожалению, Power Query пока недоступен для Excel для Android, Excel для ios или Excel для Mac.
Чтобы проиллюстрировать цель: Майк Макканн и Механика появляется в Театре Аллена с планом билета C. Поскольку в таблице поиска есть четыре совпадающих строки, Кейли хочет четыре строки, в которых написано Майк Макканн и Механика, каждая с разными совпадениями из таблица поиска.
Выберите ячейку в исходной таблице. Нажмите Ctrl + T, чтобы пометить эти данные как таблицу. На вкладке «Работа с таблицами» переименуйте таблицу с Table1 в Shows. Повторите то же самое для таблицы поиска, назвав ее «Билеты».
Выберите ячейку в таблице "Шоу". На вкладке «Данные» выберите «Из таблицы / диапазона».
После открытия редактора Power Query откройте раскрывающийся список «Закрыть и загрузить» и выберите «Закрыть и загрузить в…».
В диалоговом окне «Импорт данных» выберите «Только создать соединение».
Идите к столу с билетами. Повторите шаги, чтобы создать соединение только с билетами. Вы должны увидеть оба соединения на панели запросов:
Выберите любую пустую ячейку. Выберите данные, получить данные, объединить запросы, объединить.
Диалог слияния состоит из шести шагов. 3-й и 4-й варианты мне не кажутся интуитивно понятными.
- В раскрывающемся меню выберите "Шоу".
- Выберите билеты во втором раскрывающемся списке.
- Щелкните заголовок Ticket Plan вверху, чтобы выбрать этот столбец в качестве внешнего ключа в таблице Shows.
- Щелкните заголовок Ticket Plan внизу, чтобы выбрать этот столбец в качестве ключевого поля в таблице поиска.
- Откройте тип соединения и выберите Внутренний (только совпадающие строки).
- Нажмите ОК
Результаты изначально неутешительные. Вы видите все поля из таблицы 1 и столбец с надписью Table, Table, Table.
Щелкните значок «Развернуть» вверху столбца «Тикеты».
Снимите флажок Ticket Plan, поскольку это поле у вас уже есть. Оставшееся поле будет называться Tickets.Ticket Type, если вы не снимите флажок Использовать исходное имя в качестве префикса.
Успех! Каждая строка каждого шоу разбивается на несколько строк.
Я не очень доволен сортировкой данных. Сортировка по дате приводит к тому, что типы билетов сортируются странным образом.
Смотреть видео
В сегодняшнем случае видео было снято уже после написания статьи. Я предлагаю добавить столбец последовательности в Типы заявок, чтобы контролировать порядок сортировки.
Стенограмма видео
Изучите Excel из подкаста, эпизод 2204: вернуть все ВПР.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос от Nashville Music City. Я был там, в Нэшвилле, кто-то отвечает за планирование загрузки билетов в систему продажи билетов, и вот что у нас есть: у нас есть список событий - предстоящих событий - у нас есть дата, место проведения и план билетов. Итак, вроде бы, даже если что-то проводится во Дворце, могут быть разные планы билетов - например, может быть, пол оборудован сиденьями, или, может быть, это просто стоячая комната, верно?
Итак, в зависимости от того, какой план билета, вы должны перейти сюда к таблице поиска и найти все совпадающие события, и по сути мы собираемся сделать то, что я называю взрывом ВПР. Так что если что-то есть в Hannah C, они собираются перейти к Hannah C, а если в Hannah C есть … 1, 2, 3, 4, 5, 6-7 предметов, мы собираемся иметь чтобы вернуть семь строк - это означает, что вам нужно будет вставить еще шесть строк и скопировать эти данные. Хорошо.
Мы вообще не собираемся делать это с помощью ВПР, но вы понимаете концепцию - мы выполняем ВПР и возвращаем все ответы в виде новых строк. Хорошо, я собираюсь взять обе эти таблицы и превратить их в настоящую таблицу с помощью Ctrl + T. Первые назывались Таблица 1 - ужасное название, назовем это событиями или шоу, назовем это шоу, вот так - а второй, теперь, эй, вот что я узнал, потому что практиковал это - мы должны иметь здесь поле последовательности. Итак, = СТРОКА (A1), дважды щелкните и скопируйте это вниз, а затем скопируйте и вставьте специальные значения. Хорошо. Теперь мы сделаем то, что превратит это в таблицу - Ctrl + T, и мы назовем это билетами.
Хорошо. Итак, у нас есть концерты, у нас есть билеты. Я собираюсь перейти на вкладку «Данные», и я здесь в шоу, я хочу сказать, что хочу получить свои данные из таблицы или диапазона - между прочим, это Power Query. Если вы вернулись в Excel 2010 или 2013, вы можете бесплатно загрузить его с сайта Microsoft, загрузив инструмент Power Query. Если вы используете Mac, iOS или Android, извините, Power Query для вас не подходит. Хорошо, так что из таблицы или диапазона … найдите кого-нибудь, у кого есть … найдите друга, у которого есть … ПК с Windows, и попросите его настроить это. Хорошо. Вот таблица, мы не будем с ней ничего делать, просто закройте и загрузите, закройте и загрузите в, а затем скажите «Только создать соединение», отлично. Мы собираемся перейти к нашей второй таблице: Получить данные, из таблицы или диапазона, мы ничего не делаем с этой, Close & Load,Закройте и загрузите, «Только создать соединение», ОК. Итак, что у нас есть, это соединение с первой таблицей и соединение со второй таблицей. Мы не собираемся объединять эти два, что, по сути, похоже на выполнение ВПР, или, я думаю, соединение баз данных - это действительно то, на что это похоже. Объедините запросы, мы собираемся объединить. Хорошо.
Теперь семь вещей, которые вы должны сделать в этом диалоговом окне - и это немного сбивает с толку - мы собираемся выбрать Shows в качестве первой таблицы; в качестве второго стола выберите Билеты; выберите, какое у них общее поле, и это может быть несколько полей - вы можете щелкнуть, удерживая клавишу Control, но в этом случае есть только один тарифный план; а затем билетный план; а затем мы собираемся изменить тип соединения на внутреннее соединение с «только совпадающими строками». Хорошо. Теперь вы нажимаете ОК, и вы думаете, что вся ваша проблема будет решена, но вы просто раздавлены, потому что здесь все данные из A - они вообще не вставили никаких новых строк - и здесь, просто скучное дурацкое поле под названием Билеты, в котором есть только Таблица, Таблица, Таблица, ха
Но, к счастью, вверху находится значок «Развернуть», и мы собираемся его расширить - мне не нужно брать план, он у меня уже есть - Тип и последовательность заявки. Я не хочу, чтобы он назывался Tickets.TicketType, чего хочет Power Query, поэтому снимаю этот флажок. Хорошо. Прямо сейчас у нас есть 17 строк данных; когда я нажимаю ОК, БАМ! Взрыв. Итак, Майкл Сили и Старлайтер появляются со всеми разными типами билетов, вот так. Хорошо, и посмотрите, как эти типы билетов появляются последовательно, это здорово. Но Майкл Сили - не следующее шоу, следующее шоу состоится 5 июня. Поэтому, когда я пытаюсь отсортировать это по дате - это сводит меня с ума, я не могу этого объяснить. Сортировка по дате, Майк Мэн и механики доходит до 65, но тогда билеты все облажались. Oни'я был в неправильной последовательности, и вот почему я должен был сделать эту последовательность - мне так кажется. Я могу сортировать по последовательности. Итак, теперь, 6, 5, красиво, а затем внутри этого билеты правильные. На самом деле, на данный момент этот столбец нам больше не нужен. Итак, я могу щелкнуть правой кнопкой мыши и удалить, а затем «Закрыть и загрузить» - на этот раз я на самом деле собираюсь «Закрыть и загрузить», а не «Закрыть и загрузить» - и у нас есть результат. Хорошо.
Итак, мы перешли от списка событий к этому большому списку, но вот что самое интересное: я облажался, Майк Человек и Механика - это не Дворец Б, это Дворец С. Итак, я возвращаюсь к оригиналу вверху справа. -Ручной уголок для получения дополнительной информации о книге.
Хорошо. Темы в этом эпизоде: Кейли из Нэшвилла необходимо выполнить ВПР, чтобы получить все совпадения, обычно вставляя новые строки. И это база данных билетов, понятно? Я назову это «Взрывом ВПР», потому что каждое шоу разбивается на 16 строк. Мы собираемся использовать Power Query для решения этой проблемы, и я узнал, что дата будет отображаться в неправильной последовательности, если мы не добавим поле Sequence к типу заявки. Превратите оба набора в таблицу с помощью Ctrl + T; назовите их "Шоу и билеты"; а затем из каждой таблицы получить данные, из таблицы, закрыть и загрузить, только чтобы создать соединение; повторить для другого стола; затем данные, получить данные, объединить запросы, объединить; а затем это диалоговое окно, это меня довольно сбивает с толку - выберите «События», выберите «Билеты», нажмите «Тип билета» в обоих, измените соединение на внутреннее соединение,нажмите OK, и вы получите ужасно разочаровывающий результат, когда это просто столбец с надписью Table, Table, Table, Table; щелкните значок «Развернуть» вверху; выберите поле Ticket Sequence; не ставьте префикс имени таблицы; и вы можете сортировать по дате, сортировать по последовательности; Закройте и загрузите в электронную таблицу. Прекрасно то, что если базовые данные изменяются - просто Обновите, и вы получите свои результаты.
Теперь, привет, чтобы загрузить книгу, использованную в сегодняшнем видео, перейдите по URL-адресу в описании YouTube. Также список предстоящих семинаров - я хотел бы видеть вас на одном из моих семинаров по Power Excel.
Я хочу поблагодарить Кейли за то, что она появилась в Нэшвилле и задала мне этот замечательный вопрос. Я хочу, чтобы ты заглянул. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: return-all-vlookups.xlsx
Power Query продолжает меня удивлять. Это вторая из трехдневной серии, в которой ответ - Power Query:
- Вторник: преобразование столбца даты / времени в дату
- Сегодня: вернуть все VLOOKUP
- Четверг: создайте опрос для каждого из 1100 пунктов.
У меня есть целый список воспроизведения YouTube, который я решил с помощью Power Query.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Если есть сомнения, используйте функцию ОКРУГЛЕНИЯ!»
Майк Гирвин
