Избягвайте VLOOKUP, използвайки модела за данни. И така, имате две таблици, които трябва да се свържат с VLOOKUP, преди да можете да направите обобщена таблица. Ако имате Excel 2013 или по-нов на компютър с Windows, вече можете да направите това лесно и лесно.
Кажете, че имате набор от данни с информация за продукти, клиенти и продажби.
ИТ отделът забрави да постави сектор там. Ето справочна таблица, която картографира клиента към сектор. Време е за VLOOKUP, нали?
Не е необходимо да правите VLOOKUP, за да се присъедините към тези набори от данни, ако имате Excel 2013 или Excel 2016. И двете версии на Excel са включили двигателя Power Pivot в основния Excel. (Можете да направите това и с добавката Power Pivot за Excel 2010, но има няколко допълнителни стъпки.)
И в оригиналния набор от данни, и в справочната таблица използвайте Начало, Форматиране като таблица. В раздела Инструменти за таблици преименувайте таблицата от Таблица1 на нещо смислено. Използвал съм данни и сектори.
Изберете една клетка в таблицата с данни. Изберете Вмъкване, Осева таблица. От Excel 2013 има допълнително поле Добавяне на тези данни към модела на данни, което трябва да изберете, преди да щракнете върху OK.
Появява се списъкът с полетата на обобщената таблица с полетата от таблицата с данни. Изберете Приходи. Тъй като използвате модела за данни, в горната част на списъка се появява нов ред, предлагащ Active или All. Щракнете върху Всички.
Изненадващо, списъкът с полета на обобщената таблица предлага всички останали таблици в работната книга. Това е новаторско. Още не сте направили VLOOKUP. Разгънете таблицата Сектори и изберете Сектор. Случват се две неща, които ви предупреждават, че има проблем.
Първо, обобщената таблица се появява със същия номер във всички клетки.
Може би по-финото предупреждение е, че в горната част на списъка с полета на обобщената таблица се появява жълто поле, което показва, че трябва да създадете връзка. Изберете Създаване. (Ако сте в Excel 2010 или 2016, вземете късмета си с Auto-Detect.)
В диалоговия прозорец Създаване на връзка имате четири падащи менюта. Изберете Данни под Таблица, Клиент под Колона (Чуждестранни) и Сектори под Свързана таблица. Power Pivot автоматично ще попълни съответстващата колона под Свързана колона (Основна). Щракнете върху OK.
Получената обобщена таблица представлява смесване на оригиналните данни и справочната таблица. Не се изискват VLOOKUP.
Гледам видео
- Започвайки от Excel 2013, диалоговият прозорец Pivot Table предлага модела на данни
- Това е кодовата дума за Power Pivot Engine
- За да използвате модела на данни, направете таблица Ctrl + T от всяка таблица в работната книга
- Изградете обобщена таблица от първата таблица
- В списъка с полета на обобщената таблица променете от Активен на Всички
- Изберете поле от справочната таблица
- Или създайте връзката, или Автоматично откриване
- Auto-Detect не беше там през 2013 г.
- Благодарим на Колин Майкъл и Алехандро Кичено за това, че са предложили Power Pivot като цяло.
Видео транскрипция
Научете Excel от подкаст, епизод 2014 - Премахнете VLOOKUP!
Подкастирайки цялата тази книга, кликнете върху „i“ в горния десен ъгъл за плейлиста!
Хей, добре дошли отново в неткаста, аз съм Бил Джелен, това всъщност се нарича Премахване на VLOOKUP с модела на данни! Сега се извинявам, това е Excel 2013 и по-нови, ако сте отново в Excel 2010, трябва да изтеглите добавката Power Pivot, която разбира се е безплатна през 2010 г. Така че това, което имаме тук, е, че имаме основен набор от данни, тук има поле за клиент и тогава имам малка таблица, която картографира клиента към сектор, трябва да създам общи приходи по сектори, нали? Това е VLOOKUP, просто направете VLOOKUP, но хей, благодарение на Excel 2013, не е нужно да правим VLOOKUP! Направих и двете в таблица и в Инструменти за таблици, Дизайн, преименувам таблиците, наричам този Сектори и наричам този Данни, за да го превърна в таблица, просто изберете една клетка, натиснете Ctrl + Т. Така че, ако имаме заглавия и числа, когато натиснете Ctrl + T,те питат „Къде са данните за вашата таблица?“, Моята таблица има заглавки и след това те я наричат Table3, вие я наричате по друг начин. Добре, така създадох тези две таблици, ще се отърва от тази таблица, добре.
Така че, за да работи този трик, всички данни трябва да се съдържат в таблици. Отиваме в раздела Вмъкване, избираме обобщена таблица и точно тук долу, добавяме тези данни към модела на данни. Това звучи много безобидно, нали? Няма нищо като мигаща точка, която казва „Хей, ще ви позволи да правите невероятни неща!“ И това, което те казват тук, това, което се опитват да не кажат, е, че … Между другото, всяко копие на Excel 2013 има Power Pivot двигател зад себе си. Знаете ли, ако сте в Office 365, плащате $ 10 на месец и те искат да плащате $ 12 или $ 15 на месец, за да получите Power Pivot, допълнителните два или пет долара. Е, хей, шш, не казвайте, вие всъщност имате по-голямата част от Power Pivot вече в Excel 2013. Добре, така че щраквам ОК, отнема малко повече време за зареждане на модела на данни, добре, но това е ОК, и точно над тук,в полетата на обобщената таблица имам списък с всички полета. И така, искам да покажа приходи, разбира се, но това, което е различно, е тук с Active и All. Когато избера Всички, получавам всички таблици в работната книга. Добре, затова отивам в секторите и казах, че искам да поставя сектор в областта на редовете. Сега първоначално докладът ще бъде грешен, вижте 6,7 милиона докрай и това жълто предупреждение тук ще каже, че трябва да създадете връзка.и това жълто предупреждение тук ще каже, че трябва да създадете връзка.и това жълто предупреждение тук ще каже, че трябва да създадете връзка.
Добре сега, през 2010 г. с Power Pivot, той просто предлагаше AutoDetect, през 2013 г. те извадиха AutoDetect, а през 2016 г. върнаха AutoDetect, нали? Трябва да ви покажа как изглежда CREATE, но когато щракна върху този бутон CREATE, о, да, това е, добре, добре. Така че от първата ни таблица Данни имам поле, наречено Клиент, от свързаните таблици Сектори, имам поле, наречено Клиент и след това щракнете върху OK, добре. Но нека само да ви покажа колко готин е AutoDetect, ако случайно сте през 2016 г., те го разбраха, колко страхотно е това, нали? Не е нужно да се притеснявате за VLOOKUP и запетаята пада в края, ако VLOOKUP ви нарани главата, ще ви хареса Моделът за данни. Взех тези две таблици, обединих ги заедно, знаете ли, както щеше да направи Access, предполагам, и създаде обобщена таблица, абсолютно невероятно.Така че проверете модела на данни следващия път, когато трябва да направите VLOOKUP между две таблици. Ами този и всички останали 40 съвета са в книгата, щракнете върху „i“ в горния десен ъгъл. Можете да си купите книгата, да имате пълна кръстосана препратка към цялата тази поредица от видеоклипове, цял август, цял септември, по дяволите, може дори да пренесем октомври, за да свършим цялото нещо.
Добре, резюмирайте днес: започвайки от Excel 2013, диалоговият прозорец на обобщената таблица предлага нещо, наречено Модел на данни, това е кодовата дума за двигателя Power Pivot. Преди да създадете своите Pivot таблици, направете Ctrl + T, за да направите таблица от всяка работна книга, отделих допълнително време, за да дам име на всяка от тях. Изградете обобщена таблица от първата таблица и след това в списъка с полета се качете нагоре и променете от Активно на Всички. Изберете поле от таблицата за търсене и след това то ще ви предупреди, че или трябва да създадете връзка, или AutoDetect, през 2013 г., трябва да щракнете СЪЗДАВАНЕ. Но това е, 4 щраквания, за да го създадете, 5 ако преброите бутона ОК, така че наистина, много лесно да се направи.
Добре, Колин, Майкъл и Алехандро Кицено предложиха Power Pivot като цяло за книгите, благодарение на тях, благодарение на вас, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2014.xlsx
