1.5 数据维度转换
1.5.1 使用函数建立目录
【问题】
有时，一个工作簿中会有很多工作表，为了方便查找，要给若干个工作表建立目录，如图1-174所示。
图1-174 目录
【实现方法】
将光标放在A1单元格上，选择“公式”“定义名称”，在打开的“新建名称”对话框的“名称”框中输入“目录”，在“引用位置”中输入公式“=INDEX（GET.WORKBOOK（1），ROW（A1））&T（NOW（））”，如图1-175所示。
图1-175 自定义名称
在A1单元格中输入公式“=IFERROR（HYPERLINK（目录&"！A1"，MID（目录，FIND（"]"，目录）+1，99）），""）”，按Enter键执行计算，再将公式向下填充，即得到所有工作表的目录，如图1-176所示。
图1-176 目录公式
在目录工作表以外的合适位置，输入公式“=HYPERLINK（"＃目录！A1"，"返回目录"），即可得到“返回目录”的链接，如图1-177所示。
图1-177 返回目录公式
【公式解析】
（1）总公式为“=INDEX（GET.WORKBOOK（1），ROW（目录！A1））&T（NOW（））”。
•GET.WORKBOOK（1）：用于提取当前工作簿中所有的工作表名称。
•INDEX：按ROW（A1）返回的数字决定要显示第几张工作表的名称。
•GET.WORKBOOK（1）：在数据变动时不会自动重算，而NOW（）是易失性函数，因此在公式中加上NOW（）函数会让公式自动重算。
•T（）：将NOW（）产生的数值转为空文本，也就是相当于在工作表名称后加上&""。
（2）目录公式为“=IFERROR（HYPERLINK（目录&"！A1"，MID（目录，FIND（"]"，目录）+1，99）），""）”。
•FIND（"]"，目录）：用于查找符号“]”在自定义名称“目录”计算结果中的位置。
•MID（目录，FIND（"]"，目录）+1，99）：表示从“目录”中的“]”符号后一个字符处取值，取值长度为比较大的字符，这里设为“99”，也可自行设置其他长度。
•HYPERLINK：Excel超级链接的函数实现方法。当单击函数HYPERLINK所在的单元格时，Excel将打开链接的文件或跳转到指定的工作表的单元格。
•IFERROR：用于屏蔽错误。
（3）返回目录公式为“=HYPERLINK（"＃目录！A1"，"返回目录"）”。HYPERLINK函数格式为“HYPERLINK（link_location，[friendly_name]）”。
•link_location：必需，可以作为文本打开文档的路径和文件名。
•friendly_name：可选，单元格中显示的跳转文本或数字值。friendly_name显示为蓝色并带有下画线。如果省略friendly_name，单元格会将link_location显示为跳转文本。friendly_name可以为数值、文本字符串、名称或包含跳转文本或数值的单元格。
*特别注意：因为引用了宏表函数，所以文件保存时要保存成“启用宏的工作簿.xlsm”。
1.5.2 不使用函数建立目录
【问题】
在1.5.1节中，使用函数建立目录，下面介绍一种不使用函数建立目录的方法。
【实现方法】
（1）选定所有工作表。在工作表标签上面，右击，在弹出的快捷菜单中选择“选定全部工作表”命令，如图1-178所示。
图1-178 选定所有工作表
（2）输入公式。此时，所有工作表处于选定状态，在A1单元格中输入“=XFD1”，按Enter键执行计算，此函数返回值为“0”，如图1-179所示。
图1-179 输入公式
Excel 2003及之前的版本文件有256（2的8次方）列，即到IV列；Excel 2007及以后的版本有16384（2的14次方）列，即到XFD列。这里引用XFD1单元格的数值，为0。
（3）自动生成“兼容性报表”。单击“文件”→“信息”→“检查问题”→“检查兼容性”，如图1-180所示。
图1-180 打开兼容性检查器
在打开的“Microsoft Excel-兼容性检查器”中单击“复制到新表”按钮，如图1-181所示。自动添加一个工作表，名为“兼容性报表”，报表中自动生成目录，如图1-182所示。
图1-181 复制到新表
图1-182 生成兼容性报表
（4）复制目录到“目录”工作表。复制“兼容性报表”中的目录到“目录”工作表中，如图1-183所示。
图1-183 复制兼容性报表中的目录
1.5.3 链接到另一张表的4种方法
【问题】
由一张工作表链接到另一张工作表有4种方法，即文字、形状、图标、ActiveX控件。
【实现方法】
1）文字
文字形式的超链接设置最简单，直接选择文字所在单元格，右击，在弹出的快捷菜单中选择“链接”→“插入超链接”→“本文档中的位置”→“工资明细表”，如图1-184所示。
图1-184 文字超链接
2）形状
（1）插入形状。
（2）设置形状超链接的步骤：选中形状，右击，在弹出的快捷菜单中选择“链接”→“插入超链接”→“本文档中的位置”→“工资明细表”，如图1-185所示。
图1-185 形状超链接
3）图标
（1）插入图标：首先单击“插入”→“图标”，再选择合适图标。
（2）图标超链接和形状超链接、文字超链接的设置步骤一致：选中图标，右击，在弹出的快捷菜单中选择“链接”→“插入超链接”→“本文档中的位置”→“工资明细表”，如图1-186所示。
图1-186 图标超链接
4）ActiveX控件
（1）插入控件。单击“开发工具”→“插入”→“命令按钮”，然后选中该命令按钮，单击“属性”，改“Caption”为“工资明细表”，如图1-187所示。
图1-187 改“caption”为“工资明细表”
（2）选中该命令按钮，单击“查看代码”，或按Alt+F11组合键，打开“VBA”对话框，输入代码，如图1-188所示。
图1-188 输入代码
注意：
•命令按钮，只有在关闭“设计模式”下才能触发。要修改该按钮，要选中“设计模式”选项，如图1-189所示。
图1-189 设计模式
•带有VBA命令的工作表保存时要选择“启用宏的工作簿（.xlsm）”格式，下次打开时，要单击“启用内容”按钮，如图1-190所示。
图1-190 选择“启用内容”
以上就是链接到其他表格的4种方法，在使用时，可选用适合当前数据的方式。
1.5.4 单击订单名称，即可跳到订单详情工作表
【问题】
年末汇总表格时，“订单完成表”与“订单详情表”不在一个表格中，能不能在“订单完成表”中单击某订单，即可跳转到“订单详情表”中该订单所在行呢？
【实现方法】
这可以用HYPERLINK函数来解决。
在E2单元格中输入公式“=HYPERLINK（"＃订单详情！A"&MATCH（C2，订单详情！A：A，0），订单详情！A3）”，按Enter键执行计算，再将公式向下填充，即可得到所有订单编号。单击订单编号，即可跳转到“订单详情表”内对应编号的订单详情，如图1-191所示。其中订单详情表，如图1-192所示。
图1-191 订单完成表
图1-192 订单详情表
【公式解析】
•MATCH（C2，订单详情！A：A，0）：用MATCH函数精确匹配出C2订单在“订单详情表”中的行数。将“订单详情表中”中A与C2匹配出的行数用文本连接符（&）链接，组成单元格地址；如C2行数是3，则单元格地址是“订单详情表”中的A2；前加“＃”指单元格地址，该符号千万不能省略。
•HYPERLINK（"＃订单详情！A"&MATCH（C2，订单详情！A：A，0），订单详情！A3）：表示跳转到“订单详情表”中的对应订单，并将链接名称显示为该订单号。