excel教程及实例03.docVIP

实例：将表一中“有效”的“施工单位”及“金额”显示在表二中。 表一 施工单位 金额 是否有效 A 1 有效 B 2 有效 C 3 无效 D 4 有效 E 5 无效 F 6 有效 G 7 有效 表二 A2单元格中的数组公式为： =IF(ROW()-1COUNTIF(表1!$C$1:$C$8,有效),,INDEX(表1!$A$2:$A$8,SMALL(IF(表1!$C$2:$C$8=有效,ROW(表1!A$2:A$8)-1),ROW()-1)))，然后将向下拖填充柄即可 C2单元格中的数组公式为： =IF(ROW()-1COUNTIF(表1!$C$1:$C$8,有效),,INDEX(表1!B$2:B$8,SMALL(IF(表1!$C$2:$C$8=有效,ROW(表1!B$2:B$8)-1),ROW()-1)))，然后将向下拖填充柄即可 注：如果表头变三行，公式里几个写-1的地方改-3。 有朋友问，什么时候要用INDIRECT()?为什么要用INDIRECT()？ 举个简单的例子： 如果B2:B10单元格分别要引用Sheet2:Sheet10工作表的F6单元格，则公式分别为： B2=Sheet2!F6 B3=Sheet3!F6 …… B10=Sheet10!F6 这些公式不能使用填充的办法输入，只能手工一个一个编辑修改。用什么办法可以快速填充？首行写入公式： B2=SheetRow(2:2)!F6 拖动填充柄把公式向下填充，依次得到结果： Sheet2!F6 Sheet3!F6 …… Sheet10!F6 但它显示的只是单元格地址，不是单元格引用。如果引用这些单元格的值？ 在上面公式中，加入INDIRECT()函数就是：B2=INDIRECT(SheetRow(2:2)!F6) 在HYPERLINK()函数中，“#”表示本工作簿。 如何将字符转换为数值并相加 A列有字符型 A1为003、 A2为005、A3为012、A4为018、A5为123、A6为251 将A列转换为B列数值型后按A列有效字符相加。 B1为3（即0+0+3）、 B2为5、B3为3（即0+1+2）、B4为9、B5为6（即1+2+3）、 B6为8 方法：=SUM(--MID(A1,ROW(1:3),1))，数组公式(按ctrl+shift+enter三键结束)，使用时ROW(1:3),必须固定，用下拉时ROW(1:3),会随单元格变化，或者公式改一下=SUM(--MID(A1,ROW($1:$3),1)) 或=SUM(--MID(A1,{1,2,3},1)) 这两个公式都是数组公式。 A1单元格里的出生年月用数示,能否转换成汉字表示形式？ =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),[DBnum1]yyyymmdd)可以转为“一九七四一二二十五” 如果数字转化为“一九七四○二三十” ” 对E3：E13区域中相同的数值（与E3相同的）计和，方法 {=SUM(($A$3:$A$13=E3)*$B$3:$B$13)} 或{=SUMPRODUCT(($A$3:$A$13=E3)*($B$3:$B$13))} 或=SUMIF($A$3:$A$13,$E3,$B$3:$B$13) 象indirect、sumif、countif等函数无法从关闭的文件中取值。 补充一个： 查找函数不单可以在关闭源文件的情况下查找，而且能在删除源文件的情况下查找。 见附件。源文件是2.XLS，我不提供（其实就是jssy版主的1.xls文件的数据） 就是说BOOK1.XLS这个文件把源文件2.XLS的数据调进来了。（注意这个文件的大小，如果数据更多的话，就会发现比不做外部链接的要大） 另外一个现象是：用jssy的文件，删除1.XLS也能查找，但每次更改查找数据后都会弹出一个窗口，询问源文件的链接路径。而我做的这个却不会，就好像打开了源文件一样。我不知道为什么会有这个差异。 首先应该提出的是当数据源文件被删除后，是不可能再进行取值的。 czzqb兄提到情况，应该是一种假象。如果打开文件，按提示选择“更新”后，EXCEL系统会继续提示“继续”或“编辑链接”，如果选“编辑链接”系统将要求查找数据源文件，如果选“继续”实际上是选择“就这样打开文件”即不更新，所以当前文件会显示已有数据值及相应的原来的路径，但这不是说在没有源文件的情况下取到了值。如果在编辑栏中按F9或更改值的取值参数，系统肯定要求查找源文件，当未能查找到源文件时，就会显示错误值。 可以这么理解： SUMIF(range,criteria,sum_range) 如果sum_range的大小与range不一致，则以sum_range的第一个