1.2 公式的高级应用

为了解决一些比较复杂的实际问题，并快速地创建计算公式，有时需要使用更高级的应用公式方面的知识和技能。例如，可以使用表达式解决某些特殊问题，使用名称来简化公式，使用数组公式解决更加复杂的汇总计算问题等。本节将介绍这方面的内容。

1.2.1 在公式中使用表达式

在实际财务管理工作中，有时需要根据多个条件对数据进行处理，例如计算某个客户在某个账龄区间的应收账款，计算某个时间段内每种产品的销售额，计算每个分公司在每个城市销售每种产品的销售额等，解决这些问题都涉及多条件数据处理问题。

对于各种条件下的数据处理分析问题，往往需要联合使用IF函数、AND函数和OR函数来进行判断。不过，由于 Excel 对函数的嵌套层数有限制，因此在很多情况下，由于嵌套的限制导致无法用一个公式来解决问题。而若在公式中合理使用条件表达式就可以克服嵌套函数的缺点，这样也会使公式的结构和逻辑更加清楚。

1.什么是条件表达式

条件表达式是指根据指定的条件准则对两个项目进行比较，得到 TRUE或者FALSE的判定值。逻辑值TRUE和FALSE分别以1和0来代表，在Excel中也遵循这个规定，因此在公式中逻辑值TRUE和FALSE分别以1和0来参与运算。

例如，考虑下面的公式：

=IF（A1＞100,1,0.9）*A1

这个公式的含义是利用函数IF来进行判断：如果单元格A1的数值大于100，则公式的运算结果等于单元格A1中的数据乘以1，否则公式的运算结果等于单元格A1中的数据乘以0.9，这里就在IF函数的第一个参数使中用了条件表达式“A1＞100”。

上面的这个公式如果利用条件表达式来代替IF函数的话，可表示为：

=（（A1＞100）*1+（A1＜=100）*0.9）*A1

由此可见，在公式中可以使用各种条件表达式，这样可以更方便地创建能够完成更复杂的任务的公式。

2.简单的条件表达式

如果只需要对两个项目（常数、公式、单元格引用、函数等）进行比较，那么利用简单的比较运算符就可以建立简单的条件表达式。

例如，下面的公式都是简单的条件表达式，它们可对两个项目进行比较：

=A1＞B1

=A1＜＞（C1-200）

=A1="彩电"

=SUM（A1:A10）＞=2000

这些条件表达式都会返回逻辑值TRUE或FALSE。

逻辑运算符是条件表达式中表示逻辑关系的最基本元素，例如，在表达式“=A1＞B1”中，大于号“＞”就是一个逻辑运算符，它用来比较单元格A1和B1的数据大小关系。

要特别注意的是，在公式中使用条件表达式进行逻辑测试时，逻辑运算符是所有运算符中运算顺序最低的，因此，为了得到正确的计算结果，最好使用一组小括号将每个条件表达式括起来。例如，上面的公式写成如下的形式会是一种比较好的习惯：

=（A1＞B1）

=（A1＜＞（C1-200））

=（A1="彩电"）

=（SUM（A1:A10）＞=2000）

3.复杂的条件表达式

在实际工作中，为了完成更为复杂的任务，有时可以将两个以上的条件表达式组合在一起，建立更为复杂的条件表达式。例如，可以使用AND函数或OR函数来构造复杂的条件表达式，或者使用乘号（*）或加号（+）建立更加复杂的条件表达式。

下面的两个公式是分别使用AND函数和乘号（*）构造的条件表达式，它们的运算结果是一样的：

=IF（AND（A1＞=100,A1＜1000）,0.9,0.8）*B1=IF（（A1＞=100）*（A1＜1000）,0.9,0.8）*B1

下面的两个公式是分别使用OR函数和加号（+）构造的条件表达式，它们的运算结果是一样的：

=IF（OR（A1="彩电",A1="冰箱"）,0.9,0.8）*B1

=IF（（A1="彩电"）+（A1="冰箱"）,0.9,0.8）*B1

下面的两个公式是分别使用AND函数、OR函数和加号（+）、乘号（*）构造的条件表达式，它们的运算结果是一样的：

=IF（AND（OR（商品="彩电",商品="冰箱"）,数量＞2000）,单价*0.9,单价）

=IF（（商品="彩电"）+（商品="冰箱"）*（数量＞2000）,单价*0.9,单价）

AND函数与乘号（*）的功能是一样的，它们都是构建多个条件的与关系，也就是这些条件必须同时满足。

OR函数和加号（+）的功能是一样的，它们都是构建多个条件的或关系，也就是这些条件只要有一个满足即可。

乘号（*）和加号（+）在构造复杂的条件表达式方面，比AND函数和OR函数更加方便，构建的公式也更容易理解。

1.2.2 在公式中使用名称

默认情况下，工作表中的单元格的名称是A1、B1等用列标和行号组成的形式。用户根据需要还可以给工作表中某些单元格或单元格区域定义新的名称，并用所定义的名称代替原来的单元格或单元格区域的地址；或者对工作表的某些单元格或单元格区域进行运算后得到一个新的数据或数据数组，并将此数据或数据数组用一个新名称来代表。

使用名称建立公式有许多方面的好处，例如，合理使用名称不仅可以增强公式的可读性，使公式易于被人理解，而且还有助于快速准确地创建公式，提高公式编辑的准确性，并且名称还可以当做变量来使用，这样可以简化计算公式，从而使对数据分析和处理的工作变得更加快捷和高效。

1.定义名称的规则

定义名称要遵循一定的规则，具体要求如下所述。

● 名称的长度不能超过255个字符。但建议用户最好不要使用这么长的名称，因为名称太长就违背了定义名称的初衷。

● 名称中不能含有空格，但可以使用下画线和句点。例如，名称不能是“Month Total”，但可以是“Month_Total”或“Month.Total”。

● 名称中不能使用除下画线和句点以外的其他符号。尽管Excel的帮助信息中没有说明是否可以使用某些符号，但在Excel中定义名称时确实可以使用问号（？）和反斜线（\），不过，这两个符号不能作为名称的开头。

● 名称的第一个字符必须是字母或汉字，不能使用单元格地址或阿拉伯数字。

● 避免使用Excel 本身预设的一些有特殊用途的名称，如 Extract、Criteria、Print_Area、Print_Titles、Database等。

● 名称中的字母不区分大小写。例如，名称“MYNAME”和名称“myname”是相同的，在公式中使用哪个都是可以的。

2.在Excel 2003中为单元格或单元格区域定义名称的方法

在Excel 2003中，为单元格或单元格区域定义名称有三种基本方法：（1）使用“定义名称”对话框定义名称；（2）使用名称框定义名称；（3）使用“指定名称”对话框定义名称。下面举例说明这三种定义名称的方法。

分别采用三种不同的方法对如图1-12所示的单元格区域B2:B13定义名称“销售量”。

方法1：使用“定义名称”对话框定义名称。

利用“定义名称”对话框来定义名称的具体步骤如下所述。

1 选取要定义名称的单元格或单元格区域。

2 单击“插入”→“名称”→“定义”命令，打开“定义名称”对话框。

3 如果选择的单元格区域上面的一个单元格或左边的一个单元格是文本数据， Excel会自动将该文本作为默认的名称，如图1-12所示，单击“确定”按钮即可将该选定的单元格或单元格区域命名为默认的名称。

如果用户想要使用其他名称，可在“在当前工作簿中的名称”文本框中输入新的名称，然后单击“确定”按钮即可。

如果发觉选择的单元格区域有误，可以在“引用位置”输入框中修改单元格区域的引用。

利用“定义名称”对话框可以定义多个名称，即每定义一个名称后单击“添加”按钮，可以在不关闭“定义名称”对话框的情况下继续定义其他单元格的名称。其具体方法是：首先在“在当前工作簿中的名称”文本框中输入名称，在“引用位置”输入框中输入单元格区域（最好用鼠标选取要命名的单元格或单元格区域），然后单击“添加”按钮，就定义好了一个名称。然后再定义其他的名称。

另外还需要说明的是，尽管按上述过程定义名称时对单元格的引用没有指明该单元格所属的工作表，但当完成名称定义后，Excel 会自动为引用的单元格加上工作表的限制。例如，名称“销售量”的单元格引用为：

=Sheet1!$B$2:$B$13

这里，在具体的单元格区域前面有工作表的引用“Sheet1!”的含义是，名称“销售量”指向的是工作表“Sheet1”的单元格区域“$B$2:$B$13”，而不是其他工作表的单元格区域“$B$2:$B$13”。

方法2：使用名称框定义名称。

使用名称框定义名称是一种比较简单且适用性更强的方法。其基本步骤是：首先选取要定义名称的单元格区域（不论是整行、整列、连续的单元格区域，还是不连续的单元格区域），然后在名称框中输入名称，按【Enter】键即可，如图1-13所示。

在名称框中输入名称后，千万不要忘了按【Enter】键，这样才能完成名称的定义工作。如果在名称框中输入名字后，又用鼠标单击了其他单元格，那么定义名称的工作并没有完成。

需要注意的是，用户无法利用名称框来修改名称指定的单元格引用范围。要想这么做，只能在“定义名称”对话框里进行。

方法3：使用“指定名称”对话框定义名称。

使用“指定名称”对话框定义名称适合于要批量定义名称的场合。当工作表的数据区域有行标题或列标题时，利用“指定名称”对话框可快速定义多个名称。

例如，要把数据区域的每行分别定义一个名称，每行的名称正好是该行最左边一列单元格中的文字，那么批量定义名称的具体步骤如下所述。

1 选择要定义行名称和列名称的工作表数据区域（要包含行标题或列标题）。

2 单击“插入”→“名称”→“指定”命令，打开“指定名称”对话框，如图1-14所示。对话框中的复选框选择标记是Excel根据所选区域分析的结果，它是将每行的单元格区域定义为最左列单元格文本表示的名称。

3 单击“确定”按钮，Excel就会建立一系列的名称。打开“定义名称”对话框，可以查看定义的名称，如图1-15所示。

这种批量定义的名称代表的是固定的行数据区域或者列数据区域。当要定义名称的数据列增加或者数据行增加以后，可以再次按上述过程进行操作，这样可以对数据区域重新指定一次名称。

3.在Excel 2007中为单元格或单元格区域定义名称的方法

在Excel 2007中，定义和使用名称的相应功能被放到了“公式”选项卡的”定义的名称”功能组中，如图1-16所示。

在Excel 2007工作表中定义名称与在Excel 2003中定义名称相比，所使用的命令和定义名称对话框有所不同，但利用名称框来定义名称的方法则是完全相同的，即都是先选取单元格区域，然后在名称框中输入名称后按【Enter】键。此外，当批量定义名称时，Excel 2007与Excel 2003也是基本相同的，只是使用的命令不同。

在Excel 2007中，利用“定义名称”对话框定义名称有3种基本的方法：利用“新建名称”对话框、利用“名称管理器”对话框和利用“以选定区域创建名称”对话框。

方法1：利用“新建名称”对话框

利用“新建名称”对话框定义名称的具体步骤是：首先选择要定义名称的单元格或单元格区域，单击”定义的名称”功能组中的“定义名称”按钮，打开“新建名称”对话框，如图1-17所示；然后在此对话框中，在“名称”文本框中输入名称，在“范围”下拉列表中指定该名称的适用范围（即名称的级别），如果需要还可以重新在“引用位置”文本框中输入单元格区域地址；最后单击“确定”按钮。

如果需要为定义的名称添加一些说明信息，还可以在“备注”文本框中输入说明文字，这样便于以后了解该名称的用途、使用方法等。

方法2：利用“名称管理器”对话框

利用“名称管理器”对话框定义名称的具体步骤是：单击“定义的名称”功能组中的“名称管理器”按钮，打开“名称管理器”对话框，如图1-18所示；在此对话框中，列出了本工作簿中已经定义的所有名称的详细信息，包括名称、名称所代表的数值、引用位置、范围（即级别）以及备注信息。

在“名称管理器”对话框的左上角，有一个“新建”按钮，单击它就打开如图1-17所示的“新建名称”对话框，这样就可以在该对话框中定义名称了。名称定义完毕后则返回“名称管理器”对话框，同时新定义的名称被添加到“名称管理器”对话框列表中，如图1-19所示。

利用“名称管理器”对话框定义名称要比单独使用“新建名称”对话框有更大的优越性，可以随时查看定义名称的详细信息，便于了解已定义的名称是不是所需要的或者是否有错误。如果定义的名称不对，还可以单击“名称管理器”对话框上的“编辑”按钮，打开“编辑名称”对话框，对名称的文字和引用位置进行编辑，如图1-20所示。

如果已定义的某个名称不是所需要的，可以在选择该名称后，单击“删除”按钮将其删除。

方法3：利用“以选定区域创建名称”对话框

在Excel 2007中，利用“以选定区域创建名称”对话框可以批量定义名称，其基本方法与Excel 2003中利用“插入”→“名称”→“指定”命令自动建立名称的方法基本是一样的。即单击“定义的名称”功能组中的“根据所选内容创建”按钮，即可打开“以选定区域创建名称”对话框，如图1-21所示，那么就可以根据实际情况勾选相应的复选框定义名称了。

4.运用名称公式汇总销售数据

如图1-22所示是某公司的销售数据，现在要求按照如图1-23所示的表格结构按大区对本月计划和实际销售额进行汇总计算。

为了解决这个问题，可以使用SUMIF函数进行汇总计算。但是如果不使用名称公式，那么在SUMIF函数中必须使用单元格的引用。例如，东北大区的本月计划销售额总和的计算公式如下：

=SUMIF（销售!B:B,A2,销售!G:G）

若不去查看工作表“销售”的数据情况，单凭上面这个公式，是难以理解“销售!B:B”代表什么，“销售!G:G”代表什么。此外，如果要计算东北大区的实际销售额，则需要重新设计公式并引用新的单元格区域，即：

=SUMIF（销售!B:B,A2,销售!H:H）

由此可见，在对不同的项目进行汇总计算时，必须在两个工作表之间不断切换，以便引用相应的单元格区域，这显然是很麻烦的，稍有不慎就会出现引用错误的情况。

但是，若能对有关的单元格区域定义名称并使用公式的话，就会使公式变得非常简单。只要定义完毕名称，那么在当前的汇总工作表中即可迅速完成汇总公式的输入。

具体步骤如下所述：

切换到“销售”工作表，单击数据区域的任一单元格，按【Ctrl+A】组合键，选择整个数据区域，按照前面介绍的“指定名称”对话框批量定义名称的方法（Excel 2003）或者“以选定区域创建名称”对话框批量定义名称的方法（Excel 2007），将数据区域的每列从第2行开始的数据列，定义为第1行的标题名称，如图1-24所示。

定义完毕名称以后，就可以快速地输入下列汇总计算公式：

单元格B2：=SUMIF（大区,A2,本月指标）

单元格C2：=SUMIF（大区,A2,实际销售金额）

单元格F2：=SUMIF（大区,A2,销售成本）

汇总计算结果如图1-25所示。

1.2.3 数组公式及其应用

数组公式是指对数组进行计算的公式。前面介绍的公式都是只进行一个简单的计算过程并返回一个计算结果。当需要对两组或两组以上的数据进行计算并返回一个或多个计算结果时，则就需要使用数组公式。使用数组公式能大大简化计算过程，减少计算工作量，提高数据处理效率。

1.数组公式的特征

数组公式有以下的特征。

● 单击数组公式所在的任一单元格，就可以在公式编辑栏中看到公式前后出现的大括号“{}”，如果在公式编辑栏中单击鼠标，大括号就会消失。

●输入数组公式的每个单元格中的公式是完全相同的。

● 必须同时按【Ctrl+Shift+Enter】组合键才能得到数组公式，否则，如果只按【Enter】键，那样得到的是普通公式。

● 公式中必定有单元格区域的引用，或者必定有数组常量。

● 不能单独对数组公式所涉及的单元格区域中的某一个单元格进行编辑、删除或移动等操作。

● 数组公式可以存在于多个单元格，也可以是仅输入到一个单元格。即使输入到一个单元格的数组公式，也必须同时按【Ctrl+Shift+Enter】组合键。

2.数组公式的基本输入方法

数组公式的输入方法和基本步骤如下。

1 选定单元格或单元格区域。如果数组公式只返回一个结果，则单击需要输入数组公式的某个单元格；如果数组公式将返回多个结果，则选定需要输入数组公式的单元格区域。

2输入数组公式。

3 同时按【Ctrl+Shift+Enter】组合键，则Excel 自动在公式的两边加上大括号{}。

特别要注意的是，第3步相当重要，只有输入公式后同时按【Ctrl+Shift+Enter】组合键，系统才会把公式视为一个数组公式。否则，如果只按【Enter】键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。

此外还应注意的是，数组公式前后的大括号是系统自动加上去的，仅仅表明该公式是一个数组公式。切不可在输入数组公式的时候在数组公式的两端添加大括号。

下面我们举例说明数组公式的输入方法。

对于如图1-26所示的数据，现要计算各种产品的销售额以及销售总额。一般的方法是计算某种产品的销售额，然后复制公式，从而得到所有产品的销售额，最后再将各种产品的销售额进行加总，得到销售总额。

上述这种类型的计算，也可以使用数组公式来完成，具体步骤和方法如下。

1 选取单元格区域D2:D6，如图1-27所示。

2 在键盘上直接输入等号“=”，如图1-28所示。

3 用鼠标拾取单元格区域B2:B6，如图1-29所示。

4 在键盘上输入乘号“*”，如图1-30所示。

5 用鼠标拾取单元格区域C2:C6，如图1-31所示。

6 同时按【Ctrl+Shift+Enter】组合键，则Excel自动在公式的两边加上大括号{}，并在所选取的各单元格中显示计算结果，如图1-32所示。

上面的数组公式是在连续的单元格区域内输入的。有时候，也可能仅需要在一个单元格中输入数组公式，以便将某个复杂的计算过程用一个公式来完成。例如，要计算各种产品的销售总额，一般的方法是先计算各种产品的销售额，然后再将各种产品的销售额进行加总。实际上，完全可以用一个公式计算所有产品的销售总额，具体方法和步骤如下所述。

1 选取单元格D7，如图1-33所示。

2 单击编辑栏上的“插入函数”按钮，打开“插入函数”对话框，找到SUM函数并打开 SUM函数的“函数参数”对话框，在对话框的第一个参数框中输入“B2:B6*C2:C6”，这个过程可先用鼠标拾取单元格区域B2:B6，手工输入一个乘号“*”，然后再用鼠标拾取单元格区域C2:C6，如图1-34所示。

3 按【Ctrl+Shift+Enter】组合键，则会在单元格D7中得到一个数组公式“{=SUM（B2:B6*C2:C6）}”，并显示计算结果，如图1-35所示。

由此可见，只利用一个数组公式，就可以立即计算出各种产品的销售总额，这个过程非常简便，省去了计算每种产品销售额的中间计算过程。在这种情况下，即使删除了每种产品销售额的中间计算结果，仍能得到所有产品的销售总额。

特别要注意的是，不能在合并单元格中输入数组公式。若一定要在合并单元格中输入数组公式，应当先取消单元格的合并，然后在拟合并的任何一个单元格中输入数组公式，最后再重新合并单元格，这样才能将数组公式存放到合并单元格中，其结果就如同在合并单元格中输入了数组公式一样。

3.数组公式的应用

在上一个案例中，若要按照性质、大区、省份对销售额等指标进行多层次的汇总，要求的汇总表格结构如图1-36所示，那么该如何创建计算公式呢？

要汇总不同门店性质、不同大区的门店数，会涉及两个条件；同样，要汇总不同门店性质、不同大区、不同省份的销售数据，会涉及三个条件，这些问题都属于多条件计数和多条件求和的问题，不能使用SUMIF函数来解决。

在Excel 2003中，没有多条件计数和多条件求和的函数，但可以使用数组公式，也可以使用SUMPRODUCT函数（这个函数是数组运算函数）来解决上述的问题。

在Excel 2007中，新增了多条件计数函数COUNTIFS和多条件求和函数SUMIFS，这样就使得多条件计数和多条件求和的问题变得简单了，不过，这两个函数仅适用于解决只包含几个与条件情况下的计数与求和问题，而对于包含几个或条件，或者与条件和或条件的混合问题，还是需要使用数组公式来解决的。

使用数组公式解决本案例的问题的具体方法和步骤如下所述。

1 在单元格C2中输入下面的数组公式，计算东北地区的自营门店数：

=SUM（（性质="自营"）*（大区="东北"））

在这个公式中，条件表达式“（性质="自营"）”的含义是判断各个店铺的性质是否为“自营”，如果是，返回的结果会是 TRUE，否则返回的结果会是 FALSE，由此可见，条件表达式“（性质="自营"）”的运行结果是由TRUE和FALSE组成的一个数组。

同样，条件表达式“（大区="东北"）”的含义是判断各个店铺所在的大区是否是“东北”，如果是，返回的结果会是 TRUE，否则返回的结果会是 FALSE。因此，条件表达式“（大区="东北"）”的运行结果也是由TRUE和FALSE组成的一个数组。

上述两个条件表达式的结构都是维数相同，由TRUE和FALSE组成的数组，用乘号（*）将两个条件表达式连接起来，其计算结果也是一个数组，但这个数组的结果是由数字1和0组成的，数字1表示某个店铺的性质既是“自营”，所在大区又是“东北”；而数字0则表示某个店铺的性质是“自营”但所在大区不是“东北”，或者某个店铺的性质不是“自营”但所在大区是“东北”。

最后使用SUM函数把上面所得到的由数字1和0组成的数组的各个元素相加，就得到了东北地区的自营门店数。

2 在单元格E2中输入下面的数组公式，计算东北地区辽宁省自营门店的本月指标总和：

=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*本月指标）

这个公式是由3个与条件组成的，条件表达式“（性质="自营"）*（大区="东北"）*（省份=$D2）”的计算结果是由数字1和0组成的数组，将这个数组的各个元素（1或0）与名称“本月指标”代表的各个单元格中的数据相乘，得到一个由同时满足3个条件的实际数据和不同时满足3个条件的数据0组成的数组，最后使用SUM函数将该数组的各个元素相加，就得到了东北地区辽宁省自营门店的本月指标总和来。

3 按照与上述过程同样的道理，可以创建其他的汇总计算公式。例如汇总实际销售金额（单元格F2）的计算公式为：

=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*实际销售金额）

汇总销售成本（单元格I2）的计算公式为：

=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*销售成本）

本案例部分已完成的公式如图1-37所示。对本案例感兴趣的读者，可在如图1-37所示的汇总表中完成其他公式的设置。

说明：如果使用SUMPRODUCT函数解决这个问题，输入的计算公式应是普通公式（即不需要按【Ctrl+Shift+Enter】组合键）。例如，在单元格C2和单元格E2中应输入的公式分别为：

单元格C2：=SUMPRODUCT（（性质="自营"）*1,（大区="东北"）*1）

单元格E2：=SUMPRODUCT（（性质="自营"）*1,（大区="东北"）*1,（省份=$D2）*1,本月指标）

注意要将表达式乘以数字1，以便将逻辑值TRUE和FALSE转换为数字1和0。

如果用户使用的是Excel 2007，这种多个与条件计数和多条件求和的计算问题就会变得很简单，只需使用函数COUNTIFS和SUMIFS就可以了。

例如，在单元格C2中可以输入下面的公式

=COUNTIFS（性质,"自营",大区,"东北"）

在单元格E2中可以输入下面的公式

=SUMIFS（本月指标,性质,"自营",大区,"东北",省份,$D2）

1.2.4 快速准确创建复杂的计算公式

很多实际问题不是使用简单的函数和公式就可以解决的，而是往往需要创建非常复杂的计算公式。对于很复杂的计算公式，如果直接在单元格中输入，是很容易出错的。下面结合实际案例，介绍一种快速准确创建复杂计算公式的方法，即公式分解与综合法。

如图1-38所示的A列至C列数据为从数据库中导出的数据，其中C列的批次是包含数量和单位信息的混合字符串。现在要求把批次一列的数据分成数量和单位两列数据，分别保存在D列和E列，以便于以后对这些数据进行统计分析。

显然，这样的问题不是利用一个简单的公式就能解决的，而是需要使用数组公式。

要将批次中的数量和单位分开，需要了解批次数据中从左边开始哪些字符是数字，到哪个字符为止由数字变为了文本（汉字或字母），这样才能利用文本函数LEFT把其中的数量取出来，再利用文本函数MID把批次取出来。下面以第3行中单元格C3的批次数据为例，说明解决这个问题的思路，以及创建公式的方法和步骤。

要想判断批次数据中从左边开始哪些字符是数字，到哪个字符为止由数字变为了文本，首先需要知道批次数据有多少个字符，然后利用MID函数把每个字符取出来，再使用LEN函数得到批次数据的长度，最后利用INDIRECT函数和ROW函数得到一个从1开始到批次数据长度结束的自然数序列。具体操作方法为：选择单元格区域G2:G15（或者G列从第2行开始到某行为止，这个行数要大于批次数据字符长度），输入数组公式“=ROW（INDIRECT（"1:"&LEN（C3）））”，如图1-39所示。

有了这个自然数序列，就可以利用MID函数把批次数据的各个字符分别提取出来了，具体方法为：选择单元格区域H2:H15，输入数组公式“=MID（C3,G2:G15,1）”，如图1-40所示。

由于利用MID函数从字符串中取出的数据无论原来是数字还是文本，都是被处理成文本数据的，因此需要把提取出来的批次数据中代表数量的数字转换成数值型数据，具体方法是：选择单元格区域I2:I15，输入数组公式“=1*H2:H15”，如图1-41所示。

显然，从批次数据左侧开始取出的各个数据，只要开始出现了错误值，就表明从该字符开始就是单位了，因此需要判断从哪个位置开始出现了错误值，具体方法是：选择单元格区域J2:J15，输入数组公式“=ISERROR（I2:I15）”，如图1-42所示。

这样，就得到了批次数据各个字符串是否是数值的结果（FALSE 表示是数值， TRUE 表示是文本），而第一个出现 TRUE的位置就是单位的位置，因此可以利用MATCH函数把这个位置确定出来，具体方法是：选择单元格K2，输入公式“=MATCH（TRUE,J2:J15,0）”，得到这个位置的序号数，如图1-43所示。

知道了批次数据中哪个字符的位置是单位后，利用LEFT函数就可以很容易地把数量数字取出，利用MID函数把单位取出，具体方法是：选择单元格L2，输入公式“=1*LEFT（C3,K2-1）”，得到批次数据中的数量数字，如图1-44所示。

最后，再按照与上述过程相反的顺序，将中间的计算过程逐一消除，把中间计算过程的公式综合成一个公式，具体步骤如下。

1 单元格L3的公式引用了单元格K2的结果，而单元格K2的计算公式为“=MATCH（TRUE,J2:J15,0）”，因此，应把单元格K2的计算公式去掉等号，仅仅复制等号后面的函数本身，并把它替换掉单元格L3计算公式中的单元格引用K2，得到新的计算公式：

=1*LEFT（C3,MATCH（TRUE,J2:J15,0）-1）

2 上面这个公式中引用了单元格区域J2:J15的结果，而单元格区域J2:J15的计算公式为“=ISERROR（I2:I15）”，因此，应把上述公式中的单元格引用J2:J15替换为ISERROR（I2:I15），得到新的计算公式（需要注意从此开始是数组公式了，因此要按【Ctrl+Shift+Enter】组合键）：

=1*LEFT（C3,MATCH（TRUE,ISERROR（I2:I15）,0）-1）

3 上面这个公式中引用了单元格区域I2:I15的结果，而单元格区域I2:I15的计算公式为“=1*H2:H15”，因此，应把上述公式中的单元格引用I2:I15替换为1*H2:H15，得到新的计算公式：

=1*LEFT（C3,MATCH（TRUE,ISERROR（1*H2:H15）,0）-1）

4 上面这个公式中引用了单元格区域H2:H15的结果，而单元格区域H2:H15的计算公式为“=MID（C3,G2:G15,1）”，这样，把上述公式中的单元格引用H2:H15替换为MID（C3,G2:G15,1），得到新的计算公式：

=1*LEFT（C3,MATCH（TRUE,ISERROR（1*MID（C3,G2:G15,1））,0）-1）

5 上面这个公式中引用了单元格区域G2:G15的结果，而单元格区域H2:H15的计算公式为“=ROW（INDIRECT（"1:"&LEN（C3）））”，因此，应把上述公式中的单元格引用G2:G15替换为ROW（INDIRECT（"1:"&LEN（C3））），得到新的计算公式：

=1*LEFT（C3,MATCH（TRUE,ISERROR（1*MID（C3,ROW（INDIRECT（"1:"&LEN（C 3）））,1））,0）-1）

6 上面这个公式就是最终需要的公式，将这个公式本身原封不动地复制到单元格D3（要注意将公式复制到单元格后，按【Ctrl+Shift+Enter】组合键），就得到了批次数据中的数量。

7 在单元格E3中输入公式“=MID（C3,LEN（D3）+1,999）”，即得到批次数据中的单位。

通过上面案例的分析与公式的创建过程，我们可以了解到，很多实际问题的解决是非常复杂的，需要使用多个函数，通过一系列的计算才能得到正确的结果，这不仅要求读者熟练掌握函数的应用，更为重要的是要弄懂题意，分析问题的本质，要有一个解题的思路，并对计算过程进行分解，最后再对中间计算过程进行综合，得到最终的计算公式。