东莞dsp投放:Excel函数公式使用心得

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

　　一、怎样学习函数公式

　　这是很多新手最想知道的事，函数那么多，要从哪儿学起呢。我个人谈点小体会：

　　1、“学以致用”，用才是目的——就是你要和将要用到的东西先学。比如你根本用不上财务、工程函数，没必要一下子就往看那些专业性很强的东西（嘿嘿，那些我基本不会），这样就轻易进门了。基本上函数用得最多的逻辑判定和查找和引用这2类函数了。先不要急于学会“数组”，自己常用函数的普通用法有个大致的用法了解之后再往看它的数组用法。

　　2、善于搜索，搜一下，能找到更多的解答；善于求助发帖求助要描述清楚附上必要的图文并茂的附件，轻易得到解答，而且锻炼了自己的表述能力。

　　3、除了“求助”式学习，还要“助人”式的学习，相信这一点是众多论坛高手们都经历过的。只要有时间，少看一会儿电视少聊一会儿QQ少跟同事吹一会儿牛，到论坛上看看有没有别人不懂而你懂的，别怕出糗，是驴是马牵出来遛遛，相信你热心帮人不会被嘲笑的，况窃冬抛砖引玉，说不定你抛的对别人甚至对高手来说也是块宝玉呢。而，助人助己，有了越来越多的“求助”者给你免费提供了练习的机会，练得多了再综合各种思路的比较，自己就有了一些想法，你的水平肯定与日俱增。

　　4、一口气吃不成胖子，多记一些学习的体会，日积月累，你就是高手了。

　　二、如何解读公式 我也谈点小体会吧：

　　1、多看函数帮助。各个函数帮助里面有函数的基本用法和一些“要点”，以及对数据排序、引用类型等等的要求。当然，函数帮助并不囊括所有函数的细微之处，不然，也就不会有那么多求“解释”的帖了。

　　2、庖丁解牛——函数的参数之间用逗号隔开。（别笑话，这是最最基本的基本功，单个函数没啥，组合多个函数的公式就是靠它了），这些逗号就是“牛”的关节，先把长公式大卸八块之后逐个看明白了再拼凑起来读就轻易多了。

　　3、独孤九剑——开个玩笑啦，这里是取谐音“F9键”。F9键用来“抹黑”公式对解读尤其是数组公式有非常强的作用，不过假如公式所含数据区域太大（比如上百行）你可以改变一下区域。具体方法比如下面这个简单数组公式

　　=sum(if(A1:A3>0,B1:B3))，用鼠标在编辑栏（或F2）把把A1:A3>0部分“抹黑”，按下F9键，就看到{True;True;False}（假设A3不满足），表示if的条件是这么3行1列的逻辑值数组。——别忘了，看完之后按ESC取消哦，否则公式就变了。

　　4、公式审核——（选择公式单元格-〉菜单栏-〉[工具]-〉[公式审核]-〉[公式求值]

　　然后按照导向提示，一步步地观察公式求值的过程）就是工具〉公式审核〉公式求值那个有fx的放大镜，与F9功能基本相同，能一步步看公式运行的结果（但两者效果均有一定限制，具体情况尚未明了，fx有时会造成Excel的重启）。配合着用吧。

　　5、留意定义名称：很多人都回用定义名称来使公式简洁、直观，更轻易让“庖丁”来解，按下Ctrl+F3可以查看定义名称（或者菜单〉插进〉名称〉定义），假如名称是对单元格区域的引用，这当你点击名称框下方的“引用位置”时，会在相应区域出现虚线选择框。还可以选个空缺的地方，按下F3键，选择“粘贴列表”把定义的名称复制到空缺区域。

　　6、关于函数的一些深层次的东西，

　　公式解读之基础知识篇：

　　1熟悉运算符

　　*、+号在数组运算中，我们通常理解为“且”、“或”，但是他们与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种形式的讨论请参考：

　　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单元格。

　　关于数组公式进门到深进理解，

　　数组公式解读之基础知识篇：

　　1、概念：数组、多重计算、数组公式

　　数组：就是具有一定行列尺寸的单元格元素或数值、文本、逻辑判定等组成的单、多元素的东西，比如：

　　单元格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*……*统计区域)这么一个常用的多条件求和公式，只要它不用按三键，我们就称为“普通公式”

　　计算单个结果的数组公式：用数组公式执行多个计算而天生单个结果。——在单个单元格输进公式并按三键形成的数组公式。

　　计算多个结果的数组公式：使数组公式能计算出多个结果，必须将数组输进到与数组参数具有相同列数和行数的单元格区域中。——在多个单元格区域输进公式并按三键组成的一个整体的数组公式，我们称之为“多单元格数组公式”。

　　多条件筛选单列不重复值（解释见附件） 题目“公式里面怎么还能有空格？”

　　一个挺有意思的取行列交叉值的方法，固然以前见Gvntw版主写过取行列交叉值，不过用的人还是少。

　　空格法：举个简单的例子：=sum(1:3 C:E)——这么一个公式实质是对C1:E3求和，即对空格前与空格后的两个区域取交叉部分。 不过，这个题目不是这样的，而是由于字库或者某种误操纵产生的错误显示，假如没有继续改动该名称的话，它只是显示成这个怪样，而实质还是原来没有错的公式。但假如你按下Ctrl+F3在定义名称框点击该名称什么也不改按“添加”则会出错！！！这个也是：题目2、莫名其妙之处，名称后面“?”——我看到的是一个韩文？F3粘贴名称后变为“?”，乖乖。呵呵。还没弄明白怎么出来的。是不是由于Excel版本差异的题目，5楼看到的不是韩文。

　　至于求多条件不重复值，既然定义名称，我还是习惯于“列表”功能定义的，（原因：1、动态引用可随数据输进而增加引用范围；2、方便快速定义（插进〉名称〉指定〉首行），3、可以用于Indirect函数的再次引用，参考：列表动态引用、关于Indirect对定义名称再引用），个人习惯不同，呵呵，给个参考：为不占空间，具体的公式解释都在附件里了

　　

　　多条件单列求不重复值.rar___.rar

　　取得工作表名称

　　这是个宏表函数取得工作表名称的“定义名称”

　　方法：插进〉名称〉定义shtname，引用位置输进公式〉确定

　　在某单元格输进=shtname，则返回该工作表名称。

　　1、首先，get.document(1)是宏表4.0函数（可以搜论坛下载一个帮助说明）:

　　假如工作簿中不只一张表，用文字形式以“［book1］sheet1”的格式返回工作表的文件名。否则，只返回工作簿的文件名。工作簿文件名不包括驱动器，目录或窗口编号。通常最好使用 GET. DOCUMENT(76)

　　和 GET. DOCUMENT(88) 来返回活动工作表和活动工作簿的文件名。

　　get.document(88)以“book1”的形式返回活动工作簿的文件名。

　　2、=SUBSTITUTE(GET.DOCUMENT(1),"["&GET.DOCUMENT(88)&" ]",)——就是将［book1.xls］sheet1中的[]号及book1替换为空

　　得到sheet1

　　3、now()是一个易失性函数，随着Excel的一些动作比如编辑单元格等变化，产生当前时间（数值的一种），T()函数对文本返回文本自身，对数值返回空。所以&T(now())相当于&""，只不过这个""会随时变化。

　　这样连起来就使得我们用shtname得到的工作表名称是一个可以随着Excel动作（如改变工作表名也是动作）而“实时”变化的工作表名

　　常用的几个取当前工作表名的公式：（基本都是用宏表函数，套上文本处理）

　　=SUBSTITUTE(GET.DOCUMENT(76),"["&GET.DOCUMENT(88)& "]",)&T(NOW())

　　=REPLACE(GET.DOCUMENT(76),1,FIND("]",GET.DOCUMENT( 76)),)&T(NOW())

　　=MID(GET.DOCUMENT(76),FIND("]",GET.DOCUMENT(76))+1 ,255)&T(NOW())

　　不用宏表函数取得工作表名称

　　在单元格中输进：

　　=MID(CELL("filename",A1),FIND("]",CELL("filename", A1))+1,255)——可得到工作表名，其原理同上，主要是利用CELL函数用"filename"作第1参数取得带路径的工作表名。

　　在定义名称中使用（强烈推荐）：

　　shtname=MID(CELL("filename",!A1),FIND("]",CELL("fi lename",!A1))+1,255)——这是个工作簿级的定义名称，可以得到工作簿中公式所在工作表的名称。

　　留意：CELL函数要求Excel文件已保存。

　　再来一个公式解释

　　=INDEX(A:A,SMALL(IF(A$1:A$19<>"",ROW($1

　　

　　19),),ROW()))&""

　　实在这个公式出往和&""两个部分的话大家并不陌生，是依次列出满足某条件的记录的数组公式。

　　解释：

　　1、关于if部分：假如A1:A19不为空，则返回A1:A19的行号（条件），否则返回（留意Excel2003及之前版本的最大行数是，Excel2007将采用行，具体见有关先容）；

　　if外部套用small()+row()进行排序，将if得到的满足条件的行号和不满足条件的进行排序

　　2、关于&""：首先说一个大家很熟悉的事情，那就是引用空单元格的情况，比如=A1、=index(1:1,,1)、=index(A:A,1)、=offset(B1,,-1)、=indirect("A1")等等各种各样的方法引用A1单元格，当A1为空的时候，这些引用都返回0，假如这个0时我们所不想看到的话，经常会采用工具〉视图〉零值勾选掉的方法。但比如我们想当A1=0的时候，引用A1就显示0，当A1为空时，引用A1显示空，那么我们就可以在以上引用公式的尾巴加上&""——将A1单元格的内容与""空合并成文本，则此时返回的将不是0而是空了。留意，返回的是“文本”，假如A1是日期，则返回的是日期序列号数字的“文本”，等等（可以再套用Text(引用,"yyyy-m-d")等来显示日期）。假如A1是数值型数字，返回的则是文本型数字，切记！

　　3、基于以上两点熟悉，我们不难理解，该公式的含义就是：

　　对A列取行列交叉值（index），其行号是small函数排好序（按记录先后顺序）的行号和组成的。而由于一般情况下，Excel最底下那一行是没有内容的（空），所以这个公式能达到=if(row(1:1)>满足条件记录个数,"",index(……))一样的显示效果。值得留意的是，除了这个公式缩短了长度和计算满足条件记录个数步骤带来的好处以外，要切记返回的是文本哦！

　　这个公式的用法还不少，喜欢的朋友们可以自己多试试。

　　

详情查看：excel表格的基本操作
http://hi.baidu.com/xueexcel

2011职称计算机考试Excel中快捷键和功能键

如何给excel的单元格添加斜线并在对角线两边添加内容

课题3:Excel(电子表格)基本操作

Office2007之Word表格及Excel中斜线表头的绘制方法

轻松玩转Excel(4)——单元格操作

[转载]Excel表格基本操作

Excel中将平均值与标准差合并于一个单元格内的操作技巧

DxDbGrid与DbGridEh表格使用及导出Excel

Excel合并单元格技巧知多少(转)