필터링 된 열에서 기준 기반 계산 (Microsoft Excel)

Marty는 회사의 모든 직원 (과거 및 현재)이 나열된 큰 워크 시트를 가지고 있습니다. 워크 시트는 이름, 주소, 부서, 성별, 상태 등과 같은 각 직원에 대한 다양한 정보를 추적합니다.

Marty는 종종 자신의 요구에 맞게 데이터를 필터링합니다. 필터링 후 표시되는 행을 기반으로 남성 인 직원의 비율과 여성 인 비율을 판별하는 방법을 원합니다. SUBTOTAL 함수는 필터링 된 열에서 작동하여 다양한 개수를 제공 할 수 있지만 필터링 된 열의 내용 ( “M”또는 “F”)을 기반으로 개수를 결정할 수는 없습니다.

한 가지 방법은 피벗 테이블을 사용하여 백분율을 결정하는 것입니다.

피벗 테이블은 특히 이와 같은 단일 질문에 답하기 위해 비교적 사용하기 쉽습니다. 그러나 직원에 대한 세부 정보를 볼 수 있다는 점에서 그다지 좋지는 않습니다. 남성 / 여성 질문에 대한 전체 답변을 볼 수 있지만 동시에 해당 직원에 대한 세부 정보를 볼 수는 없습니다. 그래서 저는 Marty의 문제에 대한 반응에서 직선 공식을 사용하는 데 집중하고 싶습니다.

원하는 비율을 얻기위한 공식을 만드는 것은 처음 나타나는 것보다 어렵습니다. 예를 들어, 모든 직원 레코드가 표시 될 때 개수를 결정하는 것은 쉽습니다. 예를 들어, 성별이 C 열에 있다고 가정하고 남성 직원에 대한 레코드 비율을 결정하기 위해 다음과 같이 간단히 사용할 수 있습니다.

문제는 C 열과 다른 열로 레코드를 필터링하는 경우입니다. 예를 들어 상태 열에있는 항목으로 필터링 할 수 있습니다. 위의 수식은 필터링으로 인해 현재 표시되는 레코드뿐만 아니라 성별 열의 모든 레코드를 기반으로하는 백분율을 제공합니다.

이 시점에서 SUBTOTAL 함수가 작동 할 것이라고 생각할 수 있지만 Marty는 작동하지 않는다는 것을 알았습니다. 다시 말하지만, 성별이 C 열에 있으면 C 열의 맨 아래에 다음을 넣을 수 있습니다.

그러나 원하는 요구 사항에 대해서는 작동하지 않습니다. 주된 문제는 SUBTOTAL 함수가 표시되는 레코드 수를 제공하지만 해당 레코드가 C 열에 “M”또는 “F”를 포함하는지 여부를 구별 할 수 없다는 것입니다. 두 번째 문제는 COUNTA가 모든 레코드를 계산하는 것이 아니라 표시된 것만. 따라서 수식은 “M”또는 “F”를 포함하는 표시된 레코드의 백분율을 제공하지 않습니다.

그러나 대신 총 레코드 수에서 표시되는 레코드 수의 백분율입니다.

원하는 경우 도우미 열을 사용해 볼 수 있습니다. 각 직원의 “M”또는 “F”상태를 1 또는 0으로 나타내는 열을 추가하기 만하면됩니다.

이것은 다음과 같은 간단한 공식으로 수행 될 수 있습니다.

이 수식은 다시 성별 열이 C라고 가정합니다.이 수식이 X 열 (도우미 열)에 배치되었다고 가정하면 다음과 같은 방식으로 두 개의 SUBTOTAL 함수를 사용할 수 있습니다.

SUBTOTAL을 처음 사용하면 “M”이 포함 된 행 수가 표시됩니다.

SUBTOTAL을 두 번째로 사용하면 보이는 행의 총 수가 제공됩니다. 공식의 결과는 기록이 보이는 남성 근로자의 비율입니다. 1에서 남성 결과를 빼서 여성 근로자의 비율을 결정할 수 있습니다.

도우미 열을 사용할 수없는 경우 (또는 도우미 열을 사용하지 않으려는 경우) 이전에 논의한 접근 방식의 결함을 해결하는 것이 약간 까다 롭습니다. 좀 더 복잡한 공식이 필요합니다. 다음은 작동하는 공식이지만 좀 더 쉽게 설명 할 수 있도록 단일 공식을 네 줄로 나눴습니다.

이 공식의 작동 방식에 대해 자세히 설명하겠습니다. 잠시 기다려주세요. 시간이 좀 걸릴 것입니다. 먼저 첫 번째 행의 끝과 두 번째 행의 합계를 포함하는 수식의이 부분을 살펴 보겠습니다.

여기서 OFFSET 기능의 사용은 3 개의 매개 변수에 의존합니다. 첫 번째는 반환 될 참조를 계산하기위한 시작 지점에 해당합니다 (이 경우 시작 지점은 C2 : C9999입니다). 두 번째 매개 변수는 첫 번째 매개 변수에 정의 된 해당 범위의 시작 부분에서 오프셋 될 행 수입니다. 이 경우 오프셋 할 행은 분석중인 실제 행에서 범위의 가장 낮은 행 번호 (항상 값 2를 반환 함)를 빼서 정의됩니다. 예를 들어 분석중인 행이 10 행인 경우 여기에서 2 (시작 행)를 빼면 첫 번째 매개 변수에 지정된 범위의 시작에서 8 행의 오프셋이 제공됩니다.

세 번째 매개 변수는 공백이므로 기본값은 0입니다. 이는 첫 번째 매개 변수에 지정된 범위의 첫 번째 열에서 오프셋 될 열 수입니다. 마지막으로 네 번째 매개 변수는 숫자 1이며 OFFSET이 1 셀 높이의 범위를 반환하도록합니다.

결론은 분석중인 열의 단일 셀에 대한 참조를 반환하도록 수식의이 전체 부분이 포함된다는 것입니다. 이 수식의 설명을 위해 반환되는 항목을 “SingleCell”이라고하겠습니다. 이것을 원래 공식에 연결하면 다음과 같이됩니다.

그런 다음 첫 번째 SUBTOTAL 함수는 단일 셀에 대한 COUNTA 결과 (첫 번째 매개 변수에 사용되는 값 3으로 표시됨)를 리턴합니다. 그 결과 SUBTOTAL은 셀이 표시되는지 여부에 따라 0 또는 1을 반환합니다. (셀이 결과에서 필터링 된 경우 0이 반환됩니다. 필터링되지 않은 경우 (표시됨) 1이 반환됩니다.)

수식의 다음 부분은 ISNUMBER 및 SEARCH 함수에 의존합니다. 코드의이 부분은 셀에 “M”문자가 포함되어 있는지 여부에 따라 0 또는 1을 반환합니다. 당신이 끝내는 것은 다음으로 요약되는 것입니다 :

이 단일 행의 경우 SUMPRODUCT는 0을 반환합니다. 이는 행이 전체 개수에서 “계산되지 않음”을 의미합니다. SUMPRODUCT는 배열 기반 함수이므로 원래 범위의 모든 행을 개별적으로 곱하여 곱을 계산합니다. 따라서 두 조건을 충족하는 모든 행의 수를 결정합니다. 행이 표시되고 행에 문자 “M”이 포함됩니다.

이것은 최종적으로 보이는 행 수의 COUNTA 결과 인 최종 SUBTOTAL 함수의 결과로 나뉩니다. 최종 결과는 C 열에 “M”문자가 표시된 행 수의 백분율입니다. Marty가 원하는 정확한 결과입니다.

보이는 행에서 여성의 비율을 얻으려면 검색 대상을 변경하기 만하면됩니다. 수식에서 “M”을 “F”로 변경하면 괜찮습니다.

하지만 언급해야 할 한 가지주의 사항이 있습니다. SEARCH 함수는 대문자와 소문자를 구분하지 않습니다. 따라서 성별 열 (C 열)에서 “M”대신 “남성”을 사용하고 “F”대신 “여성”을 사용하는 경우 수식에서 “남성”을 검색하면 ” 여성 “에는”남성 “문자가 포함됩니다. 따라서 “M”및 “F”를 고수하거나 “남성”및 “여성”을 사용해야하는 경우 수식에 “여성”을 사용하고 남성 레코드의 백분율을 1 빼기 비율로 계산하는 것이 가장 좋습니다. 여성 비율.

_ 참고 : _

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

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

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

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