Как правильно суммировать числа с единицами измерения в диапазоне в 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. Воспользуйтесь постоянно бесплатными функциями ИИ! Скачайте прямо сейчас!
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.
- Лучше всего применяется к структурированным данным или при работе с таблицами, а не с прямыми ad-hoc записями в ячейках.
Устранение неполадок и рекомендации:
- Для достижения наилучших результатов всегда проверяйте извлеченные или преобразованные числа на наличие аномалий, особенно если формат единиц измерения или чисел сильно различается.
- Сделайте резервную копию исходных данных перед любыми массовыми действиями (включая использование Kutools и VBA).
- При использовании формул дважды проверьте наличие скрытых пробелов или опечаток в данных и единицах измерения.
- Если формулы не работают или возникают ошибки (например,
#VALUE!), убедитесь, что все ссылочные ячейки содержат ожидаемые форматы, и соответственно скорректируйте диапазон или критерии единиц измерения. - При использовании 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек