前置き
よくある要望だと思うんですよ。たとえバッドノウハウだとしても。
本題
たとえばこんな感じのデータがあったとします。宿題の提出期限を示した表なのですが。
表:source
| date | subject |
|---|---|
| 2017/3/2 | 数学 |
| 2017/3/2 | 社会 |
| 2017/3/3 | 英語 |
| 2017/3/4 | 国語 |
| 2017/3/5 | 理科 |
| 2017/3/5 | 英語 |
データ構造としてはこれで正しいんですが、世間一般の人が読むには直感的ではないです。次の表のようにスペースやカンマでつないで一つのセルにまとめてしまうのが一般的ではないでしょうか
表:work
| date | subjects |
|---|---|
| 2017-3-2 | 数学 社会 |
| 2017-3-3 | 英語 |
| 2017-3-4 | 国語 |
| 2017-3-5 | 理科 英語 |
表計算ソフトは帳票ツールじゃないってこと百も承知の上で。
実装
dateが任意の年月日の行を抽出するにはQUERYを使います。
QUERY(データ, クエリ, [見出し])
=QUERY(A:B, "SELECT *")
=QUERY(A:B, "SELECT * WHERE B = '英語'")
検索キーが日付の場合、注意が必要です。dateを修飾することで日付フォーマットで記述された年月日の文字列を日付型に変換して抽出してくれます。
work!cell.b2
=QUERY(source!$A$2:$B, "SELECT B WHERE A = date'2017-3-5'")
今回は任意の年月日を条件とするので、セルを参照しなければなりません。クエリでセルを参照するには次のようにします。
work!cell.b2
=QUERY(source!$A$2:$B, "SELECT B WHERE A = '"&a2&"'")
単純に文字列を&で結合すればいいんですね。
ただし、検索キーは日付型なので、このやり方では上手く行きません。ユーザからは伺い知ることができませんが、セルの日付データは数値で管理されています。日付を日付として扱うには、次のように変換してやります。
work!cell.b2
=QUERY(source!$A$2:$B, "SELECT B WHERE A = date '"&TEXT(A2,"yyyy-mm-dd")&"'")
もう一つ注意点として、年月日の区切りはハイフンを使います。日本人にはおなじみのスラッシュ区切り(例:2017/03/02)では動きません。
最後にクエリーの結果を結合して、一つのセルに収まるようにします。
work!cell.b2
=join(" ",QUERY(source!$A$2:$B, "SELECT B WHERE A = date '"&TEXT(A2,"yyyy-mm-dd")&"'"))
クエリの結果が空の場合、エラーとなるので対処しましょう。
work!cell.c2
=IFERROR(B2, "なし")
結論
workシートには余分な情報が含まれますので、印刷する時はviewシートを使います。この表をGoogleDocに転送すれば、ちゃんとした印刷物になります。
期待どおりの表ができましたね。