Excel не считает числа с запятыми

Обновлено: 04.07.2024

В этом посте рассказывается, как подсчитывать значения в одной ячейке, разделенные запятыми, с помощью формулы в Excel. Как подсчитать значения, разделенные запятыми, в одной ячейке в Excel 2013/2016. Возможно ли иметь формулу, которая может подсчитывать количество значений, разделенных запятыми, в отдельных ячейках Excel.

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

Предположим, что у вас есть список данных, содержащих текстовые строковые значения, и каждое значение разделено запятой. Например, ячейка B1 содержит «excel,word,access». Я хочу знать, сколько значений в ячейке B1. Приведенный ниже метод покажет вам, как подсчитать количество значений, разделенных запятой в одной ячейке.

Шаг 1: вам нужно выбрать пустую ячейку или соседнюю ячейку ячейки B1.

Шаг 2: введите следующую формулу на основе функции ДЛСТР и функции ПОДСТАВИТЬ в ячейку C1 и нажмите клавишу ВВОД, чтобы применить эту формулу.

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

Шаг 3: вы увидите, что общее количество значений, разделенных запятой, вычисляется в ячейке C1.

Связанные функции

Функция замены Excel
Функция Excel ПОДСТАВИТЬ заменяет новую текстовую строку на старую текстовую строку в текстовой строке. Синтаксис функции ПОДСТАВИТЬ следующий: = ПОДСТАВИТЬ (текст, старый_текст, новый_текст,[экземпляр_номер ])….
Функция ДЛСТР в Excel возвращает длину текстовой строки (количество символов в текстовой строке). Синтаксис функции ДЛСТР следующий: = ДЛСТР(текст)…
Функция Excel TRIM удаляет все пробелы из текстовой строки, кроме одиночных пробелов между словами. Вы можете использовать функцию TRIM для удаления лишних пробелов между словами в строке. Синтаксис функции TRIM следующий: = TRIM (текст)….

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

Предположим, что вы перечислили некоторый текст в одной ячейке, разделенной разрывом строки (вы можете сделать это, нажав ALT + ENTER после ввода текста), и теперь вы хотите извлечь несколько строк текста.

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

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

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

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

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

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

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

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

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

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

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

Эта формула должна быть введена как формула массива, что означает, что для ввода формулы следует использовать Ctrl+Shift+Enter. Если вам нужно получить количество для другого диапазона, просто измените диапазон в двух местах в формуле.

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

Чтобы использовать функцию на листе, введите в ячейку следующее:

Все эти методы, описанные до сих пор, будут подсчитывать запятые, которые фактически находятся в ячейке. Они не будут учитывать запятые, которые появляются в ячейке из-за форматирования. Например, если в ячейке отображается число «1234», велика вероятность того, что запятая стоит там из-за способа форматирования ячейки; на самом деле это не в самой клетке. Такие запятые не учитываются.

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

Выберите диапазон ячеек, в которых вы хотите подсчитывать запятые.
Нажмите Ctrl+H, чтобы отобразить вкладку "Заменить" диалогового окна "Найти и заменить".
В поле "Найти" введите запятую.
В поле "Заменить на" введите запятую.
Нажмите "Заменить все".

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

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

В этой статье объясняется, как исправить эту проблему форматирования чисел в Excel и переформатировать числа в формат, который работает.

Проблема: Excel не распознает числа

На приведенном ниже снимке экрана показана проблема. Столбец C представлен в европейском числовом формате, который не распознается версиями Excel для США. Как бы я ни форматировал столбец C, я не могу преобразовать его в формат, распознаваемый как числа. Если я нажму на любую из этих ячеек и попытаюсь переформатировать их как числа в Excel, это не сработает. Формат ячеек не может быть распознан Excel как числа.

Столбец C представлен в европейском числовом формате. Это не распознается моей версией Excel как числа

Шаг 1. Выделите столбец и удалите разделитель тысяч

Первое, что мы хотим сделать, это удалить десятичную точку '.' в качестве разделителя тысяч. Для этого выделите столбец с цифрами и нажмите найти-заменить. Найдите десятичную «.» и замените ее ничем (т. е. не пишите никаких символов в поле замены).

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

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

Шаг 2. Удалите запятую (,) в качестве десятичного разделителя

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

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

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

Информацию о проблемах с форматированием дат см. на странице Excel Dates Fix Format.

ПРИМЕЧАНИЕ. Инструкции по замене написанных слов на числа (например, с трех на три) см. в разделе Преобразование слов в числа в Excel.

Похоже на цифры, но не складываются

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

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

На снимке экрана выше значения в столбце C выглядят как числа, но они не складываются. Есть 3 ячейки с формулами:

СУММ. В ячейке C7 сумма по формуле сложения равна нулю, вместо того чтобы показывать правильный ответ.
СЧЕТЧИК: справа, в ячейке F4, в формуле используется функция СЧЕТЧИК. Эта функция подсчитывает все непустые ячейки. Результатом формулы является 4, что показывает, что в этом диапазоне ячеек, C3:C6, есть 4 записи.
COUNT: Однако функция COUNT в ячейке F5 показывает нулевой результат. Это показывает, что ни одно из значений в ячейках C3:C6 не распознается как число.

Преобразование текста в числа с помощью специальной вставки

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

ПРИМЕЧАНИЕ. Если вам нужно делать это часто, вы можете использовать макрос для автоматизации задачи.

Хронология видео

0:00 Введение
0:42 Проверка содержимого ячейки
1:40 Проверьте панель формул
2:00 Исправьте цифры
3:08 Получить образец файла

Как исправить текстовые числа

Чтобы исправить числа, которые отображаются как текст, выполните следующие действия:

Щелкните правой кнопкой мыши пустую ячейку и выберите "Копировать".
Выберите ячейки, содержащие «текстовые» числа.
Щелкните правой кнопкой мыши одну из выделенных ячеек и выберите "Специальная вставка".
Откроется диалоговое окно "Специальная вставка".
В разделе "Вставить" выберите "Значения".
В разделе "Операция" выберите "Добавить".
Нажмите "ОК".

Совет. После исправления чисел можно применить форматирование с помощью команд "Числовой формат" на вкладке "Главная" ленты.

Преобразование текста в числа с помощью VBA

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

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

Преобразование дат с заменой всех

Еще один способ исправить проблемные номера. В этом примере числа — это даты, и Excel не распознает их как настоящие даты.

Как показано на снимке экрана ниже, эти даты отформатированы с помощью косой черты в формате даты: м/д/гг

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

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

Выберите ячейки, содержащие даты.
На вкладке "Главная" ленты нажмите "Найти и выбрать", затем нажмите "Заменить"
- Или используйте сочетание клавиш Ctrl + H.
Откроется окно "Найти и заменить"
- Перейдите на вкладку "Заменить", если она еще не выбрана.
В поле "Найти" введите косую черту: /
В поле "Заменить на" введите косую черту: /
Далее нажмите кнопку "Заменить все".
Когда появится окно сообщения, нажмите "ОК", чтобы подтвердить замену.
Нажмите кнопку "Закрыть", чтобы закрыть окно "Найти и заменить".

Совет. После того, как даты будут исправлены, вы можете применить к списку другой формат даты. Выберите все ячейки с фиксированной датой и используйте команды «Числовой формат» на вкладке «Главная» ленты.

Исправить скрытые символы

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

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

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

Выполните следующие действия, чтобы удалить скрытые символы с помощью функции "Найти и заменить":

Выберите ячейки, содержащие числа со скрытыми символами.
На вкладке "Главная" ленты нажмите "Найти и выбрать" (в крайнем правом углу).
В раскрывающемся списке нажмите "Заменить".
Нажмите в поле «Найти».
Нажмите и удерживайте клавишу Alt, а затем на цифровой клавиатуре введите 0160
- После ввода этого кода в поле "Найти" ничего не появится.
Для Заменить на оставьте поле пустым
- Скрытые символы с кодом 160 будут заменены ничем (пустая строка)
Нажмите "Заменить все".
Нажмите "ОК", чтобы подтвердить замену.
Закройте окно "Найти и заменить".

Исправить скрытые символы с помощью VBA

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

Сохраните следующий макрос в рабочей книге, которая всегда открыта, например в личной рабочей книге. Затем добавьте кнопку на существующую панель инструментов и прикрепите макрос к этой кнопке. Чтобы исправить «текстовые» числа, выберите ячейки и нажмите кнопку на панели инструментов.

Преобразование текста в числа с помощью текста в столбцы

Этот быстрый метод, использующий функцию Excel Text to Columns, может исправить некоторые числа, которые Excel не распознает как действительные числа.

Выберите ячейки, содержащие числа.
На вкладке "Данные" нажмите "Текст в столбцы".
Откроется диалоговое окно мастера преобразования текста в столбцы, в котором показан шаг 1.
На шаге 1 выберите "С разделителями" в качестве типа файла.
Затем нажмите кнопку "Готово".

В некоторых случаях это меняет текстовые числа на реальные числа.

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

Конвертировать валюту с разными разделителями

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

Например, если в данных есть числа, показанные в немецкой валюте — 987,654,32, — ваши настройки Excel могут не распознать их как настоящие числа из-за разделителей в числах.

В немецкой валюте в качестве разделителя тысяч используется точка, а в качестве десятичного разделителя – запятая.
В валюте США используется запятая в качестве разделителя тысяч и точка в качестве десятичного разделителя.

Попробуйте выполнить следующие действия, чтобы преобразовать данные из валюты Германии в валюту США – 987 654,32

Выберите ячейки, содержащие числа.
На вкладке "Данные" нажмите "Текст в столбцы".
Откроется диалоговое окно мастера преобразования текста в столбцы, в котором показан шаг 1.
Дважды нажмите кнопку "Далее" (шаг 1 и шаг 2).
На шаге 3 нажмите кнопку "Дополнительно".
В раскрывающемся списке "Десятичный разделитель" выберите разделитель, который в настоящее время используется в значениях — в этом примере "," (запятая).
В раскрывающемся списке "Разделитель тысяч" выберите разделитель, который в настоящее время используется в значениях, -- "." (точка) в этом примере
Нажмите "ОК", затем нажмите "Готово".

Преобразование знаков минус в конце

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

Текст в столбцы

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

Выберите ячейки, содержащие числа.
Выберите «Данные»> «Текст в столбцы».
Чтобы просмотреть параметр Trailing Minus, нажмите "Далее", затем нажмите "Далее".
На шаге 3 нажмите кнопку "Дополнительно".
Установите флажок "Минус в конце для отрицательных чисел", нажмите "ОК".
Нажмите "Готово".

Примечание. Если установлен флажок "Конечный минус для отрицательных чисел", вы можете нажать "Готово" на шаге 1 мастера "Текст в столбцы".

Преобразование знака минус в конце — формула

Спасибо Бобу Райану из компании Simply Learning Excel, который прислал эту формулу для исправления импортированных чисел со знаками минус в конце.

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

В этом примере первое число со знаком минус в конце находится в ячейке A1.
Выберите ячейку B1 и введите следующую формулу:
=ЕСЛИ(ПРАВО(A1,1)="-",-ЗНАЧ(ЛЕВО(A1,ДЛСТР(A1)-1)),ЗНАЧ(A1))
Скопируйте формулу до последней строки данных.

В формуле функция ПРАВИЛЬНО возвращает последний символ в ячейке A1.

Если этот символ является знаком минус, функция ЗНАЧ возвращает числовое значение слева от завершающего знака минус.

Знак минус перед функцией ЗНАЧ меняет значение на отрицательное.

Программное преобразование завершающих знаков минус

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

Вставить как CSV

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

Скопируйте данные в другую программу
Переключиться на Excel
Нажмите правой кнопкой мыши ячейку, с которой начнется вставка, и выберите "Специальная вставка".
Выберите «Правка»> «Специальная вставка».
Выберите CSV, если он есть в списке, и нажмите "ОК".

Расшифровка: исправление чисел, которые не складываются

Вот полная расшифровка показанного выше видеоролика «Исправить номера, которые не добавляются».

Если вы импортируете или копируете данные в Excel, например выписку по счету, иногда цифры складываются неправильно. Посмотрим, как это исправить.

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

Чтобы создать итог, я могу перейти на вкладку "Главная" и справа нажать "Автосумма". И обычно это выбирает любые числа выше, но в этом случае это не так, поэтому я вручную выбираю их и нажимаю Enter, и он показывает ноль. Так что, несмотря на то, что у меня есть сотни долларов, он показывает ноль.

Проверить содержимое ячейки

Я добавлю на этот лист еще пару функций и просто узнаю, что происходит в этих ячейках.

В этой ячейке я собираюсь подсчитать все, что находится в этих ячейках, будь то текст или числа.

Здесь я буду использовать открывающую скобку COUNTA = COUNTA. Затем я выберу ячейки с номерами, закрою квадратные скобки и нажму Enter.

В этих четырех ячейках что-то есть. Но сколько из них имеют номера? Для этого мы используем COUNTA.

В этой ячейке я использую СЧЁТ, и он подсчитывает только числа. Таким образом, COUNT открывает квадратную скобку, снова выберите четыре ячейки, закройте скобку и нажмите Enter.

У нас есть четыре ячейки, в которых что-то есть, но ни в одной из этих ячеек нет чисел.

Проверить панель формул

Если я посмотрю на одну из этих ячеек и посмотрю на строку формул,

Я вижу номер, но перед номером стоит апостроф, который указывает, что это текст, а не число.

Итак, все, что мы откуда-то скачали или скопировали, пришло в виде текста.

Исправьте цифры

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

Я выберу пустую ячейку и скопирую. Затем выберите ячейки, которые я хочу исправить, и перейдите к «Вставить», щелкните стрелку раскрывающегося списка и перейдите к «Специальная вставка». А здесь я хочу добавить, поэтому я выберу это. Нажмите "ОК".

А теперь все они заменены числами, и мы можем видеть итог внизу.

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

Нажмите здесь, чтобы вернуться к показанному выше видеоролику «Исправить номера, которые не добавляются»

Получить образец файла

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

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