[[20140919155335]]　『関数のなかであるセルを参照する』（和代）　ページの最後に飛ぶ

『関数のなかであるセルを参照する』（和代）

お世話になります。
Ａ３からＡ３３迄、１日から３１日の日にちが並んでいます。
Ｂ２からＡＳ３迄、名前が表示されています。

Ｂ３の式は=(IFERROR(VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE),""))&" "&('1日'!$T$7&" ― "&'1日'!$U$7)

上記関数で1日はシート名を表わします。シート名は３１日迄あります。

＊上記関数の式を２日から３１日迄とＡＳの名前迄（Ｂ３～ＡＳ３３）にコピーしたい
　のですが、行、列以外のシート名はコピーするときに自動で変化しません。

いちいち、一つの関数を４箇所の日を手で替え、それを全箇所を修正するのは、３行ほど
やり始めたのですが、細かい箇所を修正するので目が疲れて来ました。
そこで色々考えて、ＡＶ３～ＡＶ３３迄の場所に１日から３１日表示しました。

そしてＢ３の式の1日をＡＶ３に入れ替えるとＡＶ列の１日が表示されると思ったのですが＃ＲＥＦ！になります。

どなたかご教示して頂けないでしょうか？

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 INDIRECT関数？
　
（GobGob） 2014/09/19(金) 16:33

 あぁ。。。シート名ね。。。。

 =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A2)),"")

 これでシート名取得

 んで、INDIRECT。

 =INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A2)),"")&"'!T7")

 見たいな感じ？
　
（GobGob） 2014/09/19(金) 16:37

GobGob様
早速のご返答有難うございました。

?@=INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A2)),"")&"'!T7")は
?@の式は、具体的には何処に入れるのでしょうか？

また下記Ｂ３の式に応じて答えを表示するようにしているのですが、－－－?@とＢ３の式の関連は
どのようになるのでしょうか？教えて下さい。

Ｂ３の式は=(IFERROR(VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE),""))&" "&('1日'!$T$7&" ― "&'1日'!$U$7)
（和代） 2014/09/19(金) 17:03

 数式のシート名＆セル参照してる所を例示したINDIRECT関数式に置き換える
 ですわ。

（GobGob） 2014/09/19(金) 18:06

 INDIRECT関数を使う話なら
[[20140820133226]]　『項目の配置が違うデータの抽出』（和代）
 でやったと思いますが。。。
（HANA） 2014/09/19(金) 18:29

GobGob様　HANA様、
有難うございます。

今、会社から帰ってきました。
会社にファイルがありますので
明日午後から出社したときに確認を致します。
（和代） 2014/09/19(金) 19:01

HANA様
　　「INDIRECT関数を使う話なら
　　　[[20140820133226]]　『項目の配置が違うデータの抽出』（和代）」
－－この時は、確か　この関数を使うと重たくなるので使わず、別シートで名前が縦、日にちが横並び
のデータを下記関数が入っているシートにコピー、貼り付け（行列を反対）で作成しうまくいきました。

HANA様が別シートは必要ないのではと言われ、その通りだと思い削除しました。

後に使用する担当者が、残業も入れてほしい（当初は残業する人は一人でまれな為にいらないと言って
いました）
と言われ項目を増やした為にやむをえず=(IFERROR(VLOOKUP('1日'!$S8&"_"&'1日'!$T8,マスターコード結合!$F$2:$G$66,2,FALSE),""))　&" "&('1日'!$S8&" ― "&'1日'!$T8)　の$S8&"_"&$T8の前後ＳＴをＴＵに
変更しました。それをコピー貼り付けするときにシート名だけが変化しませんでした。
　　
GobGob様へ
　「数式のシート名＆セル参照してる所を例示したINDIRECT関数式に置き換える」－－－
色々置き換えたのですが「引数が多すぎます」とメッセージが出てうまく行きません。

（あてすっぽうに入れた一例です）：=INDIRECT("'"&REPLACE(CELL("1日",$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE),""),1,FIND("]",CELL("1日",$T$7&" ― "&'1日'!$U$7)),"")&"'!T7")

私は応用が利きませんので、コピー元の関数を教えて下さい。お願い致します。

（和代） 2014/09/20(土) 16:45

 まずはヘルプをみてみたら？

 =INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!T7")

 「T7」ってのを変更するだけ。

 =(IFERROR(VLOOKUP(INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!T7")
&"_"&INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!U7")
,マスターコード結合!$F$2:$G$66,2,FALSE),""))&" "&(INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!T7")
&" ― "&INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!U7")
)
　
（GobGob） 2014/09/22(月) 08:00

 ↓ の最後のコメントだけ読むと、既に解決したようなことが書かれていますね。
[[20140820133226]]　『項目の配置が違うデータの抽出』
　
　
 シートの状況等がよくわからないのですが、
 やりたいことを簡単に言うと ↓ のようなことではないのでしょうか？

 =VLOOKUP('1日'!$C$1,F:G,2,FALSE)

 この数式のシート名（1日～31日）を1個1個手直しせずに、
 オートフィルすればシート名が変わる方法。
　
　
 A1からA31に「1日」から「31日」までが入力されているとして

 B1 =IFERROR(VLOOKUP(INDIRECT("'"&A1&"'!C1"),F:G,2,FALSE),"")

 B31までコピーします。

 それともINDIRECTは揮発性関数なので使いたくないということなのでしょうか？
　
（ウルトラマリン） 2014/09/22(月) 13:58

今、何気なくこのサイトを見ました、お返事が入っているので驚きました。
有難うございます。

GobGob様
有難うございます。
下記の通り入力しましたが、「入力した数式は正しくありません」と出ました。

=(IFERROR(VLOOKUP(INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!T7")&"_"&INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!U7"),マスターコード結合!$F$2:$G$66,2,FALSE),""))&"&" ― "&INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!U7") "&(INDIRECT("'"&REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")&"'!T7")　

どこが違っているでしょうか？

ウルトラマリン様
有難うございました。

　B1 =IFERROR(VLOOKUP(INDIRECT("'"&A1&"'!C1"),F:G,2,FALSE),"")←&"'!C1"とは何でしょうか？
これがわかれば本来の下記の式に入れたいと思いますが、宜しくお願い致します。

＊本来の式は、（Ｂ３から結果が入ります。）B3　=(IFERROR(VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスター
コード結合!$F$2:$G$66,2,FALSE),""))&" "&('1日'!$T$7&" ― "&'1日'!$U$7)の式です。

意味は、１日のＴ７（出勤時間）とＵ７（退勤時間）をシート名：マスターコード結合（コード番号と出勤退勤の時間を＆でくくりコードの時間帯を縦列に並べています。)
一例、(31 B1 9 ― 13)--Ｔ７が９時、Ｕ７が１３時ならば、?@１　Ｂ１です。プラス
"&('1日'!$T$7&" ― "&'1日'!$U$7)---?A出勤時間と退勤時間が表示　Ｂ３の結果は?@+?Aで、
(31 B1 9 ― 13)になります。　

（和代） 2014/09/22(月) 17:47

 > B1 =IFERROR(VLOOKUP(INDIRECT("'"&A1&"'!C1"),F:G,2,FALSE),"")←&"'!C1"とは何でしょうか？

 簡単な例として ↓ の式で説明しています。
 =VLOOKUP('1日'!$C$1,F:G,2,FALSE)

 VLOOKUPの検索値が全てのシートでC1だとしたらということです。
 ですので、それはC1セルのことです。

 "'!C1"　←　「"」で囲っていますから、これは文字列。
 右下どちらにコピーしても変化しませんので「$」を付ける必要はありません。
　
　
 質問を読んで、疑問に思うところがありますので、それも書いておきます。

 > Ａ３からＡ３３迄、１日から３１日の日にちが並んでいます。
 > そこで色々考えて、ＡＶ３～ＡＶ３３迄の場所に１日から３１日表示しました。
　
 A3:A33 と AV3:AV33 は何がどう違うのでしょうか？
　
　
 > Ｂ２からＡＳ３迄、名前が表示されています。

 2行目と3行目に名前が表示されているんですよね？
 なのにそのVLOOKUPの式を入れるセルもB3からなんですか？
 それとも名前が表示されているのは2行目だけの間違いなんでしょうか？
　
　
 > B3　=(IFERROR(VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE),""))&" "&('1日'!$T$7&" ― "&'1日'!$U$7)

 この式をB3から右方向にもコピーするんですよね？
 これでしたら、右にどこまでコピーしても全部同じ結果になると思うんですが、そんなことありませんか？

 A3:A33に「1日」から「31日」が入力されているとして
 元が下の式だとすれば

 =VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE)
　　↓
 =VLOOKUP(INDIRECT("'"&A3&"'!T7")&"_"&INDIRECT("'"&A3&"'!U7"),マスターコード結合!$F$2:$G$66,2,FALSE)

 右方向にもコピーする場合は　A3 → $A3 に（ただし右方向の結果は全て同じ）
　
　
 > Ｔ７が９時、Ｕ７が１３時ならば、?@１　Ｂ１です。
 > ---?A出勤時間と退勤時間が表示　Ｂ３の結果は?@+?Aで

 文字化けでしょうか、何が書いてあるのかよくわかりません。
　
（ウルトラマリン） 2014/09/23(火) 13:15

ウルトラマリン様
今晩は、お世話になります。

　
「そこで色々考えて、ＡＶ３～ＡＶ３３迄の場所に１日から３１日表示しました。」ーー何故このように
　書いたのかわかりません。日付は、A3:A33 が正しいです。迷わせてすみませんでした。

「Ｂ２からＡＳ３迄、名前が表示されています。」－－ＡＳ２迄です。
　「それとも名前が表示されているのは2行目だけの間違いなんでしょうか？」その通りです、
　これも何でＡＳ３としたのかわかりません。すみませんでした。

「この式をB3から右方向にもコピーするんですよね？、

 これでしたら、右にどこまでコピーしても全部同じ結果になると思うんですが、そんなことありません？」
 －その通りです、それで同じ結果になるので手で変更します。例えばＴＵ列を７から８、９１０、と
　右に最後の名前の人迄数値を変更していきます。そして横列の名前をコピーして、Ｂ３からＢ３３迄に
　貼り付をしたのです。

　「Ｔ７が９時、Ｕ７が１３時ならば、?@１　Ｂ１です。

 　 ---?A出勤時間と退勤時間が表示　Ｂ３の結果は?@+?Aで
　 　文字化けでしょうか、何が書いてあるのかよくわかりません。」
　　－－文字化けです。下記のことを書きたかったのです。
　　
　　Ｔ７が９時、Ｕ７が１３時ならば、シート名：マスターコード結合より、参照範囲!$F$2:$G$66,2の中に
　　９－１３があればＧ列、コードの31  B1を表示します。そして"&('1日'!$T$7&" ― "&'1日'!$U$7)の式に
　　より出勤と退勤の時間を表示させ、Ｂ３の関数の結果は３１　Ｂ１　９－１３となります。この結果を
　　コピー、貼り付けをしたかったのです。

 A3:A33に「1日」から「31日」が入力されているとして
 元が下の式だとすれば

 ?@=VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE)
　　↓
 ?A =VLOOKUP(INDIRECT("'"&A3&"'!T7")&"_"&INDIRECT("'"&A3&"'!U7"),マスターコード結合!
　$F$2:$G$66,2,FALSE)

?AをＢ３に入力すれば　#N/Aになります。

（和代） 2014/09/23(火) 20:05

 本題とは関係ありませんが、丸数字は文字化けするので (１) (２) 等にしてください。
（Mook） 2014/09/23(火) 20:14

 > Ｂ３に入力すれば　#N/Aになります。

 B3に ↓ の式を入れたらどうなりますか？
 =VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE)

 この式だったらエラーにならず正しい値が返るのに、
 INDIRECTを使った方の式はエラーになるということでしょうか？

 こちらで試してみましたが、上の式が正しい値を返しているのであればエラーにはなりません。
 #REF! ではなく #N/A なんですよね？
 もう一度よく確認してみてください。
　
　
 それが解決してから ↓ に進んでください。

 > それで同じ結果になるので手で変更します。例えばＴＵ列を７から８、９１０、と

 B列はT7セルが検索値で、右方向にコピーしたらT8、T9、T10……になればいいんでしょうか？

 INDIRECT("'"&A3&"'!T7")
　　↓
 INDIRECT("'"&$A3&"'!T"&COLUMN(G3))

 上のように変更すればいちいち手で変更しなくても、オートフィルでコピーするだけで T7、T8、T9、T10……と変化します。
 COLUMN(G3) はG列の列番号を返しますから「7」のことです。

 最終的には

 B3 =IFERROR(VLOOKUP(INDIRECT("'"&$A3&"'!T"&COLUMN(G3))&"_"&INDIRECT("'"&$A3&"'!U"&COLUMN(G3)),マスターコード結合!$F$2:$G$66,2,FALSE)&" "&INDIRECT("'"&$A3&"'!T"&COLUMN(G3))&" ― "&INDIRECT("'"&$A3&"'!U"&COLUMN(G3)),"")

 この式を下に33行目までオートフィルし、そのまま右方向にオートフィルします。
 この式を入れたがために動きが重くなるかどうかは、そちらの環境で確認してみてください。
　
　
 > そして横列の名前をコピーして、Ｂ３からＢ３３迄に貼り付をしたのです。
 　
 おっしゃっている意味がわかりませんでしたので、これについては何も考えていません。
　
（ウルトラマリン） 2014/09/24(水) 03:23

ウルトラマリン様
今から外出します。
今晩、おそがけですが見ますので
申し訳ございません。

（和代） 2014/09/24(水) 08:29

Ｂ３に入力すれば　#N/Aになります。

 [B3に ↓ の式を入れたらどうなりますか？
 =VLOOKUP('1日'!$T$7&"_"&'1日'!$U$7,マスターコード結合!$F$2:$G$66,2,FALSE)
 この式だったらエラーにならず正しい値が返るのに、
 INDIRECTを使った方の式はエラーになるということでしょうか？
 こちらで試してみましたが、上の式が正しい値を返しているのであればエラーにはなりません。
 #REF! ではなく #N/A なんですよね？
 もう一度よく確認してみてください。]

私は、> Ｂ３に入力すれば　#N/Aになります。と#ＲＥＦではなく#N/Aと書いていますが‐‐‐

最終的には B3 =IFERROR(VLOOKUP(INDIRECT("'"&$A3&"'!T"&COLUMN(G3))&"_"&INDIRECT("'"&$A3&"'!U"&COLUMN(G3)),マスターコード結合!$F$2:$G$66,2,FALSE)&" "&INDIRECT("'"&$A3&"'!T"&COLUMN(G3))&" ― "&INDIRECT("'"&$A3&"'!U"&COLUMN(G3)),"")でうまくいきました、これでほっとしました。あとは今後利用できますように覚えていきます。

有難うございます。

（和代） 2014/09/24(水) 20:53

コメント返信：

[ 一覧(最新更新順) ]

[[20140919155335]] 『関数のなかであるセルを参照する』（和代） ページの最後に飛ぶ

[[20140919155335]]　『関数のなかであるセルを参照する』（和代）　ページの最後に飛ぶ