从二维表取数的嵌套演练

Ø
从二维表取数的嵌套演练

本文有三个目的：1、解决二维表取数；2、介绍函数的嵌套使用；3、让有学习需求的朋友找得老师。

先来看题目

已知数据如下：

B10	1	2	3
预算	100	122	133
实际	99	120	132

要求得到的效果如下：

项目（B4）	1月		2月		3月
	预算	实际	预算	实际	预算	实际
产量	100	99	122	120	133	132

着色区域设置一个函数来实现，下面介绍四种做法：

l
方法1

设置公式=OFFSET($B$10,IF(C5="预算",1,2),INT((COLUMN(C4)-1)/2),)

先来个简单的判断IF(C5="预算",1,2)，如果是预算，则返回1，如否返回2

CD列取得是基础表第一列，EF列取的是基础表第二列，GH列取的是基础表第三列：

设置公式INT((COLUMN(C4)-1)/2)，第三列C6，返回1，第四列（D列）公式取整还是1

往下推理，第五列(E)列返回2，第六列也返回的是2。

l
方法2

设置特殊样式的合并单元格：
表头先设置为如下样式
1月	1月	2月	2月	3月	3月
再横向合并2个空的单元格
选定合并好的单元格，点格式刷，刷月份区域得到如下效果
1月		2月		3月

再用HLOOKUP函数

公式=HLOOKUP(--SUBSTITUTE(C4,"月","",1),$C$10E$12,MATCH(C5,$B$10B$12,0),0)

利用match函数

用字符替换函数把“月”替换为空，得到的是文本，所以加--变数值

公式：--SUBSTITUTE(C4,"月","",1)完成了我们第一步的转换，可以从基础表中找到相符的月份。

Hlookup在第几行取数，我们利用了基础表有“预算”和“实际”字段，用match函数即可

公式：MATCH(C5,$B$10B$12,0)实现的效果是：预算返回2，实际返回3

l
方法3

设置表头样式如方法2，再做如下处理：

再用SUMPRODUCT函数

=SUMPRODUCT(($C$10E$10=--SUBSTITUTE(C4,"月","",1))*($B$11B$12=C5)*$C$11E$12)

第一个条件，月份要和表头月份相符

第二个条件，预算或者实际

sumproduct针对两个条件同时满足的进行求和，即可完成取数

l
方法4

组合公式：

=OFFSET($B$10,MATCH(C5,$B$11B$12,0),INT(COLUMN()-1)/2)

我们分析取数的规律，是从B10单元格，分别下移1,2,1,2,1,2行

右移的列数分别是1,1,2,2,3,3列

所以我们设置了两个函数分别实现

管行数的公式：MATCH(C5,$B$11B$12,0)

管列数的公式：INT(COLUMN()-1)/2

最后用offset，以锁定B10作为起点，即可完成取数。