Эта сводная таблица подключается к внешнему источнику данных не поддерживаемому в этой версии excel
Обновлено: 05.07.2024
= Мир MS Excel/Статьи об Excel
| Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
| Приёмы работы с формулами [13] |
| Настройки Excel [3] |
| Инструменты Excel [4] |
| Интеграция Excel с другими приложениями [4] |
| Форматирование [1] |
| Выпадающие списки [2] |
| Примечания [1] |
| Сводные таблицы [1] |
| Гиперссылки [1] |
| Excel и интернет [1] |
| Excel для Windows и Excel для Mac OS [2] |
Прежде всего, следует разобраться, как создать подключение к внешнему источнику данных.
Это можно сделать несколькими способами
Раз речь идет о сводных таблицах, то логично использовать Мастер сводных таблиц. В Excel 2003 Мастер запускается сразу при попытке создания сводной таблицы через меню Данные -- Сводная таблица. Начиная с Excel 2007 для использования Мастера сводных таблиц, нужно вынести на Панель быстрого доступа одноимённую кнопку.
На первом шаге Мастера сводных таблиц выбираем вариант Во внешнем источнике данных -- Далее
На втором шаге Мастера для создания нового подключения нажимаем кнопку Получить данные.
После нажатия кнопки Получить данные. в открывшемся диалоговом окне предлагается выбрать источник данных для будущей сводной таблицы. Дальнейшая работа будет рассмотрена на примере файла Access. Если Вы импортируете данные из файла .mdb, то следует выбрать вариант База данных MS Access , а если более поздних версий .accdb, - то MS Access Database. Флажок Использовать Мастер запросов должен быть установлен
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 предложит сохранить изменения запроса – отвечаем положительно.
Подключение к источнику данных
Здравствуйте! Пытаюсь подключиться к базе данных через OleDbConnection, но не выходит =( using.
Подключение к удалённому источнику данных
Здравствуйте! Подскажите пожалуйста, какие есть варианты, или как обычно делают, или как сделать.
Как выполнить запрос к источнику данных MS Excel
Известно, что с помощью компонента ADO можно обращаться к любым базам данных через ODBC. Но когда я.
Импорт внешних данных в Excel из Excel
Дайте, пожалуйста, ссылку или название книги и главы, где есть описание этого подробно. Что-то.
rsuan, зачем такие сложности?
Одновременная работа нескольких пользователей с одним источником - это возможно: присвоить источнику режим общей книги.
А вот по вашему. Воббще-то импорт происходит мгновенно, коротко говоря. И один раз.
Но есть вариант связать разные книги. Там принцип обновления данных из связанного закрытого источника при запуске сразу. А конкретно - надо вам почитать ХЕЛП экселя.
Вроде есть и команда в настройках обновления данных по времени, но уже не помню. Редко применяется у меня.
То есть проверьте все настройки главной книги, если она позаимствована. А если ваша личная. то читайте или ждите ответа ещё некоторое время от кого-то, кто помнит на память это дело.
Книгу-источник делал общей (Доступ к книге) - не помогло.
По логике да, импорт произошёл (хоть и через каждые X минут), и отстань от источника, ан нет, всё равно почему-то держит его.
Делал так (Office 2007): Данные \ Существующие подключения \ Найти другие \ указываю файл \ указываю именованный диапазон ячеек. Других вариантов не нашёл, в т.ч. в Хелпере.
Настройки подключения испробовал разные. Если без фонового, без периодического обновления, без обновления во время открытия, то сделав хоть одно ручное обновление, сразу файл-источник становится доступным только для чтения.
Насчёт личной или позаимствованной книги не понял вас. Это просто другой файл, созданный мной же для тестовых целей отработки данного механизма.
Вопрос остаётся. Подключение к диапазону ячеек другой книги через Данные \ Существующие подключения, блокируют подключаемую книгу, не давая параллельно её открывать для редактирования. Можно ли как-то избежать блокировки? вас. Это просто другой файл, созданный мной же для тестовых целей отработки данного механизма. что вы ее знаете-она ваша личная, а не кто-то дал. Значит -никаких хтростей в них нет.Интересно. попробую завтра на работе поиграться.
rsuan, да. Кое-что получилось (извиняюсь за задержку).
Итак, вводные данные (эксель 2002):
1. Есть книга 1 - предназначена для всех желающих вносить туда инфу.
может быть с монопольным доступом или с общим доступом.
2. Есть книга 2 - совершенно другая с другой инфой, но она имеет для заполнения некоторых ячеек доступ к книге 1 методом импортирования данных к внешнему источнику данных.
Есть два метода организации связи с книгой 1:
1. Книга 1 закрыта. Книга 2 открыта и организуется связь "импорт от внешнего источника данных".
В этом случае, независимо от настроек книги 1 и книги 2, книга 2 может обновляться, но книга 1 может открыться только в режиме для чтения.
2. Книга 1 открывается ПЕРВОЙ. Книга 2 открывается после книги 1.
Организовать из книги 2 связь "импорт от внешнего источника данных".
В этом варианте ЛЮБОЙ может работать с книгой 1 в ОБЫЧНОМ РЕЖИМЕ для редактирования .
Книга 2 всё равно будет обновлять хоть по времени, хоть в фоновом режиме, хоть вручную "ОБНОВИТЬ".
То есть это самый благоприрятный режим работы с двумя книгами, если изначально открыть сначала книгу 1, а потом открывать вторую книгу для образования связей с книгой 1.
Можно после такого режима создания связей закрыть книгу 1, работать с книгой 2, а потом снова окрыть книгу 1 - и она откроется для полноценной работы.
Также после такого режима (режим 2) можно закрыть обе книги. Потом открыть сначала книгу 2. Через некоторое время - книгу 1, и книга 1 откроется полноценно для редактирования.
Но замечен один нюанс: иногда нельзя закрыть книгу 1 - пишет, что книга 1 заблокирована. Выход - закрыть книгу 2, потом закроется книга 1.
Также иногда бывает, что после закрытия книги 1 при открытой книге 2 потом заново открыть книгу 1 - пишет только для чтения. Но если сразу отказаться открыть книгу и повторно её открыть ===то всё нормально.
ВЫВОД: Если необходимо, чтобы клиенты не моглы исправлять данные в книге 1 при работе с книгой 2 ==== применять образование связей при помощи варианта 1.
Если же необходимо полноценная работа с книгой 1 при открытой книге 2====то применять свариант 2.
Проверить работу данных вариантов в режиме сети (когда книга 1 и книга 2 - на разных ЭВМ, да ещё возможен третий комп - откуда будут заходить на книгу 1 ) - не хватило времени. Если надо - завтра попробую. Но это на работе.
Последнее дополнение – это набор инструментов BI (business intelligence). Эти инструменты дают возможность представить данные с помощью традиционных инструментов, таких как Таблицы, сводные таблицы и диаграммы, чтобы эффективно рассказать историю. До сих пор мы изучали роль Таблиц в рабочей книге. В этой главе рассматривается, как Excel использует Таблицы для обработки внешних данных.
Рис. 8.1. Группа Получить и преобразовать данные вкладки Данные
Подключение к внешним данным
Вы можете получить доступ к внешним источникам через вкладку Данные, группу Получить и преобразовать данные (рис. 8.1). Подключения к данным хранятся вместе с книгой, и вы можете просмотреть их, выбрав пункт Данные –> Запросы и подключения.
Подключение к данным может быть отключено на вашем компьютере. Для подключения данных пройдите по меню Файл –> Параметры –> Центр управления безопасностью –> Параметры центра управления безопасностью –> Внешнее содержимое. Установите переключатель на одну из опций: включить все подключения к данным (не рекомендуется) или запрос на подключение к данным.
Рис. 8.2. Настройка доступа к внешним данным; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Подробнее о подключении к внешним источникам данных см. Кен Пульс и Мигель Эскобар. Язык М для Power Query. При использовании таблиц, подключенных к данным можно переставлять и удалять столбцы, не изменяя запрос. Excel продолжает сопоставлять запрошенные данные с правильными столбцами. Однако ширина столбцов обычно автоматически устанавливается при обновлении. Чтобы запретить Excel автоматически устанавливать ширину столбцов Таблицы при обновлении, щелкните правой кнопкой мыши в любом месте Таблицы и пройдите по меню Конструктор –> Данные из внешней таблицы –> Свойства, а затем снимите флажок Задать ширину столбца.
Рис. 8.3. Свойства Таблицы, подключенной к внешним данным
Подключение к базе данных
Для подключения к базе данных SQL Server выберите Данные –> Получить данные –> Из базы данных –> Из базы данных SQL Server. Появится мастер подключения к данным, предлагающий элементы управления для указания имени сервера и типа входа, который будет использоваться для открытия соединения. Обратитесь к своему администратору SQL Server или ИТ-администратору, чтобы узнать, как ввести учетные данные для входа.
Рис. 8.4. Подключение к базе данных SQL Server
При импорте данных в книгу Excel их можно загрузить в модель данных, предоставив доступ к ним другим инструментам анализа, таким как Power Pivot.
Имя Таблицы
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 открывает диалоговое окно:
Текст файл был подготовлен авторами книги в англо-американской традиции, поэтому перед размещением данных в книге Excel, их нужно доработать. Кликните кнопку Преобразовать данные, и измените формат столбцов OrderDate, Cost и Total используя локаль (подробнее см. Изменение настроек Power Query, действующих по умолчанию).
Рис. 8.6. Данные после преобразования в редакторе Power Query
Теперь данные можно поместить на лист Excel. Кликните Закрыть и загрузить. Excel по умолчанию поместит данные в Таблицу:
Рис. 8.7. Обработанные данные из текстового файла в Таблице Excel
Чтобы обновить подключение к текстовому файлу, необходимо обновить запрос.
Работа с данными из Azure Marketplace
Azure Marketplace является хранилищем данных и приложений анализа данных. Это облачная инфраструктура и платформа, которая используется для создания, развертывания и управления облачными службами. Данные, доступные в Azure Marketplace, можно экспортировать в текстовые файлы, поместить в модель данных Power Pivot или непосредственно в Таблицу. Если запрашиваемый набор записей превышает 1 млн, его придется импортировать в модель данных, которая поддерживает неограниченное число строк.
Для подключения к Azure Marketplace из Excel требуется URL-адрес данных и ключ учетной записи. На домашней странице Azure Marketplace щелкните ссылку Databases. Появится страница со списком наборов данных; некоторые из них бесплатны, а некоторые доступны за плату. Выберите один из бесплатных источников данных. Пройдите регистрацию. После подключения к набору данных Azure Marketplace это соединение становится таким же, как и любое другое, и его можно обновить, чтобы работать с самыми последними данными. Имейте в виду, что каждое соединение считается трансакцией.
У меня есть электронная таблица с несколькими таблицами, где данные извлекаются из внешнего источника данных (базы данных SQL). Соединения / таблицы обновляются, если изменить параметр в раскрывающемся списке и затем нажать кнопку для запуска VBA.
"Имя источника данных не найдено, и драйвер по умолчанию не указан"
Однако, если я просматриваю электронную таблицу и нажимаю обновить каждую отдельную сводную таблицу, они обновляются без ошибок.
Так что либо мне нужен способ обновить сводные таблицы с таблицами, либо иметь кнопку, которая обновляет только сводные таблицы, а не подключения к внешним данным.
Любые идеи приветствуются, я не знаю, с чего начать с этого!
4 ответа
Вы можете обновить данную сводную таблицу на Sheet1 следующим образом:
Это обновит первую сводную таблицу на Sheet1. Измените порядковый номер на другой.
Вы можете обновить все сводные таблицы на заданном листе, вызвав эту процедуру:
Вы должны вызвать указанную выше процедуру из того же кода, связанного с кнопкой, упомянутой в вашем вопросе, которая обновляет таблицы.
Если вы хотите обновить все сводные таблицы в книге, вы можете использовать эту версию процедуры:
Вы бы назвали эту версию так:
Бекки: По какой причине вы не заполняете эти сводные таблицы непосредственно из запроса SQL? Если вам по какой-то причине не нужны эти таблицы, я бы предложил просто отказаться от них и просто преобразовать данные прямо в сводные таблицы. В противном случае вы фактически дважды сохраняете одни и те же данные в файле. (Или трижды, если вы не сняли флажок «Сохранить исходные данные с файлом» в разделе «Сводная таблица»> «Данные»> «Параметры».
Если вам действительно нужно их обновить - и если к каждой таблице подключено несколько сводных таблиц, - тогда более эффективно перебирать базовые кеши сводных кэшей и обновлять любое место, где тип источника - диапазон Excel. В VBA говорят, что где pc.SourceType = xlDatabase
Если вместо этого выполнять итерацию по каждой сводной таблице, то если к одному PivotCache подключено несколько сводных таблиц, в конечном итоге вы будете выполнять больше обновлений, чем вам нужно. Например, если у вас есть 10 сводных таблиц, которые все указывают на одну и ту же таблицу, вам не нужно обновлять эти 10 сводных таблиц по отдельности. Скорее, вам просто нужно обновить один общий кеш PivotCache. Если вы обновляете эти 10 сводных таблиц по отдельности, то фактически вы обновляете каждую из этих 10 сводных таблиц 10 раз.
Конечно, если ваши сводные таблицы маленькие, вы не заметите никакой разницы между моим кодом и Excel Heros.
Я добавил следующее в конце своего кода, который, похоже, работал нормально.
Я знаю, что это старый пост, но поделитесь им на случай, если это поможет любому, кто исследует это, как и я - это может быть новым для Excel 2016 (в настоящее время у меня нет доступа к более старым версиям для тестирования), однако я обнаружил, что вы можете отключите параметр «Обновить с обновлением всего» для внешних источников данных. Это позволит вам использовать «Обновить все» на вкладке «Данные» для одновременного обновления всех сводных таблиц без повторного обновления запроса внешнего источника данных:
- Вкладка "Данные"> Раздел "Подключения"> Открытые подключения
- Выберите подключение в окне Подключения к книге и нажмите Свойства .
- В окне свойств снимите флажок «Обновлять это соединение при обновлении всех».
- Повторите эти действия для любых дополнительных внешних подключений, которые вы не хотите обновлять автоматически.
Я использую это в макросе отчетов, вызывая обновления ручного запроса один раз: (выбирая ячейку в каждой таблице, созданной запросом)
А затем вызываем refresh all по мере необходимости для обновления моих сводных таблиц:
Читайте также: