Получение прецедентов и зависимых формул с помощью API JavaScript для Excel

  • Статья

Формулы Excel часто содержат ссылки на другие ячейки на том же листе или книге. Эти перекрестные ссылки называются прецедентами и зависимыми. Прецедентом является ячейка, которая предоставляет данные формуле. Зависимый — это ячейка, содержащая формулу, которая ссылается на другие ячейки. Дополнительные сведения о функциях пользовательского интерфейса Excel, связанных с прецедентами и зависимыми элементами, такими как прецеденты трассировки и зависимые от трассировки, см. в статье Отображение связей между формулами и ячейками.

Ячейка прецедента может иметь собственные ячейки прецедентов. Каждая ячейка прецедента в этой цепочке прецедентов по-прежнему является прецедентом первоначальной ячейки. Такая же связь существует для зависимых. Любая ячейка, затронутая другой ячейкой, зависит от этой ячейки. "Прямой прецедент" — это первая предыдущая группа ячеек в этой последовательности, аналогичная концепции родителей в отношениях "родители-потомки". "Прямой зависимый" — это первая зависимая группа ячеек в последовательности, аналогичная дочерним элементам в отношениях "родители-потомки".

В этой статье приведены примеры кода, которые извлекают прецеденты и зависимые формулы с помощью API JavaScript для Excel. Полный список свойств и методов, поддерживаемых объектом, см. в Range разделе Объект range (API JavaScript для Excel).

Получение прецедентов формулы

Найдите ячейки прецедента формулы с помощью Range.getPrecedents. Range.getPrecedentsWorkbookRangeAreas возвращает объект . Этот объект содержит адреса всех прецедентов в книге. Он имеет отдельный RangeAreas объект для каждого листа, содержащий по крайней мере один прецедент формулы. Дополнительные сведения об объекте RangeAreas см. в статье Работа с несколькими диапазонами одновременно в надстройках Excel.

Чтобы найти только ячейки прямого прецедента формулы, используйте Range.getDirectPrecedents. Range.getDirectPrecedents работает как Range.getPrecedents и возвращает WorkbookRangeAreas объект, содержащий адреса прямых прецедентов.

На следующем снимке экрана показан результат нажатия кнопки Трассировка прецедентов в пользовательском интерфейсе Excel. Эта кнопка рисует стрелку из ячеек прецедента в выбранную ячейку. Выбранная ячейка E3 содержит формулу "=C3 * D3", поэтому C3 и D3 являются ячейками прецедента. В отличие от кнопки getPrecedents пользовательского интерфейса Excel, методы и getDirectPrecedents не рисуют стрелки.

Стрелка трассировки ячеек прецедента в пользовательском интерфейсе Excel.

Важно!

Методы getPrecedents и getDirectPrecedents не извлекают ячейки прецедента в книгах.

В следующем примере кода показано, как работать с методами Range.getPrecedents и Range.getDirectPrecedents . Пример получает прецеденты для активного диапазона, а затем изменяет цвет фона этих ячеек прецедента. Цвет фона для ячеек прямого прецедента имеет значение желтый, а цвет фона других ячеек прецедента — оранжевый.

// This code sample shows how to find and highlight the precedents 
// and direct precedents of the currently selected cell.
await Excel.run(async (context) => {
  let range = context.workbook.getActiveCell();
  // Precedents are all cells that provide data to the selected formula.
  let precedents = range.getPrecedents();
  // Direct precedents are the parent cells, or the first preceding group of cells that provide data to the selected formula.    
  let directPrecedents = range.getDirectPrecedents();

  range.load("address");
  precedents.areas.load("address");
  directPrecedents.areas.load("address");
  
  await context.sync();

  console.log(`All precedent cells of ${range.address}:`);
  
  // Use the precedents API to loop through all precedents of the active cell.
  for (let i = 0; i < precedents.areas.items.length; i++) {
    // Highlight and print out the address of all precedent cells.
    precedents.areas.items[i].format.fill.color = "Orange";
    console.log(`  ${precedents.areas.items[i].address}`);
  }

  console.log(`Direct precedent cells of ${range.address}:`);

  // Use the direct precedents API to loop through direct precedents of the active cell.
  for (let i = 0; i < directPrecedents.areas.items.length; i++) {
    // Highlight and print out the address of each direct precedent cell.
    directPrecedents.areas.items[i].format.fill.color = "Yellow";
    console.log(`  ${directPrecedents.areas.items[i].address}`);
  }
});

Получение зависимости формулы

Найдите зависимые ячейки формулы с помощью Range.getDependents. Как и Range.getPrecedents, Range.getDependents также возвращает WorkbookRangeAreas объект . Этот объект содержит адреса всех зависимых в книге. Он содержит отдельный RangeAreas объект для каждого листа, содержащий по крайней мере одну зависимое от формулы. Дополнительные сведения о работе с объектом см. в RangeAreas разделе Работа с несколькими диапазонами одновременно в надстройках Excel.

Чтобы найти только прямые зависимые ячейки формулы, используйте Range.getDirectDependents. Range.getDirectDependents работает как Range.getDependents и возвращает WorkbookRangeAreas объект, содержащий адреса прямых зависимых.

На следующем снимке экрана показан результат нажатия кнопки Трассировка зависимых в пользовательском интерфейсе Excel. Эта кнопка рисует стрелку из выделенной ячейки в зависимые ячейки. Выбранная ячейка D3 имеет ячейку E3 в качестве зависимой. E3 содержит формулу "=C3 * D3". В отличие от кнопки getDependents пользовательского интерфейса Excel, методы и getDirectDependents не рисуют стрелки.

Стрелка трассировки зависимых ячеек в пользовательском интерфейсе Excel.

Важно!

Методы getDependents и getDirectDependents не извлекают зависимые ячейки в книгах.

В следующем примере кода показано, как работать с методами Range.getDependents и Range.getDirectDependents . Пример получает зависимые значения для активного диапазона, а затем изменяет цвет фона этих зависимых ячеек. Цвет фона прямых зависимых ячеек — желтый, а цвет фона других зависимых ячеек — оранжевый.

// This code sample shows how to find and highlight the dependents 
// and direct dependents of the currently selected cell.
await Excel.run(async (context) => {
    let range = context.workbook.getActiveCell();
    // Dependents are all cells that contain formulas that refer to other cells.
    let dependents = range.getDependents();  
    // Direct dependents are the child cells, or the first succeeding group of cells in a sequence of cells that refer to other cells.
    let directDependents = range.getDirectDependents();

    range.load("address");
    dependents.areas.load("address");    
    directDependents.areas.load("address");
    
    await context.sync();

    console.log(`All dependent cells of ${range.address}:`);
    
    // Use the dependents API to loop through all dependents of the active cell.
    for (let i = 0; i < dependents.areas.items.length; i++) {
      // Highlight and print out the addresses of all dependent cells.
      dependents.areas.items[i].format.fill.color = "Orange";
      console.log(`  ${dependents.areas.items[i].address}`);
    }

    console.log(`Direct dependent cells of ${range.address}:`);

    // Use the direct dependents API to loop through direct dependents of the active cell.
    for (let i = 0; i < directDependents.areas.items.length; i++) {
      // Highlight and print the address of each dependent cell.
      directDependents.areas.items[i].format.fill.color = "Yellow";
      console.log(`  ${directDependents.areas.items[i].address}`);
    }
});

См. также