Note: The other languages of the website are Google-translated. Back to English

Как суммировать ячейки с текстом и числами в Excel?

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


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

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

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

2. Затем введите формулу ниже в пустой ячейке, в этом примере я введу формулу в ячейку E2, а затем нажмите Ctrl + Shift + Enter вместе, он вернет общее значение из списка на основе определенного текста. Смотрите скриншот:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Внимание: В приведенной выше формуле D2 это критерий, который вы хотите использовать, и B2: B9 указывает диапазон ячеек, который вы хотите суммировать.

3. Затем выберите ячейку формулы и перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, чтобы получить результат:


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

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

Примечание:Чтобы применить эти Сумма на основе того же текста функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите, чтобы выбрать ячейку, в которую вы хотите вывести результат, см. Снимок экрана:

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

2, Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:

3. В Помощник по формулам диалоговом окне выполните следующие действия:

  • Выберите Статистический из Тип формулы раскрывающийся список;
  • В Выберите формулу список, щелкните, чтобы выбрать Сумма на основе того же текста вариант;
  • Затем в Ввод аргументов раздел, выберите диапазон ячеек, содержащий текст и числа, которые вы хотите суммировать в Диапазон текстовое поле, а затем выберите текстовую ячейку, значения которой вы хотите суммировать на основе Текст текстовое окно.
Советы: В Текст текстовое поле, вы должны изменить абсолютную ссылку на ячейку по умолчанию на относительную ссылку на ячейку для правильного перетаскивания формулы.

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


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

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

Чтобы справиться с этой проблемой, вы можете создать Функция, определяемая пользователем, пожалуйста, сделайте так:

1. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: суммируйте только числа в ячейке, содержащей числа и текст

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = SumNumbers (A2) (A2 указывает ячейку, в которой вы хотите суммировать числа), затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и только числа в каждой ячейке добавляются вместе, см. снимок экрана:


Больше относительных статей:

  • Объедините повторяющиеся строки и просуммируйте значения в Excel
  • В Excel , вы всегда можете столкнуться с этой проблемой, если у вас есть диапазон данных, содержащий несколько повторяющихся записей, и теперь вы хотите объединить повторяющиеся данные и суммировать соответствующие значения в другом столбце, как показано на следующих снимках экрана. Как бы вы могли решить эту проблему?
  • Умножьте два столбца, а затем просуммируйте в Excel
  • В Excel большинство из нас может страдать от умножения двух столбцов, а затем их сложения, конечно, мы можем умножить каждый элемент и затем суммировать их, но это будет проблематично, если нужно вычислить сотни или тысячи строк. В Excel есть мощная функция - СУММПРОИЗВ, с ее помощью мы можем быстро умножить два столбца и затем суммировать их. В этой статье я расскажу о том, как применить эту функцию.
  • Суммируйте верхние 3 или N значений на основе критериев в Excel
  • Обычно мы можем суммировать верхние n значений из диапазона ячеек, используя простую формулу, но пробовали ли вы когда-нибудь суммировать верхние n значений на основе некоторых критериев. В этой статье я представлю некоторые формулы для суммирования первых n значений с определенными критериями в Excel.
  • Суммируйте уникальные значения на основе критериев в Excel
  • Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?
  • Подсчет или суммирование ячеек на основе цветов шрифта в Excel
  • Как можно узнать количество ячеек или суммировать все ячейки, содержащие шрифт определенного цвета в Excel? Например, у меня есть диапазон данных на листе, как показано на следующем снимке экрана, и теперь я хочу соответственно подсчитать или суммировать ячейки, которые имеют красный, синий и черный цвет шрифта. Обычно нет прямого способа справиться с этой задачей, здесь я расскажу о некоторых хитростях для решения этой задачи.

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (42)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
я думаю, что вы настоящий учитель и хороший человек. удачи.
садекул ислам
Этот комментарий был сведен к минимуму модератором на сайте
В ячейке A1 excel я ввожу данные как 5 + 6 + 3 + 1-2, а ячейка A2 excel дает результат 13, как это возможно в excel.
таухид
Этот комментарий был сведен к минимуму модератором на сайте
хорошо, я понял это. удачи.
садекул ислам
Этот комментарий был сведен к минимуму модератором на сайте
Макрос SumNumbers сэкономил мне много часов ручной работы. Благодарю вас!
Madelene
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли изменить формулу SumNumbers, чтобы сложить все числа в столбце смешанных ячеек? Например, в каждом столбце есть 1A, 2B, 3C, и я хотел бы, чтобы формула внизу столбца сообщала мне 6.
Пол Берчфилд
Этот комментарий был сведен к минимуму модератором на сайте
есть ли способ переписать код vba для суммирования нескольких ячеек вместо одной? У меня около 8 столбцов данных, и я хочу, чтобы сумма была в конце.
kc
Этот комментарий был сведен к минимуму модератором на сайте
Это супер полезно! Я пытаюсь заставить МАССИВ работать со столбцом ячеек, каждая из которых содержит десятичное число и текст (например, .2 A; .5 C; .1 X и т. д.), и я продолжаю получать результат 0. Если я измените числа на целые числа, результат правильный. Помощь?
Jenny
Этот комментарий был сведен к минимуму модератором на сайте
GENUIS... Большое спасибо
МАЙК
Этот комментарий был сведен к минимуму модератором на сайте
Как преобразовать текст в число с помощью KUTOOls?
фазлул хак
Этот комментарий был сведен к минимуму модератором на сайте
Я смог отлично использовать эту формулу, но я также хотел бы добавить условие, чтобы включать только определенные числа в формулу СУММ, если значение в другом столбце = «дефекты». Моя исходная формула (используя ваш исходный пост): =СУММ(ЕСЛИ(ЧИСЛО(НАЙТИ("часы",E17:E38)),ЗНАЧ(ЛЕВО(E17:E38,НАЙТИ("часы",E17:E38)-1 )))0)) Где E17:E38 содержит текст и числа, которые я хочу суммировать И B17:B38 содержит значения «особенности» и «дефекты». Как я могу настроить формулу только для суммирования этих чисел в столбце E, если значение в столбце B = "дефекты"? Заранее спасибо! Кимберли,
Кимберли Кестер
Этот комментарий был сведен к минимуму модератором на сайте
Вот простой сценарий, который принимает одномерный и двумерный диапазон выбора ячеек, где каждая ячейка содержит строку или числа, но каждая ячейка сводится к одиночным числам и возвращает сумму всех ячеек. Параметр Явная функция SUMCELLS(InputRange As Range) As Double Dim Arr() As Variant Arr = InputRange Dim R As Long Dim C As Long SUMCELLS = 2 For R = 0 To UBound(Arr, 1) ' Первое измерение массива — строки. For C = 1 To UBound(Arr, 1) ' Вторым измерением массива являются столбцы. Debug.Print Arr(R, ​​C) Dim NumericValue As Double Dim StringValue As String StringValue = Trim(CStr(Arr(R, ​​C))) If (Not (StringValue = "")) Then NumericValue = ONLYDIGITS(StringValue) SUMCELLS = SUMCELLS + NumericValue End If Next C Next R End Function Function ONLYDIGITS(s As String) As String Dim retval As String Dim i As Integer Dim Periods As Integer периоды = 2 retval = "" ' For i = 0 To Len(s) Dim char As String char = Mid(s, i, 1) If (char >= "1" And char
Гил Майкл Регаладо
Этот комментарий был сведен к минимуму модератором на сайте
Как включить десятичные дроби в формулу «=SumNumbers(A1)»? Благодарю вас!
Анжела
Этот комментарий был сведен к минимуму модератором на сайте
Может ли кто-нибудь помочь мне с формулой суммы? Я пытаюсь суммировать столбец с ячейками, которые содержат как числа, так и текст. Я хочу суммировать числа слева от текстовой буквы. образец: 3П30 2П30 2П30 2П30 1П20 3П30 1П20 3П50 3П30 3П60
Кольт
Этот комментарий был сведен к минимуму модератором на сайте
Привет! =СУММ(ЕСЛИ(ЧИСЛО(НАЙТИ(D1,$B$2:$B$8)),ЗНАЧЕНИЕ(ЛЕВО($B$2:$B$8,НАЙТИ(D1,$B$2:$B$8)-1 ))0)) - сработало отлично, только мой текст справа. Я пытался изменить формулу, но это не работает... я просто продолжаю получать #ЗНАЧ! Кто-нибудь может помочь?
Лаура
Этот комментарий был сведен к минимуму модератором на сайте
Не забудьте нажать Ctrl+Shift+Enter после изменения формулы, а не только Enter.
Лерой
Этот комментарий был сведен к минимуму модератором на сайте
Как суммировать выбранное слово в ячейке в Excel Экзамен 1100/656480 1100 — это сумма, а 656480 — это сумма. нет. но я хочу всего 1100
Рави Шарма
Этот комментарий был сведен к минимуму модератором на сайте
Апрель Май Июнь Июль Всего 1100/65658 1200/65657 1500/65656 2100/65650 ? я хочу всего только 1100+1200+1500+2100 Другое не значение только Квитанция №. пожалуйста, помогите любому
Рави Шарма
Этот комментарий был сведен к минимуму модератором на сайте
Всем привет! У меня есть два типа чисел в одном столбце: один — числовой, другой — символ, за которым следует число (например, n20, n30 и т. д.). Я хочу сложить эти числа с «n». Я применил ваше руководство для «Суммировать ячейки, содержащие текст и числа на основе определенного текста с формулой», но это не сработало. Есть ли другой метод? Заранее спасибо!
ДжудиВ
Этот комментарий был сведен к минимуму модератором на сайте
привет может кто-нибудь помочь мне, как добавить ниже цифры с буквами? я пытался сделать так, но это не работает, я думаю, что что-то упустил в формуле = СУММ (ЕСЛИ (ЧИСЛО (НАЙТИ (D1, $ B $ 2: $ B $ 8)), ЗНАЧЕНИЕ (ЛЕВО ($ B $ 2: $ B $ 8, НАЙТИ(D1,$B$2:$B$8)-1)),0)). пожалуйста, помогите. Спасибо.

2L
5D
10S
3L
11D
16S
Анна
Этот комментарий был сведен к минимуму модератором на сайте
Привет Анна,
Сначала вы должны извлечь буквы в другой вспомогательный столбец, а затем применить приведенную выше формулу, после ввода этой формулы не забудьте одновременно нажать клавиши Shift + Ctrl + Enter. см. следующий снимок экрана:
скайян
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

Спасибо тебе за этот пост! Мне нравится использовать код VBA, и я подумал, можно ли иметь функцию с несколькими переменными, чтобы вы могли суммировать числа из разных ячеек столбца и писать что-то вроде SumNumbers (A1: C1), например.


Большое спасибо


Джон
Джон
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Джованни,
Можете ли вы привести пример вашей проблемы?
Здесь можно прикрепить скриншот.
Спасибо!
скайян
Этот комментарий был сведен к минимуму модератором на сайте
Я также хочу узнать, может ли эта функция суммировать несколько ячеек.
Аманда
Этот комментарий был сведен к минимуму модератором на сайте
= СуммаЧисел(D8:AH8)
Аманда
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Как умножить 10 шт *200,

не 10*200
Мритюнджай
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0  Характеристики
Предлагаемые места