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

zh_jiang

   6月26日74楼Chrisfang大师关于使用基本函数显示滚动字幕的解释，让我顿开茅塞，明白不少，也更佩服大师奇思妙想的高超。但是，如何执行这个函数，按F9键不放手的作用是连续执行这个函数吧，F9键好像不是自定义的宏或名称，那是怎么设置的呀？笨人太笨，理解不了，仍然是一头雾水，敬请给于指教！

    另外，还是Chrisfang大师的使用基本函数显示滚动字幕中的问题，在给出的不用按F9键不放的例子中，放了个自动执行宏，其代码为：

  Sub Auto_Open()

   Worksheets("Sheet1").Range("E6").Value = Time

   Application.OnTime Time + TimeSerial(0, 0, 1), "Auto_Open"

  End Sub

  这个宏是怎么样代替了按F9键不放的?敬请给解释一下代码的含义，谢谢！

chrisfang

回复zh_jiang 朋友：

原贴地址链接

首先，请不要称我为大师，无论如何我连Expert都算不上，更称不上Master了，这个称呼会拉远我们的距离。

F9是excel里面的快捷键，所谓快捷键就是软件本身就有相关功能，而用某些按键就能方便调用的意思。就好比F1可以打开帮助菜单一样，F9的功能是“人工重算”的意思。在excel工具——选项——重新计算的选项设置中，有选择“自动重算”或“手动重算”，另外边上还有个按钮“重新计算所有文档(F9)”，F9的意思就是“重新计算所有文档”。它会把当前文档中的所有公式重新计算一遍生成新的结果显示出来。就这么简单。

因为我的公式里有个关键函数now()，它返回的是当前系统时间，这个函数的返回值是随着时间流逝在不断变化中的，所以我要用F9来重新计算使得这个函数返回值不断地得到更新。按住F9不放的意思，就是不断的生成当前的系统时间值来为我的函数公式提供新的参数。这个思路也很简单，没有什么高深的地方。

所以简而言之，我的那些效果只是些小把戏，实现起来并不困难，稍微构思一下函数算法就基本可以完成，所以特别适合刚接触或学习函数不久的新朋友学习。等到你对函数和excel的基础应用有了比较全面的了解，这些东西对你来说都不在话下了。套用一句现在的流行话：相信我，你能做到。[em17]

另外，你所提供的VBA代码并不是我写的，你如果回去看看帖子会看清，那是35楼的shewoyu 朋友提供的，虽然我个人感觉他的代码效果并不是十分理想。我在帖子的18楼提供了不需F9的VBA代码，那里是我自己的代码。由于在这个帖子里只讨论函数公式，我不想破坏gouweicao78版主的规矩，所以关于VBA代码你还是放到程序开发版讨论好了，或者我们可以另外开帖子讨论一下，简而言之那个代码也并不复杂。

最后，虽然我做的东西不怎么高明，但还是很高兴你能喜欢，谢谢！

[此贴子已经被作者于2006-6-30 17:59:36编辑过]

听风看雪

gouweicao78版主你好

   中国式排名的公式{=SUM(IF($B$3:$B$21<=B3,"",1/(COUNTIF$B$3:B$21,B$3:B$21))))+1}中，        IF($B$3:$B$21<=B3,""  这部分我的理解是先判断$B$3<=B3，再判断$B$4<=B3……依次类推。第一次判断$B$3<=B3的结果应为TRUE，应执行 ""，可结果却是1，我的理解错在什么地方？应该怎样正确理解这个公式？                                                                                         谢谢                                                   

                                                                    听风看雪

        再次感谢gouweicao78版主的帮助，这样详细的解释，不仅是对本公式的理解，更重要的是对这类问题的理解，对数组一直都很头疼，通过本例，对数组有了一定的认识了。不知用什么样的语言来表达感谢。如果gouweicao78版主有机会来我们太原，一定请你喝山西杏花村的好酒呀。

                                                                    听风看雪致敬

[此贴子已经被作者于2006-7-3 18:38:00编辑过]

gouweicao78

数组公式最重要的用意就是执行“多重计算”，为简化理解，我们用这个来代替

A1=20、A2=20、A3=10、A4=30（中国式排名结果：2、2、3、1），公式为：

=SUM(IF(A$1:A$4<=A1,"",1/COUNTIF(A$1:A$4,A$1:A$4)))+1

1、A$1:A$4<=A1——一次执行多重计算返回4个逻辑值{TRUE;TRUE;TRUE;FALSE}

2、COUNTIF(A$1:A$4,A$1:A$4)——一次执行多重计算返回4个逻辑值{2;2;1;1}（表示包含2个20、2个20、1个10、1个30）

3、1/COUNTIF(A$1:A$4,A$1:A$4)——返回{1/2;1/2;1/1;1/1}即{0.5;0.5;1;1}

4、IF(A$1:A$4<=A1,"",1/COUNTIF(A$1:A$4,A$1:A$4))就是

IF({TRUE;TRUE;TRUE;FALSE},"",{0.5;0.5;1;1})就是{"";"";"";1}

所以=SUM(IF(A$1:A$4<=A1,"",1/COUNTIF(A$1:A$4,A$1:A$4)))+1就是

=SUM({"";"";"";1})+1=1+1=2就是我们要的第2名，其他同理。

kendy187

QUOTE:

以下是引用gouweicao78在2006-5-8 16:05:05的发言：

得到含有备注的算式的计算结果

我们知道宏表函数EVALUATE(formula_text)可以用来将文字计算式的转化为公式并计算结果，且常用于工程预算等

比如A1单元格输入=3+5*2-6^2之类的，我们既想在A列显示公式，又想兼得计算结果，这时候就可以用Evaluate函数了，方法：在B1单元格，插入〉名称〉定义一个名称比如Result=Evaluate(A1) 然后在B1输入=Result就可以得到上式的计算结果“-23”了。这并不难理解。

然而，出于一些实际需要，往往写公式的人要做一些文字说明让人清楚地了解计算式的由来，比如A1单元格 =20[長]*35[高]+5.5[長]*23[高]-0.9*1.5[扣門洞]，让人一目了然地知道计算依据，并且不容易犯漏项等错误，那么怎么得到去掉这些文字备注之后的计算结果呢？

同样，在B1插入〉名称〉定义名称：X=Evaluate(SUBSTITUTE(SUBSTITUTE(A1,"[","*ISTEXT(""["),"]","]"")"))，然后在B1输入=X就可以了。

这么一个公式到底是什么意思呢，不要看着一堆的"["之类的符号发晕：首先Evaluate我们是知道的，Substitute也是知道的，不妨，在B1输入一个公式=SUBSTITUTE(SUBSTITUTE(A1,"[","*ISTEXT(""["),"]","]"")")——既Evaluate里面的部分，看看得到了什么：

20*ISTEXT("[長]")*35*ISTEXT("[高]")+5.5*ISTEXT("[長]")*23*ISTEXT("[高]")-0.9*1.5*ISTEXT("[扣門洞]")

就是——再回头看公式：黄色部分就是把A1里的"["左边的中括号替换成*ISTEXT(["，绿色部分，就是把A1里的"]"右中括号替换成]")，仅此而已！比如[高]变成了*istext("[高]")；

也许你会说——明明公式里是"*istext(""["（蓝色为一对双引号，把里面的东西作为字符串输入）红色部分是一对双引号，怎么得到的结果是单引号呢？呵呵：试想一下，加入红色部分是1个双引号，那么，它首先要和左边的蓝色双引号配对，就没办法把后面的左中括号包进去了。用这个反过来想就知道为什么这里是2个双引号而不是1个了。

通过上式解说，我们知道定义名称的Evaluate要计算些什么了，因为istext("[高]")是判断[高]这么个东西是否文本，当然答案是True了，20*True*35*True+……就是20*1*35*1……所以返回的正是我们想要得到的计算结果了。

QUOTE:

再次对“庖丁解牛”说一下，要找准关节入手！括号的配对是一个函数成为完整个体的表现；逗号的配对，是一个函数的参数成为完整个体的表现；英文双引号的配对，是一串字符串成为完整个体的表现。

X=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B$1,"[","ISTEXT(""["),"]","]"")*"))

这是[**]标注在数字前面

X=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B$1,"[","*ISTEXT(""["),"]","]"")"))

这是[**]标注在数字后面

请问要如何关联才能在同一计算式中数字前后都做标注(如:[A]1+2

山菊花

用宏表函数不好解决，用自定义函数：

QUOTE:

Public Function Sjh(ByVal Target As Range)
    Dim i%, j%
    Sjh = Target
    Do While InStr(Sjh, "[") > 0
        i = InStr(Sjh, "[")
        j = InStr(Sjh, "]")
        Sjh = Left(Sjh, i - 1) & Mid(Sjh, j + 1)
    Loop
    Sjh = Evaluate(Sjh)
End Function

j7vbxmQ8.rar (6.17 KB, 下载次数: 178)

2006-7-17 22:32 上传

点击文件名下载附件
[答疑解惑] 函数公式解释专用帖

kendy187

QUOTE:

以下是引用山菊花在2006-7-17 22:33:53的发言：

用宏表函数不好解决，用自定义函数：

QUOTE:

Public Function Sjh(ByVal Target As Range)
    Dim i%, j%
    Sjh = Target
    Do While InStr(Sjh, "[") > 0
        i = InStr(Sjh, "[")
        j = InStr(Sjh, "]")
        Sjh = Left(Sjh, i - 1) & Mid(Sjh, j + 1)
    Loop
    Sjh = Evaluate(Sjh)
End Function

谢谢大侠了

不过本人实在愚昧,VB的一点都看不懂

只用函数实现不了吗?

yhcyd

我在2000操作系统下有一个按单元格内字符颜色计数的公式，现在改成xp

操作系统却无法使用，显示＃NAME,不知何故，望高手指点！

littleliuyy

请问一下有什么方法能将每次筛选后的数据自动加总，而不用每次都要输入公式．除了ＳＵＢＴＯＴＡＬ这个公式外，还有没有其他？因为我的原数据是不断增加的，使用ＳＵＢＴＯＴＡＬ这个公式就得不断地改变数据区域．

chrisfang

QUOTE:

以下是引用yhcyd在2006-7-22 11:50:49的发言：

我在2000操作系统下有一个按单元格内字符颜色计数的公式，现在改成xp

操作系统却无法使用，显示＃NAME,不知何故，望高手指点！

这个公式是用宏表函数做的，需要excel设置允许宏的允许。

你可以在工具－宏－安全性中设置为中。然后关闭excel，重新打开那个文件，出现提示“是否允许运行宏”时选择允许即可。