[[20261112114051]]　『計算式が壊れないようにしたい』（キャスパー）　ページの最後に飛ぶ

『計算式が壊れないようにしたい』（キャスパー）

 以下のようにシートを作成したときに

     A   B   C   D   E
  ┌─┬─┬─┬─┬─┐
 1│ a│ 1│  │ a│ 2│
  ├─┼─┼─┼─┼─┤
 2│ b│ 2│  │ c│ 3│
  ├─┼─┼─┼─┼─┤
 3│ c│ 3│  │  │  │
  ├─┼─┼─┼─┼─┤
 4│  │  │  │  │  │
  ├─┼─┼─┼─┼─┤
 5│ a│ 3│  │  │  │ B5=SUMIF($A$1:$A$4,A5,$B$1:$B$4)+
  ├─┼─┼─┼─┼─┤    SUMIF($D$1:$D$4,A5,$E$1:$E$4)
 6│ b│ 2│  │  │  │ B6=SUMIF($A$1:$A$4,A6,$B$1:$B$4)+
  ├─┼─┼─┼─┼─┤    SUMIF($D$1:$D$4,A6,$E$1:$E$4)
 7│ c│ 6│  │  │  │ B7=SUMIF($A$1:$A$4,A7,$B$1:$B$4)+
  ├─┼─┼─┼─┼─┤    SUMIF($D$1:$D$4,A7,$E$1:$E$4)
 8│  │ 0│  │  │  │ B8=SUMIF($A$1:$A$4,A8,$B$1:$B$4)+
  └─┴─┴─┴─┴─┘    SUMIF($D$1:$D$4,A8,$E$1:$E$4)

 変更可能なセルは B1:B3とE1:E3とA5:A8です。
 それ以外は変更できないようにロックをかけたいと思っています。
 この時、
 例えばA5のセルを選択してその枠をクリックしたまま
 A8に移動すると
 B8=SUMIF($A$1:$A$4,#REF!,$B$1:$B$4)+SUMIF($D$1:$D$4,#REF!,$E$1:$E$4)
 このようになり計算式が壊れます。
 これを防ぐにはどうすればいいでしょうか？

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

 その構成ならスピルを使ってB5に埋め込みすればいいかと思います。
 _セルの選択範囲を構成する1辺がちょうど作り直せると動いてしまうので
 _keysを4行目を含めて指定してから1行削除します。

  B5:=LET(_rng,VSTACK(A1:B3,D1:E2),
     _keys,drop(A4:A8,1),
     _sum,GROUPBY(INDEX(_rng,0,1),INDEX(_rng,0,2),SUM,,0),
     XLOOKUP(_keys,INDEX(_sum,0,1),INDEX(_sum,0,2),0)
  )

セル座標を整えて無かったのを書き込み時に気が付いたのでおかしいかも
（ちくわ） 2026/11/12(木) 13:24:46

 質問の意味がよく解ってないけど
 B5:B8を選択して
 =SUMIF($A$1:$A$4,A5,$B$1:$B$4)+SUMIF($D$1:$D$4,A5,$E$1:$E$4)
 を記入、確定はctrlを押しながらenter

（記憶違い？） 2026/11/12(木) 13:38:47

 ちくわさん

 試してみました
 =LET(_rng,VSTACK(A1:B3,D1:E2),
     _keys,drop(A4:A8,1),
     _sum,GROUPBY(INDEX(_rng,0,1),INDEX(_rng,0,2),SUM,,0),
     XLOOKUP(_keys,INDEX(_sum,0,1),INDEX(_sum,0,2),0)
  )

 結果【＃スピル!】が表示されました。
 以下のように変更してみたのですが、結果同じでした。

 =LET(_rng,VSTACK(A1:B4,D1:E4),
     _keys,drop(A5:A8,1),
     _sum,GROUPBY(INDEX(_rng,0,1),INDEX(_rng,0,2),SUM,,0),
     XLOOKUP(_keys,INDEX(_sum,0,1),INDEX(_sum,0,2),0)
  )

（キャスパー） 2026/11/12(木) 14:43:47

（記憶違い？）さん
やりたいことは

A5:A8 を変更したときに結果が正しく表示されるようにしたいです。

例えば
A5とA6の間にｃを挿入すると計算式に #REF が表示されて
おかしくならないようにしたいです
（キャスパー） 2026/11/12(木) 14:46:01

エラー値「＃REF!」「#スピル!」について
調べてみてはいかがですか。
どのような理由で、「＃REF!」や「#スピル!」が返ってきているかが
わからなければ、対応することは難しいのではないでしょうか。
（匿名） 2026/11/12(木) 15:03:29

 https://www.excel.studio-kazu.jp/error/
 このサイトの説明で#spill! の説明なかったんですね

 B5:B8にデータを展開しようとしているのに空で無いというエラーです。

 B6:B8の既存の式が邪魔になっています消してください。
（ちくわ） 2026/11/12(木) 16:36:21

 (1)
 そもそもの疑問ですが、この作業は、上の表をもとにした合計表の作成ということですか。
 それなら既に指摘がありますが、GROUPBYとかで自動集計すれば済む話で、キーを与える必要もないですし、
 下の表は手を加える必要は一切ないはずです。
 （合計表というのは単なる例ということですか?）

 > 例えばA5のセルを選択してその枠をクリックしたまま A8に移動すると
 これは実質的に Ctrl+X と Ctrl+V を実行するのと同じです。(たぶん)
 これはコピー&ペーストと違って、結果に注意が必要な動作です。
 （普通に考えれば、キーと合計値は同じ行にあるべきなので、
 A列とB列はセットで移す必要があるわけですよね。）

 しかし、A5をA8にカット&ペイストすると、
 B8セルが参照していたA8が別のもので上書きされたので、参照先がなくなり、
 結果としてB8セル中に#REF!に現れたわけです。
 ちなみに、
 B5セルも  =SUMIF($A$1:$A$4,A5,$B$1:$B$4) + SUMIF($D$1:$D$4,A5,$E$1:$E$4)
 から      =SUMIF($A$1:$A$4,A8,$B$1:$B$4) + SUMIF($D$1:$D$4,A8,$E$1:$E$4)
 に変わりますが、それは意図したものですか?

 > A5のセルを選択してその枠をクリックしたまま A8に移動する
 と言うこと自体が将棋で言う「悪手」ではないですか？

 (2)
 コピーペイストを中心に操作するのがよいと思いますが、
 どうしてもということなら、これを避ける方法(のひとつ?)は、INDIRECT関数の使用です。
 合計式を、
 =SUMIF($A$1:$A$4,INDIRECT("A"&ROW()),$B$1:$B$4) + SUMIF($D$1:$D$4,INDIRECT("A"&ROW()),$E$1:$E$4)
 などとすることです。(下記(*) を参照のこと)

 (3)また、
 > 例えば
 > A5とA6の間にcを挿入すると計算式に #REF が表示されて
 > おかしくならないようにしたいです
 （冒頭に書いたように、そもそも何をされたいのかが疑問ですが）、
 これは5行目の下に行を挿入するということですか?
 B列の数式をロックしてシート保護していると、こうした挿入はできません。エラーになりますよ。
 A6以下をコピー&ペイストでA7以下に複製したのちに、A6に"c"を書き込むことになりますね。

  (*)
 Indirect関数は、そのセルの計算とまったく関係がないセルを変更しても再計算がされることに注意が必要です。
 あまり多数のセルでそれを使うと、計算が重くなると思います。
 (実際にそうしたことを実行したことがないので、理論はともかく実際の影響度合いについて私はよく知りません)
（xyz） 2026/11/13(金) 08:45:46

 >例えばA5のセルを選択してその枠をクリックしたまま
 >A8に移動すると

 >A5:A8 を変更したときに結果が正しく表示されるようにしたいです。

 この辺りがどのように移動変更するのかわかないので
 必要性が感じられない

 もしくは（xyz）さんのようにindirect関数もありかな？
 とも思いましたが
 むかしは、発揮性関数だとかやたらと叩かれましたので
 Indirect関数否定派もある一定数いるようです
（記憶違い？） 2026/11/13(金) 09:38:38

＞しかし、A5をA8にカット&ペイストすると、
＞B8セルが参照していたA8が別のもので上書きされたので、参照先がなくなり、
＞結果としてB8セル中に#REF!に現れたわけです

xyz様に回答していただいた上記の事を、
質問者自身で調べて知ることで、ただ教えるより質問者の身につくと思い、
調べてみてください。と回答しました。
「#スピル!」についても同様で、後でご自身でも調べてみてください。

「#スピル!」というエラー値は、例えば以下のような時に返ってくる値です。

まず、スピルというのは、
「1」、「2」、「3」、「4」、「5」という5つのデータがあり、
これをA1セルに代入しようとした時に、
Excelが5つ分のセルを確保して、例えば、A1セル～A5セルに
1つずつデータを入れようとする機能の事です。

この時にA2セルからA5セルまでの間にすでに値が入っていると、
5つ分のデータをセルに表示できなくなります。
従って、Excelが
「スピルという機能を正しく動作させることができません。」
と表示して、指示を出した我々に教えてくれているのです。

以下の表を作成してみてください。

そして、B1セルに「=A1:A5」と入力してみてください。
「#スピル!」という値が返ってきます。(B1セルに表示される)

その後、B3セルの値を消去してみてください。
B1～B5のセルには、A1～A5までのセルの値が表示されるようになります。

これは、
「A1～A5までの値をB1～B5に表示しなさい」
という指示を出したけれど、Excelがその指示通りに結果を返そうとしても、
B3セルにすでに値が入っているので、B列に5つ分のセルを確保できずに、
「B1セルから5つ分の値を表示したくても表示できません」
となってしまうのです。

（匿名） 2026/11/13(金) 10:41:55

 INDEX($A:$A,ROW())   とかでもイケそう...

（白茶） 2026/11/13(金) 18:05:15

コメント返信：

[ 一覧(最新更新順) ]

[[20261112114051]] 『計算式が壊れないようにしたい』（キャスパー） ページの最後に飛ぶ

[[20261112114051]]　『計算式が壊れないようにしたい』（キャスパー）　ページの最後に飛ぶ