用下面的例子来解释会更加清楚,如下图,有两个表,表1里只有姓名,但没有对应的部门情况统计,而表2就是一个完整的数据源,既包含姓名又包含所在部门。我们想要实现的结果就是利用表2的数据,将表1的部门情况匹配好,换句话说,就是在表2内查找姓名,然后把部门粘贴到表1内——正所谓“查找和粘贴”也。
如果你是计算机,面对这样问题,应该如何解决呢?
上述3件事其实就是vlookup
函数的4个参数中的前3个参数了。最终的函数如下图。
-
- 第一个参数,找谁?C5,也就是肖静这个人。
-
- 第二个参数,在哪里找?E列及F列,也就是表2的区域。这里需要注意的有2点,首先,选择区域的时候,要查找的姓名一定要在左边第一列;其次,建议选择区域的时候选择整列,这样可以避免因相对引用导致匹配出错。
-
- 第三个参数,找到以后粘贴什么?当然是粘贴姓名右侧单元格的内容呀,但如何表示右侧这个单元格呢?这里很巧妙的利用列数来进行指示:第二个参数选择的区域是有两列的,查找的姓名在左边第1列,部门在第2列,我们想要粘贴部门,也就是粘贴第2列的内容,于是,第三个参数就是这个列数——2。
-
- 第四个参数,只要记住,填写0,即可实现精确匹配。
-
平常生活中,我们想将一列数字排序,大家最易想到的是用排序按钮,然后下一列用自动填充功能填充,但是当遇到两个数据相同的时候,这样填充出来的数据就不能很好的显示排名情况。如下图所示,两个105相同的分数,但是自动填充显示的名次却不一样。我们还得对这些相同分数的排名进行手动修改。
-
如何让EXCEL帮助我们,不用排序快捷键,而且能去除重名次呢。首先我们不用将数据排序,回到最初数据形式。如下图所示:
-
下面我们需要运用RANK函数。在B2单元格中输入以下内容:
=RANK(A2,$A$2:$A$24)
。其中A2是需要确定位次的数据,$A$2:$A$24
表示数据范围,括号里的内容即表示A2单元格数据在A2:A24这个数据区域的排名情况,公式计算结果将被显示在B2单元格里。下图1为公式,下图2为计算结果的显示。 -
然后选中B2单元格,将鼠标放在右下角,鼠标变成小十字的时候,按下鼠标左键不放,往下拉到B24单元格,这样所有分数的排名就显示出来了。如下图所示:
-
[order]
参数:是表示升序或者降序排名的数字(0或者不写就是从高到低排序,非0就是从低到高排名)。从高到低的意思是,数值越大,名次越靠前,反之亦然。
在输入数据范围的时候,一定要用 $符号,否则排出来的名次会是错误的。
-
该函数由至少三部分参数~
sum_range
参数:指进行求和的单元格或单元格区域criteral_range
:条件区域,通常是指与求和单元格或单元格式处于同一行的条件区域,在求和时,该区域将参与条件的判断criterl[N]
:通常是参与判断的具体一个值,来自于条件区域
据此,为了讲解的需要,特制作如图所示的表格:
-
优秀率的计算方法:成绩达到90分以上的所有成绩的平均值。
因此在优秀率(平均分)计算单元格输入的公式为“=SUMIFS(H2:H5,I2:I5,"是")/2”即可。
-
由于
criteral
是可选择的参数,因此,我们可以使用多个条件。
-
另外,我们也可以扩展该公式的参数,实现更复杂的求和。
例如,想计算三好学生当中得优秀成绩的平均值,则在“三好优秀平均值”单元格输入公式:“=SUMIFS(F2:F5,H2:H5,"优秀",I2:I5,"是")/4”。
SUMIF函数可以对指定范围内符合条件的数进行条件求和,其条件求和功能非常的强大。
-
Range:必需。用于条件计算的单元格区域。
-
Criteria:必需。用于确定对求和单元格的条件,其形式可以是数字、表达式、单元格引用、文本或函数。
-
Sum_range:可选。要求和的实际单元格(如果要对未在range参数中指定的单元格求和)。如果省略sum_range参数,Excel会对在range参数中指定的单元格(即应用条件的单元格)求和。
-
要知道weekday函数的参数,
weekday
函数一共就有二个参数,第一参数是一个日期。就是判断这个日期是星期几。 -
第二个参数是固定好的一系列的数字,对于在中国的人来说,我们第二个参数一般设为2.
-
下面,如下图,具体给你讲述一下weekday函数的使用方法。求下面日期为星期几。
-
首先,在G1单元格输入WEEKDAY函数,因为是求星期几,然后输入WEEKDAY函数的第一参数,这个参数就是求星期几的日期。就是F1.然后输入第二参数,就是2.
-
点击回车键,即可得出要求的单元格是星期天。点击填充键,完成区域所有的日期的星期几的提取。
- 显示星期几——
aaaa