空间访问权限:游客, 头像访问权限:自己
[登录]后可以浏览更多信息
博客正文
【操作】
Excel深度探索

    为帮助朋友实现一个小功能,深度挖据了一下Excel,转载请保留出处。


一、数组

1、数组的填充
      选择A1:A5,在地址栏输入下面的一维数组,按CTRL+SHIFT+回车,将横向自动填充一个数组区域。

 ={1,2,3,4,5}

一维数组.jpg

图1-1 填充一维数组


       选择A1:C5的一块5列3行的区域,输入下面的三维数组, 按CTRL+SHIFT+回车将填充一个多维数组

={1,2,3,4,5; 6,7,8,9,10; 11,12,13,14,15}

 三维数组1.jpg

图1-2 填充多维数组



       如果填充区域和数组大小不匹配,就会出现截取或异常

填充区域小于数组.jpg

图1-3 填充区域小于数组


数组元素少于填充区域.jpg

图1-4 填充区域大于数组

2、数组的计算

     利用公式可以对数据内各元素进行运算,下图表示把三维数组{1,2,3,4,5; 6,7,8,9,10; 11,12,13,14,15}的每个元素乘以5再减1,得到的新数组填充至A1:E5

数组的计算.jpg

图1-5 数组运算范例1

    数组之间也能进行运算。比如 A1:E3 - A5:A7,表示把数组A1:E3和数组A5:A7各对应元素进行减法运算,运算结果生成一个新数组,下图范例:

A9 = A1 - A5       B9 = B1 - B5   ……
A10 = A2 - A6      B10 = B2 - B6   ……
……

三维数组2.jpg

图1-6 求两个三维数组相应元素的差


3、数组的应用——作为函数参数

   SUMPRODUCT(arr1, arr2, arr3 ...)是典型的用数组作为参数进行计算的函数,计算机制是把数组arr1, arr2, arr3 ……各对应元素相乘,再把求得的积相加,我们可以在不需要辅助列的情况下,用它一次性计算出各商品的总价,看下图。

sumproduct.jpg

图1-7 一次求出95折的采购总价

    [Updated 2018-11-22 10:18]看了Excel帮助文件,发现SUM进行数组计算也能得到同样的结果,公式为:

{=SUM(D2:D39*E2:E39*0.95)}

作者隐藏了部分内容,需要[登录]才能查看

   我们知道CountIf可以进行条件统计,但如果有多个条件,CountIf就会力不从心了,但SumProduct加数组运算可以搞定,比如统计各班>=85的人数,其中班级为条件1,>=85为条件2。 看下图,F2单元格的公式为:

=SUMPRODUCT(($A$2:$A$45=$E2)*($C$2:$C$45>=85))

sumproduct2.jpg

图1-8 数组运算加SumProduct拿下CountIf搞不定的东西

  解释:

  •  ($A$2:$A$45=$E2) 是把数组A2:A45(班级列)里的各元素跟E2单元格的值进行逻辑运算,如果相等就返回True(1),不等返回False(0),运算完毕(将在产生一个新数组暂存在内存中)凡是1班的元素都为1,非1班的元素即为0
  •  ($C$2:$C$45>=85) 同上,>=85的元素全部为1,否则为0
  •  ($A$2:$A$45=$E2)*($C$2:$C$45>=85),两个数组的对应元素进行乘法运算,只有为1班且>=85的乘积才为1,一共有4对。
  • SumProduct最后把这些乘积相加,得到4

   怎么样,这样的玩法很巧妙吧,只有把数组运算搞清楚了,才能妙笔生花哦,看不懂的自己返回好好学习数组运算。

作者隐藏了部分内容,需要[登录]才能查看

 

二、Large和Small函数

  这俩函数也能对数组进行运算,Large返回数据集中第 k 个最大值。比如用此函数得到第一名、第二名或第三名的得分,Small相反。既然是数组函数,就应该可以像玩出很多花样来,由于时间关系,暂时没有深入探索。{深度探索备忘2018-11-20}

 

三、数据库函数

1、函数解释

   在微软Excel帮助手册里数据库函数也叫工作簿函数,数据库函数都以D开头,比如:Sum对应DSum、Average对应DAverage、CountA对应DCountA,该类函数的格式为:

D函数(database,field,criteria)

  • database : 数据库,指被选中的数据区,第一行必须是数据的标志项,比如学生成绩表的班级、姓名、语文、数学等。
  • field :字段名,指定函数所使用的数据列,即database 的第一行的标志项
    database.jpg

图3-1 数据库,字段分别为:班级、姓名、语文

  • criteria:条件,一组能表示条件的单元格区域,第一行必须为字段名,第二行为表达式。右图条件.jpg表示班级为1,且姓名为“许某”,语文大于等于80

2、应用

  下面举例实现跟SumProduct类似的功能、E11单元格的公式为

=DCOUNT(A$1:C$45, "语文", E7:G8)

   注意第1个参数“A$1:C$45”必须包含标志项,即A1:C1区域。第3个参数其中的F8使用了通配符“?”以实现模糊查询,这是一个突发奇想的深度挖掘,探索结果完全符合本人预期,嘿嘿^_^。得意一下。

    由于数据库函数是一大类,用起来就很High了,下图中F11单元格的计算1班的平均分就用到了DAverage。

数据库函数的应用.jpg

图3-3 DCount 和 DAverage

 

五、subTotal分类汇总函数

1、用途

    这个函数能实现Sum、Average、Max等相同的功能,不同之处是在执行筛选时,会重新计算筛选出来的数据?赐5-1

subtotal1.jpg

图5-1 SubTotal和Sum

   上图D42 和 E42公式分别为:

=SUBTOTAL(9, D2:D41)

=SUM(D2:D41)

   两个公式都能计算语文的总分,但是在执行筛选后,D42单元格进行了重新计算,这就是SubTotal的灵活性。

subtotal2.jpg

图5-2 SubTotal筛选后重新进行了计算

2、公式解释

   公式 SubTotal(funNum, ref1, ref2, ...)

  • funNum : 函数名代号,以数字表示,见附表5-1
  • ref1 : 引用的区域

函数名

函数名

函数

包含隐藏值

忽略隐藏值

1

101

 AVERAGE

2

102

 COUNT

3

103

 COUNTA

4

104

 MAX

5

105

 MIN

6

106

 PRODUCT

7

107

 STDEV

8

108

 STDEVP

9

109

 SUM

10

110

 VAR

11

111

 VARP

   表5-1  函数名代码表

   上表中函数代号9和109都表示求和,但109还可以忽略隐藏行的数据,非常实用。值得注意的是,这里的忽略隐藏数据对列隐藏无效。!另:用数字代表函数名是不是为用活公式又添加一把利器呢。



 

未完待续 2018-11-21 10:59

四、Cell函数、Offset函数、CHOOSE函数、INDEX、MATCH、KURT、FREQUENCY,SubTotal

栏目划分
WEB应用程序 (29)
桌面应用程序 (9)
Windows操作系统 (12)
xNix操作系统 (5)
数据库技术 (3)
多媒体技术 (1)
硬件技术 (4)
办公软件 (2)
以史为鉴 (2)
教学反思1 (1)
奇闻趣事 (5)
其它 (11)
教学反思 (3)
电子技术 (2)
中学生科技 (3)
 
主人推荐
音象定位和音箱喇叭的排步
我国高层意向已定:制造大飞机
“运十”悲剧
日尔曼人
细说百越族
宇宙深处的秘密——星云
[强烈推荐]中华56民族FLASH完整珍藏版
一组漂亮的植物
  
湄潭县| 巢湖市| 喀喇沁旗| 博客| 新安县| 育儿| 资阳市| 鄂托克前旗| 陵水| 黄大仙区| 永泰县| 华安县| 漳浦县| 泗水县| 阳信县| 南丰县| 广德县| 大庆市| 和龙市| 拉萨市| 綦江县| 汉川市| 得荣县| 安福县| 潞城市| 建水县| 清丰县| 巴马| 景德镇市| 彩票| 遂川县| 温宿县| 界首市| 改则县| 抚松县| 新龙县| 阿克| 宁武县| 施秉县| 抚顺市| 鄱阳县| http:// http:// http:// http:// http:// http://