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

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

日常の業務では、現在の案件状況、作業状況、お客様対応状況、従業員管理など・・例えば定例ミーティングで現時点の作業状況を報告するというのはよくあるお話だと思います。

このようなとき、案件や作業の進捗管理システムなるものがあるとよいのですが、Excelベースで作るとなると、時として入力、そして、その管理自体が面倒になるケースもあると思います。

そこで今回はExcelの関数だけを使って進捗管理表を作成してみたいと思います。
(これまでの内容を刷新しております)

Excelの関数だけで進捗管理表を作成


以下で説明しているファイルはダウンロードできますので、ここで説明している内容をファイルを見ながら確認してもらえればと思います。

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

ここではExcelの持つ関数を使って進捗管理表を作成してみます。

作りは非常にシンプルです。
下記画像のように、A~C列は各案件の名前とその直近の内容です。
D列以降は、各案件の状況を入力します。

D列以降に日々の活動、作業内容を入力すると、C列に「直近の内容」が表示される仕組み

仕掛けとしては、「現時点の状況がどうなっているのか」を一覧として見るために、ここの「C列」に関数を埋め込みます。

今回はの管理対象は「案件単位」で試していますが、実際には顧客ごと、従業員の作業管理など工夫すれば同様に対応できると思います。

使用している関数は「INDEX」、「COUNTA」の2つだけ

今回の進捗管理表で使用している関数は、2つだけです。とてもシンプルですが、この2つの関数をネストさせていることがポイントになると思います。いわゆる親子関係を作っている状態になっています。

▼画像挿入:23


セルに入力されている関数を関数ボックスで見るとわかりますが、INDEX関数が「親」にあたり、COUNTA関数が「子」になります。

ネストの関係にあるとき、関数で処理される順番があります。最初に処理されるのは、内側にある関数となります。ここでは、「COUNTA」関数が先になります。そして、そのあと、INDEX関数が処理されます。

処理の順番はなぜ内側からか、それは図の関数ボックスを見ればわかりますが、最初にCOUNTA関数が実行され、その結果がないとINDEX関数が実行できないからですね。
したがいまして、子のCOUNTA関数の設定等を間違え、エラー値が返ってくると、それを受けとる親のINDEX関数の結果も必然的にエラーとなります。
(1つのセルに関数や数式が複数あるときの処理順は、Excel側で自動的に判断されます)

INDEX関数について

INDEX関数は、便利な関数なのですが、あまり見かけない部類に入るかと思います。

INDEX関数とは、ある特定(指定した)のセルの値を表示します。
今回の進捗管理表では、C列の「直近状況」という列に、D列以降に入力している最も右側のセルの値を表示しています。

しかし、最も右側にあるセルの列位置は、各案件(各行)によって位置が変わります

終了列の位置は行(案件)によって異なる

後述していますが、列の位置が変わってしまうと決め打ちができないのですね。
そこで、行ごとに列位置を確定するためにCOUNTA関数を使っています。

セルに「=INDEX」と入力し、関数ボックス(fx)を表示すると、引数の選択をする画面が表示されますが、今回は上のほうを選択します。

「配列、行番号、列番号」の方を使用する

関数ボックスで表示された項目の「配列」には、表示する表の範囲を選択します。
今回の進捗管理表では、INDEX関数は「1行分の範囲でかつ、複数の列」を対象とします。
つまりは、横長の表になります。

列幅は最大値を取ると最終列の意識をする必要がない

ここでは、配列の範囲に「D2:XFD2」と入力しています。これは、進捗管理表の大きさはM列までですが、仮に入力がM列を超えN、O列へと入力していくと範囲から漏れてしまうために最大列を指定しました。

「行番号」は「1」です。これは、配列で指定した「セルの範囲内」で何行目かを指定します。「セルA1から見た位置ではない」ということに注意ですね。
これは、次の「列番号」もそうですが、指定した「セルの範囲内」からの位置となります。

たとえば、次の画像のように「行番号:1」、「列番号:2」とするとセルにはどのような値が表示されると思いますか?

行番号、列番号に(仮の)値を入れてみると・・

これは、選択した範囲が「D2:XFD2」です。
その表の左上から見て、「1行目で2列目の値が表示される」ということになりますのでこうなります。

「表から見て」1行目かつ2列目の値が表示される

と、2列目のセルの値が表示されるわけですが、進捗管理表では、ここは「直近の内容を表示するというルール」にしています(と私が決めています)。
「2列目」というのは、入力しているセルによって3列目、4列目にもなり得るわけです。

したがいまして、(上の方で紹介した画像にもありましたが)ここで入力した「列番号:2」という決め打ちは使えないということなります。

ということで、次のCOUNTA関数の出番となります。

COUNTA関数について

続きましてCOUNTA関数についてです。この関数は、COUNT系の関数で利用されている方も比較的いらっしゃるのではないかと思います。

COUNTA関数は、指定したセル範囲の中でセルが空白でない個数を表示する関数ですね。
そうですね、ここでピンと来られた方は以下確認は不要かもしれません。今回はこの個数が非常に大事になるわけです。

先ほどのINDEX関数では、「2」と列番号を決め打ちしました。しかし、実際には列の位置は案件ごとにも変わりますし、同じ案件でも活動内容が増えれば、表示したい列の位置は右へ右へとずれていきます。

「〇〇(たとえば列)の位置が確定できない・・・」
こういう仕様のことを「動的(可変)」ということもあります。
たとえば、今回の場合は「列の位置が動的(可変)で決め打ちできない」などと言います。

したがいまして、ここでCOUNTA関数を使います。
もっとも、COUNTA関数は「個数」を返すのであって列位置を返すわけではありません。
つまり、ここでの使い方は、COUNTA関数の応用的な使い方とも言えると思います。

言い換えると、このCOUNTA関数の返す「個数」が列位置となるような用途として使えるようにINDEX関数を組み合わせています。

COUNTA関数で「列位置(入力セルの個数)」を求め、その数値をINDEX関数に読み込ませている(渡している)


INDEX関数の第3引数「列位置」の箇所には、「2」ではなく、「COUNTA」を入力しています。
このようにすることで、列位置にはそのときの表の状況を見たセル個数を返すという方法にしています。
ここまでできたら、あとは数式を作成したセルを下にコピーすれば出来上がりです。

「うーん、意味がよくわからん」という方は、ファイルを直接ご覧になりながら確認してもらうとわかるかもしれません。

今回作成した進捗管理表のメリットとデメリット

今回は進捗管理表を関数だけで作るという説明をしました。

この進捗管理表ですが、関数だけで作れるということがそもそものメリットに思っていますが、その他メリットやデメリットについても考えたいと思います。

メリット

最大のメリットは、関数だけで作れるということですね。
また、D列以降に作業内容を入力していくことで、各案件の最新の情報がC列にまとまってみることができます。

さらに案件が増えたときは、今の表の末尾に追加し、数式をコピーすればOKというのも簡易さではメリットだと思います。

デメリット

一方、デメリットというと、D列以降に入力する列が順番に入ってなければなりません。
たとえば、2月10日の作業(セル)と2月11日の作業(セル)の列が逆転して入力されていても、それに気づくすべがないのですね(表の作りを工夫し、Excel関数以外にもその他機能を活用すればある程度は回避できると思いますが)。

また、INDEX関数で配列なる箇所に入力する表の列範囲が最大値というのがちょっと美しくないというか、PCによっては表の入力が増えていくほど負荷が大きくなるのではないかと思います(未検証)。

もっとも、使い方や用途によってはメリットとデメリットは変わっていくと思います。

適切な形でみなさんのお役に立てれば何よりです。

タイトルとURLをコピーしました