要求:
步骤
- 数据有效性、VLOOKUP函数匹配(略)
- 配置好四个控件按钮并关联
- 数据源增加两列辅助列,并使其随控件变化
A1=INDEX(E1:K1,辅助!$C$1)
B1=INDEX(L1:M1,辅助!$A$1)
- 准备好条形图的辅助数据并排序
B17=INDEX(B2:B14,$B$1)
B19=EOMONTH(B17,0)
L2=RANK(N2,$N$2:$N$11)+COUNTIFS($N$2:N2,N2)-1
M2=HLOOKUP($M$1,$E$1:$K$11,ROW(A2),FALSE)
#数据源B列-销售额/利润额,数据源C列-时间,数据源A列-选项按钮选择的指标
N2=SUMIFS(数据源!$B:$B,数据源!$C:$C,">="&$B$17,数据源!$C:$C,"<="&$B$19,数据源!$A:$A,M2)
手动输入1-10的数字作为排名,后面两列用VLOOKUP匹配
- 选择Q2,R2,定义名称
公式-名称管理器
#xvalue
=OFFSET(辅助!$Q$2,,,COUNTIF(辅助!$Q$2:$Q$11,"<>0"),1)
#value
=OFFSET(辅助!$R$2,,,COUNTIF(辅助!$R$2:$R$11,"<>0"),1)
- 插入条形图
删除网格线,右击纵坐标轴,坐标轴选项,逆序类别,删除坐标轴,插入数据标签
系列选项-间隙宽度-50
由3个控件控制,动态变化
- 准备好折线图的辅助数据
E13=EOMONTH(E14,0)
#E23=INDEX(辅助!Q2:Q11,辅助!S1)=写字楼
#数据源B列-销售额/利润额,数据源C列-时间,数据源A列-选项按钮选择的指标
E16==SUMIFS(数据源!$B:$B,数据源!$C:$C,">="&E$14,数据源!$C:$C,"<="&E$13,数据源!$A:$A,$E$23)
- 插入折线图
由4个控件控制,动态变化
- 智能对话
- 辅助数据
D19=INDEX(B2:B14,B1)
D20=D19
D21=EOMONTH(D20,0)
#数据源L列-销售额,数据源M列-利润额,数据源C列-时间
E19=SUMIFS(数据源!$L:$L,数据源!$C:$C,">="&$D$20,数据源!$C:$C,"<="&$D$21)
F19=SUMIFS(数据源!$M:$M,数据源!$C:$C,">="&$D$20,数据源!$C:$C,"<="&$D$21)
G19=F19/E19
- 智能对话
="你好!你选择的日期是"&TEXT(INDEX(辅助!B2:B14,辅助!B1),"yyyy年m月")&",该月所有店铺的总销售额为"&辅助!E19&"元,总利润额为"&辅助!F19&"元,毛利率为"&TEXT(辅助!G19,"0.00%")&"元"
- 完善表头,随控件按钮自动变化
折线图的横坐标斜向显示-坐标轴选项-文本选项-文本框-文字方向-横排