Ако данните винаги са в пътуване, то Excel е като гара "Гранд Сентрал". Представете си, че данните са влак, пълен с пътници, който редовно влиза в Excel, прави промени и след това тръгва. Има десетки начини за влизане в Excel, който импортира данни от всякакъв тип, а списъкът расте. След като данните са в Excel, те са готови да променят формата си точно по начина, по който искате с помощта на Power Query. Данните, както всички нас, също изискват "грижи и хранене", за да поддържат нещата безпроблемни. Тук на помощ идват връзката, заявката и свойствата на данните. И накрая, данните напускат гарата на Excel по много начини: импортирани от други източници на данни, споделени като отчети, диаграми и обобщени таблици и експортирани в Power BI и Power Apps.
Най-важни неща, които можете да правите с данните в жп гарата на Excel
Ето основните неща, които можете да правите, докато данните са в гарата на Excel:
- Импортиране Можете да импортирате данни от много различни външни източници на данни. Тези източници на данни може да бъдат на вашата машина, в облака или на другия край на света. За повече информация вж . "Импортиране на данни от външни източници на данни".
- Power Query Можете да използвате Power Query (наричан преди Get & Transform) за създаване на заявки за оформяне, трансформиране и комбиниране на данни по различни начини. Можете да експортирате работата си като шаблон на Power Query, за да дефинирате операция за поток от данни в Power Apps. Можете дори да създадете тип данни, който да допълва свързаните типове данни. За повече информация вижте помощта за Power Query за Excel.
- Защита Поверителността на данните, идентификационните данни и удостоверяването винаги са постоянна грижа. За повече информация вж. "Управление на настройките и разрешенията за източник на данни " и "Задаване на нива на поверителност".
- Обновяване Импортираните данни обикновено изискват операция за обновяване, за да се внесат промени, като добавяния, актуализации и изтривания, в Excel. За повече информация вижте "Обновяване на връзка с външни данни в Excel".
- Връзки/свойства Всеки външен източник на данни има разнообразна връзка и свързана със свойства информация за свойства, която понякога изисква промени в зависимост от вашите обстоятелства. За повече информация вж. "Управление на диапазони от външни данни и техните свойства","Създаване, редактиране и управление на връзки към външни данни" и "Свойства на връзката".
- Наследени версии Традиционните методи, като например съветници за наследено импортиране и MSQuery, все още са налични за използване. За повече информация вж. " Опции за импортиране и анализ на данни " и "Използване на Microsoft Query за извличане на външни данни".
Следващите раздели предоставят повече подробности за това, което се случва зад кулисите на тази оживена жп гара на Excel.
Резюме на връзките и свойствата
Има свойства за диапазони за връзка, заявка и външни данни. Свойствата на връзката и заявката съдържат традиционна информация за връзка. В заглавието на диалоговия прозорец "Свойства на връзката " означава, че няма заявка, свързана с него, но "Свойства на заявката " означава, че има. Свойствата на диапазон от външни данни управляват оформлението и формата на данните. Всички източници на данни имат диалогов прозорец "Свойства на външни данни", но източниците на данни, които имат свързани идентификационни данни и информация за обновяване, използват по-широкия диалогов прозорец "Свойства на данни за външен диапазон".
Информацията по-долу обобщава най-важните диалогови прозорци, екрани, пътища за команди и съответните помощни теми.
| Диалогов прозорец или екран Командни пътища |
Раздели и тунели | Основна помощна тема |
|---|---|---|
|
Скорошни източници Данни>Скорошни източници |
(Няма раздели) Tunnels to Connect>Navigator dialog box |
Управление на настройките и разрешенията за източник на данни |
|
Свойства на връзката ИЛИ Съветник за връзка с данни Данни>Заявки & връзки>Раздел >"Връзки" (щракнете с десния бутон върху връзка) >Свойства |
Раздел "Използване" Раздел "Дефиниция" Раздел "Използвани в " |
Свойства на връзката |
|
Свойства на заявката Данни>Съществуващи връзки> (щракнете с десния бутон върху връзка) >Редактиране на свойствата на връзката ИЛИ Данни>Заявки & връзки| Раздел >"Заявки" (щракване с десния бутон върху връзка) >Свойства ИЛИ Заявка>Свойства ИЛИ Данни>Обнови всичко>Връзки (когато са разположени в зареден работен лист за заявка) |
Раздел "Използване" Раздел "Дефиниция" Раздел "Използвани в " |
Свойства на връзката |
|
Заявки & връзки Данни>Заявки & връзки |
Раздел "Заявки" Раздел "Връзки" |
Свойства на връзката |
|
Съществуващи връзки Данни>Съществуващи връзки |
Раздел "Връзки" Раздел "Таблици" |
Свързване към външни данни |
|
Свойства на външни данни ИЛИ Свойства на диапазон от външни данни ИЛИ Данни>Свойства (забранени, ако не са позиционирани в работен лист за заявки) |
Използва се в раздел (от диалоговия прозорец "Свойства на връзката ") Refresh button on the right tunnels to Query Properties |
Управление на диапазони от външни данни и техните свойства |
| Свойства >на връзкатаРаздел >"Дефиниция" Експортиране на файл за връзка ИЛИ Заявка>Експортиране на файл за връзка |
(Няма раздели) Тунели към Диалогов прозорец ''Файл '' Папка на източници на данни |
Създаване, редактиране и управляване на връзки към външни данни |
Основите на връзките с данни
Данните в работна книга на Excel могат да идват от две различни места. Данните може да се съхраняват директно в работната книга или във външен източник на данни, като например текстов файл, база данни или куб за онлайн аналитична обработка (OLAP). Този външен източник на данни е свързан към работната книга чрез връзка за данни, която представлява набор от информация, описваща как да намерите, да влезете и да осъществите достъп до външния източник на данни.
Основното предимство на свързването към външни данни е, че можете периодично да анализирате тези данни, без многократно да копирате данните в работната си книга, което е операция, която може да отнеме много време и да доведе до грешки. След свързване към външни данни можете също автоматично да обновявате (или актуализирате) работните книги на Excel от първоначалния източник на данни, когато източникът на данни се актуализира с нова информация.
Информацията за връзка се съхранява в работната книга и може да се съхранява във файл за връзка, като например файл за връзка с данни на Office (.odc) или файл с име на източник на данни (.dsn).
За да пренесете външни данни в Excel, ви трябва достъп до данните. Ако външният източник на данни, до който искате достъп, не е на локалния ви компютър, може да се наложи да се обърнете към администратора на базата данни за парола, потребителски разрешения или друга информация за връзката. Ако източникът на данни е база данни, уверете се, че базата данни не е отворена в монополен режим. Ако източникът на данни е текстов файл или електронна таблица, уверете се, че друг потребител не го е отворил за монополен достъп.
Много източници на данни изискват също и ODBC драйвер или доставчик на OLE DB, който да координира потока от данни между Excel, файла за връзка и източника на данни.
Следващата диаграма обобщава основните моменти за връзките с данни.
1. Съществуват най-различни източници на данни, с които можете да се свържете: услугите за анализ, SQL Server, Microsoft Access, други OLAP и релационни бази данни, електронни таблици и текстови файлове.
2. Много източници на данни имат свързан ODBC драйвер или доставчик на OLE DB.
3. Файлът за връзка дефинира цялата информация, която е необходима за достъп до и извличане на данни от източник на данни.
4. Информацията за връзка се копира от файл за връзка в работна книга и тази информация може лесно да се редактира.
5. Данните се копират в работна книга, така че да можете да ги използвате точно както използвате данните, съхранявани директно в работната книга.
Намиране на връзки
За да намерите файлове за свързване, използвайте диалоговия прозорец "Съществуващи връзки". (Избор на данни>Съществуващи връзки.) Като използвате този диалогов прозорец, можете да видите следните типове връзки:
-
Връзки в работната книга
Този списък показва всички текущи връзки в работната книга. Списъкът се създава от връзки, които вече сте дефинирали, които сте създали с помощта на диалоговия прозорец " Избор на източник на данни " на съветника за връзка с данни, или от връзки, които преди това сте избрали като връзка от този диалогов прозорец. -
Файлове за връзка на вашия компютър
Този списък се създава от папката "Моите източници на данни", която обикновено се съхранява в папката "Документи". -
Файлове за връзка в мрежата
Този списък може да бъде създаден от набор от папки във вашата локална мрежа, чието местоположение може да бъде разположено в мрежата като част от разполагането на групови правила на Microsoft Office или библиотека на SharePoint.
Редактиране на свойствата на връзката
Можете също да използвате Excel като редактор на файлове за връзки, за да създавате и редактирате връзки към външни източници на данни, които се съхраняват в работна книга или във файл за връзка. Ако не намерите желаната връзка, можете да създадете връзка, като щракнете върху "Преглед за още ", за да се покаже диалоговият прозорец " Избор на източник на данни ", и след това щракнете върху "Нов източник", за да стартирате съветника за връзка с данни.
След като създадете връзката, можете да използвате диалоговия прозорец "Свойства на връзката" (избиране назаявки за данни> & раздел >"Връзки" > (щракнете с десния бутон върху връзка) >"Свойства"), за да управлявате различни настройки за връзките към външни източници на данни и да използвате, използвате повторно или превключвате файлове за връзка.
Забележка Понякога диалоговият прозорец "Свойства на връзката" се нарича диалогов прозорец "Свойства на заявката", когато има заявка, създадена в Power Query (наричана преди "Получаване & трансформация"), свързана с него.
Ако използвате файл за връзка, за да се свържете с източник на данни, Excel копира информацията за връзка от файла на връзката в работната книга на Excel. Когато правите промени с помощта на диалоговия прозорец "Свойства на връзката ", вие редактирате информацията за връзка с данни, която се съхранява в текущата работна книга на Excel, а не първоначалния файл за връзка с данни, който може да е бил използван за създаване на връзката (което е посочено чрез името на файла, което се показва в свойството " Файл за връзка " в раздела "Дефиниция "). След като редактирате информацията за връзка (с изключение на свойствата "Име на връзка " и "Описание на връзката "), връзката към файла за връзка се премахва и свойството "Файл за връзка " се изчиства.
За да е сигурно, че файлът за връзка винаги се използва при обновяването на източника на данни, щракнете върху "Винаги опитвай да използваш този файл, за да обновиш тези данни " в раздела "Дефиниция ". Избирането на това квадратче за отметка гарантира, че актуализациите на файла за връзка винаги ще се използват от всички работни книги, които използват този файл за връзка, които също трябва да имат този набор от свойства.
Управление на връзки
С помощта на диалоговия прозорец "Връзки" можете лесно да управлявате тези връзки, включително да ги създавате, редактирате и изтривате (Изборна заявки за данни> & Връзки > раздел >Връзки (щракване с десния бутон върху връзка) >Свойства.) Можете да използвате този диалогов прозорец, за да направите следното:
- Създаване, редактиране, обновяване и изтриване на връзки, които се използват в работната книга.
- Проверка на източника на външни данни. Може да направите това, в случай че връзката е дефинирана от друг потребител.
- Показва къде се използва всяка връзка в текущата работна книга.
- Диагностициране на съобщение за грешка относно връзки към външни данни.
- Пренасочване на връзка към друг сървър или източник на данни или заменете съществуваща връзка с файла на връзката.
- Улеснете създаването и споделянето на файлове за връзка с потребителите.
Споделяне на ODC връзки и заявки във файлове
Файловете за връзка са особено полезни в случаите на постоянно споделяне на връзки, тъй като правят връзките по-лесно откриваеми, допринасят за подобряване на защитата на връзките и улесняват администрирането на източника на данни. Най-добрият начин за споделяне на файловете за връзка е да ги поставите в защитено и надеждно местоположение, като например мрежова папка или библиотека на SharePoint, където потребителите ще могат да четат файла, но само определени потребителите ще могат да го променят. За повече информация вижте "Споделяне на данни с ODC".
Използване на ODC файлове
Можете да създавате файлове за свързване към данни на Office (.odc файлове), като се свържете към външни данни чрез диалоговия прозорец " Избор на източник на данни " или като използвате съветника за връзка с данни, за да се свържете към нови източници на данни. ODC файлът използва HTML и XML етикети по избор, за да съхранява информацията за връзката. Можете лесно да преглеждате или редактирате съдържанието на файла в Excel.
Можете да споделяте файлове за връзка с други хора, за да им дадете същия достъп, който имате вие до външен източник на данни. Не е необходимо други потребители да настройват източник на данни, за да отворят файла за връзка, но може да се наложи да инсталират ODBC драйвера или доставчика на OLE DB, необходими за достъп до външните данни на компютъра им.
ODC файловете са препоръчителният метод за свързване към данни и споделяне на данни. Можете лесно да конвертирате други традиционни файлове за връзка (DSN, UDL файлове и файлове за заявки) в ODC файл, като отворите файла за връзка и след това щракнете върху бутона "Експортиране на файл на връзка" в раздела "Дефиниция " на диалоговия прозорец "Свойства на връзката ".
Използване на файлове със заявки
Файловете със заявки са текстови файлове, които съдържат информация за източника на данни, включително името на сървъра, където се намират данните, и информация за връзката, която предоставяте, когато създавате източник на данни. Файловете със заявки са традиционен начин за споделяне на заявки с други потребители на Excel.
Използване на .dqy файлове със заявки Можете да използвате Microsoft Query, за да запишете .dqy файлове, които съдържат заявки за данни от релационни бази данни или текстови файлове. Когато отворите тези файлове в Microsoft Query, можете да видите данните, върнати от заявката, и да модифицирате заявката, за да извлечете различни резултати. Можете да запишете .dqy файл за всяка заявка, която създавате, или с помощта на съветника за заявка, или директно в Microsoft Query.
Използване на .oqy файлове със заявки Можете да записвате .oqy файлове, за да се свържете с данни в OLAP база данни на сървър или в офлайн файл с кубове (.cub). Когато използвате съветника за многомерна връзка в Microsoft Query, за да създадете източник на данни за OLAP база данни или куб, .oqy файлът се създава автоматично. Тъй като OLAP базите данни не са организирани в записи или таблици, не можете да създавате заявки или .dqy файлове за достъп до тези бази данни.
Използване на .rqy файлове със заявки Excel може да отваря файлове със заявки в .rqy формат, за да поддържа драйвери за източници на данни за OLE DB, които използват този формат. За повече информация вижте документацията за вашия драйвер.
Използване на .qry файлове със заявки Microsoft Query може да отваря и записва файлове със заявки във формат .qry за използване с по-стари версии на Microsoft Query, които не могат да отварят .dqy файлове. Ако имате файл със заявка във формат .qry, който искате да използвате в Excel, отворете файла в Microsoft Query и след това го запишете като .dqy файл. За информация относно записването на .dqy файлове вижте помощта за Microsoft Query.
Използване на .iqy файлове за уеб заявки Excel може да отваря .iqy файлове с уеб заявки, за да извлече данни от интернет. За повече информация вижте "Експортиране в Excel от SharePoint".
Използване на свойства на външни данни
Диапазонът от външни данни (наричан също таблица със заявки) е дефинирано име или име на таблица, което определя местоположението на данните, внесени в работния лист. Когато се свързвате към външни данни, Excel автоматично създава диапазон от външни данни. Единственото изключение от това е отчет с обобщена таблица, свързан с източник на данни, който не създава диапазон от външни данни. В Excel можете да форматирате и оформите диапазон от външни данни или да го използвате в изчисления, както и с всички други данни.
Excel автоматично наименува диапазон за външни данни по следния начин:
- Диапазоните от външни данни от файлове за връзка към данни на Office (ODC файлове) получават същите имена като името на файла.
- Диапазоните от външни данни от бази данни се наименуват с името на заявката. По подразбиране Query_from_източник е името на източника на данни, който използвате за създаване на заявката.
- Диапазоните от външни данни от текстови файлове се наименуват с името на текстовия файл.
- Диапазоните от външни данни от уеб заявки се именуват с името на уеб страницата, от която са извлечени данните.
Ако вашият работен лист съдържа повече от един диапазон от външни данни от един и същ източник, диапазоните се номерират. Например "Моят текст", MyText_1, MyText_2 и т.н.
Диапазонът за външни данни има допълнителни свойства (да не се бърка със свойства на връзката), които можете да използвате за управление на данните, например запазване на форматирането на клетките и ширината на колоните. Можете да промените тези свойства на диапазон от външни данни, като щракнете върху "Свойства " в групата "Връзки " на раздела " Данни " и след това направите промените в диалоговите прозорци "Свойства на диапазон за външни данни " или " Свойства на външни данни ".
|
|
|---|
Поддръжка на източници на данни в Excel Services
Има няколко обекта от данни (като например външен диапазон от данни и отчет с обобщена таблица), които можете да използвате, за да свържете с различни източници на данни. Типът на източника на данни, с който можете да се свържете, обаче е различен между всеки обект от данни.
Можете да използвате и обновявате свързани данни в Excel Services. Както при всеки външен източник на данни, може да се наложи да удостоверите достъпа си. За повече информация вижте "Обновяване на връзка с външни данни в Excel". Акоимате повече информация за идентификационните данни, вж. "Настройки за удостоверяване на Excel Services".
Следващата таблица обобщава кои източници на данни се поддържат за всеки обект от данни в Excel.
|
Excel данни обект |
Създава Външни данни обхват? |
OLE DB |
ODBC |
Text файл |
HTML файл |
XML файл |
SharePoint списък |
|
|---|---|---|---|---|---|---|---|---|
| Съветник за импортиране на текст | Да | Не | Не | Да | Не | Не | Не | |
| отчет с обобщена таблица (не OLAP) |
Не | Да | Да | Да | Не | Не | Да | |
| отчет с обобщена таблица (OLAP) |
Не | Да | Не | Не | Не | Не | Не | |
| Ръководство за бързо стартиране в Outlook 2013 | Да | Да | Да | Не | Не | Да | Да | |
| XML карта | Да | Не | Не | Не | Не | Да | Не | |
| Уеб заявка | Да | Не | Не | Не | Да | Да | Не | |
| Съветник за връзка с данни | Да | Да | Да | Да | Да | Да | Да | |
| Microsoft Query | Да | Не | Да | Да | Не | Не | Не |
Забележка
Тези файлове – текстов файл, импортиран с помощта на съветника за импортиране на текст, XML файл, импортиран с помощта на XML карта, и HTML или XML файл, импортиран с помощта на уеб заявка, не използват ODBC драйвер или доставчик на OLE DB за осъществяване на връзката към източника на данни.
Заобиколно решение на Excel Services за таблици и наименувани диапазони на Excel
Ако искате да покажете работна книга на Excel в Excel Services, можете да се свържете с и да обновите данни, но трябва да използвате отчет с обобщена таблица. Excel Services не поддържа диапазони от външни данни, което означава, че Excel Services не поддържа таблица на Excel, свързана с източник на данни, уеб заявка, XML карта или заявка на Microsoft.
Можете обаче да заобиколите това ограничение, като използвате обобщена таблица, за да се свържете с източника на данни, и след това да проектирате и оформите обобщената таблица като двуизмерна таблица без нива, групи или междинни суми, така че да се показват всички желани стойности на редове и колони.
Компоненти за достъп до данни на ODBC и OLE база данни
Нека направим пътуване надолу в пътя на паметта на базата данни.
За MDAC, OLE DB и OBC
На първо място, извиняваме се за всички съкращения. Microsoft Data Access Components (MDAC) 2.8 е включен в Microsoft Windows. С MDAC можете да се свържете и да използвате данни от голямо разнообразие от релационни и нерелационни източници на данни. Можете да се свържете с много различни източници на данни с помощта на драйвери за Open Database Connectivity (ODBC) или доставчици на OLE DB, които са създадени и предоставени от Microsoft или разработени от различни трети лица. Когато инсталирате Microsoft Office, допълнителните ODBC драйвери и доставчици на OLE DB се добавят в компютъра ви.
За да видите пълен списък на доставчици на OLE DB, инсталирани на вашия компютър, покажете диалоговия прозорец "Свойства на връзките с данни" от файл за връзка с данни и след това щракнете върху раздела "Доставчик".
За да видите пълен списък на ODBC доставчици, инсталирани на компютъра ви, покажете диалоговия прозорец "Администратор на ODBC бази данни" и след това щракнете върху раздела "Драйвери ".
Можете да използвате също и ODBC драйвери и доставчици на OLE DB от други разработчици, за да получавате информация от източници, различни от източниците на данни на Microsoft, включително други типове ODBC и OLE DB бази данни. За информация относно инсталирането на тези ODBC драйвери или доставчици на OLE DB вижте документацията за базата данни или се свържете с нейния доставчик.
Използване на ODBC за свързване към източници на данни
В архитектурата на ODBC дадено приложение (например Excel) се свързва към диспечера на ODBC драйвери, който на свой ред използва конкретен ODBC драйвер (например драйвера за Microsoft SQL ODBC), за да се свърже с източник на данни (например база данни на Microsoft SQL Server).
За да се свържете с ODBC източници на данни, направете следното:
- Уверете се, че на компютъра, съдържащ източника на данни, е инсталиран подходящият ODBC драйвер.
- Да дефинирате име на източник на данни (DSN), като използвате или администратора на ODBC източник на данни , за да съхраните информацията за връзка в системния регистър или DSN файл, или низ за свързване в код на Microsoft Visual Basic, за да предадете информацията за връзка директно към диспечера на ODBC драйвери.
За да дефинирате източник на данни, в Windows щракнете върху бутона "Старт" и след това щракнете върху Контролен панел. Щракнете върху "Система и поддръжка", след което щракнете върху "Административни инструменти". Щракнете върху Производителност и поддръжка, щракнете върху Административни инструменти. и след това щракнете върху Източници на данни (ODBC). За повече информация относно различните опции щракнете върху бутона "Помощ " във всеки диалогов прозорец.
Машинни източници на данни
Машинните източници на данни съхраняват информацията за връзка в системния регистър на определен компютър с дефинирано от потребителя име. Можете да използвате машинни източници на данни само на компютър, в който те са дефинирани. Има два типа машинни източници на данни – потребителски и системни. Потребителските източници на данни могат да се използват само от текущия потребител и са видими само за този потребител. Системните източници на данни могат да се използват от всички потребители на компютъра и са видими за всички потребители на компютъра.
Машинният източник на данни е особено полезен, когато искате да предоставите допълнителна защита, тъй като помага да се гарантира, че само потребители, които са влезли, могат да виждат даден машинен източник на данни, а машинният източник на данни не може да се копира от отдалечен потребител на друг компютър.
Файлови източници на данни
Файловите източници на данни (наричани също DSN файлове) съхраняват информацията за връзка в текстов файл, а не в системния регистър, като по принцип са по-гъвкави за използване от машинните източници на данни. Например можете да копирате файлов източник на данни на всеки компютър с правилния ODBC драйвер, така че вашето приложение да разчита на изчерпателна и точна информация за връзка с всички компютри, които то използва. Или можете да поставите файловия източник на данни на един сървър, да го споделите между много компютри в мрежата и лесно да поддържате на едно място информацията за връзката.
Файловият източник на данни може да бъде и несподелим. Несподелимият файлов източник на данни се намира на един компютър и сочи към машинен източник на данни. Можете да използвате несподелими файлови източници на данни за достъп от файлови източници на данни до съществуващи машинни източници на данни.
Използване на OLE база данни за свързване към източници на данни
В архитектурата на OLE базата данни приложението, което осъществява достъп до данните, се нарича потребител на данни (например Excel), а програмата, която позволява собствен достъп до данните, се нарича доставчик на бази данни (например доставчик на OLE база данни на Microsoft за SQL Server).
Универсалният файл за връзка с данни (.udl) съдържа информацията за връзката, която потребител на данни използва за достъп до източник на данни чрез доставчика на OLE база данни на този източник на данни. Можете да създадете информация за връзката, като направите едно от следните неща:
- В съветника за връзка с данни използвайте диалоговия прозорец " Свойства на връзките с данни ", за да дефинирате връзка за данни за доставчик на OLE DB.
- Създайте празен текстов файл с разширение на името на файла .udl и след това редактирайте файла, който показва диалоговия прозорец " Свойства на връзката с данни ".