Excelファイルの数式を管理する
紹介
Microsoft Excelの魅力的な機能の一つは、数式や関数を使ったデータ処理能力です。Excelは多くの組み込み関数と数式を提供し、複雑な計算を迅速に行えます。Aspose.Cellsもまた、値の計算を支援する多くの組み込み関数と数式を提供し、アドイン関数もサポートしています。さらに、配列やR1C1数式もサポートしています。
数式と関数の使用方法
Aspose.Cells は、Microsoft Excel ファイルを表す Workbook クラスを提供します。Workbook クラスには、Excel ファイル内の各ワークシートにアクセスするための Worksheets コレクションが含まれています。ワークシートは Worksheet クラスで表されます。Worksheet クラスは Cells コレクションを提供します。Cells コレクション内の各アイテムは、Cell クラスのオブジェクトを表します。
詳細については、Cell クラスが提供するプロパティとメソッドを使用してセルに数式を適用することが可能です。
- 組み込み関数の使用。
- アドイン関数の使用。
- 配列式の操作。
- R1C1 数式の作成。
組み込み関数の使用方法
組み込み関数や数式は、開発者の労力と時間を削減するために提供される使用準備が整った関数です。Aspose.Cells でサポートされる 組み込み関数のリスト。関数はアルファベット順にリストされています。将来的には、さらに多くの関数がサポートされる予定です。
Aspose.Cells は、Microsoft Excel が提供するほとんどの数式や関数をサポートしています。開発者は、API または デザイナースプレッドシート を介してこれらの数式を使用できます。Aspose.Cells は、数学、文字列、真偽値、日付/時刻、統計、データベース、検索と参照などの豊富な数式をサポートしています。
セルに数式を追加するためには、Cell クラスの Formula プロパティを使用します。例えば 複雑な数式 など
= H7*(1+IF(P7 = $L$3,$M$3, (IF(P7=$L$4,$M$4,0))))、Aspose.Cellsでは複雑な数式もサポートされています。セルに数式を適用する際は、常に文字列を等号（=）で始めてマイクロソフトExcelで数式を作成するときと同様に関数パラメータを区切るためにコンマ（,）を使用してください。
以下の例では、ワークシートの Cells コレクションの最初のセルに複雑な数式が適用されています。この数式は、Aspose.Cells が提供する組み込みの IF 関数を使用しています。
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // The path to the documents directory. | |
| string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
| // Create directory if it is not already present. | |
| bool IsExists = System.IO.Directory.Exists(dataDir); | |
| if (!IsExists) | |
| System.IO.Directory.CreateDirectory(dataDir); | |
| // Instantiating a Workbook object | |
| Workbook workbook = new Workbook(); | |
| // Adding a new worksheet to the Excel object | |
| int sheetIndex = workbook.Worksheets.Add(); | |
| // Obtaining the reference of the newly added worksheet by passing its sheet index | |
| Worksheet worksheet = workbook.Worksheets[0]; | |
| // Adding a value to "A1" cell | |
| worksheet.Cells["A1"].PutValue(1); | |
| // Adding a value to "A2" cell | |
| worksheet.Cells["A2"].PutValue(2); | |
| // Adding a value to "A3" cell | |
| worksheet.Cells["A3"].PutValue(3); | |
| // Adding a SUM formula to "A4" cell | |
| worksheet.Cells["A4"].Formula = "=SUM(A1:A3)"; | |
| // Calculating the results of formulas | |
| workbook.CalculateFormula(); | |
| // Get the calculated value of the cell | |
| string value = worksheet.Cells["A4"].Value.ToString(); | |
| // Saving the Excel file | |
| workbook.Save(dataDir + "output.xls"); |
アドイン関数の使用方法
Excel アドインとして含めたいユーザー定義の数式を持つことができます。セル.Formula 関数を使用すると組み込み関数は正常に動作しますが、アドイン関数を使用してカスタム関数や数式を設定する必要があります。
Aspose.Cells は、Worksheets.RegisterAddInFunction() を使用してアドイン関数を登録する機能を提供します。その後、cell.Formula = anyFunctionFromAddIn と設定すると、出力される Excel ファイルには、アドイン関数から計算された値が含まれます。
以下のサンプルコードの中でアドイン関数を登録するために XLAM ファイルをダウンロードできます。同様に、出力ファイル “test_udf.xlsx” もダウンロードして出力を確認することができます。
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // Create empty workbook | |
| Workbook workbook = new Workbook(); | |
| // Register macro enabled add-in along with the function name | |
| int id = workbook.Worksheets.RegisterAddInFunction(sourceDir + @"TESTUDF.xlam", "TEST_UDF", false); | |
| // Register more functions in the file (if any) | |
| workbook.Worksheets.RegisterAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file | |
| // Access first worksheet | |
| Worksheet worksheet = workbook.Worksheets[0]; | |
| // Access first cell | |
| var cell = worksheet.Cells["A1"]; | |
| // Set formula name present in the add-in | |
| cell.Formula = "=TEST_UDF()"; | |
| // Save workbook to output XLSX format. | |
| workbook.Save(outputDir + @"test_udf.xlsx", Aspose.Cells.SaveFormat.Xlsx); |
配列式の使用方法
配列式は、数式を構成する関数に個々の数値ではなく配列を引数として取る数式です。配列式が表示されると、括弧({})で囲まれています。
いくつかのMicrosoft Excel関数は値の配列を返します。配列数式を使用して複数の結果を計算するには、配列を配列引数と同じ行数および列数のセル範囲に入力してください。
配列式をセルに適用するには、Cell クラスの SetArrayFormula メソッドを呼び出します。SetArrayFormula メソッドは以下のパラメータを取ります:
- 配列数式、配列数式。
- 行数、配列数式の結果を設定する行数。
- 列数, 配列式の結果を入力する列数。
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // The path to the documents directory. | |
| string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
| // Create directory if it is not already present. | |
| bool IsExists = System.IO.Directory.Exists(dataDir); | |
| if (!IsExists) | |
| System.IO.Directory.CreateDirectory(dataDir); | |
| // Instantiating a Workbook object | |
| Workbook workbook = new Workbook(); | |
| // Adding a new worksheet to the Excel object | |
| int sheetIndex = workbook.Worksheets.Add(); | |
| // Obtaining the reference of the newly added worksheet by passing its sheet index | |
| Worksheet worksheet = workbook.Worksheets[sheetIndex]; | |
| // Adding a value to "A1" cell | |
| worksheet.Cells["A1"].PutValue(1); | |
| // Adding a value to "A2" cell | |
| worksheet.Cells["A2"].PutValue(2); | |
| // Adding a value to "A3" cell | |
| worksheet.Cells["A3"].PutValue(3); | |
| // Adding a value to B1 | |
| worksheet.Cells["B1"].PutValue(4); | |
| // Adding a value to "B2" cell | |
| worksheet.Cells["B2"].PutValue(5); | |
| // Adding a value to "B3" cell | |
| worksheet.Cells["B3"].PutValue(6); | |
| // Adding a value to C1 | |
| worksheet.Cells["C1"].PutValue(7); | |
| // Adding a value to "C2" cell | |
| worksheet.Cells["C2"].PutValue(8); | |
| // Adding a value to "C3" cell | |
| worksheet.Cells["C3"].PutValue(9); | |
| // Adding a SUM formula to "A4" cell | |
| worksheet.Cells["A6"].SetArrayFormula("=LINEST(A1:A3,B1:C3,TRUE,TRUE)", 5, 3); | |
| // Calculating the results of formulas | |
| workbook.CalculateFormula(); | |
| // Get the calculated value of the cell | |
| string value = worksheet.Cells["A6"].Value.ToString(); | |
| // Saving the Excel file | |
| workbook.Save(dataDir + "output.xls"); |
R1C1 数式の使用方法
セルに R1C1 参照スタイルの数式を Cell クラスの R1C1Formula プロパティを使用して追加します。
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // The path to the documents directory. | |
| string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
| // Instantiating a Workbook object | |
| Workbook workbook = new Workbook(dataDir + "Book1.xls"); | |
| Worksheet worksheet = workbook.Worksheets[0]; | |
| // Setting an R1C1 formula on the "A11" cell, | |
| // Row and Column indeces are relative to destination index | |
| worksheet.Cells["A11"].R1C1Formula = "=SUM(R[-10]C[0]:R[-7]C[0])"; | |
| // Saving the Excel file | |
| workbook.Save(dataDir + "output.xls"); |
高度なトピック
- 先行および後行
- 数式で外部リンクを設定する
- 新しい行にデータを入力する際に、表やリストオブジェクトの式を自動的に伝播させる
- 名前付き範囲の式の設定
- 数式の設定 - 英語以外のユーザーへの通知
- 共有数式の設定
- 共有式の最大行数を指定
- サポートされているExcel関数