【TODAY関数・DATEDIF関数・CONCATENATE関数】エクセル関数を使った社員名簿・社員台帳の作り方

2016/03/25
Pocket

社員台帳をExcelで作ろう。そう思ってインターネットで検索すると、社員台帳用の無料のテンプレートが多く見つかります。しかし、使っていくうちに「もっとこうできればいいのに」、「この項目は必要無いな」と不便に感じることも出てくることでしょう。それなら最初から全て自分で作った方が、その後の加工がスムーズになりますね。

今回はTODAY、DATEDIF、CONCATENATEといったエクセル関数を使った社員台帳の作り方をご紹介します。

TODAY、DATEDIF、CONCATENATEを使って社員名簿・社員台帳を作ろう!

今回は以下の表を使って社員台帳を作ります。

first

このデータを元に、年齢や勤務年数、住所について関数を使って入力していきます。完成までの手順は以下のようになっています。

ステップ1:TODAY関数で今日の日付を入力する。
ステップ2:DATEDIF関数で年齢を入力する。
ステップ3:DATEDIF関数を応用し、勤務年数を入力する。
ステップ4:CONCATENATE関数を使って別シートの住所録をまとめる。

ではまず、基準日の入力から始めていきましょう!

ステップ1:TODAY関数で今日の日付を入力する。

「いつからいつまで」という期間を表示させるためには、2つの日時を設定しなければいけません。この後、DATEDIF関数を使って入力する年齢、勤務年数に必要な情報となります。「いつから」の部分ですが、こちらは年齢は各社員の誕生日、勤務年数は各社員の入社日が該当し、「いつまで」の部分は現在の日付が該当します。

この現在の日付ですが、シートに記入された日付も毎日変更されていくのが理想です。その設定を可能にするのが、TODAY関数です。

まずはF1のセルに以下の数式を入力します。

today1-2

=TODAY()

このTODAY関数でToday(今日)の日付が入力されますが、この時の注意点は( )を必ず入力すること、パソコンの日付からTODAY関数が日付を表示させるため、パソコンの日付設定を合わせておくことの2点です。

today2

セルの表示形式を変更することで、年などを表示させることもできます。

基準日となる今日現在の日付を表示させることができました。次は、各社員の年齢を入力していきましょう。

ステップ2:DATEDIF関数で年齢を入力する。

DATEDIF関数を使い、各社員の生年月日から年齢を求めます。入力方法は以下のようになっています。

=DATEDIF(開始日、終了日、単位)

最後に入力する「単位」の部分は、期間内の「年」「月」「日」のどれかが当てはまります。今回、ここで入れるのは年齢のため「年=Year」を表す「Y」を使います。ここでは使いませんが、その他は、月は「Month=M」、日は「Day=D」という形になります。

year1


=DATEDIF(D4,$F$1,”Y“)

?開始日
?終了日
?単位

終了日である現在の日時は$を用い、値が動かないよう固定します。

year2

年齢が入力されました。次は応用で、勤務年数を入力しましょう。

ステップ3:DATEDIF関数を応用し、勤務年数を入力する。

先程は年齢の入力であったため、単位は「年」だけでした。今度は勤務年数を「年、月」と2つの単位で表示させます。

kinmu1


=DATEDIF(G4,$F$1,”Y”)&”年”&DATEDIF(G4,$F$1,”YM”) &”ヶ月”

?開始日
?終了日
?単位

「Y」で勤務年数を、「YM」という単位で1年未満の勤務月数を表示させます。年齢は数字のみを表示させていますが、勤務年数は「○年×ヶ月」と表示させるため、それぞれの関数の後に&”年(ヶ月)”を加えることで反映させます。

kinmu2

ステップ4:CONCATENATE関数を使って別シートの住所録をまとめる。

最後に、CONCATENATE関数で複数のセルに分けて入力されている住所を1つのセルにまとめます。

post

ここでは、社員台帳とは別のシートに作られた住所録を使います。郵便番号、都道府県、より詳しい番地がそれぞれ別のセルに入力されています。

post2

=CONCATENATE(‘シート2′!B2,’シート2′!C2,’シート2′!D2,’シート2′!E2)

CONCATENATE関数、繋げて表示させたいセルをコンマで区切ります。2枚目のシートに住所録があるため、「’シート2’!セル番号」と入力していきましょう。

post3

社員台帳の住所のセルに、住所に関するデータが続けて表示されるようになりました。こうすることで、Excel以外のアプリケーションで住所のデータを使おうとした時にも関数で求められた値をコピーして文字列として貼り付けることができます。

 

自分に合ったエクセルの社員名簿・社員台帳作りで業務の効率化を!

今回はTODAY、DATEDIF、CONCATENATEといった関数を使った社員台帳の作り方をご紹介しました。このようにして完成したエクセルの社員台帳について、更にVlookup関数を使えば社員データの管理が簡単になりますね。

次回もお楽しみに。

■Excel活用術バックナンバー
Vlookup関数とエラー「#N/A」の回避方法を伝授!人材管理に役立つExcel Vlookup術[vol.1]
MATCH関数とVLOOKUP関数の合わせ技!人材管理に役立つEXCEL VLOOKUP術[vol.2]
INDEX関数とVLOOKUP関数を組み合わせて昇給額を算出!EXCEL VLOOKUP術[Vol.3]
VLOOKUP関数で同一の検索値が複数ある場合のデータ抽出術!EXCEL VLOOKUP術[Vol.4]前編
重複する検索値はCOUNTIF関数で個別化しよう!EXCEL VLOOKUP術[Vol.4]後編
VLOOKUP、COUNTIF、MATCH関数を使って勤務管理を効率化!EXCEL VLOOKUP術[Vol.5]

マネたまご マネたまをフォローすれば最新記事をお届けします!
運営会社 | Copyright © kaonavi, inc. All Rights Reserved.