几个好用的excel函数

Function REWARD(sales, years) As Double

　　Const r1 As Double = 0.04

　　Const r2 As Double = 0.07

　　Const r3 As Double = 0.1

　　Const r4 As Double = 0.13

　　Const r5 As Double = 0.16

　　Const r6 As Double = 0.19

 

　　Select Case sales

　　Case Is <= 2800

　　REWARD = sales * (r1 + years / 200)

　　Case Is <= 7900

　　REWARD = sales * (r2 + years / 200)

　　Case Is <= 15000

　　REWARD = sales * (r3 + years / 200)

　　Case Is <= 30000

　　REWARD = sales * (r4 + years / 200)

　　Case Is <= 50000

　　REWARD = sales * (r5 + years / 200)

　　Case Is > 50000

　　REWARD = sales * (r6 + years / 200)

　　End Select

　　End Function

 

 

 

　Function TAX(salary)

　　Const r1 As Double = 0.05

　　Const r2 As Double = 0.08

　　Const r3 As Double = 0.2

　　Select Case salary

　　Case Is <= 800

　　TAX = 0

　　Case Is <= 1500

　　TAX = (salary - 800) * r1

　　Case Is <= 2000

　　TAX = (1500 - 800) * r1 + (salary - 1500) * r2

　　Case Is > 2000

　　TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3

　　End Select

　　End Function

 

 

 

Function daodu(dy)

　　 a = Len(dy)

　　 For i = a To 1 Step -1

　　    b = Mid(dy, i, 1)

　　    daodu = daodu & b

　　 Next i

　　End Function

 

 

含数字“1” 公式是 =SUMPRODUCT(ISNUMBER(FIND("1",A1:A10))*1)

含数字“2” 公式是 =SUMPRODUCT(ISNUMBER(FIND("2",A1:A10))*1)

 

给3个公式，计算大于-5且小于5的个数：

=sum(countif(a1:a10,{">-5",">=5"})*{1,-1})

=countif(a1:a10,">-5")-countif(a1:a10,">=5")

=sumproduct((a1:a10>-5)*(a1:a10<5))

 

 

IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"))))

或  IF(B2>89,”优”,IF(B2>79,”良”,IF(B2>69,”中”,IF(B2>59,”及格”,”差”))))

还有一种方法为： IF(B2<60,”F”, IF(B2<=69,"D", IF(B2<=79,”C”, IF(B2<=89,"B","A",))))

或  IF(B2<60,”差”,IF(B2<=69,”及格”,IF(B2<=79,”中”,IF(B2<=89,”良”,”优”))))

 

 

A1:数量10单价200

A2:数量150单价18

A3:数量187单价2000

要求在B列计算出金额

=MID(A1,3,FIND("单",A1)-3)*RIGHT(A1,LEN(A1)-FIND("价",A1))

=MID(A2,3,FIND("单",A2)-3)*RIGHT(A2,LEN(A2)-FIND("价",A2))

=MID(A3,3,FIND("单",A3)-3)*RIGHT(A3,LEN(A3)-FIND("价",A3))

数量10单价200

目的是要找出10与200，并相乘就是要的结果。

MID(A1,3,FIND("单",A1)-3)*RIGHT(A1,LEN(A1)-FIND("价",A1))

分两步进行：

1.找出数量

MID(A1,3,FIND("单",A1)-3)

用MID提取函数，在A1单元格中从第3位开始，提取的长度用FIND计算，FIND("单",A1)意思是查找"单"在第几位，-3 是减去（数量和单）3个字符，结果是5-3就得到了剩下两位就是数量10。

2.找出单价

RIGHT(A1,LEN(A1)-FIND("价",A1)

RIGHT 是从后提取的函数，根据数据的特征，单价都是在单元格最后几位，

LEN(A1) 是计算A1单元格有多少字符，FIND("价",A1) 是查找"价"在第几位，LEN(A1)-FIND("价",A1) 得到的就是最后的单价200。

Function ticheng(linshou, leibie)

If leibie = "1" Then

ticheng = 0.2 * linshou

ElseIf leibie = "2" Then

ticheng = 0.1 * linshou

ElseIf leibie = "3" Then

ticheng = 0.15 * linshou

End If

End Function

 

 

Function giguan(dy)

  a = Left(dy, 6)

  If a = "510626" Then

  giguan = a & "罗江县"

  ElseIf a = "510623" Then

  giguan = a & "中江县"

  ElseIf a = "510184" Then

  giguan = a & "崇州市"

  ElseIf a = "522225" Then

  giguan = a & "思南地区"

  ElseIf a = "510181" Then

  giguan = a & "都江堰"

  ElseIf a = "513124" Then

  giguan = a & "郫县"

  End If

  End Function

 

 

CHOOSE(index_num,value1,value2,...)   返回index-num对应的项。

 

CLEAN(text)   删除文本中不能打印的字符

CONCATENATE (text1,text2,...)     合并

 

EXACT(text1,text2)               比较相同返回真假

 

FIND(find_text,within_text,start_num)      返回起始位置字符数计大小写

FINDB(find_text,within_text,start_num)     返回起始字节数

SEARCH(find_text,within_text,start_num)

SEARCHB(find_text,within_text,start_num)   返回首次出现位置（忽略大小写）

 

FIXED(number,decimals,no_commas) 把数值按decomals舍入并转化为带逗号分隔的文本形式的数值。

 

LEFT(text,num_chars)

LEFTB(text,num_bytes)

RIGHT(text,num_chars)

RIGHTB(text,num_bytes)

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)

 

Len(text)

 

LOWER(text)     全部转换为小写字母

PROPER(text)     第一个字母转化为大写，其它为小写。

UPPER(text)  Text    为需要转换成大写形式的文本。Text 可以为引用或文本字符串

 

REPLACE(old_text,start_num,num_chars,new_text)    使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。

REPLACEB(old_text,start_num,num_bytes,new_text)

SUBSTITUTE(text,old_text,new_text,instance_num)

Text    为需要替换其中字符的文本，或对含有文本的单元格的引用。

Old_text    为需要替换的旧文本。

New_text    用于替换 old_text 的文本。

Instance_num    为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则将用 new_text 替换 Text 中出现的所有 old_text。

 

REPT(text,number_times) Text需要重复显示的文本。Number_times是指定文本重复次数的正数。

 

DOLLAR 或 RMB(number,decimals)    将数值转化为货币形式的数值（美元或人民币）

 

TEXT(value,format_text)

 

TRIM(text)  删除空格

 

VALUE(text)   将代表数字的文本字符串转换成数字，Text 为带引号的文本，或对需要进行文本转换的单元格的引用。

 

用筛选公式里的IF语句，如果A1=花，草，树，则B1=植物。如果A1=牛，马，羊，则B1=动物。

B1=IF(OR(A1="花",A1="草",A1="树"),"植物",IF(OR(A1="牛",A1="马",A1="羊"),"动物",))

=IF(OR(A1={"花","草","树"}),"植物",IF(OR(A1={"牛","马","羊"}),"动物"))

 

IF(B=1,IF(C>15,4.5*15+9*(C-15),4.5*C),IF(C>15,4.2*15+8.4*(C-15),4.2*C))。

=IF(A1>8,9,IF(A1>7,8,IF(A1>6,7,IF(A1>5,6,IF(A1>4,5,IF(A1>3,4,IF(A1>2,3,IF(A1>1,2,1))))))))
=CHOOSE(FIND("1",(A1>9)*1&(A1>8)*1&(A1>7)*1&(A1>6)*1&(A1>5)*1&(A1>4)*1&(A1>3)*1&(A1>2)*1&(A1>1)*1&(A1>0)*1),10,9,8,7,6,5,4,3,2,1)
=CHOOSE(FIND("1",--(A1>9)&--(A1>8)&--(A1>7)&--(A1>6)&--(A1>5)&--(A1>4)&--(A1>3)&--(A1>2)&--(A1>1)&--(A1>0)*1),10,9,8,7,6,5,4,3,2,1)
=CHOOSE(FIND("2",(A1>9)*2&(A1>8)*2&(A1>7)*2&(A1>6)*2&(A1>5)*2&(A1>4)*2&(A1>3)*2&(A1>2)*2&(A1>1)*2&(A1>0)*2),10,9,8,7,6,5,4,3,2,1)

个人所得税公式(共涉及9个税率)
=CHOOSE(FIND("1",(A1>100000)*1&(A1>80000)*1&(A1>60000)*1&(A1>40000)*1&(A1>20000)*1&(A1>5000)*1&(A1>2000)*1&(A1>500)*1&(A1<=500)*1),A1*0.45-15375,A1*0.4-10375,A1*0.35-6375,A1*0.3-3375,A1*0.25-1375,A1*0.2-375,A1*0.15-125,A1*0.1-25,A1*0.05)

=CHOOSE(FIND("1",((A1-2000)>100000)*1&((A1-2000)>80000)*1&((A1-2000)>60000)*1&((A1-2000)>40000)*1&((A1-2000)>20000)*1&((A1-2000)>5000)*1&((A1-2000)>2000)*1&((A1-2000)>500)*1&((A1-2000)<=500)*1),(A1-2000)*0.45-15375,(A1-2000)*0.4-10375,(A1-2000)*0.35-6375,(A1-2000)*0.3-3375,(A1-2000)*0.25-1375,(A1-2000)*0.2-375,(A1-2000)*0.15-125,(A1-2000)*0.1-25,(A1-2000)*0.05)

=HLOOKUP(A1,{条件1,条件2……条件N;结果1,结果2……结果N},2,1)
说明:这一方法的条件数量不爱限制。

=HLOOKUP(3,{1,2,3,4,5,6,7,8,9,10;"A","B","C","D","E","F","G","H","I","J"},2,1)
从序列1-10找寻3，返回C
=HLOOKUP(3,{1,2,3,4,5,6,7,8,9,10;10,9,8,7,6,5,4,3,2,1},2,1)
从序列1-10找寻3，返回8

 

 

大家知道，我们在编辑Excel工作表时，除了可以用其内置的函数处理表中的数据外，还可以根据自己的实际需要，自定义函数来处理表中的数据。

　　如图1所示是一个学生基本情况登记表，学号的编制原则是：前4位是年份，第5位是初、高中代码(1是初中，2是高中)，第6位是年级代码(1分别代表初、高一等)，第7、8位是班级代码，后面是序号。下面，我们以一个具体的实例，自定义一个函数，根据学号来自动填写班级名称(即图中的D列内容)。

　　1.启动Excel 2003(其它版本请仿照操作)，打开相应的工作表。

　　2.执行“工具→宏→Visual Basic编辑器”命令(或者直接按“Alt+F11”组合键)，进入Visual Basic编辑状态(如图1)。

　　

　　图1编辑器

　　3.执行“插入→模块”命令，插入一个新模块。再双击插入的模块，进入模块代码编辑状态。

　　4.将下列代码输入其中：

　　Function bj(xh)
　　Select Case Mid(xh, 5, 1)
　　Case 1
　　bj = "初" && Mid(xh, 6, 3) && "班"
　　Case 2
　　bj = "高" && Mid(xh, 6, 3) && "班"
　　End Select
　　End Function

　　[友情提醒]

　　①上述代码中，“bj”是函数命令，“xh”是变量名称，二者均可以随意修改。

　　②自定义函数结构是(如果有多个变量，请用英文状态下的逗号分隔)：

　　Function函数名称(变量名称)

　　代码

　　End Function

　　③使用下面的代码，可以达到同样的效果：

　　Function bj(xh)
　　If Mid(xh, 5, 1) = 1 Then
　　bj = "初" && Mid(xh, 6, 3) && "班"
　　Else
　　bj = "高" && Mid(xh, 6, 3) && "班"
　　End If
　　End Function

　　5.代码输入完成后，关闭Visual Basic编辑窗口，返回Excel编辑状态。

　　6.选中D2单元格，输入公式：=BJ(A2)，再用“填充柄”将上述公式复制到D列下面的单元格区域中，班级名称即可自动填入相应的单元格中  

EXCEL中MID函数的运用

基本格式：MID(单元格，开始位置，要几位)

应用在从大字段中提取某几个数字。。比如从身份证中提取出生年月。

例如：身份证号码在A1单元格：370285781223591

具体应用为：

在B1单元格中输入：＝MID(A1,7,6)

说明：A1，数据所在单元格

7，出生年月的起始位置

6，我们需要6位到出生年月结束。

如果一些数据要排序，要按哪几位排序就可以提取出来，如学籍号。

 

 

 

 

关于Excel函数MID的用法？？

请各位帮帮忙
实在不明白为什么要用这个函数
以及当中用这个公式的的含义是什么？
A1:数量10单价200
A2:数量150单价18
A3:数量187单价2000

要求在B列计算出金额

A=MID(A1,3,FIND("单",A1)-3)*RIGHT(A1,LEN(A1)-FIND("价",A1))
=MID(A2,3,FIND("单",A2)-3)*RIGHT(A2,LEN(A2)-FIND("价",A2))
=MID(A3,3,FIND("单",A3)-3)*RIGHT(A3,LEN(A3)-FIND("价",A3))

 

数量10单价200
目的是要找出10与200，并相乘就是要的结果。
MID(A1,3,FIND("单",A1)-3)*RIGHT(A1,LEN(A1)-FIND("价",A1))

分两步进行：
1.找出数量
MID(A1,3,FIND("单",A1)-3)
用MID提取函数，在A1单元格中从第3位开始，提取的长度用FIND计算，FIND("单",A1)意思是查找"单"在第几位，-3 是减去（数量和单）3个字符，结果是5-3就得到了剩下两位就是数量10。

2.找出单价
RIGHT(A1,LEN(A1)-FIND("价",A1)
RIGHT 是从后提取的函数，根据数据的特征，单价都是在单元格最后几位，
LEN(A1) 是计算A1单元格有多少字符，FIND("价",A1) 是查找"价"在第几位，LEN(A1)-FIND("价",A1) 得到的就是最后的单价200。