Excel函数集锦（二）

011-查找函数

1. Vlookup(特征值，查询区域，对应列，精确/模糊查询)，特征值须在查询区域首列。

*第四个参数：精确查询：False/0;

模糊查询：True/1:将小于等于特征值的最大值作为查询结果；如查询区域首列为90、120、140，特征值为127，则小于等于127的最大值120为查询结果。

*如果查询无结果，返回#N/A。

a. 同一查询结果，多列数据的解决方案：Vlookup(E1,$A:$C,Coulumn(A:A),False)

b. 同一查询结果，多条数据解决方案：区前辅助列-Countif将符合待查找字符的编号-Vlookup(ROW(1:1),$A:$D,Column(B:B),false)—P224

c. 多条件查询解决方案：区前辅助列，将带查找条件合并在辅助列中，转化为单条件查找。但是这种方法容易造成合并条件后，与多条件不匹配而形成的数据误读。可以使用一个特殊符号，例如”@”在条件合并时候变区分开不同条件，同样在公式中，也要加入该符号。

d. 如何避免查找无数据时返回#N/A的方案：一来可以用If函数，不过较为繁琐。最快的是IfError(Vlookup, ” ”)。

2. Hlookup：与Vlookup用法相似，不过用来解决横向数据查找问题。

3. Match(特征值，查询区域，匹配方式)从左到右，或者从上到下查找指定数据。

匹配方式：1/省略，0，-1：

1/省略：查找小于等于特征值的最大值所在位置，查询区域必须升序排列；

0：精确查找，完全匹配。

-1：查找大于等于特征值之的最小值所在位置，查询区域必须降序排列。

4. Index(区域，行数，列数)：查找某区域内第N行第M列的单元格内容。

如果区域为单行或者单列，可以简化为：Index(区域，N)，亦即区域内第N个单元格内容。

如果区域为n个独立区域组成，可以引入第四个参数：Index(区域集合，N，M，i)，其中i表示应该查询第i个区域内的第N行第M列的单元格内容。

5. Vlookup的替代方案：Index(array,Match)。尤其是要查询结果列还在特征值所在列前面时候，该方案解决了Vlookup必须设置辅助列的问题。

6. Lookup(特征值，数组)：查询匹配函数。

数组中的首列（特征值对应列）必须是升序，匹配结果是小于等于特征值的最大值对应结果。

a. 当数组为单列/行时，返回其列/行中的数据。

b. 当数组为一个矩形区域时，返回其特征值在最后一列/行对应的结果。且：

当行数大于等于列数时，纵向查找。行数小于列数时，横向查找。

c. 当数组为双数组时(向量形式)，则：

Lookup(特征值，查询数组，返回数组)；

Lookup(特征值，{特1，返1；特2，返2；特3，返3；……})

Lookup(特征值，{特1，特2，特3…}，{返1，返2，返3…})

d. 在未排序的数组中查找：

单个条件Lookup(1,0/(查询数组=特征值)，返回数组)

多个条件Lookup(1,0/((查询数组1=特征值1)*(查询数组2=特征值2)*…)，返回数组)

7. Indirect(要转换引用的文本，引用样式)：将文本型的单元格地址转换为引用。如，'A1'为文本时，不能为函数辨识，用Indirect将其转为地址。

引用样式的设置：True: A1类；False: R1C1类。该函数可用于处理多表格汇总的情况。

8. Offset(参照点，向下N行，向右M列，返回a行，返回b列)：告诉函数以哪个单元格为参照点，往下走N行，往右走N列后得到的目的单元格所在a行b列的区域(目的单元格在返回区域的左上角)。

*输入时候，请选中目的区域的返回区域，公式输入后，Ctrl+Shift+Enter，得到返回区域。

*如果Offset只有三个参数Offset(参照点，向下N行，向右M列)，那么仅仅返回目的单元格。

*配合Match函数，本函数可以用于多条件查找。

022-日期和时间处理

1. Now()和Today()的区别：Now显示日期和具体时间（时分秒），Today仅显示日期。

2. Date(a,b,c)：a年b月c日。注意如果c为0，那日期为上月最后一天。

3. Time(a,b,c)：a小时b分钟c秒钟。

4. 时间信息的函数：Year(A1);Month(A1);Day(A1);Hour(A1);Minute(A1);Second(A1)。

5. 对于一个复杂的时间内容单元格，完全地提纯日期：可以Year&Month&Day，更好的解决方案是：Int+时间格式选择，Int的作用为获取小于引用内容的最大整数。

6. 对于求某日期的N日后得到日期，直接Date+N，便可解决。

对于求某月份后的N月后的月份，除了将日期分解为某年某月，然后计算外，还可以使用专门的Edate函数：Edate(date,N)

7. 日期间隔函数Dateif(date1,date2,返回类型)：

返回类型y、m、d，分别指两个日期相隔的年数、月数、天数。

8. 求星期的函数

a. Weekday(date,返回类型)

返回类型告诉Weekday函数，应该将星期几作为一周的第一天。如省略，默认为1！而当参数为1时，是按照西方习惯，即星期日为一周第一天的。按照中国人习惯，参数一般设置为2，这样周一才是每周第一天，返回数字N也就是星期N了。

b. Text函数：

Text(date, ”aaa”)返回周几的几，如”四”；Text(date, ”aaaa”)返回周几的全称，如”星期四”；

Text(date, ”ddd”)返回周几的英文缩写，如”Thu”；Text(date, ”dddd”)返回周几的英文全称，如”Thursday”；

9. 判断日期所在季度的函数，可以用Vlookup、Match模糊查找的方法解决，但是都相对较为复杂。神来之笔Len(2^Month(date))，取巧利用2的1~3次方为1位数，4~6次方为两位数，7~9次方为三位数，10~12次方为四位数，正好与月份季度相对应的规律。

10. 求两个日期间的工作日天数：NetWorkDays(日期1，日期2)。

为了解决日期间存在特殊的休息日，如法定节日等，那么给NetWorkDays函数再引入一个参数：

NetWorkDays(日期1，日期2，array)。array为需要特殊说明剔除的非工作日数组。

11. 自定义休息日的工作日天数，采用NetWorkDays.Intl函数。

12. 某日期后指定工作日后的日期值：Workdays(start date, workdays number, array)。此处的array同10.中的array。

13. 判断是否为闰年有两个思路：

a. 通过对年份进行分析，由于闰年的年份是可以被4整除、不能被100整除，或者能被400整除的数，所以：

If(Or(And(Mod(A1,4)=0,Mod(A1,100)<>0),Mod(A1,400)=0), ”闰年”，”平年”)

b. 利用闰年的2月有29日的特征：

If(Day(Date(A1,3,0))=29, ”闰年”， ”平年”);

If(Month(Date(A1,2,29))=2, ”闰年”， ”平年”)。

14. 文本格式的日期转为日期型：DateValue(文本格式的日期)

文本格式的时间，转换为时间型：TimeValue(文本格式的时间)

033-数组公式

1. {a,b,c……}：逗号隔开同行数组的各个元素。

{a;b;c……}：分号隔开不同行数组中的元素。

{a1,a2;b1,b2;c1,……}：多行多列二维数组。

2. 数组公式所返回的往往也是一个数组，公式输入后，Ctrl+Shift+Enter得出结果。另，选中整个公式，按F9，可以显示返回数组。对于返回结果的修改，需要整体修改，不可以只修改其中某一部分。CTE!

3. Sum(A1:A9*B1:B9)，可以代替SumProduct。

同理：和之和Sum(A1:A9+B1:B9), 差之和Sum(A1:A9-B1:B9), 以此类推其他各种行列运算。

多条件成绩和汇总Sum((条件1)*(条件2)*(A1:A9*B1:B9))

4. 统计单元格字符串中某个字符x的个数：Sum(--(Mid(A1,Row(1:99),1)='x'))。另外还有个思路:Len-Len(Substitute)

5. 一维数组与一维数组运算，结果还是一维数组；

一维数组与N维数组运算，结果是N维数组（一维数组与其各维数组一一运算）。

N维数组与N维数组运算，结果是N维数组（N维数组的各维数组一一运算）。

M维数组与N维数组不能运算。

6. Row和Column函数

a. Row()返回第几行，Column()返回第几列。

b. Row(单元格位置)返回单元格所在行，Column(单元格位置)返回单元格所在列。

c. Row(区域)返回区域内所有单元格所在行的合集。Column类似。

d. Row(a:b)，ab都是数字，即返回a~b之间的整数合集。Column类似。

e. Rows(a:b)，Rows(Ai:Ej)，都是返回首尾单元格之间的行数。Columns类似。

f. Mid(A1,Row(1:1),1)：拆分单元格内各个字符。

g. 构造1,1,2,2,3,3…的数组：Trunc(Row(2:2)/2), 同理可以构造出1,1,1,2,2,2,3,3,3…。

7. Transpose(……)：专门用于转置的函数，可以将行数组与列数组互换。

8. 多条件统计：Sum((条件区域1=条件1)*(条件区域2=条件2)*……)

统计1~100内可以被3整除的个数：Sum(--Mod(Row(1:100),3)=0)；

统计1~100内可以被3和5整除的个数：Sum((Mod(Row(1:100),3)=0)*(Mod(Row(1:100),5)=0))

9. Vlookup一般使用的时候，一般第二参数的区域首列需要为特征值比配所在列。为了解决查找列在返回列右侧的问题：

Vlookup(D1,If({1,0},B1:B9,A1:A9),2,0)。就是将查找区域变为：If({1,0},查找列,返回列)，亦即查找列和返回列的合集。

同理多条件情况下：Vlookup(E2&'@'&F2,If({1,0},C1:C9&'@'&B1:B9,A1:A9),2,0)，记得CTE！

10. 同上，Index(array,Match)也可以用数组来查询数据：Index(A1:A4,Match(H10&'@'&I10,C1:C4&'@'&B1:B4))，CTE！

11. 筛选满足条件的数据：Index(B:B,Small(If($A$1:$A$20='第1组',Row($1:$20),2^20),ROW(1:1)))&''；

Index(C:C,Small(If(($A$1:$A$14='第2组')*(LEFT($B$1:$B$14,1)='李'),Row($1:$14),2^20),Row(1:1)))&''

12. 统计不重复的个数：Sum(1/Countif(A1:A9,A1:A9))，还有一个思路Max(Match(A2:A7,A2:A7,0))。

13. 求字符串里包含的数字个数：Sum(--Isnumble(-Mid(A2,Row(1:99),1)))，为了避免使用99这样较大数字可以使用Indirect来统计字符数放在Row中：Sum(--Isnumble(-Mid(A2,Row(Indirect('1:'&Len(A2))),1)))。还有一个思路：Count(-Mid(A2,Row(1:99),1))

044-名称管理器

1. 为了避免数组或其他函数多次引用时的麻烦，可以考虑引入'名称管理器'。类似C++中的函数命名，然后在主程序外再定义一个函数。

2. 宏表函数的应用：

a. 自动计算输入的是计算式：[名称管理器]引用位置=Evaluate(A1)。

b. 求活动表名称：[名称管理器]引用位置=Get.Document(76)，出来的是包含文件名字的表名如[结算书.xlsx]汇总表，可以考虑使用Right函数，或者Trim(Right(Substitute)))的嵌套来解决，详见4-字符处理的截取字符的相关办法。

c. 求得所有活动表名称：[名称管理器]引用位置=Get.Document(1)，出来的事所有表名称组成的数组。可以按照数组再深化处理。如：Iferror(Index(定义名称,Row(1:1)),'')等。

d. 获得某文件夹里的文件目录：[名称管理器]引用位置=File('F:\Excel资料\*.*')。

e. 如何解决宏表函数不自动计算的问题：[名称管理器]引用位置=Get.Document(76)&T(Today())。

055-条件格式

1. 自定义条件格式中除了已经自动设置外的，有一个使用公式来判断单元格中是否满足条件，而更换格式的选项。除了可以选出符合条件单元格外，有两个比较特殊的。