読者です 読者をやめる 読者になる 読者になる
スポンサーリンク

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

excel ブラウザ siteinfo 正規表現 ドキュメント アクセス解析の結果

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




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


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

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

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数式を1セル分だけ貼り付けて,ドラッグしてオートフィルする。

=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...

関連する記事:

Excelブックの「シート目次」を,自動的に作成するバッチ (WSH/JScriptで,各シートへのリンク付きの目次を自動生成)
http://language-and-engineering.hatenablog.jp/entry/20110921/p1


「実行可能ドキュメント」が満たすべき性質 − テスト自動化ツール「Excelenium」で使われている技術や手法
http://language-and-engineering.hatenablog.jp/entry/20101112/p1


Google 検索の「サジェスト機能」を, Excel のシート上にVBAで作る (セルの内容に応じて入力規則を動的に変える方法)
http://language-and-engineering.hatenablog.jp/entry/20081125/1227571724