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

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

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

Диапазон внешних данных (также называемые таблица запросов) — это определенное имя или имя таблицы, которая определяет расположение данных, не будет перенесен на лист. При подключении к внешним данным, Excel автоматически создает диапазона внешних данных. Единственным исключением является отчета сводной таблицы, подключенные к источнику данных, не использующая диапазона внешних данных отчета сводной таблицы. В 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. Внесите изменения в веб-запроса в диалоговом окне Изменить веб-запрос и нажмите кнопку Готово.

Дополнительные сведения о создании и изменении веб-запросы см в разделе подключение к веб-странице (Power Query).

  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 в диалоговом окне " Параметры ". Диалоговое окно Параметры access из диалогового окна Изменить запрос.

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

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

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

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

      Примечание: Этот флажок доступен только для веб-запроса, основанного на 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.

×