수지에는 상당한 양의 데이터가 포함 된 워크 시트가 있습니다. 그녀가 워크 시트에서 열을 숨기는 것은 드문 일이 아닙니다. 그녀는 숨겨진 셀을 무시하면서 모두 같은 행에있는 셀 범위의 평균을 반환하는 수식이 필요합니다. 예를 들어 Susie가 B7 : G7을 선택하는 경우 표시되는 해당 범위의 셀만 평균을 원합니다 (이 경우 D 및 E 열은 숨겨 짐).

이러한 평균을 계산하는 Excel에는 고유 함수가 없습니다. 열에서 셀 범위의 평균을 찾으려고했다면 다음과 같은 방식으로 SUBTOTAL 함수를 사용할 수 있습니다.

=SUBTOTAL(101,A7:A12)

그러나 SUBTOTAL 함수는 제공된 범위가 행 내에있는 경우 정확한 값을 반환하지 않습니다. 원하는 경우 일부 도우미 셀을 사용하여 계산을 수행 할 수 있습니다. 다음을 B8 셀에 넣으십시오.

=(CELL("width",B7)>0)+0

셀 B8을 C8 : G8 범위로 복사합니다. 결과적으로 B8 : G8 범위의 각 셀에는 열이 숨겨져 있는지 여부에 따라 0 또는 1이 포함됩니다. 그런 다음 다음 공식을 사용하여 평균을 결정할 수 있습니다.

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

B8 : G8의 셀에 1 값이 포함 된 열에 대해서만 평균을 계산합니다 (숨겨지지 않음).

물론 헬퍼 셀을 사용하지 못할 수 있으며 평균을 계산하는 사용자 정의 함수를 만드는 것이 더 유용 할 수 있습니다. 다음은 매우 빠르게 작동합니다.

Function AverageVisible(rng As Range)

Dim rCell As Range     Dim iCount As Integer     Dim dTtl As Double

iCount = 0     dTtl = 0     For Each rCell In rng         If rCell.ColumnWidth > 0 _           And rCell.RowHeight > 0 _           And Not IsEmpty(rCell) _           And IsNumeric(rCell.Value) Then               dTtl = dTtl + rCell               iCount = iCount + 1         End If     Next     If iCount > 0 Then         AverageVisible = dTtl / iCount     Else         AverageVisible = 0     End If End Function

함수를 사용하려면 평균을 포함 할 셀에 다음 공식을 넣으면됩니다.

=AverageVisible(B7:G7)

이 함수는 범위의 모든 셀 (즉, 행, 열 또는 실제로 모든 범위에서 사용할 수 있음)을 확인하여 숨겨져 있지 않고 비어 있지 않은지 확인합니다. 셀에 숫자 값이 포함되어 있으면 평균을 계산하는 데 사용됩니다. 지정한 범위에 평균화 할 수있는 값이 포함되어 있지 않으면 함수는 0 값을 반환합니다.

이 함수는 워크 시트가 다시 계산 될 때마다 자동으로 실행됩니다. 숨겨진 열을 변경하면 Excel이 자동으로 다시 계산되지 않습니다. 따라서 열을 숨기거나 숨김을 해제 한 후 강제로 다시 계산해야합니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13262)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.