エクセルでシートにリンクを貼り付けているのですが元シートに行を挿入した

質問

エクセルでシートにリンクを貼り付けているのですが元シートに行を挿入した場合にリンク先シートの行も挿入になってほしいのですが・・・

例えば
Sheet1
　　　Ａ　　　　Ｂ
１　Ａさん　　150
２　Ｂさん　　270
３　Ｃさん　　100
４　Ｄさん　　300
５　Ｅさん　　350

と入力し、Sheet2に以下の式？を入力した場合
Sheet2の式　　　　　　　　　　　　　　　　　　　Sheet2の表示
　　　　　Ａ　　　　　　　　Ｂ　　　　　　　　　　　　　　　Ａ　　　　　Ｂ　　　　
１　=Sheet1!A1　　=Sheet1!B1*2　　　　　　　１　Ａさん　　300
２　=Sheet1!A2　　=Sheet1!B2*2　　　　　　　２　Ｂさん　　540
３　=Sheet1!A3　　=Sheet1!B3*2　　－＞　　３　Ｃさん　　200
４　=Sheet1!A4　　=Sheet1!B4*2　　　　　　　４　Ｄさん　　600
５　=Sheet1!A5　　=Sheet1!B5*2　　　　　　　５　Ｅさん　　700
このようになるかと思います。

Sheet1の3行目に行を挿入した場合ですが
Sheet1
　　　Ａ　　　　Ｂ
１　Ａさん　　150
２　Ｂさん　　270
３　←行の挿入→
４　Ｃさん　　100
５　Ｄさん　　300
６　Ｅさん　　350

Sheet2は以下のようになってしまします。
Sheet2の式　　　　　　　　　　　　　　　　　　　Sheet2の表示
　　　　　Ａ　　　　　　　　Ｂ　　　　　　　　　　　　　　　Ａ　　　　　Ｂ　　　　
１　=Sheet1!A1　　=Sheet1!B1*2　　　　　　　１　Ａさん　　300
２　=Sheet1!A2　　=Sheet1!B2*2　　　　　　　２　Ｂさん　　540
３　=Sheet1!A4　　=Sheet1!B4*2　　－＞　　３　Ｃさん　　200
４　=Sheet1!A5　　=Sheet1!B5*2　　　　　　　４　Ｄさん　　600
５　=Sheet1!A6　　=Sheet1!B6*2　　　　　　　５　Ｅさん　　700

元シートに行を挿入した場合以下のようになってほしいのですが方法はございますでしょうか？
Sheet2の式　　　　　　　　　　　　　　　　　　　Sheet2の表示
　　　　　Ａ　　　　　　　　Ｂ　　　　　　　　　　　　　　　Ａ　　　　　Ｂ　　　　
１　=Sheet1!A1　　=Sheet1!B1*2　　　　　　　１　Ａさん　　300
２　=Sheet1!A2　　=Sheet1!B2*2　　　　　　　２　Ｂさん　　540
３　=Sheet1!A3　　=Sheet1!B3*2　　－＞　　３　 空白　　空白
４　=Sheet1!A4　　=Sheet1!B4*2　　　　　　　４　Ｃさん　　200
５　=Sheet1!A5　　=Sheet1!B5*2　　　　　　　５　Ｄさん　　600
６　=Sheet1!A6　　=Sheet1!B6*2　　　　　　　６　Ｅさん　　700

因みにエクセルのバージョンは2003です。
いろいろと調べてみたのですがやり方を見つける事ができませんでした。
マクロを組めば出来そうな気もするのですがなにぶん素人なもので組み方もわからず困り果ててしまいました。
初歩的な質問で申し訳ございませんがどなたかご指導よろしくお願いします。

回答No.2

conanthe · ベストアンサー

こんにちは。ハンドルネームconantheです。解決法を2つ考えました。

1つめは、シート1とシート2を選択しておいてから行挿入する方法です。Shiftキーを押しながらシート1とシート2の下のタブをクリックすれば、シート2枚が選択できます。選択後、行挿入します。シート2の3行目に新たに式をコピーしなければいけませんが、これが一番単純なやり方です。

2つめは、「=Sheet1!A1　　=Sheet1!B1*2」の代わりに下の式を使う方法です。
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)　　=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)*2

シート2の行は増えませんが、データは下へ1行ずつスライドします。行挿入後、シート2の一番下の行の式を下に1行分コピーしてください。前もって無人の行にも式を入れておけばこの手間は省けます。0が並んで見苦しいですが、0を非表示に設定しておけば回避できます。

どちらのやり方も、シート1とシート2の構造が同じと仮定してあります。つまりAさんは必ず1行目にいるということです。違う場合は、少し式を変えなければいけません。

お礼

conanthe様、こんにちは。
ご回答ありがとうございます。
２つも案を頂きまして本当に恐縮しております。

一つ目のやり方につきましては私が素人と宣言した事へのご配慮と思っております。
やはりconanthe様が懸念された通り新たに式をコピペしなければいけないのがちょっとネックです。
ただ、シートを２枚同時に選択する方法は知らなかったので勉強になりました。

二つ目のやり方は素晴らしいです。
OFFSET関数を使う方法などは思いつきもしませんでした。
初めは読んでいて意味不明でしたが記述してみると「なるほど～」といった感じです。
解説にもコメントしてありますが挿入ではなくスライドといった考えなのですね。

シート1と2が同じ構造である事が条件である事も理解できました。
ご指摘通り構造条件がずれた場合はOFFSETの始まり、もしくはOFFSET値を変更する必要がありますね。

素人が陥りそうな懸念事項までしっかりとフォローしてある回答よりconanthe様のお人柄が想像できます。
ご多忙の中、ご回答ありがとうございます。

回答No.4

ki-aaa

間違えました。

>>まず、行挿入したい列から、最終列までをコピーします。

まず、行挿入したい行から、データのある最終行までをコピーします。

回答No.3

ki-aaa

こんにちわ。

それは、コピー、貼り付けを行うとできます。

まず、行挿入したい列から、最終列までをコピーします。

それを一行下に貼り付けます。

挿入したい行をクリアします。

これでいいと思います。

お礼

ki-aaa様、こんにちは。
ご回答ありがとうございます。

読ませてもらった瞬間に「これは間違いなく出来る」といった単純明快なやり方ですね。
シンプルすぎて自分が何を迷っていたのかと思わず苦笑いしてしまいました。

コピペ後に元行を削除すれば行を挿入したのと同じ事になりますね。
しかも、リンクの並びもコピーですから崩れる事はありませんね。
行を挿入する事で頭が固まってしまい、まったく思いもつかないやり方でした。

「エクセルの裏技」といった印象を持ちました。

ありがとうございました。

回答No.1

kybo

INDIRECT関数を使う手もありますが、INDEX関数を使う方法を紹介します。
Sheet2のA1に以下のように入れ、縦横コピーします。

=IF(INDEX(Sheet1!A:A,ROW())="","",INDEX(Sheet1!A:A,ROW()))

お礼

kybo様、ご回答ありがとうございます。
あまりに早い回答でビックリしました。

なるほど、範囲内にデータがあるか否かで判定する方法ですね。
たしかに出来ますね。
(1)．元シートに行を挿入すると参照先のシートが空白になる事
(2)．IF関数を使っているので偽の条件に計算式をわかりやすく入れられる事
私の理想通りでございます。
まったく思いつかなかったアイディアでございます。

ありがとうございます。
助かりました。