Lambda関数

LAMBDA関数により、Excel独自の数式言語を使用してカスタム関数を定義できます。 次のデモでは、LAMBDA関数をSpreadJSで使用するいくつかの例を示します。

構文 parameter - (オプション) セルの参照、文字列、数値などの関数に渡したい値。パラメータは最大253個まで入力可能です。 calculation - (必須) 実行して、関数の結果を返したい数式。最後の引数に記述し、結果を返す必要があります。 ヒント この例では動的な配列式を使用していますが、次のコードで有効にする必要があります。 基本的な使い方 セル内にLambda関数を作成する Lambdaを[名前の管理]に追加し、セルで使用する 例 華氏を摂氏に変換する 斜辺を求める 単語を数える 球体の体積を計算する
var data = { values: { 1: { 1: "例" }, 3: { 1: "例1: 華氏を摂氏に変換する", 8: "摂氏を華氏に変換する" }, 4: { 1: "[名前の管理]で以下のように定義します:", 8: "[名前の管理]で以下のように定義します:" }, 5: { 1: "名前:", 3: "ToCelsius", 8: "名前:", 10: "ToFarenheit" }, 6: { 1: "範囲:", 3: "Workbook", 8: "範囲:", 10: "Workbook" }, 7: { 1: "コメント:", 3: "華氏温度から摂氏温度に変換する", 8: "コメント:", 10: "摂氏温度から華氏温度に変換する", }, 8: { 1: "参照:", 3: "=LAMBDA(temp,(5/9)*(temp-32))", 8: "参照:", 10: "=LAMBDA(temp,(9/5)*temp+32)", }, 9: { 1: "データ", 3: "数式", 4: "結果", 8: "データ", 10: "数式", 11: "結果" }, 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: "例2: 斜辺を求める", 8: "範囲を指定して斜辺を求める" }, 17: { 1: "[名前の管理]で以下のように定義します:", 8: "[名前の管理]で以下のように定義します:" }, 18: { 1: "名前:", 3: "Hypotenuse", 8: "名前:", 10: "Hypotenuse2" }, 19: { 1: "範囲:", 3: "Workbook", 8: "範囲:", 10: "Workbook" }, 20: { 1: "コメント:", 3: "直角三角形の斜辺の長さを返します", 8: "コメント:", 10: "直角三角形の斜辺の長さを返します", }, 21: { 1: "参照:", 3: "=LAMBDA(a,b,SQRT(a^2+b^2))", 8: "参照:", 10: "=LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))", }, 22: { 1: "データ", 3: "数式", 4: "結果", 8: "データ", 10: "数式", 11: "結果", 12: "Result2", 15: "同じデータを反転させ、列で参照する:", }, 23: { 1: 3, 2: 4, 8: 3, 9: 4, 14: "データ", 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: "結果" }, 26: { 1: 9, 2: 40, 8: 9, 9: 40, 14: "Result2" }, 27: { 10: "このバージョンでは、2番目のパラメータをオプションとし、2セルの範囲を使って結果を計算しました。" }, 29: { 1: "例3: 単語を数える", 8: "範囲内の単語を数える" }, 30: { 1: "[名前の管理]で以下のように定義します:", 8: "[名前の管理]で以下のように定義します:" }, 31: { 1: "名前:", 3: "CountWords", 8: "名前:", 10: "CountWordsRange" }, 32: { 1: "範囲:", 3: "Workbook", 8: "範囲:", 10: "Workbook" }, 33: { 1: "コメント:", 3: "テキスト文字列中の単語数を返す", 8: "コメント:", 10: "セル範囲内の単語数を返す", }, 34: { 1: "参照:", 3: '=LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))', 8: "参照:", 10: "=LAMBDA(range,SUM(CountWords(range)))", }, 36: { 1: "データ", 3: "数式", 4: "結果", 8: "データ", 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: "例4: 感謝祭の日付を探す", 8: "復活祭の日付を探す" }, 44: { 1: "[名前の管理]で以下のように定義します:", 8: "[名前の管理]で以下のように定義します:" }, 45: { 1: "名前:", 3: "ThanksgivingDate", 8: "名前:", 10: "EasterDate" }, 46: { 1: "範囲:", 3: "Workbook", 8: "範囲:", 10: "Workbook" }, 47: { 1: "コメント:", 3: "指定された年のアメリカの感謝祭の日付を返します", 8: "コメント:", 10: "指定された年のアメリカの復活祭の日付を返します", }, 48: { 1: "参照:", 3: '=LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))', 8: "参照:", 10: '=LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)', }, 49: { 8: "Date", 10: "数式", 11: "結果" }, 50: { 1: "データ", 3: "数式", 4: "結果" }, 57: { 1: "例5: 範囲または配列内の重複する値を取得する", 8: "ランダムなGUIDを生成する" }, 58: { 1: "[名前の管理]で以下のように定義します:", 8: "[名前の管理]で以下のように定義します:" }, 59: { 1: "名前:", 3: "GetDuplicates", 8: "名前:", 10: "Guid" }, 60: { 1: "範囲:", 3: "Workbook", 8: "範囲:", 10: "Workbook" }, 61: { 1: "コメント:", 3: "範囲または配列内の重複する値を取得する", 8: "コメント:", 10: "ランダムなGUIDを生成する", }, 62: { 1: "参照:", 3: "=LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))", 8: "参照:", 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: "データ", 3: "数式", 4: "結果" }, 65: { 1: "jack" }, 66: { 1: "jill" }, 67: { 1: "jason" }, 68: { 1: "jack", 8: "数式" }, 69: { 1: "alex", 8: "結果" }, 70: { 1: "allen" }, 71: { 1: "alex" }, 73: { 1: "例6: 球体の体積を計算する" }, 74: { 1: "[名前の管理]で以下のように定義します:" }, 75: { 1: "名前:", 3: "SphereVolume" }, 76: { 1: "範囲:", 3: "Workbook" }, 77: { 1: "コメント:", 3: "球体の体積を計算する" }, 78: { 1: "参照:", 3: "=LAMBDA(r, 4/3*PI()*r^3)" }, 79: { 1: "データ", 3: "数式", 4: "結果" }, }, 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="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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/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; }