1. 概述
1.1 问题描述
在进行多 sheet 报表设计的时候,两个 sheet 里面都用到了扩展数据集,如何从一个 sheet 里面获取另一个sheet里面对应的数据的值呢,如下:
sheet1 sheet2
如果想从sheet1获取sheet2中对应销售员的销量值,直接在sheet1输入sheet2!B2,会出现下面的情况:
1.2 实现思路
将 sheet2 中的销售员和对应的销量进行拼接,之后在 sheet1 中获取此拼接后的值,根据 sheet1 的销售员对数据进行筛选,最后将筛选值中的销售员去除,留下的即为该销售员的销量。
注:该方案不支持聚合报表。
2. 示例
2.1 数据准备
新建数据查询 ds1 ,SQL查询为:SELECT * FROM 销量,如下图所示:
2.2 模板设计
1)将数据集字段拖到 sheet1、sheet2 的单元格 A2、B2 ,如下图所示:
SHEET1
SHEET2
2.3 输入公式
1)在sheet2的C2单元格输入公式:=A2+B2
2)在sheet1的C2和D2单元格输入公式,如下表所示:
单元格 | 公式 | 说明 |
C2 | 'sheet2'!C2 | 获取sheet2中销售员和销量拼接后的值 |
D2 | REPLACE(GREPARRAY(C2, LEFT(item,LEN(A2))=A2),A2,"") | 根据当前行销售员进行过滤,筛选出对应销量 |
公式解释:
公式 | 说明 |
---|---|
LEN(A2) | 取 A2 单元格中值的字符长度 |
LEFT(item,LEN(A2)) | 取 item 内容的最左边 LEN(A2) 个字符,其中 LEFT 返回文本串中的最左边前几个字符 |
GREPARRAY(C2, LEFT(item,LEN(A2))=A2) | 过滤数组,过滤出和 A2 单元格销售员一致的数据,GREPARRAY 过滤数组,最后形成一个新数组 |
REPLACE(GREPARRAY(C2, LEFT(item,LEN(A2))=A2),A2,"") | 把销售员替换为空,只保留销量数据,REPLACE 根据指定字符串,用其他文本来代替原始文本中的内容 |
2.4 效果预览
1)PC端
2)移动端
3. 模板下载
点击下载模板:跨sheet数据筛选和联动.cpt