Excelで介護支援記録管理システムを作ってみる～4日目
前回の続きです。
介護支援記録管理システムの基礎を構成する利用者情報を、データの入力規則を使って記録テーブルに効率良く入力できるようにしたいとき、問題となるのは、元となる利用者テーブルの中に、既に利用を終了された方が含まれることでした。
この方たちがリストに表示されないよう、関数を使ってサービス利用中の方だけを効率よく抜き出す方法として、前回の投稿ではINDEXとMATCH関数を組み合わせた方法をご紹介しました。
しかし、この方法はテーブルと関数の中間に、データを数値化し加工するフィールド（作業列）を必要とすることが問題でした。
そこで、作業列を必要とせず、直接テーブルから必要なレコードだけ抜き出し、しかも上に詰めて表示させる方法がないかどうかWeb上で検索してみると、私では思い付かない方法がありました。
それは配列数式を使う方法です。
配列数式とは配列内の 1 つ以上の項目に対して複数の計算を実行できる数式を言うのですが、通常見慣れた関数を使いながらも、通常の書式とは違う形で計算処理させることもあるため、一見すると頭が混乱しそうになります。
では、混乱を避けるため考え方から具体的に説明します。
前回同様、施設やデイサービスの利用者の中から、行事等の参加者を抜き出すような場合を考えてみます。
下図のとおり、50音順に縦に並んだ利用者名の名簿があり、その右に行事に参加したかどうか○印を付ける欄があります。
関数による段階的処理として、まず作業列①では○印を付けた利用者について一番上の方からの位置（相対的行番号）をIF 関数を使って計算しています。
次の作業列②では、上から下までSMALL 関数を使い、番号数字の中から一番小さい数、二番目に小さい数、三番目に…と言う具合に計算をしています。
そして最終列でINDEX 関数を使い、左の相対的行番号から利用者名を取り出し、順に上から詰めた形で表示させているわけです。
このままの方法だと前回と同様、作業列が必要となってしまうため、配列数式を使って作業列部分を取り込んだ計算をさせてみました。
それが下の図です。
ポイントはSMALL 関数がIF 関数による判断処理を取り込み、配列としての計算結果を導き出していることです。
ご覧のとおりIF 関数が通常とは違って、条件つまり◯印が付いてるかどうかの前提に対し、付いていない場合の処理がありません。配列を作るのに必要がないためで、もし付いているものがなければエラーとして表現されます。
この数式を入力後、CtrlキーとShiftキーを押しながらEnterキーを押すことで、配列数式として確定させます。
配列数式は式全体が{ }で囲まれて表示されます。
このようにして参加した利用者のみ上に詰めた形でリスト化できますので、あとはエラーをIFERROR関数で消せばきれいになります。
ちなみに前回の方法で使用した関数の組み合わせでは配列数式を作ることができませんでした。SMALL関数を使用したが故にできたと思われます。
なお、Microsoft 365やExcel 2021では、必要なデータだけを抜き取る機能を持ったFILTER関数を使うことができるようで、至極簡単そうです。
残念ながら私のPCはExcel 2019のため検証しませんでしたのであしからず。
さて、関数を使って疑似的にクエリを作成することは一応できましたが、２回の投稿で試した方法は、テーブルとは別にワークシート上に作業列を必要とするために少し野暮ったかったり、作業列を必要としない方法では、配列数式という少し難しい関数を使うため、私も知らないくらい、かなりマニアックでした。
ところで、少し前の「Excelでデータ入力時に他のテーブルの情報を使う方法」という投稿では、「ExcelではAccessのクエリ機能のように・・・クエリを簡単に作成することができません。」と書いてしまいましたが、実はExcelに詳しい方ならご存じのとおり、少し高度な方法として、Excelにもテーブルを編集してクエリを作成する機能がありました。
それがパワークエリ（Power Query）と呼ばれる機能です。
Excel 2010で拡張機能として出発し、Excel 2016 からは「データの取得と変換」として標準機能に組み込まれています。
少しとっつきにくいため、巷ではあまりこの機能は使われていないように見受けられます。それは多分、Excelでは直接的なデータの編集や加工がしやすいせいかと思われますが、データベースとしてのテーブルを駆使した仕事をしようとするなら、覚えておくと非常に便利な気がします。
回り道しましたが、次回ではこの方法を試すことにします。
>次回の投稿を読む
