Если данные всегда в пути, Excel похож на главный центральный станция. Представьте, что данные — это поезд, который регулярно посещает Excel, вносит изменения, а затем покидает его. Существует множество способов войти в Excel, при котором данные всех типов будут импортироваться, и список будет постоянно расти. После того как данные уже есть в Excel, вы можете изменить фигуру в нужном виде с помощью Power Query. Для бесперебойной работы данных, как и для всех нас, требуется «уход и подача». Сюда приходят свойства подключения, запроса и данных. Кроме того, данные сют с док-станции Excel во многих случаях: они импортируются из других источников, делятся как отчеты, диаграммы и совые таблицы, а также экспортируются в Power BI и Power Apps.
Вот основные вещи, которые можно делать, когда данные находятся на поезде Excel:
-
Импорт Вы можете импортировать данные из различных внешних источников. Эти источники данных могут быть на вашем компьютере, в облаке или на полпути по всему миру. Дополнительные сведения см. в теме «Импорт данных из внешних источников данных».
-
Power Query С помощью Power Query (ранее — » Get & Transform») можно создавать запросы для фигуры, преобразования и объединения данных различными способами. Вы можете экспортировать свою работу как шаблон Power Query, чтобы определить операцию потока данных в Power Apps. Вы даже можете создать тип данных в дополнение к связанным типамданных. Дополнительные сведения см. в справке Power Query для Excel.
-
Безопасность Конфиденциальность данных, учетные данные и проверка подлинности всегда являются проблемой. Дополнительные сведения см. в сведениях об управлении настройками и разрешениями источников данных, а также настройке уровней конфиденциальности.
-
Обновить Для внесения в Excel изменений, например дополнений, обновлений и удаления, обычно требуется операция обновления. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel».
-
Подключения и свойства Каждый внешний источник данных имеет связь с данными о каждом внешнем источнике данных и связанную с ним информацию о свойстве, которая иногда требует внесения изменений в зависимости от обстоятельств. Дополнительные сведения см. в дополнительных сведениях об управлении диапазонами внешних данных и их свойствами, создании,редактировании подключений к внешним данным и свойствах подключения.
-
Устаревшая версия Традиционные методы, такие как устаревшие мастеры импорта и MSQuery, по-прежнему доступны. Дополнительные сведения см. в параметрах импорта и анализа данных, а также извлечении внешних данных с помощью Microsoft Query.
В следующих разделах более подробные сведения о том, что происходит за кадром на этом занятом поезде Excel.
Существуют свойства подключения, запроса и диапазона внешних данных. Оба свойства подключения и запроса содержат традиционные сведения о под соединении. В заголовке диалогового окна «Свойства подключения» означает, что с ним не связан ни один запрос, а с свойствами запроса — нет. Свойства диапазона внешних данных контролируют макет и формат данных. Во всех источниках данных есть диалоговое окно «Свойства внешних данных», но для источников данных со связанными учетными данными и данными обновления используется большее диалоговое окно «Свойства внешних данных диапазона».
В следующих сведениях общались наиболее важные диалоговые окна, области, пути к командам и соответствующие разделы справки.
|
Диалоговое окно или области |
Вкладки и туннель |
Основной раздел справки |
|---|---|---|
|
Последние источники
Данные> |
(Без вкладок) Tunnels to Connect > Navigator dialog box |
Управление настройками и разрешениями источника данных |
|
Свойства подключения Data > Queries & Connections > Connections tab > (right click a connection) > Properties |
Вкладка Usage |
Свойства подключения |
|
Свойства запроса
Data> Existing Connections > (right click a connection) > Edit Connection Properties |
Вкладка Usage |
Свойства подключения |
|
Запросы & connections Data > Queries & Connections |
Вкладка «Подключения» |
Свойства подключения |
|
Существующие подключения
Данные> |
Вкладка «Таблицы» |
Подключение к внешним данным |
|
Свойства внешних данных |
Используется на вкладке (в диалоговом окне «Свойства подключения») Кнопка «Обновить» в правом туннелье в свойствах запроса |
Управление диапазонами внешних данных и их свойствами |
|
Свойства подключения > |
(Без вкладок) Tunnels to |
Создание, изменение подключений к внешним данным и управление ими |
Данные в книге Excel могут быть из двух разных мест. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в текстовом файле, базе данных или кубе OLAP. Этот внешний источник данных подключается к книге с помощью подключения к данным, которое является набором сведений о том, как найти внешний источник данных, войти в систему и получить к нему доступ.
Основное преимущество подключения к внешним данным — возможность периодически анализировать эти данные без многократного копирования в книгу (это операция, которая может быть трудоемкой и частой). После подключения к внешним данным вы также можете автоматически обновлять (обновлять) книги Excel из исходного источника при обновлении источника данных с новой информацией.
Сведения о под соединении хранятся в книге и также могут храниться в файле подключения, например в файле подключения к данным Office (ODC) или файле имени источника данных (DSN).
Чтобы внести внешние данные в Excel, необходим доступ к ним. Если доступ к внешнему источнику данных не находится на локальном компьютере, может потребоваться обратиться к администратору базы данных, чтобы узнать пароль, разрешения пользователей или другие сведения о под соединении. Если источником данных является база данных, убедитесь, что она не открыта в монопольном режиме. Если источником данных является текстовый файл или таблица, убедитесь, что другие пользователи не имеют доступа к ним.
Для многих источников данных для координации потока данных между Excel, файлом подключения и источником данных также требуется драйвер ODBC или поставщик OLE DB.
На следующей схеме ключевых моментах подключения к данным нет.
1. Существуют различные источники данных, к которые можно подключиться: службы Analysis Services, SQL Server, Microsoft Access, другие реляционные базы данных OLAP, электронные таблицы и текстовые файлы.
2. У многих источников данных есть связанный драйвер ODBC или поставщик OLE DB.
3. Файл подключения определяет все сведения, необходимые для доступа к источнику данных и извлечения данных из него.
4. Данные подключения копируется из файла подключения в книгу, и их можно легко изменить.
5. Данные копируется в книгу, чтобы их можно было использовать точно так же, как и данные, хранимые непосредственно в книге.
Чтобы найти файлы подключения, используйте диалоговое окно «Существующие подключения». (Выберите «Данные>существующие подключения.) В этом диалоговом окне можно увидеть следующие типы подключений:
-
Подключения в книге
В этом списке отображаются все текущие подключения в книге. Список создается на окне подключений, которые вы уже определили, с помощью диалоговых окна «Выбор источника данных» мастера подключения к данным, или на окне подключений, выбранных в этом диалоговом окне как подключение.
-
Файлы подключения на компьютере
Этот список создается из папки «Мои источники данных», которая обычно хранится в папке «Документы».
-
Файлы подключения в сети
Этот список можно создать на основе набора папок в локальной сети, расположение которых можно развернуть в сети при развертывании групповых политик Microsoft Office или библиотеки SharePoint.
Excel также можно использовать в качестве редактора файлов подключений для создания и изменения подключений к внешним источникам данных, которые хранятся в книге или файле подключения. Если нужного подключения нет, вы можете создать подключение, нажав кнопку «Обзор» и выбрав диалоговое окно «Выбор источника данных», а затем нажав кнопку «Создать источник», чтобы запустить мастер подключения к данным.
После создания подключения вы можете использовать диалоговое окно «Свойства подключения» (выберите «Свойства подключения» >«Запросы & Подключения >» > (щелкните правой кнопкой мыши «Свойства > подключения») для управления различными настройками подключений к внешним источникам данных, а также использования, повторного использования или переключения файлов подключения.
Примечание Иногда диалоговое окно «Свойства подключения» называется диалогом «Свойства запроса», если с ним связан запрос, созданный в Power Query (прежнее название — «& Преобразовать»).
Если для подключения к источнику данных используется файл подключения, Excel копирует сведения о подключении из файла подключения в книгу Excel. При внесении изменений в диалоговом окне «Свойства подключения» вы редактируете данные подключения к данным, хранимые в текущей книге Excel, а не исходный файл подключения, который мог использоваться для создания подключения (указывается именем файла, которое отображается в свойстве «Файл подключения» на вкладке «Определение»). После изменения данных подключения (за исключением свойств «Имя подключения» и «Описание подключения») ссылка на файл подключения удаляется, а свойство «Файл подключения» удаляется.
Чтобы гарантировать, что файл подключения всегда используется при обновлении источника данных, на вкладке «Определение» нажмите кнопку «Всегда пытаться использовать этот файл для обновления данных». Если этот установить, все книги, которые используют этот файл подключения, всегда будут использовать изменения файла подключения, для которых также должно быть за установлено это свойство.
В диалоговом окне Connections можно легко управлять этими подключениями, включая их создание, редактирование и удаление (выберите данные> Запросы & Connections >Connections > > (щелкните правой кнопкой мыши подключение) > Свойства.) В этом диалоговом окне можно сделать следующее:
-
Создание, изменение, обновление и удаление подключений, которые используются в книге.
-
Проверьте источник внешних данных. Это можно сделать, если подключение было определено другим пользователем.
-
Показывать, где каждое подключение используется в текущей книге.
-
Диагностика сообщения об ошибке, которое касается подключений к внешним данным.
-
Перенаправить подключение к другому серверу или источнику данных или заменить файл подключения для существующего подключения.
-
Легко создавайте файлы подключения и делитесь ими с пользователями.
Файлы подключений особенно полезны для совместного доступа к подключениям, что делает подключения более обнаруживаемыми, повышает уровень безопасности подключений и облегчает администрирование источников данных. Лучший способ поделиться файлами подключения — поместить их в безопасное и надежное расположение, например в сетевую папку или библиотеку SharePoint, где пользователи могут читать файл, но только назначенные пользователи могут изменять его. Дополнительные сведения см. в подмноговом окне «Совместное передачу данных с помощью ODC».
Использование ODC-файлов
Вы можете создавать файлы подключения к данным Office (ODC), подключаясь к внешним данным с помощью диалоговых окна «Выбор источника данных» или с помощью мастера подключения к новым источникам данных. В ODC-файле для хранения сведений о под соединении используются пользовательские HTML- и XML-теги. Вы можете легко просматривать и редактировать содержимое файла в Excel.
Вы можете поделиться файлами подключения с другими людьми, чтобы предоставить им такой же доступ к внешнему источнику данных. Другим пользователям не требуется настроить источник данных для открытия файла подключения, но им может потребоваться установить драйвер ODBC или поставщик OLE DB, необходимый для доступа к внешним данным на компьютере.
Рекомендуется использовать ODC-файлы для подключения к данным и обмена данными. Вы можете легко преобразовать другие традиционные файлы подключения (DSN, UDL и файлы запросов) в ODC-файл, открыв файл подключения и нажав кнопку «Экспорт файла подключения» на вкладке «Определение» в диалоговом окне «Свойства подключения».
Использование файлов запросов
Файлы запросов — это текстовые файлы, которые содержат сведения об источнике данных, включая имя сервера, на котором находятся данные, и сведения о под соединении, которые вы предоставляете при создании источника данных. Файлы запросов — это традиционный способ поделиться запросами с другими пользователями Excel.
Использование DQY-файлов запросов С помощью Microsoft Query можно сохранять DQY-файлы, содержащие запросы на данные из реляционных баз данных или текстовых файлов. Открыв эти файлы в Microsoft Query, вы можете просмотреть данные, возвращенные запросом, и изменить запрос, чтобы получить другие результаты. DQY-файл можно сохранить для любого запроса, который вы создаете, с помощью мастера запросов или непосредственно в Microsoft Query.
Использование OQY-файлов запросов OQY-файлы можно сохранять для подключения к данным в базе данных OLAP на сервере или в автономный файл куба (.cub). При создании источника данных для базы данных или куба OLAP с помощью мастера многомерных подключений автоматически создается OQY-файл. Так как базы данных OLAP не уорганизованы в записи или таблицы, для доступа к ним нельзя создавать запросы и DQY-файлы.
Использование RQY-файлов запросов Excel может открывать файлы запросов в формате RQY, чтобы поддерживать драйверы источников данных OLE DB, которые используют этот формат. Дополнительные сведения см. в документации к драйверу.
Использование файлов запросов QRY Microsoft Query может открывать и сохранять файлы запросов в формате QRY для использования с более ранними версиями Microsoft Query, в которые не удается открыть DQY-файлы. Если у вас есть файл запроса в формате QRY, который вы хотите использовать в Excel, откройте его в Microsoft Query, а затем сохраните в формате DQY. Сведения о сохранении DQY-файлов см. в справке Microsoft Query.
Использование файлов веб-запроса Iqy Excel может открывать файлы веб-запросов Iqy для извлечения данных из Интернета. Дополнительные сведения см. в теме «Экспорт в Excel из SharePoint».
Диапазон внешних данных (также называемый таблицей запроса) — это определенное имя или имя таблицы, которое определяет расположение данных, передаренных на таблицу. При подключении к внешним данным Excel автоматически создает диапазон внешних данных. Единственным исключением из этого правила является отчеты pivotttable, подключенные к источнику данных, в которых не создается диапазон внешних данных. В Excel можно форматирование и разложить диапазон внешних данных или использовать его в вычислениях, как и любые другие данные.
Excel автоматически называет диапазон внешних данных следующим образом:
-
Имена внешних данных в диапазоне от файлов подключения к данным Office (ODC) и имени файла одинаковы.
-
Диапазоны внешних данных из баз данных называются именем запроса. По Query_from_источником данных является имя источника данных, который использовался для создания запроса.
-
Диапазоны внешних данных от текстовых файлов называются текстовыми именами файлов.
-
Диапазоны внешних данных от веб-запросов называются именем веб-страницы, на которой были извлечены данные.
Если на вашем компьютере несколько диапазонов внешних данных из одного источника, они нуметы. Например, MyText, MyText_1, MyText_2 и т. д.
У диапазона внешних данных есть дополнительные свойства (которые не следует путать с свойствами подключения), которые можно использовать для управления данными, например для сохранения форматирования ячеок и ширины столбцов. Эти свойства диапазона внешних данных можно изменить, нажав кнопку «Свойства» в группе «Подключения» на вкладке «Данные» и внося изменения в диалоговые окна «Свойства диапазона внешних данных» или «Свойства внешних данных».
|
|
|
Существует несколько объектов данных (например, диапазон внешних данных и отчеты в отчете, которые можно использовать для подключения к различным источникам данных). Однако тип источника данных, к который можно подключиться, отличается от типа каждого объекта данных.
Вы можете использовать и обновлять подключенные данные в службы Excel. Как и для любого внешнего источника данных, может потребоваться проверка подлинности для доступа. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel». Fдополнительные сведения об учетных данных см. в службы Excel параметрах проверки подлинности.
В таблице ниже общались сведения о том, какие источники данных поддерживаются для каждого объекта данных в Excel.
|
Excel |
|
OLE |
ODBC |
Текст |
HTML |
XML |
SharePoint |
|
|
Мастер импорта текста |
Да |
Нет |
Нет |
Да |
Нет |
Нет |
Нет |
|
|
Отчет в области «Отчеты и отчеты» |
Нет |
Да |
Да |
Да |
Нет |
Нет |
Да |
|
|
Отчет в области «Отчеты и отчеты» |
Нет |
Да |
Нет |
Нет |
Нет |
Нет |
Нет |
|
|
Таблица Excel |
Да |
Да |
Да |
Нет |
Нет |
Да |
Да |
|
|
Карта XML |
Да |
Нет |
Нет |
Нет |
Нет |
Да |
Нет |
|
|
Веб-запрос |
Да |
Нет |
Нет |
Нет |
Да |
Да |
Нет |
|
|
Мастер подключения к данным |
Да |
Да |
Да |
Да |
Да |
Да |
Да |
|
|
Microsoft Query |
Да |
Нет |
Да |
Да |
Нет |
Нет |
Нет |
|
Примечание: Эти файлы: текстовый файл, импортируемый с помощью мастера импорта текста, XML-файл, импортируемый с помощью карты XML, и HTML- или XML-файл, импортируемый с помощью веб-запроса, не используют драйвер ODBC или поставщик OLE DB для подключения к источнику данных.
службы Excel обходного пути для таблиц и именовых диапазонов Excel
Если вы хотите отобразить книгу Excel в службы Excel, вы можете подключиться к данным и обновить их, но для этого нужен отчет таблицы. службы Excel не поддерживает диапазоны внешних данных, то службы Excel не поддерживает таблицу Excel, подключенную к источнику данных, веб-запросу, карте XML или Microsoft Query.
Однако это ограничение можно обойти, подключив с помощью таблицы к источнику данных, а затем спроектировать и разметить ее как двумерную таблицу без уровней, групп или подытогов, чтобы отображались все нужные значения строк и столбцов.
Давайте отпустим дорожку памяти базы данных.
О MDAC, OLE DB и OBC
Во-первых, приносим извинения за все акронимы. Компоненты доступа к данным Майкрософт (MDAC) 2.8 входят в состав Microsoft Windows. С помощью MDAC можно подключаться к данным из самых разных реляционных и нереляционных источников и использовать их. Вы можете подключаться к различным источникам данных с помощью драйверов ODBC или поставщиков OLE DB, которые были либо разработаны корпорацией Майкрософт, либо разработанными сторонними поставщиками. При установке Microsoft Office на компьютер добавляются дополнительные драйверы ODBC и поставщики OLE DB.
Чтобы отобразить полный список поставщиков OLE DB, установленных на компьютере, отобразите диалоговое окно «Свойства связи данных» из файла связи с данными, а затем перейдите на вкладку «Поставщик».
Чтобы посмотреть полный список установленных на компьютере поставщиков ODBC, отобразите диалоговое окно «Администратор базы данных ODBC» и перейдите на вкладку «Драйверы».
Вы также можете использовать драйверы ODBC и поставщики OLE DB других производителей для получения информации из других источников, кроме источников данных Майкрософт, включая другие типы баз данных ODBC и OLE DB. Чтобы получить сведения об установке этих драйверов ODBC или поставщиков OLE DB, ознакомьтесь с документацией к базе данных или обратитесь к ее поставщику.
Использование ODBC для подключения к источникам данных
В архитектуре ODBC приложение (например, Excel) подключается к диспетчеру драйверов ODBC, который, в свою очередь, использует определенный драйвер ODBC (например, Microsoft SQL ODBC) для подключения к источнику данных (например, к базе данных Microsoft SQL Server).
Чтобы подключиться к источникам данных ODBC, сделайте следующее:
-
Убедитесь, что на компьютере с источником данных установлен соответствующий драйвер ODBC.
-
Определите имя источника данных (DSN) с помощью администратора источника данных ODBC для хранения сведений о подключении в реестре или файле DSN либо строки подключения в Microsoft Visual Basic для передачи сведений о подключении непосредственно в диспетчер драйверов ODBC.
Чтобы определить источник данных, в Windows нажмите кнопку «Начните» и выберите «Панель управления». Щелкните «Система и обслуживание»и выберите «Администрирование». Щелкните «Производительность и обслуживание»и выберите «Администрирование». и щелкните «Источники данных» (ODBC). Для получения дополнительных сведений о различных параметрах нажимайте кнопку справки в каждом диалоговом окне.
Машинные источники данных
Машинные источники данных хранят сведения о подстройке в реестре на определенном компьютере с определенным пользователем именем. Машинные источники данных можно использовать только на том компьютере, на который они были определены. Существует два типа машинных источников данных — пользовательские и системные. Источники данных пользователя могут использоваться только текущим пользователем и видны только этому пользователю. Источники системных данных могут использоваться всеми пользователями на компьютере и видны всем пользователям на этом компьютере.
Машинный источник данных особенно полезен, если требуется обеспечить дополнительные сведения о безопасности, так как он гарантирует, что только пользователи, во входя в систему, смогут просматривать его и что удаленный пользователь не сможет скопировать его на другой компьютер.
Файлы источников данных
Файлы источников данных (также называемые DSN-файлами) хранят сведения о под соединении в текстовом файле, а не в реестре и, как правило, более гибки в использовании, чем машинные источники данных. Например, вы можете скопировать файлный источник данных на любой компьютер с правильным драйвером ODBC, чтобы приложение использовало согласованную и точную информацию о под соединении на всех своих компьютерах. Кроме того, вы можете разместить файл данных на одном сервере, совместно использовать его на разных компьютерах в сети и легко сохранять сведения о под соединении в одном месте.
Файл может также стать недоступным для всех пользователей. Файл, который невозможно сделать одним из источников данных, находится на одном компьютере и указывает на машинный источник данных. Для доступа к существующим машинным источникам данных из файловых источников данных можно использовать такие источники.
Использование OLE DB для подключения к источникам данных
В архитектуре OLE DB приложение, которое имеет доступ к данным, называется потребителем данных (например, Excel), а программа, которая обеспечивает доступ к данным, называется поставщиком баз данных (например, поставщиком Microsoft OLE DB для SQL Server).
Файл универсальных связей данных (UDL) содержит сведения о под соединении, которые используются потребителем данных для доступа к источнику данных через его поставщика OLE DB. Чтобы создать сведения о подмыве, с помощью одного из следующих окна:
-
В мастере подключения данных в диалоговом окне «Свойства связи с данными» определите связь данных для поставщика OLE DB.
-
Создайте пустой текстовый файл с расширением UDL и отредактйте его, чтобы отобразить диалоговое окно «Свойства ссылки на данные».
См. также
Справка по Power Query для Excel
Нужна дополнительная помощь?
Содержание
- Как это работает
- Подключаем внешние данные из интернет
- Импорт внешних данных Excel 2010
- Отличите нового и старого мастера импорта
- Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel
- Примеры использования функции БИЗВЛЕЧЬ в Excel
- Тип данных: Числовые значения MS Excel
- Классификация типов данных
- Текстовые значения
- Дата и время
- Логические данные
- Разновидности типов данных
- Число
- Текст
- Ошибки
- Подключение к внешним данным
- Подключение к базе данных
- Импорт данных из базы данных Microsoft Access
- Импорт данных с веб-страницы
- Копировать-вставить данные из Интернета
- Импорт данных из текстового файла
- Импорт данных из другой книги
- Импорт данных из других источников
- Задача для получения данных в Excel
Как это работает
Инструменты для импорта расположены во вкладке меню «Данные». 
Если подключение отключено, перейдите: 
Далее: 
На вкладке «Центр управления» перейдите: 
Подключаем внешние данные из интернет
В Excel 2013 и более поздних версиях, по умолчанию для импорта информации из внешних источников используется надстройка Power Query. Как это работает? Перейдите: 
Пропишите адрес сайта, с которого импортируются данные: 
Выберите что отобразится, нажмите кнопку «Загрузить». 
Информация подгрузится в лист Excel. Работайте с ними как с простым документом: используйте формулы графики, сводные таблицы. 
Для обновления нажмите ПКМ по таблице: 
Или: 
Импорт внешних данных Excel 2010
Перейдите: 
В новом окне пропишите адрес сайта. Получите информацию из областей страницы, где проставлены желтые ярлыки. Отметьте их мышкой, нажмите «Импорт». 
Отметьте пункт «Обновление». Тогда внешняя информация обновится автоматически. 
Отличите нового и старого мастера импорта
Преимущества Power Query:
- Поддерживается работа с большим числом страниц;
- Промежуточная обработка информации перед загрузкой на лист;
- Информация импортируется быстрее.
Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:
- Заголовки таблицы должны находиться в первой строке.
- Любая последующая строка должна содержать хотя бы одну непустую ячейку.
- Объединения ячеек в любых строках запрещены.
- Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
- Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.
Таким образом, практически любая таблица в 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.
Логические данные
Довольно интересным является тип логических данных. Он оперирует всего двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие настало» и «событие не настало». Функции, обрабатывая содержимое ячеек, которые содержат логические данные, производят те или иные вычисления.
Разновидности типов данных
Выделяются две большие группы типов данных:
- константы – неизменные значения;
- формулы – значения, которые меняются в зависимости от изменения других.
В группу “константы” входят следующие типы данных:
- числа;
- текст;
- дата и время;
- логические данные;
- ошибки.
Число
Этот тип данных применяется в различных расчетах. Как следует из названия, здесь предполагается работа с числами, и для которых может быть задан один из следующих форматов ячеек:
- числовой;
- денежный;
- финансовый;
- процентный;
- дробный;
- экспоненциальный.
Формат ячейки можно задать двумя способами:
- Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
- В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.
Для каждого из форматов, перечисленных выше (за исключением дробного), можно задать количество знаков после запятой, а для числового – к тому же, включить разделитель групп разрядов.
Чтобы ввести значение в ячейку, достаточно просто выделить ее (с нужным форматом) и набрать с помощью клавиш на клавиатуре нужные символы (либо вставить ранее скопированные данные из буфера обмена). Или можно выделить ячейку, после чего ввести нужные символы в строке формул.
Также можно поступить наоборот – сначала ввести значение в нужной ячейке, а формат поменять после.
Текст
Данный тип данных не предназначен для выполнения расчетов и носит исключительно информационный характер. В качестве текстового значения могут использоваться любые знаки, цифры и т.д.
Ввод текстовой информации происходит таким же образом, как и числовой. Если текст не помещается в рамках выбранной ячейки, он будет перекрывать соседние (если они пустые).
Ошибки
В некоторых случаях пользователь может видеть в 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
Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса. У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет.
Что же сделать в такой ситуации? Давайте рассмотрим этот случай подробнее на следующем примере.
Постановка задачи
Предположим, что у нас в папке E:Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:
С ходу, наверное, понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневая шапка и т.д.
Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:
Создаем запрос к внешнему файлу
Открыв файл Обработчик.xlsx, выберем на вкладке Данные команду Получить данные — Из файла — Из книги Excel (Data — Get Data — From file — From Excel), затем укажем местоположение исходного файла и нужный нам лист. Выбранные данные загрузятся в редактор Power Query:
Приведём их в нормальный вид:
- Удалим пустые строки через Главная — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove Empty Rows).
- Удалим ненужные 4 верхних строки через Главная — Удалить строки — Удалить верхние строки (Home — Remove Rows — Remove Top Rows).
- Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home — Use first row as header).
- Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform — Split Column).
- Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.
В итоге у нас должна получиться следующая, гораздо более приятная, картина:
Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.xlsx командой Закрыть и загрузить (Home — Close&Load) на вкладке Главная:
Находим путь к файлу в запросе
Теперь давайте посмотрим как выглядит наш запрос «под капотом», на встроенном в Power Query внутреннем языке с лаконичным названием «М». Для этого вернемся в наш запрос двойным щелчком по нему в правой панели Запросы и подключения и на вкладке Просмотр выберем Расширенный редактор (View — Advanced Editor):
В открывшемся окне во второй строке сразу же обнаруживается жёстко прописанный путь к нашему исходному файлу выгрузки. Если мы сможем заменить эту текстовую строку на параметр, переменную или ссылку на ячейку листа Excel, где этот путь будет заранее прописан, то мы сможем впоследствии легко его менять.
Добавляем умную таблицу с путём к файлу
Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую «умную» таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:
Для создания «умной» таблицы из обычного диапазона можно использовать сочетание клавиш Ctrl+T или кнопку Форматировать как таблицу на вкладке Главная (Home — Format as Table). Заголовок столбца (ячейка А1) может быть совершенно любым. Также обратите внимание, что для понятности я дал таблице имя Параметры на вкладке Конструктор (Design).
Скопировать из Проводника путь или даже ввести его вручную не представляет, конечно, особой сложности, но лучше всего минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel ЯЧЕЙКА (CELL), которая умеет выдавать кучу полезной информации об указанной в качестве аргумента ячейке — в том числе и путь к текущему файлу:
Если предположить, что файл с исходными данными всегда лежит в той же папке, что и наш Обработчик, то путь, который нам нужен можно сформировать следующей формулой:
=ЛЕВСИМВ(ЯЧЕЙКА(«имяфайла»);НАЙТИ(«[«;ЯЧЕЙКА(«имяфайла»))-1)&»Топ-100 товаров.xls»
или в английской версии:
=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»
… где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.
Параметризуем путь в запросе
Остался последний и самый главный штрих — прописать в запросе путь к исходному файлу Топ-100 товаров.xls, сославшись на ячейку А2 нашей созданной «умной» таблицы Параметры.
Для этого вернемся в запрос Power Query и ещё раз откроем Расширенный редактор на вкладке Просмотр (View — Advanced Editor). Вместо текстовой строки-пути в кавычках «E:Отчеты по продажамТоп-100 товаров.xlsx» введём туда вот такую конструкцию:
Excel.CurrentWorkbook(){[Name=»Параметры»]}[Content]{0}[Путь к исходным данным]
Давайте разберемся из чего она состоит:
- Excel.CurrentWorkbook() — это функция языка М для обращения к содержимому текущего файла
- {[Name=»Параметры»]}[Content] — это уточняющий параметр к предыдущей функции, указывающий, что мы хотим получить содержимое «умной» таблицы Параметры
- [Путь к исходным данным] — это имя столбца в таблице Параметры, к которому мы обращаемся
- {0} — это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка — не в счет и нумерация начинается от нуля, а не от единицы.
Вот и всё, собственно.
Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.
Ссылки по теме
- Что такое Power Query и зачем он нужен при работе в Microsoft Excel
- Как импортировать в Power Query плавающий фрагмент текста
- Редизайн двумерной кросс-таблицы в плоскую с помощью Power Query
Содержание
- Определение источников данных, используемых в модели данных книги
- Дополнительные сведения
- Создание сводной таблицы с внешним источником данных
- Подключение к новому внешнему источнику данных
- Как сделать сводную таблицу в Excel: пошаговая инструкция
- Создание сводной таблицы в Excel
- Работа со сводными таблицами в Excel
- Источник данных сводной таблицы Excel
- Обновление данных в сводной таблице Excel
Определение источников данных, используемых в модели данных книги
Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.
Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.
Чтобы точно определить, какие данные есть в модели, выполните следующие простые действия:
В Excel щелкните Power Pivot > Управление, чтобы открыть окно Power Pivot.
Просмотрите вкладки в окне Power Pivot.
Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.
Чтобы просмотреть происхождение таблицы, щелкните Свойства таблицы.
Если Свойства таблицы затемнены и вкладка содержит значок ссылки, указывающей на связанную таблицу, данные происходят из листа в таблице, а не из внешнего источника данных.
Для всех остальных типов данных в диалоговом окне Изменить свойства таблицы отображаются имя подключения и запрос, используемые для извлечения данных. Запомните или запишите имя подключения, а затем используйте диспетчер подключений в приложении Excel, чтобы определить сетевой ресурс и базу данных, используемые в подключении:
в Excel щелкните Данные > Подключения;
выберите подключение, используемое для заполнения таблицы в модели;
щелкните Свойства > Определение, чтобы просмотреть строку подключения.
Примечание: Модели данных были введены в Excel 2013. Вы можете использовать их для создания сводных таблиц, сводных диаграмм и отчетов Power View, визуализирующих данные из нескольких таблиц. Дополнительные сведения о моделях данных можно узнать в Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник
Создание сводной таблицы с внешним источником данных
Возможность анализировать все данные может помочь вам принимать более лучшие бизнес-решения. Но иногда трудно понять, с чем начать, особенно при наличии большого объема данных, хранящихся за пределами Excel, например в базе данных Microsoft Access или Microsoft SQL Server или в файле куба OLAP. В этом случае вы подключитесь к внешнему источнику данных, а затем создадите сводную таблицу для суммирования, анализа, изучения и представления данных.
Вот как создать сводную таблицу с помощью существующего подключения к внешним данным:
Щелкните любую ячейку на листе.
Выберите Вставка > Сводная таблица.
В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.
Выберите вариант Выбрать подключение.
На вкладке «Подключения» в окне «Показать» оставьте флажок «Все подключения» или выберите категорию подключения с источником данных, к которому вы хотите подключиться.
Чтобы повторно использовать существующее подключение или поделиться с этим подключением, используйте подключение из connections в этой книге.
В списке подключений выберите нужное подключение и нажмите кнопку » Открыть».
В разделе «Выбор места размещения отчета сводной таблицы» выберите расположение.
Чтобы поместить сводную таблицу на новый лист, начиная с ячейки A1, выберите » Создать лист».
Чтобы поместить сводную таблицу на активный лист, выберите «Существующий лист «, а затем в поле «Расположение» введите ячейку, в которой будет запущена сводная таблица.
Нажмите кнопку ОК.
Excel добавляет пустую сводную таблицу и отображает список полей, чтобы можно было отобразить нужные поля и изменить их порядок для создания собственного макета.
В разделе списка полей установите флажок рядом с именем поля, чтобы поместить поле в область по умолчанию в разделе областей списка полей.
Как правило, в область «Строки» добавляются нечисловые поля, в область «Значения» добавляются числовые поля, а в область «Столбцы» — поля даты и времени. При необходимости поля можно переместить в другую область.
Совет: Можно также щелкнуть правой кнопкой мыши имя поля, а затем выбрать «Добавить в фильтр отчета «, «Добавить в метки столбцов «, «Добавить в метки строк» или «Добавить в значения «, чтобы поместить поле в эту область раздела областей, или перетащить поле из раздела поля в область в разделе областей.
Используйте список полей для дальнейшего проектирования макета и формата сводной таблицы, щелкнув правой кнопкой мыши поля в разделе областей, а затем выбрав нужные области или перетащив поля между областями в разделе областей.
Подключение к новому внешнему источнику данных
Чтобы создать подключение внешних данных к SQL Server и импортировать данные в Excel в виде таблицы или сводной таблицы, выполните следующие действия.
Щелкните «> из других источников».
Щелкните нужное подключение.
Щелкните «SQL Server«, чтобы создать подключение к SQL Server таблице.
Щелкните «Из служб Analysis Services«, чтобы создать подключение к SQL Server analysis.
В мастере подключения к данным выполните действия по установке подключения.
На странице 1 введите сервер базы данных и укажите способ входа на сервер.
На странице 2 введите базу данных, таблицу или запрос, содержащий нужные данные.
На странице 3 введите файл подключения, который вы хотите создать.
Чтобы создать новое подключение к базе данных Access и импортировать данные в Excel в виде таблицы или сводной таблицы, сделайте следующее:
Щелкните «> из Access».
В диалоговом окне «Выбор источника данных» найдите базу данных, к которой нужно подключиться, и нажмите кнопку «Открыть».
В диалоговом окне «Выбор таблицы» выберите таблицу и нажмите кнопку «ОК «.
Если таблиц несколько, установите флажок «Включить выбор нескольких таблиц», чтобы установить флажки нужных таблиц, а затем нажмите кнопку » ОК».
В диалоговом окне «Импорт данных» выберите способ просмотра данных в книге и место их размещения, а затем нажмите кнопку «ОК «.
Таблицы автоматически добавляются в модель данных, а база данных Access добавляется в подключения к книге.
Источник
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Источник
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 вполне справляется.