学而不思则罔，思而不学则殆。

lrlxxqxa

经过一个阶段的看帖、回帖、培训、练习。。。我要在积累的同时进行总结，妈妈从我小就说，好记性不如烂笔头，不能像狗熊掰棒子一样，我要听妈妈的话，学而时习之，学以致用。

1，2楼暂且留作目录区域吧

lrlxxqxa

为方便日后阅读，占此楼层待编辑。

lrlxxqxa

跟随moon2778麻利的讲述，又重新把相关的基础知识过了一遍。感觉有一些概念更加清晰了。

先回顾一下内容纲要：
一、公式概述
二、公式的输入、编辑和复制
三、丰富的引用方式
四、函数概述
=========================================================

一、公式概述：

1. 公式概念：指对工作表中的数值执行计算的等式。
2. 必须遵循的原则：
1) 以等号“=”作为开始（即使使用加减号开始，也会自动前置等号）
2)  等号“=”之后紧接运算数和运算符。
3.运算数和运算符
1) 运算数：即参加运算的参数，可以是数值常量、单元格引用、名称或工作表函数。
2)  运算符：
算术运算符： +  -  *  /  %  ^
比较运算符： =  ><  >=  <=
文本运算符： &
引用运算符：  ,  :  _（单个空格）
注意2点：
1、输入公式并不一定要输入“=”开始，直接输入“+”或“-”号，回车确认后，系统自动会在前面前置=；
2、引用多个区域时用" "（空格）间隔，是取多个区域的交集，这个知识点以前自学的时候遗漏了，补上。

二、公式的输入、编辑和复制

1.输入公式的两种方法：

1）鼠标单击单元格实现引用；
2）手写公式输入；
2.公式的编辑：
1）双击单元格；
2）选中单元格，然后使用F2键；
3）利用公式编辑栏；
3.复制：
1) 鼠标拖动（鼠标放在选定单元格的右下角，出现十字时拖动鼠标，一次只能向一个方向拖动）
2)编辑--选择性粘贴－公式
3）自动填充（右下角出现十字时，双击鼠标左键）缺点是：左侧有空单元格时会中断填充，会以右侧单元格长度计算。
4）先选择目标区域，在编辑栏上输入公式，然后按Ctrl+Enter键（选择的时候可以F5定位区域，也可以直接在左上角的名称栏直接定位区域，不必像老师一样shift点击首尾单元格；ctrl+shift+↓也可以快速选中区域，此处注意灵活运用）

三、丰富引用方式

1.引用： 指在公式中，当用户输入单元格地址作为参与运算的参数，就是单元格进行引用
2.多种引用方式
1）A1样式及R1C1
2）相对引用，绝对引用和混合引用：形式分别为A1,$A$1,$A1或A$1
3）引用其他的工作表和工作簿区域：形式分别为 工作表!引用区域；[工作名.扩展名]文件名!引用区域
A1的样式：用字母和数字标识单元格地址，其中字母标识列标，数字表示行号
R1C1的样式：先ROW 行后column 列，与A1样式行列顺序相反。
for example
引用格式        对应区域
F2        第F列和第二行交叉处的单元格
D1:D3        第D列中第1~3行的单元格
D2:G2        第二行中的D列到第G列的单元格
B2:F7        B列第2行到F列第7行的单元格
H:H        H列全部单元格
A:C        A列到C列的全部单元格
3:3        第3行的全部单元格
2:10        第2行到第10行的全部单元格

四、函数概述

1、函数是由excel 内部预先定义用来执行计算、分析的功能模块。
2、函数通常由函数名称、括号、参数、半角逗号组成
3、函数的输入
1） 使用插入函数向导
2） 手工输入函数
引用运算符：
，逗号表示引用不相邻的多个单元格区域
：冒号表示引用相邻的单元格区域
_ 空格表示引用选定的多个单元格的交叉区域

对于举例中的九九乘法表，暂停视频后自己头一次做了下，后再看和老师的方法不太一样，感觉很多东西值得进一步发掘，今晚到此为止，明天继续。

[ 本帖最后由 lrlxxqxa 于 2010-8-3 16:07 编辑 ]

李甲

哇…………沙发，从公式开始学起，好长哦

几小米

自己的记录贴
这样日后好找

YZLSZAJ

这是记录在EXCEL HOME 论坛里的成长之路。

lrlxxqxa

发现对于r1c1格式有了初步认识后，很有启发，这样的话用在vba定位单元格岂不是很方便吗？

做了一个a1和r1c1格式的切换按钮，可以转换工作表的单元格引用方式。当然，如果在工具选项里面直接设置也是可以的，我比较懒就只想按一下搞定。

公布代码如下：

1. Sub RC_A1()
   
2.     With Application
   
3.         If .ReferenceStyle = xlR1C1 Then
   
4.             .ReferenceStyle = xlA1
   
5.         Else
   
6.             .ReferenceStyle = xlR1C1
   
7.         End If
   
8.     End With
   
9. End Sub

复制代码

把上述代码赋予按钮即可。

关于r1c1的理解，论坛搜到个很好的帖子，不过shuiyuan的附件中有错误，我更改了一下重新传上来
http://club.excelhome.net/viewth ... mp;page=1#pid548841
看了附件的图片能够很好的帮助理解。

[ 本帖最后由 lrlxxqxa 于 2010-6-3 16:38 编辑 ]

Kimez12

建议lz更新点VBA入门心得，呵呵。

lrlxxqxa

总结分为三大部分：课堂概要；课外爬帖扩展；作业扩展。

课堂概要分为六部分：

一、函数公式提示工具
二、常见错误提示及分析
三、函数嵌套结构
四、公式限制及突破
五、公式审核工具
六、逻辑函数
====================

一、函数公式提示工具：

1、参数列表提示
2、公式结构剖析
3、代码重复利用
4、帮助窗口开启
5、提示工具启用
6、Ctrl+Shift+A 自动补全函数参数

二、常见错误提示及分析：

1、#REF!
成因:1.引用的单元格地址失效；
     2.返回无效引用；
解决办法：注意检查引用的单元格地址是否在有效范围内；

2、#NAME?
成因:1.函数名称拼写错误；
     2.名称拼写错误；
     3.误当名称错误；
解决办法：编写函数公式时细心、多检查，规避拼写错误；

3、#N/A
成因：1.目标数据缺失；
      2.源数据缺失；
      3.参数数据缺失；
解决办法：编写函数时检查核实目标数据、源数据、参数是否填写完整；

4、#NULL!
成因： 结果值为空值/不存在
解决办法：预先检查计算范围，避免空值的产生；

5、#VALUE!
成因：1.数值、文本、数组、单元格引用等类型错误；
      2.文本型数字当成数值使用；
解决办法：正确填写各单元格数据类型，尤其注意不要将字符串数字当成数值；

6、#NUM!
成因：1.数值范围错误；
      2.数值类型出错；
解决办法：仔细检查公式中的数值范围和类型；

7、############
成因：1.列宽不够（单元格内容显示不完整）；
      2.数值超限（日期）
解决办法：为单元格设置适当的列宽；在日期类型数据的合理范围内填写数值；

8、#DIV/0
成因：除数为0时的出错信息
解决办法：规避出现除数为0的情况；

    通过老师的系统讲述，详细了解了这么多常见错误代表的意思。在实际工作中，我常用if+iserror函数容错，在2007版本中可以直接使用iferror函数：=if(iserror(原公式),"",原公式)，或者iferror（原公式,""）

三、函数嵌套结构

嵌套：用一个函数或公式去充当另一个函数的参数。

嵌套条件：函数公式必须能适应成目标参数类型。

单个函数毕竟功能有限，但我们可以让多个函数嵌套协同作业，解决复杂的问题，体现函数的魅力。在使用嵌套函数时，时常要用到函数提示工具，F9和帮助。熟练使用嵌套函数来解决复杂的问题是我们的必修功课。另外在实际运用中还要注意限制，比如if函数最多7层嵌套，如果还不够，可以考虑用choose函数代替。

四、公式限制及突破

1、公式的长度限制：1024个字符；

2、嵌套层次限次：7层；

五、公式审核工具

1、拨草寻蛇 F9

2、工具--公式审核

F9键实在是便利好用，有助于让我们看清楚函数的运算过程和结构

F9键之后可以ctrl+z或Esc恢复；两种方法各有优势，ctrl+z可以恢复上一步，保持不退出编辑状态；Esc直接退出编辑状态。

在我们输入长公式的时候，不妨在=前输入一个 ’或者空格 ，把公式变成文本，想好了再继续编辑！

六、逻辑函数

1、比较函数：<、>、>=、<=、=、<>等

2、信息函数：ISBLANK、ISNUMBER、ISERROR等

3、逻辑运算：AND、FALSE、IF、NOT、OR、TRUE

4、逻辑转换：0、1

5、逻辑活用：筛选
=====================================================
第二部分：课外爬帖扩展：

一、函数编辑小技巧：

编辑公式之函数提示工具

作用方向为编辑公式对“函数提示工具”的影响。反方向的应用同样具有极强的实用性。单击“函数提示工具”上的形式参数，就会选定对应的参数字符，这是一项非常有用的功能，不仅可以用来审查嵌套层次是否符合编辑思路

F3功能键，按F3功能键就能弹出当前可用的名称（“名称”可以理解为一个变量，封装了一个公式），然后就可以直接使用鼠标选取需要的名称到正在编辑的公式中了。这么做同样是提高了录入时的准确率...同时，另一个好处依然是不会打断思路，因为不用去思考到底为某个特定功能的公式取了什么名字。（这里应该是面向2007版，2003版是ctrl+F3)

最后再介绍一个功能键，F2，F2的功能就是进入编辑状态，虽然在单元格编辑公式时显示不出有什么用，当在基本菜单功能的对话框编辑公式时就比较有用。省去了腾出手去控制鼠标进入对应公式编辑框中，这看上去没有什么，但相信我，以后你会觉得很好用的。

如果单纯是需要停顿，有一个比较简单的方法，方法简单，但比较解决问题，在公式前导符“=”之前插入一个空格即可。如此，单元格的格式属性就变了，原先是功能性的公式，现在就变成了普通文本，这样就为中场休息提供了保障。(也可以插入'来把公式变成文本）

第二，不是时间上需要停顿，而是思维上需要停顿，这个也是很正常的。在写一个比较负责的条件公式时，分支多，层次深，很难一下子把公式全都书写到位的，这个时候可以使用“空文本”的方式先来填充，或者使用一段有意义的字符串先来占一个位，这个也是非常重要的。这样就在思维上将一个复杂的问题切割成几块来解决，降低了思维上的难度。

二、excel2003和2007版本的功能限制对比：

2003版本功能 最大限制

数字精度 15 位
单元格中可键入的最大数值 9.99999999999999E+307
最大正数 1.79769313486231E+308
最小负数 -2.2251E-308
最小正数 2.229E-308
最大负数 -2.2250738585073E-308
公式内容的长度 1,024 个字符
迭代次数 32,767
工作表数组个数 受可用内存限制。另外，数组不能引用整个列。例如，数组不能引用整个 C:C 列或 C1:C65536 区域。然而，数组可以引用区域 C1:D65535，因为此区域比最大工作表的大小少一行，且未包括整个 C 或 D 列。
选定区域个数 2,048
函数的参数个数 30
函数的嵌套层数 7
可用工作表函数的个数 329
计算允许的最早日期 1900 年 1 月 1 日（如果使用 1904 年日期系统，则为 1904 年 1 月 1 日）
计算允许的最晚日期 9999 年 12 月 31 日
可以输入的最大时间 9999:59:59

2007版本功能 最大限制
数字精度 15 位
可在单元格中键入的最大数值 9.99999999999999E+307
最大正数 1.79769313486231E+308
最小负数 -2.2251E-308
最小正数 2.229E-308
最大负数 -1E-307
公式内容的长度 8,192 个字符
公式的内部长度 16,384 个字节
迭代次数 32,767
工作表数组个数 受可用内存限制
选定区域个数 2,048
函数的参数个数 255
函数的嵌套层数 64
用户定义的函数类别个数 255
可用工作表函数的个数 341
操作数堆栈的大小 1,024
交叉工作表相关性 64,000 个可以引用其他工作表的工作表
交叉工作表数组公式相关性 受可用内存限制
区域相关性 受可用内存限制
每个工作表的区域相关性 受可用内存限制
对单个单元格的依赖性 40 亿个可以依赖单个单元格的公式
已关闭的工作簿中的链接单元格内容长度 32,767
计算允许的最早日期 1900 年 1 月 1 日（如果使用 1904 年日期系统，则为 1904 年 1 月 1 日）
计算允许的最晚日期 9999 年 12 月 31 日
可以输入的最长时间 9999:59:59

=======================================================
第三部分：作业中的扩展：

and、or与*、+的原理比较：

AND(logical1,logical2, ...)——详见and函数帮助：当所有的logical们都为True时返回True——这是1个单值；
OR(logical1,logical2, ...)——详见and函数帮助：当所有的logical们只要有一个为True时返回True——这是1个单值；
(A1:A2>0)*(A1:A2<8)——返回的是{True;False}*{True;True}类型的数组相乘的结果{1;0}——这是一个数组；
(A1:A2>0)+(A1:A2<8)——返回的是{True;False}+{True;True}类型的数组相加的结果{2;1}——这是一个数组；
接下来：
由and、or得到的是：
=sum(if(and(A1:A2>0,A1:A2<8),B1:B2))=sum(if(and({True;True},{True;False}),B1:B2))=sum(if(False,B1:B2))=0
and({True;True},{True;False})——有出现False返回False；or公式类似。
由*、+得到的是：
=sum(if({1;0},B1:B2))=sum({5;False})=5——可以用F9键在编辑栏看出。
而=sum(if({2;1},B1:B2))=sum({5;11})=16
注：在逻辑判断if的条件中，0=False；非0的数值都=True
从以上的对比可以看出：运算符*和+在逻辑判断中与and和or是不同的，前者得到的是数组,后者只能得到单值.在数组运算的情况下由于不能匹配故不能互换。
Apolloh在14楼补充解释: 楼主gouweicao78所说的*和+的用法，实际上是针对相同尺寸的逻辑数组的算术运算，逻辑数组A*(+)逻辑数组B=逻辑数组C。而and(逻辑数组,逻辑数组)和or(逻辑数组,逻辑数组)中and和or的参数可以是数组，但却不是数组运算，没有数组的对应关系，其结果返回单值。所以两者是不能等价的。

从上面的知识点拓展一下,平时看到很多形式如if(条件1*条件2*……，统计区域) 的简化if法(满足所有条件，返回统计区域，否则返回False（简化没写）。——即数值和False组成的数组)和连乘法(利用数组相乘返回数组——即数值和0的数组)原理并不相同.这一点狗尾草版主也强调过.
在sum中，因为简化没写的0（简化if法返回）对于加总求和并没有影响，所以大多可以通用。
在Average、Max、Min等函数中则不能通用：
False在计算中大多情况=0，但并非都是这样。
比如Average——会将连乘法中0算入平均的个数，而不会将简化if法中的False算入个数。
而由满足条件的值都是负数的情况下，max连乘法将得到0，max简化if法得到的才是正确答案。
连乘法对应的不是简化if法，而是if(条件1*条件2*……，统计区域，0）。

[ 本帖最后由 lrlxxqxa 于 2010-6-15 19:15 编辑 ]

lrlxxqxa

这部分内容很多都是我从来没接触过的函数，学习过程自然有很多收获，简单总结一二，如下所示：
大家可以注意一下标注颜色的部分，那是重点。
三个部分：知识概要、扩展知识点（SUBTOTAL、MMULT）、成果应用
===========================================================================================
第一部分：知识概要：

一、公式中数学运算
加（+）、减（—）、乘（*）、除（/）、百分比（%）、乘幂（^）

二、运算符的优先级
由高至低排列：负号、百分比、乘幂、乘除、加减。（）强制运算。

三、常用函数分类介绍：

1、求和函数：SUM、SUMIF（条件求和）、SUMPRODUCT（多条件求和）

2、汇总函数：SUBTOTAL——根据第一参数决定运算要求

   返回列表或数据库中的分类汇总。通常，使用“数据”选项卡上“大纲”组中的“分类汇总”命令更便于创建带有分 类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。

3、舍入取整函数：

INT:沿数值减小的方向舍去小数求整.
TRUNC:截断可选择保留位数
ROUNDUP:绝对值增大的方向向上舍入,可设位数
ROUNDDOWN:绝对值减小的方向向上舍入,可设位数
CEILING:绝对值增大/倍数
FLOOR:绝对值减小/倍数
EVEN:绝对值增大/偶数
ODD:绝对值增大/奇数
ROUND:绝对值四舍五入

4、随机函数：RAND——在0~1之间取随机数、RANDBETWEEN——在A~B之间取随机数
RAND()*(B-A)+A可从[A,B)中取值，注意和RANDBETWEEN(A,B)不同，区别在于前者是半开半闭区间，后者是闭区间取值。

5、公倍与公约函数：GCD、LCM
2003版本需要加载分析工具库

6、除法相关的函数：MOD（求余数）、QUOTIENT（取商的整数部分）

7、符号相关的函数：ABS（取绝对值）、SIGN（大于0返1，小于0返-1，等于0返0）

8、概率计算函数：FACT（求阶层）、PERMUT（排列）、COMBIN（组合）

9、对数计算函数：LOG、LOG10、LN（以常数E为底）

10、三角函数：DEGREES、RADIANS、SIN、COS、TAN、ASIN、ACOS、ATAN
     RADIANS：角度转弧度，=角度*PI/180
     DEGREES：弧度转角度，=弧度*180/PI
11、矩阵函数：MMUT、MINVERSE
=======================================================================
第二部分：扩展知识点一：SUBTOTAL函数

1、SUBTOTAL函数与SUM的区别：

（1）使用“数据”→“分类汇总”功能后，对于每列既有原始输入的数据，也有自动生成的SUBTOTAL数据，这些自动生成的SUBTOTAL将被忽略，而SUM函数则把SUBTOTAL的结果也重复计算进去了。
（2）使用“数据”→“筛选”功能后，SUBTOTAL 函数忽略任何不包括在筛选结果中的行，不论使用什么 function_num 值；而SUM函数则不受筛选影响对区域内全部统计。

2、SUBTOTAL的第一参数9和109的区别：
当 function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“隐藏行”命令所隐藏的行中的值；
当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“隐藏行”命令所隐藏的行中的值。

爬贴中看到草版对此函数的解读，再加以下2点
3、SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。
统计垂直区域的时候，隐藏行会对109有影响；
统计水平区域的时候，隐藏列不对109有影响。

4、SUBTOTAL不支持跨工作表的三维引用，但支持函数返回的三维引用；
前者举例：诸如Sheet1:Sheet3!A1:A10的形式的三维引用，SUBTOTAL 将返回错误值 #VALUE!
后者举例：重点理解后一个数组公式

1. =INDEX(A:A,MIN(IF(SUBTOTAL(3,OFFSET(A$1,ROW($2:$10)-1,)),ROW($2:$10))))

复制代码

1. =INDEX(A:A,MIN(IF(SUBTOTAL(3,OFFSET(A$1,ROW($2:$10)-1,))*(COUNTIF(D$12:D12,$A$2:$A$10)=0),ROW($2:$10),4^8)))&""

复制代码

再引用发哥gvntw的一句话：

SUBTOTAL 函数的三维引用生成内存数组非常实用，如累加单元格数据，生成内存数组，不足的缺点就是第 2  个参数必须是 ref 。

至于后来草版说的：

SUBTOTAL函数还可以支持函数产生的“四维引用”

这句话不理解，有待进一步学习。

扩展知识点二：MMULT函数

MMULT(ARRAY1,ARRAY2)
MMULT函数返回2数组ARRAY1,ARRAY2的矩阵积。

第一、第二参数都只能是数值；
第一参数的列数＝第二参数的行数；
结果的行数＝第一参数的行数；
结果的列数＝第二参数的列数。

介绍一个方版整理的好贴，可以用精彩来形容
http://club.excelhome.net/viewth ... p;page=1#pid2112368

countif、sumif、subtotal等函数都是运用于条件求和、条件计数等统计需求的常用函数，但这些函数都要求其第一参数为单元格区域的直接引用。当问题比较复杂、统计条件比较多的情况下，有些时候就无法直接使用这些函数进行条件统计，而使用mmult函数可以很好地替代这些函数，在复杂条件下的数组统计上进行运用。

应该说，mmult函数是数组条件统计的一大利器，用好mmult函数可以简化许多复杂问题的公式解决方案。

MMULT函数在Excel2003版中，其参数所构成的矩阵元素个数有一个最大数目的限制，不能超过5461。
公式=MMULT(ROW(1:5462),1)，因为超出了上述限制范围，此公式会返回错误值#VALUE!。
而在Excel2007及2010版本中，MMULT的矩阵运算个数不再有具体的数目限制，而是与系统可用内存大小有关。
=========================================================================================
第三部分：结合实例应用扩展

注意理解附件中=ROUND(O4*2,-1)/2和=FLOOR(O4+2,5)和=CEILING(O4-2,5)和=MROUND(O4,5)这几种形式的使用。
根据实际需求来对公式进行变形，能够很好的解决问题。
至于最后一个lookup的使用，权当娱乐吧

1. =LEFT(O4,LEN(O4)-1)*10+LOOKUP(MOD(O4,10),ROW($1:$10)-1,{0,0,0,5,5,5,5,5,10,10})

复制代码

[ 本帖最后由 lrlxxqxa 于 2010-6-18 16:16 编辑 ]