EXCEL应用技巧42招.docx

EXCEL应用技巧42招.docx

《EXCEL应用技巧42招.docx》由会员分享，可在线阅读，更多相关《EXCEL应用技巧42招.docx（23页珍藏版）》请在冰豆网上搜索。

EXCEL应用技巧42招

EXCEL2007应用技巧42招

1、使用CHOOSE函数判断员工考核成绩是否合格：

在学生考试成绩统计报表中，对学生成绩进行考评，成绩不小于75分显示为合格，小于75分显示为不合格，具体操作方法如下：

选中C2单元格，在编辑栏中输入公式：

=CHOOSE（IF（B2>=75,1,2）,"合格","不合格"），回车后填充其它单元格，最终效果如下图所示：

2、使用CHOOSE函数评定多个等级：

在销售产品销售统计报表中，考评销售员的销售等级。

如：

约定当总销售额大于200000元时，销售等级为“一等销售员”；当总销售额大于180000元时，销售等级为“三等销售员”；当总销售额大于150000元时，销售等级为“三等销售员”；当总销售额小于150000元时，销售等级为“四等销售员”，具体操作方法如下：

选中C2单元格，在编辑栏中输入公式：

=CHOOSE（IF（B2>100000,1,IF（B2>=80000,2,IF（B2>=50000,3,4）））,"四等销售员","三等销售员","二等销售员","一等销售员"），回车后填充其它单元格，最终效果如下图所示：

3、求取一组数据的反转数据：

使用CHOOSE函数来设置公式可以求取一组数据的反转数据（即原最后一行显示为现在第一行），具体操作方法如下：

选中D1：

E5单元格，在编辑栏中输入公式：

=CHOOSE（{1;2;3;4;5},A5:

B5,A4:

B4,A3:

B3,A2:

B2,A1:

B1），按（Ctrl+Shift+Enter）组合键，即可一次性返回原数组数据的反转数组数据，最终效果如下图所示：

4、使用COLUMN函数建立有规律的三级序列编号：

COLUMN函数用于返回给定引用的列标。

该函数通常配合其他函数使用，单独使用不具备太大意义。

例如本例中要建立有规律的三级序列编号，其公式设置操作如下：

选中B2单元格，在编辑栏中输入公式：

=$A2&"-"&（COLUMN（）-1），回车后即可自动返回“1-1-1”三级序列编号，再填充其它单元格，最终效果如下图所示：

函数说明：

COLUMN函数用于返回给定引用的列标。

5、使用COLUMN函数配合其他函数使用：

COLUMN函数通常配合其他函数使用，从而达到各类计算要求。

例如本例中在进行隔列求和时就使用了COLUMN函数来指定只对特定系列进行求和。

具体操作方法如下：

选中B7单元格，在编辑栏中输入公式：

=SUM（IF（MOD（COLUMN（$A2:

$Q2）,4）=0,$B2:

$Q2）），按（Ctrl+Shift+Enter）组合键，可统计E2、J2、M2、Q2单元格之和，最终效果如下图所示：

6、返回引用数据源包含的总列数：

若要返回引用数据源包含的总列数，可以使用COLUMNS函数来实现。

具体操作方法如下：

选中B2单元格，在编辑栏中输入公式：

=COLUMNS（A1:

E1），按键即可以返回引用A1：

E1单元格区域包含的列数为“5”；在B3和B4单元格中，分别输入公式：

=COLUMNS（A1:

K7）和=COLUMNS（{1,2,3,4,5;4,5,6,7,8}），按键即可以返回引用单元格区域或数组常量所包含的列数；最终效果如下图所示：

7、使用ROW函数建立有规律的三级序列编号：

ROW函数用于返回给定引用的列标。

该函数通常配合其他函数使用，单独使用效果不明显，例如本例中要建立有规律的三级序列编号，其公式设置操作如下：

选中B2单元格，在编辑栏中输入公式：

=B$1&"-"&（ROW（）-1），按键即可自动返回“1-1-1”三级序列编号，填充其它单元格，最终效果如下图所示：

函数说明：

ROW函数用于返回给定引用的行号。

8、使用ROW函数自动控制要显示的行数：

在建立工作表时通常需要通过公式控制某些单元格的显示，例如，当前工作表中显示了贷款金额、贷款年限等数据，现在在根据贷款年限计算各期偿还金额，因此需要在工作表中建立“年份”列，进而进行计算。

那么当贷款年限发生变化时，同时希望“年份”列的年限也相应改变。

具体操作方法如下：

当前工作表的B2单元格中显示了借款年限。

选中A5单元格，在编辑栏中输入公式：

=IF（ROW（）-ROW（$A$4）<=$B$2,ROW（）-ROW（$A$4）,""，按键，向下复制公式（可以根据可能长的借款年限向下多复制一些单元格），可以看到实际显示年份值与B2单元格中指定期数相等，最终效果如下图所示；如果更改B2单元格的借款年限，“年份”则会显示出相应的年份值，如下图所示。

9、返回引用数据源包含的总行数：

若要返回引用数据源包含的总行数，以下可以使用ROWS函数来实现。

具体操作方法如下：

选中B2单元格，在编辑栏中输入公式：

=ROWS（A1:

E1），按键即可以返回引用A1：

E1单元格区域包含的行数为“1”；接着在B3和B4单元格中，分别输入公式：

=ROWS（A1:

K7）和=ROWS（{1,2,3;3,4,5;4,5,6;5,6,7}），按键即可以返回引用单元格区域或数组常量所包含的行数；最终效果如下图所示：

10、使用LOOKUP函数进行查询（向量型）：

在档案管理表、销售管理表行数据表中，通常都需要进行大量的数据查询操作。

例如本例中通过LOOKUP函数建立公式可以实现输入编号后即可查询相应信息，具体操作方法如下：

1）建立相应查询列标识，并输入要查询的编号；

2）选中B9单元格，在编辑栏中输入公式：

=LOOKUP（$A$9,$A$2:

$A$6,B$2:

B$6），按键即可以得到员工编号为PR_004的员工姓名，最终效果如下图所示：

3）选中B9单元格，向右复制公式，即可得到该编码员工的其他相关销售信息；

4）当需要查询其他员工销售信息时，只需要在A9单元格中重新输入查询编号即可实现快速查询。

函数说明：

LOOKUP函数用于从单行或单列区域或者从一个数组返回值。

11、使用LOOKUP函数进行查询（数组型）：

LOOKUP函数有两种语法形式，除了上面介绍的向量型，另一种是数组型，使用数组型LOOKUP函数实现查询的操作如下：

1）建立相应查询列标识，并输入要查询的编号；

2）选中B9单元格，在编辑栏中输入公式：

=LOOKUP（$A$9,$A2:

B6），按键即可以得到员工编号为PR_004的员工姓名，最终效果如下图所示：

3）选中B9单元格，向右复制公式，即可得到该编码员工的其他相关销售信息；函数说明：

LOOKUP函数的数组形式是在数组的第1行或第1列中查找指定数值，然后返回最后一行或最后一列相同位置处的数值。

12、使用HLOOKUP函数获取数据：

本例中列出了不同的值班类别所对应的值班工资标准，现在要根据当前的值班统计表中的值班类别自动返回应计的值班工资。

以下可以使用HLOOKUP函数来返回值。

1）根据不同的值班类别建立工资标准表，将实际值班数据输入到工作表中；

2）选中G7单元格，在编辑栏中输入公式：

=HLOOKUP（F7,$A$3:

$H$4,2,0），按键即可根据日期类别返回对应的工资标准，最终效果如下图所示：

3）选中G7单元格，向下复制公式，即可得到其他员工加班类别所对应的工资标准。

函数说明：

HLOOKUP函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

13、使用HLOOKUP函数进行查询：

在本例中统计了学生各科目成绩，现在要求建立一个查询表，查询指定科目的成绩，以下可以使用HLOOKUP函数来设置公式。

1）在工作表中建立表（也可以在其他工作表中建立），如下图所示：

2）选中K3单元格，在编辑栏中输入公式：

=HLOOKUP（$K$1,$C$1:

$F$10,ROW（A2）,FALSE），按键即可根据K1单元格的科目返回第一个成绩，向下复制K3单元格的公式，可依次得到其他学生成绩，最终效果如下图所示：

14、使用VLOOKUP函数进行查询：

当在采购统计报表中记录了详细的采购信息，如果需要根据条件查看采购信息时，可以使用VLOOKUP函数来进行查询。

1）建立相应查询列标识，并输入要查询的编号；

2）选中B9单元格，在编辑栏中输入公式：

=VLOOKUP（$A$9,$A$2:

$D$6,COLUMN（B1）,FLASE），按键即可得到员工编号为PR_004的员工姓名，最终效果如下图所示：

函数说明：

VLOOKUP函数用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。

15、使用VLOOKUP函数合并两张表的数据：

本例中分别统计了学生们的两项成绩，但是两张表格中统计顺序各不相同，表格如下图所示，下面要实现将两张表格合并为一张表格。

1）直接复制第1张表格，然后建立第2项成绩的列标识，如上图所示；

2）选中I2单元格，在编辑栏中输入公式：

=VLOOKUP（G2,$D$2:

$E$10,2,FLASE），按键即可根据G2单元格中的姓名返回其“数标”成绩，最终效果如上图所示：

3）选中I10单元格，向下复制公式，即可得到其他学生的“数标”成绩。

16、使用VLOOKUP函数进行反向查询：

本例中统计了基金的相关数据，现在要根据买入基金的代码来查找最新的净值（基金的代码显示在最右列）。

以下可以使用VLOOKUP函数来设计公式。

1）建立表格如下图所示（查询表格可以位于其他工作表中，本例中便于读者查看，让其显示在同一张表格中）；

2）选中F12单元格，在编辑栏中输入公式：

=VLOOKUP（B12,IF（{1,0},$H$2:

$H$9,$B$2:

$B$9,2），按键即可根据B12单元格的基金代码从B2：

B9单元格区域找到其最新净值，最终效果如上图所示：

3）选中F12单元格，向右复制公式，即可得到其他基金代码的最新净值。

17、使用MATCH函数返回指定元素所在位置：

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。

该函数一般与其他函数配合使用，单独使用效果不明显。

通过如下实例可帮助读者了解该函数的工作原理。

1）选中B8单元格，在编辑栏中输入公式：

=MATCH（A8,B1:

B6,0），按键返回A8单元格数据在B1：

B6单元格区域中的行数，即第4行，如下图所示；

3）选中B9单元格，在编辑栏中输入公式：

=MATCH（A9,A1:

D1,0），按键返回A9单元格数据在A1：

D1单元格区域中的列数，即第3列，如上图所示；

函数说明：

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。

18、使用INDEX函数实现查找（引用型）：

如果在学生考试成绩统计报表中，要按指定条件查找学生考试成绩，可以使用INDEX函数按如下方法来设置公式：

1）选中C7单元格，在编辑栏中输入公式：

=INDEX（（A2:

E5,A2:

F5）,3,5,1），按键即可从第1个引用区域中查找到学生“孙丽萍”3门课程的平均成绩，如下图所示。

2）选中C8单元格，在编辑栏中输入公式：

=INDEX（（A2:

E5,A2:

F5）,4,6,2），按键即可从第2个引用区域中查找到学生“王保国”3门课程总考试成绩，如上图所示。

函数说明：

INDEX函数返回数据清单或数组中的元素值，此元素由行序号和列序号的索引值给定。

19、使用INDEX函数实现查找（数组型）：

例如在产品销售统计报表中，要按指定条件查找产品销售数量，可以使用INDEX函数按如下方法来设置公式：

1）选中C7单元格，在公式编辑栏中输入公式：

=INDEX（A2:

F5,2,4），按键即可查找到销售员“赵育军”第三季度产品销售量，如下图所示。

2）选中C8单元格，在编辑栏中输入公式：

=INDEX（A2:

F5,4,6），按键即可查找到销售员“王保国”全年总销售量，如上图所示。

函数说明：

INDEX函数的数组形式通常返回数值或数值数组。

20、使用INDEX配合其他函数查询出满足同一条件的所有记录：

在本例中统计了各个店面的销售情况，现在要达到的统计目的是，将某一个店面的所有记录都依次显示出来。

我们可以使用INDEX函数以下方法配合SMALL函数、ROW函数来实现：

1）在工作表中建立查询表（也可以在其他工作表中建立，本例为方便读者查看所以在当前工作表中建立），如下图所示。

2）选中F4：

F11单元格区域（根据当前记录的多少来选择，比如当前销售记录非常多，为了一次显示某一店面的所有记录，则需要向下多选取一些单元格），在编辑栏中输入公式：

=IF（ISERROR（SMALL（IF（$A$2:

$A$11）=$H$1）,ROW（2:

11）,ROW（1:

11）））,"",INDEX（A:

A,SMALL（IF（$A$2:

$A$11）=$H$1）,ROW（2:

11）,ROW（1:

11）））），如上图所示；

3）同时按组合键，可一次性将A列中所有等于H1单元格中指定的店面的记录都显示出来，如上图所示；

4）选中F4：

F11单元格区域，将光标定位到右下角，出现黑色十字形状时按住鼠标左键向右拖动，完成公式的复制（得到H1单元格中指定店面的所有记录），如上图所示。

5）如果再查询其他店面的销售记录，只需要在H1单元格中重新输入店面名称即可（可以通过数据有效性功能设置选择序列），如上图所示。

21、配合使用INDEX与MATCH函数实现查询：

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置，而INDEX函数用于返回行序号和列序号指定的值；因此使用MATCH函数返回要查看对象的位置后，可以使用INDEX函数返回这个位置的值。

因此这两个函数通常配合起来实现查找操作。

例如要实现根据编号查询指定员工的销售数据，使用INDEX函数与MATCH函数的操作如下：

1）建立相应查询列标识，并输入要查询的编号；

2）选中B9单元格，在编辑栏中输入公式：

=INDEX（$A2:

$D6,MATCH（$A9,$A2:

$A6,0）,COLUMN（B1）），按键即可得到员工编号为PR_004的员工姓名，如下图所示：

3）选中B9单元格，向右复制公式，即可得到该编码员工的其他相关销售信息，如上图所示：

4）当需要查询其他员工销售信息时，只需要在A9单元格中重新输入查询编号即可实现快速查询，如上图所示。

22、配合使用INDEX与MATCH函数实现双条件查询：

本例中统计了几款毛衣不同含毛量的价格，现在要实现查询特定品名、特定含毛量的产品的价格。

此时需要使用INDEX与MATCH函数按如下方法实现双条件查询。

1）首先在A7、B7单元格中输入要查询的尺码与品名（可以通过数据有效性功能来设置可选择序列）；

2）选中C7单元格，在编辑栏中输入公式：

=INDEX（B2:

D4,MATCH（B7,A2:

A4,0）,MATCH（A7,B1:

D1,0），按键,可以返回含毛量为“含毛量90%”，品名为“女式毛衣”产品的单价，如下图所示：

3）在A7、B7单元格中任意输入其他要查询的含毛量与品名，则可以快速查询其单价，如上图所示。

23、返回由文本值引用的数据：

若要返回由文本字符串引用的数据，可以使用INDIRECT函数如下方法来实现：

1）选中C2单元格，在编辑栏中输入公式：

=INDIRECT（A2），按键将直接引用B3单元格中的数据；

2）将光标移到C2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回由文本字符串引用的数据，如下图所示。

函数说明：

INDIRECT函数用于返回由文本字符串指定的引用，此函数立即对引用进行计算，并显示其内容。

24、引用其他工作表中的单元格的数据：

若要在当前工作表中引用其他工作表中指定单元格的值，可以配合INDIRECT函数和ADDRESS函数来实现。

例如要引用Sheet2!

工作表的A1单元格的值，可以输入公式：

=INDIRECT（"Sheet2!

"&ADDRESS（1,1））；例如要引用Sheet2!

工作表的C5单元格的值，可以输入公式：

=INDIRECT（"Sheet2!

"&ADDRESS（5,6））

函数说明：

ADDRESS函数用于按照给定的行号和列标，建立文本类型的单元格地址。

25、使用OFFSET函数实现动态查询：

例如本例中统计了学生各科目成绩，现在可以利用一个动态序号来实现各科目成绩的查询，公式的设置需要使用OFFSET函数按如下方法操作：

1）在工作表中建立查询表（也可以在其他工作表中建立），在K1单元格中输入序号“1”，如下图所示：

2）选中K3单元格，在编辑栏中输入公式：

=OFFSET（B1,0,$K$1）。

按键即可根据K1单元格中的值确定偏移量，以B1为参照，向下偏移0行，向右偏移1列，因此返回标识项“语标”，如上图所示：

3）选中K3单元格，向下复制公式，即可根据K1单元格中的数值来确定偏移量，返回各学生的成绩，如上图所示选中了K7单元格，读者可比较一下公式；

4）完成公式的设置之后，当K3单元格中的变量更改时，K3：

K12单元格的值也会作相应改变（因为指定的偏移量改变了），从而实现动态查询。

例如在K1单元格中输入“3”，其返回值如上图所示。

函数说明：

OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以为一个单元格或单元格区域。

并可以指定返回的行数或列数。

26、使用TRANSPOSE函数实现行列转置：

若要将表格中的行列标识项相互转置，可以使用TRANSPOSE函数按如下方法实现：

1）选中A6：

D6单元格区域，在编辑栏中输入公式：

=TRANSPOSE（A1:

A4）。

按组合键，即可将原行标识项转置为列标识项，如下图所示：

2）选中A7：

A9单元格区域，在编辑栏中输入公式：

=TRANSPOSE（B1:

D1）。

按组合键，即可将原列标识项转置为行标识项，如上图所示：

3）选中B7：

D9单元格区域，在编辑栏中输入公式：

=TRANSPOSE（B2:

D4）。

按组合键，即可将各表格中数据转置为上图所示的效果：

函数说明：

TRANSPOSE函数用于返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，反之亦然。

在行列数分别与数组行列数相同的区域中，必须将TRANSPOSE，输入为数组公式。

使用TRANSPOSE可在工作表中转置数组的垂直和水平方向。

27、使用DSUM函数统计特定产品的总销售数量：

本例中统计了各个店面的销售情况（为方便显示，只列举部分记录），现在要统计某一特定产品的总销售数量，可以使用DSUM函数按如下方法设置公式。

1）首先设置条件，如本例在G4：

G5单元格中设置条件，条件应该包含列标识，如下图所示；

2）选中H5单元格，在编辑栏中输入公式：

=DSUM（A1:

E11,4,G4:

G5）。

按键即可统计出品名为“男式毛衣”的总销售数量，如上图所示；

函数说明：

DSUM函数用于返回列表或数据库的列中满足指定条件的数字之和。

28、使用DSUM函数实现双条件计算：

要使用DSUM函数实现双条件查询，关键在于条件的设置。

例如在本例中要统计出店面为“总店”、品名为“男式毛衣”的总销售数量，其操作方法如下：

1）首先设置条件，如本例在G4：

H5单元格中设置条件，条件应该包含列标识（双条件），如下图所示；

2）选中I5单元格，在编辑栏中输入公式：

=DSUM（A1:

E11,4,G4:

H5）。

按键即可统计出店面为“总店”、品名为“男式毛衣”的总销售数量，如上图所示。

29、统计时去除指定条件的记录：

要实现统计时去除指定条件的记录，关键在于条件的设置。

例如本例中要将店面为“总店”或品名为“男式毛衣”的记录都去除，然后再统计总销售金额。

其操作方法如下：

1）首先设置条件，如本例在G4：

H5单元格中设置条件分别为“<>总店”与“<>男式毛衣”，如下图所示；

2）选中I5单元格，在编辑栏中输入公式：

=DSUM（A1:

E11,5,G4:

H5）。

按键即可统计出去除店面为“总店”或品名为“男式毛衣”之外的销售记录的总金额，如上图所示。

30、在DSUM函数参数中使用通配符：

在DSUM函数中可以使用通配符来设置函数参数。

使用通配符来设置函数参数，关键在于条件的设置。

具体操作方法如下：

1）在A9：

A10单元格区域中设置条件，使用通配符，即以地区以“西部”结尾，如下图所示；

2）选中B10单元格，在编辑栏中输入公式：

=DSUM（A1:

B7,2,A9:

A10）。

按键即可统计出“西部”地区利润总和，如上图所示。

3）在A12：

A13单元格区域中设置条件，使用通配符，即地区不以“新售点”结尾，如上图所示；

4）选中B13单元格，在编辑栏中输入公式：

=DSUM（A1:

B7,2,A12:

A13）。

按键即可统计新售点除外的其他地区利润总和，如上图所示。

31、避免DSUM函数的模糊匹配：

在DSUM函数中可以使用通配符来设置函数参数。

使用通配符来设置函数参数，关键在于条件的设置。

具体操作方法如下：

1）在A9：

A10单元格区域中设置条件，使用通配符，即以地区以“西部”结尾，如下图所示；

2）选中B10单元格，在编辑栏中输入公式：

=DSUM（A1:

E11,5,G4:

G5），按键得到正确的计算结果，如上图所示。

32、使用DAVERAGE函数统计特定班级平均分：

在本例中统计了各班学生各科目考试成绩，现在要统计某一特定班级指定科目的平均分，可以使用DAVERAGE函数按如下方法设置公式。

1）首先设置条件，如本例在A10：

A11单元格中设置条件，条件应该包含列标识，如下图所示；

2）选中B11单元格，在编辑栏中输入公式：

=DAVERAGE（A1:

E8,3,A10:

A11）。

按键即可统计出班级为“1”的语文科目平均分，如上图所示。

函数说明：

DAVERAGE函数用于返回列表或数据库中满足指定条件的列中数值的平均值。

33、在DAVERAGE函数参数中使用通配符：

在DAVERAGE函数中可以使用通配符来设置函数参数，例如本例中要求统计出所有新售点的平均利润，其操作方法如下：

1）在A9：

A10单元区域中设置条件，使用通配符，即以地区以“新售点”结尾，如下图所示；

2）选中B10单元格，在编辑栏中输入公式：

=DAVERAGE（A1:

B7,2,A9:

A10）。

按键即可统计出“新售点”的平均利润，如上图所示。

34、使用DAVERAGE函数实现计算后查询：

在本例中统计了各班学生各科目考试成绩，现在要统计某一特定班级各个科目的平均分，从而实现查询指定班级各科目平均分。

具体公式的设置如下：

1）首先设置条件，如本例在A10：

A11单元格中设置条件并建立求解标识，如下图所示；

2）选中B11单元格，在编辑栏中输入公式：

=DAVERAGE（$A$1:

$F$8,COLUMN（C1）,$A$10:

$A$11）。

按键即可统计出班级为“1”的语文科目平均分，向右复制B11单元格的公式，可以得到班级为“1”的各个科目的平均分，如上图所示；

3）要想查询其他班级各科目平均分，可以直接在A11单元格中查改查询条件即可。

35、使用DCOUNT函数统计满足条件的记录条数：

在本例中统计了各班学生各科目考试成绩，现在要统计某一班级人数，可以使用DCOUNT函数按如下方法来统