excel使用技巧

1.excel中比较两列数据是否相同，并标上颜色

选中B列，点击条件格式中的新建规则，选择第二项-只为包含以下内容的单元格设置格式，单元格值 - 不等于 - =$A1,设置格式显示其他颜色的字体。

2.单元格内容作为比较条件时，需要加上CONCATENATE()来连接字符串。

=SUMIFS(Sheet1!F2:F33,Sheet1!C2:C33,CONCATENATE("=",A10),Sheet1!G2:G33,CONCATENATE(">=",L8),Sheet1!G2:G33,CONCATENATE("<",M8))

注意变化的单元格与不变的单元格使用美元符号来确认。

3.判断一个单元格是否包含另一个单元格的字符串，建立规则：

选择使用公式确定要设置格式：   =ISERR(FIND($A1,$B1))

 4.计算工龄工资的公式：

LOOKUP(DATEDIF(F6,人事行政15人!$A$1,"m")/12,{0,0.5,1,1.5,2,3,4,5,6,7,8,9,10},{0,40,80,120,120,120,160,200,240,280,320,360,400})

用月份来计算可以避免闰年的天数不一致的问题。

5.时间戳转换为日期文本：

=CONCATENATE("INSERT INTO fp_projma VALUES('",A4,"','",B4,"','",C4,"','",D4,"','",TEXT(F4,"YYYY-MM-DD HH:mm"),"','','','','",TEXT(E4,"YYYY-MM-DD HH:mm"),"','',",H4,,",",G4,",'",TEXT(E4,"YYYY-MM-DD HH:mm"),"')")

6.INDIRECT()的使用，定位到括号里的内容，注意“A1”和A1的区别：

=INDIRECT("A1")——加引号，文本引用——即引用A1单元格所在的文本（B2)。

=INDIRECT(A1)——不加引号，地址引用——因为A1的值为B2，B2又=11，所以返回。

=VLOOKUP(VLOOKUP(D33,INDIRECT(B52&"!A:L"),3,0),$H$2:$O$4,8,0)

7.在字符串任意位置插入任意字符

=MID(C6,1,4)&"-"&MID(C6,5,2)&"-"&MID(C6,7,2)

8.判断某列的某个字段的其中一部分是否对应另一列中的某个值：

=IF(ISERROR(VLOOKUP(--MID(C1,6,5),A:A,1,FALSE)),"NG","OK")

解析：

VLOOKUP：精确查找（需要设置精确匹配模式）

LOOKUP：模糊匹配

MATCH：匹配查找（需要设置精确匹配模式）

尤其注意：各列的数据格式，数值，文本，不能匹配，所以需要用Value（或者--）转换成数字模式或者将数字转换成文本TEXT（A1,"0.00"）

Table_array是搜寻的范围，col_index_num是范围内的栏数。Col_index_num　不能小于1，其实等于1也没有什么实际用的。如果出现一个这样的错误的值#REF!，则可能是col_index_num的值超过范围的总字段数。选取Table_array时一定注意选择区域的首列必须与lookup_value所选取的列的格式和字段一致。比如lookup_value选取了“姓名”中的“张三”，那么Table_array选取时第一列必须为“姓名”列，且格式与lookup_value一致，否则便会出现#N/A的问题。

在使用该函数时，lookup_value的值必须在table_array中处于第一列。

使用该函数时，返回的是目标区域第一个符合查找值的数值。也就是说在目标区域存在多个目标值时，则应特别注意。