Excel 접두사 또는 접미사

Question 1

옵션 1:

쉼표로 구분된 숫자뿐만 아니라 텍스트까지 카운트하는 UDF(User Defined Function)를 제안하고 싶습니다.

작동 방식:

누르다Alt+F11VB 편집기를 얻으려면복사&반죽이 코드는기준 치수.

Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

범위에 기준을 입력한 H18:H26다음 셀에 이 수식을 입력 I18하고 채우세요.

=CountOccurrence($G$18:$G$24,H18)

옵션 2:

셀에 이 수식을 입력 I18하고 채워보세요.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

필요에 따라 셀 참조를 조정합니다.

Answer

옵션 1:

쉼표로 구분된 숫자뿐만 아니라 텍스트까지 카운트하는 UDF(User Defined Function)를 제안하고 싶습니다.

작동 방식:

누르다Alt+F11VB 편집기를 얻으려면복사&반죽이 코드는기준 치수.

Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

범위에 기준을 입력한 H18:H26다음 셀에 이 수식을 입력 I18하고 채우세요.

=CountOccurrence($G$18:$G$24,H18)

옵션 2:

셀에 이 수식을 입력 I18하고 채워보세요.

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

필요에 따라 셀 참조를 조정합니다.

Question 2

먼저 숫자를 별도의 셀로 분할해야 합니다.
- 데이터를 선택하세요
- 데이터 탭에서 "열로 분할"을 선택하세요.
- "구분됨"을 선택하고 다음
- 선택 데이터는 "쉼표"로 구분되어 완료됩니다.
이제 COUNTIF를 사용할 수 있습니다. 예:
=COUNTIF($A$2:$C$5,E2)

Answer

먼저 숫자를 별도의 셀로 분할해야 합니다.
- 데이터를 선택하세요
- 데이터 탭에서 "열로 분할"을 선택하세요.
- "구분됨"을 선택하고 다음
- 선택 데이터는 "쉼표"로 구분되어 완료됩니다.
이제 COUNTIF를 사용할 수 있습니다. 예:
=COUNTIF($A$2:$C$5,E2)

Question 3

이미 솔루션이 있는 것 같지만 동적 데이터를 처리하는 VBA가 아닌 솔루션을 추가하겠습니다. 임의로 큰 범위를 미리 채울 수 있는 일부 도우미 열을 사용합니다. 연결된 데이터가 없으면 셀이 비어 있게 됩니다. 일부 도우미 열은 제거될 수 있습니다. 반복을 최소화하기 위해 포함되었지만 모든 도우미 열을 숨길 수 있습니다.

데이터는 A열에 있습니다. B열은 쉼표 수를 기준으로 각 항목의 값 수를 결정합니다. B3의 공식은 다음과 같습니다.

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

C 열은 B 열의 누적 구성 요소 수입니다. C2는 로 입력됩니다 0. C3의 공식은 다음과 같습니다.

=IF(ISBLANK(A3),"",SUM(B3:B$3))

B열과 C열을 데이터가 있는 만큼의 행에 채웁니다. 필요한 경우 언제든지 해당 열을 확장할 수 있습니다.

E열은 단지 편의를 위한 것입니다. 구문 분석된 값에 대한 인덱스를 제공합니다. 하드 코딩 1한 다음 각 연속 행에 대해 1을 추가할 수 있습니다. 아무 이유 없이 행 번호를 기반으로 했습니다( -2아래 수식에서 는 에서 시작하도록 값을 조정하는 것입니다 1). 총 값 수를 초과하는 셀에는 공백이 표시됩니다. E3의 내 공식은 다음과 같습니다.

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

F열은 수식 반복을 피하기 위한 것입니다. 현재 구성 요소가 구문 분석될 관련 열 A 항목을 가져옵니다. F3의 공식은 다음과 같습니다.

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

E열의 구성요소 번호와 C열의 누적 구성요소 수를 비교하여 적절한 항목을 찾습니다.

G 열은 구문 분석된 구성 요소 값으로, 모두 작업하기 쉬운 단일 연속 열에 있습니다. G3의 공식은 다음과 같습니다.

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

이는 현재 요소 번호에서 마지막 "완료된" 입력 레코드의 누적 요소 수를 빼서 열 F 항목에서 구문 분석할 요소를 결정합니다.

E 열부터 G 열까지 예상되는 구성 요소 값 수(적어도 데이터 행 수의 몇 배)를 포괄할 수 있을 만큼 충분한 행에 전파되어야 합니다. $C$2:$C$10 범위를 참조하는 위의 모든 수식은 데이터의 전체 범위를 포함하도록 조정되어야 합니다.

이제 멋진 열에 구문 분석된 모든 요소가 있으므로 이를 집계하고 개수를 얻는 다양한 방법이 있습니다. 나는 동시에 고유 값 목록을 제공하는 피벗 테이블을 사용했습니다.

피벗 테이블에 대해 미리 채워진 G열의 전체 범위를 선택합니다. 행 창과 값 창에 해당 필드를 사용합니다(집계로 개수 선택). 범위에는 사용되지 않은 행의 공백이 포함되므로 기본 제공 필터를 사용하여 공백을 선택 취소하세요.

데이터가 변경되면 피벗 테이블을 새로 고치고 필터에서 새 요소 값이 선택되었는지 확인하세요.

Answer