能否在ExcelScript中使用Excel工作表函数？及调用方法咨询

Great question! The short answer is yes, you absolutely can use Excel worksheet functions in ExcelScript—but the approach is a bit different from VBA, which is likely why you hit a wall with the Application object. Let’s walk through the two primary methods to make this work, along with some key differences from VBA.

1. Assign Worksheet Functions to Cell Formulas (Just Like Manual Entry) #

This is the most straightforward approach if you want the function’s result to live directly in a cell. You simply set the formula string for a range, just as you would if you were typing it into Excel’s formula bar.

Here’s an example:

function main(workbook: ExcelScript.Workbook) {
  const activeSheet = workbook.getActiveWorksheet();
  
  // Set a SUM formula for cell A1, calculating values from B1:B10
  const sumCell = activeSheet.getRange("A1");
  sumCell.setFormula("=SUM(B1:B10)");
  
  // Use AVERAGE for another cell
  const avgCell = activeSheet.getRange("A2");
  avgCell.setFormula("=AVERAGE(C1:C5)");
  
  // Even complex functions like VLOOKUP work here
  const lookupCell = activeSheet.getRange("A3");
  lookupCell.setFormula('=VLOOKUP("ProductX", D1:E10, 2, FALSE)');
}

This method supports all Excel worksheet functions because it’s using the exact same formula syntax you know from the Excel UI. It’s perfect when you need the formula to remain in the cell for future updates.

2. Use ExcelScript’s Built-in Worksheet Function Methods (In-Memory Calculations) #

If you don’t need to leave the formula in a cell—you just want to calculate a value to use in your code logic—ExcelScript provides a set of built-in methods that mirror many worksheet functions. These are accessed through the WorksheetFunction object, which you get from the Application instance.

Here’s how to use it:

function main(workbook: ExcelScript.Workbook) {
  const app = workbook.getApplication();
  const worksheetFunctions = app.getWorksheetFunction();
  
  // Calculate a sum of hardcoded values
  const directSum = worksheetFunctions.sum([1, 3, 5, 7]);
  console.log("Direct sum result:", directSum); // Outputs 16
  
  // Calculate sum from a range of cells
  const dataRange = workbook.getActiveWorksheet().getRange("B1:B10").getValues();
  const rangeSum = worksheetFunctions.sum(dataRange);
  console.log("Range sum result:", rangeSum);
  
  // Use VLOOKUP to retrieve a value programmatically
  const lookupRange = workbook.getActiveWorksheet().getRange("D1:E10");
  const lookupResult = worksheetFunctions.vlookup("ProductX", lookupRange, 2, false);
  console.log("VLOOKUP result:", lookupResult);
}

A quick note: Not every single Excel worksheet function has a direct equivalent in the WorksheetFunction object. If you can’t find the function you need here, fall back to the cell formula method—it’ll work every time.

Key Difference from VBA #

In VBA, you can call Application.WorksheetFunction directly. In ExcelScript, you have to first get the Application object via workbook.getApplication(), then access the WorksheetFunction from it. That’s why your initial attempt to call functions directly on the Application object didn’t work—you were missing that extra step!

内容的提问来源于stack exchange，提问作者etalon11