Excel常用解决方法（一）

    日常工作中常常要用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函数，返回指定字符串对应的单元格的内容。