Урок: Импортиране на данни в Excel 2013 и създаване на модел на данни

Разделите в този урок са следните:

• Импортиране на данни от база данни

• Импортиране на данни от електронна таблица

• Импортиране на данни чрез копиране и поставяне

• Създаване на релация между импортирани данни

• Контролна точка и тест

В края на този урок има тест, с който можете да проверите какво сте научили.

Този урок използва данни, които описват олимпийските медали, страните-домакини и различни олимпийски спортни събития. Препоръчваме ви да следвате уроците подред. Тези уроци използват и Excel 2013 с активиран Power Pivot. За повече информация за Excel 2013 щракнете тук. За указания как да активиратеPower Pivot, щракнете тук.

Импортиране на данни от база данни

Започваме този урок с празна работна книга. В този раздел ще се свържем с външен източник на данни и ще импортираме тези данни в Excel за следващ анализ.

Да започнем с изтеглянето на данни от интернет. Данните описват олимпийските медали и са във вид на база данни на Microsoft Access.

1. Щракнете върху следните връзки, за да изтеглите файловете, които ще използваме в тази серия уроци. Изтеглете всеки от четирите файла в местоположение, което е лесно достъпно, като например "Изтегляния" или "Моитедокументи" или в нова папка, която създавате:
   > olympicMedals.accdb Access database
   >OlympicSports.xlsx Excel работна книга
   > Population.xlsx Excel работна книга
   >DiscImage_table.xlsx Excel работна книга

2. Отворете празна работна книга в Excel 2013.

3. Щракнете върху ДАННИ > Получаване на външни данни > От Access. Лентата се настройва динамично според ширината на вашата работна книга, затова командите в лентата може да изглеждат малко по-различно, отколкото на следващите екрани. Първият екран показва лентата, когато работната книга е широка, а вторият показва преоразмерената работна книга, която заема само част от екрана.
   
   
   
   
   
    

4. Изберете файла OlympicMedals.accdb, който изтеглихте, и щракнете върху Отвори. Появява се следният прозорец "Избор на таблица", който показва таблиците, намерени в базата данни. Таблиците в базата данни са подобни на работни листове или таблици в Excel. Отметнете квадратчето Разреши избирането на множество таблици и изберете всички таблици. След това щракнете върху ОК.
   
   

5. Появява се прозорецът "Импортиране на данни".

   Забележка: Обърнете внимание на квадратчето за отметка в долната част на прозореца, което ви позволява да добавите тези данни към модела на данни,показан на следващия екран. Моделът на данни се създава автоматично, когато импортирате или работите едновременно с две или повече таблици. Моделът на данни интегрира таблиците, позволявайки обширен анализ с помощта на обобщени таблици, Power Pivot и Power View. Когато импортирате таблици от база данни, съществуващите релации между тези таблици се използват за създаване на модела на данни в Excel. Моделът на данни е прозрачен в Excel, но можете да го преглеждате и променяте директно чрез Power Pivot добавката. Моделът на данни се обсъжда по-подробно по-късно в този урок.

   
   Изберете опцията Отчет с обобщена таблица, която импортира таблиците в Excel подготвя обобщена таблица за анализиране на импортираните таблици и щракнете върху OK.
   
   

6. След като данните са импортирани, се създава обобщена таблица, за което се използват импортираните таблици.
   
   

След като данните са импортирани в Excel и автоматично е създаден модел на данните, сте готови да преглеждате данните.

Преглед на данните чрез обобщена таблица

Преглеждането на импортираните данни е лесно, когато използвате обобщена таблица. В обобщената таблица можете да плъзгате полета (подобно на колоните в Excel) от таблиците (например таблиците, които току-що импортирахте от базата данни на Access) в различни области на обобщената таблица, за да укажете как тя да представя вашите данни. Обобщената таблица има четири области: ФИЛТРИ, КОЛОНИ, РЕДОВЕ и СТОЙНОСТИ.

Може да са ви отнели някои експерименти, за да определите в коя област трябва да се влачи поле. Можете да плъзнете толкова или няколко полета от таблиците си, колкото желаете, докато обобщената таблица не представи данните ви по начина, по който искате да ги виждате. Не се чувствайте свободни да изследвате, като плъзгате полета в различни области на обобщената таблица; базовите данни не се засягат, когато подреждате полета в обобщена таблица.

Нека разгледаме данните за олимпийските медали в обобщена таблица, като започнем с олимпийските медалисти, подредени по дисциплина, вид медал и страна или регион на спортиста.

1. В Полета на обобщената таблица разгънете таблицата Medals, като щракнете върху стрелката до нея. Намерете полето NOC_CountryRegion в разгънатата таблица Medals и я плъзнете в областта КОЛОНИ. NOC означава "Национални олимпийски комитети", което е организационната единица за съответната страна или регион.

2. След това от таблицата Discipline плъзнете дисциплина в областта РЕДОВЕ.

3. Нека филтрираме дисциплините, за да покажем само пет спорта: Archery (Стрелба с лък), Diving (Скокове във вода),Fencing (Фехтовка), Figure Skating (Фигурно пързаляне) и Speed Skating (Бързо пързаляне с кънки). Можете да направите това в областта Полета на обобщената таблица или чрез филтъра Етикети на редове в самата обобщена таблица.

   1. Щракнете някъде в обобщената таблица, за да се уверите, Excel е избрана обобщената таблица. В списъка Полета на обобщена таблица, където е разгъната таблицата Дисциплини, посочете полето Дисциплина и вдясно от полето се показва падаща стрелка. Щракнете върху падащия списък, щракнете върху (Избери всички),за да премахнете всички селекции, след което превъртете надолу и изберете Стрелба с лък, Гмуркане, Фехтовка, Фигурно пързаляне и Бързо пързаляне с кънки. Щракнете върху OK.

   2. Или, в раздела Етикети на редове на обобщената таблица, щракнете върху падащата стрелка до Етикети на редове в обобщената таблица, щракнете върху (Избери всички), за да изчистите всичко избрано, след което превъртете надолу и изберете Archery, Diving, Fencing, Figure Skating и Speed Skating. Щракнете върху ОК.

4. В Полета на обобщената таблица, от таблицата Медали плъзнете медал в областта СТОЙНОСТИ. Тъй като стойностите трябва да са числови, Excel автоматично променя Medal на Count of Medal.

5. В таблицата Medals отново изберете медал и го плъзнете в областта ФИЛТРИ.

6. Нека филтрираме обобщената таблица, за да покажем само страните или регионите с повече от 90 медала общо. Ето как.

   1. В обобщената таблица щракнете върху падащата стрелка вдясно от Етикети на колони.

   2. Изберете Филтри за стойност и изберете По-голямо от...

   3. Въведете 90 в последното поле (вдясно). Щракнете върху ОК.
      

Обобщената таблица изглежда както на следващия екран:

С малко усилия вече имате една основна обобщена таблица, която включва полетата от три различни таблици. Това, което направи задачата толкова лесна, бяха съществуващите релации между таблиците. Тъй като имаше релации между таблиците в базата данни източник и тъй като импортирахте всички таблици с една-единствена операция, Excel успя да създаде отново тези релации в модела на данни.

Какво се случва обаче, ако данните идват от различни източници или ако бъдат импортирани по-късно? Обикновено можете да създадете релации с новите данни въз основа на съответстващи си колони. В следващата стъпка ще импортирате допълнителни таблици и ще се научите да създавате нови релации.

Импортиране на данни от електронна таблица

Нека сега да импортираме данни от друг източник, в случая от съществуваща работна книга, след което да определим релациите между съществуващите и новите данни. Релациите ви позволяват да анализирате колекции от данни в Excel и да създавате интересни, завладяващи визуализации от импортираните данни.

Нека започнем със създаването на празен работен лист, след което да импортираме данни от работна книга на Excel.

1. Вмъкнете нов работен лист на Excel и го наречете Sports.

2. Отворете папката с изтеглените файлове с примерни данни и отворете OlympicSports.xlsx.

3. Изберете и копирайте данните в Лист1. Ако изберете клетка с данни, например клетката A1, можете да натиснете Ctrl + A, за да изберете всички съседни данни. Затворете работната книга OlympicSports.xlsx.

4. В работния лист Sports, преместете курсора в клетка A1 и поставете данните.

5. С осветените данни, натиснете Ctrl + T, за да форматирате данните като таблица. Можете също да форматирате данните като таблица от лентата, като изберете НАЧАЛО > Форматирай като таблица. Тъй като данните имат заглавки, изберете Моята таблица има заглавки в прозореца Създаване на таблица, който се появява, както е показано тук.
   
   
   
   Форматирането на данните като таблица има много предимства. Можете да зададете име на таблицата, за да я идентифицирате лесно. Можете също да създадете релации между таблиците, което ще ви позволи да извършвате проучвания и анализи в обобщени таблици, Power Pivot и Power View.

6. Дайте име на таблицата. В ИНСТРУМЕНТИ ЗА ТАБЛИЦА > ПРОЕКТИРАНЕ > Свойства намерете полето Име на таблица и напишете Sports. Работната книга изглежда както на следващия екран.
   

7. Запишете работната книга.

Импортиране на данни чрез копиране и поставяне

След като импортирахме данните от работна книга на Excel, нека импортираме данните от таблица, която сме намерили в уеб страница, или от друг източник, от който можем да копираме и поставяме в Excel. В следващите стъпки ще добавите от таблица градовете домакини на олимпийските игри.

1. Вмъкнете нов работен лист на Excel и го наречете Hosts.

2. Изберете и копирайте следната таблица, включително заглавките на таблицата.

1. В Excel преместете курсора в клетка A1 на работния лист Hosts и поставете данните.

2. Форматирайте данните като таблица. Както казахме по-рано в този урок, натиснете Ctrl + T, за да форматирате данните като таблица, или направете това от НАЧАЛО > Форматирай като таблица. Тъй като данните имат заглавки, изберете Моята таблица има заглавки в прозореца Създаване на таблица, който се появява.

3. Дайте име на таблицата. В ИНСТРУМЕНТИ ЗА ТАБЛИЦА > ПРОЕКТИРАНЕ > Свойства намерете полето Име на таблица и напишете Домакини.

4. Изберете колоната Edition и от раздела НАЧАЛО я форматирайте като Число с 0 цифри след десетичния знак.

5. Запишете работната книга. Работната книга изглежда както на следващия екран.

Сега, след като имате работна книга на Excel с таблици, можете да създадете релации между тях. Създаването на релации между таблиците ви позволява да смесвате данните от двете таблици.

Създаване на релация между импортирани данни

Можете веднага да започнете да използвате в обобщената таблица полета от импортираните таблици. Ако Excel не може да определи как да включи дадено поле в обобщената таблица, трябва да се създаде връзка със съществуващия модел на данни. В следващите стъпки можете да научите как да създадете релация между данните, които сте импортирали от различни източници.

1. В Лист1, в горната част на полетата на обобщената таблица щракнете върху Всички, за да видите пълния списък с налични таблици, както е показано на следващия екран.
   

2. Превъртете през списъка, за да видите новите таблици, които току-що добавихте.

3. Разгънете Sports и изберете Sport, за да го добавите към обобщената таблица. Обърнете внимание, че Excel ви подканва да създадете релация, както се вижда на следващия екран.
   
    

   Това съобщение се показва, защото сте използвали полета от таблица, която не е част от основния модел на данните. Един от начините да добавите таблица към модела на данните е като създадете релация към таблица, която вече е в модела на данни. За да създадете релацията, в една от таблиците трябва да има колона с уникални, неповтарящи се стойности. В примерните данни таблицата Disciplines, която импортирахте от базата данни, съдържа поле с кодове на спортовете, наречено SportID. Същите тези кодове на спортовете присъстват като поле в данните на Excel, които импортирахме. Нека да създадем релацията.

4. Щракнете върху СЪЗДАЙ... в осветената област Полета на обобщената таблица, за да отворите диалоговия прозорец Създаване на зависимост, както е показано в следващия екран.
   
   

5. В Таблица изберете Disciplines от падащия списък.

6. В Колона (външни) изберете SportID.

7. В Свързана таблица изберете Sports.

8. В Свързана колона (основни) изберете SportID.

9. Щракнете върху OK.

Обобщената таблица се променя, за да отрази новата релация. Но обобщената таблица все още не изглежда както трябва, заради подредбата на полета в областта РЕДОВЕ. Discipline е подкатегория на даден спорт, но тъй като подредихме Discipline над Sport в областта РЕДОВЕ, тя не е подредена правилно. Следващият екран показва тази нежелана подредба.

1. В областта РЕДОВЕ преместете Sport над Discipline. Така е много по-добре и обобщената таблица показва данните така, както искате да ги виждате, както е показано в следващия екран.
   
   

Скрито от вас, Excel изгражда модел на данните, който може да се използва в цялата работна книга, във всяка обобщена таблица или обобщена диаграма, в Power Pivot или във всеки отчет на Power View. Релациите в таблиците са основата на модела на данните и това, което определя навигацията и изчисляването на пътища.

В следващия урок Разширяване на релациите между моделите на данни с помощта на Excel 2013,Power Pivot и DAXизграждате това, което научихте тук, и стъпка напред в разширяването на модела на данни с помощта на мощна и визуална добавка Excel, наречена Power Pivot. Можете също да научите как да изчислявате колони в таблица и да използвате тази изчисляема колона, така че да може да се добави друга несвързана таблица към вашия модел на данни.

Контролна точка и тест

Преглед на наученото

Сега имате работна книга на Excel с обобщена таблица, която ви дава достъп до данните от няколко таблици, някои от които сте импортирали отделно. Научихте се да импортирате от база данни, от друга работна книга на Excel и чрез копиране на данни и поставянето им в Excel.

За да обедините тези данни, трябваше да създадете релация за таблицата, която Excel използва за свързване на редовете. Научихте също, че е изключително важно да имате колони в една таблица, които са свързани с данни в друга таблица, за да можете да създавате релации и да търсите свързани редове.

Вече сте готови за следващия урок в тази серия. Ето една връзка:

Разширяване на релациите в модел на данни с помощта на Excel 2013, Power Pivot и DAX

ТЕСТ

Искате да проверите какво сте запомнили от изучаваното? Ето вашия шанс. Следващият тест набляга на функции, възможности или изисквания, с които се запознахте в този урок. Най-долу на страницата ще намерите отговорите. Успех!

Въпрос 1: Защо е важно да конвертирате импортираните данни в таблици?

А: Не е необходимо да ги конвертирате в таблици, защото всички импортирани данни автоматично се превръщат в таблици.

Б: Ако конвертирате импортираните данни в таблици, те ще бъдат изключени от модела на данни. Само когато са изключени от модела на данни, те са достъпни в обобщените таблици, Power Pivot и Power View.

В: Ако конвертирате импортираните данни в таблици, те могат да се включат в модела на данни и да станат достъпни за обобщени таблици, Power Pivot и Power View.

Г: Не можете да конвертирате импортираните данни в таблици.

Въпрос 2: Кои от следните източници на данни можете да импортирате в Excel и да включите в модела на данни?

А: Бази данни на Access, както и много други бази данни.

Б: Съществуващи файлове на Excel.

В: Всичко, което можете да копирате и поставяте в Excel и да форматирате като таблица, включително таблици с данни в уеб сайтове, документи и всичко друго, което може да се постави в Excel.

Г: Всички от горните

Въпрос 3: Какво се случва в обобщената таблица, когато пренаредите полетата в четирите области "Полета на обобщената таблица"?

А: Нищо – не можете да пренареждате полетата, след като сте ги поставили в областите "Полета на обобщената таблица".

Б: Форматът на обобщената таблица се променя, за да отрази оформлението, но съответните данни не се променят.

В: Форматът на обобщената таблица се променя, за да отрази оформлението, и всички съответни данни се променят окончателно.

Г: Съответните данни се променят, резултатът от което са нови набори от данни.

Въпрос 4: Какво е необходимо, когато създавате релация между таблици?

А: Никоя от таблиците не може да има колона, която съдържа уникални, неповтарящи се стойности.

Б: Една от таблиците не трябва да е част от работната книга на Excel.

В: Колоните не трябва да се конвертират в таблици.

Г: Нито едно от горните не е вярно.

Отговори на теста

1. Правилен отговор: В

2. Правилен отговор: Г

3. Правилен отговор: Б

4. Правилен отговор: Г