Използване на Microsoft Query за извличане на външни данни

С помощта на Microsoft Query можете да се свързвате с външни източници на данни, да избирате данни от тези външни източници, да импортирате тези данни в работния лист и да обновявате данните, ако е необходимо, за да поддържате данните от работния лист синхронизирани с данните във външните източници.

Типове бази данни, до които имате достъп     Можете да извличате данни от няколко типа бази данни, включително Microsoft Office Access, Microsoft SQL Server и MICROSOFT SQL Server OLAP Services. Можете също да извличате данни от работни книги на Excel и от текстови файлове.

Microsoft Office предоставя драйвери, които можете да използвате за извличане на данни от следните източници на данни:

• Microsoft SQL Server Analysis Services (OLAP доставчик )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Парадокс

• Бази данни с текстови файлове

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

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

С Microsoft Query можете да изберете колоните с данни, които искате, и да импортирате само тези данни в Excel.

Актуализиране на работния лист с една операция     След като имате външни данни в работна книга на Excel, всеки път, когато вашата база данни се промени, можете да обновяване данните, за да актуализирате анализа си , без да се налага да създавате отново своите отчети с резюме и диаграми. Можете например да създадете месечно резюме на продажбите и да го обновявате всеки месец, когато се появяват новите данни за продажбите.

Как Microsoft Query използва източниците на данни     След като настроите източник на данни за конкретна база данни, можете да я използвате винаги, когато искате да създадете заявка, за да изберете и извлечете данни от тази база данни – без да се налага да въвеждате отново цялата информация за връзката. Microsoft Query използва източника на данни, за да се свърже с външната база данни и да ви покаже какви данни са налични. След като създадете вашата заявка и върнете данните в Excel, Microsoft Query предоставя на работната книга на Excel както информацията за заявката, така и източника на данни, така че да можете да се свържете отново към базата данни, когато искате да обновите данните.

Използване на Microsoft Query за импортиране на данни     за да импортирате външни данни в Excel с Microsoft Query, следвайте тези основни стъпки, всяка от които е описана подробно в следващите раздели.

Какво е източник на данни?     Източникът на данни е съхранен набор от информация, която позволява на Excel и Microsoft Query да се свързват с външна база данни. Когато използвате Microsoft Query за настройване на източник на данни, вие давате име на източника на данни и след това предоставяте името и местоположението на базата данни или сървъра, типа на базата данни и информацията за влизане и паролата. Информацията включва също и името на OBDC драйвер или драйвер за източник на данни, който е програма, която прави връзки към конкретен тип база данни.

За да настроите източник на данни с помощта на Microsoft Query:

1. В раздела Данни , в групата Получаване на външни данни щракнете върху От други източници и след това щракнете върху От Microsoft Query.

   Забележка: Excel 365 премести Microsoft Query в групата от менюта " Наследени съветници".  Това меню не се показва по подразбиране.  За да разрешите, отидете на Файл, Опции, Данни и разрешете в секцията Показване на наследени съветници за импортиране на данни .

2. Направете едно от следните неща:

   • За да зададете източник на данни за база данни, текстов файл или работна книга на Excel, щракнете върху раздела Бази данни .

   • За да зададете OLAP източник на данни за куб, щракнете върху раздела OLAP кубове . Този раздел е наличен само ако сте изпълнили Microsoft Query от Excel.

3. Щракнете двукратно върху <Нов източник на данни>.

   -или-

   Щракнете върху <нов източник на данни>и след това щракнете върху OK.

   Показва се диалоговият прозорец Създаване на нов източник на данни .

4. В стъпка 1 въведете име, за да идентифицирате източника на данни.

5. В стъпка 2 щракнете върху драйвер за типа на базата данни, която използвате като ваш източник на данни.

   Забележки: 

   • Ако външната база данни, до която искате да получите достъп, не се поддържа от ODBC драйверите, инсталирани с Microsoft Query, трябва да получите и инсталирате ODBC драйвер, съвместим с Microsoft Office, от друг доставчик, като например производителя на базата данни. Обърнете се към доставчика на базата данни за инструкции за инсталиране.

   • OLAP базите данни не изискват ODBC драйвери. Когато инсталирате Microsoft Query, се инсталират драйвери за бази данни, които са създадени с помощта на Microsoft SQL Server Analysis Services. За да се свържете с други OLAP бази данни, трябва да инсталирате драйвер за източник на данни и клиентски софтуер.

6. Щракнете върху Свързване и след това предоставете информацията, необходима за свързване с вашия източник на данни. За бази данни, работни книги на Excel и текстови файлове информацията, която предоставяте, зависи от типа на източника на данни, който сте избрали. Може да бъдете помолени да предоставите име за влизане, парола, версията на базата данни, която използвате, местоположението на базата данни или друга информация, специфична за типа на базата данни.

   Важно: 

   • Използвайте сигурни пароли, съчетаващи главни и малки букви, цифри и символи. В несигурните пароли не се смесват такива елементи. Сигурна парола: Y6dh!et5. Несигурна парола: House27. Паролите трябва да бъдат с дължина от 8 или повече знака. По-добре е да използвате фраза за достъп с 14 знака или повече.

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

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

8. Ако вашата база данни има таблици и искате конкретна таблица да се показва автоматично в съветника за заявки, щракнете върху полето за стъпка 4 и след това щракнете върху желаната таблица.

9. Ако не искате да въвеждате своето име и парола за влизане, когато използвате източника на данни, поставете отметка в квадратчето Записвай моя потребителски ИД и парола в дефиницията на източника на данни . Записаната парола не е шифрована. Ако квадратчето не е налично, обърнете се към администратора на вашата база данни, за да определите дали тази опция може да бъде достъпна.

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

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

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

Можете също да използвате съветника, за да сортирате набора резултати и да извършите просто филтриране. В последната стъпка на съветника можете да изберете да върнете данните в Excel или допълнително да прецизирате заявката в Microsoft Query. След като създадете заявката, можете да я изпълните или в Excel, или в Microsoft Query.

За да стартирате съветника за заявки, изпълнете следните стъпки:

1. В раздела Данни , в групата Получаване на външни данни щракнете върху От други източници и след това щракнете върху От Microsoft Query.

2. В диалоговия прозорец Избор на източник на данни се уверете, че е отметнато квадратчето Използвай съветника за заявки за създаване/редактиране на заявки .

3. Щракнете двукратно върху източника на данни, който искате да използвате.

   -или-

   Щракнете върху източника на данни, който искате да използвате, и след това щракнете върху OK.

Работа директно в Microsoft Query за други типове заявки     Ако искате да създадете по-сложна заявка, отколкото позволява съветникът за заявки, можете да работите директно в Microsoft Query. Можете да използвате Microsoft Query, за да преглеждате и променяте заявки, които сте започнали да създавате в съветника за заявки, или можете да създавате нови заявки, без да използвате съветника. Работете директно в Microsoft Query, когато искате да създадете заявки, които правят следното:

• Избиране на конкретни данни от поле     В голяма база данни може да искате да изберете някои от данните в поле и да пропуснете данните, които не са ви необходими. Ако например имате нужда от данни за два от продуктите в едно поле, които съдържат информация за много продукти, можете да използвате критерии, за да изберете данни само за двата продукта, които искате.

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

• Съединяване на данни по различни начини     Вътрешните съединения, които съветникът за заявки създава, са най-често срещаният тип съединение, използвано при създаването на заявки. Понякога обаче искате да използвате друг тип съединение. Ако например имате таблица с информация за продажби на продукти и таблица с информация за клиента, вътрешно съединение (типът, създаден от съветника за заявки) ще попречи на извличането на записи за клиенти за клиенти, които не са направили покупка. С помощта на Microsoft Query можете да съедините тези таблици, така че да бъдат извлечени всички записи за клиенти, заедно с данните за продажби за тези клиенти, които са направили покупки.

За да стартирате Microsoft Query, изпълнете следните стъпки.

1. В раздела Данни , в групата Получаване на външни данни щракнете върху От други източници и след това щракнете върху От Microsoft Query.

2. В диалоговия прозорец Избор на източник на данни се уверете, че квадратчето за отметка Използване на съветника за заявки за създаване/редактиране на заявки е изчистено.

3. Щракнете двукратно върху източника на данни, който искате да използвате.

   -или-

   Щракнете върху източника на данни, който искате да използвате, и след това щракнете върху OK.

Повторно използване и споделяне на заявки     Както в съветника за заявки, така и в Microsoft Query можете да запишете вашите заявки като .dqy файл, който можете да модифицирате, използвате повторно и споделяте. Excel може да отваря директно .dqy файлове, което позволява на вас или други потребители да създавате допълнителни диапазони от външни данни от същата заявка.

За да отворите записана заявка от Excel:

1. В раздела Данни , в групата Получаване на външни данни щракнете върху От други източници и след това щракнете върху От Microsoft Query. Показва се диалоговият прозорец Избор на източник на данни .

2. В диалоговия прозорец Избор на източник на данни щракнете върху раздела Заявки .

3. Щракнете двукратно върху записаната заявка, която искате да отворите. Заявката се показва в Microsoft Query.

Ако искате да отворите записана заявка и Microsoft Query вече е отворена, щракнете върху менюто Файл на Microsoft Query и след това щракнете върху Отвори.

Ако щракнете двукратно върху .dqy файл, Excel се отваря, изпълнява заявката и след това вмъква резултатите в нов работен лист.

Ако искате да споделите резюме или отчет на Excel, базиран на външни данни, можете да дадете на други потребители работна книга, която съдържа диапазон от външни данни, или можете да създадете шаблон. Шаблонът ви позволява да запишете резюмето или отчета, без да записвате външните данни, така че файлът да е по-малък. Външните данни се извличат, когато потребител отвори шаблона за отчет.

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

Форматиране на извлечени данни     В Excel можете да използвате инструменти, като например диаграми или автоматични междинни суми, за представяне и обобщаване на данните, извлечени от Microsoft Query. Можете да форматирате данните и форматирането се запазва, когато обновявате външните данни. Можете да използвате собствени етикети на колони вместо имената на полетата и да добавяте номера на редове автоматично.

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

Можете да включите тази опция (или да я изключите отново) по всяко време:

1. Щракнете върху Файл > Опции > Разширени.

2. В секцията Опции за редактиране изберете квадратчето за отметка Разширяване на форматите и формулите за диапазон от данни . За да изключите отново автоматичното форматиране на диапазон от данни, изчистете отметката от това квадратче.

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

Най-горе на страницата