当前位置:文档之家› 采购EXCEL公式运用

采购EXCEL公式运用

采购EXCEL公式运用
采购EXCEL公式运用

采购EXCEL公式运用

1 FIND(find_text,within_text,start_num) 返回值为:所要查找的字符串的第一个位置start_num eg:FIND("1",F10,0)=#Value 如果字符串未找到则返回#Value FIND("0",F10,1)=#Value 查找区分大小写eg:FIND("m","Miriam McGovern")=6

2 FINDB(find_text,within_text,start_num) 按字符来查找eg:FINDB("京","东京都") 等于 3

3 SEARCH(find_text,within_text,start_num) SEARCHB(find_text,within_text,start_num) 查找时不区分字符大小写的!

4 MATCH(lookup_value,lookup_array,match_type) 函数MATCH 返回lookup_array 中目标值的位置,而不是数值本身。例如,MATCH("b",{"a","b","c"},0) 返回2,即"b" 在数组{"a","b","c"} 中的相应位置。查找文本值时,函数MATCH 不区分大小写字母。如果函数MATCH 查找不成功,则返回错误值#N/A。如果match_type 为0 且lookup_value 为文本,lookup_value 可以包含通配符、星号(*)和问号(?)。星号可以匹配任何字符序列;问号可以匹配单个字符。

5 补充说明MATCH函数Lookup_value 为需要在Look_array 中查找的数值。例如,如果要在电话簿中查找某人的电话号码,则应该将姓名作为查找值,但实际上需要的是电话号码。Lookup_value 可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。Lookup_array 可能包含所要查找的数值的连续单元格区域。Lookup_array 可以为数组或数组引用。Match_type 为数字-1、0 或1。Match-type 指明Microsoft Excel 如何在lookup_array 中查找lookup_value。如果match_type 为1,函数MATCH 查找小于或等于lookup_value 的最大数值。Lookup_array 必须按升序排列:...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。如果match_type 为0,函数MATCH 查找等于lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。如果match_type 为-1,函数MATCH 查找大于或等于lookup_value 的最小数值。Lookup_array 必须按降序排列:TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等。如果省略match_type,则假设为1。

6 数组之间的查找!返回值为数值HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

7 较为熟悉的取字符串函数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)

8 CODE(text) Text 为需要得到其第一个字符代码的文本。eg: CODE("A") 等于65 CODE("Alphabet") 等于65

9 YEN(number,decimals) Number 为要转换成文本的数字,或对包含数字的单元格的引用,或结果为数字的公式。Decimals 为数字四舍五入的截止位。如果值为正数,则小数点右边的位将四舍五入。如果该值为负数,则小数点左边的位将四舍五入。如果忽略此参数,则假定为0,且不对数字进行四舍五入。

10 VALUE(text) Text 为带引号的文本,或对需要进行文本转换的单元格的引用。Text 可以是Microsoft Excel 中可识别的任意常数、日期或时间格式。如果Text 不为这些格式,则函数VALUE 返回错误值#VALUE!。11 TEXT(value,fromat_text) Value 为数值、计算结果为数值的公式,或对数值单元格的引用。fromat_text 所要选用的文本型数字格式。即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的格式。fromat_text 不能包含星号(*),也不能是常规型。eg: TEXT(2.715, "¥0.00") 等于"¥2.72" TEXT("4/15/91", "mmmm dd, yyyy") 等于"April 15, 1991"

12 T(value) Value 为需要进行测试的值。如果value 本身就是文本,或是对文本单元格的引用,函数T 将返回value ;如果没有引用文本,则返回""(空文本)。

13 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。

14 PHONETIC(reference) Reference 为文本串或对单个单元格或包含furigana 文本串的单元格区域的引用。如果reference 为单元格区域,则返回区域左上角单元格中的furigana 文本串。如果reference 为不相邻单元格的区域,将返回#N/A 错误值。说明:为检验!

15 PROPER(text) Text 为需要进行转换的字符串数据、包括在一组双引号中的文字串、返回文本值的公式或是对包含文本的单元格的引用。16 REPT(text,number_times) Text 需要重复显示的文本。Number_times 需要进行重复的次数,为正数,如果number_times 为0,函数REPT 返回""(空文本);如果number_times 不为整数,它将被截尾取整。函数REPT 的结果不能多于255 个字符。

17 FIXED(number,decimals,no_commas) Number 要进行四舍五入并转换成文字串的数。Decimals 为一数值,用以指定小数点右边的小数位数。No_commas 为一逻辑值。如果其值为TRUE,则函数FIXED 返回的文字不含逗号;如果no_commas 的值等于FALSE 或省略,则返回的文字中包含逗号。在Microsoft Excel 中,Numbers 的最大有效位数不能超过15 位,但decimals 可达到127。如果decimals 为负数,则Number 进行四舍五入处理的基准点将从小数点向左数起。如果省略decimals,则假设它为2。

18 EXACT(text1,text2) 比较字符串,区分大小写

19 依照货币格式将小数四舍五入到指定的位数并转换成文字DOLLAR 或RMB(number,decimals) Number 为数字、包含数字的单元格引用,或是计算结果为数字的公式。Decimals 为十进制数的小数位数。如果Decimals 为负数,则参数number 从小数点往左按相应位数

取整。如果省略Decimals,则假设其值为2 。

20 CLEAN(text) Text 要从中删除不能打印字符的任何工作表信息。

21 CONCATENATE (text1,text2,...) Text1, text2, ... 为1 到30 个将要合并成单个文本项的文本项。这些文本项可以为文字串、数字或对单个单元格的引用

22 ASC(text) Text 为文本或对包含要更改文本的单元格的引用。如果文本中不包含任何全角英文字母,则文本不会被更改

23 JIS(text) Text 为文本或对包含要更改文本的单元格的引用。如果文本中不包含任何半角英文字母或片假名,则文本不会更改。

24 REPLACE(old_text,start_num,num_chars,new_text) REPLACEB(old_text,start_num,num_bytes,new_text) Old_text 是要替换其部分字符的文本。Start_num 是要用new_text 替换的old_text 中字符的位置。Num_chars 是希望REPLACE 使用new_text 替换old_text 中字符的个数。Num_bytes 是希望REPLACE 使用new_text 替换old_text 中字节的个数。New_text 是要用于替换old_text 中字符的文本。

25 MEDIAN(number1,number2, ...) Number1, number2,... 是需要找出中位数的1 到30 个数字参数。参数必须是数字、名称、数组或包含数字的引用。Microsoft Excel 会检查每一数组变量或引用中的所有数字

inpExcel常用电子表格公式大全

Excel常用电子表格公式大全 授课:XXX

1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2 ,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表 的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M 列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求 授课:XXX

EXCEL表格计算的部分公式

=(Sheet1!B3/Sheet2!B3) =(Sheet1!C4/Sheet2!C4) =(Sheet1!D5/Sheet2!D5) =(Sheet1!E6/Sheet2!E6) =(Sheet1!F7/Sheet2!F7) =(Sheet1!G8/Sheet2!G8) =(Sheet1!H9/Sheet2!H9) =(Sheet1!I10/Sheet2!I10) =(Sheet1!J11/Sheet2!J11) =(Sheet1!K12/Sheet2!K12) =(Sheet1!L13/Sheet2!L13) =(Sheet1!M14/Sheet2!M14) =(Sheet1!N15/Sheet2!N15) =(Sheet1!O16/Sheet2!O16) =(Sheet1!P17/Sheet2!P17) =(Sheet1!Q18/Sheet2!Q18) =(Sheet1!R19/Sheet2!R19) =(Sheet1!S20/Sheet2!S20) =(Sheet1!T21/Sheet2!T21) =(Sheet1!U22/Sheet2!U22) =(Sheet1!V23/Sheet2!V23) =(Sheet1!W24/Sheet2!W24) =(Sheet1!X25/Sheet2!X25) =(Sheet1!Y26/Sheet2!Y26) =(Sheet1!Z27/Sheet2!Z27) =(Sheet1!AA28/Sheet2!AA28) =(Sheet1!AB29/Sheet2!AB29) =(Sheet1!AC30/Sheet2!AC30) =(Sheet1!AD31/Sheet2!AD31) =(Sheet1!AE32/Sheet2!AE32) =('2004年'!B3+'2005年'!B3+'2006年'!B3+'2007年'!B3+'2008年'!B3+'2009年'!B3+'2010年'!B3+'2011年'!B3+'2012年'!B3+'2013年'!B3+'2014年'!B3) =('2004年'!C4+'2005年'!C4+'2006年'!C4+'2007年'!C4+'2008年'!C4+'2009年'!C4+'2010年'!C4+'2011年'!C4+'2012年'!C4+'2013年'!C4+'2014年'!C4) =('2004年'!D5+'2005年'!D5+'2006年'!D5+'2007年'!D5+'2008年'!D5+'2009年'!D5+'2010年'!D5+'2011年'!D5+'2012年'!D5+'2013年'!D5+'2014年'!D5) =('2004年'!E6+'2005年'!E6+'2006年'!E6+'2007年'!E6+'2008年'!E6+'2009年'!E6+'2010年'!E6+'2011年'!E6+'2012年'!E6+'2013年'!E6+'2014年'!E6) =('2004年'!F7+'2005年'!F7+'2006年'!F7+'2007年'!F7+'2008年'!F7+'2009年'!F7+'2010年'!F7+'2011年'!F7+'2012年'!F7+'2013年'!F7+'2014年'!F7) =('2004年'!G8+'2005年'!G8+'2006年'!G8+'2007年'!G8+'2008年'!G8+'2009年'!G8+'2010年'!G8+'2011年'!G8+'2012年'!G8+'2013年'!G8+'2014年'!G8)

Excel常用电子表格公式大全【汇总篇】

Excel 常用电子表格公式大全【汇总篇】 篇一:Excel 常用电子表格公式汇总 Excel 常用电子表格公式汇总 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的 18 位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1," 男 "," 女 "),IF(MOD(MID(C2,17,1),2)=1," 男 "," 女 ")) 公式内的“C2”代表的是输入身份证号码的单元格。 5、求和: =SUM(K2:K56)——对 K2 到 K56 这一区域进行求和; 6、平均数: =AVERAGE(K2:K56)——对 K2 K56 这一区域求平均数; 7、排名: =RANK(K2,K$2:K$56)——对 55 名学生的成绩进行排名; 8、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 9、 学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设 K 列、 M 列和 N 列分别存放着学生的“平 时总评”、“期中”、“期末”三项成绩; 10、最高分: =MAX(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最高分; 11、最低分: =MIN(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最低分; 12、分数段人数统计: (1) =COUNTIF(K2:K56,"100") ——求 K2 到 K56 区域 100 分的人数;假设把结果存放于 K57 单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求 K2 到 K56 区域 95~99.5 分的人数;假设把结 果存放于 K58 单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58)——求 K2 到 K56 区域 90~94.5 分的人数; 假设把结果存放于 K59 单元格; (4) =COUNTIF(K2:K56,">=85")-SUM(K57:K59)——求 K2 到 K56 区域 85~89.5 分的人数; 假设把结果存放于 K60 单元格; (5) =COUNTIF(K2:K56,">=70")-SUM(K57:K60)——求 K2 到 K56 区域 70~84.5 分的人数; 假设把结果存放于 K61 单元格; (6) =COUNTIF(K2:K56,">=60")-SUM(K57:K61)——求 K2 到 K56 区域 60~69.5 分的人数; 假设把结果存放于 K62 单元格; (7) =COUNTIF(K2:K56," 说明:COUNTIF 函数也可计算某一区域男、女生人数。 如:=COUNTIF(C2:C351,"男") ——求 C2 到 C351 区域(共 350 人)男性人数; 1 / 10

excel中的一些常用计算公式

如何使用电子表格excel中的一些常用计算公式 2010-05-11 23:17:27| 分类:电脑赤脚医生| 标签:|字号大中小订阅 一、年龄计算公式: =IF((YEAR(E2)=2008)*(MONTH(E2)>8),0,2008-YEAR(E2) IF(MONTH(E2)<=8,0,-1)) 其中e2为单元格,2008为当前年份 二、身份证号中提取出生年月= MID(J11, 7, 4) & "年" & MID(J11, 11, 2) & "月" & MID(J11, 13, 2)&"日" 三、班级平均分公式=IF(COUNTIF($C$2:$C$24,J26)=0,"",SUMIF($C$2:$C$24,J26,$D$2)/COUNTIF($C$2:$C$24,J26)) 四、Excel表中身份证号码提取出生年月、性别、年龄的使用技巧 如何从Excel表中的身份证号码中提取出生年月、性别、年龄呢?看了本文就知道了。 方法一: 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。 对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取: 假如身份证号数据在A1单元格,在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式,公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11,2)) 3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考: 说明:公式中的B2是身份证号 根据身份证号码求性别: =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错")) 根据身份证号码求年龄: =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据; 方法二:

电子表格常用函数公式

电子表格常用函数公式 1.去掉最高最低分函数公式: =SUM(所求单元格…注:可选中拖动?)—MAX(所选单元格…注:可选中拖动?)—MIN(所求单元格…注:可选中拖动?) (说明:“SUM”是求和函数,“MAX”表示最大值,“MIN”表示最小值。)2.去掉多个最高分和多个最低分函数公式: =SUM(所求单元格)—large(所求单元格,1)—large(所求单元格,2) —large(所求单元格,3)—small(所求单元格,1) —small(所求单元格,2) —small(所求单元格,3) (说明:数字123分别表示第一大第二大第三大和第一小第二小第三小,依次类推) 3.计数函数公式: count 4.求及格人数函数公式:(”>=60”用英文输入法) =countif(所求单元格,”>=60”) 5.求不及格人数函数公式:(”<60”用英文输入法) =countif(所求单元格,”<60”) 6.求分数段函数公式:(“所求单元格”后的内容用英文输入法) 90以上:=countif(所求单元格,”>=90”) 80——89:=countif(所求单元格,”>=80”)—countif(所求单元格,”<=90”) 70——79:=countif(所求单元格,”>=70”)—countif(所求单元

格,”<=80”) 60——69:=countif(所求单元格,”>=60”)—countif(所求单元格,”<=70”) 50——59:=countif(所求单元格,”>=50”)—countif(所求单元格,”<=60”) 49分以下: =countif(所求单元格,”<=49”) 7.判断函数公式: =if(B2,>=60,”及格”,”不及格”) (说明:“B2”是要判断的目标值,即单元格) 8.数据采集函数公式: =vlookup(A2,成绩统计表,2,FALSE) (说明:“成绩统计表”选中原表拖动,“2”表示采集的列数) 公式是单个或多个函数的结合运用。 AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期

常用excel函数公式大全

常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。

2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。

2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式

1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法

3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。

4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和

Excel电子表格计算公式使用方法技巧总结

Excel电子表格计算公式使用方法25条公式技巧总结 对于Excel表格计算公式的方法实在太多,今天就整理了一个公式大全需要对有需要的朋友有些帮助。 1、两列数据查找相同值对应的位置 =MATCH(B1,A:A,0) 2、已知公式得结果 定义名称=EVALUATE(Sheet1!C1) 已知结果得公式 定义名称=GET.CELL(6,Sheet1!C1) 3、强制换行 用Alt+Enter 4、超过15位数字输入 这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 5、如果隐藏了B列,如果让它显示出来? 选中A到C列,点击右键,取消隐藏 选中A到C列,双击选中任一列宽线或改变任一列宽 将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。 6、EXCEL中行列互换 复制,选择性粘贴,选中转置,确定即可 7、Excel是怎么加密的 (1)、保存时可以的另存为>>右上角的"工具">>常规>>设置 (2)、工具>>选项>>安全性

8、关于COUNTIF COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:A10,">=90") 介于80与90之间需用减,为=COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 9、根据身份证号提取出生日期 (1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF (LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号")) (2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 10、想在SHEET2中完全引用SHEET1输入的数据 工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2 11、一列中不输入重复数字 [数据]--[有效性]--[自定义]--[公式] 输入=COUNTIF(A:A,A1)=1 如果要查找重复输入的数字 条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 12、直接打开一个电子表格文件的时候打不开 “文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上 13、excel下拉菜单的实现 [数据]-[有效性]-[序列] 14、10列数据合计成一列 =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) (1)、根据符合行列两个条件查找对应结果

2020年excel表格计算公式大全、使用技巧

AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符CONCATENATE 合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期 DATEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果,返回相对应条件触发的计算结果。条件计算 INDEX 返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错,该函数返回TRUE,反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始,截取指定数目的字符。截取数据 LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置 MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始,截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算

Excel常用电子表格公式大全

Excel常用电子表格公式大全 2011-04-24 08:53:41 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;

4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;

Excel表格公式使用基本操作及excel表格计算公式大全、使用技巧

Excel 部分函数列表. AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期 DATEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果,返回相对应条件触发的计算结果。条件计算 INDEX 返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错,该函数返回TRUE,反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始,截取指定数目的字符。截取数据LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始,截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算 MOD 求出两数相除的余数。数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。日期计算 NOW 给出当前系统日期和时间。显示日期时间 OR 仅当所有参数值均为逻辑“假(FALSE)”时返回结果逻辑“假(FALSE)”,否则都返回逻辑“真(TRUE)”。逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。数据排序 RIGHT 从一个文本字符串的最后一个字符开始,截取指定数目的字符。字符截取 SUBTOTAL 返回列表或数据库中的分类汇总。分类汇总 SUM 求出一组数值的和。数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式数值文本转换 TODAY 给出系统日期显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。文本数值转换VLOOKUP 在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处

Excel电子表格计算公式使用方法25条公式技巧

常用电子表格公式 一、利用函数COUNTA统计本班的应考人数(总人数) 因为函数COUNTA可以计算出非空单元格的个数,所以我们在利用此函数时,选取本班学生名字所在单元格区域(B3~B12)作为统计对象,就可计算出本班的应考人数(总人数)。 二、利用COUNT、COUNTBLANK和COUNTIF函数分别统计各科参加考试的人数、统计各科缺考人数、统计各科各分数段的人数 我们在输入成绩时,一般情况下,缺考的人相应的科目的单元格为空就可以了,是0分的都输入0。 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3从输入的18位身份证号的出生年月计算公式: 格式为2012/4/21的=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。格式为20120421的=CONCATENATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2)) 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;或 =RANK(E2,$E$2:$E$5),但遇到同分数时排名不连续。 用下面的几个公式都可以获得中国式排名,还是以上图为例,在单元格中输入公式并向下拖: =SUMPRODUCT((D$2:D$19>D2)*(1/(COUNTIF(D$2:D$19,D$2:D $19))))+1 或下面的几个数组公式,公式输入完毕后按Ctrl+Shift+Enter结束: =SUM(IF(D$2:D$19>D2,1/COUNTIF(D$2:D$19,D$2:D$19)))+1 =SUM(--(FREQUENCY(D$2:D$19,IF(D$2:D$19>=D2,D$2:D$19))>0)) =SUM(--IF(D$2:D$19>=D2,MATCH(D$2:D$19,D$2:D$19,)=ROW( $2:$19)-1)) 4、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分: =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分: =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;

电子表格常用函数公式

电子表格常用函数公式 1、自动排序函数: =RANK(第1数坐标,$第1数纵坐标$横坐标:$最后数纵坐标$横坐标,升降序号1降0升) 例如:=RANK(X3,$X$3:$X$155,0) 说明:从X3 到X 155自动排序 2、多位数中间取部分连续数值: =MID(该多位数所在位置坐标,所取多位数的第一个数字的排列位数,所取数值的总个数) 例如:612730************在B4坐标位置,取中间出生年月日,共8位数 =MID(B4,7,8) =19820711 说明:B4指该数据的位置坐标,7指从第7位开始取值,8指一共取8个数字 3、若在所取的数值中间添加其他字样, 例如:612730************在B4坐标位置,取中间出生年、月、日,要求****年**月**日格式 =MID(B4,7,4)&〝年〞&MID(B4,11,2) &〝月〞& MID(B4,13,2) &〝月〞&

=1982年07月11日 说明:B4指该数据的位置坐标,7、11指开始取值的第一位数排序号,4、2指所取数值个数,引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板:首先利用Word制作一个奖状模板并保存为“奖状.doc”,将其中班级、姓名、获奖类别先空出,确保打印输出后的格式与奖状纸相符(如图1所示)。 第二步用Excel建立获奖数据库:在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”,格式如图2所示。 第三步关联数据库与奖状:打开“奖状.doc”,依次选择视图→工具栏→邮件合并,在新出现的工具栏中选择“打开数据源”,并选择“奖状数据.xls”,打开后选择相应的工作簿,默认为sheet1,并按确定。将鼠标定位到需要插入班级的地方,单击“插入域”,在弹出的对话框中选择“班级”,并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印:选择“查看合并数据”,然后用前后箭头就可以浏览合并数据后的效果,选择“合并到新文档”可以生成一个包含所有奖状的Word文档,这时就可以批量打印了。

Excel中公式和函数的应用-教案

《Excel中公式与函数的使用》教案 保山中医药高等专科学校孔春丽 教学内容: 1、单元格引用 2、输入和编辑公式 3、插入和使用常用函数 教学目标: 1、知识目标: 理解excel中公式与函数、相对地址与绝对地址的概念。 2、技能目标: 掌握公式、常用函数以及自动求和按钮的使用,并能运用其解决一些实际问题,提高应用能力。 3、情感目标: 让学生亲身体验excel强大的运算功能,通过系统学习,培养学生科学、严谨的求学态度,和不断探究新知识的欲望。 教学重点与难点: 根据大纲,依据本节课的教学目标,确定本节课的教学重点为: 1、公式的使用 2、excel中相对地址与绝对地址的引用 3、常用函数的使用 教学方法: 1、任务驱动法、设问法 2、自学法、图示说明法、探究法、演示讲解法 3、归纳总结法 教学过程: 一、创设意境,导入新课(2分钟)(讲解法) 导入:excel的主要功能,制作表格,运算和分析数据,可以为决策提供理论依据,前面我们已经讲解了单元格的格式、单元格地址等概念,这一节将一起来完成公式和函数的应用。 新课程:Excel中公式和函数的使用 二、新课教学(总计38分钟)(讲解法、提问法、示范法) 1、单元格的引用(12分钟) 在公式中使用单元格引用的作用是引用一个单元格或一组单元格的内容,这样可以使用工作表不同部分的数据进行所期望的计算。在Excel 2003中,可以使用相对引用,绝对引用及混合引用来表示单元格的位置。所以,在创建的公式中必须正确使用单元格的引用的类型。 (1)单元格的相对引用(5分钟) 下面以计算2门学科的总成绩为例。如下图所示,将D2单元格成为活动单元格,在编辑栏中输入“=A2+B2+C2”,回车后D2单元格将得到图示的结果。

超牛Excel表格公式 excel公式计算

excel公式计算第 1 页共 1 页 Excel表格公式大全, 太强大了 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女代表的是输入身份证号码的单元格。"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”1、求和: =SUM(K2:K56) ----对K2到K56这一区域进行求和; 2、平均数: =AVERAGE(K2:K56) ----对K2 K56这一区域求平均数; 3、排名: =RANK(K2,K$2:K$56) ----对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ----假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分: =MAX(K2:K56) ----求K2到K56区域(55名学生)的最高分; 7、最低分: =MIN(K2:K56) ----求K2到K56区域(55名学生)的最低分; 8、分数段人数统计:(1) =COUNTIF(K2:K56,"100") ----求K2到K56区域100分的人数;假设把结果存放于K57单元格;(2) =COUNTIF(K2:K56,">=95")-K57 ----求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ----求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;(4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ----求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;(5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ----求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;(6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ----求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;(7) =COUNTIF(K2:K56," 第 2 页共 2 页 9、优秀率: =SUM(K57:K60)/55*100 10、及格率: =SUM(K57:K62)/55*100 11、标准差: =STDEV(K2:K56) ----求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化); 12、条件求和: =SUMIF(B2:B56,"男",K2:K56) ----假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和; 13、多条件求和:{=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))} ----假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。 14、根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW( )))/360,0) ------假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。 15、在Word中三个小窍门:①连续输入三个“~”可得一条波浪线。②连续输入三个“-”可得一条直线。连续输入三个“=”可得一条双直线。一、excel 中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如: A1〉1时,C1显示红色 0“条件格式”,条件1设为:公式 =A1=1 2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。条件2设为:公式 =AND(A1>0,A1“字体”->“颜色”,点击绿色后点“确定”。条件3设为:公式 =A1“字体”->“颜色”,点击黄色后点“确定”。 4、三个条件设定好后,点“确定”即出。二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。 第 3 页共 3 页 2、用条件格式避免重复。选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A:$A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"。

Excel常用的函数计算公式大全(一看就会)

EXCEL的常用计算公式大全 一、单组数据加减乘除运算: ①单组数据求加和公式:=(A1+B1) 举例:单元格A1:B1区域依次输入了数据10和5,计算:在C1中输入 =A1+B1 后点击键盘“Enter(确定)”键后,该单元格就自动显示10与5的和15。 ②单组数据求减差公式:=(A1-B1) 举例:在C1中输入 =A1-B1 即求10与5的差值5,电脑操作方法同上; ③单组数据求乘法公式:=(A1*B1) 举例:在C1中输入 =A1*B1 即求10与5的积值50,电脑操作方法同上; ④单组数据求乘法公式:=(A1/B1) 举例:在C1中输入 =A1/B1 即求10与5的商值2,电脑操作方法同上; ⑤其它应用: 在D1中输入 =A1^3 即求5的立方(三次方); 在E1中输入 =B1^(1/3)即求10的立方根 小结:在单元格输入的含等号的运算式,Excel中称之为公式,都是数学里面的基本运算,只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与“/”同、“^”与“乘方”相同,开方作为乘方的逆运算,把乘方中和指数使用成分数就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算,只需要先用鼠标左键点击一下刚才已做好公式的单元格,将鼠标移至该单元格的右下角,带出现十字符号提示时,开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可,即可完成公司自动复制,自动计算。 二、多组数据加减乘除运算: ①多组数据求加和公式:(常用) 举例说明:=SUM(A1:A10),表示同一列纵向从A1到A10的所有数据相加; =SUM(A1:J1),表示不同列横向从A1到J1的所有第一行数据相加; ②多组数据求乘积公式:(较常用) 举例说明:=PRODUCT(A1:J1)表示不同列从A1到J1的所有第一行数据相乘; =PRODUCT(A1:A10)表示同列从A1到A10的所有的该列数据相乘; ③多组数据求相减公式:(很少用) 举例说明:=A1-SUM(A2:A10)表示同一列纵向从A1到A10的所有该列数据相减; =A1-SUM(B1:J1)表示不同列横向从A1到J1的所有第一行数据相减; ④多组数据求除商公式:(极少用) 举例说明:=A1/PRODUCT(B1:J1)表示不同列从A1到J1的所有第一行数据相除; =A1/PRODUCT(A2:A10)表示同列从A1到A10的所有的该列数据相除; 三、其它应用函数代表: ①平均函数 =AVERAGE(:);②最大值函数 =MAX (:);③最小值函数 =MIN (:); ④统计函数 =COUNTIF(:):举例:Countif ( A1:B5,”>60”) 说明:统计分数大于60分的人数,注意,条件要加双引号,在英文状态下输入。

(完整)Excel电子表格常用公式大全,推荐文档

Excel电子表格常用公式大全 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6," />2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女 "),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格"," 不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;

7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2) =COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格; (5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格; (6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格; (7)=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格; 说明:COUNTIF函数也可计算某一区域男、女生人数。 如:=COUNTIF(C2:C351,"男") ——求C2到C351区域(共350人)男性人数; 9、优秀率:=SUM(K57:K60)/55*100 10、及格率:=SUM(K57:K62)/55*100

相关主题
相关文档 最新文档