☰

많은 열의 전치된 개수

tag-icon • tag-icon microsoft-excel vba macros countif transpose
많은 열의 전치된 개수

입력:

입력

다음과 같이 각 열에서 '1'과 '0'의 개수를 구해야 합니다.

예상 출력

Excel/매크로에서 이를 수행할 수 있는 방법이 있습니까?

답변1

"D1"열이 에서 시작 하고 에 인쇄 한다고 하면 "A3"다음과 같이 보일 수 있습니다.

Sub countAndTranspose()
Dim section As Range, count As Range, entry As Range, sectionPrint As Range

Set section = Range("D1", Cells(1, Columns.count).End(xlToLeft)) 'All headers
For Each entry In section
    Set sectionPrint = Cells(Rows.count, 1).End(xlUp) 'Last row in "A"
    Set count = Range(Cells(entry.Row + 1, entry.Column), Cells(Rows.count, entry.Column).End(xlUp))
    sectionPrint.Offset(1).Value = entry.Value 'Print header below last row in A
    sectionPrint.Offset(1, 1).Value = WorksheetFunction.CountIf(count, 1) 'Print sum of 1
    sectionPrint.Offset(1, 2).Value = WorksheetFunction.CountIf(count, 0) 'Print sum of 0
Next entry

End Sub

"D1"우리는 행의 먼 빛 에서 시작하여 가는 열 범위를 정의하는 것부터 시작합니다 .
그런 다음 이 범위를 반복하면서 열 이름을 인쇄할 위치를 선택합니다(A3 이하는 비어 있어야 합니다.)

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

답변2

Excel 파워 쿼리 솔루션

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

전문가용- 전체 절차:

let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"OPERATION", "MEDIA", "IT"}, {"OPERATION", "MEDIA", "IT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Attribute", "1", "0"})
in
    #"Replaced Value"

다른 사람들을 위해- 자세한 단계.

입력 데이터 테이블(헤더 포함)을 선택합니다.
삽입 -> 표

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

좋아요

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

"콘텐츠" 옆에 있는 이중 화살표를 클릭합니다.
선택 취소원래 열 이름을 접두사로 사용

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

좋아요

"이름" 열을 마우스 오른쪽 버튼으로 클릭 -> 제거

전체 테이블
변환 -> 열 피벗 해제를 선택합니다.

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

홈 -> 그룹화 기준

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

좋아요

"값" 열 선택
변환 -> 열을
값 열로 피벗 "개수" 선택

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

좋아요

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

전체 테이블을 선택하고
변환 -> 값 바꾸기 다음
과 같이 입력합니다.

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

좋아요

홈 -> 닫기 및 로드

답변3

OPERATION, MEDIA 및 IT 열 항목 아래에 두 가지 수식을 입력하십시오.

COUNTIF ( A2:A10, 1 )0에 대한 1의 경우
COUNTIF ( A2:A10, 0 ).

MEDIA 및 IT 아래의 수식을 복사하세요.

답변4

VBA가 아닌 솔루션의 경우 Excel 2016 또는 Microsoft 365가 있으면 LET를 활용할 수 있습니다. 이는 Power BI와 달리 Mac용 Excel에서도 작동합니다.

=LET( array, A1:E4,
       hdr, INDEX( array, 1, ),
       rSeqBody, SEQUENCE( ROWS( array ) - 1 ),
       cSeq, SEQUENCE( 1, COLUMNS( array ) ),
       body, INDEX( array, rSeqBody + 1, cSeq ),
       onesCount, MMULT( TRANSPOSE( SIGN( rSeqBody ) ), body ),
       t1s, IFERROR( INDEX( TRANSPOSE( hdr ), TRANSPOSE( cSeq ), {1,2} ), TRANSPOSE( onesCount ) ),
       t10s, IFERROR( INDEX( t1s, TRANSPOSE( cSeq ), {1,2,3} ), ROWS( body ) - TRANSPOSE( onesCount ) ),
       t10s )

다음은 예제 응용 프로그램입니다. 스크린샷

입력은 필드 이름이 있는 헤더 행을 포함하는 배열이고 출력은 아래 셀과 오른쪽 열 하나에 쏟아지는 동적 배열입니다.

작동 원리

먼저 필드 이름을 가져와서 다음과 같은 배열에 넣습니다.hdr. 그런 다음 출력을 계산하고 형성하기 위한 행 및 열 시퀀스를 생성합니다. 그런 다음 1과 0을 포함하는 배열을 만듭니다.몸. 그런 다음 1의 열을 합산합니다.개수행렬 곱셈을 사용합니다.

이것은 1을 세지 않습니다. 합산이 가능하다는 장점이 있습니다. "x"와 같은 기호라면 작동하지 않습니다. 마찬가지로 0은 계산되지 않고 단순히 1에서 계산됩니다.

결과를 전달하기 위해 hdr을 스티칭합니다.(조옮김)oneCount에(조옮김)전치된 hdr의 열을 과도하게 인덱싱하여 각 행의 열 2에 오류를 강제한 다음 IFERROR를 사용하여 !REF# 오류를 전치된 onesCount로 바꿉니다.

이 과정을 한 번 더 반복하여 행 수에서 1의 개수를 빼서 계산되는 0의 개수 열을 추가합니다.몸.

1COUNT 및 0COUNT 헤더를 원할 경우 이 헤더도 꿰맬 수 있지만 원하는 문구로 워크시트에 작성하는 것이 더 쉬울 것입니다.

이는 LET 없이도 가능하지만 지저분하고 디버그하기 어렵습니다. LET는 명확성을 제공하고 도우미 셀이 필요하지 않습니다.

관련 정보