Photo by
96nekotaro
【超簡単】遺伝的アルゴリズムを使ってリレーのチームを作成しよう！（GAS)
■改良バージョンを作りました！詳しくは見出しの『※改良バージョン（2022.7.1）』をご覧ください。
今回は、AIアルゴリズム第２弾ということで、体育の授業などでタイムを計測することがあると思いますが、それらのタイムデータを使って良い感じに（チーム間のタイム差をできるだけ無くす）チーム分けするスプレッドシートを紹介します。簡単なので是非試してみてください！
１ 導入方法（２ステップ）
（１）スプレッドシートをコピーする
（２）コピーしたスプレッドシートの「マスタ」シートへ次の①～③の手順で操作する。
①「氏名」「タイム」を入力する
・「タイム」は半角数字で入力してください。
・エクセルで記録しているタイムデータをコピペするときは「値のみ貼り付け」でコピーしてください。
②「チーム数」を入力
・入力欄右のセルが「チーム作成可能」と表示される値を入力してください。
③「gasを実行する」→「遺伝的アルゴリズムでチーム作成」を実行する
・初回は承認を求めらます。承認したら再度実行してください。
作成結果は「メイン」シートへ表示されます。背景がピンクの表が作成チームです。
２ GASのソースコード
自治体のセキュリティ制限などでスプレッドシートのコピーができない場合はGASを直接コピペして作成してください。スクリプトエディタは「拡張機能」→「Apps Script」で開けます。
※コピペした後はスプレッドシートを開きなおしてください
// 関数を実行するメニューを追加
function onOpen() {
let ui = SpreadsheetApp.getUi();
let menu = ui.createMenu('gasで実行する');
menu.addItem('遺伝的アルゴリズムでチーム作成', 'gaToCreateTeams');
menu.addToUi();
}
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();// 現在開いているスプレッドシートを取得
var mainSheet = spreadsheet.getSheets()[0];//メインシートを取得
var dataSheet = spreadsheet.getSheets()[1];//マスタシートを取得
function masterSheetClear(){
let ui = SpreadsheetApp.getUi();
//間違えてボタンを押した場合、キャンセルをクリックすると動作を終了できる。
let confirmation = ui.alert("「氏名」「タイム」を初期化", "「氏名」「タイム」を初期化します。本当によろしいですか？", ui.ButtonSet.OK_CANCEL);
if(confirmation == "CANCEL") {
ui.alert("操作をキャンセルしました");
return;
}
let last_row = dataSheet.getRange(dataSheet.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); //タイム列最終行を取得
if(last_row == 1){
ui.alert("データが一件もありません");
dataSheet.getRange(2,2).activate();
return;
}
dataSheet.getRange(2,2,last_row-1,2).clearContent();
}
//初代
function first_gene(x, y, f){
let z = x * y; //人数
let g = []; //初代
for(let i=1;i<=f; i++){
for(let k=1; k<=z; k++){
let h = [];
while(h.length < z){
let n = Math.floor(Math.random()*z);
if(h.indexOf(n) == -1){
h.push(n);
}
}
g.push(h);
}
}
//Logger.log(g);
return g;
}
//評価関数
function evaluation_function(x,parent,dic){
let pare = []
let sum_time = 0;
const y = dataSheet.getRange(2,6).getValue(); //列
for(let key in dic){
sum_time += dic[key];
}
const ide = sum_time / x //理想点
//Logger.log(ide);
//Logger.log(parent);
for(let v of parent){
let score = 0;
let cnt = 0;
let sum_v = 0;
//Logger.log(v);
for(let i of v){
sum_v += dic[i+1];
//Logger.log(i+1);
//Logger.log(dic[i+1]);
cnt += 1;
if(cnt == y){
//Logger.log(sum_v);
score += (ide - sum_v) ** 2;
//Logger.log(score);
cnt = 0;
sum_v = 0;
}
}
//Logger.log(score);
pare.push([score,v]);
}
//Logger.log(pare);
//pare = sorted(np.array(pare), key=lambda x: x[0]) //点数で並び替え
//点数で並び替え
pare.sort((a, b) => {return a[0] - b[0];} );
//Logger.log(pare);
return pare;
}
//一様交叉
function crossover(ep,sd,p1,p2){
//let ch1 = copyMatrix(p1);
let ch1 = p1.slice();
//let ch2 = copyMatrix(p2);
let ch2 = p2.slice();
//Logger.log(ch1);
//Logger.log(ch2);
let ch = [];
for(let k=0; k < ch1.length; k++){
if(ep > Math.random()){
var x = "ok";
}else{
x = "no";
}
if(x == "ok"){
let c1 = ch1[k];
let c2 = ch2[k];
ch1[ch1.indexOf(c2)] = c1;
ch2[ch2.indexOf(c1)] = c2;
ch1[k] = c2;
ch2[k] = c1;
}
}
ch.push(ch1);
ch.push(ch2);
//Logger.log(ch);
return ch;
}
function gaToCreateTeams(){
if(typeof dataSheet.getRange(1,6).getValue() == 'string'){
dataSheet.getRange(1,6).setValue(hankaku(dataSheet.getRange(1,6).getValue()));
}
let ui = SpreadsheetApp.getUi();
if(dataSheet.getRange(1,7).getValue() != "チーム作成可能"){
ui.alert("「マスタ」シートにデータが一件もないか、もしくは「チーム数」に適正な数値（半角数字）が設定されていません。");
dataSheet.getRange(1,6).activate();
return;
}
let dic = {}; //連想配列
let last_row = dataSheet.getRange(dataSheet.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); //タイム列最終行を取得
//連想配列にキーと値をセット
for(let d=2; d <= last_row; d++){
dic[dataSheet.getRange(d,1).getValue()] = dataSheet.getRange(d,3).getValue();
}
const x = dataSheet.getRange(1,6).getValue(); //行
const y = dataSheet.getRange(2,6).getValue(); //列
let elite_length = 150; //上位個体数
if(dataSheet.getRange(3,6).getValue() > 100){
elite_length = 200; //上位個体数
}
const gene_length = 20; //世代数
const ep = 0.1; //一様交叉確率
const sd = 0.00 //突然変異確率
const f = 1000 //初代の数
let first = first_gene(x,y,f) //初代
//Logger.log(first);
let parent = evaluation_function(x,first,dic) //初代評価
//Logger.log(parent);
let tops = []
for(let i=0; i < gene_length; i++){
//上位個体を選別
parent = parent.slice(0,elite_length);
if(i == 0 || top[0] > parent[0][0]){ //最高得点の更新
top = parent[0];
}else{
parent.pop();
parent.push(top);
}
tops.push(top[0])
//Logger.log('第' + (i+1) + '世代'); //各世代
//Logger.log(top[0]); //各世代の最高得点を表示
//Logger.log(top[1]);
let children = [] //子世代
//遺伝子操作
parent.forEach( function( v1, k1 ) {
parent.forEach( function( v2, k2 ) {
if(k1 < k2){
let ch = crossover(ep, sd, v1[1], v2[1]); //一様交叉
//Logger.log(ch);
children.push(ch[0]) //子孫1を格納
children.push(ch[1]) //子孫2を格納
}
});
});
//Logger.log(children);
children = evaluation_function(x,children,dic) //評価
//Logger.log(children);
//子を親にコピー
parent = copyMatrix(children);
//Logger.log(parent);
}
//最強個体保存
let team = [];
let opt = [];
let s=0;
let e=y;
for(let t=0; t<top[1].length; t++){
team[t]=top[1][t]+1;
}
//Logger.log(team);
for(let r=0; r<x ; r++){
opt.push(team.slice(s, e));
s += y;
e += y;
}
//最強チーム編成をスプレッドシートへ
mainSheet.clear();
mainSheet.getRange(4,2,x,y).setValues(opt);
//行・列の表示名
let rowName = [];
let colName1D = [];
let colName2D = [];
for(let row=1; row<=x; row++){
rowName[row-1] = ["チーム" + row];
}
for(let col=1; col<=y; col++){
colName1D.push("第" + col + "走者")
}
colName2D = [colName1D]; //１次元配列→２次元配列へ
//行・列名を３つの表へ
mainSheet.getRange(4,1,x,1).setValues(rowName);
mainSheet.getRange(3,2,1,y).setValues(colName2D);
mainSheet.getRange(3,1,x+1,y+1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(3,1,x+1,y+1).setHorizontalAlignment('center');
mainSheet.getRange(3,1,x+1,y+1).setNumberFormat('0');
mainSheet.setColumnWidths(2, y+2, 120);
//タイム表
mainSheet.getRange(7+x,1,x,1).setValues(rowName);
mainSheet.getRange(6+x,2,1,y).setValues(colName2D);
mainSheet.getRange(6+x,1,x+1,y+3).setBorder(true, true, true, true, true, true);
mainSheet.getRange(6+x,1,x+1,y+3).setHorizontalAlignment('center');
mainSheet.getRange(6+x,1,x+1,y+3).setNumberFormat('#,##0.00');
//完成チーム
mainSheet.getRange(10+x+x,1,x,1).setValues(rowName);
mainSheet.getRange(9+x+x,2,1,y).setValues(colName2D);
mainSheet.getRange(9+x+x,1,x+1,y+1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(9+x+x,1,x+1,y+1).setHorizontalAlignment('center');
mainSheet.getRange(9+x+x,1,x+1,y+1).setBackground('#FFD5EC');
mainSheet.getRange(10+x+x,2,x+1,y).setFontWeight('bold');
//
let masterID_2D = dataSheet.getRange(2,1,last_row-1,1).getValues();
let masterID = masterID_2D.flat(); //indexOfを使用するため１次元へ変換
let masterName = dataSheet.getRange(2,2,last_row-1,1).getValues();
let masterTime = dataSheet.getRange(2,3,last_row-1,1).getValues();
//タイム表
let timetable = mainSheet.getRange(7+x,2,x,y+2).getValues();
let idealTime = Number(dataSheet.getRange(3,6).getValue());
let timediffSum = 0; //チーム別タイム合計の理想タイムからの誤差合計
for(row=0; row<x; row++){
let timesum = 0;
for(col=0; col<y+2; col++){
if(col < y){
timetable[row][col] = masterTime[masterID.indexOf(opt[row][col])];
timesum += Number(masterTime[masterID.indexOf(opt[row][col])]);
}
if(col==y){ //チームごとのタイム計
timetable[row][col] = timesum;
}
if(col==y+1){ //理想タイムとの誤差
timetable[row][col] = (idealTime-timesum)**2;
timediffSum += (idealTime-timesum)**2;
}
}
}
mainSheet.getRange(7+x,2,x,y+2).setValues(timetable);
mainSheet.getRange(6+x,1).setValue("タイム表");
mainSheet.getRange(6+x,2+y).setValue("チームタイム");
mainSheet.getRange(5+x,3+y).setValue("※誤差は理想タイムとチームタイムの差の２乗");
mainSheet.getRange(6+x,3+y).setValue("誤差");
mainSheet.getRange(7+x,3+y,x+1,1).setNumberFormat('#,##0.000000');
mainSheet.getRange(6+x,5+y).setValue("理想タイム");
mainSheet.getRange(7+x,5+y).setValue(idealTime);
mainSheet.getRange(7+x,5+y).setNumberFormat('#,##0.000000');
mainSheet.getRange(6+x,5+y,2,1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(6+x,5+y,2,1).setHorizontalAlignment('center');
mainSheet.setColumnWidths(5+y, 1, 120);
mainSheet.getRange(7+x+x,4+y).setValue("←誤差合計");
mainSheet.getRange(7+x+x,3+y).setValue(timediffSum);
//mainSheet.getRange(7+x+x,3+y).setNumberFormat('#,##0.00');
mainSheet.getRange(7+x+x,3+y).setBorder(true, true, true, true, true, true);
mainSheet.getRange(7+x+x,3+y).setHorizontalAlignment('center');
//完成チーム部分
mainSheet.getRange(9+x+x,1).setValue("完成チーム");
let comp = mainSheet.getRange(10+x+x,2,x,y).getValues();
for(row=0; row<x; row++){
for(col=0; col<y; col++){
comp[row][col] = masterName[masterID.indexOf(opt[row][col])];
}
}
mainSheet.getRange(10+x+x,2,x,y).setValues(comp);
mainSheet.getRange(5+x,1).activate();
}
//2次元配列コピーメソッド
function copyMatrix(arr) {
const result = [];
for (const line of arr) {
result.push([...line]);
}
return result;
}
/**
* 全角から半角への変革関数
* 入力値の英数記号を半角変換して返却
* [引数] strVal: 入力値
* [返却値] String(): 半角変換された文字列
*/
function hankaku(strVal){
if(typeof strVal == 'string'){
// 半角変換
var halfVal = strVal.replace(/[！-～]/g,
function( tmpStr ) {
// 文字コードをシフト
return String.fromCharCode( tmpStr.charCodeAt(0) - 0xFEE0 );
}
);
// 文字コードシフトで対応できない文字の変換
return halfVal.replace(/”/g, "\"")
.replace(/’/g, "'")
.replace(/‘/g, "`")
.replace(/￥/g, "\\")
.replace(/ /g, " ")
.replace(/〜/g, "~");
}
}※改良バージョン（2022.7.1）
「性別」「別チームへ」の２つの項目を追加しました。
・「性別」項目を設定すると、できるだけ男女均等になるようチーム分けをします。
・「別チームへ」項目を設定すると、〇選択した人をできるだけ別チームへ分散化します。
※「性別」「別チームへ」ともに未設定（空欄）の場合、旧バージョンと同じ動作になります。
改良バージョンのスプレッドシートをコピーする
改良バージョンのソースコード
// 関数を実行するメニューを追加
function onOpen() {
let ui = SpreadsheetApp.getUi();
let menu = ui.createMenu('gasで実行する');
menu.addItem('遺伝的アルゴリズムでチーム作成', 'gaToCreateTeams');
menu.addToUi();
}
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();// 現在開いているスプレッドシートを取得
var mainSheet = spreadsheet.getSheets()[0];//メインシートを取得
var dataSheet = spreadsheet.getSheets()[1];//マスタシートを取得
function masterSheetClear(){
let ui = SpreadsheetApp.getUi();
//間違えてボタンを押した場合、キャンセルをクリックすると動作を終了できる。
let confirmation = ui.alert("「氏名」「タイム」「性別」「別チームへ」を初期化", "初期化します。本当によろしいですか？", ui.ButtonSet.OK_CANCEL);
if(confirmation == "CANCEL") {
ui.alert("操作をキャンセルしました");
return;
}
let last_row = dataSheet.getRange(dataSheet.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); //タイム列最終行を取得
if(last_row == 1){
ui.alert("データが一件もありません");
dataSheet.getRange(2,2).activate();
return;
}
dataSheet.getRange(2,2,last_row-1,4).clearContent();
}
//初代
function first_gene(x, y, f){
let z = x * y; //人数
let g = []; //初代
for(let i=1;i<=f; i++){
for(let k=1; k<=z; k++){
let h = [];
while(h.length < z){
let n = Math.floor(Math.random()*z);
if(h.indexOf(n) == -1){
h.push(n);
}
}
g.push(h);
}
}
//Logger.log(g);
return g;
}
//評価関数
function evaluation_function(x,parent,dic){
let pare = []
let sum_time = 0;
const y = dataSheet.getRange(2,8).getValue(); //列
for(let key in dic){
sum_time += dic[key]["time"];
}
const ide = sum_time / x //理想点
const man_ide = dataSheet.getRange(4,9).getValue(); //理想の男人数
const woman_ide = dataSheet.getRange(5,9).getValue(); //理想の女人数
const avoid_ide = dataSheet.getRange(6,9).getValue(); //理想の〇人数
//Logger.log(ide);
//Logger.log(parent);
for(let v of parent){
let score = 0;
let cnt = 0;
let m_cnt = 0; //男カウント用
let w_cnt = 0; //女カウント用
let avoid_cnt = 0; //同じチームを避けたい人数カウント用
let sum_v = 0;
//Logger.log(v);
for(let i of v){
sum_v += dic[i]["time"];
if(dic[i]["sex"] == "男"){
m_cnt += 1;
}else if(dic[i]["sex"] == "女"){
w_cnt += 1;
}
if(dic[i]["duplicate"] == "〇"){
avoid_cnt += 1;
}
cnt += 1;
if(cnt == y){
//Logger.log(sum_v);
score += (ide - sum_v) ** 2;
score += (man_ide - m_cnt) ** 2; //理想の男人数からのズレ
score += (woman_ide - w_cnt) ** 2; //理想の女人数からのズレ
score += (avoid_ide - avoid_cnt) ** 2; //同じチームを避けたい人が同チームに2人以上いた場合
//Logger.log(score);
cnt = 0;
m_cnt = 0;
w_cnt = 0;
avoid_cnt = 0;
sum_v = 0;
}
}
//Logger.log(score);
pare.push([score,v]);
}
//Logger.log(pare);
//pare = sorted(np.array(pare), key=lambda x: x[0]) //点数で並び替え
//点数で並び替え
pare.sort((a, b) => {return a[0] - b[0];} );
//Logger.log(pare);
return pare;
}
//一様交叉
function crossover(ep,sd,p1,p2){
//let ch1 = copyMatrix(p1);
let ch1 = p1.slice();
//let ch2 = copyMatrix(p2);
let ch2 = p2.slice();
//Logger.log(ch1);
//Logger.log(ch2);
let ch = [];
for(let k=0; k < ch1.length; k++){
if(ep > Math.random()){
var x = "ok";
}else{
x = "no";
}
if(x == "ok"){
let c1 = ch1[k];
let c2 = ch2[k];
ch1[ch1.indexOf(c2)] = c1;
ch2[ch2.indexOf(c1)] = c2;
ch1[k] = c2;
ch2[k] = c1;
}
}
ch.push(ch1);
ch.push(ch2);
//Logger.log(ch);
return ch;
}
function gaToCreateTeams(){
if(typeof dataSheet.getRange(1,8).getValue() == 'string'){
dataSheet.getRange(1,8).setValue(hankaku(dataSheet.getRange(1,8).getValue()));
}
let ui = SpreadsheetApp.getUi();
if(dataSheet.getRange(1,9).getValue() != "チーム作成可能"){
ui.alert("「マスタ」シートにデータが一件もないか、もしくは「チーム数」に適正な数値（半角数字）が設定されていません。");
dataSheet.getRange(1,8).activate();
return;
}
let dic = []; //空の配列
let last_row = dataSheet.getRange(dataSheet.getMaxRows(), 3).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); //タイム列最終行を取得
//連想配列にキーと値をセット
for(let d=2; d <= last_row; d++){
let arr = {}; //連想配列
//dic[dataSheet.getRange(d,1).getValue()] = dataSheet.getRange(d,3).getValue();
arr["id"] = dataSheet.getRange(d,1).getValue();
arr["name"] = dataSheet.getRange(d,2).getValue();
arr["time"] = dataSheet.getRange(d,3).getValue();
arr["sex"] = dataSheet.getRange(d,4).getValue();
arr["duplicate"] = dataSheet.getRange(d,5).getValue();
dic.push(arr); //配列の末尾に追加
}
//Logger.log(dic);
//Logger.log(dic[0]["time"]);
//Logger.log(dic[1]["time"]);
const x = dataSheet.getRange(1,8).getValue(); //行
const y = dataSheet.getRange(2,8).getValue(); //列
let elite_length = 150; //上位個体数
if(dataSheet.getRange(3,8).getValue() > 100){
elite_length = 200; //上位個体数
}
const gene_length = 20; //世代数
const ep = 0.1; //一様交叉確率
const sd = 0.00 //突然変異確率
const f = 1000 //初代の数
let first = first_gene(x,y,f) //初代
//Logger.log(first);
let parent = evaluation_function(x,first,dic) //初代評価
//Logger.log(parent);
let tops = []
for(let i=0; i < gene_length; i++){
//上位個体を選別
parent = parent.slice(0,elite_length);
if(i == 0 || top[0] > parent[0][0]){ //最高得点の更新
top = parent[0];
}else{
parent.pop();
parent.push(top);
}
tops.push(top[0])
//Logger.log('第' + (i+1) + '世代'); //各世代
//Logger.log(top[0]); //各世代の最高得点を表示
//Logger.log(top[1]);
let children = [] //子世代
//遺伝子操作
parent.forEach( function( v1, k1 ) {
parent.forEach( function( v2, k2 ) {
if(k1 < k2){
let ch = crossover(ep, sd, v1[1], v2[1]); //一様交叉
//Logger.log(ch);
children.push(ch[0]) //子孫1を格納
children.push(ch[1]) //子孫2を格納
}
});
});
//Logger.log(children);
children = evaluation_function(x,children,dic) //評価
//Logger.log(children);
//子を親にコピー
parent = copyMatrix(children);
//Logger.log(parent);
}
//最強個体保存
let team = [];
let opt = [];
let s=0;
let e=y;
for(let t=0; t<top[1].length; t++){
team[t]=top[1][t]+1;
}
//Logger.log(team);
for(let r=0; r<x ; r++){
opt.push(team.slice(s, e));
s += y;
e += y;
}
//最強チーム編成をスプレッドシートへ
mainSheet.clear();
mainSheet.getRange(4,2,x,y).setValues(opt);
//行・列の表示名
let rowName = [];
let colName1D = [];
let colName2D = [];
for(let row=1; row<=x; row++){
rowName[row-1] = ["チーム" + row];
}
for(let col=1; col<=y; col++){
colName1D.push("第" + col + "走者")
}
colName2D = [colName1D]; //１次元配列→２次元配列へ
//行・列名を３つの表へ
mainSheet.getRange(4,1,x,1).setValues(rowName);
mainSheet.getRange(3,2,1,y).setValues(colName2D);
mainSheet.getRange(3,1,x+1,y+1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(3,1,x+1,y+1).setHorizontalAlignment('center');
mainSheet.getRange(3,1,x+1,y+1).setNumberFormat('0');
mainSheet.setColumnWidths(2, y+2, 120);
//タイム表
mainSheet.getRange(7+x,1,x,1).setValues(rowName);
mainSheet.getRange(6+x,2,1,y).setValues(colName2D);
mainSheet.getRange(6+x,1,x+1,y+3).setBorder(true, true, true, true, true, true);
mainSheet.getRange(6+x,1,x+1,y+3).setHorizontalAlignment('center');
mainSheet.getRange(6+x,1,x+1,y+3).setNumberFormat('#,##0.00');
//完成チーム
mainSheet.getRange(10+x+x,1,x,1).setValues(rowName);
mainSheet.getRange(9+x+x,2,1,y).setValues(colName2D);
mainSheet.getRange(9+x+x,1,x+1,y+1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(9+x+x,1,x+1,y+1).setHorizontalAlignment('center');
mainSheet.getRange(10+x+x,2,x,y).setBackground('#FFD5EC');
mainSheet.getRange(10+x+x,2,x,y).setFontWeight('bold');
//
let masterID_2D = dataSheet.getRange(2,1,last_row-1,1).getValues();
let masterID = masterID_2D.flat(); //indexOfを使用するため１次元へ変換
let masterName = dataSheet.getRange(2,2,last_row-1,1).getValues();
let masterTime = dataSheet.getRange(2,3,last_row-1,1).getValues();
let masterSex = dataSheet.getRange(2,4,last_row-1,1).getValues();
let masterDup = dataSheet.getRange(2,5,last_row-1,1).getValues();
//タイム表
let timetable = mainSheet.getRange(7+x,2,x,y+2).getValues();
let idealTime = Number(dataSheet.getRange(3,8).getValue());
let timediffSum = 0; //チーム別タイム合計の理想タイムからの誤差合計
for(row=0; row<x; row++){
let timesum = 0;
for(col=0; col<y+2; col++){
if(col < y){
timetable[row][col] = masterTime[masterID.indexOf(opt[row][col])];
timesum += Number(masterTime[masterID.indexOf(opt[row][col])]);
}
if(col==y){ //チームごとのタイム計
timetable[row][col] = timesum;
}
if(col==y+1){ //理想タイムとの誤差
timetable[row][col] = (idealTime-timesum)**2;
timediffSum += (idealTime-timesum)**2;
}
}
}
mainSheet.getRange(7+x,2,x,y+2).setValues(timetable);
mainSheet.getRange(6+x,1).setValue("タイム表");
mainSheet.getRange(6+x,2+y).setValue("チームタイム");
mainSheet.getRange(5+x,3+y).setValue("※誤差は理想タイムとチームタイムの差の２乗");
mainSheet.getRange(6+x,3+y).setValue("誤差");
mainSheet.getRange(7+x,3+y,x+1,1).setNumberFormat('#,##0.000000');
mainSheet.getRange(6+x,5+y).setValue("理想タイム");
mainSheet.getRange(7+x,5+y).setValue(idealTime);
mainSheet.getRange(7+x,5+y).setNumberFormat('#,##0.000000');
mainSheet.getRange(6+x,5+y,2,1).setBorder(true, true, true, true, true, true);
mainSheet.getRange(6+x,5+y,2,1).setHorizontalAlignment('center');
mainSheet.setColumnWidths(5+y, 1, 120);
mainSheet.getRange(7+x+x,4+y).setValue("←誤差合計");
mainSheet.getRange(7+x+x,3+y).setValue(timediffSum);
//mainSheet.getRange(7+x+x,3+y).setNumberFormat('#,##0.00');
mainSheet.getRange(7+x+x,3+y).setBorder(true, true, true, true, true, true);
mainSheet.getRange(7+x+x,3+y).setHorizontalAlignment('center');
//完成チーム部分
mainSheet.getRange(9+x+x,1).setValue("完成チーム");
if(dataSheet.getRange(4,8).getValue() != 0){
mainSheet.getRange(8+x+x,2).setValue("背景色青 ⇒ 男");
mainSheet.getRange(8+x+x,2).setBackground('#87ceeb');
mainSheet.getRange(8+x+x,2).setHorizontalAlignment('center');
}
if(dataSheet.getRange(5,8).getValue() != 0){
mainSheet.getRange(8+x+x,3).setValue("背景色赤 ⇒ 女");
mainSheet.getRange(8+x+x,3).setBackground('#ffdab9');
mainSheet.getRange(8+x+x,3).setHorizontalAlignment('center');
}
if(dataSheet.getRange(6,8).getValue() != 0){
mainSheet.getRange(8+x+x,4).setValue("文字色赤 ⇒ チーム編成で配慮する人");
mainSheet.getRange(8+x+x,4).setFontColor('#ff0000');
}
let comp = mainSheet.getRange(10+x+x,2,x,y).getValues();
let compSex = mainSheet.getRange(10+x+x,2,x,y).getValues();
let compDup = mainSheet.getRange(10+x+x,2,x,y).getValues();
for(row=0; row<x; row++){
for(col=0; col<y; col++){
comp[row][col] = masterName[masterID.indexOf(opt[row][col])];
compSex[row][col] = masterSex[masterID.indexOf(opt[row][col])];
compDup[row][col] = masterDup[masterID.indexOf(opt[row][col])];
}
}
mainSheet.getRange(10+x+x,2,x,y).setValues(comp);
//男女色つけ、同チーム分ける人文字色つけ
for(row=0; row<x; row++){
for(col=0; col<y; col++){
if(compSex[row][col]=="男"){
mainSheet.getRange(10+x+x+row,2+col).setBackground('#87ceeb');
}else if(compSex[row][col]=="女"){
mainSheet.getRange(10+x+x+row,2+col).setBackground('#ffdab9');
}
if(compDup[row][col]=="〇"){
mainSheet.getRange(10+x+x+row,2+col).setFontColor('#ff0000');
}
}
}
mainSheet.getRange(5+x,1).activate();
}
//2次元配列コピーメソッド
function copyMatrix(arr) {
const result = [];
for (const line of arr) {
result.push([...line]);
}
return result;
}
/**
* 全角から半角への変革関数
* 入力値の英数記号を半角変換して返却
* [引数] strVal: 入力値
* [返却値] String(): 半角変換された文字列
*/
function hankaku(strVal){
if(typeof strVal == 'string'){
// 半角変換
var halfVal = strVal.replace(/[！-～]/g,
function( tmpStr ) {
// 文字コードをシフト
return String.fromCharCode( tmpStr.charCodeAt(0) - 0xFEE0 );
}
);
// 文字コードシフトで対応できない文字の変換
return halfVal.replace(/”/g, "\"")
.replace(/’/g, "'")
.replace(/‘/g, "`")
.replace(/￥/g, "\\")
.replace(/ /g, " ")
.replace(/〜/g, "~");
}
}いかがでしたか？簡単に試せるので是非やってみたください！
GASの便利さを実感していただければ嬉しいです。
この記事が気に入ったらサポートをしてみませんか？