Excelを使ったグラフの作成とグラフ思考

〇 「グラフ思考」とは

• ビジネスで日々発生するデータは、適切に分析を行うことで、ビジネスの成長点や問題点の発見などに結び付く”価値”を生み出します。
• データベースのようにデータ量が多い場合は、Excelのオートフィルターやピポットテーブルで抽出・集計してデータの大小や変化などを分析しますが（⇒Excelを使ったデータベースの作成と活用法参照）、集計結果の表を見るよりは、グラフ化して視覚的に把握する方がはるかに理解が深まります。
• また、例えば給与体系のような数値による基準を作成する場合、その数値が適切なものであるかどうかは作成した数値の入った表を眺めてもなかなか分かりませんが、グラフ化すると、問題点があるか、あるいは問題点がないかが、一目である程度確認できます。（※基準を表すグラフでは、”美しいグラフ”（均整の取れたグラフ）であれば問題が少ないと経験的に感じています。）
• 即ち、グラフ化とは、データ（数値）を視覚化することで、データ分析する思考を効率的かつ効果的に高める手段いうことができます。
• データを分析する手段として、グラフを積極的に活用する考え方を、ここでは「グラフ思考」と呼ぶことにします。

〇 事務実務で使用するグラフの種類

• Excelで用意されているグラフは16種類ほどありますが、事務実務で通常使用するのは、折れ線グラフ、棒グラフ、円グラフの3種類程度ではないでしょうか。普段から使用するグラフの種類を限定し、その作成法を定型化しておくことが、効率的なグラフ作成に必要なことであると考えます。（なお、散布図も使用機会は限られるかもしれませんが、意外と利用価値はあります。）
• 折れ線グラフ…数値が連続した線の上下で表現されるため、分析対象の時間経過による増減を表すのに適しています。⇒ex.売上高や利益額の時系列の変化。
• 棒グラフ………数値が棒の高さで表現されるため、分析対象を構成する要素の大小を比較するのに適しています。⇒ex.売上高や利益額の部署別や事業別による大きさの比較。
• 円グラフ………数値が円を分割する面積の大きさで表現されるため、分析対象を構成する要素の割合を比較するのに適しています。⇒ex.売上高や利益額の部署別や事業別による割合の比較。
• 散布図………数値で表される２つの要素の相関関係を視覚的にとらえるのに適しています。上記３グラフが元の個別データを一定の範囲で集計したものをグラフ化することが多いことに対し、散布図は元の個別データそのものをグラフ化する点に違いがあります。⇒ex.完成工事の請負金額と総利益額の関係、全社員の基準内給与と勤続年数の関係。なお、いわゆる”階段状グラフ”も散布図を利用して作成しますが、規定などによって連続して数値が変化しないものの説明に便利です（一番最後に使用例を掲載しています）。

〇 効率的なグラフ作成例

• 事務管理におけるデータのグラフ化は、（プレゼン資料と違い）グラフ化することそのものが目的ではなく、あくまで分析する思考を助ける手段と捉えるべきであり、このため短時間でグラフ化できることが重要です。一方で、社内説明には使うことも多いと思われるので、一定の見栄えも求められます。
• 過去3年間の四半期決算の状況をまとめた次のような表があるとします。
  
• この表から何らかの目的でグラフを作る場合、その目的に必要なデータだけで、新たにグラフ作成用の表を作成した方が早道です。
• 例えば、売上高の四半期決算状況について各年度の推移を比較したい場合は、グラフ作成用の表の横方向の項目は1Q（第1四半期）～4Q（第4四半期）の時間経過、縦方向は各年度とし、次のようなグラフ作成用の表を作成します（各セルの値は、元の表から計算式（例えばセルJ3は【 =C18 】）で引用しておくと、元の表を修正した場合もグラフが自動で直って便利です）。時間経過による変化を表したいので、グラフの種類は折れ線グラフとします。横方向の留意点として、このような期間実績を表す折れ線グラフは0からスタートする方が分かりやすいので、「期首」という項目も設け、その値を0とします。縦方向の留意点として、直近の年度に対する過年度の比較という意味で、凡例に直近の年度から表示されるよう、年度の新しい順に並べます。
  
• 表（セルH2～M5）をマウスでドラッグして選択し、[挿入]タブの[折れ線グラフの挿入]をクリックし、現れる選択肢のうち、[2-D折れ線]の[マーカー付き折れ線]をクリックすると、グラフが描かれます。続いて、[グラフツール]タブの[デザイン]タブをクリックすると左上2つ目に現れる[クイックレイアウト]を選び、一番左上の[レイアウト1]を選択すると、必要な凡例や軸ラベルなどのグラフ要素が揃ったグラフが次のように出来上がります。
  
• 後は、好みに応じて、グラフの書式を修正していきます。以下は、その一例です。
• グラフ中のグラフタイトルをクリックし、数式バーにイコール記号【 = 】を入力してカーソルをセルH1に合わせると、タイトルがセルH1の内容（四半期決算状況（売上高））になります。
• グラフの上で右クリックし、[プロットエリアの書式設定]を選び、枠線を[線（単色）]として色は濃い目のグレーとします。
• グラフの縦（値）軸を右クリックし、[軸の書式設定]を選び、線を[線（単色）]として色は濃い目のグレーとし、境界値は最大値を[500,000]最小値を[0]、単位は主単位を[100,000]補助単位を[50,000]とします。グラフの縦（値）軸を右クリックし、[目盛線の書式設定]を選び、線を[線（単色）]として色は濃い目のグレーとします。グラフの縦（値）軸を右クリックし、[補助目盛線の書式設定]を選び、線を[線（単色）]として色は薄目のグレーとします・
• グラフの横（項目）軸を右クリックし、[軸の書式設定]を選び、線を[線（単色）]として色は濃い目のグレーとし、軸位置は[目盛]とし、ラベル位置はグラフデータが負となる場合（赤字の場合）も考慮して[下端/左端]としておきます。グラフの横（項目）軸を右クリックし、[目盛線の追加]をクリックします。もう一度グラフの横（項目）軸を右クリックし、[目盛線の書式設定]を選び、線を[線（単色）]として色は濃い目のグレーとします。
• 軸ラベルを右クリックし、[軸ラベルの書式設定]を選び、配置で文字列の方向を[横書き]とします。また、軸ラベルの文字として単位を入れます（ここでは[千円]）。軸ラベルの場所をプロットエリアの大きさに影響しないよう、左上に移動します。
• 各年度のグラフを右クリックし、[データ系列の書式設定]を選び、線の色やマーカーとその枠線の色を好みに変えます。
• 直近の2020年度のグラフを左クリックし、[グラフツール]タブの[デザイン]タブをクリックすると現れる[グラフ要素を追加]を選び、[データラベル]続いて[上]をクリックします。表示されるデータラベルをクリックし、[ホーム]タブの[フォントサイズ]で好みのサイズに変え、[フォントの色]でグラフと同じ色に変えます。（「期首」の数字の0は削除します。 ）
• 縦（値）軸を選び、[ホーム]タブの[フォントサイズ]で好みのサイズに変えます（9ポイントを見易く10ポイントにするなど）。横（項目）軸、軸ラベル、凡例も同様に行います。
• 最後に、[プロットエリア]をクリックしてプロットエリアのサイズを見易く調整したり、[グラフエリア]をクリックしてグラフエリアのサイズを調整します。以上の処理をしたのが次のグラフです。やや面倒ですが、一度好みの形式を作っておくと、後は転用できます。
  
• 次に、総利益の四半期決算状況について、同様に次のような表を作成します。
  
• 先ほどの売上高のグラフをどこか適当なところにコピーし、グラフの上で右クリックし[データの選択]を選び[グラフデータの範囲]として表（セルH8～M11）をマウスでドラッグして選択し、またグラフタイトルをセルH7の内容にします。
• その後、修正する箇所は、縦（値）軸の主単位と補助単位、グラフの線の色、データラベル程度で、その結果が次のグラフです。売上高のグラフ作成時よりは格段に早く好みのグラフができます。
  
• 次に、営業利益の四半期決算状況について、同様に次のような表を作成します。営業利益については、営業赤字を示すマイナスデータもあるので、±0のラインを明確にするため、セルI18～M18に0を入力しておきます。
  
• 先ほどの総利益のグラフをどこか適当なところにコピーし、グラフの上で右クリックし[データの選択]を選び[グラフデータの範囲]として表（セルH14～M18）をマウスでドラッグして選択し（18行目の±0のデータも含めます）、またグラフタイトルをセルH13の内容にします。
• その後、修正する箇所は、縦（値）軸の主単位と補助単位、グラフの線の色（±0の線は黒の破線でマーカーなし）、データラベル程度で、その結果が次のグラフです。
  
• 今度は、各年度の売上高や総利益の推移を土建の要素も入れてグラフにするため、次のような表を作成します。
  
• まず、売上高の推移のグラフを作ります。時系列のグラフですが、要素が入るので、折れ線ではなく棒グラフにします。
• 先ほどの売上高のグラフをどこか適当なところにコピーし、グラフの上で右クリックし[データの選択]を選び[グラフデータの範囲]として表（セルH20～K23）をマウスでドラッグして選択し、またグラフの上で右クリックし[グラフの種類の選択]を選び[縦棒]の[積み上げ縦棒]を選択し、更にグラフタイトルをセルH19の内容にします。この段階のグラフは次のような状態です。売上高（土建の合計）もグラフ範囲に入れているのは、合計の数値のみ表示するためで、グラフそのものは表示を消します（透明にします）。
  
• 棒グラフの売上高の部分の上で右クリックし、[データ系列の書式設定]を選び、[塗りつぶし]なし、[線]なし、とし、棒グラフの土木の部分と建築の部分は塗りつぶしと線を好みの色に変えます。
• グラフの縦（値）軸を右クリックし、[軸の書式設定]を選び、境界値は最大値を[500,000]とします。
• 棒グラフの売上高の部分（この段階で透明となっている）の上で左クリックし、[グラフツール]タブの[デザイン]タブをクリックすると現れる[グラフ要素を追加]を選び、[データラベル]続いて[内側軸寄り]をクリックします。土木の部分と建築の部分のデータラベル位置は中央部分を開けるためそれぞれ[内部外側][内側軸寄り]とします。データラベルの[フォントの色]も好みに変えます。
• 凡例の[売上高]を削除し、最後に、[プロットエリア]をクリックしてプロットエリアのサイズを見易く調整したり、[グラフエリア]をクリックしてグラフエリアのサイズを調整します。以上の処理をしたのが次のグラフです。
  
• 今度は、総利益の推移を折れ線グラフで加えます。グラフの上で右クリックし[データの選択]を選び[グラフデータの範囲]を（セルH20～K26）に拡大し、またグラフの上で右クリックし[グラフの種類の選択]を選び[組合せ]の[ユーザー設定の組合せ]を選択し、売上高は[積み上げ縦棒]、総利益は[折れ線]を指定し、また総利益は[第2軸]にチェックを入れます。
• その後、修正する箇所は、第2軸（総利益の軸）の主単位と補助単位、グラフの線の色、データラベル程度で、その結果が次のグラフです。総利益のデータラベルは、売上高のデータラベルと区別するため右位置にしたうえでアンダーラインを引いてみました。
  
• 今度は、2020年度の売上高や総利益の土建割合をグラフにするため、次のような表を作成します。
  
• 表（セルH29～J31）をマウスでドラッグして選択し、[挿入]タブの[円グラフの挿入]をクリックし、現れる選択肢のうち、[ドーナツ]をクリックすると、グラフが描かれます。続いて、[グラフツール]タブの[デザイン]タブをクリックすると現れる[クイックレイアウト]を選び、[レイアウト6]を選択すると、必要な凡例などのグラフ要素が揃ったグラフが次のように出来上がります。外側の円が売上高、内側の円が総利益です。
  
• 次に、円グラフの上で右クリックし、[データ系列の書式設定]を選び[系列のオプション]で[ドーナツの穴の大きさ]を0%にします。
• その後、修正する箇所は、円グラフの塗りつぶしや枠線の色、データラベル程度で、その結果が次のグラフです。なお、データラベルは、[データラベルの書式設定]の[ラベルオプション]で[系列名]と[パーセンテージ]を選択しています。
  

〇 Excelを使ったグラフ作成の留意点

• グラフは、データを分析するために作成しますが、何を分析するためのものか、即ちグラフの目的は、あくまで1つに絞ります。目的を1つに絞れば、グラフ作成に必要なデータが何かも絞り込めます。
• 手際よくグラフを作るには、これまで見てきたように、必要なデータだけをグラフに適した表の形で整えます。様々なデータの入った大きな表から無理にグラフを作ろうとすると、グラフに不要なデータ部分をデータ範囲から外すなど、かえって時間を食います。ただ、グラフ作成用の表のデータは、できれば元の表から計算式で引用するようにします。そうしておけば、元の表のデータを修正しただけで、グラフも自動で修正されます。
• グラフ用のデータの入った表ができれば、まずはデータ範囲をマウスでドラッグして選択し、[挿入]タブのグラフを選んで自動でグラフを作らせます。もしグラフが望むものではなかったら、グラフの上で右クリックして[データの選択]を選び[行/列の切り替え]を試し、それでも望むものでなかったら、データの入った表の形式を変えてみるほうが早いかもしれません。グラフが概ね望む形であれば、[グラフ要素の追加]や[データ系列の書式設定]などで形式や装飾を整えます。
• 凡例の順序は[データの選択]の[凡例項目]で上下に変更可能ですが、[データソースの範囲]を変更すると再度変更が必要になるため、初めから希望する凡例の順序になるようデータを作った方がストレスが少なく済みます。
• グラフタイトルは、グラフの目的を的確に表す内容で、必ず表記するようにします。できれば、上の例でみてきたように、グラフ作成用の表にグラフタイトルとなる内容を記述し、グラフ中で計算式で引用するようにすれば、表とグラフの双方のタイトルが一致して分かりやすくなります。
• 縦軸の軸ラベルは省略せず、必ず単位を入れます。単位の不明なグラフは、グラフとしての価値がありません。
• 縦軸の最小値は（負のデータがある場合を除き）必ず0とします。新聞などでは変動幅を強調するため最小値を0としないグラフが多々見られますが（例えば全体から見れば数％しか変動していないのにあたかも数十％も変動したように見せるなど）、ビジネスでは全体像（最小値を0とした場合の変動幅）を掴んで判断することが何よりも重要となります。

〇 グラフの事例--貸借対照表

• 取引先の企業分析をするため貸借対照表を見ることは多いと思われますが、グラフ化することで直感的に財務状況が把握できます。
• 鹿島建設の連結貸借対照表を題材にすると、公表されている数値から次のようにグラフ化しやすい表を作ります。資産・負債の各項目はグラフ化を考えて表示順序が逆になっています（例えば資産は、その他固定資産から始まって現預金で終わる）。負債の「借入金・社債」は、流動負債である短期と固定負債である長期をまとめて表示することで、資産の「現預金」と一目で対比できるようにしています。純資産の項目名には「自己資本比率」も表示されるようにしてみました。⇒セルA11の計算式 【 ="純資産(比率"&TEXT(C11/C18,"0.0%")&")" 】
  
• 表（セルA1～C18）をマウスでドラッグして選択し、[挿入]タブの[棒グラフの挿入]をクリックし、現れる選択肢のうち、[2-D縦棒]の[積み上げ縦棒]をクリックすると、グラフが描かれます。グラフの上で右クリックし、[データの選択]を選び、[行/列の切り替え]をクリックし、凡例を削除すると、次のようなグラフになります。
  
• グラフの縦（値）軸を右クリックし、[軸の書式設定]を選び、境界値は最大値を[2,500,000]とします。
• 棒グラフの一番上にある要素の[合計]の上で右クリックし、[データ系列の書式設定]を選び、[塗りつぶし]なし、[線]なし、とし、また、[系列のオプション]で[要素の間隔]を0%にします（資産の棒と負債の棒がくっつきます）。棒グラフの他の要素の部分は塗りつぶしと線を好みの色に変えます。
• 後は、軸の書式設定で目盛の設置や目盛線の消去、グラフタイトルの挿入、データラベルの挿入などを行い[プロットエリア]や[グラフエリア]を調整した結果が次のグラフです（合計のデータラベルを[内側軸寄り]で表示するのは、売上高の推移のところで紹介した通りです）。
  
• 一度、このような貸借対照表用のグラフを作っておくと、コピーしていろいろな会社のＢ／Ｓ分析が容易にできるようになります。

〇 グラフの事例--時間外管理

• 2019/4/1に改正施行（中小企業は2020/4/1から適用）された労働基準法により、 「３６協定」によって延長できる労働時間（法定時間外労働時間）に制限が設けられました（⇒労働基準法の概要と遵守のポイント参照）。建設事業においては、経過措置として、2024年3月31日までに締結される「３６協定」では改正された規定が適用されませんが、その猶予期間も残り短くなり、早急な対応が必要となってきています。
• 具体的には、特別条項付きの新書式の 「３６協定」を法律の上限時間（1ヶ月100時間未満、2～6ヶ月平均で80時間以内、１年720時間以内、45時間を超えることができるのは6ヶ月以内）で提出したと想定し、現状の法定時間外労働が（2024年度から提出することになる）新 「３６協定」をどの程度超過しているのかを把握したうえで、今のうちからコントロールしていくことが求められます。以下は、そのための管理表と、管理状況が分かるグラフの事例です。（なお、法定休日労働の有無まで管理するのは実際には難しいので、全て法定休日労働も含んだ時間で管理していることを前提にしています。）
• 社員30人（社員1～30）の事業所を題材にし、このうち社員1～10は管理職とします（管理職はB列に1を入力）。2020年度の社員別月別の法定時間外労働時間（管理職は1日8時間週40時間を超えた労働時間）をまとめたものが次の表とします。なお、C列～N列は非表示となっていますが、2019年度の各社員の法定時間外労働時間が入力されているものとします。
  
• この社員の月別の法定時間外労働時間の表から、管理すべき項目の値を自動で計算させ、次のように「管理表」としてまとめます。B列には管理値をパラメーターとして入力できるようにし、他の数値でもシュミレーションできるようにしました。（例えば、45の代わりに42と入力すれば、月間42h超の人数が集計されます。）
  
• 「月間45h超」を管理値とするのは、45時間を超えることができるのは6ヶ月以内という制限があるので、原則は「月間45h超」が出ないことが理想だからです。セルA3の計算式は【 ="月間"&B3&"h超" 】、セルO3の計算式は【 =COUNTIFS(O$15:O$45,">"&$B3) 】です。
• 「月間60h超」を管理値とするのは、１年間で720時間以内という制限は、月平均では60時間となるからです。また、月60時間超えの賃金割増率が50%に増えることも管理する大きな理由です（中小企業は2023年4月から適用）。セルA4やセルO4の計算式はそれぞれA3とO3をコピーすれば済みます。
• 「月間80h超」を管理値とするのは、直近2～6ヶ月平均で80時間以内という制限があるので、原則としては「月間80h超」が全く出ないことが管理のポイントとなるからです。ひと月でも「月間80h超」が出ると、直近2～6ヶ月平均で80時間以内という管理は実務上はコントロールが大変難しくなります。
• 「月間100h以上」を管理値とするのは、1ヶ月100時間未満という制限があるためです。これを含め、「2-6ｹ月平均80h超」「年間720h超」「45h超6ヶ月超」の各管理値は、（法の制限値一杯の「３６協定」を提出・受理されていたとしても）非管理職がひと月でも1名でも超過すれば、臨検の際に「是正勧告」を受け改善措置の実施を命じられることになります。管理職であっても、長時間労働者として「指導票」を受け、やはり改善措置の実施を求められることになります。
• 「月間100h以上」「2-6ｹ月平均80h超」「年間720h超」「45h超6ヶ月超」については、最終的に月別の「３６協定」違反者と違反者数を算出するため、次の表のようにAA列からCH列に中間計算ゾーンを設けています。
  
• 「月間100h以上」を求めるセルAA15の計算式は【 =IF(O15>=$B$6,1,0) 】です（この計算式を AA15:AL45 の範囲にコピーします）。「月間100h以上」となった場合に1が現れますので、誰が何月に制限に抵触したか分かります。その月に「月間100h以上」となった人数を集計するセルO6の計算式は【 =COUNTIFS(AA$15:AA$45,">0") 】です（この計算式を Z6 まで横方向にコピーします）。
• ★「2-6ｹ月平均80h超」を求めるセルAM15の計算式は、やや長いですが次の通.りです。空白データによりエラーを起こさないようIFERROR関数で逃げています。（この計算式を AM15:AX45 の範囲にコピーします。）
  ★【 =IFERROR(IF(AVERAGE(N15:O15)>$B$7,1,0)+IF(AVERAGE(M15:O15)>$B$7,1,0)+IF(AVERAGE(L15:O15)>$B$7,1,0)
  　+IF(AVERAGE(K15:O15)>$B$7,1,0)+IF(AVERAGE(J15:O15)>$B$7,1,0),"") 】
  「2-6ｹ月平均80h超」となった場合に1～5が現れますので、誰が何月に制限に抵触したか分かります。数値が多いほど平均80h超の状況が継続して悪いことを示します。その月に「2-6ｹ月平均80h超」となった人数を集計するセルO7の計算式は【 =COUNTIFS(AM$15:AM$45,">0") 】です（この計算式を Z7 まで横方向にコピーします）。
• 「年間720h超」を求めるセルAY15の計算式は【 =IF(SUM($O15:O15)>$B$8,1,0) 】です（この計算式を AY15:BJ45 の範囲にコピーします）。「年間720h超」となった場合に1が現れますので、誰が何月から制限に抵触したか分かります（大幅に代休でも取らない限り、一度現れたら期末まで継続して現れます）。その月に「年間720h超」となった人数を集計するセルO8の計算式は【=COUNTIFS(AY$15:AY$45,">0") 】です（この計算式を Z8 まで横方向にコピーします）。
• 「45h超6ヶ月超」を求めるセルBK15の計算式は【 =IF(COUNTIFS($O15:O15,">45")>$B$9,1,0) 】です（この計算式を BK15:BV45 の範囲にコピーします）。「45h超6ヶ月超」となった場合に1が現れますので、誰が何月から制限に抵触したか分かります（一度現れたら期末まで継続して現れます）。その月に「45h超6ヶ月超」となった人数を集計するセルO9の計算式は【 =COUNTIFS(BK$15:BK$45,">0") 】です（この計算式を Z9 まで横方向にコピーします）。
• ★「いずれかの超過あり」を求めるセルBW15の計算式は【 =IFERROR(AA15+AM15+AY15+BK15,"") 】です（この計算式を BW15:CH45 の範囲にコピーします）。空白データによりエラーを起こさないようIFERROR関数で逃げています。「月間100h以上」「2-6ｹ月平均80h超」「年間720h超」「45h超6ヶ月超」のいずれかに抵触した場合に1～8が現れますので、誰が何月にいずれかの制限に抵触したか分かります。その月に「36協定違反」となった人数を集計するセルO10の計算式は【 =COUNTIFS(BW$15:BW$45,">0",$B$15:$B$45,"=0") 】です（「36協定違反」なので非管理職だけを対象に集計しています。この計算式を Z10 まで横方向にコピーします）。
• 最後に一目で長時間労働者数が分かるグラフを作ります。セルA1～Z10をマウスでドラッグして選択し、[挿入]タブの[折れ線グラフの挿入]で[折れ線]をクリックし、[クイックレイアウト]の[レイアウト1]を選択します。グラフの上で右クリックし[データの選択]を選び、[凡例項目（系列）]から「対象人数」「2-6ｹ月平均80h超」「年間720h超」「45h超6ヶ月超」のチェックを外し、[横軸ラベル]から「パラメーター」のチェックを外します。再びグラフの上で右クリックし[グラフの種類の変更]を選び[組み合わせ]を選択し、「月間45h超」「月間60h超」「月間80h超」「月間100h以上」は折れ線グラフ、「36協定違反」は棒グラフとします。36協定違反者数が問題なので、棒グラフにはデータラベルを表示します。後は、グラフの色などを調整した結果が次のグラフです。
  
• なお、管理職の長時間労働者も含め、「いずれかの超過あり」に該当する社員別月別の法定時間外労働時間の表のセルがピンクに塗りつぶされるように条件付き書式を設定しました。この場合の、セルO15の条件付き書式のルール（計算式）は【 =AND(BW15>0,BW15<999) 】で、その適用先を【 =$O$15:$Z$45 】にします。こうすることで、「月間100h以上」「2-6ｹ月平均80h超」「年間720h超」「45h超6ヶ月超」の少なくともいずれかに抵触しているのがどこか色でも識別できます。

〇 グラフの事例--モデル賃金

• モデル賃金とは、定期採用した新入社員が、入社年から定年まで、毎年、昇給・進級成績が標準だった場合、どのように賃金が上昇していくかを示すものです。企業は、業績、採用方針、労使交渉なども踏まえ、毎年度、定昇率、ベースアップ、諸手当などを見直すので、それに合わせてモデル賃金も毎年度修正されます。
• モデル賃金があれば、中途採用する場合も、中途採用者の年齢や経験年数に応じた賃金設定の際に、説得力のある資料として活用することができます。
• 次の表（セルA1～AM29、U列以降は画面では省略）は、大卒を新卒採用した場合のモデル賃金の作成事例です。横軸は4/1時点の年齢としますが、入社1年次を22歳とし、定年前年の59歳まで計算します。縦軸では、所定内（基準内）給与並びに時間外手当（非管理職のみ、平均時間外をここでは月30時間とする）を含んだ月例給与を計算します。昇給・進級成績が入社時から定年前年までずっと標準成績だった場合の「標準モデル」（3行～11行）の他に、成績がずっと最上位であった場合の「最速モデル」（12行～20行）と、成績がずっと最下位であった場合の「最遅モデル」（21行～29行）も作成します。
  
• このモデルでは、所定内給与は、本給、等級手当及び役付手当で構成されます。
  　本給は、毎年度定昇があり、定昇率は昇給成績に応じて、標準が2.2%（セルC2）、最上位が+0.5%、最下位が-0.5%としています（56歳以上は定昇率を半減する設定です）。
  　等級は、1級からスタートして最高位の7級まであり、このうち5級から7級を管理職としています。1級から6級の各等級の社員が次の等級へ進級できるまでの必要経過年数は標準で5年ですが、進級成績で最上位が続いていれば4年に短縮し、逆に最下位が続いていれば6年に延ばすものとします。また、6級以上の幹部職に進級できるのは標準成績以上の場合とします（標準成績未満では5級止まりとする設定です）。等級手当は、等級が上がるほど高くなりますが、本給の低い若年層（1級から3級まで）では経過年数に応じ変動（増額）させることで、毎年、一定幅以上の昇給額を確保できるようにしています（若年層でなかなか給与が上がらないことが中途退職の一つの理由となるためです）。
  　役付手当は、5級から7級までの管理職に、役職（課長、副部長、担当部長、部長）に応じた100,000円～160,000円の手当を支給しますが、4級から5級に進級した場合、月30hの時間外手当がなくなっても、課長の管理職手当の方が多くなるよう設定しています。4級は管理職ではないのですが、主任に処遇して月額10,000円の役付手当を支給しています。なお、セルB10の時間外手当の計算式は【 =ROUND(B8/160*1.25*30,0) 】です。
• 以上の計算結果をグラフ化するため、セルA30～AM37（U列以降は画面では省略）にグラフデータとしてまとめています。なお、37行目は、モデル賃金を社外のモデルと比較するため、「政府統計の令和2年賃金事情等総合調査」からモデル所定内賃金（大学卒、総合職、建設業、5歳ごとのデータ）を転記したものです。この結果、グラフは次の通りとなります。
  
• 赤の実線が標準モデル、青の実線が最速モデル、緑の実線が最遅モデルとなります。各色の点線は、非管理職で月30ｈの時間外手当を含んだ金額です。毎年定昇があるので年齢とともに右肩上がりですが、進級する年は等級手当や役付手当が増えるのでより大きく変動しています。
• 年功序列式の賃金体系に見えるかもしれませんが、優秀な社員や経験豊富な社員の中途退職を防ぎ、なおかつやる気も維持してもらうには、基本的には可能な限り給与水準を高めることに尽きますが、毎年一定程度の昇給を保証して生活の安定を図ること、業務実績に基づく成績によって昇給・進級する制度を設計しその仕組みを社員にオープンにすること、また評価基準や評価枠を守るなど公平な評価に努めることなどが肝要と考えます。このモデルはそういった考えを基本に置いて作ったものとご理解ください。なお、このモデルでは、最速モデルと最遅モデルで月例給与は、30代で平均16%、40代で平均24%、50代で平均38%の開きが出る計算で、実際には（長期的な視点でとらえた）成果主義的要素もかなり含んでいると言えます。（短期的な成果に対しては、別途、賞与で配慮する仕組みを作ればよいと思います。）
• なお、グレーの点線は、政府統計2020年度モデル所定内賃金（大学卒、総合職、建設業）ですが、統計対象企業は資本金5億円以上や労働者1,000人以上と大企業クラスから選ばれており、やや高めに感じます（事例の最速モデルに近い）。
• 次に、モデル賃金をグラフ化したものに、実在社員の賃金の変動を合わせて表示することで、モデル賃金と実在賃金の乖離状況や、個人別の適切性を視覚的に把握できます。個人別の昇給額は、昇給成績によって決まってしまうためどうしてもミクロ視点で考えがちですが、給与原資の適切な配分は人事政策では何よりも重要であるため、全社員を大局的に比較するマクロ視点でも検証・確認しておきたいものです。
• 次の表（セルA1～AS35、T列以降は画面では省略）は、先ほどのグラフに、社員1～社員30の所定内給与を重ね合わせるためのものです。2行～4行は先ほどの各モデルのデータを転記したものです。6行～35行は社員の給与実績データです。B列は2021/4/1時点の年齢、C列～G列は過去5年間の所定内給与の個人別実績額です。
  
• セルH6～AS35（T列以降は画面では省略）に各社員の各年齢における所定内給与額が表示されるようにしていますが、その計算式はセルH6で次の通りで、これを他のセルにコピーします。データがない年度のセルの値をNA()関数で#NA（空白セル）としているのは、グラフ作成時に 0（ゼロ）と認識させない（ゼロと認識されグラフで結ばれるのを防ぐ）ためです。
  【 =IF($B6-H$1>4,NA(),IF(AND($C6<>"",$B6-H$1=4),$C6,IF(AND($D6<>"",$B6-H$1=3)
  　,$D6,IF(AND($E6<>"",$B6-H$1=2),$E6,IF(AND($F6<>"",$B6-H$1=1),$F6
  　,IF(AND($G6<>"",$B6-H$1=0),$G6,NA())))))) 】
• ★また5行には2021年度の実在平均(所定内)を計算しています。2021年度の値だけを平均する（2017～2020年度の数値は拾わないようにする）必要があるので、計算式にはAVERAGEIFS関数を使用し、更にAVERAGEIFS関数がエラーを起こした時のためにIFERROR関数で逃げています。計算式は、セルH5で次の通りで、これをセルH6～AS6にコピーします。
  ★【 =IFERROR(AVERAGEIFS(X6:X35,X6:X35,">0",Y6:Y35,#N/A),NA()) 】
• 以上の計算結果をグラフ化する場合のグラフデータの範囲は、【 A2～A35,H2～AS35 】です。この結果、グラフは次の通りとなります。
  
• 赤の実線の標準モデル、青の実線の最速モデル、緑の実線の最遅モデルと重ねて、各社員の過去5年間の所定内給与の実績が折れ線で描かれています。実年齢はモデル年齢より1～2年食っていることも少なくない（新卒採用時に既に23歳や24歳だった場合など）ので、実績を表す折れ線もモデルより1～2年右にずれる人も少なからず出ています。また、ピンク色の破線で、実年齢における実在平均が描かれています（傾向が分かりやすいようスムージングして表示していますが、概ね赤の実線の標準モデルに沿った線となっていることが確認できます）。
• このグラフで、特に標準モデルから極端に高くなっている社員や、極端に低くなっている社員に注視し、なぜそうなっているのかを検証するなどの対応を取ります（グラフで見れるのは、あくまで実年齢に対する金額の比較ですので、能力や経験を加味すれば理由（正しいメリハリ）がある場合がほとんどですが、全体的なバランスから考えると不適切といえるケースを発見することもあります）。

〇 グラフの事例--安否確認

• 地震などの際に社員の安否情報を収集する民間の「安否確認サービス」に加入している企業も多いと思われます。「安否確認サービス」を導入した場合、いざというときに短時間で社員全員が安否情報を登録するよう、普段からの訓練が欠かせません。
• 安否情報が訓練上の発災時刻からの時間経過とともにどのように登録されたかを確認することは、訓練を評価するために欠かせません。そのため、折れ線グラフで時間経過による登録数の累計割合(%)の変化を表したいと思います。
• 次の表（セルA1～L362、21行以降は画面では省略）は、発災時刻から6時間（360分）経過するまで、分単位で累計登録率を計算するためのものです。
  
• A列は各社員が安否状況を登録した回答時間です。「安否確認サービス」では、登録結果をCSV出力できるものが多いと思いますが、そのCSV出力結果のうち、回答時間だけをセルA2から下方向にコピーします。もし訓練打切り時に未回答があった場合は、回答時間に「－」（マイナス記号）など何らかのデータが入っているものとします。
• セルB2は、訓練のスタート時刻ですので手入力します。セルB4は、訓練の総対象者で、その計算式は【 =COUNTA(A:A)-1 】です。
• C列は、スタート時刻からの経過時間（分）で、セルC2に0を入力し、セルC3の計算式は【 =C2+1 】で、この計算式をセルC4～C362までコピーします。
• D列は、スタート時刻からの1分ごとの経過時刻であり、セルD2の計算式は【 =B$2+C2/(24*60) 】で、この計算式をセルD3～D362までコピーします。
• E列は、経過時刻の回答数で、セルE2の計算式は【 =COUNTIFS(A:A,D2) 】で、この計算式をセルE3～E362までコピーします。
• F列は、経過時刻の回答累計で、セルF2の計算式だけは【 =E2 】ですが、セルF3の計算式は【 =E3+F2 】で、この計算式をセルF4～F362までコピーします。
• G列は、経過時刻の回答割合(%)で、セルG1の計算式は【 =TEXT(B2,"yyyy/mm/dd") 】、セルG2の計算式は【 =F2/$B$4 】で、この計算式をセルG3～G362までコピーします。
• H列～L列は。この事例では毎月15日に安否確認訓練をやっていて、過去5回分の経過時刻の回答割合(%)を（計算式を除いた）値のみコピーしたものです。
• 以上の計算結果をグラフ化する場合のグラフデータの範囲は、【 C1:C362,G1:L362 】です。この結果、グラフは次の通りとなります。
  
• 訓練を予告したかしなかったか、スタート時刻が勤務中か夜間・休日か、（開始メールは必ず送るとして）訓練途中で督促メールを送ったか送らなかったかなどで、経過時間による累計登録率は変わってきます。今回のグラフとともに、過去（この例では過去5回分）の累計登録率の推移も合わせて表すことで、安否確認の課題などがはっきりしてきます。スタート時刻直後に登録する人が多くいる反面、必ず制限時間（例えばスタート時刻から6時間）以内に登録しない人がいます。どんな訓練でも、聞いていない人や聞いていても参加しようとしない人が数パーセントはいるので、ロングテールのあるグラフとなります。

〇 グラフの事例--契約印紙代

• 散布図を使った”階段状グラフ”の事例として、請負契約書の契約印紙代と契約金額の関係をグラフ化してみます。
• 請負に関する契約を紙ベースで締結すると、印紙税法により印紙税が課税され、収入印紙を貼付することで納税します。その印紙税額の定めは国税庁の「印紙税額一覧表」（国税庁（印紙税））にあるとおりです。
• 一覧表などから必要な部分を抜き出すと、次の表のような定めとなっています。建設工事請負に係る契約書は、記載された契約金額が100万円超えの場合、金額に応じて50%～20%の減額措置が取られています（従って同じ請負契約でも工事より設計の方が税率が高いことになります）。なお、”記載された契約金額”は、契約書面から消費税額が明らかな場合は税抜金額となり、また変更契約において契約書面から変更金額が明らかな場合は当該変更金額となります。
  
• この表の内容を、X軸を契約金額、Y軸を印紙税額として、散布図でグラフ化するため、次のように加工します。
  
• C列は契約金額で、D列は通常の印紙税額、Ｅ列は建設工事の印紙税額です。通常の印紙税額で見ると、契約金額が0円から9,999円までは印紙税額は0円、契約金額が10,000円から1,000,000円までは印紙税額は200円、契約金額が1,000,001円から2,000,000円までは印紙税額は400円、……ということを表しています。
• セルC3の-99,999,999,999円や、セルC29の99,999,999,999円は、それぞれグラフを描くのに十分小さい金額と十分大きい金額を表しているだけで、この数字自体に意味はありません。
• 契約金額は負の場合（減額契約）もあるので、X軸はマイナスの数値も表示する必要がありますが、セルF5:F6のデータは契約金額が0円のラインを縦に引くためのものです。
• 以上の計算結果をグラフ化する場合、グラフの種類は散布図（平滑線）
  を使います。グラフデータの範囲は、【 =C2:F29 】です。X軸の書式設定で、最大値を60億円、最小値を-10億円、目盛線の単位を5億円、補助目盛線の単位を1億円とします。Y軸の書式設定では、最大値を60万円、最小値を0円、目盛線の単位を10万円、補助目盛線の単位を2万円、またラベルの位置は[下側/左側]としています。データ系列（[印紙税額][（建設工事）][±0]の3つ）の書式設定で、[印紙税額]の系列をここでは緑の太めの実線とし、また[スムージング]のチェックを外します。[（建設工事）]の系列をここでは青の太めの実線とし、同じく[スムージング]のチェックを外します。[±0]の系列については、黒の波線としました。
• その他、若干の装飾を加え、グラフは次の通りとなります。
  
• また、同じグラフをコピーしたうえで目盛のスケールを変え、X軸の最大値を12百万円、最小値を-2百万円、目盛線の単位を百万円、補助目盛線の単位を20万円とし、Y軸の書式設定では、最大値を6千円、最小値を0円、目盛線の単位を千円、補助目盛線の単位を2百円として描画したものは次の通りです。契約金額0円付近にわずかに縦の青線が走りますが、これは契約金額が0円～9,999円だけは印紙税額は0円で、その前後は200円となっているためです（税制として理解しがたい定めです）。
  
• 散布図を使ったこの方法は、様々な規定を視覚的に説明するのに大変便利です。「労働保険徴収法の概要と遵守のポイント」における「メリット収支率とメリット増減率の関係」のグラフももこの方法で描画しています。