Excel〜データの集計

複数の条件に合致したデータの集計１

・ データベースから複数の条件に合うデータを集計します。

■TEXT(値,表示形式)
値に特定の表示形式を指定したうえで文字列に変換して表示します。表示形式を指定する記号は【"】ダブルフォーテーションではさみます。
■DAVERAGE(Database,フィールド,Criteria)
表の中で検索条件に合うデータを探し指定した項目のデータの平均を計算する。Databaseは調べる表全体、フィールドには平均を求める項目のセル番号を指定するか数式や文字列を【"】ではさみ指定する。検索条件は別表を作成しCriteriaに指定する。
■DCOUNTA(Database,フィールド,Criteria)
表の中で検索条件に合うデータを探し指定した項目のデータの個数を計算する。Databaseは調べる表全体、フィールドには平均を求める項目のセル番号を指定するか数式や文字列を数式や文字列を【"】ではさみ指定する。検索条件は別表を作成しCriteriaに指定する。

　数値データ全体ではなく、複数の条件に合致したデータの平均を求めたいときなどに利用します。
元データは日経平均株価の時系列データ。シートの記入例（図1-14

日経平均株価(図1-14

　ここでは年や曜日ごとの騰落率がプラスとなる確率を集計してみます。前段階として日付データを年、月、曜日などの要素に分解します。A列の日付データは1900年1月1日を1としたシリアル値で管理されていますので、これから必要な要素を取り出すにはTEXT関数を使用します。
TEXT関数の表示形式にはいろいろな種類がありますがセルの書式設定(ユーザー設定)で使われるものとほぼ同じなのでそれが参考になります。またIF関数を使用し騰落が前日比上昇なら1や55日移動平均より終値が上なら1となるようにしています。（図1-15

日経平均株価(図1-15

　次にCriteriaで検索条件を示した別表のセル範囲を指定します。
検索条件は【B2:D3】の範囲です。年【2008年】、曜【月曜】、55日比【55日移動平均線より株価が上】の3つの条件すべてに合致した場合の平均を計算します。フィールドには【騰落】を指定していますが、これを平均したものは前日比がプラスとなる確率となります。

　よってDAVERAGE関数を使って前日比上昇確率を求めるとセル【G3】の数式は、
　=DAVERAGE($A$10:$O$8962,$L$10,$B$2:$D$3)となります。（図1-16
また、条件に合致するデータの個数はDCOUNT関数で求められ【H3】のセルの数式は
　=DCOUNT($A$10:$O$8962,$L$10,$B$2:$D$3)となります。（図1-17

DAVERAGE関数で集計(図1-16 DCOUNT関数で集計(図1-17

　別表の作り方を詳しく書きます。別表1行目には、条件の対象となる項目名を必ず入力しておき、2行目以降に条件を記入します。条件を同じ行に並べれば【かつ】を意味するAND条件、異なる行に入力すると【または】を意味するOR条件になります。（図1-18
DAVERAGE、DCOUNT関数以外のDSUM、DMAX、DMIN関数などの別表も上記と同じになります。

検索条件の別表(図1-18
Excelに戻る


複数の条件に合致したデータの集計２

・ Excel2007で追加された関数を使いデータベースから複数の条件に合うデータを集計します。

■AVERAGEIFS(対象範囲,範囲1,条件1,範囲2,条件2,…)　Excel2007
範囲内で条件に合うセルを探し、これと同じ行にある対象範囲のセルの平均を計算する。条件には平均を求める項目のセル番号を指定するか数式や文字列を【"】ではさみ指定する。
■COUNTIFS(範囲1,条件1,範囲2,条件2,…)　Excel2007
表範囲内で条件に合うセルの個数を数える。複数の条件がある場合はすべての条件に合致したセルを数える。条件にはセル番号を指定するか数式や文字列を【"】ではさみ指定する。

　Excel2007から追加された関数を使えば別表がなくてもDAVERAGE、DCOUNT関数と同様のことができます。 ここでは別表を使用していますが項目名を入れる必要がないので、別表もシンプルなものになります。 前述と同じ、年【2008年】、曜【月曜】、55日比【55日移動平均線より株価が上】の3つの条件で上昇確率を求めています。
それぞれAVERAGEIFS関数（図1-19、　COUNTIFS関数（図1-20となります。

AVERAGEIFS関数で集計(図1-19
COUNTIFS関数で集計(図1-20
Excelに戻る


複数の条件に合致したデータの集計３

・ フィルタ機能で抽出した結果だけを集計します。

■SUBTOTAL(集計方法,範囲)
指定した集計方法で範囲を集計する。表示されているデータだけを集計の対象とする。 。

SUBTOTALの集計方法(図1-21

　オートフィルタを使って抽出した結果だけを集計したい場合には、SUBTOTAL関数を使います。 SUM関数など他の関数では隠れた部分の数値まで集計されてしまいます。そこでSUBTOTAL関数で集計方法を指定し範囲を指定すると見えているセルのみ集計できるようになります。
集計方法は別表（図1-21にある通りです。オートフィルタの条件を変更すると自動的に計算結果も更新されます。
SUBTOTAL関数を使うと【G3】セルは
　=SUBTOTAL(1,L11:L8962)となります。 （図1-22

　オートフィルタ機能は、表内の任意のセルを選択した後、【データ】→【フィルタ】→【オートフィルタ】を選びセルに表示される【▼】ボタン をクリックするとデータが抽出できるようになります。


SUBTOTAL関数で集計(図1-22
Excelに戻る


ランキング順に表示する

・ 上位10銘柄を表示等ランキング表示します。

■VLOOKUP(検索値,範囲,列番号,検索の型)
検索値に指定した値を範囲に指定した表の左端で探し、該当する行の列番号で指定した列からデータを取り出す。検索の型は完全一致の場合FALSE、近似値はTRUEとなる。
■RANK(数値,範囲,順序)
数値が範囲の中で何番目の大きさか調べる。順序を省略もしくは0で降順、1と指定すると昇順の順位となる。

　上位10のデータを表示する等ランキングでデータを抽出する場合は、RANK関数とVLOOKUP関数を利用することで可能になります。
まず順位をつけたいデータを用意します。ここではα値を利用します。 このときもVLOOKUP等の関数を利用して他のシートからのリンクにしておくと後で元データを更新したさいにも自動更新されるので便利です。
　日本水産のα値の順位を調べるので「数値」はセルAB6を指定し「範囲」はデータ全体の【AB6:AB230】とします。 このときオートフィルで全体にコピーすることを考え絶対参照にしておきます。（図1-23
また、「順序｣の項目は省略するか0を指定すると降順となり1を指定すると昇順の順位が表示されます。 今回は降順なので省略しました。

RANK(図1-23

　図1-24はβ値上位20の銘柄とデータを表示させた表です。
まずB列に表示させたい順位を入力します。次にその順位のｺｰﾄﾞを表示させるようにVLOOKUPの関数を入力します。 1位の銘柄を表示する場合、「検索値」は順位なのでセルB68、「範囲」はRANK関数で表示した表となります。 ここではシート名【一覧】の【AC6:AK230】となります。「列番号」は直接数字を入力してもよいのですがここでは1行目に表示されている数字を参照しています。この数字はCOLUMN()関数で表示される数字で列番号を示しています。ここではコードを表示したいのでAK列を検索するようにしています。「検索の型」は完全一致なのでFALSEとします。
　ただしこの方法では、同順がある場合にエラーとなります。この場合は、RANKで求める値に極小の値を加えることで対処します。少し正確でないですが、ここではこの程度の精度で十分と思われます。
　残りの銘柄名やその他の指標は上で求めたコードを元にVLOOKUP関数で検索します。

降順で表示(図1-24

Excelに戻る