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