エクセル 行の挿入すると別シートに行が挿入され、関数も自動更新される方法

エクセルで表を作っています。
1週間程悩み、同じようなような質問を沢山なさってる方もいましたので参考にし
OFFCET/INDIRECT/マクロなど参考にしましたがやはりできませんでした。
どなたかお助けください。
状態は下記のとおりです

Sheet1,Sheet2に同じ項目でデータを作成します。
Sheet1にA2にID番号を入力するとSheet2のA2にデータが自動入力されるようになっています。

Sheet2のA2→=IF(Sheet1!A2="","",VLOOKUP(Sheet1!A2,Sheet1!$A$2:$B$500,1,0))
Sheet2のB2→=IF(Sheet1!A2="","",VLOOKUP(Sheet1!A2,Sheet1!$A$2:$B$500,2,0))
と入力しています。

設定したいことは以下のとおりです。
①Sheet1に行の追加すると、Sheet2の同じ位置に行を追加・削除
②Sheet1にA1にID番号を入力するとSheet2のA2に反映され、Sheet2のB2にも自動で反映されるようにしたい。（但し、Sheet2のA2/B2も関数を自動に反映させたい。）


①に関してCtrlを押しSheet1/Sheet2をクリックすれば行の追加はできるのですが･･･

どうしても組み立てられません。画像も添付致しますのでよろしくお願いします。

できれば、関数でお願いしたいのです。

No.4 ベストアンサー 回答者： fujillin 回答日時： 2017/12/14 17:23

#3です。

#3で提示したマクロのシート名の修正を忘れていました。
（テストは違うシートで行っていましたので）

念のため訂正したものを以下に載せておきます。

Private Sub Worksheet_Change(ByVal Target As Range)
　If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
　Application.EnableEvents = False
　Worksheets("Sheet1").Range("A:B").Copy _
　　Destination:=Worksheets("Sheet2").Range("A:B")
　Application.EnableEvents = True
End Sub

この回答へのお礼

fujilinさんへ
お世話になっております。
わざわざ訂正までありがとうございます。

本当にすごいですね。さらさらと関数もマクロも。そういうことができれば効率あがるな~と思いました。関数がんばってはいるんですが、どうしても2つ以上3つの関数となると
組み立て方がわからなくなります。

弟子入りさせてください・・・と思いました。
また是非お力添えください。よろしくお願いします。

お礼日時：2017/12/15 10:55

No.3 回答者： fujillin 回答日時： 2017/12/14 15:38

#2です。

ようやくおっしゃっている意味がわかりました。
なぜわからなかったかというと、普通はそういう発想はしないからでしょうね。

SHeet2は常にSheet1と同じにしておくようにセットされているのに、敢えてそれを変更する（壊す）操作を許容することになるからです。
これは、本来の目的とは矛盾する操作といえますよね？

端的に言い換えると、A1セルに他のシートを参照する参照式が入っている時に
「DeleteキーでA1セルの内容を消したり、他の値に書き換えても変わらないようにしたい」
ということとほぼ同様です。
普通に考えれば、これを防止したい時には、このような操作を禁止（できなくする）すると思います。

誤操作や（または悪意で）このようなことが起きることはありますので、そのような事故を防止したい場合、通常は、シートの保護機能を利用すると思います。（そのための保護機能とも言えるでしょう）
あるいは、ユーザの変更を許可するのであればそのままとします。（この場合は、変更されます）

ご質問のような内容であるなら、#2で提示したような関数式では無理だと思いますが、無理やり実現するなら「A、B列に変更があったら、Sheet1のA,B列をSheet2にコピペする（あるいはunDo処理を行う）」みたいなマクロを作成しておくことで実現は可能でしょう。
しかしながら、普通はこのようなことは行わないと思います。
なぜなら、仕組みを知らないユーザからみると、上記のように「A1セルでdeleteキーを押して内容を消しても、復活してしまう」という、自分の操作に対してわけがわからない（不快な）結果を生じてしまうことになるからだと思います。


シートの保護機能を利用なさることをお勧めしますが、どうしてもと言うのであれば、こんな感じでしょうか？
(シート2のマクロシートに設定）

Private Sub Worksheet_Change(ByVal Target As Range)
　If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
　Application.EnableEvents = False
　Worksheets("Sheet23").Range("A:B").Copy _
　Destination:=Worksheets("Sheet24").Range("A:B")
　Application.EnableEvents = True
End Sub

※ 本来の目的（？）のシート1の変更も常時反映するようにするためには、別途同様のマクロが必要になります。（シート1の変更で同様の処理を行う）

この回答へのお礼

なるほど～よくわかりました。
基本的にそういうことをしないということですね。
要望を汲み、励んでおりましたが根本的に違うと＝だから検索してもでてこない
お恥ずかしい限りです。
やっとこれを諦めます。
マクロもありがとうございました。
助かりました。

色々ありがとうございました。また是非ご指導お願いします。

お礼日時：2017/12/14 16:24

No.2 回答者： fujillin 回答日時： 2017/12/13 18:36

＃1です。

＞Sheet2のA列B列には既に関数が入っているため
＞挿入された場合も関数が反映されるという意味です。
意味がわかりません。
同じ表示にしながら、それとは別の関数が設定されているということでしょうか？
関数式が別に設定されているなら、そのセルの値はその計算結果に依存するので、同じ表示にすることは無理だと思います。

設定されている関数というのが、「同じ表示にするための関数」が入っているという意味なら、#1で回答したように、「常に同じセル位置を参照する関数」を各セルに設定しておけば良いだけだと思いますが・・・
私の解釈がどこかおかしいのでしょうか？


例えば、Sheet2のA列、B列の各セルに
　=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))
の式を入れておくだけではダメということでしょうか？
（上の式はコピーフィル可能です）

ただし、上の式の場合、Sheet1のセルに空白セルがあると、計算結果として0が表示されます。
（これはエクセルの関数式でセルを参照した時の仕様のようです）
これを表示したくない場合は、エクセルの設定で、「0を表示しない」設定にしておくか、式が複雑になりますが、以下のようにすることでも可能です。
　=IF(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))="","",INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN())))


※ どうやら、ご質問内容を理解できていないようなので、ご参考になるかならないかもわかりませんが・・・

この回答へのお礼

何度も書き込みありがとうございます。
＞例えば、Sheet2のA列、B列の各セルに
　=INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))
の式を入れておくだけではダメということでしょうか？
（上の式はコピーフィル可能です）

上記をSheet2にA2貼り付けました。
そこに行を挿入した場合、上記の関数は自動的にはいりませんよね？
再度フィルドラッグしなおさないと関数式ははいりませんよね？

それを自動化できますか？という意味です。
何度もすみません。

お礼日時：2017/12/14 10:20

No.1 回答者： fujillin 回答日時： 2017/12/12 17:11

こんにちは

なんとなくですが、Sheet1を編集（行の挿入・削除を含む）しても、常にSheet1のA、B列とSheet2のA、B列を同じ表示にしたいということのように思えますが、違うのでしょうか？

仮にそうであるとして、
＞（但し、Sheet2のA2/B2も関数を自動に反映させたい。）
の意味がよく分かりませんが、単純に、同じ行、列を参照するようにINDIRECT関数で設定しておけばよいように思います。
（INDIRECTでなく直接の参照設定だと、行の追加・削除を行った際に、エクセルがセル位置を調整しようとするのでうまくいかないかと…）

この回答へのお礼

>常にSheet1/2を同じ表示にしたい･･･違うでしょうか？
そのとおりです。

＞但し、Sheet2のA2/B2も関数を自動に反映させたい。
とういうのは、Sheet2のA列B列には既に関数が入っているため
挿入された場合も関数が反映されるという意味です。

お礼日時：2017/12/13 17:30