excel - 创建大型公式的替代方案

Question

有没有一种方法可以评估值表的一系列公式而不将它们组合成一个大公式？

用一个例子来说明会更容易。说 Table1 看起来像这样:

        A        B       C            D                E          F
1    PRODUCT    COST   MARKUP    SECRET FORMULA      PRICE      PROFIT
2    burger     4.00    50%         =22*12345    =B2*(1+C2)*D2  =E2-B2
3    fries      3.00    50%         =22*12345    =B3*(1+C3)*D3  =E3-B3
4    soda       1.50    50%         =22*12345    =B4*(1+C4)*D4  =E4-B4

现在我想构建 Table2 来为每种产品的一系列加价率返回利润，如下所示:

       A        B        C         D         E       F
1   MARKUP    10%       20%      30%       40%     50%
2   burger
3   fries
4   soda 

在 Table2!B2 中，当单元格 Table1!$C2 的值设置为 Table2!B$1 的列标题值时，我想显示单元格 Table1!$C2 的值。换句话说，我想要一个汉堡在加价为 10% 时的利润。然后通过跨行和单元格复制，我可以观察到当我将标记从 10% 更改为 50% 时对每个项目的利润的影响。

我知道我可以通过将表 1、列 D 和 E 中的公式组合成表 2 的单个大公式来手动执行此操作。例如，单元格 Table2B1 将具有以下公式:

= Table1!B2*(1 + B$1)*22*12345

但是假设 secret 公式实际上是一系列非常复杂的计算，涉及具有数十个依赖项的几列。我的实际公式集跨越大约 20 列，并生成一个 8 行公式……无法编辑。

因此，在表 1 中构建了一系列相关公式后，最好使用这些公式生成表 2，而不必将它们全部复制到表 2 中。

Answer 1

所以只是为了感受一下这个 super 方程式有多么庞大和难以管理，我把它贴在下面:

=Table6[[#This Row],[FBLR]]*AC$1- ( (Table6[[#This Row],[Base Salary]] + IF(IF(Table6[[#This Row],[exempt]]= "Y", IF( ((AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D $31,0 ), (AC$1-'收支平衡'!$D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0), (AC$1-'收支平衡' !$D$32)) *IF(Table6[[#This Row],[exempt]]="Y",Table6[[#This Row],[Pay rate]],Table6[[#This Row],[Pay rate]]*1.5), 0)) + Fringe!$D$27*IF( ( Table6[[#This Row],[Base Salary]] + IF(IF(Table6[[#This Row],[exempt]] ="Y", IF( ((AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$ D$31,0 ), (AC$1-'盈亏平衡'!$D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'盈亏平衡)连'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0 ), (AC$1-'收支平衡' '!$D $32)) *IF(Table6[[#This Row],[exempt]]="Y",Table6[[#This Row],[Pay rate]],Table6[[#This Row],[Pay rate]] *1.5), 0)) > Fringe!$E$27,Fringe!$E$27, ( Table6[[#This Row],[Base Salary]] + IF( IF(Table6[[#This Row],[exempt] ]="Y", IF( (AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'! $D$31,0 ), (AC$1-'收支平衡'!$D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0 ), (AC$1-'收支平衡'偶数'!$D$32)) *IF(Table6[[#This Row],[exempt]]="Y",Table6[[#This Row],[Pay rate]],Table6[[#This Row], [工资率]]*1.5), 0)))+ ( Table6[[#This Row],[Base Salary]] + IF( IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0 ) , (AC$1-'收支平衡'!$D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'收支平衡'!$D $32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32 )-'收支平衡'!$D$31,0 ), (AC$1-'收支平衡'!$D$32)) *IF(Table6[[#This Row],[exempt]]="Y",Table6[ [#This Row],[Pay rate]],Table6[[#This Row],[Pay rate]]*1.5), 0))*Fringe!$D$28+Fringe!$D$29*Fringe!$E$29 +Fringe!$D$30*Fringe!$E$30+ IF(Table1[[#This Row],[TC?]]="Y",Fringe!$F$34*12,IF(Table1[[#This Row] ,[TC?]]="N",边缘!$E$34*12*边缘!$D$34,(边缘!$F$34*12+边缘!$E$34*12*边缘!$D$34)/2 )) + ( Table6[[#This Row],[Base Salary]] + IF( IF(Table6[[#This Row],[exempt]]="Y", IF( (AC$1-'收支平衡'! $D$32)>'收支平衡'!$D$31,(AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0),(AC$1-'收支平衡'!$ D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF((AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31 , (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0 ), (AC$1-'收支平衡'!$D$32)) *IF(Table6[[#This Row ],[exempt]]="Y",Table6[[#This Row],[Pay rate]],Table6[[#This Row],[Pay rate]]*1.5), 0))*Fringe!$D $32 + ( Table6[[#This Row],[Base Salary]] + IF( IF(Table6[[#This Row], [豁免]]="Y", IF( (AC$1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡偶数'!$D$31,0 ), (AC$1-'收支平衡'!$D$32))>0, IF(Table6[[#This Row],[exempt]]="Y", IF( (AC $1-'收支平衡'!$D$32)>'收支平衡'!$D$31, (AC$1-'收支平衡'!$D$32)-'收支平衡'!$D$31,0 ), (AC$1 -'收支平衡'!$D$32)) *IF(Table6[[#This Row],[exempt]]="Y",Table6[[#This Row],[Pay rate]],Table6[[#This Row],[Pay rate]]*1.5), 0))*Fringe!$D$33 + Fringe!$E$35*12*Fringe!$D$35 +Table1[[#This Row],[Base Salary]]*边缘!$D$36+边缘!$E$37*边缘!$D$37)