☰

경고:

tag-icon • tag-icon google-spreadsheets
경고:

한 자리 등급 숫자와 그 옆에 있는 텍스트(등급 1~9)가 혼합된 셀 집합이 있습니다. 제가 하고 싶은 것은 각 셀에 대해 숫자를 추출하고 평균을 산출하는 것입니다. Search 및 RegexExtract와 같은 기능에 대해 알고 있지만 첫 번째 일치 항목만 반환하는 것처럼 보이므로 이를 사용하여 모든 숫자를 얻는 방법을 이해하지 못합니다.

다음은 단일 셀에 있는 텍스트의 샘플입니다.

A - 1
B - 5
C - 9
D -4
E -7
F -  2

위 텍스트의 경우 {1,5,9,4,7,2} 배열을 만들고 이를 평균 함수에 전달하려고 합니다.

또한 Google Sheets를 사용하고 있지만 대부분의 표준 Excel 기능은 동일하게 작동합니다. 감사해요.

답변1

저는 다음 두 단계의 해결 방법으로 문제를 해결할 수 있다고 제안하고 싶습니다.

경고:

  • VBA는 Google 시트에서는 작동하지 않으므로 아래 표시된 방법은 Excel 사용자를 위한 것입니다.
  • XLSX엑셀 파일은 구글 시트에 적합/편집 가능한 파일 형식 으로 변환이 가능합니다 .
  • SUMPRODUCTGoogle 시트에서도 작동합니다.

여기에 이미지 설명을 입력하세요

1 단계:

  • 누르거나ㅏlt+F11 또는아르 자형ight를 클릭하세요.에스het TAB 및 팝업 메뉴 히트에서VVB 편집기를 얻는 코드입니다.

  • 씨오피와피이 코드를 다음과 같이 평가하세요.중모듈을 선택한 다음 시트로 돌아갑니다.

    Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitNum = SplitNum + xStr
End If
 Next
End Function

  • 셀에 다음 수식을 입력하세요 AI5.=SplitNum(AH1,TRUE)

2 단계:

  • 얻으려면ㅏVERAGE 이 수식을 Cell 에 입력하세요 AJ5.

    =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)

Google 시트 사용자는 다음 공식을 사용할 수 있습니다.

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

주의 -필요에 따라 수식의 셀 참조를 조정합니다.

답변2

원하는 값이 AVERAGE해당 값인 경우 스프레드시트에서 A1의 데이터를 사용하여 시도해 보세요.

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

각 숫자를 추출하는 대신 숫자가 아닌 숫자만 제거합니다. 그런 다음 분할하여 평균을 냅니다. 때로는 원하는 것을 추출하기보다는 원하지 않는 것을 제거하는 것이 편리한 기술입니다.

여기에 이미지 설명을 입력하세요

을 위한뛰어나다사용자(Excel 2016+를 사용하는 경우)TEXTJOIN 다음을 사용할 수 있습니다.정렬공식:

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

이는 배열 수식이므로 ctrl+를 shift누른 상태에서 을 눌러 "확인"해야 합니다 enter. 이 작업을 올바르게 수행하면 Excel은 {...}수식 입력줄에서 관찰된 대로 수식 주위에 중괄호를 배치합니다.

관련 정보