EXCEL中数组函数运用范例
发布时间:2023-08-08 15:16:59
工作簿内一共有两个工作表Sheet1和Sheet2,先说Sheei1,如下
A B C
货号 序号 名称
101 1 车背带
101 2 合前片
101 3 合后片
101 4 车手带
101 5 车边片
101 6 合前袋
101 7 车后手带
202 1 车前片链
202 2 车前袋
202 3 合包
202 4 车后片
202 5 车手垫
202 6 合前袋
表Sheet2如下:
A B C
货号 序号 名称
101 5 [此单元格空]
101 2 [此单元格空]
101 3 [此单元格空]
101 7 [此单元格空]
202 3 [此单元格空]
202 1 [此单元格空]
要求根据Sheet2内容,查找Sheet1,在Sheet2的名称列填写入正确的内容。
题目分析:
根据要求,如果要在Sheet2某一行n填入正确的名称,首先需要在Sheet1找到这样的行:该行第一列内容等于Sheet2某行n第一列,该行第二列内容等于Sheet2某行n第二列。
因为涉及到数据的查找,拟采用数组公式实现。
1.首先定义名称:
(定义名称使用菜单:插入-名称)
dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
这样做的好处是在于无论Sheet1添加了多少行,dataA始终包括A列有内容的行,这样比起直接使用A:A整列,要节约很多的计算时间。dataB同理。
2.匹配条件
先匹配A列,选中Sheet2!D2:D13(因为原始数据从2~13行),在编辑栏输入
=IF(dataA=A2,1,0)
用Ctrl+Shift+回车,这样可以看到凡是与Sheet2!A2匹配的Sheet1的行,在这里都变成了1,而不匹配的则是0。
同理,在Sheet2!E2:E13输入
=IF(dataB=B2,1,0)
这样我们在D、E两列就得到了分别与Sheet2!A和Sheet2!B列匹配的数组。
3.合并匹配条件
我们需要的是两个条件的与运算,因此使用乘法是最好的,只有两个条件同时成立,1*1=1,否则两个乘数里面至少有1个为0,结果为0。
well,在Sheet2!F2:F13中输入数组公式:
=IF((dataA=A2)*(dataB=B2),1,0)
如此一来,就得到了一个新的由1和0组成的数组,某元素对应Sheet1的行如果能够匹配,该元素为1,否则为0。
4.检索行号
有了这个数组,我们需要得到匹配出来的行号,因为数组是由若干0和一个1组成的,所以我们只需要使用MATCH函数,就可以得到与Sheet2!An匹配的Sheet1的行号。
在Sheet2!G1输入
=MATCH(1,IF((dataA=A2)*(dataB=B2),1,0),1)
注意此处虽然仅仅是在一个单元格里面输入公式,但仍然是一个数组公式,需要使用Ctrl+Shift+Enter。
结果就是Sheet1中匹配的行号。
5.检索结果
有了行号,检索结果就很容易了,我们使用INDIRECT函数。
在Sheet2!C2输入数组公式:
=INDIRECT("Sheet1!$C$" & MATCH(1,IF((dataA=A2)*(dataB=B2),1,0),1))
注意依然使用Ctrl+Shift+Enter,正确的结果就出现了。
对于下面的行,只需要拖动填充句柄复制公式就可以了。
另外别忘了删除掉用来演示的D、E、F、G列的已经不再需要的公式。
猜你喜欢
- 当我们设置文字是有下划线,但在文字后输入空格,即空白的地方不显示下划线,那么怎样做才能做到软件自动对空格的地方也加上下划线呢,下面我们来看一
- 在Win10 1909系统中怎么关闭多余启动项?针对这一问题,小编今天为大家带来了Win10 1909多余启动项关闭教程,一起来看看吧。Wi
- 误差线和趋势线是为了方便用户分析、处理数据及预测数据的走向和走势,而为word图表提供的一种功能。误差线与趋势线只能用在二维图表中,不能用在
- 首先,依次点击布局——页面设置——文档网络——字体设置(右下方)设置之前的界面,问题症状表现设置,应该这样修改,如下图:弹出的界面,点击确定
- 我们在编辑文档的时候,总有出错的情况,如手太快,把写好的文字该删除了等等,这个时候,我们可以使用WPS中“撤销”和“恢复”功能来修改和恢复数
- Word下划线怎么删除?有用户在使用Word文档编辑文字时,会出现字体下面存在下划线的情况,那么有没有什么方法可以将其删除呢?今天小编就来和
- Ping是一个非常好用的网络指令,在遇到网络卡顿或者断网的情况下,网管通常第一句话就是让你ping一下。使用它可以让你快速检测电脑网络是否正
- 具体操作如下: 选中“深圳办事处”字样,点击 “插入—链接—书签”。下图2处 在新弹窗口中,输入“深圳“两字后,点击”添加“按钮
- 使用word来排版文档,有时候会由于不经意间的鼠标操作,导致文档的界面发生改变,最突出的问题莫过于本文给大家讲解的,文档页与页之间的距离突然
- 分两步来完成。① 合并成一页第一种情况,如果两页的文档是在同一个文档中,那么,你可以通过设置纸张的大小来使得新的一页里能容下原来两页的内容。
- 单位里要制作禁烟宣传条幅,那个“禁止吸烟”标志,可以用字母来转换实现:输入一个英文小写字母“z”(注意:一定要是小写的哟),将其字体设置为W
- word怎么输入分数?点击菜单栏的插入,在出现的选项中点击对象。在对象类型中选择Microsoft公式3.0。在出现的窗口中点击如图所示分式
- 在编辑长篇文章的时候,给每个文档加上页码,是很常用的功能。个别文章需要自定义页码,很多朋友可能觉得这一功能很简单,但实际上zaiword中自
- 当我们使用win10操作系统计算机时,一些小伙伴可能会在某种情况下遇到系统提示:不是有效的win32应用。对于这样的问题,主编认为我们的计算
- 很多人是因为还不知道熟练运用Word快捷键。善于利用快捷键可以大幅提高工作效率,减轻不少的工作负担。因此,今天小编给大家分享的是很多人使用过
- 应用场景下图为一份4页的文档,一共罗列了24个人物姓名,下面我们想将所有姓名按姓氏笔画由少到多进行排序,有没有什么便捷的方法呢?下面我们一起
- Word中怎么设置页眉?相信许多用户在办公或者学习的时候都需要使用到Word文档,而一份美观的Word文档会带来很多便利。而页眉设置就是其中
- 一、首先点击左上角“Office按钮”二、弹出的选项内点击“Word 选项”三、进入Word选项后,点击“高级”四、取消勾选“输入法控制处于
- 最近有用户问电脑桌面的图标桌面任意摆放。如果想要实现随意摆放图标,我们可以用鼠标右击桌面,在弹出的选项中进行相关的选择便可以实现这种操作。w
- 在wps文字中添加和删除页眉横线的方法:点击WPS页眉左上角“插入”→“页眉页脚” 菜单栏最右边出现“页眉页脚”快