Не удается получить данные из базы данных Excel

Обновлено: 04.07.2024

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Исходный номер базы знаний: 904953

Симптомы

В Microsoft Office Access 2007, Microsoft Office Access 2003 или Microsoft Access 2002 нельзя изменять, добавлять или удалять данные в таблицах, связанных с книгой Microsoft Excel.

Кроме того, такое поведение может наблюдаться при выполнении любого из следующих условий:

Вы создаете запрос для извлечения данных из таблиц, связанных с книгой Excel.
Вы создаете форму, которая обращается к данным из таблиц, связанных с книгой Excel.
Вы используете DAO или ADO для программного обновления таблиц, связанных с книгой Excel.

При выполнении запроса на обновление записей в связанной книге Excel вы получаете следующее сообщение:

Операция должна использовать обновляемый запрос

При использовании DAO для программного обновления таблиц, связанных с книгой Excel, вы получаете следующее сообщение:

Ошибка выполнения '3027' Не удается обновить. База данных или объект доступны только для чтения.

При попытке обновить связанные данные в ADO появляется то же сообщение, но номер ошибки может быть примерно таким:

При выполнении запроса на вставку записей в книгу Excel появляется следующее сообщение об ошибке, даже если книга Excel не связана с базой данных Access:

Операция должна использовать обновляемый запрос

Причина

Это ожидаемое поведение происходит, когда выполняется одно из следующих условий:

Вы используете Office Access 2007.
У вас установлен пакет обновления 2 (SP2) для Microsoft Office 2003 или более поздний пакет обновления, а также любые обновления Access 2003, выпущенные после пакета обновления 2 (SP2) для Office 2003.
Вы установили обновление для Access 2002 (KB904018) от 18 октября 2005 года.
Вы установили приложение среды выполнения Access, включающее Microsoft Office 2003 с пакетом обновления 2 (SP2) или более поздней версии, все обновления Access 2003, выпущенные после Office 2003 с пакетом обновления 2 (SP2), или обновление для Access 2002 (KB904018), которое от 18 октября 2005 года или позже.

Временное решение

Чтобы обойти это ожидаемое поведение, используйте один из следующих методов.

Способ 1. Используйте Microsoft Excel

Откройте связанную книгу Excel в Microsoft Excel и внесите изменения в книгу. После внесения изменений сохраните их и закройте книгу.

Способ 2. Используйте Office Access 2007, Access 2003 или Access 2002

Импортируйте связанную книгу Excel в Access, а затем внесите изменения в данные. После внесения изменений экспортируйте данные в виде файла Excel .xls.

Чтобы экспортировать таблицу из Access в Excel, запустите в Access следующий код.

В этом примере кода замените следующие заполнители:

является заполнителем для запроса, который вы запускаете для таблиц в базе данных Access. Результат запроса экспортируется в книгу Excel.

является заполнителем для рабочего листа в Excel, в который вы хотите экспортировать результат. Чтобы запустить этот пример кода, нажмите CTRL+G, чтобы открыть окно Immediate, введите WorkArounds и нажмите клавишу ВВОД.

Дополнительная информация

По юридическим причинам корпорация Майкрософт отключила в Access 2003 и Access 2002 функцию, позволяющую пользователям изменять данные в связанных таблицах, которые указывают на диапазон в книге Excel. Однако если вы вносите изменения непосредственно в книгу Excel, они отображаются в связанной таблице в Access.

Аннотация. Это первое руководство из серии, предназначенной для ознакомления и ознакомления с Excel и его встроенными функциями обработки и анализа данных. Эти учебные пособия создают и улучшают рабочую книгу Excel с нуля, строят модель данных, а затем создают потрясающие интерактивные отчеты с помощью Power View. Учебники предназначены для демонстрации функций и возможностей Microsoft Business Intelligence в Excel, сводных таблицах, Power Pivot и Power View.

Примечание. В этой статье описываются модели данных в Excel 2013. Однако те же функции моделирования данных и Power Pivot, представленные в Excel 2013, применимы и к Excel 2016.

Из этих руководств вы узнаете, как импортировать и исследовать данные в Excel, создавать и улучшать модель данных с помощью Power Pivot, а также создавать интерактивные отчеты с помощью Power View, которые вы можете публиковать, защищать и делиться ими.

Учебники из этой серии следующие:

Импорт данных в Excel 2013 и создание модели данных

В этом руководстве вы начнете с пустой книги Excel.

В этом руководстве представлены следующие разделы:

В конце этого руководства есть тест, который вы можете пройти, чтобы проверить свои знания.

В этой серии руководств используются данные, описывающие олимпийские медали, принимающие страны и различные олимпийские спортивные мероприятия. Мы предлагаем вам пройти все уроки по порядку. Кроме того, в учебных пособиях используется Excel 2013 с включенным Power Pivot. Для получения дополнительной информации об Excel 2013 нажмите здесь. Чтобы получить инструкции по включению Power Pivot, нажмите здесь.

Импорт данных из базы данных

Мы начинаем это руководство с пустой рабочей тетради. Цель этого раздела — подключиться к внешнему источнику данных и импортировать эти данные в Excel для дальнейшего анализа.

Давайте начнем с загрузки некоторых данных из Интернета. Данные описывают олимпийские медали и представляют собой базу данных Microsoft Access.

Нажмите на следующие ссылки, чтобы загрузить файлы, которые мы используем в этой серии руководств. Загрузите каждый из четырех файлов в легкодоступное место, например Загрузки или Мои документы, или в новую созданную вами папку:
> OlympicMedals.accdb Доступ к базе данных
> OlympicSports.xlsx Книга Excel
> Population.xlsx Книга Excel
> DiscImage_table.xlsx Книга Excel

В Excel 2013 откройте пустую книгу.

Нажмите ДАННЫЕ > Получить внешние данные > Из Access. Лента динамически настраивается в зависимости от ширины вашей книги, поэтому команды на вашей ленте могут выглядеть немного иначе, чем на следующих экранах. На первом экране показана лента, когда книга широкая, а на втором изображена книга, размер которой был изменен, чтобы занимать только часть экрана.

Выберите загруженный файл OlympicMedals.accdb и нажмите «Открыть». Появится следующее окно Select Table, отображающее таблицы, найденные в базе данных. Таблицы в базе данных аналогичны рабочим листам или таблицам в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Затем нажмите ОК.

Появится окно импорта данных.

Примечание. Обратите внимание на флажок в нижней части окна, который позволяет добавить эти данные в модель данных, как показано на следующем экране. Модель данных создается автоматически, когда вы одновременно импортируете или работаете с двумя или более таблицами. Модель данных объединяет таблицы, что позволяет выполнять расширенный анализ с использованием сводных таблиц, Power Pivot и Power View. Когда вы импортируете таблицы из базы данных, существующие отношения базы данных между этими таблицами используются для создания модели данных в Excel. Модель данных прозрачна в Excel, но вы можете просматривать и изменять ее напрямую с помощью надстройки Power Pivot. Модель данных более подробно рассматривается далее в этом руководстве.

Выберите параметр "Отчет сводной таблицы", который импортирует таблицы в Excel и подготовит сводную таблицу для анализа импортированных таблиц, и нажмите "ОК".

После импорта данных создается сводная таблица с использованием импортированных таблиц.

Данные, импортированные в Excel, и модель данных, созданная автоматически, готовы к исследованию данных.

Изучение данных с помощью сводной таблицы

Изучать импортированные данные легко с помощью сводной таблицы. В сводной таблице вы перетаскиваете поля (аналогичные столбцам в Excel) из таблиц (например, таблицы, которые вы только что импортировали из базы данных Access) в разные области сводной таблицы, чтобы настроить способ представления ваших данных. Сводная таблица состоит из четырех областей: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

Может потребоваться некоторое время, чтобы определить, в какую область следует перетаскивать поле. Вы можете перетаскивать столько полей из своих таблиц, сколько хотите, пока сводная таблица не представит ваши данные так, как вы хотите их видеть. Не стесняйтесь исследовать, перетаскивая поля в разные области сводной таблицы; базовые данные не затрагиваются при упорядочении полей в сводной таблице.

Давайте рассмотрим данные об олимпийских медалях в сводной таблице, начав с олимпийских медалистов, систематизированных по дисциплине, типу медали и стране или региону спортсмена.

В полях сводной таблицы разверните таблицу медалей, нажав стрелку рядом с ней.Найдите поле NOC_CountryRegion в расширенной таблице медалей и перетащите его в область COLUMNS. НОК – национальные олимпийские комитеты, которые являются организационными подразделениями страны или региона.

Затем из таблицы "Дисциплины" перетащите "Дисциплина" в область ROWS.

Давайте отфильтруем дисциплины, чтобы отобразить только пять видов спорта: стрельбу из лука, дайвинг, фехтование, фигурное катание и конькобежный спорт. Это можно сделать в области полей сводной таблицы или с помощью фильтра меток строк в самой сводной таблице.

Нажмите в любом месте сводной таблицы, чтобы убедиться, что выбрана сводная таблица Excel. В списке полей сводной таблицы, где развернута таблица «Дисциплины», наведите указатель мыши на ее поле «Дисциплина», и справа от поля появится стрелка раскрывающегося списка. Щелкните раскрывающийся список, щелкните (Выбрать все), чтобы удалить все варианты, затем прокрутите вниз и выберите Стрельба из лука, Дайвинг, Фехтование, Фигурное катание и Конькобежный спорт. Нажмите "ОК".

Или в разделе «Ярлыки строк» сводной таблицы щелкните раскрывающийся список рядом с «Ярлыками строк» в сводной таблице, нажмите («Выбрать все»), чтобы удалить все выбранные элементы, затем прокрутите вниз и выберите «Стрельба из лука», «Прыжки в воду», «Фехтование», «Фигурное катание» и « Конькобежный спорт. Нажмите "ОК".

В полях сводной таблицы из таблицы медалей перетащите медаль в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически меняет Medal на Count of Medal.

В таблице Медали снова выберите Медаль и перетащите ее в область ФИЛЬТРОВ.

Давайте отфильтруем сводную таблицу, чтобы отобразить только страны или регионы с более чем 90 медалями. Вот как.

В сводной таблице нажмите раскрывающийся список справа от меток столбцов.

Выберите «Фильтры значений» и выберите «Больше чем…».

Введите 90 в последнее поле (справа). Нажмите "ОК".

Ваша сводная таблица выглядит следующим образом.

С небольшими усилиями у вас теперь есть базовая сводная таблица, включающая поля из трех разных таблиц. Что делало эту задачу такой простой, так это уже существующие отношения между таблицами. Поскольку в исходной базе данных существовали связи между таблицами и поскольку вы импортировали все таблицы в рамках одной операции, Excel мог воссоздать эти связи между таблицами в своей модели данных.

Но что, если ваши данные получены из разных источников или импортированы позднее? Как правило, вы можете создавать отношения с новыми данными на основе совпадающих столбцов. На следующем шаге вы импортируете дополнительные таблицы и узнаете, как создавать новые связи.

Импорт данных из электронной таблицы

Теперь давайте импортируем данные из другого источника, на этот раз из существующей книги, а затем укажем отношения между нашими существующими данными и новыми данными. Отношения позволяют анализировать наборы данных в Excel и создавать интересные и захватывающие визуализации из импортируемых данных.

Давайте начнем с создания пустого листа, а затем импортируем данные из книги Excel.

Вставьте новый лист Excel и назовите его «Спорт».

Перейдите к папке, содержащей загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

Выберите и скопируйте данные на Лист1. Если вы выберете ячейку с данными, например ячейку A1, вы можете нажать Ctrl + A, чтобы выбрать все соседние данные. Закройте книгу OlympicSports.xlsx.

На рабочем листе "Спорт" поместите курсор в ячейку A1 и вставьте данные.

Не снимая выделения с данных, нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы. Вы также можете отформатировать данные в виде таблицы на ленте, выбрав ГЛАВНАЯ > Форматировать как таблицу. Поскольку у данных есть заголовки, выберите Моя таблица имеет заголовки в появившемся окне Создать таблицу, как показано здесь.

Форматирование данных в виде таблицы имеет много преимуществ. Таблице можно присвоить имя, чтобы ее было легко идентифицировать. Вы также можете устанавливать связи между таблицами, что позволяет исследовать и анализировать их в сводных таблицах, Power Pivot и Power View.

Назовите таблицу. В ИНСТРУМЕНТАХ ДЛЯ ТАБЛИЦ > ДИЗАЙН > Свойства найдите поле Имя таблицы и введите Спорт. Рабочая книга выглядит следующим образом.

Сохраните книгу.

Импорт данных с помощью копирования и вставки

Теперь, когда мы импортировали данные из книги Excel, давайте импортируем данные из таблицы, найденной на веб-странице, или из любого другого источника, из которого мы можем копировать и вставлять в Excel.На следующих шагах вы добавите города-организаторы Олимпийских игр из таблицы.

Вы можете использовать Microsoft Query для получения данных из внешних источников. Используя Microsoft Query для извлечения данных из ваших корпоративных баз данных и файлов, вам не нужно повторно вводить данные, которые вы хотите проанализировать в Excel. Вы также можете автоматически обновлять отчеты и сводки Excel из исходной исходной базы данных всякий раз, когда база данных обновляется новой информацией.

С помощью Microsoft Query вы можете подключаться к внешним источникам данных, выбирать данные из этих внешних источников, импортировать эти данные на свой лист и обновлять данные по мере необходимости, чтобы данные вашего листа были синхронизированы с данными во внешних источниках.< /p>

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

Microsoft Office предоставляет драйверы, которые можно использовать для извлечения данных из следующих источников данных:

Службы Microsoft SQL Server Analysis Services (поставщик OLAP)

Доступ к Microsoft Office

Microsoft Office Excel

Базы данных текстовых файлов

Вы также можете использовать драйверы 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, перейдите на вкладку Базы данных.

Чтобы указать источник данных куба OLAP, щелкните вкладку Кубы OLAP. Эта вкладка доступна, только если вы запускали Microsoft Query из Excel.

Нажмите , а затем нажмите OK.

Отображается диалоговое окно "Создать новый источник данных".

На шаге 1 введите имя для идентификации источника данных.

На шаге 2 выберите драйвер для типа базы данных, которую вы используете в качестве источника данных.

Если внешняя база данных, к которой вы хотите получить доступ, не поддерживается драйверами ODBC, которые устанавливаются вместе с Microsoft Query, вам необходимо получить и установить совместимый с Microsoft Office драйвер ODBC от стороннего поставщика, например производитель базы данных. Обратитесь к поставщику базы данных за инструкциями по установке.

Для баз данных OLAP не требуются драйверы ODBC.При установке Microsoft Query устанавливаются драйверы для баз данных, созданных с помощью служб Microsoft SQL Server Analysis Services. Для подключения к другим базам данных OLAP необходимо установить драйвер источника данных и клиентское программное обеспечение.

Нажмите «Подключиться», а затем укажите информацию, необходимую для подключения к источнику данных. Для баз данных, рабочих книг Excel и текстовых файлов предоставляемая информация зависит от выбранного типа источника данных. Вас могут попросить указать имя для входа, пароль, версию используемой базы данных, расположение базы данных или другую информацию, относящуюся к типу базы данных.

Используйте надежные пароли, в которых сочетаются прописные и строчные буквы, цифры и символы. Слабые пароли не смешивают эти элементы. Надежный пароль: Y6dh!et5. Слабый пароль: House27. Пароли должны состоять из 8 и более символов. Лучше использовать парольную фразу, состоящую из 14 и более символов.

Очень важно помнить свой пароль. Если вы забудете свой пароль, Microsoft не сможет его восстановить. Храните записанные вами пароли в безопасном месте, отдельно от информации, которую они помогают защитить.

После ввода необходимой информации нажмите "ОК" или "Готово", чтобы вернуться в диалоговое окно "Создать новый источник данных".

Если в вашей базе данных есть таблицы и вы хотите, чтобы конкретная таблица автоматически отображалась в мастере запросов, установите флажок для шага 4, а затем выберите нужную таблицу.

Если вы не хотите вводить имя пользователя и пароль при использовании источника данных, установите флажок Сохранить мой идентификатор пользователя и пароль в определении источника данных. Сохраненный пароль не зашифрован. Если флажок недоступен, обратитесь к администратору базы данных, чтобы определить, можно ли сделать этот параметр доступным.

Примечание по безопасности. Избегайте сохранения информации для входа в систему при подключении к источникам данных. Эта информация может храниться в виде обычного текста, и злоумышленник может получить доступ к информации, чтобы поставить под угрозу безопасность источника данных.

После выполнения этих шагов имя вашего источника данных появится в диалоговом окне "Выбор источника данных".

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

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

Чтобы запустить мастер запросов, выполните следующие действия.

Убедитесь, что в диалоговом окне "Выбор источника данных" установлен флажок "Использовать мастер запросов для создания/редактирования запросов".

Дважды щелкните источник данных, который вы хотите использовать.

Выберите источник данных, который вы хотите использовать, а затем нажмите кнопку "ОК".

Работайте непосредственно в Microsoft Query для других типов запросов. Если вы хотите создать более сложный запрос, чем позволяет мастер запросов, вы можете работать непосредственно в Microsoft Query. Вы можете использовать Microsoft Query для просмотра и изменения запросов, которые вы начинаете создавать в мастере запросов, или вы можете создавать новые запросы без использования мастера. Работайте непосредственно в Microsoft Query, если вы хотите создать запросы, выполняющие следующие действия:

Выбор определенных данных из поля В большой базе данных может потребоваться выбрать некоторые данные в поле и опустить данные, которые вам не нужны. Например, если вам нужны данные для двух продуктов в поле, содержащем информацию для многих продуктов, вы можете использовать критерии, чтобы выбрать данные только для двух нужных вам продуктов.

Получать данные на основе разных критериев при каждом выполнении запроса. Если вам нужно создать один и тот же отчет или сводку Excel для нескольких областей в одних и тех же внешних данных, например отдельный отчет о продажах для каждого региона, вы можете создать параметр запрос. При выполнении запроса с параметрами вам будет предложено ввести значение, которое будет использоваться в качестве критерия при выборе записей запросом. Например, запрос с параметрами может предложить вам указать конкретный регион, и вы можете повторно использовать этот запрос для создания каждого из ваших региональных отчетов о продажах.

Различные способы соединения данных Внутренние соединения, создаваемые мастером запросов, являются наиболее распространенным типом соединений, используемым при создании запросов. Однако иногда вы хотите использовать другой тип соединения.Например, если у вас есть таблица с информацией о продажах продуктов и таблица с информацией о клиентах, внутреннее соединение (тип, созданный мастером запросов) предотвратит получение записей о клиентах, не совершивших покупку. С помощью Microsoft Query вы можете объединить эти таблицы, чтобы получить все записи о клиентах, а также данные о продажах для тех клиентов, которые совершили покупки.

Чтобы запустить Microsoft Query, выполните следующие действия.

Убедитесь, что в диалоговом окне "Выбор источника данных" снят флажок "Использовать мастер запросов для создания/редактирования запросов".

Дважды щелкните источник данных, который вы хотите использовать.

Выберите источник данных, который вы хотите использовать, а затем нажмите кнопку "ОК".

Повторное использование и совместное использование запросов Как в мастере запросов, так и в Microsoft Query вы можете сохранять свои запросы в виде файла .dqy, который можно изменять, повторно использовать и делиться им. Excel может напрямую открывать файлы .dqy, что позволяет вам или другим пользователям создавать дополнительные диапазоны внешних данных из того же запроса.

Чтобы открыть сохраненный запрос из Excel:

На вкладке "Данные" в группе "Получить внешние данные" нажмите "Из других источников", а затем нажмите "Из Microsoft Query". Отобразится диалоговое окно «Выбор источника данных».

В диалоговом окне "Выбор источника данных" перейдите на вкладку "Запросы".

Дважды щелкните сохраненный запрос, который хотите открыть. Запрос отображается в Microsoft Query.

Если вы хотите открыть сохраненный запрос, а Microsoft Query уже открыт, откройте меню "Файл Microsoft Query" и выберите "Открыть".

Если дважды щелкнуть файл .dqy, Excel откроется, выполнит запрос, а затем вставит результаты на новый лист.

Если вы хотите поделиться сводкой или отчетом Excel, основанным на внешних данных, вы можете предоставить другим пользователям рабочую книгу, содержащую диапазон внешних данных, или создать шаблон. Шаблон позволяет сохранить сводку или отчет без сохранения внешних данных, что позволяет уменьшить размер файла. Внешние данные извлекаются, когда пользователь открывает шаблон отчета.

После создания запроса в Мастере запросов или Microsoft Query вы можете вернуть данные на лист Excel. Затем данные становятся диапазоном внешних данных или отчетом сводной таблицы, который можно форматировать и обновлять.

Форматирование извлеченных данных В Excel можно использовать такие инструменты, как диаграммы или автоматические промежуточные итоги, для представления и суммирования данных, извлеченных с помощью Microsoft Query. Вы можете форматировать данные, и ваше форматирование сохраняется при обновлении внешних данных. Вы можете использовать собственные метки столбцов вместо имен полей и автоматически добавлять номера строк.

Excel может автоматически форматировать новые данные, которые вы вводите в конце диапазона, чтобы они соответствовали предыдущим строкам. Excel также может автоматически копировать формулы, повторяющиеся в предыдущих строках, и распространять их на дополнительные строки.

Примечание. Чтобы можно было расширить диапазон до новых строк, форматы и формулы должны появиться как минимум в трех из пяти предшествующих строк.

Вы можете включить эту опцию (или снова отключить) в любое время:

Нажмите «Файл» > «Параметры» > «Дополнительно».

В Excel 2007: нажмите кнопку Microsoft Office , выберите «Параметры Excel» и выберите категорию «Дополнительно».

В разделе «Параметры редактирования» установите флажок «Расширить форматы и формулы диапазона данных». Чтобы снова отключить автоматическое форматирование диапазона данных, снимите этот флажок.

Обновление внешних данных При обновлении внешних данных вы запускаете запрос для получения любых новых или измененных данных, которые соответствуют вашим спецификациям. Вы можете обновить запрос как в Microsoft Query, так и в Excel. Excel предоставляет несколько вариантов обновления запросов, в том числе обновление данных при каждом открытии книги и автоматическое обновление через определенные промежутки времени. Вы можете продолжать работать в Excel во время обновления данных, а также проверять состояние во время обновления данных. Дополнительные сведения см. в статье Обновление подключения к внешним данным в Excel.

Основное преимущество подключения к внешним данным заключается в том, что вы можете периодически анализировать эти данные в Microsoft Office Excel без повторного копирования данных, что может занять много времени и привести к ошибкам. После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) свои книги Excel из исходного источника данных всякий раз, когда источник данных обновляется новой информацией.

Важно! На вашем компьютере могут быть отключены подключения к внешним данным. Чтобы подключаться к данным при открытии книги, необходимо включить подключения к данным с помощью панели центра управления безопасностью. Дополнительные сведения см. в разделах Добавление, удаление или просмотр доверенного издателя, Надежные документы и Просмотр моих параметров и настроек в центре управления безопасностью Microsoft Office.

Чтобы открыть диалоговое окно «Существующие подключения», выберите «Данные» > «Существующие подключения».Вы можете отобразить все доступные вам подключения и таблицы Excel в своей книге. Вы можете открыть соединение или таблицу из списка, а затем использовать диалоговое окно «Импорт данных», чтобы решить, как вы хотите импортировать данные.

Примечание. Вы также можете открыть диалоговое окно «Существующие подключения» из диалогового окна «Изменить источник данных сводной таблицы». Дополнительные сведения об изменении источника данных для сводной таблицы см. в разделе Изменение источника данных для сводной таблицы.

В раскрывающемся списке Показать выполните одно из следующих действий:

Все Чтобы отобразить все подключения, щелкните Все подключения. Это выбрано по умолчанию.

Рабочая книга Чтобы отобразить только список недавно использовавшихся подключений, нажмите Подключения в этой рабочей книге.

Этот список создается из соединений, которые вы уже определили, которые вы создали с помощью диалогового окна "Выбор источника данных" мастера подключения к данным или которые вы ранее выбрали в качестве соединения в этом диалоговом окне.

Компьютер. Чтобы отобразить только подключения, доступные на вашем компьютере, выберите Файлы подключения на этом компьютере.

Этот список создается из папки "Мои источники данных", которая обычно хранится в папке "Мои документы" на вашем компьютере.

Сеть Чтобы отобразить только подключения, доступные из файла подключения, доступного из сети, выберите Файлы подключения в сети. Этот список создается из библиотеки подключений к данным Excel (DCL) на сайте Microsoft SharePoint Services. DCL — это библиотека документов на сайте SharePoint Services, которая содержит коллекцию файлов подключения к данным Office (ODC) (ODC). Как правило, DCL настраивается администратором сайта, который также может настроить сайт SharePoint для отображения файлов ODC из этого DCL в диалоговом окне «Внешние подключения». Дополнительные сведения см. в справке центра администрирования SharePoint Services.

Выберите нужное подключение и нажмите кнопку Открыть. Появится диалоговое окно «Импорт данных».

Решите, как вы хотите импортировать данные, а затем нажмите кнопку ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, щелкните знак вопроса (?).

Совет. Если вы не видите нужное соединение, вы можете создать его. Нажмите «Найти больше», а затем в диалоговом окне «Выбор источника данных» нажмите «Новый источник», чтобы запустить мастер подключения данных и выбрать источник данных, к которому вы хотите подключиться.

Примечание. Если вы выбираете подключение из категорий «Файлы подключения в сети» или «Файлы подключения на этом компьютере», файл подключения копируется в книгу как новое подключение к книге, а затем используется в качестве информации о новом подключении.< /p>

В раскрывающемся списке Показать выполните одно из следующих действий:

Все Чтобы отобразить все соединения, щелкните Таблицы рабочей книги. Это выбрано по умолчанию.

Подключения. Чтобы отобразить таблицы, определяемые только подключением, выберите "Таблицы подключений".

Рабочая книга Чтобы отобразить таблицу в рабочей книге, выберите Таблицы рабочей книги.

Выберите нужное подключение и нажмите кнопку Открыть. Появится диалоговое окно «Импорт данных».

Читайте также: