Можете да използвате Microsoft Query, за да извлечете данни от външни източници. Като използвате Microsoft Query, за да извлечете данни от вашите корпоративни бази данни и файлове, не е необходимо да въвеждате отново данните, които искате да анализирате в Excel. Можете също да обновявате вашите отчети и резюмета на Excel автоматично от първоначалната база данни източник всеки път, когато базата данни се актуализира с нова информация.
Научете повече за Microsoft Query
С помощта на Microsoft Query можете да се свържете с външни източници на данни, да изберете данни от тези външни източници, да импортирате тези данни в работния лист и да обновявате данните, ако е необходимо, за да поддържате данните в работния лист синхронизирани с данните във външните източници.
Типове бази данни, до които можете да получите достъп Можете да извлечете данни от няколко типа бази данни, включително Microsoft Office Access, Microsoft SQL Server и OLAP услугите на Microsoft SQL Server. Можете също да извлечете данни от работни книги на Excel и от текстови файлове.
Microsoft Office предоставя драйвери, които можете да използвате за извличане на данни от следните източници на данни:
- Microsoft Услуги за анализ на SQL Server (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:
В раздела " Данни ", в групата "Получаване на външни данни " щракнете върху "От други източници" и след това върху "От Microsoft Query".
Забележка
Excel 365 премести Microsoft Query в наследената група менюта на съветници . Това меню не се показва по подразбиране. За да разрешите, отидете на "Файл", " Опции", " Данни" и "разреше" в секцията "Показване на съветници за импортиране на наследени данни ".
Направете едно от следните неща:
- За да зададете източник на данни за база данни, текстов файл или работна книга на Excel, щракнете върху раздела "Бази данни ".
- За да зададете източник на данни за OLAP куб, щракнете върху раздела OLAP кубове . Този раздел е наличен само ако сте изпълнили Microsoft Query от Excel.
Щракнете двукратно върху< "Нов източник> на данни".
-или-
Щракнете върху <"Нов източник> на данни" и след това върху OK.
Показва се диалоговият прозорец "Създаване на нов източник на данни ".В стъпка 1 въведете име, за да идентифицирате източника на данни.
В стъпка 2 щракнете върху драйвер за типа на базата данни, която използвате като ваш източник на данни.
Забележка
- Ако външната база данни, до която искате достъп, не се поддържа от ODBC драйвери, които са инсталирани с Microsoft Query, трябва да получите и инсталирате съвместим с Microsoft Office ODBC драйвер от друг доставчик, като например производителя на базата данни. Свържете се с производителя на базата данни за инструкции за инсталиране.
- OLAP базите данни не изискват ODBC драйвери. Когато инсталирате Microsoft Query, се инсталират драйвери за бази данни, които са създадени с помощта на Услуги за анализ на SQL Server. За да се свържете с други OLAP бази данни, трябва да инсталирате драйвер за източник на данни и клиентски софтуер.
Щракнете върху "Свързване" и след това предоставете информацията, която е необходима, за да се свържете с вашия източник на данни. За бази данни, работни книги на Excel и текстови файлове информацията, която предоставяте, зависи от типа на източника на данни, който сте избрали. Може да бъдете помолени да предоставите име за влизане, парола, версията на базата данни, която използвате, местоположението на базата данни или друга специфична за типа на базата данни.
Важно
- Използвайте сигурни пароли, съчетаващи главни и малки букви, цифри и символи. В несигурните пароли не се смесват такива елементи. Сигурна парола: Y6dh!et5. Несигурна парола: House27. Паролите трябва да бъдат с дължина от 8 или повече знака. По-добре е да използвате фраза за достъп с 14 знака или повече.
- Изключително важно е да запомните паролата си. Ако я забравите, Microsoft не може да я извлече и да ви я предостави. Съхранявайте записаните пароли на сигурно място, далече от информацията, която защитават.
След като въведете необходимата информация, щракнете върху OK или "Готово ", за да се върнете към диалоговия прозорец "Създаване на нов източник на данни ".
Ако вашата база данни съдържа таблици и искате определена таблица да се показва автоматично в съветника за заявки, щракнете върху полето за стъпка 4 и след това щракнете върху желаната таблица.
Ако не искате да въвеждате вашето потребителско име и парола, когато използвате източника на данни, изберете квадратчето за отметка "Запиши моя потребителски ИД и парола в дефиницията на източника на данни ". Записаната парола не е шифрована. Ако квадратчето не е налично, обърнете се към администратора на базата данни, за да определите дали тази опция може да бъде направена достъпна.
Забележка
Избягвайте да записвате информация за влизане, когато се свързвате към източници на данни. Тази информация може да се съхранява като обикновен текст и злонамерен потребител би могъл да получи достъп до информацията, за да компрометира защитата на източника на данни.
След като изпълните тези стъпки, името на вашия източник на данни се появява в диалоговия прозорец "Избор на източник на данни ".
Използване на съветника за заявки за дефиниране на заявка
Използване на съветника за заявки за повечето заявки Съветникът за заявки улеснява избирането и обединяването на данни от различни таблици и полета във вашата база данни. Като използвате съветника за заявки, можете да изберете таблиците и полетата, които искате да включите. Вътрешно съединение (операция на заявка, която задава, че редове от две таблици се комбинират на базата на идентични стойности на полета) се създава автоматично, когато съветникът разпознае поле за първичен ключ в една таблица и поле със същото име във втора таблица.
Можете също да използвате съветника, за да сортирате набора резултати и да извършите просто филтриране. В последната стъпка на съветника можете да изберете да върнете данните на Excel или допълнително да прецизирате заявката в Microsoft Query. След като създадете заявката, можете да я изпълните или в Excel, или в Microsoft Query.
За да стартирате съветника за заявка, изпълнете следните стъпки:
- В раздела " Данни ", в групата "Получаване на външни данни " щракнете върху "От други източници" и след това върху "От Microsoft Query".
- В диалоговия прозорец " Избор на източник на данни " се уверете, че квадратчето за отметка "Използвай съветника за заявки за създаване/редактиране на заявки ".
- Щракнете двукратно върху източника на данни, който искате да използвате.
-или-
Щракнете върху източника на данни, който искате да използвате, и след това щракнете върху OK.
Работа директно в Microsoft Query за други типове заявки Ако искате да създадете по-сложна заявка, отколкото съветникът за заявки позволява, можете да работите директно в Microsoft Query. Можете да използвате Microsoft Query, за да преглеждате и променяте заявки, които започвате да създавате в съветника за заявки, или да създавате нови заявки без използване на съветника. Работете директно в Microsoft Query, когато искате да създадете заявки, които правят следното:
- Избиране на определени данни от поле В голяма база данни може да искате да изберете част от данните в поле и да пропуснете данни, които не са ви нужни. Ако например ви трябват данни за два от продуктите в поле, което съдържа информация за много продукти, можете да използвате критерии, за да изберете данни само за двата продукта, които желаете.
- Извличайте данни на базата на различни критерии всеки път, когато изпълнявате заявката Ако трябва да създадете един и същ отчет на Excel или резюме за няколко области в едни и същи външни данни – например отделен отчет за продажбите за всеки регион – можете да създадете параметризирана заявка. Когато изпълнявате параметризирана заявка, получавате подкана за стойност, която да се използва като критерий, когато заявката избира записи. Например параметризирана заявка може да ви подкани да въведете конкретен регион и можете да използвате повторно тази заявка за създаването на всеки от отчетите за регионалните си продажби.
- Обединяване на данни по различни начини Вътрешните съединения, които създава съветникът за заявки, са най-често използваният тип съединение, използван при създаването на заявки. Понякога обаче искате да използвате различен тип съединение. Ако например имате таблица с информация за продажби на продукти и таблица с информация за клиенти, вътрешно съединение (тип, създаден от съветника за заявки) ще попречи на извличането на записи за клиенти на клиенти, които не са направили покупка. С помощта на Microsoft Query можете да съедините тези таблици, така че да бъдат извлечени всички записи за клиенти заедно с данните за продажбите на тези клиенти, които са направили покупки.
За да стартирате Microsoft Query, изпълнете следните стъпки:
- В раздела " Данни ", в групата "Получаване на външни данни " щракнете върху "От други източници" и след това върху "От Microsoft Query".
- В диалоговия прозорец " Избор на източник на данни " се уверете, че квадратчето за отметка "Използвай съветника за заявки за създаване/редактиране на заявки " не е отметнато.
- Щракнете двукратно върху източника на данни, който искате да използвате.
-или-
Щракнете върху източника на данни, който искате да използвате, и след това щракнете върху OK.
Повторно използване и споделяне на заявки Както в съветника за заявки, така и в Microsoft Query можете да записвате заявките си като .dqy файл, който можете да модифицирате, използвате повторно и споделяте. Excel може да отваря директно .dqy файлове, което позволява на вас или на други потребители да създавате допълнителни диапазони от външни данни от същата заявка.
За да отворите записана заявка от Excel:
- В раздела " Данни ", в групата "Получаване на външни данни " щракнете върху "От други източници" и след това върху "От Microsoft Query". Показва се диалоговият прозорец "Избор на източник на данни ".
- В диалоговия прозорец " Избор на източник на данни " щракнете върху раздела "Заявки ".
- Щракнете двукратно върху записаната заявка, която искате да отворите. Заявката се показва в Microsoft Query.
Ако искате да отворите записана заявка и Microsoft Query вече е отворена, щракнете върху менюто " Файл за заявки на Microsoft" и след това щракнете върху "Отвори".
Ако щракнете двукратно върху .dqy файл, Excel се отваря, изпълнява заявката и след това вмъква резултатите в нов работен лист.
Ако искате да споделите резюме или отчет на Excel, базиран на външни данни, можете да дадете на други потребители работна книга, която съдържа диапазон от външни данни, или можете да създадете шаблон. Шаблонът ви позволява да запишете резюмето или отчета, без да записвате външните данни, така че файлът да е по-малък. Външните данни се извличат, когато потребител отвори шаблона за отчет.
Работа с данните в Excel
След като създадете заявка в съветника за заявки или в Microsoft Query, можете да върнете данните в работен лист на Excel. След това данните стават външен диапазон от данни или отчет с обобщена таблица, който можете да форматирате и обновявате.
Форматиране на извлечените данни В Excel можете да използвате инструменти, като например диаграми или автоматични междинни суми, за да представяте и обобщавате данните, извлечени от Microsoft Query. Можете да форматирате данните и вашето форматиране се запазва, когато обновявате външните данни. Можете да използвате собствени етикети на колони вместо имена на полета и да добавяте номера на редове автоматично.
Excel може автоматично да форматира новите данни, които въвеждате в края на диапазона, така че да съответстват на предишните редове. Excel може също автоматично да копира формули, които са повторени в предишните редове, и да ги разширява до допълнителни редове.
Забележка
За да се разширят до нови редове в диапазона, форматите и формулите трябва да се появят в поне три от петте предишни реда.
Можете да включите тази опция (или да изключите отново) по всяко време:
- Щракнете върху "Разширени опции> за файла>".
- В секцията "Опции за редактиране " изберете отметката "Разширяване на форматите и формулите на диапазона от данни ". За да изключите отново автоматичното форматиране на диапазон от данни, изчистете това квадратче за отметка.
Обновяване на външни данни Когато обновявате външни данни, вие изпълнявате заявка, за да извлечете всички нови или променени данни, които отговарят на вашите спецификации. Можете да обновите заявка както в Microsoft Query, така и в Excel. Excel предоставя няколко опции за обновяване на заявки, включително обновяване на данните всеки път, когато отворите работната книга, и автоматично обновяване на интервали от време. Можете да продължите да работите в Excel, докато данните се обновяват, и можете да проверите състоянието, докато данните се обновяват. За повече информация вижте "Обновяване на връзка с външни данни в Excel".