Резюме: Това е първият урок от серията, която има за цел да ви помогне да опознаете и да използвате удобно Excel и вградените функции за комбиниране и анализиране на данни. В тези уроци създаваме от самото начало и усъвършенстваме една работна книга на Excel, изграждаме модел на данните и след това създаваме невероятни интерактивни отчети с помощта на Power View. Уроците имат за цел да демонстрират функциите за бизнес разузнаване на Microsoft и възможностите на Excel, обобщените таблици, Power Pivot и Power View.
В тези уроци ще научите как да импортирате и изследвате данни в Excel, да изграждате и прецизирате модел на данни чрез Power Pivot и да създавате интерактивни отчети с Power View, които можете да публикувате, защитавате и споделяте.
Серията включва следните уроци:
- Импортиране на данни в Excel 2016 и създаване на модел на данните
- Разширяване на релациите в модел на данни с помощта на Excel, Power Pivot и DAX
- Създаване на базирани на карта отчети на Power View
- Включване на данни от интернет и задаване на настройки по подразбиране за отчетите на Power View
- Помощ за Power Pivot
- Създаване на удивителни отчети на Power View – част 2
В този урок започваме с празна работна книга на Excel.
Разделите в този урок са следните:
- Импортиране на данни от база данни
- Импортиране на данни от електронна таблица
- Импортиране на данни чрез копиране и поставяне
- Създаване на релация между импортирани данни
- Контролна точка и тест
В края на този урок има тест, с който можете да проверите какво сте научили.
Този урок използва данни, които описват олимпийските медали, страните-домакини и различни олимпийски спортни събития. Препоръчваме ви да следвате уроците подред.
Импортиране на данни от база данни
Започваме този урок с празна работна книга. В този раздел ще се свържем с външен източник на данни и ще импортираме тези данни в Excel за следващ анализ.
Да започнем с изтеглянето на данни от интернет. Данните описват олимпийските медали и са във вид на база данни на Microsoft Access.
Щракнете върху следните връзки, за да изтеглите файловете, които ще използваме в тази серия уроци. Изтеглете всеки от четирите файла в местоположение, което е лесно достъпно, като например "Изтегляния " или "Моите документи", или в нова папка, която сте създали:
> База данни на OlympicMedals.accdb на Access
> OlympicSports.xlsx работна книга на Excel
> Population.xlsx работна книга на Excel
> DiscImage_table.xlsx работна книга на ExcelОтворете празна работна книга в Excel.
Щракнете върху "Данни > ", "Получаване на данни > от база данни > " от база данни на Microsoft Access. Лентата се настройва динамично според ширината на вашата работна книга, затова командите в лентата може да изглеждат малко по-различно, отколкото на следващия екран.
Изберете файла OlympicMedals.accdb, който изтеглихте, и щракнете върху Импортиране. Появява се следният прозорец "Навигатор", който показва таблиците, намерени в базата данни. Таблиците в базата данни са подобни на работни листове или таблици в Excel. Отметнете квадратчето Избор на няколко таблици и изберете всички таблици. След това щракнете върху "Зареждане > в".
Появява се прозорецът "Импортиране на данни".
Забележка
Обърнете внимание на квадратчето за отметка в долната част на прозореца, което ви позволява да добавяте тези данни към модела на данни, показан на следващия екран. Моделът на данни се създава автоматично, когато импортирате или работите с две или повече таблици едновременно. Моделът на данни интегрира таблиците, позволявайки задълбочен анализ с помощта на обобщени таблици, Power Pivot и Power View. Когато импортирате таблици от база данни, съществуващите релации между тези таблици се използват за създаване на модела на данни в Excel. Моделът на данни е прозрачен в Excel, но можете да го преглеждате и променяте директно с помощта на добавката Power Pivot. Моделът на данни е разгледан по-подробно по-нататък в този урок.
Изберете опцията Отчет с обобщена таблица, която импортира таблиците в Excel и подготвя обобщена таблица за анализиране на импортираните таблици, и натиснете ОК.
След като данните са импортирани, се създава обобщена таблица, за което се използват импортираните таблици.
След като данните са импортирани в Excel и автоматично е създаден модел на данните, сте готови да преглеждате данните.
Преглед на данните чрез обобщена таблица
Преглеждането на импортираните данни е лесно, когато използвате обобщена таблица. В обобщената таблица можете да плъзгате полета (подобно на колоните в Excel) от таблиците (например таблиците, които току-що импортирахте от базата данни на Access) в различни области на обобщената таблица, за да укажете как тя да представя вашите данни. Обобщената таблица има четири области: ФИЛТРИ, КОЛОНИ, РЕДОВЕ и СТОЙНОСТИ.
Може да се наложи да експериментирате, докато определите до коя област трябва да плъзнете полето. Можете да плъзгате колкото полета желаете от вашите таблици, докато обобщената таблица покаже данните така, както искате да ги виждате. Не се колебайте да изследвате, като плъзгате полета в различни области на обобщената таблица; Подреждането на полетата в обобщена таблица не влияе върху данните.
Нека разгледаме данните за олимпийските медали в обобщена таблица, като започнем с олимпийските медалисти, подредени по дисциплина, вид медал и страна или регион на спортиста.
В Полета на обобщената таблица разгънете таблицата Medals, като щракнете върху стрелката до нея. Намерете полето NOC_CountryRegion в разгънатата таблица Medals и я плъзнете в областта КОЛОНИ. NOC означава "Национални олимпийски комитети", което е организационната единица за съответната страна или регион.
След това от таблицата Discipline плъзнете дисциплина в областта РЕДОВЕ.
Нека филтрираме дисциплините, за да покажем само пет спорта: Archery (Стрелба с лък), Diving (Скокове във вода),Fencing (Фехтовка), Figure Skating (Фигурно пързаляне) и Speed Skating (Бързо пързаляне с кънки). Можете да направите това в областта Полета на обобщената таблица или чрез филтъра Етикети на редове в самата обобщена таблица.
- Щракнете някъде в обобщената таблица, за да се уверите, че е избрана обобщената таблица на Excel. В списъка с полета на обобщената таблица , където таблицата "Дисциплини " е разгъната, посочете нейното поле "Дисциплина" и вдясно от полето се появява стрелка за падащ списък. Щракнете върху падащото меню, щракнете върху **(Избери всички)**, за да изчистите всичко избрано, след което превъртете надолу и изберете Archery, Diving, Fencing, Figure Skating и Speed Skating. Щракнете върху OK.
- Или, в раздела Етикети на редове на обобщената таблица, щракнете върху падащата стрелка до Етикети на редове в обобщената таблица, щракнете върху (Избери всички), за да изчистите всичко избрано, след което превъртете надолу и изберете Archery, Diving, Fencing, Figure Skating и Speed Skating. Щракнете върху ОК.
В Полета на обобщената таблица, от таблицата Медали плъзнете медал в областта СТОЙНОСТИ. Тъй като стойностите трябва да са числови, Excel автоматично променя Medal на Count of Medal.
В таблицата Medals отново изберете медал и го плъзнете в областта ФИЛТРИ.
Нека филтрираме обобщената таблица, за да покажем само страните или регионите с повече от 90 медала общо. Ето как.
- В обобщената таблица щракнете върху падащата стрелка вдясно от Етикети на колони.
- Изберете Филтри за стойност и изберете По-голямо от...
- Въведете 90 в последното поле (вдясно). Щракнете върху ОК.
Обобщената таблица изглежда както на следващия екран:
С малко усилия вече имате една основна обобщена таблица, която включва полетата от три различни таблици. Това, което направи задачата толкова лесна, бяха съществуващите релации между таблиците. Тъй като имаше релации между таблиците в базата данни източник и тъй като импортирахте всички таблици с една-единствена операция, Excel успя да създаде отново тези релации в модела на данни.
Какво се случва обаче, ако данните идват от различни източници или ако бъдат импортирани по-късно? Обикновено можете да създадете релации с новите данни въз основа на съответстващи си колони. В следващата стъпка ще импортирате допълнителни таблици и ще се научите да създавате нови релации.
Импортиране на данни от електронна таблица
Нека сега да импортираме данни от друг източник, в случая от съществуваща работна книга, след което да определим релациите между съществуващите и новите данни. Релациите ви позволяват да анализирате колекции от данни в Excel и да създавате интересни, завладяващи визуализации от импортираните данни.
Нека започнем със създаването на празен работен лист, след което да импортираме данни от работна книга на Excel.
Вмъкнете нов работен лист на Excel и го наречете Sports.
Отворете папката с изтеглените файлове с примерни данни и отворете OlympicSports.xlsx.
Изберете и копирайте данните в Лист1. Ако изберете клетка с данни, например клетката A1, можете да натиснете Ctrl + A, за да изберете всички съседни данни. Затворете работната книга OlympicSports.xlsx.
В работния лист Sports, преместете курсора в клетка A1 и поставете данните.
С осветените данни, натиснете Ctrl + T, за да форматирате данните като таблица. Можете също да форматирате данните като таблица от лентата, като изберете НАЧАЛО > Форматиране като таблица. Тъй като данните имат заглавки, изберете Моята таблица има заглавки в прозореца Създаване на таблица, който се появява, както е показано тук.
Форматирането на данните като таблица има много предимства. Можете да зададете име на таблицата, за да я идентифицирате лесно. Можете също да създадете релации между таблиците, което ще ви позволи да извършвате проучвания и анализи в обобщени таблици, Power Pivot и Power View.
Дайте име на таблицата. В TABLE DESIGN > Properties намерете полето Table Name и въведете Sports. Работната книга изглежда както на следващия екран.
Запишете работната книга.
Импортиране на данни чрез копиране и поставяне
След като импортирахме данните от работна книга на Excel, нека импортираме данните от таблица, която сме намерили в уеб страница, или от друг източник, от който можем да копираме и поставяме в Excel. В следващите стъпки ще добавите от таблица градовете домакини на олимпийските игри.
- Вмъкнете нов работен лист на Excel и го наречете Hosts.
- Изберете и копирайте следната таблица, включително заглавките на таблицата.
- В Excel преместете курсора в клетка A1 на работния лист Hosts и поставете данните.
- Форматирайте данните като таблица. Както казахме по-рано в този урок, натиснете Ctrl + T, за да форматирате данните като таблица, или от HOME > Форматирай като таблица. Тъй като данните имат заглавки, изберете Моята таблица има заглавки в прозореца Създаване на таблица, който се появява.
- Дайте име на таблицата. В "СВОЙСТВА НА ПРОЕКТИРАНЕ > НА ТАБЛИЦА " намерете полето " Име на таблица " и въведете "Домакини".
- Изберете колоната Edition и от раздела НАЧАЛО я форматирайте като Число с 0 цифри след десетичния знак.
- Запишете работната книга. Работната книга изглежда както на следващия екран.
Сега, след като имате работна книга на Excel с таблици, можете да създадете релации между тях. Създаването на релации между таблиците ви позволява да смесвате данните от двете таблици.
Създаване на релация между импортирани данни
Можете веднага да започнете да използвате в обобщената таблица полета от импортираните таблици. Ако Excel не може да определи как да включи дадено поле в обобщената таблица, трябва да се създаде връзка със съществуващия модел на данни. В следващите стъпки можете да научите как да създадете релация между данните, които сте импортирали от различни източници.
В Лист1, в горния край на"Полета на обобщената таблица" щракнете върху"Всички ", за да видите пълния списък с налични таблици, както е показано на следващия екран.
Превъртете през списъка, за да видите новите таблици, които току-що добавихте.
Разгънете Sports и изберете Sport, за да го добавите към обобщената таблица. Обърнете внимание, че Excel ви подканва да създадете релация, както се вижда на следващия екран.
Това съобщение се показва, защото сте използвали полета от таблица, която не е част от основния модел на данните. Един от начините да добавите таблица към модела на данните е като създадете релация към таблица, която вече е в модела на данни. За да създадете релацията, в една от таблиците трябва да има колона с уникални, неповтарящи се стойности. В примерните данни таблицата Disciplines, която импортирахте от базата данни, съдържа поле с кодове на спортовете, наречено SportID. Същите тези кодове на спортовете присъстват като поле в данните на Excel, които импортирахме. Нека да създадем релацията.Щракнете върху СЪЗДАЙ... в осветената област Полета на обобщената таблица, за да отворите диалоговия прозорец Създаване на зависимост, както е показано в следващия екран.
В "Таблица" изберете "Таблица за модел на данни": "Дисциплини " от падащия списък.
В Колона (външни) изберете SportID.
В "Свързана таблица" изберете "Таблица с модел на данни": Спорт.
В Свързана колона (основни) изберете SportID.
Щракнете върху OK.
Обобщената таблица се променя, за да отрази новата релация. Но обобщената таблица все още не изглежда както трябва, заради подредбата на полета в областта РЕДОВЕ. Discipline е подкатегория на даден спорт, но тъй като подредихме Discipline над Sport в областта РЕДОВЕ, тя не е подредена правилно. Следващият екран показва тази нежелана подредба.
В областта РЕДОВЕ преместете Sport над Discipline. Така е много по-добре и обобщената таблица показва данните така, както искате да ги виждате, както е показано в следващия екран.
Скрито от вас, Excel изгражда модел на данните, който може да се използва в цялата работна книга, във всяка обобщена таблица или обобщена диаграма, в Power Pivot или във всеки отчет на Power View. Релациите в таблиците са основата на модела на данните и това, което определя навигацията и изчисляването на пътища.
В следващия урок "Разширяване на релациите в модела на данни" чрез Excel, Power Pivot** и DAX** ще надграждате върху това, което научихте тук, и преминавате през разширяването на модела на данни чрез мощната и визуална добавка на Excel, наречена Power Pivot. Можете също да се научите как да изчислявате колони в таблица и да използвате тази изчисляема колона, за да може към вашия модел на данни да се добави таблица, която иначе не е свързана.
Контролна точка и тест
Преглед на наученото
Сега имате работна книга на Excel с обобщена таблица, която ви дава достъп до данните от няколко таблици, някои от които сте импортирали отделно. Научихте се да импортирате от база данни, от друга работна книга на Excel и чрез копиране на данни и поставянето им в Excel.
За да обедините тези данни, трябваше да създадете релация за таблицата, която Excel използва за свързване на редовете. Научихте също, че е изключително важно да имате колони в една таблица, които са свързани с данни в друга таблица, за да можете да създавате релации и да търсите свързани редове.
Вече сте готови за следващия урок в тази серия. Ето една връзка:
Урок: Разширяване на релациите в модела на данни чрез Excel, Power Pivot и DAX
ТЕСТ
Искате да проверите какво сте запомнили от изучаваното? Ето вашия шанс. Следващият тест набляга на функции, възможности или изисквания, с които се запознахте в този урок. Най-долу на страницата ще намерите отговорите. Успех!
Въпрос 1: Защо е важно да конвертирате импортираните данни в таблици?
А: Не е необходимо да ги конвертирате в таблици, защото всички импортирани данни автоматично се превръщат в таблици.
Б: Ако конвертирате импортираните данни в таблици, те ще бъдат изключени от модела на данни. Само когато са изключени от модела на данни, те са достъпни в обобщените таблици, Power Pivot и Power View.
В: Ако конвертирате импортираните данни в таблици, те могат да се включат в модела на данни и да станат достъпни за обобщени таблици, Power Pivot и Power View.
Г: Не можете да конвертирате импортираните данни в таблици.
Въпрос 2: Кои от следните източници на данни можете да импортирате в Excel и да включите в модела на данни?
А: Бази данни на Access, както и много други бази данни.
Б: Съществуващи файлове на Excel.
В: Всичко, което можете да копирате и поставяте в Excel и да форматирате като таблица, включително таблици с данни в уеб сайтове, документи и всичко друго, което може да се постави в Excel.
Г: Всички от горните
Въпрос 3: Какво се случва в обобщената таблица, когато пренаредите полетата в четирите области "Полета на обобщената таблица"?
А: Нищо – не можете да пренареждате полетата, след като сте ги поставили в областите "Полета на обобщената таблица".
Б: Форматът на обобщената таблица се променя, за да отрази оформлението, но съответните данни не се променят.
В: Форматът на обобщената таблица се променя, за да отрази оформлението, и всички съответни данни се променят окончателно.
Г: Съответните данни се променят, резултатът от което са нови набори от данни.
Въпрос 4: Какво е необходимо, когато създавате релация между таблици?
А: Никоя от таблиците не може да има колона, която съдържа уникални, неповтарящи се стойности.
Б: Една от таблиците не трябва да е част от работната книга на Excel.
В: Колоните не трябва да се конвертират в таблици.
Г: Нито едно от горните не е вярно.
Отговори на теста
- Правилен отговор: В
- Правилен отговор: Г
- Правилен отговор: Б
- Правилен отговор: Г
Забележка
Данните и изображенията в тази поредица уроци са базирани на следното:
- Набор от данни за Олимпийските игри от Guardian News & Media Ltd.
- Изображения на флаговете от Factbook на CIA (cia.gov)
- Данни за населението от Световната банка (worldbank.org)
- Olympic Sport Pictograms (Пиктограми за олимпийските спортове) от Thadius856 и Parutakupiu