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

 03-5294-5990

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

time 2017/02/06

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

こんにちは。前回の記事で「バッチグー」と書いたら、レビューのときに「古すぎて若者には判らないのでは?」という指摘を受けました。

とんでもない。JMIRIの社員は全員ピチピチです。

(前回の記事はこちらです)

では、続きにまいりましょう。

性別と疾病分類を変えられるようにしてみよう

mini001

単純に考えれば、前編で作ったデータソースに、新しいデータをコピーしてくれば、切り替えられるんです。

(コピぺ範囲… 「j0062」シートのA列 → 「ツール」シートのA列へ。

「j0062」シートのAA:AV列 → 「ツール」シートのB:W列へ。)

mini002

これでグラフを切り替えられました! ( ̄▽ ̄)

mini003

あとは、総数と性別をどこかに手書きすれば、一件落着!

mini023

~おわり~

 

 

 

もっとイケてるツールを作りたい人へ

確かにさっきのコピペじゃ、神ってるとは言い難いですね。

総数と性別をいちいちテキストボックスに手書きするのも、とっても面倒です!

それでは、もう少し恰好をつけてみましょう。

 

先ほどは手動でコピペしましたが、これを自動で行えばよいのです。

つまり、どこかで「性別」と「疾患分類」を指定すれば、データを検索してくれるような関数を組んでやれば、自動化が完成します。

 

では、「性別」と「疾患分類」を指定するエリアを作りましょう。

指定するところは、とりあえずテキストで書いておきましょうφ(..。) 「男」、「Ⅹ 呼吸器系の疾患」、っと……

mini004

ついでに、データソース部分も、ちょっとだけ見栄えよくしました。mini005

フォントを変えて、列幅を調整して、枠線をつけてみました。

「疾患分類」は文字数が多くなるので、データソース部分と疾患指定部分を同じB列に合わせるように、データソース部分を移動させました。

データソースを自動で取得しよう

自動取得の準備として、CSVファイルをExcelで開いたものと、今回作るツールが、同じExcelブックに入っていることを確認しましょう。

mini006

それでは、データを自動で取得してみましょう (^▽^)

使うのは、みんな大好き!VLOOKUP関数です。覚えてますか?

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

ゆる~く言い換えると、

=VLOOKUP(どの言葉で検索するの?, 取得したいデータの範囲。ただし検索する言葉は一番左!, 左から何番目のデータが欲しいの?, FALSE固定で問題なし!)

例を挙げると、こんな感じ。

mini016

=VLOOKUP($B$4,$F$4:$H$7,3,FALSE)

このVLOOKUPの結果は、「ごはん」の人気「3」が取得できます(⌒ー⌒)

 

さてこれを、データソースの部分に使っていきます。

mini007

では、手始めにC3の「総数」からvlookup化してみます。

mini009

mini008

mini010

検索値 「ツール」シートの、B10
範囲 「j0062」シートの、A6~BX63
列番号 27

mini011

=VLOOKUP($B$10,'j0062'!$A$6:$BX$63,27,FALSE)

目的の数字、「1829」を取得することができました。

ちなみに、検索値と範囲は、動かないように「絶対参照」しております。

あとは左の「0歳」、「1~4歳」……は、この数式をコピペすればいいだけ!

mini012

ぜんぶ1829になっちゃいました……(・・;)

そう、列番号「27」を固定にしているためです。この列番号、ひとつ右のセルを取得したければ、1増やさなくてはなりません。

でも、数式内に固定値を書いていくのは面倒!

そんな時は、別のセルに書いて、それを参照してしまいましょう。

=VLOOKUP($B$10,'j0062'!$A$6:$BX$63,C5,FALSE)

mini013

さて、実はこれだけだと、男性のデータしか取得できないんです!!

なぜって、VLOOKUPで指定する列番号が、「総数《男女合計》」「男性」「女性」で違うんです! ヽ(TдT)ノ

mini019

よく見ると、2・27・52と、25ずつ増えていることがわかります。

ということは、こんな風にすれば、計算できるんじゃないでしょうか?

総数《男女合計》 (25×)+
(25×)+27
(25×)+52

 

(25×性別ごとの数字)+総数《男女合計》の列番号VLOOKUPで指定する列番号

ちなみに、ここで言っている総数《男女合計》の列番号とは、上の図の ↑「総数」は2~ と書いてある数字(2,3,4,5……25,26)のことです。

 

ではこれを使って、先ほど行番号を書いたセルを、可変にしましょう。

まずは性別の選択によって、性別ごとの数字を取得します。IF文を使って、男なら1、女なら2、どちらでもなければ0にします。

=IF(A4="男",1, IF(A4="女",2, 0))

mini020

総数《男女合計》の列番号をどこかのセルに書き、VLOOKUPで指定する列番号を計算します。

=(25*$A$11)+C6

mini021

mini022

これで、データはばっちりです♪

あとは、B3セルの性別と疾患分類も、数式にしちゃいましょう。

mini014

mini015

やった!変わった!(≧▽≦)

総数の部分を自動化しよう

右上の総数の部分も、手書きだとめんどくさいので、自動化しちゃいましょう。

総数の数字自体はすでにVLOOKUPで取得しているので、書式を整えて、どこかのセルに書いておきます。

="(総数: " & TEXT(C3,"#,##0") & ")"

mini017

これを、グラフ側のテキストボックスから参照すれば、総数も自動化されます。

=ツール!$C$4

mini018

続きは次回!

長くなりましたが、これで、「性別」と「疾患分類」で切り替わるグラフを作ることができました。

これでも充分使えますが、次回はさらにこれを整えていこうと思います。

 

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

後編へ移動

タグ

このブログについて

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

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

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

[詳細]


お問い合わせ

arrowup

mail