Bereiche: Arbeiten mit dem Raster in Office-Skripts
Die folgenden Beispiele sind einfache Skripts, mit denen Sie Ihre eigenen Arbeitsmappen ausprobieren können. Sie bilden die Bausteine für größere Lösungen. Erweitern Sie diese Skripts, um Ihre Lösung zu erweitern und häufige Probleme zu lösen.
Lesen und Protokollieren einer Zelle
In diesem Beispiel wird der Wert von A1 gelesen und in der Konsole ausgegeben.
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();
// Get the value of cell A1.
let range = selectedSheet.getRange("A1");
// Print the value of A1.
console.log(range.getValue());
}
Lesen der aktiven Zelle
Dieses Skript protokolliert den Wert der aktuellen aktiven Zelle. Wenn mehrere Zellen ausgewählt sind, wird die zelle oben links protokolliert.
function main(workbook: ExcelScript.Workbook) {
// Get the current active cell in the workbook.
let cell = workbook.getActiveCell();
// Log that cell's value.
console.log(`The current cell's value is ${cell.getValue()}`);
}
Hinzufügen von Daten zu einem Bereich
Dieses Skript fügt einem neuen Arbeitsblatt einen Satz von Werten hinzu. Die Werte beginnen in Zelle A1. Die in diesem Skript verwendeten Daten sind vordefiniert, können aber von anderen Stellen in oder aus der Arbeitsmappe stammen.
function main(workbook: ExcelScript.Workbook) {
// The getData call could be replaced by input from Power Automate or a fetch call.
const data = getData();
// Create a new worksheet and switch to it.
const newWorksheet = workbook.addWorksheet("DataSheet");
newWorksheet.activate();
// Get a range matching the size of the data.
const dataRange = newWorksheet.getRangeByIndexes(
0,
0,
data.length,
data[0].length);
// Set the data as the values in the range.
dataRange.setValues(data);
}
function getData(): string[][] {
return [["Abbreviation", "State/Province", "Country"],
["AL", "Alabama", "USA"],
["AK", "Alaska", "USA"],
["AZ", "Arizona", "USA"],
["AR", "Arkansas", "USA"],
["CA", "California", "USA"],
["CO", "Colorado", "USA"],
["CT", "Connecticut", "USA"],
["DE", "Delaware", "USA"],
["DC", "District of Columbia", "USA"],
["FL", "Florida", "USA"],
["GA", "Georgia", "USA"],
["HI", "Hawaii", "USA"],
["ID", "Idaho", "USA"],
["IL", "Illinois", "USA"],
["IN", "Indiana", "USA"],
["IA", "Iowa", "USA"],
["KS", "Kansas", "USA"],
["KY", "Kentucky", "USA"],
["LA", "Louisiana", "USA"],
["ME", "Maine", "USA"],
["MD", "Maryland", "USA"],
["MA", "Massachusetts", "USA"],
["MI", "Michigan", "USA"],
["MN", "Minnesota", "USA"],
["MS", "Mississippi", "USA"],
["MO", "Missouri", "USA"],
["MT", "Montana", "USA"],
["NE", "Nebraska", "USA"],
["NV", "Nevada", "USA"],
["NH", "New Hampshire", "USA"],
["NJ", "New Jersey", "USA"],
["NM", "New Mexico", "USA"],
["NY", "New York", "USA"],
["NC", "North Carolina", "USA"],
["ND", "North Dakota", "USA"],
["OH", "Ohio", "USA"],
["OK", "Oklahoma", "USA"],
["OR", "Oregon", "USA"],
["PA", "Pennsylvania", "USA"],
["RI", "Rhode Island", "USA"],
["SC", "South Carolina", "USA"],
["SD", "South Dakota", "USA"],
["TN", "Tennessee", "USA"],
["TX", "Texas", "USA"],
["UT", "Utah", "USA"],
["VT", "Vermont", "USA"],
["VA", "Virginia", "USA"],
["WA", "Washington", "USA"],
["WV", "West Virginia", "USA"],
["WI", "Wisconsin", "USA"],
["WY", "Wyoming", "USA"],
["AB", "Alberta", "CAN"],
["BC", "British Columbia", "CAN"],
["MB", "Manitoba", "CAN"],
["NB", "New Brunswick", "CAN"],
["NL", "Newfoundland and Labrador", "CAN"],
["NT", "Northwest Territory", "CAN"],
["NS", "Nova Scotia", "CAN"],
["NU", "Nunavut Territory", "CAN"],
["ON", "Ontario", "CAN"],
["PE", "Prince Edward Island", "CAN"],
["QC", "Quebec", "CAN"],
["SK", "Saskatchewan", "CAN"],
["YT", "Yukon Territory", "CAN"]];
}
Ändern einer angrenzenden Zelle
Dieses Skript ruft angrenzende Zellen mithilfe relativer Verweise ab. Beachten Sie, dass, wenn sich die aktive Zelle in der obersten Zeile befindet, ein Teil des Skripts fehlschlägt, da er auf die Zelle über der aktuell ausgewählten zelle verweist.
function main(workbook: ExcelScript.Workbook) {
// Get the currently active cell in the workbook.
let activeCell = workbook.getActiveCell();
console.log(`The active cell's address is: ${activeCell.getAddress()}`);
// Get the cell to the right of the active cell and set its value and color.
let rightCell = activeCell.getOffsetRange(0,1);
rightCell.setValue("Right cell");
console.log(`The right cell's address is: ${rightCell.getAddress()}`);
rightCell.getFormat().getFont().setColor("Magenta");
rightCell.getFormat().getFill().setColor("Cyan");
// Get the cell to the above of the active cell and set its value and color.
// Note that this operation will fail if the active cell is in the top row.
let aboveCell = activeCell.getOffsetRange(-1, 0);
aboveCell.setValue("Above cell");
console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
aboveCell.getFormat().getFont().setColor("White");
aboveCell.getFormat().getFill().setColor("Black");
}
Ändern aller angrenzenden Zellen
Dieses Skript kopiert die Formatierung in der aktiven Zelle in die benachbarten Zellen. Beachten Sie, dass dieses Skript nur funktioniert, wenn sich die aktive Zelle nicht an einem Rand des Arbeitsblatts befindet.
function main(workbook: ExcelScript.Workbook) {
// Get the active cell.
let activeCell = workbook.getActiveCell();
// Get the cell that's one row above and one column to the left of the active cell.
let cornerCell = activeCell.getOffsetRange(-1,-1);
// Get a range that includes all the cells surrounding the active cell.
let surroundingRange = cornerCell.getResizedRange(2, 2)
// Copy the formatting from the active cell to the new range.
surroundingRange.copyFrom(
activeCell, /* The source range. */
ExcelScript.RangeCopyType.formats /* What to copy. */
);
}
Ändern jeder einzelnen Zelle in einem Bereich
Dieses Skript führt eine Schleife über den aktuell ausgewählten Bereich aus. Es löscht die aktuelle Formatierung und legt die Füllfarbe in jeder Zelle auf eine zufällige Farbe fest.
function main(workbook: ExcelScript.Workbook) {
// Get the currently selected range.
let range = workbook.getSelectedRange();
// Get the size boundaries of the range.
let rows = range.getRowCount();
let cols = range.getColumnCount();
// Clear any existing formatting.
range.clear(ExcelScript.ClearApplyTo.formats);
// Iterate over the range.
for (let row = 0; row < rows; row++) {
for (let col = 0; col < cols; col++) {
// Generate a random hexadecimal color code.
let colorString = `#${Math.random().toString(16).substr(-6)}`;
// Set the color of the current cell to that random hexadecimal code.
range.getCell(row, col).getFormat().getFill().setColor(colorString);
}
}
}
Abrufen von Gruppen von Zellen basierend auf speziellen Kriterien
Dieses Skript ruft alle leeren Zellen im verwendeten Bereich des aktuellen Arbeitsblatts ab. Anschließend werden alle Zellen mit einem gelben Hintergrund hervorgehoben.
function main(workbook: ExcelScript.Workbook) {
// Get the current used range.
let range = workbook.getActiveWorksheet().getUsedRange();
// Get all the blank cells.
let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);
// Highlight the blank cells with a yellow background.
blankCells.getFormat().getFill().setColor("yellow");
}
Formeln
Bereiche verfügen über Werte und Formeln. Die Formel ist der auszuwertende Ausdruck. Der Wert ist das Ergebnis dieses Ausdrucks.
Einzelne Formel
Dieses Skript legt die Formel einer Zelle fest und zeigt dann an, wie Excel die Formel und den Wert der Zelle separat speichert.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set A1 to 2.
let a1 = selectedSheet.getRange("A1");
a1.setValue(2);
// Set B1 to the formula =(2*A1), which should equal 4.
let b1 = selectedSheet.getRange("B1");
b1.setFormula("=(2*A1)");
// Log the current results for `getFormula` and `getValue` at B1.
console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}
Behandeln eines Fehlers #SPILL! , der von einer Formel zurückgegeben wird
Dieses Skript transponiert den Bereich "A1:D2" in "A4:B7" mithilfe der TRANSPOSE-Funktion. Wenn die Transponieren zu einem #SPILL Fehler führt, löscht sie den Zielbereich und wendet die Formel erneut an.
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
// Use the data in A1:D2 for the sample.
let dataAddress = "A1:D2"
let inputRange = sheet.getRange(dataAddress);
// Place the transposed data starting at A4.
let targetStartCell = sheet.getRange("A4");
// Compute the target range.
let targetRange = targetStartCell.getResizedRange(inputRange.getColumnCount() - 1, inputRange.getRowCount() - 1);
// Call the transpose helper function.
targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
// Check if the range update resulted in a spill error.
let checkValue = targetStartCell.getValue() as string;
if (checkValue === '#SPILL!') {
// Clear the target range and call the transpose function again.
console.log("Target range has data that is preventing update. Clearing target range.");
targetRange.clear();
targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
}
// Select the transposed range to highlight it.
targetRange.select();
}
Ersetzen aller Formeln durch ihre Ergebniswerte
Dieses Skript ersetzt jede Zelle im aktuellen Arbeitsblatt, die eine Formel enthält, durch das Ergebnis dieser Formel. Dies bedeutet, dass nach der Ausführung des Skripts keine Formeln mehr vorhanden sind, nur Werte.
function main(workbook: ExcelScript.Workbook) {
// Get the ranges with formulas.
let sheet = workbook.getActiveWorksheet();
let usedRange = sheet.getUsedRange();
let formulaCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);
// In each formula range: get the current value, clear the contents, and set the value as the old one.
// This removes the formula but keeps the result.
formulaCells.getAreas().forEach((range) => {
let currentValues = range.getValues();
range.clear(ExcelScript.ClearApplyTo.contents);
range.setValues(currentValues);
});
}
Neue Beispiele vorschlagen
Wir freuen uns über Vorschläge für neue Beispiele. Wenn es ein gängiges Szenario gibt, das anderen Skriptentwicklern helfen würde, teilen Sie uns dies im Abschnitt Feedback unten auf der Seite mit.
Siehe auch
Office Scripts