Как разделить тысячи в Excel

Обновлено: 14.05.2024

В этом посте мы покажем вам все способы форматирования больших чисел в виде тысяч, миллионов или миллиардов.

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

Числа в миллионах или даже миллиардах бывает трудно оценить, и они могут легко быть неверно истолкованы с коэффициентом 10.

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

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

Отображение чисел в виде тысяч или миллионов не только повышает удобочитаемость, но и помогает уместить все на одной странице.

В этом посте я покажу вам 5 простых способов представить большие числа в виде тысяч или миллионов. Загрузите пример книги, чтобы следовать ей!

Используйте простую формулу для форматирования чисел в виде тысяч, миллионов или миллиардов

Простым способом отображения чисел в тысячах или миллионах является использование простой формулы для деления числа на тысячу или миллион.

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

Ячейка B3 содержит исходное число, и эта формула вычисляет количество тысяч, отображая остаток в виде десятичного числа.

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

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

Символ карата (^) возводит число 10 в степень 6, что равно 1 миллиону. Вы можете использовать этот же трюк, если хотите отобразить значения в миллиардах, поскольку 10^9 равно 1 миллиарду.

Вы также можете использовать функцию ОКРУГЛ, чтобы удалить лишние десятичные точки из результата. Приведенная выше формула даст только одну цифру после запятой.

Примечание. При использовании функции ОКРУГЛ вы потеряете точность чисел.

Вместо этого, если вы хотите сохранить точность, но отображать только один десятичный знак, вы можете изменить формат.

Выберите ячейки, которые вы хотите отформатировать.
Перейдите на вкладку "Главная" на ленте.
Нажмите команду десятичного формата, чтобы уменьшить количество знаков после запятой. Возможно, вам придется нажать на нее несколько раз в зависимости от того, сколько знаков после запятой вы хотите отобразить.

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

Это еще один способ разделить ваши числа на тысячу или миллион, но без использования формулы.

Вы можете использовать специальный параметр вставки, чтобы разделить число на тысячу или миллион.

Недостаток в том, что исходный номер будет перезаписан, и этот процесс немного сложнее. Возможно, вы захотите сохранить числа в другом месте электронной таблицы, прежде чем использовать этот метод!

Введите число делителя, например 1000, в другую ячейку.
Скопируйте ячейку числа делителя (в данном примере ячейку D2), нажав Ctrl + C или щелкнув ячейку правой кнопкой мыши и выбрав Копировать во всплывающем меню. После того, как ячейка будет скопирована в буфер обмена, вокруг нее появится зеленая пунктирная линия.

Выберите ячейки, которые вы хотите разделить.
Нажмите правой кнопкой мыши на большое число, которое необходимо разделить, и выберите "Специальная вставка" во всплывающем меню или нажмите Ctrl + Alt + V на клавиатуре.

Откроется меню "Специальная вставка".

Выберите параметр «Разделить» в разделе «Операция» меню «Специальная вставка». Вы также можете выбрать параметр «Значения» в разделе «Вставить», чтобы не перезаписать любое форматирование, которое у вас есть.
Нажмите кнопку ОК.

При этом большие числа будут заменены значениями, разделенными на 1000.

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

Используйте функцию ТЕКСТ для форматирования чисел в виде тысяч, миллионов или миллиардов

Функция ТЕКСТ в Excel чрезвычайно полезна для форматирования чисел и дат в определенные форматы.

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

Синтаксис для функции ТЕКСТ

значение — числовое значение, которое вы хотите отформатировать.
format – это текстовая строка, представляющая правило форматирования вывода.

Функция ТЕКСТ принимает число и возвращает отформатированное текстовое значение.

Пример с функцией ТЕКСТ

В этом сообщении функция ТЕКСТ будет использоваться для добавления запятых-разделителей, скрытия любых тысяч цифр и добавления k в конце, чтобы обозначить, что результат исчисляется тысячами.

В этом примере большое число находится в ячейке B3, а формула ТЕКСТ используется в ячейке C3.

Приведенная выше формула покажет число из ячейки B3 в виде текстового значения в тысячах вместе с k, указывающим, что число выражено в тысячах.

Заметили, что результаты выровнены по левому краю? Это означает, что результаты представляют собой текстовые значения, а не числа.

Последняя запятая (,) необходима, чтобы указать, что числа меньше 1000 останутся скрытыми, так как после этой запятой нет заполнителей.

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

Число в ячейке C3 гораздо легче читать, чем необработанное число в ячейке B3.

Используйте приведенную выше формулу, если хотите отобразить числа в миллионах.

Используйте приведенную выше формулу, если хотите отобразить числа в миллиардах. Дополнительная запятая — это все, что нужно, чтобы скрыть каждый набор из 3 цифр в результатах, и любой символ (символы) может быть добавлен в конец по мере необходимости.

Использование пользовательского формата для отображения чисел в виде тысяч, миллионов или миллиардов

Также можно отформатировать ваши числа как тысячи или миллионы, используя собственный числовой формат.

Таким образом, вам не нужно изменять исходное число или использовать формулу для отображения чисел в желаемом формате.

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

Чтобы добавить собственный формат, выполните следующие действия.

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

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

Чтобы отобразить какие-либо символы в конце ваших чисел, вам нужно будет вставить их между двумя символами двойных кавычек.

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

Это может быть простой способ создать формат тысяч или миллионов, который также позволяет использовать отрицательные числа.

< бр />

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

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

Преимущество использования пользовательского формата заключается в том, что вы по-прежнему можете выполнять дальнейшие вычисления с отформатированными числами. Первоначальные цифры остаются без изменений. Изменился только внешний вид цифр.

Используйте условное форматирование для форматирования чисел в виде тысяч, миллионов или миллиардов

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

Выполните следующие действия, чтобы создать этот условный формат.

При этом откроется упрощенная версия меню «Формат ячеек» только с вкладками «Число», «Шрифт», «Граница» и «Заливка».

Перейдите на вкладку "Число".
Выберите «Пользовательский» из доступных вариантов категории.

Введите приведенную выше строку в раздел «Тип». Если вы уже использовали это пользовательское форматирование в другом месте, вы также сможете выбрать его из предопределенных параметров под полем «Тип».
Нажмите кнопку "ОК".

Это закроет меню «Формат ячеек», а меню «Новое правило форматирования» снова станет активным. Теперь вы должны увидеть предварительный просмотр форматирования, которое будет применено, если формула верна.

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

Теперь вы увидите, что любые числа в вашем выборе отображаются нормально, если они меньше 1000, и отображаются в тысячах, когда они выше 1000.

Теперь вы повторяете описанные выше шаги, чтобы добавить еще одно правило условного форматирования для обработки чисел, превышающих 1000000.

Процесс точно такой же, как и раньше, с небольшими изменениями в форматировании только ячеек с вводом и используемом пользовательском формате.

На шаге 6 введите значение 1000000 в поле ввода.

На шаге 10 используйте указанный выше пользовательский формат.

< /p>

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

Для чисел менее 1 КБ подходит общий формат по умолчанию, поскольку запятые-разделители не нужны.

Если вы ожидаете отрицательные числа, вам нужно будет повторить процесс, чтобы добавить правила для чисел, меньших или равных -1000 и меньших или равных -1000000.

Выводы

В Excel есть много способов представить пользователю большие числа.

Все это облегчает чтение чисел и предотвращает ошибки при чтении числа из отчета.

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

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

Используете ли вы какие-либо из этих советов для отображения больших чисел в тысячах, миллионах или миллиардах? Знаете ли вы какие-либо другие методы? Дайте мне знать в комментариях ниже!

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

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

Вы бы предпочли посмотреть это руководство? Нажмите кнопку воспроизведения ниже!

Как изменить десятичный разделитель в Excel

Чтобы изменить десятичный разделитель или разделитель тысяч в Excel, сначала нажмите «Файл» в меню в верхней части экрана.

Затем перейдите в «Параметры» внизу слева.

Вы увидите окно, в котором нажмите «Дополнительно».

Затем в разделе "Параметры редактирования" найдите раздел "Использовать системные разделители". Здесь вы можете указать, какие разделители вы хотите использовать.

Мы собираемся изменить десятичный разделитель и использовать запятую вместо точки, поэтому мы просто перепишем эту точку и введем запятую.

Для разделителя тысяч мы будем использовать точку и сделаем это таким же образом.

Теперь просто подтвердите, нажав OK, и все! Сепараторы заменены так, как нам нужно.

Не упустите прекрасную возможность узнать:

Если вы нашли этот урок полезным, поставьте нам лайк и посмотрите другие уроки от EasyClick Academy. Узнайте, как использовать Excel быстро и легко!

Вы впервые пользуетесь EasyClick?Мы будем более чем рады приветствовать вас в нашем онлайн-сообществе. Нажмите кнопку «Подписаться» и присоединяйтесь к EasyClickers!

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

В большинстве англоязычных стран разделителем тысяч является запятая, а разделителем десятичных знаков — точка. Тем не менее, пространство для волос становится популярным в качестве разделителя тысяч. В некоторых странах, таких как Нидерланды и Бельгия, все наоборот; запятая для десятичного разделителя и точка для разделителя тысяч.

Эта запись является частью III серии сообщений: Форматирование данных ячеек в программах для работы с электронными таблицами. Возможности и функции, описанные в этой статье, основаны на Calc 6.4.3, Excel 365 и версии Google Таблиц по состоянию на май 2020 года.

Включить разделитель тысяч

В некоторых приложениях для работы с электронными таблицами, таких как LibreOffice Calc, Microsoft Excel и Google Sheets, по умолчанию разделитель тысяч отсутствует. Чтобы увидеть разделитель тысяч, необходимо отформатировать ячейку, чтобы он отображался.

Включить разделитель тысяч в Calc

Чтобы включить разделитель тысяч в электронной таблице Calc:

Выберите ячейки, в которых вы хотите, чтобы в числах отображался разделитель тысяч.
Выберите значок «Формат как число» или нажмите Ctrl+Shift+1; этот шаг автоматически добавляет разделитель тысяч и два десятичных знака.
Измените количество десятичных разрядов с помощью значков "Добавить десятичный разряд" и "Удалить десятичный разряд". Например, если вы хотите отображать только целые числа, дважды или более нажимайте "Удалить десятичный разряд" до тех пор, пока вы не перестанете видеть цифру десятичного разряда.

Альтернативный метод предполагает использование окна формальных ячеек:

Выберите ячейки, в которых вы хотите, чтобы числа отображали разделители тысяч.
Нажмите Ctrl+1. Вы также можете щелкнуть правой кнопкой мыши ячейку или одну из выделенных ячеек и выбрать «Формат ячеек». Откроется окно «Формат ячеек».
В разделе "Параметры" на вкладке "Числа" включите разделитель тысяч.
Введите количество знаков после запятой, которое должно отображаться в этом числе.
Нажмите "ОК".

Включить разделитель тысяч в Excel

Чтобы включить разделитель тысяч в электронной таблице Excel:

Выберите ячейки, в которых вы хотите, чтобы числа отображали разделители тысяч.
Откройте окно «Формат ячеек». Щелкните правой кнопкой мыши ячейку или одну из выделенных ячеек и выберите "Формат ячеек".
На вкладке "Числа" выберите "Число" в разделе "Категория".
В разделе "Параметры" установите флажок "Использовать разделитель 1000".
Введите число отображаемых десятичных разрядов.
Нажмите "ОК".

Вы также можете использовать группу номеров на ленте:

Выберите ячейки, в которых вы хотите, чтобы числа отображали разделители тысяч.
В группе «Число» на ленте выберите «Стиль запятой». На этом шаге добавляется разделитель тысяч и настраивается число до двух знаков после запятой.
Используйте значки "Увеличить десятичное число" и "Уменьшить десятичное число", чтобы задать количество отображаемых десятичных разрядов.

Однако второй метод настраивает число на учет, даже если он не отображает символ валюты. Затем формат меняется на пользовательский, когда вы работаете на шаге 3. Я не могу определить, как это будет работать по сравнению с первым методом. Я предлагаю вам использовать первый метод (тот, который открывает окно «Формат ячеек»), если вам необходимо отформатировать его как обычное число.

Включить разделитель тысяч в Таблицах

Чтобы включить разделитель тысяч в электронной таблице Google Sheets:

Замените десятичные разряды по мере необходимости: используйте код формата

В предыдущем разделе после того, как вы добавили разделитель тысяч, вам нужно будет решить, сколько десятичных цифр вы хотите отображать.Если число содержит больше десятичных цифр, чем число, которое вы настроили для отображения, оно будет округлено в большую сторону. Если в числе меньше десятичных цифр, в конце добавляются нули. Например, установите количество десятичных цифр равным трем цифрам; число 5,503 отображается как 5,503, 6,7826 отображается как 6,783, а 8,9 отображается как 8,900.

Что делать, если вы хотите отобразить тысячу разделителей, но также хотите отображать десятичные цифры только по мере необходимости?

Это использование хэша в коде формата. Хэш представляет собой число, которое будет отображаться только при необходимости.

Отображение десятичных разрядов в Calc по мере необходимости

В Calc, чтобы отображать разделители тысяч, позволяя десятичным цифрам появляться только по мере необходимости:

Отображение десятичных разрядов в Excel по мере необходимости

В Excel, чтобы отображать разделители тысяч, позволяя десятичным цифрам появляться только по мере необходимости:

Отображение десятичных разрядов в Таблицах по мере необходимости

В Таблицах, чтобы отображать разделители тысяч, позволяя отображать десятичные цифры только по мере необходимости:

Максимальное количество знаков после запятой

Во всех трех приложениях для работы с электронными таблицами одним из шагов является определение максимального количества отображаемых цифр. Вам, наверное, интересно, почему должно быть максимальное количество десятичных цифр. Есть ли способ не устанавливать максимум, как в «Общем» (Excel и Calc) и «Автоматически» (Таблицы)?

На самом деле, даже у этих числовых форматов по умолчанию есть ограничение. Calc, Excel и Sheets могут отображать до 15 цифр в ячейке, включая целые числа и десятичные разряды.

Если вы закодируете более 15 цифр, их поведение изменится:

Calc округляет самый правый и самый маленький десятичный разряд. Если цифры в основном представляют собой целые числа, они заменяются экспоненциальной записью.
Excel усекает (не округляет) самые правые и наименьшие десятичные разряды. Если цифры в основном представляют собой целые цифры, они заменяются научными обозначениями. Более того, все оставшиеся 15 цифр вы можете увидеть только в строке формул. В ячейке может отображаться только семь или десять цифр в зависимости от того, переведено число в экспоненциальное представление или нет соответственно.
Таблицы обрезают крайние правые и наименьшие десятичные разряды. Если цифры в основном представляют собой целые цифры, но имеют хотя бы один десятичный знак, они заменяются научными обозначениями. Если все цифры являются целыми числами, Листы преобразуют их в текст — число автоматически выравнивается по левому краю, — но с ним все еще можно выполнять математические вычисления.

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

Изменение десятичного разделителя; ты не можешь

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

Вы не можете изменить десятичный разделитель, просто заменив десятичный разделитель в поле Код формата, Тип или Пользовательский номер. Например, ваш язык или локальные настройки настроены на филиппинский язык, где десятичным разделителем является точка, система распознает точку как десятичный разделитель. Если вы измените код и будете использовать точку, например, в качестве разделителя тысяч, а запятую в качестве десятичного разделителя, это может испортить код, и итоговый формат будет не таким, как вы ожидаете.

Изменение разделителя тысяч

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

Приложения для работы с электронными таблицами не принимают никаких символов в качестве разделителя тысяч. Во-первых, он не примет символ, который уже используется в качестве десятичного разделителя. Calc, Excel и Sheets принимают пробелы между символами, пробелы и дефисы в качестве разделителей тысяч. Для других символов одна таблица может принять его, но не другие.

Заключение

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

Код, известный как Код формата в Calc, Тип в Excel и Пользовательский числовой формат в Таблицах, полезен для настройки десятичных разрядов и разделителей тысяч. Они состоят из решеток и нулей; нули оставляют конечные и ведущие нули, в то время как хэши отображают цифры только по мере необходимости.

Изменение разделителей с запятых на десятичные или десятичных знаков на запятые в Microsoft Excel

От группы обучения Avantix | Обновлено 23 ноября 2021 г.

Применимо к: Microsoft® Excel® 2013, 2016, 2019 и 365 (Windows)

В зависимости от вашей страны или региона Excel может отображать десятичные точки или точки вместо запятых для больших чисел. Десятичная точка (.) или запятая (,) используются в качестве разделителя групп в разных регионах мира. Вы можете изменить запятые на десятичные точки или точки или наоборот в вашей книге Excel временно или постоянно. Отображение запятых или десятичных точек по умолчанию зависит от ваших глобальных системных настроек (Региональные настройки на панели управления) и параметров Excel.

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

Excel распознает числа без разделителей групп или тысяч, поэтому эти разделители на самом деле являются форматом.

Ваши региональные настройки в панели управления на вашем устройстве по умолчанию используются Excel для определения того, должны ли десятичные знаки быть точкой или точкой (например, используется в США и на английском языке в Канаде) или десятичные знаки должны быть запятой (используется в многие европейские страны и французская Канада).

Хотите узнать больше об Excel? Посетите наш виртуальный класс или онлайн-курсы Excel >

Замена запятых на десятичные дроби путем изменения глобальных системных настроек

Важно вводить данные в Excel в соответствии с вашими глобальными системными настройками в Панели управления. Например, если вы вводите число 1045,35, а в вашей системе в качестве региона используется США, в качестве десятичного разделителя используется точка. Если вы введете число 1045,35 во Франции, запятая будет использоваться в качестве десятичного разделителя в соответствии с системными настройками, где Франция является регионом.

Ниже показана панель управления в Windows 10:

Чтобы изменить региональные настройки на панели управления в Windows 10:

В поле "Начать поиск" в левом нижнем углу экрана введите "Панель управления". Появится панель управления.
Нажмите Часы и регион. Это может отображаться как «Языки и региональные стандарты».
Под областью нажмите «Изменить формат даты, времени или числа». Появится диалоговое окно. Если вы измените регион, Excel (и другие программы) будут использовать настройки, основанные на выбранном регионе, поэтому США будут использовать точки в качестве десятичных знаков и запятые в качестве разделителей тысяч.
Если вы не хотите менять регион, нажмите Дополнительные настройки. Появится диалоговое окно «Настроить формат».
Перейдите на вкладку "Числа".
Выберите нужные параметры в полях Десятичный символ и Символ группировки цифр. В этом диалоговом окне также можно изменить другие параметры (например, формат негативов).
Дважды нажмите "ОК".

Ниже показано диалоговое окно "Настройка формата":

Имейте в виду, что это изменит настройки по умолчанию на вашем устройстве для всех книг Excel (а также других программ, таких как Microsoft Access или Microsoft Project). Если другой пользователь откроет книгу на другом устройстве, разделители будут отображаться в соответствии с настройками его системы.

Замена запятых на десятичные и наоборот путем изменения параметров Excel

Вы также можете изменить параметры Excel, чтобы запятые отображались как разделители групп или тысяч, а не десятичные дроби, и наоборот. Этот параметр повлияет на любую книгу, которую вы откроете после внесения этого изменения.

Чтобы изменить параметры Excel таким образом, чтобы запятые отображались как разделитель тысяч, а десятичные знаки отображались как разделитель десятичных чисел (при условии, что ваши глобальные настройки в региональных настройках установлены противоположным образом):

Перейдите на вкладку "Файл" на ленте.
Нажмите "Параметры".
В категориях слева нажмите "Дополнительно".
Снимите флажок Использовать системные разделители в области редактирования.
В поле "Десятичный разделитель" введите нужный символ, например десятичную дробь или точку (.).
В поле "Разделитель тысяч" введите нужный символ, например запятую (,).
Нажмите "ОК".

Ниже показано диалоговое окно "Параметры", в котором параметр "Использовать системные разделители" отключен, а в качестве разделителей введены десятичная дробь и запятая:

Вы можете захотеть изменить параметры обратно на Использовать системные разделители, когда закончите свою задачу.

Создание формулы для изменения разделителей и форматирования с использованием текущих системных разделителей

Еще один вариант — написать формулу в другой ячейке или столбце для преобразования разделителей. Это хорошо работает, если данные представляют собой текст с разделителями, которые не используются в текущих настройках системы. Если вы пишете формулу для преобразования текста в числа, вы также можете сохранить исходные значения. В Excel 2013 или более поздних версиях вы можете использовать функцию ЧИСЛОЗНАЧ для преобразования текста в числа.

Функция ЧИСЛОЗНАЧ имеет следующие аргументы:

Текст — текст, который нужно преобразовать в число.

Decimal_separator — символ, используемый в исходной ячейке для разделения целой и дробной части результата.

Group_separator — символ, используемый в исходной ячейке для разделения групп чисел, например тысяч от сотен и миллионов от тысяч.

Функция вводится следующим образом:

=ЧИСЛОЗНАЧ(Текст,Десятичный_разделитель, Разделитель групп)

Например, если у вас 2 500 300,00 в ячейке A1, а в региональных настройках для вашего устройства выбрано значение США, вы можете ввести следующую формулу в ячейку B1:

=ЧИСЛОЗНАЧ(A1,",",".")

Возвращаемое значение обычно отображается как число без форматирования. Затем вы можете применить соответствующее форматирование, нажав Ctrl + 1 и используя Формат ячеек, чтобы применить форматирование с системными разделителями.

Изменение разделителей с помощью Flash Fill

Flash Fill — это утилита, доступная в Excel 2013 и более поздних версиях, которую можно использовать для очистки и/или переформатирования данных. Это не формула, поэтому, если исходные данные изменятся, вам потребуется повторно запустить Flash Fill.

В приведенном ниже примере числа, введенные в столбец A, отформатированы с использованием системных настроек (в данном случае США). В столбец B мы ввели образцы данных в B2 и B3 с разными разделителями (точки для разделителя групп и запятые для десятичного разделителя). Обычно лучше ввести несколько примеров, чтобы показать шаблон в Excel. Вам понадобятся данные с шаблоном слева, чтобы использовать мгновенное заполнение. После того, как вы ввели несколько примеров паттерна в ячейки справа от исходных данных, в ячейке под образцами (в данном случае в ячейке B4) нажмите Ctrl + E. Flash Fill заполнит оставшиеся ячейки, пока не достигнет конец набора данных.

Следующий пример был создан с использованием Flash Fill в столбце B (данные, которые заполняются в столбце B, являются текстом):

Замена запятых на десятичные знаки на листе Excel для выбранного диапазона с помощью функции «Заменить»

Команду «Заменить» можно использовать для замены запятых десятичными знаками или наоборот в диапазоне ячеек на листе. Важно понимать, что это изменит диапазон ячеек на текст, поэтому вы не сможете использовать определенные функции, такие как СУММ. Вы можете заменить на копию данных.

Чтобы заменить запятые десятичными с помощью замены:

Выберите диапазон ячеек, в которых вы хотите заменить запятые десятичными знаками. Вы можете выбрать диапазон ячеек, столбец или столбцы или весь рабочий лист.
Нажмите Ctrl + 1 или щелкните правой кнопкой мыши и выберите "Формат ячеек". Появится диалоговое окно.
Перейдите на вкладку "Число".
В категориях слева нажмите «Текст», а затем нажмите «ОК». Примечательно, что данные будут текстовыми, а не числовыми.
Перейдите на вкладку "Главная" на ленте и в группе "Редактирование" нажмите "Найти и выбрать". Появится раскрывающееся меню.
Нажмите «Заменить». Либо нажмите Ctrl + H. Появится диалоговое окно.
В диалоговом окне "Найти и заменить" перейдите на вкладку "Заменить".
В поле "Найти" введите запятую (символ, который вы хотите найти).
В поле "Заменить на" введите десятичную точку или точку (символ, который вы хотите заменить).
Нажмите «Заменить все», если вы уверены, что хотите заменить все символы или символы в выбранном диапазоне.Нажмите «Заменить», если вы хотите найти и заменить один за другим в выбранном диапазоне. Нажмите «Найти все», если вы хотите сначала найти все символы, которые нужно заменить, а затем решить, хотите ли вы заменить все. Нажмите «Найти далее», если вы хотите найти символы один за другим, и решите, хотите ли вы заменить каждый символ, нажав «Заменить». После замены всех символов Excel отображает диалоговое окно с количеством замен.

Ниже показано диалоговое окно «Найти и заменить» с запятой (,), введенной в поле «Найти», и десятичным знаком (.) в поле «Заменить на»:

В предыдущем примере только запятые были заменены на десятичные дроби, но у вас могут быть данные, содержащие и запятые, и десятичные дроби (например, 1475,55). В этом случае вам потребуется заменить несколько раз.

Чтобы заменить запятые на десятичные и десятичные на запятые с помощью замены:

Выберите диапазон ячеек, в которых вы хотите заменить запятые десятичными и десятичные дроби запятыми. Вы можете выбрать диапазон ячеек, столбец или столбцы или весь рабочий лист.
Нажмите Ctrl + 1 или щелкните правой кнопкой мыши и выберите "Формат ячеек". Появится диалоговое окно.
Перейдите на вкладку "Число".
В категориях слева нажмите «Текст», а затем нажмите «ОК». Примечательно, что данные будут текстовыми, а не числовыми.
Перейдите на вкладку "Главная" на ленте и в группе "Редактирование" нажмите "Найти и выбрать". Появится раскрывающееся меню.
Нажмите «Заменить». Либо нажмите Ctrl + H. Появится диалоговое окно.
В диалоговом окне "Найти и заменить" перейдите на вкладку "Заменить".
В поле "Найти" введите десятичное число.
В поле "Заменить на" введите звездочку (*).
Нажмите «Заменить все», если вы уверены, что хотите заменить все символы или символы в выбранном диапазоне. Нажмите «Заменить», если вы хотите найти и заменить один за другим в выбранном диапазоне. Нажмите «Найти все», если вы хотите сначала найти все символы, которые нужно заменить, а затем решить, хотите ли вы заменить все. Нажмите «Найти далее», если вы хотите найти символы один за другим, и решите, хотите ли вы заменить каждый символ, нажав «Заменить». После замены всех символов Excel отображает диалоговое окно с количеством замен.
В поле "Найти" введите запятую.
В поле "Заменить на" введите десятичную точку или точку.
Нажмите «Заменить все», если вы уверены, что хотите заменить все символы или символы в выбранном диапазоне. Нажмите «Заменить», если вы хотите найти и заменить один за другим в выбранном диапазоне. Нажмите «Найти все», если вы хотите сначала найти все символы, которые нужно заменить, а затем решить, хотите ли вы заменить все. Нажмите «Найти далее», если вы хотите найти символы один за другим, и решите, хотите ли вы заменить каждый символ, нажав «Заменить». После замены всех символов Excel отображает диалоговое окно с количеством замен.
В поле "Найти" введите звездочку (*).
В поле "Заменить на" введите запятую.
Нажмите «Заменить все», если вы уверены, что хотите заменить все символы или символы в выбранном диапазоне. Нажмите «Заменить», если вы хотите найти и заменить один за другим в выбранном диапазоне. Нажмите «Найти все», если вы хотите сначала найти все символы, которые нужно заменить, а затем решить, хотите ли вы заменить все. Нажмите «Найти далее», если вы хотите найти символы один за другим, и решите, хотите ли вы заменить каждый символ, нажав «Заменить». После замены всех символов Excel отображает диалоговое окно с количеством замен.

Опять же, результатом будет текст, а не числа.

Подпишитесь, чтобы получать больше статей, подобных этой

Была ли эта статья полезной для вас? Если вы хотите получать новые статьи, присоединяйтесь к нашему списку адресов электронной почты.

Читайте также: