Excelリンク先の表

質問

='1'!z31をオートフィルをするとz32.z33と増えていきます。
私は'2'.'3'と増えてほしくz31は固定したいのですが、どうすればできますか？

回答No.3

bunjii

>私は'2'.'3'と増えてほしくz31は固定したいのですが、どうすればできますか？
作業用の列を使用してテキストファイルへ書き出してからインポートする方法では如何でしょうか？
仮にA列が作業用に利用可能としてA1セルへ次の数式を設定し、下へA31セルまでオートフィルでコピーします。
="="&ROW(A1)&"!z31"

次にA1:A31を選択し、コピーでクリップボードへ記憶させます。
メモ帳を開いてクリップボードから貼り付けると文字列として次のようなデータができます。
=1!z31
=2!z31
=3!z31
　　・
　　・
　　・
=29!z31
=30!z31
=31!z31
メモ帳を名前を付けて保存で「数式.CSV」として保存します。
数式（=1!Z31）を入力したいセル（N4）を選択して「外部データの取り込み」で先に保存した「数式.CSV」を指定してインポートすれば数式として設定されます。
あなたが指定している '1'! はSheet名の 1 指定していますので書式としては 1!Z31 のようにシングルクォーティションは必要ありません。

お礼

丁寧なご回答ありがとうございます。
まだまだ素人な為質問すると思います。よろしくお願いします。

回答No.2

kagakusuki

>仕入表の　ｎ4　='1'!z31 n5 ='2'!z31 1列31行作りたいのです。

　それでしたら次のようになります。

　まず、仕入表のN4セルに次の様に入力して下さい。

=INDIRECT("'"&1+ROW()-ROW($N$4)&"'!Z31")

　その上でN4セルをオートフィルで下にコピーして行かれると良いと思います。

　又、別のやり方としては、まずN4セルに次の様に入力して下さい。

★1☆

　尚、「★」や「☆」は「(N4～N34のセル範囲に入力される)数式の中で使われる事のない文字」であれば何でも良く、例えば「あ1い」等としてしまっても構いません。
　次にN4セルをオートフィルで下にコピーして行かれると

N4セルの内容　　　 ★1☆
N5セルの内容　　　 ★2☆
N6セルの内容　　　 ★3☆
N7セルの内容　　　 ★4☆
N8セルの内容　　　 ★5☆
N9セルの内容　　　 ★6☆
N10セルの内容　　　 ★7☆
N11セルの内容　　　 ★8☆
N12セルの内容　　　 ★9☆
N13セルの内容　　　 ★10☆
N14セルの内容　　　 ★11☆
N15セルの内容　　　 ★12☆
N16セルの内容　　　 ★13☆
N17セルの内容　　　 ★14☆
N18セルの内容　　　 ★15☆
N19セルの内容　　　 ★16☆
N20セルの内容　　　 ★17☆
N21セルの内容　　　 ★18☆
N22セルの内容　　　 ★19☆
N23セルの内容　　　 ★20☆
N24セルの内容　　　 ★21☆
N25セルの内容　　　 ★22☆
N26セルの内容　　　 ★23☆
N27セルの内容　　　 ★24☆
N28セルの内容　　　 ★25☆
N29セルの内容　　　 ★26☆
N30セルの内容　　　 ★27☆
N31セルの内容　　　 ★28☆
N32セルの内容　　　 ★29☆
N33セルの内容　　　 ★30☆
N34セルの内容　　　 ★31☆

となる筈です。
　次に、N4～N34のセル範囲をまとめて範囲選択してから、[Ctrl]キーを押しながら[H]キーを押して「検索と置換」ダイアログボックスを起動させます。
　次に、「検索と置換」ダイアログボックスの「検索する文字列」欄に

☆

と入力し、続けて「置換後の文字列」欄に

'!Z31

と入力してから、[すべて置換]ボタンをクリックします。
　すると

N4セルの内容　　　 ★1'!Z31
N5セルの内容　　　 ★2'!Z31
N6セルの内容　　　 ★3'!Z31
N7セルの内容　　　 ★4'!Z31
N8セルの内容　　　 ★5'!Z31
N9セルの内容　　　 ★6'!Z31
N10セルの内容　　　 ★7'!Z31
N11セルの内容　　　 ★8'!Z31
N12セルの内容　　　 ★9'!Z31
N13セルの内容　　　 ★10'!Z31
N14セルの内容　　　 ★11'!Z31
N15セルの内容　　　 ★12'!Z31
N16セルの内容　　　 ★13'!Z31
N17セルの内容　　　 ★14'!Z31
N18セルの内容　　　 ★15'!Z31
N19セルの内容　　　 ★16'!Z31
N20セルの内容　　　 ★17'!Z31
N21セルの内容　　　 ★18'!Z31
N22セルの内容　　　 ★19'!Z31
N23セルの内容　　　 ★20'!Z31
N24セルの内容　　　 ★21'!Z31
N25セルの内容　　　 ★22'!Z31
N26セルの内容　　　 ★23'!Z31
N27セルの内容　　　 ★24'!Z31
N28セルの内容　　　 ★25'!Z31
N29セルの内容　　　 ★26'!Z31
N30セルの内容　　　 ★27'!Z31
N31セルの内容　　　 ★28'!Z31
N32セルの内容　　　 ★29'!Z31
N33セルの内容　　　 ★30'!Z31
N34セルの内容　　　 ★31'!Z31

となりますから、続けて今度は、「検索と置換」ダイアログボックスの「検索する文字列」欄に

★

と入力し、「置換後の文字列」欄に

='

と入力してから、[すべて置換]ボタンをクリックしますと

N4セルの内容　　　='1'!Z31
N5セルの内容　　　='2'!Z31
N6セルの内容　　　='3'!Z31
N7セルの内容　　　='4'!Z31
N8セルの内容　　　='5'!Z31
N9セルの内容　　　='6'!Z31
N10セルの内容　　　='7'!Z31
N11セルの内容　　　='8'!Z31
N12セルの内容　　　='9'!Z31
N13セルの内容　　　='10'!Z31
N14セルの内容　　　='11'!Z31
N15セルの内容　　　='12'!Z31
N16セルの内容　　　='13'!Z31
N17セルの内容　　　='14'!Z31
N18セルの内容　　　='15'!Z31
N19セルの内容　　　='16'!Z31
N20セルの内容　　　='17'!Z31
N21セルの内容　　　='18'!Z31
N22セルの内容　　　='19'!Z31
N23セルの内容　　　='20'!Z31
N24セルの内容　　　='21'!Z31
N25セルの内容　　　='22'!Z31
N26セルの内容　　　='23'!Z31
N27セルの内容　　　='24'!Z31
N28セルの内容　　　='25'!Z31
N29セルの内容　　　='26'!Z31
N30セルの内容　　　='27'!Z31
N31セルの内容　　　='28'!Z31
N32セルの内容　　　='29'!Z31
N33セルの内容　　　='30'!Z31
N34セルの内容　　　='31'!Z31

とする事が出来ます。

回答No.1

kagakusuki

例えば一番最初に

='1'!Z31

という参照先の関数を入れるセルのセル番号がB2セルであった場合で、尚且つ下方向にオートフィルして行くものとした場合には、次の様な関数にしますと、参照先のシートを'2'、'3'、'4'と増やして行く事が出来ます。

=INDIRECT("'"&1+ROW()-ROW($B$2)&"'!Z31")

　又、最初のシートを'1'ではなく、例えば'3'にしたい場合には次の様にします。

=INDIRECT("'"&3+ROW()-ROW($B$2)&"'!Z31")

　又、シート名を'1'、'2'、'3'、'4'ではなくSheet1、Sheet2、Sheet3、Sheet4にしたい場合には次の様にします。

=INDIRECT("Sheet"&1+ROW()-ROW($B$2)&"!Z31")

　又、シート名を'1'、'2'、'3'、'4'ではなくSheet2 (1)、Sheet2 (2)、Sheet2 (3)、Sheet2 (4)にしたい場合には次の様にします。

=INDIRECT("'Sheet2 ("&1+ROW()-ROW($B$2)&")'!Z31")

　又、シート名は'1'、'2'、'3'、'4'とするものの、下方向ではなく、右に向かってオートフィルして行く場合には次の様にします。

=INDIRECT("'"&1+COLUMN()-COLUMN($B$2)&"'!Z31")

補足

初めての質問で分かりずらいですよね？

Seet1(1)～Seet31(31)日報を作っています。
別のSeet（仕入表）にリンクしています。

仕入表の　ｎ4　='1'!z31 n5 ='2'!z31 1列31行作りたいのです。
現在オートフィルですると
　　　　　ｎ4　='1'!z31 n5 ='1'!z32 　下一桁が増えていくのです。

ご回答頂いた関数　=INDIRECT("'"&1+ROW()-ROW($B$2)&"'!Z31")
をどこに入れればいいのかわからずにいます。

これを作ったものが退職しておりません・・・31行手入力すればいい事なのですが・・
店舗も多く困っています。素人質問で大変申し訳ございませんがよろしくお願いします。