核心结论 #

Power Query的内存占用逻辑和VBA存在明显差异，对老旧设备的适配性更好，不会始终将所有查询数据驻留内存。

Power Query的内存加载机制 #

• 按需加载数据：Power Query默认不会长期占用内存存储所有查询表数据。仅在查询被刷新执行、编辑预览，或依赖该查询的工作表对象（如透视表、图表）需要调用数据时，才会临时将对应数据加载到内存。
• 自动释放与缓存策略：查询执行完成后，若未主动开启Power Query的永久缓存（默认是按需缓存模式），临时数据会被自动释放。如果将查询设置为「仅创建连接」而非直接加载到工作表，内存占用会进一步降低——此时仅保存查询逻辑，实际数据不会常驻内存。
• 查询折叠优化：如果你的ODBC数据源支持查询折叠（如SQL Server、Oracle等关系型数据库），Power Query会将过滤、聚合等处理逻辑推给数据源执行，仅返回最终需要的结果，不会把全量数据拉取到本地内存，这能大幅削减内存压力。

与VBA的内存负载对比 #

• VBA的内存特性：如你所知，VBA执行时会将处理的全量数据加载到内存，直到子程序结束才释放。处理大数据量时，老旧设备极易出现内存不足问题，且VBA内存管理机制相对原始，偶尔会出现内存泄漏情况。
• Power Query的优势：
  • 无需一次性加载全量数据，借助查询折叠可让数据源端先完成大部分计算，本地仅获取最终结果。
  • 即使不支持查询折叠，Power Query的内存管理也更高效，数据处理过程中会自动释放中间步骤的临时数据，仅保留最终必要的部分。
  • 设置查询为「仅连接」后，用户打开Excel文件时仅加载查询定义，不会自动拉取数据，内存占用和普通空表相近，仅在手动刷新时才会加载数据。

针对老旧设备的优化建议 #

• 优先利用查询折叠：可在Power Query编辑器的「高级编辑器」中检查步骤是否支持折叠（带闪电图标表示支持），确保尽可能多的处理逻辑由数据源执行。
• 设置查询为仅创建连接：避免直接将数据加载到工作表，通过透视表等对象按需调用数据，减少常驻内存的数据量。
• 关闭不必要的预览：在Power Query编辑器中关闭数据预览，减少无意义的内存加载。
• 拆分复杂查询：将大查询拆分为多个依赖的小查询，每个查询仅处理必要数据，降低单次加载的内存压力。

内容的提问来源于stack exchange，提问作者Teb Schleb