Уникайте VLOOKUP, використовуючи модель даних. Отже, у вас є дві таблиці, які потрібно об’єднати з VLOOKUP, перш ніж ви зможете створити зведену таблицю. Якщо у вас є Excel 2013 або новіша версія на ПК з Windows, тепер ви можете зробити це просто і легко.
Скажімо, у вас є набір даних із інформацією про товар, клієнтів та продажі.
ІТ-відділ забув помістити туди сектор. Ось таблиця пошуку, яка відображає клієнта по сектору. Час для ПЕРЕГЛЯДУ, так?
Немає необхідності робити VLOOKUP для приєднання до цих наборів даних, якщо у вас Excel 2013 або Excel 2016. Обидві ці версії Excel включили движок Power Pivot в основний Excel. (Ви також можете зробити це за допомогою надбудови Power Pivot для Excel 2010, але є кілька додаткових кроків.)
І в вихідному наборі даних, і в таблиці пошуку використовуйте Домашня сторінка, Формат як таблиця. На вкладці Інструменти таблиці перейменуйте таблицю з таблиці1 на щось значуще. Я використовував дані та сектори.
Виберіть одну комірку в таблиці даних. Виберіть Вставити, зведену таблицю. Починаючи з Excel 2013, є додаткове поле Додати ці дані до моделі даних, яке слід вибрати перед натисканням кнопки OK.
З'явиться список полів зведеної таблиці з полями з таблиці даних. Виберіть Дохід. Оскільки ви використовуєте модель даних, угорі списку з’являється новий рядок, що пропонує Активний або Усі. Натисніть кнопку Усі.
Дивно, але список полів зведеної таблиці пропонує всі інші таблиці у книзі. Це новаторсько. Ви ще не зробили VLOOKUP. Розгорніть таблицю Сектори та оберіть Сектор. Дві речі попереджають вас про наявність проблеми.
Спочатку зведена таблиця з’являється з однаковим номером у всіх комірках.
Можливо, більш тонке попередження - жовте поле з'являється у верхній частині списку полів зведеної таблиці, що вказує на те, що вам потрібно створити зв'язок. Виберіть Створити. (Якщо ви перебуваєте в Excel 2010 або 2016, пощастіть з функцією автоматичного виявлення.)
У діалоговому вікні Створення зв’язків у вас є чотири спадні меню. Виберіть Дані в таблиці, Клієнт у стовпці (Іноземні) та Сектори у відповідній таблиці. Power Pivot автоматично заповнить відповідний стовпець у відповідній колонці (основний). Клацніть OK.
Отримана зведена таблиця - це змішання вихідних даних та таблиці пошуку. Не потрібні VLOOKUP.
Переглянути відео
- Починаючи з Excel 2013, діалогове вікно зведеної таблиці пропонує модель даних
- Це кодове слово для Power Pivot Engine
- Щоб використовувати модель даних, складіть таблицю Ctrl + T з кожної таблиці книги
- Побудуйте зведену таблицю з першої таблиці
- У списку полів зведеної таблиці перейдіть з Активний на Усі
- Виберіть поле з таблиці пошуку
- Або створіть зв'язок, або Автовизначення
- Автовизначення не було в 2013 році
- Дякуємо Коліну Майклу та Алехандро Кікено за те, що вони загалом запропонували Power Pivot.
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2014 - Усуньте ПЕРЕГЛЯД!
Підкастируючи всю цю книгу, натисніть на "i" у верхньому правому куті списку відтворення!
Ей, ласкаво просимо до мережі, я Білл Джелен, це насправді називається Eliminate VLOOKUP за допомогою моделі даних! Тепер я перепрошую, це Excel 2013 та новіші версії. Якщо ви повернулися до Excel 2010, вам доведеться завантажити надбудову Power Pivot, яка, звичайно, є безкоштовною ще в 2010 році. Основний набір даних, тут є поле Клієнт, і тоді у мене є маленька таблиця, яка відображає клієнта по секторах, мені потрібно створити загальний дохід за секторами, так? Це VLOOKUP, просто зробіть VLOOKUP, але ей, завдяки Excel 2013, нам не потрібно робити VLOOKUP! Я зробив обидва ці дані в таблиці, і в «Інструменти таблиць», «Дизайн» я перейменовую таблиці, я називаю цей «Сектори» і називаю цей «Дані», щоб зробити його таблицею, просто виберіть одну клітинку, натисніть Ctrl + Т. Отже, якщо у нас є деякі заголовки і деякі цифри, коли ви натискаєте Ctrl + T,вони запитують "Де дані для вашої таблиці?", у моїй таблиці є заголовки, а потім вони називають її Таблиця3, а ви називаєте це інакше. Добре, саме так я створив ці дві таблиці, я збираюся позбутися цієї таблиці, добре.
Отже, щоб цей фокус спрацював, усі дані повинні містити таблиці. Ми переходимо на вкладку Вставка, вибираємо зведену таблицю і прямо тут, унизу, додаємо ці дані до моделі даних. Це звучить дуже нешкідливо, так? Немає нічого подібного до спалаху, який говорить: "Гей, це дозволить вам робити дивовижні речі!" І те, про що вони тут говорять, чого вони намагаються не сказати, - це, до речі, кожна копія 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 назад, добре? Я повинен показати вам, як виглядає CREATE, але коли я натискаю цю кнопку CREATE, о так, це все, добре, добре. Отже, з нашої першої таблиці Дані, у мене є поле, яке називається Клієнт, з відповідних секторів таблиці, у мене є поле, що називається Клієнт, і тоді ти натискаєш OK, добре Але дозвольте мені просто показати вам, наскільки крутий AutoDetect, якщо ви опинитесь у 2016 році, там вони зрозуміли, наскільки це чудово, правда? Вам не потрібно турбуватися про VLOOKUP, і наприкінці кома падає. Якщо VLOOKUP болить голову, вам сподобається модель даних. Взяв ці дві таблиці, об’єднав їх, знаєте, як би це зробив Access, я думаю, і створив зведену таблицю, абсолютно дивовижну.Тож перевірте модель даних наступного разу, коли вам доведеться робити ПЕРЕГЛЯД між двома таблицями. Ну, це та всі інші 40 порад є в книзі. Клацніть на “i” у верхньому правому куті. Ви можете придбати книгу, мати повне перехресне посилання на цілу серію відео, увесь серпень, увесь вересень, чорт візьміть, ми можемо навіть перенестись на жовтень, щоб все зробити.
Добре, підсумуйте сьогодні: починаючи з Excel 2013, діалогове вікно зведеної таблиці пропонує щось, що називається модель даних, це кодове слово для механізму Power Pivot. Перш ніж створювати зведені таблиці, виконайте Ctrl + T, щоб створити таблицю з кожної книги, я витратив додатковий час, щоб назвати кожну з них. Створіть зведену таблицю з першої таблиці, а потім у списку полів підніміться вгору та перейдіть з Активний на Усі. Виберіть поле з таблиці підстановки, і тоді воно попередить вас, що вам або потрібно створити зв'язок, або Автовизначення, у 2013 році ви повинні натиснути СТВОРИТИ. Але це те, що, 4 клацання, щоб створити його, 5, якщо порахувати кнопку ОК, це дійсно, дуже просто зробити.
Гаразд, Колін, Майкл та Алехандро Кікено запропонували Power Pivot загалом для книг, завдяки їм, завдяки вам, що завітали, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2014.xlsx
