☰

Excel에서 각 행의 숫자 합계가 >0인 경우에만 배열의 행 수를 어떻게 계산합니까?

tag-icon • tag-icon microsoft-excel worksheet-function
Excel에서 각 행의 숫자 합계가 >0인 경우에만 배열의 행 수를 어떻게 계산합니까?

Excel에 임의의 숫자(예: A2 ~ ET361)가 포함된 150열 x 360행 배열이 있습니다.

각 열(예: 셀 B1에서 ET1까지)에 대해 그 앞의 열에 대해 0보다 큰 행 수를 어떻게 계산합니까?

기준:

B1은 0보다 큰 셀(A2~A361)의 수를 계산해야 합니다.
C1은 각 행의 합이 0보다 큰 행 수(A2:B2, A3:B3, ..., A361:B361)를 계산해야 합니다.
D1은 각 행의 합이 0보다 큰 행 수(A2:C3, ..., A361:C361)를 계산해야 합니다.

COUNTIF 수식을 사용해 보았지만 행 수가 아닌 셀 수만 반환했습니다.
중첩된 ROWS() 및 IF() 수식이 필요하다고 생각합니까? 또한 Excel 파일의 공간을 절약하기 위해 이 문제를 처리하기 위해 또 다른 150 x 360 행렬을 만들고 싶지 않습니다.

또한 스프레드시트를 복잡하게 만드는 매크로와 VBA를 사용하고 싶지 않습니다.

전체 방정식에 복잡성이 추가되어 소계 함수가 작동하지 않습니다.

각 행의 열 합이 0보다 큰 위의 행 수를 계산하려면 행렬 내의 각 셀이 필요합니다. Barry의 솔루션은 '소계' 공식이 있는 셀에 대해 '소계' 공식이 작동하지 않기 때문에 이 경우에는 작동하지 않습니다(테스트했습니다).

다른 대안이 있나요?

답변1

단일 공식 솔루션을 생각해낼 수는 없었지만(아마도 다른 사람은 그럴 것입니다!) 다른 150 x 360 행렬보다 훨씬 적은 스프레드시트 공간을 차지하는 솔루션을 생각해냈습니다.

기본 아이디어는 데이터의 한 열에 대해 각 행의 누적 합계를 계산한 다음 이를 데이터 테이블("가정 분석")에서 사용하여 모든 열에 대한 개수를 생성하는 것입니다.

시작점은 단일 데이터 열의 행에 대한 계산 열입니다.

아래 스크린샷과 같이 10개의 데이터 열이 포함된 워크시트를 설정했습니다.

도우미 칼럼

데이터 오른쪽에 도우미 열 L을 설정했습니다.

셀 L1에는 COUNTIF합계가 0보다 큰 해당 열의 행이 포함되어 있습니다.

행 합계의 경우 각 행의 열에 대한 간단한 합계(다시 말하면 열 A에 대해서만) 대신 함수에서 반환된 범위의 합계를 사용합니다 OFFSET. 이 함수는 다음과 같은 형식을 갖습니다.

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

셀 L3에는 첫 번째 표현식이 있습니다 SUM(OFFSET(...)). 셀 A2에서 아래로 0행, 오른쪽으로 0열, 높이가 1행이고 너비가 셀 L2의 값과 동일한 범위에 대한 행 합계를 계산합니다. 이 경우 L2의 값은 1입니다.

이 수식은 360개 행을 통해 아래로 복사됩니다. 각 경우에는 셀 L2의 값에 따라 결정되는 너비와 1행 높이 범위의 합이 계산됩니다.

예를 들어 L2의 값이 2로 변경되면 열의 수식은 360개 행 각각에 대해 A 및 B 열 값의 행별 합계를 계산합니다. 그리고 L1 셀에는 A2:B361 범위에서 합계가 0보다 큰 행 수가 표시됩니다.

단일 데이터 열의 행 수 계산

데이터 테이블

Excel의 데이터 테이블 기능을 사용하면 해당 계산에 대한 입력 중 하나(또는 두 개)의 값을 변경하는 것이 계산에 미치는 영향을 신속하게 확인할 수 있습니다. 리본의 탭 섹션 What-If Analysis에 있는 버튼을 통해 설정됩니다 .Data ToolsData

첨부된 그림은 데이터 테이블 설정을 보여줍니다.

데이터 테이블은 R1:S10 범위에 생성됩니다. 테이블 상단의 셀 S1에는 입력이 변경될 결과 셀이 있습니다. 이 경우 결과 셀에는 도우미 열 L 상단에 있는 수식 =L1에 대한 참조인 수식이 포함됩니다 .COUNTIF

R2:R10 셀에 "가정" 값을 미리 입력했습니다. 표시된 값(1, 2, ..., 9)은 OFFSET이 반환할 범위의 너비를 나타냅니다. 그리고 "열 입력 셀"은 L1도우미 열에서 합산되는 행의 너비를 결정하는 셀인 셀입니다.

간단히 말해서, 너비 1-9(열 "A", "A:B", "A:C" 등에 해당)를 입력하고 데이터 테이블은 합계가 0보다 큰 행 수를 계산합니다. 각 열 범위에 대해.

데이터 테이블 설정

마지막 사진은 최종 결과를 보여줍니다. 데이터 테이블은 입력 데이터의 각 열에 대한 행 개수, 즉 0보다 큰 (이전 열의) 행별 합계 개수를 계산했습니다. 해당 개수는 데이터의 셀 S2:S10에 반환되었습니다. 테이블. 함수를 사용하여 원본 데이터의 첫 번째 행에 개수를 전송했습니다 TRANSPOSE.

최종 결과

모든 계산이 포함된 예제 워크시트를 사용할 수 있습니다.여기.

답변2

귀하가 요청한 내용을 올바르게 이해했다면 맨 위 행에 각 열에 대한 총 개수가 표시되기를 원할 것입니다.개별 세포그 앞의 모든 열에 0보다 큰 값이 있습니다. 오른쪽?

그렇다면 참조를 잠그기 위해 기호를 CountIf사용하고 사용하는 것은 매우 간단합니다.$

B1 셀에 =CountIf($A2:A361,">0"). 클릭하고 오른쪽으로 드래그하세요. 기호 는 A 열과 현재 열 사이의 모든 항목을 항상 계산하도록 를 $잠급니다 . A수식을 드래그하면 다음과 같이 표시됩니다.

  • C1=Countif($A2:B361,">0")
  • D1:=Countif($A2:C361,">0")
  • E1:=Countif($A2:D361,">0")
  • 등...

CountIf전체 범위를 계산할 수 있으므로 한 번에 하나의 셀이나 수식을 선택할 필요가 없습니다. 따라서 이 방법을 사용하면 현재 열 왼쪽에 있는 모든 단일 셀의 개수를 쉽게 계산할 수 있습니다.

답변3

OFFSET함수를 사용하면 범위 내의 개별 행을 분리할 수 있습니다. 그런 다음 각 행을 합산 SUBTOTAL하고 >0인 행을 계산할 수 있으므로 SUMPRODUCT복사된 B1의 이 수식은 도우미 셀 없이 작업을 수행해야 합니다.

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

설명된 것과 유사한 기술을 사용합니다.여기[여기에는 필터링이 없지만 OFFSET으로 생성된 각 범위를 합산하려면 SUBTOTAL을 사용해야 합니다.]

chuff의 솔루션과 동일한 결과를 얻을 수 있습니다.

관련 정보