excel计算公式.docx

1、excel计算公式用EXCEL快速计算天数的技巧(做进度计划用)1.计算两个日期中的工作日天数运用networkdays函数，“=networkdays（Date1，Date2 ）”，括号中的Date1，Date2 分别代表两个日期，即可得到两个日期之间的工作日数。如果遇到两个日期除周末外，有省/市/自治区和国家/地区的法定假日以及其他非法定假日，那么，可以使用“=networkdays（Date1，Date2 ，Holidays）”Holidays 表示不在工作日历中的一个或多个日期所构成的可选区域。例如，2008年10月1日至3日为国家法定节假日，在excel空白单元格A4、A5中分别输入

2、2008年10月1日和2008年10月3日，在“=networkdays（Date1，Date2 ，Holidays）”函数中Holidays用鼠标选中A4：A5，即可计算出排除国家法定节假日的工作日数了 。备注：如果该函数不可用，并返回错误值 #NAME?，请安装并加载“分析工具库”加载宏。2.计算两个日期中的天数DATEDIF(start_date,end_date,unit)Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。日期有多种输入方法：带引号的文本串（例如 2001/1/30）、系列数（例如，如果使用1900 日期系统则 36921 代表 2001 年 1

3、月 30 日）或其他公式或函数的结果（例如，DATEVALUE(2001/1/30)）。End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。Unit 为所需信息的返回类型： Y 时间段中的整年数。 M 时间段中的整月数。 D 时间段中的天数。 MD start_date 与 end_date 日期中天数的差。忽略日期中的月和年。 YM start_date 与 end_date 日期中月数的差。忽略日期中的日和年。 YD start_date 与 end_date 日期中天数的差。忽略日期中的年。 说明 Microsoft Excel 按顺序的系列数保存日期，这样就可以对其进

4、行计算。如果工作簿使用 1900 日期系统，则 Excel 会将 1900 年 1 月 1 日保存为系列数 1。而如果工作簿使用 1904 日期系统，则 Excel 会将 1904 年 1 月 1 日保存为系列数 0，（而将 1904 年 1 月 2 日保存为系列数 1）。例如，在 1900 日期系统中 Excel 将 1998 年 1 月 1 日保存为系列数 35796，因为该日期距离 1900 年 1 月 1 日为 35795 天。请查阅 Microsoft Excel 如何存储日期和时间。Excel for Windows 和 Excel for Macintosh 使用不同的默认日期系

5、统。 示例：DATEDIF(2001/1/1,2003/1/1,Y) 等于 2，即时间段中有两个整年。DATEDIF(2001/6/1,2002/8/15,D) 等于 440，即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之间有 440 天。DATEDIF(2001/6/1,2002/8/15,YD) 等于 75，即在 6 月 1 日与 8 月 15 日之间有 75 天，忽略日期中的年。DATEDIF(2001/6/1,2002/8/15,MD) 等于 14，即开始日期 1 和结束日期 15 之间的差，忽略日期中的年和月。=DATEDIF(B2,TODAY(),y) =D

6、ATEDIF(B2,TODAY(),ym) =DATEDIF(B2,TODAY(),md) =DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2用Excel可以很轻松地计算出两个时间的天数差，方法有2种：方法1：在A1单元格输入前面的日期，比如2004-10-10，在A2单元格输入后面的日期，如2005-6-7。接着单击A3单元格，输入公式=DATEDIF(A1,A2,d)。然后按下回车键，那么立刻就会得到两者的天数差240。提示：公式中的A1和A2分别代表前后两个日期，顺序是不可以颠倒的。此外，DATEDIF函数是Excel

7、中一个隐藏函数，在函数向导中看不到它，但这并不影响我们的使用。方法2：任意选择一个单元格，输入公式=2004-10-10-2005-6-7，然后按下回车键，我们可以立即计算出结果。 - Excel 中 最 便 利 的 工 作 表 函 数 之 一 Datedif 名 不 见 经 传， 但 却 十 分 好 用。Datedif 能 返 回 任 意 两 个 日 期 之 间 相 差 的 时 间， 并 能 以 年、 月 或 天 数 的 形 式 表 示。 您 可 以 用 它 来 计 算 发 货 单 到 期 的 时 间， 还 可 以 用 它 来 进 行2000 年 的 倒 计 时。 - Excel 中 的Da

8、tedif 函 数 带 有3 个 参 数， 其 格 式 如 下： - =Datedif(start_date,end_date,units) - start_date 和end_date 参 数 可 以 是 日 期 或 者 是 代 表 日 期 的 变 量， 而units 则 是1 到2 个 字 符 长 度 的 字 符 串， 用 以 说 明 返 回 日 期 差 的 形 式（ 见 表1）。 图1 是 使 用Datedif 函 数 的 一 个 例 子， 第2 行 的 值 就 表 明 这 两 个 日 期 之 间 相 差1 年 又14 天。 units 的 参 数 类 型 对 应 的Datedif 返

9、回 值“y” 日 期 之 差 的 年 数（ 非 四 舍 五 入）“m” 日 期 之 差 的 月 数（ 非 四 舍 五 入）“d” 日 期 之 差 的 天 数（ 非 四 舍 五 入）“md” 两 个 日 期 相 减 后， 其 差 不 足 一 个 月 的 部 分 的 天 数“ym” 两 个 日 期 相 减 后， 其 差 不 足 一 年 的 部 分 的 月 数“yd” 两 个 日 期 相 减 后， 其 差 不 足 一 年 的 部 分 的 天 数在EXCEL中两个日期相减如何得出月数 要求余数大于15天为一个月，小于15天的则忽略不计=DATEDIF(A1,B1,m)+(DATEDIF(A1,B1,m

10、d)=15)比较常用的Excel技巧(转)1、两列数据查找相同值对应的位置=MATCH(B1,A:A，0)2、已知公式得结果定义名称=EVALUATE(Sheet1!C1)已知结果得公式定义名称=GET.CELL(6,Sheet1!C1)3、强制换行用Alt+Enter4、超过15位数字输入这个问题问的人太多了，也收起来吧。一、单元格设置为文本;二、在输入数字前先输入5、如果隐藏了B列，如果让它显示出来?选中A到C列，点击右键，取消隐藏选中A到C列，双击选中任一列宽线或改变任一列宽将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。6、EXCEL中行列互换复制，选择性粘贴，选中转置，确定即可7、

11、Excel是怎么加密的(1)、保存时可以的另存为右上角的工具常规设置(2)、工具选项安全性8、关于COUNTIFCOUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,=90)介于80与90之间需用减，为=COUNTIF(A1:A10,80)-COUNTIF(A1:A10,90)另外补充：条件：列中包含某字符A1-含字符EA目的：赋予含该字符对应单元格相应公式或数值套用公式：=if(COUNT(search(EA,A1),公式或数值1,公式或数值2)=if(ISERR(search(EA,A1),公式或数值2,公式或数值1)Count因为Search得到的是代表位置的数

12、字或者#Value错误，Count对数字可以统计，对错误值得到0。Iserr条件反转。Isnumber当A1为数字时，则返回true9、根据身份证号提取出生日期(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身份证EXCEL补充：A)提取生日（年-月-日）：=IF(LEN(A1)=15,TEXT(CON

13、CATENATE(19,MID(A1,7,6),#-00-00),TEXT(MID(A1,7,8),#-00-00)B)提取年龄：=YEAR(TODAY()-IF(LEN(A1)=15,CONCATENATE(19,MID(A1,7,2),MID(A1,7,4)C)提取性别：=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1),2)=1,男,女)10、想在SHEET2中完全引用SHEET1输入的数据工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。11、一列中不输入重复数字数据-有效性-自定义-公式输入=COUNTIF(A:A,A

14、1)=1如果要查找重复输入的数字条件格式公式=COUNTIF(A:A,A5)1格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上13、excel下拉菜单的实现数据-有效性-序列14、10列数据合计成一列=SUM(OFFSET($1,(ROW()-2)*10+1,10,1)15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH)(1)、根据符合行列两个条件查找对应结果=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)(2)、根据符合两列数据查找对应结

15、果(为数组公式)=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0)16、如何隐藏单元格中的0单元格格式自定义0;-0;或选项视图零值去勾。呵呵，如果用公式就要看情况了。17、多个工作表的单元格合并计算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)18、获得工作表名称(1)、定义名称：Name=GET.DOCUMENT(88)(2)、定义名称：Path=GET.DOCUMENT(2)(3)、在A1中输入=CELL(filename)得到路径级文件名在需要得到文件名的单元格输入=MID(A1,FIND(*

16、,SUBSTITUTE(A1,*,LEN(A1)-LEN(SUBSTITUTE(A1,)+1,LEN(A1)(4)、自定义函数PublicFunctionname()DimfilenameAsStringfilename=ActiveWorkbook.namename=filenameEndFunction19、A)如何获取一个月的最大天数:=DAY(DATE(2002,3,1)-1)或=DAY(B1-1),B1为2001-03-01B)数据区包含某一字符的项的总和,该用什么公式=sumif(a:a,*&某一字符&*,数据区)C)最后一行为文本：=offset($b$1,MATCH(CHAR(

17、65535),b:b)-1,)D)最后一行为数字：=offset($b$1,MATCH(9.9999E+307,b:b)-1,)或者：=lookup(2,1/(b1:b1000),b1:b1000)E)评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。看看trimmean()函数帮助。被去掉的分数：最大两个：=large(data,1;2)最小两个：=small(data,1;2)F)怎样很简单的判断最后一位是字母right(a1)*1G)出错的字母=IF(ISNUMBER(-RIGHT(A1,1),数字,字母)=IF(ISERR(RIGHT(A1

18、)*1),字母,数字)H)如何设置单元格，令其不接受包含空格的字符选定A列数据有效性自定义公式=iserror(find(,a1)数据-有效性-自定义-公式=len(a1)=len(trim(a1)注*单元格消空格用函数=TRIM(A1)I)实现跳格累加如=a1+a4+a7+a10+a13+a16+a19+a22.=sum(n(offset(a1,(row(1:10)-1)*3,)J)在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?=INDIRECT(A1&!&E1)A1为工作表名K)奇数行求和=SUMP

19、RODUCT(A1:A1000)*MOD(ROW(A1:A1000),2)L)偶数行求和=SUMPRODUCT(A1:A1000)*NOT(MOD(ROW(A1:A1000),2)M)查看字符串字数=LEN(A1)N)求非空单元格数量公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了)=COUNTIF($E$3E$65536,?*)O)动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.=SUM(INDIRECT(A1:A&ROW()-1)20、比较好用的EXCEL文档修复工具ExcelRecovery21、EXCEL开方运算将8开3次方，可以用这个公式，在单元格中输入=

20、8(1/3)22、单元格中的数据分散对齐文本格式全角输入23、查找工作表中的链接Ctrl+或编辑链接24、如何让空单元格自动填为0选中需更改的区域查找空替换025、把Word里的数字转换到Excel方法有多种，选中复制设置输入单元格为文本选择性粘贴值选中表格转换为文本粘贴分列对分列选项设置为文本另存为文本文件EXCEL中打开文本文件对导入文本对话框进行对应设置26、扩展你的命名域Excel允许你定义命名的域，这样你的方程式就能够更具描述性，例如，你可以使用“=SUM(Bonus)”来替换“=SUM(C2:C25)”。尽管Excel的命名域通常都是静态的单元域，例如“C2:C25”，但你也可以在

21、添加新数据的时候创建一个动态的命名域。要将一个域更名并让其变成动态，请注意工作簿的名称，以及第一个数据单元所在的地址，它需要不是一个列标题，例如“$C$2”。点击“插入名称定义”，在“在当前工作簿的名称”中输入工作簿名。在“引用位置”中，输入“=OFFSET(Bonus!$C$2,0,0,COUNTA(Bonus!$C:$C)”，并注意其中不能有空格，接着按下回车键来创建命名域。这个方程式就能够定义你的命名域，计算列中的数值，并能够按单元格的数字延伸这个域。27、单双行简单选在A列前插入一空列，A1中输入1，A2中输入2，选中A1A2，将光标移到A2右下角，当光标变成实心十字时，按住ctrl见

22、往下拖动到想要的位置，再按这列排序，就把单双行分开了28、如何在已有的单元格中批量加入一段固定字符？例如：在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的号码全部再加两位，即要在每个人的证书号码前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办法，省时又省力：1）假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列；2）在B2单元格写入：=13&A2后回车；3）看到结果为13xxxxxxxxxxxxx了吗？鼠标放到B2位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束。当你放开鼠标左键时就全部都改好了。若是在原证书号后面加13则

23、在B2单元格中写入：=A2&“13”后回车。29、如何设置文件下拉窗口的最下面的最近运行的文件名个数？打开“工具”，选“选项”，再选“常规”，在“最近使用的文件清单”下面的文件个数输入框中改变文件数目即可。若不在菜单中显示最近使用的文件名，则将“最近使用的文件清单”前的复选框去掉即可。30、在EXCEL中输入如“-”、“-”之类的格式后它即变成月日，月日等日期形式，怎么办？这是由于EXCEL自动识别为日期格式所造成，你只要点击主菜单的“格式”菜单，选“单元格”，再在“数字”菜单标签下把该单元格的格式设成文本格式就行了。31、在EXCEL中如何使它象WORD一样的自动定时保存文件？点击“工具”菜

24、单“自动保存”项，设置自动保存文件夹的间隔时间。如果在“工具”菜单下没有“自动保存”菜单项，那么执行“工具”菜单下“加载宏.”选上“自动保存”，“确定”。然后进行设置即可。32、用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。但是不是用页眉来完成？在EXCEL的文件菜单页面设置工作表打印标题；可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可。这样Excel就会自动在各页上加上你划定的部分作为表头。33、在Excel中如何设置加权平均？加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要理解加权平均值其实

25、就是总量值（如金额）除以总数量得出的单位平均值，而不是简单的将各个单位值（如单价）平均后得到的那个单位值。在Excel中可设置公式解决（其实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果就是这些量值的加权平均值。34、如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？如何才能一次打印多个工作表？把鼠标移到工作表的名称处（若你没有特别设置的话，Excel自动设置的名称是“sheet1、sheet2、sheet3.”），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时你的所有操作都是针对全部工作表了，不管是设置页眉和页脚还是打印你工作表。35、EXCEL中有序号一栏，由于对表格进行调整，序号全乱了,可要是手动一个一个改序号实在太慢太麻烦，用什么方法可以快速解决？如果序号是不应随着表格其他内容的调整而发生变化的话，那么在制作EXCEL表格时就应将序号这一字段与其他字段分开，如在“总分”与“排名”SPA 体验新版博客