お久しぶりです。弊社内で毎週実施しているミーティング(案件やタスクの確認会)の中の小ネタとして、過去に発表したExcelのTipsをご紹介します。

「年齢毎の各サービスの売上金額のCSVを作成したけど、予想以上に年齢のパターンが多かった。。。」時に使えるtipsをご紹介します。

年齢別の各サービスの売上金額CSVイメージ

上記のようなデータをデータ分析基盤内で作成し、Microsoft PowerPointやMicrosoft Excelスプレッドシートなどで、報告用の資料を作成する時に、思ったより年齢のパターンが多く、PowerPointに貼ると縦に長すぎて上手く表現できない。Microsoft Excelスプレッドシートではスクロールしないと見れない。となります。

そこで、考えられる対応策は、

  1. 分析基盤内のデータに対して、改めて年代(10代・20代)でbin切りをする。
  2. Microsoft Excelスプレッドシート内で年代(10代・20代)でbin切りをして、年代で集約する。

1で、データベースに対してSQLを実行する場合
20代、30代のような10歳刻みの場合

–20代、30代のような10歳刻みであれば10で割って、floorして、10倍するだけでできる
SELECT
  age,
  FLOOR(age/10) * 10,
  CONCAT(STRING(INTEGER(FLOOR(age/10) * 10)), "代")
FROM
  table name;

10-14,15-19のような5歳刻みの場合

-5歳刻みの場合で、シンプルなのは、こちら
SELECT
case  
when age>= 10 and age < 15 then '10-14' 
when age>= 15 and age < 20 then '15-19' 
when age>= 20 and age <25 then '20-24' 
when age>= 25 and age <30 then '25-29' 
when age>= 30 and age <35 then '30-34' 
when age>= 35 and age <40 then '35-39' 
when age>= 40 and age <45 then '40-44' 
-省略-
else 'その他' end as ages
FROM
  Table name


-少しおしゃれなやり方 5で割って、floorしてから、5をかける
SELECT
age,
INTEGER(FLOOR(age/5) * 5) as age1,
CONCAT(STRING(INTEGER(FLOOR(age/5)*5)),'-',STRING(INTEGER(FLOOR(age/5) * 5 ) + 4 )) as age2
FROM
 table name;
▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
-おしゃれなやり方の結果イメージ
Row	age	age1	age2	 
1	56	55	55-59	 
2	36	35	35-39	 
3	47	45	45-49	 
4	52	50	50-54	 
5	40	40	40-44	 
6	60	60	60-64	 
7	47	45	45-49	 
8	47	45	45-49	 
9	47	45	45-49	 
10	47	45	45-49

として、10歳刻みや5歳刻みのCSVを改めて作成する。(分析基盤にいつでも、直接触れられる環境なら、この方法でも良いです!)

出来れば、今のCSVで対応したい!って方は、次を参考にしてください

   

2.Microsoft Excelスプレッドシート内で年代(10代・20代)でbin切りをして、年代で集約
20代、30代のような10歳刻みの場合

=FLOOR([年齢],[基準値])
[年齢]には年齢が記入されているセル番号
[基準値]には刻みの値
ex:=FLOOR(C5,10) 
10歳刻みのイメージ(Excel)

10-14,15-19のような5歳刻みの場合

=INT([年齢]/5)*5
=[年齢]/5 → INT関数で切り捨て整数化 → ×5する。
ex:=INT(C5/5)*5
5歳刻みのイメージ(Excel)

上記のどちらかで10歳刻み、5歳刻みを作成し、下のようにSUMIF関数などで、10代のA売上、20代のA売上をサマリーしていく流れで対応可能です。

 

 

1.2をご紹介しましたが、どちらもやりたくない、一発で対応したい人は、「bin切りをpivot table内で実行」をしてみてください。

まずは冒頭CSVイメージのデータをそのままpivot tableにしてください。(自動的に年齢が昇順になります)

行に年齢,値に各売上 ※元々のCSVと同じもの作ります。。

キャプチャに記載の通り、元々のCSVイメージと全く同じものをpivot tableにしています。
ここからが、今回のTipsになります。

以下のように、年齢全体を選択している形式にしてください。(太い↓みたいなカーソルの時にできます)

上記、年齢を選択した状態で右クリック

ピボットテーブルのグループ化

グループ化を選択すると

グループ化 閾値設定

上記のようにグループ化の閾値設定画面が立ち上がり、先頭の値、末尾の値、単位 の設定が可能です。
この設定画面にて、単位を”10″とすると、

ピボットテーブル 年齢グループ化 単位10

と10歳刻みのグループ化をすることが可能です。
しかしこれでは、10歳が1-10の中に入り、目的と違う結果なので、以下のように設定してください。

年代刻みの先頭の値を0から単位10にした場合

となりまして、正確な10歳刻みのグループ化が完成します!
同じように閾値設定画面にて、単位5とすると、

ピボットテーブル 年齢グループ化 単位5

と年齢5歳刻みのグループ化した形になります。

このようにちょっとしたtipsで、グループ化の集計もできますので、参考にしていただき、皆さんのお役に立てればと思います。

 


弊社では、このようにデータを抽出、加工し、Microsoft ExcelやPowerPointを用いて、見やすいレポートにしたり、データから見える課題の報告や課題解決にむけたご提案など、クライアント企業のデータ活用を推進しています。
弊社のデータ活用支援の一部はこちらをご覧ください。

  

  

一覧へ戻る