Excelの2つのシート比較について

職場の有給管理について何か良い案がありましたらお教えいただきたいです。

シート1
事前有給申請日を纏めたシート（年度始まりに予め申請する決まりとなっている為12月まであります）

シート2
勤怠システムから実際の有給取得日をダウンロードしたシート
（形式CSVからExcelへ変換）
月締めに毎月ダウンロードします（▲は半日休暇の意味です）

この2つのシートを比較し、実際に事前申請した日に有給を取得しているのかを確認したいです。
申請と取得日が合っていたらセルを黄色、事前申請以外で取得していたら赤などの色分けをする
為に、どんな方法があるか教えていただけたら幸いです。

宜しくお願いいたします。

No.4 回答者： fujillin 回答日時： 2022/03/08 18:11

こんにちは

わざわざ表計算に向かない形式になさっているようなので、その分面倒で複雑なものになります。
また、セル位置を隠したご質問なので、具体例を回答したところで、考え方のみになってしまいますけれど・・・

※ ご提示の例のシート1の「A、B、B、D、E」の行は、「A、B、C、D、E」の間違えと解釈しました。
（個人の対象列が2列以上ある場合には、以下の考え方は使えません）


＞申請と取得日が合っていたらセルを黄色、事前申請以外で取得していたら
＞赤などの色分けをする
色分けをなさりたいのなら、「条件付き書式」ということになりますが、あくまでも例なので、添付図では各表を同一シート内において、「シート2」の方だけに設定してあります。
（同様に考えれば、シート1の方も設定可能でしょう）

「同じ日付（▲付きの場合は変換して）の日付が、シート1の同一人物蘭にあるか否か」を判定式にすれば良いので、

添付図では、
1）B11:H15を選択し、条件付き書式の「数式を～」を選択して数式欄に
=COUNTIF(OFFSET($A$3:$A$9,,MATCH($A11,$B$2:$F$2,0)),IF(LEFT(B11,1)="▲",MID(B11,2,9),B11))>0
を入力し、書式に黄色を設定。

2）上記以外を赤にすれば良いですが、空白セルを除くために、同様に
=(B11<>"")*(COUNTIF(OFFSET($A$3:$A$9,,MATCH($A11,$B$2:$F$2,0)),IF(LEFT(B11,1)="▲",MID(B11,2,9),B11))=0)
の式を入力し、書式に赤（添付図では橙ですが）を設定。

という設定にしてあります。


※ テストする場合は、添付図と同じものを作成して、「日にち」や「人名」を変えるなどしてみてください。
※ 日付は（▲付きのものを除き）全てシリアル値と仮定しています。

この回答へのお礼

回答ありがとうございます。

お礼、大変遅くなり申し訳御座いません。
皆さまのご意見を参考にし、シートを作成し直しております。
また質問する事があるかと思いますが、どうぞ宜しくお願いいたします。

お礼日時：2022/03/14 15:18

No.3 回答者： mygoonickname 回答日時： 2022/03/08 14:34

No.1の者です。

シート１と、シート２のセルの位置が不明なので、仮に下記として記載。
シート１のAが、セルC2に入っていて、シート２の21/12/29がセルC2に
入っているとして、下記の様な式を入れるでしょうか？

シート２のAの右側の空欄(21/12/29の下)に下記関数を入れる。
=COUNTIF(OFFSET([Book1]Sheet1!$C$3:$C$10,0,(ROW()-3)/2),C2)
※[Book1]は、シート１のブック名を入れる。
※$C$3:$C$10は、シート１の22/4/8の日付の列のセル範囲を指定
※(ROW()-3)/2　この部分の3は、関数を入れる行の数字に合わせる。
※C2は、１つ上のセルの、21/12/29のセルを参照
シート１に同じ日付があれば、シート２の関数の結果が１と表示される
ので、それを見て、色は質問者さんの方で塗る。
この色を元に、シート１に色を塗る。　これも手作業。

この回答へのお礼

回答ありがとうございます。

毎月シート2はダウンロードする事から始める事を考えると、1つ1つ手作業の方が早い気もします。
もう少し表示形式を整えてやりやすい形にしてみます。
ありがとうございました。

お礼日時：2022/03/08 15:26

No.2 回答者： 銀鱗 回答日時： 2022/03/08 13:35

シート１に記入された日付がシート２にあるかという事を確認したいという事でしょうか。

ならば、
個人別にBookが用意されていて、
且つ、
シート２のB列に有給取得した日付があるとするなら……。

シート１のA１セルに入力されている日付をシート２のB列から拾う場合は、
MATCH関数やCOUNTIF関数を使うと良いでしょう。

　＝MATCH(A1,シート2!B:B,0)
　＝COUNTIF(シート2!B:B,A1)
これで「１」以上の値が返れば一致した日付があると判断できる。

関数の使い方を確認してください。
難しいことではありません。

この場合のポイントは、別のシートを参照するとき
　シート名 ＋ ！ ＋ セル番地　
という形で参照することです。

　＝A1
　↓
　＝Sheet2!A1

　＝A1:A10
　↓
　＝Sheet2!A1:A10

こんな感じ。


・・・

質問文に添付された図を見ると、個別ではなく全員分をまとめてあるようですので、
上記のやり方を応用しましょう。
応用できないのであれば、一つずつ手作業で処理しましょう。その方が結果的に早く終わります。

この回答へのお礼

回答ありがとうございます。

大元の有給管理シートが●(有給),〇(半日休暇）をカレンダーに入力して管理している為、一度日にちを書き出しました。
1人当たり9日ほどしか事前申請（1年分）でありませんが、別シートへ分ける
必要がありますでしょうか？
MATCH関数やCOUNTIF関数で一致した日のカウントで返したいのでは無いの
です。申請日＝取得日、申請日以外の日付を知りたいのです。
上手く伝えられず申し訳ありません。

お礼日時：2022/03/08 13:59

No.1 回答者： mygoonickname 回答日時： 2022/03/08 13:30

こんにちは。

直接の回答ではありませんが、どちらのシートに色を付けるのでしょうか？
例えば、色を付けるブックに、シートを１枚追加し、もう片方のデータを
コピーして、行列を入れ替えて貼り付け。
条件付き書式で、追加したシートを参照して色を付けるとかでしょうか？

ただ、シート２の日付の先頭に▲があるという事は、そこは文字ですよね？

事前申請以外で取得したかは、どう判定するかですかね？
日付は当然違いますし、▲を除いた個数が合っているか？

この回答へのお礼

回答ありがとうございます。

事前申請と有給取得日が合っていれば両方のシートに色を付ける。
申請以外の取得日にはシート2に色を付ける。
といったイメージとなります。
▲は文字となりますので、文字以外で判定する形です。

お礼日時：2022/03/08 13:45