Это действительно здорово, когда вы, наконец, настроили источники данных и сформировали их так, как хотите. Надеемся, что при обновлении данных из внешнего источника операция пройдет без проблем. Но это не всегда так. Изменения в потоке данных на протяжении всего процесса могут вызвать проблемы, которые вызовут ошибки при попытке обновить данные. Некоторые ошибки легко исправить, некоторые — временные, а некоторые — трудно диагностировать. Ниже приведен набор стратегий, которые вы можете использовать, чтобы справляться с ошибками, которые встречаются на вашем пути.
Ошибки двух типов
При обновлении данных могут возникать ошибки двух типов.
Местное Если в книге Excel возникает ошибка, то по крайней мере ваши усилия по устранению неполадок ограничены и более управляемы. Возможно, обновленные данные вызвали ошибку с функцией или данные создали недопустимое условие в раскрывающемся списке. Эти ошибки доставляют много хлопот, но их довольно легко отследить, идентифицировать и исправить. В Excel также улучшена обработка ошибок благодаря более понятным сообщениям и контекстно-зависимым ссылкам на целевые разделы справки, которые помогут вам разобраться и устранить проблему.
"Удаленный доступ" Однако ошибка, которая приходит из удаленного внешнего источника данных, — это совсем другое дело. Что-то произошло в системе, которая может находиться через дорогу, на другом конце света или в облаке. Для таких типов ошибок требуются разные подходы. К распространенным ошибкам удаленного подключения относятся:
- Не удалось подключиться к службе или ресурсу. Проверьте соединение.
- Не удалось найти файл, к которому вы пытаетесь получить доступ.
- Этот сервер не отвечает, возможно на нем проводится обслуживание.
- Это содержимое недоступно. Возможно, она была удалена или временно недоступна.
- Подождите, пожалуйста... Выполняется загрузка данных.
Исследование ошибок
Ниже приведены несколько рекомендаций, которые помогут вам справиться с ошибками, которые могут возникнуть.
Поиск и сохранение определенной ошибки Сначала изучите область "Запросы & подключения " (выберите "Запросы данных>" & "Подключения", выберите подключение, затем откройте всплывающий элемент). Узнайте, какие ошибки доступа к данным возникли, и запишите любые дополнительные сведения. Затем откройте запрос, чтобы увидеть ошибки на каждом шаге запроса. Все ошибки отображаются на желтом фоне для удобства идентификации. Запишите сведения об ошибке или сделайте снимок экрана, даже если вы не до конца понимаете его. Коллега, администратор или сотрудник службы поддержки в вашей организации может помочь вам понять, что произошло, и предложить решение. Дополнительные сведения см. в разделе Работа с ошибками в Power Query.
Получение справки Выполните поиск на сайте справки и обучения Office . Здесь содержится не только обширное содержимое справки, но и информация по устранению неполадок. Дополнительные сведения см. в разделе "Исправления и временные решения недавних проблем в Excel для Windows".
Использование технического сообщества Используйте сайты Microsoft Community для поиска обсуждений, связанных с вашей проблемой. Весьма вероятно, что вы не первый, кто столкнулся с проблемой, другие имеют с ней дело и, возможно, даже нашли решение. Дополнительные сведения см. в сообществах Microsoft Excel и Office Answers Community.
Поиск в Интернете Используйте предпочитаемую поисковую систему для поиска других сайтов в Интернете, которые могут предоставить нужные обсуждения или подсказки. Это может занять много времени, но это способ забросить более широкую сеть, чтобы найти ответы на особенно острые вопросы.
Обращение в службу поддержки Office К этому моменту вы, вероятно, понимаете проблему гораздо лучше. Это поможет вам сосредоточиться на разговоре и свести к минимуму время, затрачиваемое на взаимодействие со службой службы поддержки Майкрософт. Дополнительные сведения см . в разделе Служба поддержки Microsoft 365 и Office.
Общие сведения об ошибках источника данных
Хотя вы не сможете решить проблему, вы можете точно выяснить, в чем заключается проблема, чтобы помочь другим понять ситуацию и решить ее за вас.
Проблемы со службами и серверами Периодические ошибки сети и связи являются вероятным виновником. Самое лучшее, что вы можете сделать, это подождать и попробовать еще раз. Иногда проблема просто исчезает.
Изменения местоположения или доступности База данных или файл были перемещены, повреждены, переведены в автономный режим для обслуживания либо база данных завершилась сбоем. Возможно повреждение дисковых устройств и потеря файлов. Дополнительные сведения см. в статье "Восстановление потерянных файлов в Windows 10".
Изменения в проверке подлинности и конфиденциальности Может внезапно случиться так, что разрешение перестало работать или в параметры конфиденциальности было внесено изменение. Оба события могут препятствовать доступу к внешнему источнику данных. Узнайте у администратора или администратора внешнего источника данных, что изменилось. Дополнительные сведения см. в разделах Управление параметрами и разрешениями источника данных и Установка уровней конфиденциальности.
Открытые или заблокированные файлы Если открыт текст, CSV-файл или книга, любые изменения в файл не включаются в обновление, пока файл не будет сохранен. Кроме того, если файл открыт, он может быть заблокирован, и пока файл не будет закрыт. Это может произойти, если другой пользователь использует версию Excel без подписки. Попросите их закрыть файл или провести его проверку. Дополнительные сведения см. в статье Разблокировка файла, заблокированного для редактирования.
Изменения схем на сервере Кто-то изменяет имя таблицы, столбца или тип данных. Это почти никогда не разумно, может иметь огромное влияние, и особенно опасно для баз данных. Остается надеяться, что команда управления базами данных установила надлежащие средства контроля, чтобы предотвратить это, но промахи случаются.
Блокировка ошибок при свертывании запросов Power Query старается по возможности повысить производительность. Часто лучше выполнить запрос к базе данных на сервере, чтобы воспользоваться преимуществами более высокой производительности и емкости. Этот процесс называется свертыванием запросов. Однако Power Query блокирует запрос, если существует вероятность компрометации данных. Например, определено слияние между таблицей книги и таблицей SQL Server. Для данных книги задано значение "Конфиденциальность", а для данных SQL Server установлено значение "Организационные". Так как конфиденциальность является более строгой, чем организационная, Power Query блокирует обмен информацией между источниками данных. Свертывание запросов происходит в фоновом режиме, поэтому ошибка блокировки может удивить вас. Дополнительные сведения см. в разделах Основы свертывания запросов, Свертывание запросов и свертывание с помощью диагностики запросов.
Общие сведения об ошибках Power Query
Часто с помощью Power Query можно точно выяснить, в чем заключается проблема, и устранить ее самостоятельно.
Переименованные таблицы и столбцы Изменение исходных имен таблиц и столбцов или заголовков столбцов почти наверняка вызовет проблемы при обновлении данных. Запросы используют имена таблиц и столбцов для формирования данных почти на каждом этапе. Не изменяйте и не удаляйте исходные имена таблиц и столбцов, если только вы не хотите привести их в соответствие с источником данных.
Изменения типов данных Иногда изменение типа данных может привести к ошибкам или непредвиденным результатам, особенно в функциях, для которых может потребоваться определенный тип данных в аргументах. Например, замена текстового типа данных в числовой функции или попытка произвести вычисление на нечисловом типе данных. Дополнительные сведения см. в статье Добавление или изменение типов данных.
Ошибки на уровне ячеек Ошибки таких типов не препятствуют загрузке запроса, но отображают ошибку в ячейке. Чтобы просмотреть сообщение, выберите пробел в ячейке таблицы, содержащей ошибку. Ошибки можно удалить, заменить или просто сохранить. Примеры ошибок ячеек:
- Преобразование Вы пытаетесь преобразовать ячейку, содержащую НС, в целое число.
- Математические При попытке умножить текстовое значение на числовое.
- Операторы объединения Вы пытаетесь объединить строки, но одна из них является числовой.
Безопасно экспериментируйте и выполняйте итерации Если вы не уверены, что преобразование может оказать негативное влияние, скопируйте запрос, проверьте изменения и выполните итерацию по вариантам команды Power Query. Если команда не работает, просто удалите созданный шаг и повторите попытку. Чтобы быстро создать образец данных с той же схемой и структурой, создайте таблицу Excel из нескольких столбцов и строк, а затем импортируйте ее (выберите данные>из таблицы или диапазона). Дополнительные сведения см. в статье Создание таблицы и импорт из таблицы Excel.
Преобразуйте с умом
Вы можете почувствовать себя ребенком в кондитерской, когда поймете, что можно делать с данными в редакторе Power Query. Но не поддавайтесь искушению съесть все конфеты. Следует избегать преобразований, которые могут непреднамеренно вызвать ошибки обновления. Некоторые операции просты, например перемещение столбцов в другую позицию в таблице, и не должны приводить к ошибкам обновления в дальнейшем, так как Power Query отслеживает столбцы по их имени.
Другие операции могут привести к ошибкам обновления. Одно общее эмпирическое правило может стать вашим путеводным светом. Старайтесь не вносить существенных изменений в исходные столбцы. Чтобы перестраховаться, скопируйте исходный столбец с помощью команды ("Добавить столбец", "Настраиваемый столбец", "Дублировать столбец" и т. д.), а затем внесите изменения в скопированную версию исходного столбца. Ниже приведены операции, которые иногда могут приводить к ошибкам обновления, а также некоторые рекомендации для повышения удобства работы.
| Операция | Рекомендация |
|---|---|
| Фильтрация | Повышайте эффективность за счет фильтрации данных как можно раньше в запросе и удаления ненужных данных, чтобы сократить ненужную обработку. Кроме того, с помощью автофильтра можно искать или выделять определенные значения и воспользоваться преимуществами фильтры конкретных типов, доступные в столбцах даты, даты и времени (например, месяц, неделя, день). |
| Типы данных и заголовки столбцов | Power Query автоматически добавляет в запрос два шага сразу после первого шага источника: "Повышенные заголовки", который продвигает первую строку таблицы в качестве заголовка столбца, и "Измененный тип", который преобразует значения из типа данных "любой" в тип данных на основе проверки значений в каждом столбце. Это полезное удобство, но иногда вы захотите явно управлять этим поведением, чтобы предотвратить случайные ошибки обновления. Дополнительные сведения см. в статьях Добавление или изменение типов данных и Повышение или понижение уровня заголовков строк и столбцов. |
| Переименование столбца | Не переименовывайте исходные столбцы. Используйте команду "Переименовать " для столбцов, добавленных другими командами или действиями. Дополнительные сведения см. в статье Переименование столбца. |
| Разделить столбец | Разделение копий исходного столбца, а не исходного столбца. Дополнительные сведения см. в статье Разделение столбца текста. |
| Объединение столбцов | Объединяйте копии исходных, а не исходных столбцов. Дополнительные сведения см. в статье Объединение столбцов. |
| Удаление столбца | Если вам нужно сохранить небольшое количество столбцов, воспользуйтесь командой "Выбрать столбец ", чтобы сохранить нужные столбцы. Подумайте о разнице между удалением одного столбца и удалением других столбцов. Если вы решите удалить другие столбцы и обновить данные, новые столбцы, добавленные в источник данных с момента последнего обновления, могут остаться незамеченными, так как они будут считаться другими столбцами при повторном выполнении шага удаления столбца в запросе. Такая ситуация не возникает, если вы явно удалите столбец. Совет Команды скрытия столбца (как в Excel) нет. Однако если столбцов много и вы хотите скрыть многие из них, чтобы сконцентрировать работу, можно сделать следующее: удалите столбцы, запомните созданный шаг, а затем удалите его, прежде чем загружать запрос обратно на лист. Дополнительные сведения см. в статье Удаление столбцов. |
| Замена значения | При замене значения источник данных не изменяется. Вместо этого вы вносите изменения в значения в запросе. При следующем обновлении данных искомое значение может немного измениться или отсутствовать, поэтому команда "Заменить " может работать не так, как предполагалось. Дополнительные сведения см. в статье Замена значений. |
| Pivot и Unpivot | При использовании команды "Сводный столбец " может возникнуть ошибка, если вы поворачиваете столбец. Не агрегатируйте значения, но возвращаете более одного значения. Такая ситуация может возникнуть после операции обновления, которая приводит к непредвиденному изменению данных. Используйте команду "Отменить сводку других столбцов ", если известны не все столбцы и вы хотите, чтобы новые столбцы, добавленные во время операции обновления, также были отменены. Используйте команду "Отменить сводку только выделенных столбцов", если вы не знаете число столбцов в источнике данных и хотите убедиться, что выбранные столбцы не сведены после операции обновления. Дополнительные сведения см. в статье "Столбцы со сводкой" и "Отменить разбивку по столбцам". |
Опережая события
Предотвращение возникновения ошибок Если внешним источником данных управляет другая группа в вашей организации, они должны знать о вашей зависимости от них и избегать изменений в своих системах, которые могут вызвать проблемы в дальнейшем. Ведение записей о влиянии на данные, отчеты, диаграммы и другие артефакты, зависящие от данных. Настройте линии связи, чтобы убедиться, что они понимают последствия, и принять необходимые меры для обеспечения бесперебойной работы. Найдите способы создания элементов управления, которые минимизируют ненужные изменения и предвосхищают последствия необходимых изменений. По общему признанию, это легко сказать, а иногда и трудно сделать.
Перспективность с помощью параметров запроса Используйте параметры запроса, чтобы смягчить изменения, например, в расположении данных. Вы можете создать параметр запроса, чтобы подставить новое расположение, например путь к папке, имя файла или URL-адрес. Существуют дополнительные способы использования параметров запроса для устранения проблем. Дополнительные сведения см. в статье Создание запроса с параметрами.