2004年4月28日（木）「マクロなしExcelばなし」Tweet

　新番終番はこのリストでOKかな…。感想サイトリンク集もこっそり追加修正しておきました。

　日記というのは日々思ったりやったりしたことを綴るもの…、つことで懲りずにExcelメモ。興味のない方はさっくり読み飛ばしちゃってくださいな。IE見の場合はフォントサイズを「小」にしないと表が見にくいです。（Ctrlキーを押しながらマウスホイールを上へくるっと回すべし）

　前回のExcel話で、感想率、終了番組評価、新番組継続率の入力フィールドはデータを入力すると、下表のようになる、と書きました。

　感想率表の場合、E列のデータは感想率、つまり0%〜100%までの数値が入ります（0%は入らないけど）。ですから、サイトごとの感想扱い率や番組ごとの感想率はSUMIF関数を使い、サイトごと／番組ごとに感想率を拾い合計し、取り扱い数で割ることによって計算が可能です。

　しかし、継続率や終了番組評価の場合、Eの評価列に入るのは「不可」「継続」、「駄作」「殿堂入り」などの、数値ではないデータが入ります。そして、ただその「不可」や「駄作」などの数を数えるのではなく、番組ごとにそれぞれの数を数える必要があるわけです。それはつまり、感想率表において、「(1)ある番組における(2)感想率20%以下になっている サイト数はいくつ？」というような二重の検索条件をクリアしなければならないというのと同じ、ということになったりします。

　Excelにおいて、二重の検索条件から検索するには、Database関数を使う必要があります。ですが、Database関数は他の関数とはちょっと扱いが違ってややこしかったりするのと、フィールド名（列の名前）が必須だったりするため、正直 なんかよくわからん のです。何かうまくいくイメージがわかないのよね。

　つことで知ってる範囲で、集計する方法を考えてみた。まず考えたのはこれー

※意味のあるところだけ抜粋

　見ての通り、さっきの表１のようなシートから、別シートにコピーし、その際「不可」〜「継続」を別の列に数値「１」として表示するようにした表です。

　A〜D列は表１からリンク貼り付けするだけだから特に何も書かないけども（でもセルに「0」が入らないような工夫はする）、E〜H列の各セルには、

（例えばE2セルの場合）
　=IF(表１!E2=$E$1,1,"")

のような計算式が入っています。この場合「表１の評価が入った列（E列）にフィールド名（列の一番上：この場合は「不可」）と同じものが入っていたら、“ 1 ”としなさい」という式ですね。

　列が分かれたことにより、検索列における検索条件がひとつとなり、例えば、「番組3」の「見切り」とされた数をもとめる場合、SUMIF関数を用いて

　=SUMIF(D:D,番組3,G:G)
　※単純化してあります

　という計算式で求めることができます。これは感想率集計でも使ったように、「D列に『番組3』とある行をG列（見切り列）において合計しなされ」というもので、表２で計算すると、D列に『番組3』と入っている行、行4と行8が計算対象行になり、結果 見切りのG列の計算は、G4+G8 というものになり、この場合「2」が出てきます。

　てなわけで、これで計算できるように…はなったんですが、この計算方法だと、今度の調査には対応しきれないのです。問題は表１のF列、そう、今回から導入予定の「コメント」欄ですな。

　表示する方法を考えた際、それぞれのコメントはまず「番組ごとにまとまっている」必要があり、かつそれは「継続〜不可／殿堂入り〜駄作」の順で表示されなければならない。表２の計算方法だと、その対応したコメントが引っ張り出せないのでぃす。そこで表２は結局

※意味のあるところだけ抜粋
※長いのでサイト→サ／番組→番に変わってます

という表３に変更。E〜L列に数値ではなくサイト名や番組名を表示させるようにしました。出し方は表２における1を表示させる方法とさほど変わらないので割愛。

　表２で行った、各番組ごとの各評価集計は、SUMIFではなくCOUNTIFを使い、たとえば、番組3の見切り数を数える場合（さっきの例と同じです）、

　=COUNTIF(J:J,番組3)

　のように、J列の番組3を数えるという計算で求めることができます。ですが、COUNTIF関数はSUMIF関数のように、検索列と計算（合計）列を別に持つことができないため、各サイトにおける評価数を集計するために、サイト名を表記した列（E、G、I、K列）が別に必要になっていたりします。

　さて、これでなぜ各番組ごと、各評価ごとの「コメント」が拾えるようになるのかというと、それは、「それぞれの評価ごとに分かれた列において、番組タイトルのある行番号が拾えるようになるから」ですね。行番号、つまり上から何行目にあるかがわかることにより、それに対応したコメントを拾うことができるのです。

　表３はもともと、表１のE列（評価列）を８列に分解したというだけのもので、表３にある評価は列は変わっていても、行番号は表１と同じです。そしてコメントは表１のF列固定で記入されています。つまり、表３のそれぞれの評価列にある番組名を見つけた行番号をそのまま表１のコメント列の行番号に当てはめて引っ張り出せば、ある番組のある評価のコメントが出てくるという算段です。

　具体的にはMATCH関数を使います。例によって「番組3」の「見切り」評価をつけている行番号を引っ張り出すには…

　=MATCH(番組3,$J$1:$J$10000,0)

　のようにすれば、J列の頭から検索し、「4」（行）という値が出ます。次の「番組3」を探すには同じ計算式では出せませんが、これもOFFSET関数を使い検索範囲を変更してやれば問題なく算出することが出来ます。＜10月31日のだらけ日記参照（ちょっと違うけど）

　出てきた「4」の数値をもとに、表１から、

　=INDEX(表１!$F$1:$F$10000,4（さっきの計算結果：行）,1（列）)

　というある範囲から（この場合はF1〜F10000）指定した行および列の値を引っ張り出すINDEX関数をもって、コメントを検出、出力します。

　実際はこの基本計算に加え、検索値が見つからなかった場合のエラー表示をしないようにしたり（エラー値がそのままあると計算できないため）、うっかりミスをしないようなチェックセルを入れたり、コメントにサイトリンクを併記した形のHTMLを出力するための形にしていたりするので、もうちょっとだけ複雑になっています。

　そんな感じにだいたい形になってきてます。あとは集計しながら微調整だろうなぁ。