excel-2007 - 用于复杂查找的更好的 Excel 公式

Question

我正在尝试改进我继承的复杂查找过程。查找是通过几个 UDF 结合一些标准工作表函数生成的。但是，问题是当用户更新源表中的某些数据时，重新计算时间是 Not Acceptable 。

所以，我看了一下，并认为我可以编写一个更好的 Excel 公式解决方案。好吧，我确实找到了一个解决方案，但是 Excel 无法处理大型数据集，并且当我的 VBA 针对数据集运行公式时它会崩溃(可以理解!)。

现在，我可以在 VBA 中完全实现这一点，但是用户必须在每次更改后按下按钮或其他内容进行更新。我想要的是一种更简单的方法，如果有的话，使用一些高级 Excel 2007 公式。由于我不太精通这些公式，因此我正在寻求帮助!

好的，这是我必须处理的。

源表

Tid、结算日期和月末价格(由 1、2、3 等标识的层期)在如下列中

Tid   SettleDate   1   2   3   4   5   6   7   8   9   10  ...   n

公式表

在其他列中，我有以下列

InitLayer   LiqdLayer   InstrClass   Tid   SettleDate   InitPrice   LiqdPrice   Position

我还在整个数据集右侧的列中有图层编号，如下所示:

1   2   3   4   5   ...   n

我需要做的是通过查找源表上的价格，根据数据集中的某些逻辑在这些列中填写适当的价格变化。

在伪公式中，这就是我需要为 FormulaSheet 中的每一层列发生的事情

If Layer < InitLayer OR Layer > LiqdLayer Then Return "-"

ElseIf Layer = InitLayer Then (Layered Price - InitPrice) * Position

     where Layered Price is obtained by finding the Intersect of the LayerNumber 
     Column and Tid Row in the SourceSheet

ElseIf Layer = LiqdLayer Then Previous Layered Price * Position

     where Previous Layered Price is obtained by finding the Intersect of the Previous   
     LayerNumber Column and Tid Row in the SourceSheet

Else (LayeredPrice - Previous Layered Price) * 6

     where Layered Price and Previous Layered Price are defined as above

End If

我确实想出了这个公式，它适用于小数据集，但它的 Toooooooooo 对于大型数据集来说又大又讨厌，或者只是太大而讨厌的时期!

=IF(OR(CH$3<$AT6,CH$3>$AU6),"-",IF($AT6=CH$3,(HLOOKUP(CH$3,layered_prices,RIGHT(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4),LEN(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4))-1)-1,FALSE)-$AV6)*$C6,IF($AU6=CH$3,($AW6-HLOOKUP(CG$3,layered_prices,RIGHT(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4),LEN(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4))-1)-1,FALSE))*$C6,(HLOOKUP(CH$3,layered_prices,RIGHT(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4),LEN(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4))-1)-1,FALSE)-HLOOKUP(CG$3,layered_prices,RIGHT(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4),LEN(ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4))-1)-1,FALSE))*$C6)))

公式键

CH = Layer Number
CG = Previous Layer Number
AT = InitLayer
AU = LiqdLayer
AX = InstrClass (used to find a separate lookup for Currencies)
T = Tid
G = SettleDate (used to find a separate lookup for Currencies)
AV = InitPrice
AW = LiqPrice
C = Position
layered_prices = named range for the range of prices under the layer columns in SourceSheet
layered_tid = named range for tid rows in SourceSheet
layered_curtid = named range for currency tid rows in Source Sheet (just a separte lookup if InstrType = Currency, formula the same

是否有任何其他公式或公式组合可以让我以比我创造的怪物更有效的方式获得我正在寻找的东西？

Answer 1

我同意 Kharoof 的评论。您应该将此公式分成几列。根据我的统计，您还需要 4 列。好处有两方面:1) 您的公式变得更短，因为您不会一遍又一遍地重复相同的功能；2) 节省内存，因为 Excel 将计算一次而不是多次。

例如，您调用完全相同的 ADDRESS功能四次。 Excel 不会“记住”评估公式时的内容，因此每次都会重新计算。如果你把它放在它自己的单元格中，那么 Excel 将在任何依赖它的单元格之前评估该单元格，并将其存储为一个值而不是公式。当其他单元格引用它时，Excel 将提供预先评估的结果。

首先，这是您的最终公式应该是:([括号] 中的名称表示辅助列适合该列。它将是一些单元格引用，如 CI$3，但我不确定您想把它放在哪里。您必须根据添加这些列的位置更新这些引用。)

=IF(OR(CH$3<$AT6,CH$3>$AU6),"-",IF($AT6=CH$3,([LayerNumber]-$AV6)*$C6,IF($AU6=CH$3,($AW6-[PreviousLayerNumber])*$C6,([LayerNumber]-[PreviousLayerNumber])*$C6)))

这是四个辅助列:

[ADDRESS] = ADDRESS(MATCH(IF($AX6="CUR",$T6 & " " & $G6,$T6),IF($AX6="CUR",layered_curtid,layered_tid),1),1,4)
[RIGHT] = RIGHT([ADDRESS],LEN([ADDRESS])-1)
[LayerNumber] = HLOOKUP(CH$3,layered_prices,[RIGHT]-1,FALSE)
[PreviousLayerNumber] = HLOOKUP(CG$3,layered_prices,[RIGHT]-1,FALSE)

通过将其拆分，公式的每一步都更容易遵循/调试，并且可以更快地处理 Excel。如果你想要一些定量的改进，五个公式的组合将比你现在拥有的单个公式短 70% 左右。