【excel】多表汇总查询实现

产品功能实现

数据在不同表中,在第一张总表里面对他进行跨表汇总统计,不需要复制粘贴统计完成动态引用查询
在这里插入图片描述
在这里插入图片描述
第一步:vlookup

知识点补充

查询vlookup

基本逻辑

表名!区域
=Jan!&"!A1:B100"

在这里插入图片描述
返回对应值,返回文本字符串需要活化处理

=indirect(H$85&"!A1:B100"//引用
=vlookup($f86,indirect(,H$85&"!A1:B100"),2,0)//查询(产品id,引用,第二列,精确)

快捷键补充

ctrl + e (删掉列中多余表格)在第二行中删除
在这里插入图片描述

address函数

构建变化参数所用range;
根据指定行号和列号获得工作表中的某个单元格的地址,例如,address(2,3)返回$c 2 相 当 于 c e l l 的 使 用 行 列 锁 定 , 在 例 如 , a d d r e s s ( 77 , 300 ) 返 回 2相当于cell的使用行列锁定,在例如,address(77,300)返回 2cell使address77300KN$77,可以使用其他函数(如row和cloumn函数)为address函数提供行号和列号参数。

引用F4

绝对引用:固定不变
相对引用:相对变动
eg:$A$1固定range位置;
A$1 固定行,相对列;
$A1固定列,相对行
A1相对值,整个range都在变动

判断值

表名!range表示来源

,,表示预留位置

address接受五个函数不满足就占位

返回文本值

模仿address函数
eg:=if(G6)<>"",G63&"!"&if((E63=1)+(E63=3) ,"$ “,”")&char(64+C63)&if((e63=1)+(e63=2),"$","")&D63
表示如果G63不为空,G63用!填充,&表示链接绝对引用和相对引用的地址,e63=1或3,用 $填充,char构造字母输入构造if表示对第二个绝对引用做符号1或者2锁定行
在这里插入图片描述
在这里插入图片描述

名称管理器

求和

1、查找和替换—点击选项—格式—从单元格选择格式—吸取颜色(目标是根据颜色进行查找)
2、ctrl+a选中单元格—点击关闭
3、点击公式—名称管理器—新建名称
(需要填写范围工作簿,引用位置ctrl+a调用过的)—关闭
4、求和区域输出
=sum(名称)

product函数

对于范围进行求和g11到g15乘积
在这里插入图片描述

sumif使用

sumif(范围,需要的关键字所在的range,求和区域)

offset位移

countifs条件计算

=countifs(关键词1范围,关键词1range,筛选1范围,筛选词1range,筛选2范围,筛选词2range)属于或的关系
只能执行一一对应关系,只能加上公司c的对应
在这里插入图片描述

sumproduct(最后阶段是求和)

F4绝对引用是$列 $行,F9展开可以查看是否正确使用公式,不0逻辑{1,0}

  • 联合计算sum代表求和,product乘积(先乘再相加)
    在这里插入图片描述

  • 按日进行销售额计算(绝对引用数组单价区域1,求和区域2)下拉
    在这里插入图片描述

  • 关键字求和(- -(确认关键字范围=关键字所在range),求和区域)
    在这里插入图片描述

  • 条件求和,定义x条件sumproduct(((关键词区域=关键词1)+(关键词区域=关键词2))(筛选区域=筛选条件1)(筛选区域=筛选条件2),求和区域)
    eg:限定关键词(公司,年份,月份)公司A,年份2014,月份2,
    在这里插入图片描述
    用或的关系(公司A或者+公司c的关系一起统计)
    且的关系(年且*月)
    在这里插入图片描述
    在这里插入图片描述

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页