質問: 学習塾を経営しております。 以下のように、指導している各生徒のテストの進捗状況をGoogle Spreadsheetで管理しております。 > > スプレッドシート名:生徒Aのテスト進捗 > > > > > | 分野 | 回数 | 合否 | > | --- | --- | --- | > | 展開 | 第1回 | 合格 | > | 展開 | 第2回 | 合格 | > | 因数分解 | 第1回 | 合格 | > | 因数分解 | 第2回 | 不合格 | > > > ここに例えば「展開 第3回」という範囲を追加したいです。 しかし他の生徒も同様のテストを受けるので、生徒ごとにこのようなスプレッドシートを作っており、 上記の表をそれぞれコピペして管理しているためテスト範囲の更新作業がかなり面倒です。 そもそものテスト範囲を大元のスプレッドシートにまとめて記しておいて、更新した場合は それが各生徒のスプレッドシートに自動的に反映できれば嬉しいのですがそんなことは可能でしょうか。 大元のファイルを変更するだけで以下のようにできれば幸いです。 > > スプレッドシート名:生徒Aのテスト進捗 > > > > > | 分野 | 回数 | 合否 | > | --- | --- | --- | > | 展開 | 第1回 | 合格 | > | 展開 | 第2回 | 合格 | > | **展開** | **第3回** | | > | 因数分解 | 第1回 | 合格 | > | 因数分解 | 第2回 | 不合格 | > > > どうかお力添えをよろしくお願いいたします。
回答: ぜんぜんベストプラクティスとも思っていませんが(こんだけVLOOKUPとか使ってたら重そう) 関数だけでもこんな風にできるよという一例を...。 (どう書くのが見やすいのか...。) ### マスタシート | 行数 | A列 | B列 | C列 | | --- | --- | --- | --- | | 1 | ID | 分野 | 回数 | | 2 | id001 | 展開 | 第1回 | | 3 | id002 | 展開 | 第2回 | | 4 | id005 | 展開 | 第3回 | | 5 | id003 | 因数分解 | 第1回 | | 6 | id004 | 因数分解 | 第2回 | ★順番は生徒シートに表示したい順序、IDは一意であればでOK ★途中に行の追加ガンガンしていってOK ### 生徒シート(入力値) | 行数 | A列 | B列 | C列 | D列 | | --- | --- | --- | --- | --- | | 1 | =ARRAYFORMULA('マスタシート'!A:C) | | | 合否 | | 2 | | | | =VLOOKUP($A2,$F:$I,4,FALSE) | | 3 | | | | =VLOOKUP($A3,$F:$I,4,FALSE) | | 4 | | | | =VLOOKUP($A4,$F:$I,4,FALSE) | | 5 | | | | =VLOOKUP($A5,$F:$I,4,FALSE) | | 6 | | | | =VLOOKUP($A6,$F:$I,4,FALSE) | | 行数 | F列 | G列 | H列 | I列 | | --- | --- | --- | --- | --- | | 1 | id | 分野 | 回数 | 合否 | | 2 | id001 | =VLOOKUP($F2,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F2,'マスタシート'!$A:$C,3,FALSE) | 合格 | | 3 | id002 | =VLOOKUP($F3,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F3,'マスタシート'!$A:$C,3,FALSE) | 合格 | | 4 | id003 | =VLOOKUP($F4,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F4,'マスタシート'!$A:$C,3,FALSE) | 合格 | | 5 | id004 | =VLOOKUP($F5,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F5,'マスタシート'!$A:$C,3,FALSE) | 不合格 | | 6 | id005 | =VLOOKUP($F6,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F6,'マスタシート'!$A:$C,3,FALSE) | | ★更新するときは 1. 上の行のD~H行をコピペして新しい行へ 2. F列のIDを新しいIDに変更(マスタシートに追加したID) 3. I列に合否を記入 ### 生徒シート(表示値) | 行数 | A列 | B列 | C列 | D列 | E列 | F列 | G列 | H列 | I列 | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | ID | 分野 | 回数 | 合否 | | id | 分野 | 回数 | 合否 | | 2 | id001 | 展開 | 第1回 | 合格 | | id001 | 展開 | 第1回 | 合格 | | 3 | id002 | 展開 | 第2回 | 合格 | | id002 | 展開 | 第2回 | 合格 | | 4 | id005 | 展開 | 第3回 | | | id003 | 因数分解 | 第1回 | 合格 | | 5 | id003 | 因数分解 | 第1回 | 合格 | | id004 | 因数分解 | 第2回 | 不合格 | | 6 | id004 | 因数分解 | 第2回 | 不合格 | | id005 | 展開 | 第3回 | | ★左半分が求めてらっしゃるもので、右半分がテスト実施順の合否入力になります。