Управление диапазонами внешних данных и их свойствами

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

В этой статье

Дополнительные сведения о диапазоны внешних данных и свойств

Поиск диапазона внешних данных

Изменение имени диапазона внешних данных

Изменение запроса диапазона внешних данных

Копирование диапазона внешних данных и его запроса

Выбор способа добавления новых данных в диапазон внешних данных

Указание форматирования и разметки данных

Копирование формул при расширении диапазона внешних данных

Закрепление диапазона внешних данных

Настройка имен полей в диапазоне внешних данных

Удаление диапазона внешних данных

Дополнительные сведения о внешних диапазонах и свойствах

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

Свойства диапазона внешних данных

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

  • Для диапазонов внешних данных, созданных с помощью мастера подключения данных и Microsoft Query, используйте диалоговое окно Свойства внешних данных.

  • Для диапазонов внешних данных, созданных из импортированного текстового файла или веб-запроса, получающего данные HTML, используйте диалоговое окно Свойства диапазона внешних данных.

  • Для диапазонов внешних данных, созданных из веб-запроса, получающего данные XML, используйте диалоговое окно Свойства карты XML.

Использование шаблонов с диапазонами внешних данных

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

К началу страницы

Поиск диапазона внешних данных

  1. Выделите лист, на котором требуется найти диапазон внешних данных.

  2. В строка формул щелкните стрелку возле поля Имя, а затем выберите имя нужного диапазона внешних данных.

К началу страницы

Изменение имени диапазона внешних данных

  1. На вкладке Формулы в группе Определенные имена выберите команду Диспетчер имен.

    Изображение ленты Excel

  2. В диалоговом окне Диспетчер имен выберите имя диапазона внешних данных, а затем нажмите кнопку Изменить. Также можно дважды щелкнуть имя.

  3. В диалоговом окне Изменить имя введите новое имя для ссылки, отображаемой в поле Имя.

Примечание : Кнопка Закрыть закрывает только диалоговое окно Диспетчер имен. Для фиксации сделанных изменений нажимать кнопку Закрыть не нужно.

К началу страницы

Изменение запроса диапазона внешних данных

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

Изменение запроса диапазона внешних данных, созданного с помощью Microsoft Query

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

  2. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  3. В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения свойства .

  4. В диалоговом окне Свойства подключения откройте вкладку Определение, а затем нажмите кнопку Изменить запрос.

Дополнительные сведения о создании и редактировании запросов в Microsoft Query содержатся в справочной системе Microsoft Query.

Изменение запроса для диапазона внешних данных, созданного посредством импорта текстового файла

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

  2. На вкладке Данные в группе Подключения нажмите кнопку Подключения.

    Изображение ленты Excel

  3. В диалоговом окне Подключения к книге нажмите кнопку Свойства.

  4. В диалоговом окне Свойства подключения откройте вкладку Определение, а затем нажмите кнопку Изменить запрос.

  5. В диалоговом окне Импорт текстового файла нажмите кнопку Импорт.

  6. Внесите изменения в окне мастера текстов (импорт), а затем нажмите кнопку Готово.

    Дополнительные сведения об импорте текстовых файлов см. в статье Импорт и экспорт текстовых файлов.

Изменение запроса для диапазона внешних данных, созданного из веб-запроса

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

  2. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  3. В диалоговом окне Подключения к книге нажмите кнопку Свойства.

  4. В диалоговом окне Свойства подключения откройте вкладку Определение, а затем нажмите кнопку Изменить запрос.

  5. Измените веб-запрос в диалоговом окне Изменить веб-запрос и нажмите кнопку Завершить.

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

Изменение запроса для диапазона внешних данных, созданного с помощью мастера подключения данных

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

  2. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  3. В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения свойства .

  4. В диалоговом окне Свойства откройте вкладку Определения.

  5. Выполните одно из следующих действий:

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

    • В поле Тип команды выберите значение SQL или По умолчанию, а затем в поле Текст команды измените оператор SQL.

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

К началу страницы

Копирование диапазона внешних данных и его запроса

  1. В строке формул щелкните стрелку возле поля Имя, а затем выберите имя нужного диапазона внешних данных.

    Для таблицы Excel выберите имя диапазона, а затем нажмите клавиши CTRL + A, чтобы выделить заголовок таблицы.

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

  2. На вкладке Главная в группе Буфер обмена нажмите кнопку Копировать.

    Изображение ленты Excel

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

  4. Выделите ячейку в верхнем левом углу область вставки.

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

  5. На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

Примечание : При копировании части диапазона внешних данных базового запрос не копируются и скопированные данные, не обновляются.

К началу страницы

Выбор способа добавления новых данных в диапазон внешних данных

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

  1. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

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

    • Добавить новые строки, удалить неиспользуемые ячейки

      Примечания : 

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

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

    • Добавить новые строки, очистить пустые ячейки

      Примечания : 

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

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

    • Заменить существующие ячейки, очистить пустые ячейки

      Примечания : 

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

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

К началу страницы

Указание форматирования и разметки данных

  1. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  2. В группе Формат и разметка данных установите один или несколько флажков, указанных ниже.

    • Чтобы имена полей использовались как первая строка, установите флажок Включить имена полей.

      Примечание : Этот флажок доступен только для диапазона внешних данных, преобразованного из программы из пакета Microsoft Office 2003, или для диапазона внешних данных, созданного программно.

    • Чтобы добавить номера строк или столбцов, установите флажок Включить номера строк.

      Примечание : Этот флажок недоступен для импортированного текстового файла, файла XML или веб-запроса.

    • Для сохранения примененного форматирования ячейки установите флажок Автоформат данных.

      Примечание : В случае веб-запроса этот флажок автоматически снимается при выборе значения Полное форматирование HTML в диалоговом окне Параметры. Окно Параметры можно открыть из диалогового окна Изменить запрос.

    • Для сохранения установленной ширины столбцов установите флажок Корректировка ширины столбцов.

    • Для сохранения примененной сортировки столбцов, фильтров и разметки установите флажок Сохранить сведения о сортировке/фильтре/формате для столбца.

      Примечание : Этот флажок недоступен для веб-запросов.

    • Для сохранения примененного фильтра установите флажок Сохранить фильтр столбца.

      Примечание : Этот флажок доступен только для веб-запроса, основанного на данных XML.

К началу страницы

Копирование формул при расширении диапазона внешних данных

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

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

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

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

  3. Выберите ячейку в диапазоне внешних данных.

  4. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  5. В диалоговом окне Свойства внешнего диапазона установите флажок заполнить формулами соседние столбцы.

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

К началу страницы

Закрепление диапазона внешних данных

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

Закрепление данных в таблице Excel

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

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

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

Закрепление данных в диапазоне

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

  1. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  2. В группе Определение запроса установите флажок Сохранить определение запроса.

Примечание : При сохранении книги из нее будет удален запрос. Однако сохраненные файлы запросов к базам данных (DQY и ODC) не удаляются. При сохранении запроса в ходе его создания в мастере запросов или в Microsoft Query файл запроса сохраняется на локальном компьютере и может быть повторно использован для загрузки внешних данных. Дополнительные сведения см. в справке Microsoft Query.

К началу страницы

Настройка имен полей в диапазоне внешних данных

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

  1. Вставьте пустую строку над диапазоном внешних данных, выполнив одно из действий, указанных ниже.

    • Выделите ячейку, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки щелкните стрелку рядом с кнопкой Вставить и выберите команду Вставить строки на лист. Поскольку была выделена одна ячейка, новая строка будет вставлена над текущей строкой.

    • Щелкните область выделения строки, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки нажмите кнопку Вставить. Поскольку была выделена вся строка, новая строка будет вставлена над выделенной строкой.

  2. В ячейки пустой строки введите текст подписей.

  3. Щелкните ячейку в диапазоне внешних данных.

  4. На вкладке Данные в группе Подключения нажмите кнопку Свойства.

    Изображение ленты Excel

  5. В диалоговом окне Свойства диапазона данных в группе Формат и разметка данных снимите флажок Включить имена полей и нажмите кнопку OK.

  6. Чтобы удалить существующие имена полей и обновить диапазон внешних данных, нажмите кнопку Обновить данные Изображение кнопки .

Примечание : При извлечении данных из базы данных изменения имен столбцов, вносимые в Microsoft Query, сохраняются в диапазоне внешних данных. Сведения об изменении имен столбцов в Microsoft Query см. в справке Microsoft Query.

К началу страницы

Удаление диапазона внешних данных

  1. Откройте лист, содержащий диапазон внешних данных, который требуется удалить.

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

    Если диапазон внешних данных является таблицей Excel, нажмите сочетание клавиш CTRL + A, чтобы выделить всю таблицу.

  3. Чтобы удалить диапазон внешних данных, нажмите клавишу DELETE.

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

К началу страницы

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×