Excelで関数だけを使って進捗管理表

Excelで関数だけを使って進捗管理表を作る

Excelを使った作業等の報告、例えば定例ミーティングで現時点の作業状況を印刷し、配布するなどは良くあるパターンだと思います。
(ちなみに関数を組み込むだけでなく、マクロやVBAを組み込むことでかなり使い勝手の良い、立派な報告書が作れます)

関数だけを使って進捗管理表を作成してみる

ここではExcelの持つ関数、比較的メジャーどころな関数だけを使って進捗管理表を作ってみたいと思います。
今回はの管理対象は「顧客の進捗状況」で試してみますが、実際には案件の進捗管理や従業員の作業管理など時系列で管理する必要がある場合でも工夫すれば同様に対応できると思います。

■顧客ごとの進捗管理表
関数だけを使ったExcelの進捗管理表の画像

この管理表の「キモ」はB列とC列に常に最新の情報が自動表示されていることです。 B列とC列は常に最新が表示されているの画像
B列には(この表では)顧客ごとに対応した回数の総計を表示し、C列では最後に対応したときの内容が表示されるようにしています。

一方、上記のような形ではないパターンが下記になります。
関数などを利用しないとこのような形になることが多いかと思います。

■一般(関数を使わない)的な顧客ごとの進捗管理表
関数を使わない進捗管理表の画像

一般的な進捗表の場合は、対応回数が増えるに連れ、右側の列をどんどん占有していきます。Excel上だけで使うならば、これでも対応できます。
ただしこの表では顧客3社分しかないですが、顧客が増えるに連れ、対応回数に差が出てくるとやはり見づらいもになります。
特に印刷を使うとその見づらさは大きくなることが考えられます。

関数を使うとこの部分がある程度回避できる

そこで始めに示した形の表にすることで見づらさや管理のしづらさ(間違い)をある程度は回避することができます。
以降は先程示した顧客進捗管理表の作成の説明です。

【日付ベースの一元的管理は時として管理しづらくなる】
下記の一般的な管理表の場合は、日付を軸にして作業内容を書き込んでいます。
関数を使わない進捗管理表は日付ベースで管理の画像
この形では各顧客の対応が同じ月日に発生していればいいのですが、現実的には上記の流れが多いので、無駄な空白セルができてしまいます。

そこで関数を入れた顧客進捗管理表では以下の形にして「顧客ごとに独立して日付を持たせる」ようにしました。
関数あり進捗管理表は日付を顧客ごとに持たせたの画像
これで顧客ごとにセルを左詰めして書き込むができるので書き手からは入力しやすくなります。
また同時にこの日付を配置し、簡単な関数を使うことで今回のキモに生かすことができます。

【使うExcel関数は3個！？】
ここで紹介している関数付きの顧客進捗管理表では関数は3個です。
実際には関数以外の機能(例えば条件付き書式)や色々と関数を追加することで更に見やすく、管理しやすく、分かりやすい表が作れます。
関数あり進捗管理表は3個の関数だけでなりたっているの画像

【各関数の役割】
セルB2(結合B3)：COUNTA関数
この関数はA社の対応内容が書かれているセルが何個あるか数えています。
ただし範囲はZ列までの中でとなっていますのでこれを超えて書き込まれても増えることはないので、実際にはどんな表を作るかによって個別に設定した方がよいでしょう。

セルC2：MAX関数
この関数はA社の日付が書かれているセルで最も大きい(最新の)日付を探して表示しています。
ただしCOUNTA関数同様に範囲はZ列までの中でとなっていますのでこれを超えて書き込まれても増えることはないので、実際にはどんな表を作るかによってCOUNTA関数に合わせて設定した方がよいでしょう。

セルC3：OFFSET関数
この関数は使い方がいくつかありますが、今回は「セルの位置を指定すると、その位置(アドレス)にあるセルの内容を表示する」という仕組みを使っています。
そしてこの表では「最新の対応内容」のセルを表示して(させて)います。
ポイントとしては、最新の対応内容が入力されているセルの位置は不確定です。対応回数が増えれば、その分セルの位置的には右に移動するからです。簡単に言うとセルの位置が固定できず決め打ちできません。
そこで今回はCOUNTA関数の入っているセルB2の(関数の結果)値を使っています。イメージとしては
「もしCOUNTAのセルの対応回数がNなら、セルD3から右にN個分のセルの内容を表示しなさい」と言った内容になります。と、することで、いわゆる「汎用的な作り」になっています。

一顧客分の関数が出来上がれば、後は行全体を選択してコピー、貼り付けで関数の内容も自動調整されてコピーできます。行ごと選択してコピーの画像
なお、利用PCにもよりますが、関数を入れすぎると表示が重く(遅く)なったりすることがあるのでバランスが取れた形にすると更に望ましくなります。

実務レベルでは表を作るときの前提条件を考えておく

上記の表でもそうですが、特に関数(マクロ、VBA)を含んだ表を作るときには前提条件を考えておく必要があります。この表の場合は
・より右のセルに入る日付は常に最新である(そうでないとB列、C列で矛盾が発生する可能性あり)
・日付が入っている場合は必ず内容も入力されている
・対応内容はZ列までとする
などです。
このように考えておくことで、表としての「仕様」が固まり、表作成の前提条件となります。また利用者に前提条件を強制的に守らせるためにシートを保護する、あるいは入力規則を併用するなどといった事前対策も考えることができます。