更新日: 2024年11月25日


IV. DBを使って人事評価から特許活用まで(1)

1. 人事評価システム構築の背景

2001年、FTTH用受光素子の事業化が中止となった後、駄待ち狐は知財(特許)戦略担当になりました。会社には連綿と知財部門は存在していましたが、特許出願件数はトップクラスなのに知財収支(ライセンス収入と特許使用料支出の差分)は大赤字という状況に業を煮やした本社経営陣が技術部門傘下組織に知財戦略担当を置くことにしたのです。駄待ち狐には別のプロジェクトで開発技術者を続けるという選択肢もあったのですが、「二択の時は経験したことのない方を選ぶ」という天邪鬼な性格のため自らこの道を選びました。家庭でやるべきことが多すぎて残業できないという事情と、研究開発はやり尽くしたという思いもありました。

そんな中、2003年に新たな人事評価システムを構築するという話が降って湧きました。その当時、駄待ち狐は分社の研究所(100名規模)に所属していましたが、そこで独自の人事評価制度を立上げようとしていました。もちろん人事部門も関っていますが、推進役は所長の命を受けた技術企画課です。研究所幹部による喧々諤々を経て新しい評価制度の運用が始ったのですが、当初は所員全員がExcel帳票に記入してメールで提出し、技術企画課が一覧表にまとめるという運用でした。この一覧表にまとめるという作業に技術企画課の実務担当者が音を上げ、何とかIT化できないかという話になったのです。

ここまでの話では、知財戦略担当の駄待ち狐と人事評価システムの構築には何の接点もなさそうですが、然に非ず。駄待ち狐が所属していたのは知財・技術助成課というところで、技術助成というのは技術企画課がやりたくない邪魔くさい仕事を担当する役回りでした。そこで、技術企画課はIT化を技術助成に丸投げしてきたのです。新たな制度を立上げるのは技術企画課にふさわしい高度な業務であるが、IT化などという些末で面倒な仕事は技術助成にやらせておけという訳です。駄待ち狐も知財・技術助成課の一員として見て見ぬふりはできません。

技術企画課としては、技術助成が要件定義だけして、100万円程度の予算で零細SIerに丸投げ(孫投げ)するものだと思っていました。しかし、人事評価制度そのものがまだ流動的な段階で要件定義をして、社内の状況が全く分からないSIerにシステムを作らせたのでは、完成は遅れに遅れ、その間に要件そのものが変ってしまい、追加料金で揉めて・・・炎上必至です。これこそアジャイル型開発(当時はまだそんな言葉を聞いたことはありませんでしたが)の見本みたいな案件です。そこで、駄待ち狐は「そのシステムは私が作ります」と宣言したのです。

2. システム導入前の運用

IT化する前に所員全員が記入していたExcel帳票は以下のようなものです。中央の大きな面積を占めている部分に最大で5項目まで、期(半年間)の初めに目標、期の終りに実績と補足説明を本人が記入して上司と面談します。目標には3段階の難易度、実績には5段階の達成度を本人と上司がそれぞれ数値で記入し、マトリクステーブルでその項目の評価が決ります。項目にはトータル100%となる業務比率も記入し、その加重平均で業績点が決定されるという仕組みです。業績点とは別に学会・論文等のドキュメント点、特許出願等の知財点があり、その合計点で所員の成果を順位付けするのです。

このExcel帳票でも、マトリクステーブルによる点数決定や合計点の計算は自動化されています。問題は全員の帳票から業績点・ドキュメント点・知財点を抽出した一覧表を作成する部分です。Excelで一覧表を作成すれば点数順にソートすることは簡単ですし、マクロを使えば一覧表の作成も造作ない・・・ようですが、一覧表は所員全員に対して一律に作る訳ではありません。会社には職位があって、職位ごとにランク付けをする必要があります。Excel帳票にも職位は記載されていますが、この欄の記載内容でグループ化して、それぞれのグループに対して一覧表を作る必要があります。

ここまででもExcelのマクロで対応できそうですが、一番ややこしいのが人事査定会議で個人帳票が修正されることです。一般社員の査定は課長以上が集って目合せされ、他の課の課長や部長、所長の口出しで業績点が変る(大体は低くされる)のです。さらにドキュメント点や知財点にケチがつくこともあり、突出成果の点数はそもそも査定会議で決定されます。その度に該当の個人帳票を修正し、一覧表も再作成です。査定会議は2日がかりになり、初日に行われた会議で出た修正箇所を実務担当者が徹夜で手直しして翌日に再開するという行事が半年毎に繰返されようになりました。これでは実務担当者が音を上げても不思議はありません。

3. 人事評価システムの概要

以上の状況を打開するための最善策は、本人入力 ⇒ 上司承認 ⇒ 査定会議を一気通貫で行う人事評価システムをweb上に構築することです。Excel帳票は使わず、そこに記入していたデータをweb入力してサーバに保存し、そのデータを参照して上司が評価入力と承認を行います。査定会議では個人データを自動集計した一覧表をweb上で表示するとともに、個人帳票の修正が必要な場合は直接そのデータにアクセスして修正します。リアルタイムで自動再集計を行えば、「徹夜の手直し」は不要になって会議はサクサク進みます。「私が作ります」と宣言した時から、駄待ち狐の頭にはこの全体像が出来上っていました。

システムを作る上での最初の課題は本人確認です。誰でも他人の帳票が見れたり、上司になりすまして承認できたりしては人事評価システムとして意味をなしません。幸いなことに、2001年から研究所のホームーページ(HP)が運用されており、個人認証によるアクセス制限もかけられていました(因みにHP運用も技術助成の仕事です)。ユーザIDは社員番号で、初期パスワードは人事データから取得した生年月日ですが、もちろん「パスワードは変えて下さい」というお願いはしてあります。このホームページから人事評価システムにリンクを張り、環境変数としてユーザIDを取得すれば、アクセスしたのが誰かは明白です。

正しくアクセスしなかった場合はNG画面になりますが、環境変数からユーザIDが取得できた場合はメニュー画面が表示されます。「茅野 照樹」という名前はユーザIDを使ってサーバ上の所員名簿(TSVファイル)から検索されたものですが、それ以外の情報も名簿から取得して課長であれば部下の名前を抽出します。自分の個人帳票を記入するボタンは所長を除く全員に表示されますが、茅野照樹さんは課長ではないので部下帳票の承認メニューは表示されません。目合せ会議(査定会議)用の一覧表を表示できるかどうかはCGIプログラムに書込まれており、茅野照樹さんは技術企画課の実務担当者なので一覧表を表示できます。

このサイトで再現したシステムは上記の画面遷移をしますが、動作イメージを掴んでもらうためのもので元のシステムを大幅に簡略化しています。もちろん、社内機密に係るデータは一切含まれていませんし、茅野照樹を始め一覧表に出てくる名前は全て仮名です。左下の画面を開いていただくと、Excel帳票の記入内容と同じであるのが分ると思います。Excel帳票を全くそのままweb画面にしたのでは却って見にくくなりますが、あんまり変えてしまうとこれまた「分かりにくい」と非難されます。駄待ち狐としては絶妙のバランスだと自負しています。

画面をスクロールして出てくる「(3)技術記録」と「(4)特許出願・知財活動」は注釈欄にある通り知財・技術助成課による集計結果が自動的に反映されます。このシステムの導入前から技術助成では所員のアクティビティをリスト管理しており、社員番号をキーとしてデータ結合するのは簡単です。最後に出てくる「業績ポイント内訳」と「評価」はもちろん自動計算ですが、この数値が一覧表の基礎データとして抽出されます。再現システムにはありませんが、上司のメニュー画面には部下のリストが出て、一人一人の帳票を開いて評価入力と承認を行うことができます。ただし、部下の記入内容を書換えることはできません。

右下の画面が本システムの真骨頂、査定会議用の一覧表です。縦軸が個人名、横軸がポイント名ですが、「テーマ業績」等をクリックするとその点数でリストがソートされます。最初の表示は所員名簿順で、左上の「チーム名」をクリックするとこれに戻ります。元のシステムでは、個人名の右側の「GO」ボタンで個人帳票が開きます。この時はオールマイティモードになっており、自動計算以外の全ての欄を修正することができます。個人帳票を閉じる時にその修正は即時反映され、一覧表の点数も修正されます。これはある意味リスキーですが、査定会議で出席者全員が目を光らせているので、ここでの間違いはないものとしています。

4. Perlプログラム

上記のメニュー画面はmokuhyo_menu.cgi、個人帳票画面はmokuhyo_kojin.cgi、一覧表画面はmokuhyo_list.cgiというPerlのCGIによって表示されますが、元のプログラムはそれぞれ625行、2,179行、466行あります。全行をここで開示しても、個人情報を含むデータファイル一式を揃えないと動作しません。そこで、mokuhyo_kojin.cgiの全体構成、データベース(DB)機能の実装、Excelファイルへの出力の3点に絞って順次説明します。「Excelファイルへの出力」は「I. 人生の余技として作成したプログラム」の「7-2. VBScript」で紹介を予告した「個人帳票Excel出力」になります。

4.1 mokuhyo_kojin.cgiの全体構成

2,000行以上のプログラムを思いつくままに作ったのでは目標とする動作をする訳もなく、動作しないという結果しか残りません。一方でこの開発はアジャイル型であり、プロトタイプの実運用を通じて出てきた課題(システム要因だけでなく評価制度自体が変更されることもある)に素早く対応していく必要があります。2020年に書かれた「アジャイル型製品開発の成功要因と落とし穴」に「アジャイル型アプローチだからといって業務仕様を定義するドキュメントを軽視してはいけない」との記載がありますが、駄待ち狐は数多く手がけたプログラムの中で唯一この時だけフローチャートとパラメータリストを作成しました。

フローチャートにある1回目というのはメニュー画面から個人帳票を開いた時の動作ですが、新しい期になってまだ個人ファイル(4.2で説明するTSVファイル)がない場合と、再度のアクセスで個人ファイルができている場合があります。前者の場合でもブランクの個人帳票を開くのではなく、ユーザIDに基づいて取得した情報や、技術助成が作成したアクティビティリストに基づくポイントを表示します。2回目以降というのは個人帳票画面にある「記入内容をサーバに保存」をクリックした時の動作で、hiddenでパラメータを渡した上で自分自身(mokuhyo_kojin.cgi)を呼出します。この時はユーザが記入した内容をサーバに書込みます。

一方、パラメータリストにあるmokuhyo_kojin.cgiの呼出モード$OP1とセキュリティ管理情報$SC0~$SC3に基づいて、個人帳票のどの項目に記入できるかを制御しています。<TEXT>タグや<TEXTAREA>タグに"READONLY"属性を指定すると書込み禁止になるので、タグ内に入れた$readonly変数を""にすれば書込み可、"READONLY"にすれば書込み不可となります。また、リスト末尾の帳票者とは個人帳票にデータが表示されている人のことで、本人記入時は記入者と同じですが、上司記入時には部下の情報になります。

4.2 データベース機能の実装

2003年当時、LAMP(Linux+Apache+MySQL+PHP)というDB構築手法は既に存在し、LinuxをWindowsに置換えたWAMPもありました。しかし、プログラミングが本職ではない駄待ち狐はそんな手法を知る由もなく、TSVファイルをDBとしてPerlで読み書きするという一世代前のアプローチをしました。人事評価システムの次に紹介する特許活用システムでWAMPを使うようになるのですが、WAMPそのものが動作するようになるまでに相当な工数を要しました。人事評価システムの初版リリースは時間勝負だったので(時間がかかると「何でSIerに発注しないんだ!」と言われる)、TSVを使う選択は正しかったと思います。

画像を新しいタブで開くと高解像度で見ることができます。

TSVファイルの構成は上記のようになっています。黒字はユーザが記入する項目、青字は自動計算する項目、黄色のハイライトは別のTSVファイルから取込む項目で、16行10列のテーブルになっています。赤字はExcel帳票のセル番号で、次項で紹介するExcel出力で使います。DBとして見ればTSVファイル1つが160個のフィールドを持つレコードであり、所員の人数分のTSVファイルが存在することになります。TSVファイルがきれいに16行に収ったのは単なる偶然ですが、10列にしているのには意味があります。以下のプログラムでTSVファイルを読出せば、@data配列の添字を連番にすることができます。

コードの表示にはPrism.jsを使用しています。
prism.cssの設定は団塊爺ちゃんの備忘録を参考にしました。

#---------------------------------------------
# 個人ファイルからの読出し
#---------------------------------------------
if(open(IN, $KOJIN_FILE)){
	$row = 0;
	while(<IN>){
		$row++;
		s/\n//;
		@data[$row*10 .. $row*10+9] = split(/\t/, $_);
	}
	close(IN);
	foreach(@data){
		s/&%/\n/g;
	}
}

Perlに馴染みのない方のために補足しますと、配列の要素は$data[$i]ですが、配列全体もしくは部分配列はは@dataです。6行目の"while(<IN>)"で読出されたファイルの各行はデフォルト変数$_に代入されます。8行目の"s/\n//;"は\nを空白に置換えることを意味しますが、=~による変数の指定がなければ$_に対して適用されます。つまり、行末の\nが削除されます。9行目の"@data[$row*10 .. $row*10+9]"は、例えば$row=3であれば、($data[30], $data[31], ... $data[39])を意味し、$_をタブで区切ったものが各要素に代入されます。whileループでTSVの全項目が$data[10]~$data[169]に読込まれることになります。

13行目にある"s/&%/\n/g;"はユーザがテキストエリアに記入する際に改行を使った場合の対応です。この改行も\nになるので、TSVにそのまま書込むとそこでTSV自体が改行されてしまいます。これを避けるために、テキストエリアに記入された\nを&%に変換してTSVに書込んでいるので、それを元に戻す処理です。&%に特に意味はないのですが、テキストエリア内に&%と書く人はいないだろうと考えました。最後のgは複数あったら全部置換えるという意味です。一方、以下に示すのは書込みの一例(フローチャートで1回目の最後にあるもの)です。


#---------------------------------------------
# 個人ファイルへの書込み(1回目のみ)
#---------------------------------------------
if(-f $KOJIN_FILE){
	open(OUT, ">$KOJIN_FILE");
	for($row = 1; $row <= $rowmax; $row++){
		for($col = 0; $col <= 9; $col++){
			$_ = $data[$row*10 + $col];
			s/\r\n/&%/g;
			s/\n/&%/g;
			if($col != 9){
				print OUT ($_."\t");
			}
			else{
				print OUT ($_."\n");
			}
		}
	}
	close(OUT);
}

冒頭のif文でTSVファイルが存在するかどうかを確認しているのは、新しい期で最初に個人帳票を開く時はここで書込みを行う必要がないためです。テキストエリアに記入された\nを&%に変換する処理については、Windows上で新たに記入した改行コードは\r\nになっているので、9行目でこれを&%に変換し、残った\nを10行目で&%に変換しています。mokuhyo_kojin.cgiでは個人帳票に対応する一つのTSVファイルに対してデータを読出したり、書込んだりするだけなので、DBが所員の人数分のTSVファイルであったとしても特に問題はありません。

4.3 Excelファイルへの出力

「3. 人事評価システムの概要」で「Excel帳票は使わず」と書きましたが、人事評価システムにはExcelファイルの出力機能もあります。システム自体はExcelと全く無縁で動作しますが、「部下と面談する時にパソコンの画面を見ながらっていう訳にはいかんでしょ。今まで通りExcelのプリントアウトがないと困るよ」という昔気質の課長さんがいるからです。mokuhyo_kojin.cgiの中のExcel出力部分は以下の通りで、webサーバがIISだったのでVBScriptを使いました。しかし、VBScriptは2023年にMicrosoftが非推奨化を発表しており、いずれ廃止されます。現時点でも駄待ち狐が使えるIISはないので、動作確認はできません。


#---------------------------------------------
# VBSによるExcelへの書出し
#---------------------------------------------
print "<SCRIPT LANGUAGE='VBScript'>\n";
print "<!--\n";
print "Sub xlsOut()\n\n";
print "Dim xlsApp\n";
print "Dim pathName\n";
print "Dim fileOrgName\n\n";
print "pathName = 'https://bidingfox.xii.jp/'\n'\n";
print "fileOrgName = pathName & 'kojin_genshi.xls'\n";
print "Set xlsApp = CreateObject('Excel.Application')\n";
print "xlsApp.Visible = true\n";
print "xlsApp.Workbooks.Open fileOrgName\n";

@data_xls[10 .. 169] = @data[10 .. 169];
$data_xls[16] = ("長期目標 ".$data[16]."まで");
for($ii= 2; $ii <= 6; $ii++){
	if($data[$ii*10+4] ne ""){
		$data_xls[$ii*10+4] = $data[$ii*10+4]/100;
	}
}
$data_xls[89] = $data[84] + $data[89];
$data_xls[89] = "" if $data_xls[89] == 0;
$data_xls[104] = $data[103] + $data[104];
$data_xls[106] = $data[105] + $data[106];
@cell_pos[10 .. 19] = (AS2, AV2, BC2, BF2, F5, F6, AF5, AQ5, AY2, BI2);
@cell_pos[20 .. 29] = (A11, H11, Z11, AQ11, F11, V11, W11, "", BD11, BE11);
@cell_pos[30 .. 39] = (A15, H15, Z15, AQ15, F15, V15, W15, "", BD15, BE15);
@cell_pos[40 .. 49] = (A19, H19, Z19, AQ19, F19, V19, W19, "", BD19, BE19);
@cell_pos[50 .. 59] = (A23, H23, Z23, AQ23, F23, V23, W23, "", BD23, BE23);
@cell_pos[60 .. 69] = (A27, H27, Z27, AQ27, F27, V27, W27, "", BD27, BE27);
@cell_pos[70 .. 79] = (E37, T37, U37, E39, T39, U39, E41, T41, U41, T44);
@cell_pos[80 .. 89] = ("", "", "", "", "", "", "", "", "", T46);
@cell_pos[90 .. 99] = (W46, AL46, AR34, AR38, AR42, B2, P2, W2, AD2, AI2);
@cell_pos[100 .. 109] = (AB37, AB38, AB39, "", AL37, "", AL38, AL39, AL40, AL41);
@cell_pos[110 .. 119] = (X11, X15, X19, X23, X27, BH11, BH15, BH19, BH23, BH27);
@cell_pos[150 .. 159] = (BI11, BI15, BI19, BI23, BI27, BJ11, BJ15, BJ19, BJ23, BJ27);
@cell_pos[160 .. 169] = (BF11, BF15, BF19, BF23, BF27, BG11, BG15, BG19, BG23, BG27);

for ($ii = 10; $ii <= 169; $ii++){
	if($data_xls[$ii] =~ /\n/){
		@data_tmp = split(/\n/,$data_xls[$ii]);
		if($#data_tmp == 0){
			print "xlsApp.ActiveSheet.Range(\"$cell_pos[$ii]\").Value = \"$data_tmp[0]\"\n";
		}
		else{
			for($jj = 1; $jj <= $#data_tmp+1; $jj++){
				$cell_pos2 = ("CG".$jj);
				print "xlsApp.ActiveSheet.Range(\"$cell_pos2\").Value = \"$data_tmp[$jj-1]\"\n";
				if($jj == 1){
					$data_tmp2[$jj] = ("=CG".$jj."&CG10");
				}
				elsif($jj == $#data_tmp+1){
					$data_tmp2[$jj] = ($data_tmp2[$jj-1]."&CG".$jj);
					$jj++;
					$cell_pos3 = ("CG".$jj);
					print "xlsApp.ActiveSheet.Range(\"$cell_pos3\").Formula = \"$data_tmp2[$jj-1]\"\n";
					print "xlsApp.ActiveSheet.Range(\"$cell_pos[$ii]\").Value = xlsApp.ActiveSheet.Range(\"$cell_pos3\").Value\n";
				}
				else{
					$data_tmp2[$jj] = ($data_tmp2[$jj-1]."&CG".$jj."&CG10");
				}
			}
		}
	}
	elsif($cell_pos[$ii] ne ""){
		print "xlsApp.ActiveSheet.Range(\"$cell_pos[$ii]\").Value = \"$data_xls[$ii]\"\n";
	}
}
print "End Sub\n'-->\n</SCRIPT>\n";

10~11行目で指定したkojin_genshe.xls(個人帳票の原紙)を12~4行目で開きます。このセルに@data配列を代入していきますが、代入の際に修正が必要な項目もあるので一旦@data_xlsにコピーします。17~26行目がその修正で、日付に日本語を付加し、パーセントを小数に変換し、Excel帳票では集約表示されるポイントを合算します。27~39行目では代入するセルを指定し、@data_xlsと同じ添字の@cell_posにセル番号を割付けます。最後に68行目で$cell_pos[$ii]に$data_xls[$ii]を代入すればOKのはずですが、ややこしいのは@data_xlsに改行コード\nが含まれていた場合の対応です。これが42~66行目の処理になります。

42行目で$data_xls[$ii]に\nが含まれているかどうかを判断し、含まれている場合は43行目で\nで区切って@data_tmpに格納します。44行目にある$#data_tmpは@data_tmpの最後の要素の添字の値で、これが0ということは要素が1つなので68行目と同様にそのまま代入します。要素が2つ以上の場合は、各要素をExcelのセル内改行コードで結合して$cell_pos[$ii]に代入する必要があります。ものの本にはセル内改行コードは"=CHAR(10)"だとありますが、VBScriptの中でCHAR(10)を文字列結合してもセル内改行はされません。

一方、Excel上でセル内改行のないセルと"=CHAR(10)"セルをさらに別のセル上で文字列結合すると、セル内改行が付加されます。そこで、kojin_genshe.xlsで帳票の領域外であるCG10セルに予め"=CHAR(10)"を書込んでおき、CG1~CGn(nは@data_tmpの要素数)に@data_tmpの要素を記入して、セルとして結合するということにしました。それが48~64行目のややこしいforループです。実際にはもっとうまいやり方があると思いますが、時間勝負ということもあって動けばそれでよしとしました。今やこのVBScriptを動かすすべはないので、改めて「うまいやり方」を検討することもできません。


この人事評価システムは2003年の下期から実運用を開始し、2011年下期まで8年半に亘って使われ続けました。当初の予想通り、運用中のシステム手直しは数限りなくありましたが、全て数時間~数日で対応しました。零細SIerに丸投げしていてはとてもできない芸当です。2012年以降に使われなくなったのは、会社全体の体制変更で研究所独自の人事評価制度が維持できなくなったためです。システムの規模、独自性、有用性、可撓性を考えると、駄待ち狐が作ったプログラムの中で最高のものと言えるかもしれません。しかし、所詮は見て見ぬふりができずに引受けた余技、しかもコップ(研究所)の中の嵐(いずれ過ぎ去る)です。

DBを使って人事評価から特許活用まで(2)へ続く