Как суммировать ячейки, разделённые текстом и числами, в Excel?
Если у вас есть список ячеек, содержащих как Разделить по тексту и числу, так и числа, суммирование только числовых значений может быть затруднительным. Например, вы можете захотеть рассчитать итоги на основе определённых текстовых критериев или просто проигнорировать текст и просуммировать числа. В этом руководстве представлены различные способы выполнения данной задачи в Excel.
Суммирование ячеек, содержащих Разделить по тексту и числу, на основе определённого текста с помощью формулы
Вы можете использовать формулу массива, чтобы суммировать числа на основе соответствующей текстовой строки в ячейке. Выполните следующие действия:
1.Сначала введите текстовые строки, для которых нужно просуммировать соответствующие числа, в ячейки одного столбца.
2. Введите приведённую ниже формулу в пустую ячейку — в данном примере это ячейка E2. После этого одновременно нажмите клавиши Ctrl + Shift + Enter, и вы получите итоговое значение из списка на основе заданного текста. См. снимок экрана:
Примечание: в приведённой выше формуле D2 — это критерий, который вы хотите использовать, а B2:B9 указывает диапазон ячеек для суммирования.
3. Затем выделите ячейку с формулой и перетащите маркер заполнения вниз до тех ячеек, к которым нужно применить эту формулу, чтобы получить результат:
Суммирование ячеек, содержащих Разделить по тексту и числу, на основе определённого текста с помощью удобной функции
Если приведённая выше формула кажется вам сложной для понимания, Kutools для Excel предлагает удобный инструмент — Сумма на основе идентичного текста, с помощью которого вы сможете получить итоговый результат из ячейки, содержащей смесь чисел и текста, не запоминая ни одной формулы.
1. Щелкните ячейку, в которую нужно вывести результат. См. снимок экрана:
2. Затем последовательно выберите Kutools > Помощник формул > Помощник формул. См. снимок экрана:
3. В диалоговом окне Помощник формул выполните следующие действия:
- Выберите Статистическиеиз раскрывающегося списка Тип формулы;
- В списке Выберите формулущёлкните, чтобы выбрать опцию Сумма на основе идентичного текста;
- Затем в разделе Ввод аргументавыберите диапазон ячеек, содержащих Разделить по тексту и числу, которые необходимо просуммировать, в поле Диапазон, а затем выберите ячейку с текстом, на основе которой следует суммировать значения, в поле Текст.Совет: в поле Текстзамените стандартную абсолютную ссылку на относительную, чтобы правильно протянуть формулу.
4. Затем нажмите кнопку ОК, и будет рассчитан первый результат. После этого перетащите маркер заполнения, чтобы применить формулу к другим ячейкам. См. снимок экрана:
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Суммирование ячеек, содержащих Разделить по тексту и числу, игнорируя текст, с помощью пользовательской функции
Если у вас есть данные, содержащие смесь текста и чисел, и вы хотите быстро извлечь и суммировать только числа из каждой ячейки в Excel, как это сделать?
Чтобы решить эту задачу, создайте пользовательскую функцию (UDF), выполнив следующие шаги:
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 есть мощная функция СУММПРОИЗВ (SUMPRODUCT), которая мгновенно умножает два столбца и суммирует полученные произведения. В этой статье я покажу, как эффективно использовать эту функцию.
- Суммирование первых 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 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек