お久しぶりです。弊社内で毎週実施しているミーティング（案件やタスクの確認会）の中の小ネタとして、過去に発表したExcelのTipsをご紹介します。
「年齢毎の各サービスの売上金額のCSVを作成したけど、予想以上に年齢のパターンが多かった。。。」時に使えるtipsをご紹介します。
上記のようなデータをデータ分析基盤内で作成し、Microsoft PowerPointやMicrosoft Excelスプレッドシートなどで、報告用の資料を作成する時に、思ったより年齢のパターンが多く、PowerPointに貼ると縦に長すぎて上手く表現できない。Microsoft Excelスプレッドシートではスクロールしないと見れない。となります。
そこで、考えられる対応策は、
- 分析基盤内のデータに対して、改めて年代（10代・20代）でbin切りをする。
- 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-14,15-19のような5歳刻みの場合
=INT([年齢]/5)*5
=[年齢]/5 → INT関数で切り捨て整数化 → ×5する。
ex:=INT(C5/5)*5
上記のどちらかで10歳刻み、5歳刻みを作成し、下のようにSUMIF関数などで、10代のA売上、20代のA売上をサマリーしていく流れで対応可能です。
1.2をご紹介しましたが、どちらもやりたくない、一発で対応したい人は、「bin切りをpivot table内で実行」をしてみてください。
まずは冒頭CSVイメージのデータをそのままpivot tableにしてください。（自動的に年齢が昇順になります）
キャプチャに記載の通り、元々のCSVイメージと全く同じものをpivot tableにしています。
ここからが、今回のTipsになります。
以下のように、年齢全体を選択している形式にしてください。（太い↓みたいなカーソルの時にできます）
上記、年齢を選択した状態で右クリック
グループ化を選択すると
上記のようにグループ化の閾値設定画面が立ち上がり、先頭の値、末尾の値、単位 の設定が可能です。
この設定画面にて、単位を”10″とすると、
と10歳刻みのグループ化をすることが可能です。
しかしこれでは、10歳が1-10の中に入り、目的と違う結果なので、以下のように設定してください。
となりまして、正確な10歳刻みのグループ化が完成します！
同じように閾値設定画面にて、単位5とすると、
と年齢5歳刻みのグループ化した形になります。
このようにちょっとしたtipsで、グループ化の集計もできますので、参考にしていただき、皆さんのお役に立てればと思います。
弊社では、このようにデータを抽出、加工し、Microsoft ExcelやPowerPointを用いて、見やすいレポートにしたり、データから見える課題の報告や課題解決にむけたご提案など、クライアント企業のデータ活用を推進しています。
弊社のデータ活用支援の一部はこちらをご覧ください。
その他の記事も読む
・地域住民の生活行動範囲に基づいた「生活圏エリアマーケティング -LAM-」
・【Excel】CSVファイルの文字化けを解消する方法
・【Excel】住所から都道府県名だけを抜き出す方法
・Excel Power Pivotをリボンに表示する方法
・【Excel】bin切りをpivot table内で実行
・ExcelのデータバーをPowerpointに貼る方法
・【Excel】XMATCH関数の基本的な使い方とINDEX関数の組み合わせ
・Excelで2つのシートを比較する方法
・【Excel】重複なしのデータ個数を算出する方法
・【Excel】セルの数式をそのままコピーペーストする方法