Excelで値をコピーすると、隣の列にある数式が変更されてしまう

質問

Excel2007で、2003以前でなるのかは試していないのですが…
値を切り取って違うところに貼り付けすると、隣の列の計算式の参照先が変化してしまい困っています。
分かり辛いですが…

sheet1とsheet2があります。
sheet1にはデータが入っており、sheet2でsheet1のデータを集計しています。

sheet1には
C列に名前、D列にデータ、E列にデータが入ってます。
sheet2には
B列に名前が列挙してあります。sheet1内の検索用です。
そしてC列に
=SUMIF(sheet1!C$1:C$150, sheet2!$B1, sheet1!E$1:E$150)-SUMIF(sheet1!C$1:C$150, sheet2!$B1, sheet1!D$1:D$150)
という計算式が入っています。
sheet1のC列の中で、名前(sheet2!$B1)が一致するE列の合計値からD列の合計値を引いた結果を集計しています。
そして、人数分この式がコピーされています。
他には見た目的な装飾以外には特に何も書かれていません。

ここで、sheet2の検索用の名前が減ったり、順番が移動したので、切り取って違う位置（同じ列です）に張り付けました。
例えば、5人分の名前を切り取って一つ上の行に張り付けします。
思いとしては、となりの計算式は当然変化せず再計算されて、計算結果も上の行に一つ分ずれる予定だったのですが、結果としては何故か数式が変化して、(sheet2!$B)の部分が移動した名前列を追いかけるように1個分変化してしまいます。（移動した5個分だけが数式変化します）
当然名前と計算結果の行がずれてしまい、具合が悪いです。

$で絶対参照にすると、数式をコピーした際に行移動した分が変化しないので、結局数式を1個1個変更することになるので面倒です。
かと言って、R1C1形式で相対参照にすれば解決する可能性もありますが（試してません）、多分R1C1形式に慣れて無い人が使えば混乱してしまう気がするので、なるべく避けたいです。

コピーして貼り付けする分には数式は変化しないので、とりあえずはそれで対処してます。
（コピーして貼り付けした後に不要な名前を削除）

どなたか、解決方法か、こういう仕様である利点、もしくは自分の間違いを教えていただけないでしょうか。

回答No.1

chie65536（@chie65535） · ベストアンサー

「切り取り＆貼り付け」は、エクセルは「セルの移動」として処理します。

従って「切り取り＆貼り付け」でセルを移動させると、そこを参照していた式がすべて書き替えられます。

この書き替えは「例外はない」ので、＄の有無による絶対参照/相対参照でも、R1C1形式による参照でも、必ず書き替えが起こります。

もし「セルの入れ替え」を行うなら「OFFSET関数を用いて、起点となるセルに、絶対に移動しないセルを用いる」しかありません。

例えば「A1セルは絶対に移動したりしない」と判っているなら「$B1」と書く代わりに「OFFSET($A$1,0,1,1,1)」と書きます。

また、セルの中身を参照する場合に「何があっても同じ場所を参照する」と言う場合は「INDIRECT("$B$1")」と書けば、この式は、セルが動こうが消えようがお構いなしに「常にB1セルの値を返す」ようになります。

「参照するセルが移動してしまう可能性があるのにも関わらず、参照式を追従させたくない」と言う場合は、上記のように「OFFSET関数」または「INDIRECT関数」を使用しましょう。

お礼

なるほど。
理解しました。
個人的な感想で言えば、結構謎の仕様なんですね。
ありがとうございました。

回答No.2

MackyNo1

セルのドラッグや切り取りでセルの移動操作をした場合は、数式の整合性を保つために、セル範囲を自動的に追随するように設定されています。

数式部分を変更しないのであれば、以下のように数式を変更するのが簡単かもしれません。

Sheet2!$B1
　　↓
INDIRECT("Sheet2!$B"&ROW(A1))

お礼

理解しました。
実はまだ思った動きにならないのですが、がんばってみます。
ありがとうございました