EXCEL高手应用技巧.xls

1.1.如如何何使使用用excelexcel进进行行动动态态跨跨表表取取数数 有两个文件，第一个文件有31张日报表，每天一个表单；第二个文件仅一个表单；如何在第二个文件中， 输入1时由函数动态取出第一张表单数（如sheet1），输入2时取（sheet2）数，依次类推。，如何设公式。在同一文件中可以用indirect和address 组合， 解答：前提是两个工作表都要打开：=indirect(“book2sheet“1;2,0,1)(省略了行数和列数参数)不能正常在单元格中显示，此时按每个数组元素返回的单个单元格引用 #value!#value!是按三维方式排列的，可用n和t函数转换后变为普通的数组，见右面00 #value!#value!00 #value!#value!00 i.复复杂杂的的用用多多元元素素数数组组参参数数的的每每参参数数返返回回多多个个单单元元格格引引用用 此时offset函数按每个数组元素分别返回一个多单元格区域的引用，不可能在单元格中显示全部值 就是用n和t函数也只能显示每个多单元格区域的第一个值，目前只发现sumif和counif函数可计算这样的返回结果。 提示：据我理解，对返回多个多单元格区域引用的结果可以继续计算的函数有一个特点，就是那个函数的某个参数 必须用引用而不能用数组常数(或计算出的数组)。按这个规则，dsum、dcount、dcounta函数应该是可以的， 我已确定dsum可以，其他的请网友自己试验。1159 第1行 一个可继续对这样的引用结果计算的例子：2323 第2行 求出右面区域中每隔一行中大于10的数的和并返回一个数组12511 第3行 第1行0 结果正确！451234 第4行 第3行0 再检验此计算公式返回数组的第2个大值151011 第5行 第5行00 结果也正确！2310 第6行 第7行0069 第7行 j.offset函函数数常常用用的的场场合合131415 第8行 定义动态的区域名称并用于其他函数中 在“插入名称定义”中定义动态的名称数值 data=offset(offset!$l$119,1,count(offset!$l$119:$l$65536)10 求总和0求数值单元格个数0平均值#div/0!20 试试在l列中继续输入数值看变化。12 求sheet2和sheet3表中姓名为张三的工资总和13 05 结合我在indirect函数的使用中的关于工作表名的几个宏表函数名称定义的方法，见f126格中的链接4 就可以按指定的工作表间求出总和。indirect函数的使用2 7.sum函函数数的的使使用用 语法： sum(参参数数1,参参数数2,.,参参数数30) 结果：返回所有参数中的数字之和。 说明：参数最多只能有30个，并且可以省略(即,间没有参数或最后有一个,)； 参数可以为引用、返回数值和文本及逻辑值的计算表达式、数组； 参数如为引用，可以是区域联合、区域交叉、三维区域引用，只要引用不再参与数组运算就可以； 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 下面作一些详细的分析： a.对对文文本本、逻逻辑辑值值及及错错误误值值的的计计算算 对引用中的文本、数字型的文本、逻辑值忽略不计算。姓名工资婚姻状况 3500a11000true 公式=sum(h11:j14)，只计单元格中的数值，不计文本、逻辑值a22000false 和i12格中的文本1000a31500 对数组中的文本、数字型的文本、逻辑值忽略不计。#n/a#div/0! 3500 数组公式，不带、号输入，按ctrl+shift+enter三键结束。 公式=sum(“姓名“,“a1“,“1000“,true,2000,false,1500,h11:h14=“a2“) 错误值，不管是在引用、参数、还是在数组中均返回错误，此处excel的帮助中有错误。 #n/a引用中有错误值#div/0! 作为参数的计算表达式的结果为错误值 # 数组中有错误值 参数或作为参数的计算表达式为不是数字型的文本，返回错误。要不返回错误用f中提出的方法。 # 直接用不是数字的文本作参数 #value! 以返回不是数字的文本表达式作为参数 参数或作为参数的计算表达式为数字型的文本，转为数值后计算； 参数或作为参数的计算表达式为逻辑值时，true算1，false算0。 34 公式为=sum(10,21,12,true,false,“2“,“2“&“0“) 其中的21为逻辑值true算1，直接参数true算1，其他的false算0，文本2和表达式文本20转换后计算。 b.以以引引用用的的运运算算作作参参数数 区域联合86 请注意区域联合运算外的一对括号，那是不可少的， abcd 此运算在sum函数中算1个参数，当sum中的参数1102030 多于30个时可用此法来减少参数。2112131 区域交叉70 注意括号及2个引用间的空格，交叉引用3122232 在sum函数中也只算1个参数，此处实际运算返回4132333 的是h31:k32和i29:j34相交的b31:c32区域。5142434 联合区域不能在数组公式中继续进行计算。6152535 交叉引用在数组公式中可以可以继续进行计算。 42 公式为=sum(h29:k34 i:i)12)*(h29:k34 i:i) 实际计算的是i29:i34区域大于12的值的和 c.以以三三维维引引用用作作参参数数 #ref!公式为=sum(sum:sumif!h29:h34)，其中sum:sumif!h29:h34为对从sum工作表开始至sumif工作表结束 的h29:h34的区域引用。 象这样的直接三维引用不可继续用于数组计算中。 象下面这样的数组公式为什么是可以正确运算的？ #ref!公式为=sum(h28:h34,i34,j29:k29),(h29:k34 i:i)12)*(h29:k34 i:i),sum:sumif!h29:h34) 请注意上面的数组公式中sum函数有3个参数，第1个是联合区域引用，第2个是交叉区域引用计算的数组，第3个是三维区域引用。 由于联合区域和三维引用区域均没有继续进行数组运算，所以在sum函数的数组公式中作为单独的参数是可以的。 提示：我们在使用sum函数的数组公式时，经常只考虑有1个参数，其实是忘了sum函数最多可有30个参数。 d.以以没没有有打打开开的的工工作作薄薄的的指指定定表表的的指指定定区区域域引引用用作作参参数数 600 公式为=sum(c:excelhomefunctionindirect函数的使用.xlssheet2!$b:$b) 引用了c:excelhomefunctionindirect函数的使用.xls 工作薄sheet2表的整个b列。 只要路径所指定的文件存在就不需要打开文件，如不存在就返回错误。 e.以以(由由一一个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作sum函函数数的的参参数数 sum函数不作为其他函数的参数可以使用，见g54格，0000 公式为=sum(indirect(“h“&row()/2&“:j“&row()/2+4) 实际相当于sum(indirect(“h27:j31“)，即对h27:j31区域求和。 h54格是将这样的sum函数放在if函数中作为参数，就错误了， 因为row()返回的是一个数组并不是一个数值，这样在数组公式中indirect函数返回的并不是一个二维的区域引用， 而是三维的区域引用(第3维的尺寸是1)，所以sum的计算出错。 可以将公式改为i54格的样子(本论坛的会员提出的方法，先用sum函数将row函数的数组变为数值)，或用sumif代替(见j54格)。 e.以以(由由多多个个元元素素的的数数组组参参数数产产生生的的)单单元元格格区区域域引引用用作作sum函函数数的的参参数数 一般是indirect函数和offset函数才能以多个数组元素，产生一系列对多个单元格区域的引用，返回的是三维的区域引用， sum函数只能对第1个元素指定的区域求和，如h64格的公式。0 用内嵌sumif函数代替就正确了，见h65格。0 f.以以非非数数字字型型文文本本作作参参数数的的方方法法 a中指出参数或作为参数的计算表达式为不是数字型的文本，返回错误。 要解决直接参数为非数字的问题，可按图设置。#value! 按图设置后，就按lotus1-2-3的方式忽略文本。见h69格。 不利因素是，所有的公式均按loutus1-2-3的方式处理， 很多excel的表达式就会出错。 g.sum函函数数在在数数组组公公式式中中的的一一些些应应用用 多多条条件件计计数数a部门的男性员工有几人？3 姓名部门性别工资(b92:b105=“a“)*(c92:c105=“男“)返回2个逻辑数组的乘积，基于 a1a男1000 true*true=1；true*false=0；false*false=0 a2b女1500 所以此处是逻辑与的关系，在excel的if函数的条件中，0表示false，非0的数值表示true。 a3c女1000 去除if函数可以简化公式为3 如有2个以上并列条件，可将几个条件式相乘。 a4d女800 a、b两部门的男性员工有几人？4 a5b女2000 基于true+false=1；false+false=0；true+true=2 a6c男2500 而(b92:b105=“a“)和(b92:b105=“b“)不可能同时满足，所以此处是条件或的关系， a7d男1500 再乘以(c92:c105=“男“)作为并列条件。 a8a男1000 a部门所有女性员工和a部门工资1500以上的男性员工总数是多少？ a9c女10003 a10d男2000 因为(c92:c105=“女“)和(d92:d105=1500)可能同时满足，所以再用not(not()转换，基于 a11a男3000 not(true)=false；not(false)=true；not(0)=true；not(非0数值)=false a12b男900 a13a女1800 a14a女2500 多多条条件件求求和和 a部门女性员工的工资总额是多少？4300 基于：false*任何数=0；true*任何数=原来的数 (b92:b105=“a“)*(c92:c105=“女“)为并列条件，*d92:d105后就是满足条件的工资。 所有女性员工的工资和男性员工工资1500以上的工资总额是多少？19600 如加if函数就可以不用not(not()19600 提示：以(c92:c105=“女“)+(d92:d105=1500)这样的形式表示条件或的关系，在条件可能同时满足时要用not(not()转换 或用if函数判别。否则会多计数量的。 统计偶数行的工资总和是多少？11300 其中的(mod(row(d92:d105),2)=0)就是判别是否偶数行。 特特别别提提示示：sum函函数数在在绝绝大大多多数数的的情情况况下下用用于于数数组组公公式式中中只只能能返返回回一一个个值值，以以后后会会讲讲到到在在