Excel 필터링 상태의 계산 요약-excel-php.cn

Excel 필터링 상태의 계산 요약

WBOY

풀어 주다： 2022-06-08 11:34:25

앞으로

4637명이 탐색했습니다.

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

Excel 필터링 상태의 계산 요약

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

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

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

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

Excel 필터링 상태의 계산 요약

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)

Excel 필터링 상태의 계산 요약

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

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

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 함수를 사용하여 곱의 합을 구합니다.