Как правильно суммировать числа с единицами измерения в диапазоне Excel?
При работе с Excel вы можете столкнуться с ситуацией, когда нужно суммировать числа, содержащие единицы измерения — например, «5 кг», «12 фунт» или «10 м». Такие данные часто встречаются при учёте веса, длины или количества: в списках закупок, инженерных измерениях, лабораторных журналах и логистических отчётах, где каждое значение сопровождается обозначением единицы измерения. Однако стандартная функция СУММ Excel в этом случае не сработает, поскольку Excel воспринимает такие ячейки как текстовые строки, а не числовые значения. Из-за этого функция СУММ и другие базовые математические операции либо игнорируют их, либо возвращают ошибки. Чтобы точно просуммировать такие данные, необходимо сначала извлечь числовую часть, а уже затем выполнить расчёт. В этом руководстве описаны несколько практичных и эффективных способов решения задачи — от формул и встроенных функций до удобных надстроек, таких как Kutools для Excel, — которые помогут корректно суммировать числа с единицами измерения в самых разных реальных сценариях.
➤ Суммирование чисел с единицами измерения с помощью формулы
➤ Суммирование чисел с единицами измерения с помощью Kutools для Excel
➤ Суммирование чисел по одинаковым единицам измерения с помощью Kutools для Excel
➤ Объединение Дублирующая строка и суммирование соответствующих чисел с помощью Kutools для Excel
➤ Макрос VBA: автоматическое извлечение чисел из текстовых строк с единицами измерения и их суммирование
➤ Использование Power Query для извлечения и суммирования чисел с единицами измерения
Суммирование чисел с единицами измерения с помощью формулы
Когда все ваши значения выражены в одной и той же единице измерения и вам нужно быстро получить результат с помощью формулы, массивные формулы Excel помогут извлечь числовую часть и просуммировать её. Этот метод идеально подходит для диапазонов, где каждая ячейка содержит число, непосредственно за которым следует одинаковая единица измерения — например, «8 кг», «15 кг» и так далее.
Выберите пустую ячейку для результата и введите следующую формулу:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)После ввода формулы подтвердите её, одновременно нажав Ctrl + Shift + Enter (на некоторых системах это требуется для массивных формул), чтобы Excel распознал её как операцию с массивом. После этого в выбранной ячейке появится сумма числовых частей.
Совет: В приведённой выше формуле B2:B7 обозначает диапазон данных, содержащий значения с единицами измерения, которые нужно сложить, а «кг» — это единица измерения в ваших данных. Убедитесь, что все записи в диапазоне используют одну и ту же единицу измерения и не содержат лишних пробелов или различий в написании — иначе формула не сможет корректно извлечь значения. Если ваша единица измерения или диапазон отличаются, внесите соответствующие изменения. Эта формула не поддерживает случаи, когда в диапазоне присутствуют разные типы единиц измерения.
Суммирование чисел с единицами измерения с помощью Kutools для Excel
Для пользователей, которые предпочитают простой и наглядный подход или хотят избежать запоминания формул, Kutools для Excel предлагает прямой способ очистки числовых значений. С помощью функции Удалить определенные символы вы легко удалите нечисловые символы (например, единицы измерения) и сразу сможете суммировать числа. Это идеальное решение, если ваши данные содержат числа и единицы измерения в одном столбце, а вам нужно быстро подготовить их к расчётам.
1. Выделите ячейки, содержащие числа с единицами измерения. Перейдите на вкладку Kutools, нажмите Текст и выберите Удалить определенные символы. См. снимок экрана:
2. В диалоговом окне Удалить определенные символы установите флажок только напротив параметра Нечисловые. Эта настройка удаляет все символы, кроме цифр. См. снимок экрана:
3. Нажмите ОК или Применить. Теперь в выделенных ячейках остались только числовые значения, которые можно легко просуммировать с помощью простой формулы. Выберите пустую ячейку и введите приведённую ниже формулу, чтобы получить результат:
=SUM(B2:B7)На изображении ниже показано, как выглядят очищенный столбец и его сумма после выполнения этой процедуры.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Примечание: Kutools выполняет прямую замену в выбранных ячейках, поэтому рекомендуется работать с копией данных, если вам нужно сохранить оригинальный текст. Это решение лучше всего подходит для единиц измерения одного типа. После использования этой функции убедитесь, что все очищенные значения являются числами, прежде чем выполнять суммирование.
Суммирование чисел с одинаковыми единицами измерения с помощью Kutools для Excel и функции СУММ
Суммирование чисел по одинаковым единицам измерения с помощью Kutools для Excel
Если ваша таблица содержит значения в разных единицах измерения — например, вес в «кг» и «фунтах» — и вы хотите отдельно суммировать каждую из них (например, получить общий вес в килограммах и общий вес в фунтах), Kutools предлагает эффективное решение с помощью функции **Помощник формул**. Это особенно полезно при работе со сводными таблицами, составлении отчётов по запасам или подготовке данных для последующего сравнения.
Kutools для Excel — включает более 300 незаменимых инструментов для Excel. Работайте в Excel быстрее, проще и эффективнее.Скачать сейчас!
1. Выберите ячейку, в которой должен появиться итоговый результат. Перейдите в меню Kutools > Помощник формул > Статистические > Сумма по одинаковому тексту.
2. В диалоговом окне Помощник формул настройте расчёт: в разделе Ввод аргумента выберите весь диапазон данных в качестве Диапазона, а в поле Текст введите или выберите конкретную единицу измерения (например, «кг» или «фунт»). Затем нажмите ОК.
Совет: По умолчанию формула использует абсолютные ссылки (например, $B$2:$B$7). Если вы планируете автоматически заполнять эту функцию для нескольких единиц измерения или строк, вручную измените ссылки на относительные — по мере необходимости.
Теперь в ячейке результата отображается итоговая сумма для выбранной единицы измерения. Повторите эту операцию для каждой единицы, чтобы получить все групповые итоги.
Примечание: Этот метод отлично подходит для таблиц, где чётко указан тип единицы измерения: он автоматизирует суммирование по нескольким единицам измерения и минимизирует ошибки, возникающие при ручной группировке.
Объединение Дублирующая строка и суммирование соответствующих чисел с помощью Kutools для Excel
В некоторых наборах данных элементы или объекты встречаются более одного раза с указанием количества и единиц измерения — например, фрукты с весом, выраженным в разных единицах. Допустим, вы хотите суммировать значения, сгруппировав их по элементу и единице измерения: объединить дублирующиеся строки и свести итоговые значения в один результат. Для таких задач идеально подходит функция Kutools для Excel Расширенное объединение строк, специально разработанная для быстрой и простой агрегации.
1. Выделите таблицу, содержащую дублирующиеся элементы и смешанные единицы измерения, затем перейдите в меню Kutools > Объединить и разделить > Расширенное объединение строк.
2. В диалоговом окне Объединить строки на основе столбца сначала выберите столбец, содержащий единицы измерения, и отметьте его как Первичный ключ. Это позволит Kutools группировать данные по единицам измерения. См. снимок экрана:
3. Далее выделите столбец с числовыми значениями (например, «Вес»), выберите опцию Вычислить и укажите Сумма, чтобы значения суммировались внутри каждой группы.
4. Нажмите кнопку ОК, и ваша таблица автоматически свернётся по единицам измерения с объединением соответствующих числовых значений. Эта функция особенно полезна при обработке данных инвентаризации и продаж, где требуется агрегация нескольких записей по совпадающим единицам измерения.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Совет: Вы можете группировать данные сразу по нескольким ключам (например, по товару и единице измерения), чтобы добиться более глубокой консолидации.
Объединение дубликатов и суммирование чисел
Макрос VBA: автоматическое извлечение чисел из текстовых строк с единицами измерения и их суммирование
Опытным пользователям, стремящимся к полной автоматизации или работающим со сложными и разнообразными шаблонами строк с единицами измерения, макрос VBA позволяет за один проход извлечь и просуммировать нужные данные. Этот подход особенно эффективен, когда диапазон содержит числа с различными единицами измерения или несогласованными структурами данных, а встроенные формулы Excel оказываются слишком ограничивающими или громоздкими для настройки под каждый конкретный случай.
1. Откройте редактор VBA, нажав Разработчик > Visual Basic. В окне Microsoft Visual Basic for Applications выберите Вставка > Модуль, затем вставьте приведённый ниже код в открывшийся модуль:
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. Чтобы запустить код, щёлкните в любом месте макроса и нажмите клавишу F5 или кнопку 
«Выполнить» над кодом в редакторе. После этого появится диалоговое окно с запросом на выбор диапазона, содержащего числа с единицами измерения. Скрипт автоматически извлечёт начальное числовое значение из каждой текстовой ячейки, просуммирует все результаты и отобразит итоговую сумму во всплывающем окне.
Советы и примечания по использованию:
- Этот макрос извлекает числовые символы с начала каждой строки до первого нечислового знака — он корректно обрабатывает большинство форматов, таких как «5 кг», «12 фунт» или «20,5 м».
- Если формат чисел несогласован или числа встроены в строку, может потребоваться дополнительная настройка.
- При необходимости вы можете изменить код VBA, чтобы единицы измерения собирались или обрабатывались отдельно.
- Чтобы обезопасить исходные данные, всегда применяйте макросы к копии или резервной версии листа.
- Функциональность макросов должна быть включена в настройках Excel.
Это решение на основе VBA отличается универсальностью в работе с различными форматами данных и существенно экономит время при обработке больших объёмов информации.
Использование Power Query для извлечения и суммирования чисел с единицами измерения
Power Query — встроенное средство преобразования данных в Excel — идеально подходит для обработки крупных или сложных наборов данных, содержащих числа с единицами измерения.
Он особенно эффективен при несогласованной структуре данных или когда требуется автоматизировать повторяющиеся задачи.
1.Выделите диапазон с вашими данными (включая числа с единицами измерения), перейдите на вкладку Данные и нажмите кнопку Из таблицы/диапазона. Если появится запрос, убедитесь, что ваши данные содержат заголовки.
2. В редакторе Power Query выберите столбец с текстовыми значениями. Перейдите на вкладку Добавить столбец > Извлечь > Текст до разделителя или Текст между разделителями.
Выбор зависит от формата ваших данных:
- Используйте пробел в качестве разделителя, если данные имеют вид «12 кг»
- Используйте букву в качестве разделителя, если данные имеют вид «12кг»
3. При необходимости измените тип данных нового столбца на Десятичное число или Целое число, щёлкнув правой кнопкой мыши по заголовку столбца и выбрав пункт Изменить тип.
4. (Необязательно) Если вы хотите суммировать данные по единицам измерения, сначала отделите единицу измерения с помощью команды Извлечь текст после разделителя, затем примените команду Группировать по(на вкладке)Главная) и выберите операцию Сумма для нового числового поля.
5. Нажмите кнопку Закрыть и загрузить, чтобы импортировать обработанные данные и полученные суммы прямо в Excel — на новый лист или в виде таблицы.
Преимущества:
- Обеспечивает чистую и быструю обработку сложных, несогласованных или объёмных данных.
- Шаги можно сохранить и повторно применять к будущим или обновлённым данным, что обеспечивает высокую повторяемость подхода.
- Вы можете настроить логику преобразования не только для простого извлечения, но и для обработки префиксов, суффиксов, десятичных дробей и группировки по различным единицам измерения.
Ограничения:
- Пользователям, впервые осваивающим Power Query, предстоит пройти определённый путь обучения.
- Идеально подходит для структурированных данных и работы с таблицами, а не с отдельными ячейками.
Устранение неполадок и рекомендации:
- Для наилучших результатов всегда проверяйте извлечённые или преобразованные числа на аномалии — особенно когда форматы чисел или единицы измерения существенно различаются.
- Создайте резервную копию исходных данных перед выполнением любых массовых операций (включая использование Kutools и VBA).
- Внимательно проверяйте данные и единицы измерения на скрытые пробелы или опечатки при использовании формул.
- Если формулы не работают или возвращают ошибки (например,)
#ЗНАЧ!), убедитесь, что все ячейки со ссылками содержат данные в ожидаемом формате, и при необходимости скорректируйте диапазон или критерии единиц измерения. - При использовании VBA убедитесь, что макросы включены, и обязательно сохраните файл перед запуском — это значительно упростит восстановление в случае ошибки.
- Шаги преобразования в Power Query можно скорректировать после предварительного просмотра через панель «Применённые шаги», если ваши данные изменились.
Связанные статьи:
- Как объединить несколько ячеек, содержащих ведущие нули, в одну ячейку в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек