次のExcelシートをご覧ください。
問題点一覧表のデータがあって、そこに入力されている報告者名がドロップダウンで選択できて、選択するとその報告者の報告がリストで表示されます。
新しい報告者が報告すると、選択肢に新しい報告者が追加されています。
この仕組みはVBAを使わずに関数とテーブル機能だけで実現しています。
フィルターや並べ替え機能も使っていません。
これを作る手順を紹介します。
始まりの状態のExcelブックは、ここからダウンロードしたブックの問題点一覧です。ここから実際に操作ができます。
データの蓄積
データは、「いつ、だれが何をした」を1行ごとにまとめた形とします。この形が、Excelが様々な処理をしやすいデータベース構造になります。
最終的にはデータテーブルにしておくといろいろなメリットが出ます。今この時点ではテーブルにはしません。
重複したデータを削除する計算式
今回、同じ担当者が何度も書き込みます。しかし、選択するのにリスト表示されるのは1人1つのデータです。
ということは、重複しているデータが整理されるような仕組みが必要ということです。
機能としては、データタブの中に重複の削除機能がありますが、これを使うとデータが追加されるたびに自動実行するVBAが必要になります。
そこで、計算式でなんとかできないものか、考えてみます。
方法としては、はじめてその担当者名が登場したときに増えていくインデックス番号を用意し、そのインデックス番号を1から順番に検索してその時に登場した担当者名をリストしていくという方法です。
なんだか面倒な感じもしますが、まず、操作してみましょう。もし難しく思えたら下の青文字のところは読み飛ばしOKです！
セルD2は、「1」と入力します。必ず1つめの担当者名になるので1です。
セルD3は、次の計算式です。
=IF(COUNTIF($B$3:B3,B3)=1,D2+1,D2)
D3のセルを11行目までオートフィルします。
セルは、初めてのものかもしれないし初めてではないかもしれません。
どちらかを選択するので、IF関数を使います。COUNTIF関数でB3からB3までの範囲の中にB3の値が何個入っているか数え、その数が1ならば初めて登場したことになるので、上の値に1足します。これでインデックスが一つ進みます。もしもCOUNTIF関数の結果1以外ならば、上の値をそのままにし、インデックスを進めません。
数える範囲のB3の片方だけが絶対参照になっています。下にこの計算式をコピーする場合、こうしておけば範囲のはじめを常にB3に固定できるので、検索範囲をどんどん広げることができるのです。
次に、セルE2の計算式です。
=IFERROR(INDEX(B:B,MATCH(ROW()-1,D:D,0)),””)
E2のセルを11行目までオートフィルすればよいです。
現在の行番号をROW関数で求め、そこから1を引けば、求めたいインデックス番号が求まります。それをMATCH関数でインデックス番号の一覧から縦位置を求め、それに対応する位置の担当者を求めます。
ここまでが、重複データを表示するための中間処理です。
実際には使用者は意識しなくてもいいものですね。
では、実際にE列にリストされたものを、セルL2のドロップボックスの選択肢にします。
セルL2をクリックした後、データタブのデータの入力規則で、入力値の設定をリストにします。
そして次の計算式を元の値に入力し、OKボタンをクリックます。
=OFFSET($E$1,1,0,COUNTIF(E:E,”*?”)-1)
OFFSET関数は、範囲を出力する関数です。セルE1に対して、下に1つ、右に0つ動いたセルから、縦にE列すべての中の何らかの1文字が入っているセル個数－1個の範囲のセル範囲という意味です。
「*?」は、1文字以上の何かという意味です。1引いているのはセルE1の項目名分を数えたくないからです。
これで、セルF2のデータを選択できるようになります。
該当データを表示させる
該当データを表示させるためにも、中間処理が必要です。セルF2に次の計算式を入力します。
=IF(ROW()=2,IF(B2=$L$2,1,0),IF(B2=$L$2,F1+1,F1))
これを11行目までコピーします。
もしも2行目なら、L2とB列の文字が一致していたら1そうでなければ0、もしも2行目以外なら、L2とB列の文字が一致していたら上の値に1を足して一致していなかったら上の値を出します。
基本的には、一致しているかどうかを求める数式なのですが、1つ目のデータの場合、一つ上は項目名の文字列なので演算ができないので、直接0か1か判断しています。
そして、セルH2に次の計算式を入力し、J11までの範囲にコピーします。
=IFERROR(INDEX(A:A,MATCH(ROW()-1,$F:$F,0)),””)
ここまでで、計算式の入力は完了です。
H列が日付シリアル値になってしまったら、表示形式を日付に変更します。
テーブルにする
全体の範囲をテーブルにします。
実際に手入力するのはA列からC列までなのですが、全体をテーブルにしたいのです。
理由は次の通りです。
- D列からJ列まで計算式が入っています。
- テーブルに設定していた場合、A、B、C列に追加すると自動でその行のD列からJ列までの計算式が入ります。
- 今回、D列からJ列の計算式は、A列からC列のデータ量よりも少ないはずですが、最大の場合を考えると、全部担当者はバラバラであれば、手入力したデータ量と同じだけの種類が出てきます。
- また、全部が一人の担当者のデータであれば、手入力されたデータと同じ量のデータが表示されます。
- 以上を踏まえると、入力したら中間処理データを含め、自動で手入力と同じだけの計算式が自動で生成されてほしいのです。
- 以上の理由からA列からJ列のデータをテーブル化します。
セルA1をクリックして、ホームタブの中のテーブルとして書式設定から任意のものを選択し、先頭行を項目となるように設定します。
今回は、特にフィルターや並べ替えをしなくていいので、テーブルツールのフィルターボタンのチェックを外します。
テーブルにすると同じ項目名は許されません。また空白も許されません。そこで、元データのAからC列と、抽出した結果のH列からJ列が同じ項目名なので、H列からJ列の項目名に「.」を付けています。また、空白列のG列にも「.」だけの項目名にしています。
見せたくないデータを非表示＆列幅
見せたくないデータは非表示にして混乱しないようにするので、D列からF列まで非表示にします。
また列幅をいい感じに調整します。
まとめ
このようなツールでVBAを使わなければ、マクロ機能がない、ExcelOnlineでも動作するものが作れます。
ExcelOnlineで操作できるということは、スマホで外出先でも入力できるということです。
営業支援ツールが作りやすいかもしれませんね。
マクロ、VBAを使わずに関数だけで機能を付けるというツールの作り方もこれから注目されるかもしれません。
また、今回は重複データを削除したり、フィルターをしたりする計算式を紹介しましたが、Excel2019では重複を削除するUNIQUE関数、フィルターをするFILTER関数が搭載されるので、もっと作成をするのは簡単になります。
ExcelOnlineでもこの関数が使えるようになるのも近いと思います。
また、機能で行う場合は、データタブの重複の削除機能、今回のようなフィルター動作はデータタブの中の詳細で出せるフィルターオプションを使います。
そのような機能も、関数に置き換えることができるので、もしかしたら使わなくなってくる機能なのかもしれませんね。フィルターオプションはExcelの最もレガシーなフィルター機能でオートフィルターが現れてから紹介される機会が減りました。そのように使われなくなっていくのかもしれません。私はいつまでも何かに使いますけどね。
コメント