Excel関数について,参照データの範囲が変わる時

質問

Excelの関数について教えてください。

まずExcelで、”Sheet1とSheet2があり、Sheet２には、Sheet1のデータをもとに情報が入力されるように関数{IF(Sheet1!A1="","",Sheet1!A1)}を200行目まで入力されている”ものがあるとします。

それを使って以下のような作業を行います。
1. 職場のシステムからExcelデータを抽出する
2. 抽出したデータをエクセル（sheet1)に貼り付ける。
3. Sheet2にSheet1をもとにした計算結果が表示される

以上が現在、行っている作業です。
ただ問題があり、毎回、sheet1に貼り付けるデータの範囲（行数）が異なるため、sheet2のどこまでに数式を設定しておけばいいのかわかりません。今は適当に２００行目まで数式を入力してあります。

『質問』Sheet1に貼り付けるデータの範囲（行数）と同じ範囲（行数）分だけ、Sheet2にも数式が表示されるようにしたい。（例）sheet1に2345行あるデータが貼り付けられた→それをもとに、Sheet2に関数を2345行目まで、自動で入力する。

大変お手数ですが、分かる方教えて頂けますか？よろしくお願いします。

回答No.2

Chiquilin

#1です。
やり方に拘りがあるなら 特に私から申し上げることはありません。
数式でのリンクなんてやってる限り 改善のしようがありませんので。

> ちなみに、Microsoft Queryは会社で使用できません。
どういう理由でですか？　禁止されているのですか？　だとしたらどうやって
禁止しているのでしょう。

それはともかく Excelから直接接続を作成すればいいだけだと思います。

回答No.1

Chiquilin

> Sheet2に関数を2345行目まで、自動で入力する。
「入力」を自動化するのはマクロ以外ありません。ただこんなことにマクロを使
うくらいなら そもそも数式で対応するのを止めた方がいいくらいです。

根本的なやり方の問題だと思います。 Excelのバージョンが不明ですが 2007以降
なら データの取り込み自体を見直した方が早いと思いますけど。

> 1. 職場のシステムからExcelデータを抽出する
これを外部データの取り込みの Microsoft Queryで取り込むのが良いかと思います。
ちょっとした計算なら SQLで計算させてから取り込むこともできます。

それが無理でもテーブル書式になっているので 隣りに計算結果を入れておけば 最
終行がどうのと気にする必要もありません。つまり Sheet2は必要ありません。

補足

おっしゃる通り、根本的にデータの取り込み方に問題があると思います。ただ、記入している通り、職場のシステムだからこそ、データの抽出方法を変えることができません。

そのため、データを抽出しては、必要ない列を削除し、また、並び替えるという作業を毎月のように行っています。

その作業が少しでも、楽になればと思ったのですが、何か方法はありませんか？

ちなみに、Microsoft Queryは会社で使用できません。また、上記の通り、今回の作業の一番の目的は、列の削除及び並べ替えであり、計算などは行いません。