Функция TEXT() не дополняется должным образом в Excel 2010

Question

У меня есть лист Excel, который состоит из трех страниц:

1. "Eingaben" / Input - пользователь вводит в некоторые данные
2. "Экспорт" - данные форматируются и дополняются для экспорта файла PRN.
3. "Константе" - содержит некоторые постоянные значения для использования на вкладке экспорта

На первой странице есть поле, содержащее число (форматирование при вводе как "Стандарт"). Данный номер "67922200305"

В экспорте формула говорит

=TEXT(Konstante!$B$5&Eingabe!$D2;"000000000000000000000000")

поэтому предполагается, что перед входным значением должен стоять префикс "60308700", а все это должно быть дополнено нулями до длины 24.

Странно, но вывод в Export:

000006030870067922200000

где я ожидал

000006030870067922200305

Формат поля экспорта - Стандартный, а константа - Текст.

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

Answer 1

Функция TEXT принимает числовое значение в качестве первого параметра, поэтому результат конкатенации (т.е. Konstante!$B$5&Eingabe!$D2) конвертируется в число. Обратите внимание, что то же самое происходит с предложением комментария (Konstante!$B$5&TEXT(Eingabe!$D2))).

Взгляните на эту запись в Википедии:

Хотя Excel может отображать 30 десятичных знаков, его точность для заданного числа ограничена 15 значащими цифрами, а точность вычислений может быть даже меньше из-за трех проблем: округления, усечения и двоичного хранения.

Ваш номер (6030870067922200305) слишком длинный, а в Excel хранится только 15 значащих цифр (т. 603087006792220).

Вы можете достичь того, что вы пытаетесь сделать, имея дело только с текстовыми значениями. Например, вы можете использовать функции LEN и REPT :

=REPT(0;24-(LEN(Konstante!$B$5)+LEN(Eingabe!$D2)))&Konstante!$B$5&Eingabe!$D2

Здесь я повторяю 0 чтобы достичь 24 (меньше постоянной и входной длины), затем я конкатанирую константу и входные значения.

Надеюсь это поможет.