Excelで介護支援記録管理システムを作ってみる～9日目
多忙だったため、だいぶ間が開いてしまいました。
前回のブログでは、利用者に対する介護支援内容について、特定の日付で時系列順にまとめて日誌に添付するため、「日付」をキーとしてその日付の部分だけを抜き出す日誌シートを作りました。
その上で、このシートに該当する日付のデータのみ表示させる目的で、パワークエリを使い記録テーブルを元にしたテーブル（「クエリテーブル」と呼ぶことにします）を作成し、それに「日付フラグ」を追加することを試みました。
しかし、残念ながらそれはできない結果となりました。テーブルの外にあるセルの値を参照するという編集が無理だったからです。
仕方ないので、取り敢えず新たな検索用データシート上に、パワークエリを使い、単に記録テーブルとリンクするだけの「検索用テーブル」を作るところまで進めたのが前回でした。
そこで今回は、Power Query エディターは使わず、思い切って直接、検索用テーブルを加工し「日付フラグ」の追加を試みることにしました。
しかし、これには問題があります。
パワークエリで作成したクエリテーブルは、加工元のテーブルでデータの追加や修正を行った後、更新を行えば加工元と同じデータに書き換えられます。
この機能は非常に忠実で、例えばクエリテーブルを見やすくするため、一時的に一部の項目（列）を削除しても、更新を行うとその項目が再び復活して表示されます。これが嫌ならPower Query エディターでクエリテーブルを再編集するしかありません。
これと同様に、クエリテーブルにPower Query エディターは使わずに列を追加できたとしても、更新をかければ折角作った列が消えてしまうのではないか、という心配です。
もしそうなら徒労に終わってしまいますが、ものは試しということでやってみました。
テーブルを普通に加工するやり方で、検索用テーブルの最右列のその隣の列、一番上の行に"CK1"と表題を入力すると、自動的に列が追加できます。ちなみに"CK"は"check"の省略形です。
その下の行のセルには、下図のとおり、日誌シートの日付を入力するセル（$C$2）の値を参照し、検索用テーブルの日付がそれに一致するかどうか判定する数式として"=IF([@日付]=日誌!$C$2,1,0)"を入力します。
すると他のデータ行にも、すべて同じ数式が自動的に入力されました。
なお"[@日付]"とは構造化参照で「同一行にある日付列の値」を指します。
日誌シートの日付を入力した"C2"セルには"2024/5/10"を入力しておいたので、目論見どおり、該当する上位3つのデータの「CK1」に"1"が、その下の行にはすべて"0"と表示されました。
ここまではうまく行ったので、日誌シートに戻り、加工した検索用テーブルから「CK1」が"1"のデータを引っ張ってきて、それらを順に表示させることにします。
テーブルから該当データを抽出する数式として、Microsoft365やExcel2021ではFILTER関数を使うことができますが、私のPCはExcel2019のため、以前の投稿でご紹介した配列数式を使うことにしました。
まず表示部の1行目、「利用者名」の列にINDEX関数とSMALL関数を組み合わせ、テーブルから該当するデータを時系列順で取り出す数式を、エラーを表示させないIFERROR関数でくるんで入力します。
配列数式となるようCtrlキーとShiftキーを押しながらEnterキーを押して入力すると、該当の利用者名が表示されました。
同様に、時刻、実施内容、実施記録、担当者の各列についても検索用テーブルの「CK1」が"1"であるデータの当該項目が表示されるよう、配列数式を入力していきます。
１行目に入力後、すべて正しく表示されていることを確認したら、オートフィルを使って下の全ての行にコピーします。すると下図のとおり、指定した"2024/5/10"の記録が抽出され表示されました。
ただし「実施内容」の列をよく見ると、セルの書式設定で「折り返して全体を表示する」にしているにもかかわらず、正しく表示されないため、範囲を設定してホームメニューの中の「書式」→「行の高さの自動調整」をクリックします。すると以下のようになりました。
他の日付に変えても正しく表示されるかどうか確認するため、日付を"2024/5/11"に変えてみます。するとシート中の記録の表示が下図のとおりに変わりました。
しかし、実施記録の行の高さが変化しません。
これらは、数式を使って情報を表示させた場合に起きるExcelの欠点とも言える現象と思いますが、文句を言っても仕方ないので、先ほどと同様に実施記録の列を選んだあと、ホームメニューの中の「書式」→「行の高さの自動調整」をクリックしておきましよう。
すると途中で途切れていた記録も全体が表示され、下図のとおりすべての行が適正な高さになりました。
こうして、日誌シートの左上にあるセル（$C$2）に目的の日付を入力するだけで、直ちにその結果が表示されるようになりました。単純明快です。
さて、先に問題点として心配していた点はどうだったでしょうか。
元となる記録テーブルに、毎日新しいデータを追加していき、その都度日誌を作成する場合には、クエリテーブルである検索用テーブルを必ず更新してから行う必要があります。
恐る恐る、下図のとおり記録テーブルに新しいデータを追加入力します。
その上で、検索用テーブルを更新してみます。
すると、下図のとおり、検索用テーブルの外観に全く変化は生ぜず、データが正しく追加された形となりました。
こうしてExcelのパワークエリで作成したテーブルを直接加工してもうまくいくことが確認できました。これは大きな収穫です。
日付だけで検索する日誌シートはうまくいきましたので、次回以降は複数の検索条件を組み合わせて抽出する動静記録シートを作成することにします。
>次の投稿を読む
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
