选择合适的函数组合.pdf

选择合适的函数组合 Excel 2007 中的内置函数多达350 多个，函数与公式技巧比较熟练的用户在面对特定的问 题时总是会自觉地想到所要使用的特定函数。 例如，排序问题经常要用到 SMALL、LARGE、RANK、COUNTIF 函数；多条件求和或计 数通常会使用SUMPRODUCT 函数；连续出现或频率计算通常都会用到FREQUENCY 函数或 MODE 函数；不重复值的处理问题最常用的是 MATCH 函数；三维引用和筛选状态下的公式处 理通常都会使用 SUBTOTAL 函数；多个分支条件的选择处理则会用到 LOOKUP 函数或 CHOOSE 函数；数值的条件转换比较常用的是TEXT 函数等等。 要用这些函数来编写出正确而且高效的公式，拥有良好的思维习惯和大局观远比熟练的函数 技巧来得重要。 养成良好的思维习惯，可以抓住问题的关键，把握住解决问题的主要方向。而良好的大局观 则可以选择合适的函数来高效的组合公式。 例1，要在字符串“excelhome office competition ”中计算字母“c ”出现的次数。以常规 的思路来考虑，可以通过MID 函数依次取得字符串中的各个字符，然后统计其中字符为“c ”的 个数。 假定字符串位于A1 单元格中，依照上面的思考可以创建下面的公式： {=COUNT(1/(MID(A1,ROW(INDIRECT(1:LEN(A1))),1)=c))} 如果换个角度进行考虑，将字符串中的所以字母“c ”全部清除，然后将剩余的字符串长度 与原字符串长度进行比较，所得的差值就是字符“c ”的实际出现次数。 照此思路则可以写出下面的公式： =LEN(A1)-LEN (SUBSTITUTE(A1,c,)) 显然，这比前一个公式简洁了许多。 例2，经纬度换算，要把 120.2328 度转换为度分秒的形式。 以常规思路来考虑，这是一个10 进制转换为60 进制的数学换算问题。将小数部分乘以60 所得的整数部分为“分”，剩余的小数部分再乘以60 所得的整数部分为“秒”。 假定经纬度数据存放在A1 单元格中，以上述思路可以写出下面的转换公式： =INT(A1) 度 INT((A1-INT(A1))*60) 分 1 ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,2)秒 这样的公式虽然思路并不复杂，但编写较为繁琐。如果换个思路，考虑到时间的分秒也是 60 进制，可以借助时间序列值的格式转换来实现 10 进制与60 进制的换算。 按照这个思路可以写出下面的转换公式： =TEXT(A1/24,[h]度m 分s.00秒) 此公式将经纬度数据视为日期时间序列值，整数部分表示天数，小数部分为时间。将其用 TEXT 函数转换为时间格式，则可以表示为“×天×小时×分×秒”。如果将数值除以24 小时再 用TEXT 函数转换，则变化为“×小时×分×秒”，此格式与度分秒的进制一致，格式代码中进 行相应文字替换就可以得到最终的转换结果。 例3，在图 1-1 所示的A1:D6 单元格区域中存放着一些字符，其中包含字母、数字、符号 和中文，要统计其中存放字母的单元格个数。 图 1-1 查询字母单元格 以常规思路来考虑，Excel 中没有直接可以判断字符是否为字母的函数，要判断单元格中存 放的是否为英文字母，通常会通过与字母A 和字母Z 进行比较来得到结果。 依照这个思路可以写出公式： {=SUM((UPPER(A1:D6)=Z)*(UPPER(A1:D6)=A))} 用UPPER 函数将字母转换为大写字母，然后分别与字母A 和Z 比较大小，在此区间内的 即为字母。 除了这种常规思路以外，还可以又另外一种更巧妙的方法： {=COUNT(N(INDIRECT(A1:D665536)))} 这个公式利用了Excel 中的单元格地址引用方式。单元格地址的A1 引用方式为字母和数字 的组合，字母表示列标，数字表示行号。将单元格中的字