スプレッドシートを使っていて最近知ったのが、MAP関数とLAMBDA関数。
ほぼスクリプトだなと思いました。
マクロとかGASとか、もっというとプログラミング齧ってみたいという人にとってすごく良いプログラミング的な考え方を知るきっかけになるのでは、と思いました。
そこで、実際に関数の意味と使いどころの一例を紹介してみようと思います。
使いどころの一例
例えば、あなたはチームリーダーで各メンバーの予実進捗管理や売り上げ見込みの管理をしないといけないとします。
チーム単位で予実管理のアプリを使えるような環境ではないとして、代わりにスプレッドシートまたはExcelでやろうと考えます。
1つのファイルでチーム全員の予実を管理するためのスプレッドシートをつくることにします。
1シート1人のメンバーが、それぞれ同じフォーマットの表に数字を入れていくとしましょう。
各メンバーの数字の集計は自動で集計シートに表示され、メンバーが増えたり減ったりしても関数はいじらないようにしたいと思います。
メンバーの増減のたびに関数をいじったりしたら抜け漏れが怖いですからね。
このようなことが実現できるスプシを作っていこうと思います。
作りました。
https://docs.google.com/spreadsheets/d/15s4hrPVMcSkKL65OINW7ZTHVE0jry2lWBLw_QZeR7jw/edit?gid=0#gid=0
集計シートと、他に各メンバーのシートがあります。
それぞれのシートは同じフォーマットになっており、数字を入れるセルの位置は同じです。
各メンバーが数字を入れれば、集計シートにチーム全体の集計が勝手に表示されるというようなスプシです。
でなるべくメンテフリーにしたいので、メンバーの増減があってもシートをコピペし、集計シートの2行目に集計対象のシート名を追加削除するだけでOKです。
この内容を実現できるのが、MAP関数、LAMBDA関数なのです。
関数の内容
=SUM(MAP($B$2:$Z$2, LAMBDA(s, IF(s="",0,INDIRECT("'" & s & "'!" & ADDRESS(ROW(), COLUMN(), 4))))))
集計シートの、数字を集計したいセルに上記の関数を入れると各メンバーシートの同じ位置のセルを参照して集計してくれます。
この関数を集計したいセルにコピペしていけばOKです。
解説
このシートの場合、集計シートのB2～D2までにシート名が記載してあります。
このシート名を参照して各セルの値を集計するようになっています。
それがこの部分です。
MAP($B$2:$Z$2, MAPは、セルの範囲を配列として扱うことができます。
この場合は、[“Aさん”,”Bさん”,”Cさん”]という配列のイメージです。
この配列を順番に読み込んでいって、同じ名前のシート名を探していければOKですね。
順番に読み込んでいって、という部分でLAMDA関数が活躍します。
LAMBDA関数はほぼFor分と言っていいと思うのですが、MAP関数で指定した配列を頭から順番に読み込んで、アルファベット1文字に代入します。
ここでは「s」ですね。(sに特に意味はありません・・・)
LAMBDA(s, IF(s="",0,INDIRECT("'" & s & "'!" & ADDRESS(ROW(), COLUMN(), 4))))))で、ちょっとごちゃごちゃしているので段落を分けて整理しますね。
ADDRESS関数の最期の「4」というのは、A1形式でセルの参照を表現しますという意味です。
(絶対参照と相対参照を行と列の組み合わせ4パターンを1-4の数字で指定できます。分かりにくい。)
これで、この関数を集計シートのセルに入力すれば他シートの同じ位置のセルを参照できるというわけです。
それを一番最初のSUMで足し算してるわけですね。
何が言いたいかというと
const ss = SpreadsheetApp.openById(**********);
const agrigationSheet = ss.getSheetByName("集計");
const sheetNames = ["Aさん","Bさん","Cさん"];
let result = 0;
let cellRange = SpreadSheet.
for(let i = 0; i < sheetNames.length; i++){
const sheet = ss.getSheetByName(i);
const value = sheet.getRange().getValue(6,4);
result += value;
}
agrigationSheet.getRange(6,4).setValue(result);GASで書くと↑みたいなことをこの関数だけで実行できるんですよね。
しかも集計したいセルの位置を可変または範囲にするともっと複雑になりますからね。
いちいちGAS書かなくても繰り返し処理ができるって素敵ですね。
Excelでも使える関数ですのでExcel派の人でもできますよ。
こうやってみると、MAP関数、LAMBDA関数って
for(let i = 0… というところ、LAMBDA(s, …のところに似ていませんか？
プログラミングの考え方の一端をかんじられるかもしれません。
気になる方は一回使ってみて下さい。
上の方にサンプルのスプレッドシートも公開していますのでコピーを作成して色々いじってみてください。
コメント