Сбой инициализации источника данных excel

Обновлено: 21.01.2025

Я в Excel 2010 подключаюсь к нескольким,отдельно доступ к БД 2010 из Excel через подключения к данным сводной таблицы.

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

однако, если я сохраню и закрою после обновления нескольких, а затем вернусь и обновлю последний, нет вообще проблема.

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

могу ли я воссоздать этот эффект через VBA без сохранения/закрытия? есть ли лучшее решение этой проблемы?

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

Код

Строку Подключения

Попытки Решения

Отключить Фоновое Обновление - уже отключен
отключить автоматическое восстановление (для сохранения памяти)
очистить "отменить стек" (для сохранения память)
'DoEvents' для задержки выполнения кода до завершения каждого обновления, изменение:

решение!

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

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

Это не полный ответ, а попытка помочь отладки, так что, надеюсь, мы сможем найти решение.

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

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

был ли BackgroundQuery всегда ложным?
была ли заметная задержка между каждым набором диалоговых окон (указывающая, что Excel ждет завершения обновления) или все они появились сразу после последнего один?
какая строка кода вызывает начальную ошибку? Если вы обновите соединения в обратном порядке (раскомментировав строку "шаг -1"), вы получите ошибку при том же соединении?
когда вы говорите, что можете обновить соединения вручную, это через другой макрос или через Data > > Connections > > Refresh?
ошибки, если вы вручную выберите "RefreshAll"?

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

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

вот что я сделал в VBA, когда я получал эту ошибку, хотя я скажу, что я не использовал его с БД MS access. У меня была одна книга excel, которую я использовал как "бегун", и она открывала другие книги одну за другой и обновляла их соединения. В основном у меня была переменная для path и extension и поместите имена каждой книги в массив и выполните цикл через массив.

Я объединил путь и расширение, чтобы дать мне полное имя файла, вы увидите это в цикле.

вот как выглядела моя петля :

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

в моем случае это был baseNameOfWorkbook & " POS Report"

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

Дайте мне знать, если это сработает.

вы можете использовать VBA для вызова обновлений индивидуально через activeworkbook.объект подключения. См.этот столб переполнения стека для некоторых подсказок по этому методу. Более атомистическое приложение может позволить лучше понять и контролировать. Например, как только у вас есть все шаги на месте, вы можете попробовать вставить DoEvents до решить вопрос.

очистить системную память, вы всегда можете запустить что-то вроде этого:

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

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

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

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

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

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

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

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

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

Получить сведения о справке На сайте 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-адрес. Существуют и другие способы уменьшения проблем с помощью параметров запроса. Дополнительные сведения см. в теме Создание запроса с параметрами.

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 предложит сохранить изменения запроса – отвечаем положительно.

Вот источник данных:

Я присоединился к Azure AD.

Похоже, новая установка OLEDB не сработала, хотя сообщалось, что она установлена успешно. Я вижу его в списке программ и компонентов «Поставщик Microsoft AS OLE DB для SQL Server 2016 RC1».

У меня 64-разрядный офис.

Решено! Перейти к решению.

У меня была именно эта ошибка. Я удалил надстройку, а затем переустановил, выбрав 64-битную версию. После этого я смог войти в систему и экспортировать данные для анализа. Хотя я установил правильную версию, мой коллега установил неправильную версию. Чистая установка решила проблему для нас обоих.

Я ответил на ваш вопрос? Отметьте мой пост как решение!

Горжусь тем, что являюсь суперпользователем!

У моего коллеги такая же проблема. Есть обновления?

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

Попробуйте следующее обходное решение:

2. Закройте все офисные приложения

3. Перейдите по адресу: C: Program Files (x86) Microsoft Office root VFS ProgramFilesX86 Microsoft Analysis Services AS OLEDB 140 и переименуйте adal.dll в adal_orig.dll (если у вас 64-разрядная версия Excel, используйте версию из Program Files/ProgramFilesX64)

4. Скопируйте adal.dll отсюда: C: Program Files (x86) Microsoft Analysis Services AS OLEDB 130 в: C: Program Files (x86) Microsoft Office root VFS ProgramFilesX86 Microsoft Analysis Services AS OLEDB 140 (если у вас 64-разрядная версия Excel, используйте версию из Program Files/ProgramFilesX64)

5. Откройте файл odc и посмотрите, работает ли он у вас.

В любой момент вы можете вернуться к исходный файл adal.

У меня работает этап установки

Это не сработало. Возможно, потому что версия AS OLEDB в моей системе только 110.

C: Program Files Microsoft Office 15 root vfs ProgramFilesX86 Microsoft Analysis Services AS OLEDB 110

Это Похоже, вы связали внутреннюю вики-страницу для пользователей только для клиентов Microsoft. Можете ли вы опубликовать обходной путь на этом форуме или в общедоступной вики-странице?

Попробуйте следующее обходное решение:

2. Закройте все офисные приложения

3. Перейдите по адресу: C: Program Files (x86) Microsoft Office root VFS ProgramFilesX86 Microsoft Analysis Services AS OLEDB 140 и переименуйте adal.dll в adal_orig. dll (если у вас 64-разрядная версия Excel, используйте версию из Program Files/ProgramFilesX64)

5. Откройте файл odc и посмотрите, работает ли он у вас.

В любой момент вы можете вернуться к исходный файл адала.

Вы гений. Престижность.

Вы не можете понять, насколько вы мне помогли. @jllelis

На самом деле, я уже некоторое время хорошо пользуюсь Excel. Мне нравится использовать запросы и отношения данных для расчета прибыли от мега-данных иностранной валюты. Так случилось, что мой Excel Office 2016 был удален ИТ-отделом после года работы. Другой ИТ-персонал пришел и установил Office 2016 Professional Plus, и я был рад вернуться к своим данным и выполнить свои вычисления как обычно.

-Удаление и замена надстроек

-Запуск последнего офиса обновления

Я перепробовал множество вариантов, но ни один из них не подошел.

Я все сделал в этот пост, и я все еще получаю эту проблему !!

Мы только что выпустили улучшение для« Анализировать в Excel »setup/ опыт загрузки

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

На прошлой неделе я только начал получать эту ошибку. Я загрузил надстройку, удалил ее и переустановил безрезультатно. Есть ли способ принудительно установить 64-битную совместимость во время установки?

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

Попробуйте изменить строку подключения в строке подключения, обновив Provider = MSOLAP.7 поставщику = MSOLAP.8. Перейдите на вкладку ДАННЫЕ >> Запросы и подключения >> щелкните правой кнопкой мыши имя подключения >> Свойства >> вкладка Определение >> Строка подключения. Не думаю, что это будет окончательное решение, но, возможно, пока оно поможет.

«Произошла следующая системная ошибка: (Операция успешно завершена.)»

Привет, Эндрю, у меня такая же проблема. Я ответил об этом, потому что коллега не сталкивается со вторым вопросом. У меня есть запрос в службу поддержки, и я жду ответа, я поделюсь, когда получу от них решение. Эту «проблему с Azure» я решил раньше, установив Power BI Publisher для Excel и войдя в свою учетную запись в нижней части профиля (панель инструментов Power BI), но теперь это не решает! Тем не менее, вы можете попробовать это сделать, а также убедиться, что ваша учетная запись вошла в Excel, поскольку это, похоже, проблема аутентификации.

У меня установлен PowerBI Publisher, и я вошел в свой профиль, и он не работает.

Но, как говорится - лучше один раз увидеть, чем сто раз услышать. Поэтому испытаем сразу на практике, что нам может дать эта надстройка. В качестве знакомства с некоторым функционалом и работой с Power Query возьмем печально известный многим отчет – Оборотно-Сальдовая ведомость. Выглядит он примерно так:

А нам надо все это безобразие привести в такой вид, чтобы можно было создать сводную таблицу и уже в этой сводной таблице крутить и вертеть данные в любом ракурсе. Т.е. на выходе надо сделать что-то вроде этого:

Результат - плоская таблица

А из этого вот такая сводная таблица:

Проблемы видны сразу: многоуровневая шапка, несколько столбцов значений. Напрямую из такой таблицы не построить нормальную сводную, в которой можно нормально просматривать и сравнивать данные. Поэтому в любом случае необходимо эту таблицу преобразовывать таким образом, как показано на втором скрине "Результат - плоская таблица".
Если делать подобное вручную – то уйдет как минимум час, при этом время будет напрямую зависеть от кол-ва строк. Если же делать это через Power Query – это займет максимум полчаса независимо от количества строк. И то только в первый раз – познакомившись с надстройкой поближе и 10 минут будет за глаза для данной операции. Я постараюсь пошагово расписать весь процесс с иллюстрациями своих действий. Чтобы удобно было повторять действия из статьи можно скачать файл :

Пример работы с Power Query (62,9 KiB, 2 185 скачиваний)

В приложенном есть лист, еще не преобразованной ОСВ. Он называется "Не обработанная ОСВ". Так же есть лист с уже подготовленной ОСВ(лист "Оборотно-сальдовая ведомость"), а так же с готовым запросом Power Query, созданной на его основе умной таблицей(лист "Лист2") и сводной таблицей(лист "Лист3").

Итак, приступим. Сначала нам надо подготовить саму оборотно-сальдовую ведомость(в дальнейшем я буду называть её кратко – ОСВ). Для этого выделяем ОСВ от заголовков и до конца, без строки итогов:

Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:

Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать -Из таблицы

Появится окно редактора запросов:

Теперь производим преобразования данных, для приведения этой таблицы в нормальную, так называемую "плоскую". Здесь есть два варианта - какой из них использовать выбирать вам:
Вариант 1 (проще в понимании логики, но менее универсальный для таблиц с добавляющимися столбцами)

Вкладка Главная –Использовать первую строку в качестве заголовков(на рисунке выше выделено красным). Запоминаем где у нас Сальдо на начало периода, где Обороты за период, где Сальдо на конец периода. Это еще пригодится. Так же это можно сделать с вкладки Преобразование -Таблица -Использовать первую строку в качестве заголовков
Еще раз повышаем заголовки(Главная –Использовать первую строку в качестве заголовков) и теперь у нас вместо Оборотов за период и Сальдо только Дебет и Кредит. Поэтому вспоминаем изначальную структуру и к каждому Дебет и Кредит дописываем через нижнее подчеркивание Тип операции: Сальдо на начало периода, Обороты за период и Сальдо на конец периода:
Теперь выделяем первые 2 столбца(Номер и Наименование), переходим на вкладку Преобразование -Отменить свертывание других столбцов:

Здесь не мешает пояснить, что делает вообще означает данная команда. Несмотря на странное название, делает она очень нужную вещь: она транспонирует все столбцы, отличные от выделенных, преобразуя их в два столбца: в одном будут записаны имена заголовков, в другом значение каждой строки для этого заголовка:
Теперь осталось только отделить Типы от Дебета и Кредита. Для этого выделяем столбец Атрибут -правая кнопка мыши -Разделить столбец -По разделителю. В появившемся окне в выпадающем списке "Выберите или введите разделитель" выбираем Пользовательский и записываем в появившееся поле нижнее подчеркивание:
Переименовываем столбцы, чтобы получилось что-то вроде этого(главное, чтобы было понятно что в каком столбце):
Теперь выделяем столбец с суммой, переходим на вкладку Преобразование и находим там пункт Тип данных. Указываем тип Десятичное число:

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

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

На этом преобразования завершены, переходим на вкладку Главная -Закрыть и загрузить. В исходной книге будет создан новый лист, на котором будет создана умная таблица из преобразованных данных. Теперь на основании этой умной таблицы мы можем создать сводную таблицу(выделяем любую ячейку внутри таблицы -вкладка Вставка -Сводная таблица) или производить другие действия для анализа данных.
При этом очень важный нюанс: не придется делать подобные преобразования каждый раз при изменении данных – если мы внесем изменения в исходную таблицу ОСВ, то для получения актуальных данных и преобразования достаточно будет обновить результирующую таблицу(выделять любую ячейку результирующей умной таблицы -вкладка Конструктор -Обновить). Даже если добавить еще пару столбцов с Дебетом и Кредитом(здесь это не уместно, но в других отчетах с подобной структурой это вполне вероятно) и обновить - то эти столбцы так же будут обработаны и включены в итоговую таблицу.
Если же надо применить все тоже самое для другой таблицы – то выделяем любую ячейку результирующей умной таблицы -вкладка Запрос -Изменить. Находим самый первый шаг в Примененных шагах(как правило он называется Источник – выделяем и в строке формул меняем имя таблицы на имя таблицы нужной таблицы):

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

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