Posts Tagged Excel
多条件求和：countifs，sumproduct，数据库函数
最先是因为Cisar问我countifs函数的使用，后来忽然感触到，很多时候，可以不用透视表而用countifs函数来进行多条件汇总分析。
最近在做大批量数据（数据记录超过6000条）分析的时候，而且要进行多个不同条件的汇总分析。在使用countifs函数分析的时候，发现数据表的计算了太大了。没有办法，只得将工作表设置为手动计算。但还是很烦人，每次计算花费很长时间。遇到数据量巨大的时候，计算速度是个问题。这个时候，只好将引用区域进行控制，减少不必要的引用。
在计算速度上，countifs的计算速度比sumproduct要好。
以下是来源：
| SUMIFS blows SUMPRODUCT away when it comes to calculating Speed. The Excel 2007 formulas SUMIFS and COUNTIFS and AVERAGEIFS are much faster than SUMPRODUCT and SUM array formulas. http://www.teachexcel.com/excel-tutorials/n-1021,Excel-Formula-Efficiency-4–SUMIFS–amp–COUNTIFS-Are-Fast.html |
关于数据库函数的criteria区域，office 的帮助文档有说明：
| DCOUNT(database,field,criteria) ，Criteria 是包含所指定条件的单元格区域。
而vlookup函数则支持引用的criteria是数组。 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)，Table_array 为两列或多列数据。使用对区域或区域名称的引用。table_array 第一列中的值是由 lookup_value 搜索的值。 谢谢excelhome论坛jianbing的帮助。以前还没好好读office文档中的这个说明。 |
总的看来，Excel 2007下的countifs，sumifs是一个提高计算速度的好公式。
将文本转换为数据
好久没有用Excel VBA写程序了。最近对一些分析模型做更新，于是重新写。提取的数据比较dirty，得进行一些处理。比如有的数值，提取出来却成了文本格式。
在立即窗口中，在语句前面添加 “?”，可以立即知道该语句的结果。
引用excel中的函数方法：Application.WorksheetFunction.函数。但是，若该函数在VBA中有，则不要采用该方式引用。
下面是一个将文本转换为数字的程序。
Dim i As Integer
Dim j As Integer
Sub ForceString2Value()
For i = 2 To 6598
For j = 13 To 16
With Worksheets("Invest")
‘ determine whether there is character "”%” in cells
If InStr(1, Cells(i, j), "%") Then
Cells(i, j) = CDbl(Left(Cells(i, j), Len(Cells(i, j)) – 1)) / 100
Else
‘ in some cells, those strings can’t be changed to value.
On Error Resume Next
’ if the cell is string, then use CDbl to change string to value
Cells(i, j) = CDbl(Cells(i, j))
End If
End With
Next j
Next i
End Sub
用Median和if同时求得多个分类的中位数
有时候需要对一列的多个类别的数据分别求中位数，而透视表中又没有中位数。可以用Median和if函数想结合来求得。方法如下：
当存在多个条件的时候，可以将if里面的条件改为多次嵌套，这样得到多条件求和。
{=MEDIAN(IF($A$2:$A$14=F2,if($B$2:$B$14=G2,C2:C14))}
另外，在对数据进行分析的时候，有的时候需要增加辅助列。这个时候，Excel的数据经常会出一个问题。添加辅助列之后，原来的同一行记录的数据会被打乱。
Solution：先将排序去掉，再添加辅助列，保存。再重新排序。这时，原来的行记录就不会被打乱了。
books I read in 2009
利用豆瓣的一个API查阅了一下自己在2009年读过的书。豆瓣上的记录显示2009年一共读了68本。大致浏览了一下，2009年读过的书的类别有着很强的阶段性。
前期花了很多的时间在学习和PowerPoint演示和Excel相关的书籍，后来有一段时间集中学习知识管理方面的书籍，年末开始转向Finance and Investment方面的书籍。预计2010年的阅读书籍会以Finance and Investment为主。
2009期间读的一些书的点评：
Influence: Science and Practice, 这是一本绝对经典的书。从心理学的角度对人的行为做了分析和阐述，很有道理。
懂得爱： 人如何去和身边的交往，去感同身受他们的感觉，去替别人着想。
追逐日光：讲的是毕马威会计师事务所（KPMG）的董事长和首席执行官，53岁的尤金·奥凯利在得知自己患了癌症，在最后的100多天的日子里的感悟。看完之后，自己的最大感触是，生活的每一天，一定要好好爱自己，爱身边的人，爱生活。
高德拉特的系列书：目标、关键链、绝不是靠运气、仍然不足够。高德拉特作为TOC理论的倡导者，所撰写的一系列书籍，阐述如何应用TOC理论来解决实际的问题。
系统化思考方面的书籍：《系统思考——适于管理者的创造性整体论》、《系统思考（修订版）》，包括之前看的《Asking the Right Questions》，杰拉尔德温伯格的《系统化思维导论》等书，让我对如何系统化地思考问题有了更深的了解。另外，《粵海變革》这本书也对我如何从多个相互制约的因素之间去思考问题有所帮助。
管理方面的书籍：《格鲁夫给经理人的第一课》让我知道了一些在实务管理上，管理者该如何去做。《實戰麥肯錫》讲述了作为咨询项目团队，该怎么样合作去完成项目。《中国人为什么组织不起来》，这本书在我看来，提出了问题，但还是没有很好地解答这个方面的问题。《管理大未来》，看了，没有太多印象。《关键时刻》，看完这本书，我最大的感触是，用心去做事情。想起看到金错刀写的关于腾讯马化腾的敬业态度，很是感触。
知识管理：因为要做知识管理的相关项目，所以把能够找到的知识管理的书籍很多都读了。读完之后，发现没有比较让我很满意的书。这可能是理论和实践之间还是存在比较大的差距。比较推荐的是《知识管理–推动企业成长的加油站》和 Harvard Business Review one Knowledge Management.
Finance方面：《金融的逻辑》，让我对金融的作用有了一个入门的了解，也开始对金融变得越来越有兴趣了。之后阅读了《巴菲特传：一个美国资本家的成长》，成功是需要一种艰苦卓绝的努力才能够实现的。《年报掘金》，很好的一本关于如何分析上市公司年报的书，不过其中涉及到比较多专业的知识，外行的阅读可能会有点吃力。《明明白白开年报》，这本书相对而言更通俗易懂。
Excel和PowerPoint方面的书就不想点评了，太多了。
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
窗体控件和控件工具箱控件
在Excel中有两种不同的控件。窗体控件和控件工具箱控件。
一种是窗体工具条控件（Forms toolbar controls），菜单“视图”→“工具栏”→“窗体”工具条控件窗口。
另外一种是控件工具箱控件（ActiveX控件），菜单“视图”→“工具栏”→“控件工具箱”（也可以选择“Visual Basic”，然后再在工具栏上选择“控件工具箱”），将出现“控件工具箱”窗口。
窗口工具条控件是Excel5和Excel95留下来的东西（在Excel 97后的版本中，添加一个Dialog Sheet后，将可以看到这个窗体工具条窗口），从Excel 97开始，Dialog Sheet被UserForm代替，并且开始使用ActiveX控件。
控件工具箱控件是用户窗体上的控件子集，这些控件只能用于Excel 97或更高版本的Excel中。
窗体控件比ActiveX控件简单，基本上只有一个Click事件。而ActiveX控件可以响应丰富的事件，ActiveX控件的事件只能放在控件所在的类模块（工作表模块）或窗体模块。过程名称由控件名和事件名称组成。如果你在控件不存在的时候就创建这个控件的事件过程，然后再在代码中引用这个控件，将会出现编译错误，所以必须使用代码创建事件过程。
关于Excel VBA的程序说明
Excel vba的程序又根据有无执行对象分为过程程序和事件程序。
过程程序简单的说就是通过代码完成一个任务。它和事件程序的主要区别就是它没有明确的服务对象，但可以被其他程序所调用。过程程序又根据是否有返回值分为以下两种。
1. 子过程程序：运行后不能返回值。
2. 函数过程程序：运行后能够返回值。
自定义函数一般存放在添加的模块中，但也可以存放在指定对象的（如工作表对象）的代码窗口中。不同的是，在模块中的函数可以在任何一个程序中调用，而存放在对象代码窗口中的函数只能被本代码窗口内的程序所调用。如果要变下在工作表公式中使用的函数，就必须在添加的模块中编写。
事件程序是指当某一特定事件发生时才执行的程序。例如，当选取单元格时触发某个程序的运行。选取单元格的动作就是一个事件。使用事件程序的好处是极大增强了操作者与程序的互动性。
事件程序的添加和过程程序有所不同。事件程序有指定的对象，所以添加前要先选取该对象并打开该对象的代码窗口，然后在代码窗口的对象列表中选取该对象或该对象的子对象。并在过程列表中选取需要的事件。在代码窗口内会自动添加该程序的开始和结束语句。
学习Excel的一些感想
以Excel表格的功能为主线，则学习完了之后，感觉难以有效应用。即，读了剑谱，但不知道怎么用出来。
而以应用为主线，则知道目前的东西该如何实现，但在遇到新的问题的时候，不知道该如何来创新地实现。这就如没有经历正规的训练而单纯地混江湖。
比较好的方式是，对一般应用者而言，以应用为主线进行讲授，中间穿插一些东西的功能。
对希望系统掌握的人，以功能为主，穿插一些实际的应用。
在学习excel的时候，有的功能可以有多种实现的方法。对有的人来说，他们可能宁愿去用那种相对简单但比较繁琐一点的方法。而要更进一步，则需要花费精力去掌握一些更复杂但有效的方法。
但任何一种方法，如果不是经常用到，那么，过些时日，就会忘记。因此，到底学到何种程度，在某种程度也取决于工作中要用到哪些功能和使用的频率。
Excel help: Good but not Enough
The Excel help file is good, but not enough. Many operating functions and skills need to be learnt by other tutorial.
Excelhome.net is a wonderful website. Often, I will turn it for help when I have some difficulties.
Of course, I need to learn more about Excel VBA. In order to master it, I have to take time reading tutorial books about VBA and make practice.
Error in renaming a sheet in Excel
I renamed a sheet and am getting the error, "While renaming a sheet or chart, you entered an invalid name, Try one of the following" blah blah blah. The sheet name is valid, does not have any characters and isn’t too long.
What’s the problem? Well, I searched internet and found this might solve the problem.
The following information is from: http://www.mrexcel.com/forum/showthread.php?t=157564
This topic may be dead, but i think i know what causes this phenomenon. And this might be helpful for someone else who might be searching the forum.
This happens to me every time I try to rename a sheet in an excel file that is in a "temp" directory. There are usually illegal characters in the file name, IE. "Sales Analysis -[1]". If I choose rename and name my sheet "Book1" Excel names the sheet ".xls]Book1", then you get the error.
At that point the only thing you can do is to "X" out of the workbook and select save changes. Then select file —>open and click My recent documents to open the workbook. Then click file save as, and save the file to a normal directory with a valid file name. Now you can change the invalid sheet name.
复制内容的技巧
在excel中，要将一个单元格中的很多文字放到该单元格下面的多个单元格中，每个单元格中放一个文字。采用的方法是：
1.将单元格的列宽度调整为一个汉字宽。
2.选择单元格，Editor–>Fill–>Justify.
3. ok.
Recent Comments