Excel別シートの数値を反映

こんにちは。

提示されている SUMIF 関数の引数は途中で切れていることから、「参照箇所を変更したい」と書かれているのは「INDIRECT 関数の引数の中のセル参照箇所を変更する方法を知りたい」といったことでしょうか？

その場合、その数式の入っている表の構成（セル配置）と参照先の表の構成（セル配置）が提示されていないので、以下では、それらを推測した上でセル参照箇所の変更方法を書いています。予めご了解ください。

まず、INDIRECT 関数の ROWS($A$1:K4) の部分に注目してみると、これは明らかに数式全体を行方向にコピー・貼り付けして使用することを前提にして書かれていることが分かります。

そこで、INDIRECT 関数の引数部分のみを取り出して数式として実行し、更にこれをコピーし行方向に適当な行数だけ貼り付けしてみたのが図１です。

【 図１ 】

尚、A1セルに書き込んだ数式は、下記のようになっています。これを行方向にコピー・貼り付けしています。

="吸水渠土工調書!R"&(ROWS($A$1:K4)-1)*7/3+10&"C9:R"&(ROWS($A$1:K4)-1)*7/3+15&"C9"

この図１で分かるのは、

• 「R1C1形式」でセル範囲の参照を行うための文字列であること。
• 小数部分が含まれる文字列は INDIRECT 関数に渡すと #REF! エラーになること。

です。

また、INDIRECT 関数では、第２引数に FALSE を指定しているので第１引数の文字列を「R1C1形式」（ Rの後ろの数字で行数を、Cの後ろの数字で列数を指定する形式）として処理する、ということも分かります。

つまり、提示されておられる数式は、最初のセルに書き込んだ数式をコピーし 3行おきに貼り付けて使用していることが推測できます。

図２では、A1セルに書き込んだ数式をコピーし 3行おきに貼り付けした結果です。

【 図２ 】

セル構成が分からないので、推測できるのはここまでです。

ですが、ここまでの推測が正しいと仮定すると、参照範囲の変更は比較的容易に行えます。

図２の数式を詳細に見比べていくと、以下のようなことが分かります。（全て「吸水渠土工調書」シートが対象です。）

• 全て C9列（ I列 ）を参照している。
• 行数は 7行おきのグループとなっている。
• 各グループの先頭から 6行ずつを取り出して表示（参照）している。

ということです。

これらのことから、参照箇所を変更する場合には、

1. この数式を書き込むセル位置（ 3行おき ）を指定しているのは「 (ROWS($A$1:K4)-1) 」の部分と「 /3 」の部分になる。ここでは、1行目【 (ROWS($A$1:K4)-1)/3 = 1 】、4行目【 (ROWS($A$1:K7)-1)/3 = 2 】、7行目【 (ROWS($A$1:K10)-1)/3 = 3 】、・・・というような数値（ 1,2,3,・・・）を返すことで下記 2. の参照先指定が正常に行われる。
2. 「吸水渠土工調書」シートのセル参照位置は、I列（ C9列 ）固定で、＊7 の部分でグループごとの行数（ 7行 ）を、上記 1. の値と ＊7 と +10 でグループごとの先頭セルの参照開始行位置（最初は 17行目【 1. の値＊7+10 = 17 】）を、上記 1. の値と ＊7 と +15 でグループごとに取り出す行数（ 6行【 1. の値＊7+15 - (1. の値＊7+10) + 1 = 6 】）を指定している。（ここでの全角の＊記号は半角の＊記号と考えてください。このエディタでは文章中に半角の＊記号を直接入れると斜体を指定する特殊文字と判定されてしまうようですので。）

というように考えて変更すれば良いかと思います。

以下、具体的な変更例です。

例えば、数式を書き込むセル位置を 4行おきにしたい場合には、図２の A1セルに書き込む数式を下記のように変更すれば良いことになります。

="吸水渠土工調書!R"&(ROWS($A$1:K5)-1)*7/4+10&"C9:R"&(ROWS($A$1:K5)-1)*7/4+15&"C9"

図３は、この A1セルに書き込んだ数式をコピーし 4行おきに貼り付けした結果です。

【 図３ 】

更に加えて、参照先の先頭セルを J列の 20行目から 9行おきのグループとし、8行分ずつ取り出す（参照する）場合の数式は下記のようになります。

="吸水渠土工調書!R"&(ROWS($A$1:K5)-1)*9/4+11&"C10:R"&(ROWS($A$1:K5)-1)*9/4+18&"C10"

図４は、この A1セルに書き込んだ数式をコピーし 4行おきに貼り付けした結果です。

【 図４ 】

以上です。

あとは、参照範囲を変更した数式を INDIRECT 関数の引数として組み込み、SUMIF 関数で条件指定したセル範囲の合計を求めれば良いかと思います。

尚、SUMIF 関数の条件部分などが提示されていないので、数式全体の結果がどのようになるかは全く分かりません。ご了解のほど。

もしも、参照変更したいセル範囲が全く異なるような場合には、数式を入れている表の構成（セル配置）と参照先シートの名称、及び参照先シートの表の構成（セル配置）、SUMIF 関数を使った数式全体（変更前）の提示、Excel の製品バージョン（ Microsoft 365 の Excel、Excel2024 といった名称）などをお知らせいただければ、もっと適切な変更方法もご紹介できるかと思います。
もちろん、個人情報に関わる部分は隠すなり変更するなりしていただく必要がありますが、出来るだけ詳しくお知らせください。

Windows11 と Microsoft365 の Excel の組み合わせで動作確認しています。

ご参考になれば幸いです。

【　修正　】

説明文と数式中で K4 あるいは K5 と書かなければならない箇所で、G4 あるいは G5 と書いてしまっていたため、該当箇所を修正いたしました。

全体的な説明と数式の結果には影響しないのですが、質問文に提示されている数式と異なってしまうため修正いたしました。失礼いたしました。

【念のため追記】

表のセル配置について。

例えば、A列のセルを 3行ずつセル結合している場合、A1セル（ A1:A3 の結合セル）に入れた数式をコピー・貼り付けする際は 3行おきに貼り付けるというようなことを意識せずとも、そのまま普通に A4セル（ A4:A6 の結合セル）以降に貼り付けしていけば図２のような 3行おきに貼り付けた場合の文字列が出来上がります。

4行ずつセル結合されている場合も同様で、A1セル（ A1:A4 の結合セル）に入れた数式を普通にコピー・貼り付けしていけば図３、図４のような文字列が出来上がります。

注：この返信は翻訳ツールを使用して翻訳されています。文法や意味の誤りがあるかもしれませんが、ご了承ください。ご理解いただきありがとうございます。返信内容で不明な点がありましたら、コメントを残していただければ、できるだけ早く返信いたします。

愛しい naoki yoshida,

元の数式に含まれるSUMIF関数は不完全であり、ワークシート参照を正しく構築するには連結演算子を追加する必要があります。

動的に参照される範囲内のすべての値を合計したい場合は、次の数式を使用してください。

=SUM(INDIRECT("'Intake Channel Geotechnical Report'!R"&((ROWS($A$1:K4)-1)*7/3+10)&"C9:R"&((ROWS($A$1:K4)-1)*7/3+15)&"C9",FALSE))

この数式において：

• 'Intake Channel Geotechnical Report' は参照元のワークシート名です。
• +10 は開始行のオフセットを決定します。
• +15 は終了行のオフセットを決定します。
• C9 は、R1C1参照形式における列I（アイ）を表します。
• FALSE は、Excelに対してR1C1形式で参照を解釈するよう指示します。

参照するワークシートを動的に変更したい場合は、セルB1にワークシート名を入力し、次の数式を使用してください。

=SUM(INDIRECT("'"&$B$1&"'!R"&((ROWS($A$1:K4)-1)*7/3+10)&"C9:R"&((ROWS($A$1:K4)-1)*7/3+15)&"C9",FALSE))

こうすることで、別のワークシートを参照する必要があるときに、いつでもB1の値を変更できるようになります。

適切な数式をテストし、期待通りの結果が返されるか確認してください。もし結果が期待と異なる場合は、対象のワークシート名、列、開始行、終了行、および適用すべき条件をお知らせください。それらの情報をもとに、数式を正確に調整いたします。

もし回答が役に立つなら、「はい」をクリックして、どうかアップボートしてください。この回答について追加の質問がある場合は、「コメント」をクリックしてください。

注意:このスレッドに関連するメール通知を受け取りたい場合は、フォーラムのドキュメントにある手順に従ってメール通知を有効にしてください。