日常工作中常常要用Excel处理这样或那样的数据,其中Excel的公式以及Macro的使用会减少很多的重复工作。有些操作在VBA中很容易实现,但是如果仅仅使用公式时,很多操作就不容易实现了。本文记录一些我常用到的公式。
一、截取当前sheet名
在当前sheet中,取得sheet名的操作:
=MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,LEN(CELL("FILENAME",A1)))
解说:
1. CELL("FILENAME",A1)
可以取得类似 D:\test\[test.xls]Sheet1 的字符串。其中Sheet名是当前所在Sheet的名称。
2. 通过MID来解析并拆分出字符串Sheet1,以达到目的。
二、获得当前Workbook的sheet一览。
这个操作需要使用Workbook的Name定义。
操作方式:
1. 增加Name定义Sht,内容为
=REPLACE(GET.WORKBOOK(1), 1, FIND("]", GET.WORKBOOK(1)), ) & T(NOW())
2. 在公式中使用时,用INDEX(Shts, 数字) 来取得对应的Sheet名。
例如:
=IF(ROW()>COUNTA(shts),"",HYPERLINK("#"&INDEX(shts,ROW())&"!A1",INDEX(shts,ROW())))
上面的公式得到的结果是,以行号(ROW())为索引,逐行显示Sheet名,并且将显示单元格连接到对应的Sheet中的A1单元格上。
解说:
1. Name定义中,使用 & T(NOW())的意图是随时更新链接信息,如果没有,则在修改后,可能出现使用的列表是修改前的情况。
2. GET方法,必须在Name定义中使用,直接写在公式中,不会被识别。查找了一下原因,是因为GET方法是宏表函数。关于GET,还有GET.DOCUMENT等。
增加Name的VBA
Sub addName()
Dim strName As String
strName = "Shts"
Names.Add Name:=strName, RefersToR1C1:= _
"=REPLACE(GET.WORKBOOK(1),1,FIND(""]"",GET.WORKBOOK(1)),)&T(NOW())"
End Sub
三、取得字符串对应的单元格的值
=INDIRECT(ADDRESS(3, 2, 1, FALSE, INDEX(Shts,(ROW()+2))), FALSE )
解说:
1. ADDRESS函数,返回类似Sheet1!$E$4的字符串。
2. INDIRECT函数,返回指定字符串对应的单元格的内容。
分享到:
相关推荐
Microsoft excel是一款功能非常强大的电子表格软件。它可以轻松地完成数据的各类数学运算,并用各种二维或三维图形形象地表示出来,从而大大简化了数据的处理工作。但若仅利用excel的常用功能来处理较复杂的数据,...
在新手使用Excel时,常常不知道用什么函数来解决问题,下面就列出来常用EXCEL函数 用法
Excel常用电子表格公式,里面有一些常用的函数应用,让你轻松解决一些常用的excel问题。
Professor Excel Tools Premium(Excel教授)是专为微软Excel和国产WPS Office中的表格软件量身定做的一款专家级excel插件工具箱,内置多达125个超级实用的Excel函数极大的扩展强化了Excel表格的功能,强大的复制和...
解决方法如下: 1、假定你的数据在A至E列,请在F1单元格输入公式: =LARGE($A1:$E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值...
Professor Excel Tools Premium(Excel教授)是专为微软Excel和国产WPS Office中的表格软件量身定做的一款专家级excel插件工具箱,内置多达125个超级实用的Excel函数极大的扩展强化了Excel表格的功能,强大的复制和...
一些最常用的EXCEL操作技巧:(非常实用) 快速将数据从MSFLEXGRID控件导出到EXCEL(保证比那些一格一格写数据的要快的多), 快速将数据从EXCEL导入到MSFLEXGRID控件(同上) 如何通过剪贴板将数据在程序和EXCEL中相互...
淘宝上花钱请人扫描的,觉得好的话还是买实体书! 一本非常不错的书籍,所以...适合需要用Excel解决复杂问题,或准备利用Excel VBA技术开发Excel应用程序的读者,也适合大中专院校的学生阅读,还可作为VBA的培训教材。
常用的Excel打开方式有两种 1)通过数据库打开; 2)OLE方式打开。 由于方式1)操作繁琐,经常出现莫名的错误,这里选用方式2). 4、准备步骤 首先新建一个Dialog窗体程序,添加list control和两个按钮 1...
Microsoft excel是一款功能非常强大的电子表格软件。它可以轻松地完成 数据的各类数学运算并用各种二维或三维图形形象地表示出来从而大大简化 了数据的处理工作。但若仅利用excel的常用功能来处理较复杂的数据...
《别说你懂Excel:500招玩转Excel表格与数据处理》按照“学以致用”的原则,力求解决用户在使用Excel中遇到的各种疑难问题,以及传授提高工作效率的相关经验与技巧。 全书共分为18章,由浅入深,全面地讲解了Excel...
excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式 按...
本书采用拟人化的手法介绍一个个Excel中的“小伙伴”,让他们一起解决那些精选的、在各行各业 “出镜率”都较多的问题,让读者在愉快阅读时有所思索,有所感悟,最终帮助他们解决实际数据处理效率难题
一、 基本方法 7 1. 快速选中全部工作表 7 2. 快速启动EXCEL 7 3. 快速删除选定区域数据 7 4. 给单元格重新命名 7 5. 在EXCEL中选择整个单元格范围 7 6. 快速移动/复制单元格 8 7. 快速修改单元格式次序 8 8. 彻底...
单击按钮,在弹出的下拉菜单中单击“Excel选项”按钮,在弹出的“Excel选项”对话框中,单击“常用”选项,在“使用Excel时采用的首选项”选项组中的“屏幕提示样式”下拉列表中,选择所需的选项。 2.为什么在功能...
《Excel统计分析与应用》精选62个专业案例,覆盖90%以上的统计模型,以实验教程的形式讲解如何以Excel为工具,解决各种统计分析问题。全书共11章,第1~2章介绍Excel基本操作及其统计分析常用功能;第3~10章通过37...
ExtJS实现Excel导出,最常用的可以解决大部分问题
1 advance_fenye2.0.php 一个分页的类,非常实用 2 char.php 汉字编码解决 3 class-excel-xml.inc.php excel文件生成类 4 image.php 图片缩放类
【修复Excel】:当您的Excel某些功能无法使用时,或者无缘无故多出很多菜单、工具栏时,本工具可以瞬间恢复Excel到默认状态 【破解VBA密码】:将VBA编写的且有密码的程序进行破解,瞬间完成 【生成2003样式菜单】:...