Excel пропустил число: почему СУММ видит 280 вместо 430 и когда исправление портит данные

В четырёх ячейках видны 120, 90, 150 и 70. Диапазон указан правильно, но сумма равна 280. Для человека все четыре записи выглядят числами. Для Excel одна из них может быть текстом — и при суммировании диапазона она не участвует в расчёте.

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

Как выполнена проверка

Проверка проведена 14 ноября 2026 года в настольном Microsoft Excel для Windows, версия 16.0.20527.20150, русский интерфейс, десятичный разделитель — запятая. Учебный XLSX подготовлен программно. Перед открытием из него удалены сохранённые результаты всех 52 формул — элементы <v> в ячейках с формулами; включён пересчёт при открытии fullCalcOnLoad.

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

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

Один диапазон, два типа значения

На первом листе в B2 находится число 120, в B3 — 90, в B4 — текст «150», в B5 — число 70. Формула =SUM(B2:B5) вернула 280. Проверка =ISNUMBER(B4) показала ЛОЖЬ, а =ISTEXT(B4) — ИСТИНА. После преобразования текста через VALUE получено число 150, а сумма с преобразованным значением составила 430.

B4 при этом входит в диапазон B2: B5. Причина не в потерянной строке: SUM складывает 120 + 90 + 70, игнорируя текстовую ячейку. Такое поведение диапазона описано в справке SUM [1].

Чтобы повторить пример вручную, на отдельном листе можно ввести в B4 '150: обычный апостроф перед числом заставляет Excel воспринимать ввод как текст. В самой ячейке апостроф обычно не отображается. В нашем файле текстовый тип был задан при создании XLSX; сам ручной ввод апострофа не проверялся.

В формулах статьи используются английские имена и запятые между аргументами. В русской версии Excel имена и разделитель аргументов могут отличаться:

Например, русские записи проверки и суммы: =ЕЧИСЛО(B4) и =СУММ(B2:B5). Несколько аргументов при русских региональных настройках часто разделяются точкой с запятой. Если формула не принимается, сначала проверьте её синтаксис, а не меняйте данные.

Выравнивание ячейки не доказывает тип: его можно изменить вручную. Зелёный треугольник зависит от настройки проверки ошибок. ISNUMBER проверяет непосредственно значение без преобразования. В нашем опыте =ISNUMBER(«150») вернула ЛОЖЬ: цифры внутри кавычек остались текстом. Это согласуется со справкой функций IS [2].

Формула в ячейке ещё не означает число

На втором листе базой каждого расчёта служили одни и те же числа 120, 90 и 70. К ним через SUM добавлялась ссылка на проверяемую ячейку. Получены следующие результаты:

="150″ — настоящая формула, но её результатом является текст. TEXT тоже возвращает текст: она подходит, например, для включения красивой суммы в подпись. Microsoft рекомендует хранить исходное число отдельно и строить дальнейшие вычисления по нему, а не по результату TEXT. См. документацию TEXT [3].

Числовой формат 00000 работает иначе: число 150 остаётся числом и лишь отображается как 00150. Тип и результат SUM это подтверждают. Форматирование не превращает число в самостоятельный текстовый код из пяти символов.

Есть ещё одно различие. У нас =SUM(«150») вернула 150, а =SUM(B6) при текстовом «150» в B6 — 0. Поэтому утверждение «SUM никогда не складывает текстовые числа» слишком широкое. Строка, переданная прямо как аргумент, и ссылка на текстовую ячейку обрабатываются по-разному. Для исходной проблемы важно правило о текстовых значениях в ссылочных ячейках диапазона.

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

Преобразование: сначала рядом с исходником

Изменение числового формата — не то же самое, что изменение типа уже сохранённого значения. Нельзя считать ячейку исправленной только потому, что в меню выбрано «Числовой»: повторно проверьте её через ISNUMBER. В нашей проверке различие типов исследовалось на заранее подготовленных ячейках; переключение формата через меню не выполнялось.

Для одной случайной суммы можно повторно ввести число без апострофа. Для большого столбца безопаснее сначала получить числовые результаты рядом с исходными строками. Например, =VALUE(B4) в нашем опыте вернула числовое 150. Microsoft описывает такой подход в инструкции преобразования чисел, сохранённых текстом [4]. При наличии предупреждения в меню Excel также может быть команда «Преобразовать в число».

Перед заменой исходника проверьте ошибки преобразования, количество обработанных строк и новую сумму. Не оборачивайте всё сразу в IFERROR(…, 0): ошибка станет незаметным нулём. Итог может выглядеть правдоподобно, хотя часть исходных данных так и не разобрана.

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

Пробелы после импорта: одинаковый вид, разные символы

У обычного пробела код 32, у неразрывного — 160. На экране разницы почти не видно. Для проверки в книге подготовлены строка «150 «с обычными пробелами по краям и строка, полученная как =CHAR(160)&"150».

TRIM удаляет лишние обычные пробелы, но не неразрывный пробел 160. Это совпадает с ограничением из справки TRIM [5]. Кроме того, очищенная строка остаётся строкой: удаление лишних символов и получение числового типа — разные действия.

На листе «Пробелы» для обычных пробелов в B5 проверена цепочка =VALUE(TRIM(B5)). Для строки с символом 160 в B6 — =VALUE(SUBSTITUTE(B6,CHAR(160),"")). Вторая формула сначала удаляет конкретный символ, затем преобразует оставшееся значение. В обоих примерах итог равен 150.

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

Другой источник неоднозначности — разделители. Строки 2.500,27 и 2,500.27 используют разные соглашения. Для первой мы проверили =NUMBERVALUE(«2.500,27″,»,",".") и получили 2500,27: десятичная запятая и групповая точка заданы явно. Если их не задать, функция использует текущие настройки. Поэтому случайная замена точки на запятую во всём столбце опасна.

В отдельной проверке NUMBERVALUE для пустой строки вернула 0. То же поведение описано в справке NUMBERVALUE [6]. Если пустая ячейка означает «данных пока нет», такое преобразование незаметно подменит смысл отсутствия значением ноль.

Когда текст нужно сохранить

Для суммы «00150» обычно означает 150. Для артикула или идентификатора эти пять символов могут быть самим значением. Массовое преобразование такого столбца уничтожит ведущие нули.

В нашем опыте число 150 с форматом 00000 и текстовый код «00150» выглядели одинаково. Но ISNUMBER дала для первого ИСТИНА, для второго ЛОЖЬ. LEN вернула соответственно 3 и 5: для числа длина определялась по преобразованному в текст значению 150, а не по его пятисимвольному оформлению.

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

Для длинных кодов есть дополнительное документированное ограничение: Excel поддерживает точность до 15 значащих цифр в числовом значении. Это не «15 знаков после запятой» и не только вопрос отображения в научной записи. При вводе длинной числовой последовательности могут измениться сами цифры; последующий текстовый формат их не восстановит. См. рекомендации Microsoft о ведущих нулях и больших числах [7].

Ручной ввод длинного кода в этой проверке не выполнялся: ограничение приведено по документации. При реальном импорте назначайте тип по смыслу столбца: суммы и количества — числа, идентификаторы — текст. Возможности автоматического преобразования зависят от версии Excel; тип конкретного столбца нужно проверить до загрузки данных.

Для подозрительной суммы полезна последовательность: проверить диапазон → проверить тип → определить смысл значения → преобразовать в соседнем столбце → сверить ошибки, строки и итог. Только после этого заменять исходник. Отсутствие ошибки в формуле само по себе ещё не доказывает правильность расчёта.

Материал и учебная книга подготовлены с помощью ChatGPT. Все числа и коды вымышлены. Результаты отмеченных опытов проверены в настоящем Microsoft Excel; свойства, не проверенные ручным вводом или через меню, отделены от наблюдений и подкреплены документацией Microsoft.

Документация для повторения

Справка Microsoft Support, проверенная при подготовке материала 14 ноября 2026 года. Точные названия страниц для поиска:

[1] SUM function.[2] IS functions.[3] TEXT function.[4] Convert numbers stored as text to numbers in Excel.[5] TRIM function.[6] NUMBERVALUE function.[7] Keeping leading zeros and large numbers.