Zenn
👤

【AppSheet】従業員の部署・役職を管理するアプリ

2023/05/25に公開
#
組織
AppSheet
#
人事
tech

はじめに

前回は、度々変更される組織をどう管理していくかというテーマでアプリを作成してみた。
今回は、会社において重要なリソースである従業員に着目してみる。
従業員は、入社してから退社するまで、異動により様々な組織を渡り歩き、経験や実績とともに役職を与えられる。1人1人の情報を漏れなく適切に管理していくことが人事の担当者に求められる。
うちの会社では、一般的なクラウド型の基幹業務ソフトを使っており、大抵のことはできているので実務に支障はないようだが、細々とした要望には費用が伴うプラグインが必要で、十分に満足しているというわけではなさそうだ。
なにはさておき、AppSheetを使って「組織」テーブルと連動した、従業員の部署・役職を管理するアプリを作成したいと思う。
なお、アプリ名は「人事管理アプリ」とする。

役職の仕組みを確認する

前回の組織の模式図をもとに、役職を設定してみる。
役職には1級～4級までの階級があり、役職と階級の組み合わせにより役職手当の金額が決定される。
また、TL待遇ではない所長、PL、SPLは、管理職ではないので、階級は設けてられていない。


役職手当一覧表

データモデルについて

「組織」テーブルは前回3パターン作成したうちの、3つ目の「自己参照型モデル」を使用する。
従業員は異動が通達されることにより、所属する組織や役職が任命されたり、解任されたりする。なお、1人の従業員が複数の組織を兼任することは珍しくなく、その手当は最大金額のものを摘要させる。
また、役職には呼称と待遇の2つを組み合わせるパターンもある。例えば、「支社長（部長2級）」のようなものであり、その従業員の役職手当は待遇によるものを適用させる。
こうした煩雑な人事の仕組みを、「従業員」テーブルと「組織」・「役職」テーブルの間に、「異動」という中間テーブルを置くことで管理させる。


データモデルのイメージ

アプリを作成する

Table：従業員

「従業員」テーブルは、「個人情報テストジェネレータ」というWebアプリから作成したダミーデータを使用する。まずはアプリで生成したデータをスプレッドシートに複写する。
「ID従業員」のカラムには、一旦、🥒さん直伝の関数を入力し、8桁16進数の値をランダムに発生させ、次にその範囲のセルを「コピー → 特殊貼り付け → 値のみ貼り付け」で、関数から値に変換して貼り付ける。

8桁16進数の値を発生させる関数

=DEC2HEX(RANDBETWEEN(0, 16^8),8)


従業員テーブルのソースデータ（スプレッドシート）

先ほどのスプレッドシートで作成した従業員のソースデータを、AppSheetのAdd new Dataで選択して取り込み、カラムの設定を行う。　
今回は、必要最小限のカラムを用意したが、これに採用区分（正規社員、派遣社員、パート、アルバイト、嘱託など）や、居住地住所、連絡先などの情報を追加していけばよい。


Table：従業員

https://www.youtube.com/@yomikakiPC/featured

氏名の入力

従業員の氏名は、「鈴木 一郎」のように氏と名の間にスペースを挟んで入力させたいが、入力する人によっては「鈴木 一郎」（半角スペース）だったり、「鈴木　一郎」（全角スペース）だったりするので、「氏」と「名」を別々に入力させたものを、バーチャルカラム（VC）で間の半角スペースを自動的に入れて結合し、氏名のデータとする。

VC氏名：FORMULA／「氏」と「名」の間に半角スペースを入れて結合する

[氏]&" "&[名]

年数の計算

2つの日付から年数を計算するには、それぞれの日付を8桁の数値に変換し差をとる。その差を10,000で割り、小数点以下を切り捨てると年数が計算できる。
例)
今日の日付:2023/05/20
生年月日:1980/05/03
20,230,520 - 19,800,503 = 430,017
430,017 ÷ 10,000 = 43.0017 ≒ 43

VC年齢：FORMULA／「今日の日付」から「生年月日」を引き、年齢を算出する

FLOOR(
  (
    NUMBER(TEXT(TODAY(),"yyyymmdd"))
      - NUMBER(TEXT([生年月日],"yyyymmdd"))
  ) / 10000
) & "歳"

VC在籍年数：FORMULA／在職中の場合は「今日の日付」から「入社年月日」を引き、そうでなければ（退職していれば）「退社年月日」から「入社年月日」を引いて、在籍年数を算出する

IF(ISBLANK([退社年月日]),
  FLOOR(
    (
      NUMBER(TEXT(TODAY(),"yyyymmdd"))
        - NUMBER(TEXT([入社年月日],"yyyymmdd"))
    ) / 10000
  ) & "年",
  FLOOR(
    (
      NUMBER(TEXT([退社年月日],"yyyymmdd"))
        - NUMBER(TEXT([入社年月日],"yyyymmdd"))
    ) / 10000
  ) & "年"
)

従業員の経歴

「異動」テーブルのうち、その従業員に該当するレコードの一覧が経歴となる。

VC現在の所属部署：FORMULA／異動テーブルから、在籍中でかつID従業員が同じレコードを抽出し、ID異動のリストを作成する

SELECT(異動[ID異動], 
  AND(ISBLANK([解任年月日]), [ID従業員] = [_THISROW].[ID従業員])
)

役職手当の計算

従業員が複数の組織に従事している場合、その手当は最高金額のものを摘要させる。

VC役職手当：FORMULA／異動テーブルから、在籍中でかつID従業員が同じレコードを抽出し、VC役職手当のリストを作成し、その最大値を求める

MAX(
  SELECT(異動[VC役職手当],
    AND( ISBLANK([解任年月日]) , [ID従業員] = [_THISROW].[ID従業員])
  )
)

従業員リストの在籍確認

従業員のリストは、「在籍中」か「退社」かで分けて表示したいため、バーチャルカラム（VC）でグループ分け用のラベルを用意しておく。

VC在籍：FORMLUA／「退社年月日」が空欄であれば「在籍」とし、値が入力されていれば「退社」とする

IF(ISBLANK([退社年月日]), "在籍", "退社")

View：従業員

VC在籍で用意した「在籍」か「退社」でグループ分けし、一覧表として表示させる。また入力がし易いように、Column orderで表示させるカラムとその順序を設定している。


View：従業員

Table：組織

「組織」テーブルは、AppSheetのAdd new Dataから、前回作成した「組織図3」アプリの「組織」テーブルを取り込む。


Table：組織

組織に所属している従業員のリスト

現在、その組織に所属している従業員は誰かを確認できるように、バーチャルカラム（VC）で「VC現在の所属従業員」というリストを用意している。これは、後に説明する「異動」テーブルのうち、現在、有効となっている（解任されていない）レコードの中から、その組織に該当する異動のリストが、最新の従業員のリストということになる。

VC現在の所属従業員：FORMULA／異動テーブルから、解任されてなく、かつID組織が同じレコードを抽出し、ID異動のリストを作成する

SELECT(異動[ID異動], 
  AND(ISBLANK([解任年月日]), [ID組織] = [_THISROW].[ID組織])
)

View：組織

組織のビューでは、色々とグループ分けを試してみたが、どれもいまいちだったので、とりあえずグループには分けず、親子構造のみが分かるように表示させている。
たまたま便宜的に組織の名称を「A～U」までのアルファベットとしていたため、Sort byで「VC組織名」の昇順で並び替えると、フォルダー形式のように表示されているが、実際にはこうはならないと思う。


View：組織

Table：役職

「役職手当」は、「役職名」と「階級」の組み合わせで管理するようになっている。
後に説明する「異動」テーブルでは、Refで役職を選択する際に、「役職」と「階級」が同時に分かった方が分かりやすいので、「VC役職名（階級）」というバーチャルカラム（VC）を持たせて、「役職」テーブルのLABELに設定している。


Table：役職

階級の設定

階級はEnum型で、「1級」「2級」「3級」「4級」の値を、ボタン形式で選択できるように設定している。

役職名の表記の仕方

前述でも述べたように、「役職」テーブルのLABELは「VC役職名（階級）」に設定しているが、所長／PL／SPL／支社長の役職には階級がなく、「（階級）」の部分が不要のため、下記のように処理している。

VC役職名（階級）：FORMULA／「階級」が空欄でない（階級がある）ときは、「役職名（階級）」で表記し、そうでないときは「役職名」のみで表記する

IF(ISNOTBLANK([階級]),  [役職名]&"("&[階級]&")",  [役職名])

役職に該当している従業員のリスト

組織テーブルの時と同様に、「VC現在の該当従業員（役職）」と「VC現在の該当従業員（待遇）」という２つのバーチャルカラム（VC）を用意して、現在、その役職に該当する従業員と待遇が確認できるようにしている。

VC現在の該当従業員（役職）：FORMULA／異動テーブルから、在籍中でかつID役職が同じレコードを抽出し、ID異動のリストを作成する

SELECT(異動[ID異動], 
  AND(ISBLANK([解任年月日]), [ID役職] = [_THISROW].[ID役職])
)

VC現在の該当従業員（待遇）：FORMULA／異動テーブルから、在籍中でかつID待遇が現在のID役職と同じレコードを抽出し、ID異動のリストを作成する

SELECT(異動[ID異動], 
  AND(ISBLANK([解任年月日]), [ID待遇] = [_THISROW].[ID役職])
)

View：役職

役職ビューは、今のところ役職名と役職手当が分かればよいので、必要最小限に表示するようにしている。


View：役職

Table：異動

最後に今回の肝となる「異動」テーブルを設定する。
「従業員」テーブルと「組織」・「役職」テーブルの間に置く中間テーブルで、Refを用いて各テーブルと紐づけする。
1人の従業員が複数の組織と役職を持つことがあるので、「解任年月日」に日付が入力されているか否かで、現在、従事している組織やその役職が分かるようする。
また、役職には呼称と待遇の2つを組み合わせるパターンもあるので、「ID待遇」として該当する役職のIDを持つようにする。


Table：異動

「従業員」テーブルとの紐づけ

選択する従業員は、在籍する従業員の中から選択し、退社した従業員は除外する。


異動：ID従業員

Valid If／従業員テーブルから、在籍中のID従業員のリストを作成し、在籍年数で降順に並び替える

ORDERBY(
  SELECT(従業員[ID従業員], ISBLANK([退社年月日])),
  [VC在籍年数], TRUE
)

「組織」テーブルとの紐づけ

選択する組織は、稼働している組織の中から選択し、廃止された組織は除外する。


異動：ID組織

Valid If／廃止年月日が空欄のもの

ISBLANK([ID組織].[廃止年月日])

「役職」テーブルとの紐づけと待遇の設定

選択する役職には条件はつけておらず、「役職名（階級）」の中から選択する。
また、役職とは別に待遇を選択する場合のテーブルは、同じ「役職」テーブルを使用する。


異動：ID役職


異動：ID待遇

役職手当の摘要

上位の「従業員」テーブルに役職手当を渡すために、バーチャルカラム（VC）として用意しておく。
従業員が複数の組織と役職に従事している場合もあるので、保有している手当の中から、最高金額のものを摘要させる。

VC役職手当：FORMULA／有効な異動のうち（「解任年月日」が空欄のもの）、待遇の設定がなかったら役職に相当する役職手当を取得し、待遇が設定されていれば、その待遇に相当する役職手当を取得する。無効な異動の場合は「""」（BLANK／空欄）を返す

IF(ISBLANK([解任年月日]),
  IF(ISBLANK([ID待遇]), [ID役職].[役職手当], [ID待遇].[役職手当]),
  ""
)

異動のレコードが有効か無効かを判別

異動が発生した時は「新任」のフラグを立て、その職務が解かれた時に「解任」のフラグをたてることで、当該のレコードが有効か無効かを判別させる。

VC新任／解任：FORMULA／「解任年月日」が空欄だったら「新任」を、そうでなかったら「解任」を返す

IF(ISBLANK([解任年月日]), "新任", "解任")

View：異動

「異動」ビューは、まず「新任」（有効）か「解任」（無効）かでグループを大別し、その次に異動の日付（「異動年月日」）でグループ分けして、最新のものから（降順）に表示させる。


View：異動

テーブルのリレーションを確認する

SettingsのDataからRelationshipsで、テーブルのリレーションを確認してみる。
データモデルのイメージ通り、「従業員」テーブルと「組織」テーブル・「役職」テーブルが「異動」テーブルを挟むように紐づけされていることが確認できる。


リレーション

アプリの動作を確認する

Desktop mode(Preview)と各テーブルのスプレッドシートで、アプリの動作を確認する。

従業員のビュー

従業員のビューでは、現在、その従業員が所属している組織と役職、過去に所属してきた組織と役職の履歴（異動履歴）を確認することができる。


Desktop mode（従業員のビュー）


ソースデータ（従業員）

組織のビュー

組織のビューでは、現在、その組織に所属している従業員と、過去に所属したことのある従業員の履歴（異動履歴）を確認することができる。


Desktop mode（組織のビュー）


ソースデータ（組織）

役職のビュー

役職のビューでは、現在、その役職に該当する従業員と、過去に該当していた従業員の履歴（異動履歴）を確認することができる。


Desktop mode（役職のビュー）


ソースデータ（役職）

異動のビュー

異動のビューでは、異動の日付ごとにグループ分けされた従業員のリストを確認することができる。


Desktop mode（異動のビュー）


ソースデータ（異動）

最後に

今回は、AppSheetを使って会社の人事を管理するアプリを作ってみた。
イメージ通りのものができたとは思うが、実務で使ってもらえるかどうかは、基幹業務ソフトとの比較で決まる。
人事の担当者によると、定期的に発生する書類作成の自動化など、細々とした要望に応じた機能の追加や変更を行うには、別途プラグインが必要になり費用が発生するので、簡単にはアップデートの要望を出すことはできないとのことだった。
AppSheetの長所としては、アップデートの手軽さや用途・応用の範囲が広いといったことになると思うが、いずれにしてもヒアリングからモデリングまで、SEとしてのスキルが問われることになるのは間違いない。

https://www.appsheet.com/Template/AppDef?appName=人事管理アプリ_SampleRelease-122981919&appId=人事管理アプリ_SampleRelease-122981919&quickStart=False#Data.Columns.異動

Discussion