患者中心の保健医療を支える
処方情報分析のリーディングカンパニー

 03-3239-6840

厚労省のデータで、性別x傷病分類で切替可能な、年齢別ヒストグラムを作ってみよう(後編)

time 2017/02/13

※解説記事内の画像はExcel 2010のものです。

こんにちは。

前回までに、「性別」と「疾患分類」を任意に切り替えられるヒストグラムを作りました。

厚労省のデータで、性別x傷病分類で切替可能な、年齢別ヒストグラムを作ってみよう(前編)

厚労省のデータで、性別x傷病分類で切替可能な、年齢別ヒストグラムを作ってみよう(中編)

ここまでくれば、あと少し!

最終回である今回は、グラフを整えたり、ちょっと便利な機能を追加したりしてみましょう (*⌒0⌒)ノ

「性別」と「疾患分類」をリストで指定してみよう

前回、「性別」と「疾患分類」は、直接セルに手入力しておりました。

mini101

これをリスト化することで、入力が楽になるし、入力ミスも防ぐことができます。

mini102

では、リストの作り方です。

リスト化したいセル(今回はB10)を選択し、「データ」タブ → 「データの入力規則」 → 「データの入力規則」 をクリックします。

mini103

すると「データの入力規則」ダイアログが表示されるので、「入力値の種類」に「リスト」を選択します。

すると、「元の値」という欄が表示されるので、右側のボタンをクリックします。

mini104

すると、範囲を選択するダイアログに変わりますので、元データの入っているシート「j0062」の、疾患分類の範囲を選択します。

='j0062'!$A$6:$A$63

mini105

選択すると、「元の値」欄に範囲が自動で入力されるので、右側のボタンを押して範囲を確定し、「OK」を押します。

mini106

これで、疾患分類をリスト化することができました。

では、「性別」もリスト化してしまいましょうか。

総数、男、女の3つだけなので、直接入力で作成してしまいましょう。

先ほどと同じく、リスト化したいセル(今回はA10)を選択し、「データ」タブ → 「データの入力規則」 → 「データの入力規則」 をクリックします。

入力値の種類は「リスト」、そして元の値には「総数,男,女」と、カンマ区切りで入力します。

mini107

OKをクリックすると、性別のリストができました。

mini108

ヒストグラムをパーセント表示にしてみよう

例えば、次の2つのグラフを比較するとき。

患者数がだいぶ違うので、単純に比較してもいいのか、迷うところです。

mini109

そんな時は、グラフをパーセント表示にして、比較してみましょう。

グラフのデータソースの下に行を追加して、総数の下に、母数となる患者数の合計を、SUM関数で入れました。

mini110

ここで注意!厚労省のデータは単位未満を四捨五入しているので、ここで算出した患者数の合計値と、厚労省が出している総数は、ずれることがあります。mini111

ここでは、SUMの結果を母数として使っていきます。

さて、各年代の下に、《年代ごとの患者数/母数》という関数を入力し、年代ごとの割合を算出していきます。

他の年代にもこの数式をコピペするので、母数を絶対参照にするのを忘れずに!

=D3/$C$4

mini112

mini113

コピペしたら、変な値になっちゃいました…。

あ、セルの表示形式をパーセント表示にするのを忘れていました。

mini114

これで、きれいになりました。

では、グラフのデータソースを、患者数からこのパーセンテージに変更します。

グラフを選択して、右クリック → 「データの選択」をします。

 

mini115

「データソースの選択」ダイアログで、凡例項目(系列)の「編集」をクリック。

mini116

「系列値」の値を、患者数の範囲から、割合の範囲に指定し直します。

mini117

これで、グラフをパーセンテージにして、比較することができます。

mini118

いままで左上にあった、「(単位:千人)」は、「(患者シェア)」に書き換えました。

また、縦軸の最大値や間隔は、現在自動になっていますが、見やすいように固定にしてもいいですね。

(その場合はデータを変える都度、手動で調整することになりますが……)

 

「ツール」シートもきれいにしておこう

さて、ツールシートには、使う人にとっては特にいらない情報が見えていましたね。

mini119

それに、「性別」と「疾患分類」を入力する箇所も、一番上にあった方が便利です。

見えなくても良い情報は、行列を非表示にするか、フォントを見えづらい色にしてしまいましょう。

mini120

さらに、重要なセルをユーザが誤って消さないように、ロックをかけてしまえば、なお良いですね。

今回のこのツールでは、ユーザが編集するのは「▼選択してください」の中身だけでよいので、それ以外のセルをロックしてしまいましょう。

ユーザに編集させたいセルを選択して、右クリック。

「セルの書式選択」を選択します。

mini121

「セルの書式設定」ダイアログが出てくるので、「保護」タブの「ロック」のチェックを外します。

これで、このセルだけロックがかからなくなります。

mini122

では、シートをロックしましょう。

リボン「校閲」の「シートの保護」をクリックします。

mini123

「シートの保護」ダイアログが現れるので、OKを押します。

mini124

これでロックがかかりました。

関数を消そうとしても、ダイアログが出て、消すことができません。

mini124

 

これでツール完成です!

おめでとうございます!!ヾ(@^▽^@)ノ

 

もっと簡単なやり方があったんじゃないの?

…という声が社内から上がりましたので、簡単にお答えします。

・男女の選択方法がわかりづらかった。なんとかならないの?

やり方は他にもあると思います ( ̄ー ̄)

今回はなるべく厚労省のデータに手を加えない方法で作ってみましたが、もちろんこれを組み替えることも可能です。

例えば、厚労省のデータはこのように配置されていると、初回でご説明しました。

ministry008

これを縦に組み替えてやります。

mini126

どこかに「性別」という列を作って、疾患分類と組み合わせてキーにしてやれば、単純なVLOOKUPで値が取得できるはずです。

 

・なんでピボットテーブルを使わないの?

ピボットテーブルは、ローデータを集計するときにとても便利な機能です。

ですが、この厚労省データは、すでに集計済みのデータなので、ピボットテーブルは使えません (ノд・。)

mini127

もしどうしてもピボットテーブルを使いたい場合は、上述の「縦に並べる」テクを使って、無理やりローデータ風にしてやれば、できないこともないけれど……

かなり面倒くさいと思います(;´▽`A``

あとがき

全3回に渡りお送りしました、「厚労省データでツールを作ってみよう」シリーズ、いかがでしたでしょうか?

ご紹介したテクニックをすべて使わなくても、どこかに参考になる箇所があれば、うれしく思います。

 

(* ̄▽ ̄)ノ~~ See you!

タグ

このブログについて

このブログ(JMIRI Office部)は、製薬企業のマーケティング部門の方やアナリティクス/レポーティングを担当している方に向け、Excel / PowerPointなどのOfficeソフトの使い方、分析の考え方、グラフの使い方、美しい資料の作り方、効率的な作業の進め方など、仕事に役立つ様々な情報を少しゆるめに発信していきます。

Author:医療情報総合研究所(JMIRI)について

医療情報総合研究所 JMIRIは、まだビックデータという言葉が無いころに日本で初めて処方情報データベースの運用・分析を開始した、処方情報分析のリーディングカンパニー。これまでに累計数十万ページ規模の分析レポート/グラフを製薬企業に向けて作成/提供してきました。

[詳細]


お問い合わせ

arrowup

mail