質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.01%

テストのタスク表を一元管理したい

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 410

rhyme1992

score 6

学習塾を経営しております。
以下のように、指導している各生徒のテストの進捗状況をGoogle Spreadsheetで管理しております。

スプレッドシート名:生徒Aのテスト進捗

分野 回数 合否
展開 第1回 合格
展開 第2回 合格
因数分解 第1回 合格
因数分解 第2回 不合格

ここに例えば「展開 第3回」という範囲を追加したいです。

しかし他の生徒も同様のテストを受けるので、生徒ごとにこのようなスプレッドシートを作っており、
上記の表をそれぞれコピペして管理しているためテスト範囲の更新作業がかなり面倒です。

そもそものテスト範囲を大元のスプレッドシートにまとめて記しておいて、更新した場合は
それが各生徒のスプレッドシートに自動的に反映できれば嬉しいのですがそんなことは可能でしょうか。

大元のファイルを変更するだけで以下のようにできれば幸いです。

スプレッドシート名:生徒Aのテスト進捗

分野 回数 合否
展開 第1回 合格
展開 第2回 合格
展開 第3回
因数分解 第1回 合格
因数分解 第2回 不合格

どうかお力添えをよろしくお願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • macaron_xxx

    2018/08/17 09:25

    可能です。方法は色々あります。管理方法にもよりますが、idふってARRAYFORMULAとVLOOKUPぐらいを組み合わせるだけでもできそうですね。

    キャンセル

  • rhyme1992

    2018/08/17 10:07

    macaron_xxx様 ご回答ありがとうございます。よろしければその方法を御指南頂けないでしょうか。よろしくお願いします。

    キャンセル

回答 3

+2

回答として望んでないだろうことを承知で、私ならこうするだろうなというのを。

まずシートを次のような構成にします

Examシート

id category range
exp01 展開 第一回
fct01 因数分解 第一回

idを重複がないようにします(入力規則があれば)
細かいことですが、idの数字部分は桁を揃えるとソートがしやすいです

performシート

examid user judge date
exp01 A 不合格 2018-04-01
exp01 A 合格 2018-04-11

examidをリストから選択できるようにします。
性質的に、いずれ合格になる、はほぼ明らか(塾なのでね)なので、合格があっても無意味だと思っています。0回以上の不合格と1回の合格があるのではないかと思いこういう構成
場合によっては、user列ではなく、シートをuser名にしてもいいのかもしれないです。

こういうデータにしておいて、一日一回くらいのトリガでreportシートに全員分をデータを集計して作るか、proceedシートでA1セルで名前(固定リスト)を選択するとリストアップされるシートを作るか、にすると思います。

ただ、gasでこの形のデータを集計するのは面倒なので、Accessを使って集計するようにすると思います(googlesheetの利点はどこからでも見れたり、web連携しやすいことですが、データの性質からいってその利点は利として働くのか疑問でもあります)

こういう集計をするのだとしたらSpreadsheetApp.getActiveSpreadsheet().getSheetByName('').getDataRange().getValues()みたいなもので一旦データを取って、arrayをfilter,sortして、mapかreduceで集計したい形にして、setValues()する、ということになるかと思います。

--
ただただ行を追加すると考えても、次の諸問題を解決しないと自動化することは難しいような印象です。

  1. 自動処理を組んだ時点で、各生徒のシートはデータとして同じなのか
  2. どういうタイミングで行追加するのか(随時?一日一回?何かの切っ掛け?)
  3. 間違って追加したものを消すのはどうするのか
  4. 間に入れないといけないようだが、未知の分野(の第一回)が出てきた場合、どこに挿入するのか

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+2

papinianus様の回答にもありますが諸問題を考慮しないと、
コードの実行タイミングも難しいと思います。

こういう集計をするのだとしたら

…とありましたので、なんとなくこんな感じかなと自分の勉強がてらに書いてみました。

行を追加するのではなくて、更新したいスプレッドシートの値を全部取得して、
テンプレートの値とマージするような処理です。
(罫線とか列サイズとかいろいろとあると思いますが未考慮です。。。)

function teratail_test(){
  Logger.log("--開始--");

  //------テンプレートのスプレッドシートを指定
  var template_ssid = "**********"; //テンプレート用のスプレッドシートID
  var template_sheet = SpreadsheetApp.openById(template_ssid).getActiveSheet(); //テンプレートのスプレッドシートの現在のシートを有効(シートは1枚しかないことを想定)

  var template_table = template_sheet.getRange(1, 1, template_sheet.getLastRow(), template_sheet.getLastColumn()); //シートの左上から最終行、最終列までの範囲を取得
  var template_table_value = template_table.getValues(); //範囲の値を取得
  //Logger.log(template_table_value);
  //------

  //------生徒用のスプレッドシートを指定
  var check_ssid = "**********"; //生徒用のスプレッドシートID
  var check_sheet = SpreadsheetApp.openById(check_ssid).getActiveSheet(); //生徒用のスプレットシートの現在シートを有効(シートは1枚しかないことを想定)

  var check_table = check_sheet.getRange(1, 1, check_sheet.getLastRow(), check_sheet.getLastColumn()); //シートの左上から最終行、最終列までの範囲を取得
  var check_table_value = check_table.getValues(); //範囲の値を取得
  //Logger.log(check_table_value);
  //------


  //------生徒用のスプレッドシートのセルを上書き
  var tocopy = [];
  var t = 0;
  //テンプレートシートで取得した行数分繰り返し
  for(var i = 0; template_table_value.length > i; i++){
    if(t < check_table_value.length){
      if(template_table_value[i][0] !== check_table_value[t][0] || template_table_value[i][1] !== check_table_value[t][1]){
      //テンプレートと生徒の分類、回数が同じじゃない場合
        tocopy.push(template_table_value[i]);
      }else{
      //テンプレートと生徒の分類、回数が同じ場合
        tocopy.push(check_table_value[t]);
        t++;
      }
    }else{
      //テンプレートシートの方が行が多かった場合
      tocopy.push(template_table_value[i]);
    }
  }
  //Logger.log(tocopy);
  var write_value = check_sheet.getRange(1, 1, tocopy.length, check_sheet.getLastColumn()).setValues(tocopy); //生徒用スプレッドシートに書き込み
  //------

  Logger.log("--終了--");
}

書いてから思うのは、自分のコードが柔軟に対応できていないなぁとか思ってしまいますね。。。
テンプレートのセル始まりと生徒さんのセル始まりがずれててもうまく動かないですし。 あぁ…


修正:
・コードにコメントを追加

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/17 11:53

    惑わせましたが「こういう集計をするのだとしたら」は私が書いた方法で報告または集計をするとしたらです。
    質問者さんがやりたい方法の実装として回答を拝見しましたが、分野と回数の出現順が揃っていることなど提示された情報が最大限遵守されると仮定すれば、適切に組まれていると思います(私が質問者だったらBAだと思います)。

    キャンセル

  • 2018/08/17 14:20

    hot-lemoned様、papinianus様
    ご回答ありがとうございます。
    GASの解読に時間がかかるので、取り急ぎお礼のご連絡です。
    ありがとうございました。

    キャンセル

  • 2018/08/17 17:06

    papnianus様
    すいません、私が誤読しておりました…
    ご指摘ありがとうございます。
    またありがたきお言葉をいただけて光栄です。

    rhyme1992様
    コードに少しコメントを入れますのでお手すきの際にご確認ください。
    このコードだと単体スプレッドシートにしか有効されないので、
    ファイル命名で取得したり、フォルダに含まれているなどの条件でお試しください。

    キャンセル

checkベストアンサー

+1

ぜんぜんベストプラクティスとも思っていませんが(こんだけ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回

★左半分が求めてらっしゃるもので、右半分がテスト実施順の合否入力になります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/08/17 16:13 編集

    marcaron_xxx様
    ご回答ありがとうございます。
    idを付与するというのはこういうことなんですね、勉強になりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.01%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる