タグ: 平均

episode7 GAS【平均・カウント・昇順と降順の並び替え】キャラ身長で演算「HUNTER×HUNTERで覚えるGoogle Apps Script」

この記事のシリーズを読むことで、アニメHUNTER×HUNTER（ハンターハンター）を通してGAS（Google Apps Script）の最初の一歩が学べます。

この記事では、

・キャラクターの身長の平均

・身長170cm以上のキャラクターの人数をカウント

・カスタム数値形式で身長のcmや人数の人を表示する方法

・念系統を昇順、身長を降順で並び替え

・演算結果と並び替えのクリア

についての概要が掴めます。

今回はキャラの平均身長を求めるAVERAGE関数と、身長170cm以上という条件に該当する人数を数えるCOUNTIF関数を使いますので、Excelの関数に慣れている方でしたら馴染みやすいかもしれません。

それでは早速コードを書いて行きたいと思います。

まず、この様な簡易的なスプレッドシートを用意します。

ボタンの作り方とスクリプトの登録方法につきましては、episode2の記事 をご参照下さいませ。

最初に、キャラクターの身長を求めてセルG2（2,7）に入力するコードを書いて行きます。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均を求める
function hightcm(){
  ss.getRange(2,7).setFormula('=AVERAGE(D1:D5)');
}

このスクリプト名の「hightcm」をコピーして、オレンジのボタンの右端の3点リーダーから「スクリプトを割り当て」を選んで登録します。

ボタンを押すとこの様に実行されました。

単位の「cm」は、メニューの「表示形式」→「123数字」→「カスタム数値形式」のダイアログボックスにこの様に入力します。cmはダブルクォーテーションで囲っています。

次に、身長170cm以上のキャラクターの人数を求めてセルG6（6,7）に入力するスクリプトを掲載致します。
先ほどのコードの下にこの様に付け加えます。

COUNTIF関数を用いております。

//身長170cm以上の人数を求める
function cm170over(){
  ss.getRange(6,7).setFormula('=COUNTIF(D1:D5,">=170")');
}

緑のボタンに「cm170over」を登録して実行致します。

この様に求まりました。

人数の単位の「人」は、先ほどのカスタム数値形式で「0人」とそのままダブルクォーテーションを付けずに指定します。

次に並び替えを行います。

・念を昇順
・念が同じ場合は身長の降順

になる様に動作するスクリプトを掲載致します。
念は3列目、身長は4列目になっております。

並び替えをするのに、sortメソッド、ascendingがtrueで昇順、falseで降順（背の高い順）を使用しております。

また、下にこのコードを追記します。

//念を昇順、身長を降順で並び替え
function sortNenHight(){

  //並び替えの範囲と並べ替え種類の指定
  ss.getRange(1,1,5,4).sort([
    {column: 3,ascending: true},
    {column: 4,ascending: false},
  ])

}

ピンクのボタンに「sortNenHight」を割り当ててボタンを押します。

実行結果です。
同じ変化系のヒソカとキルアでは、身長が高い方が上になっております。

最後に、並び替えを戻して計算結果を消去するスクリプトを掲載します。
順番を元に戻す処理も考えて、A列に予め番号を割り振っておきましたので、A列である「column: 1」を「ascending: true」で昇順にします。

//計算結果のクリアと順番を元に戻す（キャラクター順）
function sortClear(){

  //並び替えの範囲と並べ替え種類の指定
  ss.getRange(1,1,5,4).sort([
    {column: 1,ascending: true}
  ])

  //計算結果のクリア
  ss.getRange(2,7).clear();
  ss.getRange(6,7).clear();

}

このスクリプト「sortClear」を灰色のボタンに割り当てて実行します。

元の順番に戻って、計算結果もクリア出来ました。
ここまでのコードをまとめます。

//アクティブなスプレッドシートを選択するグローバル領域（共通の処理）
const ss=SpreadsheetApp.getActiveSheet();

//身長の平均を求める
function hightcm(){
  ss.getRange(2,7).setFormula('=AVERAGE(D1:D5)');
}


//身長170cm以上の人数を求める
function cm170over(){
  ss.getRange(6,7).setFormula('=COUNTIF(D1:D5,">=170")');
}



//念を昇順、身長を降順で並び替え
function sortNenHight(){

  //並び替えの範囲と並べ替え種類の指定
  ss.getRange(1,1,5,4).sort([
    {column: 3,ascending: true},
    {column: 4,ascending: false},
  ])

}


//計算結果のクリアと順番を元に戻す（キャラクター順）
function sortClear(){

  //並び替えの範囲と並べ替え種類の指定
  ss.getRange(1,1,5,4).sort([
    {column: 1,ascending: true}
  ])

  //計算結果のクリア
  ss.getRange(2,7).clear();
  ss.getRange(6,7).clear();

}

お疲れ様でした。ブレイクタイムPhotoは、

みなとみらいの夜景、コスモクロック21です。

←episode6　　　　　　　　　　　　　　　episode8（最終回）→

←GAS自作集サンプルへ戻る

■ 関連記事 ■

HUNTER×HUNTERのGAS自作サンプル集のページに戻る
初めてのGASに戻る

自己紹介

ブラウザだけでMySQLを即学習【サザエさんの例 その4】｜合計と平均｜最大値と最小値｜COUNT ｜LIMIT ｜GROUP BY

この記事では、ブラウザだけでMySQLを「サザエさんの例」を通してサンプルのコードを書きながら学習する内容になっております。

途中からでもなるべく学習可能な様に書いております。

使用ブラウザはmyCompilerのMySQLを使っています。

今回学習するテーブルのイメージです。

現在、ブラウザのMySQLの左側の画面はこの様なコードが入っています。

-- create a table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL
);
-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27);
-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';
SELECT * FROM sazaefamily;
SELECT * FROM sazaefamily WHERE age>=25;
SELECT name FROM sazaefamily WHERE id=4;
SELECT name FROM sazaefamily WHERE name LIKE '%野%';
SELECT name,age FROM sazaefamily WHERE age<15;
SELECT name,age FROM sazaefamily ORDER BY age DESC;

テーブルを年齢順に並び替えて、この様なイメージになっています。

今日は、サザエさんのキャラクターの年齢の、平均や最大値、件数のカウントなどを求めて行きます。

まず、年齢の合計と平均を求めて見ます。

SELECT SUM(age),AVG(age) FROM sazaefamily;

実行結果です。

年齢の合計を求めるというのは、あまり実用的ではありませんね。
平均年齢については、小数点以下を表示させないようにROUND関数でAVG関数を囲いネストします。

SELECT SUM(age), ROUND(AVG(age)) FROM sazaefamily;

次に年齢の最大値と最小値を求めます。
年齢順に並べた表のイメージです。

SELECT MAX(age),MIN(age) FROM sazaefamily;

実行結果です。

年齢が高い順に3名抽出します。

SELECT * FROM sazaefamily ORDER BY age DESC LIMIT 3;

実行結果です。

この表の件数を求めます。

SELECT COUNT(*) FROM sazaefamily;

実行結果です。

年齢が平均以上のキャラクターの名前と年齢を求めます。

SELECT name,age FROM sazaefamily WHERE age >= (SELECT AVG(age) FROM sazaefamily);

実行結果です。

最後にGROUP　BY を使って、男女別の平均年齢を求めます。

SELECT gender, ROUND(AVG(age)) FROM sazaefamily GROUP BY gender;

実行結果です。

ここまでのコードのまとめです。

-- create a table
CREATE TABLE sazaefamily (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  age INTEGER NOT NULL
);
-- insert some values
INSERT INTO sazaefamily VALUES (1, '磯野波平', '男性',54);
INSERT INTO sazaefamily VALUES (2, 'フグ田サザエ', '女性',24);
INSERT INTO sazaefamily VALUES (3, '磯野カツオ', '男性',11);
INSERT INTO sazaefamily VALUES (4, '磯野ワカメ', '女性',9);
INSERT INTO sazaefamily VALUES (5, '穴子さん', '男性',27);
-- fetch some values
SELECT * FROM sazaefamily WHERE gender = '女性';
SELECT * FROM sazaefamily;
SELECT * FROM sazaefamily WHERE age >= 25;
SELECT name FROM sazaefamily WHERE id = 4;
SELECT name FROM sazaefamily WHERE name LIKE '%野%';
SELECT name, age FROM sazaefamily WHERE age < 15;
SELECT name, age FROM sazaefamily ORDER BY age DESC;
SELECT SUM(age), ROUND(AVG(age)) FROM sazaefamily;
SELECT MAX(age), MIN(age) FROM sazaefamily;
SELECT * FROM sazaefamily ORDER BY age DESC LIMIT 3;
SELECT COUNT(*) FROM sazaefamily;
SELECT age, name FROM sazaefamily WHERE age >= (SELECT AVG(age) FROM sazaefamily);
SELECT gender, ROUND(AVG(age)) FROM sazaefamily GROUP BY gender;

お疲れ様でした。ブレイクタイムPhotoは、

隅田公園の梅の花です。

→ブラウザだけでMySQLを即学習【サザエさん】その5へ

ご精読誠にありがとうございました。

【オススメ入門書】
3ステップでしっかり学ぶ My SQL入門

MySQL自作サンプル集へ戻る

写真クリエイターとしての活動
自己紹介