新型コロナウイルス「第4波」が本格化しています。
関西を中心にB.1.1.7変異株が猛威を振るっています。大阪では病床が満杯となり、きわめて深刻な事態となっています。
当サイトではこれまで、東京・埼玉を中心に、グラフを自動化するExcelマクロを公開してきましたが、今回、全都道府県に対応したグラフ描画マクロにリニューアルし、公開することにしました。
記事後半には、従来の記事をそのままつなげます。
都道府県ごとの感染状況・病床状況を自動でグラフにするExcelマクロ
さっそくですがダウンロードはこちらからです。
Microsoft365環境で作成しております。互換性には気を使ったつもりではありますが、さまざまな環境での動作確認は残念ながらできていません。
「クエリ」を使ったデータインポートを使用しています。Excel2013以前では確実に動作しません。(Power Query拡張機能を入れれば動くかもしれません)
データは「東洋経済オンライン」のデータを使用しています。
※東洋経済のデータは、時々抜けがあったり、大阪府の計上漏れの反映が遅れたりします。
現在、大阪府の計上漏れと、福岡県の4/30のデータの漏れを修正するコードを組み込んだ状態にしています。
【2021/05/08 UPDATE】大幅にアップデートした現行版に変更しました。
ブック内に説明を書いていますが、ざっとここに採録します。
2種類のグラフが描画できます。
ひとつは、病床・重症病床の状況がわかるグラフ。
東洋経済のデータが、自宅療養+調整中+入院を「Hospitalized」と合算しているため、縦軸100%に確保病床をとるとこんな感じになってしまいますが、「自宅療養の方も本来病床に入れるべき」という意味では、病床を大幅に上回る患者が出ていることは見える感じです。
黄色の重症病床の埋まり具合ははっきりわかります。
もうひとつは、「週変化率・2週変化率」の入った新規感染者数グラフです。
7日変化率=当日直前7日間の平均/一週間前の日の直前7日間の平均
14日変化率=当日直前14日間の平均/ 2週間前の日の直前14日間の平均
と計算しています。１より大きければ感染拡大、小さければ感染収束となります。
実効再生産数と似ていますが、「一人が何人…」といった概念ではなく、一週間でどれだけ感染者が増えたか減ったかを表しています。東洋経済の簡易な実効再生産数は、これを「世代時間5日」に変換してるだけなので、こっちのほうが分かりやすいと判断しました。
また、変化率は右軸なのですが、目盛りを対数にしました。
2021/5/8 もう一つグラフを追加しました。
陽性率は、統計が汚い県は異常に高かったりしますが、ひとまずこのルールで計算したものを出してみました。
使い方
起動すると、最初に次のシートが開きます。
東洋経済のデータは、最大で2日遅れるため、ここに2日間程度の最新データを追記します。
「新規感染」のらんまで埋まっていれば、残りは空欄でかまいません。全部入れると全部反映することもできます。
逆に、「新規感染」を空欄にすると、反映されませんので、当日の感染者数が出る前に、都道府県・日付まであらかじめ入力してお待ちいただいても、グラフには影響しません。
「地方」を選ぶと、「都道府県」にはその地方の都道府県がリストで選べます。
確保病床数・重症病床数は、「確保病床」シートにまとめて設定して、それを関数で引いてきています。変更する場合は「確保病床」シートを変更します。
この数字は、病床状況の縦軸スケールに反映します。あふれている県のグラフの全体像を見る時は、グラフの方で手動で縦軸を右クリックして変更してください。
「グラフ出力」ボタンを押すと、左側の「グラフ描画対象」で指定した都道府県のグラフのみが描画されます。
「連続出力」ボタンを押すと、下のデータ記入欄に入れてある都道府県をすべて連続で出力します。新規ブックが自動で作成され、そこに画像として貼り付けられます。
この新規シートは、出力完了後、並べ替えができるように配置しています。
「並べ替え」ボタンを使って、先頭行を見出しとして使用し、任意の順番で並べ替えできます。地方名、都道府県名のほか、7日変化率、14日変化率、陽性率での並べ替えが可能です。
以上がこの最新版の解説です。
以下は、第3波までに公開していた記事の採録です。記述はすべて当時のままです。
都県ごとの作成し、最新版にない特徴をもったグラフも描いています。
【2021/04/08 UPDATE】東京都の病床を描くブックをリニューアルしました。東京都の確保病床を軸の最大値にしたほか、週変化率・2週変化率がわかるグラフを追加しました。
12月24日発表分から、神奈川県で感染者が急増。その後、年末に東京都で1000人を超え、年明け1月7日発表分が2447人という恐るべき数字となりました。
一都三県は感染経路調査を縮小。発表される感染者数は減ってきていますが、実態が見えないのではないかといううっすらとした危惧はあります。埼玉県では変異株のクラスターが発生してしまいました。
日々の発表数には曜日によってばらつきがあり、大きな傾向がつかみにくいところがあります。
東京都・神奈川県のデータは常時csv形式で公開されており、Excelへの読み込みが容易です。埼玉県も、ちょっと取り込みにくい形式ですが日々のデータがcsvで公開されています。
これを利用して、「7日間平均」のグラフを自動的に作成するマクロをExcelで作りました。本文からダウンロードしていただけます。
新規感染者数を「7日間平均」で把握する・病床の状況を可視化する
自治体が発表する新規感染者数は、曜日によって大きく増減するという特徴があります。平日・休日の人流の違い、曜日ごとに検査体制が異なることの影響だと考えられますが、
このため、日々のデータを追うだけでは、感染の大きな傾向をつかみにくいのです。
そこで、東京都の公式サイトでも、7日間平均のグラフが表示されるようになっています。(神奈川県は出ていません)
東京都のグラフも、サイトでは小さくて見づらいところがあります。
そこで、せっかくデータが公開されているのだから、手元のExcelで手軽に作ってしまおう、ということです。
【2021/1/31 UPDATE】埼玉県の感染者数・7日間移動平均を自動でグラフ化できるファイルを追加しました。
システム要件
動作には、次のバージョンのMicrosoft Excelが必要です。
■Office 365
■Excel 2019
■Excel 2016のうち、「新しいクエリ」ボタンが存在するもの
※それ以前のバージョンでPowerQueryをインストールして動作するかは未確認です。
※上記バージョンにおいて「クエリが新しいバージョンで作成されたため更新できない」エラーが出るマシンが確認されています。状況確認中ですが、Officeの更新をおためしください。
東京都の新規感染者数+7日間移動平均グラフ
単純に、公表感染者数と7日間平均だけを描くブックです。
当初はこれ一本のみを公開していました。第三波の拡大をうけて、続きのブックを追加しました。
東京都の新規感染者数+7日間移動平均+入院数・重症者数・死亡数グラフ
日々の感染者数だけでは状況の深刻度を誤認するおそれがあるため、病床の状況と死亡者数を追加しました。重症者・死亡者を第2軸にして強調しています。
東京都オープンデータに無いデータについては、入院数・重症者数は東京都のGitHubから、死者数は東京都HPから取得します。中のクエリにURLが直接入っています。
【2021/04/08 UPDATE】リニューアルしました。東京都の確保病床を軸の最大値にとり、満床近くなったら分かるようにしました。また、週変化率がわかるグラフを追加しました。これにともない、毎回グラフを新規描画するのではなく、もともとあるグラフのデータ範囲を変更するようにしましたので、グラフの書式を変更していただくと、以後その書式のグラフが得られます。
神奈川県の新規感染者数+7日間移動平均グラフ
※2021/01/13 東京都のファイルに手を加えて、新規に作成しました。
埼玉県のOpenDataと公表数の差+7日間移動平均グラフ ※Chrome必須
【2021.3.7 UPDATE】グラフのデザインを変更しました。
埼玉県の新型コロナ感染者OpenDataで最初グラフを書いていたのですが、どうも実態と合わないため検証したところ、日々報道で公表されている新規感染者数と、OpenDataから集計した新規感染者数に、大幅な差異があることが判明しました。
このため、両方のデータから差を出してグラフ化するブックを作成することとしたものです。しばらく手動で描いていましたが、2/17ようやく自動化しました。
下のようなグラフになります。
埼玉県のOpenData+7日間移動平均グラフ ※Chrome必須
※注 こちらはOpenDataのみを使用する旧バージョンです。日々の公表数とは大幅な差があります。ご注意ください。今となっては、VBAの参考コードとして見ていただく程度の価値しかありません。
埼玉県の場合は、毎日違うURLがどんどんどんどんどんどん追加されている形式のオープンデータなので、取り込みに大変苦労して今頃になりました。
しかも、URLを直接Power Queryに放り込むと、気まぐれにプロトコルエラーが出ます。なので、面倒でも一度ダウンロードしてローカルに置いてからPower Queryに入れる必要がありました。
つまり、ちょっと面倒なWebスクレイピングをしないといけないのです。
VBAの標準機能で、Internet Explorerを駆動できるのですが、Microsoftも非推奨の勧告まで出してるので今さらIEを使うのもはばかられます。そこで、Selenium Basicというモジュールを導入して、Google Chromeを使ってスクレイピングしました。
【埼玉県】新型コロナウイルス感染症の発生状況 | Open Data Saitama をスクレイピングして、一番下のダウンロードリンクをつきとめ、それをダウンロードフォルダに saitama-covid-opendata.csv というファイル名でダウンロード。そのファイルを既存のクエリに読ませてグラフ化するという手順を自動化しています。ですので、一度Google Chromeが起動して、勝手に閉じます。
Selenium Basicの導入手順は、ブック内に詳しく記載しています。
使い方
■ブックを開くと、「手動追加データ」シートがアクティブになります。
このシートに設置したボタンを押すと、次の通り動作します。
■既存のグラフを削除
■各都県のサイトから最新のcsvデータを読み込み(PowerQuery)
■最新1日分のデータはまだcsvに反映されてないことがあります。そのときはボタン左側の位置に、すでに入れてある例のように追加します。
※都県のデータと重複している日付は、都県のデータが自動的に優先されます
■埼玉県の最新ファイルでは、手入力のデータを公表数のデータに自動追加します。
■以上のデータをマクロが表にまとめ、グラフを描画します。
■グラフをコピーして「貼り付け用グラフ」シートに画像として貼り付けます
■それを自動的にコピーしてから終了するので、SNSなどにすぐに貼り付けることが出来ます。
■ボタンを押す前に、ボタン下の「グラフ開始日」に日付を入れておくと、その日以降のデータのみを使用したグラフを描くことができます。
コメント