企业管理套表表格函数公式大全企业管理套表表格函数公式大全 Excel 常用函数公式及技巧搜集（常用的）Excel 常用函数公式及技巧搜集（常用的） 【身份证信息？提取】【身份证信息？提取】 从身份证号码中提取出生年月日从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1 =IF(A2,TEXT(LEN(A2)=15)*19Ctrl+; 如何判断某日是否星期天如何判断某日是否星期天 =WEEKDAY(A2,2)=WEEKDAY(A2,2) =TEXT(A1,aaaa)=TEXT(A1,aaaa) =MOD(A1,7)2=MOD(A1,7)1) =SUMPRODUCT(-(MOD(ROW(INDIRECT(DATE(YEAR(NOW(),MONTH(NOW(),1)=TEXT(A1,yyyy年m月d日;) 象 22 怎样转换成 22 日？转成当年当月的日子象 22 怎样转换成 22 日？转成当年当月的日子 公式为：=date(year(now(),month(now(),22)=date(year(now(),month(now(),22) 将“2006 年 5 月”转换成“2006 年 05 月”将“2006 年 5 月”转换成“2006 年 05 月” 公式为：=TEXT(A8,yyyy年mm月;)=TEXT(A8,yyyy年mm月;) 也可以这样处理：选中单元格，设置单元格公式数字自定义，将 yyyy“年” m“月”改为：yyyy“年”mm“月” ，即可。但这方法打印出来显示为：2006/5/ 将“1968 年 6 月 12 日”转换为“1968/6/12”格式将“1968 年 6 月 12 日”转换为“1968/6/12”格式 =YEAR(A1) 其实不是数据排序，应该是数据填充。 输入公式=LEFT(E3,4)+3)A1) 把不同单元格的内容合到一个单元格里把不同单元格的内容合到一个单元格里 比如：A1=好，A2=好，A3=学，A4=习 在 A5 输入公式，使 A5 单元格内容为：好好学习 =TEXT(A1100.0;0+#;0+#;0+0.0%;0+0.0%;选择性贴上 5.选除 6.按确定 如果还要后面自动显示万元 可以到 格式单元格数字(卷标)自订 把 G/通用格式改成 G/通用格式万元 选择性粘贴还有这种用法，真神奇。 我原来都是另选一列， 用函数 round(B1/10000,0)， 再用 “选择性粘贴数值” 复盖原来数据，这样处理有一个好处，就是小数点后面没有那么多的数字。 可以采用=ROUND(D14/10000,0) 2.#”文本” 、#.#”文本” 、#,#.#”文本”等(同上); 3.DBNum1$-804G/通用格式、DBNum2$-804G/通用格式等(数值的大 小写格式)； 4”文本”(在原有的文本上加上新文本或数字); 50000000（发票号码等号码输入）; 6.yyyy/mm 7.yyyy/m/daaaa-ex.2003/12/20 星期六 8.m月d日(ddd)-ex.12 月 20 日(Sat) 9.Subject(Total:0)-单纯加上文字 10.Balance*#,#0_-对齐功能 11.蓝色+*#,#0_;-*#,#0_-正负数的颜色变化 12.*;*;*;*-仿真密码保护(搭配 sheet 保护) 13.红色0（小于 0 时显示红色，大于 0 时绿色，都以绝对 值显示） 140#,#0.00;（大于） 、= （大于等于） 、（小于） 、=（小于等于） 、=（等于） 、（不等于） ，如果你觉 得这些符号不好记，就干脆使用“”或“=”号来表示。 由于自定义格式中最多只有 3 个数字段，Excel 规定最多只能在前两个数字段中 包括 2 个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数 字使用第 3 段格式。 如果仅包含一个条件测试， 则要根据不同的情况来具体分析。 自定义格式的通用模型相当于下式：；0正数格式；50000$#,#0.00_);红色999999#,#0,_m吨;999#,_k_m千克;#_k克”可以看到，使用 条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工 作表的可读性和效率。 另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式； ；0只显示负数和零，输入的正数则不显示；格式“；”则隐藏所有的 输入值。自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影 响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。 怎样定义格式怎样定义格式 怎样定义格式表示如 00062920020001、00062920020002 只输入 001、002 答：格式-单元格-自定义-00062920020-确定 在工具按钮之间设置分隔线在工具按钮之间设置分隔线 工具栏中只有不同组的工具按钮才用分隔线来隔开，如果要在每一个工具按 钮之间设置分隔线该怎么操作？ 答：先按住“Alt”键，然后单击并稍稍往右拖动该工具按钮，松开后在两 个工具按钮之间就多了一根分隔线了。如果要取消分隔线，只要向左方向稍稍拖 动工具按钮即可。 自定义区域为每一页的标题自定义区域为每一页的标题 自定义区域为每一页的标题。 方法：文件-页面设置-工作表-打印标题-顶端标题行与左顶标题列 这样就可以每一页都加上自己想要的标题。 一个单元格内格式问题一个单元格内格式问题 如 果 我 做 了 一 个 表 某 一 列 是 表 示 重 量 的 ， 数 值 很 多 在 1-1524745444444 之间的数不等。这些表示重量的数。如果我想次 给他们加上单位，但要求是单位是999999 吨，之下999 是千克，其余的是克。 如何办 答：9999#.00,吨;*,*.00千克 定制单元格数字显示格式定制单元格数字显示格式 定制单元格数字显示格式，先选择要定制的单元格或区域，单击鼠标右键单 元格格式选择数字选项选择自定义在“类型”中输入自定义的数 字格式。 如何输入自定义的数字格式：需要先知道自定义格式中那些常用符号的含意，具 体可以先不选择自定义，而选择其它已有分类观看示例，以便得知符号 的意义。 比如：先选择百分比然后马上选择自定义，会发现类型中出现 0.00%，这就是百分比的定义法，把它改成小数位 3 位的百分比显示法只要 把 0.00%改成 0.000%就好了，把它改成红色的百分比显示法只要把 0.00% 改成红色0.00%就好了。 巧用定位选条件单元格巧用定位选条件单元格 Excel 表格中经常会有一些字段被赋予条件格式。如果要对它们进行修改，那么 首先得选中它们。可是，在工作中，它们经常还是处在连续位置。按”Ctrl”健 逐列选取恐怕有点太麻烦。其实，我们可以使用定位功能来迅速查找它们。方法 是点击“编辑定位”单命令，在弹出的“定位”对话框中，点击“定位条件” 按钮，在弹出的“定位条件”对话框中，选中“条件格式”单选项成为可选。选 择“相同”则所有被赋予相同条件格式的单元格会被选中。 工作表的标签的字体和大小可以更改吗工作表的标签的字体和大小可以更改吗 答：在桌面上点右键内容外观，相关的设定都在此更改。 sheet1 工作表的 A1、A2、A3 单元格分别链接到 sheet2、sheet3、sheet4 解答： 1、=indirect(sheet3;4,),SUMPRODUCT(VLOOKUP(A3,表 二!$G$3:$J$42,2;3;4,) =IF(COUNTIF(表二!$B$3:$B$42,A3),SUMPRODUCT(VLOOKUP(A3,表 二!$B$3:$E$42,2;3;4,),SUMPRODUCT(VLOOKUP(A3,表 二!$G$3:$J$42,2;3;4,) =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0),SUM(OFFSET(表 二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,3),SUM(OFFSET(表 二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,3) =IF(ISERROR(MATCH(A3,表二!$B$3:$B$42,0),SUM(OFFSET(表 二!$G$2,MATCH(A3,表二!$G$3:$G$42,0),1,3),SUM(OFFSET(表 二!$B$2,MATCH(A3,表二!$B$3:$B$42,0),1,3) =IF(ISERROR(VLOOKUP(A3,表二!$B$3:$F$42,4,0),SUM(INDIRECT(表 二!H如果是 58.72 元，就收 58.5 元。这可难坏了我。经过研究， 我发现，可以在设置单元格中，设成分数，以 2 为分母，可以解决问题。但是打 印出来的却是分数不好看，而且求和也不对。请各位高手给予指点。是这样的， 如果是 57.01 元，则省去，即收 57.00 元；如果是 57.31 元，则进为 57.50 元； 如果是 57.70 元，也收 57.50 元；要是 57.80 元，则收 58.00 元。 假设数据在 A1 =INT(A1)+IF(A1-INT(A1)0.7),1,0.5) 简化一下： =INT(A1)+0.5*(A1-INT(A1)0.3)+(A1-INT(A1)0.7) int 函数取整数部分，A1-int(A1)取小数部分，根据你的意思：0.3)+(A1-INT(A1)0.7)”即(小数部分 0.3)+(小数部分0.7) 我们知道这是省略 if 的判断语句，条件为真返回 true（也就是 1） 否在为 false （0） ，那么如果小数=0.3，则两个条件都为 0，即整数部分+0.5*0=整数部分， 介于 0.30.7，则为整数部分+0.5*（1+0） ，大于 0.7 肯定也大于 0.3 啦，则为整 数部分+0.5*（1+1） 。 请问，如果是由几个分表汇总的总表想如此处理，该如何做。 例：e112 位置=SUM(一库入库!G112,二库入库!G112,四库入库!G112,保健酒基地 入库!G112,下陆仓库入库!G112) 汇总的结果为 100.24，而我要求如果小数为 24 的话自动视为 1 累加，否则 不便。 就是小数为 0.24 才加 1，否则都舍掉？ 若是：=if（sum 公式-int（sum 公式）=0.24，int（sum 公式）+1，sum 公 式） 想把小数点和后面的数字都去掉，不要四舍五入想把小数点和后面的数字都去掉，不要四舍五入 比如： 12.30 变成 12.00 45.3245.00 25.3825.00 6.546.00 13.0213.00 59.6859.00 23.6223.00 =Rounddown(A1,0)=Rounddown(A1,0) 你要把 A1 换成你要转换的那个单元格啊，然后拖动就可以了！ 我那里用的那个 A1 只是告诉你一个例子而已，你要根据你的实际情况来修 改一下才能用的。 =INT(A1)=INT(A1) =TRUNC(A1,0)=TRUNC(A1,0) 求真正的四舍五入后的数求真正的四舍五入后的数 请教如何在 Excel 中，求“金额合计”（小数点后二位数）时，所取的数值 应是所求单元格中写的数字（四舍五入四舍五入后的数字），而不是（四舍五入四舍五入前）的数 字。因为只有这样行和列及关联的工作表才能对得上，例