トップ «前の日記(2007-05-12) 最新 次の日記(2007-05-16)» 編集

十日日記


2007-05-14

Link AVERAGEIFの代替関数組合わせ

たとえ最新のExcel 2007ユーザーでなくとも、Excelを利用しているなら一読しておきたいマイクロソフトの資料がある。「Excel 2007におけるパフォーマンスの改善」だ。これを読むと、見た目以外に変化がないように感ぜられるExcelも、それなりに進歩を遂げていることがわかる。

たとえばキャッシュメモリの上限は、2000/2002/2003/2007で64MB/128MB/1GB/2GBと増大している。また再計算においても、新しいバージョンほど最適化された動作を行なうようである。Excel 2007では、ついにマルチスレッドに対応した。

しかしこの文書の白眉は、Excelファイルの動作をいかに軽快にするかという課題を、ワークシート関数、VBA関数それぞれのレベルで解説している点だ。わざわざ「計算時間の測定」という項まで設け、Win32 APIを利用した測定関数を紹介しているほどの凝りようである。配列数式の遅さが数字として表われているし、手練手管な超絶関数技巧よりも作業セルを使ったほうが高速なことも書かれている。

この文書で目を開かされた(Sukat, his eyes uncovered!)ワークシート関数を1つだけ挙げるとするなら、SUMPRODUCT関数である。この関数は本来は内積を求めるもので、単価と数量とが書かれた行列から価額を出すような場合に使うわけだが、現実には別の目的で使用される。Excel 2007で単一の関数として実現されているCOUNTIFS(複数条件でのカウント)やAVERAGEIF(SUMIFの平均版)をExcel 2003以下で実現するのに有用なのだ。

それを書いていると大変だから、Excel豆知識29-3Excelでお仕事の紹介記事を参照。(ちなみに、「Excelでお仕事」はすばらしいExcelサイトである。)要はSUMPRODUCTを使って論理演算を行なっているわけだ。

ところが今回の記事で初見だったのは、論理演算における表現である。Excelでお仕事の紹介記事にあるように、私がこれまで見てきた日本語の解説ページはすべて

=SUMPRODUCT((R3C1:R8C1=R6C)*(R3C2:R8C2=RC5)*(R3C3:R8C3))

このように引数1つで乗算を重ねている。上の例では、最初の2項が条件式で、第3項が数値の配列だ。これは、次のように書き換えられる。

=SUMPRODUCT(--(R3C1:R8C1=R6C),--(R3C2:R8C2=RC5),(R3C3:R8C3))

まず、乗算を重ねるよりも関数の引数を増やしたほうが速い。それにGUIで関数を組み立てやすい。これはまあよい。驚いたのは「--」である。負数を2回かけることでTrue/Falseを1/0にできるのだ! しかも「*1」を行なうよりも速いのだという。最初は何か意味のある演算子かと思ったくらいだ。

最後にSUMPRODUCTを使ったオマケの話をしたい。Excel 2007のAVERAGEIFはSUMIFとCOUNTIFとの組み合わせで実現できるという話を目にすることがあるが、それはちょっと怪しい。つまり

=SUMIF(検索範囲, 条件, 合計範囲)/COUNTIF(検索範囲, 条件)

のように使用する心づもりかもしれないが、これでは合計範囲にNULLがある場合には計算が狂ってしまう。エラーも出ない(危険!)。このようなときにも、SUMPRODUCTが使える。たとえば次のようにすればよいだろう。

=SUMIF(検索範囲, 条件, 合計範囲)/SUMPRODUCT(--(条件),--(ISNUMBER(合計範囲)))

ここでのキモはISNUMBERを使ってNULLでない(より正確には数値の)セルの数をカウントしていることだ。しかし文書にあるとおり、SUMPRODUCTの部分は作業セルとして別の箇所に出しておくのが賢明かもしれない。

Tags: Excel
[]


プロフィール

渡辺 慎太郎(na@10days.org)

分野別表示

Admin | Client | Dev | Excel | Linux | PC | PDA | Web | iPad | web | 家電 | 文具 | | 英語 | 言語 | | 音楽

月別表示

1999|07|
2003|05|06|07|08|09|10|11|12|
2004|01|02|03|04|05|06|07|09|10|11|12|
2005|01|02|03|04|05|06|07|08|09|10|11|12|
2006|01|02|03|04|05|06|07|08|09|10|11|12|
2007|01|02|03|04|05|06|07|08|09|10|11|12|
2008|01|02|03|04|05|06|07|08|09|10|11|12|
2009|01|02|03|04|05|06|07|08|09|10|11|12|
2010|01|02|03|04|05|06|07|08|09|10|11|12|
2011|01|02|03|04|05|06|07|08|09|10|11|12|

最近の記事

雨量情報 dictionary.com Yahoo google Yahoo! 路線情報 東京アメッシュ l-mura l-aka l-momo 目次 r-mura r-aka r-daidai r-kiiro asahi.com nogulabo r-sora r-midori r-midori r-momo