Вы можете изменить свойства внешнего диапазона данных, чтобы управлять данными, импортированными на лист через подключение к внешнему источнику данных, такому как база данных, веб-запрос или текстовый файл.
Дополнительные сведения о диапазонах внешних данных и их свойствах
Диапазон внешних данных — это определенное имя или имя таблицы, которое определяет расположение данных, которые вводятся на лист. При подключении к внешним данным Excel автоматически создает диапазон внешних данных. Единственным исключением из этого является отчет сводной таблицы, подключенный к источнику данных. Отчет сводной таблицы не использует внешний диапазон данных. В Excel можно отформатировать и выложить диапазон внешних данных или использовать его в вычислениях, как и любые другие данные.
Свойства диапазона внешних данных
Внешний диапазон данных имеет дополнительные свойства (не путать со свойствами запроса или соединения), которые можно использовать для управления данными, например сохранение форматирования ячеек и ширины столбца. Эти свойства внешнего диапазона данных можно изменить, щелкнув Свойства в группе Подключения на вкладке Данные . Затем внесите изменения, выполнив следующие действия:
- Для диапазонов внешних данных, созданных из Microsoft Query и мастера подключения к данным, используйте диалоговое окно Свойства внешних данных .
- Для диапазонов внешних данных, созданных из импортированного текстового файла или веб-запроса, который извлекает ДАННЫЕ HTML, используйте диалоговое окно Свойства диапазона внешних данных .
- Для диапазонов внешних данных, созданных на основе веб-запроса, который извлекает XML-данные, используйте диалоговое окно Свойства карты XML .
Поиск диапазона внешних данных
- Выберите лист, на котором требуется выполнить поиск по диапазону внешних данных.
- В строке формул щелкните стрелку рядом с полем имя и выберите имя нужного диапазона внешних данных.
Изменение имени диапазона внешних данных
- ВыберитеДиспетчер именформул>.
- В диалоговом окне Диспетчер имен щелкните имя диапазона внешних данных и нажмите кнопку Изменить. Можно также дважды щелкнуть имя.
- В диалоговом окне Изменение имени введите новое имя ссылки в поле Имя .
Примечание
Кнопка Закрыть закрывает только диалоговое окно Диспетчер имен . Вам не нужно нажимать кнопку Закрыть , чтобы зафиксировать уже внесенные изменения.
Изменение базового запроса для диапазона внешних данных
Вы можете изменить базовый запрос для внешнего диапазона данных, созданного из Microsoft Query, импортированного текстового файла, веб-запроса или мастера подключения к данным.
Изменение базового запроса для диапазона внешних данных, созданного из Microsoft Query
- Щелкните ячейку в диапазоне внешних данных, для которой требуется изменить базовый запрос.
- Выберите Свойства данных>.
- В диалоговом окне Свойства внешних данных щелкните Свойства подключения
- В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
Дополнительные сведения о создании и редактировании запросов в Microsoft Query см. в справке по Microsoft Query.
Изменение базового запроса для внешнего диапазона данных, созданного из импортированного текстового файла
- Щелкните ячейку в диапазоне внешних данных, для которой требуется изменить базовый запрос.
- Выберите Подключения к данным>& вкладке Запросы>Подключения , щелкните правой кнопкой мыши нужное подключение и выберите Свойства.
- В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
- В диалоговом окне Импорт текстового файла нажмите кнопку Импорт.
- Внесите изменения в импортированный текстовый файл в мастере импорта текста и нажмите кнопку Готово.
Дополнительные сведения об импорте текстовых файлов см. в разделе Импорт и экспорт текстовых файлов.
Изменение базового запроса для диапазона внешних данных, созданного из веб-запроса
- Щелкните ячейку в диапазоне внешних данных, для которой требуется изменить базовый запрос.
- Выберите Свойства данных>.
- Выберите Подключения к данным>& вкладке Запросы>Подключения , щелкните правой кнопкой мыши нужное подключение и выберите Свойства.
- В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
- Внесите изменения в веб-запрос в диалоговом окне Изменение веб-запроса и нажмите кнопку Готово.
Дополнительные сведения о создании и редактировании веб-запросов см. в разделе Подключение к веб-странице.
Изменение базового запроса для внешнего диапазона данных, созданного с помощью мастера подключения к данным
Щелкните ячейку в диапазоне внешних данных, для которой требуется изменить базовый запрос.
Выберите Свойства данных>.
В диалоговом окне Свойства внешних данных щелкните Свойства подключения
В диалоговом окне Свойства подключения откройте вкладку Определение .
Выполните одно из следующих действий:
В поле Тип команды щелкните Таблица , а затем в текстовом поле Команда измените значение на имя соответствующей таблицы, представления или запроса.
В поле Тип команды щелкните SQL или По умолчанию , а затем в текстовом поле Команда измените инструкцию SQL.
Примечание
В зависимости от того, как было определено подключение, поле Тип команды может быть недоступно (оно отображается неактивным).
Копирование диапазона внешних данных и его базового запроса
- В строке формул щелкните стрелку рядом с полем имя и выберите имя диапазона внешних данных, который требуется скопировать.
Для таблицы Excel выберите имя диапазона, а затем нажмите клавиши CTRL+A, чтобы выбрать заголовки таблицы.
Если вы хотите включить метки столбцов или формулы, которые не входят в диапазон внешних данных, выделите ячейки, содержащие метки столбцов или формулы, которые требуется скопировать. Щелкните стрелку рядом с полем "Имя" в строке формул и щелкните имя диапазона внешних данных, который требуется скопировать. - Выберите Главная>Копировать.
- Перейдите к книге, в которую нужно вставить диапазон внешних данных.
- Щелкните левую верхнюю ячейку области вставки.
Чтобы убедиться, что внешние данные не заменяют существующие данные, убедитесь, что на листе нет данных под или справа от выбранной ячейки. - На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.
Примечание
Если вы копируете только часть внешнего диапазона данных, базовый запрос не копируется, и скопированные данные не могут быть обновлены.
Указание способа добавления новых данных во внешний диапазон данных
Вы можете управлять обработкой меньшего или большего набора данных, возвращаемого в Excel при обновлении данных.
Выберите Свойства данных>.
В диалоговом окне Свойства внешнего диапазона данных в разделе Если количество строк в диапазоне данных изменяется при обновлении, выберите один из следующих вариантов:
Вставка ячеек для новых данных, удаление неиспользуемых ячеек
Примечание
- При добавлении одной или нескольких строк в источник данных ячейки непосредственно под диапазоном внешних данных перемещаются вниз, но ячейки справа от диапазона внешних данных не перемещаются.
- При удалении одной или нескольких строк в источнике данных ячейки непосредственно под диапазоном внешних данных перемещаются вверх, но ячейки справа от диапазона внешних данных не перемещаются.
Вставка целых строк для новых данных, очистка неиспользуемых ячеек
Примечание
- При добавлении одной или нескольких строк в источник данных ячейки непосредственно под и справа от диапазона внешних данных перемещаются вниз.
- При удалении одной или нескольких строк в источнике данных ячейки непосредственно под и справа от диапазона внешних данных не перемещаются.
Перезапись существующих ячеек новыми данными, очистка неиспользуемых ячеек
Примечание
- При добавлении одной или нескольких строк в источник данных ячейки непосредственно в диапазоне внешних данных перезаписываются, но ячейки справа от диапазона внешних данных не перемещаются.
- При удалении одной или нескольких строк в источнике данных ячейки непосредственно под и справа от диапазона внешних данных не перемещаются.
Указание форматирования и макета данных
Выберите Свойства данных>.
В разделе Форматирование и макет данных выполните одно или несколько из следующих действий.
Чтобы включить имена полей в качестве первой строки, выберите поле Включить имена полей проверка.
Чтобы добавить столбец номеров строк, установите флажок Включить номера строк проверка.
Примечание
Это поле проверка недоступно для импортированного текстового файла, XML-файла или веб-запроса.
Чтобы сохранить примененное форматирование ячейки, выберите поле Сохранить форматирование ячейки проверка.
Примечание
Для веб-запроса этот параметр автоматически удаляется при выборе полного форматирования HTML в диалоговом окне Параметры . Диалоговое окно Параметры открывается из диалогового окна Изменение запроса .
Чтобы сохранить заданные ширины столбцов, установите флажок Настроить ширину столбца проверка.
Чтобы сохранить сортировку, фильтрацию и макет столбцов, установите флажок Сохранить сортировку, фильтрацию и макет столбца проверка.
Примечание
Это поле проверка недоступно для веб-запроса.
Чтобы сохранить примененную фильтрацию столбцов, установите флажок Сохранить фильтр столбцов проверка.
Примечание
Это поле проверка доступно только для веб-запроса, основанного на XML-данных.
Замораживание данных во внешнем диапазоне данных
При замораживании диапазона внешних данных данные сохраняются, но не базовый запрос, поэтому замороженный диапазон внешних данных не может быть обновлен.
Замораживание данных в таблице Excel
- Щелкните лист, содержащий таблицу Excel, из которой требуется удалить подключение к данным.
- В строке формул щелкните стрелку рядом с полем "Имя", а затем выберите имя диапазона внешних данных, из которого требуется удалить подключение к данным. Затем выбирается диапазон данных.
- На вкладке Сервис в группе Данные внешней таблицы нажмите кнопку Отменить связь. Диапазон данных остается и по-прежнему имеет то же имя, но подключение удаляется.
Удаление диапазона внешних данных
- Щелкните лист, содержащий внешний диапазон данных, который требуется удалить.
- В строке формул щелкните стрелку рядом с полем имя, а затем выберите имя диапазона внешних данных, который требуется удалить.
Если диапазон внешних данных является таблицей Excel, нажмите клавиши CTRL+A, чтобы выделить всю таблицу. - Чтобы удалить диапазон внешних данных, нажмите клавишу DELETE.
- Чтобы удалить базовый запрос, нажмите кнопку Да при запросе Excel.