有这么一种情况,相信有很多小伙伴们都有遇到:需要比较两个工作表或两个工作簿微妙的改动之处,该如何快速且便捷地找出不同之处?
以同一个工作簿中到两个工作表为例(比较两个工作簿可以将其复制到同一个工作簿中比较),具体步骤如下:
TRUE
,如果不对应相同,即有变动之处则显示 FALSE
,这个显示可以自己定义。=IF(Sheet1!A1:F12=Sheet2!A1:F12,TRUE,FALSE)
【Ctrl+Shift+Enter】
组合键。
具体操作如下:
这里使用了Excel中的数组,若需要继续深入了解Excel数组相关知识,可继续阅读下文。
数组就是指一组数,这些数可以是纵向的一组,也可以是横向的一组,也可以是二维数组,数组的表示一般为"{}"所包括。
在Excel中,数组可以理解为多个单元格的集合,比如A1:A3
,A2:B5
等待。
通常数组分为一维数组和二维数组。
A2:A5
(单列),B2:C2
(单行)。B3:C5
。数组的特点是:
1. 所有的数组,都能在一定连续单元格区域表示出来。
2. 数组的尺寸由构成数组元素的个数来确定。
3. 同一维度的数组,要注意尺寸特征,同一数组中,不存在尺寸不同的行或列。
4. 在编辑栏可以用F9键查看数组的运算结果。
数组可以进行加减乘除四种通常的运算,无论哪种运算,输入数组公式后需要按下 【Ctrl+Shift+Enter】
组合键。
所以我们可以得到以下的几种计算方式。以乘法为例。
维度:行方向或列方向,如
A2:A8
元素:数组中的每一个值,如A2:A8
里面的值,元素的个数为7
同向一维数据间的批量运算。即一维横向数组与一维横向数组运算,或者是一维纵向数组与一维纵向数组运算。其运算规则是相同的,即2个数组对应位置的数据分别进行运算,生成一个大小和方向不变的新数组。
如计算 A2:A5
与 B2:B5
之间两两相乘,结果保存在 C2:C5
,其是列方向上的一维数组。
即不同方向的一维数据批量运算。A2:A3
列方向上一维数组与 B2:C3
行方向上的一维数组相乘做计算,得到2行2列的多维数组。
计算过程:先用第一个数组的第一元素与第二个数组的所有元素相乘,再用第一个数组的第二个元素与第二个数组的所有元素相乘,以此类推。
此运算的前提是,二维数组要与一维数据同方向上的大小相同。
A2:C2
一维数组乘以A3:C4
二维数组,最终得到跟原始二维数组一样的2行3列二维数组。
计算方式以一维数组的每一个元素与二维数组相乘。
如果用列方向的数组计算,此时一维数组必须是两个元素。
这种运算要求2个数组大小相同,运算规则很简单,数组对应位置的数据相运算即可,生成一个大小不变的新的二维数组。
如用 A2:C3
二维数组与A4:C5
二维数组相乘,得到一个相同维度和元素的二维数组。
需要完成如下功能,大于零的判断,即大于零返回自身,否则为0。
IF(logical_test,[value_if_true],[value_if_false])
IF(A3>0,A3,0)
{=(A3:A7>0)*A3:A7}
方法,{}
包围,同一行用逗号分隔,不同行用分号分隔。
输入公式:=SUM(B3:B42*C3:C42),按【Ctrl+Shift+Enter】结束公式输入。 选择B3:B42时,运用快捷键 【Ctrl+Shift+?】快速选择。
使用两种方法,可以看出数组方法更加高效。
计算步骤
输入公式:
=SUM((A3:A18>0)*A3:A18)
此处需要用到 OR
、LEN
函数及数组。巧用数组与函数结合,批量处理,节约时间成本。
在数组中,+
号代表OR
;*
号代表AND
;
输入公式:
=LEN(B3:B12)=15
=LEN(B3:B12)=18
=(LEN(B3:B12)=15)+(LEN(B3:B12)=18)
统计一车间男职工工资总和。
输入公式:
=SUM((B3:B11="一车间")*(C3:C11="男")*D3:D11)
判断条件:男士:60岁及以上;女式:55岁及以上。
输入公式:
=IF((B3:B12="男")*(C3:C12>=60)+(B3:B12="女")*(C3:C12>=55),"退休","否")
判断条件:4个裁判都评判通过才通过。
=AND(B3="通过",C3="通过",D3="通过",E3="通过")
=AND(B3:E3="通过")
SUMPRODUCT 在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。 SUMPRODUCT(array1, [array2], [array3], ...)
数组公式:
=SUMPRODUCT(($A$3:$A$13=F3)*$B$3:$D$13)
数组公式:
=SUMPRODUCT((B3:B12>100)*B3:B12)
=SUMPRODUCT(((B3:B12<100)+(B3:B12>110))*B3:B12)
FREQUENCY, 分段求频率 计算数值在某个区域内的出现频次,然后返回一个垂直数组。 FREQUENCY(data_array, bins_array) 说明:
bins_array
中的元素多一个。数组公式:
=FREQUENCY(C3:C123,F4:F6)