常用函数及透视表方法

1常用函数及透视表方法2013年3月2主要内容一、常用函数二、透视表方法3vlookup函数Lookup_value：指需要搜索的值Table_array：指搜索数据区域Col_index_num：满足条件的单元格在搜索区域的列数Range_lookup：匹配精度。False表示大致匹配，True表示精确匹配。一般，填写false。上述案例表达意思：在数据区域2（即$G$3:$H$2000）中查找，与A4单元格数值配置的值，找到后填写区域2的第1列数，大致匹配。详见附件1。4vlookup函数的同类函数Vlookup按列搜索Hlookup按行搜索，公式用户与Vlookup函数类似。5is类函数isna函数检测值是否为#N/A，如果是则返回True，否则返回False。isnumber函数检测值是否为数字，如果是则返回True，否则返回False。Istext函数检测值是否为文本，如果是则返回True，否则返回False。举例：检测单元格A6的值为#N/A，所以返回True。检测单元格A4的值为26，所以返回False。注：详见附件1。6if函数Logical_test判断条件Value_if_true满足上述判断条件时，返回该参数值。Value_if_false不满足上述判断条件时，返回该参数值。举例：判断是否满足A2>3的条件，如果满足，就返回“big”，否则返回”small”。这里A2=1判断是否满足A5>3的条件，如果满足，就返回“big”，否则返回”small”。这里A2=4注：详见附件1。7逻辑函数and函数即“与”函数，检查是否所有参数均为true。如果所有参数值均为true，则返回true，否则返回false。or函数即“或”函数。如果任一参数值为true，则返回true；如果所有参数值为false，则返回false。举例：下图中，因第1个条件不满足，故返回false。下图中，因第2个条件满足，故返回true注：详见附件1。8实战经验1-1IF(ISNA(VLOOKUP(A4,$G$3:$H$20,1,FALSE)),0,VLOOKUP(A4,$G$3:$H$20,1,FALSE))Q：以下公式表示什么意思？如果满足ISNA(VLOOKUP(A4,$G$3:$H$20,1,FALSE))条件，则返回0，否则返回VLOOKUP(A4,$G$3:$H$20,1,FALSE)。其中，VLOOKUP(A4,$G$3:$H$20,1,FALSE)表示的意思请查看vlookup函数介绍即附件1。ISNA(VLOOKUP(A4,$G$3:$H$20,1,FALSE))表示检查vlookup函数返回的值是否为#N/A。综上所述，整个公式表示的意思为：如果在区域$G$3:$H$20中，搜索不到A4单元格的值，那么，就返回0，否则返回搜索到的值。Tips：该综合公式的好处就是，当你搜索到的值需要返回数字进行运算时，#N/A是无法参与运算的，因此，需要消灭#N/A可以用上述方法。9实战经验1-2IF(ISNUMBER(VLOOKUP(A4,$G$3:$H$20,1,FALSE)),VLOOKUP(A4,$G$3:$H$20,1,FALSE),0)融会贯通：以下公式和上一公式能达到同样效果，即如果搜索到的值不是数字，则返回0。如果满足ISNUMBER(VLOOKUP(A4,$G$3:$H$20,1,FALSE))条件，则返回VLOOKUP(A4,$G$3:$H$2000,1,FALSE)，否则返回0。其中，ISNUMBER(VLOOKUP(A4,$G$3:$H$20,1,FALSE))表示检查vlookup函数返回的值是否为数字。综上所述，整个公式表示的意思为：如果在区域$G$3:$H$2000中，搜索到A4单元格的值且判断为数字，那么，就返回搜索到的值，否则返回0。Tips：还有一个比较笨，但同样奏效的方法，就是单独使用vlookup搜索出结果后，在上述结果的基础上使用“替换”，把所有#N/A替换为0。10实战经验1-3IF(AND(A2>=75%,B2>=2%),100,0)Q：以下公式表示什么意思？如果同时满足A2单元格≥75%、B2单元格≥2%，则返回100，否则返回0。Tips：当使用IF函数，需要判断多个条件时，可结合使用逻辑函数（即AND或OR函数）。11left、right函数left函数从一个文本字符串的第一个字符开始返回指定个数的字符。right函数从一个文本字符串的最后一个字符开始返回指定个数的字符。举例：从单元格A5的第1个字符开始，返回指定3位字符。其中A5=神州行幸福卡从单元格A15的最后1个字符开始，返回指定3位字符。其中A15=神州行幸福卡注：详见附件1。12连接函数concatenate函数把多个文本合并成一个。&连接符把多个文本合并成一个。举例：把A4、A5、A6、A7单元格的字符合并，结果为中国移动201203。B14表示把A14、A15、A16、A17单元格的字符合并，结果为中国移动201203。注：详见附件1。13实战经验2LEFT(B2,4)&"-“&RIGHT(LEFT(B2,6),2)&"-“&RIGHT(LEFT(B2,8),2)Q：以下公式表示什么意思？上述公式共有4个连接符号“&”，即把B2的左个字符和“-”和B2右6个字符的最后2个字符和“-”和B2右8个字符的左后2个字符连接起来。如下图示：Tips：使用透视表进行组合计算，必须使用YY-MM-DD格式的日期，此时可以使用上述连接符号&把字符串修改格式。14round类函数round函数按指定位数对数字进行四舍五入。rounddown函数按指定位数对数字向下舍入（注意负数情况）。roundup函数按指定位数对数字向上舍入（注意负数情况）。举例：对A4进行四舍五入，其中，num_digits=-1表示保留小数点前1位，A4=12.2。对A5进行四舍五入，其中，num_digits=1表示保留小数点后1位，A5=12.64注：详见附件1。15实战经验3ROUNDUP(B2/20,0)Q：以下公式表示什么意思？如果直接翻译，则上述公式表示对B2除以20后，进行下上舍入，保留0位小数。如果把B2当成一个表示ARPU的数字单元格，则上述公式表示ARPU≤20时，返回1；20<ARPU≤40时，返回2；40<ARPU≤60时，返回3……依次类推，如下图示：Tips：如果需要在excel中对用户的消费清单进行分析，上述公式是否很有用？再结合透视表（第二部分将讲述到），统计速度是否比地市库快多了？16其他函数1average函数返回参数的算术平均值，参数可以是数组或引用。sum函数计算区域所有单元格数值的和。len函数计算字符个数。max函数返回一组数值中的最大值，忽略逻辑值和文本。min函数返回一组数值中的最小值，忽略逻辑值和文本。mod函数返回两数相除的余数。17实战经验4MOD(ROW(),2)MOD(COLUMN(),3)Q：以下公式表示什么意思？ROW()表示返回行数，COLUMN()表示返回列数。MOD(ROW(),2)表示取行数除以2的余数。MOD(COLUMN(),3)表示取列数除以3的余数。Tips：需要对某区域单元格增加间隔条纹时，在“格式菜单->条件格式”中使用上述公式，即可快速实现。18其他函数2count函数计算包含数字的单元格以及参数列表中的数字个数。countif函数计算区域中满足给定条件的单元格数目。sumif函数对满足条件的单元格求和。value函数将文本转成数值。text函数将数字转成文本。更多函数请在菜单的中查询。19实战经验5-1COUNTIF(B2:B14,"神州行")Q：以下公式表示什么意思？计算B2:B14区域中，神州行的个数。注意：神州行需加上英文格式的双引号。Tips：需要计算区域中满足给定条件的单元格数目时，使用上述公式即可快速实现。20实战经验5-2SUMIF(B2:B14,"神州行",C2:C14)Q：以下公式表示什么意思？对B2:B14区域中，神州行的C列进行求和。注意：神州行需加上英文格式的双引号。Tips：需要计算区域中满足给定条件的单元格进行求和时，使用上述公式即可快速实现。21主要内容一、常用函数二、透视表方法22建立透视表第1步：选中数字区域任一单元格，点击“数据”菜单的“透视表”选项。第2步：在弹出对话框中，点击“完成”。第3步：即可建立该数据区域的透视表。23数据处理第1步：将“客户号码”拖拽至统计区域（即绿色区域）。第2步：将“分公司”拖拽至左侧的“行字段”，结果如下图所示。24字段设置第1步：选中统计区域（上一页的绿色区域