--- title: 【Sisense Formula】～ごとの平均、最大、最小ってどうやって求めるの？ description: 棒グラフに基準線を引きたい。例えば平均ラインとか…そんな時にAVG関数で平均を求めますが、何で平均を取るのかによって数式が変わってきます。都道府県毎の平均、会員当たりの平均から最大、最小などの数式の書式をマスターします。 image: https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/12_avg_all_chart-1024x579-Oct-07-2020-03-04-18-68-AM.png --- ![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png) ![INSIGHT LAB_文字のみ](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png) [![お問い合わせ](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7) - [Sisenseとは](https://service.insight-lab.co.jp/sisense) - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [トレーニング](https://service.insight-lab.co.jp/data-analytics-dojo/training_course) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) ###### 5 分で読むことができます。 # [【Sisense Formula】～ごとの平均、最大、最小ってどうやって求めるの？](https://knowledge.insight-lab.co.jp/sisense/formula/avg_aggregation) 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 更新日時 2020年10月16日 ###### Topics: [Formula](https://knowledge.insight-lab.co.jp/sisense/tag/formula) ![【Formula】～ごとの平均、最大、最小ってどうやって求めるの？](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/12_avg_all_chart-1024x579-Oct-07-2020-03-04-18-68-AM.png) 目次 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/00_corn-300x199-Oct-07-2020-03-04-14-10-AM.jpg?width=300&height=199&name=00_corn-300x199-Oct-07-2020-03-04-14-10-AM.jpg)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/00_corn-3.jpg) 週末は札幌にいたんだけど、北海道のとうもろこしが一番おいしい季節なもので、２本ほど持ち帰った。今朝の朝食はピュアホワイト（とうもろこしの品種）。 うーん、シフクの時間… 以前、道南を旅行した時に「とうきみあります」っていう看板を目にしたのを思い出した。トウキビの間違いじゃないかと思ったのだが、NAVERで調べると、北海道南部から青森、秋田、岩手では「とうきみ」と呼ばれているらしい。 宮城に行くと「とうみぎ」、広島県では「まんまん」... とうもろこしは全国色んな呼称で食されているようだ。 つまりどこでも穫れるものとも言える訳だが、僕は全国どこを探しても、この時期の北海道のとうもろこしの右に出るものはないと思う。嘘だと思うなら北海道に行って食べてみてー **[北海道の旅行・ツアー（うちのお客さんのサイト）](https://www.jtb.co.jp/kokunai/area/hokkaido/)** さて、今回は**[【Sisense Data Modeling】インポートクエリを使ってデータベースSQLでインポートする](https://knowledge.insight-lab.co.jp/sisense/data-modeling/import_query)**で作成したec\_sql\_unionのデータを使って、顧客ごとの注文金額を都道府県で比較してみる。ウィジェット内で計算式を設定する時にすんなりいかないので、ここはしっかりマスターしておきたい。 ## 1. AVG関数で平均を求める 平均注文額を求める場合、２通りの方法がある。 **(1)AVG関数を使って求める** **(2)注文金額の合計 / 注文会員のユニーク数** 特に(1)で平均を求める場合、何をもって平均するのか。 ここでは「会員ごと」であり、その会員ごとの平均を都道府県で比較することをゴールとしている。 その時に、全体の平均に対して、各都道府県は上回っているのか、下回っているのかも求めてみる。 ### 1.1 ソート問題をカスタム列でクリアする Sisenseはソートに弱い。このやり方が万能というのは、どうもないんじゃないかと最近思っている。 今回は都道府県を北～南に都道府県コード順に並べたいとする。 ウィジェット上で値のみで（区切り基準を使わず）単純な棒グラフや折れ線グラフにしたい場合は**[【Sisense Widget】棒グラフのソート順を設定する裏ワザ](https://knowledge.insight-lab.co.jp/sisense/widget/columnchart_sortoder)**で行った方法が有用なのだが、区切り基準を指定して積み上げグラフで表現したい場合などには破綻してしまう。 じゃあ、カスタム列を作ってこんなふうにしたら悩まずに済むんじゃないか。。。 01-北海道 02-青森県 ・ ・ 47-沖縄県 これならテキストで普通にソートしてくれる（この表記が許されるというのが前提ではあるが）。 ではカスタム列をどうやって作るか。 まず、カスタム列の追加を行う。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/01_custom-1024x504-Oct-07-2020-03-04-19-34-AM.png?width=1024&height=504&name=01_custom-1024x504-Oct-07-2020-03-04-19-34-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/01_custom-Oct-07-2020-03-04-25-76-AM.png) 都道府県ポイントデータの「都道府県コード」と「都道府県」を「-(ハイフン)」でつなげるためには以下のように数式を指定する。 ・カラム名　 　#都道府県 ・数式 　　　　RIGHT('0' + ToString(\[都道府県コード\]),2) + '-' + \[都道府県\] [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/02_custom_formula-1024x404-Oct-07-2020-03-04-16-17-AM.png?width=1024&height=404&name=02_custom_formula-1024x404-Oct-07-2020-03-04-16-17-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/02_custom_formula-Oct-07-2020-03-04-32-26-AM.png) ### 1.2 ウィジェットにAVG()で平均値を表示する 今回は、都道府県名を横に表示したいので「横棒グラフ（いわゆるBar Chart）」を作成する。 上で作成した「#都道府県」をカテゴリに設定し、値は次の数式を指定する。 ``` AVG([会員番号],SUM([注文金額])) ``` [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/03_avg_formula-1024x492-Oct-07-2020-03-04-17-07-AM.png?width=1024&height=492&name=03_avg_formula-1024x492-Oct-07-2020-03-04-17-07-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/03_avg_formula-Oct-07-2020-03-04-14-81-AM.png) [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/04_barchart-1024x487-Oct-07-2020-03-04-29-16-AM.png?width=1024&height=487&name=04_barchart-1024x487-Oct-07-2020-03-04-29-16-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/04_barchart-Oct-07-2020-03-04-27-57-AM.png) ちょっと解説しよう。 チャートにおけるAVGの計算ではどの単位で平均するのかが重要だ。 Avg(\[注文金額\])とすると、注文金額の平均なので、明細単位での平均となる。 今回の場合、顧客ごとの平均を求めたいわけだから、上記の数式で指定をすることになる。 ここで、明細テーブルの会員番号を指定する場合と会員テーブルの会員番号を指定する場合で結果が異なるので注意が必要だ。 前者の場合は注文実績のある会員の平均となる一方で、後者では全ての（注文実績のない会員も含めて）会員の平均となる。 整理すると以下がAVGの基本書式だ。 ``` AVG(集計単位, 計算式) ``` このような「～ごとの集計」のことをSisenseでは**multi-pass aggregation**という。 multi-pass aggregationの書式を使用できる関数はAVGの他に**Min(),Max()**などがある。 ### 1.3 平均値のラインを入れる 次に平均値の計算式を使用してさらにその平均を求めてみる。 具体的には、1.2では都道府県毎の平均値を求めたが、その平均を求めるという方法。 ``` AVG([#都道府県],AVG([会員番号],SUM([注文金額]))) ``` [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/05_avgavg_formula-1024x492-Oct-07-2020-03-04-20-74-AM.png?width=1024&height=492&name=05_avgavg_formula-1024x492-Oct-07-2020-03-04-20-74-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/05_avgavg_formula-Oct-07-2020-03-04-13-35-AM.png) AVG(集計単位, 計算式)に当てはめただけなので、あえて説明は不要と思うので割愛する。 系列を「線」とし、色をオレンジで指定するとこうなる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/06_barchart_avgline-1024x577-Oct-07-2020-03-04-09-55-AM.png?width=1024&height=577&name=06_barchart_avgline-1024x577-Oct-07-2020-03-04-09-55-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/06_barchart_avgline-Oct-07-2020-03-04-08-74-AM.png) ### 1.4 最大、最小のラインを入れる 1.3で入れた平均のラインと同様の方法で最大のラインを入れてみる。 まずは、先程作成した「平均」を複製する。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/07_copy-1024x505-Oct-07-2020-03-04-24-08-AM.png?width=1024&height=505&name=07_copy-1024x505-Oct-07-2020-03-04-24-08-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_copy-Oct-07-2020-03-04-28-40-AM.png) AVGをMAXに入れ替えるだけなので、数式はこれでいい。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/08_max-1024x493-Oct-07-2020-03-04-17-87-AM.png?width=1024&height=493&name=08_max-1024x493-Oct-07-2020-03-04-17-87-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/08_max-Oct-07-2020-03-04-11-56-AM.png) ``` MAX([#都道府県],AVG([会員番号],SUM([注文金額]))) ``` 数式を指定したら、系列を線にして色を緑で指定すると、以下のように会員当たりの注文金額が最も多い佐賀県と同じ高さに緑のラインが引かれている。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/09_barchart_max-1024x582-Oct-07-2020-03-04-12-50-AM.png?width=1024&height=582&name=09_barchart_max-1024x582-Oct-07-2020-03-04-12-50-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/09_barchart_max-Oct-07-2020-03-04-24-91-AM.png) MAXと同じ要領でMINを指定すると会員当たりの注文金額が最も少ない秋田県と同じ高さに緑のラインが引かれる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/10_barchart_min-1024x575-Oct-07-2020-03-04-33-09-AM.png?width=1024&height=575&name=10_barchart_min-1024x575-Oct-07-2020-03-04-33-09-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/10_barchart_min-Oct-07-2020-03-04-20-14-AM.png) ## 2. 全体平均を求める 現在のウィジェット内の平均ライン（オレンジ）は都道府県毎の平均を平均したものなので、全体平均ではない。 では都道府県を考慮せずに単純な全国平均を求めるにはどうしたらよいか。 ### 2.1 AVGにALL()を追加してみる 単純な理屈からすると、今回の**AVG()**関数に[【Sisense Formula】ALL()とStar(☆)の合わせワザで構成比を表示する](https://knowledge.insight-lab.co.jp/sisense/widget/all_and_star)で学んだ**ALL()**を追加してあげるとうまくいくんじゃないかと思う。では早速やってみるね。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/13_avg_ng_expression-1024x458-Oct-07-2020-03-04-15-54-AM.png?width=1024&height=458&name=13_avg_ng_expression-1024x458-Oct-07-2020-03-04-15-54-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/13_avg_ng_expression-Oct-07-2020-03-04-22-84-AM.png) ここで設定した数式は ``` (AVG(AVG([会員番号],SUM([注文金額]))),ALL([#都道府県])) ``` 結果は [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/14_avg_ng_chart-1024x537-Oct-07-2020-03-04-26-65-AM.png?width=1024&height=537&name=14_avg_ng_chart-1024x537-Oct-07-2020-03-04-26-65-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/14_avg_ng_chart-Oct-07-2020-03-04-10-59-AM.png) ありゃ、AVG(\[会員番号\],SUM(\[注文金額\]))と変わらない。 じゃあ、他のパターンで (AVG(\[会員番号\],SUM(\[注文金額\])),ALL(\[#都道府県\])) ・・・上の結果と同じ (AVG(\[#都道府県\],AVG(\[会員番号\],SUM(\[注文金額\]))),ALL(\[#都道府県\]))・・・AVG(\[#都道府県\],AVG(\[会員番号\],SUM(\[注文金額\])))と同じ うーん...うまくいかない。 ### 2.2 AVGを使わずに数式で平均を求める 冒頭で平均を求める方法は2種類あると書いた。 今度は平均を求める2つめの方法 (2)注文金額の合計 / 注文会員のユニーク数 を用いて計算する。 今回の例では、都道府県全体（都道府県を無視してと言ってもいい）の平均を求めるから、ALL()を用いて計算させる。 数式は以下のとおり ``` (SUM([注文金額]),ALL([#都道府県])) / (COUNT([会員番号]),ALL([#都道府県])) ``` [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/11_devided-1024x492-Oct-07-2020-03-04-29-85-AM.png?width=1024&height=492&name=11_devided-1024x492-Oct-07-2020-03-04-29-85-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/11_devided-Oct-07-2020-03-04-31-60-AM.png) 解説すると**AVG()**関数に[【Sisense Formula】ALL()とStar(☆)の合わせワザで構成比を表示する](https://knowledge.insight-lab.co.jp/sisense/widget/all_and_star)でご紹介したX軸を無視して合計を算出する方法を分子、分母で使用している。 結果はこうなる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/12_avg_all_chart-1024x579-Oct-07-2020-03-04-18-68-AM.png?width=1024&height=579&name=12_avg_all_chart-1024x579-Oct-07-2020-03-04-18-68-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/12_avg_all_chart-Oct-07-2020-03-04-33-94-AM.png) ## 3. まとめ 〜ごとの平均の数式について説明してきた。 ちょっと納得いかないところもあるんだけどね。 そういうもんだと割り切って使おうね。 ではまた！ Sisenseを体験してみませんか？ INSIGHT LABではSisense紹介セミナーを定期開催しています。Sisenseの製品紹介や他BI製品との比較だけでなく、デモンストレーションを通してSisenseのシンプルな操作性やプレゼンテーション機能を体感いただけます。 [![詳細はこちら](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0) - [Tweet](https://twitter.com/share) ![Turtle](https://knowledge.insight-lab.co.jp/hubfs/Sunflower.jpg) #### 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 可視化領域を中心とした業務に従事しています。 ###### [前の投稿](https://knowledge.insight-lab.co.jp/sisense/data-modeling/cdata) [![【Connector】CDataのkintone Connectorでkintoneのデータをダッシュボード化してみる](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/03_kintone_connectors-1024x512-Oct-07-2020-03-08-11-98-AM.png)](https://knowledge.insight-lab.co.jp/sisense/data-modeling/cdata) ###### [【Sisense Connector】CDataのkintone Connectorでkintoneのデータをダッシュボード化してみる](https://knowledge.insight-lab.co.jp/sisense/data-modeling/cdata) ###### [次の投稿](https://knowledge.insight-lab.co.jp/sisense/widget/blox1_indicator) [![【BloX】BloX体験-1 : デフォルトテンプレートを使ってシンプルなインジケータを作ってみる](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/14_widget1-1024x159-Oct-07-2020-03-20-47-29-AM.png)](https://knowledge.insight-lab.co.jp/sisense/widget/blox1_indicator) ###### [【Sisense BloX】BloX体験-1 : デフォルトテンプレートを使ってシンプルなインジケータを作ってみる](https://knowledge.insight-lab.co.jp/sisense/widget/blox1_indicator) RECOMMEND こちらの記事も人気です。 ###### 4 分で読むことができます。 #### [【Sisense Administaration】無料のSSL証明書"Let's Encrypt"でSSL化してみる](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) ###### 2020年 10月 16日 ###### 8 分で読むことができます。 #### [【API連携】Spotify音楽データの分析①【Sisense】](https://knowledge.insight-lab.co.jp/sisense/information/spotify_music_data_1) ###### 2020年 10月 16日 ###### 2 分で読むことができます。 #### [【Sisense Widget】折れ線グラフを階段状に表示する](https://knowledge.insight-lab.co.jp/sisense/widget/step_line_chart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】Radar Chartを使ってポケモンの種族値を表示してみた](https://knowledge.insight-lab.co.jp/sisense/widget/radarchart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Formula】CASE関数を使って条件分岐する](https://knowledge.insight-lab.co.jp/sisense/formula/case) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】折れ線グラフにおける欠落値の対処法](https://knowledge.insight-lab.co.jp/sisense/widget/line-graph) ###### 2020年 10月 16日 ##### 運営会社 運営会社 - [会社概要](https://www.insight-lab.co.jp/about/company.html) - [お問い合わせ](https://contact.insight-lab.co.jp/main) ##### メニュー メニュー - Sisenseとは - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) [![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=200&height=55&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png "INSIGHT LAB_文字のみ_WHT-1")](https://www.insight-lab.co.jp/) - [プライバシーポリシー](https://www.insight-lab.co.jp/privacy.html) Copyright © 2020 INSIGHT LAB, Inc.