Excel 필터링 상태의 계산 요약

이 글에서는 excel 관련 지식을 소개합니다. 필터링 후 일련번호를 추가하는 방법, 필터링 후 곱하는 방법, 필터링 후 조건에 따라 계산하는 방법을 함께 살펴보겠습니다. 여러분.

관련 학습 권장사항: excel 튜토리얼

1. 필터링 후 일련번호 추가

아래 그림과 같이 필터링된 상태에서 연속된 일련번호를 유지하려면 먼저 필터를 취소하고 추가하면 됩니다. D2 셀에 다음 수식을 입력한 후 아래로 당깁니다.

=SUBTOTAL(3,E$1:E2)-1

SUBTOTAL 함수는 보이는 셀 내용만 계산합니다.

첫 번째 매개변수는 3을 사용하는데, 이는 COUNTA 함수 실행을 위한 계산 규칙, 즉 두 번째 매개변수에 보이는 셀 개수를 센다는 의미입니다.

두 번째 매개변수는 동적으로 확장된 범위 E$1:E2를 사용합니다. 수식을 아래로 내리면 이 범위는 E$1:E3, E$1:E4, E$1:E5,...

수식은 항상 계산됩니다. 첫 번째 행부터 수식이 있는 행까지 E열에 표시되는 비어 있지 않은 셀의 수입니다. 결과에서 1을 빼면 계산된 결과는 일련번호와 동일하며 필터링 후에도 계속 유지됩니다.

참고로 이 수식을 =SUBTOTAL(3,E$2:E2)로 변경하면, 즉 수식이 있는 행부터 시작하면 일련번호 결과는 양호하지만 마지막 행은 필터링 중에 Excel에서 요약 행으로 처리됩니다.

2. 필터링 후 곱하기

아래 그림과 같이 E열을 필터링한 후 총액에 단가를 곱하여 계산해야 합니다.

E2 셀 수식은 다음과 같습니다.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

필터링된 제품을 계산하려면 문제의 핵심은 데이터가 표시되는지 여부를 확인하는 것입니다.

이 눈에 보이는 상태를 어떻게 판단하나요?

OFFSET 및 SUBTOTAL 기능을 결합해야 합니다.

먼저 OFFSET 함수를 사용하여 셀 E3을 기준점으로 하고 다차원 참조를 얻기 위해 행 1~13을 아래쪽으로 오프셋합니다. 이 다차원 참조에는 행 하나와 열 하나가 있는 13개의 참조 영역이 포함되어 있습니다. 즉, E4부터 E16까지의 개별 셀이 각각 참조됩니다.

다음으로 SUBTOTAL 함수를 첫 번째 매개변수로 사용합니다. 즉, E4부터 E16까지 각 셀에 표시되는 셀 수를 계산합니다. 셀이 표시된 상태이면 이 셀에 대한 통계 결과는 1입니다. 그렇지 않으면 통계 결과는 0입니다. 다음 효과와 유사한 메모리 배열을 얻습니다.

{1;0;1;1;1;1;0;0;1;1;0;1;0}

위 결과에 개수를 곱한 값을 사용합니다. F 열과 G 열의 단가는 셀이 표시 상태인 경우 1*수량*단가와 동일하고, 그렇지 않은 경우 0*수량*단가와 동일합니다.

마지막으로 SUMPRODUCT 함수를 사용하여 곱의 합을 구합니다.

3. 필터링 후 조건에 따라 집계

아래 그림과 같이 E열의 부서를 필터링한 후 3년 이상 근속자 수를 집계해야 합니다.

E2 셀 수식은 다음과 같습니다.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

상반기 계산 원리 위의 예도 동일하며 핵심은 셀이 표시되는지 여부를 결정하는 것입니다.

수식 후반부의 통계 조건(G4:G16>3)에 전반부의 판정 결과를 곱하여 두 조건이 동시에 충족되었음을 의미합니다. 즉, 가시 상태이고 열 G가 3보다 큽니다.

4. 필터링 후 제목 자동 수정

아래 그림과 같이 E열의 부서명을 필터링한 후 셀 D1의 제목이 해당 부서명으로 자동 변경되기를 원합니다.

=LOOKUP(1,0 /SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&"통계 테이블"

SUBTOTAL 및 OFFSET 함수의 조합은 목적은 여전히 ​​D열의 단위를 결정하는 것입니다. 그리드가 표시되는지 여부입니다. 0과 1로 구성된 메모리 배열을 가져옵니다.

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

0/이 메모리 사용 배열, 0과 오류 값으로 구성된 새 메모리 배열 가져오기:

{#DIV/0!;0;#DIV/0!…;0;0;0;0;#DIV/0!;0;# DIV /0!;0;#DIV/0!}

LOOKUP 함수는 쿼리 값으로 1을 사용하고, 위 메모리 배열에서 마지막 0의 위치를 ​​찾아 해당 위치에 있는 E열의 내용을 반환합니다.

궁극적인 목표는 필터링 후 마지막으로 표시된 셀의 내용을 추출하는 것입니다.

추출된 내용을 "통계표"와 연결하여 자동으로 업데이트되는 표 제목으로 바꿔보세요.

관련 학습 권장 사항: excel 튜토리얼

위 내용은 Excel 필터링 상태의 계산 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!