LAMBDA関数の一例を紹介します。
最初のシートは、再帰的なLAMBDA関数の例をいくつか示しています。
2枚目と3枚目には、より複雑な例が2つあります。
var data = {
Recursive: {
values: {
1: { 1: "例" },
3: { 1: "例1： 階乗を計算する" },
4: { 1: "［名前の管理］で以下のように定義します：" },
5: { 1: "名前：", 2: "myFact" },
6: { 1: "範囲：", 2: "Workbook" },
7: { 1: "コメント：", 2: "数値の階乗を計算する" },
8: {
1: "参照：",
2: "=LAMBDA(num,\r\n IF(num<2,\r\n 1,\r\n num * myFact(num - 1)\r\n )\r\n)",
},
14: { 1: "データ", 2: "数式", 3: "結果" },
15: { 1: 4 },
16: { 1: 16 },
17: { 1: 52 },
19: { 1: "例2： 文字列内の文字を置換する" },
20: { 1: "［名前の管理］で以下のように定義します：" },
21: { 1: "名前：", 2: "ReplaceChars" },
22: { 1: "範囲：", 2: "Workbook" },
23: { 1: "コメント：", 2: "文字列中の指定された文字を置き換える" },
24: {
1: "参照：",
2: '=LAMBDA(str, chars, sub\r\n IF(chars="",\r\n str,\r\n ReplaceChars(\r\n SUBSTITUTE(str, LEFT(chars), sub),\r\n MID(chars,2,LEN(chars) - 1),\r\n sub\r\n )\r\n )\r\n)',
},
34: { 1: "データ", 2: "数式", 3: "結果" },
35: { 1: "WARNING!! <script>" },
36: { 1: "#4 & #7 + $803*" },
37: { 1: "Generally (#25) free" },
39: { 1: "例3： 文字列の値を反転させる" },
40: { 1: "3a. ［名前の管理］で以下のように定義します：" },
41: { 1: "名前：", 2: "HEAD" },
42: { 1: "範囲：", 2: "Workbook" },
43: { 1: "コメント：", 2: "文字列の最初の文字を返す" },
44: {
1: "参照：",
2: '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n LEFT(str, 1)\r\n )\r\n)',
},
50: { 1: "3b. ［名前の管理］で以下のように定義します：" },
51: { 1: "名前：", 2: "TAIL" },
52: { 1: "範囲：", 2: "Workbook" },
53: { 1: "コメント：", 2: "文字列から1文字目を除いたものを返す" },
54: {
1: "参照：",
2: '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n RIGHT(str, LEN(str) - 1)\r\n )\r\n)',
},
60: { 1: "3c. ［名前の管理］で以下のように定義します：" },
61: { 1: "名前：", 2: "REVERSE" },
62: { 1: "範囲：", 2: "Workbook" },
63: { 1: "コメント：", 2: "文字列を逆順で返す" },
64: {
1: "参照：",
2: "=LAMBDA(str,\r\n IF(LEN(str)<2,\r\n str,\r\n REVERSE(TAIL(str)) & HEAD(str)\r\n )\r\n)",
},
70: { 1: "データ", 2: "数式", 3: "結果" },
71: { 1: "palindrome" },
72: { 1: "backwards" },
73: { 1: "forwards" },
75: { 1: "例4： 文字列が回文であるかどうかを調べる" },
76: { 1: "［名前の管理］で以下のように定義します：" },
77: { 1: "名前：", 2: "IsPalindrome" },
78: { 1: "範囲：", 2: "Workbook" },
79: { 1: "コメント：", 2: "文字列が回文である場合に TRUE を返します" },
80: {
1: "参照：",
2: '=LAMBDA(str, \r\n LET(\r\n replaceStr, ReplaceChars(str, "!@#$%^&*()[]<>-?.,\'"" ", ""),\r\n lowStr, LOWER(replaceStr),\r\n lowStr = REVERSE(lowStr)\r\n )\r\n)',
},
87: { 1: "データ", 2: "数式", 3: "結果" },
88: { 1: "I, man, am Regal, a German am I" },
89: { 1: "Never odd or even" },
90: { 1: "If I had a Hi-Fi" },
91: { 1: "Madam, I'm Adam" },
92: { 1: "Too hot to hoot" },
93: { 1: "No lemons, no melon" },
94: { 1: "Too bad I hid a boot" },
95: { 1: "Lisa Bonet ate no basil" },
96: { 1: "Warsaw was raw" },
97: { 1: "Was it a car or a cat I saw?" },
},
formulas: {
15: { 2: "FORMULATEXT(D16)", 3: "MYFACT(B16)" },
16: { 2: "FORMULATEXT(D17)", 3: "MYFACT(B17)" },
17: { 2: "FORMULATEXT(D18)", 3: "MYFACT(B18)" },
35: { 2: "FORMULATEXT(D36)", 3: 'REPLACECHARS(B36,"!@#$%^&*()[]<>-?.,","")' },
36: { 2: "FORMULATEXT(D37)", 3: 'REPLACECHARS(B37,"!@#$%^&*()[]<>-?.,","")' },
37: { 2: "FORMULATEXT(D38)", 3: 'REPLACECHARS(B38,"!@#$%^&*()[]<>-?.,","")' },
71: { 2: "FORMULATEXT(D72)", 3: "REVERSE(B72)" },
72: { 2: "FORMULATEXT(D73)", 3: "REVERSE(B73)" },
73: { 2: "FORMULATEXT(D74)", 3: "REVERSE(B74)" },
88: { 2: "FORMULATEXT(D89)", 3: "ISPALINDROME(B89)" },
89: { 2: "FORMULATEXT(D90)", 3: "ISPALINDROME(B90)" },
90: { 2: "FORMULATEXT(D91)", 3: "ISPALINDROME(B91)" },
91: { 2: "FORMULATEXT(D92)", 3: "ISPALINDROME(B92)" },
92: { 2: "FORMULATEXT(D93)", 3: "ISPALINDROME(B93)" },
93: { 2: "FORMULATEXT(D94)", 3: "ISPALINDROME(B94)" },
94: { 2: "FORMULATEXT(D95)", 3: "ISPALINDROME(B95)" },
95: { 2: "FORMULATEXT(D96)", 3: "ISPALINDROME(B96)" },
96: { 2: "FORMULATEXT(D97)", 3: "ISPALINDROME(B97)" },
97: { 2: "FORMULATEXT(D98)", 3: "ISPALINDROME(B98)" },
},
cellStyles: {
B2: 0,
"B4:D4": 1,
B5: 2,
"B6:B8": 3,
"C6:C8": 4,
"D6:D8": 5,
B9: 6,
C9: 7,
D9: 8,
"B10:B13": 9,
"C10:C13": 10,
"D10:D13": 11,
B14: 12,
C14: 13,
D14: 14,
B15: 15,
C15: 16,
D15: 15,
"B16:B18": 17,
"C16:D18": 18,
"B20:D20": 1,
B21: 2,
"B22:B24": 3,
"C22:C24": 4,
"D22:D24": 5,
B25: 6,
C25: 7,
D25: 8,
"B26:B33": 9,
"C26:C33": 10,
"D26:D33": 11,
B34: 12,
C34: 13,
D34: 14,
"B35:D35": 15,
"B36:B38": 17,
"C36:D38": 18,
"B40:D40": 1,
B41: 2,
"B42:B44": 3,
"C42:C44": 4,
"D42:D44": 5,
B45: 6,
C45: 7,
D45: 8,
"B46:B49": 9,
"C46:C49": 10,
"D46:D49": 11,
B50: 12,
C50: 13,
D50: 14,
B51: 2,
"B52:B54": 3,
"C52:C54": 4,
"D52:D54": 5,
B55: 6,
C55: 7,
D55: 8,
"B56:B59": 9,
"C56:C59": 10,
"D56:D59": 11,
B60: 12,
C60: 13,
D60: 14,
B61: 2,
"B62:B64": 19,
"C62:C64": 20,
"D62:D64": 5,
B65: 21,
"C65:C70": 22,
"D65:D70": 23,
"B66:B69": 24,
B70: 25,
"B71:D71": 15,
"B72:B74": 17,
"C72:D74": 18,
"B76:D76": 1,
B77: 2,
"B78:B80": 19,
"C78:C80": 20,
"D78:D80": 5,
B81: 21,
"C81:C87": 22,
"D81:D87": 23,
"B82:B86": 24,
B87: 25,
"B88:D88": 15,
"B89:B98": 17,
"C89:C98": 26,
"D89:D98": 18,
A99: 27,
},
styles: {
records: [
{ foreColor: 0, font: 0, border: [null, null, 0] },
{ foreColor: 0, font: 1, border: [null, null, 1] },
{ foreColor: 0, font: 1 },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2, 2] },
{ backColor: 3, font: 1, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, border: [2, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1 },
{ backColor: 4, foreColor: 1, font: 1, border: [2] },
{ backColor: 6, foreColor: 5, border: [3, 3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 3, 3, 3] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, border: [2, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, 2, 2] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, null, 3, 3] },
{ foreColor: 9 },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#000000", style: 1 },
{ color: "#7f7f7f", style: 1 },
],
colors: ["#44546a", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99", "#fa7d00", "#f2f2f2", "#0563c1"],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: { 0: 36, 1: 172, 2: 300, 3: 141 },
rowHeight: { 1: 24, 2: 21, 3: 21, 19: 21, 39: 21, 75: 21 },
spans: ["B81:B87", "B65:B70", "C45:D50", "C55:D60", "C65:D70", "C81:D87", "C9:D14", "C25:D34"],
},
},
Calendar: {
values: {
0: { 1: "Calendar関数" },
2: { 1: "この例のLAMBDA関数は、連続した日付からセル範囲にカレンダーを生成します。" },
5: { 1: "Calendarの構文：" },
6: { 1: "=CALENDAR(serial, mark)" },
7: { 1: "serial： カレンダーを生成するための連続した日付。" },
8: { 1: 'mark： 連続した日付に "X " 印を付ける場合はTrue、それ以外はFalse。' },
10: { 1: "この例のLAMBDA関数は複雑で、いくつかの定義済みの名前が必要です：" },
12: { 1: "名前：", 2: "months" },
13: {
1: "参照：",
2: '={"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}',
},
15: { 1: "名前：", 2: "days" },
16: { 1: "参照：", 2: '={"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}' },
18: { 1: "名前：", 2: "getDay" },
19: { 1: "範囲：", 2: "Workbook" },
20: { 1: "コメント：", 2: "連続する日付の曜日をテキストで取得します。" },
21: { 1: "参照：", 2: "=LAMBDA(serial, INDEX(days, WEEKDAY(serial, 2),1))" },
23: { 1: "名前：", 2: "vcat" },
24: { 1: "範囲：", 2: "Workbook" },
25: {
1: "コメント：",
2: "2つの配列を順に重ねて1つの配列に連結します。",
},
27: {
1: "参照：",
2: "=LAMBDA(top,bot,\r\n LET(width, MIN(COLUMNS(top), COLUMNS(bot)),\r\n topH, ROWS(top),\r\n arrayMAKE(ROWS(bot)+topH, width,\r\n LAMBDA(i,j,\r\n IF(i <= topH,\r\n INDEX(top, i, j),\r\n INDEX(bot, i-topH, j)\r\n )\r\n )\r\n )\r\n )\r\n)",
},
41: { 1: "LAMBDAの実装" },
42: { 1: "［名前の管理］で以下のように定義します：" },
43: { 1: "名前：", 2: "Calendar" },
44: { 1: "範囲：", 2: "Workbook" },
45: { 1: "コメント：", 2: "連続した日付からセル範囲にカレンダーを生成します。" },
46: {
1: "参照：",
2: '=LAMBDA(serial, mark,\r\n LET(daysInMonth, EOMONTH(serial, 0)-EOMONTH(serial, -1),\r\n foMonth, DATE(YEAR(serial), MONTH(serial), 1),\r\n dayPadding, WEEKDAY(foMonth, 2)-1,\r\n calendarRows, ROUNDUP((dayPadding+daysInMonth)/7, 0),\r\n body, arrayMAKE(calendarRows, 7,\r\n LAMBDA(i, j,\r\n LET(seqNum, ((i-1)*7+j)-dayPadding,\r\n IFS(seqNum<=0, "",\r\n seqNum=DAY(serial), IF(mark, "X", seqNum),\r\n seqNum<=daysInMonth, seqNum,\r\n TRUE, ""\r\n )\r\n )\r\n )\r\n ),\r\n vcat(\r\n arrayMAKE(1, 7,\r\n LAMBDA(i, j,\r\n IF(j=1,\r\n INDEX(months, MONTH(serial)),\r\n IF(j=2,\r\n YEAR(serial), \r\n ""\r\n )\r\n )\r\n )\r\n ),\r\n arrayMAKE(calendarRows+1, 7,\r\n LAMBDA(i, j,\r\n IF(i=1,\r\n INDEX(days, j, 1), \r\n INDEX(body, i-1, j)\r\n )\r\n )\r\n )\r\n)))',
},
84: { 1: "例" },
85: { 1: "日付：", 2: "2021-12-31T16:00:00.000Z", 4: "数式：" },
86: {
1: "マーク：",
2: false,
4: "結果："
}
},
formulas: { 85: { 5: "FORMULATEXT(F87)" }, 86: { 5: "CALENDAR(C86,C87)" } },
cellStyles: {
B1: 0,
"B3:H4": 1,
"B6:C6": 2,
"B7:C7": 3,
"B8:H9": 4,
"B11:H12": 1,
"B13:B14": 5,
"C13:C14": 6,
"D13:G14": 7,
"H13:H14": 8,
"B16:B17": 5,
"C16:C17": 6,
"D16:G17": 7,
"H16:H17": 8,
"B19:B22": 5,
"C19:C22": 6,
"D19:G22": 7,
"H19:H22": 8,
"B24:B25": 5,
"C24:C25": 6,
"D24:G25": 7,
"H24:H25": 8,
B26: 9,
C26: 10,
"D26:G26": 11,
H26: 12,
B27: 13,
C27: 14,
"D27:G27": 15,
H27: 16,
B28: 9,
C28: 10,
"D28:G28": 11,
H28: 12,
"B29:B39": 17,
"C29:C39": 18,
"D29:G39": 19,
"H29:H39": 20,
B40: 13,
C40: 14,
"D40:G40": 15,
H40: 16,
"B42:D42": 21,
B43: 2,
"B44:B83": 5,
"C44:H45": 22,
"C46:H83": 23,
B85: 21,
"B86:B87": 24,
"C86:C87": 25,
"E86:E87": 24,
"F86:L94": 3,
},
styles: {
records: [
{ font: 0 },
{ foreColor: 0, font: 1, wordWrap: true },
{ foreColor: 0, font: 1 },
{ backColor: 2, foreColor: 1, font: 1, border: [0, 0, 0, 0] },
{ backColor: 3, border: [1, 1, 1, 1] },
{ backColor: 5, foreColor: 4, font: 1, border: [2, 2, 2, 2] },
{ backColor: 6, font: 1, border: [2, null, 2, 2] },
{ backColor: 6, font: 1, border: [2, null, 2] },
{ backColor: 6, font: 1, border: [2, 2, 2] },
{ backColor: 5, foreColor: 4, font: 1, border: [2, 2, null, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [2, null, null, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [2] },
{ backColor: 6, font: 1, wordWrap: true, border: [2, 2] },
{ backColor: 5, foreColor: 4, font: 1, border: [null, 2, 2, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [null, null, 2, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [null, null, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [null, 2, 2] },
{ backColor: 5, foreColor: 4, font: 1, border: [null, 2, null, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [null, null, null, 2] },
{ backColor: 6, font: 1, wordWrap: true },
{ backColor: 6, font: 1, wordWrap: true, border: [null, 2] },
{ foreColor: 0, font: 2, border: [null, null, 3] },
{ backColor: 6, font: 1, border: [2, 2, 2, 2] },
{ backColor: 6, font: 1, wordWrap: true, border: [2, 2, 2, 2] },
{ backColor: 7, foreColor: 4, border: [2, 2, 2, 2] },
{ backColor: 9, foreColor: 8, border: [0, 0, 0, 0] },
],
borders: [
{ color: "#7f7f7f", style: 1 },
{ color: "#b2b2b2", style: 1 },
{ color: "#000000", style: 1 },
{ color: "#accdea", style: 5 },
],
colors: ["#44546a", "#fa7d00", "#f2f2f2", "#ffffcc", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99"],
fonts: ["700 24px Calibri", "700 14.7px Calibri", "700 17.3px Calibri"],
},
others: {
columnWidth: { 0: 36, 1: 78, 2: 83 },
rowHeight: { 0: 31, 41: 24, 42: 21, 84: 24, 85: 21 },
spans: [
"C16:H16",
"C17:H17",
"C47:H83",
"C44:H44",
"C45:H45",
"C13:H13",
"C14:H14",
"C46:H46",
"B47:B83",
"C24:H24",
"C25:H25",
"B26:B27",
"C26:H27",
"B28:B40",
"C28:H40",
"B3:H4",
"B11:H12",
"C19:H19",
"C20:H20",
"C21:H21",
"C22:H22",
],
},
},
NiceAxis: {
values: {
0: { 1: "NiceAxis関数" },
2: {
1: "この例のLAMBDA関数は、グラフ化するデータの最小値と最大値から、適切な軸の境界と増分値を計算する。",
},
5: { 1: "NiceAxisの構文：" },
6: { 1: "=NiceAxis(min, max)" },
7: { 1: "min： グラフ化するデータの最小値。" },
8: { 1: "max： グラフ化するデータの最大値。" },
10: { 1: "LAMBDAの実装" },
11: { 1: "［名前の管理］で以下のように定義します：" },
12: { 1: "名前：", 2: "Calendar" },
13: { 1: "範囲：", 2: "Workbook" },
14: { 1: "コメント：", 2: "グラフの適切な軸の境界と増分値を計算します" },
15: {
1: "参照：",
2: "=LAMBDA(min_0,max_0,\r\n LET(\r\n min_1,MIN(min_0,max_0),\r\n max_1,MAX(min_0,max_0),\r\n delta,IF(min_1=max_1,9,max_1-min_1),\r\n min_2,\r\n IF(min_1=0,\r\n 0,\r\n IF(min_1>0,\r\n MAX(0,min_1-delta/100),\r\n min_1-delta/100\r\n )\r\n ),\r\n max_2,\r\n IF(max_1=0,\r\n IF(min_1=0,1,0),\r\n IF(max_1<0,\r\n MIN(0,max_1+delta/100),\r\n max_1+delta/100\r\n )\r\n ),\r\n power,LOG10(max_2-min_2),\r\n factor,10^(power-INT(power)),\r\n major_3,\r\n XLOOKUP(\r\n factor,\r\n {0,2.1,5,10},\r\n {0.2,0.5,1,2},,\r\n -1\r\n )*10^INT(power),\r\n min_3,major_3*INT(min_2/major_3),\r\n max_3,\r\n major_3*\r\n IF(max_2/major_3=INT(max_2/major_3),\r\n max_2/major_3,\r\n INT(max_2/major_3)+1\r\n ),\r\n CHOOSE({1;2;3},min_3,max_3,major_3)\r\n )\r\n)",
},
56: { 1: "例" },
57: { 1: "データ", 3: "数式：" },
58: { 1: 12.5, 3: "最小値" },
59: { 1: 23.6, 3: "最大値" },
60: { 1: 22.8, 3: "増分値" },
61: { 1: 38.3 },
62: { 1: 92.6 },
63: { 1: 37.8 },
64: { 1: 42.9 },
65: { 1: 83.7 },
66: { 1: 16.9 },
67: { 1: 44.8 },
},
formulas: { 57: { 4: "FORMULATEXT(E59)" }, 58: { 4: "NICEAXIS(MIN(B59:B68),MAX(B59:B68))" } },
cellStyles: {
B1: 0,
"B3:H4": 1,
"B6:C6": 2,
"B7:C7": 3,
"B8:F9": 4,
B10: 2,
"B11:D11": 5,
B12: 2,
"B13:B55": 6,
"C13:G14": 7,
"C15:G55": 8,
B57: 5,
B58: 9,
"D58:D61": 10,
"E58:E61": 3,
"F58:G58": 3,
"B59:B68": 11,
},
styles: {
records: [
{ font: 0 },
{ foreColor: 0, font: 1, wordWrap: true },
{ foreColor: 0, font: 1 },
{ backColor: 2, foreColor: 1, font: 1, border: [0, 0, 0, 0] },
{ backColor: 3, border: [1, 1, 1, 1] },
{ foreColor: 0, font: 2, border: [null, null, 2] },
{ backColor: 5, foreColor: 4, font: 1, border: [3, 3, 3, 3] },
{ backColor: 6, font: 1, border: [3, 3, 3, 3] },
{ backColor: 6, font: 1, wordWrap: true, border: [3, 3, 3, 3] },
{ backColor: 7, foreColor: 4, font: 1 },
{ backColor: 7, foreColor: 4, font: 1, border: [3, 3, 3, 3] },
{ backColor: 9, foreColor: 8, border: [0, 0, 0, 0] },
],
borders: [
{ color: "#7f7f7f", style: 1 },
{ color: "#b2b2b2", style: 1 },
{ color: "#accdea", style: 5 },
{ color: "#000000", style: 1 },
],
colors: ["#44546a", "#fa7d00", "#f2f2f2", "#ffffcc", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99"],
fonts: ["700 24px Calibri", "700 14.7px Calibri", "700 17.3px Calibri"],
},
others: {
columnWidth: { 0: 36, 1: 86, 6: 130 },
rowHeight: { 0: 31, 10: 24, 11: 21, 56: 24, 57: 21 },
spans: ["B3:H4", "B16:B55", "C16:G55", "C13:G13", "C14:G14", "C15:G15"],
},
},
customNames: {
arrayMAKE: "LAMBDA(r,c,f, LET(seq, SEQUENCE(r,c), IF(seq, LET(i, ROUNDDOWN((seq-1)/c, 0)+1, j, MOD(seq-1,c)+1, f(i,j)))))",
Calendar:
'LAMBDA(serial,mark, LET(daysInMonth, EOMONTH(serial, 0)-EOMONTH(serial, -1), foMonth, DATE(YEAR(serial), MONTH(serial), 1), dayPadding, WEEKDAY(foMonth, 2)-1, calendarRows, ROUNDUP((dayPadding+daysInMonth)/7, 0), body, arrayMAKE(calendarRows, 7, LAMBDA(i,j, LET(seqNum, ((i-1)*7+j)-dayPadding, IFS(seqNum<=0, "", seqNum=DAY(serial), IF(mark, "X", seqNum), seqNum<=daysInMonth, seqNum, TRUE, "" ) ) ) ), vcat( arrayMAKE(1, 7, LAMBDA(i,j, IF(j=1, INDEX(months, MONTH(serial)), IF(j=2, YEAR(serial), "" ) ) ) ), arrayMAKE(calendarRows+1, 7, LAMBDA(i,j, IF(i=1, INDEX(days, j, 1), INDEX(body, i-1, j) ) ) ) )))',
days: '{"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}',
getDay: "LAMBDA(serial, INDEX(days, WEEKDAY(serial, 2), 1))",
HEAD: 'LAMBDA(str, IF(str="", "", LEFT(str,1)))',
IsPalindrome: 'LAMBDA(str, LET( replaceStr, ReplaceChars(str, "!@#$%^&*()[]<>-?.,\'"" ", ""), lowStr, LOWER(replaceStr), lowStr = REVERSE(lowStr) ) )',
months: '{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}',
myFact: "LAMBDA(num, IF(num<2, 1, num * myFact(num - 1) ) )",
NiceAxis:
"LAMBDA(min_0,max_0, LET( min_1,MIN(min_0,max_0), max_1,MAX(min_0,max_0), delta,IF(min_1=max_1,9,max_1-min_1), min_2, IF(min_1=0, 0, IF(min_1>0, MAX(0,min_1-delta/100), min_1-delta/100 ) ), max_2, IF(max_1=0, IF(min_1=0,1,0), IF(max_1<0, MIN(0,max_1+delta/100), max_1+delta/100 ) ), power,LOG10(max_2-min_2), factor,10^(power-INT(power)), major_3, XLOOKUP( factor, {0,2.1,5,10}, {0.2,0.5,1,2},, -1 )*10^INT(power), min_3,major_3*INT(min_2/major_3), max_3, major_3* IF(max_2/major_3=INT(max_2/major_3), max_2/major_3, INT(max_2/major_3)+1 ), CHOOSE({1;2;3},min_3,max_3,major_3) ) )",
ReplaceChars: 'LAMBDA(str,chars,sub, IF(chars="", str, ReplaceChars( SUBSTITUTE(str, LEFT(chars), sub), MID(chars,2,LEN(chars) - 1), sub ) ) )',
REVERSE: "LAMBDA(str, IF(LEN(str)<2, str, REVERSE(TAIL(str)) & HEAD(str) ) )",
TAIL: 'LAMBDA(str, IF(str="", "", RIGHT(str, LEN(str) - 1)))',
vcat: "LAMBDA(top,bot, LET(width, MIN(COLUMNS(top), COLUMNS(bot)), topH, ROWS(top), arrayMAKE(ROWS(bot)+topH, width, LAMBDA(i,j, IF(i <= topH, INDEX(top, i, j), INDEX(bot, i-topH, j) ) ) ) ) )",
},
};
window.onload = function () {
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
// add custom name
foreachObj(data.customNames, function (name, formula) {
workbook.addCustomName(name, formula);
});
workbook.setSheetCount(3);
workbook.sheets[0].name("Recursive");
workbook.sheets[1].name("Calendar");
workbook.sheets[2].name("NiceAxis");
initSheet(workbook.getSheet(0), data.Recursive);
initSheet(workbook.getSheet(1), data.Calendar);
initSheet(workbook.getSheet(2), data.NiceAxis);
workbook.getSheet(1).setValue(85, 2, new Date(2022, 0, 1));
workbook.resumePaint();
};
function initSheet(sheet, data) {
setSheetPr(sheet, data);
setCells(sheet, data);
}
function setCells(sheet, data) {
foreachObj(data.values, function (r, row) {
foreachObj(row, function (c, v) {
setValue(sheet, Number(r), Number(c), v);
});
});
foreachObj(data.formulas, function (r, row) {
foreachObj(row, function (c, v) {
setFormula(sheet, Number(r), Number(c), v);
});
});
foreachObj(data.cellStyles, function (ref, id) {
setStyle(sheet, ref, data.styles.records[id], data.styles);
});
}
function setValue(sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setValue(r, c, v);
}
function setFormula(sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setFormula(r, c, v);
}
function setStyle(sheet, ref, v, styles) {
if (v === undefined || v === null) return;
var range = sheet.getRange(ref);
var foreColor = styles.colors[v.foreColor];
var backColor = styles.colors[v.backColor];
var font = styles.fonts[v.font];
var wordWrap = v.wordWrap;
if (foreColor) {
range.foreColor(foreColor);
}
if (backColor) {
range.backColor(backColor);
}
if (font) {
range.font(font);
}
if (wordWrap) {
range.wordWrap(wordWrap);
}
var border = v.border || [];
var borderTop = styles.borders[border[0]];
var borderRight = styles.borders[border[1]];
var borderBottom = styles.borders[border[2]];
var borderLeft = styles.borders[border[3]];
if (borderTop) {
range.borderTop(createLineStyle(borderTop));
}
if (borderBottom) {
range.borderBottom(createLineStyle(borderBottom));
}
if (borderLeft) {
range.borderLeft(createLineStyle(borderLeft));
}
if (borderRight) {
range.borderRight(createLineStyle(borderRight));
}
}
function setSheetPr(sheet, data) {
// set column width
foreachObj(data.others.columnWidth, function (index, v) {
sheet.setColumnWidth(Number(index), v);
});
// set row height
foreachObj(data.others.rowHeight, function (index, v) {
sheet.setRowHeight(Number(index), v);
});
// set spans
var spans = data.others.spans || [];
for (var i = 0; i < spans.length; i++) {
var range = sheet.getRange(spans[i]);
sheet.addSpan(range.row, range.col, range.rowCount, range.colCount);
}
// set tables
var tables = data.others.tables || [];
for (var i = 0; i < tables.length; i++) {
var table = tables[i];
var range = sheet.getRange(table.ref);
sheet.tables.add(table.name, range.row, range.col, range.rowCount, range.colCount);
}
}
function createLineStyle(v) {
return new GC.Spread.Sheets.LineBorder(v.color, v.style);
}
function foreachObj(obj, func) {
if (!obj) return;
var keys = Object.keys(obj);
for (var i = 0; i < keys.length; i++) {
var key = keys[i];
var v = obj[key];
func(key, v);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
