Използвали ли сте някога VLOOKUP, за да внесете колона от една таблица в друга таблица? Сега, когато Excel има вграден модел на данни, функцията VLOOKUP е остаряла. Можете да създадете релация между две таблици с данни въз основа на съответстващи си данни във всяка от таблиците. След това можете да създадете листове на Power View и да изграждате обобщени таблици и други отчети с полета от всяка таблица, дори когато таблиците са от различни източници. Ако например имате данни за продажбите по клиенти, може да искате да импортирате и свържете данни за интелигентно време, за да анализирате тенденциите в продажбите по години и месеци.
Всички таблици в работната книга са изброени в обобщената таблица и списъците с полета в Power View.
Когато импортирате свързани таблици от релационна база данни, Excel често може да създаде тези релации в модела на данни, който създава на заден план. Във всички останали случаи ще трябва да създадете релациите ръчно.
-
Уверете се, че работната книга съдържа поне две таблици и че всяка таблица има колона, която може да бъде нанесена в колона на друга таблица.
-
Направете едно от следните неща: Форматирайте данните като таблица или Импортирайте външни данни като таблица в нов работен лист.
-
Дайте на всяка таблица смислено име: В Инструменти за таблица щракнете върху Проектиране > Име на таблица > въведете име.
-
Уверете се, че колоната в една от таблиците съдържа уникални стойности на данните, без дубликати. Excel може да създаде релацията само ако една колона съдържа уникални стойности.
Например за да свържете продажбите по клиенти с интелигентно време, и двете таблици трябва да съдържат дати в един и същ формат (например 1.1.2012) и поне една от таблиците (интелигентно време) трябва да съдържа колона, в която всяка дата се включва само по веднъж.
-
Щракнете върху Данни > Зависимости.
Ако Зависимости е в сиво, значи работната книга съдържа само една таблица.
-
В диалоговия прозорец Управление на зависимости щракнете върху Създай.
-
В диалоговия прозорец Създаване на зависимост щракнете върху стрелката за Таблица и изберете таблица от падащия списък. В релацията "един към много" тази таблица трябва да е откъм страната "много". В примера с клиентите и интелигентното време трябва да изберете първо таблицата с продажби по клиенти, защото във всеки един ден е възможно да възникнат множество продажби.
-
За Колона (външна) изберете колоната, която съдържа данните, свързани със Свързана колона. Например ако имате колона за дата и в двете таблици, ще изберете тази колона сега.
-
За Свързана таблица изберете таблица, която има поне една колона с данни, свързани с таблицата, която току-що сте избрали за Таблица.
-
За Свързана колона (основна) изберете колона, която има уникални стойности, съвпадащи със стойностите на колоната, която сте избрали за Колона.
-
Щракнете върху OK.
Повече информация за релациите между таблици в Excel
Бележки за релациите
-
Можете да разберете дали съществува релация, като плъзнете полета от различни таблици в списъка с полета на обобщената таблица. Ако не получите подкана да създадете релация, Excel вече разполага с необходимата информация за релацията, за да свърже данните.
-
Създаването на релации е сходно с използването на функцията VLOOKUP: трябват ви колони, съдържащи съвпадащи данни, така че Excel да може да направи кръстосана препратка от редовете в една таблица към редовете в друга. В примера с интелигентното време таблицата "Клиент" трябва да има стойности за дата, които съществуват също и в таблицата за интелигентно време.
-
В модела на данни релациите между таблиците могат да бъдат "един към един" (всеки пътник има една бордна карта) или "един към много" (всеки полет има много пътници), но не и "много към много". Релациите от тип "много към много" водят до грешки на циклична зависимост, като например "Открита е циклична зависимост". Тази грешка ще възникне, ако създадете директна връзка между две таблици, които са "много към много" или ако създадете недиректни връзки (верига от релации между таблици, които са "един към много" във всяка релация, но "много към много", когато се погледнат в цялост). Прочетете повече за Релации между таблици в модел на данни.
-
Типовете данни в двете колони трябва да са съвместими. За подробности вижте Типове данни в модели на данни в Excel
-
Други начини да създадете релации, които може да са по-интуитивни, особено когато не сте сигурни коя колона да използвате. Вижте Създаване на релация в изглед на диаграма в Power Pivot.
Пример: Свързване на данни за интелигентно време с данни за полетите на авиолиниите
Можете да научите както за релациите между таблици, така за интелигентното време чрез използване на безплатни данни от пазара на Microsoft Azure. Някои от тези набори от данни са много големи, което изисква бърза интернет връзка, за да може изтеглянето на данните да приключи в рамките на разумен период от време.
-
Стартирайте добавката Power Pivot in Microsoft Excel и отворете прозореца на Power Pivot.
-
Щракнете върху Получаване на външни данни > От услуга за данни > От пазара на Microsoft Azure. Началната страница на пазара на Microsoft Azure се отваря в съветника за импортиране на таблици.
-
Под Цена щракнете върху Безплатни.
-
Под Категория щракнете върху Наука и статистика.
-
Намиране на DateStream и щракнете върху абонамент.
-
Въведете акаунта си в Microsoft и щракнете върху Влизане. Визуализация на данните трябва да се покаже в прозореца.
-
Превъртете до края и щракнете върху Избор на заявка.
-
Щракнете върху Напред.
-
Изберете BasicCalendarUS и щракнете върху Готово, за да импортирате данните. При наличие на бърза интернет връзка импортирането би трябвало да отнеме около минута. След като импортирането завърши, би трябвало да видите отчет за състоянието на 73 414 прехвърлени редове. Щракнете върху Затвори.
-
Щракнете върху Получаване на външни данни > От услуга за данни > От пазара на Microsoft Azure, за да импортирате втори набор от данни.
-
Под Тип щракнете върху Данни.
-
Под Цена щракнете върху Безплатни.
-
Намерете Закъснения на полетите на въздушни превозвачи в САЩ и щракнете върху Избери.
-
Превъртете до края и щракнете върху Избор на заявка.
-
Щракнете върху Напред.
-
Щракнете върху Готово, за да импортирате данните. При наличие на бърза интернет връзка импортирането може да отнеме около 15 минути. След като импортирането завърши, би трябвало да видите отчет за състоянието на 2 427 284 прехвърлени редове. Щракнете върху Затвори. Сега би трябвало да имате две таблици в модела на данните. За да може да се създаде релация между тях, необходимо е наличието на съвместими колони във всяка от таблиците.
-
Обърнете внимание, че DateKey в BasicCalendarUS е във формат 1/1/2012 12:00:00 AM. Таблицата On_Time_Performance също има колона за дата и час – FlightDate, чиито стойности са указани в същия формат: 1/1/2012 12:00:00 AM. Двете колони съдържат съвпадащи данни от един и същ тип данни и поне една от колоните (DateKey) съдържа само уникални стойности. В следващите няколко стъпки ще използвате тези колони, за да създадете релация между таблиците.
-
В прозореца на Power Pivot щракнете върху Обобщена таблица, за да създадете обобщена таблица в нов или съществуващ работен лист.
-
В списъка на полетата разгънете On_Time_Performance и щракнете върху ArrDelayMinutes, за да го добавите в областта на стойностите. В обобщената таблица би трябвало да видите общата сума на закъсненията на полетите, измерени в минути.
-
Разгънете BasicCalendarUS и щракнете върху MonthInCalendar, за да го добавите в областта на редове.
-
Обърнете внимание, че обобщената таблица сега съдържа месеци, но общата сума на минутите за всеки месец е една и съща. Повтарящи се идентични стойности указват, че е необходима релация.
-
В списъка на полетата, в "Може да е необходима релация между таблици” щракнете върху Създаване.
-
В "Свързана таблица" изберете On_Time_Performance, а в "Свързана колона (основна)" изберете FlightDate.
-
В "Таблица" изберете BasicCalendarUS, а в "Колона (външна)" изберете DateKey. Щракнете върху OK, за да създадете релацията.
-
Обърнете внимание, че сумата на минутите закъснение сега се различава за всеки месец.
-
В BasicCalendarUS изберете и плъзнете YearKey в областта на редовете, над MonthInCalendar.
Сега вече можете да разпределите закъсненията на пристигащите по години и месеци или по други стойности в календара.
Съвети: По подразбиране месеците са изброени по азбучен ред. Чрез добавката Power Pivot можете да промените сортирането им, така че месеците да се покажат в хронологичен ред.
-
Уверете се, че таблицата BasicCalendarUS е отворена в прозореца на Power Pivot.
-
В таблицата "Начало" щракнете върху Сортиране по колона.
-
В "Сортиране" изберете MonthInCalendar
-
В "По" изберете MonthOfYear.
Обобщената таблица сега сортира всяка комбинация от месец и година (октомври 2011, ноември 2011) по номера на месеца в рамките на годината (10, 11). Промяната на реда за сортиране се извършва лесно, защото информационният канал DateStream предоставя всички необходими колони, за да може да проработи сценарият. Ако използвате различна таблица за интелигентно време, стъпката, която трябва да извършите, ще се различава.
"Може да са необходими релации между таблици"
Докато добавяте полета към обобщена таблица, ще бъдете информирани, ако е необходима релация между таблици, за да се придаде смисъл на полетата, които сте избрали в обобщената таблица.
Макар че Excel може да ви съобщи, че е необходима релация, програмата не може да ви каже кои таблици и колони да използвате или дали релацията между таблици е възможна изобщо. Пробвайте да изпълните стъпките по-долу, за да получите необходимите отговори.
Стъпка 1: Определете кои таблици да укажете в релацията
Ако вашият модел съдържа само няколко таблици, може да е очевидно кои от тях трябва да използвате. Но при по-големи модели може да имате нужда от малко помощ. Една възможност е да използвате изгледа на диаграма в добавката Power Pivot. Изгледът на диаграма осигурява визуално представяне на всички таблици в модела на данни. Като използвате изгледа на диаграма, можете бързо да определите кои таблици са отделени от останалата част от модела.
Забележка: Възможно е да се създадат нееднозначни релации, които са невалидни, ако бъдат използвани в обобщена таблица или в отчет на Power View. Да предположим, че всички таблици са свързани по някакъв начин с други таблици в модела, но когато се опитате да комбинирате полета от различни таблици, получавате съобщението "Може да е необходима релация между таблици". Най-вероятната причина за това съобщение е, че сте попаднали на релация от тип "много към много". Ако проследите веригата на релациите между таблици, които се свързват с таблиците, които искате да използвате, вероятно ще откриете, че имате две или повече релации между таблици от тип "една към много". Няма лесно заобиколно решение на този проблем, което да работи във всяка ситуация, но можете да опитате да създадете изчисляеми колони, за да съберете колоните, които искате да използвате, в една таблица.
Стъпка 2: Намерете колони, които могат да се използват за създаване на път от една таблица към следващата
След като определите коя таблица не е свързана с останалата част от модела, прегледайте нейните колони, за да определите дали друга колона в модела съдържа съответни стойности.
Например да допуснем, че имате модел, който съдържа продажби на продукти по територия, и че след това импортирате демографски данни, за да разберете дали има корелация между продажбите и демографските тенденции във всяка територия. Тъй като демографските данни произлизат от различен източник на данни, таблиците, в които се съдържат, са първоначално изолирани от останалата част на модела. За да интегрирате демографските данни в модела, ще трябва да намерите колона в една от таблиците с демографски данни, съответстваща на колона, която вече използвате. Ако например демографските данни са организирани по регион и вашите данни за продажбите дават информация за региона, в който те са осъществени, бихте могли да свържете двата набора от данни чрез намиране на обща колона, например "Държава", "Пощенски код" или "Регион", за да предоставите стойностите за справка.
Освен съвпадащите стойности има няколко допълнителни изисквания за създаване на релации:
-
Стойностите на данните в колоната за справка трябва да са уникални. С други думи колоната не може да съдържа дублиращи се стойности. В модела на данни нулите и празните низове са равностойни на празни стойности, които представляват различни стойности на данни. Това означава, че не можете да имате няколко нули в колоната за справка.
-
Типовете данни на колоната източник и на справочната колона трябва да са съвместими. За повече информация относно типовете данни вж. Типове данни в модели на данни.
За да научите повече за релациите между таблиците, вижте Зависимости между таблици в модел на данни.
