EXCELLにて。シート間の計算式で、行を挿入しても計算式が反映されるようにしたい。出来る？

質問

こんにちは。文章だと表現しにくいのですが・・。
レシピがカンタンに出来るように、まず「シート１」に原材料一覧をつくり、「シート２～」以降にレシピをつくり、レシピの横に「シート１」と同じ原材料一覧をはりつけて、原材料名、原価がすぐわかるようにしました。
今は、シート１０位までしかないので、「シート１」のセルを、「シート２」から「シート１０」まで、９回コピーしているのですが、これがシート１００とかになると、面倒だと思い・・。
以降、分かりやすいように「生き物」の名前で説明させて頂きますが・・。
「シート１」のＡ１、Ａ２と順番にＡ１０まで名前、Ｂ１、Ｂ２と順番にＢ１０まで年令を入れます。
例えばＡ１から順にＡ１は「アリ」Ａ２は「イルカ」Ａ３は「ウサギ」Ａ４は「おたまじゃくし」というように・・。
ソレを「シート２」以降に貼り付けしています。
計算式としては
「='シート１'!Ａ１」「='シート１'!Ａ２」
と言うような式を「シート２」のＡ１もしくは違うセル（違うといってもＨ３、Ｈ４～Ｈ１２など、同じように縦に貼り付けるのは一緒です）に貼り付けています。
ところが、「ウサギ」と「おたまじゃくし」の間に「えび」を忘れていた場合、「シート１」の「ウサギ」と「おたまじゃくし」の間に「セルを挿入」して、「えび」を入れることは簡単なのですが、「シート２」以降には反映されておらず、相変わらず「ウサギ」「おたまじゃくし」のままになっています。Ｂ１、Ｂ２の行も同じくですが。
そこで、質問・・。
「シート１」のＡ３（ウサギ）とＡ４（おたまじゃくし）の間にセルを挿入した場合
｛結果Ａ３（ウサギ）、Ａ４（えび）、Ａ５（おたまじゃくし）となる｝
に「シート２」以降のセルにも、同時にセル挿入（Ａ４のえび）されるような方法（計算式）はございますか？

どなたか、お教え下さい。稚拙な文面お詫び致します。

回答No.8

yokomaya

#5です。それは失礼申し上げました。お許しください。しかしながら少し根本の問題はやはりあると思います。ＥＸＣＥＬは本来行と列の２次元の表から構成されています。それをシートというものを使い分けることで３次元に扱うことが出来るわけですがその事にはかなり慎重になる必要があります。
ひと目で見通せる一枚と異なりシートを分けるということは切り替えなければ見えない領域にデータを並べることですから扱いが非常にやりにくくなります。circlekazさんは原材料一覧を作られたとの事ですからそれと同じ構造を貼り付けるのではなく原材料を入力すればそこから原価や付随するデータをＶＬＯＯＫＵＰなどで引き出す構造にすれば順序などに無用な気を使う必要はありません。原材料の入力が誤らないように一覧から選ぶ入力規則を用いればなお理想的かと。このように別シートにある場合は名前を定義しておきデータの入力規則でリスト範囲のところに=定義した名前のように入力すればマウスで選べます。
恐らく原材料一覧を貼り付けるとの事ですからレシピ毎に１シートではないかと想像しますが僕なら１レシピ１行で作ります。非常に横長になるかもしれませんがその１行を１ページに見せて表示するシートを別途設けることでデータ構造が極めてすっきりするからです。
この構造なら和洋中についても必ずしも分離する必要を覚えません。和洋中の区別列を設ければフィルターで抽出できますから。データを扱う場合行と列に意味のあるはっきりとした２次元構造にすることがＥＸＣＥＬが本来持つ種々の機能(フィルター、、ピボットetc)を効果的に用いるためにもとても大切です。
ＥＸＣＥＬではデータ構造をどう設計するかが最も肝な部分と思いますからすぐには難しいかも知れませんが徐々に試行錯誤されていくことをお勧めします。

回答No.7

NCU

全体をひとつの配列にしてしまえばよろしいかと。

Sheet2の!A1:B10を選択した状態で、「=」を入力。
そのままSheet1のA1:B10を選択し、Ctrl+Shift+Enter
結果として、こんな式になります。→ {=Sheet1!A1:B10}

全体がひとつなので、部分の変更は一切できません。

回答No.6

NNAQ

=Sheet1!A1
では無く、
=INDIRECT("Sheet1!A1")　か、
=INDIRECT("Sheet1!A"&ROW(A1))
で、良いのでは。

又は、原材料一覧に名前定義して
=INDEX(名前,ROW(A1),COLUMN(A1))
とか。

回答No.5

yokomaya

そもそもシートを分ける事の必要性の方が疑問ではないでしょうか？ＥＸＣＥＬでは列が実に256列もあるのにです。Ａ列に項目名があって横スクロールでも消えない枠固定をご存知ないのでは？

お礼

シートを分ける必要性はあります。洋食も和食もデザートも全て同じシートにごっちゃって訳にはいかないので・・。
「枠固定」も知っています。
ただ、高度（私にとって）な技術となると、説明文よんでもチンプンカンプンで。
語学力がないと言われればそれまでですが、皆様のお力をおかし頂きたくて。

回答No.4

mshr1962

単純にシート全体を作業グループにして、一行挿入してやればいいのでは
内容が同じならこのほうが楽だと思いますが．．．
「複数のワークシートに同じデータを入力する」
http://www.microsoft.com/japan/users/tips/Excel/122.aspx
「作業グループで行える作業」
http://support.microsoft.com/kb/402374/ja

回答No.3

zap35

材料が最大で５０個だとして（A1:A50に格納される）、貼りつけたい開始セルに

=IF(Sheet1!A1:A50="","",Sheet1!A1:A50)

と入力して、それを下にコピー。

配列にする手もありますが、わざわざ配列にしなくてもＯＫでした。

回答No.2

fighting-okan

読解能力がないので、検討違いでしたらごめんなさい。
LOOKUP関数を使われてはいかがですか?

回答No.1

zap35

材料がシート１のＡ１から並んでいるとき、別シートのＨ４から下に表示させたいなら、Ｈ４に

=OFFSET(Sheet1!$A$1,ROW()-ROW($H$4),0)

と入力して、下にコピーします。
シート名、材料の開始セル、貼りつけるセルの開始位置は必要に応じて変更して下さい