Как суммировать ячейки с текстом и числами в Excel?
Если у вас есть список ячеек, содержащих как текст, так и числа, сложение только числовых значений может быть затруднительным. Например, вы можете захотеть рассчитать итоги на основе определенных текстовых критериев или просто игнорировать текст и суммировать числа. В этом руководстве мы познакомим вас с несколькими методами для достижения этого в Excel.
Суммирование ячеек, содержащих текст и числа, на основе определенного текста с помощью формулы
Вы можете использовать формулу массива для суммирования чисел на основе соответствующей текстовой строки внутри ячейки, выполните следующие действия:
1. Сначала запишите текстовые строки, по которым вы хотите суммировать относительные числа в столбце ячеек.
2. Затем введите следующую формулу в пустую ячейку; в этом примере я введу формулу в ячейку E2, а затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить общее значение из списка на основе определенного текста. Смотрите скриншот:
Примечание: В приведенной выше формуле D2 — это критерий, который вы хотите использовать, а B2:B9 указывает диапазон ячеек, которые вы хотите суммировать.
3. Затем выберите ячейку с формулой и перетащите маркер заполнения вниз до ячеек, куда вы хотите применить эту формулу, чтобы получить результат:
Суммирование ячеек, содержащих текст и числа, на основе определенного текста с помощью удобной функции
Если приведенная выше формула трудна для понимания, Kutools for Excel предоставляет полезный инструмент — Сумма на основе одинакового текста. С помощью этой функции вы можете получить общий результат в ячейке, смешанной с числами и текстом, без необходимости запоминать какие-либо формулы.
1. Щелкните, чтобы выбрать ячейку, куда вы хотите вывести результат, смотрите скриншот:
2. Затем нажмите Kutools > Помощник формул > Помощник формул, смотрите скриншот:
3. В диалоговом окне Помощник формул выполните следующие действия:
- Выберите Подсчет из выпадающего списка Тип формулы;
- В списке Выберите формулу щелкните, чтобы выбрать опцию Сумма на основе одинакового текста;
- Затем, в Ввод аргумента разделе выберите диапазон ячеек, содержащих текст и числа, которые вы хотите суммировать в Диапазон текстовое поле, а затем выберите текстовую ячейку, на основе которой вы хотите суммировать значения в Текст текстовом поле. Советы: В текстовом поле Текст вам следует изменить абсолютную ссылку на ячейку по умолчанию на относительную ссылку на ячейку для правильного перетаскивания формулы.
4. Затем нажмите кнопку ОК, первый результат будет рассчитан, затем перетащите маркер заполнения, чтобы распространить формулу на другие ячейки, смотрите скриншот:
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Суммирование ячеек, содержащих текст и числа, игнорируя текст с помощью пользовательской функции
Если у вас есть следующие данные, смешанные с текстом и числами, и вы хотите извлечь и добавить только числа в каждой ячейке. Как быстро выполнить это в Excel?
Для решения этой проблемы вы можете создать пользовательскую функцию. Следуйте этим шагам:
1. Удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
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
- Например, у меня есть диапазон данных, который содержит столбцы Имя и Заказ, и теперь мне нужно суммировать только уникальные значения в столбце Заказ на основе столбца Имя, как показано на следующем скриншоте. Как быстро и легко решить эту задачу в Excel?
- Подсчет или суммирование ячеек на основе цветов шрифтов в Excel
- Как можно получить количество ячеек или просуммировать все ячейки, содержащие определенный цвет шрифта в Excel? Например, у меня есть диапазон данных на листе, как показано на следующем скриншоте, и теперь я хочу соответственно подсчитать или суммировать ячейки, имеющие красный, синий и черный цвет шрифта. Обычно нет прямого способа справиться с этой задачей, здесь я расскажу о некоторых хитростях для ее решения.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!