EXCEL表格函数公式大全.docx
- 文档编号:6325674
- 上传时间:2023-01-05
- 格式:DOCX
- 页数:111
- 大小:328.94KB
EXCEL表格函数公式大全.docx
《EXCEL表格函数公式大全.docx》由会员分享,可在线阅读,更多相关《EXCEL表格函数公式大全.docx(111页珍藏版)》请在冰豆网上搜索。
EXCEL表格函数公式大全
Excel常用函数公式与技巧搜集〔常用的〕
[信息?
提取]
从中提取出生年月日
=TEXT
=TEXT
=IF
显示格式均为yyyy-m-d。
〔最简单的公式,把单元格设置为日期格式〕
=IF
显示格式为yyyy-mm-dd。
〔如果要求为"1995/03/29"格式的话,将"-"换成"/"即可〕
=IF
显示格式为yyyy年mm月dd日。
〔如果将公式中"0000年00月00日"改成"0000-00-00",则显示格式为yyyy-mm-dd〕
=IF A2>=18,MID A2,7,8>,"19"&MID A2,7,6>> 显示格式为yyyymmdd。 =TEXT< =IF =MID =IF 从中提取出性别 =IF =IF =IF =IF 从中进行年龄判断 =IF =DATEDIF〔A1,TODAY〔〕,"Y"〕 〔以上公式会判断是否已过生日而自动增减一岁〕 =YEAR =YEAR =YEAR =YEAR 按号计算至今天年龄 =DATEDIF 以20##10月31日为基准日,按按计算年龄<周岁>的公式 =DATEDIF 按号分男女年龄段 按号分男女年龄段,号在K列,年龄段在J列〔号为18位〕 男性16周岁以下为 1 男性16周岁〔含16周岁〕以上至50周岁为2 男性50周岁〔含50周岁〕以上至60周岁为3 男性60周岁〔含60周岁〕以上为 4 女性16周岁以下为 1 女性16周岁〔含16周岁〕以上至45周岁为2 女性45周岁〔含45周岁〕以上至55周岁为3 女性55周岁〔含55周岁〕以上为 4 =MATCH =SUM<-- ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ [年龄和工龄计算] 根据出生年月计算年龄 =DATEDIF =DATEDIF =DATEDIF 根据出生年月推算生肖 中国人有12生肖,属什么可以推算出来。 即用诞生年份除以12,再用除不尽的余数对照如下: 0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊例如: XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。 =MID<"猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD 如何求出一个人到某指定日期的周岁? =DATEDIF<起始日期,结束日期,"Y"> 计算距离退休年龄的公式 =IF 其中E2为年龄〔可用的公式生成〕; V2为法定退休年龄〔男60,女50〕公式为: =IF D2为男或女〔可用的公式生成〕;U2为出生年月日〔可用的公式生成〕。 求工齡 =DATEDIF =DATEDIF =DATEDIF =DATEDIF 计算工龄 =DATEDIF =DATEDIF =DATEDIF 如果只需要算出周年的话,可以用=datedif<"1978-8","2006-5","Y"> 年龄与工龄计算 有出生年月如何求年龄? 有工作时间如何求工龄? 〔求出的结果为多少年另几个月,如: 0303的形式,即3年零3个月〕。 a1是出生年月或工作时间: =datedif =text 如[B2]=1964-9-1则: =TEXT =TEXT 如果你找不到DATEDIF函数,也可以不用DATEDIF函数, 如[B2]=1964-9-1则: =TEXT =TEXT 自动算出工龄日期格式为 能否用: 〔yyyy.mm.dd〕这种格式来计算出工龄有多长呢~? 以前用这样一段〔=TEXT 但这种方法只能用: 〔yyyy-mm-dd〕这样的日期格式才能实现! 你不妨把"."替换成"-",不就行了吗,再说后者是日期的一种标准格式, =TEXT ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ [时间和日期应用] 自动显示当前日期公式 =YEAR =MONTH =DAY< 如何在单元格中自动填入当前日期 Ctrl+; 如何判断某日是否星期天 =WEEKDAY =TEXT =MOD 某个日期是星期几 比如20##2月9日,在一单元格显示星期几。 =TEXT =TEXT =TEXT =TEXT 什么函数可以显示当前星期 如: 星期二10: 41: 56 =TEXT mm: ss"> 求本月天数 设A1为2006-8-4 求本月天数 A1=DAY 也有更簡便的公式: =DAY 当前月天数: =DATE 用公式算出除去当月星期六、星期日以外的天数 =SUMPRODUCT<-- "&DATE 显示昨天的日期 每天需要单元格显示昨天的日期,但双休日除外。 例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。 =IF =IF 关于取日期 怎么设个公式使A1在年月日向后推5年,变成2011-7-15 =DATE =EDATE 如何对日期进行上、中、下旬区分 =LOOKUP 如何获取一个月的最大天数 "=DAY 日期格式转换公式 将"01/12/2005"转换成"20050112"格式 =RIGHT =YEAR<$A2>&TEXT 也可以用下列两方法: 1、先转换成文本,然后再用字符处理函数。 2、[数据]-[分列][日期]-[MDY] 将"20##9月"转换成"200509"格式 先用公式: =text 将"2005-8-6"格式转换为"20050806"格式 用公式: =TEXT 反之,将20050806转为日期2005-8-6格式,可用公式: =DATE 另四种公式: =text 2005-08-06 =--TEXT 2005-8-6 =TEXT<20050806,"0000-00-00">*1,单元格设置日期型显示: 2005-8-6 =VALUE 2005-8-6 将"20060501"转换为"2006-05-01"格式 =DATE 将"199306"转换为"1993-6" 公式1: =LEFT 公式2: =--TEXT e-m 公式3: =TEXT 把198405转换成1984.05 一、查找—1984,替换—1984. 二、如果全部是年月的话,我个人建议, 1、采取辅助=mid 2、选中这列,用数据中的分列。 然后…………… 三、单元格格式/数字/自定义,类型下面输入: ####"."## 将文本"2004.01.02"转换为日期格式: 2004-1-2 =DATE 将2005-8-6转换为20##8月6日格式 =TEXT 象22怎样转换成22日? 转成当年当月的日子 公式为: =date 将"20##5月"转换成"20##05月" 公式为: =TEXT 也可以这样处理: 选中单元格,设置单元格公式-数字-自定义,将yyyy"年"m"月"改为: yyyy"年"mm"月",即可。 但这方法打印出来显示为: 2006/5/ 将"1968年6月12日"转换为"1968/6/12"格式 =YEAR 1968/6/12 =TEXT 1968/06/12 将"1968年6月12日"转换为"1968-6-12"格式 =YEAR 1968-6-12 =TEXT 1968-06-12 将1993-12-28的日期格式转换成1993年12月 =CONCATENATE =YEAR 也可以自定义格式[$-404]e"年"m"月" 将"1978-5-2"包含年月日的日期转换成"197805"只有年月的格式 =year 要将"99.08.15"格式转换成"1999.08.15"如何做 选中列,数据菜单中选分列,分列过程中"格式"选"日期YMD",结束。 要保持2005/8/6格式 当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本〔前面加'号〕或使用公式=TEXT 也可以用另一种公式: =IF /00! /00">,TEXT 将"二○○三年十二月二十五日"转为"2003-12-25"格式, 1、可以用数组公式将中文日期转化为日期系列数{=14610+MATCH $55153>,"[DBNum1]yyyy年m月d日">,0>} 该公式速度较慢。 2、改进后的公式,速度要快的多: {=DATE<1899+MATCH $2100>,"[DBNum1]0000">,0>,MONTH $366>,"[DBNum1]m月d日">,0>>,DAY $366>,"[DBNum1]m月d日">,0>>>} 要设置为1900年的日期格式。 日期格式转换 如A列是月份数为8,B列是日期数为18,如何在C列显示"8月18日" =A1&"月"&B1&"日" 反之,要将C列的"8月18日"直接分别到D、E列,显示月份和日期, 月数份=LEFT 日期数=MID 也可分别用公式: =month<--c5> =day<--c5> 日期格式转换问题 输入的日期是: 04-07-26.与另一格的"001"合并,合并出来是: 040726001. =TEXT 要想自动取得"编制日期: XXXX年X月X日" 可在该单元格输入="编制日期: "&TEXT ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ [排名与排序筛选] 一个具有11项汇总方式的函数SUBTOTAL =SUBTOTAL<9,$B$2: B2> 在数据筛选求和上有意想不到的功能,11项功能为: 1、求平均数,2、求计数,3、求计数值〔自动筛选序列〕4、求最大值,5、求最小值,6、求乘积,7、求总体标准偏差,8、求标准偏差、9、求和,10、求方差,11、求总体方差。 自动排序 =SUBTOTAL<3,$B$2: B2>*1 =IF 按奇偶数排序 我想请教怎样按奇数顺序然后再按偶数顺序排序 =IF =IF =ROW<>*2-1- 自动生成序号 比如在第二列中输入容回车后第一列的下一行自动生成序列号。 =IF 如何自动标示A栏中的数字大小排序? =RANK $A$5> =RANK A> 如何设置自动排序 A列自动变成从小到大排列 B=SMALL A$28,ROW<1: 1>> A列自动变成从大到小排列 B=LARGE A$28,ROW<1: 1>> 重复数据得到唯一的排位序列 想得到数据的出现总数吗〔{1,2,2,3,4,4,5}数据的出现总数为5〕? 解答: 不需要插列,不需要很多的函数就行了.=RANK B$12>+COUNTIF B3,B3>-1 按字符数量排序 制作歌曲清单时,习惯按字符数量来排列分类,但是EXCEL并不能直接按字数排序。 需要先计算出每首歌曲的字数,然后再进行排序。 如A、B列分别为"歌手"和"歌名",在C1输入"字数",在C2输入公式: =LEN 排序字母与数字的混合容 日常使用中,表格经常会有包含字母和数字混合的数据,对此类数据排序时,通常是先比较字母的大小,再比较数字的大小,但EXCEL是按照对字符进行逐位比较来排序的,如下表: A7排在第5位,而不是第1位。 排序结果无法令人满意。 A 1 A122 2 A29 3 A317 4 A43 5 A7 6 B20 7 B3 8 C144 9 C5 10 C33 A B 1 A7 A007 2 A29 A029 3 A43 A043 4 A122 A122 5 A317 A317 6 B3 B003 7 B20 B020 8 C5 C005 9 C33 C033 10 C144 C144 如果希望EXCEL改变排序的规则,需要将数据做一些改变。 在B1中输入公式: LEFT 单击B2,单击工具栏上的"升序排列"即可。 随机排序 如A、B列分别为"歌手"和"歌名",在C1输入"次序",在C2输入公式: =RAND〔〕,下拖,单击C2,单击工具栏上的"降序排列"即可对歌曲清单进行随机排序。 排序的问题 我想要这样的排序: 2001-2003 2004-2006 2007-2009 2010-2012; 其实不是数据排序,应该是数据填充。 输入公式=LEFT 怎样才能让数列自动加数 怎样做才能让数列自动加数 A A0001 B B0001 A A0002 C C0001 A A0003 B B0002 C C0002 公式为=A1&"000"&COUNTIF A1,A1>向下拖 =TEXT A1,A1>,"! "&A1&"0000">否则数字超过9就错误了。 一个排序问题 一个电子表格,格式
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 表格 函数 公式 大全