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