松廼屋｜論点解説　薬剤師国家試験対策ノート【衛生】論点：食中毒統計 / Excel Tips 編

2019/07/03 09:59

　この機会に、Microsoft Excel のデータベースの簡単・便利な使い方をマスターして、薬剤師国家試験【衛生】で頻出する論点「食中毒統計」をEBM（Evidence-Based Medicine）的なアプローチで、完全攻略するアイテム「マイデータベース」を手に入れましょう！解説します。

松廼屋｜論点解説　薬剤師国家試験対策ノート【衛生】論点：食中毒統計 / Excel Tips 編

食中毒統計は、その原因物質の特性などをもとに一定のトレンドを持っているとはいえ、食中毒は、ほぼ毎日、日本のどこかで発生していますから、その集計結果は日々移り変わります。1年に1度は、食中毒統計の年報のようなまとまった報告書が提出されていれば、それを熟読すればいいのですが、食中毒統計を活用する人たちは、ミクロな目的からマクロな目的まで、様々な目標や課外解決に至るための多種多様の目的を持っているので、手元にあるデータから調べ自分が知りたいことがすぐわかるツールがあると便利です。特に、薬学を学ぶ薬学生の皆さんや薬剤師の皆さんは、科学的根拠と薬学のバックグラウンドに基づく職能から薬学的考察を行う上で、生の最新のデータが手元にあり、最新で最善の判断ができることは、そのスキルとして期待されることです。医療と健康にかかわる仕事をされている方たちにとって、食中毒統計の知識が最新ですぐ手に入りわかりやすくまとまっていることは、とても有用です。
そこで、このeラーニング / 松廼屋｜論点解説では、過去5年間の食中毒のデータを使用して、Excel のデータベースを作成し、そのデータベースを活用してデータマイニングやピボットテーブルによる集計、さらにピボットグラフ作成などをさくっと素早く効率的に行う方法について、実際のエクセルファイルを用いて、データベースを作る形で解説します。

https://matsunoya.thebase.in/blog

食中毒統計 / Excel Tips 編

目次｜
1｜準備するもの
2｜基本となるデータベースを作成する
3｜データベースへ新たな項目やクロス集計図表を加える
4｜応用
5｜追記

▽＝＝ PR　松廼屋 Mats.theBASE ＝＝▽
デジタルダウンロード商品にExcel TipsとPowerPoint Tipsを詰め込んでテンプレートで販売中です。
今回の論点解説が気に入ったら、是非、ダウンロード商品をお買い求めください。購入後、ダウンロードすればすぐに使用できます。クレジットカード決済のみで取り扱っています▼

△＝＝ PR  https://matsunoya.thebase.in/ ＝＝△

さあ、はじめましょう！

食中毒統計 / Excel Tips 編

目次｜
1｜準備するもの
2｜基本となるデータベースを作成する
3｜データベースへ新たな項目やクロス集計図表を加える
4｜応用
5｜追記

1｜準備するもの

1-1｜厚生労働省HP / 食中毒統計資料
(3)過去の食中毒事件一覧から5年分のエクセルファイルをPCにダウンロードして保存し用意します。
(3)過去の食中毒事件一覧 / 内訳｜
平成30年（2018年）食中毒発生事例 Excel形式：252KB
平成29年（2017年）食中毒発生事例 Excel形式：196KB
平成28年（2016年）食中毒発生事例 Excel形式：215KB
平成27年（2015年）食中毒発生事例 Excel形式：222KB
平成26年（2014年）食中毒発生事例 Excel形式：183KB

1-2｜Microsoft Excel を搭載したPC
解説ではOS Windows 10 & Microsoft Office 2016 / Excel 2016を使用します。
1-3｜データベース作成にかかる時間
所要時間は、PCのExcelを起動してから30分間ほどです。

2｜基本となるデータベースを作成する

操作をショートカットキーで解説します。ショートカットキーは覚えると素早くデータベースを作成したり様々な操作を数秒で行えるので、この機会に、実際にデータベースを作りながら覚えると良いです。
2-1｜Microsoft Excelの起動と新規ファイル作成
Excelを起動しましょう。たいていはExcelのアイコンをクリックすると起動します。そうしたら、「新規」-「空白のブック」をクリックして、新規の空白のブックファイルを開きます。このファイルに名前を付けて保存します。例：「食中毒統計2014-2018」

ショートカットキー｜Fn（ファンクションキー）+ F12

このとき、PCのドキュメントフォルダーに保存すると、OSがWindows 10の場合は、OneDriveと自動的に同期したり、様々な拡張機能がスムーズに使用できるので、ドキュメント内に保存することをお勧めします。

2-2｜データベースの作成と元データのコピー
新規ファイル「食中毒統計2014-2018」のスプレッドシートにデータベースのデータを入れる箱を作ります。Excelではこの箱を「テーブル」と呼びます。ファイルを開いて新規シートを表示します。OneDriveと同期して自動保存する設定になっているか左上のマークで確かめ、自動保存しない設定にしてください。このほうが軽く動きます。データベース作成が終わったら、最後に手動で保存しましょう。新規データベースの作成の方法は、色々やり方がありますが、ここでは、厚生労働省の食中毒統計データを元に作成するので、まず、ダウンロードしてきた平成26年（2014年）食中毒発生事例のファイルを開きます。開いたファイルのB2セル（左から2列目、上から2行目のセル）項目「都道府県名等」をクリックして選択してください。ここから、2014年の食中毒統計の表をすべて選択しコピーします。

ショートカットキー｜Select B2 Cell, Ctrl+Shift+↓→ Ctrl+C

少し解説｜B2セルをクリックしたら、左手小指でコントロールキーを押したまま左手薬指でシフトキーを一緒に押し、そのまま右手薬指で下向き矢印↓を押し、右手薬指だけ指をいったんキーから離し、今度は右手小指で右向き→を押します。これで、表のすべてのセルを選択できたのを確かめ、両手の指をすべてキーから離して、左手の小指でコントロールキーを押したまま左手の人差し指でキーボードのCを押し表全体をコピーします。
新規ファイル「食中毒統計2014-2018」のD3セルを選択して、コピーしたテキストのみ（表についた書式やその他の機能を移さないようにします）をすべて値貼り付けします。そして、D1セルに戻ります。

ショートカットキー｜Select D3 Cell, Alt H V S V Entr Ctrl+↑

少し解説｜Altキーを押してキーから指を離し、一つ一つのキーをHVSVとポンポンポンポンと押します。値貼り付けになっていることを確かめて、右手小指でEnterキーをポンと押します。そのあと、コントロールキーを押しながら上向き矢印↑を押すとD1セルに移動します。
データベースに必須の要件は、何かご存知ですか。それは、それぞれのレコードのid（ユニークキー）です。idは、レコードごとに異なっていて、決して同じではない英数字の組み合わせです。普通の表とデータベースが違うのは、このidがそれぞれの行（レコード）の左端にあることです。idをつけましょう。ただしここでは、idを右端につける方法をお教えします。同じ行の中にあれば、idはどの列にあってもいいからです。まず、表を「テーブル」に変えて、データベース機能が使えるようにして、そのあと、テーブルの右端M3セルにidの列を付加します。今回は、idは、1から連続する数字にします。そして、D3セルに戻ります。

ショートカットキー｜Select D3 Cell, Ctrl+Shift+↓→ Ctrl+T Entr
Ctrl+→　→ "id" Entr 1 Ctrl+Shift+↓ Alt H F I S Entr Ctrl+↑←

これで、エクセルでデータベース機能を活用したデータマイニングをするための箱「テーブル」が出来上がりidが加わりました。このテーブルに、さらに2015 - 2018の食中毒統計のデータを入れていきます。2015年のファイルを開いてB3セルを選択し、表の項目を除くデータだけ全て選択してコピーします。
※事前に、各年次の表の項目が一致していることを確認します。届出制度の改正などがあった場合、翌年の項目の順番や項目の数が異なる場合があります。

ショートカットキー｜Select B3 Cell, Ctrl+Shift+↓→ Ctrl+C

新規ファイル「食中毒統計2014-2018」のD3セルを選択して、テーブルの最終行のすぐ下の行のセルを選択し、コピーしたデータを値貼り付けし、D3セルに戻ります。

ショートカットキー｜Select D3 Cell, Ctrl+↓　↓  Alt H V S V Entr Ctrl+↑

残りのデータ（2016-2018）も同様にダウンロードしたファイルからコピーして値貼り付けしてください。サクッとできましたね。では、最後に新たに加えたレコードにもidを付加します。D3セルをクリックしてから、右端のM4セルを選択し、1からデータの最終行のセルまですべて選択した後、連続番号を入力します。D3セルに戻ります。

ショートカットキー｜Select D3 Cell, Ctrl+→　↓
Ctrl+Shift+↓ Alt H F I S Entr Ctrl+↑←

以上の操作で、食中毒統計（5年間 / 2014-2018）の基本のデータベースが出来上がりました。シートの下にあるシート名をクリックし、「食中毒統計DB」と変えてから、ファイルを保存します。

3｜データベースへ新たな項目やクロス集計図表を加える

皆さん、自分のPCにマイデータベースが、加わりましたか？では、この章では、薬剤師国家試験の学習ツールアイテムとしてデータマイニングが容易にできるように、もう少し、レコードの情報を加え、さらにピボットテーブルとピボットグラフを加えます。
3-1｜年・月・日の項目を付加する
食中毒統計DBシートのデータベースには、「発生月日」という項目があります。食中毒が発生した年月日がここに入っています。書式を日付に変更します。

ショートカットキー｜Select D3 Cell, →↓ Ctrl+Shift+↓ Alt H FM 「表示形式」-「日付」Entr

ここで、E3セルの「発生月日」という項目の下向き三角▼をクリックして、データの内容を確認してください。コピーしてきたデータである2014年の1月から12月、そのあとの2015-2018のそれぞれの1月から12月のデータがもれなくすべて存在することを確認します。たまに、数値ではなく文字列として入力されている場合があります。フィルターから文字列入力のレコードを抽出して、数値に変換しておきます。これは、この後の関数入力の年月日のデータがエラーだった場合にも発見できます。年月日は、それぞれ、年と月と日に分けておくと、この食中毒が年次推移で増加しているのかとか、8月に多く発生しているのかとか、同じ日に同時に起きているのかとか、考察するためデータマイニングによって集計するときに便利です。それぞれの項目をデータベースの右端の列に、関数で付加します。年月日のシリアルから、それぞれ年、月、日を抽出する関数を示します。

関数1｜年月日のシリアルから年を抽出する｜=YEAR([@発生月日])
関数2｜年月日のシリアルから月を抽出する｜=MONTH([@発生月日])
関数3｜年月日のシリアルから日を抽出する｜=DAY([@発生月日])

データベースの左端のD3セルを選択した後、右端の列に順番に新しい項目（年、月、日）を入力し、その下のセルにそれぞれの関数を入力して、全ての行に同じ関数をコピーします。

ショートカットキー｜Select D3 Cell, Ctrl+→　→"年"〔Tab〕”月”〔Tab〕“日” Entr

ここで、年の項目の下のN4セルが選択されているのを確認します。タブキーをテーブル内で使用したとき、最後にエンターキーを押すと最初にタブキーを押したセルの下のセルを選択します。
N4セルを選択して、関数1を入力します。タブキーを押して、O4セルを押して、関数2を選択します。タブキーを押してP4セルを選択して、関数3を入力します。
この状態で、オートフィル機能が働いて、同じ列のすべての行に同じ関数がコピーされる場合がありますが、ここでは、自分で最後の行までコピーする方法を示します。

ショートカットキー｜Select N4 Cell, Ctrl+Shift+→↓ Ctrl+D

これで、食中毒統計データベースに、新たなデータマイニング用の項目「年」「月」「日」が加わりました。それぞれの項目のセルの下向き三角▼をクリックして、中身を確認、必要な数値がすべて入っていることと、エラーや空白がないことを確かめてから、ファイルを上書き保存します。

ショートカットキー｜Ctrl+S

3-2｜データマイニングの最強アイテム / ピボットテーブルを作成する
Excelのデータベース機能を活用したデータマイニングの最強アイテムといえば、皆さんもご存知のピボットテーブルとピボットグラフです。膨大なビッグデータを、集計し視覚化して直感的に必要な考察などのアウトプットを得られるツールです。作成したデータベースから、ピボットテーブルとピボットグラフを作ります。データベースの左上端のD3セルを選択して、テーブルをすべて選択し、ピボットテーブルを新規のシートに挿入します。

ショートカットキー｜Select D3 Cell, Ctrl+Shift+→↓ Alt N V Entr

新規シートにピボットテーブルが表示されたら、シート右上の「ピボットテーブルのフィールド」の下にあるデータベースの項目をスクロールして最後から3行の年・月を表示します。年を列に、月を行にドラッグアンドドロップします。値に患者数をドラッグアンドドロップし、フィルターに原因物質をドラッグアンドドロップします。ピボットテーブルができました。

3-3｜データマイニングの最強アイテム / ピボットグラフで視覚化する
出来上がったピボットテーブルのフィルター（B1セル）の下向き三角▼をクリックして、食中毒の原因物質ごとに、各発生年の患者数の月次推移を見てみます。ここでは、寄生虫-クドアをクリックして選びます。クドアの5年間の月次推移のピボットテーブルが出来上がりました。

データを視覚化して直感的に月次推移を読み取ることができるよう、ピボットグラフを挿入します。ピボットテーブルの左の上の端A3セルを選択して、Excel上部に表示されたメニューのピボットテーブル分析からピボットグラフ「積み上げ縦棒」を選び、書式でグラフの高さを11cmにします。

ショートカットキー｜Select A3 Cell, Alt JT C "積み上げ縦棒" Entr Alt JA H 11 Entr


グラフを見やすい位置にドラッグして移動します。ピボットテーブルの横に置いてみましょう。ここで、グラフのデザインを選んで、月次推移が読み取りやすくします。グラフを選択し、デザイン-クイックレイアウトから矢印キーを押して好みのデザインを選びます。今回は、グラフの下に表がついているものを選択します。

ショートカットキー｜Select the fig. Alt JC L ↓　→ Entr

課題｜仕上がったグラフを見て、寄生虫-クドアの食中毒が、季節によってどのように変動するか、また、5年間の年次推移のトレンドを、目で確かめて、5年間の食中毒統計から推察されることを考察して記述してください。
■
■
■
グラフは、書式を整えて必要なテキストを入力して仕上げます。何のグラフかわかるようにグラフの上のI1セルにラベルを付けます。グラフの上のI1セルを選択して、文字列を参照する関数を入力します。文字列を参照するときは、イコールの後に文字列が入っているセルを参照して&でつなぎます。間に特定の文字列を入れるときは、" "で文字を囲んで記入します。

関数4｜=A1&"｜"&B1

ショートカットキー｜Select I1 Cell, "=A1&"｜"&B1"


あなただけのオリジナルのデータマイニングツールが出来上がりました。これで、薬剤師国家試験の衛生で食中毒統計を論点とした問題は、全て最新のデータによって勉強できるようになりました。さらに、お好みで、マイデータベースをカスタマイズしてワンランクアップ & 食中毒統計を完全攻略しましょう！

食中毒統計 / 月次推移（2014-2018）

原因物質｜
アニサキス▼

腸炎ビブリオ▼

ノロウイルス▼

カンピロバクター▼

ウェルシュ菌▼

腸管出血性大腸菌▼

4｜応用

マイデータベースが出来上がったら、原因食品の項目から、気になる魚を抽出して、データマイニングしてみよう！
1｜データの入ったテーブルの右端に項目「原因食品2」を追加、原因食品のデータを全てコピーして値貼り付け、魚の名前は原則、ひらがな/漢字名を「カタカナ」に置換して統一します。例えば、項目「原因食品2」のデータの最初の行のセルから最後の行のセルまですべて選択します。Ctrl+Hで、置換する前「ひらめ」と置換した後「ヒラメ」の文字を入れます。Att+Aですべて置換します。

2｜データの入ったテーブルの右端に、さらに魚の名前を入力する項目「魚類」を右端に追加、「原因食品2」の項目セルの下向き三角▼をクリックして、魚の名前「例：ヒラメ」でフィルターします。右隣の項目「魚類」の空白セル最上部にヒラメと入力して、入力したセルからフィルターしたまま最後のセルまで全て選択し、Ctrl+Dでヒラメと入力します。
3｜データの入ったテーブルの右端に、さらに項目「その他魚類」を追加、ヒラメ単独の場合はレコードに「単品」と入力します。テーブルをすべて選択して、ピボットテーブルを挿入します。フィルターに「魚類」、「その他魚類」、列に「病因物質」、行に「年」、値に「患者数」を入れます。魚類でヒラメ、その他魚類で単品を選択、ピボットグラフを挿入し考察します。
仕上がったグラフを見て、ヒラメの食中毒が、どのような原因物質によって発生するか、また、5年間の年次推移のトレンドを、目で確かめて、5年間の食中毒統計から推察されることを考察して記述してください。
■
■
■

なお、Excelは、iPhoneでMicrosoft Excelのアプリ（無料 / 有料）をダウンロードすると、iPhoneを使用しながら、閲覧や編集ができます。トライしてみるのもおすすめです。ただし、すごく軽いファイルのほうが動きやすいです（笑）。

今回は、以上です。お疲れさまでした。
＝＝＝＝＝
デジタルダウンロード商品にExcel TipsとPowerPoint Tipsを詰め込んでテンプレートで販売中です。
今回の論点解説が気に入ったら、是非、デジタルダウンロード商品をお買い求めください。購入後、ダウンロードすればすぐに使用できます。クレジットカード決済のみで取り扱っています▼
個別にカスタマイズした商品のご相談を承ります。CONTACTからご相談ください。CONTACT https://thebase.in/inquiry/matsunoya
すべてのデジタルダウンロード商品
こちら https://matsunoya.thebase.in/categories/457317
Excel TipsとPowerPoint Tips を詰め込んだテンプレート
こちら

5｜追記

ピボットテーブルに初めて触れた方は驚かれると思いますが、ピボットテーブルは、データ更新のコマンドが走るたびに、テーブルの書式をリセットします。はるか昔から現在に至るまで、バージョンは変わってもずっとそうなのです。せっかく一生懸命努力を重ね行の高さと列の幅を整え、少し色などを足して見やすく整えたのに、行の高さが、列の幅が、特別な色が、リセットされてしまう。これを、防ぐ方法があります。
@Mats_blnt_pharm on Twitter の人気企画「Mats'リテラシーTips」でご紹介しました。ご参考になさってください。
Microsoft Excel pivot table tips ｜

1. 行の高さを固定するには？

普通の操作ではA1セル左角上の四角をクリックでシート全体選択、行左端を右クリックしてR「行の高さ」を押し値入力ですが、更新時に書式が初期化されることがあるので、見えない部分の右端の列に■を入力&フォントサイズ調整をすると、■の大きさで行の高さが止まります。

2. 列の幅と書式を保持するには？

ピボットテーブル左上角(図ではA3セル)を右クリック、O (ピボットテーブルオプション)を選択、「レイアウトと書式」-書式の Alt+A「更新時に列幅を自動調整」で☑をはずし、Alt+P「更新時にセル書式を保持」に☑を入れます。

また、ひとつ、役立つTipsを学びましたね。では、また。

以上、BlNt より

松廼屋 Mats.theBASE BLOG　https://matsunoya.thebase.in/blog

BLOG https://matsunoya.thebase.in/blog をお気に入りに登録してください。eラーニング / 薬剤師国家試験対策ノートの論点解説を公開中です。
更新日：2019.06.25　制作：滝沢幸穂（Yukiho.Takizawa）phD ■Facebook プロフィール　https://www.facebook.com/Yukiho.Takizawa

Twitter、Instagram、Facebookからの情報発信も、更新中です。

お友達や知り合いに、松廼屋 Mats.theBASE BLOGで学習したeラーニングを勧めてみたい方は、いいね！、口コミ、おススメなど、よろしくお願いします！
インスタグラムからの情報発信はこちらです。