Excel中的异常计算列 [英] Exceptions in Excel calculated columns

问题描述

（替代标题：为什么地球上的Excel不支持使用参数的用户定义的公式，而不需要使用VB和需要的问题？）。

[已更新澄清我的问题]

在excel中定义一个表时，它将倾向于自动在列中复制一个公式。这非常像填写。

但是，如果您需要规则的例外情况如何？

在我正在构建的表中做一些计算，第一行在某种程度上往往是特殊的。所以，我想要自动填充，但是不要在第一行，或者不在标记为自定义的单元格上。 Excel文档在计算列中提到异常，但仅引用查找它们并消除它们。

例如，第一行是计算初始值
全部剩下的行计算一些增量更改。
一个简单的例子 - 一列和四行的表：

  A 
 1数量
 2 = 42 
 3 = A2 + 1 
 4 = A3 + 1 
  

第一个公式必须与其他公式不同。
这将创建一个A2 = 42，A3 = 43，A4 = 44的简单编号列表。
但现在，我想改变它增加2而不是1
如果我将A3编辑为A2 + 2，Excel将表更改为：

  A 
 1数
 2 = A1 + 2 
 3 = A2 + 2 
 4 = A3 + 2 
  

当然会被打破 - 应该允许A2继续是特殊情况

这不是（异常 - 特别是表的第一行）难以置信的常见要求？

解决方案

如果您将数据格式化为表格，则可以使用表格公式（例如[@ABC]）而不是A1格式（例如A1，$ C2等）。但是有两个方法可以解决。

首先没有上一行的表公式语法，而excel将默认返回到A1格式，但是可以使用偏移公式将当前单元格移动到上一行，如下所示。但是在这种情况下，它将返回一个＃值错误，因为我不能+1到ABC。

  ABC 
 1 = OFFSET（[@ ABC]， -  1,0）+1 
 2 = OFFSET（[@ ABC]， -  1,0）+1 
 3 = OFFSET（[@ ABC]， - 1,0）+1 
 4 .... 
  

所以第二个窍门是使用if语句来初始化该值，购买检查前一行值=标题值。如果同样使用初始值，则添加增量。注意假设表名为Table1

  ABC 
 1 = IF（OFFSET（[@ ABC]， -  1,0 ）= Table1 [[＃Headers]，[ABC]]，42，OFFSET（[@ ABC]， -  1,0）+1）
 2 = IF（OFFSET（[@ ABC]， -  1,0 ）= Table1 [[＃Headers]，[ABC]]，42，OFFSET（[@ ABC]， -  1,0）+1）
 3 = IF（OFFSET（[@ ABC]， -  1,0 ）= Table1 [[＃Headers]，[ABC]]，42，OFFSET（[@ ABC]， -  1,0）+1）
 4 .... 
  

请注意，您可以将初始值设置为表格外的单元格，以定义初始值（以$ A $ 1为单位）和增量$ A $ 2）如下

  ABC 
 1 = IF（OFFSET（[@ ABC]， -  1,0 ）= Table1 [[＃Headers]，[ABC]]，$ A $ 1，OFFSET（[@ ABC]， -  1,0）+ $ A $ 2）
 2 = IF（OFFSET（[@ ABC] -1,0）= Table1 [[＃Headers]，[ABC]]，$ A $ 1，OFFSET（[@ ABC]， -  1,0）+ $ A $ 2）
 3 = IF（OFFSET @ABC]， -  1,0）= Table1 [[Headers]，[ABC]]，$ A $ 1，OFFSET（[@ ABC]， -  1,0）+ $ A $ 2）
 4 .. .. 
  

我一直使用这个IF OFFSET组合来迭代和循环表。 >

如果你有很多列需要确定它们是否是第一行，如果第一行可以有一列测试，其余的可以使用更简单的方法。例如ABC将为其他人的第一行为假，然后增加初始值的DEF。

  ABC DEF 
 1 = OFFSET（[@ ABC]， -  1,0）= Table1 [[＃Headers]，[ABC]] = IF（[@ ABC]，$ A $ 1，OFFSET（[@ DEF]， -  1,0）+ $ A $ 2）
 2 = OFFSET（[@ ABC]， -  1,0）= Table1 [[＃Headers]，[ABC]] = IF（[@ ABC]，$ A $ 1，OFFSET DEF]， -  1,0）+ $ A $ 2）
 3 = OFFSET（[@ ABC]， -  1,0）= Table1 [[Headers]，[ABC]] = IF（[@ ABC] ，$ A $ 1，OFFSET（[@ DEF]， -  1,0）+ $ A $ 2）
 4 .... 
  

希望有助于

(Alternate title: Why on earth doesn't Excel support user-defined formulas with parameters without resorting to VB and the problems that entails?).

[ Updated to clarify my question ]

In excel when you define a table it will tend to automatically replicate a formula in a column. This is very much like "fill down".

But ... what if you need exceptions to the rule?

In the tables I'm building to do some calculations the first row tends to be "special" in some way. So, I want the auto-fill down, but just not on the first row, or not on cells marked as custom. The Excel docs mention exceptions in computed columns but only in reference to finding them and eliminating them.

For example, first row is computing the initial value The all the remaining rows compute some incremental change. A trivial example - a table of 1 column and 4 rows:

    A
1 Number
2 =42
3 =A2+1
4 =A3+1

The first formula must be different than the rest. This creates a simple numbered list with A2=42, A3=43, A4=44. But now, say I'd like to change it to be incremented by 2 instead of 1. If I edit A3 to be "A2+2", Excel changes the table to be:

    A
1 Number
2 =A1+2
3 =A2+2
4 =A3+2

Which of course is busted -- it should allow A2 to continue to be a special case.

Isn't this (exceptions - particularly in the first row of a table) an incredibly common requirement?

解决方案

If you have the data formatted as a table you can use table formulas (eg [@ABC]) instead of A1 format (eg A1, $C2 etc). But there are 2 tricks to account for.

Firstly there is no table formula syntax for the previous row, instead excel will default back to A1 format, but you can use the offset formula to move you current cell to the previous row as shown below. However in this case it will return an # value error since I cant +1 to "ABC".

     ABC
1   =OFFSET([@ABC],-1,0)+1
2   =OFFSET([@ABC],-1,0)+1
3   =OFFSET([@ABC],-1,0)+1
4   ....

So the second trick is to use a if statement to intialise the value, buy checking if the previous row value = heading value. If the same use the initial value else add the increment. Note assumes table is named Table1

     ABC
1   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
2   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
3   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
4   ....

Note you can set the initial value to be a cell outside the table to define the initial value (in say $A$1) and increment (in say $A$2) as below

     ABC
1   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
2   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
3   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
4   ....

I use this IF OFFSET combination all the time for iterating and looping in tables.

If you have alot of columns that need to determine if they are the first row you can have one column test if first row and the rest can work with a simpler if. eg ABC will give true for first row false for others, then DEF with increment the initial value

     ABC                                            DEF
1   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
2   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
3   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
4   ....

Hope that helps

这篇关于Excel中的异常计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！