ＶＬＯＯＫＵＰで複数シートの検索方法

質問

エクセルで勤務表を作成しているのですが

勤務形態が100種類以上あり、番号付けしています。
勤務形態により、始業時刻が異なり、また、平日・土曜・日祝によりそれぞれの勤務形態で始業時刻が３種類有ります。
平日・土曜・日祝の３種類の勤務形態シートを作成しています
勤務表には勤務番号を入力し指定のセルへ勤務時間を参照する関数がわかりません。。。

平日でA番はA時～ B番はB時～ C番はC時～
同じA番でも土曜ならD時～
また同じA番でも日祝ならF時～
そこで
１日　A番A時～
２日　C番C時～
３日　B番B時～
４日　A番D時～　（土曜）
５日　A番F時～　（日祝）

平日・土曜・日祝の勤務番号の時間は固定でローテーションです

平日シート
　　　A　　　　　B
１　1111　　10：00
２　2222　　11：00
３　3333　　12：00
４　4444　　13：00

土曜シート
　　　A　　　　　B
１　1111　　10：30
２　2222　　11：30
３　3333　　12：30
４　4444　　13：30

日祝シート
　　　A　　　　　B
１　1111　　10：45
２　2222　　11：45
３　3333　　12：45
４　4444　　13：45

３種類のシートから

勤務表シート
　　　A　　　　　　　　B　　　　　C
１　１日（月）　　　　1111　　10：00
２　２日（火）　　　　2222　　11：00
３　３日（水）祝　　 1111　　10：45
４　４日（木）　　　　4444　　13：00
５　５日（金）　　　　2222　　11：00
６　６日（日）　　　　2222　　11：45

このように表示したいのですがいい方法はないでしょうか？
よろしくお願いします。

回答No.1

keithin · ベストアンサー

わざわざシートを分けるのを止めてしまうのが、一番簡単な方法です。

添付図：
シート「一覧」に形態ごとのデータを平日、土曜、日祝と並べます

勤務表シートの、とりあえず理解しやすいようにＢ列に
Ｂ２：
=IF(A2="","",IF(OR(WEEKDAY(A2)=1,COUNTIF(祝日一覧,A2)),4,IF(WEEKDAY(A2)=7,3,2)))
のように用意します

Ｃ列に形態を記入、そのＤ列は
Ｄ２：
=IF(C2="","",VLOOKUP(C2,一覧!A:D,B2,FALSE))
のように、タイプごとに一覧のどの列を取ってくれば良いのかだけで入れ替えます。

計算式のシクミが理解できたら、実際にはＢ列の数式をＤ列の数式の中に直接組み込むことで、Ｂ列を用意する必要はありません。

＃実際にはご相談は「たとえば」で各曜日シートにもっといろいろ参照したいデータがあるんですというお話なんでしょうが、それも例えばＢＣＤ列を各曜日「開始時刻」、ＥＦＧ列は各曜日の「終了時刻」という具合にタイプごとに何列目を決めておけば、同じ数式の工夫で十分出来ます。

＃参考
ご質問の直接の回答として。
もしどーしても曜日ごとのシートをなんとしても絶対準備したくてたまらないなら、
=VLOOKUP(形態,IF(タイプ=日祝,日祝!A:B,IF(タイプ=土曜,土曜!A:B,平日!A:B)),2,FALSE)
という数式にしても構いません。

お礼

ありがとうございました！できました！
一つのシートにまとめると大量のデータ量になってしまい
変更があった際にすぐ修正ができるようにシートを分けておりました。

回答No.2

neKo_deux

平日、土曜、日祝日で勤務番号というのが2222とか3333とか共通していますが、これは同じ数字と考えてよいのでしょうか？
であれば、勤務形態シートというのを１枚にしてしまうのが真っ当だと思います。

勤務形態シート
　　　A　　　　　B　　　　　C　　　　　D
１　勤務番号　平日　　　　土曜日　　　日祝
２　1111　　10：00　　　10：30　　　10：45
３　2222　　11：00　　　11：30　　　11：45
４　3333　　12：00　　　12：30　　　12：45
５　4444　　13：00　　　13：30　　　13：45

勤務表シート
　　　A　　　　　　B　　　　　C　　　　　D
１　日付　　　　　種別　　　勤務番号　　時刻
２　１日（月）　　1　　　　1111　　　　10：00
３　２日（火）　　1　　　　2222　　　　11：00
４　３日（水）祝　3　　　　1111　　　　10：45
５　４日（木）　　1　　　　4444　　　　13：00
６　５日（金）　　1　　　　2222　　　　11：00
７　６日（土）　　2　　　　2222　　　　11：45

=VLOOKUP(勤務番号, 範囲, 列位置, FALSE)
になるように、
D2:=VLOOKUP($C2, 勤務形態シート!$A$2:$D$5, $B2, FALSE)
とか。

お礼

ありがとうございます！