怎样提取EXCEL表中多个工作表的同一列最后一个数据?

Excel工作表可以理解为一个由行、列组成的二维数组。Excel公式支持数组和数组公式。数组可以是常量数组,也可以是单元格区域的引用。使用数组和数组公式时,可以逐数组元素或逐单元格处理后返回结果。

下面直接从一个实例入手,再去讨论相关知识点。

1 实例(从文本里提取数字)

相关数据及需要达到的效果:

朋友过生日送礼金300元

关键是要获得第一个数字出现的位置。

可以返回3,表示字符1在字符串“吃饭15元”中的位置。

如果要查找0,1,2,3,4,5,6,7,8,9十个字符呢?这时,数组及数组公式就可以派上用场了。使用数组{0,1,2,3,4,5,6,7,8,9}作为参数,可以逐步处理每一个数字在字符串中出现的位置,返回的也是一个数组(10个值)。由此,我们选择一个有10个单元格的连续区域D2:M2,输入=FIND({0,1,2,3,4,5,6,7,8,9},B2,1),同时按下CTRL+SHIFT+ENTER,然后复制数组公式,得到如下效果:

错误值#VALUE!表示某数字在字符串中不存在,所以也就不能返回位置值。

为了避免出现错误值,可以在字符串后串一个包含10个数字字符的数字字符串:

下一步要获取数字长度。

函数LEN()可以返回字符数,函数LENB()可以返回字符串的字节数,一个数字一个字节,一个汉字两个字节,组合使用可以获取数字的长度:=LEN(B2)-(LENB(B2)-LEN(B2))

使用mid函数即可以截取数字,在公式前使用两个负号,可将文本转换为数字:

以下公式可以做一个改良:

得到的下面公式具有同样效果,更通用和简洁:

2 数组及数组公式相关知识

数组就是一组数据,数组公式可以进行多重运算(对每一个数组元素或区域的每一个单元格都应用操作),减少了多次写于单元格的过程,可以实现常用公式较烦锁的操作,一步到位。

当我们对一个区域应用数组公式时,如D2:D6,使用此区域,输入{=B2:B6*C2:C6},按CTRL+SHIFT+ENTER,会在D2:D6的每个单元格输入{=B2:B6*C2:C6},都是一样,此时数组公式是一个整体,不能更改数组的一部分,也就是不能单独删除其中的一个单元格的数组公式,我们要删除数组公式要全部选中,当我们有时修改一个单元格的数组公式,不能退出时,大家记得按ESC键。

2.3 一个单元格显示数组的情况

由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值。

可分为横向数组、纵向数组、区域数组(多行多列),也可以按维来分,横向数组和纵向数组都属于一维数组,区域数组属于二维数,像这种{1,2,3,1}就是常量数组

2.5 怎样查看数组的结果(也就是选择公式的一部分求结果),当然是独孤九剑F9或使用“公式求值”来按顺序求值复合公式的每一部分。

2.6 数组成员中间有时有分号,有时用逗号是怎么回事?横向数组用逗号分开,纵向数组用分号分开。

2.7 常量数据在函数里的应用

a) 数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。

b) 区域数组,是一个矩形的单元格区域,如 $A$1:$D$5

d) 数组公式中的参数必须为"矩形",如{1,2,3;1,2}就无法引用了

同时按下CTRL+SHIFT+ENTER,数组公式的外面会自动加上大括号{}予以区分。

3.2 求表二产品AA的总价

先用判断区域C50:C54是否有等于AA的,结果返回

4 Row()函数在数组公式中的运用

a) 谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为参数。

b) 我们先来做一个题目:求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵),=SUM(ROW($A$1:$A$100))。

c) 我们利用Row(A1:A100)来产生1到100自然数,然后用Sum求和。我们都知道,ROW()是用于返回单元格行号的函数,通常它只能引用一个参数。但是在数组公式中,该函数就能引用多个单元格作为参数,对于整个引用区域进行分别运算,从而就能返回一组数据。

知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续的正整数。

当然COLUMN()的作用和ROW()是相同的,上面的计算也能用以下公式:

但是相对于ROW()的引用方式来说,A1:A100要比A1:CV1更直观地体现出所引用的是100行还是100列,所以ROW()一般来说使用得更普遍些,当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。

}

很多时候,我们都需要从工作簿中的各工作表中提取数据信息。如果你在给工作表命名时遵循一定的规则,那么可以将VLOOKUP函数与INDIRECT函数结合使用,以从不同的工作表中提取数据。这项技术可以节省时间,提高效率。

假如有一张包含各种客户的销售数据表,并且每个月都会收到一张新的工作表。这里,给工作表选择命名规则时要保持一致。例如,可以使用Sales_Jan_2020、Sales_Feb_2020、Sales_Mar_2020。也就是说,将工作表按一定规则统一命名。

在汇总表上,我们希望从每个月份工作表中查找给客户XYZ的销售额。假设你在单元格区域B3:D3中输入有日期,包括2020年1月、2020年2月、2020年3月,在单元格A4中输入有客户名称。每个月销售表的结构是在列A中是客户名称,在列B中是销售额。

那么,就可以试试下面这个公式:

这个公式的工作原理:TEXT函数以Jan_2020的格式来格式化日期,&符号是一个连接运算符,因此最终会将Sales_Jan_2012!

当你有多个统一结构的数据源工作表,并需要从中提取数据时,本文介绍的技巧尤其有用。

注:本文整理自,供有兴趣的朋友参考。

}

  生活中我们偶尔会碰到这种情况,当你之前将所有数据已经都整理在一个Excel表格里了,但临时因为需要,却需要将里面的数据分开放在不同的工作表里,该怎么操作呢?利用透视表,我们就能分开这个表格了。

  如下图所示,从销售一部到销售七部的所有业绩,全部都在一个表里面,现在我们将表格中数据拆分到7个工作表中,并自动命名。


  Excel拆分工作表方法:

  1、普通Excel表格转换成透视表

  我们首先,选中表格,然后单击「插入」-「表格」-「数据透视表」,在弹出的「创建数据透视表」中点击「确定」按钮。

  将需要拆分的类别拖到「筛选」中,其他的全部拖到「行」里面去。

  比如,这里我需要按“部门”进行拆分,我将“部门”拖拽到“筛选”中,部门外其他的拖拽到“行”中。

  进入「设计」-「布局」,在「报表布局」中选择「以表格形式显示」和「重复所有项目标签」。

  在「分类汇总」中选择「不显示分类汇总」。

  在「总计」中选择「对行和列禁用」。

  4、生成多个工作表

  选中表格后,进入「分析」-「数据透视表」-「选项」-「显示报表筛选页」-「确定」,此时,就已经将表格分类并生成到各自的工作表中了。

  5、透视表转成普通表

  我们用Shift+左键,选中所有的工作表,来统一设置一下。点击左上角的三角形,即可选中整张表格,先复制内容,然后粘贴为“值”,最后再删除开头两行。此时,透视表就变成了普通的表格。

}

我要回帖

更多关于 excel固定第一个工作表 的文章

更多推荐

版权声明:文章内容来源于网络,版权归原作者所有,如有侵权请点击这里与我们联系,我们将及时删除。

点击添加站长微信