１個前の特定セルから値を参照したい

質問

http://okwave.jp/qa/q5535868.html

に似ているのですが（かつ、これだとエラーが出た）

以下のような条件です。

シートは複数あり、これらは全部同じ条件でコピペで作られます。
特定の場所は、必ず、１か所、前の同じ場所から参照で作られます（１番最初の部分は直接値が入ります）

シート名は不特定の名前が入ります。

このような場合、式はどのように書けばよいでしょうか？
Excel2013です。

よろしくお願いします。

回答No.5

kagakusuki

後、VBAを使用して、シートのタブが並んでいる順番で、1つ前のシートの指定したセルを参照するユーザー定義関数(Excelに元々備わっている関数ではなく、Excelの仕様者が新たに作ったオリジナルの関数)というものも存在します。

【参考URL】
　インストラクターのネタ帳 > エクセルマクロ・Excel VBAの使い方 > ユーザー定義関数 > 左隣のシートを相対参照
　　http://www.relief.jp/itnote/archives/003281.php

補足

ありがとうございます。

現在、Officeが不安定な状態で、検証できない状態です。

回答No.4

kagakusuki

>これでは仕様を全く満たしません。
>Sheet1でない可能性もあるわけです。

　勘違いしないで下さい。回答No.2の関数はSheet1に入力されている値を返すものでは御座いません。
　参照すべき元データが入力されているシートはあくまで「あ」シート、「い」シート、「う」シート、「え」シート、「お」シートなどといった、Sheet1のリストにシート名が記載されているシートであり、Sheet1はあくまで「それらの元データが入力されているシートがどれとどれであるのか」という事と「どの順番でセルの値を参照して行くのか」という事を示すためのリストを設けておくために使用しているに過ぎません。
　ですから、回答No.2に対する質問者様の補足コメント

>[Sheet1]
>A2 B2 C2 D2
>-- 1 -- =B2*2

にある様に、Sheet1というシート名のシートを「元データを入力するためのシート」として使用されるのであれば、シート名のリストを設けておくためのシートとしてはSheet1を使う事は出来ませんから、リストは「Sheet6」や「Sheetま」などといった使用していないシートに設けるようにせねばなりません。
　その際には当然、各シートに設ける関数も

=IF(ISERROR(1/(INDIRECT("'"&INDEX(Sheet6!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheet6!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]")<>"")),"",INDIRECT("'"&INDEX(Sheet6!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheet6!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]"))

=IFERROR(INDIRECT("'"&INDEX(Sheet6!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheet6!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]"),"")

=IF(ISERROR(1/(INDIRECT("'"&INDEX(Sheetま!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheetま!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]")<>"")),"",INDIRECT("'"&INDEX(Sheetま!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheetま!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]"))

=IFERROR(INDIRECT("'"&INDEX(Sheetま!$A:$A,MATCH(REPLACE(CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),1,FIND("]",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]),FIND(".xl",CELL("filename",[この関数を入力するセルの相対参照形式のセル番号]))),),Sheetま!$A:$A,0)-1)&"'![前のシートにおける参照元のセルの(シート名を含まない)セル番号(この場合はD2)]"),"")

などの様に変更せねばなりません。
　リストにシート名を並べる順番も参照して行く順番に合わせて、上から順番に並べておかなければなりません。
　もし、シート名が変更になった場合には、それに合わせてリストに記載されているシート名も、その都度変更せねばなりません。

補足

ありがとうございます。

現在、Officeが不安定な状態で、検証できない状態です。

回答No.3

imogasi

直接回答ではないが、下記の理解は必須です。No２回答にも出ているようです。
参考までに記してみます。
参考までに似ているというWEB
http://okwave.jp/qa/q5535868.html
で、出ている関数の意味は理解できましたか。
ーー
左が関数を入れたセルの値　右は入れる関数を示す。
このブックのシート名をCELL関数で取り出す
私の開いているブックのあるシートSheet1を前提に説明します。
=CELL("filename",A1) C:\Users\XX\Documents\[２セルXX.xlsm]Sheet1
[の位置（先頭からの何文字目か）
22 =FIND("[",CELL("filename",A1))
]の位置（先頭からの何文字目か）
33 =FIND("]",CELL("filename",A1))

上記フルパスとシート名からシート名を切り出す
２セル判定.xlsm =MID(D9,22+1,D12-D11-1)
シート名部分を取り出す
=RIGHT(D9,LEN(d9)-A13) Sheet1
ーーー
この質問は
・特定の場所
・前の同じ場所
・不特定の名前
のような一般性を持たせようとする表現（実は現実の事項を隠したいのだろう）があります。
何もわかりにくくせず、シート名が、S1、S2,S3 と３つあって=S2!A３のように書くのがなぜ都合が悪いのか理由を書いてある方がありがたい。
＞前の同じ場所から
前のシートの同位置セルから、ということか
ーー
関数ではシートタブ的に直前とか直後を表すことはできないと思います。
シートが作られた来歴では、前や次があっても、移動されることがあり得る。
ーー
しかしVBAでは、やや似たことができます。
しかしシートタブは自由に位置を移動できるので、あまり使い道はないと思います。
VBAでは
Sheet1,Sheet2,Sheet3,Sheet4の４つのシートがシートタブ的に左からこの順序であるとします。
Sub test02()
Worksheets("Sheet4").Range("A1").Select
MsgBox ActiveSheet.Previous.Name
Worksheets("Sheet2").Activate
MsgBox ActiveSheet.Next.Name
End Sub
のように.Previousや.Next があります。
またシートのいんでっインデックス番号というのもあります。
Sub test03()
MsgBox ActiveSheet.Index
MsgBox Sheets("Sheet4").Index
End Sub
シートタブの位置は固定できないと思います（ブックやシートを保護する方法は別にして）。
すると「前」は不安定になります。
ですから具体的にシート名を使わざるを得ない。
改めてシート名を入力して使うのが嫌なら、回答に出るだろう、複雑な関数を使わないと、ほかに、しかたがないと思う。
VBAではシートの特定は下記の指定で行える。
・名前
・インデックス番号
・Active
・Previous/Next
・その他（多分ないと思う）
ふつうは「名前による指定」になるということ。

補足

ありがとうございます。

現在、Officeが不安定な状態で、検証できない状態です。

回答No.2

kagakusuki

今仮に、御質問の件に関わるデータが入力されているシートが5枚あり、1番最初のシートのシート名が「あ」で、2番目のシート名が「い」、3番目のシート名が「う」、4番目のシート名が「え」、5番目のシート名が「お」であるものとします。
　そして、2～5番目のシートのB2セルに、「1つ前のシート」のC3セルの値を表示させるものとします。

　まず、上記以外のシートで、使用していない適当なシート(ここでは仮にそのシートのシート名がSheet1であるものとします)のA列に

A1セル　　シート名
A2セル　　あ
A3セル　　い
A4セル　　う
A5セル　　え
A6セル　　お

の様に、各シート名を途中に空欄が無い様に順番に入力されているリストを作成して下さい。
　尚、A1セルの「シート名」は単に何のリストであるのかを解り易くするために入れた項目名に過ぎませんので、必ずしも入力しておかなければならない訳では御座いません。

　その上で、2番目のシートのB2セルに次の様な関数を入力して下さい。

=IF(ISERROR(1/(INDIRECT("'"&INDEX(Sheet1!$A:$A,MATCH(REPLACE(CELL("filename",B2),1,FIND("]",CELL("filename",B2),FIND(".xl",CELL("filename",B2))),),Sheet1!$A:$A,0)-1)&"'!C3")<>"")),"",INDIRECT("'"&INDEX(Sheet1!$A:$A,MATCH(REPLACE(CELL("filename",B2),1,FIND("]",CELL("filename",B2),FIND(".xl",CELL("filename",B2))),),Sheet1!$A:$A,0)-1)&"'!C3"))

　尚、もし「1つ前のシート」のC3セルが空欄だった場合において、同様の空欄ではなく、0が表示されてしまっても構わないという場合には、下記の様な関数にしてしまっても構いません。

=IFERROR(INDIRECT("'"&INDEX(Sheet1!$A:$A,MATCH(REPLACE(CELL("filename",B2),1,FIND("]",CELL("filename",B2),FIND(".xl",CELL("filename",B2))),),Sheet1!$A:$A,0)-1)&"'!C3"),"")

　次に、2番目のシートのB2セルをコピーした後で、3～5番目のシートを纏めて選択してから、そのまとめて選択したシートのB2セルに対して、貼り付けを行って下さい。

　以上です。

お礼

お礼欄ですみません。

ファイル名も変更になる可能性もあります。

補足

ありがとうございます。
これでは仕様を全く満たしません。
Sheet1でない可能性もあるわけです。

単純に構造的に書くとこのようになります。

※順番が「ばらばらに書いてあるように見えますが、実際のシートの順番はこのとおりです
[Sheet1]
A2 B2 C2 D2
-- 1 -- =B2*2

[Sheet2]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet1のD2の内容

[Sheet4]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet1のD2の内容

[Sheet3]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet4のD2の内容

[Sheetは]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet3のD2の内容

[Sheetほ]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝SheetはのD2の内容

[Sheet5]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝SheetほのD2の内容

以下略・・・

回答No.1

shintaro-2

>１か所、前の同じ場所から

日本語として、意味不明です。

エクセルのセル番号で具体例を複数示してください。

お礼

お礼欄ですみません。

ファイル名も変更になる可能性もあります。

補足

単純に構造的に書くとこのようになります。

※順番が「ばらばらに書いてあるように見えますが、実際のシートの順番はこのとおりです
[Sheet1]
A2 B2 C2 D2
-- 1 -- =B2*2

[Sheet2]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet1のD2の内容

[Sheet4]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet1のD2の内容

[Sheet3]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet4のD2の内容

[Sheetは]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝Sheet3のD2の内容

[Sheetほ]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝SheetはのD2の内容

[Sheet5]
A2 B2 C2 D2
-- ＊ -- =B2*2
＊＝SheetほのD2の内容

以下略・・・