Hinzufügen von Verweismethoden zu Zellwerten

Fügen Sie Verweismethoden zu Zellwerten hinzu, um Benutzern den Zugriff auf dynamische Berechnungen basierend auf dem Zellwert zu gewähren. Die EntityCellValue Typen und LinkedEntityCellValue unterstützen Verweismethoden. Fügen Sie z. B. eine Methode zu einem Produktentitätswert hinzu, der seine Gewichtung in verschiedene Einheiten konvertiert.

Die folgende Abbildung zeigt ein Beispiel für das Hinzufügen einer ConvertWeight Methode zu einem Produktentitätswert, der eine Pfannenkuchenmischung darstellt.

Die DoubleCellValueTypen , BooleanCellValueund StringCellValue unterstützen auch Verweismethoden. Die folgende Abbildung zeigt ein Beispiel für das Hinzufügen einer ConvertToRomanNumeral Methode zu einem Double-Werttyp.

Verweismethoden werden nicht im Datentyp Karte angezeigt.

Hinzufügen einer Verweismethode zu einem Entitätswert

Um einem Entitätswert eine Verweismethode hinzuzufügen, definieren Sie sie mithilfe des Typs Excel.JavaScriptCustomFunctionReferenceCellValue in JSON. Im folgenden Codebeispiel wird gezeigt, wie eine einfache Methode definiert wird, die den Wert 27 zurückgibt.

const referenceCustomFunctionGet27: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GET27" 
} 

Die Eigenschaften werden in der folgenden Tabelle beschrieben.

Eigenschaft	Beschreibung
type	Gibt den Verweistyp an. Diese Eigenschaft unterstützt function nur und muss auf Excel.CellValueType.functionfestgelegt werden.
functionType	Gibt den Typ der Funktion an. Diese Eigenschaft unterstützt nur JavaScript-Verweisfunktionen und muss auf Excel.FunctionCellValueType.javaScriptReferencefestgelegt werden.
Namespace	Der Namespace, der die benutzerdefinierte Funktion enthält. Dieser Wert muss mit dem Namespace übereinstimmen, der vom customFunctions.namespace-Element im einheitlichen Manifest oder dem Namespace-Element im reinen Add-In-Manifest angegeben wird.
id	Der Name der benutzerdefinierten Funktion, die dieser Verweismethode zugeordnet werden soll. Der Name ist die Großbuchstabenversion des Benutzerdefinierten Funktionsnamens.

Wenn Sie den Entitätswert erstellen, fügen Sie die Verweismethode der Eigenschaftenliste hinzu. Das folgende Codebeispiel zeigt, wie Sie einen einfachen Entitätswert namens Math erstellen und ihm eine Verweismethode hinzufügen. Get27 ist der Methodenname, der Benutzern angezeigt wird (z. B. A1.Get27()).

function makeMathEntity(value: number){
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: "Math value",
    properties: {
      "value": {
        type: Excel.CellValueType.double,
        basicValue: value,
        numberFormat: "#"
      },
      Get27: referenceCustomFunctionGet27
    }
  };
  return entity;
}

Im folgenden Codebeispiel wird gezeigt, wie Sie eine instance der Math Entität erstellen und der ausgewählten Zelle hinzufügen.

// Add entity to selected cell.
async function addEntityToCell(){
  const entity: Excel.EntityCellValue = makeMathEntity(10);
  await Excel.run( async (context) => {
    const cell = context.workbook.getActiveCell();
    cell.valuesAsJson = [[entity]];
    await context.sync();
  });
}

Implementieren Sie schließlich die Verweismethode mit einer benutzerdefinierten Funktion. Im folgenden Codebeispiel wird gezeigt, wie die benutzerdefinierte Funktion implementiert wird.

/**
 * Returns the value 27.
 * @customfunction
 * @excludeFromAutoComplete
 * @returns {number} 27
 */
function get27() {
  return 27;
}

Im vorherigen Codebeispiel stellt das @excludeFromAutoComplete Tag sicher, dass die benutzerdefinierte Funktion nicht auf der Excel-Benutzeroberfläche angezeigt wird, wenn ein Benutzer sie in ein Suchfeld eingibt. Ein Benutzer kann die benutzerdefinierte Funktion jedoch weiterhin getrennt von einem Entitätswert aufrufen, wenn er sie direkt in eine Zelle eingibt.

Wenn der Code ausgeführt wird, wird ein Math Entitätswert erstellt, wie in der folgenden Abbildung dargestellt. Die -Methode wird in der Formel AutoVervollständigen angezeigt, wenn der Benutzer auf den Entitätswert aus einer Formel verweist.

Hinzufügen von Argumenten

Wenn Ihre Verweismethode Argumente benötigt, fügen Sie diese der benutzerdefinierten Funktion hinzu. Im folgenden Codebeispiel wird gezeigt, wie Sie einer Methode mit dem Namen ein Argument namens xaddValuehinzufügen. Die -Methode fügt dem x Wert eine hinzu, indem eine benutzerdefinierte Funktion namens addValueaufgerufen wird.

/**
 * Adds a value to 1.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} x The value to add to 1.
 * @return {number[][]}  Sum of x and 1.
 */
function addValue(x): number[][] {  
  return [[x+1]];
}

Verweisen auf den Entitätswert als aufrufende Objekt

Ein häufiges Szenario ist, dass Ihre Methoden auf Eigenschaften des Entitätswerts selbst verweisen müssen, um Berechnungen auszuführen. Es ist z. B. nützlicher, wenn die addValue Methode den Argumentwert dem Entitätswert selbst hinzufügt. Geben Sie an, dass der Entitätswert als erstes Argument übergeben wird, indem Sie das @capturesCallingObject Tag auf die benutzerdefinierte Funktion anwenden, wie im folgenden Codebeispiel gezeigt.

/**
 * Adds x to the calling object.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} math The math object (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum.
 */
function addValue(math, x): number[][] {  
  const result: number = math.properties["value"].basicValue + x;
  return [[result]];
}

Sie können einen beliebigen Argumentnamen verwenden, der den Excel-Syntaxregeln in Namen in Formeln entspricht. Da es sich um eine mathematische Entität handelt, hat das aufrufende Objektargument den Namen math. Der Argumentname kann in der Berechnung verwendet werden.

Beachten Sie Folgendes zum vorherigen Codebeispiel.

• Das @excludeFromAutoComplete Tag stellt sicher, dass die benutzerdefinierte Funktion nicht auf der Excel-Benutzeroberfläche angezeigt wird, wenn ein Benutzer sie in ein Suchfeld eingibt. Ein Benutzer kann die benutzerdefinierte Funktion jedoch weiterhin getrennt von einem Entitätswert aufrufen, wenn er sie direkt in eine Zelle eingibt.
• Das aufrufende Objekt wird immer als erstes Argument übergeben und muss vom Typ anysein. In diesem Fall heißt math sie und wird verwendet, um die value-Eigenschaft aus dem math -Objekt abzurufen.
• Es wird ein doppeltes Array von Zahlen zurückgegeben.
• Wenn der Benutzer mit der Verweismethode in Excel interagiert, wird das aufrufende Objekt nicht als Argument angezeigt.

Beispiel: Berechnen der Produktumsatzsteuer

Der folgende Code zeigt, wie eine benutzerdefinierte Funktion implementiert wird, die die Mehrwertsteuer für den Preis pro Einheit eines Produkts berechnet.

/**
 * Calculates the price when a sales tax rate is applied.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} product The product entity value (calling object).
 * @param {number} taxRate The tax rate (0.11 = 11%).
 * @return {number[][]}  Product unit price with tax rate applied.
 */
function applySalesTax(product, taxRate): number[][] {
  const unitPrice: number = product.properties["Unit Price"].basicValue;
  const result: number = unitPrice * taxRate + unitPrice;
  return [[result]];
}

Das folgende Codebeispiel zeigt, wie die Verweismethode angegeben wird, und enthält die id der applySalesTax benutzerdefinierten Funktion.

const referenceCustomFunctionCalculateSalesTax: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "APPLYSALESTAX" 
} 

Der folgende Code zeigt, wie die Verweismethode dem product Entitätswert hinzugefügt wird.

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {
        type: Excel.CellValueType.string,
        basicValue: productID.toString() || ""
      },
      "Product Name": {
        type: Excel.CellValueType.string,
        basicValue: productName || ""
      },
      "Unit Price": {
        type: Excel.CellValueType.formattedNumber,
        basicValue: price,
        numberFormat: "$* #,##0.00"
      },
      applySalesTax: referenceCustomFunctionCalculateSalesTax
    },
  };
  return entity;
}

Ausschließen benutzerdefinierter Funktionen von der Excel-Benutzeroberfläche

Verwenden Sie das @excludeFromAutoComplete Tag im JSDoc-Tag von benutzerdefinierten Funktionen, die von Verweismethoden verwendet werden, um anzugeben, dass die Funktion aus der Formel AutoVervollständigen und dem Formel-Generator ausgeschlossen wird. Dies verhindert, dass Benutzer versehentlich eine benutzerdefinierte Funktion getrennt vom Entitätswert verwenden.

Hinweis

Wenn die Funktion manuell ordnungsgemäß in das Raster eingegeben wird, wird die Funktion weiterhin ausgeführt.

Wichtig

Eine Funktion kann nicht die beiden Tags @excludeFromAutoComplete und @linkedEntityLoadService aufweisen.

Das @excludeFromAutoComplete Tag wird während des Builds verarbeitet, um eine functions.json-Datei vom Custom-Functions-Metadata-Paket zu generieren. Dieses Paket wird automatisch dem Buildprozess hinzugefügt, wenn Sie Ihr Add-In mit dem Yeoman-Generator für Office-Add-Ins erstellen und eine vorlage für benutzerdefinierte Funktionen auswählen. Wenn Sie das Custom-Functions-Metadata-Paket nicht verwenden, müssen Sie die excludeFromAutoComplete Eigenschaft manuell zur functions.json-Datei hinzufügen.

Im folgenden Codebeispiel wird gezeigt, wie Die APPLYSALESTAX benutzerdefinierte Funktion mit JSON in der functions.json-Datei manuell definiert wird. Die excludeFromAutoComplete-Eigenschaft ist auf true gesetzt.

{
    "description": "Calculates the price when a sales tax rate is applied.",
    "id": "APPLYSALESTAX",
    "name": "APPLYSALESTAX",
    "options": {
        "excludeFromAutoComplete": true,
        "capturesCallingObject": true
    },
    "parameters": [
        {
            "description": "The product entity value (calling object).",
            "name": "product",
            "type": "any"
        },
        {
            "description": "The tax rate (0.11 = 11%).",
            "name": "taxRate",
            "type": "number"
        }
    ],
    "result": {
        "dimensionality": "matrix",
        "type": "number"
    }
},

Weitere Informationen finden Sie unter Manuelles Erstellen von JSON-Metadaten für benutzerdefinierte Funktionen.

Hinzufügen einer Funktion zu einem Basiswerttyp

Verwenden Sie zum Hinzufügen von BooleanFunktionen zu den grundlegenden Werttypen , doubleund stringdenselben Prozess wie für Entitätswerte. Das folgende Codebeispiel zeigt, wie Sie einen doppelten Basiswert mit einer benutzerdefinierten Funktion namens addValueerstellen. Die Funktion fügt den Wert x dem Basiswert hinzu.

/**
 * Adds the value x to the number value.
 * @customfunction
 * @capturesCallingObject
 * @param {any} numberValue The number value (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum of the number value and x.
 */
export function addValue(numberValue: any, x: number): number[][] {
  return [[x+numberValue.basicValue]];
}

Das folgende Codebeispiel zeigt, wie sie die addValue benutzerdefinierte Funktion aus dem vorherigen Beispiel in JSON definieren und dann mit einer Methode namens createSimpleNumberdarauf verweisen.

const referenceCustomFunctionAddValue: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "ADDVALUE" 
} 

async function createSimpleNumber() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("A1");
    range.valuesAsJson = [
      [
        {
          type: Excel.CellValueType.double,
          basicType: Excel.RangeValueType.double,
          basicValue: 6.0,
          properties: {
            addValue: referenceCustomFunctionAddValue
          }
        }
      ]
    ];
    await context.sync();
  });
}

Optional arguments

Im folgenden Codebeispiel wird gezeigt, wie Eine Verweismethode erstellt wird, die optionale Argumente akzeptiert. Die Verweismethode wird benannt generateRandomRange und generiert einen Bereich von Zufallswerten.

const referenceCustomFunctionOptional: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GENERATERANDOMRANGE" 
}

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      generateRandomRange: referenceCustomFunctionOptional
    },
  };
  return entity;
}

Das folgende Codebeispiel zeigt die Implementierung der Verweismethode als benutzerdefinierte Funktion namens generateRandomRange. Es gibt ein dynamisches Array von Zufallswerten zurück, die der Anzahl von rows und columns angegebenen entsprechen. Die min Argumente und max sind optional, und wenn nicht angegeben, werden standardmäßig 1 und 10verwendet.

/**
 * Generates a dynamic array of random numbers.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} rows Number of rows to generate.
 * @param {number} columns Number of columns to generate.
 * @param {number} [min] Lowest number that can be generated. Default is 1.
 * @param {number} [max] Highest number that can be generated. Default is 10.
 * @returns {number[][]} A dynamic array of random numbers.
 */
function generateRandomRange(rows, columns, min, max) {
  // Set defaults for any missing optional arguments.
  if (min === undefined) min = 1;
  if (max === undefined) max = 10;

  let numbers = new Array(rows);
  for (let r = 0; r < rows; r++) {
    numbers[r] = new Array(columns);
    for (let c = 0; c < columns; c++) {
      numbers[r][c] = Math.round(Math.random() * (max - min) ) + min;
    }
  }
  return numbers;
}

Wenn der Benutzer die benutzerdefinierte Funktion in Excel eingibt, zeigt AutoVervollständigen die Eigenschaften der Funktion an und gibt optionale Argumente an, indem es sie in Klammern ([]) umgibt. Die folgende Abbildung zeigt ein Beispiel für die Eingabe optionaler Parameter mithilfe der generateRandomRange Verweismethode.

Mehrere Parameter

Verweismethoden unterstützen mehrere Parameter, ähnlich wie die Excel-Funktion SUM mehrere Parameter unterstützt. Das folgende Codebeispiel zeigt, wie Sie eine Verweisfunktion erstellen, die null oder mehr Produktnamen verkettet, die in einem Produktarray übergeben werden. Die Funktion wird dem Benutzer als concatProductNames([products], ...)angezeigt.

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Concatenate the names of given products, joined by " | " 
 * @param {any[]} products - The products to concatenate.
 * @returns A string of concatenated product names. 
 */ 
function concatProductNames(products: any[]): string { 
  return products.map((product) => product.properties["Product Name"].basicValue).join(" | "); 
}

Im folgenden Codebeispiel wird veranschaulicht, wie eine Entität mit der concatProductNames Verweismethode erstellt wird.

const referenceCustomFunctionMultiple: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "CONCATPRODUCTNAMES" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      concatProductNames: referenceCustomFunctionMultiple,
    },
  };
  return entity;
}

Die folgende Abbildung zeigt ein Beispiel für die Eingabe mehrerer Parameter mithilfe der concatProductNames Verweismethode.

Mehrere Parameter mit Bereichen

Verwenden Sie ein mehrdimensionales Array, um das Übergeben von Bereichen an Ihre Verweismethode wie B1:B3 zu unterstützen. Im folgenden Codebeispiel wird gezeigt, wie Sie eine Verweisfunktion erstellen, die null oder mehr Parameter summiert, die Bereiche enthalten können.

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Calculate the sum of arbitrary parameters. 
 * @param {number[][][]} operands - The operands to sum. 
 * @returns The sum of all operands. 
 */ 
function sumAll(operands: number[][][]): number { 
  let total: number = 0; 
 
  operands.forEach(range => { 
    range.forEach(row => { 
      row.forEach(num => { 
        total += num; 
      }); 
    }); 
  }); 
 
  return total; 
} 

Im folgenden Codebeispiel wird veranschaulicht, wie eine Entität mit der sumAll Verweismethode erstellt wird.

const referenceCustomFunctionRange: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "SUMALL" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      sumAll: referenceCustomFunctionRange
    },
  };
  return entity;
}

Die folgende Abbildung zeigt ein Beispiel für die Eingabe mehrerer Parameter, einschließlich eines Bereichsparameters, mithilfe der sumAll Verweismethode.

Supportdetails

Verweismethoden werden in allen benutzerdefinierten Funktionstypen unterstützt, z. B . flüchtige Funktionen und Streamingfunktionen . Alle benutzerdefinierten Funktionsrückgabetypen – Matrix, Skalar und Fehler – werden unterstützt.

Wichtig

Eine verknüpfte Entität kann keine benutzerdefinierte Funktion haben, die sowohl eine Verweismethode als auch einen Datenanbieter kombiniert. Wenn Sie verknüpfte Entitäten entwickeln, halten Sie diese Typen von benutzerdefinierten Funktionen getrennt.

Siehe auch

• Erstellen von benutzerdefinierten Funktionen in Excel
• Automatisches Erstellen von JSON-Metadaten für benutzerdefinierte Funktionen
• Manuelles Erstellen von JSON-Metadaten für benutzerdefinierte Funktionen