Избегайте ВПР с использованием модели данных. Итак, у вас есть две таблицы, которые нужно объединить с помощью функции ВПР, прежде чем вы сможете создать сводную таблицу. Если у вас есть Excel 2013 или новее на ПК с Windows, теперь вы можете сделать это просто и легко.
Допустим, у вас есть набор данных с информацией о продуктах, клиентах и продажах.
ИТ-отдел забыл поместить туда сектор. Вот таблица поиска, которая отображает клиентов в сектор. Время для ВПР, верно?
Если у вас есть Excel 2013 или Excel 2016, нет необходимости выполнять ВПР для объединения этих наборов данных. Обе эти версии Excel включают механизм Power Pivot в ядро Excel. (Вы также можете сделать это с помощью надстройки Power Pivot для Excel 2010, но есть несколько дополнительных шагов.)
И в исходном наборе данных, и в таблице поиска используйте Home, Format as Table. На вкладке Table Tools переименуйте таблицу из Table1 во что-нибудь значимое. Я использовал данные и секторы.
Выберите одну ячейку в таблице данных. Выберите «Вставка», «Сводная таблица». Начиная с Excel 2013, есть дополнительное поле Добавить эти данные в модель данных, которое следует выбрать перед нажатием кнопки ОК.
Появится список полей сводной таблицы с полями из таблицы данных. Выберите "Доход". Поскольку вы используете модель данных, в верхней части списка появляется новая строка, предлагающая Активные или Все. Щелкните Все.
Удивительно, но список полей сводной таблицы предлагает все остальные таблицы в книге. Это новаторский. Вы еще не сделали ВПР. Разверните таблицу секторов и выберите сектор. Две вещи предупреждают вас о проблеме.
Во-первых, сводная таблица отображается с одинаковым номером во всех ячейках.
Возможно, более незаметным предупреждением является появление желтого поля в верхней части списка полей сводной таблицы, указывающего на то, что вам необходимо создать связь. Выберите "Создать". (Если вы работаете в Excel 2010 или 2016, попробуйте автоматическое определение.)
В диалоговом окне «Создание отношения» есть четыре раскрывающихся меню. Выберите Данные в таблице, Клиент в столбце (иностранный) и Секторы в связанной таблице. Power Pivot автоматически заполнит соответствующий столбец под Связанным столбцом (основным). Щелкните ОК.
Результирующая сводная таблица представляет собой гибрид исходных данных и таблицы поиска. ВПР не требуется.
Смотреть видео
- Начиная с Excel 2013, диалоговое окно сводной таблицы предлагает модель данных.
- Это кодовое слово для Power Pivot Engine.
- Чтобы использовать модель данных, создайте таблицу Ctrl + T из каждой таблицы в книге.
- Создайте сводную таблицу из первой таблицы
- В списке полей сводной таблицы измените значение с Активное на Все.
- Выберите поле из справочной таблицы
- Либо создайте связь, либо автоматически определите
- Автоопределения не было в 2013 году
- Спасибо Колину Майклу и Алехандро Кисено за то, что они предложили Power Pivot в целом.
Стенограмма видео
Изучите Excel из подкаста, выпуск 2014 - Устранение ВПР!
Подкастируя всю эту книгу, нажмите «i» в правом верхнем углу списка воспроизведения!
Привет, добро пожаловать обратно в сетевую трансляцию, я Билл Джелен, на самом деле это называется Устранение ВПР с помощью модели данных! Приношу свои извинения, это Excel 2013 и новее. Если вы вернулись в Excel 2010, вам нужно загрузить надстройку Power Pivot, которая, конечно же, бесплатна еще в 2010 году. Итак, у нас есть основной набор данных, здесь есть поле «Клиент», а затем у меня есть небольшая таблица, которая отображает клиентов по секторам. Мне нужно создать общий доход по секторам, верно? Это ВПР, просто сделайте ВПР, но, спасибо, Excel 2013, нам не нужно делать ВПР! Я превратил оба из них в таблицу, и в Инструменты таблицы, Дизайн, я переименовал таблицы, я назвал этот сектор Секторами, и я назвал этот один Данные, чтобы превратить его в таблицу, просто выберите одну ячейку, нажмите Ctrl + Т. Итак, если у нас есть заголовки и числа, когда вы нажимаете Ctrl + T,они спрашивают: «Где данные для вашей таблицы?», у моей таблицы есть заголовки, и тогда они называют ее Table3, вы называете это как-то иначе. Хорошо, вот как я создал эти две таблицы, я собираюсь избавиться от этой таблицы, хорошо.
Итак, чтобы этот трюк сработал, все данные должны находиться в таблицах. Мы переходим на вкладку «Вставка», выбираем «Сводная таблица» и прямо здесь, внизу, добавляем эти данные в модель данных. Звучит очень безобидно, правда? Нет ничего лучше мигающей точки, которая говорит: «Эй, это позволит тебе творить удивительные вещи!» И вот что они здесь говорят, они стараются не сказать, что … Кстати, за каждой копией Excel 2013 стоит механизм Power Pivot. Вы знаете, если вы пользуетесь Office 365, вы платите 10 долларов в месяц, и они хотят, чтобы вы платили 12 или 15 долларов в месяц, чтобы получить Power Pivot, дополнительные два или пять долларов. Ну, эй, тсс, не говори, у вас на самом деле большая часть Power Pivot уже есть в Excel 2013. Хорошо, поэтому я нажимаю ОК, требуется немного больше времени, чтобы загрузить модель данных, хорошо, но это нормально, и сразу Вот,в полях сводной таблицы у меня есть список всех полей. Итак, я хочу показать выручку, конечно, но разница здесь в Active and All. Когда я выбираю «Все», я получаю все таблицы в книге. Хорошо, я перехожу к секторам и сказал, что хочу поместить сектор в область строк. Теперь, изначально отчет будет неправильным, посмотрите на 6,7 миллиона полностью вниз, и в этом желтом предупреждении будет сказано, что вам нужно наладить отношения.и это желтое предупреждение здесь говорит о том, что вы должны создать отношения.и это желтое предупреждение здесь говорит о том, что вы должны создать отношения.
Хорошо, теперь, в 2010 году с Power Pivot, это было бы просто, он предлагал AutoDetect, в 2013 году они убрали AutoDetect, а в 2016 году они вернули AutoDetect, хорошо? Я должен показать вам, как выглядит СОЗДАТЬ, но когда я нажимаю эту кнопку СОЗДАТЬ, о да, вот и все, хорошо, хорошо. Итак, из нашей первой таблицы Data у меня есть поле под названием Customer, из связанной таблицы Sectors у меня есть поле под названием Customer, а затем вы нажимаете ОК, хорошо. Но позвольте мне просто показать вам, насколько крутой AutoDetect, если вы оказались в 2016 году, там они разобрались, как это круто, правда? Вам не нужно беспокоиться о ВПР, запятая ставится в конце. Если от ВПР болит голова, вам понравится модель данных. Взял эти две таблицы, соединил их вместе, как, наверное, сделал бы Access, и создал сводную таблицу, совершенно потрясающе.Поэтому проверьте модель данных в следующий раз, когда вам нужно будет выполнить ВПР между двумя таблицами. Что ж, этот и все остальные 40 советов есть в книге. Нажмите на букву «i» в правом верхнем углу. Вы можете купить книгу, получить полную перекрестную ссылку на всю серию видеороликов, весь август, весь сентябрь, черт возьми, мы можем даже перенести на октябрь, чтобы все было сделано.
Хорошо, напомним сегодня: начиная с Excel 2013, диалоговое окно сводной таблицы предлагает нечто, называемое моделью данных, это кодовое слово для механизма Power Pivot. Прежде чем создавать сводные таблицы, нажмите Ctrl + T, чтобы создать таблицу из каждой книги, я потратил дополнительное время, чтобы назвать каждую из них. Создайте сводную таблицу из первой таблицы, а затем в списке полей перейдите вверх и измените значение с Активное на Все. Выберите поле из справочной таблицы, и тогда оно предупредит вас, что вам нужно либо создать связь, либо AutoDetect, в 2013 вам нужно нажать CREATE. Но это что, 4 щелчка, чтобы создать, 5, если считать кнопку ОК, так что это действительно очень легко сделать.
Хорошо, Колин, Майкл и Алехандро Кисено предложили Power Pivot в целом для книг, спасибо им, спасибо, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2014.xlsx
