次のような表があります。
左にチェックしなければならない番号の一覧があって、右にチェックが済んだ番号を入力していきます。
左のチェック表を上から見ていって、その時点で、保留ではなくチェック済みでもない一番上のものに対して、今はまだチェックをしないで保留にするのか、もしくは、右の表に番号を書き込んでチェック済みにするかの選択をしていきます。
保留の場合は、左の表の保留欄に1と入力、チェック済みの場合は、右の表に番号を入れてチェック済みとします。
その入力がされると、左の表の「済み」欄にその番号が右の表にもある個数を計算します。「Chk」欄には、保留が1の保留状態、または済みが1の状態ならば1になるようにします。
そうすると、次にチェックしなければいけないのは、「Chk」項目を上から見ていって一番初めに0になっている行になります。
こういう仕組みにしておくことで、一目でチェック済みかどうかがわかります。
しかし、データが何百行もあったら、未チェックの項目を探してジャンプするのは大変です。
そこで、一番初めに0が出てくるのは何行目かを探し、その行に対する番号にジャンプが一気にできれば、検索する手間もありませんし、非常に正確なチェックができます。
そのような仕組みを考えていきます。
テーブル機能
テーブル機能は一覧表を扱う上でとても便利なExcelの機能です。近年は一覧表が設定してあれば、必ず設定したほうがよいといわれるほどの機能です。
テーブルにはたくさんのメリットがありますが、その中でも、今回は計算式に対して便利になる使い方を使っていきます。
テーブル機能は、そのテーブルに属している1つのセルをクリックして、Ctrl＋Cのショートカットキーで設定できます。テーブルを設定すると、テーブル名や色を設定できます。今回は左側のテーブルに「ナンバー一覧」、右のテーブルに「チェック済み一覧」というテーブル名を設定します。
COUNTIF関数
COUNTIF関数は、ある条件を満たすセルの数を数えるのに使います。今回は、「ナンバー一覧」テーブルの「済み」項目にCOUNTIF関数を適用して、計算式が入った行の番号が「チェック済み一覧」テーブルの中に何個あるか調べます。
COUNTIF関数は次のような使い方をします。
=COUNTIF(数える範囲,数える値)
そしてそれぞれの引数には次のように入ります。
- 数える範囲：「チェック済み一覧[番号]」
- 数える値：「ナンバー一覧[@番号]」
今回、テーブルが設定されているので、A1のようなセル参照ではなく、テーブル名と項目名を使用できます。それにより絶対参照を意識しなくてもよくなります。これがテーブルを使う1つ目の理由です。
教える値に入っている「@」は計算式の入っている行と同じ行の1セルを指します。
また、今回は、計算式内で参照している数える値は、計算式が入るテーブルと同じテーブルにあります。この場合、テーブル名を省略することができます。
ナンバー一覧テーブルの「済み」項目には以下の数式が入ります。
=COUNTIF(チェック済み一覧[番号],[@番号])
これにより、「済み」項目には、チェック済みテーブルにその番号のある数が求まるのでここが0の項目が未チェックだとわかります。
IF関数とOR関数の組み合わせ
IF関数やAND関数、OR関数は、そうかそうではないかや、はいいいえなどの2つに一つを表すことができる論理関数と呼ばれています。
今回のチェック作業では、保留かどうかはその作業をする人が決め、保留の場合は1を入力し保留状態にしますが、その値と合わせて、保留かチェック済みのものを調べることになりますね。もしそうなら1、そうでなければ0という数値にしておき、0のものを探せば、すぐにどれを次はチェックすべきかわかります。
今回は、保留の場合は1が入っているはずです。またチェック済みは1以上の数字になっています。そのどちらかだったら、という条件にするので、「または」を表すOR関数を使います。
以下の表のように、保留が1または済みが1以上のものが、Chkが1になればいいのです。
OR関数は次のような使い方をします。
=OR(第一条件,第二条件)
今回、OR関数の引数には次のように入ります。
- 第一条件：[@保留]=1
- 第二条件：[@済み]>=1
つまり、次のようになります。
=OR([@保留]=1,[@済み]>=1)
この結果が合っていたら（真の場合）、1、そうでなかったら（偽の場合）、0とします。
それを実現するのはIF関数です。
IF関数は次のような使い方をします。
=IF(条件,条件の結果真の場合の値,偽の場合の値)
今回、IF関数の条件には次のように入ります。
条件：OR([@保留]=1,[@済み]>=1)
- 真の場合：1
- 偽の場合：0
つまり、次のようになります。
=IF(OR([@保留]=1,[@済み]>=1),1,0)
これにより、保留でもチェック済みではないものは、「Chk」項目の一番上にある0で判明します。
テーブルの参照範囲の自動拡張
ここで、チェック済テーブルの一番下に、未チェックの番号1つを入力してみます。
すると、ナンバー一覧のその番号の済み欄が1になり、チェック済みになったことがわかります。
普通のExcelの計算式では、参照した場所の外側に値を入力してもその値は計算の対象にならないので計算値は変わることはありません。
しかしテーブルを設定しておくと、テーブルのすぐ次の行にデータを入力すると、テーブル範囲がそこまで自動的に広がるので、COUNTIF関数の計算式の中に入れた「チェック済み一覧[番号]」が表している範囲も自動的に広がり、計算の対象になってくれます。
このことにより計算式を作り直す手間や、より複雑な計算式を考えることなく、計算式を作ることができるのです。
これがテーブル機能の計算式のすごいところの2つ目です。
MATCH関数
一番初めに0が出てくるのが何行目か調べられれば、自動的にそこにジャンプできる機能の実現に一歩近づきます。
その行数を調べるのに、MATCH関数を使います。
今回は、Chkが0になるところを探しますが、Chkの中にはたくさんの0が入ることが考えられます。
しかし、MACTH関数は、上から探していって一番初めに出てきたとことだけを探し当てるので、今回の目的にはぴったり合っているのです。
MATCH関数は次のように使います。
=MATCH(探す値,探す範囲,探し方)
MATCH関数の今回の使い方では、次のような引数になります。
- 探す値：0
- 探す範囲：ナンバー一覧[Chk]
- 探し方：探す値そのものを探す場合は0を指定
つまり、次のようになります。
=MACTH(0,ナンバー一覧[Chk],0)
実際には、MATCH関数はこれから説明する関数の中に2つ入るので、この計算式はどこにも入れずにいったんこのような計算式だということを覚えておきます。
INDEX関数
上からの行数がわかったら、ナンバー一覧の番号列のその行数にある値を見たら、何番かわかりますよね。範囲の中の何番目の値を調べるのがINDEX関数です。
INDEX関数は次のように使います。
=INDEX(範囲,何番目)
今回のINDEX関数では次のような引数になります。
- 範囲：ナンバー一覧[番号]
- 何番目：MACTH(0,ナンバー一覧[Chk],0)
つまり、次のようになります。
=INDEX(ナンバー一覧[番号], MACTH(0,ナンバー一覧[Chk],0))
これで、次の検索する番号が何番かはっきりわかります。
この計算式も次に説明する関数の中に入るので、どこにも入れずに覚えておきます。
HYPERLINK関数
Excelの関数の中でも最も変わり種の関数かもしれません。ハイパーリンク機能が関数になっているのです。この関数で作った文字列をクリックすると、指定したセルやURLにジャンプできるのです。
HYPERLINK関数は次のように使います。
=HYPERLINK(リンク先,表示文字列)
今回はリンク先をMATCH関数で調べた行数にあたるセル、表示文字列はINDEX関数で求めた番号とします。
ポイントはリンク先です、リンク先がWebページであればそのまま意識せずに書いていいのですが、セル参照を表すときは、セル参照を表す文字列の前に「#」が付いた文字になります。
もし、一番初めの番号であればセルB8になるので、「#B8」となります。
さらに、MATCH関数で求めた何番目は、ナンバー一覧テーブルの[Chk]項目の何番目であり、Excelシートの行番号ではありません。今回の表では、ナンバー一覧テーブルの[Chk]項目の上には7行あるので、Excelシートの行番号にするためには、MATCH関数で求めた何番目に7を足します。
そうなると、次のような計算式になります。
- リンク先：”#B”& MACTH(0,ナンバー一覧[Chk],0)
- 表示文字列：INDEX(ナンバー一覧[番号], MACTH(0,ナンバー一覧[Chk],0))
リンク先のセル参照をあらわす文字列は「#B」の文字に、MATCH関数で求めた何番目を付けたもの、表示文字列はINDEX関数で求めた、目的の番号ということになります。
つまり、次のようになります。
=HYPERLINK(“#B”&MACTH(0,ナンバー一覧[Chk],0),INDEX(ナンバー一覧[番号],MACTH(0,ナンバー一覧[Chk],0))
この式を、上の方の空いているセルに入力します。
これで、HYPERLINK関数で設定したセルの値は、常に保有ではなく、未処理の番号のうち、一番上のものが表示され、リンクになっている文字列をクリックすると、その番号にジャンプするようになります。
まとめ
今回は未チェックの項目をチェックし、チェックが終わったものを次々と確認していくための効率的なツールをExcelで作成する考え方と手順を紹介しました。
Excelのテーブル機能を使うことで、複雑な作業が簡素化され、直感的に一覧表を扱えます。テーブルを設定することで、計算式の記述がシンプルになり、エラーの可能性が低減します。
また、テーブルの範囲が自動的に拡張される機能は、新しい番号を追加するだけで、計算式が自動的に更新され、行も自動的に拡張されます。
COUNTIF関数を駆使して、未チェックの項目を瞬時に特定できます。この機能により、作業の進捗を把握しやすくなり、効率的な管理が可能です。
IF関数とOR関数を用いて、保留かチェック済みかを判断する仕組みを構築しました。これにより、未処理の項目を素早く発見し、重要な作業に優先的に取り組むことができます。
MATCH関数とINDEX関数を利用して、未処理の項目の位置やその番号を見つけ出すことができます。これにより、作業の進捗管理がより洗練され、スムーズな操作が可能です。
HYPERLINK関数を使用することで、作業者はクリック一つで未処理の項目に素早くジャンプできます。これにより、迅速な対応と正確な作業が実現されます。
これらの機能や関数を組み合わせることで、Excelを使った作業が非常に効率的になります。未処理の項目を見逃す心配がなくなり、迅速かつ正確な業務が可能です。是非、自身の業務に応用してみてください。Excelのパワフルな機能を駆使すれば、あなたも簡単にこれらのツールを作成できることでしょう。お役立ていただければ幸いです。
