[习题] [201401函中]E组第九讲作业提交贴

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-10分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

hsl215

一、
1、

1. =INFO("DIRECTORY")
   
2. =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)

复制代码

2、第一个公式为宏表函数（当前工作簿中的工作表数量），第二为信息函数，此公式会计算所有打开的工作簿中的工作表数量及加载项中隐藏的工作表

1. =GET.WORKBOOK(4)&T(NOW())
   
2. =INFO("NUMFILE")&T(NOW())

复制代码

3、公式1是信息函数，公式2为宏表函数，公式3与4、5与6是配对函数

1. =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)&T(NOW())
   
2. =GET.WORKBOOK(38)&T(NOW())
   
3. =MID(SHEETNAME2,FIND("]",SHEETNAME2)+1,9)
   
4. =GET.DOCUMENT(76)&T(NOW())
   
5. =MID(SHEETNAME3,FIND("]",SHEETNAME3)+1,9)
   
6. =GET.DOCUMENT(1)&T(NOW())

复制代码

二、

1. =EVALUATE(SUBSTITUTE(Sheet1!B11,",","+"))

复制代码

三、公式1为宏表函数，公式2、3部门的两种公式，且公式3为数组公式，公式4为链接公式直接下拉即可

1. =GET.WORKBOOK(1)&T(NOW())
   
2. =MID(INDEX(SHEETALLNAME,ROW(A1)),FIND("]",INDEX(SHEETALLNAME,ROW(A1)))+1,9)
   
3. =TRANSPOSE(MID(SHEETALLNAME,FIND("]",SHEETALLNAME)+1,9))
   
4. =HYPERLINK(INDEX(SHEETALLNAME,ROW(A1))&"!A1",B22)

复制代码

四、公式1为宏表函数取背景色，公式2为求和公式

1. =GET.CELL(63,Sheet1!C32)+NOW()*0
   
2. =SUMIF(D32:D36,">0",C32)

复制代码

更新求和公式，

1. =SUMPRODUCT(C32:C36,(D32:D36>0)+(C32:C36>400))

复制代码

201401函中第9讲作业-hsl215.rar (13.83 KB, 下载次数: 15)

2014-10-30 16:09 上传

点击文件名下载附件

ldxhzy

1.1 文档目录
      新建名称“ 文档目录”，其引用位置   =CELL("filename")
      C2公式：=LEFT(文档目录,FIND("[",文档目录)-1)
1.2工作表数量
      新建名称“ 工作表数量”，其引用位置  =GET.WORKBOOK(4)
      C4公式 ：=工作表数量
1.3当前工作表名称
      C6公式： =MID(文档目录,FIND("]",文档目录)+1,99)

2    激活E11单元格， 新建名称 MyAdd，其引用位置  =EVALUATE(SUBSTITUTE(Sheet1!B11,",","+"))
       E11公式： =MyAdd
       E11公式下拉            

3   新建名称“部门”，其引用位置  =GET.WORKBOOK(1)
      B22公式： =INDEX(部门,ROW(A2))
      B22公式下拉
      （链接 函数不会写）
4  

76868850

1.1=CELL("filename")
1.2=INFO("NUMFILE")
1.3=MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
2.定义result=EVALUATE(SUBSTITUTE(Sheet1!B11,",","+"))
  ="="&SUBSTITUTE(B11,",","+"),转换为数值，分列。
3.1.先定义名称：
GETSHEETS=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100)
输入=INDEX(GETSHEETS,ROW(A1))&T(NOW())
往下拖即可列出所有工作表名
2.C22=HYPERLINK("#"&B22&"!B22",B22)
4.定义COLOR=GET.CELL(63,Sheet1!C32),得出单元格填充的颜色为22，再用SUMIF对单元格为22的求和。

满坛皆为吾师

本次作业宏表函数定义均以"x"为名,不做单独说明
1、

1. =INFO("DIRECTORY")
   
2. =INFO("NUMFILE")
   
3. =x         x=GET.WORKBOOK(4)
   
4. =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)

复制代码

2、E11下拉

1. =x     x=EVALUATE(SUBSTITUTE(B11,",","+"))

复制代码

3、表名区域数组,链接C22下拉

1. =INDEX(MID(x,FIND("]",x)+1,99),ROW(2:6))    x=GET.WORKBOOK(1)

复制代码

1. =HYPERLINK("#"&C22&"!A1",C22)

复制代码

4、求和

1. =SUM(C33,C35:C36)

复制代码

D32=x下拉    x=(GET.CELL(63,C32)+GET.CELL(24,C32))>0

1. =SUMIF(D32:D36,">0",C32)   

复制代码

meililin

201401函中第9讲 作业-E01-meililin.rar (13.82 KB, 下载次数: 4)

2014-10-29 23:00 上传

点击文件名下载附件
第一题：
1问

1. =INFO("DIRECTORY")

复制代码

2问
定义名称：getsheets=GET.WORKBOOK(4)

1. =getsheets

复制代码

3问

1. =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

复制代码

第二题：
定义名称：sum_jss=EVALUATE(SUBSTITUTE(Sheet1!$B11,",","+"))

1. =sum_jss

复制代码

第三题：
定义名称：getsheetsname=GET.WORKBOOK(1)
部门（数组公式）：

1. =MID(INDEX(TRANSPOSE(getsheetsname),ROW(A2)),FIND("]",INDEX(TRANSPOSE(getsheetsname),ROW(A2)))+1,99)

复制代码

链接：

1. =HYPERLINK("#"&B22&"!a1","单击跳转")

复制代码

第四题：
带颜色部分数字由两部分组成，一部分为用条件格式设置的填充色，另一种是手工填充的
定义名称：getintercol=GET.CELL(63,Sheet1!$C32)
辅助区域：d32=getintercol
结果数组公式

1. =SUM(IF(C32:C36>400,C32:C36),IF(D32:D36>0,C32:C36))

复制代码

滴答滴

第一题、
1、
=INFO("DIRECTORY")
2、
=INFO("NUMFILE")
或者定义名称
sheetnum=GET.WORKBOOK(4)
调用该名称
3、
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)

第二题、
定义名称
运算求和=EVALUATE(SUBSTITUTE(Sheet1!A11,",","+"))
调用该名称

第三题、
部门
定义名称
getsheets=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())
考虑F9刷新，连接易失性函数&t(now())
然后调用方法
INDEX(getsheets,ROW(A2))
考虑下拉容错
=IFERROR(INDEX(getsheets,ROW(A2)),"")

连接：
=HYPERLINK(“[201401函中第9讲 作业.xls]”&B22&"!A1",B22)
若考虑到文件名会修改，可以定义名称
文件名="["&GET.DOCUMENT(88)&"]"
然后上面的公式修改为
=HYPERLINK(文件名&B22&"!A1",B22)

第四题、
定义名称
getcolor=GET.CELL(63,Sheet1!C32)+INT(RAND())
或者
getcolor=GET.CELL(63,Sheet1!C33)+NOW()*0
然后D32:D36辅助列
应用名称=getcolor

特别注意C36的背景颜色是通过条件格式生成的，宏表函数无法取到颜色，所以需要补充一个sumif，条件格式定义为">400"变色
求和公式
=SUMPRODUCT(C32:C36*(D32:D36>0))+SUMIF(C32:C36,">400")
或
=SUMIF(D32:D36,">0",C32:C36)+SUMIF(C32:C36,">400")

还需要注意，此处名称定义时，后面不能用&t(now())文本型，否则求和是逻辑判定会出错


还可以对表格区域设置“筛选”
然后按颜色筛选后，点击求和按钮
或者应用公式
=SUBTOTAL(9,C32:C36)

瑾瑄

1=INFO("DIRECTORY")
  =INFO("NUMFILE")
  =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
2定义名称:计算=EVALUATE(SUBSTITUTE(Sheet1!B11,",","+"))
                E11=计算
3定义名称:GETSHEETS=GET.WORKBOOK(1)
               部门=INDEX(GETSHEETS,ROW(A2))
               链接=HYPERLINK("#'"&B22&"'!A1",MID(B22,FIND("]",B22)+1,99))
4.定义名称:颜色=GET.CELL(63,Sheet1!E29)
                分别在D32:D36输入公式=颜色
                合计=SUMIF(D32:D36,">0",C32:C36)+SUMIF(C32:C36,">400")

一杯清荼

题一：
     文档目录：
                =INFO("DIRECTORY")
                =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)
     工作表数量：
                =INFO("NUMFILE")
                定义名称：=Snuber
                         名称为:Snuber
                         引用位置为：=GET.WORKBOOK(4)
     当前工作表名称:
                =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)
                定义名称法：=REPLACE(Sname,1,FIND("]",Sname),)
                           Sname=GET.WORKBOOK(3)
                              或=GET.CELL(62)
                           
题二：
        定义名称:=add
             add=EVALUATE(SUBSTITUTE(B11,",","+"))
题三：
        定义名称 Sname=GET.WORKBOOK(1)
        b22=INDEX(MID(Sname,FIND("]",Sname)+1,99),ROW()-20)
        c22==HYPERLINK("#"&B22&"!A1","跳转")
题四：
        辅助列: d32=fuzhu
        定义名称：fuzhu=OR(GET.CELL(63,C32),C32>400)
        c38=SUM(C32:C36*D32:D36)

XMergeD

第一题

1. =INFO("DIRECTORY")

复制代码

1. =INFO("NUMFILE")

复制代码

1. =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)

复制代码

第二题
选中E11单元格，定义名称（heji），名称的引用位置输入

1. ：=EVALUATE(SUBSTITUTE(Sheet1!B11,",","+"))

复制代码

关闭后，在E11输入=heji

第三题
定义名称（sheetname），名称的引用位置输入

1. =TRANSPOSE(GET.WORKBOOK(1))

复制代码

选中B22：B26 输入区域数组公式

1. =MID(sheetsname,FIND("]",sheetsname)+1,99)

复制代码

在C22输入公式

1. =HYPERLINK("[201401函中第9讲 作业.xls]"&B22&"!A1",B22)

复制代码

下拉。

第四题
选中D32单元格，定义名称（backcolor），名称的引用位置输入

1. =GET.CELL(63,Sheet1!$C$32)

复制代码

，再在D32单元格输入公式=backcolor，在C38单元格输入公式

1. =SUMIF(D32:D36,">0",C32)

复制代码