教程 | Excel性能优化方法（四）
2024-11-05 04:49:53
01
对一组连续的行或列使用 INDEX
还可以从一个查找操作返回多个单元格。要查找多个连续的列，可以使用数组公式中的 INDEX 函数一次返回多个列（使用 0 作为列号）。还可以使用 INDEX 函数一次返回多个行。
这将从之前的 MATCH 语句创建的存储行返回列 A 到列 J
02
使用子集区域进行多索引查找
在大型工作表中，可能经常需要使用多个索引来进行查找，例如查找某个国家/地区的产品量。为此，可以连接索引并使用连接的查找值执行查找。然而，有两个原因会导致其效率低下：
计算查找的子集区域通常更有效（例如，通过查找国家/地区的第一行和最后一行，然后在该子级区域内查找产品）。
03
考虑三维查找选项
除行和列外，若还要查找要使用的表，则可以使用以下方法，这些方法重点处理如何使 Excel 查找或选择表。如果要查找的每个表（第三维）都存储为一组已命名结构化表、区域名称或存储为表示区域的文本字符串表，则可以使用 CHOOSE 或 INDIRECT 函数。使用 CHOOSE 和区域名称是一种有效方法。 CHOOSE 是不可变函数，但最适用于相对较少的表。此示例动态使用 TableLookup_Value 来选择要用于查找表的区域名称 ( TableName1, TableName2, ... )。
下面的示例使用 INDIRECT 函数和 TableLookup_Value 动态创建要用于查找表的工作表名称。这种方法的优点是简单，能够处理大量表。因为 INDIRECT 是可变的单线程函数，所以即使没有数据发生变化，查找也是每次计算时计算的单线程。使用此方法速度较慢。
还可以使用 VLOOKUP 函数查找要用于表的工作表名称或文本字符串，然后使用 INDIRECT 函数将结果文本转换为区域。
另一方法是将所有表聚合到一个大型表中，该表包含另一标识各个表的列。然后可以使用前面的示例中所示的多索引查找方法。
04
使用通配符查找
MATCH、VLOOKUP 和 HLOOKUP 函数允许你在按字母顺序完全匹配的情况下使用通配符 ? （任何单个字符）和 *（没有字符或任意数量的字符）。有时，可使用此方法避免多个匹配项。
05
优化数组公式和 SUMPRODUCT
数组公式和 SUMPRODUCT 函数功能强大，但必须谨慎处理。单个数组公式可能需要多次计算。优化数组公式计算速度的关键在于，确保尽可能减少在数组公式中计算的单元格和表达式的数量。请记住，数组公式与可变公式有点相似：如果它引用的任何一个单元格发生更改、可变或者经过重新计算，则数组公式会计算该公式中的所有单元格并计算执行计算所需的所有虚拟单元格。要优化数组公式的计算速度，请执行以下操作：将数组公式中的表达式和区域引用提取到单独的辅助列和行中。这样可以更好地利用 Excel 中的智能重新计算过程。不要引用完整行，或引用超出需要的行和列。数组公式被迫计算公式中的所有单元格引用，即使单元格为空或未使用。从 Excel 2007 开始，有 100 万行可用，引用整个列的数组公式的计算速度极其缓慢。从 Excel 2007 开始，如果可以，请使用结构化引用来最大程度地减少数组公式计算的单元格数。在 Excel 2007 之前的版本中，尽可能使用动态区域名称。尽管它们可变，但这是值得的，因为它们最大程度缩小了区域大小。注意同时引用行和列的数组公式：这将强制计算矩形区域。尽可能使用 SUMPRODUCT；它比等效的数组公式速度稍微快一点。
热门标签
