Как се изметат данните през Excel

Ето основните неща, които можете да правите, докато данните са във жп гара Excel:

• Импортиране    Можете да импортирате данни от много различни външни източници на данни. Тези източници на данни могат да бъдат на вашия компютър, в облака или по средата на света. За повече информация вижте Импортиране на данни от външни източници на данни.

• Power Query    Можете да използвате Power Query (наричано по-рано Get & Transform), за да създавате заявки за оформяне, трансформиране и комбиниране на данни по различни начини. Можете да експортирате работата си като шаблон на Power Query, за да дефинирате операция на потока от данни в Power Apps. Можете дори да създадете тип данни, за да допълнят свързаните типове данни. За повече информация вижте Помощ за Power Query за Excel.

• Защита    Поверителността на данните, идентификационните данни и удостоверяването винаги са постоянен проблем. За повече информация вижте Управление на настройките и разрешенията на източника на данни и Задаване на нива на поверителност.

• Обновяване    Импортираните данни обикновено изискват операция за обновяване, за да се въвеждат промени, като например допълнения, актуализации и изтривания, в Excel. За повече информация вижте Обновяване на връзка с външни данни в Excel.

• Връзки/свойства    Всеки външен източник на данни има свързана с нея информация за асортиментна връзка и свойство, която понякога изисква промени в зависимост от обстоятелствата. За повече информация вижте Управление на диапазони отвъншни данни и техните свойства , Създаване, редактиранеи управление на връзки към външни данни и свойства на връзката.

• Наследен    Традиционните методи, като например "Съветници за стари импортиране" и MSQuery, все още са налични за използване. За повече информация вижте Опции за импортиране и анализ на данни и Използване на Microsoft Query за извличане на външни данни.

Следващите раздели предоставят повече подробности за това какво се прави зад кулисите на тази заета жп гара на Excel.

Има свойства на диапазон от връзки, заявки и външни данни. Свойствата на връзката и заявката съдържат традиционна информация за връзката. В заглавието на диалоговия прозорец "Свойства на връзката" означава, че няма заявка, свързана с нея, но "Свойства на заявка" означава, че има. Свойствата на диапазона от външни данни контролират оформлението и формата на данните. Всички източници на данни имат диалогов прозорец "Свойства на външни данни", но източниците на данни, които са свързани с идентификационни данни и информация за обновяване, използват по-големия диалогов прозорец Свойства на данни от външен диапазон.

Следващата информация обобщава най-важните диалогови прозорци, прозорци, пътища на команди и съответните помощни теми.

Диалогов прозорец или екран Командни пътища	Раздели и тунели	Основна помощна тема
Последни източници Данни > последни източници	(Без раздели) Диалогов прозорец "Тунели за свързване> "Навигатор"	Управление на настройките и разрешенията на източника на данни
Свойства на връзката ИЛИ Съветник за връзка с данни Данни > заявки & връзки > връзки > (щракнете с десния бутон върху връзка) > свойства	РазделЪт "Използване на раздела Дефиниция", използван в раздела	Свойства на връзката
Свойства на заявката Данни> съществуващи връзки > (щракнете с десния бутон върху връзка) > редактиране на свойствата на връзката OR Заявки> данни & връзкас | Раздел "Заявки" > (щракнете с десния бутон върху връзка) > свойства OR Query> свойства OR Data> Обновяване на всички> връзки (когато са разположени в натоварен работен лист на заявката)	РазделЪт "Използване на раздела Дефиниция", използван в раздела	Свойства на връзката
Заявки & връзки Заявки > данни & връзки	Раздел "Заявки" – раздел "Връзки"	Свойства на връзката
Съществуващи връзки Данни > съществуващи връзки	Раздел "Връзки" – раздел "Таблици"	Свързване към външни данни
Свойства на външни данни OR Свойства на диапазона от външни данни OR Свойства>данни (забранени, ако не са разположени в работен лист на заявка)	Използва се в раздела (от диалоговия прозорец "Свойства на връзката")   Бутонът "Обновяване" в десния тунел до "Свойства на заявката"	Управление на диапазони от външни данни и техните свойства
Свойства на връзката > раздел "Дефиниция" > експортиране на файл за връзка OR Query> файл за връзка за експортиране	(Без раздели) Диалогов прозорец "Тунели към файл" Папка "Източници на данни"	Създаване, редактиране и управление на връзки към външни данни

Данните в работна книга на Excel могат да идват от две различни местоположения. Данните може да се съхраняват директно в работната книга или да се съхраняват във външен източник на данни, като например текстов файл, база данни или куб за онлайн аналитична обработка (OLAP). Този външен източник на данни е свързан към работната книга чрез връзка за данни, която е набор от информация, която описва как да намерите, влезете и да получите достъп до външния източник на данни.

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

Информацията за връзката се съхранява в работната книга и може също да се съхранява във файл на връзка, като например файл за връзка с данни на Office (ODC) (.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 (преди наречена Get & Transform), свързана с нея.

Ако използвате файл за връзка, за да се свържете към източник на данни, Excel копира информацията за връзката от файла на връзката в работната книга на Excel. Когато правите промени с помощта на диалоговия прозорец Свойства на връзката, редактирате информацията за връзка с данни, която се съхранява в текущата работна книга на Excel, а не в първоначалния файл за връзка с данни, който може да е бил използван за създаване на връзката (указано с името на файла, което се показва в свойството Файл на връзка в раздела Дефиниция). След като редактирате информацията за връзката (с изключение на свойствата Име на връзка и Описание на връзката), връзката към файла на връзката се премахва и свойството Файл на връзка се изчиства.

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

С помощта на диалоговия прозорец Връзки можете лесно да управлявате тези връзки, включително създаването, редактирането и изтриването им (Изберете данни> Заявки & Връзки > Връзки > (щракнете с десния бутон върху връзка) > Свойства.) Можете да използвате този диалогов прозорец, за да направите следното: 

• Можете да създавате, редактирате, обновявате и изтривате връзки, които се използват в работната книга.

• Проверете източника на външни данни. Може да искате да направите това, в случай че връзката е дефинирана от друг потребител.

• Показване къде се използва всяка връзка в текущата работна книга.

• Диагностициране на съобщение за грешка за връзки към външни данни.

• Пренасочете връзка към друг сървър или източник на данни или заместете файла на връзката за съществуваща връзка.

• Можете лесно да създавате и споделяте файлове за връзка с потребители.

Файловете на връзката са особено полезни за съгласувано споделяне на връзки, което прави връзките по-откриваеми, спомага за подобряване на защитата на връзките и улеснява администрирането на източниците на данни. Най-добрият начин да споделяте файлове за връзка е да ги поставите в защитено и надеждно местоположение, като например мрежова папка или библиотека на SharePoint, където потребителите могат да четат файла, но само определените потребители могат да променят файла. За повече информация вижте Споделяне на данни с ODC.

Използване на ODC файлове

Можете да създавате файлове за връзка с данни на Office (ODC) (.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, MyText_1, MyText_2 и т.н.

Диапазонът от външни данни има допълнителни свойства (да не се бърка със свойствата на връзката), които можете да използвате за управление на данните, като например запазване на форматирането на клетки и ширината на колоните. Можете да промените тези свойства на диапазона от външни данни, като щракнете върху Свойства в групата Връзки в раздела Данни и след това направите промените в диалоговите прозорци Свойства на диапазон от външни данни или Свойства на външни данни.

Има няколко обекта с данни (например диапазон от външни данни и отчет с обобщена таблица), които можете да използвате, за да се свържете с различни източници на данни. Обаче типът на източника на данни, към който можете да се свържете, е различен между всеки обект с данни.

Можете да използвате и обновявате свързани данни в Excel Services. Както при всеки външен източник на данни, може да се наложи да удостоверите достъпа си. За повече информация вижте Обновяване на връзка с външни данни в Excel. Fили повече информация за идентификационните данни, вижте Настройки за удостоверяване на Excel Services.

Следващата таблица обобщава кои източници на данни се поддържат за всеки обект с данни в Excel.

Excel данни обект	Създава Външни данни диапазон?		OLE DB	ODBC	Текст файл	HTML файл	XML файл	SharePoint списък
Съветник за импортиране на текст	Да		Не	Не	Да	Не	Не	Не
Отчет с обобщена таблица (не-OLAP)	Не		Да	Да	Да	Не	Не	Да
Отчет с обобщена таблица (OLAP)	Не		Да	Не	Не	Не	Не	Не
Таблица на Excel	Да		Да	Да	Не	Не	Да	Да
XML карта	Да		Не	Не	Не	Не	Да	Не
Уеб заявка	Да		Не	Не	Не	Да	Да	Не
Съветник за връзка с данни	Да		Да	Да	Да	Да	Да	Да
Microsoft Query	Да		Не	Да	Да	Не	Не	Не

Заобиколно решение на Excel Services за таблици на Excel и наименувани диапазони

Ако искате да покажете работна книга на Excel в Excel Services, можете да се свържете и обновите данни, но трябва да използвате отчет с обобщена таблица. Excel Services не поддържа диапазони от външни данни, което означава, че Excel Services не поддържа таблица на Excel, свързана към източник на данни, уеб заявка, XML карта или Microsoft Query.

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

Нека да предприемем пътуване надолу по лентата с памет на базата данни.

За MDAC, OLE DB и OBC

На първо място, извинения за всички акроними. Компонентите на Microsoft Data Access (MDAC) 2.8 са включени в Microsoft Windows . С MDAC можете да се свързвате и да използвате данни от голямо разнообразие от релационни и нерелационни източници на данни. Можете да се свържете с много различни източници на данни с помощта на драйвери за open Database Connectivity (ODBC) или OLE база данни доставчици, които са или създадени и изпратени от Microsoft, или са разработени от различни трети страни. Когато инсталирате Microsoft Office, към вашия компютър се добавят допълнителни ODBC драйвери и OLE доставчици на DB.

За да видите пълен списък на доставчиците на OLE DB, инсталирани на вашия компютър, покажете диалоговия прозорец Свойства на връзката към данни от файл на връзка за данни и след това щракнете върху раздела Доставчик.

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

Можете също да използвате ODBC драйвери и доставчици на OLE бази данни от други производители, за да получавате информация от източници, различни от източници на данни на Microsoft, включително други типове ODBC и OLE бази данни. За информация относно инсталирането на тези ODBC драйвери или доставчици на OLE DB проверете документацията за базата данни или се обърнете към доставчика на вашата база данни.

Използване на ODBC за свързване към източници на данни

В архитектурата на ODBC приложение (например Excel) се свързва с диспечера на ODBC драйвери, който от своя страна използва конкретен ODBC драйвер (като например ODBC драйвера на Microsoft SQL), за да се свърже към източник на данни (например база данни на Microsoft SQL Server).

За да се свържете към ODBC източници на данни, направете следното:

1. Уверете се, че съответният ODBC драйвер е инсталиран на компютъра, който съдържа източника на данни.

2. Дефинирайте име на източник на данни (DSN), като използвате администратора на ODBC източник на данни, за да съхранявате информацията за връзката в системния регистър или DSN файл, или низ за свързване в код на Microsoft Visual Basic, за да предадете информацията за връзка директно на диспечера на ODBC драйвери.

   За да дефинирате източник на данни, в Windows щракнете върху бутона Старт и след това щракнете върху Контролен панел. Щракнете върху Система и поддръжкаи след това щракнете върху Административни инструменти. Щракнете върху Производителност и поддръжка, щракнете върху Административни инструменти. и след това щракнете върху Източници на данни (ODBC). За повече информация относно различните опции щракнете върху бутона Помощ във всеки диалогов прозорец.

Машинни източници на данни

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

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

Файлови източници на данни

Файловите източници на данни (наричани още DSN файлове) съхраняват информацията за връзката в текстов файл, а не в системния регистър, и обикновено са по-гъвкави за използване от машинните източници на данни. Можете например да копирате файлов източник на данни на всеки компютър с правилния ODBC драйвер, така че вашето приложение да може да разчита на съгласувана и точна информация за връзка към всички компютри, които използва. Или можете да поставите източника на данни на файл на един сървър, да го споделите между много компютри в мрежата и лесно да поддържате информацията за връзката на едно място.

Източникът на данни на файл също може да бъде неспоменаем. Неспоменаем файлов източник на данни се намира на един компютър и сочи към машинен източник на данни. Можете да използвате неспоменаеми източници на данни за файлове за достъп до съществуващи източници на данни на машината от файлови източници на данни.

Използване на OLE DB за свързване към източници на данни

В архитектурата на OLE DB приложението, което има достъп до данните, се нарича потребител на данни (например Excel), а програмата, която позволява основен достъп до данните, се нарича доставчик на база данни (например Microsoft OLE DB Provider за SQL Server).

Файлът universal Data Link (.udl) съдържа информацията за връзката, която потребителят използва за достъп до източник на данни чрез доставчика на OLE база данни на този източник на данни. Можете да създадете информацията за връзката, като направите едно от следните неща:

• В съветника за връзка с данни използвайте диалоговия прозорец Свойства на връзка за данни, за да дефинирате връзка за данни за доставчик на OLE база данни. 

• Създайте празен текстов файл с разширение на име на .udl файл и след това редактирайте файла, който показва диалоговия прозорец Свойства на връзката към данни.