HOME Open Source
今回、公開するプログラムは表の検索プログラムです。以下のような、身長と体重の表と同じような内容のデータが記述されている表を、異なるブックに存在する表データから検索し、抽出するサンプルです。
表の一致を、何の一致で、検索するかというところなのですが、通常は、シート名であったり、表のデータ項目名(先頭行)の一致で検索するのでは、ないかと思われます。ですが、これには、欠点があり、シート名や項目名が変更されたり、データの行や列が入れ替わったりすると、同じとは判定できなくなってしまいます。
これらの欠点を解消したものが、今回、公開したサンプルで、数学の統計学を利用した、表の検索となっています。表の一致検索に、こういったアプローチもあるかと思われますので、何かの参考にして、頂ければと思います。
ソースコードは、Microsoft社の製品 エクセル向けに書いたものなので、利用するには、ご使用のコンピュータに、エクセルがインストールされていることが、必須条件です。
このサンプルプログラムにおける、表一致の判定基準は、表内の個々のデータに対する文字列長の、平均値を集計し、それぞれの文字列長データの分散(ばらつき)が等しいかどうかにより、判定しました。これは、数値データと文字列データを気にすることなく判定できるメリットがあるかと思います。
判定には、統計学でいうところのF検定を利用していますが、少し分かりにくところもあるかと思いますので、簡単に説明したいと思います。
比較するデータ(変量)をAとBとします。『Aの分散とBの分散が等しい』という仮説を帰無仮説。対立仮説を、『Aの分散と、Bの分散は等しくない』とした場合は、両側検定を採用します。また、対立仮説に『Aの分散は、Bの分散よりも大きい』を採用する場合は、片側検定を採用します。
今回は、前者の『等しくない』を対立仮定としたいので、両側検定を採用します。続いて、データAとデータBのF値を算出するわけですが、これは以下の計算式で、算出できます。
F値＝データAの分散÷データBの分散
帰無仮説を棄却する領域を棄却領域と言い、以下のように定義され、VBA関数を使用することで、算出できます。また、有意水準には、0.05を採用しています。
'棄却域: F <= p_lft もしくは、 F >= p_rgt
p_lft = WorksheetFunction.F_Inv(0.05,データAの個数 - 1, データBの個数 - 1)
p_rgt = WorksheetFunction.F_Inv_RT(0.05, データAの個数 - 1, データBの個数 - 1)
先に求めた、F値が上記の棄却域に該当すれば、対立仮説の『分散が等しくない』という仮説が受容され、棄却域から外れていれば、帰無仮説である『分散が等しい』という仮説が、受容されたことになります。積極的に帰無仮説を肯定するという意味と区別するため、統計の世界では、受容という言葉を使用しているようです。
比較する他の表データによっては(数値の桁数が同じぐらいの場合)、このやり方では、不都合が生じる場合もありますので、その場合は、状況に応じて、仕様や判定方法を変更して、対応させてみてください。
文書比較のために、サンプルのデータをエクセルファイルに掲載していますが、サンプルデータに含まれる個人名などの情報は、実在の人物や団体とはまったく関係ありませんので、よろしくお願いいたします。
昔、働いていた会社で、商品の在庫管理を行っていた(私、職を転々としております)のですが、取引先の倉庫から、メールで送られてくる在庫データの形式が、日々変化しているのです。このデータを毎日、社内の在庫管理データに変換していた私から、すると非常に困るわけなのですが… 取引先の方に説明しても、一向に直さないんです。はっきり言うと、ただの嫌がらせだと思うんです。
どうしたものかと思い、色々、考えて、たどり着いたのが、今回の統計学を応用した、表の検索プログラムでした。このやり方に、気付いたときは、非常に嬉しかったのを、今でもハッキリ覚えています。
『嫌がらせからの脱却!』に見事成功しました。
サンプルに含まれるデータは、私の創作によるものです、サンプルデータに含まれる個人名、会社名などの情報は、実在の人物とはまったく関係ありません。
作者は、本ソフトウェアの使用または使用不能から生じるコンピュータの故障、情報の消失、その他あらゆる直接的及び間接的被害に関して一切の責任を負いません。
ご使用にあたり、改善の要望、不具合の発生等ありましたら、画面下のアドレスまで、ご連絡頂きますよう、宜しくお願いいたします。ご面倒、ご不便をお掛けしますが、宜しくお願いいたします。
