Excelの実践スキルを共有：勤怠データを素早く整理！

前の記事「Excel グラフの学習: ターゲット値を使用した複数系列の複数条件ヒストグラムの作成」では Excel グラフのチュートリアルについて学習しましたが、今日は出席データの整理に関する記事を共有します。チュートリアルはたった 2 つの簡単な公式なので、一度覚えてしまえば、社内に何人いても怖くありません。

勤怠管理マシンはほぼすべての企業で使用されており、毎月初めと月末に勤怠管理マシンからエクスポートされた勤怠データを整理するのに多くの時間がかかります。今日教えます 2つの計算式を使えば、勤怠データを1分で整理できます。

通常勤怠マシンからエクスポートする勤怠データは、Excel で開くと基本的に次の形式になります。

勤怠データをさらに収集するために、上記の形式は、次のような効果にまとめられています。

すべての人の毎日のパンチイン記録が同じ行に記録され、異常は、次の結果に従って比較後にマークされます。通勤時間。

勤務時間は以下の通りです。

午前の勤務時間は8:00、午前の勤務時間は12:00、午後の勤務時間は13:30、午後の勤務時間は次のとおりです。時刻は17:30

このようなエフェクト変換は非常に面倒に感じますが、実は2つの計算式と基本的な操作スキルをマスターすれば1分で完了します。 . それを達成する方法を見てみましょう。

#1. 基本データの並べ替え

#基本データの右側にいくつかの列を追加し、時間を朝の仕事、朝の時間に分割します。仕事を終え、午後の仕事と午後の仕事を終え、対応する時間をマークします。

カード番号、人物、日付の 3 つの列を、カードの対応する位置にコピーします。右にクリックし、「重複の削除」機能を使用します:

「OK」をクリックすると、重複したコンテンツが削除され、1 人あたり 1 日あたり 1 行のみ保持されます:

次のタスクは、対応するパンチイン時間を対応する位置に記入することです。異常なデータには特定の時間を表示せず、「異常」という単語のみを表示します。この目的を達成するには 2 つの公式を使用する必要がありますが、まず最初の公式を見てみましょう。

2. 数式を使用して出勤時間を記録します。

出勤時間の統計を容易にするために、次のようにします。まず、通勤時間に基づいてメモを作成し、次のことを実行する必要があります。 図の効果:

通勤時間に応じて 4 つの状況に分けられます:

1. 8 時より前のチェックインは午前中の出勤とみなされます;

2. 12 時以降のチェックインは午前中の退勤とみなされます。午後の場合、12:00 から 12:30 までのチェックインは午前中に退社したものとみなされると人為的に規定されています;

3. 同様に、13:00 から 13 時までのチェックインも人為的に規定されています:30 は午後に出勤することを意味します;

4. 17:30 以降に出勤することは午後に仕事を終えることを意味します;

5. それ以外の時間に出勤することは無効です。 、空白が表示されます。

セル E2 の数式は次のとおりです:

=IF(D2=$L$1, D2=$P$1,D2=$N$1, $ N$2,"")、ダブルクリックして塗りつぶし、画像の効果を実現します。

最初の IF は です: =IF(D2

When d2( When出勤時刻) が k1 (朝の勤務時間) 以下の場合、if 関数の結果は k2 (「朝の勤務時間」という 4 つの単語) となり、それ以外の場合は null 値が返されます。

2 番目の if は ：IF(AND(D2>=$L$1,D2

d2 (パンチイン時間) が L2 (朝の非番時間) 以上、o1 (人為的に指定されたパンチイン期限) 以下の場合、次の結果が得られます。 if 関数は L2 (「午前中は非番」という 4 つの単語) であり、それ以外の場合は null 値が返されます。

3 番目の if は : IF(AND(D2>=$P$1,D2 # ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

d2 (パンチ時間) が p2 (人為的に指定された出勤開始時間) 以上、M1 (午後の勤務時間) 以下の場合、if 関数の結果は M2 ( 「午後は仕事を休みます」という 4 つの単語)、それ以外の場合は null 値が返されます。

4 番目の if は : IF(D2>=$N$1,$N$2,"")

When d2 (チェックインtime) が N1 (午後の非番時間) 以上である場合、if 関数の結果は N2 (「午後の非番時間」という 4 つの単語) となり、それ以外の場合は null 値が返されます。

備考情報を入力したら、対応する領域に対応する時間を入力します。このとき、数式を使用して右クリックしてプルダウンし、時間の入力を完了することができます。それがどの魔法の公式なのか見てみましょう。バー。

3.充填時間

セル K2 に数式を入力します:

=TEXT(SUMIFS($ D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),"hh:mm:ss;;例外;")

右に引き下げて時間の入力を完了します。

この数式では text と sumifs という 2 つの関数が使用されています。数式の原理を見てみましょう。

sumifs 関数の構造は sumifs (合計が必要なデータ領域、条件領域 1、条件 1、条件領域 2、条件 2...) です。今日の例では、 3つの条件の実際の合計はD列になります 3つの条件は、日付、カード番号、備考情報であり、3つの条件を満たす数字はすべて一意であるため、合計結果と参照結果は一致します。

したがって、式は次のようになります:

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$ E ,K$2)

Excel では日付と時刻の本質は数値と整数であるため、数式を使用して結果を数値にします。日付を表します。小数点は時間を表します。上の領域のセルの書式を時間に変更して、効果を確認してください。以下に示すように、数値は特定の時間になります:

実際には、セルの書式を設定することで、ほぼ目的は達成できましたが、表示効果を向上させて強制的に時刻形式で表示させるために、sumifsの外にテキスト関数を追加し、通常の形式であっても時刻に従って表示されるようにしました。同時に0の位置が異常として表示されます。

テキスト、テキスト（データ、指定された形式）の使用法を簡単に説明します。この例では、2 番目のパラメータの形式は、時、分、秒の表示モードとして定義されています。 、文字 h 、m および s はそれぞれ時、分、秒を表し、どちらも 2 桁で表示されます。

形式コード内のセミコロンは、データの種類に応じて表示モードを個別に設定できます。テキストは、データが正の数、負の数、ゼロ、テキストの 4 つのタイプに分けられることを指定します。この例では、正の数値は時間形式に従って表示され、負の数値とテキストは指定された形式以外は表示されず、ゼロは「異常」という単語として表示されます。

text 関数に関しては、今日はこの例での使用法を理解するだけで済みます。

要約:

1. 補助列の合理的な使用: 出席データの編成は常に厄介な問題であり、多くの場合、 1 ステップで正しく理解するのは非常に困難ですが、現時点では、補助列を適切に使用することで問題の難易度を大幅に下げることができます。

2. この式は万能ではありません。勤怠の基本データは時間であり、関連する規制がデータ間の論理関係を形成します。この例は、複数のシフトが共存する、より標準化された状況に適しています。場合によっては、この方法では実現できず、各人のシフトに応じた通勤時間なども総合的に考慮する必要があり、集計するには勤怠管理機の支援ソフトが必要となります。

関連する学習の推奨事項: Excel チュートリアル

以上がExcelの実践スキルを共有：勤怠データを素早く整理！の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。