精华内容
下载资源
问答
  • 本书从函数功能、函数格式、参数说明、注意事项、Excel 版本提醒、案例应用、交叉参考7 个方面,全面、细致地介绍了Excel 2016/2013/2010/2007/2003 中公式和函数的使用方法、实际应用和操作技巧。最后3 章还将公式...
  • excel 当前行突出显示 在Excel中突出显示当前月份的生日 (Highlight Current Month Birthdays in Excel) August seems to be a very popular birthday month among my Excel friends. I won't mention any names ...

    excel 当前行突出显示

    在Excel中突出显示当前月份的生日 (Highlight Current Month Birthdays in Excel)

    Highlight Current Month Birthdays in Excel

    August seems to be a very popular birthday month among my Excel friends. I won't mention any names here, because most of them are quite elderly, and the shock might upset them. 😉 Anyway, to all of them, and you, if you're celebrating this month -- happy birthday!

    在我的Excel朋友中,八月似乎是一个非常受欢迎的生日月份。 在这里我不会提及任何名字,因为它们中的大多数都是年纪较大的,并且震惊可能会使他们不高兴。 😉无论如何,对于所有人,以及您,如果您正在庆祝本月的生日快乐!

    生日清单 (The Birthday List)

    If you're in charge of an employee list, and have to send birthday greetings, or hiring date anniversary congratulations, you can use Excel to help you keep track.

    如果您负责员工名单,并且必须发送生日问候或雇用日期周年祝贺,则可以使用Excel来帮助您跟踪。

    In the worksheet shown below, there are fake employee names and birth dates. In a separate column you could use the MONTH function to check which birth dates are in the current month.

    在下面显示的工作表中,有假的员工姓名和生日。 在单独的列中,您可以使用MONTH函数检查当月的出生日期。

    This formula compares the month of the date in column C, to the month of today's date. If the months are the same, the result is TRUE. =MONTH(C2)=MONTH(TODAY())

    此公式将C列中日期的月份与今天日期的月份进行比较。 如果月份相同,则结果为TRUE。 = MONTH(C2)= MONTH(TODAY())

    condformatbirthmonth00

    使用条件格式突出显示生日 (Highlight Birthdays with Conditional Formatting)

    Instead of adding another column with formulas, you could use conditional formatting to highlight the current month's dates in column C. To highlight the dates:

    您可以使用条件格式在C列中突出显示当前月份的日期,而不是在公式中添加另一列。要突出显示日期,请执行以下操作:

    • Select all the dates in column C. In this example, cell C2 is the active cell.

      选择列C中的所有日期。在此示例中,单元格C2是活动单元格。
    • On the Ribbon's Home tab, click Conditional Formatting

      在功能区的“主页”选项卡上,单击“条件格式”
    • Click New Rule

      点击新规则
    • In the New Formatting Rule dialog box, click on Use a Formula to Determine Which Cells to Format

      在“新格式设置规则”对话框中,单击“使用公式确定要格式化的单元格”
    • =MONTH(C2)=MONTH(TODAY())

      = MONTH(C2)= MONTH(TODAY())

    • Click the Format button, and select the formatting you want for the highlighted cell. In this example, the cells will be filled with light blue.

      单击格式按钮,然后为突出显示的单元格选择所需的格式。 在此示例中,单元格将用浅蓝色填充。

    condformatbirthmonth01

    • Click OK to apply the conditional formatting.

      单击确定以应用条件格式。

    发现生日 (Spot the Birthdays)

    On the worksheet, all the birthdays from the current month are highlighted with the formatting that you selected. When you open the file at the start of each month, you'll quickly spot all the fake employees who are celebrating their birthdays.

    在工作表上,以您选择的格式突出显示当月的所有生日。 在每个月初打开文件时,您会Swift发现所有正在庆祝生日的假员工。

    翻译自: https://contexturesblog.com/archives/2010/08/06/highlight-current-month-birthdays-in-excel/

    excel 当前行突出显示

    展开全文
  • Excel里面自动获取当前时间

    千次阅读 2019-10-15 10:34:41
    最近在做API的测试,URL增加了新的日期参数,本来应该指向当前月份,可是上个月的数据要在当月20号才有(9月的数据要在10月20号才有结果),现在10月就需要用到8月的数据。 临时解决方案是告诉客户在URL里面加个参数...

    最近在做API的测试,URL增加了新的日期参数,本来应该指向当前月份,可是上个月的数据要在当月20号才有(9月的数据要在10月20号才有结果),现在10月就需要用到8月的数据。

    临时解决方案是告诉客户在URL里面加个参数:date=2019-8

    对应的API测试也要加个时间参数,但是不想写死,每次改的麻烦。
    查了一下,Excel获取时间有以下用法:
    获取上两个月时间:=TEXT(EDATE(NOW(),-2),“yyyy-m”) -->2019-8

    1、Excel如何显示当前日期函数公式:

    显示当前年份函数:【=YEAR(NOW())】–>2019

    显示当前月份函数:【=MONTH(NOW())】 -->10

    显示当前日期函数:【=DAY((NOW()))】–>14

    显示当前星期函数:【=TEXT(NOW(),“aaaa”)】–>Monday

    显示当前准确时间:【=TEXT(NOW(),“hh:mm:ss”)】–>19:13:51

    显示当前年月日:【=TODAY()】–>10/14/2019

    显示当前年月日和时间:【=NOW()】

    使用方法:直接在某单元格输入以上函数公式即可获得结果。

    2、Excel如何通过日期判断星期几:

    通过日期显示星期几:【=WEEKDAY(A2,2)】

    通过日期显示中文带星期:【=TEXT(A2,“aaaa”)】

    通过日期计算中文星期:【=TEXT(A4,“aaa”)】

    通过日期计算英文星期简写:【=TEXT(A5,“ddd”)】

    通过日期计算英文星期:【=TEXT(A5,“dddd”)】

    使用方法:指定输入日期的单元格,输入以上函数公式即可获取星期。

    3、Excel如何求月份天数函数:

    求某日期的月份天数:【=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))】 使用方法:需要指定包含日期的单元格。

    直接求当前月份天数:【=DAY(EOMONTH(NOW(),0))】 使用方法:直接在某单元格输入以上公式即可获取天数。

    求当前月份天数减去周六和周日:【=SUMPRODUCT(–(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))】 使用方法:直接在某单元格输入以上公式即可获取天数。

    4、Excel快捷键快速获取日期时间:

    当然,在有些情况下,我们可以直接通过Excel快捷键来获取当前的日期和时间。

    获取当前年月日快捷键:【Ctrl+;】

    获取当前时间快捷键:【Ctrl+Shift+;】

    获取年月日和时间:先在单元格使用【Ctrl+;】,然后空格在使用快捷键【Ctrl+Shift+;】

    另外

    来自微软:日期和时间函数(参考)

    文章参考Word联盟

    读取Excel时遇到的问题

    然而在使用Java去读取数据的时候,发现返回的就是那个函数公式,而不是直接返回函数公式获取到的日期,经过一番研究,原来是需要对Excel里面的内容格式进行判断并进一步的处理一下:
    器里面的内容有如下几种类型:
    Excel里面的内容类型

    解决方法:

    在写读取的方法时尽量考虑到加一个判断,对每一种类型进行判断。
    比如内容是函数的话,需要这样子来处理才能得到函数对应的内容,不然就直接返回函数公式本身。

        	}else if(cell.getCellType()==Cell.CELL_TYPE_FORMULA) {
        		inputValue=cell.getStringCellValue().toString().trim();
        	}
    
    展开全文
  • Excel常用公式与函数总结(Excel 2010)

    千次阅读 2019-02-10 21:53:56
     在Excel中输入函数时是不区分大小写的,无论是大写还是小写,结束函数编辑时,Excel会自动转化为大写。对于函数中的参数,当参数是文本时,要用双引号引起来,单纯的数字不需要加双引号,除非这个数字是文本性数字...

    基本说明:

        在Excel中输入函数时是不区分大小写的,无论是大写还是小写,结束函数编辑时,Excel会自动转化为大写。对于函数中的参数,当参数是文本时,要用双引号引起来,单纯的数字不需要加双引号,除非这个数字是文本性数字;如果数字前面有比较运算符,也要加双引号。此外,在编辑公式与函数时,函数中的标点符号都是英文状态下的标点符号。

        示例:IF(A2>100,1000,"无奖金")、 COUNTIF(B:B,">10")。

        在Excel中输入公式与函数时,若函数只有一对括号可以不用输入右括号,若函数有多对括号,则要输入所有的括号。

        1)逻辑函数

    在逻辑判断中,0表示FALSE,所有非零数值表示TRUE。

    # IF函数:

    IF(logical_test,[value_if_true],[value_if_false]),IF函数执行真假值判断,并根据逻辑测试值返回不同的结果:如果逻辑测试值的结果为TRUE,IF函数将返回第二个参数值,如果逻辑测试值的结果为FALSE,IF函数将返回第三个参数值。

    ##// 注意IF函数的嵌套使用:

    如 IF(F2>=4,5000,IF(F2>=2,3000,0))或者是IF(F2<2,0,IF(F2<4,3000,5000)),前者与后者是等价的。

    补充说明:在Excel 2010中,IF函数最多可以嵌套64层。

     

    # AND()函数:

    AND(logical1,logical2,...),它的功能是判断多个条件是否同时成立:当所有参数的计算结果都为TRUE时,AND函数返回TRUE;当所有参数的计算结果中只要有一个为FALSE,则AND函数返回FALSE。

    示例:IF(AND(F2>=4,E2="A类"),5000,0)

     

    # OR()函数:

    OR(logical1,logical2,...),它的功能是判断多个条件中是否至少有一个成立:在其参数组中,任何一个参数逻辑值为TRUE,则OR函数返回TRUE;当所有参数的逻辑值都为FALSE时,OR函数才返回FALSE。

    示例:IF(OR(F2>=4,D2>=10),5000,0)

    ##// AND与OR函数的嵌套使用示例:IF(OR(F2>4,AND(E2="A类",D2>=10)),5000,0)

     

    # NOT()函数:

    NOT(logical),它是对逻辑值求反:如果输入参数的逻辑值为FALSE,NOT函数返回TRUE;如果输入参数的逻辑值为TRUE,NOT函数返回FALSE。

    补充说明:AND、OR、NOT这三个函数很少单独使用,一般都是与其它函数嵌套使用。

     

    # IFERROR()函数:

    IFERROR(value,value_if_error):它的功能是根据传入的参数是否是错误值返回指定的值。参数列表中,第一个参数表示传入的待判断的值(如果第一个参数value表示的值不是错误值时,IFERROR()函数就返回value),第二个参数表示当第一个参数表示的值为错误值时,IFERROE()函数应返回的结果。

     

        2)查找函数

    # VLOOKUP函数:

    VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]),它的功能是在区域或数组的第一列查找相应的数据,返回与指定值(查找区域第一列中与要查找的值匹配的值)同行的该区域或数组中相应列的值。参数列表中,第一个参数表示要查找的值,第二个参数表示要查找的区域,第三个参数是返回的值所在区域的列号,第四个参数是查找的类型。

    示例:VLOOKUP(B2,$E$2:$F$13,2,1)

    补充说明1:查找的区域要采用绝对引用(避免查找结果出错),或者是为要查找的数据区域命一个名,然后将要查找的区域换成它的名称(自定义名称默认是绝对引用);使用VLOOKUP函数查询的时候只能在查询区域的第一列进行查询,也就是说只能用查找区域第一列中的数据与要查找的值进行匹配,从而查找出与要查找的值相对应的值。查找的类型分为模糊查找和精确查找,TRIUE/1表示模糊查找,FALSE/0表示精确查找,对于模糊查找,查找区域的第一列一定要是按升序排列的(避免查询结果出差),对于精确查找,查询区域的第一列可以是无序的。

    补充说明2:当要查找的值或者查询区域为数值型数据类型时,一般采用模糊查找;当要查找的值或者查询区域是姓名、证件号、编号等文本型数据时,一般采用精确查找。

     

    # HLOOKUP()函数:

    HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

    它的函数结构于VLOOKUP()函数完全一样的,只不过是把VLOOKUP()函数中的列换成行。

    示例:HLOOKUP(B2,$H$2:$S$3,2,1)

    ##// VLOOKUP()函数和HLOOKUP()函数的比较:VLOOKUP中的V是Vertical首字母,意为垂直的,VLOOKUP函数可以理解为按列查找;HLOOKUP中的H是Horizontal的首字母,意为水平的,HLOOKUP函数可以理解为按行查找。

    ##// 注意VLOOKUP()函数的嵌套使用。

     

    # Match()函数:

    Match(lookup_value,lookup_array,[match_type]):它的功能是在查找区域中搜索指定项,并返回该项在查找区域中的位置,这个位置可以是查找区域的行号也可以是列号。参数列表中,第一个参数表示要查找的值,第二个参数表示查找的区域,第三个参数表示查找的类型。

    补充说明:查找的区域一般采用绝对引用,而且查找的区域只能是一行或者是一列,不能选择多行或多列;查找的类型有三种:1或者省略表示模糊查找,查找小于或等于lookup_value的最大值,如果找不到要查找的值,就返回小于查找值的最大值的位置,这种情况的前提是查找的区域是按照升序排列的;0表示精确查找,此时查找的与区域可以是无序的;-1也表示模糊查找,查找大于或等于lookup_value的最小值,如果找不到要查找的值,就返回大于查找值的最小值的位置,这种情况的前提是查找的区域是按照降序排列的。

     

    # INDEX()函数:

    INDEX(array,row_num,[column_num]):它的的功能是根据输入参数返回指定位置(包括要查找的值所在区域的行号、要查找的值所在区域的列号)的值。参数列表中,第一个参数表示要查找的区域,第二个参数表示要查找值所在区域的行号,第三个参数表示要查找的值所在区域的列号。

    补充说明1:函数INDEX(array,row_num,[column_num])中当参数array所表示的数据区域只有一列时,第三个参数的值只能是0或1或省略,而且这三个值对函数最后的返回结果没有影响。

    补充说明2:当INDEX()函数MATCH()函数的嵌套使用时,如果要利用单元格的自动填充功能,需要把INDEX()函数和MATCH()函数的查找区域都设置为绝对引用。

    补充说明3:INDEX()函数还有另外一种用法,形如INDEX(reference,row_num,[column_num],[area_num]),它与INDEX(array,row_num,[column_num])的功能相同,只不过传入的数据区域变成非连续的数据区域。参数列表中,第一个参数表示传入的非连续数据区域,第二个参数表示要查找的值在指定数据区域内的行号,第三个参数表示要查找的值在指定数据区域内的列号,第四个参数表示要查找的值在第几个数据区域。其中reference参数所表示的非连续数据区域整体要用括号括起来,非连续数据内每个单独的区域之间要用","隔开。

    示例:非连续数据区域可以如下图所示

    则函数INDEX((B2:E3,B5:E7,B9:E12),2,1,3)表示要查找的值位于第三个数据区域中的第二行第一列,因此函数的返回结果是"Toyota"。

     

    # LOOKUP()函数:它的功能是根据输入参数返回要查找的指定值。

    向量形式:形如LOOKUP(lookup_value,lookup_vector,result_result),参数列表中,第一个参数表示要查找的值,第二个参数表示查找区域,而且查找区域必须为单行或单列数据,第三个参数表示返回的结果所在的区域,这个区域也必须是单行或单列的数据。

    补充说明:使用向量形式的LOOKUP()函数进行查找的时候,查找区域的数据必须是按照升序排列的。

    数组形式:形如LOOKUP(lookup_value,array),参数列表中,第一个参数表示要查找的值,第二个参数表示查找的区域,查找区域为多行多列数据也就是数组形式的区域。LOOKUP()的数组形式可以理解为在查找区域的首列进行查找,返回查找区域的最后一列中的相应值,这种情况的前提是查找区域的行数要大于列数。如果查找区域的列数大于行数,则查找的规则就变成在第一行查找,返回最后一行中的相应值。

    补充说明:使用数组形式的LOOKUP()函数进行查找的时候,查找区域第一列的数据必须是按照升序排列的。

     

    # CHOOSE()函数:

    CHOOSE(index_num,value1,[value2],[value3],...):它的功能是根据序号从数值参数列表中选择对应的内容。参数列表中,第一个参数就表示序号,第一个参数后面的所有参数组成一个数值参数列表。

    示例:把带有月份的日期转换呈相应的季度:CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4)。

     

        3)统计函数

    # count系列函数:

    count(range):它的功能是统计输入参数所表示的区域中包含的数值性数字的个数;

    counta():它的功能是统计输入参数所表示的区域中包含的非空值单元格的个数;

    countblank():它的功能是统计输入参数所表示的区域中包含的空白单元格的个数。

    补充说明:这三个函数很少单独使用,一般都是与其它函数嵌套使用。

     

    # 条件系列函数:

    条件计数函数countif(range,creteria):它的功能是根据判断条件统计条件区域内指定值的个数。参数列表中,第一个参数表示条件计数的区域,第二个参数表示判断的条件。

    多条件计数函数countifs(criteria_range1,criteria1,criteria_range2,criteria2,...)函数:它的功能是统计满足多个条件的相应记录的个数。参数列表中,第一个和第二个参数分别是条件区域1和判断条件1,第三个和第四个参数分别是条件区域2和判断条件2,以此类推······

    条件求和函数sumif(range,criteria,[sum_range]):它的功能是根据判断条件与条件区域的匹配关系求取求和区域中相应值的和。参数列表中,第一个参数是条件区域,第二个参数是判断条件,第三个参数是求和区域。

    补充说明:使用sumif()函数时要注意单元格地址的绝对引用与相对引用问题。

    条件求平均值函数averageif(range,criteria,[average_range]):它的函数结构与sumif()函数一样,只不过其功能由条件求和变成了条件求平均值。

    多条件求和函数sumifs(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...):它的功能是根据多个判断条件与条件区域的匹配关系求取求和区域中相应值的和。参数列表中,第一个参数表示求和区域,第二个和第三个参数分别表示条件区域1和判断条件1,第四个和第五个参数分别表示条件区域2和条件2,以此类推……

    补充说明:使用sumifs()函数时要注意单元格地址的绝对引用与相对引用问题。

    多条件求平均值函数averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...):它的函数结构与sumifs()函数一样,只不过其功能由多条件求和变成了多条件求平均值。

     

    # MEDIAN()函数:它的功能是求取一组数据中的中位数。当数据的个数是偶数个时,MEDIAN()函数求的中位数是该组数据中间两个数的平均值。求取中位数时,数据区域要按照升序或者降序排列好。

     

    # MODE()函数:它的功能是求取一组数据中的众数。

     

    # 排位系列函数:

    RANK(number,ref,[order])函数:它的功能是返回指定值在指定数据区域内的排位。参数列表中,第一个参数表示待排位的数字,第二个参数表示排位的数字区域,第三个参数表示排位的类型(0或者省略表示按照降序排位,1表示按照升序排位)。

    RANK.EQ(number,ref,[order])函数:与RANK(number,ref,[order])函数完全相同。

    RANK.AVG(number,ref,[order])函数:返回结果与前面两个函数稍微有些不同(具体可以通过实际操作观察)。

     

    # LARGE(array,k)函数:它的功能是返回一组数据中第k大的值。参数列表中第一个参数表示数据区域,第二个参数表示要求的数据区域中第几大的值。

     

    # SMALL(array,k)函数:它的函数结构与LARGE(array,k)函数相同,只不过是其功能由求一组数据中低级大的值变成了求一组数据中第几小的值。

     

        4)引用函数

    # OFFSET()函数:

    OFFSET(reference,rows,cols,[height],[width]):它的功能是根据给定的偏移量返回新的引用区域。参数列表中,第一个参数是参照点,第二个参数是行偏移量,第三个参数是列偏移量,第四个参数是可选参数(可选参数是被方括号"[]"括起来的),表示返回的引用区域的行数,第五个参数也是可选参数,表示返回的引用区域的列数。

    补充说明:参数列表中的数值性参数也可以是负数,负数表示朝相反的方向引用。

     

    # INDIRECT()函数:

    INDIRECT(ref_text,[a1]):它的功能是返回一个由文本字符串指定的引用。参数列表中,第一个参数表示引用的文本,第二个参数为可选参数表示引用的类型(实际工作中一般第二个参数都省略)。

    补充说明:在一个工作簿内部跨工作表引用另外一张表中某个单元格的方式是,在单元格内输入

    ="被引用工作表的名称!被引用单元格的地址"即可。

    示例:如在同一个工作簿的汇总表中引用2009年度这张工作表里面的B8单元格,可以在指定位置的单元格里面输入="2009年度!B8"。

     

    # ROW()系列函数:

    ROW()函数里面可以有参数也可以无参数,没有参数就返回公式所在单元格的行号,有参数时,如果输入参数是某个单元格的引用,则ROW(reference)返回该引用单元格所在的行号。如果要引用整行,例如引用第一行可以写成ROW(1:1)或者写完ROW( 后点击第一行的行号。

    # COLUMN()函数:它的函数结构与ROW()函数完全相同,只不过其功能由返回行号变成返回列号。

    # ROWS([array])函数:它的功能是返回输入参数所占有的行数;

    # COLUMNS([array])函数:它的功能是返回输入参数所占有的列数。

     

    5)数学函数

    # ROUND系列函数:

    ROUND(number,number_digits):它的功能是返回对传入数值按指定位数进行四舍五入后的值。参数列表中第一个参数是待四舍五入的数字,第二个参数是四舍五入后要保留的小数位数(第二个参数还可以是负数,例如,第二个参数如果是-2的话,ROUND()函数返回的值就是四舍五入到百位的数。如ROUND(234.12,-2)=200。

    补充说明:对于Excel单元格中的数值x,点击增加或减少数值小数位数的按钮来改变数值x后的小数位数,这仅仅改变了数值x显示的方式,并没有改变数值x的实际值。

    ROUNDUP(number,num_digits):它的功能是对传入的数值按照指定的位数进行向上舍入。参数列表中,第一个参数表示待四舍五入的数,第二个参数表示四舍五入后要保留的小数位数。(第二个参数同样可以是负数,例如,第二个参数如果是-2的话,ROUND()函数返回的值就是四舍五入到百位数)。如ROUNDUP(34.234,2)=34.24。

    ROUNDDOWN(number,num_digits):它的函数结构与ROUNDUP()函数完全相同,只不过其功能变成对传入的数值按照指定的位数进行向下舍入。

     

    # 取整函数:

    INT(number):它的功能是返回对传入数值取整后的值。如果传入的值是整数,则INT()函数进行正常的取整,如果传入的数值是负数,INT()函数则会先取传入数值的绝对值,再对绝对值进行向上取整。如,INT(-11.6)=12。

    TRUNC(number,[num_digits]):如果没有给TRUNC()函数传入可选参数num_digits,TRUNC()函数会将传入数值的小数部分截去,并返回相应的值(被截去的小数部分可能会补0);如果给TRUNC()函数传入了可选参数num_digits,TRUNC()函数会按照传入参数num_digits指定的保留位数截去传入数值的小数部分(被截去的小数部分可能会补0)。

    示例1:TRUNC(1025.5685)=1025.0000

    示例2:TRUNC(1025.5685,2)=1025.5600

    补充说明:TRUNC()函数的可选参数num_digits可以是负数。例如TRUNC(234.13,-1)=230.00。

     

    # MOD()函数:

    MOD(number,divisor):它的功能是返回两数相除后的余数。参数列表中,第一个参数是被除数,第二个参数是除数。

     

    # RAND()函数:

    RAND():RAND()函数没有参数,它的功能是返回范围在0~1之内的随机数。

     

    # RANDBETWEEN()函数:

    RANDBETWEEN(bottom,top):它的功能是返回两个指定的数之间的一个随机整数。参数列表中,第一个参数是要返回的最小值(返回结果可以取到),第二个参数是要返回的最大值(返回结果也可以取到)。

    补充说明:RANDBETWEEN()函数还可以返回两个指定日期之间的日期

     

    # CONVERT()函数:

    CONVERT():它的功能是将传入的数值从一个度量系统转换到另一个度量系统。并返回转换后的值。参数列表中,第一个参数是传入的要转换单位数值,第二个参数转换前的某个度量系统里的单位,第二个参数是要转换的目标度量系统里的单位。

     

    6)日期时间函数

    # 日期与时间本质:在Excel中日期和时间本质上就是数值,只是外观显示成一种日期时间格式的样子。Excel规定1900年1月1日为1,以此类推。日期转换成数值后,它的整数部分表示日期,小数部分表示时间。

    # 在单元格中插入当前系统日期的快捷键:<Ctrl+;> ,插入当前时间的快捷键是<Ctrl+Shift+;>

     

    # TODAY()函数:

    TODAY():这个函数没有参数,其功能是返回当前系统的日期。而且它是一个动态函数,会随着工作表的更新而更新。

    # NOW()函数:它的函数结构与TODAY()函数完全一样,只不过其功能是返回当前系统的时间。

     

    # 提取日期系列函数:

    YEAR(serial_number):它的功能是提取并返回传入的日期参数中的年份。

    MONTH(serial_number):它的功能是提取并返回传入的日期参数中的月份。

    DAY(serial_number):它的功能是提取并返回传入的日期参数中的日。

    补充说明:与提取日期函数相对的是合并日期函数DATE(year,month,day),它可以将位于不同单元格的年、月、日合并到同一单元格中。

     

    # 提取时间系列函数:

    HOUR(serial_number):它的功能是提取并返回传入的时间参数中的小时。

    MINUTE(serial_number):它的功能是提取并返回传入的时间参数中的分钟。

    SECOND(serial_number):它的功能是提取并返回传入的时间参数中的秒。

    补充说明:与提取时间函数相对的是合并时间函数TIME(hour,minute,second),它可以将位于不同单元格的小时、分钟、秒合并到同一单元格中。

     

    # WEEKDAY()函数:

    WEEKDAY(serial_number,[return_type]):它的功能是将传入的具体的日期转化为相应的星期几,并返回转换后的值。参数列表中,第一个参数是待判断星期几的日期,第二个参数是可选参数,它返回的值与日期之间关系的类型(例如,第二个参数若为1或者省略,则返回值中1表示星期日,6表示星期天。第二个参数为其它值时的情况,具体可参见使用WEEKDAY()函数时Eexcel给出的参数提示)。

     

    # NTETWORKDAYS()函数:

    NETWORKDAYS(start_date,end_date,[holidays]):它的功能是计算并返回传入的两个指定日期之间所有的工作日。参数列表中,第一个参数表示开始日期,第二个参数表示结束日期,第三个参数为可选参数,表示要剔除的节假日。

     

    # WORKDAY()函数:

    WORKDAY(start_date,days,[holidays]):它的功能是计算并返回传入的指定日期向前(此时第二个参数为负数)或者向后(此时,第二个参数为正数)数个工作日后的日期。参数列表中,第一个参数表示开始日期,第二个参数表示工作日(不含星期六和星期天)的天数,第三个参数为可选参数,表示要剔除的节假日。

     

    # EOMONTH()函数:

    EOMONTH(start_date,months):它的功能是返回传入的指定日期向前(此时第二个参数为负数)或者向后(此时第二个参数为正数)几个月的那个月的最后一天日期。参数列表中,第一个参数表示开始日期,第二个参数表示向前或者向后的月数(例如,如果第二个参数的值为0,则表示函数返回指定日期当月的最后一天的日期)。

    示例1:EOMONTH("2018-09-07",0)="2018-09-30"

    示例2:EOMONTH("2018-09-07",-1)="2018-08-31"

     

    # EDATE()函数:

    EDATE(start_date,months):它的功能是返回传入的指定日期向前或者向后几个月的日期。参数列表中,第一个参数表示开始日期,第二个参数表示向前或者向后的月数。

    示例:EDATE("2018-09-07",2)="2018-11-07"

     

    # DATEDIF()函数:

    DATEDIF(start_date,end_date,unit):它的功能是计算并返回传入的两个指定日期的之间的间隔。参数列表中,第一个参数表示开始日期,第二个参数表示结束日期,第三个参数表示两个指定日期之间间隔时间的单位。

    补充说明:第三个参数可以分别为:y表示时间间隔以年为单位;m表示时间间隔以月为单位;d表示时间间隔以天为单位;ym表示时间间隔是忽略传入的两个指定日期的年份后它们之间相差的月数;yd表示时间间隔是忽略传入的两个指定日期的年份后它们之间的天数差;md表示时间间隔是忽略传入的两个指定日期的年份和月份后它们之间的天数差。

    示例1:DATEDIF("2010-08-06","2013-05-22","y")=2

    示例2:DATEDIF("2013-08-06","2013-11-15","m")=3

    示例3:DATEDIF("2012-08-15","2013-09-04","d")=385

    示例4:DATEDIF("2008-04-05","2013-05-11","ym")=1

    示例5:DATEDIF("2012-03-21","2013-03-25","yd")=189

    示例6:DATEDIF("2011-12-19","2013-03-25","md")=6

     

    7)文本函数

    # LEN()函数:

    LEN(text):它的功能是返回传入的文本字符串中的字符数。

     

    # LENB()函数:

    LENB(text):它的功能是返回传入的文本字符串中字符占用的字节数。

     

    # FIND()函数:

    FIND(find_text,within_text,[start_num]):它的功能是查找指定字符(区分大小写)在指定的查找文本中第一次出现的位置,并返回查找结果。参数列表中,第一个参数表示要查找的字符(字符串),第二个参数表示查找的区域(在那个文本中查找),第三个参数为可选参数表示查找开始的位置(字符串中的位置从1开始计数,当第三个参数省略时,默认从字符串中的位置1开始查找)。

     

    # SEARCH()函数:

    SEARCH(find_text,within_text,[start_num]):它的函数结构和FIND()函数完全一样,只不过查找时不区分大小写。

     

    # MID()函数:

    MID(text,start_num,num_chars):它的功能是从传入文本的指定位置,提取并返回指定个数的字符。参数列表中,第一个参数表示要从中提取字符的文本,第二个参数表示要提取字符的开始位置,第三个参数表示要提取字符的个数。

    补充说明:FIND()函数可以和MID()函数嵌套使用,用于提取文本中指定的字符。

     

    # LEFT()函数:

    LEFT(text,[num_chars]):它的功能是从输入文本的左侧提取并返回指定个数的字符。参数列表中,第一个参数表示要提取字符的文本,第二个参数为可选参数,表示要提取的字符个数。

     

    # RIGHT()函数:

    RIGHT(text,[num_chars]):它的函数结构和LEFT()函数完全一样,只不过是从传入文本的右侧提取字符。

     

    # TRIM()函数:

    TRIM(text):它的功能是将传入文本前后的空格和中间多余的空格删除,但仍会在文本中间保留一个空格。

    补充说明:要想清除文本中的所有空格,我们可以用替换的方法,将文本中的所有空格替换成""。

     

    # CLREAN()函数:

    CLWEAN(text):它的功能是清除传入的文本中的非打印字符。

     

    # CONCATENATE()函数:

    CONCATENATE(text1,[text2],...):它的功能时将传入的多个文本合并并返回合并后的结果。

    补充说明:一般情况下,要完成相同的连接目标,使用连字符"&"比CONCATENATE()函数要简单。

     

    # 处理英文的函数:

    PROPER(text):它的功能时将传入文本中每个单词的首字母转换成大写,并返回转换后的结果。

    UPPER(text):它的功能是将传入文本中的字母全部转换成大写,并返回转换后的结果。

    LOWER(text):它的功能是将传入文本中的字母全部转换成小写,并返回转换后的结果。

     

    # 文本替换函数REPLACE()和SUBSTITUTE():

    REPLACE(old_text,start_num,num_chars,new_text):它的功能是以字符为单位,根据指定的位置对传入的文本参数进行替换,并返回替换后的结果。参数列表中,第一个参数表示要在其中替换字符的文本,第二个参数表示替换的起始位置,第三个参数表示要替换的字符个数,第四个参数表示要替换成什么样的文本。

    示例1:REPLACE("2018-RR-09",5,1,"")="2018RR-09"

    示例2:REPLACE("2018-RR-09",5,2,"OO")="2018OOR-09"

    示例3:REPLACE("2018-RR-09",5,2,"O")="2018OR-09"

     

    SUBSTITUTE(text,old_text,new_text,[instance_num]):它的功能是根据指定的文本对传入的文本参数进行替换,并返回替换后的结果。参数列表中,第一个参数表示要在其中替换字符的文本,第二个参数表示要替换掉的文本,第三个参数表示替换后的新文本,第四个参数为可选参数表示替换第几次出现的文本(第四个参数如果省略,则默认替换text中出现的所有形如old_text的文本)。

    示例1:SUBSTITUTE("2018-RR-09","-","",2)="2018-RR09"

    示例2:SUBSTITUTE("2018-RR-09","-R","X",1)="2018XR-09"

     

    # TEXT()函数:

    TEXT(value,format_text):它的功能是将传入的数字转换成按照指定格式显示的文本。参数列表中第一个参数表示要设置格式的数字,第二个参数表示格式设置的代码。

    示例:TEXT("4008008020","000-000-0000")=400-800-8020

     

    8)信息函数

    # ISTEXT()函数:

    ISTEXT(value):它的功能是判断传入的参数是否是文本,并返回判断结果。

     

    # ISNUMBER()函数:

    ISNUMBER(value):它的功能是判断传入的参数是否是数值,并返回判断结果。

     

    # ISNONTEXT()函数:

    ISNONTEXT(value):它的功能是判断传入的参数是否是非文本(数值和空单元格),并返回判断结果。

     

    # ISBLANK()函数:

    ISBLANK(value):它的功能是判断传入的参数是否是空单元格,并返回判断结果。

     

    # ISERROR()函数:

    ISERROR(value):它的功能是判断传入的参数是否是错误值(这个错误值包含Excel中所有可能的错误值),并返回判断结果。

     

    # IFERROR()函数:

    IFERROR(value,value_if_error):它的功能是判断传入的参数是否是错误值,并根据判断结果返回相应的值。若传入的参数不是错误值,则返回value代表的公式计算的结果,反之返回指定的代表错误值的结果。

     

    9)数组公式

    数组公式的应用场景:当公式与函数的返回结果有多个值,或者想对公式与函数的返回结果进行多项计算时,这个时候可以选择使用数组公式(用<Ctrl+SHift+enter>键确定输入)。

    示例1:{=C3:C15*D3:D15}

    它返回的是C3:C15与D3:D15对应单元格里值的乘积(一共有13个积)。因为它的返回结果有多个值,所以应该先选中多个单元格,然后再输入数组公式。

     

    示例2:{=SUM(C3:C15*D3:D15)},它返回的是C3:C15与D3:D15对应单元格里值的乘积之和。

    补充说明:上述两个示例中的{}并不是手动添加上去的,而是在向单元格中输入公式与函数完成后,按<Ctrl+SHift+enter>键时自动添加的。

     

    # FREQUENCY()函数:

    FREQUENCY(data_array,bins_array):它的功能是以垂直数组的形式返回传入的数据在指定的范围内出现的频数(可以理解为分组统计)。参数列表中,第一个参数表示传入的要分组统计的数据,第二个参数表示设置的频率分布的区间。

    补充说明:注意FREQUENCY()函数返回的结果是多个值,也可以说是一个数组。

    示例(图片中左边的数据只是其中的一部分):

     

    # TRANSPOSE()函数:

    TRANSPOSE(array):它的功能是对传入数组或者数据区域进行行列转置,并返回转置后的结果。TRANSPOSE()函数可以用于一维表和二维表之间的相互转换。

    补充说明1:TRANSPOSE()函数是要以数组公式的形式输入的,函数输入完成后要按<Ctrl+SHift+enter>组合键才能得到返回结果。

    补充说明2:要向实现转置的目的,除了使用TRANSPOSE()函数外,还可以使用Excel中的选择性粘贴功能。二者的区别是对于TRABSPOSE()函数实现的转置,原表与转换后的表之间的数据是联动(就是说转置后的表中的数据会随着原表中的数据的变动而变动)的;对于使用选择性粘贴功能实现转置,原表与转置后的表之间的数据不存在联动关系。

     

    # 将数组公式应用于查找中:

    此时应该注意单元格的相对引用与绝对引用问题。

     

    注:本文主要参考刘伟的视频教程《Excel 2010公式与函数》。

    PS:练习数据地址:https://pan.baidu.com/s/1uiM_D-_6Hq6pGZ7XTiSkeQ

    展开全文
  • 先来看下面这个表格,要计算从一月份开始,到当前月份的累计销量:C2单元格可以输入以下公式,然后下拉:=SUM($B$2:B2)这就是一个最基础的“半封闭”用法,其中的$B$2:B2部分,第一个B2使用了绝对引用,第二个B2使用...

    作者:祝洪忠 转自:Excel之家ExcelHome

    小伙伴们好啊,今天老祝和大家一起学习一个Excel函数公式中的常用技巧——锁头不锁尾。

    先来看下面这个表格,要计算从一月份开始,到当前月份的累计销量:

    f994dcaece00e34a6f0842fbea06fd2f.png

    C2单元格可以输入以下公式,然后下拉:

    =SUM($B$2:B2)

    442b00f9438bbc829c135640c88007c2.gif

    这就是一个最基础的“半封闭”用法,其中的$B$2:B2部分,第一个B2使用了绝对引用,第二个B2使用了相对引用,在公式下拉时会依次变成$B$2:B3、$B$2:B4、$B$2:B5……这样逐步扩大的求和范围。最后得到的结果,就是从B2单元格开始,到公式所在行的B列这个范围之和了。

    这种自动扩展的引用区域技巧,在日常公式中经常会用到,接下来咱们就列举几个有代表性的应用。

    1、判断数据是否重复出现

    如下图,要统计B列的姓名是否为重复出现。

    C2使用的公式为:

    =IF(COUNTIF($B$2:B2,B2)>1,"重复","")

    ba5a82e266467791a7b8fd6dcb6254f0.gif

    COUNTIF函数使用动态扩展的区域$B$2:B2作为统计范围,计算B列员工姓名在这个区域中出现的次数,如果出现的次数大于1,就是重复。

    以B2为例,令狐冲首次出现,C2单元格公式中的COUNTIF计算结果为1,表示该姓名在$B$2:B2这个区域中没有重复出现:

    =COUNTIF($B$2:B2,B2)

    而到了C8单元格,COUNTIF公式的引用区域变化为$B$2:B8:

    =COUNTIF($B$2:B8,B8)

    在$B$2:B8这个区域中,令狐冲出现了两次,也就是说B8是重复出现的。

    2、按部门添加序号

    如下图,要根据B列的部门填写序号,每个部门都要从1开始排序。

    A2单元格公式为:

    =B2&-COUNTIF($B$2:B2,B2)

    f2d7b5a4c45226bf80818d5145a2903e.gif

    这个公式中,COUNTIF函数以$B$2:B2作为动态扩展的统计区域,计算B列的部门出现的次数。

    如果该部门是首次出现,结果就是1,如果是第二次出现,结果就是2……

    最终的统计结果,就可以看做是部门的序号了。

    3、不允许录入重复数据

    如果把COUNTIF函数的这种用法,与数据验证功能相结合,就可以实现拒绝录入重复数据。如果要输入大量的员工姓名,这种方法特别实用。

    ddb2e6196880b75a9667bf862b9a26ce.gif

    数据验证中的公式为:

    =COUNTIF($D$2:D2,D2)=1

    数据验证可以根据预先指定的条件,对输入的内容进行自动判断,拒绝不符合条件的内容输入。

    实际使用的时候,公式中的D2需要换成实际选中数据区域的首个单元格,比如你选中的区域是A2:A20,公式就写成:

    =COUNTIF($A$2:A2,A2)=1

    4、必须连续输入,不允许有空单元格

    使用数据验证功能,还可以限制必须连续输入。如果输入的不完整或是输入后又删除了记录,Excel就不允许在下面继续输入了:

    82804218c854a703c789164a8937f195.gif

    数据验证的公式为

    =COUNTBLANK($D$2:D2)=0

    COUNTBLANK用于统计数据范围中空单元格的个数。这里约束的条件就是空单元格数量为0。

    同样,使用的时候要注意把公式中的D2换成你所选区域的活动单元格地址。

    好了,今天咱们分享的内容就是这些吧,祝各位小伙伴一天好心情!

    图文制作:祝洪忠

    展开全文
  • Excel函数公式大全以及用法

    万次阅读 多人点赞 2018-09-07 10:25:23
    首先得说明下excel如何输入函数公式的方法方法是:单击a4单元格(a4单元格的位置是第A列,第4行交叉的那个单元格,也即是我们彩色实例的那个位置),在键盘上输入 =a1+a2+a3 ,然后按键盘的enter键(回车键)即可...
  • excel表格时间和日期应用 – Excel常用函数公式及技巧自动显示当前日期公式=YEAR(NOW()) 当前年=MONTH(NOW()) 当前月=DAY((NOW())) 当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=WEEKDAY(A2,2...
  • 营长说之前一位做互联网金融的MM咨询Excel的问题,就是Excel如何根据起止日期自动计算每个月的计息天数?我觉得很有代表性,就拿出来和各位分享。按照惯例,我们先看具体案例问题,如下图。问题:根据购买日期和期限...
  • 数据分析最常用的excel函数公式大全

    千次阅读 2021-04-15 09:28:31
    一、数学函数 1.取整 =INT(数字) 2.求余数 =MOD(除数,被除数) 3.四舍五入 =ROUND(数字,保留...水平方向查找 =HLOOKUP(查找目标,查找范围,返回值的行数,精确OR模糊查找) 推荐学习课程:纵横职场必备Excel秘技100招
  • Excel公式大全【300函数】

    万次阅读 多人点赞 2020-10-23 14:56:52
    Excel公式大全一、逻辑函数二、日期函数三、查找和引用函数四、文本函数五、数学函数六、统计函数描述统计推断统计七、数据库函数七、三角函数八、信息函数 一、逻辑函数 序列 函数 解释 语法 备注 1 and 与...
  • Excel日期函数 返回下个月末公式: DATE(YEAR(A1),MONTH(A1)+2,1)-1
  • excel中计算表格中公式Excel中计算年度成本和节省 (Calculate Annual Costs and Savings in Excel) This workbook shows how to calculate annual costs and savings in Excel. It’s amazing how all those ...
  • 一、Excel公式使用准则 Excel如何输入函数公式的方法是:单击a4单元格(a4单元格的位置是第A列,第4行交叉的那个单元格,也即是我们彩色实例的那个位置),在键盘上输入 =a1+a2+a3 ,然后按键盘的enter键(回车键)...
  • excel公式

    2021-07-26 21:39:33
    每个参数可以使区域、单元格引用、数组、常数、公式或另一个函数的结果 2、SUMIF条件求和函数 SUMIF(区域,条件,求和区域) 如:=SUMIF(B2:B25,”>5”)让数列B2到B25中大于5的数求和 如:=SUMIF(C2:C10,”女...
  • excel公式和函数

    2021-05-18 14:06:42
    1.excel公式 excel中先说行,在说列 (因为列少,可以快速定位) 行是abcd 列是1234 函数和公式在输入的时候,会有提示,按table选中,而不是空格 1.公式的基本使用 选择单元格 键入等号“=”。(注意: Excel 中的公式...
  • Excel中,日期是可以参与计算的,比如我们工作中计算星期、年龄、工龄、账龄、利息,以及复杂一点的汇总某个时间段的数据等等...1、获取当前系统日期公式:=TODAY()TODAY()函数返回系统当前日期,函数不需要参数。...
  • 根据身份证计算年龄,并且出生月份大于当前月份,则年龄加1 =IF(MONTH(DATE(MID(D1,7,4),MID(D1,11,2),MID(D1,13,2))) < MONTH(NOW()), YEAR(NOW())-MID(D1,7,4), YEAR(NOW())-MID(D1,7,4)+1) 根据短日期计算...
  • 使用公式时碰到了一个问题,那就是公式明明已经编辑好了,但是在单元格里不生效,直接把公式显示出来了,网上资料说有4种原因,但是我4种都不是,是第5种原因,如下图: 这种情况是由于单元格格式不是常规导致的,...
  • Excel常用公式

    2020-04-02 11:46:49
    输入公式的时候手速慢一点,多看公式的描述。 理解什么是相对引用和绝对引用! sum() 求和函数 average() 求平均值 max() 求最大值 min() 求最小值 median() 求中位数 mode() 求众数 large() 求第K个最大值 ...
  • 例如a1单元格的值是:aIEOWLC2009-5-3AEWDD2009-3...从a1单元格单元格中取日期,截止到月份,例如“2009-5”, 那么可以用公式 =MID(A1,FIND("200",A1,1),FIND("-",A1,FIND("200",A1,1)+5)-1-FIND("200",A1,1)+1)
  • 首先得说明下excel如何输入函数公式的方法方法是:单击a4单元格(a4单元格的位置是第A列,第4行交叉的那个单元格,也即是我们彩色实例的那个位置),在键盘上输入 =a1+a2+a3 ,然后按键盘的enter键(回车键)即可...
  • 如何利用Excel公式制作日历

    千次阅读 2020-03-11 17:15:09
    对控件设置格式 年份对应的控件依次设置:【当前值】:1900,【最小值】1900,【最大值】:9999,【步长】:1,【单元格链接】:$B$2 月份对应的控件依次设置:【当前值】:1,【最小值】1,【最大值】:12,【步长...
  • excel表格的函数公式大全

    千次阅读 2014-07-28 15:12:29
    这篇教程中将为你介绍excel表格的公式大全,能使你更好的使用excel!  1、ABS函数  函数名称:ABS  主要功能:求出相应数字的绝对值。  使用格式:ABS(number)  参数说明:number代表需要求...
  • EXCEL公式使用技巧

    2021-09-01 17:12:02
    EXCEL公式使用技巧便于日常使用日期的获取 便于日常使用 日期的获取 公式 效果 描述 NOW() 2021/9/1 当前日期,例如当前是2021年9月1日 TODAY() 2021/9/1 YEAR(TODAY())&MONTH(TODAY()) 20219 当...
  • EXCEL公式函数整理

    2020-11-17 10:40:37
    文本函数 UPPER 将一个字符串中所有小写字母转换为大写字母;...NOW 返回当前日期和时间,如图。 逻辑函数 IF(logical_test, value_if_true, value_ if_false)判断logical_test是否是真,如果真则返回值va
  • Excel 公式

    2019-09-24 15:31:54
    一、身份证相关 ...说明:COUNTIF函数在计算文本型数字时,会默认按数值型进行处理,但是Excel中的数字精度只有15位,并且身份证号码是18位,因此会把前15位相同的身份证全部识别为相同的内容。在第...
  • 第一步新建俩个excel:第一张名字是表一,第二张是表二;  第一张表里面的内容如下  第二张:第一列随便写。但为了对应,还是把工号号码写成一样。  如果我们要查一月的成绩   你点击一下的单元格,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,498
精华内容 599
关键字:

当前月份公式excel