fx函数.docx

fx函数.docx

《fx函数.docx》由会员分享，可在线阅读，更多相关《fx函数.docx（8页珍藏版）》请在冰豆网上搜索。

fx函数

整理：

贾汝亮

一、给数据排序函数

Rank函数：

为指定的单元格数值区域排序。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=rank（a1，$a$1:

$a$10,0）将升序排列。

B1=rank（a1，$a$1:

$a$10,1）将降序排列。

（一般最后可以省略，保持默认）

二、统计单元格数目函数

1、countif函数：

计算某一区域内满足条件的单元格数目。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=countif（a1:

a10，“>=85”）将显示大于85分的单元格数目。

2、count函数：

计算某一区域内包含数值的单元格数目。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=count（a1:

a10）将显示a1:

a10的单元格数目。

（注意：

可以多选数值区域）

3、counta函数：

计算某一区域内包含非空单元格数目。

用法：

1、在a1:

a10单元格输入0-100、其它文本的数值。

2、B1=counta（a1:

a10）将显示a1:

a10的单元格数目。

（注意：

可以多选数值区域。

可以是文本和数值）

4、countblank函数：

计算某一区域内包含空单元格数目。

用法：

1、在a1:

a10单元格输入0-100的数值，包含空单元格。

2、B1=countblank（a1:

a10）将显示a1:

a10的空单元格数目。

区别：

count函数：

数值

counta函数：

数值、文本

countblank函数：

空单元格

三、文本合并函数

Concatenate函数：

将多个字符串合并成一个。

第一种用法：

1、a1:

a10单元格输入字符串。

B1=Concatenate（a1，a2，……，a10）。

2、显示a1:

a10的内容。

第二种用法：

使用&符号连接

四、提取单元格字符函数

1、len函数：

返回文本字符串的字符个数。

用法：

1、A1单元格输入“中华人民共和国”。

2、B1=len（a1）。

显示“7”。

即：

“中华人民共和国”是七个字符组成。

2、left函数：

从一个字符串的第一个字符开始返回指定个数的字符。

用法：

1、A1单元格输入“中华人民共和国”。

2、B1=left（a1，5）。

显示“共”。

即：

从第一个字符开始的第五个字符。

3、mid函数：

从文本字符中指定的起始位置起，返回指定长度的字符。

用法：

1、A1单元格输入“中华人民共和国”。

2、B1=left（a1，3，2）。

显示“人民”。

即：

从第三个字符开始的二个字符。

4、right函数：

从一个字符串的最后一个字符开始返回指定个数的字符。

用法：

1、A1单元格输入“中华人民共和国”。

2、B1=right（a1，3）。

显示“共和国”。

即：

从最后一个字符开始的第三个字符。

注意：

如果只是单纯的对某一列数据进行提取某个字段，可以用分列的方法更简单，注意最后一步的“不导入此列”的用法。

五、最大值、最小值函数

1、max函数：

返回某一区域数值的最大值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=max（a1:

a10）。

则显示a1:

a10中数值的最大数。

2、Large函数：

返回某一区域数值第k个最大值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=Large（a1:

a10，3）。

则显示a1:

a10中数值的第三个最大数。

3、Min函数：

返回某一区域数值的最小值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=Min（a1:

a10）。

则显示a1:

a10中数值的最小数。

4、Small函数：

返回某一区域数值第k个最小值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=Small（a1:

a10，3）。

则显示a1:

a10中数值的第三个最小数。

六、求平均值函数

1、Average函数：

返回某一区域数值的平均值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=Average（a1:

a10）。

则显示a1:

a10中数值的平均值。

2、Averageif函数：

计算某一区域内满足条件的单元格数值平均值。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=Averageif（a1:

a10，“>=85”）将显示大于85分的单元格数值的平均值。

七、求和函数

1、sum函数：

对某一区域数值求和。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=sum（a1:

a10）。

则显示a1:

a10中数值的和。

2、sumif函数：

求某一区域内满足条件的单元格数值的和。

用法：

1、在a1:

a10单元格输入0-100的数值。

2、B1=sumif（a1:

a10，“>=85”）将显示大于85分的单元格数值的和。

八、日期函数

1、now函数：

返回当前的日期和时间。

用法：

a1=now（）。

显示当前的日期和时间。

2、today函数：

返回当前的日期

用法：

a1=today（）。

显示当前的日期。

九、if函数

If函数的语法：

如果符合条件就显示“TRUE（真）”反之就显示“FALSE（假）”。

并且可以嵌套。

（1）1、在a1:

a10单元格输入0-100的数值。

2、B1=if（a1>=80,”优秀”,”良好”）。

意思就是：

如果a1>=80,成立就显示优秀。

不成立就显示良好。

（2）1、在a1:

a10单元格输入0-100的数值。

B1=if（a1>=90,”优秀”，if（a1>=80,”良好”,if（a1>=60,”及格”,”不及格”）））。

意思就是：

如果a1>=90,成立就显示优秀。

如果a1>=80,成立就显示良好。

如果a1>=60,成立就显示及格。

不成立就显示不及格。

十、四舍五入函数

1、Round函数：

进行四舍五入。

A1:

a10输入带不同位数小数的数值。

B1=Round（a1，2）显示a1单元格按四舍五入保留2位小数。

2、Roundown函数：

向下舍入数字。

A1:

a10输入带不同位数小数的数值。

B1=Roundown（a1，2）显示a1单元格向下舍入数字保留2位小数。

2、Roundup函数：

向上舍入数字。

A1:

a10输入带不同位数小数的数值。

B1=Roundup（a1，2）显示a1单元格向上舍入数字保留2位小数。

十一、将一个字符串的部分字符用另一个字符替换

Replace函数：

将一个字符串的部分字符用另一个字符替换。

1、a1:

a10单元格输入数值或文本。

2、B1=Replace（a1，2，3，“字符”）。

B1单元格显示：

a1单元格从第二个字符开始的三个字符被“字符”代替。

十二、指定重复次数的文本函数

Rept函数：

指定重复次数的文本函数。

1、a1:

a10单元格输入数值或文本。

2、B1=Rept（a1，3）b1单元格显示a1单元格内容3次。

十三、指定重复次数的文本函数举例应用

1、a1:

a10单元格输入数值。

2、D1输入“*”（拖动时注意$d$1）。

B1=if（a1>=90,rept（d1,4）,if（a1>=80,rept（d1,3）,if（a1>=60,rept（d1,2）,rept（d1,1））））。

含义：

如果a1单元格数值大于等于90，就重复4次“*”。

以此类推。

十四、将部分字符隐藏的应用举例

1、a1:

a10单元格输入数值（18位身份证号）。

2、D1输入“*”（拖动时注意$d$1）。

3、B1=Replace（a1，4，4，rept（d1,4））

b1单元格显示：

a1单元格中的数值将从第四个字符开始的四个字符将用4个“*”替代。

十五、去最高分、最低分求平均值函数

Trimmean函数：

去最高分、最低分求平均值函数。

第一种用法：

1、a1:

a8输入8个数值。

A9单元格输入：

=Trimmean（a1:

a8,0.5）,就计算从头去两个、从尾去两个的数值的平均值。

0.5的含义：

8个数值×0.5=4。

4就是从头2个，从尾去两个。

第二种用法：

另外用max（最大值）函数、Large（第k个最大值）函数、Min（最小值）函数、Small（第k个最小值）函数来求解也可以。

比较第一种方法麻烦些，但是好理解。

十六、求两个不同工作表的相同部分公式

第一种方法：

准备工作1、第一个工作表的名称为“表一”2、在a列中输入部分字符。

3、第二个工作表的名称为“表二”4、a列中输入部分字符。

两表中的a列有相同的字符。

怎样求出相同的字符呢？

方法：

首先，任选一个工作表（一般选字符多的工作表）。

比如选择“表二”的b1单元格。

输入：

=if（countif（表1!

A:

A,A2）>0,"相同",""）。

拖动向下填充，两表中相同的字符显示“相同”。

利用筛选选择表二的“相同”的单元格。

复制到表一，利用高级筛选，把表一中的数据筛选出来。

然后通过自定义序列，按指定的顺序排列出来。

公式说明：

A:

A是指的表一的列，也可以写成：

$A$2:

$A$20表示表一A列中A2：

A20单元格的绝对引用。

A2表示表二的A列中的第二个单元格。

第二种方法：

利用高级筛选。

1、先把表一（或表二）复制到表二中（注意：

表头要一致）。

2、执行高级筛选。

3、列表区域：

表二内容。

条件区域：

被复制到表二中的表一内容。

这样就把两表中相同的部分筛选出来了。

再进一步处理就可以了。

十七、几个实用技巧

1、如何把1、2、3变成大写数字。

单元格格式-数字-特殊

2、ctrl+;插入当前日期。

ctrl+shift+;插入当前日期和时间。

3、Ait+41420“√”Ait+41409“×”Ait+178“m²”

Ait+179“m³”

4.快速选择当前正在编辑的单元格范围：

“ctrl+shift+*”

5、excel2010中调出数据透视表向导的方法：

ait+d，然后按p键

十八、利用数据透视表快速算及格率、优秀率、平均分的方法

A

B

C

D

E

F

G

H

I

1

学校

班级

姓名

语文

数学

语文优秀

语文及格

数学优秀

数学及格

总人数

2

a

1

a

85

95

1

3

b

1

b

71

46

1

4

2

c

36

68

1

按上面表格进行设计，在e2单元格输入：

=if（c2>=80,1,0）

在f2单元格输入：

=if（c2>=60,1,0）。

顺序依次类推，总人数填充为一（一个人）。

设置完毕插入数据透视表。

语文数学设置字段平均值，语文数学优秀及格、总人数设置字段求和（必须是求和，这样就把优秀人数统计出来）。

然后再插入计算项：

（语文优秀/总人数）或复制出数据进行后期操作也可。

公式：

=if（c2>=80,1,0）的含义：

大于等于80分的显示为1，其余显示为0。

（这里满足大于等于80分的必须设计为1，要么后面数据透视表的求和就不准了）。

其它公式类推。

十九、AND函数

含义：

满足条件一、条件二、条件三……显示为“真”，否则为“假”

应用举例：

比如我要统计语文成绩大于60，小于80的人数。

B列为语文成绩。

在c2单元格输入：

=if（and（b2>60,b2<80）,1,0）。

含义：

如果d2单元格大于60或小于80的就显示为1，其余显示为0。

二十、利用LOOKUP函数统计分数段

1、在a1:

a10单元格输入0-100的数值。

2、在c1：

c4中输入：

0、60、80、90。

3、在d1:

d4输入：

不及格、及格、良好、优秀。

4、在b1单元格输入=lookup（a1,c1:

d4）拖动填充柄。

注意：

c1:

d4要绝对引用。

这个公式的含义：

大于等于0小于60，不及格。

大于等于60小于80，及格。

大于等于80小于90，良好。

大于等于90，优秀。

二十一、快速求出各分数段函数：

FREQUENCY

准备工作：

1、a1:

a10单元格输入姓名，2、b1:

b10单元格输入0-100的数值。

3、A11输入：

60以下A12输入：

60-69分A13输入：

70-79分A14输入：

80-89分A15输入：

90-100分（各个分数段）。

4、在c1-c4单元格输入：

59、69、79、89

用法：

1、选中b11-b15（必须全部选中）。

2、插入函数FREQUENCY。

3、第一个参数：

b1:

b10。

第二个参数：

c1：

c4（要绝对引用）。

4、按shift+ctrl+enter数组公式确认。

说明：

c1单元格59说明：

小于等于59的分数（也就是60分以下）。

C2单元格69说明：

大于等于60，小于等于69（也就是60-69分）。

C3单元格79说明：

大于等于70，小于等于79（也就是70-79分）。

C4单元格89说明：

大于等于80，小于等于89（也就是80-89分）。

余下就是大于等于90分以上的。