excel全部公式及实例解释.xls
- 文档编号:230506
- 上传时间:2022-10-07
- 格式:XLS
- 页数:5
- 大小:41.50KB
excel全部公式及实例解释.xls
《excel全部公式及实例解释.xls》由会员分享,可在线阅读,更多相关《excel全部公式及实例解释.xls(5页珍藏版)》请在冰豆网上搜索。
描述原数公式结果解释公式abs绝对值-100.5100.5/=ABS(B2)100100/=ABS(B3)and且TRUEFALSE/=AND(B4,B5)FALSETRUETRUE/=AND(B6,B7)TRUEFALSEFALSE/=AND(B8,B9)FALSEaverage平均值10060/=AVERAGE(B10:
B12)5030clean清除非打印字符#$%44325679开心1#$%44325679开心1/=CLEAN(B13)column列B142/=COLUMN(B14)columns列数A15:
B152/=COLUMNS(A15:
B15)concatenate合并字符1212哈哈/=CONCATENATE(B16,B17)哈哈count数值个数12/=COUNT(B18:
B21)爱5counta非空个数13/=COUNTA(B22:
B25)爱5countblank空格个数11/=COUNTBLANK(B26:
B29)爱5countif满足条件个数12/=COUNTIF(B30:
B33,0.5)爱5exact是否相同爱1FALSE/=EXACT(B34,B35)爱1.爱4TRUE/=EXACT(B36,B37)爱4even偶数舍入2324最近的绝对值较大的偶数/=EVEN(B38)-23-24/=EVEN(B39)1.12/=EVEN(B40)-1.1-2/=EVEN(B41)find查找包含的起始love2(B44,B45,1)1为开始检查的位置/=FIND(B42,B43,1)iloveu爱你3/=FIND(B44,B45,1)1我爱你findb查找包含的起始love2/=FINDB(B46,B47,1)iloveu爱你4/=FINDB(B48,B49,1)1我爱你ABCDE12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849fixed小数位数65468.5355465468.54(B46,2,1)数值小数逗号/=FIXED(B50,2,1)-63546.656-63,546.66/=FIXED(B51,2,0)floor向下舍入23.4567213的倍数/=FLOOR(B52,3)23.456718/=FLOOR(B53,9)ceiling向上舍入24.456727/=CEILING(B54,3)25.456726/=CEILING(B55,2)hlookup横向查找123123哈哈第二行0精确/=HLOOKUP(B56,B58:
F59,2,0)456456啊/=HLOOKUP(B57,B58:
F59,2,0)456456123123789789啊啊哈哈哈哈拜拜拜拜if500正数/=IF(B600,正数,负数)-501负数/=IF(B610,正数,负数)1正数/=IF(B620,正数)-5FALSE/=IF(B630,正数)index单元格值哈哈+123a-b第56行第二列第一列/=INDEX(A:
C,56,2)哈哈+啊hlookup横向查找/=INDEX(A:
B,56,1)indirect单元格值B57456B57单元格值哈哈+啊/=INDIRECT(B66)B58456/=INDIRECT(B67)int向下取整-100.5465-101小于原数的最大整数/=INT(B68)10564.35410564/=INT(B69)10.94410/=INT(B70)-5.1454-6/=INT(B71)isblank是否为空1FALSE/=ISBLANK(B72)家FALSE/=ISBLANK(B73)TRUE/=ISBLANK(B74)iserr是否错误值1FALSE/=ISERR(B75)#N/AFALSE/=ISERR(B76)#DIV/0!
TRUE/=ISERR(B77)iserror1FALSE/=ISERROR(B78)#N/ATRUE/=ISERROR(B79)#DIV/0!
TRUE/=ISERROR(B80)islogical是否逻辑值1FALSE/=ISLOGICAL(B81)TRUETRUE/=ISLOGICAL(B82)FALSETRUE/=ISLOGICAL(B83)isna是否NA1FALSE/=ISNA(B84)#N/ATRUE/=ISNA(B85)#DIV/0!
FALSE/=ISNA(B86)istext是否文本1FALSE/=ISTEXT(B87)#N/AFALSE/=ISTEXT(B88)#DIV/0!
FALSE/=ISTEXT(B89)哈TRUE/=ISTEXT(B90)1+1TRUE/=ISTEXT(B91)isnontext非文本?
32TRUE/=ISNONTEXT(B92)哈FALSE/=ISNONTEXT(B93)1TRUE/=ISNONTEXT(B94)ismunber是否数值1TRUE/=ISNUMBER(B95)4.65465E+14TRUE/=ISNUMBER(B96)3+2FALSE/=ISNUMBER(B97)哈FALSE/=ISNUMBER(B98)isref是否引用B95TRUE/=ISREF(B99)1FALSE/=ISREF
(1)B95+B96FALSE/=ISREF(B99+B100)ABCDE5051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101laege第n大36/=LARGE(B102:
B104,2)69/=LARGE(B102:
B104,1)93/=LARGE(B102:
B104,3)left左取值46465454464/=LEFT(B105,3)j4hnkhjhj4h/=LEFT(B106,3)我爱你545我爱你/=LEFT(B107,3)爱454jhj爱45/=LEFT(B108,3)leftb左取值46465454464/=LEFTB(B109,3)j4hnkhjhj4h/=LEFTB(B110,3)我爱你545我/=LEFTB(B111,3)爱454jhj爱4/=LEFTB(B112,3)len长度4643/=LEN(B113)j4h3/=LEN(B114)爱你53/=LEN(B115)爱4544/=LEN(B116)lenb长度4643/=LENB(B117)j4h3/=LENB(B118)爱你55/=LENB(B119)爱4545/=LENB(B120)lookup非精确查找我爱你hh/=LOOKUP(B121,B122:
B123,C122:
C123)我爱你hhjnjklhjh我爱你hh/=LOOKUP(B124,B125:
C126)我爱你hhjnjklhjhlower小写1我atTA1我atta/=LOWER(B127)match返回位置43返回符合特定值特定顺序的项在数组中的相对位置/=MATCH(B128,B129:
D129,0)234max最大值5698/=MAX(B130:
B132)4099/=MAX(B131:
B133,99)989/=MAX(3,4,9)mida爱12jh哈1爱12中取值/=MID(B133,2,3)midba爱12jh哈1爱1/=MIDB(B134,2,3)mod余数10031/=MOD(100,3)10072/=MOD(100,7)mode出现最多的数22/=MODE(B137:
B141)2345not求反0TRUE/=NOT(B142)TRUE/=NOT(B143)FALSETRUE/=NOT(B144)TRUEFALSE/=NOT(B145)5FALSE/=NOT(B146)ABCDE102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146odd绝对值较大的奇数1/=ODD(B147)1.2363/=ODD(B148)2.93/=ODD(B149)-1.6-3/=ODD(B150)offset偏移-10-1.6/=OFFSET(B151,-1,0)-21-3/=OFFSET(B152,-2,1)or或TRUE,FALSETRUE/=OR(TRUE,FALSE)TRUE,TRUETRUE/=OR(TRUE,TRUE)FALSE,FALSEFALSE/=OR(FALSE,FALSE)product全部数乘机318/=PRODUCT(B156:
B158)23rand随机数0.558287860.611168426/=RAND()0.2115436140.079398716/=RAND()rank排名22/=RANK(B161,B$161:
B$165)31/=RANK(B162,B$161:
B$165)23/=RANK(B163,B$161:
B$165,-1)14/=RANK(B164,B$161:
B$165,0)11/=RANK(B165,B$161:
B$165,1)replace1爱ghyhh1换hyhh/=REPLACE(B166,2,2,换)replaceb1爱ghyhh1换ghyhh/=REPLACEB(B167,2,2,换)rept重复重复重复重复/=REPT(B168,3)规律备注*s*数*if满足条件*b双字节汉字*a非空*is*是否*right1爱ghyhh开心yhh开心/=RIGHT(B176,5)rightb1爱ghyhh开心h开心/=RIGHTB(B177,5)round四舍五入123.4467123.45/=ROUND(B178,2)123.4467123.4/=ROUND(B179,1)roundup四舍五入123.4467123.45/=ROUNDUP(B180,2)123.4467123.5/=ROUNDUP(B181,1)rounddown四舍五入123.4467123.44/=ROUNDDOWN(B182,2)123.4467123.4/=ROUNDDOWN(B183,1)rowB184184列号/=ROW(B184)rowsB185:
B19915列数/=ROWS(B185:
B199)search检索字串11/=SEARCH(B186,B187)13爱3125/=SEARCH(B186,B187,3)searchb检索字串11/=SEARCHB(B188,B189)13爱3126/=SEARCHB(B188,B189,3)sign正负0/=SIGN(B190)11/=SIGN(B191)-1-1/=SIGN(B192)00/=SIGN(B193)ABCDE147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193small第?
小11/=SMALL(B194:
B200,1)32/=SMALL(B194:
B200,3)26541sum+210/=SUM(B201:
B203)35sumif条件求和28/=SUMIF(B204:
B206,2)35/=SUMIF(B204:
B206,2)榴莲3榴莲9苹果2苹果6葡萄5葡萄7香蕉1香蕉8榴莲12/=SUMIF(B$207:
B$214,榴莲,C207:
C214)苹果11/=SUMIF(B$207:
B$214,B216,C208:
C215)葡萄9/=SUMIF(B$207:
B$214,B217,C209:
C216)香蕉20/=SUMIF(B$207:
B$214,B218,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 全部 公式 实例 解释