EXCEL公式应用.doc

在EXCEL中如何利用身份证号码计算出生年月年龄及性别1、身份证号码简介（18位）：16位为地区代码；710位为出生年份；1112位为出生月份；1314位为出生日期；1517位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。2、确定“出生日期”：18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。正确输入了身份证号码。(假设在D2单元格中)将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式“=MID(D2,7,4)&-&MID(D2,11,2)&-&MID(D2,13,2)”即可计算出“出生日期”。关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID（text,starl_num,num_chars）。Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。3、确定“年龄”： “出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“=INT(TODAY()-E2)/365)”即可计算出“年龄”。关于这个函数公式的具体说明：TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。操作格式是TODAY（）。用TODAY（）-E2，也就是用当前日期减去出生日期，就可以计算出这个人的出生天数。再除以 “365”减得到这个人的年龄。计算以后可能有多位小数，可以用【减少小数位数】按钮，将年龄的数值变成“整数”，也可在公式= (TODAY()-E2)/365中再嵌套一个 “INT”函数取整数，即“ =INT(TODAY()-E2)/365)”，这样就会自动将后面的小数去掉，只保留整数部分。4、确定“性别”：每个人的性别可以利用“身份证号码”进行判断，18位身份证号码中，第1517位为顺序号，奇数为男，偶数为女。将光标定位在“性别”单元格中，然后在单元格中输入函数公式“=IF(VALUE(MID(D2,15,3)/2=INT(VALUE(MID(D2,15,3)/2),女,男)”即可计算出“性别”。关于这个函数公式的具体说明：函数公式中，MID(D2,15,3)的含义是将身份证中的第1517位提取出来。VALUE(MID(D2,15,3)的含义是将提取出来的文本数字转换成能够计算的数值。VALUE(MID(D2,15,3)/2=INT(VALUE(MID(D2,15,3)/2)的含义是判断奇偶。(“INT”在上面说过是取整函数，如果是偶数，则前后相等；如果是奇数，则前后不相等。)=IF(VALUE(MID(D2,15,3)/2=INT(VALUE(MID(D2,15,3)/2),女,男)的含义是若是“偶数”就填写“女”，若是“奇数”就填写“男”。如何计算日期:=IF(A1-2008-1-1,B1*0.105,B1*0.095) A1的日期格式也应该为YYYY-MM-DD这种的格式 - 那么C列单元格显示“#value!的前面B列和A列的值是什么情况？你写一来那你用这个公式=IF(OR(A1=,B1=),IF(A1-2008-1-1,B1*0.105,B1*0.095)这个公式就是当A1或B1中有一个是空值的，C1就为空用EXCEL表如何通过参加工作日期和当前日期计算出工作时间:方法1：在A1单元格输入前面的日期，比如“2004-10-10”，在A2单元格输入后面的日期，如“2005-6-7”。接着单击A3单元格，输入公式“=DATEDIF(A1,A2,d)”。然后按下回车键，那么立刻就会得到两者的天数差“240”。 提示：公式中的A1和A2分别代表前后两个日期，顺序是不可以颠倒的。此外，DATEDIF函数是Excel中一个隐藏函数，在函数向导中看不到它，但有关Excel中日期显示和计算的几个问题悬赏分：100 - 解决时间：2010-3-9 16:20 1.要求我在单元格A中输入X后，单元格B中显示2010-3-X，也就是自动加上当前月份，且单元格C中显示2010-4-X，也就是下月的这一天，我设置单元格格式不管用，自动变成了1900-1-X2.如何计算两个日期之差？我用这个函数=DAYS360(A1,G5)，他是将一年当做360天来算的，这个很明显不准，我要求按照真实的年月日来算出真实的相差日期（把润平年都算进去）3.如何调用其他工作表中的数据？将其他工作表中的数据提取显示到一个总表中 问题补充：3的补充描述：假设有表一名叫总表表二名叫数据1，表三名叫数据2，表四名叫数据3等然后在表一的A2处显示表二的A1，A3出显示表三的A1，A4出显示表四的A1而无论表二三四的名字叫数据123或者是数值123或者任意的名字，都可以对应的显示出来提问者： lexer - 六级最佳答案1、单元格B中的公式为：=DATE(YEAR(TODAY(),MONTH(TODAY(),A1)单元格C中的公式为：=DATE(YEAR(TODAY(),MONTH(TODAY()+1,A1)如果你在单元格A中输入的数值大于一个月，系统会自动根据当前月份有的天数，计算出应该显示的日期。2、要显示真实的相差日期的单元格中的公式为：=G5-A1要注意的是，显示结果的单元格格式，要设置为“常规”（应该是常规，我的是日文，是标准，反正就是格式中的第一个）。默认格式为日期，结果是正数时，显示的会变成类似1900/1/3的内容。结果是负数时，不能正常显示。3、根据你的描述，我的理解是，你所存放的数据都在一个EXCEL中，只是不同的SHEET，这样的话，你在总表的A2中输入“=”然后用鼠标选中表二的“A1”，之后回车；在总表的A3中输入“=”然后用鼠标选中表三的“A1”之后回车，等等，以此类推。举个例子说：我新建一个EXCEL，默认有三个SHEET,我拿SHEET1来当总表，SHEET2、SHEET3都为数据表，我在SHEET1的A2中输入“=”然后用鼠标点SHEET2,然后用鼠标选中SHEET2的A1单元格，之后回车，这时，在SHEET1中的A2单元格中的公式为“=Sheet2!A1”，内容为SHEET2中A1单元格的内容。这时，我将SHEET2改名为数据1，你回发现SHEET1中A2单元格的显示内容没变。这时你查看一下A2单元格的公式，会发现公式变成了“=数据1!A1”。表三、表四的做法以此类推。以上例子可以看出，无论你的表名是什么，在设置好公式后，总表中的公式都会自动查找到对应的单元格，显示对应的单元格的内容。如果不能满足你的要求，或者你还有那些地方不明白，请再补充问题说明，我再来修改我的答案。 excel中知道两个日期的时间差跟终止时间，如何计算起始时间悬赏分：10 - 解决时间：2009-2-8 09:28 excel中知道两个日期的时间差跟终止时间，如何计算起始时间？如：时间差是37：28：45，终止时间是2009-2-20 12：10：05，如何计算起始日期时间？ 问题补充：EXCEL表格中到达时间跟行程时间的日期时间设置只能精确到分，如2009-2-10 12：18：15，后面的秒显示不出来，怎么调整才能到秒啊？谢谢提问者： 匿名最佳答案如果你的终止时间在A1,时间差在B1 那在C1输入 =TEXT(a1-b1,yyyy-mm-dd h:mm:ss) excel中知道两个日期的时间差跟终止日期，如何计算起始日期悬赏分：0 - 解决时间：2009-5-22 12:32 excel中知道两个日期的时间差跟终止日期，如何计算起始日期如A1是2018-11-1；B1是10。其中A1是终止日期，B1表示有效期10年。请问怎麼计算得到C1是2008-11-1 问题补充：非常谢谢!提问者： nc55555 - 一级最佳答案c1输入=date(year(a1)-b1,month(a1),day(a1) 在Excel中，单击某一个单元格让其背景色变为黄色的办法（这个办法可以在讲课时让别人能清楚地看到老师的鼠标正在位于哪一个单元格）在要实现这一功能的表中插入以下语言：Dim xPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If x Then With Range(x).Interior .ColorIndex = 0 End With End If x = Target.Address With Target.Interior .ColorIndex = 6 End WithEnd Sub要实现单击某单元格，让其字体颜色变为红色的方法同样是在要实现这一功能的表中插入以下语言：Dim xPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If x Then With Range(x).font .ColorIndex = 0 End With End If x = Target.Address With Target.font .ColorIndex = 6 End WithEnd Sub 二者的区别在于font与Interior，font是字体的意思，Interior是背景色的意思（好像是）。就这么点东西我琢磨了一下午，终于琢磨透了，这周末我要给我们同事进行Excel培训，掌握了这个方法后我就可以在讲Excel的时候讲到哪儿让哪个单元格的颜色变化了，有助于同学们跟着我的思路走，哈哈！ 附：要在整个工作簿中的各个工作表里都使用这个功能，需要把代码放在thisworkbook的Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)事件里-网上高手指点 用条件格式，选中所有日期单元格 设置条件格式today() 选择颜色 OK china811231 - 二级 2010-1-8 13:54 设置公式方式条件格式： =TODAY()=A1 提前三天变色： =（TODAY()-3）=A1 比如要判断A列数据，符合条件就在B列上变成红色，可选中A1，然后点菜单栏上的“格式”“条件格式”，“条件1”中选“公式”，在旁边输入： =A1=0 ，再点开“格式”选择你要的颜色，保存后将A1的格式用格式刷刷向其他单元格就就行了。请教各位大牛如何在Excel中If命令的基础上让相关的框根据结果变颜色悬赏分：30 - 解决时间：2008-10-9 23:42 在下有个问题如果在Excel中，让B81的数据框确定是否在-15，15的区间内如果在的话显