[[20190302184836]]　『最終提出日を管理したい』（なとー）　ページの最後に飛ぶ

『最終提出日を管理したい』（なとー）

提出物の管理をexcel上でしております。
提出はだいたい週一で、提出した際には「○」をリストから選択してます。
列は1週目、2週目…と右に5週目まであり、行は各個人の名前が50行ほどです。
シートは月毎に変わり、シート名は「yyyy年m月度」でコピーを作成するで左にシートを増やしてます。

1列は各個人番号
2列は各個人名
4～8列2行目にm/d表記での日付
4～8列、3～50行で「○」を選択するかしないか

のようなフォーマットです。

・最後に提出した日(当月の一番右に「○」が入力されているセルの列の2行目)を読み取り9列に表示させたい
・人によっては3ヶ月以上提出がない場合があり、シートを3つ以上跨ぐことがあるので、「○」が当月にない場合は右隣のシートの9列を読み込ませたい

これをexcelの関数で行いたいのですが可能でしょうか？
詳しい方教えて下さると助かります。

説明不足なところもありますので、逆質問も遠慮なく下さい。

お願いいたします。

＜使用 Excel：Excel2013、使用 OS：unknown ＞

追記です

        １  ２  ３  ４  ５  ６  ７  ８  ９
１
２                     日  日  日  日  日  
３    番  あ        ○       ○              
４    番  い
５    番  う        ○  ○  ○       ○
６    番  え
７    番  お        ○  ○  ○  ○  ○
８    番  か        ○  ○  ○  ○  ○
 :
 :

のようになってます。
この場合、あ氏は６列３行目のセル、い氏え氏は先月(右隣のシート)の最終提出日、う氏お氏か氏の3名は８列３行目のセル
をそれぞれ読み取らせるような関数が知りたいです。

何卒よろしくお願いいたします。
（なとー） 2019/03/02(土) 19:52

○がある場合はできますよね。

ない場合は、問題はシート名だけですよね。

＞当月にない場合は右隣のシートの9列を読み込ませたい

どにかのセルに隣のシート名を用意できませんか。

（マナ） 2019/03/02(土) 20:21

 名前定義
 名前に「右シート」

 参照範囲に下記数式

 =MID(INDEX(GET.WORKBOOK(1)&T(NOW()),GET.DOCUMENT(87)+1),FIND("]",INDEX(GET.WORKBOOK(1)&T(NOW()),GET.DOCUMENT(87)+1))+1,31)

 =右シート

 ってやれば、シート名が出ます。
 （一応古いマクロなんで、マクロを有効にしないとエラーになります。
（BJ） 2019/03/02(土) 21:10

お二方、ありがとうございます。

右隣のシート名を表示させるセルを用意することはできました。

ここからはどうすれば良いのでしょうか？
すみません、excel初心者なもので…
（なとー） 2019/03/02(土) 22:37

右シートの同じ行でよいなら
=INDIRECT(右シート&"!I"&ROW())

行が違うなら、Match関数で行数を調べるとか。

（マナ） 2019/03/02(土) 22:46

ご教示の通り、＝indirect…の関数で
右シートの同じセルにある値を反映させることができました！
ありがとうございます。

しかし、
「この範囲で一番右に○があるセルの2行目のセルの値を反映,○がなければ"右シートの同セルの値を反映(＝indirect…)"」
という動きまでが上手くいきません。
常に"右シートの…"状態です。
iferrorを使っているのですが、間違っているのでしょうか？
（なとー） 2019/03/03(日) 01:45

＞iferrorを使っているのですが、間違っているのでしょうか？

＞という動きまでが上手くいきません。

間違っていないと思いますが、
どんな式にしていますか？

（マナ） 2019/03/03(日) 01:57

＝iferror(vlookup("○",「○を入力する範囲」,「日付入力する範囲」,false),indirect…

です。
「」内は、実際の式ではRC[-5]等の表記となっていて見づらいため
ここでは日本語を代入しております。

すみません、最近excelを触り始めたもので
式もめちゃくちゃだと思いますが、訂正していただけると助かります。
（なとー） 2019/03/03(日) 03:40

=IFERROR(INDEX(R2C4:R2C8,MATCH("なとー",RC[-5]:RC[-1])),INDIRECT(右シート&"!I"&ROW()))

で、できませんか？

（マナ） 2019/03/03(日) 05:50

あら、INDIRECTのところが、R1C1形式になっていなかった。

（マナ） 2019/03/03(日) 06:14

INDIRECT(右シート&"!RC",FALSE)

（マナ） 2019/03/03(日) 06:27

ありがとうございます。

IFERROR(INDEX(R2C2:R2C8,MATCH("○",RC[-5]:RC[-1])),INDIRECT(右シート&"!RC",&FALSE))

で○の一番右を読み込むことまでできましたが、
当月に○がない場合は右シートの同セルを読みに行かず、「#REF!」のエラーが出てしまいます。

間違っているところの訂正お願いします。。。
（なとー） 2019/03/03(日) 11:30

わたしには、わかりません。

こんなときは、数式を組み立てるときの逆操作です。
分解して期待通りになるか確認してください。

 １）=MATCH("○",RC[-5]:RC[-1])
 ２）=INDEX(R2C2:R2C8,MATCH("○",RC[-5]:RC[-1])
 ３）=INDIRECT(右シート&"!RC",&FALSE)
 ４）=IFERROR(INDEX(R2C2:R2C8,MATCH("○",RC[-5]:RC[-1])),INDIRECT(右シート&"!RC",&FALSE))

（マナ） 2019/03/03(日) 11:57

 INDIRECTは、必要部分のみ使う。

 INDIRECT(右シート&"!RC")
（BJ） 2019/03/03(日) 12:08

○がある場合は正常に動作しておりますので、
エラーの場合の関数が違うのでしょうか？

エラーの場合の関数は
INDEX(右シート&"!RC")
です。

これはエラーの場合、右シートのシート名を表示するように指示している
ということですか？
（なとー） 2019/03/03(日) 13:38

１）～４）の結果はどうでしたか。
期待通りでしたかと質問したのですが…

（マナ） 2019/03/03(日) 14:07

すみません、プレビューで終わってしまったのか更新できていませんでした。

1)○がある場合1/3と表示される . ○がない場合#N/A
2)○がある場合正常 .○がない場合#N/A
3)○がある場合#REF .○がない場合#REF
4)○がある場合正常 .○がない場合#REF

です。
（なとー） 2019/03/03(日) 14:32

１）と２）は期待通りの結果ということですか？
式の意味が理解できていると考えて良いですね。

３）４）は、そもそも、こちらでは、
「この数式には問題があります。」
となるのですが？
本当にその数式で確認していますか？

（マナ） 2019/03/03(日) 14:42

期待通りの動きをしているのは○がある場合の
2) 4)です。
1) 3)は○があっても期待通りではありません。
1)は日付に関係なく、決まって1/3と表示されます。
3)はエラーです。

すみません、訂正です。

13:38の
「エラーの場合の関数はINDEX…」→「エラーの場合の関数はINDIRECT…」です。
11:30
INDEXの後ろのR2C2→R2C4

です。
ご迷惑をお掛けしまして申し訳ありません。
（なとー） 2019/03/03(日) 15:09

順番に

＞1)は日付に関係なく、決まって1/3と表示されます。

これは、セルの書式が日付になっているからで、値は３ですね。
○の入力されているセルや数がかわっても1/3ですか。
この式で、どういう値が期待されるか理解していますか。

（マナ） 2019/03/03(日) 15:29

1)○の位置を色々変えると1/2,1/3,1/4,1/5と動きました。
問題ないようです。
MATCHはその位置から何番目かという答えが返ってくるものだと考えております。

（なとー） 2019/03/03(日) 15:37

では、セルに○以外が入ることはないですね。
２つ、３つ入った場合でも問題ないですか。

（マナ） 2019/03/03(日) 15:50

セルには○か空欄かですね。

色々なパターンで○を入力しましたが、
一番右の○の位置を返してくれています。
（なとー） 2019/03/03(日) 15:53

では、２）の式の意味も理解できているということでよいですか。

（マナ） 2019/03/03(日) 16:02

 >エラーの場合の関数は
 >INDEX(右シート&"!RC") 
 > 13:38の
 >「エラーの場合の関数はINDEX…」→「エラーの場合の関数はINDIRECT…」です。
 ということは、
 INDIRECT(右シート&"!RC")　がエラーになるということですかね。
 ↑は数式として正しくありません。
　マナさんは正しく
 >INDIRECT(右シート&"!RC",FALSE)
 >（マナ） 2019/03/03(日) 06:27
 と書かれていますが、現在の数式はどうなんでしょうか？

（チオチモリン） 2019/03/03(日) 16:33

2)の式も理解しました。

名前定義の「右シート」がどういう動きをしているのかもう一度説明していただけますでしょうか？
私の中では、右隣シートのシート名を表示するという認識なのですが、
間違っていますでしょうか？
（なとー） 2019/03/03(日) 16:43

チオチモリンさん

現在の数式はどうなんでしょうか？

現在は
BJdさんの指摘通り,FALSEを外したり
もう一度付けたりと両方やっていますが、動き自体変わっている感じはしません。
1)～4)も付けた場合と外した場合で両方テストしました。
（なとー） 2019/03/03(日) 16:49

＞私の中では、右隣シートのシート名を表示するという認識なのですが

はい。そんな感じです。
わたしが、最初に考えていたのが↓の方法です。

https://qiita.com/ywindish/items/db8d02be92cbdc533aa1

作業用セルを用意しておいて、シートを追加する際に、
手作業で、右シートの名前を入力すればよいと考えていました。

それを、BJさんが、セルを使わなくても自動で取得できる方法を教えてくれたのです。
わからなければ、セルに名前を都度、入力でよいのではないでしょうか。
そんな手間でもないですよね。
わたしなら、理解できてていないので、そうします。

（マナ） 2019/03/03(日) 17:01

 ＞色々なパターンで○を入力しましたが、 
 ＞一番右の○の位置を返してくれています。

 ホントに？
 ↓ の場合、「3月5日」が返りましたか？

	A	B	C	D	E	F	G	H	I
1									
2				3/1	3/2	3/3	3/4	3/5	
3						○		○

 以上、確認だけ
（笑） 2019/03/03(日) 17:04

＞1)～4)も付けた場合と外した場合で両方テストしました。

 ３）=INDIRECT(右シート&"!RC",&FALSE)
                            ~~~
この &　ば何ですか。他は訂正がありましたが、
この部分については、訂正がないのではなぜでしょうか。

これが原因で、

 ＞３）４）は、そもそも、こちらでは、 
 ＞「この数式には問題があります。」 
 ＞となるのですが？ 
 ＞本当にその数式で確認していますか？

そもそも、提示した式は

 =IFERROR(INDEX(R2C4:R2C8,MATCH("なとー",RC[-5]:RC[-1])),INDIRECT(右シート&"!I"&ROW()))

と

 =IFERROR(INDEX(R2C4:R2C8,MATCH("なとー",RC[-5]:RC[-1])),INDIRECT(右シート&"!RC",FALSE) )

の２通りですが、同様に「#REF!」がでたのでしょうか。

（マナ） 2019/03/03(日) 17:10

＞↓ の場合、「3月5日」が返りましたか？

ホントだだめですね。

（マナ） 2019/03/03(日) 17:17

 参照文字列はＲ１Ｃ１形式なので
 >FALSEを外し
 てはいけません。

 >INDIRECT(右シート&"!RC")
　↑は 右シートのＡ1形式での「ＲＣ」を意味します。

 以下２０１０のヘルプより抜粋
 参照形式  オプションです。参照文字列で指定されたセルに含まれるセル参照の種類を、論理値で指定します。
 参照形式に TRUE を指定するか省略すると、参照文字列には A1 形式のセル参照が入力されていると見なされます。
 参照形式に FALSE を指定すると、参照文字列には R1C1 形式のセル参照が入力されていると見なされます。

（チオチモリン） 2019/03/03(日) 17:19

 ついでに確認

 ＞人によっては3ヶ月以上提出がない場合があり、シートを3つ以上跨ぐことがあるので

 1）前月シートにも○がなかったら、2か月前のシートを参照するということ？
　　最大で何か月前までさかのぼるんですか？
 2）最終的にシートの数は何枚ぐらいになる予定ですか？
 3）「R1C1形式」にしてるんですか？

 とりあえず以上です
（笑） 2019/03/03(日) 17:24

1）前月シートにも○がなかったら、2か月前のシートを参照するということ？　　最大で何か月前までさかのぼるんですか？
右隣のシートの9列を読み込ませると書いております。
今のブックでは2018年9月度が一番右のシートとなっていて、一番左の日付に9/1を作り全て○にしています。
それ以降の日付で○がない場合は9/1と表示される魂胆ですが、間違っておりますでしょうか？

 2）最終的にシートの数は何枚ぐらいになる予定ですか？
1年で12枚にしようと思ってます。

 3）「R1C1形式」にしてるんですか？
あえて、という意味ですか？

ちなみに、3月5日と返りませんでした。
365日全てを試していなかったので気付きませんでした。
（なとー） 2019/03/03(日) 17:42

マナさん

誤字です。すみません。
FALSEの前に&は本来ないものです。
提示された関数と一致しているものを入力して動作させております。

ネットに繋げない環境でPCを触っておりますので、
この掲示板へはiPhoneで入っております。
コピペできず、手入力で間違いが目立ってしまい申し訳ありません。
（なとー） 2019/03/03(日) 17:45

 ＞ちなみに、3月5日と返りませんでした。 
 ＞365日全てを試していなかったので気付きませんでした。

 日付がいつかなんて関係ありません。
 1月であろうが、12月であろうが、F3とH3の2か所が○のとき、
 H2の日付が返りますか、ということ。
 あえてA1形式で表現しましたけど。

 数式の "○" を"ー"にしてみたらどうですか？
 LOOKUP 関数でもできそうですけど。

 これ以上のコメントは控えます。
（笑） 2019/03/03(日) 18:06

＞FALSEの前に&は本来ないものです。

申し訳ありません。だとすると、わたしの関数の知識では
シート名にスペースがあるとかくらいしか思いつきません。

INDIRECT("'"&右シート&"'!RC",FALSE)

（マナ） 2019/03/03(日) 18:08

マナさん
ありがとうございます。
本当ですね。

一番右のシート名に半角でスペースが入っておりました。
「2018年9月度」←
半角スペースを削除すると、「#REF」エラー表示の方は解決しました。

あとは笑さんの指摘通りF3とH3の二ヶ所に○が入った時の問題なのですが、
どうやらG3に○があるかないかで、H2の値が返ってくるかこないか
のようです。

どこを修正すれば良いのでしょうか？

（なとー） 2019/03/03(日) 18:24

＞半角スペースを削除すると、「#REF」エラー表示の方は解決しました。

再掲
＞INDIRECT("'"&右シート&"'!RC",FALSE)

意味は、
[[20060814154532]]　『空白文字を含んだシート名の参照方法』（もんた）

＞あとは笑さんの指摘通りF3とH3の二ヶ所に○が入った時の問題なのですが、

笑さんの 2019/03/03(日) 18:06のコメントは読んでいないのですか？

今回は、これでもよいですが。
＞MATCH("なとー",RC[-5]:RC[-1]))

（マナ） 2019/03/03(日) 19:33

コメント返信：

[ 一覧(最新更新順) ]

[[20190302184836]] 『最終提出日を管理したい』（なとー） ページの最後に飛ぶ

[[20190302184836]]　『最終提出日を管理したい』（なとー）　ページの最後に飛ぶ