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

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

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

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

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

  • Microsoft SQL Server Услуги за анализ (OLAP доставчик )

  • Microsoft Office Достъп

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Парадокс

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

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

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

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

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

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

Диаграма, показваща как Query използва източниците на данни

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

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

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

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

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

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

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

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

    -или-

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

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

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

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

    Забележки: 

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

    • OLAP базите данни не изискват ODBC драйвери. Когато инсталирате Microsoft Query, драйверите се инсталират за бази данни, които са създадени с помощта на Microsoft SQL Server услуги за анализ. За да се свържете с други 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. Щракнете върху Файл > Опции > Разширени.

    В Excel 2007: Щракнете върху бутона Microsoft Office бутона Изображение на бутона Office щракнете върху Excel Опциии след това щракнете върху категорията Разширени.

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

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

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

Нуждаете се от още помощ?

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

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

×