以前こちらの記事で、
「最低限必要なエクセル(スプレッドシート)知識」
について触れ、
シンプルなチェックシートが作られるまでの過程を説明させていただきました。

今回はそのシンプルなチェックシートに「集計」を追加して、進捗を把握しやすくするための方法を説明していきます。
・集計の概要
- 進捗表(指定の内容をカウント)を作成する
- 進捗表(進捗率)を作成する
- 1つの集計用シートで「複数シートの進捗」を管理する
集計の概要

こちらのようなチェックシートを例にお話をさせていただきます。
このシートは単純にOKやNGを入れることのできるシートです。
ですが、
「現在、OKが何個でNGが何個で・・・」という集計部分が全くないため、
目視で数える必要があるシートになっています。
上記例のチェックシートは9項目しかないので目視でも進捗はわかりますが、
大量に項目数があるようなシートでは把握が難しくなるため、
集計があると簡単に把握できて便利です。
では以下で基本的な集計機能を作っていきます。
進捗表(指定の内容をカウント)を作成する
「OKが何個あってNGが何個あって・・・」と数えるのは手間なので、
関数で計算をできるようにします。
まず画像のように「項目の数」「OKの数」「NGの数」をカウントするための欄を作成します。

その後、カウントをするために関数を入力します。
使用する関数は以下になります。
項目数のカウントに使う関数
=count(セルの範囲)

↓
count関数というもので、
指定した範囲で「数字が入力されたセルが何個あるのか」を数える関数です。
OKの数のカウントに使う関数
=countif(セルの範囲,”OK”)

NGの数のカウントに使う関数
=countif(セルの範囲,”NG”)

※注意点:文字列を””で囲う必要があるので注意(今回の場合は、 “OK” や “NG”)
↓
countif関数というもので、
指定した範囲で「指定した条件が入力されたセルが何個あるのか」を数える関数です。
(今回の場合はOKのセルが何個あるのか / NGのセルが何個あるのか、をそれぞれ数えるように設定しています)
これらを入力することで以下のように数をカウントできるようになります。

進捗表(進捗率)を作成する
現状の状態でカウントはできるような状態にはなりましたが、
「全体で何%くらいの進捗感なのか」がわからない状態なので、機能を追加していきます。
まず、画像のように「進捗率」をカウントするための欄を作成します。
(今回は全体の項目数に対してOKの数の割合がどの程度か、という進捗を出します)

次に進捗率を出すセルを選択した状態で画像の「表示形式をパーセントに設定」をクリックします。
これを設定することで、その欄に入力した「数字」が「パーセント」で表示されるようになります。
その後、カウントをするために関数を入力します。
使用する関数は以下になります。
=OKをカウントしているセル / 項目数をカウントしているセル
(つまり今回の例の場合では「=D2/D1」と入力しています)

↓
これは関数というよりは単純な計算ですが、
「/」というのは割り算を表しています。
なので、「D2割るD1=0.333….」となり、
その数値をパーセント表示した結果「33.33%」と表示されるようになります。

1つの集計用シートで「複数シートの進捗」を管理する
チェックシートや様々な表など、
1つのファイル内に複数のシートを作成することが頻繁にあると思います。
そんな時、各シートの進捗を確認するにあたり、
1つ1つシートを開いて確認していたのでは手間がかかります。
上記のようなケースに対応するため、
集計用のシートを作り、そのシートで全体の進捗を把握できるようにします。
今回は以下画像のように、
チェックシートが3つのシート(シートA、シートB、シートC)に渡って作成されているケースを想定します。
(3つのシートは全て同じような体裁のチェックシートにしています)

現状の場合、
シートAの進捗を確認するためにシートAを開いて、
シートBの進捗を確認するためにシートBを開いて・・・
のように進捗確認に手間がある状態なので、
進捗を確認するためのシートを作ります。
まず以下のように集計専用のシートを増やし、表を作ります。

その後、
以下のように関数を使って「他のシートからデータを参照」できるようにします。
=count(indirect(参照する文字列(シート名)&”! 指定する範囲”))
今回の例のケースで「シートA」の「項目数」をカウントしたい場合は、
=count(indirect($B7&”!B7:B”))
となります。

この関数は、
count関数とindirect関数を組み合わせたものになります。
少し複雑に見えるかもしれませんが、構成はシンプルです。
count関数はこちらで説明した通りで、
指定した範囲で「数字が入力されたセルが何個あるのか」を数える関数です。
ですが今回の場合、
「別シート(シートA)の、指定した範囲の数字が入力されたセルが何個あるのか」を数えたいので、
「別シート(シートA)である」という点を考慮しなければいけません。
それに伴いIndirectという関数を組み合わせました。
(indirectを使わない方法もあるのですが、コピペのし易さや後からの調整のし易さも踏まえて今回はこちらの方法を取り入れています)
indirect関数とは、
指定した文字列のセルを参照してその値を返すというものです。
そうは言ってもなかなかイメージがわきづらいと思いますので、
今回使った
=count(indirect($B7&”!B7:B”))
を分解して説明します。
まずindirect部分の
indirect($B7&”!B7:B”)
についてですが、
$B7というのは
「この集計シート」のB7セルを参照している=「シートA」という文字列を参照しているということです。

つまり、
$B7&”!B7:B” というのは、
「シートA」という名前のシートのセルB7:Bの範囲を参照する
という関数になります。
ちなみにシートBを参照したい場合は、
集計シートのB8セルにある文字列を参照する必要があるので、
indirect($B8&”!B7:B”)
となります。
この関数は、
その指定した範囲のセルをそのまま参照して引っ張ってくる形となるため、単純に
indirect($B7&”!B7:B”)
とした場合は以下のようになります。

なぜこうなるのかですが、
以下はシートAのB列(範囲指定したB7:Bの箇所)の内容になります。

「シートA」の指定した範囲には1-9のナンバーが振られていて、
「集計シート」ではその値をそのまま参照して引っ張ってきた結果となっている訳です。
ただ今回の目的は、
「そのまま引っ張ってくる」ではなく、
「該当するセルの数を数えたい」です。
そこで、
Indirect関数を囲うように、
count(indirect($B7&”!B7:B”))
とすることで、
「Indirectを使って参照したセルの数をcount関数で数える」
という構成とすることができ、以下のように集計が可能となります。

基本的な考え方は上記「count関数 + indirect関数」と同様です。
今回の例の場合、
項目数については「count関数 + indirect関数」でカウント可能ですが、
OKとNGの数についてはcountifを使う必要があります。
countifを使う理由についてはこちらと同じで、
「指定した範囲のセルの数を数える」ではなく
「指定した範囲の指定した条件のセルの数を数える」という必要があるためです。
関数の構成としては以下となります。
=countif(indirect(参照する文字列(シート名)&”! 指定する範囲”),”カウントイフで数えたい対象文字列”)
これを今回のチェックシートの例に当てはめて「シートAのOKの数を数えたい」場合は以下になります。
=countif(indirect($B7&”!h7:h”),”OK”)

分解すると、
(indirect($B7&”!h7:h”)
の部分については先ほどのcountの時と同じ使い方です。
違う点は、
countifという関数なので、
「”OK” という文字列だけを数えたい」という指定が必要になります。
countifの基本構成は
=countif(セルの範囲,”OK”) ですが、
要は今回のindirectを交えたもので噛み砕くと
=countif(indirectを使ったセルの範囲,”OK”)
となったということになります。
上記を入力することで以下のように別のシート(シートA)のOKの数を数えることができるようになります。

NGのカウントについても同様に、
=countif(indirect($B7&”!h7:h”),”NG”)
のように最後の文字列の部分を調整することで集計可能です
上記までができたら、
シートAの関数をそのままシートB,シートCの欄にペーストするのみで集計ができるようになります。
最後に進捗率の部分ですが、
この点はこちら同様に「OKの数 / 項目数」とするのみです。
(シートAの進捗率であれば「D7/C7」)

上記までができたら、
シートA、シートB、シートCをそれぞれ開く必要はなく、
集計シート1つで全てのシートの進捗が1ビューで把握できるようになったと思います。
今回まででスプレッドシートの基本的な部分を紹介させていただきました。
以降も、より良い使い方や一歩前に進むための関数などをご紹介させてただきます。