Bazaprogram.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Внешние данные excel

Внешние данные excel

На этом шаге мы рассмотрим как обрабатываются данные, возвращаемые из MS Query и записанные в рабочий лист.

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

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

В диалоговом окне Свойства внешнего диапазона (рис. 1) можно корректировать различные свойства диапазона внешних данных.

Рис. 1. Диалогово окно Свойства внешнего диапазона

Чтобы вызвать это диалоговое окно, указатель ячейки должен находиться в пределах диапазона внешних данных. Доступ к диалоговому окну можно получить, воспользовавшись одним из трех методов:

  • Щелкните правой кнопкой мыши и из контекстного меню выберите команду Свойства диапазона данных .
  • Выберите команду Данные | Внешние данные | Свойства диапазона данных .
  • Щелкните на кнопке Свойства диапазона данных на панели инструментов Внешние данные (это окно появляется автоматически, когда Вы выполняете запрос).

В диалоговом окне Свойства внешнего диапазона имеются следующие опции:

  • Имя. Имя диапазона внешних данных, создаваемое Excel по умолчанию. Это имя можно изменить или оставить.
  • Определение запроса. Если флажок Сохранить определение запроса установлен, то определение запроса сохраняется вместе с диапазоном внешних данных. Если для базы данных требуется пароль, можно также задать пароль, и тогда не нужно будет его вводить при обновлении запроса.
  • Обновление экрана. Определяет, как и когда обновляются данные.
  • Формат и разметка данных. Определяет, как будет размещен диапазон внешних данных.

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

Если установлен флажок опции Сохранить определение запроса в диалоговом окне Свойства внешнего диапазона , то определение запроса сохраняется вместе с рабочей книгой. Для обновления запроса надо поместить курсор где-нибудь в таблице внешних данных, а затем выполнить одно из следующих действий:

  • Щелкнуть правой кнопкой мыши и выберать из контекстного меню команду Обновить данные .
  • Выберать команду Данные | Обновить данные .
  • Щелкнуть на кнопке Обновить данные панели инструментов Внешние данные .

Excel запустит Query и использует первоначальный запрос для получения текущих данных из внешней базы данных.

Использование нескольких запросов

Отдельная рабочая книга, если это необходимо, может содержать много диапазонов внешних данных. Каждому диапазону присваивается уникальное имя, поэтому с каждым из них можно работать независимо от других. Excel автоматически отслеживает запрос, который используется для получения каждого из диапазонов внешних данных.

Копирование или перемещение внешних данных

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

Если данные, возвращаемые по запросу, больше не нужны, их можно удалить. Для этого следует выделить весь диапазон внешних данных и выберать команду Правка | Удалить .

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

Если полученные результаты запроса неудовлетворительные, то запрос можно модифицировать. Для этого следует поместить курсор где-нибудь в пределах таблицы внешних данных, а затем использовать один из следующих способов обновления запроса:

  • Щелкнуть правой кнопкой мыши и выберать из контекстного меню команду Изменить запрос .
  • Выберать команду Данные | Внешние данные | Изменить запрос .

Excel запустит Query , после чего можно изменить первоначальный запрос. По окончании корректировки выберите команду Файл | Вернуть данные в Microsoft Excel . Excel вновь будет активизирован, выполнится модифицированный запрос, а диапазон внешних данных обновится.

На следующем шаге мы рассмотрим создание запроса в MS Query без использования Мастера запросов .

Подключение к внешним данным (импорт)

Основным преимуществом соединения с внешними данными является то, что вы можете периодически анализировать эти данные в Microsoft Office Excel без повторного анализа данных, что является операцией, которая может занять много времени и погрешности. После подключения к внешним данным вы также можете автоматически обновлять (или обновлять) книги Excel из исходного источника данных, когда источник данных обновляется с новыми данными.

Важно: Подключение к внешним данным может быть отключено на вашем компьютере. Чтобы подключаться к данным при открытии книги, включите функцию подключения к данным на панели центра управления безопасностью или сохраните книгу в надежном расположении. Дополнительные сведения можно найти в разделе Создание, удаление и изменение надежного расположения для файлов, Добавление, удаление и просмотр надежного издателя, а также Просмотр параметров и параметров в центре управления безопасностью Microsoft Office.

На вкладке данные выберите пункт существующие подключения. Откроется диалоговое окно » существующие подключения «.

Примечание: Вы также можете открыть диалоговое окно существующие соединения в диалоговом окне изменение источника данных сводной таблицы . Дополнительные сведения об изменении источника данных для сводной таблицы можно найти в разделе изменение исходных данных сводной таблицы.

В раскрывающемся списке Показать выполните одно из следующих действий:

Чтобы отобразить все подключения, выберите пункт все подключения. По умолчанию этот флажок установлен.

Чтобы отобразить только список недавно использовавшихся подключений, выберите пункт подключения в этой книге.

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

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

Этот список создается из папки Мои источники данных , которая обычно хранится в папке Мои документы на компьютере.

Чтобы отобразить только подключения, доступные из файла подключения, доступного из сети, щелкните значок файлы подключения в сети. Этот список создается из библиотеки подключений к данным Excel (ДКЛ) на сайте Microsoft SharePoint Services. ДКЛ — это библиотека документов на сайте SharePoint Services, содержащая набор файлов подключения к данным Office (ODC). Обычно ДКЛ устанавливается администратором сайта, который может также настраивать сайт SharePoint для отображения ODC-файлов из этого ДКЛ в диалоговом окне внешние подключения . Дополнительные сведения можно найти в справке по центру администрирования SharePoint Services.

Совет: Если вы не видите нужное подключение, вы можете создать подключение. Нажмите кнопку Обзори в диалоговом окне Выбор источника данных нажмите кнопку создать источник , чтобы запустить мастер подключения данных, чтобы можно было выбрать источник данных, к которому нужно подключиться.

Примечание: Если вы выбрали подключение из файлов подключения в сети или файлы подключения на этих категориях компьютеров, файл подключения копируется в книгу как новое подключение к книге и используется в качестве сведений о новом соединении.

Выберите нужное подключение и нажмите кнопку Открыть.

В диалоговом окне Импорт данных в разделе выберите способ просмотра данных в книге выполните одно из указанных ниже действий.

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

Чтобы создать таблицу для простой сортировки и фильтрации, нажмите кнопку Таблица.

Чтобы создать отчет сводной таблицы для суммирования больших объемов данных путем статистической обработки и суммирования данных, щелкните отчет сводной таблицы.

Для создания отчета сводной таблицы и отчета сводной диаграммы для наглядного представления данных щелкните Сводная диаграмма и отчет сводной таблицы.

Чтобы сохранить выбранное подключение в книге для последующего использования, выберите команду создать подключение.

С помощью параметра создать подключение можно сохранить выбранное подключение в книге для последующего использования. Например, если вы подключаетесь к источнику данных куба OLAP и хотите преобразовать ячейки сводной таблицы в формулы листа с помощью команды преобразовать в формулы (на вкладке Параметры в группе Сервис выберите пункт инструменты OLAP), вы можете использовать этот параметр, так как вам не нужно сохранять отчет сводной таблицы.

В группе Куда следует поместить данные? выполните одно из следующих действий:

Чтобы поместить отчет сводной таблицы или сводной диаграммы на существующий лист, выберите вариант существующий лист, а затем введите адрес первой ячейки в диапазоне ячеек, где нужно найти отчет сводной таблицы.

Кроме того, можно нажать кнопку Свернуть диалоговое окно , чтобы временно скрыть диалоговое окно, выбрать начальную ячейку на листе, а затем нажать кнопку Развернуть диалоговое окно .

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки a1, нажмите кнопку Новый лист.

Кроме того, вы можете изменить свойства подключения, нажав кнопку Свойства, а затем внесите изменения в диалоговом окне Свойства подключения, диапазон внешних данныхили Свойства карты XML , а затем нажмите кнопку ОК.

На вкладке данные в группе Получение внешних данных нажмите кнопку существующие подключения.

В диалоговом окне существующие подключения в раскрывающемся списке Показать выберите один из указанных ниже вариантов.

Чтобы отобразить все подключения, выберите пункт все подключения. По умолчанию этот флажок установлен.

Чтобы отобразить только список недавно использовавшихся подключений, выберите пункт подключения в этой книге.

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

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

Этот список создается из папки Мои источники данных , которая обычно хранится в папке Мои документы .

Чтобы отобразить только подключения, доступные из файла подключения, доступного из сети, щелкните значок файлы подключения в сети.

Этот список создается из библиотеки подключений к данным служб Excel (ДКЛ) на сайте Microsoft Office SharePoint Server 2007. ДКЛ — это библиотека документов на сайте Microsoft Office SharePoint Services 2007, которая содержит коллекцию файлов подключения к данным Office (ODC). Обычно ДКЛ устанавливается администратором сайта, который может также настраивать сайт SharePoint для отображения ODC-файлов из этого ДКЛ в диалоговом окне внешние подключения . Дополнительные сведения можно найти в справке по центру администрирования Office SharePoint Server 2007.

Совет: Если вы не видите нужное подключение, вы можете создать подключение. Нажмите кнопку Обзори в диалоговом окне Выбор источника данных нажмите кнопку создать источник , чтобы запустить мастер подключения данных, чтобы можно было выбрать источник данных, к которому нужно подключиться.

Примечание: Если вы выбрали подключение из файлов подключения в сети или файлы подключения на этих категориях компьютеров, файл подключения копируется в книгу как новое подключение к книге и используется в качестве данных нового подключения.

Выберите нужное подключение и нажмите кнопку Открыть.

В диалоговом окне Импорт данных в разделе выберите способ просмотра данных в книге выполните одно из указанных ниже действий.

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

Чтобы создать таблицу для простой сортировки и фильтрации, нажмите кнопку Таблица.

Чтобы создать отчет сводной таблицы для суммирования больших объемов данных путем статистической обработки и суммирования данных, щелкните отчет сводной таблицы.

Для создания отчета сводной таблицы и отчета сводной диаграммы для наглядного представления данных щелкните Сводная диаграмма и отчет сводной таблицы.

Чтобы сохранить выбранное подключение в книге для последующего использования, выберите команду создать подключение.

С помощью параметра создать подключение можно сохранить выбранное подключение в книге для последующего использования. Например, если вы подключаетесь к источнику данных куба OLAP и хотите преобразовать ячейки сводной таблицы в формулы листа с помощью команды преобразовать в формулы (на вкладке Параметры в группе Сервис выберите пункт инструменты OLAP), вы можете использовать этот параметр, так как вам не нужно сохранять отчет сводной таблицы.

В группе Куда следует поместить данные? выполните одно из следующих действий:

Чтобы поместить отчет сводной таблицы или сводной диаграммы на существующий лист, выберите вариант существующий листи введите первую ячейку в диапазоне ячеек, где нужно найти отчет сводной таблицы.

Кроме того, можно нажать кнопку Свернуть диалоговое окно , чтобы временно скрыть диалоговое окно, выбрать начальную ячейку на листе, а затем нажать кнопку Развернуть диалоговое окно .

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки a1, нажмите кнопку Новый лист.

Кроме того, вы можете изменить свойства подключения, нажав кнопку Свойства, внеся изменения в диалоговом окне свойства подключения , диапазон внешних данных или Свойства карты XML , а затем нажав кнопку ОК.

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

Excel — Подключение и получение данных с SQL сервера

Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.

Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.

Задача для получения данных в Excel

И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Настройка Excel для получения данных с SQL сервера

Настройка, делается достаточно просто, но требует определенных навыков и консультации администратора SQL сервера. Вы, конечно, можете попросить программиста настроить Excel на работу или сделать это сами, просто спросив пару пунктов, а каких мы сейчас узнаем.

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».

  • Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
  • Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

В итоге у меня загрузятся из базы вот такие данные:

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Можно вытянуть данные Excel из внешнего (и динамического).файл csv?

У меня есть скрипт, который выводит ряд показателей производительности для компьютера в a .csv. В Excel выполняет базовые статы чек на стандартное отклонение и т. д.

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

есть ли способ иметь ссылку на ячейку/таблицу внешней .csv без повторного импорта каждый раз?

6 ответов

самый простой способ сделать это, чтобы импортировать данные в Access или SQL Express, а затем использовать функции связи и функции в Excel. После первоначальной настройки, все что вам нужно сделать, это нажать кнопку Обновить все, и он будет тянуть данные и пересчитывать значения.

вы можете напрямую импортировать CSV с функцией подключения к данным,но он будет хотеть, чтобы вытащить данные В и добавить его на лист. Это будет работать, пока вы импортируете его на вторичное устройство Лист и оставьте свои формулы и расчеты на другом листе. При нажатии кнопки Обновить с помощью этого метода, он будет запрашивать имя файла, а затем повторно импортировать его, используя те же параметры, как первоначально использовались. Этот метод также обычно медленнее или непригоден для использования с большими наборами данных.

Windows

вы также можете использовать Власти Запрос что немного более гибкий путь достигнуть этих же как в @Энди-Линч ответ.

  1. Get it здесь
  2. установки
  3. перейдите на новую вкладку ленты «Power Query».
  4. либо нажать кнопку «из файла» или «из интернета» и следуйте инструкциям на экране.

см. ниже анимированный gif для превью:

MacOS

Power Query недоступен для MacOS; а также диалоговое окно открытия файла Excel «из текста» в MacOS, принимающее URL-адрес (по крайней мере, в версии 15.33).

чтобы создать такое соединение, сначала нужно создать файл веб-запроса (.iqy), as объяснил в этом руководстве, а затем импортировать его с помощью команды меню» Данные -> получить внешние данные -> выполнить сохраненный запрос».

вопрос «Как я могу ссылаться на данные в .файл csv?

мой способ был бы вырезать .csv-файл и использовать макрос, содержащий VBA и ADO, который получает данные и помещает его непосредственно в лист Excel. Источником может быть любая ODBC-совместимая база данных.

Excel 2010 имеет данные> > получить внешние данные-из различных источников данных. Тем не менее, я предпочитаю писать свой собственный код, потому что у меня есть абсолютный контроль и я могу форматировать результаты в макросе. Обновление ПО нажмите кнопку, и вы можете сделать что-нибудь с ним.

Я мог бы сообщить вам некоторые особенности, если вы скажете мне, из какой базы данных поступают метрики.

ПС посмотри на список драйверов в данных>>получение внешних данных>> существующими подключениями>> обзор >> источник >> другие дополнительные >> далее, что приводит вас в список данных свойств. Затем вы должны знать, какую исходную базу данных и какой драйвер использовать. Вы можете написать мне на cliffbeacham на Hotmail с более подробная информация.

более простой способ (спасибо этот пост) является просто импортировать данные, как вы бы для текстового файла, который вы уже скачали (то есть Data > Import Text File ), но вместо ввода имени файла вставьте URL-адрес CSV-файла.

импортируйте файл csv напрямую через File->From Text проверьте свойства соединения в Data->Connections

Читать еще:  Год в excel
Ссылка на основную публикацию
Adblock
detector