Excelで行を挿入しても移動しない方法

Excelで行を挿入しても関数の指定値が移動しない方法はありますでしょうか？

SkyDriveのexcel web appでアンケートと作って使っていたんですが、途中で別のシートに入力されてきたものを様々な関数をつかって見やすく集計しました。

しかし、新しい集計結果はどうも挿入されているらしく、せっかく新しい集計結果が入ってもうまく集計されません。

具体的にはシート1のA3の文字列をシート2から検索しシート3のA3に表示させる。それをA4、A5とコピー100くらいまでコピーしておく。
さらにその中から最頻値をA1に返す。

というのをつくったのですがこの時入力はシート1のA10ほどまでしかしてなかったのです。でそれ以下は式がエラーを返している状態なので値がシート1に入力されていけばうまく行くのですが、新しい結果はシート1のA11に挿入されるようでシート3のA11はシート1に挿入後はA12を指定してしまっているので集計結果に合算してくれないのです。

うまい解決方法はございませんでしょうか。

No.3 回答者： MarcoRossiItaly 回答日時： 2014/01/26 16:41

ご質問の、ずれ防止の数式は、No.1 さんのように INDEX 関数とか ROW とか、あるいは INDIRECT とか数値の連続データとかを組み合わせていけば、適当に作れるとは思いますが。

しかし、それ以前の基本的な問題として、行を挿入するときの操作が適切ではない可能性を確認してください。そこが適切であれば、あえて難しい数式をひねり出す必要もないのかもしれません。


＞……新しい結果はシート1のA11に挿入されるようでシート3のA11はシート1に挿入後は（シート1の）A12を指定してしまっているので集計結果に合算してくれないのです。


質問者さんのしたいことというのは、行の挿入後、シート3 の A11 セルの数式が引き続きシート1 の A11 から参照し続けるとともに、シート1 の A11 にあったデータをそのすぐ下のセル（A12）にコピーしたいということですかね。さらには、シート1 の A11 には、別の値を記入したいんだと。

そうであれば、下の (1) または (2) の方法などにより、操作します。

操作の内容を要約すると、11 行目ではなく 12 行目に挿入するよう変えるだけで、問題は解決するのではないかということです。その場合 11 行目は移動しないのだから、シート3 の数式における参照元のセル番地も当然、変化しないままとなります。

(1)-1
シート1 の 11 行目の行番号を右クリックして「コピー」。次いで、12 行目の行番号を右クリックして「コピーしたセルの挿入」を実行。最後に、A11 など 11 行目のセルを好きな値で上書きする。

(1)-2
シート1 の A11:E11 といった 1 行のセル範囲を選択し、その範囲上で右クリックして「コピー」。次いで、A12 セルを右クリックして「コピーしたセルの挿入」を実行し、「下方向にシフト」を指定。最後に、A11 など 11 行目のセルを好きな値で上書きする。

(2)-1
シート1 の 12 行目の行番号を右クリックして「挿入」を実行。次いで、空白となっている 12 行目に、11 行目にある必要な値や数式をコピーして貼り付け。最後に、A11 など 11 行目のセルを好きな値で上書きする。

(2)-2
シート1 の A12:E12 といった 1 行のセル範囲を選択し、その範囲上で右クリックして「挿入」を実行し、「下方向にシフト」を指定。後は (2)-1 と同様に操作。

No.2 回答者： chayamati 回答日時： 2014/01/26 09:11

【相対アドレス】と【絶対アドレス】の使い分けによるものだと思います。

規定値は【相対アドレス】ですが、指定アドレスの場所でF4押下毎に
【相対アドレス】⇒【絶対アドレス】⇒【列が相対 行が絶対】⇒【列が絶対 行が相対】⇒【相対アドレス】
絶対のときは列名、行No.の左に＄が付きます。

よろしければ
シート3のA3の式を提示して下さい。

No.1 回答者： keithin 回答日時： 2014/01/26 02:24

シート１の（移動してしまう）Ａ３を「直接参照しない」数式を工夫します。

あなたがどんな数式を記入してるのかさっぱり判りませんが、たとえば
シート３のＡ３：
=VLOOKUP(INDEX(Sheet1!A:A,ROW(A3)),Sheet2!セル範囲,列,FALSE)
みたいにしとけばＯＫです。




＃参考

＞でそれ以下は式がエラーを返している状態なので値がシート1に入力されていけばうまく行くのですが、新しい結果はシート1のA11に挿入されるようでシート3のA11はシート1に挿入後はA12を指定してしまっている

一体何がどーなってるのか、さっぱりイミフメイです。
頑張って言葉で説明しようとして思いっきりひっくり返ってる、典型的な失敗例ですね。

回答者達は大概の場合もうちょっとエクセルには慣れていますので、
○具体的にどこ番地のセルに実際にどんな数式を記入してるのか
○どこシートのどこ番地のセルに具体的にどういう風に手を動かして操作したのか

そういった、あなたのエクセルの「目に見える事実」をご相談で情報提供するようにしてみて下さい。今後のご参考に。