Excel函数解题大法

前言：本文由excel精英培训网兰色幻想原创，转载请注明作者和转自EXCEL精英培训网.

1、什么是零除法？

所谓0除法，就是故意让0除以一组数（有时也可以用1除）。如：

0/(a2:a10=c1)

1/(a2:a10=c1)

2、用0以一组数的目的是什么？

如果分母是0，结果是错误值，如果分母不是0呢，结果就是0。这样就可以把一组数变成0和错误值组成的新数组。有些函数象LOOKUP要求第二个参数必须是升序排列，COUNT函数可以忽略错误值计算个数，这样我们就可以把0除后的数组作为它的参数。

3、实例：

实例一：计算A列大于10的个数。

公式：{=COUNT(0/(A2:A6>10))}

分析：

1、计算大于10，那么我们需要让这些数一一和10进行对比。即：

A2：A6>10

对比后大于10数字变成TRUE，否则变成FALSE。即：

{FALSE;TRUE;FALSE;TRUE;FALSE}

2、现在计算大于10的个数，变成了计算TRUE的个数，怎么排除FALSE呢，我们就可以用0除的方法把FALSE转换为错误值。

0/（{FALSE;TRUE;FALSE;TRUE;FALSE}）

结果：{#DIV/0!;0;#DIV/0!;0;#DIV/0!}

3、通过0零除后，我们可以用COUNT函数统计出数字的个数，也就是大于10的个数了。

COUNT（{#DIV/0!;0;#DIV/0!;0;#DIV/0!}）

结果：2

因为是一组数在一起运算，所以要用数组公式形式输入，即按ctrl+shift，然后按ENTER结束输入

实例二：计算最后一个A的单价

如下图所示，A产品多次入库，要求计算出A最新入库的单价A

分析：

1、判断是否为A，当然要用=号来对比一下。

C5:C10=B13

结果是：{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

2、怎么样查找出最后一个TRUE的位置呢，我们知道LOOKUP函数是从后向前查找的，但是要求第二个参数必须是升序排列，但第一步的结果是TRUE和FALSE组成的数组，不符合条件，但是LOOKUP有一点特点就是可以忽略错误值，所以我们就使用0除的方法把FALSE转换为错误值，这样就符合要求了，即：

0/(C5:C10=B13)

结果：{0;#DIV/0!;0;#DIV/0!;0;#DIV/0!}

3、有了上面的结果，我可以用任一个大于等于0的数来查找最后一个0的位置，然后返回第三个参数中对应的值了。

最终的公式为 =LOOKUP(1,0/(C5:C10=B13),D5:D10)

2、mod截尾法

[日期：2012-06-17]

来源：excel精英培训作者：兰色幻想

[字体：大中小]

前言：这个方法可不是我创的，而且大部分高手都在用的，我这里只是总结一下，希望对一些学习数组的新手有帮助。如果文中有不足的地方，请函数高手们多多指点。：）

      我们经常需要用MATCH查找一个内容所在的位置，但有时查找的和被查找的都是通过很复杂的返回的，公式就会显示十分繁锁。这时我们就可以利用MOD函数给数字提前加一个“序号”尾巴，等排序后根据尾巴就可以知道它原来的行数。    先说一个MOD函数添加尾巴的原理：
            第一步：把数字扩充100倍。
               123       -----       123*100=12300
            第二步：加上小尾巴
               12300+row(1:1)  =12301 这里利用ROW产生的数字序列，可以批量给一列数添加1-N的小尾巴。
            第三步：使用mod函数截取小尾巴。
               mod(12301,100)  =1 '这里的1就是原来123所在的位置
   下面看一个实例吧：
            如下图所示，A列是销售员，B列是销售数量，要求返回销售最多的销售员名称。
               最大销售量.jpg

            分析：
               首先我们需要用sumif计算出每一个销售员的销量和，然后找到最大的，再看看最大的数在第几个位置，然后用index在A列取出相对应的销售员。
               如果用一般的方法，我们可以这样：
                     {=INDEX(A2:A6,MATCH(MAX(SUMIF(A2:A6,A2:A6,B2:B6)),SUMIF(A2:A6,A2:A6,B2:B6),)) }
               公式说明：
                           MAX(SUMIF(A2:A6,A2:A6,B2:B6)) 返回最大销售量
                           MATCH(MAX(SUMIF(A2:A6,A2:A6,B2:B6)),SUMIF(A2:A6,A2:A6,B2:B6),)) ，返回最大销售量的行数
                           INdex （）：根据行数返回销售员
            如果用MOD方法，可以这样：
                        =INDEX(A2:A6,MOD(MAX(SUMIF(A2:A6,A2:A6,B2:B6)/1%+ROW(1:5)),100))
            公式说明：
                        SUMIF(A2:A6,A2:A6,B2:B6)/1%  ‘销售量和扩大100倍
                        SUMIF(A2:A6,A2:A6,B2:B6)/1%+ROW(1:5)) ’扩大一百倍的销售量和批量添加1~5的小尾巴。
                        MAX（）：从含有小尾巴的数字中返回最大的
                        MOD（MAX(),100），利用返回余数的原理，把小尾巴再提取出来，就是这个符合条件的所在行数。
                        INDEX() ：根据小尾巴，提出销售员

3 带"B"的函数

[日期：2012-06-17]

来源：excel精英培训作者：兰色幻想

[字体：大中小]

我们很熟悉MID和FIND函数的使用，却对带B的函数用的很少，其实EXCEL提供的每一个函数都有特定的用途，那么函数中带B和不带B的函数有什么区别呢？
   其实很简单，带B是按单字节处理，而不带B是按双字节处理，我们常用的字符，数字和英文是单字节，而汉字是双字节，如果使用带B的函数，就可以区分单双字节了。
   先举一个选简单的例子。
=LEFT("我CD",2) 返回的是“我C” ，LEFT不分单双字节
=LEFTB("我CD",2) 返回的只是“我”  这里“我”是占两个字节数，LEFTB分单双

   书归正题，说一下怎么利用MIDB函数+SEARCHB函数巧妙拆汉字和数分，比如：
      单元格A1的内容是：白菜3.467
      要求把数字拆分出来。
      解：
   第一步：使用searchb的模糊查找特点，查找第一个单字节字符的位置，即：
         =SEARCHB("?",A1)    ?号是单字节占位符，这里作为通配符用
第二步：使用MIDB函数进行截取。MIDB也是按单字节位置进行截取的。即：
                =MIDB(A1,SEARCHB("?",A1),99)

Excel函数解题大法

1、0除法

2、mod截尾法

3 带"B"的函数