Урок: Импортиране на данни в Excel 2013 и създаване на модел на данни
Applies ToExcel за Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Резюме:    Това е първият урок от серията, която има за цел да ви помогне да опознаете и да използвате удобно Excel и вградените функции за комбиниране и анализиране на данни. В тези уроци създаваме от самото начало и усъвършенстваме една работна книга на Excel, изграждаме модел на данните и след това създаваме невероятни интерактивни отчети с помощта на Power View. Уроците имат за цел да демонстрират функциите за бизнес разузнаване на Microsoft и възможностите на Excel, обобщените таблици, Power Pivot и Power View.

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

Серията включва следните уроци:

  1. Импортиране на данни в Excel 2016 и създаване на модел на данни

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

  3. Създаване на базирани на карта отчети на Power View

  4. Включване на данни от интернет и задаване на настройки по подразбиране за отчетите на Power View

  5. Помощ за Power Pivot

  6. Създаване на удивителни отчети на Power View – част 2

В този урок започваме с празна работна книга на Excel.

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

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

Този урок използва данни, които описват олимпийските медали, страните-домакини и различни олимпийски спортни събития. Препоръчваме ви да следвате уроците подред. 

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

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

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

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

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

  3. Щракнете върху ДАННИ > Получаване на външни данни > От Access. Лентата се настройва динамично според ширината на вашата работна книга, затова командите в лентата може да изглеждат малко по-различно, отколкото на следващите екрани. Първият екран показва лентата, когато работната книга е широка, а вторият показва преоразмерената работна книга, която заема само част от екрана.Импортиране на данни от AccessИмпортиране на данни от 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 е избрана. В списъка Полета на обобщената таблица , където е разширена таблицата Disciplines , посочете нейното поле Discipline и отдясно на полето се показва падаща стрелка. Щракнете върху падащото меню, щракнете върху (Избери всички), за да премахнете всички селекции, след което превъртете надолу и изберете Стрелба с лък, Гмуркане, Фехтовка, Фигурно пързаляне и Бързо пързаляне с кънки. Щракнете върху 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. Работната книга изглежда както на следващия екран.Наименуване на таблица в Excel

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

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

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

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

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

City

NOC_CountryRegion

Alpha-2 Code

Edition

Season

Melbourne / Stockholm

AUS

AS

1956

Summer

Sydney

AUS

AS

2000

Summer

Innsbruck

AUT

AT

1964

Winter

Innsbruck

AUT

AT

1976

Winter

Antwerp

BEL

BE

1920

Summer

Antwerp

BEL

BE

1920

Winter

Montreal

CAN

CA

1976

Summer

Lake Placid

CAN

CA

1980

Winter

Calgary

CAN

CA

1988

Winter

St. Moritz

SUI

SZ

1928

Winter

St. Moritz

SUI

SZ

1948

Winter

Beijing

CHN

CH

2008

Summer

Berlin

GER

GM

1936

Summer

Garmisch-Partenkirchen

GER

GM

1936

Winter

Barcelona

ESP

SP

1992

Summer

Helsinki

FIN

FI

1952

Summer

Paris

FRA

FR

1900

Summer

Paris

FRA

FR

1924

Summer

Chamonix

FRA

FR

1924

Winter

Grenoble

FRA

FR

1968

Winter

Albertville

FRA

FR

1992

Winter

London

GBR

UK

1908

Summer

London

GBR

UK

1908

Winter

London

GBR

UK

1948

Summer

Munich

GER

DE

1972

Summer

Athens

GRC

GR

2004

Summer

Cortina d'Ampezzo

ITA

IT

1956

Winter

Rome

ITA

IT

1960

Summer

Turin

ITA

IT

2006

Winter

Tokyo

JPN

JA

1964

Summer

Sapporo

JPN

JA

1972

Winter

Nagano

JPN

JA

1998

Winter

Seoul

KOR

KS

1988

Summer

Mexico

MEX

MX

1968

Summer

Amsterdam

NED

NL

1928

Summer

Oslo

NOR

NO

1952

Winter

Lillehammer

NOR

NO

1994

Winter

Stockholm

SWE

SW

1912

Summer

St Louis

USA

US

1904

Summer

Los Angeles

USA

US

1932

Summer

Lake Placid

USA

US

1932

Winter

Squaw Valley

USA

US

1960

Winter

Moscow

URS

RU

1980

Summer

Los Angeles

USA

US

1984

Summer

Atlanta

USA

US

1996

Summer

Salt Lake City

USA

US

2002

Winter

Sarajevo

YUG

YU

1984

Winter

  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, 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.

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

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

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

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

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

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

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

Забележки: Данните и изображенията в тази поредица уроци са базирани на следното:

  • Набор от данни за Олимпийските игри от Guardian News & Media Ltd.

  • Изображения на флаговете от Factbook на CIA (cia.gov)

  • Данни за населението от Световната банка (worldbank.org)

  • Olympic Sport Pictograms (Пиктограми за олимпийските спортове) от Thadius856 и Parutakupiu

Нуждаете ли се от още помощ?

Искате ли още опции?

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

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