Управление встречами, планами, бюджеты — это просто с помощью Microsoft 365.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  3. В диалоговом окне "Изменение имени" введите новое имя ссылки в поле "Имя".

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

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

    • Чтобы добавить столбец с номерами строк, выберите поле Include row numbers (Включить номера строк).

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

    • Чтобы сохранить форматирование ячеок, которое вы применяли, выберите элемент «Сохранить форматирование ячеок».

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

    • Чтобы сохранить за настраиваемую ширину столбцов, установитежжок "Изменить ширину столбца".

    • Чтобы сохранить применяемую сортировку, фильтрацию и макет столбцов, выберите вариант "Сохранить столбец" (сортировка, фильтрация или макет).

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

    • Чтобы сохранить применяемую фильтрацию столбцов, выберите поле "Сохранить фильтр столбца".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание: Следующая процедура относится только к диапазону внешних данных, преобразованного из программы 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. В диалоговом окне "Свойства диапазона внешних данных" в области "Форматирование и макет данных" разметка, включите имена полей и нажмите кнопку "ОК".

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

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

  1. Щелкните на нем нужный диапазон внешних данных.

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

    Если это таблица Excel, нажмите CTRL+A, чтобы выбрать всю таблицу.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Нужна дополнительная помощь?

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

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

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

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

×