文章目录
一、indirect单元格引用
1.indirect与Index引用方式的对比
INDEX(数组,行序数,[列序数],[区域序数])
返回数据清单或数组中的元素值,此元素由行序号和列序号的索引值给定
- 取每周第二个数值(index)
INDIRECT(单元格引用,[引用样式])
返回由文本字符串所指定的引用
- 取每周第二个数值(indirect)
2.跨表引用
(1) 顺序相同
-
获取张三每个月的业绩
- 1月业绩如下:
- 2月业绩如下
- 3月业绩如下
以此类推其他月份,可以得出规律“‘4月’!G2、‘5月’!G2,…”
- 1月业绩如下:
-
获取张三每个月的业绩(indirect)
(2) 顺序不同
-
获取张三每个月的业绩(indirect)
每个月张三所在位置不同
- 1月张三的业绩在第2行
- 2月张三的业绩在第4行
- 3月张三的业绩在第2行
-
先考虑在一张表中张三的业绩怎么取?用vlookup()函数
-
以此类推,在别的月份也是这样获取,只是表名会变化,即将“‘1月’!A:H”改为“INDIRECT(A4&“!A:H”)”
3.跨表引用时的单引号问题
若跨表引用时,引用列有空格,如“1 月”中间有空格,会发现结果报错;那么如何解决呢?可以在引用列A4前后加上单引号,中间用&连接即可。
二、Indirect名称引用
1.为区域定义名称
- 获取张三、李四、王五、赵六每个月的业绩
- 目标数据
- 目标数据
- 步骤
首先写出“张三在1月的业绩”,“VLOOKUP(“张三”,‘1月’!A:H,7,0)”
然后改变“张三”和“1月”即实现区域引用,“张三”可以用B2、C2、D2、E2来代替,且A-H的引用区域不变,如下图:可实现向右拖拽
最后,将1月改成INDIRECT(A$ 3&“!$ A:$ H”),可实现向下拖拽
2.制作二级下拉列表
- 汇总“张三、李四、王五、赵六”12个月的业绩总和
- 首先选中“张三”1-12月份单元格数据,点击“公式-名称管理器-新建名称-张三”,如下图:
再次选中张三1-12月数据,发现会显示“张三”,此时的张三代表1-12的数据,如下图:
以此类推,新建名称“李四、王五、赵六”
- 接着选中任意一个单元格,点击“数据-有效性-序列”,输入“张三,李四,王五,赵六”,中间用英文逗号隔开,即可得到一个下拉列表
- 最后输入“=indirect(G3)”,可得到张三1-12月的业绩数据,然后用sum()汇总;选择不同的下拉列表,会得出不同的汇总结果
- 首先选中“张三”1-12月份单元格数据,点击“公式-名称管理器-新建名称-张三”,如下图:
- 制作省份的下拉列表,并根据省份筛选其下城市
选中任意一列(做省份的下拉列表),点击“数据-有效性-序列”,来源是各省份数据,此时下拉列表就做好了
接下来做城市下拉列表,首先选中各省份下的城市数据,点击“公式-名称管理器-名称”,输入“吉林省”,此时,再选中吉林省数据发现会显示吉林省,其他省份亦如此。
最后,如何根据各省份显示不同的城市名称呢?首先选中G列,点击“数据-有效性”,选中“序列”,输入来源“=indirect($f1)”,即不管F列选了什么,都会将所选文字转成一次引用,并将引用对应的结果返回。
此时会发现,选择“吉林省”,会出现吉林省下属城市;选择“江苏省”,会出现江苏省下属城市
系列文章目录
第1讲:excel常用的基础操作
第2讲:excel单元格格式设置
第3讲:查找、替换、定位
第4讲:排序与筛选
第5讲:分类汇总、数据有效性
第6讲:数据透视表
第7讲:excel常用函数与公式
第8讲:if函数
第9讲:countif函数
第10讲:sumif函数
第11讲:vlookup函数
第12讲:match+index函数
第14讲:日期函数
第15讲:条件格式与公式
第16讲:简单文本函数
第17讲:数学函数
第18讲:lookup和数组
第19讲:indirect函数
第20讲:图表基础
第21讲:经典excel动态图表实现原理
第22讲:制作甘特图与动态甘特图
第23讲:双坐标柱形图、饼图美化与ppt图表
第24讲:宏表函数