Вопрос или проблема
Я помогаю коллеге исправить таблицу в Excel с такой же проблемой, как указано в этом сообщении на ozgrid.
Проблема: Связная таблица с 31 вычисляемым полем. Некоторые вычисляемые поля являются вычислениями исходных данных, в то время как другие – это вычисления результатов других вычисляемых полей (это проблемная часть).
Пример и единственное решение, которое у меня есть на данный момент:
Общие продажи $ за последний год ='Продажи по цепочке $ за последний год'+'Продажи с начала недели $ за последний год'
Общие продажи $ за предыдущий год ='Продажи по цепочке $ за предыдущий год'+'Продажи с начала недели $ за предыдущий год'
Изменение продаж $ ='Общие продажи $ за последний год'-'Общие продажи $ за предыдущий год'
Общие продажи $ за последний год и Общие продажи $ за предыдущий год используют значения источника данных, Изменение продаж $ использует результаты Общие продажи $ за последний год и Общие продажи $ за предыдущий год для вычисления изменения. При изменении источника данных (например, изменении значений, заголовков столбцов, вставке столбцов и т. д.) Изменение продаж $ будет давать ошибку #NAME? при обновлении. Если я изменю Изменение продаж $ на формулу с использованием ссылок на источник данных (т. е. =('Продажи по цепочке $ за последний год'+'Продажи с начала недели $ за последний год')-('Продажи по цепочке $ за предыдущий год'+'Продажи с начала недели $ за предыдущий год')), это работает, но таких формул 20-30, которые нужно изменить.
Существует ли способ использовать результаты вычисляемых полей в вычисляемом поле и все еще корректно обновлять это в Excel 2010?
Перейдите в параметры сводной таблицы, затем отметьте поле, которое говорит «Показать для значений ошибок».
Я столкнулся с этой же проблемой и нашел обходное решение.
- Сохраните копию вашего рабочего документа перед тем, как продолжить.
- Выберите вашу сводную таблицу и экспортируйте вычисляемые поля: Инструменты сводной таблицы → Анализ → Поля, элементы и наборы → Список формул. Это создаст список всех ваших формул на новом листе. Вам может потребоваться скопировать это в отдельный документ для удобства обращения позже.
- Создайте новый столбец в исходных данных и обновите свою сводную таблицу, она сломается.
- Просмотрите каждую вашу формулу по одной, чтобы увидеть, какая из них вызывает ошибку (Инструменты сводной таблицы → Анализ → Поля, элементы и наборы → Вычисляемое поле → нажмите на выпадающее меню на строке Имя).
- Вы обнаружите, что по крайней мере в одной формуле ссылка изменилась на
#NAME, где она ищет данные, которые по какой-то причине больше не может найти. Нажмите Изменить, чтобы редактировать эти формулы. - Найдите ту же формулу в списке формул, который вы только что экспортировали. Скопируйте и вставьте формулу в строку формул. Это исправит сломанную ссылку.
- Повторите шаги 5 и 6 для каждой из сломанных формул.
- Вам придется повторять шаги 1-7 каждый раз, когда вы добавляете новый столбец в исходные данные. Если у вас есть несколько столбцов, добавьте их все сразу, чтобы сделать это только один раз.
Я буду честен: я не знаю, почему Excel так ломается, но это по крайней мере исправляет ситуацию.
Ответ или решение
Ошибка #NAME? в вычисляемых полях при обновлении сводной таблицы
При работе со сводными таблицами в Excel, особенно при использовании вычисляемых полей, пользователи могут столкнуться с ошибкой #NAME? при обновлении данных. Эта ошибка чаще всего возникает, когда вычисляемые поля ссылаются на другие вычисляемые поля, что может приводить к нестабильности в формуле при изменении исходных данных. Ниже представлен подробный анализ проблемы и возможные решения, которые помогут вам эффективно справиться с данной ситуацией.
Проблема
Ваша сводная таблица содержит 31 вычисляемое поле, из которых некоторые являются простыми вычислениями исходных данных, а другие — сложными, основанными на ранее вычисленных полях. Как показано в вашем примере:
- Total Sales $ TY – вычисляется как сумма ‘Chain Sales Dlrs TY’ и ‘WTD Sales TY’
- Total LY Sales $ – аналогично для предыдущего года
- Chg Sales $ – вычисляется как разница между двумя предыдущими полями
Когда вы вносите изменения в исходные данные (например, изменяете значения, наименования колонок или добавляете новые колонки), сводная таблица может не суметь найти ссылки на вычисляемые поля, что приводит к возникновению ошибки #NAME?. Эта проблема особенно актуальна для полей, которые ссылаются на результаты других вычисляемых полей.
Возможные решения
-
Проверка параметров сводной таблицы:
- Перейдите в режим редактирования сводной таблицы.
- Откройте Параметры сводной таблицы и установите флажок Показать вместо значений ошибок. Это позволит скрыть ошибку #NAME?, однако не решает основную проблему.
-
Экспорт формул вычисляемых полей:
- Создайте резервную копию вашего рабочего документа перед внесением каких-либо изменений.
- Выделите сводную таблицу и в меню Параметры сводной таблицы выберите Анализ → Поля, элементы и наборы → Список формул. Это создаст новый лист с полным списком ваших формул.
-
Обновление сводной таблицы и проверка формул:
- Добавьте новые колонки в исходные данные, после чего ваша сводная таблица может сломаться. Перейдите к формуле, которая выдает ошибку #NAME?, и внесите необходимые изменения.
- Перепроверяйте каждую формулу по одной, используя ранее экспортированный список. Возможно, часть формул станет недоступной из-за изменения в именах полей или ссылок.
-
Корректировка формул:
- Если вы обнаружили, что какая-либо формула ссылается на поле, которое больше не существует (выдает #NAME?), исправьте это, выбрав опцию Изменить и вставьте исправленную формулу из списка.
-
Регулярные проверки:
- При внесении изменений в исходные данные старайтесь вносить их одновременно, чтобы избежать необходимости многократного повторения вышеперечисленных шагов.
Заключение
Причина возникновения ошибки #NAME? в сводных таблицах с вычисляемыми полями часто связана с изменениями в исходных данных или неправильными ссылками в формулах. Понимание того, как Excel обрабатывает вычисляемые поля и ссылки на них, поможет вам избежать подобных проблем. Следуя предложенным шагам, вы сможете минимизировать время на устранение ошибок и повысить стабильность работы сводных таблиц в ваших проектах.