구문
parameter - (선택 사항) 함수로 전달하려는 값(예: 셀 참조, 문자열 또는 숫자). 최대 253개 매개 변수를 입력할 수 있습니다.
calculation - (필수) 실행하여 함수의 결과로 반환하려는 수식. 이것은 마지막 인수여야 하며 결과를 반환해야 합니다.
팁
여기서는 예시의 동적 배열 수식을 사용했으며, 사용자는 다음 코드를 사용하여 활성화해야 합니다.
기본 용도
셀에서 Lambda 함수 만들기
Lambda를 이름 관리자에 추가한 다음 셀에서 사용합니다.
예시
화씨를 섭씨로 변환
빗변 찾기
단어 수 계산
구의 체적 계산
var data = {
values: {
1: { 1: "Examples" },
3: { 1: "Example 1: Convert Farenheit to Celsius", 8: "Convert Celsius to Farenheit" },
4: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
5: { 1: "Name:", 3: "ToCelsius", 8: "Name:", 10: "ToFarenheit" },
6: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
7: {
1: "Comment:",
3: "Convert a Farenheit temperature to Celsius",
8: "Comment:",
10: "Convert a Celsius temperature to Farenheit",
},
8: {
1: "Refers To:",
3: "=LAMBDA(temp,(5/9)*(temp-32))",
8: "Refers To:",
10: "=LAMBDA(temp,(9/5)*temp+32)",
},
9: { 1: "Data", 3: "Formula", 4: "Result", 8: "Data", 10: "Formula", 11: "Result" },
10: { 1: 104, 8: 40 },
11: { 1: 86, 8: 30 },
12: { 1: 68, 8: 20 },
13: { 1: 50, 8: 10 },
14: { 1: 32, 8: 0 },
16: { 1: "Example 2: Find the hypotenuse", 8: "Find the hypotenuse using a range" },
17: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
18: { 1: "Name:", 3: "Hypotenuse", 8: "Name:", 10: "Hypotenuse2" },
19: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
20: {
1: "Comment:",
3: "Returns the length of the hypotenuse of a right triangle",
8: "Comment:",
10: "Returns the length of the hypotenuse of a right triangle",
},
21: {
1: "Refers To:",
3: "=LAMBDA(a,b,SQRT(a^2+b^2))",
8: "Refers To:",
10: "=LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))",
},
22: {
1: "Data",
3: "Formula",
4: "Result",
8: "Data",
10: "Formula",
11: "Result",
12: "Result2",
15: "Same data inverted, refereced in columns:",
},
23: { 1: 3, 2: 4, 8: 3, 9: 4, 14: "Data", 15: 3, 16: 5, 17: 7, 18: 9 },
24: { 1: 5, 2: 12, 8: 5, 9: 12, 15: 4, 16: 12, 17: 24, 18: 40 },
25: { 1: 7, 2: 24, 8: 7, 9: 24, 14: "Result" },
26: { 1: 9, 2: 40, 8: 9, 9: 40, 14: "Result2" },
27: { 10: "This version makes the 2nd parameter optional and calculates the result using a 2-cell range." },
29: { 1: "Example 3: Count words", 8: "Count words in a range" },
30: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
31: { 1: "Name:", 3: "CountWords", 8: "Name:", 10: "CountWordsRange" },
32: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
33: {
1: "Comment:",
3: "Returns the word count in a text string",
8: "Comment:",
10: "Returns the word count in a cell range",
},
34: {
1: "Refers To:",
3: '=LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))',
8: "Refers To:",
10: "=LAMBDA(range,SUM(CountWords(range)))",
},
36: { 1: "Data", 3: "Formula", 4: "Result", 8: "Data", 10: "Formula:", 11: "Result:" },
37: { 1: "Something wicked this way comes.", 8: "Something wicked this way comes." },
38: { 1: "I came, I saw, I conquered.", 8: "I came, I saw, I conquered." },
39: {
1: "A quick brown fox jumped over the lazy dog.",
8: "A quick brown fox jumped over the lazy dog.",
},
40: { 1: "Use the Force, Luke!", 8: "Use the Force, Luke!" },
43: { 1: "Example 4: Find the date for Thanksgiving", 8: "Find the date for Easter" },
44: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
45: { 1: "Name:", 3: "ThanksgivingDate", 8: "Name:", 10: "EasterDate" },
46: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
47: {
1: "Comment:",
3: "Returns the date Thanksgiving in the USA falls on for a given year",
8: "Comment:",
10: "Returns the date Easter in the USA falls on for a given year",
},
48: {
1: "Refers To:",
3: '=LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))',
8: "Refers To:",
10: '=LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)',
},
49: { 8: "Date", 10: "Formula", 11: "Result" },
50: { 1: "Data", 3: "Formula", 4: "Result" },
57: { 1: "Example 5: Get the duplicate values in a range or array", 8: "Generate a random GUID" },
58: { 1: "Define the following in Name Manager:", 8: "Define the following in Name Manager:" },
59: { 1: "Name:", 3: "GetDuplicates", 8: "Name:", 10: "Guid" },
60: { 1: "Scope:", 3: "Workbook", 8: "Scope:", 10: "Workbook" },
61: {
1: "Comment:",
3: "Gets the duplicate values in a range or array",
8: "Comment:",
10: "Generate a random GUID",
},
62: {
1: "Refers To:",
3: "=LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))",
8: "Refers To:",
10: '=LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))',
},
64: { 1: "Data", 3: "Formula", 4: "Result" },
65: { 1: "jack" },
66: { 1: "jill" },
67: { 1: "jason" },
68: { 1: "jack", 8: "Formula" },
69: { 1: "alex", 8: "Result" },
70: { 1: "allen" },
71: { 1: "alex" },
73: { 1: "Example 6: Compute the volume of a sphere" },
74: { 1: "Define the following in Name Manager:" },
75: { 1: "Name:", 3: "SphereVolume" },
76: { 1: "Scope:", 3: "Workbook" },
77: { 1: "Comment:", 3: "Compute the volume of a sphere" },
78: { 1: "Refers To:", 3: "=LAMBDA(r, 4/3*PI()*r^3)" },
79: { 1: "Data", 3: "Formula", 4: "Result" },
},
formulas: {
10: { 3: "FORMULATEXT(E11)", 4: "TOCELSIUS(B11)", 10: "FORMULATEXT(L11)", 11: "TOFARENHEIT(I11)" },
11: { 3: "FORMULATEXT(E12)", 4: "TOCELSIUS(B12)", 10: "FORMULATEXT(L12)", 11: "TOFARENHEIT(I12)" },
12: { 3: "FORMULATEXT(E13)", 4: "TOCELSIUS(B13)", 10: "FORMULATEXT(L13)", 11: "TOFARENHEIT(I13)" },
13: { 3: "FORMULATEXT(E14)", 4: "TOCELSIUS(B14)", 10: "FORMULATEXT(L14)", 11: "TOFARENHEIT(I14)" },
14: { 3: "FORMULATEXT(E15)", 4: "TOCELSIUS(B15)", 10: "FORMULATEXT(L15)", 11: "TOFARENHEIT(I15)" },
23: {
3: "FORMULATEXT(E24)",
4: "HYPOTENUSE(B24,C24)",
10: "FORMULATEXT(L24)",
11: "HYPOTENUSE2(I24:J24,)",
12: "HYPOTENUSE2(I24,J24)",
},
24: {
3: "FORMULATEXT(E25)",
4: "HYPOTENUSE(B25,C25)",
10: "FORMULATEXT(L25)",
11: "HYPOTENUSE2(I25:J25,)",
12: "HYPOTENUSE2(I25,J25)",
},
25: {
3: "FORMULATEXT(E26)",
4: "HYPOTENUSE(B26,C26)",
10: "FORMULATEXT(L26)",
11: "HYPOTENUSE2(I26:J26,)",
12: "HYPOTENUSE2(I26,J26)",
15: "HYPOTENUSE2(P24:P25,)",
16: "HYPOTENUSE2(Q24:Q25,)",
17: "HYPOTENUSE2(R24:R25,)",
18: "HYPOTENUSE2(S24:S25,)",
},
26: {
3: "FORMULATEXT(E27)",
4: "HYPOTENUSE(B27,C27)",
10: "FORMULATEXT(L27)",
11: "HYPOTENUSE2(I27:J27,)",
12: "HYPOTENUSE2(I27,J27)",
15: "HYPOTENUSE2(P24,P25)",
16: "HYPOTENUSE2(Q24,Q25)",
17: "HYPOTENUSE2(R24,R25)",
18: "HYPOTENUSE2(S24,S25)",
},
37: {
3: "FORMULATEXT(E38)",
4: "COUNTWORDS(B38)",
10: "FORMULATEXT(L38)",
11: "COUNTWORDSRANGE(I38:J41)",
},
38: { 3: "FORMULATEXT(E39)", 4: "COUNTWORDS(B39)" },
39: { 3: "FORMULATEXT(E40)", 4: "COUNTWORDS(B40)" },
40: { 3: "FORMULATEXT(E41)", 4: "COUNTWORDS(B41)" },
50: { 8: "YEAR(NOW())", 10: "FORMULATEXT(L51)", 11: "EASTERDATE(I51)" },
51: {
1: "YEAR(NOW())",
3: "FORMULATEXT(E52)",
4: "THANKSGIVINGDATE(B52)",
8: "I51+1",
10: "FORMULATEXT(L52)",
11: "EASTERDATE(I52)",
},
52: {
1: "B52+1",
3: "FORMULATEXT(E53)",
4: "THANKSGIVINGDATE(B53)",
8: "I52+1",
10: "FORMULATEXT(L53)",
11: "EASTERDATE(I53)",
},
53: {
1: "B53+1",
3: "FORMULATEXT(E54)",
4: "THANKSGIVINGDATE(B54)",
8: "I53+1",
10: "FORMULATEXT(L54)",
11: "EASTERDATE(I54)",
},
54: {
1: "B54+1",
3: "FORMULATEXT(E55)",
4: "THANKSGIVINGDATE(B55)",
8: "I54+1",
10: "FORMULATEXT(L55)",
11: "EASTERDATE(I55)",
},
55: {
1: "B55+1",
3: "FORMULATEXT(E56)",
4: "THANKSGIVINGDATE(B56)",
8: "I55+1",
10: "FORMULATEXT(L56)",
11: "EASTERDATE(I56)",
},
65: { 3: "FORMULATEXT(E66)", 4: "GETDUPLICATES(B66:B72)" },
68: { 10: "FORMULATEXT(K70)" },
69: { 10: "GUID()" },
80: { 2: "SEQUENCE(11)", 3: "FORMULATEXT(E81)", 4: "SPHEREVOLUME(C81#)" },
},
cellStyles: {
"B2:C2": 0,
"B4:D4": 1,
"I4:K4": 1,
L4: 2,
"B5:E5": 3,
"I5:L5": 3,
"B6:B9": 4,
"C6:C9": 5,
"D6:D9": 6,
"E6:E9": 7,
"I6:I9": 4,
"J6:J9": 5,
"K6:K9": 6,
"L6:L9": 7,
B10: 8,
C10: 9,
"D10:E10": 10,
I10: 8,
J10: 9,
"K10:L10": 10,
B11: 11,
C11: 12,
D11: 13,
E11: 14,
I11: 11,
J11: 12,
"K11:L11": 13,
"B12:B14": 15,
"C12:C14": 16,
"D12:D14": 17,
"E12:E14": 18,
"I12:I14": 15,
"J12:J14": 16,
"K12:L15": 17,
B15: 19,
C15: 20,
D15: 21,
E15: 22,
I15: 19,
J15: 20,
"B17:D17": 1,
"E17:E18": 2,
"I17:K17": 1,
L17: 2,
"B18:E18": 2,
"I18:L18": 3,
"B19:B22": 4,
"C19:C22": 5,
"D19:D22": 6,
"E19:E22": 7,
"I19:I22": 4,
"J19:J22": 5,
"K19:K22": 6,
"L19:L22": 23,
"M19:M22": 7,
B23: 24,
C23: 25,
D23: 26,
E23: 27,
I23: 28,
"J23:L23": 29,
M23: 30,
"P23:S23": 31,
"B24:C27": 32,
"D24:E27": 17,
"I24:J27": 32,
"K24:M27": 17,
O24: 26,
"P24:S25": 32,
O25: 25,
"O26:O27": 26,
"P26:S27": 17,
"K28:M28": 33,
"B30:D30": 1,
"E30:E31": 2,
"I30:K30": 1,
"B31:E31": 2,
"I31:J31": 2,
"B32:B34": 4,
"C32:C34": 5,
"D32:D34": 6,
"E32:E34": 7,
"I32:I34": 4,
"J32:J34": 5,
"K32:K34": 6,
"L32:L34": 7,
B35: 34,
C35: 35,
D35: 36,
E35: 37,
I35: 34,
J35: 35,
K35: 38,
L35: 39,
B36: 40,
C36: 41,
D36: 42,
E36: 43,
I36: 40,
J36: 41,
K36: 44,
L36: 45,
B37: 46,
C37: 31,
D37: 25,
E37: 47,
I37: 24,
J37: 25,
"K37:L37": 48,
"B38:B41": 49,
"C38:C41": 16,
"D38:E41": 17,
"I38:I41": 49,
"J38:J41": 16,
"K38:L38": 17,
"B44:D44": 1,
"I44:K44": 1,
"L44:L45": 2,
B45: 2,
"I45:L45": 2,
"B46:B48": 4,
"C46:C48": 5,
"D46:D48": 6,
"E46:E48": 7,
"I46:I49": 4,
"J46:J49": 5,
"K46:K48": 6,
"L46:L48": 7,
B49: 34,
C49: 35,
D49: 36,
E49: 37,
K49: 50,
L49: 51,
B50: 40,
C50: 41,
D50: 42,
E50: 43,
"I50:J50": 52,
K50: 48,
L50: 53,
B51: 54,
C51: 52,
D51: 26,
E51: 27,
"I51:I56": 49,
"J51:J56": 16,
"K51:L56": 17,
"B52:B56": 49,
"C52:C56": 16,
"D52:E56": 17,
"B58:D58": 1,
"I58:K58": 1,
"L58:L59": 2,
B59: 2,
"I59:L59": 2,
"B60:B62": 4,
"C60:C62": 5,
"D60:D62": 6,
"E60:E62": 7,
"I60:I62": 4,
"J60:J62": 5,
"K60:K62": 6,
"L60:L62": 7,
B63: 34,
C63: 35,
D63: 36,
E63: 37,
I63: 34,
J63: 35,
K63: 36,
L63: 37,
B64: 40,
C64: 41,
D64: 42,
E64: 43,
"I64:I67": 55,
"J64:J67": 56,
"K64:K67": 57,
"L64:L67": 58,
B65: 59,
C65: 60,
"D65:E65": 10,
"B66:B72": 49,
C66: 16,
"D66:E67": 17,
"C67:C72": 61,
I68: 40,
J68: 41,
K68: 42,
L68: 43,
I69: 62,
J69: 63,
"K69:K70": 17,
I70: 8,
J70: 9,
"B74:D74": 1,
B75: 2,
"B76:B79": 4,
"C76:C79": 5,
"D76:D78": 6,
"E76:E78": 7,
D79: 50,
E79: 51,
B80: 59,
C80: 60,
"D80:E80": 64,
"B81:B91": 49,
"C81:C91": 16,
"D81:E91": 17,
},
styles: {
records: [
{ foreColor: 0, font: 0, border: [null, null, 0] },
{ foreColor: 0, font: 1, border: [null, null, 1] },
{ foreColor: 0, font: 1 },
{ foreColor: 0, font: 1, border: [null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2] },
{ backColor: 3, font: 1, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2, 2] },
{ backColor: 6, foreColor: 5, border: [null, null, 3, 2] },
{ backColor: 6, foreColor: 5, border: [null, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [null, 3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [null, 2, 3, 3] },
{ backColor: 6, foreColor: 5, border: [3, null, 3, 2] },
{ backColor: 6, foreColor: 5, border: [3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 3, 3, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 2, 3, 3] },
{ backColor: 6, foreColor: 5, border: [3, null, 2, 2] },
{ backColor: 6, foreColor: 5, border: [3, 3, 2] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 3, 2, 3] },
{ backColor: 8, foreColor: 7, font: 1, border: [3, 2, 2, 3] },
{ backColor: 3, font: 1, border: [2, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 4, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [4] },
{ backColor: 4, foreColor: 1, font: 1, border: [4, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, 3] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 4, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 3] },
{ backColor: 6, foreColor: 5, border: [3, 3, 3, 3] },
{ backColor: 9, border: [5, 5, 5, 5] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2] },
{ backColor: 3, font: 1, border: [2, null, null, 2] },
{ backColor: 3, font: 1, border: [2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2, 2] },
{ backColor: 3, font: 1, border: [null, null, 2, 2] },
{ backColor: 3, font: 1, border: [null, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, 3, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 4] },
{ backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2] },
{ backColor: 6, foreColor: 5, border: [3, null, 3, 3] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, null, 2, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [2, 2, 2] },
{ backColor: 4, foreColor: 1, font: 1 },
{ backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2, 4] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 4] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, null, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, null, null, 2] },
{ backColor: 3, font: 1, wordWrap: true, border: [null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 2] },
{ backColor: 6, foreColor: 5, border: [3, null, 3] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, null, null, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [2, 2] },
{ backColor: 4, foreColor: 1, font: 1, border: [null, 2, 2, 2] },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#000000", style: 1 },
{ color: "#7f7f7f", style: 1 },
{ color: "#9bc3e6", style: 1 },
{ color: "#b2b2b2", style: 1 },
],
colors: [
"#44546a",
"#ffffff",
"#4472c4",
"#d9e3f2",
"#5b9bd5",
"#3f3f76",
"#ffcc99",
"#fa7d00",
"#f2f2f2",
"#ffffcc",
],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: {
0: 30,
1: 86,
2: 86,
3: 186,
4: 179,
5: 39,
6: 39,
7: 39,
8: 86,
9: 86,
10: 275,
11: 187,
12: 112,
13: 39,
15: 68,
16: 68,
17: 68,
18: 68,
},
rowHeight: { 1: 24, 2: 21, 3: 21, 16: 21, 29: 21, 43: 21, 57: 21, 73: 21 },
spans: [
"B78:C78",
"B79:C79",
"B80:C80",
"B70:C70",
"I70:J70",
"B71:C71",
"B72:C72",
"B76:C76",
"B77:C77",
"K63:L68",
"B65:C65",
"B66:C66",
"B67:C67",
"B68:C68",
"B69:C69",
"I69:J69",
"B61:C61",
"I61:J61",
"B62:C62",
"I62:J62",
"B63:C64",
"D63:E64",
"I63:J68",
"B55:C55",
"I55:J55",
"B56:C56",
"I56:J56",
"I59:L59",
"B60:C60",
"I60:J60",
"B52:C52",
"I52:J52",
"B53:C53",
"I53:J53",
"B54:C54",
"I54:J54",
"B49:C50",
"D49:E50",
"I49:J49",
"K49:L49",
"I50:J50",
"B51:C51",
"I51:J51",
"B47:C47",
"D47:E47",
"I47:J47",
"B48:C48",
"D48:E48",
"I48:J48",
"B40:C40",
"I40:J40",
"B41:C41",
"I41:J41",
"I45:L45",
"B46:C46",
"D46:E46",
"I46:J46",
"B37:C37",
"I37:J37",
"B38:C38",
"I38:J38",
"B39:C39",
"I39:J39",
"B34:C34",
"I34:J34",
"K34:L34",
"B35:C36",
"D35:E36",
"I35:J36",
"K35:L36",
"B31:E31",
"I31:J31",
"B32:C32",
"I32:J32",
"K32:L32",
"B33:C33",
"I33:J33",
"K33:L33",
"B23:C23",
"I23:J23",
"P23:S23",
"O24:O25",
"B30:D30",
"I30:J30",
"B21:C21",
"D21:E21",
"I21:J21",
"B22:C22",
"D22:E22",
"I22:J22",
"B18:E18",
"I18:L18",
"B19:C19",
"D19:E19",
"I19:J19",
"B20:C20",
"D20:E20",
"I20:J20",
"B14:C14",
"I14:J14",
"B15:C15",
"I15:J15",
"B17:D17",
"I17:K17",
"B11:C11",
"I11:J11",
"B12:C12",
"I12:J12",
"B13:C13",
"I13:J13",
"B9:C9",
"D9:E9",
"I9:J9",
"K9:L9",
"B10:C10",
"I10:J10",
"B7:C7",
"D7:E7",
"I7:J7",
"K7:L7",
"B8:C8",
"D8:E8",
"I8:J8",
"K8:L8",
"B2:C2",
"I4:K4",
"B5:E5",
"I5:L5",
"B6:C6",
"D6:E6",
"I6:J6",
"K6:L6",
],
},
customNames: {
CountWords: 'LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))',
CountWordsRange: "LAMBDA(range,SUM(CountWords(range)))",
EasterDate: 'LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)',
GetDuplicates: "LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))",
Guid: 'LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))',
Hypotenuse: "LAMBDA(a,b,SQRT(a^2+b^2))",
Hypotenuse2: "LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))",
SphereVolume: "LAMBDA(r, 4/3*PI()*r^3)",
ThanksgivingDate:
'LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))',
ToCelsius: "LAMBDA(temp,(5/9)*(temp-32))",
ToFarenheit: "LAMBDA(temp,9/5*temp+32)",
},
};
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);
});
initSheet1(workbook.getSheet(0));
workbook.resumePaint();
};
function initSheet1(sheet) {
setSheetPr(sheet);
setCells(sheet);
}
function setCells(sheet) {
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]);
});
}
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) {
if (v === undefined || v === null) return;
var styles = data.styles;
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) {
// 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);
}
}
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="ko-kr"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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;
}
