Excelの最難関関数として君臨していたデータベース関数ですが、最近は最難関関数の座をキューブ関数に取られてしまったのですが、まだ、やはりデータベース関数使える人はすごいなって思われそうなので、データベース関数でしかできないことを紹介します。
データベース関数って、ほんと難しいわりに何に使うかってイメージしにくいんですよね。他の関数が簡単なケースだったりもします。
でも今回は、データベース関数ならではの使い方を紹介します。
私は、Excel関数はSUM関数の次にデータベース関数を使い始めたので、実は私のExcel経験年数＝データベース関数経験年数なのです。その時に使っていた方法を紹介します。鼻血覚悟の大盤振る舞いです。
なので、おそらく生産管理をしている方には、かなり面白い関数ではないかなと思います。
日本中で、このアプローチでデータベース関数を解説記事を書けるのは、おそらく私と、その当時一緒に仕事をしていてExcel関数を教えまくった後輩君の2人だけだと思います。
今回は演習ブック付きです。こちらから。ダウンロードして使ってください。
データベース関数って？の前に詳細フィルター
Excelでフィルターするって、もちろん並べ替えとフィルターの中のフィルターで設定されるフィルターで下向き三角から条件を設定するフィルターを思いつくじゃないですか。
でも、Excel95のちょっと前にこのフィルター機能ってできたのです。その当時は「オートフィルター」という名称でした。
その前はフィルターできなかったのかというと、表計算でフィルターできないって致命的じゃないですか。だから違う方法で実現できたのです。
それが、データタブの「詳細設定」でできるフィルター。
このダイアログボックスは「フィルターオプション」という名称になっていますね。
昔は、まさにメニューバーの「データ」の中に「フィルターオプション」という機能名であったこのフィルターでフィルターしていました。
データベースと検索条件範囲、抽出範囲を指定しなければなりません。
データベースは、元テーブルの項目を含めた範囲。
検索条件範囲は、条件を記述した範囲、項目を含めます。
抽出範囲は、抽出する項目の一覧の項目名範囲のみ。この項目だけ抽出されます。
今回の表は、フルマラソンを走ったタイムが選手ごとに記録されています。
この表で、ゴールが3時間未満の選手名とゴールのタイムを抽出するには、次のような範囲になります。
元データ範囲はA2からG12、検索条件範囲はB14からB15、抽出範囲はE14からF14です。
3時間は日付シリアル値で0.125ですので、条件範囲は<0.125の条件になっています。
OKすれば、抽出範囲以下にデータが抽出されます。
何よりも検索条件範囲を指定しなければならないのが面倒ですが、抽出を元データ以外のところに抽出できるという絶対的なメリットがあります。
マクロやVBAでフィルターを扱う時は、元のデータ以外に結果を出せるのでこのフィルターでやるのが本当にお勧めなのです。
データベース関数って
なぜはじめにフィルターオプションというレガシーな機能を紹介したかというと、私がこの機能を大好きだというのもあるのですが、いやいや。
データベース関数の引数の設定が、フィルターオプションそっくりなのです。
その前にデータベース関数ってどんなものか軽くお話します。
Dなんちゃらという名称の関数です。なんちゃらは、合計を求めるSUM、平均を求めるAVERAGE、個数を求めるCOUNTAなどが入る関数があります。
今回は個数を数えるデータベース関数、DCOUNTA関数について紹介していきます。
DCOUNTA関数は、元データの中で、検索条件範囲の条件に当てはまったものの中の、指定したデータ項目のデータの数を求めるという関数です。
つまりフィルターオプションの結果、検出された行数ということになります。
書式は次の通り。
=DCOUNTA(元データ範囲,数えるデータの項目,検索条件範囲)
元データ範囲と検索条件範囲は、フィルターオプションと同様のシート上のセル範囲を指定すればいいと思います。
元データ範囲は、A2からG12ですね。
検索条件範囲は、B14からB15ですね。
数えるデータの項目は1つのセルです。データの中に空白がなければ、数を数える関数であればどの項目で数えても同じなので、元データの空白のないデータ項目、例えばこの表だと時間は記録してないかもしれないけど、選手名って必ず入力してるじゃないですか。だから選手名と書いてあるセル、どれかでいいので入力します。セル指定じゃなくて”選手名”と文字列で指定することもできます。
この項目はDSUMのように、個数を数えるのではなくて、合計値などの計算を集計するのであれば、集計する1項目だけ指定します。DCOUNT関数では、数値のデータである必要があります。DCOUNTA関数では空白ではない項目ならどの項目でもOKです。
今回はA2でいいかなと思います。
つまり、ゴールが3時間未満の選手の数は次の計算式で求まります。
=DCOUNTA(A2:G12,A2,B14:B15)
適当なセル、今回はセルD15に求めてみます。
4人という結果になりました。
データベース関数はこう使え！実例
ここまでのことって、COUNTIF関数で求まりますよね。
=COUNTIF(G2:G12,B15)
数式もシンプルだし、何より面倒な条件範囲を作らないで、条件の書いてある1セルだけ指定すればいいのですから簡単です。
でも、ここで、次のシチュエーションを考えてみましょう。
今回、せっかく10kmごとの地点のタイムを計りました。
マラソンって、実施する時、道路を止めるじゃないですか。道路を止める時間って、できるだけ少ない方がいいと思うんですよ。
そこで、各地点でどのくらいのタイムで走るのをやめさせたら、どんな感じで完走者を出せていくのかの推移って知れるじゃないですか。
その推移を求める計算式は1つのDCOUNTA関数を1つ作ってコピーするだけでいいのです。
こんな条件の表と出来上がりイメージを作っておきます。
16行目に締め切りタイムを時刻形式で入力しておきます。
セルB15は下の0km時点のタイムをそのままコピーする「=B16」、セルC15には下のタイムと比較演算子を結合する「=”<“&C16」という式を入れ、右にコピーしておきます。
元データはA2からG12、検索条件はB14からG14の範囲です。
19行目にそれぞれの地点での完走者数を求めるには、次のように考えれば一番簡単ではないでしょうか。
同じ法則だから計算式は1つのみで横にコピーするだけにしたいですよね。
セルB19には、元データ範囲はすべてを選んでいいと思うのですが、検索条件は、B18からB19の範囲です。
横に１つコピーしたとして、セルC19には、元データ範囲はすべて、検索条件は、B18からC19の範囲です。
このように一つコピーしたことだけを考え、比較します。
違いは検索条件の終わりのセル、「B19」が「C19」に移動しただけで、他は移動していません。
その移動していない部分だけ絶対参照にすればいいのです。
数えるデータの項目は選手名でいいので、セルA2を絶対参照にして固定します。
つまりB19に入力すべき数式は次の通りです。
=DCOUNTA($A$2:$G$12,$A$2,$B$14:B15)
それで横にコピーします。
20行目には、全体に対する完走率を求めます。
完走率は、19行目に求めた計算式を全体の人数で割ればいいのです。全体の人数は選手名の数を数えましょう。選手名が入っているセルはコピーしても固定なので絶対参照にします。
セルB20には次の計算式が入ります。
=B19/COUNTA($A$3:$A$12)
それでパーセンテージ表示にして横にコピーしましょう。
21行目は、その項目単体で見て何％残ったかというのは全体の人数を基準にするのではなく、一つ前の時点で通過できた人数を基準にします。
一つ前を使うのでB列ではC列から始めます。
セルC21には次の計算式が入ります。
=C19/B19
それでパーセンテージ表示にして横にコピーしましょう。
これで完成です。
まとめ
データベース関数はこのように積み重ねた条件で計算する場合、そして、その計算式をコピーして使う時に役立ちます。
今回は、マラソンの通過時間を題材にしましたが、実は生産管理において効果を発揮します。
生産管理において不良品が累積していった時の通過率と、その工程での不具合率、歩留まりを管理することがとても大事です。
その状況をロット後に分析し、原因を早期に掴めば余計な材料や工程を通過させることがなくなるので、コストの削減につながります。
検査の結果をデータで取得しておけば、検査の閾値を決定するうえでも、この方法は有効です。閾値を何度も入れ替えるだけで、最終的な良品数を求めることもできます。
もしかしたらその時はシナリオ機能を使ってシミュレーションする方法が有効化もしれないですね。
コメント