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

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

Забележка:  Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

Забележка: Тази статия описва модели на данни в Excel 2013. Обаче същите модели на данни и функции на Power Pivot, въведени в Excel 2013, са в сила и за Excel 2016.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Импортиране на данни от Access

    Импортиране на данни от Access с малка лента

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

    прозорецът "избор на таблица"

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

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


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

    Прозорецът "Импортиране на данни"

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

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

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

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

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

Четирите области "Полета на обобщената таблица"

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

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

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

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

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

    1. Щракнете на произволно място в обобщената таблица, за да сте сигурни, че обобщената таблица на Excel е избрана. В списъка Полета на обобщената таблица, където разгънахте таблицата Disciplines, посочете с мишката полето Discipline и вдясно от него ще се покаже падаща стрелка. Щракнете върху падащата стрелка, щракнете върху (Избери всички), за да изчистите всичко избрано, след което превъртете надолу и изберете Archery, Diving, Fencing, Figure Skating и Speed Skating. Щракнете върху ОК.

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

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

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

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

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

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

    3. Въведете 90 в последното поле (вдясно). Щракнете върху OK.
      прозорецът "филтри по стойност"

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

актуализирана обобщена таблица

С малко усилия вече имате една основна обобщена таблица, която включва полетата от три различни таблици. Това, което направи задачата толкова лесна, бяха съществуващите релации между таблиците. Тъй като имаше релации между таблиците в базата данни източник и тъй като импортирахте всички таблици с една-единствена операция, 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. Разгънете спорт и изберете спорт , за да го добавите към обобщената таблица. Обърнете внимание, че Excel ви подканва да създадете релация, както се вижда на следващия екран.
    Подканата "СЪЗДАВАНЕ... релация" в "Полета на обобщената таблица"

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

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

    Прозорецът "Създаване на зависимост"

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

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

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

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

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

Промените в обобщена таблица, че да отрази нова връзка. Но обобщената таблица не изглежда правилно още доста, поради подреждане на полетата в областта за редове . Дисциплина е подкатегория на даден спорт, но тъй като ние подредени дисциплина над спорт в областта за редове , не е организирана правилно. Следващия екран показва този нежеланата подредба.
Обобщената таблица с нежеланата подредба

  1. Преминаване спорт над дисциплина в областта за редове . Това е много по-добре и обобщената таблица показва данните, как искате да го видите, както е показано на следващия екран.

    обобщена таблица с правилната подредба

Скрито от вас, 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. Правилен отговор: Г

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

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

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

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

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

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×