Как мне отображать отформатированные числа только в отформатированном виде в ячейках Excel?

Когда я использую Excel, иногда формула, которую я использую, выдает ответ, например, 67.9999999.

Когда я вставляю это в другую ячейку, которую я отформатировал на 2 десятичных знака, она показывает 68, но в верхней строке формул все равно отображается как 67.99999.

Как я могу изменить это, не проходя вручную через каждую и не переписывая её в формате на 2 десятичных знака, чтобы обе: ячейка и строка формул отображали число с 2 десятичными знаками?

Спасибо

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

Обратите внимание, что формат ячейки Общий

Выберите ячейки, которые хотите исправить, и выполните этот короткий макрос:

Sub FixData()
    Dim r As Range

    For Each r In Selection
        r.Value = r.Text
    Next r
End Sub

что даст:

(макрос не пытается изменить формат ячейки)

Конечно. Скопируйте данные из публичной таблицы (в то время, когда никто её не открывал и не мог модифицировать перед тем, как вы вставите исправленные данные обратно!) в вашу таблицу в каком-нибудь удобном месте. Если это таблица, которую вы используете только для этой цели, или одну, которую вы просто открываете вновь, делаете работу, а затем закрываете, никогда не сохраняя, то это будет лучше: любой из этих двух вариантов.

Как только данные будут там со всеми десятичными знаками, которые были изначально, напишите формулу в соседнем столбце, например, такую:

=ROUND( DATA, 2)

ROUND() изменит каждое значение на два десятичных знака так же, как Excel выбирает, как его отображать, показывая только два десятичных знака.

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

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

Если вы не знакомы с Вставить | Специальная | Значения, есть множество способов это сделать, но самый простой — использовать комбинацию клавиш. Я использую другую, которая трудно объяснима здесь, но многие годы использовал Alt-E-S-V, затем нажимал Enter. ЛЮБОЙ способ, который вы знаете, как это сделать, в конечном итоге приводит к счастью.

У вас все еще есть оригинальные значения в выбрасываемой таблице, так что быстро проверьте, что ничего странного не произошло. Если всё выглядит хорошо, сохраните её, а затем закройте выбрасываемую таблицу. Если что-то выглядит странно, попробуйте разобраться, почему, но затем просто закройте публичную таблицу БЕЗ сохранения (вы всегда можете начать совершенно заново, верно?), нажмите Ctrl-Z или скопируйте оригинальные данные обратно на то, что вы только что вставили. Три хороших способа убедиться, что неприятная работа исчезает, оставляя вам оригинал.

Ключ в том, что вы готовы, как вы сказали выше, выполнить работу по “скопировать, исправить, вернуть назад”. Если что-то делает это невозможным (например, публичная таблица никогда не закрывается всеми возможными пользователями достаточно долго, чтобы вы могли это сделать), тогда этот метод не сработает.

Кто угодно с такой же потребностью (я не думаю, что Fiona Appleton будет читать этот ответ через пять лет!), может получить пользу от ответа, который я дал на:

Десятичные знаки все еще в представлении строки формул (не в представлении ячеек) – нужно округлить и удалить

в котором задается похожий вопрос.

Это ОЧЕНЬ похоже, но отличается, так что вставка его сюда не совсем уместна. В основном, он объясняет выгоды и убытки от Precision As Displayed, чтобы помочь кому-то выбрать, использовать это или нет, в зависимости от их ситуации. Он указывает, что есть некоторые подходы VBA, и другой ответ на этот вопрос предлагает одно из этих решений. В нем также есть метод экспортирования данных в нужном формате (экспортирование это необходимость в другом вопросе, но здесь это не кажется таковой, так что это может не быть полезным). В нем также упоминается недостаток скрытия редактора формул, если цель состоит в том, чтобы пользователи НИКОГДА не видели точные значения, только отображаемые значения, но это не будет иметь значения, если только не нужно избежать точных значений на виду.