[[20221108080226]]　『月別勤務カレンダーから指定した日の勤務者を抽出』（ひろし）　ページの最後に飛ぶ

『月別勤務カレンダーから指定した日の勤務者を抽出』（ひろし）

月ｶﾚﾝﾀﾞｰの中にシフト勤務者が有る表で指定日の勤務者を抽出する。

  A　 B 　C 　D　 E 　F 　G
  日　月　火　水　木　金　土       数字：日、アルファベット：勤務者
3         1 B 2 C 3 D 4 A 5 E
4 6   7   8   9   10  11  12
5 13  14  15  16  17  18  19
6 20  21  22  23  24  25  26 
7 27  28  29  30
　　　
 =IFERROR(VLOOKUP(D10,月別勤務!$A$3:$B$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$C$3:$D$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$E$3:$F$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$G$3:$H$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$I$3:$J$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$K$3:$L$7,2,0),IFERROR(VLOOKUP(D10,月別勤務!$M$3:$N$7,2,0),"無")))))))

関数を短くしたい

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

 >月ｶﾚﾝﾀﾞｰの中にシフト勤務者が有る表
「 1 B 2 C 3 D 4 A 5 E」だけを言っているのですか。
 とするとそのカレンダーで何をしたいのですか。
 >指定日の勤務者を抽出する。
 提示された表と式との関連性が分かりません。

 抽出先は何処にどのようになっているんですか。
 仮のデータを示して説明してください。
（はてな） 2022/11/08(火) 09:35:47

 ＞ =IFERROR(VLOOKUP(D10,月別勤務!$A$3:$B$7,2,0)・・

 その式から判断すると、実際の表はN列まで有って、日とアルファベットは別セルですよね？
 あと、行数が5行しかないですが、6行に亘る月もあるので、1行増やす必要があると思いますけども。

 以上を勘案すると、こうかな？
 　　　　　　　　　　　↓
 =IFERROR(VLOOKUP(D10,OFFSET(月別勤務!$A$3,0,MOD(DAY(D10)+6-月別勤務!$M$3,7)*2,6,2),2,0)&"","無")

（半平太） 2022/11/08(火) 11:13:53

 ＞ =IFERROR(VLOOKUP(D10,OFFSET(月別勤務!$A$3,0,MOD(DAY(D10)+6-月別勤務!$M$3,7)*2,6,2),2,0)&"","無")

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓こうじゃないとまずいかも　　
 　 =IFERROR(VLOOKUP(D10,OFFSET(月別勤務!$A$3,0,MOD(DAY(D10)+6-DAY(月別勤務!$M$3),7)*2,6,2),2,0)&"","無")

（半平太） 2022/11/08(火) 13:38:55

説明不足でした。申し訳ございません。目的は年間勤務表作成です
?@１ケ月カレンダー(横：曜日　縦：週　壁に掛けるタイプ)にシフト者を記入する
　既に自動で12ケ月分作成しシフト者を入力済です
?A１２ケ月分を１年カレンダー一覧表に作成する為ですが既に完成済です。
?Bお願いしたいのは、１ケ月→１年カレンダーに変更する関数が長いので短くしたい

 　　　　１ケ月カレンダー　　　　　　　　　　１年カレンダー一覧表
A B C D E F G H I J K L M N 　　　　　　123456789101112131415…30
日　月　火　水　木　金　土       →  4月BCDAE…                 A
        1 B 2 C 3 D 4 A 5 E　　　　　5月　　
6   7   8   9   　　　　　　　　　　 6月
1年カレンダーの4月1日(D10)に入る関数です(答えはBです)
=IFERROR(VLOOKUP(D10,$A$3:$B$7,2,0),IFERROR(VLOOKUP(D10,$C$3:$D$7,2,0),IFERROR(VLOOKUP(D10,!$E$3:$F$7,2,0),IFERROR(VLOOKUP(D10,$G$3:$H$7,2,0),IFERROR(VLOOKUP(D10,$I$3:$J$7,2,0),IFERROR(VLOOKUP(D10,$K$3:$L$7,2,0),IFERROR(VLOOKUP(D10,$M$3:$N$7,2,0),"無"))))))) 　
関数を短くしたい
（ひろし） 2022/11/09(水) 05:35:29

 ＞関数を短くしたい
 現在の数式は(長いだけで）結果は1年分正しく出ている、と言うことですか？

 検索値がD10セルの参照になっていますが、「(D10)に入る関数」とはどう言うことですか？ 
 循環参照になりませんか？ 別シートのD10に入る関数と言うことなんですか？

 ＞既に自動で12ケ月分作成しシフト者を入力済です
 ２カ月目以降のカレンダーはどこにあるんですか？（セルアドレスが分からないのですが・・）

（半平太） 2022/11/09(水) 08:59:08

質問1：全て正常です。問題なし(1年ｶﾚﾝﾀﾞｰに反映)、但し、数式が長いだけです
質問2：循環参照になりません
質問3：D10(1年ｶﾚﾝﾀﾞｰ)の説明不足でした。以下の通りです
　　　　１年カレンダー一覧表
　　1 2 3 4 5 6 7 8 9 101112131415…30→
４　金土日月火水木土→最初の金のｾﾙ番号はD10です。土はE10です
月　B C D A E… A　　→最初のBに長い数式が入る(=IFERROR(VLOOKUP(D10,$A$3…

５　日月火水木金　　　　　　　　　　火
月

６　水
月
大変申し訳ございませんでした。宜しくお願い致します。
（ひろし） 2022/11/10(木) 04:36:58

 ＞最初の金のｾﾙ番号はD10です。

 １．であれば、最初の長い数式は「(D11)に入る関数」ですよね？

 ＞　　1 2 3 4 5 6 7 8 9 101112131415…30→
 ＞４　金土日月火水木 土→最初の金のｾﾙ番号はD10です。土はE10です
 ＞月　B C D A E… A　　→最初のBに長い数式が入る(=IFERROR(VLOOKUP(D10,$A$3…

 ２．暦上の4月1日は金曜で合っていますが、例示のカレンダーの1日は火曜ですよね？(多分11月のもの）
 　　4月の「B」とどう言う関係にあるんですか？
 　　1カ月を一つ作ると(初日がBの場合）、全ての月の初日に「B」を出すと言うことなんですか？

（半平太） 2022/11/10(木) 10:20:43

 横から失礼します

 >関数を短くしたい
 全く違った提案ですので、余計なお節介でしたら無視してください。

 月別勤務シートのA3:N8が月別のカレンダーとするとき、
 年間カレンダー（例示だと今年の11月かな？）のD10は2022/11/1と日付データとにし、表示形式をaaaで「火」を表示する
 E10は=D10+1として、右にフィル
 D11は、INDEX関数、WEEKDAY関数、WEEKNUM関数を組み合わせ月別勤務シートを参照して、シフトを表示する

 D11 =INDEX(月別勤務!$A$3:$N$8,WEEKNUM(D10)-WEEKNUM($D$10)+1,WEEKDAY(D10)*2)　これを右にフィル

 WEEKNUM(D10)-WEEKNUM($D$10)+1　その月の第何周か→参照する行番号
 WEEKDAY(D10)*2　何曜日か（日付とシフトで一つの曜日に２つ使ってるんですよね？)

（jjj） 2022/11/10(木) 13:30:06

 当初の表
    |[A]|[B]|[C]|[D]|[E]|[F]|[G]
 [1]|   |   |   |   |   |   |   
 [2]|日 |月 |火 |水 |木 |金 |土 
 [3]|   |   |1B |2C |3D |4A |5E 
 [4]|  6|  7|  8|  9|   |   |   
 [5]|   |   |   |   |   |   |   
 2回目の表
    |[A]|[B]|[C]|[D]|[E]|[F]|[G]|[H]|[I]|[J]|[K]|[L]|[M]|[N]
 [1]|   |   |   |   |   |   |   |   |   |   |   |   |   |
 [2]|日 |   |月 |   |火 |   |水 |   |木 |   |金 |   |土 |   
 [3]|   |   |   |   |  1|B  |  2|C  |  3|D  |  4|A  |  5|E  
 [4]|  6|   |  7|   |  8|   |  9|   |   |   |   |   |   |   
 どちらなんでしょうか。
（はてな） 2022/11/10(木) 17:35:11

(答えはBです)?

（?） 2022/11/10(木) 22:50:10

 質問者さんの反応がありませんが、私の提案についての補足です。

 質問者さんの長い式から推測すると、

 VLOOKUPで月別勤務のどこにD10（例示では金）があるかをA列を探し、なければC列、
 そこもなければE列、最後はM列までを列ごとに検索して、見つかったセルの右の値を
 表示させているのだと理解しました。

 D10（金）を検索するということは、検索値は文字列ではなく日付データだろうし、
 月別の方も日付データでなければ、この数式でシフトを求めることはできないと
 思うので、

 カレンダーに書き込まれたデータなら、列ごとに検索せずとも、検索地が当該月の
 第何週の何曜日かがわかれば、自ずと答えが出ると考えての提案でした。

 この長い式を短くするという質問からは外れてますが…

（jjj） 2022/11/14(月) 09:49:45

コメント返信：

[ 一覧(最新更新順) ]

[[20221108080226]] 『月別勤務カレンダーから指定した日の勤務者を抽出』（ひろし） ページの最後に飛ぶ

[[20221108080226]]　『月別勤務カレンダーから指定した日の勤務者を抽出』（ひろし）　ページの最後に飛ぶ