Excel에서 3D를 참조하여 SUMIF

Posted on: 2020年11月12日 2020年11月12日
Tags: excel-generals

그래서 우리는 이미`link : / excel-generals-3d-reference-in-excel [3D reference is in Excel]`을 배웠습니다. 재미있는 사실은 일반적인 Excel 3D 참조가 SUMIF 함수와 같은 조건부 함수에서 작동하지 않는다는 것입니다. 이 기사에서는 SUMIF 함수로 작업하는 3D 참조를 얻는 방법을 배웁니다.

Excel에서 3D 참조가있는 SUMIF의 일반 공식

복잡해 보이지만 그다지 많지 않습니다.

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&name_range_of_sheet_names&”‘!”

&

“criteria_range”),criteria,INDIRECT(“‘”&name_range_of_sheet_names&”‘!”

&”sum_range”)))

” ‘”name_range_of_sheet_names “‘”: * 시트 이름을 포함하는`link : / excel-range-name-all-about-excel-named-ranges-excel-range-name [named range]`입니다. 이건 매우 중요합니다.

“criteria_range”: * 범위를 포함하는 기준의 텍스트 참조입니다. (3 차원 참조 작업에 대한 모든 시트에서 동일해야합니다.)

기준 : * 합산을 위해 입력하려는 조건입니다. 텍스트 또는 셀 참조 일 수 있습니다.

“sum_range”: * 합 범위의 텍스트 참조입니다. (3 차원 참조 작업에 대한 모든 시트에서 동일해야합니다.)

이론은 충분합니다. SUMIF 함수가 작동하는 3D 참조를 해봅시다. === 예 : Excel의 3D 참조를 사용하여 여러 시트의 지역별 합계 : *

우리는`link : / excel-generals-3d-reference-in-excel [간단한 3D 참조 예제]`에서 가져온 것과 동일한 데이터를 사용합니다. 이 예에서는 유사한 데이터를 포함하는 5 개의 서로 다른 시트가 있습니다. 각 시트에는 한 달의 데이터가 포함됩니다. 마스터 시트에서 모든 시트의 지역별 단위 및 컬렉션 합계를 원합니다. 먼저 Units에 대해 해보겠습니다. 단위는 모든 시트에서 D2 : D14 범위에 있습니다.

이제 SUMIF 함수와 함께 일반 3D 참조를 사용하는 경우 = SUMIF (Jan : Apr! A2 : A14, Master! B4, Jan : Apr! D2 : D14)

#VALUE를 반환합니다! 오류. 그래서 우리는 그것을 사용할 수 없습니다. 위에서 언급 한 일반 공식을 사용합니다.

Excel의 SUMIF 수식을 참조하는 위의 일반 3D를 사용하여 다음 수식을 셀 C3에 작성합니다.

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&Months&”‘!”

&

“A2:A14”),Master!B3,INDIRECT(“‘”&Months&”‘!”

&”D2:D14″)))

여기 월은 시트 이름이 포함 된`link : / excel-range-name-all-about-excel-named-ranges-excel-range-name [named range]`입니다. 이것은 매우 중요합니다.

Enter 키를 누르면 정확한 출력을 얻을 수 있습니다.

How does it work? The core of the formula is the INDIRECT function and the named range. Here the string”‘”&Months&”‘!” & “A2:A14″translates to an array of range references of each sheet in the named range.*

\{“‘Jan’!D2:D14″;”‘Feb’!D2:D14″;”‘Mar’!D2:D14″;”‘Apr’!D2:D14”}

이 배열은 실제 범위가 아닌 범위의 텍스트 참조 *를 포함합니다. 이제 텍스트 참조이므로 INDIRECT 함수에서 실제 범위로 변환하는 데 사용할 수 있습니다. 이것은 두 INDIRECT 함수 모두에서 발생합니다. INDIRECT 함수 내부의 텍스트를 확인한 후 (꽉 유지) 공식은 다음과 같습니다.

=SUMPRODUCT(SUMIF(INDIRECT\{“‘Jan’!A2:A14″;”‘Feb’!A2:A14″;”‘Mar’!A2:A14″;”‘Apr’!A2:A14”}), Master!B3,INDIRECT(\{“‘Jan’!D2:D14″;”‘Feb’!D2:D14″;”‘Mar’!D2:D14″;”‘Apr’!D2:D14”})

이제 SUMIF 함수가 작동합니다 (예상 할 수 있듯이 INDIRECT가 아님). 조건은 첫 번째 범위 ” ‘Jan’! A2 : A14″와 일치합니다. 여기에서 INDIRECT 함수는 동적으로 작동하며이 텍스트를 실제 범위로 변환합니다 (_ 그래서 먼저 F9 키를 사용하여 INDIRECT를 풀려고하면 결과를 얻지 못합니다 _). 다음은 ” ‘Jan’! D2 : D14″범위에서 일치하는 값을 합산합니다. * 이것은 배열의 각 범위에 대해 발생합니다. 마지막으로 SUMIF 함수에 의해 반환 된 배열이 있습니다.

=SUMPRODUCT(\{97;82;63;73})

이제 SUMPRODUCT는 최선을 다합니다. 이 값을 합산하면 3D SUMIF 함수가 작동합니다.

자, 여러분,이 방법으로 3D SUMIF 기능을 얻을 수 있습니다. 이것은 약간 복잡합니다. 동의합니다. 이 3D 공식에는 오류에 대한 많은 범위가 있습니다. 명확한 셀의 각 시트에 SUMIF 함수를 사용한 다음 ‘link : / excel-generals-3d-reference-in-excel [normal 3D referencing]’을 사용하여 해당 값을 합산하는 것이 좋습니다.

내가 충분히 설명했으면 좋겠다. 다른 Excel / VBA 관련 쿼리를 참조하는 Excel에 대해 의문이있는 경우 아래 의견 섹션에서 질문하십시오.

Excel에서 3D를 참조하여 SUMIF

Excel에서 3D 참조가있는 SUMIF의 일반 공식

관련 기사 :

인기 기사 :