完美Excel

二次方程式的一般形式为：

其解为：

其中，b2-4ac被称为判别式，其值决定等式实根的数量（0，1或2）。
求解二次方程式的工作表模型如下图，例如求解方程：

工作表模型中相应的数据如图。

步骤1 在单元格区域A1:C4中输入上图所示的数据。
步骤2 选择A3:C4，单击“公式——定义的名称——根据所选内容创建”，给单元格A4:C4命名为其上方单元格的内空。
步骤3 在单元格E3中输入“判别式”，在E4中输入公式：

=b*b-4*a*c

步骤4 将单元格E4命名为disc。
步骤5 在单元格A7和C7中输入上图所示的文本。在单元格B7中输入公式：

=(-b+SQRT(disc))/(2*a)

在单元格D7中输入公式：

=(-b-SQRT(disc))/(2*a)

现在，测试二次方程式，如果判别式的值为负数，那么会发生什么呢？试着为a、b、c输入值1、3、6，在单元格B7和D7中会显示错误值#NUM!。因为没有输入虚数范围，所以负数不可能有平方根。
因此，需要改进工作表模型，当判别式为负值时，提供其他信息。
步骤6 在单元格A6中输入如图所示的文本。
步骤7 在单元格B6中输入公式：

=IF(disc<0,"无解",IF(disc=0,1,2))

如果判别式为负值，则返回0；为0则返回1；其他情况返回2。
步骤8 将单元格A7中的文本替换为公式：

=IF(B6=0,”",IF(B6=1,”双根”,”根1″))

如果只有1个根，则返回文本“双根”；如果有两个不同的根，则返回“根1”；当没有实根时，返回空字符串。
步骤9 在单元格B7中，当判别式为0或正值时，返回1个根，否则返回空字符串，公式为：

=IF(disc>=0,(-b+SQRT(disc))/(2*a),”")

步骤10 将单元格C7中的文本替换为公式：

=IF(B6=2,”根2″,”")

当判别式为正值时，返回文本“根2”。
步骤11 在单元格D7中输入公式：

=IF(disc>0,(-b-SQRT(disc))/(2*a),”")

当判别式为正值时，返回第二个根。
注意，单元格B7中测试判别式disc>=0，而D7中测试判别式disc>0，这避免了相等的根在工作表中显示两次。
至此，求解二次方程式的工作表模型建立完成。可以在A4:C4中输入方程的系数值进行方程求解。

统计某值在单元格区域中出现的次数
例如下图所示，统计指定区域中“迟到”的次数：

使用了数组公式：

{=SUM(IF(B1=B5:B29,1,0))}

其中，单元格B1中是要统计的值，单元格区域B5：B29是统计区域，将要统计的值与统计区域中的单元格逐一比较，相同为1，否则为0，最后相加得到统计结果。
当然，也可以使用COUNTIF函数：

=COUNTIF(B5:B29,B1)

将上例再进一步扩展，如果迟到一次扣10分，那么在这段时间里累计扣分为多少呢？同样使用IF函数和SUM函数组成的数组公式得到结果：

{=SUM(IF(B1=B5:B29,C5:C29,0))}

如下图所示：

统计两个区域中不相同的单元格数
如下图所示，要求统计单元格区域A4：B8和单元格区域D4：E8中不相同的单元格数。

公式如下：

{=SUM(IF(A4:B8<>D4:E8,1,0))}

应用时，两个单元格区域的大小必须相同。在公式中，将两个区域对应的单元格相互比较，如果不相同为1，相同则为0，最后相加得到结果。
小结
从上面的例子可以看出，对于这类要求，可以使用通用的数组公式：

{=SUM(IF(条件,1,0))}

该公式，用于计算条件为True时的次数，其中条件通常是一个逻辑公式。可以将同一个值与单元格区域中的各单元格值进行比较，也可以比较两个相同的区域。

在Excel工作表函数中，IF函数是最基本的函数，经常被使用，也为众多Excel用户熟知。
IF函数的语法：

IF(逻辑测试,结果为True时的返回值,结果为False时的返回值)

说明：

• IF函数有三个参数，其中第一个参数为逻辑测试表达式，其测试结果将决定返回的值。
• 为简单起见，第三个参数可以省略。
• 如果第二个参数或第三个参数为文本，则需要使用双引号将文本括住。
• 可以再次使用IF函数作为第二个参数或第三个参数，即嵌套使用IF函数。

示例1：

=IF(A1>100,”请重新输入小于100的值”,”正确的输入值”)

=IF(A1>0,”正数”,IF(A1<0,"负数","零"))

示例2：
假如要计算加班费，规定如下：如果没有加班，则没有加班费；如果加班不大于2个，则发放加班费100元；如果加班不大于4个，则发放加班费300元；如果加班大于4个，则发放加班费500元。
公式如下，在单元格C2中输入公式后，拖动该单元格右下角至单元格C5即可。

一个简单的示例：查找Excel工作表中的重复数据
记得一位网友曾问：要求找出Excel工作表中的重复数据并显示在工作表相应的单元格中。我给出了一个数组公式供参考，但不是太符合要求，因为这个数组公式虽然找出了重复数据，但是如果将数组公式向下复制时超出了出现重复数据的数量，会在相应单元格中显示错误。不久，这位朋友获得了更好的一个公式。这个公式非常好，完美地解决了这类问题，因此，我将其转贴于此，供有兴趣的朋友参考。
先看看下图：

在列A和列B中存在一系列数据（表中只是示例，可能还有更多的数据），要求找出某人（即列A中的姓名）所对应的所有培训记录（即列B中的数据）。也就是说，在单元格E1中输入某人的姓名后，下面会自动显示这个人所有的培训记录。
我们知道，Excel的LOOKUP系列函数能够很方便地实现查找，但是对于查找后返回一系列的结果，这类函数无能为力，因此只能联合其它函数来实现。
这里，在方法一中使用了INDEX函数、SMALL函数、IF函数和ROW函数，在方法二中还使用了Excel 2007中新增的IFERROR函数。
方法一：

• 选择单元格E3；
• 输入公式：
  =INDEX(B:B,SMALL(IF($A$2:$A$25=$E$1,ROW($A$2:$A$25),65536),ROW(1:1))) & “”
  然后同时按下Ctrl+Shift+Enter键，即输入数组公式。
• 选择单元格E3后下拉至所有单元格。

方法二：

• 选择单元格F3；
• 输入公 式：
  =IFERROR(INDEX($A$2:$B$9,SMALL(IF($A$2:$A$9=$E$1,ROW($A$2:$A$9)-ROW($A$2)+1,ROW($A$9)+1),ROW(1:1)),2),”")
  然后同时按下Ctrl+Shift+Enter键，即输入数组公式。
• 选择单元格F3后下拉至所有单元格。

示例文档下载：

一个复杂的示例：查找不同工作表中的数据并显示满足条件的所有数据
对于VLOOKUP函数来说，其主要缺点是仅能返回与查找条件相匹配的单条数据，不能够返回与某条件相匹配的所有数据，但其优势是能够相当容易地获取与所给条件相匹配的第一条数据，如下图1所示。

图1：使用VLOOKUP函数返回包含某顾客姓名相应的行中的数据是很容易的方式
在Excel 2007中使用结构化引用，VLOOKUP函数不仅容易使用，而且可读性也很强。在示例中，将表Table1中第一列的值与单元格A9中的值相匹配，并从表中第3列返回值。但是，不能返回表中第二个与“Dan”相匹配的值。事实上，很多时候我们都会碰到这样的情况，我们想要返回与条件相匹配的所有值，但是VLOOKUP函数只能返回满足条件的第一个值。
下面，让我们看看如何从表中获取满足条件的所有数据。首先，准备一些要操作的数据。我们以Office自带的Northwind示例数据库的一部分数据来演示，将其中的部分数据导入Excel表中，如下图2、图3、图4所示。

图2：导入到工作表Customers中的“Customer”表并命名为“tblCustomers”

图3：导入到工作表Orders中的“Orders”表并命名为“tblOrders”

图4：导入到工作表Details中的“OrderDetails”表并命名为“tblDetails”
当然，上述数据都不需要自已手工输入，只需从Northwind数据库中导入即可。现在，希望选择订单号（Order Number）后，能够显示该订单的详细信息，如下图5所示。

图5：当选择某订单号后，会显示该订单的详细信息
下面是图5显示的界面的主要设计过程。
步骤1 在工作表相应的单元格中输入下列字符：
单元格B1：Order Number
单元格C3：Order Information
单元格C4：Customer
单元格F4：Order Date
单元格F5：Status
单元格F6：Salesperson
单元格F7：Ship Date
单元格C10：Order Details
单元格C11：Product
单元格D11：Quantity
单元格E11：Unit Price
单元格F11：Discount
单元格G11：Total Price
单元格H11：Status ID
步骤2 创建包含所有订单IDs的一个命名区域，然后使用该名称在数据有效性中创建订单号的下拉列表。为此，单击“公式—定义名称”，在“新建名称”对话框中输入：
名称：OrderIds
引用位置：=tblOrders[ID]
注：数据有效性不能够引用不同工作表中的单元格区域，除非为该区域定义名称。
步骤3 选择单元格D1，将其命名为rngOrderId。
步骤4 选择单元格D1，单击“数据—数据有效性”，在“允许”中选择“序列”，在“来源”框中输入“=OrderIds”。
步骤5 选择单元格C5，输入下列函数：
=VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#标题], 0), FALSE)
步骤6 与单元格C5中的函数相似，设置剩余单元格的查找函数。
C6：=VLOOKUP($C$5,tblCustomers,MATCH(“Address”,tblCustomers[#标题],0), FALSE)
C7：=VLOOKUP($C$5, tblCustomers, MATCH(“City”,tblCustomers[#标题],0), FALSE) & “, ” & VLOOKUP($C$5, tblCustomers, MATCH(“State”,tblCustomers[#标题],0), FALSE) & ” ” & VLOOKUP($C$5, tblCustomers, MATCH(“Zip”,tblCustomers[#标题],0), FALSE)
H4：=VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#标题], 0), FALSE)
H5：=VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#标题], 0), FALSE)
H6：=VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#标题], 0), FALSE)
H7：=VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#标题], 0), FALSE)
至此，完成了我们的界面的上半部分，如图6所示。

图6：已完成的查找界面
接下来，让我们看看后面将要使用的公式中的一些函数。

• 实际上，我们将不会使用VLOOKUP函数。因为需要返回多个条目，因此需要一种方式来返回一组值，而VLOOKUP函数不具备这样的功能。这里，我们使用INDEX函数返回指定行和列交叉部分的值。
• 我们需要指定想要在单元格中显示的数组中的条目，这里可使用SMALL函数和ROW函数来实现。
• 我们希望如果在源数据中添加额外的列时确保数据能够自动调整，这里使用MATCH函数。
• 最后，我们希望有错误处理功能，这里使用IFERROR函数来确保如果在计算中有错误将只是显示空单元格。（IFERROR函数是Excel 2007中新增的函数）

好了，先让我们来看看已经完成的函数，如下图7中的单元格C12所示（注意，这是一个数组公式，其左右两侧的花括号不需要自已输入。在输完公式后按下Ctrl+Shift+Enter组合键即可）。

图7：数组公式用于返回与特定值相匹配的第N项
这个公式看起来很复杂，为便于阅读，将其书写如下：
{
=IFERROR(
INDEX(tblDetails,
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
MATCH(C$11, tblDetails[#标题], 0)
),
“”)
}
首先，看看SMALL函数，该函数接受一组值并从中返回第N小的值。
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
本例中，数组的值由IF函数确定。特别地，如果Details表中某行的Order ID与在下拉列表中选择的Order ID相等，那么添加该行的行号到数组值中。通过使用ROW函数，Excel能够自动调整公式来向下填充单元格。并且，通过使用结构化引用，使得工作表数据能够自动适应源数据的变化。
然后，再看看INDEX函数，该函数接受一个二维数据并返回数组中指定行列位置的值。
INDEX(tblDetails,
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
MATCH(C$11, tblDetails[#标题], 0)
),
我们已经知道，本例中的SMALL函数返回与输入的Order ID相匹配的Order Details列表中ROW(Nth)的值，这里是第一个值即行号。接着需要获取列号，即使用MATCH函数，本例中该函数接受C11的值并查找在Details表中有相同名称的列。
最后使用的是IFERROR函数，如果包含的计算式发生错误，使用空字符串（””）替换错误值。
现在，选择单元格C12，拖动其右下角的填充柄至单元格H12。选择单元格区域C12：H12，拖动填充句柄向下10行或更多。现在，将显示满足该订单号的所有信息，并且当我们改变Order ID时，将会自动更新相关信息。

图8：完成的界面表，使用数组公式向下填充以获取所有相关信息
示例文档下载：