Excel에서 범위에 단위가 포함된 숫자를 올바르게 합산하는 방법은 무엇입니까?
Excel로 작업하는 동안 "5 kg", "12 lbs", 또는 "10 m"과 같이 단위가 포함된 숫자를 합산해야 하는 상황을 마주칠 수 있습니다. 이는 특히 무게, 길이, 양 등을 추적할 때 흔히 발생하며, 예를 들어 구매 목록, 공학 측정값, 실험실 기록, 물류 데이터 등 각 값에 측정 단위가 추가되는 경우입니다. 그러나 이러한 데이터에 Excel의 표준 SUM 함수를 사용하려고 하면 원하는 대로 작동하지 않습니다. 그 이유는 Excel이 텍스트(예: 단위)가 포함된 값을 텍스트 문자열로 해석하기 때문이며, SUM 및 기타 기본 수학 함수는 이를 무시하거나 오류를 반환합니다. 이러한 값들을 정확히 더하려면 계산을 수행하기 전에 숫자 부분을 추출하는 방법이 필요합니다. 이 튜토리얼에서는 다양한 실제 시나리오에서 단위와 함께 있는 숫자를 올바르게 더하는 데 도움이 되는 여러 실용적이고 효과적인 방법을 안내해 드립니다. 여기에는 수식, 내장 기능, 그리고 Kutools for Excel과 같은 유용한 추가 기능들이 포함됩니다.
수식을 사용하여 단위가 있는 숫자 합산하기
모든 값이 동일한 단위를 공유하고, 빠르고 수식 기반 솔루션이 필요한 경우, Excel 배열 수식은 숫자 부분을 추출하고 합계를 계산하는 데 도움이 될 수 있습니다. 이 방법은 모든 셀에 숫자가 바로 뒤에 동일한 단위가 오는 범위에 적합합니다. 예를 들어, "8 kg", "15 kg" 등이 있습니다.
결과를 표시할 빈 셀을 선택하고 다음 수식을 입력하세요:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)수식을 입력한 후, 배열 수식으로 인식되도록 Ctrl + Shift + Enter를 함께 눌러 확인합니다 (일부 시스템에서는 배열 수식에 이것을 요구함). 그러면 선택한 셀에 숫자 부분의 합계가 표시됩니다.
팁: 위 수식에서 B2:B7은 단위가 포함된 값을 합산하려는 데이터 범위를 나타내며, "kg"은 데이터에 포함된 단위입니다. 범위의 모든 항목이 단위에서 일관되고, 여분의 공백이나 철자 불일치가 없는지 확인하세요. 그렇지 않으면 수식이 값을 올바르게 추출하지 못합니다. 단위나 범위가 다른 경우, 이에 맞게 조정하세요. 이 수식은 범위에 여러 유형의 단위가 있는 경우를 처리하지 않습니다.
Kutools for Excel을 사용하여 단위가 있는 숫자 합산하기
공식을 암기하지 않고도 더 쉬운 시각적인 접근 방식을 선호하는 사용자를 위해, Kutools for Excel은 숫자 값을 직접 정리할 수 있는 간단한 방법을 제공합니다. Remove Characters 기능을 사용하면 비숫자 단위를 쉽게 제거할 수 있으므로, 한 열에 숫자와 단위가 혼합되어 있는 데이터에서 빠르게 숫자를 계산 가능하게 만들 수 있습니다.
1. 단위가 포함된 숫자가 있는 셀을 강조 표시하세요. Kutools 탭으로 이동하여 Text를 클릭하고 Remove Characters를 선택하세요. 스크린샷 보기:
2. Remove Characters 대화 상자에서 Non-numeric 옵션만 선택하세요. 이 설정은 숫자를 제외한 모든 문자를 제거합니다. 스크린샷 보기:
3. OK 또는 Apply를 클릭하세요. 이제 선택한 셀에는 숫자 내용만 남아있으며, 간단한 수식으로 이를 합산할 수 있습니다. 빈 셀을 선택하고 아래 수식을 입력하여 결과를 얻으세요:
=SUM(B2:B7)아래 이미지는 이 절차 이후 정리된 열과 합계가 어떻게 보이는지를 보여줍니다.
Kutools for Excel - 필수적인 300개 이상의 도구로 Excel을 강화하세요. 영구적으로 무료인 AI 기능을 활용하세요! 지금 바로 구매하기
참고: Kutools는 선택한 셀에서 직접 교체를 처리하므로 원본 텍스트를 유지해야 할 경우 데이터의 복사본을 작업하는 것이 좋습니다. 이 솔루션은 단일 유형의 단위에 가장 적합합니다. 이 기능을 사용한 후 합산하기 전에 모든 정리된 값이 숫자인지 확인하세요.
Kutools for Excel과 SUM 함수를 사용하여 동일한 단위를 가진 숫자 합산하기
Kutools for Excel을 사용하여 동일한 단위를 기준으로 숫자 합산하기
테이블에 다른 단위의 값이 포함되어 있는 경우 — 예를 들어, "Kg"과 "Pound"로 표현된 무게 — 그리고 각 단위별로 별도로 합산하고 싶다면 (예: 총 Kg 및 총 Pound 금액), Kutools의 Formula Helper 기능이 이를 효율적으로 처리할 수 있습니다. 이는 요약표, 재고 보고서 작성 또는 추가 비교를 위한 데이터 준비에 유용합니다.
Kutools for Excel - Excel용 필수 도구 300개 이상이 포함되어 있습니다. 영구적으로 무료인 AI 기능을 활용하세요! 지금 다운로드하세요!
1. 합산된 결과를 표시할 셀을 선택하세요. Kutools > Formula Helper > Statistical > Sum based on same text로 이동하세요.
2. Formulas Helper 대화창에서 다음과 같이 계산을 설정하세요: Arguments input에서 전체 데이터 범위를 Range로 선택하고, 특정 단위(예: "Kg" 또는 "Pound")를 Text 상자에 입력하거나 선택하세요. 그런 다음 OK를 클릭하세요.
팁: 기본 수식은 절대 참조(예: $B$2:$B$7)를 사용합니다. 여러 단위 또는 행에 대해 이 기능을 자동으로 채울 계획이라면, 필요에 따라 참조를 상대적으로 변경하세요.
이제 결과 셀에 선택한 단위의 총합이 표시됩니다. 각 단위별로 반복하여 모든 그룹의 총계를 얻으세요.
참고: 이 방법은 명확하게 레이블된 단위 유형이 있는 테이블에 잘 작동하며, 다중 단위 합산을 자동화하여 수동 그룹화로 인한 오류를 최소화합니다.
Kutools for Excel을 사용하여 중복 행 결합 및 해당 숫자 합산하기
일부 데이터 세트는 관련 수량과 단위가 있는 항목 또는 개체가 여러 번 나열된 것을 특징으로 합니다. 예를 들어, 다양한 단위로 무게가 입력된 과일 등이 있습니다. 아이템과 단위별로 값을 합산하고 중복 행을 병합하여 총계를 통합하려고 한다면, Kutools for Excel의 Advanced Combine Rows 기능이 이러한 시나리오에 딱 맞게 설계되었습니다. 이 기능은 집계를 빠르고 간편하게 만들어줍니다.
1. 중복 항목과 혼합된 단위를 포함하는 테이블을 선택하고, Kutools > Merge & Split > Advanced Combine Rows로 이동하세요.
2. Combine Rows Based on Column 대화 상자에서 먼저 단위가 포함된 열을 선택하고 Primary Key로 지정하세요. 이로써 Kutools는 데이터를 단위별로 그룹화합니다. 스크린샷 보기:
3. 다음으로 숫자 값(예: "Weight")이 있는 열을 강조 표시한 후 Calculate를 선택하고 Sum을 선택하여 각 그룹에 대한 값을 더하세요.
4. OK를 클릭하면 테이블이 단위별로 요약되고 필요한 대로 숫자를 결합합니다. 이는 특히 여러 항목을 단위별로 집계해야 하는 재고 및 판매 데이터에 유용합니다.
Kutools for Excel - 필수적인 300개 이상의 도구로 Excel을 강화하세요. 영구적으로 무료인 AI 기능을 활용하세요! 지금 바로 구매하기
팁: 더 깊은 통합을 위해 하나 이상의 키(예: 항목과 단위)로 그룹화할 수 있습니다.
중복 결합 및 숫자 합산
VBA 매크로: 단위가 있는 텍스트 문자열에서 숫자 추출 및 자동으로 합산하기
완전한 자동화를 원하거나 단위 문자열의 복잡한 패턴으로 작업하는 고급 사용자의 경우, VBA 매크로는 추출 및 합산을 한 번에 처리할 수 있습니다. 이 방법은 특히 범위에 다른 단위가 포함된 숫자나 일관되지 않은 데이터 구조가 포함된 경우, 그리고 내장된 Excel 수식이 너무 제한적이거나 각 사례에 맞게 조정하기 어렵거나 번거로울 때 유용합니다.
1. Developer > Visual Basic을 클릭하여 VBA 편집기를 엽니다. Microsoft Visual Basic for Applications 창에서 Insert > Module을 클릭하고 다음 코드를 모듈에 붙여넣으세요:
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를 누르거나 
코드 편집기 창 위의 Run 버튼을 클릭하세요. 그러면 숫자와 단위가 포함된 범위를 선택하라는 메시지가 담긴 대화 상자가 나타납니다. 이 스크립트는 각 텍스트 셀에서 선행 숫자 값을 자동으로 추출하고 모든 결과를 합산한 다음, 합계를 팝업 창에 표시합니다.
팁 및 사용 시 주의사항:
- 이 매크로는 문자열의 시작부터 연속적으로 숫자 문자를 추출하며, 숫자가 아닌 문자가 나오기 전까지 계속됩니다. 즉, "5kg", "12 lbs", 또는 "20.5 m"과 같은 대부분의 형식에서 작동합니다.
- 숫자의 형식이 일관되지 않거나 문자열 내에 포함되어 있는 경우 추가적인 사용자 정의가 필요할 수 있습니다.
- 필요에 따라 VBA 코드를 수정하여 단위를 별도로 수집하거나 처리할 수 있습니다.
- 원본 데이터 손실을 방지하려면 항상 워크시트의 복사본이나 백업에서 매크로를 사용하세요.
- Excel 설정에서 매크로 기능이 활성화되어 있어야 합니다.
이 VBA 솔루션은 데이터 변동이 있을 때 매우 유연하며, 대규모 데이터 세트에 적용될 때 상당한 시간 절약이 가능합니다.
Power Query를 사용하여 단위가 있는 숫자 추출 및 합산하기
Power Query는 Excel의 내장 데이터 변환 도구로, 단위가 포함된 숫자를 처리하는 큰 규모 또는 복잡한 데이터 세트에 이상적입니다.
데이터 구조가 일관되지 않거나 반복 작업에 대한 자동화가 필요한 경우 특히 유용합니다.
1. 숫자와 단위가 포함된 데이터 범위를 선택하고 Data 탭으로 이동하여 From Table/Range를 클릭하세요. 메시지가 나타나면 데이터에 헤더가 있는지 확인하세요.
2. Power Query Editor에서 텍스트 값이 있는 열을 선택합니다. Add Column 탭 > Extract > Text Before Delimiter 또는 Text Between Delimiters.
데이터 형식에 따라 선택하세요:
- 데이터가 "12 kg"처럼 생겼다면 공백 구분자를 사용하세요.
- 데이터가 "12kg"처럼 생겼다면 글자 기반 구분자를 사용하세요.
3. 새 열의 데이터 유형을 필요에 따라 Decimal Number 또는 Whole Number로 변경하세요. 열 헤더를 우클릭하고 Change Type을 선택하세요.
4. (선택 사항) 단위별로 합산하려면 Extract Text After Delimiter를 사용하여 단위를 분리한 다음 Home 탭에서 Group By를 선택하고 새로운 숫자 필드에 대해 Sum을 선택하세요.
5. Close & Load를 클릭하여 처리된 데이터와 합계를 새로운 시트 또는 테이블로 Excel에 직접 가져옵니다.
장점:
- 복잡하고 일관되지 않거나 대량의 데이터를 깔끔하고 빠르게 처리합니다.
- 단계를 저장하고 미래의 데이터나 업데이트된 데이터에 다시 적용할 수 있어 이 접근 방식의 반복성이 매우 높습니다.
- 접두사, 접미사, 소수점, 그리고 다양한 단위별로 그룹화하는 것과 같이 단순 추출 이상의 변환 논리를 사용자 정의할 수 있습니다.
제한 사항:
- Power Query를 처음 접하는 사용자는 학습 곡선이 있을 수 있습니다.
- 구조화된 데이터 또는 테이블 작업에 가장 적합하며, 임시 셀 항목에는 덜 적합합니다.
문제 해결 및 제안:
- 최상의 결과를 위해 항상 추출되거나 변환된 숫자를 검토하여, 특히 단위나 숫자 형식이 크게 다를 경우 이상 현상을 확인하세요.
- 대량 작업(Kutools 및 VBA 포함)을 수행하기 전에 항상 원본 데이터를 백업하세요.
- 수식을 사용할 때 숨겨진 공백이나 데이터 및 단위의 오타를 꼭 확인하세요.
- 수식이 실패하거나 오류가 반환되면(예:
#VALUE!), 참조된 모든 셀이 예상된 형식을 포함하고 있는지 확인하고, 범위 또는 단위 기준을 조정하세요. - VBA를 사용할 때 매크로가 활성화되었는지 확인하고 실행 전에 저장하여 복구를 쉽게 하세요.
- Power Query 변환 단계는 Applied Steps 창에서 미리 본 후 데이터가 변경된 경우 조정할 수 있습니다.
관련 문서:
- Excel에서 선행 0이 있는 여러 셀을 하나의 셀로 결합하는 방법은 무엇입니까?
- 다양한 워크시트/워크북에서 범위를 결합하는 방법은 무엇입니까?
- Excel에서 하나의 열 값을 기준으로 빠르게 행을 병합하고 일부 계산을 수행하는 방법은 무엇입니까?
최고의 오피스 생산성 도구
| 🤖 | Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행 | 코드 생성 | 사용자 정의 수식 생성 | 데이터 분석 및 차트 생성 | Kutools Functions 호출… |
| 인기 기능: 중복 찾기, 강조 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 또는 셀 병합 | 반올림(수식 없이) ... | |
| 슈퍼 LOOKUP: 다중 조건 VLOOKUP | 다중 값 VLOOKUP | 다중 시트 조회 | 퍼지 매치 .... | |
| 고급 드롭다운 목록: 드롭다운 목록 빠르게 생성 | 종속 드롭다운 목록 | 다중 선택 드롭다운 목록 .... | |
| 열 관리자: 지정한 수의 열 추가 | 열 이동 | 숨겨진 열의 표시 상태 전환 | 범위 및 열 비교 ... | |
| 추천 기능: 그리드 포커스 | 디자인 보기 | 향상된 수식 표시줄 | 통합 문서 & 시트 관리자 | 자동 텍스트 라이브러리 | 날짜 선택기 | 데이터 병합 | 셀 암호화/해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵게/이탤릭/취소선 필터 등) ... | |
| 15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...) | 50+ 종류의 차트(간트 차트, ...) | 40+ 실용적 수식(생일을 기반으로 나이 계산, ...) | 19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...) | 12 가지 변환 도구(단어로 변환하기, 통화 변환, ...) | 7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...) | ... 등 다양 |
Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다. 가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...
Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다
- Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
- 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
- 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!
모든 Kutools 추가 기능. 한 번에 설치
Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.
- 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
- 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
- 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
- 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
- 최고의 가성비 — 개별 추가 기능 구매 대비 절약