观远 BI

中国式报表Pro—正确使用Excel Lookup函数

创建于 2023-09-18 / 最近更新于 2023-09-19 / 1915
字体: [默认] [大] [更大]

适合场景

1)跨视图查询数据,即查询条件和查询结果来源于不同数据源或者视图

2)查询条件包含静态文本

选择函数

     Excel里常见的Lookup查询函数有 LOOKUP/VLOOKUP/HLOOKUP/XLOOKUP,适合场景各有不同,一般建议使用XLOOKUP,兼容单条件、多条件,横向、纵向查询,且支持把查询无数据显示的#N/A错误值替换为希望的值;如果使用其他Lookup函数,建议外面嵌套IFERROR来处理错误值。

使用思路

     Lookup类函数一般至少需要用到3个参量,分别是「搜索值」、「搜索范围」、「结果存放范围」。其中「搜索值」一般指定当前模板上的单元格,但是「搜索范围」、「结果存放范围」来源于视图,需要先把视图里的数据放入模板内才能引用。

使用步骤

1)创建查询表:建议在模板里新建一个Sheet,把视图里的字段拖入表格区域, 多维度时设置动态属性里分组方式为“归类重复”(对应属性G=Repeat, 确保扩展后所有单元格不合并);

图片.png

2) 检查模板里用作「搜索值」的单元格扩展后是否有合并单元格的情况,有的话需要在另外插入的行列或者Sheet里制作同样结构的表头并设置分组方式为“归类重复”;

图片.png

      在旁边插入行列:

图片.png

      预览效果:

图片.png

3)手动输入需要的Lookup函数;由于行和列号会跟随扩展偏移,公式里要注意是绝对引用还是相对引用。

     例如大区所在A4,纵向扩展时列不变、行号会变,公式里写作$A4; 月份所在的D1,横向扩展时,列变行不变,写作D$1。多条件查询写法参考下图。

图片.png

4)把该单元设置为模板单元格,并指定父格,从而实现跟随表头扩展;

xlookup.gif

5)预览数据无误后隐藏多余的Sheet和行列。

图片.png

3 人点赞过