关于论坛函数版新人问题的一个粗略总结：（教你解答问题）

liuguansky

关于论坛函数版新人问题的一个粗略总结：

                                                                                
写在前头：                                                                                
　  记不清什么时候注册的EH会员了，也许很长时日了，也许只在近眸。                        
　　姑且当它只在近眸吧。                                                                        
   
　　在论坛也有一两个月的蜇伏了吧，我感觉解答问题成了我一个至关重要的学习手段，对我巩固知识，提升领悟的帮助无以为大。                                                                                
    我是去年九、十月份才开始真正有自我意识来学习函数的，当时在一个EXCEL学习群里面，每天有人问问题，但是自己解决不了，就用百度，找到答案了，回答别人。哈哈，说来也惭愧，只因太无聊啊。                           
    于是，我就发现了一个问题，百度的答案很多都出自于EH,或链接于此，遥想好似以前也是无聊有注册一个会员，就进去看了下，在函数版混了几天，发现我完全是鸡立鹤群啊，没有一个问题我是可以解决的。        
　　于是凭借自己有点数学基础（当然仅限于高中，大学完全的荒废啊，不解释。），看到问题，先在EH站内搜索，搜索到了，学习了，再去帮别人解决，真的是痛苦啊。不过亦苦亦乐啊，有时写了一个自己都不懂的公式，来博取别人的感谢，真的是一件快事啊，荣誉感特强；有时看到高手写了一个精妙公式，看到心领神会，也是激动好一阵，遇见问题就想用，感觉那段学习的时光真是很鼓舞人心的。                                                        　　
　　于是，开始学取不重复值，学SUM,学TEXT，学LOOKUP，学MMULT等等（当然只是些初级运用），这个阶段完全是看哪个问题问的人多，就弄清楚这个问题；哪个人用的函数解的漂亮就学哪个函数；哪个函数看起来更能吸引眼球，就多使用哪个函数。                                                                                
　　每日沉溺于此，机械的写公式，慢慢的很生涩的公式就可以信手拈来了，有时也会有灵犀一动，举一反三。思维也不那么死化，思路的取舍也清晰起来。                                                                　
　　虽在EH时日不多，但对函数版的问题大致都了然一二，现对问题总结于下，同时附上前辈，版主们的学习资料，当然很多都是我学习的，感觉帮助很大的。只为略表敬意，无以为它。                                       

　　若有不尽、不对之处，望以补充、雅正，不甚感激。
　　其它更为详尽解释，请见函数版置顶贴，或EH站内搜索。
    函数公式解释专用帖                                                                        
    http://club.excelhome.net/thread-161588-1-1.html                                                                        
        函数用法讨论专辑                                                                       
    http://club.excelhome.net/thread-96652-1-1.html                                                                        
                                                                                
　　以下论述，皆只叙以文字，若带来视觉疲劳，请于EH函数版解答问题，聊以宽心。

[ 本帖最后由 liuguansky 于 2010-3-29 21:03 编辑 ]

liuguansky

第一问：                取不重复值               
推荐学习资料：                               
        ★★★★★        http://club.excelhome.net/viewth ... B%D6%D8%B8%B4%D6%B5               
                方版               
通用格式：                               
        =index(返回值列,small(if(条件,row(区域),65536),row(1:1)))&""                       
        这个公式基本可以解决论坛的问题的60%以上，毫不夸张。                       
        我不知道这个公式对大家的影响有多少深远，我以我的体会打一个比方：                       
        VLOOKUP之于我初见EXCEL，正如此之于我想见EXCEL。                       
        很多返回最大值，最小值，多值记录都可以用这个公式                       
近期案例：                               
        http://club.excelhome.net/viewth ... p;page=1#pid3685400                       
使用心得：                               
        １.条件的确认：                       
        不能同时满足的条件用+，可以同时满足的条件用*                       
        比如，部门为部门A和部门B，就用（区域="部门A"）+（区域="部门B"）                       
        比如，年龄大于20小于30，就用（区域>20）*（区域<30）                       
        我感觉这样比且，或更好理解。                       
        再一点，关于if(（区域="部门A"）+（区域="部门B"),row(区域))                       
        这样形式中的“+”与“*”的理解，我认为把他理解为两个true,false的数组相加，相乘，比or,and更好理解。                       
        只要去关注数组返回的是１还是０就可以了。也可以用于理解不能同时满足用+这句话。                       
        ２.65536的作用：                       
        为什么要用65536？                       
        EXCEL2003版本最大行号为65536，所以当条件为false时，返回一个很大的行号，那么index时就会索引到后面没有                       
        记录的行，从而生成一个空文本，可以防止公式下拉出错。其实可以换成索引区域最后非空单元格的下一个单元格，                       
        但是要判断这个单元格，所以用了最后单元格来代替。因为我们一般很少用到那么远的区域。                       
        另外index索引的时候，如果是空值，将返回0值，同offset，vlookup等。所以配合&""或者T()来防错返回空值。                       
        ３.诸多变形                       
        当我们对这个公式运用的特别熟练了，可以对方版总结贴上的其它求不重复公式加以研究，选出自己喜欢的格式来                       
        运用。下面我来说下我对这个公式（仅针对这个公式的这个格式而言）的一些变式说明如下：                       
        A. 解决排序                       
        基本形式是if条件后返回的row(区域),其实这也是一个默认的排序，就是按行号大小，也就是出现的先后顺序；                       
        如果要满足条件的多记录，按其它条件排序返回结果呢？                       
        变形                       
        =index(返回值列,mod(small(if(条件,row(区域)+排序值区域*100,65536),row(1:1)),100))&""                       
        在row()加上一个较大的排序区域值，让row()不能起作用，排序值来起作用.                       
        而我们要返回的是row()，所以对排序后返回的数据要还原，用mod(,100)或者--right(,2)                       
        向无限变形                       
        ＝mod(small(if(条件,row(区域)+次排序值区域*100+主排序值区域*10000,65536),row(1:1)),100)                       
        可以设置多重排序值。                       
        这样，不管写多复杂的公式，多少次转换，你的思路都可以清晰明净，洒洒而书。                       
        B.代替筛选                       
        这应该是这个公式最为常见的一个应用。                       
        if后的条件，等同于筛选的条件，不过更为灵活。                       
        条件的设置参照前面我对+，*的理解                       
        就是两组数的运算，来生成条件所需的TRUE，FALSE（即运算结果1,0)                       
        同时结合排序思维可以解决，排序，筛选问题。                       
难点解析：                               
        １.条件的设置是此公式的难点。                       
        常见条件设置示例：                       
        大于60，小于70                       
        (区域>60)*(区域<70)                       
        小于60，大于70                       
        (区域<60)+(区域>70)                       
        条件１大于60且条件２小于７０                       
        （区域１>60)*(区域2<70)                       
        条件１大于60或条件２小于７０                        可能同时满足
        (区域１>60)+(区域２<70)                        那么这里就有可能生成2，但是对IF判断不起影响，如果用muult就要注意转换
        ２.排序关键值的返回易忽略点.                       
        mod，--right返回                mid,right,left返回的结果均为文本，若要参与后续的数值引用，需进行转换       
        常见数值类文本转换数值                       
        1*                       
        +0                       
        -0                       
        /1                       
        --                       
        value()

信手写来，刍了一章，未加修饰，徒增刺目。一日续展一章，以慰众兴。

　　待续。

[ 本帖最后由 liuguansky 于 2010-3-29 20:12 编辑 ]

cflood

不错，等待第二章。

李甲

支持，这就是明显的作用嘛……

alwstr

你的帖子 肯定差不了

刚开始学习的时候你指点过我不少，继续向你学习。。。。
（不过说说，你的QQ加不了人， 本来还想去给你做师傅的 ）

wangg913

帮你顶顶，文字太长，需耐心看。。。。

liuguansky

原帖由 alwstr 于 2010-3-29 20:34 发表
你的帖子 肯定差不了

刚开始学习的时候你指点过我不少，继续向你学习。。。。
（不过说说，你的QQ加不了人， 本来还想去给你做师傅的 ）

哈哈，现在去把它取消了，忘记了。

Eric_f765

楼主，也是个有心人啊！向你学习！

liuguansky

第二问：多条件求和，计数
推荐学习资料：
  ★★★★http://club.excelhome.net/viewthread.php?tid=228370&extra=page%3D1%26amp%3Bfilter%3Ddigest
　这个的学习资料很难找，希望谁有的可以共享下。以上资料权当一个提升后的学习资料吧。
通用格式：
　sum(条件*条件。。。。）
　sum(条件*条件。。。*数据）
　感觉这问的内容很简单，但是问的人却是出奇的多。
　真正实用的一个公式。
　第一问是排序筛选，这一问就是条件计数求和，其实关键东西还是一样的。条件的确认与转换！！！
近期案例：
　http://club.excelhome.net/viewthread.php?tid=554256&page=1#pid3689279
使用心得：
　我感觉就是一个条件的堆积，只要是需要的条件往上面放就可以了.
　第一问是在if后面堆条件，这个是直接在SUM里面堆。
　所以看到多条件的这样的问题，也不用怕，把所有的条件判断都放在一起。
　很多论友问*的作用，如果难理解，就把它理解成乘号。你要统计哪些区域，哪些区域不统计，那么你是不是要返回
一个1,0,0,1这样交错的数组呢?有了这样的数组，我们用SUM可以计数，与数据区域相乘再SUM就可以求和了。
　所以关键问题来了：如何把条件进行一个合理的堆积？
　条件的确认第一种，最直接的，就是所说的全部套用的。
　SUM(条件１*条件２*条件３.。。。。）
　把所有要满足的条件用*连起来就可以了。
　适用于那些条件很明确的，比如统计男，统计水果类，等等。这些有明确的条件可以直接用“区域＝目标值”就可
以完成一个条件的设置。
　比如要统计锻造车间女员工的人数：SUM(（姓别区间＝女）*（车间区间＝锻造车间））这样的最直接的条件判断
一定要用（），因为*的运算优于＝，如果不用（）可能造成错误结果，所以大家要养成（）套住一个完整条件的习惯。
　另外的条件就是一些间接的条件了，这个的写法就没有一个定势了，应依据实际的需求选用相应的条件判断。
　常用的条件判断的一些函数实例：
　1.month(日期区间)＝目标月份(同理year().day(),weekday()等等日期类的处理函数)
　2.mid/left/right取文本类的函数（当然取了文本，如果目标值是数值，也要进行转换）
　3.find/search,countif,match等查找类的函数（结合通配符的运用）
以下假设判断条件的区域为data，常用条件如下：
　1.month(data)=2,２月数据;year(data)=1998,1998年数据;….
　2.left(data)="我"，第一个字是我的数据;mid(data,3,4)="加工车间",第三个字开始四个字为加工车间的数据….
　3.isnumber(search("*车间*",data)),含车间的数据;countif(data,data)>2,个数大于两个的数据;
  再把这些条件合一起，就解决了一个多条件的计数与求和问题了。
　关于条件设置的思维：
　我的想法是，你目测几个数据，自己在心里返回一个1　0　1　0这样交叉的符合你需求的数组，按你的理解写出
你的条件，用F9抹黑查看，看是不是与你期望返回的数据相符，如果不符再进行分析调试。
　如果F9后，有#N/A值出现，可能是条件数据区域不相匹配;有其它错误出现，可以按错误的类型进行分析；如果没有
错误值出现，但是得出的结果还是与期望不符，就要检查你设置的条件是否有遗漏，或是条件设置有误等等。
　对于与期望不符的情况，你可以用F9抹黑了条件，与出现误差的数据进行比对，看与期望的差异是由于哪一个因素所
引起的，再针对这一方面进行公式的完善。
难点解析：
　条件的设置！
　我第一问有讲过，这问我还是强调来说明这个问题。
　其实第一问IF后的条件设置与SUM()里面的条件设置是一样的，如果你真的可以熟练的设置你需求的条件，那么再套了
固定的格式公式，解决排序，筛选，之后的多条件计数，求和将易于翻掌。
　下面请允许我再罗嗦并多次的讲这个条件设置问题：
　1.关于多条件或
　有人说或，就有+啊。
　当然这话没错，但是我这里提一点，关于或的条件的可交性。
　就是或的条件是不是可以同时满足的一个注意点。
　如果或的条件可以同时满足，那么对于同时满足两个或以上的条件，那么我们条件１+条件２+。。这样的格式计算出
来的条件判断数组就会有２，或大于２的数据产生，对我们后续的SUM求和计数都将产生与期望不符的结果，当然IF对
这个没有影响。
　那么如何去判断两个条件是否可交？（可交就是可同时成立，下同）。这个一般要依据实际的数据采集的情况来定，
没有一定的定势。
　那么对于这样可交的或条件，其实我们可以在外面套一个if就可以。If(（条件１+条件２+条件３.。）>0,
其实这里我们可以这样想，回到sum(if(这样的形式的话，就更好理解了。这样if后的条件就可以等同第一问的了。
　所以我们的目标是生成一个1,0的期望求和计数区域，你要这个原则，向着这个原则，设置条件应该不是难事。
　２.关于复杂的条件判断。
　A.注意数值型文本，单元格格式的转换。多接触一些文本处理的函数，或者添加辅助列来加以完成。
　B.先一个一个条件进行设置处理，再来看共同点，进行合并简化；
　3.简单多条件求和计数的&简化
　就是把所有的直接的条件判断用&连接起来，再把条件区域也用&转接起来，进行一个＝比较。
易忽略点：
　1.处理空值，错误值有时用sum(if(形式更为合理。
　2.对于多条件，可以根据实际的情况分析合并设置；
　3.avarage,max,min等函数同此用法，可予以借鉴；
　４.条件中可以再嵌套条件，但是各条件与数值源应保持相同维度。

昨日有事，误了一问，有生愧疚，望众见谅。明天续展，愿为关注，不足之处，恳请雅涵。

direnjie

仰望楼主。