Excel函数的高级应用

Excel函数的高级应用2.1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数1．TRANSPOSE函数TRANSPOSE函数的功能是求矩阵的转置矩阵。

公式为= TRANSPOSE(array)式中，Array—需要进行转置的数组或工作表中的单元格区域。

函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。

【例2-7】假设矩阵A中的值如图2-18中单元格区域A2:C5，求其转置矩阵的步骤如下：图2-18 求转置矩阵（1）选取存放转置矩阵结果的单元格区域，如E2:H4。

（2）单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数TRANSPOSE，在该函数对话框中输入（可用鼠标拾取）单元格A2:C5，按“Crtl+Shift+Enter”组合键，即得转置矩阵如图2-18所示。

利用TRANSPOSE函数可以把工作表中的某些行（或列）排列的数据转换成列（或行）排列的数据。

例如，由于工作需要，要把工作表中的某些行数据改为列数据，若一个一个地改动数据，将是很麻烦也很费时的，而利用TRANSPOSE函数则可以很轻松地进行这项工作。

但需要注意的是，利用TRANSPOSE函数对行（列）数据进行转换，则无法单独修改其中转换单元格区域中的某单元格的数据。

2．MINVERSE函数MINVERSE函数的功能是返回矩阵的逆矩阵。

公式为= MINVERSE(array)式中，array—具有相等行列数的数值数组或单元格区域。

MINVERSE函数的使用方法与TRANSPOSE函数是一样的。

在求解线性方程组时，常常用到MINVERSE函数。

3．MMULT函数MMULT函数的功能是返回两数组的矩阵乘积。

结果矩阵的行数与array1 的行数相同，列数与array2 的列数相同。

公式为= MMULT(array1,array2)式中array1, array2—要进行矩阵乘法运算的两个数组。

array1的列数必须与array2 的行数相同，而且两个数组中都只能包含数值。

array1和array2可以是单元格区域、数组常数或引用。

如果单元格是空白单元格或含有文字串，或是array1的行数与array2 的列数不相等时，则函数MMULT返回错误值#V ALUE!。

同样地，由于返回值为数组公式，故必须以数组公式的形式输入。

以例2-7的原矩阵和其转置矩阵为例，它们的乘积矩阵求解方法如下：（1）选取存放乘积矩阵结果的单元格区域，如J2:L5。

（2）单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数MMULT，在该函数对话框中的array1栏中输入（可用鼠标拾取）单元格区域A2:C5，在array2栏中输入单元格区域E2:H4，然后按“Crtl+Shift+Enter”组合键，即得矩阵的乘积如图2-18所示。

2.1.2.13 ROUND函数ROUND函数的功能是返回某个数字按指定位数舍入后的数字。

公式为= ROUND(number,num_digits)式中number—需要进行舍入的数字；num_digits—指定的位数，按此位数进行舍入。

如果num_digits大于0，则舍入到指定的小数位；如果num_digits等于0，则舍入到最接近的整数；如果num_digits小于0，则在小数点左侧进行舍入。

利用ROUND函数可以防止利用格式工具栏上的【增加小数位数】或【减少小数位数】所带来的看起来“假数据”问题的出现，使得工作表上显示的数据真实可靠。

实际上，如果需要调整数据的小数位数，最好使用ROUND函数，而不要使用格式工具栏上的【增加小数位数】或【减少小数位数】按钮。

例如，若单元格A1中的数据为14.3772，若使用格式工具栏上的【减少小数位数】按钮将小数位数设为两位，则单元格A1中的数据显示为14. 38，看起来似乎单元格A1的数据为14.38，但实际上仍为14.3772。

若在单元格B1中输入公式“=3*A1”，则单元格B1中的数据显示为43.13，也许“不明真相”的人认为单元格B1的数据算错了（14.38乘以3应该等于43.14），但实际上单元格的数据为43.1316，这种看起来的“假”数据可能会对实际工作带来不便。

因此，正确的方法应是：单元格B1中应输入公式“=ROUND(3*ROUND(A1,2),2)”，结果为43.14，即先将单元格A1的数据用函数ROUND四舍五入，然后再对计算后的数据四舍在我们建立一个图表之后，还可以通过向工作表中加入更多的数据系列或数据点来更新它。

用来增加数据的方法取决我们想更新的图表的种类——内嵌图表或图表。

如果要向工作表中的内嵌图表中添加数据，我们可以拖放该工作表中的数据。

使用复制和粘贴是向图表中添加数据最简单的方法。

例如，我们为上例中的销售统计图增加一个“内存”栏目。

增加图表数据的操作步骤如下：（1）输入“内存”行的数据。

（2）单击激活图表，可以看到数据出现带颜色的线框，在Excel 2000中它们被称作选定柄。

因此如果在工作表上拖动蓝色选定柄，将新数据和标志包含到矩形选定框中，可以在图表中添加新分类。

如果只添加数据系列，在工作表上拖动绿色选定柄，将新数据和标志包含到矩形选定框中。

如果要添加新分类和数据点，请在工作表上拖动紫色选定柄，将新数据和标志包含到矩形选定框中。

（3）向下拖动紫色拖动柄将内存数据包含，如图8-16所示。

（4）送开鼠标后就可以看到如图8-17的显示了。

如果要删除图表中的数据系列，可以向上拖动鼠标，将数据区中图表数据区中移走即可。

上面提到的方法对于相邻数据处理最为直接。

但是如果数据是不相邻的，就需要从下列方法选择一个。

方法一是，重新选择数据区域。

操作步骤是：（1）先激活图表，然后按下“图表向导”按钮，出现图8-2的对话框。

（2）按下“下一步”按钮，在对话框中重新选择数据区域。

（3）按下“完成”按钮即可。

方法二是，通过复制和粘贴来完成。

操作步骤是（1）选择含有待添加数据的单元格。

如果希望新数据的行列标志也显示在图表中，则选定区域还应包括含有标志的单元格。

（2）单击“复制”按钮。

（3）单击该图表。

（4）如果要让Microsoft Excel 自动将数据粘贴到图表中，单击“粘贴”按钮；如果要指定数据在图表中的绘制方式，单击“编辑”菜单的“选择性粘贴”命令，然后选择所需的选项。

对于不必要在图表中出现的数据，我们还可以从图表中将其删除。

删除图表中数据的操作方法如下：(1) 激活图表。

选择要清除的序列，如图8-18所示。

(2)执行“编辑”菜单中“清除”子菜单中的“系列”命令，就会看到清除图表数据后的图表，如图8-19所示。

应当说明的是：我们清除图表中的数据，并不会影响工作表中单元格的数据。

我们可以看到虽然图表已经清除，但工作表中的数据并未被清除掉。

提示：增加或删除数据后，Excel会自动更新已有的图例。

每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。

涉及函数：A VERAGE、COUNTIF 、IF、ISERROR、MAX、MIN、RANK、SUM 1、启动Excel，同时选中A1至K1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题（参见图1）。

2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（参见图1）。

3、选中J3单元格，输入公式：=SUM(C3:I3)，用于计算第一位学生的总分。

4、选中K3单元格，输入公式：=RANK(J3,$J$3:$J$62)，计算出第一位学生总分成绩的名次（此处，假定共有60位学生）。

注意：如果将上述公式修改为：=COUNTIF($J$3:$J$62,">="&am J3)，同样可以计算出第一位学生的名次。

5、同时选中J3和K3单元格，将鼠标移至K3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至K62单元格，完成其他学生的总分及名次的统计处理工作。

6、分别选中C63、C64单元格，输入公式：=MAX(C3:C62)和=MIN(C3:C62)，用于统计“语文”学科的最高分和最低分。

7、选中C65单元格，输入公式：=IF(ISERROR(AVERAGE(C3:C62)),"",AVERAGE(C3:C62))，用于统计“语文”学科的平均分。

注意：直接用“=AVERAGE(C3:C64)”公式也可以计算平均分，但是，如果成绩表中没有输入成绩时，这一公式将显示出一个错误的值，因此，我们用“ISERROR”函数来排除这一错误。

8、选中C66单元格，输入公式：=SUM(C3:C62)，用于统计“语文”学科的总分。

9、同时选中C63至C66单元格，用“填充柄”将上述公式复制到D63至J66单元格中，完成其它学科及总分的最高分、最低分、平均分和总分的统计工作。

至此，一个基本的成绩统计表制作完成（参见xls文件下载），下面我们来进一步处理一下。

10、选中C3单元格，执行“格式、条件格式”命令，打开“条件格式”对话框（如图2），按最左边一个方框右侧的下拉按钮，在随后出现的下拉列表中，选“公式”选项，并在右侧的方框中输入公式：=C3>=AV ERAGE(C3:C62)，然后按“格式”按钮，打开“单元格格式”对话框，将字体颜色设置为“蓝色”。

再按“添加”按钮，仿照上面的操作，添加一个公式为“=C3注意：经过这样的设置后，当学生的“语文”成绩大于或等于平均分时，显示蓝色，反之显示红色。

[1][2]下一页11、再次选中C3单元格，按“格式”工具条上的“格式刷”按钮，然后在D3至J3单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学科及总分的条件格式设置工作。

12、再一次选中C3单元格，打开“条件格式”对话框，将其中的两项公式修改为：=C3>=A VERAGE($C$3:$C$62)和=C3 依照上面的操作，将D3至J3单元格的条件格式中的公式修改为绝对引用（添加“$”符号）。

注意：这样做的目的，是为了将其条件格式，通过“格式刷”复制到下面的各单元格中。

13、同时选中C3至J3单元格，按“格式”工具条上的“格式刷”按钮，然后在C4至J62单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学生各科成绩及总分的条件格式设置工作。

把学生的成绩填入到表格中试试看，效果不错吧。

如果你经常要统计学生的成绩，我们将其保存为模板，方便随时调用。

< AN cla =f14>14、将工作表中的学生成绩等内容删除，执行“文件、保存（另存为）”命令，打开“另存为”对话框（如图4），按“保存类型”右侧的下拉按钮，在随后出现的下拉列表中，选“模板（*.xlt）”选项，然后给定一个名称（如“成绩统计”），按下保存按钮。