お知らせ
（2024/6/25 追記）
Script Lab は Microsoft が正式に管理することになったようです。
その結果、Gistを使ったスニペットの管理機能は削除されました。
なお、記事後半の該当部分は簡単な修正にとどめます。
（2023/11/30 追記）
2023/8/15 に追記した不具合(下記)は解消したようです。
私の環境では動作するようになりました。
（2023/8/15 追記）
現在、デスクトップ版Excelで不具合が生じています。
私の環境（Microsoft® Excel® 2021 MSO (バージョン 2307 ビルド 16.0.16626.20170) 64 ビット ）も同じ問題が発生しています。
なお、Web版Excel（Excel for the web）では動作しています。
はじめに
最近、Script LabでExcelのユーザー定義関数（カスタム関数）をよく書いています。
Googleスプレッドシート + GAS(Google Apps Script)に近い感覚で手軽に操作できます。
Script Labは無料のOfficeアドインです。
WindowsやMac上のExcelだけでなく、Excel on the web（Web版Excel）もサポートしているのでMicrosoftアカウントがあれば、ブラウザからでもすぐに試してみることができます。
詳細は下記にて。
Script Lab未体験の方、TypeScript・JavaScript入門者の方を対象に、簡単な例をいくつか示します。
注意
本記事中、ユーザー定義関数はユーザー定義のワークシート関数を指します。
ユーザー定義関数（カスタム関数）はまだ正式機能ではありません。
動作確認
ChromeBookで Chrome + Excel on the web を使って動作を確認しています。
ユーザー定義関数の作成
Script Labの導入手順も含めていくつかの例を示します。
例1 正規表現を使った電話番号の表記統一
昨年投稿した「Excelでちょっとした正規表現を使いたい」の内容をユーザー定義関数で書いてみます。
具体的には、電話番号の書式0123(45)6789や(0123)45-6789などを0123-45-6789に統一する関数です。
使用する言語がTypeScript・JavaScriptなので正規表現が簡単に利用できます。
手順
Web版Excelではメニューの 挿入 - Officeアドイン から追加します。
（デスクトップ版ではメニューの 開発 - アドイン）
問題がなければメニューに Script Lab が追加されます。
Script Lab - Code をクリックすると画面右にCodeペインが開きます。
ここで、New Snippetを選ぶと新規スニペットのエディタ画面が開きます。
（環境によっては最初からエディタ画面が出ることもあるようです。）
まずはスニペット名を変更します。
新規スニペットの名前は Blank snippet なので、スニペット名をクリックして...
名前を変更し、説明を記入します。
注意
スニペット名はユーザー定義関数名に影響します。
後で変更すると、使用中のユーザー定義関数がすべてエラーになります。
変更しないで済むように名前をつけましょう。
（記事中の図は"Blank snippet"のままなので読み替えてください。）
エディタのひな形をすべて削除します。（赤丸のRun ボタンは後述）
エディタに次のコードを貼り付けます。
/**
* 電話番号を 0XXX-YY-ZZZZ に統一する
* @customfunction
*/
function ex1(tel: string): string {
return tel.replace(/\(?(\d+)\D(\d+)\D(\d+)/, "$1-$2-$3");
}
関数直前のJSDocコメントに@customfunctionを書くとユーザー定義ワークシート関数になります。
ユーザー定義関数が書かれると、エディタ画面の Run ボタンが Register に変わります。
Registerをクリックして、変更を反映させます。
Custom Functions (Preview) と表記されているので、まだ正式機能ではありません。
以上でユーザー定義ワークシート関数の完成です。
下図はワークシートに入力した様子です。
数式オートコンプリートにも対応するので、関数名の一部を入力すると候補に上がります。
全角データ（セルB6）もあるので、ASC関数をかませます。
うまくいきました。なお、初回は時間がかかります。
数式を下にコピー。
先に注意書きしましたが、Script Labで作成した正式な関数名は、SCRIPTLAB.<スニペット名>.<関数名>です。
セルに=scrまで入力すれば、ユーザー定義関数だけが候補として表示されます。（"SCRIPTLAB"にヒット）
POINT 1
・JSDocコメントに@customfunctionを書く。
・関数を作成・編集したら、Register をクリックして変更を反映させる。
例2 文字列の切り分け
簡単な例を使って、文字列と数値の扱いを確認します。
ここでは４桁の生徒コード（例：1年2組3番 は 1203，3年A組34番 は 3A34）から、学年・組・出席番号を切り分ける例を４パターン示します。
以下のコードでは、「関数の型宣言」「+を使った型変換」「戻り値が２次元配列」あたりがポイントです。
関数の型を宣言するだけで、数値データも文字列として渡すことができます。
戻り値も関数内のデータの型で返されます。
戻り値を２次元配列にするとスピル動作をします。（数式セルに隣接する複数行・列のセル範囲に値を返す）
以下、例示したコードは、例1のスニペットに追加でペーストしてください。
(1) 文字列で返す
/**
* 文字列の切り分け 1203 -> "1", "2", "03"（文字列で返す）
* @customfunction
*/
function ex2_1(str: string): string[][] {
return [[str[0], str[1], str.slice(2)]];
}
POINT 2
・引数や戻り値の型を明記する。
・戻り値が２次元配列の場合スピルになる。
(2) 数値で返す
/**
* 文字列の切り分け 1203 -> 1, 2, 3（数値で返す）
* @customfunction
*/
function ex2_2(str: string): number[][] {
return [[+str[0], +str[1], +str.slice(2)]];
}
(3) 数値・文字列混在で返す
１年Ｂ組３番のように、文字が混在する場合です。
せっかくなので、小文字は大文字に変換します。
/**
* 文字列の切り分け 1B03 -> 1, "B", 3（数値と文字列混在で返す）
* @customfunction
*/
function ex2_3(str: string): any[][] {
return [[+str[0], str[1].toUpperCase(), +str.slice(2)]];
}
(4) 正規表現版
正規表現を使う手も。
/**
* 文字列の切り分け 1203 -> "1", "2", "03"（正規表現版）
* @customfunction
*/
function ex2_4(str: string): string[][] {
const res = /(.)(.)(..)/.exec(str);
return [[res[1], res[2], res[3]]];
}
上記(1)〜(4)の順にワークシートに入力しました。（数式表示）
なお、セルB5を除くB列の生徒コード「1203」は数値です。
結果です。
関数はC列に入力していますが、スピルによってC〜E列に結果が入ります。
例３ 月間予定表
引数を省略する例です。（動作の説明はコメントのとおり。）
ここでのポイントは、関数の使用時に引数を省略するとExcelはnullを渡すことです。
必要に応じて、ifを使ってnullを判定する必要があります。
/**
* 年, 月 から縦方向にその月の 日, 曜 を返す
* @customfunction
* @param [year] 西暦 (省略時 今年)
* @param [month] 月 (省略時 今月)
* @returns 日, 曜 の n行2列
*/
function ex3(year?: number, month?: number): any[][] {
const d = new Date();
let y = year !== null ? year : d.getFullYear();
let m = month !== null ? month - 1 : d.getMonth();
d.setFullYear(y, m, 1); // 月初日
const w1 = d.getDay(); // 月初日の「曜」(0 - 6)
d.setMonth(m + 1, 0); // 月末日 (翌月1日の前日)
const dn = d.getDate(); // 月末日の「日」 = その月の日数
const week = ["日", "月", "火", "水", "木", "金", "土"];
return Array.from({ length: dn }, (_, i) => [i + 1, week[(w1 + i) % 7]]);
}
ワークシートで確認します。
JSDocコメントの@paramは関数の動作に無関係ですが、ワークシートで関数を入力する際のヒント表示に[ ]を表示させます。（下図赤線）
引数を省略したので、原稿執筆時（2022年4月）の曜日を返します。
うまくいきました。
では、横方向に結果を得たい場合は...
配列（スピル）はTRANSPOSEワークシート関数で転置できます。
横方向になります。
POINT 3
・引数を省略するとExcelはnullを渡す。
・@paramは数式のヒント表示に影響する。
補足
引数を省略してもExcelはnullを渡すので、ワークシート関数として使用する場合、デフォルト引数構文は機能しません。
nullは引数の型numberに反しますがエラーを出さずに引数として渡ります。
関数を他のスクリプトから呼び出す場合は引数省略時の対応を要すると思います。（特に検証していません。）
なお、例示したコードでは、形式的にオプション引数として?を付けていますが、実際には引数を省略してもnullが渡されるので?は不要です。
記事の内容には無関係ですが、メタデータには?の有無が反映されています。（下図）
例4 金種表
ワークシート関数を組み合わせるよりも、簡単でわかりやすく金種表計算ができます。
(1) 紙幣・硬貨の一覧を返す関数
金種一覧を返すだけの関数ですが、2000円札の使用を引数で指定します。
引数省略時はnullが偽値となるので、2000円札不使用です。
/**
* 金種行作成：kinsyu([2000円札可否])
* @customfunction
* @param [yen2k] 2000円札可否(省略時 否)
* @returns 枚数
*/
function ex4_kinsyu(yen2k?: boolean): number[][] {
return [yen2k ?
[10000, 5000, 2000, 1000, 500, 100, 50, 10, 5, 1] :
[10000, 5000, 1000, 500, 100, 50, 10, 5, 1]];
}
関数を使う前に、セルC2に入力規則を設定します。
ドロップダウンになります。
セルC2を引数にします。
セルC2の切り替えで金種の範囲が変わります。
(2) 金額から金種別の枚数を返す関数
JavaScriptのArray.reduce()メソッドを利用します。
「上位金種で商を求め、余りを下位金種に渡す」操作を繰り返すだけです。
/**
* 金種別枚数取得(1行)：maisu1(金額, 金種範囲)
* @customfunction
* @param kingaku 金額
* @param kinsyu 金種範囲
* @returns 枚数(1行)
*/
function ex4_maisu1(kingaku: number, kinsyu: number[][]): number[][] {
const m: number[] = []; // 金種別枚数
kinsyu[0].reduce((x: number, y: number) => {
m.push(Math.floor(x / y));
return x % y;
}, kingaku);
return [m];
}
セルB5に適当な金額を入れて、上記関数を追加します。
スピル範囲演算子を使ったセル参照（下図数式のC4#） ができるため、2000円札の有無による金種範囲の変化に対応できます。
注）実際は数式のコピーを意識して$C$4#がベター。
うまく動くようです。（2000円札使用）
2000円札を使用しないように切り替えると...
バッチリです。
(3) 金額を範囲で渡して、金種別の枚数を一括で返す関数
参考までに、ユーザー定義関数も別のユーザー定義関数から呼び出せます。
/**
* 金種別枚数取得(複数行一括取得)：maisuAll(金額範囲, 金種範囲)
* @customfunction
* @param kingaku 金額範囲
* @param kinsyu 金種範囲
* @returns 枚数(複数行)
*/
function ex4_maisuAll(kingaku: number[][], kinsyu: number[][]): number[][] {
return kingaku.map((k) => ex4_maisu1(k[0], kinsyu)[0]);
}
１つの関数で...
すべての結果を返します。
スニペットの管理など
スニペットの保存と利用
スニペットは、Gistによってブックとは独立して管理します。
記事冒頭に追記したとおり、Gistを使ったスニペットの管理機能は削除されました。以下は、参考までに残しておきます。
GitHubアカウントを持っていない場合は新規登録して、Gistが使えるようにしましょう。
（コードはテキストファイルなので簡単に管理できます。自身で管理する場合、Gistは必須ではありません。）
Gistへは、エディタ画面 から、Share - New secret gistで保存します。
「secret」といってもコード共有サービスなので、関係者間でURLを介してスニペットを共有できます。
外部アクセスから保護されているわけではないので注意してください。
Script Labから提示されたURLをコピーします。（下図赤丸）
ブラウザから、コピーしたURLでGitHubにログインすると...
トークンが返されるので、コピーしてScript Labに戻ってペーストします。
これで、Script LabとGitHubが紐づくので、次回以降は自動ログインされます。
先のブラウザは閉じても構いません。
CodeペインのMy Snippetsで自作スニペットがリスト表示されます。
Gist上に置いたスニペットは My shared gists on GitHub にあります。
使用時はワンクリックで端末上（My snippets on this computer）に置くことができます。
また、スニペットを編集した場合はGistへの更新（下図）をお忘れなく。
ユーザー定義関数は独立したスニペットにまとめる
本記事では触れませんが、
1) HTML + CSS でフォームを作成し
2) エディタ画面のRunボタンでフォームを表示して
3) フォームからスクリプトを実行する
のがScript Labの標準的な使い方です。
このようなスニペットにユーザー定義関数を混在させるとエディタ画面のRunボタンが使えなくなる（POINT 1参照）ので注意してください。
ユーザー定義関数用のスニペットは目的や用途別に複数に分けて管理するとよいでしょう。
端末上のスニペットはエディタ画面のDeleteボタンで削除できます。
Excelやブラウザを閉じた場合、スニペットはキャッシュに残っているようです。
Web版ではブラウザのリロードなどで必要な対応をしてください。
おわりに
Script Labには多くのサンプルが付いてきます。
もちろんカスタム関数のサンプルもあります。
勉強にもなりますし、かなり高度な作業も可能です。
無理にワークシート関数の複雑な組み合わせで処理しようとせず、スクリプトで簡潔に書いてみませんか。
参考文献
Script LabはOffice JavaScript APIでアドイン開発するための試作環境です。
ここではOffice JavaScript APIのカスタム関数機能を利用しているので、Office JavaScript APIのドキュメントが主な資料になります。
（Script Lab 公式）
（スピルに関する解説）
（引数に関する解説）
（その他）