Excel データ整理とグラフ化の例題(コロナウイルス統計を題材に)

2021年7月27日

厚生労働省の「新型コロナウイルス感染症について」のオープンデータを題材に

データ整理とグラフ化を行います。

画像に alt 属性が指定されていません。ファイル名: image-217.png
PCR検査の実施件数と検査陽性率
陽性者/重傷者/死亡者

データの取得

以下のデータが別々になっているのでダウンロードしてシート別に貼り付けます。

  • 陽性者数
  • PCR検査実施人数
  • 重傷者(途中のスクショは別のデータを使ってしまっていたのでスルーしてください)
  • 死亡者数

利用にあたって陽性者数とPCRはその日の発生者、

重傷者は当日の該当者(回復によって減少)、

死亡者数は過去からの累計と数値の特性が異なることに留意する必要があります。

データの加工

累計から新規発生者を逆算

「死亡者数」が累計になっているため、新規発生者数に逆算します。

元のデータを(累計)とし、C列に死亡者数列を作成します。

まず最初のデータは累計も新規発生者も同じのため、

そのまま参照するか値コピーで同じにします。

そして次以降の行は、その行から一つ前の行を引く数式にして全行にコピーします。

データを統合(結合して横に並べる)

データによって開始日が異なったり、日の欠けがあり

グラフにし辛いため新しいシートにデータを統合します。

まず開始日を決めて、オートフィルで日付の行を作成します。

次に年・月・週の列を作ります。

年はYEAR関数、月はMONTH関数、週はWEEKNUM関数です。

次にXLOOKUP関数でそれぞれのシートを検索し、

合致しないものは0か空白にします。(エラーにしない)

ここまで来るとグラフにしやすいですが、

曜日による影響が大きく凹凸が激しくなるため、ピボットテーブルで集計します。

(どうやっても祝日や年末年始などの影響はでますが)

まず行には年を入れて、その次に月か週を入れます。(集計する基準。今回は週)

月と週は年を超えると数値がリセットされるため、どちらでも先に年が必要です。

そして値に数値の合計と日付の最小(最小では、その週・月の最初の日付になる)を設定します。

重傷者だけは新規発生者でなく、その日の入院者数のため特性が異なるため平均にします。

(最良は週の中の最終日の数値ですが、加工が面倒なので平均。最小や最大が良い場合もあります)

※スクショは途中まで重傷者の「合計」で撮ってしまっているので、スルーしてください。

このような表になるため調整します。

「デザイン」より「レポートのレイアウト」、「表形式で表示」を設定します。

次に「アイテムのラベルをすべて繰り返す」を設定します。

最初の状態では小計と総計が表示されるため、それぞれ削除します。

それぞれ「小計を表示しない」、「行と列の集計を行わない」を設定します。

次に陽性者数÷検査実施で陽性率を計算します。

コピーしてから計算しても構いませんが、ピボットテーブルで計算します。

それにはピボットテーブル分析から集計フィールドを選択します。

列名と数式を設定します。

元データで計算すると率の合計か平均になってしまいますが、

この方法であれば、合計した結果から計算を行ってくれます。

ピボットテーブルの内容を別シートにコピーし、

必要に応じて列名と表示形式を調整します。

どうするのが最適化はケースバイケースですが、

フォントはメイリオ、日付はyyyy/mm/dd、数値はカンマ区切り、

パーセントは小数点位置を指定で統一すると数値が読みやすくなります。

グラフ作成

張り付けたシートからグラフを作成します。

今回の例では「検査実施数/陽性率」、

「陽性者/重傷者/死亡者」のグラフを作成します。

この例ではC列からH列を選択して折れ線グラフを作成します。

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

左の「凡例項目」から検査実施と陽性率以外のチェックを外します。

残した二つだけのグラフになります。

そのままでは数値のスケールが違いすぎて、陽性率が表示されません。

よって軸の調整が必要になります。

それには右クリックして「グラフの種類の変更」を指定します。

「すべてのグラフ」タグから「組み合わせを選択します」

検査実施が集合縦棒になるので折れ線に戻し、

(棒のままや面の方がよい場合もありますが)

陽性率の第2軸にチェックします。

この状態でグラフタイトルをタイトルするとひとまず完成です。

軸の書式設定で「軸の種類」を「テキスト軸」、

「軸位置」を「目盛」にすると読みやすくなります。

(ケースバイケースではありますが)

場合によってはマーカーを付けるのも選択肢です。

完成品のグラフです。

医療関係の専門的は知識はないですが、数字的にはこのような傾向が見えます。

  • 2021年の7月に入り全国の検査数は減っているが、陽性率が高めで上昇傾向
  • 年末年始や連休があると上昇しやすい

次にグラフをコピーして「データの選択」を開き、

凡例項目のチェックを入れ替えます。

グラフの内容が変わりますが、最初の時点では陽性率の軸が残っているのと、

死亡者が小さすぎるため 「グラフの種類の変更」 で調整します。

(重傷者と死亡者を第2軸にし、マーカー付きも普通の折れ線に変更)

更に重さ別に色を変更しています。(陽性者…青、重傷者…黄、死亡者…赤)

重症者と死亡者のスケールが違うことは何らかの明記が必要です。

また重傷者は新規発生者ではなく、その時点での該当者です。

(長く治療すると残り続ける数値で、回復すると減少)

医療関係の専門的は知識はないですが、数字的にはこのような傾向が見えます。

  • 2021年の7月に入り、陽性者も重傷者も増えているが、過去最大ではない
  • これまでは陽性者/重傷者/死亡者は同じ推移をしていたが、2021年の7月は死亡者が急減していて重傷者の上昇も緩い