あるサンプルの実験データを取得するとき、その評価項目が多岐にわたると、実験データが色々なファイルに散逸し管理が大変になることがあります。
電子実験ノートなどがあれば良いのかもしれませんが、そのようなものがない場合には、エクセルで何とかできれば便利です。
すべてのデータを一つの表にまとめてしまうと、表サイズが大きくなりすぎて、入力が面倒など別の問題が発生します。
という事で、できるだけ小さい単位で表(ファイル)を作成し、必要に応じてそれらが結合できれば便利です。
本記事では、こういった複数ファイルに分かれたエクセルファイルを、エクセルの機能（リレーションシップ等）を活用して管理する方法を紹介します。
複数ファイルに分かれた実験データ管理に使用するエクセルの機能
本記事で紹介する手法は、以下のような手順で実験データを管理しています。
- 複数のexcelファイルに分かれて保存されてる実験データを、エクセルを使用してまとめて読込む
- リレーションシップの機能を使用して、ファイル間の結合を行う
- スライサーを使用してデータ抽出
複数のファイルに分かれたデータを結合するためリレーションシップという機能を使用しています。
今回結合するファイル / リレーションシップとは
リレーションシップでは、その表の中でデータを一意に特定する「主キー」と、関連したテーブル間を結ぶために設定する「外部キー」を表中に設定することで、分かれた表を結合することが可能です。
今回は、以下のように処方表、調合表、評価表としてそれぞれ.xlsxファイルに分かれて保存されている表を結合する方法を題材にします。
処方表：サンプルの構成材料比率が記載
調合表：処方表に記載の構成材料比でサンプルを作製した際の調合条件
評価表：出来上がったサンプルの評価結果
処方表中の「処方No」、調合表中の「調合No」、評価表中の「評価No」は、各表中で主キーとして設定されています。
主キーの列中に記載されている番号を指定することで、その表の中で一意にデータ行を識別できます。
調合表中に含まれる「処方No」、評価表中に含まれる「調合No」は、処方表や調合表と結合するための外部キーとなります。
各表間の関連は以下のようになっています。
ファイルの読み込み
上記の処方表、調合表、評価表をまとめた表を作成するために、専用のエクセルファイル「まとめ表.xslx」を作成します。
「データ」→「データの取得」→「ブックから」で、処方表が保存されたエクセルファイルを読込みます。
（複数のシートがある場合は）読込むシートの選択を行い、「データの変換」をクリックします。
以下のようにPower Queryエディターが立ち上がります。
右側のプロパティ 名前で表の名前を設定します。
なお、このエディターでは、列の追加や列のデータ型の変換など、読込んだデータの整形を行うことが可能で、実施した内容が「適用したステップ」に実施順に記録されています。
「閉じて読込む」をクリックすると、まとめ表中に読込んだ表がテーブルとして表示されます。
調合表、評価表についても同様にして読み込みます。
データのマージ
「データ」→「クエリと接続」から以下のサイドバーを表示させます。
サイドバー中の「評価表」をダブルクリックし、Power Query エディターを立ち上げます。
「ホーム」→結合「クエリのマージ」から、表の結合が可能です。
結合したい表や、キーの列を選択し、（以下の列では調合表、調合No）OKを押すことで、表同士のキーの値を元にした結合が実行されます。
Power Query エディター上には、以下のように結合された列が、表示されます。
調合表列の右にある両矢印をクリックし、結合する列を選択します。
処方No、調合時温度、攪拌速度を結合したのが以下の例です。
再度「クエリのマージ」を使用し、以下のように処方表と結合を実施します。
各列を右クリックし、不要な列の削除や、列の配置の変更（ドラッグアンドドロップでも可）を行い、好みの形に整形します。
結合された表の関係は、エクセル「power pivot」→「管理」→ power pivot for excel「ホーム」→「ダイアグラムビュー」で表示可能です。
データの抽出
全てのデータが結合された評価表から必要なデータを抽出するには、フィルターを使用します。
連続値の範囲指定をしたい場合には、各列の▼から数値フィルターを設定します。
サンプル名などの少数のカテゴリでフィルターを行いたい場合には、表を選択した状態で、「テーブルデザイン」→「スライサーの挿入」で以下のようにボタンでスライスができるようなウィジェットが挿入されます。
以上が、複数のファイルに分かれたデータを結合して、まとめる方法になります。
今回は、最初の一歩という事で、それぞれのファイルにはきれいな表が保存されていましたが、測定装置などから出力される神エクセル状態のファイルを読みだして整形することも可能です。
また、データの集計が可能なpower pivotや、可視化に特化したpower BIの活用でより高度なことができるようなります。
実験データの管理ではありませんが、今回紹介したリレーションシップやpower pivotのより詳細な内容を把握するには以下の本が便利でした。
コメント