Excel источник данных запрос

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

С помощью Microsoft Query можно подключаться к внешним источникам данных, выбирать данные из них, импортировать их на свой сайт и обновлять данные, чтобы данные на вашем компьютере всегда были синхронизированы с данными во внешних источниках.

Типы баз данных, к которые можно получить доступ     Вы можете извлекать данные из баз данных нескольких типов, включая Microsoft Office Access, Microsoft SQL Server и Microsoft SQL Server OLAP Services. Вы также можете получать данные из Excel книг и текстовых файлов.

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

  • Microsoft SQL Server Analysis Services (поставщик OLAP )

  • Microsoft Office Доступа

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Парадокс

  • Базы данных текстовых файлов

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

Выбор данных из базы данных     Чтобы получить данные из базы данных, создайте запрос ( вопрос о данных, хранимых во внешней базе данных). Например, если данные хранятся в базе данных Access, может потребоваться узнать данные о продажах для определенного продукта по регионам. Вы можете получить часть данных, выбрав только данные для продукта и региона, которые вы хотите проанализировать.

С помощью Microsoft Query можно выбрать нужные столбцы данных и импортировать только эти данные в Excel.

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

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

Диаграмма использования источников данных в Microsoft Query

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

Что такое источник данных?     Источник данных — это хранимый набор сведений, позволяющий Excel и Microsoft Query подключаться к внешней базе данных. При использовании Microsoft Query для работы с источником данных вы даете источнику данных имя, а затем ука вашему имени и расположению базы данных или сервера, тип базы данных, а также сведения о вашем учете и пароле. Эти сведения также включают имя драйвера OBDC или драйвера источника данных — программы, которая создает подключения к базе данных определенного типа.

Чтобы настроить источник данных с помощью Microsoft Query:

  1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.

  2. Выполните одно из указанных ниже действий.

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

    • Чтобы указать источник данных куба OLAP, перейдите на вкладку Куба OLAP. Эта вкладка доступна, только если вы запустили Microsoft Query из Excel.

  3. Дважды щелкните <новый источник данных>.

    ИЛИ

    Щелкните <новый источник>и нажмите кнопку ОК.

    Отобразилось диалоговое окно Создание источника данных.

  4. На шаге 1 введите имя для определения источника данных.

  5. На шаге 2 выберите драйвер для типа базы данных, используемой в качестве источника данных.

    Примечания: 

    • Если внешняя база данных, к которой вы хотите получить доступ, не поддерживается драйверами ODBC, установленными с помощью Microsoft Query, необходимо получить и установить драйвер ODBC, совместимый с Microsoft Office, у стороного поставщика, например производителя базы данных. За инструкциями по установке обратитесь к поставщику базы данных.

    • Для баз данных OLAP драйверы ODBC не требуются. При установке Microsoft Query драйверы устанавливаются для баз данных, созданных с Microsoft SQL Server Analysis Services. Чтобы подключиться к другим базам данных OLAP, необходимо установить драйвер источника данных и клиентское программное обеспечение.

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

    Важно: 

    • Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях не используются сочетания таких элементов. Надежный пароль: Y6dh!et5. Ненадежный пароль: House27. Пароль должен состоять не менее чем из 8 знаков. Лучше всего использовать парольную фразу длиной не менее 14 знаков.

    • Очень важно запомнить свой пароль. Если вы забудете пароль, корпорация Майкрософт не сможет его восстановить. Все записанные пароли следует хранить в надежном месте отдельно от сведений, для защиты которых они предназначены.

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

  8. Если в базе данных есть таблицы и вы хотите, чтобы определенная таблица автоматически отображалась в мастере запросов, щелкните поле для шага 4 и выберите таблицу.

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

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

После выполнения этих действий имя источника данных появится в диалоговом окне Выбор источника данных.

Использование мастера запросов для большинства запросов     Мастер запросов упрощает выбор и сбор данных из разных таблиц и полей базы данных. С помощью мастера запросов можно выбрать таблицы и поля, которые вы хотите включить. Внутреннее объединить (операция запроса, которая определяет, что строки из двух таблиц объединяются на основе одинаковых значений полей) создается автоматически, когда мастер распознает поле первичного ключа в одной таблице и поле с одинаковым именем во второй таблице.

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

Чтобы запустить мастер запросов, выполните следующие действия:

  1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.

  2. Убедитесь в том, что в диалоговом окне Выбор источника данных был выбран вариант Использовать мастер запросов для создания и изменения запросов.

  3. Дважды щелкните нужный источник данных.

    ИЛИ

    Выберите нужный источник данных и нажмите кнопку ОК.

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

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

  • Извлечение данных на основе различных критериев при каждом запуске запроса     Если вам нужно создать один и тот же Excel отчет или сводку для нескольких областей в одном и том же внешнем данных , например для отдельного отчета о продажах для каждого региона, можно создать запрос с параметрами. При запуске запроса с параметрами запрашивается значение, которое будет применяться в качестве критерия при выборе записей. Например, запрос с параметрами может потребовать ввести определенный регион и повторно использовать его для создания каждого из региональных отчетов о продажах.

  • Различными способами для сбора данных     Внутренние соединители, которые создает мастер запросов, являются наиболее распространенными типами, используемыми при создании запросов. Однако иногда необходимо использовать другой тип. Например, если у вас есть таблица сведений о продажах продуктов и таблица сведений о клиенте, внутреннее присоединиться (тип, созданный с помощью мастера запросов) предотвращает истощить записи клиентов, которые не сделали покупку. С помощью Microsoft Query вы можете объединить эти таблицы, чтобы получить все записи клиентов, а также данные о продажах для тех клиентов, которые сделали покупки.

Чтобы запустить Microsoft Query, выполните следующие действия:

  1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query.

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

  3. Дважды щелкните нужный источник данных.

    ИЛИ

    Выберите нужный источник данных и нажмите кнопку ОК.

Повторное использование запросов и общий доступ     Как в мастере запросов, так и в Microsoft Query можно сохранить запросы в качестве DQY-файла, который можно изменять, повторно использовать и совместно использовать. Excel можно открывать DQY-файлы напрямую, что позволяет создавать дополнительные диапазоны внешних данных из того же запроса.

Чтобы открыть сохраненный запрос из Excel:

  1. На вкладке Данные в группе Внешние данные нажмите кнопку Из другихисточников и выберите из Microsoft Query. Отобразилось диалоговое окно Выбор источника данных.

  2. В диалоговом окне Выбор источника данных перейдите на вкладку Запросы.

  3. Дважды щелкните сохраненный запрос, который вы хотите открыть. Запрос отображается в Microsoft Query.

Если вы хотите открыть сохраненный запрос, а Microsoft Query уже открыт, откройте меню Файл запроса Майкрософт и нажмите кнопку Открыть.

Если дважды щелкнуть DQY-файл, Excel откроется, выполняется запрос, а затем вставляет результаты на новый.

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

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

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

Excel форматирование новых данных, которые вы введите в конце диапазона, в соответствие с предыдущими строками. Excel также можно автоматически копировать формулы, которые повторялись в предыдущих строках, и распространять их на дополнительные строки.

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

Вы можете включить этот параметр (или отключить его снова) в любое время:

  1. Щелкните Файл > Параметры > Дополнительно.

    В Excel 2007: нажмите кнопку Microsoft Office Изображение кнопки Office , Excel Параметры, а затем выберите категорию Дополнительные.

  2. В разделе Параметры правки выберите параметр Расширить форматы диапазонов данных и формулы. Чтобы снова отключить автоматическое форматирование диапазона данных, сверните этот квадрат.

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

К началу страницы

Нужна дополнительная помощь?

Источник

Содержание

  • Создание SQL запроса в Excel
    • Способ 1: использование надстройки
    • Способ 2: использование встроенных инструментов Excel
    • Способ 3: подключение к серверу SQL Server
  • Вопросы и ответы

SQL в Microsoft Excel

SQL – популярный язык программирования, который применяется при работе с базами данных (БД). Хотя для операций с базами данных в пакете Microsoft Office имеется отдельное приложение — Access, но программа Excel тоже может работать с БД, делая SQL запросы. Давайте узнаем, как различными способами можно сформировать подобный запрос.

Читайте также: Как создать базу данных в Экселе

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

Способ 1: использование надстройки

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

Скачать надстройку XLTools

  1. После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.

    2. Принятие лицензиного соглашение на использование компонента Microsoft NET Framework 4

  2. После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.

    3. Загрузка обязательных файлов

  3. Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».

    4. Окно подтверждения согласия на установку надстройки

  4. Затем начинается процедура установки непосредственно самой надстройки.

    5. Установка надстройки

  5. После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».

    6. Закрытие окна установщика надстройки

  6. Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».

    7. Окно лицензии надстройки XLTools

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

    8. Окно активации пробного периода надстройки XLTools

  8. Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».

    9. Активация пробной лицензии надстройки XLTools

  9. После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
    Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.

    10. Переход к созданию умной таблицы в Microsoft Excel

    Lumpics.ru

  10. Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».

    11. Окно форматирования таблицы в Microsoft Excel

  11. После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.

    12. Наименование таблицы по умолчанию в Microsoft Excel

  12. При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.

    13. Измененное наименование таблицы в Microsoft Excel

  13. После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».

    14. Переход во вкладку XLTools в Microsoft Excel

  14. После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».

    15. Переход в окно выполнения SQL надстройки XLTools в программе Microsoft Excel

  15. Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

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

    Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

    • ORDER BY – сортировка значений;
    • JOIN – объединение таблиц;
    • GROUP BY – группировка значений;
    • SUM – суммирование значений;
    • DISTINCT – удаление дубликатов.

    Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

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

Окно выполнения SQL запроса надстройкой XLTools в Microsoft Excel

Урок: «Умные» таблицы в Экселе

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

  1. Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».

    2. Переход во вкладку Данные в Microsoft Excel

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

    3. Переход в Мастер подключения данных в Microsoft Excel

  3. Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».

    4. Окно Мастера подключения данных в Microsoft Excel

  4. Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».

    5. Окно выбора типа источника Мастера подключения данных в Microsoft Excel

  5. Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».

    6. Окно выбора базы данных в Microsoft Excel

  6. Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».

    7. Окно выбора таблицы базы данных в Microsoft Excel

  7. После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».

    8. Окно сохранения файла подключения данных в Microsoft Excel

  8. На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
    • Таблица;
    • Отчёт сводной таблицы;
    • Сводная диаграмма.

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

    После того, как все настройки импорта указаны, жмем на кнопку «OK».

    9. Окно импорта данных в Microsoft Excel

  9. Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.

    10. Переход в окно подключений в Microsoft Excel

  10. После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.

    11. Переход в свойства базы данных в Microsoft Excel

  11. Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».

    12. Окно свойств подключения в Microsoft Excel

  12. После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

Отправка запроса к базе данных в окне подключения к книге в Microsoft Excel

Способ 3: подключение к серверу SQL Server

Кроме того, посредством инструментов Excel существует возможность соединения с сервером SQL Server и посыла к нему запросов. Построение запроса не отличается от предыдущего варианта, но прежде всего, нужно установить само подключение. Посмотрим, как это сделать.

  1. Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».

    2. Переход к окну подключения к серверу SQL Server в Microsoft Excel

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

Окно Мастера подключения данных в программе Microsoft Excel

Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек. Каждый пользователь может выбрать тот вариант, который удобнее для него и является более подходящим для решения конкретно поставленной задачи. Хотя, возможности надстройки XLTools, в целом, все-таки несколько более продвинутые, чем у встроенных инструментов Excel. Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

Теперь немного о действиях, совершенных мной с целью подключения к базе:

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

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

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

В итоге получаем данные прямо из базы, что и требовалось.

Теперь наше подключение будет находиться в книге и доступно в меню «(1) Данные» -> «(2) Подключения».

Как видно из кнопок формы, здесь мы можем:

«(1) Добавить» в книгу новое подключение из уже существующих;

«(2) Удалить» подключение из книги;

Отредактировать «(3) Свойства» подключения;

«(4) Обновить» данные, предоставляемые подключением.

Подытоживая весь вышеописанный опыт, хочется сказать, что конечно же по функционалу и кругу решаемых задач MS Excel не сравнится с такими системами как MS SQL Server Management Studio, Oracle SQL Developer и подобными. Но, в условиях ограниченного времени и доступных инструментов, при необходимости быстро получить доступ к данным из базы, MS Excel вполне справляется.

Источник

Содержание

  1. Как это работает
  2. Подключаем внешние данные из интернет
  3. Импорт внешних данных Excel 2010
  4. Отличите нового и старого мастера импорта
  5. Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel
  6. Примеры использования функции БИЗВЛЕЧЬ в Excel
  7. Тип данных: Числовые значения MS Excel
  8. Классификация типов данных
  9. Текстовые значения
  10. Дата и время
  11. Логические данные
  12. Разновидности типов данных
  13. Число
  14. Текст
  15. Ошибки
  16. Подключение к внешним данным
  17. Подключение к базе данных
  18. Импорт данных из базы данных Microsoft Access
  19. Импорт данных с веб-страницы
  20. Копировать-вставить данные из Интернета
  21. Импорт данных из текстового файла
  22. Импорт данных из другой книги
  23. Импорт данных из других источников
  24. Задача для получения данных в Excel

Как это работает

Инструменты для импорта расположены во вкладке меню «Данные».
Если подключение отключено, перейдите:
Далее:
На вкладке «Центр управления» перейдите:

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

В Excel 2013 и более поздних версиях, по умолчанию для импорта информации из внешних источников используется надстройка Power Query. Как это работает? Перейдите:
Пропишите адрес сайта, с которого импортируются данные:
Выберите что отобразится, нажмите кнопку «Загрузить».
Информация подгрузится в лист Excel. Работайте с ними как с простым документом: используйте формулы графики, сводные таблицы.
Для обновления нажмите ПКМ по таблице:
Или:

Импорт внешних данных Excel 2010

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

Отличите нового и старого мастера импорта

Преимущества Power Query:

  1. Поддерживается работа с большим числом страниц;
  2. Промежуточная обработка информации перед загрузкой на лист;
  3. Информация импортируется быстрее.

Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:

  1. Заголовки таблицы должны находиться в первой строке.
  2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
  3. Объединения ячеек в любых строках запрещены.
  4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
  5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

Таким образом, практически любая таблица в Excel может быть преобразована в базу данных. Ее строки являются записями, а столбцы – полями данных.

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.

Примеры использования функции БИЗВЛЕЧЬ в Excel

Пример 1. В таблице, которую можно рассматривать как БД, содержатся данные о различных моделях смартфонов. Найти название бренда смартфона, который содержит процессор с минимальным числом ядер.

Вид таблиц данных и критериев:

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

=МИН(СТОЛБЕЦ(B1))

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

В результате получим следующее:

В ячейке A4 запишем следующую формулу:

Описание аргументов:

  • A8:F15 – диапазон ячеек, в которых хранится БД;
  • 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
  • A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

Результат вычислений:

При изменении значений в таблице параметров условий мы будем автоматически получать выборку соответственных им результатов.

Тип данных: Числовые значения MS Excel

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

Фактически, к числовым типам данных относятся:

  • сами числа (и целые и дробные и отрицательные и даже записанные в виде процентов)
  • дата и время

Несколько особенностей числовых типов данных

Если введенное число не помещается в ячейку, то оно будет представлено в экспоненциальной форму представления, здорово пугающей неподготовленных пользователей. Например, гигантское число 4353453453453450 х 54545 в ячейку будет записано в виде 2,37459Е+20. Но, как правило в «жизни» появление «странных чисел» в ячейках excel свидетельствует о простой ошибке.

Если число или дата не помещается в ячейку целиком, вместо цифр в ней появляются символы ###. В этом случае «лечение» ещё более простое — нужно просто увеличить ширину столбца таблицы.

Иногда есть необходимостью записать число как текст, например в случае записи всевозможных артикулов товаров и т.п. дело в том, что если вы запишите 000335 в ячейку, Excel посчитав это значение числом, сразу же удалит нули, превратив артикул в 335. Чтобы этого не произошло, просто поместите число в кавычки — это будет сигналом для Excel, что содержимое ячейки надо воспринимать как текст, то есть выводить также, как его ввел пользователь. Естественно, производить с таким числом математических операций нельзя.

Что представляет собой дата в MS Excel?

Если с числами все более-менее понятно, то даты имеют несколько особенностей, о которых стоит упомянуть. Для начала, что такое «дата» с точки зрения MS Excel? На самом деле все не так уж и просто.

Дата в Excel — это число дней, отсчитанных до сегодняшнего дня, от некой начальной даты. По умолчанию этой начальной датой считается 1 января 1900 года.

А что же текущее время? Ещё интереснее — за точку отсчета каждых суток берется 00:00:00, которое представляется как 1. А дальше, эта единичка уменьшается, по мере того как уменьшается оставшееся в сутках время. Например 12.00 дня это с точки зрения MS Excel 0,5 (прошла половина суток), а 18.00 — 0,25 (прошли 3 четверти суток).

В итоге, дата 17 июня 2019 года, 12:30, «языком экселя» выглядит как 43633 (17.06.19) + 0,52 (12:30), то есть число 43633,52.

Как превратить число в текст? Поместите его в кавычки!

Классификация типов данных

Тип данных — это характеристика информации, хранимой на листе. На основе этой характеристики программа определяет, каким образом обрабатывать то или иное значение.

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

В свою очередь константы делятся на пять групп:

  • Текст;
  • Числовые данные;
  • Дата и время;
  • Логические данные;
  • Ошибочные значения.

Текстовые значения

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

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

Дата и время

Ещё одним типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Он характеризуется тем, что с его помощью можно указывать на листе и проводить расчеты с датами и временем. Примечательно, что при вычислениях этот тип данных принимает сутки за единицу. Причем это касается не только дат, но и времени. Например, 12:30 рассматривается программой, как 0,52083 суток, а уже потом выводится в ячейку в привычном для пользователя виде.

Существует несколько видов форматирования для времени:

  • ч:мм:сс;
  • ч:мм;
  • ч:мм:сс AM/PM;
  • ч:мм AM/PM и др.

Аналогичная ситуация обстоит и с датами:

  • ДД.ММ.ГГГГ;
  • ДД.МММ
  • МММ.ГГ и др.

Есть и комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.

Также нужно учесть, что программа отображает как даты только значения, начиная с 01.01.1900.

Логические данные

Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.

Разновидности типов данных

Выделяются две большие группы типов данных:

  • константы – неизменные значения;
  • формулы – значения, которые меняются в зависимости от изменения других.

В группу “константы” входят следующие типы данных:

  • числа;
  • текст;
  • дата и время;
  • логические данные;
  • ошибки.

Число

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

  • числовой;
  • денежный;
  • финансовый;
  • процентный;
  • дробный;
  • экспоненциальный.

Формат ячейки можно задать двумя способами:

  1. Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
  2. В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.

Для каждого из форматов, перечисленных выше (за исключением дробного), можно задать количество знаков после запятой, а для числового – к тому же, включить разделитель групп разрядов.

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

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

Текст

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

Ввод текстовой информации происходит таким же образом, как и числовой. Если текст не помещается в рамках выбранной ячейки, он будет перекрывать соседние (если они пустые).

Ошибки

В некоторых случаях пользователь может видеть в Excel ошибки, которые бывают следующих видов:

  • #ДЕЛ/О! – результат деления на число 0
  • #Н/Д – введены недопустимые данные;

  • #ЗНАЧ! – использование неправильного вида аргумента в функции;
  • #ЧИСЛО! – неверное числовое значение;
  • #ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
  • #ИМЯ? – неправильное имя в формуле;
  • #ПУСТО! – неправильно указан адрес дапазона.

Подключение к внешним данным

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

Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.

Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.

Свойства Таблицы, подключенной к внешним данным

Подключение к базе данных

Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.

Подключение к базе данных SQL Server

При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.

Существует много различных типов доступных источников данных, и иногда шаблоны соединений по умолчанию, представленные Excel, не работают.

Импорт данных из базы данных Microsoft Access

Мы научимся импортировать данные из базы данных MS Access. Следуйте инструкциям ниже

Шаг 1 – Откройте новую пустую книгу в Excel.

Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.

Шаг 3 – Нажмите « Доступ» в группе «Получить внешние данные». Откроется диалоговое окно « Выбор источника данных ».

Шаг 4 – Выберите файл базы данных Access, который вы хотите импортировать. Файлы базы данных Access будут иметь расширение .accdb.

Откроется диалоговое окно «Выбор таблицы», в котором отображаются таблицы, найденные в базе данных Access. Вы можете импортировать все таблицы в базе данных одновременно или импортировать только выбранные таблицы на основе ваших потребностей анализа данных.

Шаг 5 – Установите флажок Включить выбор нескольких таблиц и выберите все таблицы.

Шаг 6 – Нажмите ОК. Откроется диалоговое окно « Импорт данных ».

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

  • Таблица
  • Отчет сводной таблицы
  • PivotChart
  • Power View Report

У вас также есть возможность – только создать соединение . Далее отчет по сводной таблице выбран по умолчанию.

Excel также дает вам возможность поместить данные в вашу книгу –

  • Существующий лист
  • Новый лист

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

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

  • Если вы выберете « Таблица» , опция «Существующая рабочая таблица» будет отключена, будет выбрана опция « Новая рабочая таблица», и Excel создаст столько таблиц, сколько будет импортировано таблиц из базы данных. Таблицы Excel отображаются в этих таблицах.

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

    Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей сводной таблицы вместе с полями в каждой таблице.

  • Если вы выберете PivotChart , Excel импортирует таблицы в рабочую книгу и создаст пустую PivotChart для отображения данных в импортированных таблицах. У вас есть возможность создать сводную диаграмму на существующем или новом листе.

    Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей PivotChart вместе с полями в каждой таблице.

  • Если вы выберите Power View Report , Excel импортирует таблицы в рабочую книгу и создаст Power View Report в новой рабочей таблице. В последующих главах вы узнаете, как использовать отчеты Power View для анализа данных.

    Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей Power View Report вместе с полями в каждой таблице.

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

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

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

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

Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей сводной таблицы вместе с полями в каждой таблице.

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

Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей PivotChart вместе с полями в каждой таблице.

Если вы выберите Power View Report , Excel импортирует таблицы в рабочую книгу и создаст Power View Report в новой рабочей таблице. В последующих главах вы узнаете, как использовать отчеты Power View для анализа данных.

Таблицы Excel для импортированных таблиц данных не будут отображаться в книге. Однако вы найдете все таблицы данных в списке полей Power View Report вместе с полями в каждой таблице.

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

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

Импорт данных с веб-страницы

Иногда вам может понадобиться использовать данные, которые обновляются на веб-сайте. Вы можете импортировать данные из таблицы на веб-сайте в Excel.

Шаг 1 – Откройте новую пустую книгу в Excel.

Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.

Шаг 3 – Нажмите « Из Интернета» в группе « Получить внешние данные ». Откроется диалоговое окно « Новый веб-запрос ».

Шаг 4 – Введите URL-адрес веб-сайта, с которого вы хотите импортировать данные, в поле рядом с адресом и нажмите «Перейти».

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

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

Шаг 7 – Нажмите кнопку «Импорт» после того, как вы выбрали то, что вы хотите.

Откроется диалоговое окно « Импорт данных ».

Шаг 8 – Укажите, куда вы хотите поместить данные и нажмите Ok.

Шаг 9 – Организовать данные для дальнейшего анализа и / или представления.

Копировать-вставить данные из Интернета

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

Шаг 1 – Вставьте новый лист.

Шаг 2 – Скопируйте данные с веб-страницы и вставьте их на лист.

Шаг 3 – Создайте таблицу с вставленными данными.

Импорт данных из текстового файла

Если у вас есть данные в файлах .txt или .csv или .prn , вы можете импортировать данные из этих файлов, рассматривая их как текстовые файлы. Следуйте инструкциям ниже

Шаг 1 – Откройте новый лист в Excel.

Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.

Шаг 3 – Нажмите « Из текста» в группе «Получить внешние данные». Откроется диалоговое окно « Импорт текстового файла ».

Вы можете видеть, что текстовые файлы с расширениями .prn, .txt и .csv принимаются.

Шаг 4 – Выберите файл. Имя выбранного файла появится в поле Имя файла. Кнопка «Открыть» изменится на кнопку «Импорт».

Шаг 5 – Нажмите кнопку «Импорт». Мастер импорта текста – появляется диалоговое окно « Шаг 1 из 3 ».

Шаг 6 – Выберите опцию «С разделителями», чтобы выбрать тип файла, и нажмите «Далее».

Откроется мастер импорта текста – шаг 2 из 3 .

Шаг 7 – В разделе «Разделители» выберите « Другое» .

Шаг 8 – В поле рядом с Другой введите | (Это разделитель в текстовом файле, который вы импортируете).

Шаг 9 – Нажмите Далее.

Откроется мастер импорта текста – шаг 3 из 3 .

Шаг 10 – В этом диалоговом окне вы можете установить формат данных столбца для каждого из столбцов.

Шаг 11. После завершения форматирования данных столбцов нажмите кнопку «Готово». Откроется диалоговое окно « Импорт данных ».

Вы увидите следующее –

  • Таблица выбрана для просмотра и отображается серым цветом. Таблица – единственный вариант просмотра, который у вас есть в этом случае.

  • Вы можете поместить данные либо в существующий рабочий лист, либо в новый рабочий лист.

  • Вы можете установить или не устанавливать флажок Добавить эти данные в модель данных.

  • Нажмите OK после того, как вы сделали выбор.

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

Вы можете поместить данные либо в существующий рабочий лист, либо в новый рабочий лист.

Вы можете установить или не устанавливать флажок Добавить эти данные в модель данных.

Нажмите OK после того, как вы сделали выбор.

Данные появятся на указанном вами листе. Вы импортировали данные из текстового файла в книгу Excel.

Импорт данных из другой книги

Возможно, вам придется использовать данные из другой книги Excel для анализа данных, но кто-то другой может поддерживать другую книгу.

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

Шаг 1 – Нажмите DATA> Соединения в группе Соединения на ленте.

Откроется диалоговое окно « Подключения к книге».

Шаг 2. Нажмите кнопку «Добавить» в диалоговом окне «Подключения к книге». Откроется диалоговое окно « Существующие подключения ».

Шаг 3 – Нажмите кнопку Обзор для более … Откроется диалоговое окно « Выбор источника данных ».

Шаг 4 – Нажмите кнопку « Новый источник» . Откроется диалоговое окно мастера подключения к данным .

Шаг 5 – Выберите Other / Advanced в списке источников данных и нажмите Next. Откроется диалоговое окно «Свойства ссылки на данные».

Шаг 6 – Установите свойства канала передачи данных следующим образом –

  • Перейдите на вкладку « Соединение ».

  • Нажмите Использовать имя источника данных.

  • Нажмите стрелку вниз и выберите « Файлы Excel» в раскрывающемся списке.

  • Нажмите ОК.

Перейдите на вкладку « Соединение ».

Нажмите Использовать имя источника данных.

Нажмите стрелку вниз и выберите « Файлы Excel» в раскрывающемся списке.

Нажмите ОК.

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

Шаг 7 – Найдите место, где у вас есть рабочая книга для импорта. Нажмите ОК.

Откроется диалоговое окно « Мастер подключения к данным » с выбором базы данных и таблицы.

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

Шаг 8 – Нажмите Далее. Откроется диалоговое окно мастера подключения к данным с сохранением файла подключения к данным и завершением.

Шаг 9 – Нажмите кнопку Готово. Откроется диалоговое окно « Выбор таблицы ».

Как вы заметили, Name – это имя листа, которое импортируется как тип TABLE. Нажмите ОК.

Соединение данных с выбранной вами рабочей книгой будет установлено.

Импорт данных из других источников

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

Шаг 1 – Откройте новую пустую книгу в Excel.

Шаг 2 – Перейдите на вкладку ДАННЫЕ на ленте.

Шаг 3 – Нажмите Из других источников в группе Получить внешние данные.

Появляется выпадающий список с различными источниками данных.

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

Задача для получения данных в 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.

Источники

  • https://public-pc.com/podklyuchenie-vneshnih-dannyh-v-excel/
  • https://exceltable.com/funkcii-excel/vyborka-iz-bazy-dannyh-bizvlech
  • http://bussoft.ru/tablichnyiy-redaktor-excel/tipy-dannyh-v-redaktore-elektronnyh-tablicz-ms-excel.html
  • https://lumpics.ru/data-types-in-excel/
  • https://MicroExcel.ru/tipy-dannyh/
  • https://baguzin.ru/wp/glava-8-rabota-s-vneshnimi-dannymi-v-tablitsah-excel/
  • https://coderlessons.com/tutorials/bolshie-dannye-i-analitika/izuchite-analiz-dannykh-excel/import-dannykh-v-excel
  • https://info-comp.ru/obucheniest/375-excel-get-data-from-sql-server.html

Источник

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). Получение и хранение огромного количества данных стало важной частью современного бизнес-ландшафта, и анализ таких данных становится все более сложным. Чтобы справиться с этой тенденцией, Microsoft продолжает добавлять в Excel средства обработки запросов и анализа данных.

Последнее дополнение – это набор инструментов BI (business intelligence). Эти инструменты дают возможность представить данные с помощью традиционных инструментов, таких как Таблицы, сводные таблицы и диаграммы, чтобы эффективно рассказать историю. До сих пор мы изучали роль Таблиц в рабочей книге. В этой главе рассматривается, как Excel использует Таблицы для обработки внешних данных.

Предыдущая глава        Содержание    Следующая глава

Ris. 8.1. Gruppa Poluchit i preobrazovat dannye vkladki Dannye

Рис. 8.1. Группа Получить и преобразовать данные вкладки Данные

Скачать заметку в формате Word или pdf, примеры в формате Excel

Подключение к внешним данным

Вы можете получить доступ к внешним источникам через вкладку Данные, группу Получить и преобразовать данные (рис. 8.1). Подключения к данным хранятся вместе с книгой, и вы можете просмотреть их, выбрав пункт Данные –> Запросы и подключения.

Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.

Ris. 8.2. Nastrojka dostupa k vneshnim dannym

Рис. 8.2. Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.

Ris. 8.3. Svojstva Tablitsy podklyuchennoj k vneshnim dannym

Рис. 8.3. Свойства Таблицы, подключенной к внешним данным

Подключение к базе данных

Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.

Ris. 8.4. Podklyuchenie k baze dannyh SQL Server

Рис. 8.4. Подключение к базе данных SQL Server

При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.

Существует много различных типов доступных источников данных, и иногда шаблоны соединений по умолчанию, представленные Excel, не работают. Посетите сайт https://www.connectionstrings.com/. Там есть много примеров строк подключения, статьи и форум вопросов и ответов.

Имя Таблицы

Excel импортирует каждую выбранную таблицу базы данных в новую Таблицу Excel на новом листе в активной книге. Он создает имена Таблиц в формате Table_Name, где Name – это имя таблицы базы данных или представления в базе данных SQL Server. При импорте одной таблицы имя таблицы будет Table_ServerName_ DatabaseName_TableName, где ServerName – имя сервера, DatabaseName – имя базы данных, а TableName – имя импортируемой таблицы.

Работа с текстовыми файлами

Текстовые файлы обычно представлены в виде CSV-файла (значения, разделенные запятыми), txt-файла (с разделителями табуляции) или PRN-файла (с фиксированными полями или пробелами). Excel по-прежнему создает подключение к данным при импорте текстовых файлов, но не помещает данные в Таблицу. Это связано с отсутствием информации о данных, содержащихся в текстовом файле. Базы данных имеют определенные правила, такие как уникальные имена полей/столбцов, в то время как текстовые файлы не связаны этими правилами. Excel загружает текстовые данные в запрос; Excel не пытается принудительно поместить данные в Таблицу или другой структурированный формат.

Подключение к текстовому файлу

Чтобы выбрать текстовый файл для импорта, пройдите по меню Данные –> Получить и преобразовать данные –> Из текстового/CSV-файла. Форматы файлов по умолчанию – PRN, TXT и CSV. После выбора файла Excel открывает диалоговое окно:

Ris. 8.5. Okno importa tekstovogo fajla

Рис. 8.5. Окно импорта текстового файла

Текст файл был подготовлен авторами книги в англо-американской традиции, поэтому перед размещением данных в книге Excel, их нужно доработать. Кликните кнопку Преобразовать данные, и измените формат столбцов OrderDate, Cost и Total используя локаль  (подробнее см. Изменение настроек Power Query, действующих по умолчанию).

Ris. 8.6. Dannye posle preobrazovaniya v redaktore Power Query

Рис. 8.6. Данные после преобразования в редакторе Power Query

Теперь данные можно поместить на лист Excel. Кликните Закрыть и загрузить. Excel по умолчанию поместит данные в Таблицу:

Ris. 8.7. Obrabotannye dannye iz tekstovogo fajla v Tablitse Excel

Рис. 8.7. Обработанные данные из текстового файла в Таблице Excel

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

Работа с данными из Azure Marketplace

Azure Marketplace является хранилищем данных и приложений анализа данных. Это облачная инфраструктура и платформа, которая используется для создания, развертывания и управления облачными службами. Данные, доступные в Azure Marketplace, можно экспортировать в текстовые файлы, поместить в модель данных Power Pivot или непосредственно в Таблицу. Если запрашиваемый набор записей превышает 1 млн, его придется импортировать в модель данных, которая поддерживает неограниченное число строк.

Для подключения к Azure Marketplace из Excel требуется URL-адрес данных и ключ учетной записи. На домашней странице Azure Marketplace щелкните ссылку Databases. Появится страница со списком наборов данных; некоторые из них бесплатны, а некоторые доступны за плату. Выберите один из бесплатных источников данных. Пройдите регистрацию. После подключения к набору данных Azure Marketplace это соединение становится таким же, как и любое другое, и его можно обновить, чтобы работать с самыми последними данными. Имейте в виду, что каждое соединение считается трансакцией.

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Excel использование если с диапазоном ячеек
  • Excel источник данных для выпадающего списка
  • Excel использование если с датами
  • Excel источник данных в ячейке
  • Excel использование встроенных экономических функций

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии