Вопрос или проблема
В Excel у меня была таблица, которая работала нормально. Я скопировал это на разные вкладки. Теперь, когда я суммирую группу ячеек, я получаю ноль в качестве суммы. Если я выделяю ячейки, функция суммы в нижнем колонтитуле работает корректно. Если у меня уже работает сумма, когда я затем кликаю на формулу в строке ввода на панели инструментов и затем нажимаю на формулу, чтобы увидеть, какие ячейки выделены, когда я нажимаю Enter, сумма превращается в ноль?
Это была работающая формула
потом, когда я нажимаю на формулу и нажимаю Enter, сумма становится нулевой
Ваша проблема в том, что существует как минимум одна круговая ссылка.
Когда вы создаете круговую ссылку, Excel выдаст сообщение об ошибке и, если сможет, рассчитает результат и поместит его в ячейку. Однако любое последующее вычисление таблицы приведет к тому, что Excel поместит 0 в затронутую ячейку/ячеек.
Именно это и происходит. Это может быть очень неявно, особенно с дальнейшими эффектами от результатов SPILL. (Под “дальнейшими” я имею в виду “не от самого вычисления, а от его самого существования”… Например, попробуйте ограничить количество элементов в диапазоне SPILL, показывая пустые ячейки в отображаемом столбце, а затем используйте LARGE(), чтобы отобразить на одну меньше из результатов SPILL. Это работает нормально, отображает необходимые результаты, пока не произойдет перерасчет, когда он начинает отображать нули в результатах SPILL.)
Но… вам не обязательно верить мне на слово. Посмотрите на самый ЛЕВЫЙ край вашей статусной строки (“нижнего колонтитула”) и вы увидите “Круговые ссылки: H125” на обоих ваших (не совсем полезных, идентичных) изображениях. Мы не можем увидеть ничего в области H125, ни, конечно, формул в любом месте, которые зависят на каком-то уровне от этой ячейки, но можно предположить, что, если вы исправите эту ситуацию, остальное станет на свои места. Или по крайней мере сможет показать следующий дефект в какой-то степени цепочки дефектов. (Надеюсь, ноль больше.)
(Не так много можно добавить без дополнительной информации. Наверное, много, поскольку нельзя заранее сказать, что неизвестный регион влияет на что-то, не имея возможности посмотреть на ячейку, на которую он ведет.)
Ответ или решение
Проблема с нулевым результатом в Excel: Как её решить
При работе с таблицами Excel пользователи иногда сталкиваются с неожиданной ситуацией, когда сумма ячеек, которые они пытаются просуммировать, показывает ноль. Это может быть связано с несколькими причинами, но наиболее распространённой является наличие циклических ссылок. Давайте рассмотрим эту проблему подробнее и предложим возможные решения.
1. Что такое циклическая ссылка?
Циклическая ссылка в Excel возникает, когда формула ссылается на себя непосредственно или косвенно. Это может произойти, если вы копируете формулы между листами или используете динамические массивы. При наличии циклической ссылки Excel не может правильно рассчитать значение, так как он "зацикливаться", пытаясь разрешить формулу. В результате ячейка может отображать ноль.
2. Как обнаружить и устранить циклические ссылки
-
Проверка состояния в строке состояния: Ваша строка состояния (footer) может указать на наличие циклических ссылок, например «Циклические ссылки: H125». Ищите такие предупреждения и отмечайте, в каких ячейках они возникают.
-
Перейдите к указанной ячейке: Используйте указанный адрес ячейки (например, H125), чтобы проверить формулы, содержащиеся в этой ячейке. Если формула ссылается на саму себя или на другую ячейку, которая в свою очередь ссылается на неё, это и есть ваша проблема.
-
Перемещение формул: Если вы заметили, что вы используете формулы, которые могут привести к циклическим ссылкам, попробуйте переместить их в другую ячейку или изменить структуру формул так, чтобы они больше не ссылались на себя.
3. Проверка диапазонов и формул
При работе с динамическими массивами Excel может автоматически менять диапазоны при копировании формул. Убедитесь, что ваши ссылки на ячейки корректны и не накладывают друг на друга.
-
Изменение ссылок на ячейки: Проверьте, не меняются ли ссылки на ячейки при использовании в разных листах. Убедитесь, что используемые вами ссылки являются абсолютными (например, $A$1) или относительными, в зависимости от вашего замысла.
-
Устранение пробелов и некорректных символов: Иногда ноль может возникнуть из-за пробелов или ненормативных символов в ячейках. Убедитесь, что все значения числовые и не содержат скрытых символов.
4. Повторный расчет
После внесения исправлений в формулы и удаление циклических ссылок, попробуйте выполнить повторный расчет таблицы:
- Функция пересчёта: Вы можете принудительно пересчитать все формулы в Excel, нажав
Ctrl+Alt+F9.
5. Заключение
Проблема с получением нуля в результате суммирования в Excel часто связана с циклическими ссылками, которые мешают нормальному расчёту. Следуя описанным выше шагам, вы сможете идентифицировать и устранить эти ссылки, гарантируя, что расчеты в вашей таблице будут выполняться корректно.
Если после проверки всех указанных аспектов проблема остается, рекомендуется обратиться к документации Microsoft или профессиональному консультанту, так как могут быть дополнительные обстоятельства, требующие индивидуального анализа.