Excel公式百科.doc

Excel计算公式1. Excel2003最大值，最小值，求和，平均值等A1写“最大值”，A2公式=MAX(E3:E100)B1写“最小值”，B2公式=MIN(E3:E100)C1写“求和”，C2公式=SUM(E3:E100)D1写“平均值”，D2公式=AVERAGE(E3:E100)2.在excel表格中不同日期显示不同颜色例如，想再每个月日期为奇数时显示红色，偶数时显示绿色，就是隔天一个颜色，格式条件格式公式：=MOD（A1，2）=1，设置单元格格式背景为红色确定； 3. excel 下拉填充 序号0102030405=TEXT(ROW(A1),00)4. EXCEL隔行求和（如从O7单元格开始，到05000，后面每隔16行开始求和）=IF(MOD(ROW()-6,16),SUM(OFFSET($O$7,ROW()-22,16,)5. ExcelIf函数有sheet1和sheet2两个页，其中有A，B，C列.A列是人名，B2是语文成绩,B3为数学成绩C为分数.第一页sheet1已经是填好的表格。现在需要做的是：假如sheet2的A列的人名与sheet1的A列的人名相同，再把“语文成绩”或“数学成绩”输入，则把对应B列的成绩引入到sheet2的C列中来。=VLOOKUP(A1,SHEET1!A1:C100,2,0)6. EXCEL里设置时间相加（还是时间）例如在A单元格中输入10：30，在B单元格中输入0：30 c输入公式=SUM(A1:B1)C列设置单元格格式为的时间格式或自定义格式h:mm时间相加，不超过24小时，如果是25就会变成1：007. excel函数例如：A1是2011-02-02，在B1显示2012-02-01，如果A1是2011-02-01，就必须显示2012-01-31. B1中输=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1)8.EXCEL单元格A1是1*150*250，怎样在B1单元格中显示为1，B2为150，B3为250数据分列分隔符号：*确定！9.在EXCEL里一个格子里面输入DK123+000002,拖动时000002,两个数字可以分别以定值增加！=DK123+&680+(row(a1)-1)*3&680+row(a1)*310. excel在一组数字前全部加一个符号比如一列数字 12，23，54，54，65，我要在全部加个符号A，变成A12,A23,A54,A54,A65假设A列是12,23,34.，需要在B列完成你所要的结果，那么在B列的第一行的单元格内键入：=CONCATENATE(A,A1) 11.EXCEL中，一个百分数，在大于等于100%的情况下，得满分4分，小于50%得0分，但大于等于50%小于100%情况下=IF(A11,4,MAX(0,MIN(A1-0.5)*150,20)12. excel用函数提取某个符号前的字符串？比如A1=复合PVC-85/1.5，在B1用什么函数提取“/”之前的值，就是提取出“复合PVC-85”？=LEFT(A1,SEARCH(/,A1)-1)13. EXCEL自动输出某一范围的随机数值,比如A1到E99,全部自己输出1.10-1.99的随机数=TEXT(RAND()*(1.99-1.10)+1.10,0.00)*114. EXCEL排序 例如 326 我想要结果是236 ；353我想要结果是335 =MIN(MID(TEXT(B5,000),1,2,3,1)*1)&LARGE(MID(TEXT(B5,000),1,2,3,1)*1,2)&MAX(MID(TEXT(B5,000),1,2,3,1)*1)15.用excel求和后等于11.5，总是12 一：把单元格拉大点看看是否有变化？二：看看是否设置了小数点位数为0，工具选项编辑自动设置小数点数为0的勾取掉？三：是否你的公式使用了ROUND或者ROUNDUP函数。16. Excel表格问题： 假设表1有1000个数据，表2有5000个数据，怎样才能知道表1中的数据在表2中有没有出现，有哪些数据出现过？假设在A列，B列输入：=IF(COUNTIF(SHEET2!A:A,A1)0,有,)然后对B列进行非空 筛选！17. EXCEL设置在A单元格输入30，在B单元格要变成0:30=INT(A1/60)&:&MOD(A1,60)18. Excel判断比如一个表中，A为各分店商品名称，B为库存量，然后想做一个表，由各分店填好后，复制在一个计表中，一个公式就能统计出各种商品的库存总和？=SUMIF($A:$A,a*,$B:$B)19. excel中有3列数字分别表示时分秒，合并到一列里？ =TEXT(A1&:&B1&:&C1,H:MM:SS)20. EXCEL里，比如：2个，26箱，50根，136台，去掉后面的字，只要数字？=LEFT(A1,LENB(A1)-LEN(A1)21. excel 三列穿插变一列在EXCEL表中，如1 2 34 5 67 8 9变成123456789=OFFSET($A$1,INT(ROW(A1)-1)/3),MOD(ROW(A1)+2,3)22. excel的操作，用公式可以使表1中A1的值=表2、表3、表4表N中A1项的和？ 总表中的F6项=其他每张表的F5项的和？=sum(sheet2:sheetN!A1) 23. Excel 比如1998年5月19日出生，格式为：19980519 ，把这个年月日分成三个格子，即：年、月、日各占个格子选中整列-数据-分列-勾选（固定宽度），然后点下一步，完成，即可拆分。24.在excel中将0.4545四舍五入？ =ROUND（A1，0）后面的0说的是你要保留的小数位数25. excel函数如输入为1，2，3，4，则输出为1； 如输入为5，6，7，8，则输出为2； 以此类推。 =int(a1-1)/4)+126. Excel里计算某区域数的平均值，但其中一些数为0，计算时不计算在内， 比如10个数有2个是0就只除以8=SUM(A3:A6)/COUNTIF(A3:A6,0)27. Excel里的日期2010-1-2转成2010-01-02 设置单元格格式自定义YYYY-MM-DD确定！28. excel在一列日期前再加相同的符号， 比如2010-10-11 2010-10-12 2010-10-13这三个日期前加AE-，设置单元格格式自定义AE-确定！29. excel实现sheet1中姓名重复的多行同时引用在sheet1中会随机出现姓名相同的多行，比如姓名为A的人一共出现了5次，怎么把这5行的内容自动引用到sheet2中呢？（不采用筛选方式，要求sheet1中输入后，自动在sheet2中就汇总了）sheet2 B2输入=INDEX(sheet1!B:B,SMALL(IF(sheet1!$A$1:$A$5000=$A$2,ROW($1:$5000),48),ROW(1:1)&30. 用Excel做员工生日提醒，日期在A3，提醒在B3=IF(AND(MONTH(TODAY()=MONTH(A3),DAY(A3)=DAY(TODAY(),今天是某某生日,)31. Excel mid函数1142509如果字符串里第五位是5的话，显示1，否则0=IF(mid(1142509,5,1)=5,1,0）32. excel工作表中，把相同名称对应的不同数值求和，放到另外一张工作表， ID放在C列， 金额在E列如：ID 金额chenlihua 100xiong 500deng 1000chenlihua 500xiong 300将相同的ID：chenlihua，对应的金额100和500加起来假设数据所在的工作表为sheet1=sumif(sheet1!c:c,chenlihua,sheet1!e:e)33. excel表中今天输入日期是2011-3-9明天日期2011-3-10电脑可以自动变换日期=TODAY()34 excel多个表格里加上同样数据单元格格式自定义BB确定！35. EXCEL将括号中的内容提取出来 A1是11(60,61,62)B1得到：60，61，62=MID(A1,FIND(,A1)+1,FIND(),A1)-FIND(,A1)-1)36.excel：自动填色 例如：任何一个单元格只要满足A,B,C,D其中一个字，就自动整行填色？=OR(ISERROR(FIND(A,A1)*ISERROR(FIND(B,A1)*ISERROR(FIND(C,A1)*ISERROR(FIND(D,A1)*1=0 可以采用条件格式，因从“A”到“D”是连续的，直接用“介于”“A”“D”然后选择填充颜色即可。37. EXCEL排版 文字前多加两空格,一个个单元格的敲空格进去太慢用自定义格式 38. excel公式做一个表格 数量 单价 金额 然后在数量 单价这2栏填写数字 金额栏的金额自动弹出来，金额一栏是空白的=IF(A1*B1=0,A1*B1)39. EXCEL中用函数实现将同时符合两列条件的数据找出来例如A B1 22 22 11 11 22 1数据记录中，满足 在B列值为1 且 在A列值为2 的数据有多少个？=SUMPRODUCT(A1:A100=1)*(B1:B100=2)40. excel默认单元格格式内数据为正数而不是通用格式单元格内做加法，第一次输入时50，第二次再加50，必需要在第一个50前加上一个+号，让excel默认第一个数就是正数。不是要输加号就是要输等于号03版，单击菜单【工具】【选项】【1-2-3的帮助】选项卡下方勾选“转换Lotus 1-2-3公式”即可不用输入+号或=号。41. excel 100万以内 乘以1.2%的税 101-500万的乘以1% 合计共纳税多少 =IF(A10,盈利,亏损)45. excel中，像支票软件一样把每一格的小写数字转换合并成大写金额小格在A1:H1,则在I1输入公式:=SUMPRODUCT(A1:H1*10(6-COLUMN(A:H)把数字整合， J1用公式:=IF(I10dbnum2;)&IF(INT(I1),元,)&TEXT(RIGHT(FIXED(I1),2),dbnum20角0分;&IF(ABS(I1)1%,元整,),零角,IF(ABS(I1)1,零),零分,整)46.excel函数 当p500时 r=4.5:；当500=p510时，5；以后每增加10元，值加0.5 p在A列，r的公式：=IF(A10,H$1,)48. Excel中，A1单元格内容是A01，让A2单元格显示A02.5显示A05.，在A2输入=left(a1)&text(right(a1,2)+1,00)49. Excel里输入三月份，输入三就出来月份设置单元格格式为自定义型,类型中输入G/通用格式月份,确定。50. excel表格运算大米A B大米 1000小麦 2000大豆 200大豆 800大米 100小麦 300大米 500C1输入=SUMIF(a1:a100,大米,b1:b100)51. 254. excel函数中=sum(a1:a5),一拖动，自动生成=sum(a6:a10)=SUM(OFFSET($A$1,(ROW(A1)-1)*5,5,)52. _ 这个符号怎么打？切换到英文输入法，摁住shift键之后摁0右边那个键53. excel中任意一个空白行，例如一百行，数字随即在-8.0至-13.0之间选中这100行,然后在上方公式栏内输入: =RAND()*(-8-(-13)-1354. 252. 有关excel的公式问题做张表格，前面是100的时候后面格显示80 当前面是80的时候后面格显示50单元格为A1=if(a1=100,80,if(a1=80,50,)不为80或50的时候显示空白55. excel 如果A2，B2都不等于0，则C2=C1+A2+B2； 如果A2，B2都等于0，则C2=0;=IF(OR(A20,B20),C1+A2+B2,0）56. 250. excel判断一组数据中大于60小于70的数据个数呢?假如数据是在A列 =COUNTIF(A:A,70)-COUNTIF(A:A,1，选择符合条件的背景颜色即可，一目了然 64. Excel函数例：输入五名学生的成绩：A1：学生 B1：成绩 C1：等级A2：a B2：90 C2：空A3：b B3：80 C3：空A4：c B4：70 C4：空A5：d B5：60 C5：空A6：e B6：50 C6：空C2-C6填写等级：大于等于90为优秀，大于等于80小于90为良好，大于等于60小于80为及格，小于60为不及格，该如何用多重IF函数来输入等级？=IF(B2=90,优秀,IF(B2=80,良好,IF(B2=60,及格,不及格)65. EXCEL使用SUM函数计算总评成绩，平时成绩占30%，期末成绩占70%sum=（平时成绩）*30%+（期末成绩）*70%66. 238.表格函数公式40分为界限。0-40分 040-45分 0.145-50分 0.250-55分 0.355-60分 0.460-65分 0.565-70分 0.670-75分 0.775-80分 0.880-85分 0.985-90分 1.090-95分 1.195-100分 1.2=IF(AND(A1=0,A10,B3+C3-D3,0)其中：B3单元格是上月C3单元格是入库D3单元格是出货68. excel判断判断，只要B列的数值在A列中，就显示OK，否则NOA列 B列1 66 98912=IF(COUNTIF(A:A,B1)0,OK,NO)69. Excel问题1、在现有的单元格内的数字前面加上统一的字符（数字或字母）2、在现有的单元格内的数字后面加上统一的字符（数字或字母）比如A列 A1-A300每个单元格都是一些无序而不一样位数的数字，（如：A1、23658947，A2、325482）基本是在610位的数字。现在要在每个单元格里的数字后面加“#pp.oji”这几个字符。加在前面 =#pp.ojiA1放在后面 =A1&“#pp.oji 回车下拉要在英文状态时输入公式70. excel字符串 C9672-22030变成 C9672-2030位置固定=replace(A1,7,1,)位置不固定，要把-后面的一个字符去掉=replace(A1,find(-,A1)+1,1,)71. EXCEL中数字,共16位,只留后7位,剩下前几位删除?比如值在A1单元格， =RIGHT(A1,7)72. excel单元格是0，单元格有时是0.00000001，有时是 0.00，工具选项计算重新计算以显示精度为准前打勾！73. 232. EXCEL表格自动求和小数点位数在做EXCEL表格式，某列中数字是通过公式计算得出的数值，计算结果显示很多位小数，公式加上round函数。例如=ROUND(A2+B2,2)。74. 230. EXCEL将一组数字中选取最后4个一组数字123456789，选取6789最后4个数字a1格123456789；则在B1里填入Right(a1,4)75. excel表格在excel中，假设036这三个数字代码是A,458这三个数字的代码是B。就是说不管打0还是3还是6，系统自动出字母A,打4还是5还是8，系统出字母B,假如是356组合，系统就会出ABA,在B1输入： =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,A),3,A),6,A),4,B),5,B),8,B)在A1里输入 356 回车，B1里就会显示 ABA76. EXCEL函数表格A1中数字是1000，那么我想在A2中复制A1数字要求显示出的是1000*2，A3显示数字是1000*3，A4，A5.如果A2显示为：2000，输入公式：=1000*row()。如果A2显示为：1000*2，输入公式：=1000*&row()。78. EXCEL输入数字显示它所对应的文字当输入1是会自动显示优秀，输入2时显示“良好”，输入3时显示“及格”，4时显示“不及格”=IF(A1=1,优秀,IF(A1=2,良好,IF(A1=3,及格,不及格)79. excel大于15小于20时怎么自动变黄格式条件格式公式：=OR(A115,A120)设置背景颜色为黄色确定！80. excel 条件格式如果A1=分类1 那么 A2底色为灰色选中A2格式条件格式公式：=A1=分类1,设置背景颜色为灰色确定！81. EXCEL一个学号，求出学号的第八个数字=MID(A1,8,1)然后用格式刷子刷成常规！82. excel去掉每一行中第一个数字前的空格数据分列分隔符号空格确定！83. excel筛选例如 A列下面正负能相抵，B列还得一样的 ，怎么能快速筛选出开剩下的3和-4列啊？A B1 00032-1 000322 00056-2 000563 00078-4 00078C1输入=sumif(A:A,B1)填充到底，C列不等于0的筛选出来84. excel数据输入 0101仨方法：1）设置单元格格式为文本；2）输入 0101；3）如果都是四位数字，那么右键单元格格式数字自定义：0000确定！85. 输入1-2然后向下拉 它只会变成1-3,1-4,1-5,1-6，1-7,1-8,1-9,2-1这样的序列，将1-2向下拉后变成2-3,3-4,4-5A1中输入：=row()&-&row()+186. excel中求和结果总是慢一步工具选项计算重新计算自动计算！87.用EXCEL由两个起止日期计算年龄知道了出生日期，知道检测日期，数据的表示形式是，如：1979-11-20，截止日期2009-05-03， A1是1979-11-20A2是2009年5月3日A3=INT(A2-A1)/365）88. excel相同名字累加excel 表格中 计算出同一列的（区域自己选定）中相同数据的个数比如： 小明小明小张小明小张=COUNTIF(B1:B4,小明)89. EXCEL,如果9：00开工，做50个产品，每个所需时间是5分钟，如果把结束时间几点几分显示在下一格？=A1+5*50/144090. excel只保留到百位 后面的数字不要比如说25123 只保留到251就可以了同一单元格如何操作？=left(a1,len(a1)-2)91. EXCEL将电话号码中间四位变成*=REPLACE(A1,5,4,*) 说的是手机号码。92. Excel表的单元格格式锁定，隐藏的单元格不被释放，格式不被修改可以修改数字和添加数字，但不能加入单元格和添加行或列工具选项安全性设置你的修改密码。93. EXCEL单元格输入两列字XX为你要输入的内容空格和Alt键+回车键94. EXCEL表格，点打印预览时右下角有签名，但是文档中看不到在关闭打印预览之后 文件-页面设置-页眉页脚-自定义页脚-把右下角的文字删除-确定在打印预览时-设置-页眉页脚-自定义页脚-把右下角的文字删除-确定95.设定一个excel公式， 当X3时，=X*3% 当3X6时，=X*4% 当6X10时，=X*5% 当10时，X*=IF(A1=3,A1*.03,IF(A1=6,A1*.04,IF(A185,10+2*(A1-85)/15,IF(A1Q6（Q6是大于等于0的）则显示0，如果M6-T60Q6则显示Q6,如果0M6-T6Q6,0,IF(M6-T60,Q6-(M6-T6),Q6)110. excel利用IF单元格A1等于单元格A18，且当A18大于10时为11，当A18小于1时为5，当A18为0时，为-5(单元格A18内的值为：1-10）=IF(AND(A1=A18,A1810),11,IF(AND(A1=A18,A180),5,IF(AND(A1=A18,A18=0),-5,)111. EXCEL表格中分别找出两位数的个位和十位数如L：50、56、62分别找出两位数的个位和十位数假设A1起是你的数据,B1输入 =INT(A1/10) 得到十位C1输入 =MOD(A1,10) 得到个位112. EXCEL的数值取整数12.6%13.5%95.1%等等数字，显示结果为整数。即“不要四舍”“要五入”，结果是13%，14%，96%假如数据在A1:A100中,B1中输入=ROUNDUP(A1,2)113. excel的if函数正常的公式是C=A/B，但是A=0，的时候C=无穷=IF(A1=0,无穷,A1/B1)114. excel运算后，正是红，负是绿点菜单上的(格式)-(条件格式)-在(条件一)的(单元格数值)-选大于-输入0-再点下面的( 格式(F). )(字型)-在( 颜色(C) )下拉选 红色-(确定)-再点下(增加(A)-在(条件二)的(单元格数值)-选小于-输入0-再点下面的( 格式(F). )(字型)-在( 颜色(C) )下拉选 绿色-(确定)-(确定)115. excel 小数保留