Как подсчитать или суммировать ячейки, содержащие текст, в Excel — Очень просто
Приложения
0 2.801 3 минут на чтение
В настоящее время мы можем использовать Microsoft Office в Интернете , что очень полезно. В основном для людей, которые используют такие программы, как Excel, поскольку он является одним из лучших с точки зрения электронных таблиц и многого другого.
В приложении вы видите, что рабочая область состоит из сеток, и в каждой из них вы можете работать, вставляя цифры, буквы, и даже можно применять стили или формат в таблице или ячейке в Excel или создать ежедневник .
Поэтому важно отметить, что каждое пространство в сетке называется ячейкой. Таким образом, будет легко ввести данные в каждую ячейку и, следовательно, можно будет добавить их. В этой статье мы покажем вам, как считать или добавить ячейки, содержащие текст в Excel.
Excel: легко подсчитать ячейки, содержащие текст
Есть формулы для определения, подсчитать или добавить значения, содержащие текст . Таким образом, ячейки, содержащие числа и пробелы, могут быть исключены из выполняемой работы. Под рабочей областью может быть одна или две ячейки, например, если вы работаете из ячеек с A1 по A5 и с D1 по D4, вы можете найти в ячейке A7 и поместить формулу.
Поэтому сначала мы должны написать: «= СЧЁТЕСЛИ» чтобы отобразились параметры, затем нажмите «СЧЁТЕСЛИ» в появившемся меню. Выбрав его, вы увидите, как формула автоматически помечается в ячейке, в которой вы находитесь: «= СЧЁТЕСЛИ (».
При просмотре формулы можно выбрать весь диапазон ячеек, в котором вы работали. Таким образом, формула будет полной и серьезной: «= СЧЁТЕСЛИ (A1: D5;» * »)» . Затем нажмите клавишу ВВОД, и вы увидите, что в ячейке, где формула, было число по количеству ячеек с текстом в выбранном диапазоне.
Отображаемый результат зависит от количества ячеек, содержащих текст , потому что ячейки с номерами не будут учитываться при подсчете. Важно отметить, что D5 помещается в формулу, потому что было указано, что до номера 5 в ячейке A есть ячейки с проводками. Хотя в поле D буква достигает ячейки 4; следовательно, ячейка D5 пуста.
Легко работать в Excel с помощью формулы add if
В Excel также можно добавлять ячейки, содержащие текст, а также содержащие ключевое слово. Например, у вас есть список слов с соответствующим числом результатов, но вам нужно только подсчитывать слова, содержащие определенные буквы , чтобы определить, сколько продуктов с таким названием.
Итак, словцо – ключ может состоять из трех букв содержащее слово вроде: «муравей» в случае, если слово: брюки. Следовательно, наличие ключевого слова будет подсчитывать количество ячеек, которые его содержат, независимо от того, находится ли слово, шорты или брюки в рабочей области, все ячейки, содержащие указанные буквы, будут расположены.
Конкретные ячейки рабочей области должны быть включены в формулу, поэтому, если список слов лежит клетка От A2 до A10 и ячейки B2 — B10 список по номеру, формула будет выглядеть так: = СУММАРИЕСЛИ (A2: A10; «* Муравей *»; B2: B10) .
Число равно общей сумме числа чисел или статей, содержащих ключевое слово. Итак, между штанами и шортами 2500 единиц. Этот способ можно использовать в магазинах , среди прочих, которые хотят отслеживать свои продукты. Поскольку они могут получить быстрый результат, просто добавив количество ячеек, содержащих одно и то же слово.
Короче говоря, Excel — полезный инструмент, который может легко помочь людям добавлять или подсчитывать ячейки, содержащие текст или ключевые слова. Следовательно, это настольное приложение, которое в основном используется в офисных рабочих местах, среди прочего.
report this adПодобные предметы
Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
Главная » Уроки MS Excel » Подсчет и суммирование
В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.
Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:
Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:
Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:
Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.
В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:
Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).
Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:
Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:
Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:
Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:
Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.
СУММЕСЛИ — условная сумма в Excel
Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий.
Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.
В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:
Меняем условие, меняется и результат:
Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.
Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?
На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.
В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:
Нажав Enter получим результат:
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!
Оцените качество статьи. Нам важно ваше мнение:
Как суммировать ячейки с текстом и числами в Excel
Если вы ищете ячеек Excel SUM с текстом и числами , то вы находитесь в правильном месте.
Скачать практическую рабочую тетрадь
3 способа суммирования ячеек с текстом и числами в Excel
1. Использование функции ПОДСТАВИТЬ для суммирования ячеек с текстом и числами
2. Использование функций LEFT и FIND
3. Применение комбинированной формулы
Как суммировать ячейки с текстом в Excel
Вывод
Дальнейшие чтения
Скачать рабочую тетрадь
3 способа суммирования ячеек с текстом и числами в Excel
Excel предлагает различные способы суммирования ячеек с текстом и числами. Все они очень просты в использовании.
1. Использование функции ПОДСТАВИТЬ для суммирования ячеек с текстом и числами
Формула, которую мы использовали здесь, использует две функции Excel: функцию ПОДСТАВИТЬ

В итоге синтаксис функции ПОДСТАВИТЬ выглядит следующим образом:
=ЗАМЕНИТЬ (текст, старый_текст, новый_текст, [экземпляр])
текст: Ячейка или ячейки для изменения.
old_text: Старый текст для замены.
new_text: Новый текст для замены.
Экземпляр [Необязательно]: Экземпляр для замены. По умолчанию все экземпляры будут заменены.
Вы можете подробно узнать об этой функции, прочитав эту документацию от Microsoft.
Важно отметить, что синтаксис функции SUM следующий:
=СУММ(число1, [число2] ,…)
Функция СУММ принимает 3 типов входных данных: положительных или отрицательных числовых значения , диапазон и ссылки на ячейки . Он принимает эти входные данные и показывает их суммирование в качестве выходных данных. Первый аргумент является обязательным, остальные необязательны, и он занимает до 255 чисел.
Здесь нам нужно рассчитать Общий вес Продуктов в ячейке D10 путем добавления отдельных весов столбца D.
Во-первых, напишите формулу в ячейке D10 вот так.
=СУММ(ПОДСТАВИТЬ(D5:D9, "кг", "")+0)
Здесь D5:D9 относится к Весам из Продуктов .
Во-вторых, нажмите ENTER .
В конце концов, мы получим вывод как 82 .
Расшифровка формулы:
{=СУММ(ПОДСТАВИТЬ(D9:D13, «кг», «»)+0)}
- СУММ(ЗАМЕНИТЬ(«25 кг, 10 кг, 10 кг, 7 кг, 30, кг» , «кг», «»)+0)
- СУММ(«25, 10, 10, 7, 30» +0)
[Заменено « кг » пустым текстом. ]
- СУММА(25, 10, 10, 7, 30)
[После замены « кг » Excel по-прежнему воспринимал его как текст. Добавив Ноль ( 0 ), значения были изменены на числовые.]
- 82
[Функция СУММ добавила значения и вернула желаемое суммирование.]
Подробнее: Все простые способы сложения (суммирования) столбца в Excel
Аналогичные показания
- Как добавить строки в Excel с помощью формулы (5 способов)
- [Исправлено!] Формула СУММА Excel не работает и возвращает 0 (3 решения)
- Как суммировать несколько строк и столбцов в Excel
- Суммирование диапазона ячеек в строке с помощью Excel VBA (6 простых методов)
- Как суммировать выбранные ячейки в Excel (4 простых метода)
2.

Формула, которую мы использовали здесь, использует четыре функции Excel: функцию
В итоге синтаксис функции НАЙТИ выглядит следующим образом:
Эта функция используется для поиска определенной текстовой строки в текстовой строке.
= НАЙТИ (найти_текст, внутри_текста, [начальный_номер])
find_text: Текст, который нужно найти.
внутри_текста: Текст для поиска.
начальный_номер [Необязательно]: Начальная позиция поиска. По умолчанию start_num=1 . Первый символ в в пределах_текста — это символ номер 1.
Подробно узнать об этой функции можно, прочитав эту документацию от Microsoft.
Однако синтаксис функции LEFT следующий:
Эта функция используется для извлечения символов из текста.
=ЛЕВО (текст, [количество_знаков])
текст : Текстовая строка, из которой нужно извлечь символы.
num_chars [Необязательно]: количество символов для извлечения. Он начинается слева. По умолчанию num_chars=1 .
Синтаксис функции ЗНАЧЕНИЕ следующий:
Эта функция преобразует тексты в числовые значения.
=ЗНАЧЕНИЕ (текст)
текст: Текст, который необходимо преобразовать в числа.
Кроме того, вы можете ознакомиться с разделом функций первого метода SUM для понимания функции. Там это объясняется.
Здесь нам также необходимо рассчитать общий вес из продуктов в ячейке D10 путем сложения отдельных весов столбца D .
Во-первых, напишите формулу в ячейке D10 вот так.
=СУММ(ЗНАЧ(ЛЕВО(D5:D9,НАЙТИ(" ",D5:D9))-1)))
Во-вторых, нажмите ENTER и получите вывод как 82 .
Расшифровка формулы:
{=СУММ(ЗНАЧЕНИЕ(ЛЕВО(D9:D13,НАЙТИ(» «,D9:D13)-1)))} «25 кг, 10 кг, 10 кг, 7 кг, 30 кг»)-1)))
[Функция ПОИСК вернул позиции пробелов в каждой ячейке.]
- СУММА(ЗНАЧЕНИЕ(ЛЕВО(D9:D13,(2, 2, 2, 1, 1))))
[Поскольку каждое число имеет пробел в конце, вычитание единицы из его местоположения дает цифровое значение каждой ячейки.]
- СУММА(ЗНАЧЕНИЕ(ЛЕВО(«25 кг, 10 кг, 10 кг, 7 кг, 30 кг»,(2, 2, 2, 1, 1))))
- СУММА(ЗНАЧ(25, 10, 10, 7, 30))
[Функция LEFT извлекает числовые символы из текста, используя числа цифр каждой ячейки. ]
- СУММА(25, 10, 10, 7, 30)
[Функция VALUE преобразовала числовые символы в числовые значения.]
- 82
[Функция СУММ добавила значения и вернула желаемое суммирование.]
Подробнее: Как складывать числа в Excel (2 простых способа)
3. Применение комбинированной формулы
Мы можем суммировать ячейки с текстом и числами, используя комбинацию различных функций. Мы можем применить комбинацию СУММ , ЕСЛИ , IНОМЕР , НАЙТИ, и ЛЕВЫЙ функции. Предположим, у нас есть набор данных ниже, который имеет заголовки столбцов как Номер файла и Размер в столбцах B и C соответственно. Размеры файлов указаны в 3 разных масштабах КБ , МБ и ГБ . Нам нужно вычислить общее количество КБ , МБ, и ГБ в Столбец F с заголовком столбца Итого .
Во-первых, напишите следующую формулу в ячейке F5 вот так.
=СУММ(ЕСЛИ(ЧИСЛО(НАЙТИ(E5,$C$5:$C$15)),
ЗНАЧЕНИЕ(ЛЕВО($C$5:$C$15,НАЙТИ(E5,$C$5) :$C$15)-1)),0)
)
Здесь C5:C15 относится к ячейкам заголовка столбца Размер и E5 относится к КБ в заголовке столбца Единица .
Во-вторых, нажмите ENTER .
В-третьих, используйте маркер заполнения , перетащив курсор вниз, удерживая правый нижний угол ячейки F5 .
В итоге мы получим КБ , МБ, и ГБ в качестве выходных данных.
Как суммировать ячейки с текстом в Excel
Мы можем очень легко суммировать ячейки с текстом в Excel. Мы можем использовать функция СЧЁТЕСЛИ в этом случае вычислить легко. Предположим, у нас есть следующий набор данных с заголовками столбцов Company Name и Branch . Нам нужно найти сумму общих ветвей в столбце F с заголовком столбца Count .
Сначала напишите следующую формулу в ячейке F5 вот так.
=СЧЁТЕСЛИ($C$5:$C$15,E5)
Здесь, C5:C15 относится к ячейкам с заголовками столбцов как Branch , а E5 относится к USA .
Во-вторых, нажмите ENTER
Наконец, используйте ручку заполнения , чтобы получить результат Count различных ветвей.
Заключение
Вот и все о сегодняшней сессии. А это способы суммирования ячеек с текстом и числами в Excel. Мы твердо верим, что эта статья будет очень полезна для вас. Не забудьте поделиться своими мыслями и вопросами в разделе комментариев и изучить наш сайт ExcelDemy , универсальный поставщик решений для Excel.
Дополнительная литература
- Как суммировать только видимые ячейки в Excel (4 быстрых способа)
- Сумма, если ячейка содержит текст в Excel (6 подходящих формул)
- Как суммировать только положительные числа в Excel (4 простых способа)
- Сумма значений по дням в Excel (6 методов)
- Формула суммирования двух чисел в Excel
- Сумма, если ячейка содержит определенный текст в Excel (6 способов)
Как суммировать только числа и игнорировать текст в одной ячейке в Excel
Иногда нам требуется суммировать только числа из длинной текстовой строки. Мы можем выполнить такую работу, комбинируя наши существующие функции несколькими способами. В этой статье мы покажем вам шесть простых способов суммирования только чисел и игнорирования текста в одной и той же ячейке в Excel. Если вам это тоже интересно, загрузите нашу рабочую тетрадь и следуйте за нами.
Скачать практическую рабочую тетрадь
6 простых способов суммировать только числа и игнорировать текст в одной ячейке в Excel
1. Использование функций СУММ, ЛЕВЫЙ и НАЙТИ
2. Использование функций СУММПРОИЗВ, ЛЕВЫЙ и НАЙТИ
3. Применение функций TRIM и IFERROR
4. Сочетание функций СУММ, ЕСЛИОШИБКА и ПРАВИЛЬНО
5. Суммирование только чисел на основе категорий
6. Встраивание кода VBA
Вывод
Статьи по Теме
Скачать рабочую тетрадь
Загрузите это учебное пособие для практики, пока вы читаете эту статью.
6 простых способов суммировать только числа и игнорировать текст в одной ячейке в Excel
Чтобы продемонстрировать подход, мы рассмотрим набор данных, где мы вводим 10 имена сотрудников и их зарплаты в одну и ту же ячейку. Итак, наш набор данных находится в диапазоне ячеек B5:B14 . Мы покажем сумму чисел в ячейке D5 .
Если мы посчитаем число вручную в калькуляторе, то получим результат суммы 15830 . Итак, после выполнения каждого метода сумма будет 15 830 .
1. Использование функций СУММ, ЛЕВЫЙ и НАЙТИ
В нашем первом методе мы будем использовать функции СУММ , ЛЕВЫЙ и НАЙТИ , чтобы получить сумму только чисел и игнорировать текст в той же ячейке. Шаги этого метода приведены ниже:
📌 Шаги:
- Прежде всего, выберите ячейку D5 .
- Теперь запишите в ячейку следующую формулу.
=СУММ(--ЛЕВЫЙ(B5:B14,НАЙТИ(" ",B5:B14)))
- Нажмите Введите .
- Вы увидите, что сумма чисел соответствует нашему результату, рассчитанному вручную.
Таким образом, мы можем сказать, что наша формула работает отлично, и мы умеем суммировать только числа и игнорировать текст в той же ячейке Excel.
🔎 Разбивка формулы
Разбиваем формулу для ячейки D5 .
👉
НАЙТИ(» «,B5:B14) : Функция НАЙТИ ищет местоположение «Пробел» во всех 10 строках. Здесь функция возвращает 5 .
👉
ЛЕВЫЙ(B5:B14,НАЙТИ(» «,B5:B14)) : Функция ЛЕВЫЙ будет извлекать значения из строк. Количество символов будет равно результату Функция НАЙТИ .
👉
СУММ(–ЛЕВЫЙ(B5:B14,НАЙТИ(» «,B5:B14))) : Наконец, функция СУММ суммирует все числа, извлеченные функцией ЛЕВЫЙ , и отображает нам конечный результат. Здесь значение равно 15 830 .
Подробнее: Сумма, если ячейка содержит текст в Excel (6 подходящих формул)
2. Использование функций СУММПРОИЗВ, НАЛЕВО и НАЙТИ
В этом методе мы будем использовать СУММПРОИЗВ , ЛЕВЫЙ и НАЙТИ функции для получения суммы только чисел и игнорирования текста в той же ячейке. Шаги этого подхода приведены ниже:
📌 Шаги:
- Сначала выберите ячейку D5 .
- После этого запишите в ячейку следующую формулу.
=СУММПРОИЗВ(--ЛЕВЫЙ(B5:B14,НАЙТИ(" ",B5:B14)))
- Затем нажмите Введите .
- Вы получите сумму всех чисел, которая равна ручному результату.
Итак, мы можем сказать, что наша формула работает точно, и мы умеем суммировать только числа и игнорировать текст в той же ячейке в Excel.
🔎 Разбивка формулы
Разбиваем формулу для ячейки D5 .
👉
НАЙТИ(» «,B5:B14) : Функция НАЙТИ ищет местоположение «Пробел» во всех строках 10 . Здесь функция возвращает 5 .
👉
ЛЕВЫЙ(B5:B14,НАЙТИ(» «,B5:B14)) : Функция ЛЕВЫЙ будет извлекать значения из строк. Количество символов будет равно результату функции НАЙТИ .
👉
СУММПРОИЗВ(–ЛЕВО(B5:B14,НАЙТИ(» «,B5:B14))) : Наконец, функция СУММПРОИЗВ суммирует все числа, извлеченные LEFT и отобразите нам окончательный результат. Здесь значение равно 15 830 .
Подробнее: Как использовать СУММЕСЛИ с текстом в Excel (9 простых способов)
3. Применение функций TRIM и IFERROR
В этом подходе мы будем использовать функции SUM , TRIM , IFERROR , LEFT и FIND для получения суммы только чисел и игнорирования текста в той же ячейке. Шаги этого процесса приведены ниже:
📌 Шаги:
- Сначала выберите ячейку D5 .
- После этого запишите в ячейку следующую формулу.
=СУММ(--ОТРЕЗАТЬ(ЕСЛИОШИБКА(ЛЕВО(B5:B14,НАЙТИ(" ",B5:B14)),0)))
- Затем нажмите Введите .
- Наконец, вы получите сумму всех чисел, которая равна результату, полученному вручную.
Следовательно, мы можем сказать, что наша формула работает эффективно, и мы можем суммировать только числа и игнорировать текст в той же ячейке в Excel.
🔎 Разбивка формулы
Разбиваем формулу для ячейки D5 .
👉
НАЙТИ(» «,B5:B14) : Функция НАЙТИ ищет местоположение «Пробел» во всех 10 строках. Здесь функция возвращает 5 .
👉
ЛЕВЫЙ(B5:B14,НАЙТИ(» «,B5:B14)) : Функция ЛЕВЫЙ будет извлекать значения из строк. Количество символов будет равно результату Функция НАЙТИ .
👉
ЕСЛИОШИБКА(ЛЕВАЯ(B5:B14,НАЙТИ(» «,B5:B14)),0) : Функция ЕСЛИОШИБКА проверяет результат функции ЛЕВАЯ . Если есть значение, функция покажет его, иначе функция покажет ноль (0) .
👉
TRIM(IFERROR(LEFT(B5:B14,FIND(» «,B5:B14)),0)) : Функция TRIM удалит все ненужные символы из извлеченных строк.
👉
СУММ(-ОТРЕЗАТЬ(ЕСЛИОШИБКА(ЛЕВО(B5:B14,НАЙТИ(» «,B5:B14)),0))) : Наконец, функция СУММ суммирует все числа, полученные из Функция TRIM и отображает окончательный результат. Здесь значение равно 15 830 .
4.

В этом процессе функции СУММА , ЕСЛИОШИБКА и ПРАВО помогут нам получить сумму только чисел и игнорировать текст в той же ячейке. Для применения этой формулы числа должны оставаться после текста. Процедура этого процесса объясняется ниже шаг за шагом:
📌 Шаги:
- Во-первых, выберите ячейку D5 .
- Затем запишите в ячейку следующую формулу.
=СУММ(ЕСЛИОШИБКА(--ПРАВО(B5:B14,4),0))
- Нажмите Введите .
- Наконец, вы увидите сумму всех чисел, которая равна ручному результату.
Таким образом, можно сказать, что наша формула работает успешно, и мы умеем суммировать только числа и игнорировать текст в той же ячейке Excel.
🔎 Разбивка формулы
Разбиваем формулу для ячейки D5 .
👉
ПРАВО(B5:B14,4) : Функция ПРАВО извлекает последние четыре цифры строк. Здесь он возвращает нам зарплату сотрудников.
👉
ЕСЛИОШИБКА(–ПРАВО(B5:B14,4),0) : Функция ЕСЛИОШИБКА проверяет результат функции ПРАВИЛЬНО . Если есть значение, функция покажет его, иначе функция покажет ноль (0) .
👉
СУММ(ЕСЛИОШИБКА(–ПРАВО(B5:B14,4),0)) : Наконец, функция СУММ суммирует все числа, полученные из функции ЕСЛИОШИБКА , и отображает окончательный результат. Здесь значение равно 15 830 .
Подробнее: Как суммировать, если ячейка содержит определенный текст в Excel (6 способов)
5. Суммирование только чисел по категориям
В следующем методе мы будем суммировать только числа и игнорировать текст на основе некоторых критериев. Чтобы завершить процесс, мы используем SUM , IF , ISNUMBER , VALUE , LEFT и FIND функции для получения суммы только чисел и игнорирования текста в той же ячейке. Для применения этой комбинированной формулы мы должны использовать другой набор данных.
Шаги этого метода объясняются ниже:
📌 Шаги:
- В начале выберите ячейку F5 .
- Теперь запишите в ячейку следующую формулу.
=СУММ(ЕСЛИ(ЧИСЛО(НАЙТИ(E5,$B$5:$C$14)),ЗНАЧЕНИЕ(ЛЕВО($C$5:$C$14,НАЙТИ(E5,$C$5:$C$14) -1)),0))
- Затем нажмите Введите .
- После этого дважды щелкните на значке Fill Handle , чтобы скопировать формулу до ячейки F7 .
- Наконец, вы увидите сумму всех чисел, основанную на наших трех желаемых категориях.
Наконец-то мы можем сказать, что наша формула работает эффективно, и мы умеем суммировать только числа и игнорировать текст в той же ячейке Excel.
🔎 Разбивка формулы
Разбиваем формулу для ячейки F5 .
👉
НАЙТИ(E5,$C$5:$C$14) : Функция НАЙТИ проверяет значение E5 . Здесь значение равно 5 .
👉
НАЛЕВО($C$5:$C$14,НАЙТИ(E5,$C$5:$C$14)-1) : Функция НАЛЕВО извлечет числа из строк. В этой ячейке функция вернет 250 .
👉
ЗНАЧЕНИЕ(ЛЕВОЕ($C$5:$C$14,НАЙТИ(E5,$C$5:$C$14)-1)) : Функция ЗНАЧЕНИЕ изменит символы из текста до Формат значения .
👉
ЧИСЛО(НАЙТИ(E5,$B$5:$C$14)) : Функция ЧИСЛО проверит значение Функция НАЙТИ . Здесь функция возвращает TURE .
👉
ЕСЛИ(ЧИСЛО(НАЙТИ(E5,$B$5:$C$14)),ЗНАЧЕНИЕ(ЛЕВО($C$5:$C$14,НАЙТИ(E5,$C$5:$C$14)-1 )),0) : эта функция возвращает 250 .
👉
СУММА(ЕСЛИ(ЧИСЛО(НАЙТИ(E5,$B$5:$C$14)),ЗНАЧЕНИЕ(ЛЕВО($C$5:$C$14,НАЙТИ(E5,$C$5:$C$14) -1)),0)) : Наконец, функция СУММ суммирует все числа, полученные из функции ЕСЛИ , и отображает окончательный результат. Здесь значение равно 265 .
Подробнее: Как суммировать ячейки с текстом и числами в Excel (2 простых способа)
6. Внедрение кода VBA
Написание кода VBA также может помочь вам суммировать только числа и игнорировать текст в той же ячейке. Шаги этого процесса приведены ниже:
📌 Шаги:
- Чтобы начать подход, перейдите на вкладку Developer и нажмите Visual Basic .
Если у вас его нет, вам нужно включить вкладку «Разработчик» . Или Вы также можете нажать ‘Alt+F11’ для открытия редактора Visual Basic .
- Появится диалоговое окно.
- Теперь на вкладке Вставить в этом поле выберите параметр Модуль .
- Затем запишите следующий визуальный код в это пустое поле редактора.
Функция sum_only_numbers(main_range As Range, необязательный str_delim As String = " ") As Double Dim data As Variant, long_num As Long Для каждого элемента в main_range данные = Разделить (элемент, str_delim) Для long_num = LBound(данные) To UBound(данные) Шаг 1 sum_only_numbers = sum_only_numbers + Val (данные (long_num)) Следующий long_num Следующий элемент Завершить функцию
- После этого нажмите «Ctrl+S» , чтобы сохранить код.
- Закройте вкладку Editor .
- После этого выберите ячейку D5 и запишите в ячейку следующую формулу.
=sum_only_numbers(B5:B14)
- Нажмите Введите .
- Таким образом, вы увидите сумму всех чисел, которая равна ручному результату.
Наконец, мы можем сказать, что наш визуальный код сработал успешно, и мы можем суммировать только числа и игнорировать текст в той же ячейке в Excel.
Подробнее: Как суммировать текстовые значения, подобные числам, в Excel (3 метода)
Заключение
Вот и конец этой статьи. Я надеюсь, что эта статья будет вам полезна, и вы сможете суммировать только числа и игнорировать текст в одной и той же ячейке Excel. Пожалуйста, поделитесь с нами любыми дополнительными вопросами или рекомендациями в разделе комментариев ниже, если у вас есть дополнительные вопросы или рекомендации.