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