Как посчитать числа с запятой в excel
Точка вместо запятой дробных чисел может привести к существенным последствиям при расчетах в программе Excel. Чаще всего такие ошибки случаются, когда импортируются данные в таблицу из других источников.
Если в дробных числах вместо запятой стоит точка, то программа автоматически воспринимает их как текстовый тип данных. Поэтому перед тем как выполнять математические расчеты и вычисления следует отформатировать и подготовить импортированные данные.
Как в Excel заменить точку, на запятую?
Выделите и скопируйте данные из ниже приведенной таблицы:
| № п/п | Валютная пара | Пересчет | Продажа | Покупка | Спрэд |
| 1 | EUR/USD | 1 | 1.1347 | 1.1350 | 0.0003 |
| 2 | GBP/USD | 1 | 1.5438 | 1.5442 | 0.0004 |
| 3 | USD/CHF | 1 | 0.9543 | 0.9547 | 0.0004 |
| 4 | USD/JPY | 100 | 1.1948 | 1.1945 | -0.0003 |
Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.
Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.
Примечание. Если копировать данные из других источников без специальной вставки, то вместе с данными копируется и формат. В таком случаи формат ячеек «Общий» (по умолчанию) может быть изменен. Тогда нельзя визуально отличить, где распознано число, а где текст.
Все последующие действия необходимо выполнять с чистого листа. Удалите все, что есть на листе или откройте новый для дальнейшей работы.
Чтобы заменить точку, на запятую в импортированных данных можно использовать 4 способа:
Способ 1 замена точки на запятую в Excel через Блокнот
Программа Блокнот Windows не требует использования сложных настроек и функций, а лишь выступает посредником в копировании и предварительной подготовки данных.
- Скопируйте данные из исходной таблички на этой странице. Откройте программу Блокнот Windows («Пуск»-«Все программы»-«Стандартные»-«Блокнот») и вставьте в него скопированные данные для подготовки.
- Выберите в меню «Правка» опцию «Заменить» (или комбинацию горячих клавиш CTRL+H). В появившимся диалоговом окне, введите в поле: «Что» точку (.), а в поле «Чем» запятую (,). И нажмите кнопку «Заменить все».
Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.
Это весьма простой, но очень эффективный способ.
Способ 2 временно меняем настройки Excel
Перед тем как поменять точку на запятую в Excel правильно оцените поставленную задачу. Возможно, лучше заставить программу временно воспринимать точку, как разделитель десятых в дробных числах. Просто в настройках мы указываем, что в дробных числах разделитель точка вместо запятой.
Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.
После выполнения вычислений настоятельно рекомендуется вернуть настройки по умолчанию.
Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.
Способ 3 временно меняем системные настройки Windows
Принцип этого способа похож на предыдущий. Только здесь мы меняем похожие настройки в Windows. В настройках региональных стандартов операционной системы нужно заменить запятую, на точку. Теперь подробнее о том, как это сделать.
Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» - вводим нужное нам значение. Дальше ОК и ОК.
Внимание! Если вы откроете этот файл на другом компьютере, где установлены другие системные параметры региональных стандартов, то могут возникнуть проблемы с вычислениями.
Способ 4 используем функцию найти и заменить в Excel.
Данный способ похож на первый. Только здесь мы используем ту же функцию из Блокнота, но уже в самом Excel.
В этом способе, в отличие от выше приведенных, мы сначала вставляем скопированную табличку на чистый лист, а потом подготавливаем ее к выполнению вычислений и расчетов.
Важным недостатком данного способа является сложность его реализации, если некоторые дробные числа с точкой после вставки были распознаны как дата, а не как текст. Поэтому мы сначала избавимся от дат, а потом разберемся с текстом и точками.
- Предварительно выделите столбцы где будут находится дробные числа с точкой в качестве разделителя. В данном случаи это 3 столбца D:F.
- Установите для выделенного диапазона текстовый формат ячеек, чтобы заранее избежать автоматического преобразования в формат даты некоторых чисел. Для этого выберите текстовый формат из выпадающего списка на закладке «Главная» в разделе «Число». Или нажмите CTRL+1 , в появившимся окне «Формат ячеек» выберите закладку «Число», а в разделе «Числовые форматы» укажите «Текстовый».
- Скопируйте таблицу и щелкните правой кнопкой мышки по ячейке A1. Из контекстного меню выберите опцию «Специальная вставка». Выберите «Текст в кодировке Unicode» и нажмите ОК. Обратите внимание, как теперь отображаются значения в ячейках: D3, D5, E3, E5, в отличии от самого первого копирования таблицы.
- Нажмите на инструмент «Главная»-«Найти и выделить»-«Заменить» (или нажмите комбинацию CTRL+H).
- В появившемся окне введите в поле «Найти» - точку, а в во второе поле введите запятую. И нажмите «Заменить все».
- Снова выделите 3 столбца D:F и измените формат ячеек на «Числовой» CTRL+SHIFT+1. Не забудьте увеличить разрядность до 4-х, как описано в пункте №2.
Все точки сменились на запятые. А текст автоматически преобразился в число.
Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:
Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.
Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.
В этой статье я расскажу о приемах сложения чисел через запятую в одной ячейке Excel, как показано на скриншоте ниже. Если вас интересует этот случай, прочтите подробности.
Сумма чисел с запятыми от Kutools for Excel
Суммировать числа через запятую по формуле
Чтобы суммировать числа с запятыми в одной ячейке, вы можете применить формулу.
Выберите пустую ячейку B2, в которую вы поместите результат, и введите эту формулу,
=SUM(IF(ISERR(VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))),0,VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1)))) ,
пресс Shift + Ctrl + Enter ключи. Смотрите скриншот:
Чаевые:
1. $ A $ 1 - это первая ячейка столбца, содержащая ячейку, в которой вы хотите просуммировать свои числа с запятыми, A2 - это ячейка, в которой вы хотите суммировать свои числа, вы можете изменить их по своему усмотрению.
2. Эта формула суммирует только числа как единичные цифры.
Суммировать числа с запятыми по тексту в столбцы
Если у вас есть ячейка, содержащая несколько десяти цифр с запятыми, разделенными запятыми, как показано на скриншоте ниже, вы можете применить Текст в столбцы функция, чтобы сначала разделить числа запятыми, а затем суммировать их.
1. Выберите ячейку, в которой вы хотите просуммировать числа, и нажмите Данные > Текст в столбцы, и проверьте разграниченный из всплывающего диалогового окна. Смотрите скриншот:
2. Нажмите Следующая, проверить Запятая в Шаг 2 из 3 мастер и щелкните Следующая , чтобы перейти к Шаг 3 из 3, наконец, выберите ячейку для размещения результата разделения. Смотрите скриншот:
3. Нажмите Завершить, а теперь числа разделены запятыми подряд.
4. Выберите пустую ячейку и введите = СУММ (G1: J1), нажмите Enter, чтобы просуммировать числа.
Сумма чисел с запятыми от Kutools for Excel
Если у вас есть Kutools for Excel, вы можете применить его Сумма числа в ячейке функция для быстрого суммирования каждой цифры в ячейке, а также может применять ее Разделить клетки функция для суммирования чисел, разделенных запятыми.
Суммируйте все цифры в ячейке
1. Выберите пустую ячейку, в которую вы поместите результат расчета, и нажмите Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:
2. в Формула Помощник диалоговое окно, прокрутите, чтобы выбрать Суммировать числа в ячейке из Выберите формулу раздел, затем выберите ячейку, в которой вы хотите суммировать цифры в Ячейка текстовое поле, см. снимок экрана:
3. Нажмите Ok, и каждая цифра в выбранной ячейке была добавлена.
Примечание. Эта функция принимает числа как однозначные числа, например 15 как 1 и 5.
Наконечник. Если вы хотите получить бесплатную пробную версию функции извлечения текста, пожалуйста, перейдите к бесплатной загрузке Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.
Суммируйте все числа, разделенные запятыми, в одну ячейку
1. Выберите ячейку, в которой нужно суммировать числа, разделенные запятыми, нажмите Kutools > Слияние и разделение > Разделить клетки. Смотрите скриншот:
2. в Разделить клетки диалог, проверьте Разделить на строки or Разделить на столбцы как хотите и проверьте Другое вариант и введите запятую , в следующее текстовое поле. Смотрите скриншот:
3. Нажмите Ok, и выберите ячейку для размещения значений разделения. Смотрите скриншот:
4. Нажмите Ok, а числа разбиваются на ячейки запятыми.
5. Теперь вы можете суммировать, подсчитывать или производить другие расчеты по мере необходимости.
В этом руководстве вы узнаете, как использовать комбинацию функций LEN, TRIM и SUBSTITUTE для подсчета значений, разделенных запятыми, в ячейке в Excel.
Как подсчитать значения, разделенные запятыми, в ячейке в Excel?
Как показано на скриншоте ниже, в столбце B есть список строк, разделенных запятыми, в котором вы хотите подсчитать значения, разделенные запятыми, в каждой ячейке. Для этого сделайте следующее.
Общая формула
аргументы
A1: Представляет ячейку, содержащую строку, разделенную запятыми, в которой вы будете считать значения, разделенные запятыми.
Как пользоваться этой формулой?
1. Выберите пустую ячейку для вывода результата. В этом случае я выбираю ячейку D3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. А затем перетащите его маркер заливки полностью вниз, чтобы применить его к другим ячейкам.
=LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1
Внимание: В этой формуле B3 - это ячейка, содержащая строку, разделенную запятыми, внутри которой вы будете считать значения, разделенные запятыми. Вы можете изменить его по своему усмотрению.
Как работает эта формула?
=LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1
1. LEN(TRIM(B3)) : Функция TRIM удаляет все пробелы извлечения из строки, разделенной запятыми, «AA, BB, CC, DD», и сохраняет только один пробел между словами. Затем функция LEN вычисляет общую длину «AA, BB, CC, DD» и возвращает результат как 14;
2. LEN(SUBSTITUTE(TRIM(B3),",",""))
- SUBSTITUTE(TRIM(B3),",","") : Как показано выше, функция TRIM удаляет все пространство извлечения из «AA, BB, CC, DD» и возвращает результат как «AA, BB, CC, DD». А затем функция ЗАМЕНА заменяет все запятые в «AA, BB, CC, DD» ничем и получает результат «AA BB CC DD»;
- LEN("AA BB CC DD") : Функция LEN возвращает количество символов в текстовой строке «AA BB CC DD». Результат - 11.
3. 14-11 1 +: Здесь используйте общую длину «AA, BB, CC, DD», вычитая общую длину «AA BB CC DD», чтобы получить общее количество запятых в строке (14-11 = 3). Поскольку после последней запятой всегда стоит слово, вам нужно добавить 1 к числу 3, чтобы получить общее количество слов в строке, разделенных запятыми. Здесь результат 4.
Связанные функции
Функция ДЛСТР в Excel
Функция Excel LEN возвращает количество символов в текстовой строке.
Функция ПОДСТАВИТЬ в Excel
Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.
Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.
Родственные формулы
Подсчет вхождений определенных символов в ячейку Excel
В этом руководстве показано, как применять формулы на основе функций LEN и SUSTITUTE для подсчета количества раз, когда определенный символ появляется в ячейке Excel.
Подсчет определенных символов в диапазоне ячеек
В этом руководстве представлены некоторые формулы для подсчета количества раз, когда определенный символ появляется в диапазоне в Excel.
Подсчитайте конкретные слова в ячейке в Excel
В этой статье будет представлена формула для подсчета количества раз, когда определенное слово появляется в ячейке Excel.
Подсчитайте конкретные слова в диапазоне в Excel
В этой статье объясняется формула для подсчета количества раз, когда определенное слово появляется в диапазоне ячеек в Excel.
Подсчитать количество символов в ячейке в Excel
Общее количество символов включает не только все буквы, но также все пробелы, знаки препинания и символы в ячейке. Следуйте этому руководству, чтобы легко подсчитать общее количество символов в ячейке с помощью функции ДЛСТР в Excel.
Подсчитать количество символов в диапазоне в Excel
В этой статье объясняется формула для подсчета общего количества символов, которые появляются в диапазоне ячеек в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Если содержимое ячейки разделено запятой в одной ячейке, например «A1, A2, A3, A4, A5», и вы хотите подсчитать общее количество значений, разделенных запятыми в этой ячейке, что вы можете сделать? В этом случае общее количество значений, разделенных запятыми, должно быть 5. В этой статье мы покажем вам два метода подсчета значений, разделенных запятыми, в одной ячейке в Excel.
Подсчитайте значения, разделенные запятыми, в одной ячейке с формулой
В качестве примера возьмите данные на скриншоте ниже. Для подсчета значений, разделенных запятыми, в каждой ячейке диапазона B3: B7 выполните следующие действия.
1. Выберите пустую ячейку для вывода результата.
2. Введите в нее формулу ниже и нажмите Enter ключ. Выделите ячейку результата и перетащите ее Ручка заполнения вниз, чтобы получить все результаты.
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1
Легко считайте значения, разделенные запятыми, в одной ячейке с помощью удивительного инструмента
Для Подсчитайте количество ячеек через запятую полезности Kutools for Excel, вы можете легко подсчитать значения, разделенные запятыми, в ячейке в Excel, не применяя формулы вручную.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода результата.
2. К Kutools вкладку нажмите Формула Помощник > Formula Helper.
3. в Помощник по формулам диалоговое окно, настройте следующим образом.
- Найти и выбрать Подсчитайте количество значений, разделенных запятой в Выберите формулу коробка;
Наконечник: вы можете проверить Фильтр поле введите определенные слова, чтобы отфильтровать имена формул. - В Ячейка поле выберите ячейку, в которой вы хотите подсчитать значения, разделенные запятыми;
- Нажмите OK кнопка. Смотрите скриншот:
4. Теперь результат помещается в выбранную ячейку. Выделите эту ячейку результата, а затем перетащите ее маркер заполнения вниз, чтобы получить другие результаты. Смотрите скриншот:
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме:
Подсчитайте повторяющиеся значения в столбце в Excel
Если у вас есть список данных на листе с уникальными значениями и повторяющимися значениями, и вы хотите не только подсчитывать частоту повторяющихся значений, но также хотите знать порядок появления повторяющихся значений. Методы, описанные в этой статье, могут оказать вам услугу.
Подсчитать количество ячеек с текстом или числом в Excel
Например, у вас есть рабочий лист с текстом и числами, и вы хотите подсчитать общее количество ячеек с текстом и числами отдельно, что бы вы сделали? В Excel для этого можно использовать формулу. Пожалуйста, просмотрите это руководство для получения более подробной информации.
Подсчитать, если ячейка содержит текст или часть текста в Excel
Предположим, у вас есть указанные ниже данные, и вы хотите подсчитать количество ячеек, содержащих текст «Apple», количество ячеек, содержащих текст «Оранжевый», а ячейки содержат «Персик» отдельно, как это сделать? В этом руководстве мы подробно объясняем функцию СЧЁТЕСЛИ, чтобы помочь вам быстро подсчитать количество ячеек, если они содержат текст или часть определенного текста в Excel. Кроме того, мы представляем замечательную функцию, позволяющую легко достичь ее с помощью только щелчков мышью.
Подсчитайте количество ячеек между двумя значениями или датами в Excel
Если вам нужно подсчитать номера ячеек, которые содержат определенные значения между двумя числами или датами, формула функции CountIf может быстро вам помочь. В этом руководстве мы покажем вам подробности подсчета количества ячеек между двумя значениями или датами в Excel.
При вводе значений в ячейки листа Excel все данные проходят через встроенный программный фильтр форматирования. Он позволяет упростить работу пользователя. Поэтому введенные данные могут отличаться от отображаемых значений после ввода.
К каким проблемам может привести фильтр форматов ячеек, рассмотрим на конкретных примерах. А также найдем лучшие решения для выхода из сложившихся ситуаций.
Если Excel считает некорректно числа после запятой
Рассмотрим простой пример, когда содержимое ячеек отличается, от отображения их значений. Например, могут возникнуть математические ошибки при округлении дробных чисел.
На готовом примере продемонстрируем следующие расчеты.
- Заполните исходную табличку как показано на рисунке:
- В ячейках B и C задайте финансовый формат (CTRL+1 «Формат ячеек»-«Число»-«Финансовый»).
- В ячейку C1 запишите то, что отображается в ячейке B1 ( 1,53 – результат после округления до двух знаков после запятой ). Так же само введите число в C2 как отображено в B2 ( символы валют не проставляем, так как они преставятся автоматически благодаря финансовому формату ).
- В третьей строке просуммируем значение каждой колонки таблички.
Как видите точность расчета знаков после запятой в Excel, может отличаться. Форматирование в реальности не округляет числовые значения в ячейках. Они остаются те же и реальными отображаются в строке формул.
При суммировании большого количества таких округлений ошибки могут быть очень большими. Поэтому при расчетах и вычислениях нельзя округлять с помощью форматирования. Необходимо знать, как округлить сумму в Excel. Точное округление можно сделать только специальными функциями такими как:
- =ОКРУГЛ;
- =ОКРУГЛВВЕРХ;
- =ОКРУГЛВНИЗ;
- =ЦЕЛОЕ ( функция позволяет в Excel округлить до большего целого ).
Для эффективного использования этих функций в больших количествах применяются массивы функций. Но это уже рассмотрим на следующих уроках.
Автоматическая вставка десятичной запятой
Автоматический фильтр форматов рассчитан на упрощение работы с программой, особенно если научится им управлять. Очень часто нам приходится поставить запятую при вводе денежных сумм для отображения копеек. Десятичный разделитель запятая в Excel может проставлятся автоматически при заполнении финансовых данных в денежном эквиваленте. Для этого:
- Откройте окно «Фай»-«Параметры».
- В окне «Параметр Excel» перейдите на «Дополнительно»-«Параметры правки» и отметьте галочкой пункт «Автоматическая вставка десятичной запятой». Число знаков после разделителя пусть останется «2». Теперь проверим результат.
- В ячейку A1 введите 1234 и нажмите «Enter», в результате мы видим 12,34 как на рисунке:
Теперь можно спокойно вводить суммы с копейками, не разделяя их запятой. После каждого ввода запятая будет проставляться автоматически перед двумя последними числами.
Стоит отметить, что если в сумме 00 копеек, то знаки после запятой в Excel нужно вводить обязательно. Иначе может получиться так 0,01 или 0,2.
Если при вводе с автоматическим режимом вставки разделителя вы ввели запятую, то она останется в том месте, где вы ее вводили.
То есть количество знаков после запятой можно изменить или перенести саму запятую.
Читайте также: