Excelで，表データを「要約」した円グラフを作成する方法　…SUMIF関数のワイルドカードにINDIRECT(ADDRESS(ROW()))して，情報をまとめる

下記は，2013年になってから今(9月)までの，本ブログ訪問者の利用ブラウザ。

f:id:language_and_engineering:20130916140045p:image

集計データをブラウザの種類ごとに要約して，円グラフにしてある。

この元データは，下記のように，ブラウザのバージョン情報まで含まれている。

これだと，情報が細かすぎるので，円グラフにしづらい。つまり，概要が分かりづらい。

InternetExplorer 8.0	97,790
InternetExplorer 9.0	62,050
InternetExplorer 10.0	56,582
Chrome 28.0.1500.95	46,082
Chrome 27.0.1453.116	45,410
Firefox 22.0	38,885
Firefox 21.0	37,274
Chrome 28.0.1500.72	35,056
Chrome 27.0.1453.110	33,068
Safari 8536.25	32,869
InternetExplorer 7.0	27,625
Firefox 23.0	27,520
Chrome 27.0.1453.94	22,021
Chrome 26.0.1410.64	20,889
Safari 534.30	18,416
Chrome 29.0.1547.66	16,698
Chrome 29.0.1547.57	14,369
InternetExplorer 6.0	12,475
Chrome 28.0.1500.71	10,791
Chrome 29.0.1547.62	10,396
Opera 9.80	9,667
Firefox 20.0	8,167
Safari 533.1	8,111
Safari 536.30.1	7,873
Firefox 17.0	5,857
Safari 7534.48.3	4,620
Chrome 27.0.1453.93	4,323
不明	3,918
AppleWebKit 536.26	2,734
Safari 536.29.13	2,539
Chrome 29.0.1547.65	2,295
Chrome 26.0.1410.65	2,188
Firefox 19.0	1,599
Sleipnir 2.9.16	1,596
Safari 534.59.8	1,566

（以下省略）

細かすぎる表データから，情報を「要約」した形で円グラフを作り，

Webページへのアクセス状況をわかりやすく・おおまかに視覚化したい。

できるだけ手早く終わらせるには，どのようなExcel関数を使ったらよいか？（VBAマクロは使わない）

作成方法

下記のようなExcel関数を使う。

SUMIF関数

INDIRECT関数

ADDRESS関数

ROW関数

COLUMN関数

「はてなカウンター」のアクセス解析画面から，ユーザーエージェントについて

上記のような細かい統計情報を取得し，Excelシートの左上にペースト。

そして，その隣に，下記のような「ざっくりしたブラウザ種別」を貼り付ける。

Chrome
InternetExplorer
Firefox
Safari
Opera
Sleipnir
AppleWebKit

その隣の列に，下記のようなExcel数式を１セル分だけ貼り付けて，ドラッグしてオートフィルする。

=SUMIF(A$1:A$50,"*"&INDIRECT(ADDRESS(ROW(),COLUMN()-1))&"*",B$1:B$50)

これで，ざっくりしたブラウザ種別ごとに，集計結果の数値が算出される。

あとは，その部分をもとに円グラフをワンタッチで生成するだけだ。

キモとなるExcelの数式を簡単に説明すると：

ADDRESS(ROW(),COLUMN()-1)　は，「現在のセル（そのセル自体，自分自身）の左側のセル」の参照用の番地を文字列で返す。G1セル上ではこの数式は "F1" として評価される。

INDIRECT( 文字列 )　によって，セルの番地文字列が，セルとして変換される。文字列をセルに変換している。

SUMIF(検索範囲，検索条件，集計範囲)　によって，特定の検索条件にマッチする行のみを集計し，合計値を算出している。検索条件には文字列のワイルドカードを使う事ができ，正規表現のように，文字列のあいまい検索が可能。たとえば，"*Firefox*" という検索条件であれば，"Firefox" を含む任意の文字列がマッチする。

数式そのものに，検索対象となるキーワード（ブラウザ名）を埋め込まないで，外にくくり出してセル参照しているところがポイント。

そうすれば，シートのメンテナンス性が高まる。新規ブラウザを追加しやすい。

こういうふうにドキュメントのメンテナンス性を向上させたい時に， INDIRECT / ADDRESS が大活躍する。

参考

SUMIFで文字列のあいまい検索をする：

指定した文字列が含まれるかどうかを判断する：Excel 一般｜即効テクニック｜Excel VBAを学ぶならmoug
http://www.moug.net/tech/exopr/009005...

エクセル関数の質問！2 条件で文字列を含まないものだけを合計したいのですがどう...
http://detail.chiebukuro.yahoo.co.jp/...

Excel(エクセル)基本講座：条件付き合計の関数(SUMIF,SUMIFS)
http://www.eurus.dti.ne.jp/~yoneyama/...

「隣りのセル」を参照する：

エクセルで現在のセル位置を返す関数というのはありますか？
http://detail.chiebukuro.yahoo.co.jp/...

Office TANAKA - ワークシート関数の解説[INDIRECT関数]
http://officetanaka.net/excel/functio...

ADDRESS関数の使い方　初心者のエクセル(Excel)学習・入門
http://excel.onushi.com/function/addr...

UAの「AppleWebKit」はAndroid：

AndroidのブラウザはSafari？スマホのUser-Agentを見てみよう！ | CreaMo!-クリーモ!-クリエイティブをMotto!集めるブログ
http://creamo.jp/webtech/android-user...