엑셀 스프레드시트 특정문자 앞 숫자를 모두 더하는 수식

엑셀 스프레드시트 특정문자 앞 숫자를 모두 더하는 수식

엑셀 스프레드시트 숫자만 추출하는 방법

 

엑셀 스프레드시트에서 숫자와 문자열이 혼합된 셀에서 숫자만 추출해 합계를 계산하려면 사용자 정의 함수(UDF) 또는 배열 수식을 사용할 수 있습니다. 아래 두 가지 방법을 소개합니다.

▤ 목차

1. 숫자와 문자열이 혼합된 데이터 셀

---

1. 사용자 정의 함수 (UDF) 사용

UDF는 VBA(Visual Basic for Applications)를 사용해 숫자만 추출하는 방법입니다.

단계:

• VBA 편집기 열기:
  Alt + F11 키를 누릅니다.
• 모듈 추가:
  삽입 > 모듈을 선택합니다.
• 아래 코드를 입력:

Function ExtractNumbers(Cell As Range) As Double
    Dim RegExp As Object
    Dim Matches As Object
    Dim Result As Double
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "\d+"
    RegExp.Global = True
    
    Set Matches = RegExp.Execute(Cell.Value)
    Result = 0
    
    For Each Match In Matches
        Result = Result + CDbl(Match.Value)
    Next Match
    
    ExtractNumbers = Result
End Function

 

Ctrl + S로 저장하고 닫습니다.

엑셀에서 함수 사용: 숫자와 문자열이 혼합된 데이터가 A1:A10 범위에 있다면, =SUM(ExtractNumbers(A1))를 사용해 숫자만 합산합니다.

---

2. 수식으로 해결 (ARRAYFORMULA 또는 TEXTJOIN)

VBA를 사용하지 않고 엑셀 내장 기능으로만 해결하려면 배열 수식을 활용합니다.

단계:

1. 중간 단계로 숫자만 추출:

• MID, TEXTJOIN, FILTER 함수 등으로 숫자 추출을 수행할 수 있습니다.
  예를 들어 A1에 "abc123def45"가 있다면: 
  
   
  =SUMPRODUCT(--TEXTSPLIT(TEXTJOIN(" ",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,""))," "))
  
  이 수식은 셀 내에서 숫자를 분리하고 합산합니다.

1. 구글 스프레드시트 예제:

• REGEXREPLACE로 숫자만 추출:
  
   
  =SUM(FILTER(ARRAYFORMULA(IFERROR(REGEXEXTRACT(A1:A, "\d+"), "")), LEN(A1:A)>0))

---

3. 팁

VBA를 사용하는 방법은 유연하지만, 파일이 매크로 사용을 지원하는 형식(.xlsm)으로 저장되어야 합니다.

배열 수식은 복잡할 수 있지만, 매크로를 피하고 싶을 때 적합합니다.

구글 스프레드시트에서도 REGEXEXTRACT 및 ARRAYFORMULA로 비슷한 작업을 수행할 수 있습니다.

 

2. 한 셀에 여러 숫자들 중 특정문자 앞에 있는 숫자만 추출

주어진 데이터에서 특정문자 앞에 있는 숫자만 추출하여 계산할 수 있습니다.

이를 위해 엑셀에서 사용할 수 있는 두 가지 방법을 소개합니다.

---

1. 엑셀의 내장 함수로 숫자 추출

엑셀의 텍스트 처리 함수를 조합하여 kW 앞에 있는 숫자를 추출하고 계산할 수 있습니다.

숫자 추출 공식:

셀에 데이터가 입력된 범위를 가정하고, 예를 들어 A1에 "11-1404 경기도 고양시 106.68kW 진으뜸 2024/11/29(금)"이 있다고 할 때:

 

공식 1: FIND와 MID 함수 활용

=--MID(A1, FIND(" ",A1, FIND("kW",A1)-15)+1, FIND("kW",A1)-FIND(" ",A1, FIND("kW",A1)-15)-1)

FIND("kW", A1): kW의 위치를 찾습니다.

FIND(" ", A1, FIND("kW", A1) - 15): kW 앞에 위치한 숫자의 시작점을 찾습니다.

MID: 특정 위치의 텍스트를 추출합니다.

--: 텍스트를 숫자로 변환합니다.

 

모든 셀의 합계:

모든 셀에 대해 추출한 값을 합산하려면:

=SUM(--MID(A1:A10, FIND(" ",A1:A10, FIND("kW",A1:A10)-15)+1, FIND("kW",A1:A10)-FIND(" ",A1:A10, FIND("kW",A1:A10)-15)-1))

 

(배열 수식을 사용해야 하며, 엑셀 365 이상에서 자동 배열 수식이 지원됩니다.)

---

2. 구글 스프레드시트에서 REGEX 사용

구글 스프레드시트에서는 정규식을 활용하면 더 간단히 처리할 수 있습니다.

숫자 추출 공식:

셀에 데이터가 입력된 범위를 가정하고, 예를 들어 A1에 "11-1404 경기도 고양시 106.68kW 진으뜸 2024/11/29(금)"이 있다고 할 때:

=ARRAYFORMULA(SUM(IFERROR(REGEXEXTRACT(A1:A, "(\d+\.?\d*)kW"), 0)))

REGEXEXTRACT: kW 앞의 숫자를 추출합니다.

IFERROR: 숫자가 없는 셀에 대해 0으로 처리합니다.

ARRAYFORMULA: 범위에 대해 자동으로 적용합니다.

---

3. 엑셀에서 VBA를 활용한 방법

더 복잡한 상황이나 반복적인 작업이 많다면, VBA를 사용해 특정 패턴에서 숫자를 추출할 수 있습니다.

VBA 코드:

Function ExtractKW(Cell As Range) As Double
    Dim RegExp As Object
    Dim Matches As Object
    Dim Result As Double
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "(\d+\.?\d*)kW" ' kW 앞의 숫자만 추출
    RegExp.Global = False
    
    If RegExp.test(Cell.Value) Then
        Set Matches = RegExp.Execute(Cell.Value)
        Result = CDbl(Matches(0).SubMatches(0))
    Else
        Result = 0
    End If
    
    ExtractKW = Result
End Function

사용 방법:

VBA를 열고 코드를 추가한 뒤 저장합니다.

데이터가 있는 셀에서 =ExtractKW(A1)을 입력하여 kW 앞의 숫자를 추출합니다.

합산하려면 =SUM(ExtractKW(A1:A10))을 사용합니다.

▤ 목차

---

요약

엑셀 함수만 사용: FIND, MID 등을 조합.

구글 스프레드시트: REGEXEXTRACT로 간단히 처리.

복잡한 경우: VBA를 활용해 자동화.

 

 

하루에 한번~ 하늘을 바라보아요~