image

したがって、「link：/ excel-generals-3d-reference-in-excel [3D参照はExcelにある]」とは何かをすでに学習しました。面白い事実は、通常のExcel3D参照が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 [namedrange]`です。これは非常に重要です。

“criteria_range”：*範囲を含む基準のテキスト参照です。 （3D参考書のすべてのシートで同じである必要があります。）

基準：*それは単にあなたが合計するために置きたい条件です。テキストまたはセル参照にすることができます。

“sum_range”：*合計範囲のテキスト参照です。 （3D参考書のすべてのシートで同じである必要があります。）

理論はこれで十分ですが、SUMIF関数を使用して3D参照を実行してみましょう。 ===例：Excelの3D参照を使用した複数のシートからの地域ごとの合計：*

image

image

`link：/ excel-generals-3d-reference-in-excel [simple 3D referenceexample]`で取得したものと同じデータを取得しています。この例では、同様のデータを含む5つの異なるシートがあります。各シートには、1か月のデータが含まれています。マスターシートでは、すべてのシートからユニットの合計と地域ごとのコレクションが必要です。最初にユニットに対してそれを行いましょう。単位はすべてのシートで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 [namedrange]`です。これは非常に重要です。

Enterキーを押すと、正確な出力が得られます。

imageHow 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関数内のテキストを解決した後（しっかりと保持）、式は次のようになります。

=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関数が実行されます（ご想像のとおり、間接関数ではありません）。条件は最初の範囲 “‘Jan’！A2：A14″に一致します。ここで、INDIRECT関数は動的に機能し、このテキストを実際の範囲に変換します（そのため、最初にF9キーを使用してINDIRECTを解決しようとすると、結果が得られません）。次に、範囲 “‘Jan’！D2：D14″で一致した値を合計します。*これは、配列内の各範囲で発生します。最後に、SUMIF関数によって返される配列があります。

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

これで、SUMPRODUCTは最善を尽くします。これらの値を合計すると、3DSUMIF関数が機能します。

そうですね、これで3DSUMIF関数を実現できます。これは少し複雑です、私はそれに同意します。この3D数式には、エラーの可能性がたくさんあります。明確なセルの各シートでSUMIF関数を使用してから、 `link：/ excel-generals-3d-reference-in-excel [normal 3Dreferenceing]`を使用してこれらの値を合計することをお勧めします。

私は十分に説明できたと思います。他のExcel / VBA関連のクエリの参照に優れていることに疑問がある場合は、以下のコメントセクションで質問してください。

関連記事：

link：/ excel-generals-relative-and-absolute-reference-in-excel [Excelの相対参照と絶対参照] | Excelでの参照は、すべての初心者にとって重要なトピックです。経験豊富なExcelユーザーでさえ、参照を間違えます。

link：/ lookup-formulas-dynamic-worksheet-reference [Dynamic Worksheet Reference] | ExcelのINDIRECT機能を使用して動的に参照シートを提供します。これは簡単です…​

link：/ excel-range-name-expanding-references-in-excel [Expanding References in Excel] |展開参照は、下または右にコピーすると展開します。そのために、列番号と行番号の前に$記号を使用します。ここに1つの例があります…​

link：/ excel-range-name-absolute-reference-in-excel [All About Absolute Reference] | Excelのデフォルトの参照型は相対ですが、セルと範囲の参照を絶対にする場合は、$記号を使用します。これがExcelでの絶対参照のすべての側面です。

人気の記事：

link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50のExcelショートカット] |あなたの仕事をより速くしてください。これらの50のショートカットにより、Excelでの作業がさらに高速になります。

link：/ forms-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数] |これは、さまざまな範囲やシートから値を検索するために使用される、Excelの最も使用され人気のある関数の1つです。

link：/ Tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] |この驚くべき関数を使用して、条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。

ダッシュボードを準備するには、Countif関数が不可欠です。

link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法] |これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。