excel：行挿入までは参照できない？

質問

何枚ものワークシートを「IF」で一覧表に
参照するまでは、なんとかできましたが、
次の問題点にぶつかってしまいました。

参照元のデータ途中に行を挿入しても、
参照先であるデータには「自動的に」反映されません。
手動で新たに設定しなおせば、もちろん反映はされるのですが、
元の行データを操作した際に、一覧のデータまで「自動的」に反映させたいです。
これを解決する方法はありますか？

回答No.1

noname#9284 · ベストアンサー

元のシートに挿入された行が2行目以降でしたら、
数式のあるシートの一番上の数式が入っているセルの右下の小さな四角を
マウスポインタでポイントしますと＋の形になります。
そこでWクリックしますと、セル参照も上から連続して入力されます。

あとはINDIRECTという関数を使ったり、ROWという関数を使ったりすれば上記の操作をしなくてもすみますが、
これは再計算関数なのでどこかのセルに何か入力したりするたびにExcelは再計算をしてくれて
結構もたもた重いファイルになってしまいます。

補足

度々のご回答、感謝！です！！

元のデータ行を増やした時に、一覧表の方は自動的に行が増える....というリアルタイムリンクのような事は、できても、データそのものが重くなってしまうという事ですね。

一覧表の方に各月少しずつ余行を持たせて対応するのが、一番簡単な方法でしょうか？

回答No.6

nishi6

一覧を表示するシートを算式で設定する方法は、ある意味限界があるでしょう。

#1の補足に、
＞元のデータ行を増やした時に、一覧表の方は自動的に行が増える....というリアルタイムリンクのような事は、できても・・・

これは、算式が設定してあれば表示することができるかもしれませんが、自動的に行は増えないでしょう。
500行目まで一覧を表示するように算式を設定してあっても、1000行目に入力されたら表示できません。

『一覧』の内容が不明でこの質問の回答としては、 hirorin2004さん の『これもケースバイケースという事になります』が正解だと思います。一覧表の内容が分からないのが難点です。

例えば、列単位にまとめるなら、
　　A2=INDEX('1月'!$C:$C,ROW())
　　B2=INDEX('2月'!$C:$C,ROW())
　　C2=INDEX('3月'!$C:$C,ROW())
のようにして、下にコピーします。書式をユーザー定義で『#;#;』としておけば、0は表示されません。
しかし、算式を登録していない箇所は表示できません。

INDIRECTを使えば、=INDIRECT("A2")　や　=INDIRECT("'1月'!A"&ROW())　ですが面倒ですね。

一覧という意味から、各シートをパターン的に処理することになるでしょう。
以下、下の例をVBAで表示してみます。

[シート１月]
　　　Ａ　　Ｂ　　　Ｃ　　　Ｄ
１　種類　項目１　項目２　１月計
２　あい　　１　　１０　　１１
３　うえ　　２　　２２　　２４
４　おか　　４　　４４　　４８

のようなシートが12ヶ月あって、種類と月計をワークシート『一覧』に一覧表示してみます。

Private Sub Worksheet_Activate()
　　Dim ichiranWs As String　'// 一覧を表示するワークシートのシート名
　　Dim Ws As Worksheet　　　'// ワークシート
　　Dim WsCot As Integer　　 '// ワークシートカウンタ

　　ichiranWs = ActiveSheet.Name

　　For Each Ws In Worksheets
　　　　If Right(Ws.Name, 1) = "月" Then
　　　　　　WsCot = WsCot + 1
　　　　　　'// このあたりは例
　　　　　　Ws.Columns("A").Copy Destination:=ActiveSheet.Columns((WsCot - 1) * 2 + 1)
　　　　　　Ws.Columns("D").Copy
　　　　　　Worksheets(ichiranWs).Columns(WsCot * 2).PasteSpecial Paste:=xlValues
　　　　End If
　　　　ActiveSheet.Range("A1").Select
　　Next
End Sub

※条件
　各月のシートはシート名『**月』

ワークシート『一覧』のコードウインドウに貼り付けます。
『'// このあたりは例』以下の３行のように処理すれば、簡単でしょう。特に算式は必要ありません。
このコードは、ワークシート『一覧』をアクティブにするたびにワークシート『一覧』を更新します。

お礼

詳しく教えていただいてありがとうございました。
シートの目的を明記していなかったので、ご回答いただくのに解りづらくて申し訳ありませんでした。
教えていただいたものを、少しずつ整理して勉強しなおしてみます。
ありがとうございました

回答No.5

noname#9284

いえ、ROW関数とCOLUM関数については自動再計算関数かどうかわかりません。
混乱させてしまったようでしたらすみません。
ただ、INDIRECTは確かに自動再計算関数です。

お礼

前回の質問から引き続き度々のご回答、ありがとうございました。
自動再計算関数についても多々勉強になりました。
しかしデータがどんどん重くなっていってしまいそうですね、ちょっと欲張りすぎたかもしれません。
少し簡素なデータに押さえておこうと思います。ありがとうございました。

回答No.4

noname#9284

ちょっと余談になってしまいますが、
先ほど自動再計算関数のことについて少し触れました。
下記は、Sheet1のA1の値を引っ張ってくる数式です。
Sheet1のA1の上にセルを挿入してみてください。
この数式は新しいA1の値を引っ張ってきてくれます。
列も同様です。

=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))

INDIRECTだけではなくてROWやCOLUMNも自動再計算関数じゃなかったかなと思います。
この数式には自動再計算関数が複数使われていますので、
この数式が1000ほどのセルに埋め込まれていますと、
どこかに数値を入力するたびに(自動再計算関数が３つだとして)３＊１０００＝３０００回の再計算がいちいち行われる事になり
スペックの充分ではないマシンにとって辛いものがあります。

回答No.3

imogasi

（１）例えば、Sheet1のＡ３に=Sheet2!A3といれ、Sheet1上でＡ１０まで式を複写します。
これでＳｈｅｅｔ２のＡ３：Ａ１０までの値の変化は即座にＳｈｅｅｔ１のＡ３：Ａ１０の各セルに反映されます。
（２）Ｓｈｅｅｔ２のＡ８で１行、行挿入するとＳｈｅｅｔ１のＡ８：Ａ１０は=Sheet2!A9とかに変化して以前の設定がおかしくならないようにしてくれます。
（３）しかしＳｈｅｅｔ２に行挿入したからといって、Ｓｈｅｅｔ１にまで行挿入する機能（操作メニュー）はありません。
また関数式によっては、行挿入や削除はできません。
関数式は１つのセルの「値しか」設定できません。
（４）ＶＢＡで考えようとしましたが、ＩＮＳＥＲＴメソッドで行挿入を行うのは簡単ですが、（Ａ）ＩＮＳＥＲＴされたイベントを捉えるとか（Ｂ）挿入された後からこのセル（この行）が挿入されたものかどうかの痕跡を調べる方法がないと思うので、ＶＢＡでも難しいようにおもう。
ＵｓｅｄＲａｎｇｅなどが増加することを捉える方法も
削除と挿入をされると見分けがつかない。
（５）「範囲」の定義とかを使ってどうにかできないか
と思うが判りません。
やや質問の主旨を推定しています。上記のような質問でなければ、お許し下さい。

お礼

ご回答ありがとうございます。
関数式に対してちょっと欲張りな事をイメージしてしまったようです。
可能性をいろいろとご指摘いただいてありがとうございました。
今後の勉強の役立てます！

回答No.2

noname#9284

> 一覧表の方に各月少しずつ余行を持たせて対応するのが、一番簡単な方法でしょうか？

データ入力が歯抜けになっていても、支障が無ければそれでいいと思います。
ただ、関数によっては合計範囲に空白があるとエラーになるものもありますので
ケースバイケースですね。

元のデータの並び順が整然としていなくても、
参照先のシートで整頓する事もできます。
詳細な情報がありませんので、これもケースバイケースという事になります。