学而不思则罔，思而不学则殆。

simple666 · 发表于 2010-6-23 09:58

跟着LRLX的帖继续学习

lrlxxqxa · 发表于 2010-6-29 15:18

内容概要（浓缩版）：

一.、EXCEL日期的常识

1）多种（输入）显示方式：2010年6月29日，2010-6-29,2010/6/29，会自动存储成日期序列值。
2）日期是特殊的数值格式，在excel的实际存储是数值。EXCEL的日期系统由 1900-1-1开始计算，以1900-1-1当天为 1，每增加一天，其日期序列号即增加1。
3）EXCEL不支持负值的日期或时间格式。小于1900年的输入日期格式则把它当成文本处理。

二、常用日期与时间函数

1、TODAY，NOW
属于易失性函数，其返回结果随当前系统时间而变化，不需要任何函数。

2、YEAR，MONTH，DAY，HOUR，MINUTE，SECOND
比较简单，常作为其它函数的参数参与计算，分别用于返回系统当前的年月日小时分秒等信息。

3、WEEKDAY
WEEKDAY(serial_number,return_type)

Serial_number  表示一个顺序的序列号，代表要查找的那一天的日期。输入的日期可以是引用单元格(A1)，日期的文本字符串("2010-5-6"),日期序列值（40330）或其他公式函数的结果。注意第二个参数的应用，符合中国习惯的 2平时输入不能省略。

4、DATE
DATE(year, month, day)
如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式，而不是数字格式。若要显示序列号或要更改日期格式，请在“开始”选项卡的“数字”组中选择其他数字格式。

5、time
TIME(hour,minute,second)
返回某一特定时间的小数值。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。
函数 TIME 返回的小数值为 0（零）到 0.99999999 之间的数值，代表从 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 之间的时间。
其用法同date函数，支持数值溢出。

6、DATEDIF
这是一个隐藏函数，用于计算两日期之间的间隔时间，其优点在于可自动对比前后日期的月份或天数的大小，从而自动计算“足齡”年份。其表达式为=DATEDIF(小日期，大日期，”代码”)
其中第三个参数“代码”总共有六种，功能分别如下：
Y       计算相隔的整年数
M    计算相隔的整月数
D       计算相隔的总天数
MD    计算同一月中两日期的天数差（若后日期小于前日期，则计算当月至次月两日期的天数差）
YM    计算同一年中两月份的月数差（若后月份小于前月份，则计算当年至次年两月份的月数差）
YD    计算同一年中两日期的天数差（若后月日小于前月日，则计算当年至次年两日期的天数差）

7、时间统计
1）在进行时间的统计运算时，超过24小时的时间会被软件自动减掉24小时。设置自定义单元格格式为［h］可获得正确统计结果。
2）在两时间相减出现负值时（如跨夜工时的计算），可通过IF函数或MOD函数得到正确结果。使用MOD函数，将24小时作为1取模，可以快速计算出正确工时。
=============================================================================
爬贴收获：关于隐藏函数datedif
datedif暗藏着不少玄机，引方版的帖子来学习

http://club.excelhome.net/viewth ... p;page=1#pid3025669
http://club.excelhome.net/viewth ... p;page=2#pid3018349
其中对于返回整月的研究
http://club.excelhome.net/viewthread.php?tid=165589

在多个版本中，Datedif函数的算法发生了改变，Excel2003 SP3、Excel2007 SP1、Excel2007 SP2 以及还未正式上市的Excel 2010中，这个函数的运算结果都有所不同。更早期的版本尚无研究。
工作表函数Datedif与VBA中的函数Datediff也不相同。

注意：方版的示例是以2007的sp2版本为基础说明的。
============================================================
工作应用：
（员工工龄以及错误日期修正）

一、在计算工龄中：

1、比较两种方法使用不同函数的异同

=DATEDIF(A2,NOW(),"Y")&"年"&DATEDIF(A2,NOW(),"Ym")&"月"&DATEDIF(A2,NOW(),"mD")&"天"

复制代码

=INT(ABS((TODAY()-A2)/365))&"年"&INT(ABS(MOD((TODAY()-A2),365)/30))&"个月"

复制代码

2、日期输入错误时，Excel默认为文本，公式报错，可结合下面的方法先修正再计算。

二、如何修正错误日期：

1、初次修正后还存在错误：

=IF(A2>"0",(LEFT(A2,LEN(A2)-2)&1)+RIGHT(A2,2)-1,A2)

复制代码

2、上公式经草版修正为如下数组公式，变得强大：

=MAX(IFERROR(DATE(LEFT(A2,4),MID(A2,6,{1;2}),RIGHT(A2,{1,2})),A2))

复制代码

注意理解mid的数组参数用法

三、工作表中的代码是一个自定义函数b]

目的还是为了返回正确的时间间隔。
在方版的精华中有类似功能，如=Datedif(A2,B2,"Y")的算法可以如下2公式代替并解释：
=YEAR(B2)-YEAR(A2)-1+(DATE(YEAR(B2),MONTH(A2),DAY(A2))<=B2)
或
=YEAR(B2)-YEAR(A2)-1+(A2<=DATE(YEAR(A2),MONTH(B2),DAY(B2)))

至于那些代码，目前看不懂，暂且搁置。

[ 本帖最后由 lrlxxqxa 于 2010-6-29 16:49 编辑 ]

lrlxxqxa · 发表于 2010-7-14 11:56

http://hi.baidu.com/dawin2046/bl ... 79360d3af3cf93.html

虽然不能听课，但半懂总比不懂好

dawin · 发表于 2010-7-14 14:34

呃。我也进楼层了。。
收藏+回帖，双重标记，找时间继续看函数。。

taxue616 · 发表于 2010-7-21 22:27

真厉害呀，向你学习！

lrlxxqxa · 发表于 2010-7-27 17:10

先说一下老朋友VLOOKUP吧。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
精确查找时顺序运算；模糊查找时类似二分法策略

1、写给对第4参数“省略”还是“简化”，模糊查找还是精确查找有疑惑的朋友

首先，第4参数为0或FALSE表示精确匹配；为<>0或TRUE或省略表示模糊匹配，要求升序排列，否则答案可能错误。注意使用模糊匹配就要进行排序；
其次，说下省略和简化的区别。省略第4参数是指写完第3参数后不写逗号（此时模糊匹配），如果写了逗号就是简化（此时精确匹配）

2、VLOOKUP在精确查找时支持通配符

VLOOKUP通配符.rar (3.33 KB, 下载次数: 49)

在第4参数为0或者FALSE时，第1参数可以使用"*"、"?"来通配；模糊查找时会把*和？当做文本而非通配符处理；

3、当需要按照多个并列条件查找时；

添加辅助列或者用"&"合并多条件后作为一个查询值

4、从右往左查询；

利用IF{1,0}或者choose函数构建相应的内存数组

5、第3参数可以根据引用列自动调整；

利用COLUMN函数进行调整，方便批量填充，如用COLUMN(),COLUMN(A:A)形式，如果需要偏移，在此基础上加减N即可，如COLUMN()+N

6、对查找结果出现#N/A值（引自apolloh）
为什么VLOOKUP或MATCH的查找结果出现#N/A值？ http://www.exceltip.net/thread-880-1-1-25594.html

精确查找时，查找值在查找范围列中不存在。
近似查找时，查找值小于查找范围列的所有值。
查找值与查找范围列的数据类型不匹配，如文本型数字和数值型数字，虽然外形相同，但实质却不同。
查找值看似存在于查找范围的数据中，但有一方含有不可见字符，如空格、换行符或其他无法显示的字符。

Excel 中有哪些错误值，分别是如何产生的？ http://www.exceltip.net/thread-332-1-1-25594.html

7、如果查找到多个匹配值，全部显示；

单纯利用VLOOKUP无法实现，需要用到数组公式

显示查找到的多个值.rar (11.42 KB, 下载次数: 51)

=INDEX($B:$B,SMALL(IF($A$1:$A$10=$C$1,ROW($1:$10),4^8),ROW(A1)))&""

复制代码

变换C1数值可看到效果。

8、用VLOOKUP构建内存数组

VLOOKUP构建内存数组.rar (3.78 KB, 下载次数: 35)

关键在于把握好第3参数，如附件中

=VLOOKUP("*",$A$1:$L$1,(ROW(1:6)-1)*2+TRANSPOSE(ROW(1:2)),)

复制代码

9、VLOOKUP返回的是查找值而非引用，此点与INDEX+MATCH组合区别

在引用动态图片的时候用INDIRECT和INDEX+MATCH实现，但用VLOOKUP则会报错，就是由此原因所致；

10、使VLOOKUP实现调用多表查询（用为成果展示吧）

vlookup多表调用2003.rar (18.45 KB, 下载次数: 44)

步骤1、定义名称：name

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW())

复制代码

步骤2：C2数组公式

=IF(ISERROR(VLOOKUP($B2,INDIRECT("'"&INDEX(name,MATCH(0,0/COUNTIF(INDIRECT("'"&name&"'!A:A"),$B2),))&"'!A:I"),2,)),"",VLOOKUP($B2,INDIRECT("'"&INDEX(name,MATCH(0,0/COUNTIF(INDIRECT("'"&name&"'!A:A"),$B2),))&"'!A:I"),2,))

复制代码

如果是2007可以用IFERROR:

=IFERROR(VLOOKUP($B2,INDIRECT("'"&INDEX(name,MATCH(0,0/COUNTIF(INDIRECT("'"&name&"'!A:A"),$B2),))&"'!A:I"),2,),"")

复制代码

我对excel工作表产生的多维空间的理解 http://www.exceltip.net/thread-9288-1-1-25594.html

[ 本帖最后由 lrlxxqxa 于 2010-7-27 23:51 编辑 ]

wangg913 · 发表于 2010-7-29 08:46

更新提醒，学习站位贴。

lrlxxqxa · 发表于 2010-7-29 13:11

小米写的。
address那里（第4页）我没写到，要借鉴

lrlxxqxa · 发表于 2010-7-29 13:13

原帖由 wangg913 于 2010-7-29 08:46 发表
更新提醒，学习站位贴。

呵呵，欢迎大头兄，多谢捧场！

laoshenji · 发表于 2010-7-29 17:59

谢谢楼主分享，拜读您的学习笔记如同听课一般！

		自动登录	找回密码
密码			免费注册

学而不思则罔，思而不学则殆。

跟着dawin学sql

函数II-1