Следующий диапазон данных не был обновлен excel
После довольно долгих поисков я теряю рассудок и надеюсь, что кто-нибудь может мне помочь.
Я стараюсь описать свою проблему как можно яснее и кратко:
Я создаю книгу MS Excel 2010, которая включает несколько (более 90) внешних подключений к спискам и библиотекам SharePoint 2013.
Эти подключения были созданы с помощью встроенной функции SharePoint «Экспорт в Excel» (в списке / библиотеке-ленте), а затем файлы подключения были экспортированы в другую ферму SP. (Первый «источник» -SP-Farm - от заказчика, второй - наш собственный интранет)
Мне нужно обновлять эти подключения один раз в день с помощью автоматического макроса.
Задание таймера откроет книгу ночью и выполнит макрос «RefreshAllConnections», который делает немного больше, чем просто обновление (например, записывает дату и время обновления). Таким образом, когда это происходит, пользователь не присутствует.
И вот где моя проблема:
Время от времени некоторые из этих подключений не могут быть обновлены. Excel отображает предупреждение (перевод с немецкого):
"следующий диапазон данных не может быть обновлен: owssvr (. ) Вы хотите продолжить обновление? (ОК) / (Отмена)
Что я выяснил на данный момент:
1) Проблема всегда в библиотеке
2) Это довольно случайно, какая библиотека не будет обновляться и когда
3) Проблема устраняется сама собой через некоторое время (поэтому я предполагаю, что это как-то связано с библиотекой, используемой / изменяемой кем-то другим)
Теперь вот шансы:
1) В предупреждении всегда используется "старое / исходное" имя соединения, которое я уже изменил.
2) Когда я нажимаю ОК, макрос просто продолжается в следующей строке, никакой ошибки не возникает.
3) Если я нажму «Отмена», появится ошибка 1004 (которую я, по крайней мере, могу поймать, так что все будет в порядке).
Поскольку это происходит автоматически ночью, поблизости нет пользователей, которые могли бы ответить на эти предупреждения. Так:
1) Макрос должен автоматически отвечать на эти предупреждения с помощью «Отменить», ЕСЛИ они всплывают (и я понятия не имею, как это сделать!)
2) Я отключаю их через «Application.displayAlerts = false.
ОДНАКО: это автоматически ответит им ответом по умолчанию, то есть «ОК». Однако это не вызывает ошибку, которую я мог бы уловить, поэтому мой макрос теперь не будет, независимо от того, действительно ли сработало обновление или нет.
Ну вот и все. Извините за длинный пост и спасибо, что прочитали.
Надеюсь, у кого-то из вас есть идея.
Может ли быть проблема в подключениях, автоматически созданных SharePoint?
Вы можете изменить свойства диапазона внешних данных для управления данными, импортируемыми на сайт с помощью подключения к внешнему источнику данных, такому как база данных, веб-запрос или текстовый файл.
Диапазон внешних данных — это определенное имя или имя таблицы, которое определяет расположение данных, которые были доставлены на таблицу. При подключении к внешним данным Excel автоматически создается диапазон внешних данных. Единственным исключением из этого является отчет, подключенный к источнику данных, так как в отчете не используется диапазон внешних данных. В Excel вы можете форматирование и размыкать диапазон внешних данных или использовать его в вычислениях, как и любые другие данные.
Свойства диапазона внешних данных
У диапазона внешних данных есть дополнительные свойства (их не следует путать с свойствами запроса или подключения), которые можно использовать для управления данными, например для сохранения форматирования ячеок и ширины столбцов. Эти свойства диапазона внешних данных можно изменить, нажав кнопку Свойства в группе Подключения на вкладке Данные. Затем внести изменения, вы можете сделать следующее:
Для диапазонов внешних данных, созданных с помощью Microsoft Query и мастера подключения данных, используйте диалоговое окно Свойства внешних данных.
Для диапазонов внешних данных, созданных из импортируемого текстового файла или веб-запроса, который извлекает HTML-данные, используйте диалоговое окно Свойства диапазона внешних данных.
Для диапазонов внешних данных, созданных из веб-запроса, который извлекает данные XML, используйте диалоговое окно Свойства карты XML.
Выберите нужный диапазон внешних данных.
На строка формул щелкните стрелку рядом с полем Имя ивыберите имя нужного диапазона внешних данных.
Выберите Формулы >Диспетчер имен.
В диалоговом окне Диспетчер имен щелкните имя диапазона внешних данных и нажмите кнопку Изменить. Можно также дважды щелкнуть имя.
В диалоговом окне Изменение имени введите новое имя ссылки в поле Имя.
Примечание: Кнопка Закрыть закрывает только диалоговое окно Диспетчер имен. Вам не нужно нажимать кнопку Закрыть, чтобы зафиксировать уже внесенные изменения.
Вы можете изменить запрос для диапазона внешних данных, созданного из Microsoft Query, импортируемого текстового файла, веб-запроса или мастера подключения к данным.
Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.
Выберите Свойства >данных.
В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
Дополнительные сведения о построении и редактировании запросов в Microsoft Query см. в справке Microsoft Query.
Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.
Выберите > подключения & запросы> Connections, щелкните правой кнопкой мыши нужное подключение и выберите Свойства.
В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
В диалоговом окне Импорт текстового файла нажмите кнопку Импорт.
Внести изменения в импортируемый текстовый файл в мастере импорта текста и нажмите кнопку Готово.
Дополнительные сведения об импорте текстовых файлов см. в документе Импорт и экспорт текстовых файлов.
Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.
Выберите Свойства> данных.
Выберите > подключения & запросы> Connections, щелкните правой кнопкой мыши нужное подключение и выберите Свойства.
В диалоговом окне Свойства подключения перейдите на вкладку Определение и нажмите кнопку Изменить запрос.
В диалоговом окне Изменение веб-запроса внести изменения в веб-запрос и нажмите кнопку Готово.
Дополнительные сведения о создании и редактировании веб-запросов см. в Подключение на веб-страницу.
Щелкните ячейку в диапазоне внешних данных, для которой вы хотите изменить запрос.
Выберите Свойства> данных.
В диалоговом окне Свойства внешних данных нажмите кнопку Свойства подключения .
В диалоговом окне Свойства подключения перейдите на вкладку Определение.
Выполните одно из указанных ниже действий.
В поле Тип команды выберите таблицу, а затем в текстовом поле Команда измените значение на имя соответствующей таблицы, представления или запроса.
В поле Тип команды выберите SQL или По умолчанию, а затем в текстовом поле Команда введите SQL команд.
Примечание: В зависимости от того, каким образом было определено подключение, поле Тип команды может быть недоступно (оно отображается затемнено).
В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, который вы хотите скопировать.
Для Excel выберите имя диапазона и нажмите CTRL+A, чтобы выбрать заглавные таблицы.
Если вы хотите включить подписи столбцов или формулы, которые не являются частью внешнего диапазона данных, выберем ячейки, содержащие подписи столбцов или формулы, которые нужно скопировать. Щелкните стрелку рядом с полем Имя в панели формул и выберите имя диапазона внешних данных, который вы хотите скопировать.
Выберите Главная >Копировать.
Переключение на книгу, в которую нужно вклеить диапазон внешних данных.
Щелкните левую верхнюю ячейку область вставки.
Чтобы гарантировать, внешние данные данные не заменяют существующие, убедитесь в том, что в ячейке или справа от нее нет данных.
На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.
Примечание: Если скопировать только часть диапазона внешних данных, запрос не будет скопирован, а скопированные данные не будут обновлены.
Вы можете управлять обработкой меньшего или большего набора данных, который возвращается в Excel при обновлении данных.
Выберите Свойства> данных.
В диалоговом окне Свойства диапазона внешних данных в поле Если количество строк в диапазоне данных изменяется при обновлении, выберите одно из следующих вариантов:
Вставка ячеек для новых данных, удаление неиспользованых ячеек
При добавлении одной или более строк в источник данных ячейки непосредственно под диапазоном внешних данных перемещаются вниз, но ячейки справа от диапазона внешних данных не перемещаются.
При удалении одной или более строк в источнике данных ячейки непосредственно под диапазоном внешних данных перемещаются вверх, но ячейки справа от диапазона внешних данных не перемещаются.
Вставка строк для новых данных, очистка неиспользованых ячеек
При добавлении в источник данных одной или двух строк ячейки непосредственно под и справа от диапазона внешних данных перемещаются вниз.
При удалении одной или двух строк из источника данных ячейки непосредственно под и справа от диапазона внешних данных не перемещаются.
Переописывание существующих ячеек с помощью новых данных, очистка неиспользовавших ячеек
При добавлении одной или более строк в источник данных ячейки непосредственно под диапазоном внешних данных перезаписываются, но ячейки справа от диапазона внешних данных не перемещаются.
При удалении одной или двух строк из источника данных ячейки непосредственно под и справа от диапазона внешних данных не перемещаются.
Выберите Свойства> данных.
В области Форматирование и макет данныхсделайте следующее:
Чтобы включить имена полей в качестве первой строки, выберите поле Включить имена полей.
Примечание: Этот квадратик доступен только для диапазона внешних данных, преобразованного из программы Microsoft Office 2003 или в диапазон внешних данных, созданный программным путем.
Чтобы добавить столбец с номерами строк, выберите включить номера строк.
Примечание: Этот флажок не доступен для импортируемого текстового файла, XML-файла или веб-запроса.
Чтобы сохранить форматирование ячейки, которое вы применяли, выберите сохранить форматирование ячеок.
Примечание: Для веб-запроса этот параметр автоматически очищается при выборе параметра Полное форматирование HTML в диалоговом окне Параметры. Для доступа к диалоговом окну Параметры вы можете присоединения к диалоговом окну Изменить запрос.
Чтобы сохранить за настраиваемую ширину столбцов, установите установите в поле Изменить ширину столбца.
Чтобы сохранить применяемую сортировку, фильтрацию и макет столбцов, выберите сохранить столбец для сортировки, фильтрации и макета.
Примечание: Этот поле не доступно для веб-запроса.
Чтобы сохранить применяемую фильтрацию столбцов, выберите сохранить фильтр столбцов.
Примечание: Этот флажок доступен только для веб-запроса, основанного на данных XML.
Примечание: Следующая процедура относится только к диапазону внешних данных, преобразуемого из программы Microsoft Office 2003, или к диапазону внешних данных, который создается программным путем. Все диапазоны внешних данных, созданные с помощью пользовательского интерфейса, создаются Excel таблицами. Эти таблицы автоматически расширяются при добавлении новых строк и заполняют вниз вычисляются столбцы.
Введите формулу в ячейку, смежную с первой строкой данных во внешнем диапазоне данных.
Примечание: Первая строка данных может быть первой или второй в диапазоне внешних данных в зависимости от того, содержит ли первая строка заглавные строки.
Вы выберите ячейку и дважды щелкните маркер заполнения формулу, чтобы скопировать формулу во все строки во внешнем диапазоне данных.
Выделите ячейку в диапазоне внешних данных.
Выберите Свойства> данных.
В диалоговом окне Свойства диапазона внешних данных выберите вариант Заполнить вниз формулы в столбцах, смежных с данными.
Примечание: Если диапазон внешних данных расширяется при обновить, Excel копирует только формулы, смежные с диапазоном внешних данных или внутри них.
При заморозке диапазона внешних данных сохраняются данные, но не их запрос, поэтому заблокированный диапазон внешних данных невозможно обновить.
Щелкните на нем таблицу Excel, из которой вы хотите удалить подключение к данным.
В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, из которого вы хотите удалить подключение к данным. Затем будет выбран диапазон данных.
На вкладке Инструменты в группе Данные внешней таблицы нажмите кнопку Оторужить связь. Диапазон данных остается без названия, но подключение удаляется.
Примечание: Следующая процедура относится только к диапазону внешних данных, преобразуемого из программы Microsoft Office 2003, или к диапазону внешних данных, который создается программным путем. Все диапазоны внешних данных, созданные с помощью пользовательского интерфейса, создаются Excel таблицами. Эти таблицы автоматически расширяются при добавлении новых строк и заполняют вниз вычисляются столбцы.
Выберите Свойства> данных.
В окне Определениезапроса разоберите поле Определение запроса сохранить.
Примечание: При сохранение книги из нее удаляется ее запрос. Однако сохраненные файлы запросов к базе данных (DQY или ODC-файлы) не удаляются. Если вы сохранили запрос при его создания в мастере запросов или в Microsoft Query, файл запроса будет сохранен на вашем компьютере, и вы сможете использовать его повторно для извлечения внешних данных. Дополнительные сведения см. в справке Microsoft Query.
Примечание: Следующая процедура относится только к диапазону внешних данных, который создается с помощью мастера подключения данных или Microsoft Query (а не к импортируемму текстовом файлу или веб-запросу), к диапазону внешних данных, преобразованного из программы Microsoft Office 2003, или к внешнему диапазону данных, созданному программным путем. Все диапазоны внешних данных, созданные с помощью пользовательского интерфейса, создаются Excel таблицами.
Вставьте пустую строку над диапазоном внешних данных одним из следующих образом:
Вы выберите ячейку, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки щелкните стрелку рядом с кнопкой Вставить ивыберите Вставить строки на лист. Поскольку вы выбрали одну ячейку, над текущей строкой вставляется одна новая строка.
Щелкните селектор строки, над которой нужно вставить новую строку. Затем на вкладке Главная в группе Ячейки нажмите кнопку Вставить. Поскольку вы выбрали одну строку целиком, над этой строкой будет вставлена одна новая строка.
Введите нужные подписи в ячейки пустой строки.
Выделите ячейку в диапазоне внешних данных.
Выберите Свойства> данных.
В диалоговом окне Свойства диапазона внешних данных в списке Форматирование и разметка данных сберитеполе Включить имена полей и нажмите кнопку ОК.
Чтобы удалить существующие имена полей и обновить диапазон внешних данных, щелкните ячейку в диапазоне внешних данных и выберите обновить данные .
Примечание: При извлечении данных из базы данных изменения имен столбцов в Microsoft Query сохраняются во внешнем диапазоне данных. Сведения об изменении имен столбцов в запросе см. в справке Microsoft Query.
Щелкните на нем нужный диапазон внешних данных.
В области формул щелкните стрелку рядом с полем Имя ивыберите имя диапазона внешних данных, который вы хотите удалить.
Если диапазон внешних данных является Excel, нажмите CTRL+A, чтобы выбрать всю таблицу.
Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.
По умолчанию с помощью этой ссылки вы можете указать, что при ее автоматическом обновлении в книге, которая содержит ее, с помощью стеблицы.
Обновление вручную
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.
Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.
Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных при открытии книги
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Данные проверьте поле Обновлять данные при открытии файла.
Обновление вручную
Щелкните в любом месте сводной таблицы.
Отобразит вкладку "Параметры" и "Конструктор" в области "Инструменты для работы со таблицами".
На вкладке Параметры в группе Данные сделайте следующее:
Чтобы обновить данные в соответствие с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT+F5.
Вы также можете щелкнуть правой кнопкой мыши и нажать кнопку Обновить.
Чтобы обновить все стебли в книге, нажмите кнопку Обновить и выберите обновить все.
Если обновление занимает больше времени, чем вы ожидаете, щелкните Параметры > обновить > Состояние обновления, чтобы проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
Щелкните Параметры > параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных в pivottable при открытии книги
Щелкните в любом месте сводной таблицы.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры таблицы на вкладке Данные выберите параметр Обновлять данные при открытии файла.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет "на лету" делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:
Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:
- Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
- На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.
Способ 1. Сборка таблиц для сводной с помощью Power Query
Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.
Сначала создадим новый пустой файл в Excel - в него будет происходить сборка и в нем же потом будет создаваться сводная таблица.
Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос - Из файла - Excel (Get Data - From file - Excel) и укажем исходный файл с таблицами, которые надо собрать:
В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :
Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого - Источник (Source) :
Теперь мы видим общий список всех листов. Если в файле кроме листов с данными есть еще какие-то побочные листы, то на этом шаге наша задача - отобрать только те листы, с которых нужно загрузить информацию, исключив все остальные с помощью фильтра в шапке таблицы:
Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :
Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):
Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:
Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:
Сохраним всё проделанное с помощью команды Закрыть и загрузить - Закрыть и загрузить в. (Close & Load - Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :
Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка - Сводная таблица (Insert - Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:
Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data - Refresh All) .
Способ 2. Объединяем таблицы SQL-командой UNION в макросе
Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.
Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert - Module и копируем туда следующий код:
Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) .
Минусы такого подхода:
- Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
- При изменении количества листов необходимо правки в код макроса (массив SheetNames).
Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:
И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft - Microsoft Access Database Engine 2010 Redistributable
Способ 3. Мастер консолидации сводных таблиц из старых версий Excel
Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:
В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands) :
После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:
А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:
Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.
Ссылки по теме
если мне не нужно собирать данные из разных источников как мне сделать точно такой же конструктор чтобы я мог перетаскивать шапки и у меня менялся отчет
Обожаю такие формулировки вопросов. Куда перетаскивать? Какой конструктор? Если вы про создание сводной таблицы по одному диапазону без консолидации, то см. первую ссылку в Ссылки по теме .
Возможно изменить функцию так, что-бы имена листов вычислялись из книги (каждый раз писать новые имена в макрос не удобно)
Спасибо!
Добрый день, Николай!
Спасибо за Ваш сайт и за данную статью.
на одном из форумов нашел вот такое решение с листами .
http://www.excelworld.ru/forum/10-17185-1
Заменить в макросе:
'массив имен листов с исходными таблицами
SheetsNames = Array("a1", "a2", "a3", "a4";)
Dim s As String
'Ввод массива имен листов с исходными таблицами
s = InputBox("Имена листов через запятую";)
s = Replace(s, ", ", ",";) 'Удаляем возможные пробелы после запятой
SheetsNames = Split(s, ",";)
Добрый день! Подскажите пожалуйста как поменять столбцы, в вашем файле берет столбик А:К, а мне например нужен К:Р )
Здавствуйте я использовал ваш пример в своих таблицах , все прекрасно но
есть нюанс\\ Дело в том что этот макрос не подсчитывает строки ,если их больше 50.000
(у меня тоже 4 листа как в примере но каждый состоит из 110.000-120.000 строк )
Может я что-то упускаю.
Подскажите если не трудно.
В коде этого ограничения точно нет. Возможно, это уже нехватка ресурсов компьютера (ограничение кэша Excel 2010 версии - 2 Гб). Точнее сказать, не видя вашего примера, сложно.
Может вам уже бесплатную надстройку PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо.
Добрый день! Подскажите, пожалуйста, а как в PowerPivot можно объединить данные из двух источников данных? (у меня данные в sharepoint, отдельно я их из списков могу получить, а как объединить в один источник данных для построения сводной - не могу найти.
как я установила опытным путем, ограничение 65536, т.е. кол-во строк в старом Excel
причем что характерно: если строк будет больше 65536, то брать в сводную таблицу будет не первые 65536, а например, первые 4000. или 9000.
т.е. получается даже не обрыв таблицы, а совсем что-то неадекватное.
дело точно не в кэше Excel, потому что я разбила каждую из своих 12 таблиц по 85 тыс.строк на две таблицы по 40-45 тыс строк.и стало все нормально.
т.е. 12 таблиц по 85 тыс.строк не сводились.
а 24 таблицы по 45 тыс.строк сводятся абсолютно корректно. кэша хватает
Николай, вещь полезная, но при использовании данного варианта становится невозможно использование СРЕЗОВ. Оптимизация кода не планируется?
Спасибо за наводку. Подумаем
Я подошел к решению этой проблемы с другой стороны. Я сделал макрос который на новом листе формирует таблицу, которая содержит дание с других листов независимо от колиства строк в этих таблицах, а также выделяет новою таблицу. Осталось только вставить сводную таблицу, но проблема заключается в том как задать диапазон даных для сводной таблици, так как количество строк может менятся. Я только начал изучать VBA и ище многого не усвоил, помогите пожайлуста.
Включите макрорекордер и создайте простую сводную таблицу. Затем посмотрите код VBA, который получился и замените в нем фиксированный диапазон с исходными данными на:
Вы можете использовать по-старинке так называемые "динамические диапазоны" (с формулой СМЕЩ, англ. OFFSET), но сейчас уже следует использовать "умные таблицы", которые автоматически расширяют диапазон. Соответственно, необходимо ссылаться на диапазон данных по имени этого диапазона, а не по адресу, и на таблицу - по ее имени.
Если у вас в столбце одновременно есть и текстовые и числовые значения, то что вы планируете увидеть в сводной?
хотелось бы видеть и текстовые и числовые данные.Вообще элементы как правило имеют числовые значения, но некоторые - тектовые и они просто не попадают в сформированную сводную таблицу
Тогда, боюсь, единственный вариант - форматировать все как текст. Поля смешанного типа тут не поддерживаются.
Макрос допускал ошибку на Win 8 x64, Office 2013 x64.
Помогла замена " Microsoft.Jet.OLEDB.4.0 " на " Microsoft.ACE.OLEDB.12.0 "
Спасибо за уточнение. На 64-битном Office мне его негде было потестировать
Добрый день, помогите пожалуйста:)
Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить
Спасибо
Выдает ошибку в SheetNames(1) Подскажите пожалуйста что я сделал неправильно? Ведь у PetrovichOskol видимо все получилось. С уважением. Юрий. |
Подскажите пожалуйста, вообще нет возможности сделать обновление сводной таблицы, которая была построена Вашим макросом?
Попробовал решить задачу с обновлением таблицы без повторного ее создания. Да и просят, вижу, многие.
Постарался не отходить от Вашего стиля, вынес часть кода по созданию объекта ADODB.Recordset в отдельную функцию GetData().
И добавил процедуру обновления кэша: UpdateCache() :
- получил ссылку на уже созданную сводную таблицу,
- подменил ей источник данных (PivotCache.Recordset),
- обновил саму таблицу.
Этого оказалось достаточно. При этом текущее состояние таблицы не нарушается, обновляются только данные.
Сравните:
Н.П.: "UNION ALL SELECT * FROM [" & SheetsNames(i) & "$]"
К.Л.: "UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
Различие вот здесь: у меня arrFiles(2) возвращает путь к файлу К.Л. --> C:\Users\User1\Downloads\KL_PivotWkbks\DB-Manitoba.xls
У Николая путь к файлу опущен.
формат строки подключения вот здесь:
www.connectionstrings.com/excel-2003/
При указании имени листа Excel надо добавлять знак "$" и оборачивать его в квадратные скобки "[" "]", как это показано выше.
Еще одно добавление для тех, кто будет читать, по поводу установки Microsoft Access. Эта программа есть не у всех (в стандартный комплект не входит), но здесь она и не нужна, нужен только OLEDB провайдер данных (Microsoft.Jet.OLEDB.4.0 или его аналоги под конкретную среду).
Владимир Безносюк, чуть выше (05.07.2013 11:58:49), спрашивает:
"Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить".
Вероятно, правильным будет использовать один, главный, кэш для всех созданных таблиц.
Пример изменения кэша для всех сводных таблиц книги:
В результате у всех созданных Вами сводных таблиц будет единый кэш. Правда, сводные таблицы, использующие его, будут иметь некоторые общие черты, такие как вычисляемые элементы и сгруппированные поля (возможность разделить кэш всё равно существует, но это другая тема).
"Работать" такая книга будет намного лучше.
Все поля выделены серым цветом и выглядят недоступными, однако они по-прежнему доступны для нажатия, и любые сделанные изменения остаются. Microsoft возмутила меня.
Вероятно, это означает, что вы сгруппировали два или более листа . Будьте очень осторожны. Любое изменение одного листа влияет на все сгруппированные листы (удаление строк, ввод значений и т. Д.)
Чтобы разгруппировать листы: • Щелкните вкладку любого листа, который не является частью группы, или • Щелкните правой кнопкой мыши одну из вкладок сгруппированных листов . Выберите: Разгруппировать
После того, как последний лист будет разгруппирован . метка [GROUP] исчезнет.
Чтобы сгруппировать листы . • Удерживайте клавишу CTRL при выборе вкладок.
Этот ответ цитируется Роном Кодерром.
На скриншоте показаны общие варианты работы с Excel вне зависимости от файлов. Если вы не можете редактировать книгу, проверьте, защищены ли структура книги или листы. Лента обзора> Снять защиту.
Хорошо, та же проблема возникла на всех листах в одной книге. Было выполнено следующее:
- закрытая и открытая книга (обрабатывает возможность «в режиме редактирования»; плюс)
- Гарантированный выбор одного рабочего листа (ранее не предлагалось)
- нажал Снять защиту в Файл -> Информация -> (раздел Защитить книгу)
- Отредактировал рабочий лист и сохранил книгу (доказано отсутствие защиты Excel или файлов)
Ничего не помогло. Это обошло проблему.
- Закрытая рабочая тетрадь
- Сделал копию трудовой книжки
- Изменено имя оригинала (у которого проблемы
- Открыл копию книги (с оригинальным названием)
Ни причина проблемы, ни "простое" исправление не были обнаружены, но теперь все в порядке.
В большинстве случаев вам просто нужно показать лист , который был кем-то скрыт или из-за неправильной конфигурации.
Если он был скрыт, откроется диалоговое окно с просьбой подтвердить действие отображения. Это было протестировано здесь в Excel 2010 и Excel 365.
У меня была эта проблема. Файл находился в режиме SHARED. Я этого не осознавал. Я щелкнул ПОДЕЛИТЬСЯ РАБОЧЕЙ КНИГОМ и снял флажок с надписью «разрешить изменения более чем одним пользователем . », и все мои пункты меню вернулись.
У меня была такая же проблема, исправленная снятием флажка с опции совместного использования книги (разрешить изменения более чем одним пользователем одновременно . )
У меня этого много, проблема в том, чтобы показать панель предварительного просмотра, где сохранен файл. Если на панели предварительного просмотра отображается файл, XL считает, что он открыт, поэтому изменения из меню запрещены. Щелкните другой файл или закройте панель предварительного просмотра, и все должно быть в порядке.
Возможно, включена защита листа, что отключит несколько параметров
Убедитесь, что вы не находитесь в режиме редактирования, нажав Esc (клавиша Escape), так как режим редактирования> закроет серым цветом многие пункты меню, в частности, большинство параметров редактирования (кроме вырезать> и вставить). Будет выделено серым цветом Параметры на Панели инструментов; многие параметры под данными и> под окном.
Инструменты -> Защита -> Снять защиту листа
Читайте также: