index、vlookup，match
一、 match函数
返回查找值在数组或单元格区域的相对位置即索引号，即返回要查找的值在查找区域的第几个位置
语法：match(lookup_value,lookup_array,match_type)
//lookup_value需要查找的值，lookup_array要查找的数组范围或单元格区域，
//match_type为可选参数，表排列顺序，有3个值，1表升序，0表任意顺序，-1表降序
例1：=MATCH(45,A1:E1,1) //查找45在A1:E1这个区域第几位，如45在C1第三位就返回3
例2：=match(B2,$A$2:$A$17,0) //查找B2单元格的值在处在A2:A17这个区域那个位置
二、index函数
1. 连续引用语法：=index(区域，要引用单元格所在行，要引用单元格所在列)
例：=index(A2:C6,2,3) //表引用A2:C6这个区域中的第2行第3列单元格的值
//区域可理解为一个方框，如A2:C6，A2是方框左上单元格编号，C6是区域右下单元格编号
//这里2和3是指A2:C6这个区域的第2行第3行，不是整个工作表
2. 非连续引用语法：=index((区域1，区域2，区域3，区域N)，要引用数值在区域的行，要用数值所在区域的列，要引用的是第几区域)
例：=index((A2:C3,A5:C6),2,3,2) //表要引用第2个区域(这里第2区域是A5:C6)中第2行第3列的值
//多个区域间用逗号隔开
来自：http://jingyan.baidu.com/article/ae97a646ae4ea2bbfd461dfa.html
三、LOOKUP,vlookup及HLOOKUP函数
语法：vlookup(要查找的值，查找区域，要在区域的那列，false/ture)
//参数1为要查找的值， //参数2为查找区域，可看作一方框，用左上和右下单元格中间加冒号表示
//参数3表要在区域的那列查找，参数4的false表精确匹配，ture表近似匹配
例：=VLOOKUP(H3,$A$3:$F$19,5,FALSE) //使用精确匹配的方式在A3:F19这个区域的第1列查找值为H3的记录第5列的值
=vlookup("*"&F2&"*",$C$1:$D$8,2,0)
=VLOOKUP(A2,Sheet1!$A$1:$B$28,2,false) //在sheet1表中的A1与B28范围内查找其第一列值A2的记录，并返回该记录第2列的值
说明：查找范围可以按住鼠标左键拖动来自动生成要查找的范围值，默认生成的是相对值，可以选择这些值按F4转换成绝对值。
=LOOKUP($D2,test!$A:$A,test!B:B) //从test表的A列中查找值为D2的那行, 并输出那行后面的, 要拖动, 难描述, 但有时中文查不准
//注: LOOKUP函数查找的列$A:$A要排序, 否则可能出错, 即有的查不出来, 据说它是用二分法查找的, 不是逐一搜索
四、countifs函数——统计符合多个条件的记录的总数
对某一区域内满足多条件的单元格进行统计
语法：=countifs(第1条件区，第1条件，第2条件区，第2条件。。。第N条件区，第N条件)
例：求9月上半月上海发货平台的发货单数，
=COUNTIFS(A2:A13,"上海发货平台",B2:B13,"<2014-9-16") //因为是上半月所以是9月16号前
=countifs(c:c,">60",d:d,">60",e:e,">=130") //统计C和D列中值大于60，且E列中值大于或等130的数目
=countif(f:f,"缺考") //统计F列中值为缺考的总数
=COUNTIFS(A:A,"=罗宇能",B:B,"=2015/4/13") //统计A列中值等于罗宇能，B列值等于2015/4/13的总数
=COUNTIFS(Sheet2!$A:$A, $C4, Sheet2!$D:$D, P$3)
//统计A例中的值等于C4单元格的值并且D列中值等P3单元格的值的数，加$表决对，不对因为拖动而改变值
五、IF函数
=IF(A1>0,"★","●") //判断如果A1的值大于0输入五星★，否则输入圆点●
=IF(COUNTIFS(Sheet2!$A:$A,$C4,Sheet2!$D:$D,P$3)>0,"√","×")
=IF(COUNTIFS([cj4w.xls]Sheet!$B:$B,$C4,[cj4w.xls]Sheet!$D:$D,D$3)>0,"√","×")
//对cj4w.xls文件sheet表中B列值等当前C4值并且D列值等当前D3值的记录进行统计，结果大于0时打勾，否则打叉
=IF(COUNTIFS(Sheet1!$A:$A,"*"&$C4&"*",Sheet1!$A:$A,"*"&"201605"&E$3&"*"))>0,"√","×") //双引号中的固定文本，&是连字答，相当于delphi的+
六、$与&符号
1. $是绝对引用符号，加在单元格行序号或列序号前，加后行序号或列序号值不随拖动而改变
例1：=IF(A1>0,"★","●")，这里的A1横拖到下一单元格变B1，竖拖下一单元格变A2
例2：=IF($A1>0,"★","●")，横拖这个表达式A值不变，竖拖会变，因为1前没有加绝对引用符号$
例2：=IF($A$1>0,"★","●")，随便怎么拖，该表达值不变，都是判断A1单元格的值，这样A和1都不变
2. &是连接符号，作用同concatenate(text1,text2,...textN)函数
用以把两个单元格的值连在一起，相当于delphi的+，PHP的点.的作用，结果同=concatenate(A1,B1)
如A1值为111，B1值为222，A1&B1值为111222
七, LEFT和RIGHT函数 获取单元格上前及后N个字符
=left(a1,5) //从a1单元格中获取前5个字符
=RIGHT(a1,5) //从a1单元格获取最后5个字符
=left(A1, find("：",A1, 1)-1) //从A1单元格中获取冒号之前的内容, find("：",A1, 1)为得到冒号的位置
八, OFFSET获取指定单元格开始上下左右偏移指定处的值
=OFFSET(S5,0,2) //获取S5单元格, 往下偏移0, 往右偏移2个单元格处的单元格的值, 负值则反方向移动, 后面还有两个参数表要获取的行和列数,详见函数说明
=OFFSET(A1,ROW()-1,0) //从A1隔一行取一个值, 如A1列为1-9, 可取出1,3,5,7,9
="早"&LEFT(kqjl!B6,5)&"晚"&RIGHT(kqjl!B6,5) //从kqjl表的B6单元格最左边5个, 加面加早, 从最右边再取5个字符,前面加晚字
=OFFSET(kqjl!B6,ROW()-5,0) //从kqjl表的B6开始, 间隔一行取值, 前5行不算
="早"&LEFT(OFFSET(kqjl!B6,ROW()-5,0),5)&"晚"&RIGHT(OFFSET(kqjl!B6,ROW()-5,0),5)
九, ADDRESS函数, 用以返回指定值所处单元格的地址
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
//Row_num为单元格行号, Column_num为单元格列标
//ABS_num 返回的引用类型, 值为1或省略表绝对引用, 值为2表绝对引用行号相对列号, 值3表相对行绝对列, 4 相对引用
//Sheet_text 为一文本，指定作为外部引用的工作表的名称，如果省略 sheet_text，则不使用任何工作表名
十, row,clomn 获取当前单元格的行号和列号
=(ROW()-2)*10 //从第2行开始填, 数值从0开始, 每次递增10
=COLUMN()-1 //列号减1
十一, 判断时间,当时间是文本时
=IF(TIMEVALUE("08:00")>TIMEVALUE(TEXT(B1,"h:m")),"OK","")
将excel函数获取的值转为固定值
通过excel函数获取的值，当原表格数据删除或修改后会影响获取的值，
解决方法：复制这些单元格——选择性粘贴——值，就是再把这些单元格的值复制一下再粘贴其值
从身份证号码中取出生年月日
=IF(LEN(A2)=18,MID(A2,7,8),IF(LEN(A2)=15,MID(A2,7,6),"错误"))
//如果是18位数的就从第7位开始取8位, 如是15位数的就从第7位开始取6位, 否则输出错误,15位的年有少19这两个数
=IF(LEN(A2)=18,TEXT(MID(A2,7,8),"0000-00-00"),IF(LEN(A2)=15,TEXT(MID(A2,7,6),"0000年00月00日"),FALSE))
//将得取的按1987-04-20格式显示
提取单元格中括号内的字符
=MID(A2,FIND("（",A2)+1,FIND("）",A2)-1-FIND("（",A2)) //提示单元格A2中括号内的字符,
//FIND("（",A2)+1表得到左括号的序号并加1, FIND("）",A2)-1-FIND("（",A2)表得到右括号的序号减1再减左括号的序号, 为要提取的字符长度
从另一个excel文件中引入数据
=[excel文件名]Sheet1!$B$2
例在monthTest.xlsx文件的A1中输入=[MSWeekReport.xls]Sheet!B1 //调用当前目录下另一个名叫MSWeekReport.xls的文件sheet表中的B1的数据
注：两个文件要都处于打开，可以不在同一目录，被引的文件MSWeekReport.xls打开后引用了就可以关闭了
=datedif(A2,B2,"d")
=text(a1,"e/m/d")
=text(B2,"md")
=text(B2,"d")
看不懂的
=index(sheet1!$D$2:$D$7,match(sheet2!A2&B2&C2,sheet1!$A2:$A$7&Sheet1!$C$2:$c$7,0)))