[答疑解惑]函数公式解释专用帖

gouweicao78 · 发表于 2006-4-13 10:40

本帖最后由 wangg913 于 2012-9-14 23:19 编辑

[注]文字解释属原创行为，未经允许，请勿转载

为使本帖更具有连续性方便大家阅读，本人删除合并了部分帖子，并未扣分，请大家见谅！再次声明，不要发感谢帖和顶帖，可用短信息或在原帖编辑

在论坛学习已有时日，常见新手求助后高兴地拿着答案回去了，可是问题解决了，却因为不能明白公式的含义，碰到类似问题自己还难以举一反三应用甚至连一点小改动都需要再次求助；对函数公式略知一二者因不明公式含义不易拓展思路……等等现象，虽大多数都能在原帖得到热心版主、坛友的解答，屡见妙答，但没见到的人又重新发帖问及类似问题，不利于各种问题的综合汇总，遂发此帖作为公式解释专用！

由于本人工作原因，如未及时解答，请见谅。
十分感谢山菊花、hbhfgh4310、chrisfang等好友的支持！

坛有坛规，帖也有帖规：

1、本帖为“解惑”帖，非“解题”帖。如有需要解题者另发帖求助。需解释公式含义的分2种情况：

已发帖求助得到解决的，或看不懂别人帖子中的解答的，请将链接及楼层（比如http://club.excelhome.net/……

第XX楼）和对不懂的地方的简要说明以跟帖形式求助（或者最好用发短信息给加盟此帖的高手们），无须另传附件；

论坛无链接的帖子，请传附件并在附件中说明不解之处。

2、谢绝一切“顶帖、感谢帖”——避免因占用空间造成帖子不便他人阅读，如果要谢，请用实际行动支持论坛，是论坛给了大家学习的良好平台！如果执意要“顶”，只好建议版主删了。

3、竞赛题请看发题者的总结。一般结题会有详细的解说。

4、由于本人才疏学浅，解释难免有不到之处或者错误观点，敬请谅解，并发短信息给我或别的解答者，以便在原解答楼层更详细说明，避免占用空间。

补充内容 (2017-10-29 08:48): http://club.excelhome.net/thread-117862-1-1.html

gouweicao78 · 发表于 2006-4-13 10:40

怎样学习函数公式

这是很多新手最想知道的事，函数那么多，要从哪儿学起呢。我个人谈点小体会：
1、“学以致用”，用才是目的——就是你要和将要用到的东西先学。比如你根本用不上财务、工程函数，没必要一下子就去看那些专业性很强的东西（嘿嘿，那些我基本不会），这样就容易入门了。基本上函数用得最多的逻辑判断和查找和引用这2类函数了。先不要急于学会“数组”，自己常用函数的普通用法有个大致的用法了解之后再去看它的数组用法。
2、善于搜索，见置顶帖在中文Excel应用论坛的最佳学习方法。搜一下，能找到更多的解答；善于求助发帖求助要描述清楚附上必要的图文并茂的附件，容易得到解答，而且锻炼了自己的表述能力。
3、除了“求助”式学习，还要“助人”式的学习，相信这一点是众多论坛高手们都经历过的。只要有时间，少看一会儿电视少聊一会儿QQ少跟同事吹一会儿牛，到论坛上看看有没有别人不懂而你懂的，别怕出糗，是驴是马牵出来遛遛，相信你热心帮人不会被嘲笑的，况且，抛砖引玉，说不定你抛的对别人甚至对高手来说也是块宝玉呢。而，助人助己，有了越来越多的“求助”者给你免费提供了练习的机会，练得多了再综合各种思路的比较，自己就有了一些想法，你的水平肯定与日俱增。
4、一口气吃不成胖子，多记一些学习的体会，日积月累，你就是高手了。另外，到“优秀会员园地”瞧瞧，不少学习心得哦。

[此贴子已经被作者于2006-4-14 8:26:24编辑过]

补充内容 (2017-10-29 08:48):
http://club.excelhome.net/thread-117862-1-1.html

gouweicao78 · 发表于 2006-4-13 10:40

如何解读公式

我也谈点小体会吧： 1、多看函数帮助。各个函数帮助里面有函数的基本用法和一些“要点”，以及对数据排序、引用类型等等的要求。当然，函数帮助并不囊括所有函数的细微之处，不然，也就不会有那么多求“解释”的帖了。
2、庖丁解牛——函数的参数之间用逗号隔开。（别笑话，这是最最基本的基本功，单个函数没啥，组合多个函数的公式就是靠它了），这些逗号就是“牛”的关节，先把长公式大卸八块之后逐个看明白了再拼凑起来读就容易多了。
3、独孤九剑——开个玩笑啦，这里是取谐音“F9键”。F9键用来“抹黑”公式对解读尤其是数组公式有非常强的作用，不过如果公式所含数据区域太大（比如上百行）你可以改变一下区域。具体方法：比如下面这个简单数组公式 =sum(if(A1:A3>0,B1:B3))，用鼠标在编辑栏把把A1:A3>0部分“抹黑”，按下F9键，就看到{True;True;False}（假设A3不满足），表示if的条件是这么3行1列的逻辑值数组。——别忘了，看完之后按ESC取消哦，否则公式就变了。
4、公式审核——就是工具〉公式审核〉公式求值那个有fx的放大镜，与F9功能基本相同，能一步步看公式运行的结果（但两者效果均有一定限制，具体情况尚未明了，fx有时会造成Excel的重启）。配合着用吧。
5、注意定义名称：很多人都回用定义名称来使公式简洁、直观，更容易让“庖丁”来解，按下Ctrl+F3可以查看定义名称（或者菜单〉插入〉名称〉定义），如果名称是对单元格区域的引用，这当你点击名称框下方的“引用位置”时，会在相应区域出现虚线选择框。还可以选个空白的地方，按下F3键，选择“粘贴列表”把定义的名称复制到空白区域。
6、关于函数的一些深层次的东西，请看置顶帖：[寻根究底] 函数用法讨论专辑。

[此贴子已经被作者于2006-4-13 11:56:38编辑过]

gouweicao78 · 发表于 2006-4-13 10:41

公式解读之基础知识篇：

1认识运算符
*、+号在数组运算中，我们通常理解为“且”、“或”，但是他们与and、or函数是有着本质的区别的。请看另一贴的论述：关于运算符*、+与and、or在逻辑判断中的应用

2、认识row()\row(1:1)\rows($1:1)及column()函数。
常见用数组公式取得多条记录的开头都是=if(row(1:1)>XXX,""……表示当公式下拉复制超过一定行数（满足条件记录的个数）时，显示为空。书写方式有row()-n、row(1:1)、rows($1:1)，前2个得到的是一个{}1行1列的数组，后一个得到的是一个单值，此外，如果未知数据行数要形成“多单元格数组公式”，则必须使用row()-n的形式；如果已确定要得到记录的行数，则也可以用row(1:X)实现。比如：=IF(ROW(1:6)>COUNTIF(A1:A6,">0"),"",SMALL(IF(A1:A6>0,A1:A6),ROW(1:6)))——谢谢gvntw版主补充。
备注：n是公式所在第一行的上一行的行号。比如在A7输入第一个公式，则n=6。
由于column函数与row函数很相似，就不在此赘述了。
关于3种形式的讨论请参考：函数讨论帖18

3、认识“值”类型和数字格式：
数值、文本以及逻辑值、错误值。这里就讲讲数字吧。
常犯的错误：见到“数字”就以为是“数值”了，其实数字分“数值型数字”和“文本型数字”。（注：日期是数值的一种特殊形式。）。且，由于数字格式不一致，容易导致公式错误如查找不到或不能求和等运算。
文本型数字转换为数值型数字的方法：Value()函数转换，*1、/1、+0、-0、--（两个减号）转换，这几种转换是在函数公式里的方法。
基础操作法：a、复制一个空白单元格，选择需要转换区域，选择性粘贴为“加”；b、选择1列数据区域，菜单〉数据〉分列〉完成（前一步可以选择为日期、文本）；c、利用工具〉选项〉“错误检查”选项，选择需要转换的区域，点击头一个单元格左上角出现的感叹号〉“转换为数字”。
函数公式得到结果为文本的情况：使用文本函数比如Text、Char、CONCATENATE、Fixed、Left、Right、Mid、Substitute等函数以及文本合并符&得到的均为文本型。
数字格式多种多样：设置单元格格式〉数字选项卡下面除了“常规”，点击其他任何选项以及右边相应格式，然后再点击常规最下方的“自定义”就可以看到刚才所选格式的表达方式了，这些方式都可以在自定义格式和Text函数第2参数中得到应用。具体可参考论坛中关于自定义格式的帖子。

4、认识引用：
（1）引用样式：
A1——用列标字母与行标数字表示，A1表示第1行第1列：
R1C1——用R与行标数字、C与列标数字表示，R1C3表示第1行第3列，就是C1单元格。
（2）绝对引用与相对引用：
A1样式：A1——相对引用，横竖拉动公式都会变；A$1——列相对行绝对引用，横拉列标变而竖拉行标不会变
$A1——列绝对行相对引用，横拉列标不会变而竖拉行标会变。$A$1——横竖拉都不变。
R1C1引用样式——R[-1]C[3]——当前单元格的往上1行往右3列的位置，比如当前单元格是B2，则R[-1]C[3]表示E1单元格。
（3）循环引用：
请参阅轻松控制循环引用完全教程

[此贴子已经被作者于2006-11-25 11:08:19编辑过]

[ 本帖最后由 gouweicao78 于 2009-9-18 00:05 编辑 ]

gouweicao78 · 发表于 2006-4-13 10:42

关于数组公式入门到深入理解，请参考[URL=http://club.excelhome.net/viewthread.php?tid=68777&px=0][精华荟萃]函数应用之靓帖分类汇总[/URL]
数组公式解读之基础知识篇：
1、概念：数组、多重计算、数组公式（此部分为讨论帖5的结论）
数组：就是具有一定行列尺寸的单元格元素或数值、文本、逻辑判断等组成的单、多元素的东西，比如：
单元格A1在数组公式中也可以算1行1列的数组，A1:A2是一个2行1列的单元格数组，A1:B10是一个10行2列的单元格数组，{1,2;3,4}是一个2行（用分号隔开）2列（用逗号隔开）的常量数组，{True,False}是一个1行2列的逻辑值常量数组等等。
多重计算：对一组或多组值执行多重计算。
数组公式：Excel自带帮助文件“数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ({ }) 中。按 Ctrl+Shift+Enter 可以输入数组公式。”——记住按三键。
在这里：数组公式仅仅是一个“称呼”，用以区别“普通公式”——不按三键。
经过大多数人讨论，尽管意见各异，为了统一定义方便理解，我们称“只有按Ctrl+shift+enter结束的公式才是数组公式”。按这3个键的的作用在于通知Excel：“嘿！我是数组哦，要对我执行的是多重计算，别搞错了哦”！
比如=sumproduct(条件1*条件2*……*统计区域)这么一个常用的多条件求和公式，只要它不用按三键，我们就称为“普通公式”

计算单个结果的数组公式：用数组公式执行多个计算而生成单个结果。——在单个单元格输入公式并按三键形成的数组公式。
计算多个结果的数组公式：使数组公式能计算出多个结果，必须将数组输入到与数组参数具有相同列数和行数的单元格区域中。——在多个单元格区域输入公式并按三键组成的一个整体的数组公式，我们称之为“多单元格数组公式”。多单元格数组的理解可以参考一下本帖46楼的“戏说”。

[此贴子已经被作者于2006-11-25 10:57:00编辑过]

gouweicao78 · 发表于 2006-4-13 10:42

呵呵，这个预留的楼层就用来做本帖的目录吧。
山菊花在7楼给了不少精彩解释的链接，用幽默风趣的文风，色彩绚丽的背景；hbhfgh4310在10楼对各种条件下“不重复值”的解题过程先大卸八块模拟每一个细节再完整拼合起来；让我们一起轻松领略函数公式的无穷魅力吧！
1楼：帖规；2楼：学习心得；3楼：解读公式方法；
4楼：函数公式基础知识
15楼：多条件筛选单列不重复值
19楼：取得工作表名称
26楼：关于&""
28楼：数值按多个关键字排序
33楼：多条件计数
41、42楼：关于“隐含交叉”
43楼：Sumif经典用法——三维引用多表汇总
46楼：“戏说”数组和多单元格数组公式
47楼：得到含有备注的算式的计算结果
48楼：VLOOKUP三维引用跨多表查询
68楼，102楼：关于MMULT
71楼，77楼：获得滚动文字
73楼，112楼：从身份证提取出生日期
78楼，79楼，106楼：中国式排名
114楼：定义名称问题
118楼：18位身份证号码
125楼：提取不重复值的区域内存数组公式
136楼：gvntw版主的只用函数实现分表合并与总表拆分。 公式解释
137楼：gvntw版主的函数版：本页小计、累计 OR ONLY 本页小计公式解释
139楼：[函数用法讨论系列10] LOOKUP的查找策略！之流程图再谈二分法

[此贴子已经被czzqb于2006-12-16 10:17:37编辑过]

山菊花 · 发表于 2006-4-13 11:10

5-1、Match + Row +Small 取不重复值

5-2、Indirect + Text + Min 取不重复值

6-1、理解公式 COUNTIF(A1:A10,A1:A10)

6-2、理解公式 = SUM(1/COUNTIF(A1:A10,A1:A10))

6-3、理解公式 = INDEX(A2:A7,MATCH(,COUNTIF(B$1:B2,A2:A7),))

7、查找前三名的成绩

8、理解公式 Large(A3:A16,Row(1$14))

9-1、公式解释：MATCH(0,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),)

9-2、公式解释：=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(B2,"元",REPT(" ",15)),ROW($1:$99),15),),2,15),"0;;;!0"))

10、根据班级名称定义动态区域

11、15位身份证号码升级到18位

12、INDIRECT()函数的理解

13、SUMIF()函数中求和区域与条件区域的自动匹配

14、一个关于三维引用的实例

15、只有按三键的才是数组公式吗？

16、区域数组公式与单值数组的区别

[此贴子已经被作者于2008-9-4 11:24:55编辑过]

teawater · 发表于 2006-4-14 11:45

老大：这是个按日期排序数组公式，请帮忙解释下，

=IF(MIN(IF(($C$2:$C$12=C2)*($B$2:$B$12="合格")*($J$2:$J$12<>0),ROW($B$2:$B$12)))=ROW(),"先出","")

复制代码

又：建议此帖置顶，找了好久才找到！

gouweicao78 · 发表于 2006-4-14 12:00

to teawater：欢迎哦，第一个客户。呵呵，这两天都在编辑上面的心得呢。

应该附上原帖链接的，http://club.excelhome.net/viewthread.php?tid=161811&px=0第2楼，四海飘零作品。

解释：

1、数组公式，记住按三键。

2、庖丁动刀：=IF(MIN(IF(($C$2:$C$12=C2)*($B$2:$B$12="合格")*($J$2:$J$12<>0),ROW($B$2:$B$12)))=ROW(),"先出","")

绿色部分表示3个条件都成立——理解请看上面4楼链接（认识运算符）。其返回值是row(B2:B12)，即当“品名与当前行的品名C2相同，物料状态为合格，剩余数量不等于0”时，返回物料状态对应的行号。

min(if(3个条件成立，返回对应行号数组))——取得符合3个条件的对应行号(该附件的3、4、5行）的最小值3。

如果min=row()当前行号，则返回"先出"。

也就是说：符合那些条件的记录不止一个，用min找到第一个，标示为“先出”！P> 更正：由此过程可以知道，这个并非“按日期排序”。

[此贴子已经被作者于2006-4-14 12:01:11编辑过]

hbhfgh4310 · 发表于 2006-4-14 12:58

关于提取基础资料中符合条件的记录此类问题在论坛中经常出现，有些会员希望得到满意的解释，但考虑到提问者对EXCEL的了解程度很少做过详细的解释。Gouweicao78好友的提议非常好，我就此问题做个专门的解释，希望能给大家带来一些方便。关于这类问非常多，大致可以分为以下几类：一、列示单列符合一个条件的记录二、列示多列符合多个条件的记录三、列示不重复值四、列示重复次数最多的值五、按重复次数的大小列示不重复值六、列示区域内符合条件的记录七、列示单元格内的不重复值八、有条件的列示单元格内的不重复值 …… 类似的问题还有许多，只要掌握了原理，万变不离其宗，再复杂的问题也能解决，把复杂的问题拆开、分解，分别进行处理就没有解决不了的问题。由于本人水平有限，加之问题千变万化，很难把所有的问题都一一进行讲解。跟此帖的目的一是让更多的人喜欢excel，掌握excel的强大功能；二是被Gouweicao78兄的热心所感动。我们都是有工作的人，能拿出大段的时间做这件事全凭兴趣，相臛ouweicao78兄一定付出了许多的辛苦和宝贵的时间。再次对Gouweicao78兄的热心表示感谢，希望有更多的人像论坛中的铁杆会员一样喜欢EXCEL。具体讲解见附件。

eyMAkuzG.rar (20.45 KB, 下载次数: 9919)

[此贴子已经被gdliyy于2006-4-16 22:46:37编辑过]

		自动登录	找回密码
密码			免费注册

[推荐] [答疑解惑]函数公式解释专用帖

评分

评分

评分

评分

评分

评分