选择合适的函数组合归类.pdfVIP

选择合适的函数组合 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 查