如何在Excel中跟踪间接先例? [英] How to trace indirect precedents in Excel?

问题描述

Excel中的Trace Precedents/Dependents按钮在分析复杂电子表格的结构中非常有用.

The Trace Precedents/Dependents buttons in Excel are extremely useful in analyzing the structure of a complex spreadsheet.

我有一个工作表，其中包含许多调用，这些调用的先例是使用CELL()，OFFSET()等引用函数以编程方式生成的，其中这些函数的参数为​​表达式.

I have a sheet comprised of many calls whose precedents are generated programmatically, using reference functions like CELL(), OFFSET() etc., where the arguments to these functions are expressions.

使用跟踪按钮时，excel不会跟踪公式中未明确引用的单元格.

When using the trace buttons, excel won't trace the cells that are not explicitly referred to in the formula.

是否可以获取特定单元格的完整先例?

Is there a way to get the full set of precedents of a specific cell?

注意:搜索Google，我在Office Marketplace中看到了一个可以购买的应用程序，据说可以做到.我想知道是否可以通过提供的Excel功能来做到这一点?

Note: searching Google I saw an application in the Office Marketplace that is available for purchase and supposedly does that. I wonder if there is a way of doing so with the provided Excel functionality?

这是一个示例:在单元格M5中，我有以下公式，该公式引用了D5，D13和G6.相对于G4，G6是通过OFFSET()函数计算的.

here's an example: in cell M5 I have the following formula, which references D5, D13, and G6. G6 is calculated by the OFFSET() function, relative to G4.

=IMSUM($D5,IMPRODUCT($D13,OFFSET($G$4, 2*(ROW()-ROW($G$4)), 0)))

在跟踪单元格的先例时，Excel不会显示G6单元格.而是显示偏移量的底数，即G4.

When tracing the cell's precedents, Excel does not show the G6 cell. Instead, it shows the base of the offset, which is G4.

推荐答案

a)对Excel的整个公式引擎进行反向工程

a) reverse engineer the whole formula engine of Excel

b)为您的情况编写一个VBA解析器-即从公式字符串中获取OFFSET的参数并对其求值

b) write a VBA parser just for your case - i.e. get the arguments of OFFSET from the formula string and evaluate them

c)如果可以避免的话，请不要使用易失性公式-如
=IMSUM($D5,IMPRODUCT($D13,INDEX($G$4:$G$999, 2*(ROW()-ROW($G$4)) + 1)))

c) don't use volatile formulas if you can avoid it - like
=IMSUM($D5,IMPRODUCT($D13,INDEX($G$4:$G$999, 2*(ROW()-ROW($G$4)) + 1)))

这篇关于如何在Excel中跟踪间接先例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！