エクセルと照合について

質問

エクセルと照合について

工場に勤務していて、シフト表と給与計算をしています。
エクセルの表がうまく貼りつけられず（列がずれるため）、縦書きですいません。
以下はシート1です。

出勤予定表（Ａ１）
Ａさん（Ａ２）
1/1（Ｂ1）←1/1は日付です。
1（Ｂ２）
1/2(Ｃ１)
空欄（Ｃ２）←休日のことです。
1/3(Ｄ１)
3（Ｄ２）
1/4(Ｅ１)
空欄（Ｅ２）
1/5(Ｆ１)
1(Ｆ2)
1/6(Ｇ１)
3(Ｇ２)
以下ＢさんからＥさんまで5人分あります。

以下は勤務時間コードです。
1　5：00～13：00
2　8：00～15：00
3　13：00～21：00

以下はシート2に作成している、実際の出勤日です。

Ａさん（Ａ２）
1/1（Ｂ1）←1/1は日付です。
5：00～13：00（Ｂ２）
1/2(Ｃ１)
空欄（Ｃ２）←休日のことです。
1/3(Ｄ１)
13：00～21：00（Ｄ２）
1/4(Ｅ１)
空欄（Ｃ２）
1/5(Ｆ１)
5：00～13：00（Ｆ2)
1/6(Ｇ１)
13：00～21：00(Ｇ２)
他にＢさんからＥさんまで、シート3からシート6まで作成しています。

要は二つのシートをマクロか関数かであっているかどうか照合したのですが、できるのでしょうか。
目視はできるだけ避けたいです。
超初心者ですいません。

お願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
外していたらごめんなさい。

Sheet2～6までの2行目データは必ず、↓の画像のSheet1のB8～B10のいずれかが入っているものとします。
（もし他のデータが入っているとエラー表示になります）

Aさんだけの方法ですが、他の人も同様です。

Sheet1のB2セルに
=IF(Sheet2!B2="","",INDEX($A$8:$A$10,MATCH(Sheet2!B2,$B$8:$B$10,0)))

という数式を入れ、オートフィルで列（右方向）にコピーすると
画像のような感じになります。

以上、参考になればよいのですが
外している可能性も高いので
その場合はごめんなさいね。m(__)m

回答No.5

imogasi

質問文でシートの情況をセルごとに書いているが、読者には読み取りにくい。
回答者にデータ情況まで作らしているのは良くない。既出回答者が画像まで作って貼り付けている。
また質問表現の仕方を下記表現を参考に勉強のこと。
データ例
３人、４日分で説明
Sheet1
A1:C4　ーは空白セル　勤務タイプのセル範囲部分
- 1月1日 1月2日 1月3日 1月4日
Aさん 1 - 3 2
Bさん 2 3 1
Cさん 1 3 - 3
ーーー
A6:B8　検索表　セル範囲部分
1 5：00～13：00
2 8：00～15：00
3 13：00～21：00
ーー
チェック結果　セル範囲部分
A10:
Aさん 0 #N/A 0 1
Bさん 0 0 0 #N/A
Cさん 0 0 #N/A 1
　　0はOK、１は時間が違う、#N/Aは非勤務日（私の作った式からの表示）。
式を少し変えれば、表示を変えることはやさしい。
ーー
Sheet1のほかにAさん、Bさん、Cさんシートがあるとする。
Aさんシート
B1:F2
1月1日 1月2日 1月3日 1月4日
5：00～13：00 - 13：00～21：00 9：00～15：00
Bさんシート
B1:F2
1月1日 1月2日 1月3日 1月4日
8：00～15：00 13：00～21：00 5：00～13：00 ー
Cさんシート
B1:F2
1月1日 1月2日 1月3日 1月4日
5：00～13：00 13：00～21：00 - 13：00～20：00
ーーー
問題はSheet1の
B10の式　=IF(VLOOKUP(B2,$A$6:$B$8,2,FALSE)=INDEX(INDIRECT($A10&"!$A$1:$X$100"),2,COLUMN()),"0","1")と入れてE10まで式を複写
B10:E10を範囲指定して、＋ハンドルを出し、B１２：E12まで式を複写。
結果は上記。
ーーー
VLOOKUPとINDIRECT関数を利用している。
勤務の無い日は#N/Aガ出ているが、ＩＦ関数をかぶせて、空白かどうか聞いて出さないようにしてください。
ーー
この質問問題ではAさんなどのデータが5：00～13：00のように文字列になっているが、それに従ってやってみたが、これでは時間計算にも使えず、現実的でなかろう。そんなことを質問者はわかっていて、こんな質問に下のかな。

回答No.4

jackson1945

たびたびすいません。画像をつけました。
あと,数式が間違っていました。
シートAのB3=IF(HLOOKUP(B1,出勤!$A$1:$G$6,2,FALSE)=B2,"",HLOOKUP(B1,出勤!$A$1:$G$6,2,FALSE))
シートBのB3=IF(HLOOKUP(B1,出勤!$A$1:$G$6,3,FALSE)=B2,"",HLOOKUP(B1,出勤!$A$1:$G$6,4,FALSE))
シートCのB3=IF(HLOOKUP(B1,出勤!$A$1:$G$6,4,FALSE)=B2,"",HLOOKUP(B1,出勤!$A$1:$G$6,5,FALSE))
シートDのB3=IF(HLOOKUP(B1,出勤!$A$1:$G$6,5,FALSE)=B2,"",HLOOKUP(B1,出勤!$A$1:$G$6,6,FALSE))
シートEのB3=IF(HLOOKUP(B1,出勤!$A$1:$G$6,6,FALSE)=B2,"",HLOOKUP(B1,出勤!$A$1:$G$6,7,FALSE))
それぞれのB4～はB3のセルをコピー貼り付けでOKです。

回答No.3

jackson1945

すいません。NO.1ですが,思いっきりずれてしまいました。
シート1
出勤予定表　1/1　以下日
Ａさん　　　出席
Ｂさん　　　出席
Ｃさん　　　欠席
と考えました

回答No.2

jackson1945

関数でやってみました
Hlookupを使います(実際にやってみました)

シート1
出勤予定表　1/1　1/2　1/3　1/4　1/5　1/6
Ａさん　　　　1　　2　　1　　2　　3
Ｂさん　　　　2　　2　　2　　　　　　　1　　　

シート2
出勤予定表　1/1　1/2　1/3　1/4　1/5　1/6
Ａさん　　　　1　　2　　1　　2　　　　　　　　

1/5を空白にしています
シート2のB3に=IF(HLOOKUP(Sheet2!B1,Sheet1!A1:G2,2,FALSE)=B2,"",HLOOKUP(Sheet2!B1,Sheet1!A1:G2,2,FALSE))
を入力
意味はシート2の1/1の出欠の数字(ここでは1)をシート1の1/1の出欠が同じだった場合空白,違ったらシート1の数字を明記
B3のセル(数式)をB3からG6までコピーすると

シート2
出勤予定表　1/1　1/2　1/3　1/4　1/5　1/6
Ａさん　　　　1　　2　　1　　2
　　　　　　　　　　　　　　　　　3

上記のように1/5の「3」が表示されます。
違いがわかりやすいようにB3～G6を選択し,メニューの書式の条件付き書式でセルの値が1～3の間にして色を付けるとわかりやすいです。
3行目の最後(例だとH3)にcount関数をB3～B6の範囲で指定すると数字が入っている個数がわかりますよ

シート3
出勤予定表　1/1　1/2　1/3　1/4　1/5　1/6
Ｂさん　　　　2　　2　　2　　　　1　　

シート3のB3に=IF(HLOOKUP(Sheet2!B1,Sheet1!A1:G2,3,FALSE)=B2,"",HLOOKUP(Sheet2!B1,Sheet1!A1:G2,3,FALSE))
を入力
シート2と違うのはFALSEの前が3になっただけです。
シート1のＡさんが2行目,Ｂさんが3行目,Ｃさんが4行目ということです

以降は行数を確認して数式を変更するだけです。

シート1だけで確認されたいのであれば,count関数をシート1で参照させればいいと思います。

例えば　シート1の空白セルで「=Sheet2!H3」(例でＡさんのシートで相違の個数をcount関数を算出したセル)とすれば,「1」と出ます。

以上関数ですると大変面倒でした。
lookup関数ばかり使っていたので他にもいい方法があると思います。

アクセスのほうが簡単だと思いますよ。

補足

アクセスですると、どんな感じになるのでしょうか。