Данный источник не содержит видимых таблиц excel

Обновлено: 05.02.2023

Сводные таблицы - один из самых замечательных инструментов в 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) и укажем исходный файл с таблицами, которые надо собрать:

Запрос к файлу 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), спрашивает:
"Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить".

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

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

Общие сведения об извлечении, преобразовании, загрузке (ETL) о том, где могут возникать ошибки

При обновлении данных могут возникать ошибки двух типов.

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

Не удалось подключиться к службе или ресурсу. Проверьте подключение.

Не удалось найти файл, к который вы пытаетесь получить доступ.

Сервер не отвечает и, возможно, находится в состоянии обслуживания.

Это содержимое не доступно. Возможно, он был удален или временно недоступен.

Подождите. данные загружаются.

Получить сведения о справке На сайте Office справки и обучения. Она не только содержит большой объем справки, но и сведения об устранении неполадок. Дополнительные сведения см. в устранении и обходных решениях недавних проблем в Excel для Windows.

Использование технического сообщества Используйте веб-Community Майкрософт для поиска обсуждений, относящихся к вашей проблеме. Весьма вероятно, что вы не первый, кто испытывает проблему, другие люди занимаются ее решением и даже могут найти решение. Дополнительные сведения см. в Microsoft Excel Community и Office Answers Community.

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

Обратитесь в Office поддержки На этом этапе, скорее всего, вы понимаете проблему гораздо лучше. Это поможет вам сосредоточиться на беседе и сократить время, затраченное на поддержку Майкрософт. Дополнительные сведения см. в Microsoft 365 и Office службе поддержки клиентов.

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

Проблемы со службами и серверами Скорее всего, причина — периодические ошибки сети и связи. Лучше всего подождите и попробуйте еще раз. Иногда проблема просто утихает.

Изменения расположения или доступности База данных или файл были перемещены, повреждены, переведены в автономный режим на обслуживание или аварийно сбой базы данных. Дисковые устройства могут быть повреждены, а файлы будут потеряны. Дополнительные сведения см. в этой Windows 10.

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

Открытые или заблокированные файлы Если открыт текст, CSV или книга, изменения, внесенные в файл, не включаются в обновление до тех пор, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован и к нему нельзя получить доступ, пока он не будет закрыт. Это может произойти, если другой человек использует версию Excel. Попросите их закрыть файл или проверить его. Дополнительные сведения см. в статьи Разблокировкафайла, заблокированного для редактирования.

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

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

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

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

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

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

Математические Вы пытаетесь умножить текстовое значение на числовое значение.

Объединения Вы попытались объединить строки, но одна из них числовая.

Безопасно экспериментируйте и итерации Если вы не уверены, что преобразование может иметь отрицательное влияние, скопируйте запрос, проверьте изменения и итерации с помощью вариантов команды Power Query. Если команда не работает, просто удалите созданное вами шаг и попробуйте еще раз. Чтобы быстро создать образец данных с одной схемой и структурой, создайте Excel таблицу из нескольких столбцов и строк и импортировать их (выберите данные > Из таблицы илидиапазона). Дополнительные сведения см. в таблицах Создание таблицы и Импорт из Excel таблицы.

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

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

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

Типы данных и заглавные колонок столбцов

Power Query автоматически добавляет в запрос два шага сразу после первого шага: "Продвиганые заглавные колонок", которая преобразует первую строку таблицы в заглавный, и Changed Type(Измененный тип), который преобразует значения из типа Данных Any в тип данных на основе проверки значений из каждого столбца. Это удобно, но иногда может потребоваться явно контролировать это поведение, чтобы предотвратить ошибки случайного обновления.

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

Переименование столбца

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

Разделить столбец

Разделение копий исходного столбца, а не исходного столбца.

Объединение столбцов

Объединять копии исходных столбцов, а не исходных.

Удаление столбца

Если нужно сохранить небольшое количество столбцов, используйте выбор столбца, чтобы сохранить нужные.

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

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

Дополнительные сведения см. в статье Удаление столбцов.

Замена значения

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

Дополнительные сведения см. в области Замена значений.

Pivot и Unpivot

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

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

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

Дополнительные сведения см. в статьях Сводные столбцы и Ото всех столбцов.

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

Future-proof with query parameters Используйте параметры запроса для уменьшения изменений, например расположения данных. Вы можете создать параметр запроса, чтобы заменить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.

Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.

Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.

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

В Excel щелкните Power Pivot > Управление, чтобы открыть окно Power Pivot.

Просмотрите вкладки в окне Power Pivot.

Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.

Окно со вкладками PowerPivot

Чтобы просмотреть происхождение таблицы, щелкните Свойства таблицы.

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

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

в Excel щелкните Данные > Подключения;

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

щелкните Свойства > Определение, чтобы просмотреть строку подключения.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Загрузка из Excel в 1С, довольно частая задача, которую приходится решать программистам 1С. В данной заметке будет показано, как произвести загрузку данных из файла Excel в 1С, наиболее быстрым и оптимальным способом. Перед тем как показать примеры кода, объяснюю как производить отладку и проверку выполнения SQL запросов к файлу Excel, для последующей загрузки данных в программу 1С.

Для примера будет использоваться файл формата Excel со следующей структурой и данными:

Загрузка из Эксель в 1С. SQL запрос к файлу Excel. Загрузка из Excel с помощью запроса SQL в 1С.

Данные Excel для загрузки в 1С


Чтобы выполнить SQL запрос к файлу Excel и проверить результат, необходимо запустить программу Microsoft Excel, перейти в главном меню программы на вкладку «Данные», далее нажать кнопку «Из других источников» и в появившемся меню выбрать пункт «Из Microsoft Query»:

Запуск MSQuery для загрузки из Эксель в 1С

Запуск MSQuery для загрузки из Эксель в 1С


После появится окно «Выбор источника данных», в нем необходимо выбрать пункт «Excel Files*» и нажать «ОК»:

Выбор источника для загрузки

Выбор источника для загрузки


Далее выбрать непосредственно ваш файл Excel для выборки данных. Может появится предупреждение, что «Данный источник данных не содержит видимых таблиц», игнорируем его и нажимаем «ОК». После необходимо в параметрах установить пункт отображать системные таблицы:

Выбор системные таблицы

Выбор системные таблицы


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

Выбор столбцы запроса

Выбор столбцы запроса


Дальше необходимо несколько раз нажать «Далее» и на последнем шаге, выбрать пункт «Просмотр или изменение данных в Microsoft Query» и «Готово»:

Просмотр изменение


Откроется окно «Microsoft Query», в котором мы видим выборку данных из нашего файл Excel. Для тестирования и редактирования SQL запросов к файлу Excel, используется кнопка «SQL», при помощи которой вызывается окно редактирования запроса:

Запрос SQL


Теперь, когда вы знаете, как тестировать ваши SQL запросы к файлу Excel, попробуем загрузить данные в программу 1С, для этого создадим внешнюю обработку, добавим основную форму, для удобства вывода информации добавим реквизит формы с типом «Табличный документ» и отобразим его на форме. В модуле формы добавим процедуру «ПриОткрытии».
Далее приведен код с поясняющими комментариями:

Результат выполнения внешней обработки по загрузке данных из файла Excel в 1С с помощью запроса SQL, показан на изображении:

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

Недопустимое имя поля сводной таблицы

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

Недопустимое имя поля сводной таблицы ошибка

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

Рассмотрим данные ниже,

Пример сводной таблицы данных

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

Relicate Pivot Error

Исправления для устранения ошибки — недопустимое имя поля сводной таблицы

Исправление 1. Создайте таблицу Excel из данных, а затем превратите ее в сводную таблицу.

При создании таблицы Excel заголовки к пустым столбцам добавляются автоматически.

Шаг 1. Выберите необходимые данные

Шаг 2. Нажмите «Вставить» в верхнем меню.

Шаг 3: нажмите на таблицу

Шаг 4. В открывшемся окне «Создать таблицу» проверьте диапазон данных.

Шаг 5: Нажмите ОК

Создание таблицы из минимальных данных

Исправление 2: проверьте диапазон в диалоговом окне «Создание сводной таблицы».

Иногда по ошибке будет выбран пустой столбец рядом с последним столбцом.

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

Проверить диапазон

Исправление 3: проверьте, скрыты ли столбцы с пустыми заголовками

Шаг 1. Выберите все столбцы в сводной таблице и щелкните по ней правой кнопкой мыши.

Шаг 2: Выберите «Показать» в контекстном меню.

Показать столбцы

Исправление 4: Разъедините объединенные ячейки в строке заголовка.

Шаг 1. Найдите объединенную ячейку в строке заголовка и щелкните по ней.

Шаг 2. В верхнем меню нажмите «Домой».

Шаг 3. Нажмите стрелку вниз рядом с кнопкой «Объединить и центрировать»

Шаг 4: выберите Unmerge Cells

Разъединить объединенные ячейки

Исправление 5: проверьте, не перекрывается ли название заголовка с пустым заголовком рядом с ним.

Рассмотрим приведенный ниже пример: серийный номер из столбца A перекрывает B

Перекрытие имен

Шаг 1. Щелкните каждый столбец и проверьте его содержимое в строке формул. См. Пример ниже, A1 имеет значение Серийный номер.

Читайте также: