文 / 靳伟(知乎)
链接:https://www.zhihu.com/question/24277854
老衲多年前遭遇异缘,获得Excel五轮真经一部, 其中Excel心法共有五层, 每层除了心法之外还有招式. 老衲对Excel虽然所知甚少,但觉得只要苦练心法, 层层递进, 直达第五层,应该就算是精通Excel了。
(不过根据老衲多年以来面试经验,一般自称精通Excel的, 大多都是初入江湖的小朋友, 功力一般在一二层之间. 此外, Excel是数据处理工具,用Excel打游戏或绘画者, 虽然可能是豪侠巨擘, 但我觉得不应该作为精通Excel这件事的参照系.)
——–一切有为法分割线——–
Excel五轮心法:
总纲
章一.基础应用/Short Cut
章二.函数/公式
章三.图表/图形
章四.数据透视表
章五. VBA /VSTO
跋:Excel局限性和小技巧
——–三法印分割线——–
总纲:
先贤有言,三生万物. 可见万物皆数也. 雷蒙三圣雷奥兹云, 数理昭昭, 惟精惟一. Excel心法, 以纷繁之数始, 需去其杂芜糟粕,截之以齐; 续而密经实纬, 攒之以方; 再而形诸图表, 文饰藻绘, 方可示人. 至于心法高处, 名为VBA, 千变万化,言语不可尽其妙也.
下图就是雷奥兹(RayOzzie), Excel大法开山祖师, 和比尔门,鲍尔默并称雷蒙三圣.
翻译一下:
Excel操作上, 第一步是对数据进行清洗, 去除不合规格的脏数据, 将数据调整成整齐合理的格式. 然后添适宜的数据辅助列,补充数据维度. 最后是将处理好的数据以美观的图/表形式向他人展示.最高级的功能叫做VBA
VBA的使用非常灵活强大, 不是几句话能说清楚的。
Excel招式繁多, 先给各位施主一个直观印象. 一般止于二级菜单. 重点功能老衲用星号标记:
章一. 基础操作 /Shortcuts
基础操作中的入门法门是数据整理.这个是最基本的柱础, 腰马合一, 力从地起. 但很不幸的,大多号称精通Excel的少侠们尚未具备这个意识。
原始数据一般都长成这样。
这是个糟糕的数据样本,但是还不是最糟的. 从不同的人手里收集原始数据的时候, 这种情况特别常见.
好的数据格式是:
世间任何功夫都是由浅入深,循序渐进,数据整理就是其中最基本最重要的入门招式。不过入门招式,往往也意味着很辛苦,别无捷径,唯手熟心细尔。
数据整理之起式:清洗
吾宗神秀大师有云:身是菩提树,心如明镜台,时时勤拂拭,莫使有尘埃.
心需拂拭, 同理,数需清洗. 使其平熨齐整, 利于后续使用。
清洗的对象,简称脏数据. 一般有如下几种情况:
1. 同名异物:例如公司里面有两个李明, 如果不加区别地导入数据并进行合并统计, 可能就会出问题。
2. 同物异名:例如性别, 有的人写成男女, 有的人写成M/F, 有的人干脆写成0/1。
3. 单位错乱:例如金额, 人民币和美元一旦混同, 那绝对是一场灾难
4. 规格不合:例如身份证号为9527。
5. 格式混乱:最典型的就是日期! 例如10/6/11, 根本说不清楚是11年10月6日, 还是11年6月10日, 抑或是10年6月11日,因为美式日期, 英式日期, 中式日期各自都不相同! 老衲生平目睹过的日期惨案足足有12306桩那么多! 另外一种是分位符.美利坚的分位符是”,” 而欧罗巴诸国的分位符是”.”如果是一位叫Chateaubriand的美国同事发过来一个数字”123.456″, 根本说不清楚这是一百挂零, 还是十万有余.老衲生平目睹过的分位符惨案足足有1024桩那么多!
假使少侠天资过人,心如明镜, 眼疾手快, 刷刷刷刷检出来”123.456″个脏数据, 怎么处理呢?
数据整理之承式:规制
做数据之前,先要和其他人协商好, 各个数据都是什么格式, 不同数据表之间的格式是否要统一, 之间是否有依赖关系.如果数据不满足依赖关系如何处理。
例如先约定好,性别一律写成”男/女”. 如果写成M/F的, 那么M就当成男性, F就当成女性来处理(使用替换, 或者使用中间映射表).写成Nan/Ny的, 直接当脏数据抛弃掉。
数据整理之转式:分组
在数据预处理中,分组是一个很重要的手段, 例如各位少侠要面对的是本公司的工资表, 想看看整体是否失衡, 可以将资历分为中低高三组(日企),对应人员的工资进行汇总; 但具体资历分层的节点的把握, 则需要小心, 必要时还需要反复尝试.例如可分成
a.工作1年以下,
b.工作1年-3年
c.工作3年-5年
d.工作5年以上
跑出来一看,wow, 公司是大学生创业基金支援的, 全部员工都是工作1年以下……
这个时候就得按更细粒度的月来进行划分了。
数据整理之合式:聚类
聚类则更灵活,例如最早登记报册的只有员工的姓名工号, 乱糟糟一大把, 业余活动组织不起来怎么办?
这个时候找IT要一下各人上班的时候的浏览网页,从网页记录推算一下各人爱好, 然后按照爱好进行聚类, 变成篮球俱乐部, 羽毛球娱乐部,DOTA俱乐部…..这以后的工作就好开展了。
上述四种,强调意识, 不限于方法。
——————————-
老衲一生中,对于Excel最喜欢的功能是表格格式, 不单单是因为美观整齐, 表格格式还集成了筛选, 排序, 甚至冻结窗格的功能,叫表格格式这么平淡的名字, 若依老衲, 应该起名叫”般若波罗蜜多”格。
在表格格式内别有乾坤:
然后说说神鬼莫测七招式:数据工具
先说说分列.这种密集大魔王造出的数据, 大家想必都很常见吧.贴到Excel里面还依然是密密麻麻令人头晕目眩.
只需将之选中,使用”分列”式:
Duang(此处应该有掌声)
接下来是”速填”式,说实话, 这是个相对比较无聊的招式. 如果功力达到第二层, 使用公式函数,取而代之乃是易如反掌.
不过既然至此,老衲继续用上面的例子:
从”代码缩略”下面一直选到底,点击”快速填充” . Duang:
删除重复项实而不华,特别推荐. 非常简单, 不赘述.
数据验证意正身正,实用, 略繁琐, 多人协作时很推荐. 不赘述.
合并计算和”快速填充”有点相似,如果功力递进到第四层, 使用数据透视表来取代之简直是易如反掌. 且便捷不止十倍.先不详述.
模拟分析是堪称第一层的屠龙绝技,看似厉害, 其实用处很小. 而且这个模拟分析和公式/函数关联极大,留待第二章详述.
关系则是一种高不成低不就的招式.不能说没用, 但它的功能呢, 其一可以使用辅助列取代, 其二可以使用vlookup取代, 其三可以使用数据库取代.等说到数据透视表的时候再详述不迟.
中级法门是数据导入,早期数据清洗什么的, 早在入库前就已经有人替你完成了, 便当之至:
连个SQLserver看看:
能练到这一级,少侠, 你功力已然不浅了.想必SQL这种中等功夫你也有过粗练.
(没练过的看这里:SQL基础教程语句汇总 )
至于MDX这种小无相功,功力也不在Excel五轮心经之下, 但修习全凭机缘,老衲先不多讲了.
(好奇想练练的看这里:MDX的基本语法及概念 )
注意事项:
Excel初级法门中有一道奇毒,名为”合并单元格”, 想老衲数度往生极乐, 多缘于”合并单元格”之手. 作为原始数据, 尽量不要使用”合并单元格”,这个功能在后续处理数据的时候会带来大量的麻烦. 足以令人呕血三升. “合并单元格”一般是在最后一步,确定数据不再修改的时候才可以使用.
另外和别的人协作处理数据的时候,最好将处理好的原始数据和呈现数据一起提交给他人,方便他人未来进一步修改.
至于哪些Shortcuts比较重要,个人觉得是单元格位置的操作, 能避免在万千数据里频繁地拖动滚动条.
——色想受行识分割线——
章二.函数/公式
此二者非常容易混淆,画张图比较容易说清楚:
只要在上方公式区输入的,等号之后的内容, 都属于公式(绿色).
而函数(红色),则是后面带一对括号的那些内容.
这一节的初级要点是熟悉Excel现有的函数库
其中比较普世的是以下四类:
数量不多, 经常去[该网站因为政策法规不予展示] 上搜一下, 就知道用法了。
这个常用函数里面有一个人气堪比AKB48的,那就是VLOOKUP (以及他的妹妹HLOOKUP)
VLOOKUP其实就是建立两个表的关联,将B表的内容, 自动导入到A表:
———–断见取见———–
中级要点是如何将这么多函数做成一个复杂的公式。
复杂的公式,核心就是函数的嵌套, 函数里面套函数, 招中有招, 直至八八六十四招. 如长江大河, 滔滔不绝.函数的嵌套最多可以套64层(Excel 2013, 从前Excel 2003-2007为最多套7层). 函数的总字数长度可达恒河沙数(老衲记不清具体数量的时候就暂时说恒河沙数)。
下图这个例子就是一个简单的多层嵌套,主要是if逻辑上的嵌套。
不过心有五蕴,人有三昧, 简言之就是地球人还没有进化成三体星人的形态, 嵌套的数量一多, 就会令人心毒盛起, 头晕目眩, 前列腺紧张,根本看不清楚自己在写什么.
臣子恨,何时雪?
怎么办?中间列!
这回老衲举一个实用的例子,个税计算:
正统的个税计算算法是这样的:
写成公式是这样的:
把公式摘出来给各位欣赏一下:
=IF([月工资]-3500<=0,0,IF([月工资]-3500<=1500,([月工资]-3500)*0.03,IF([月工资]-3500<=4500,([月工资]-3500)*0.1-105,IF([月工资]-3500<=9000,([月工资]-3500)*0.2-555,IF([月工资]-3500<=35000,([月工资]-3500)*0.25-1005,IF([月工资]-3500<=55000,([月工资]-3500)*0.3-2755,IF([月工资]-3500<=80000,([月工资]-3500)*0.35-5505,IF([月工资]-3500>80000,([月工资]-3500)*0.45-13505,0))))))))
看到这个公式是不是感到口干舌燥,头晕目眩, 前列腺紧张?
但如果使用中间列,将公式拆解, 每个子部分做成一列, 就会立刻神清气爽:
最后将不需要的列隐藏起来,Mission complete~
然后该说说模拟运算了.
公式可以拖动,其中参考的单元格在拖动的时候位置也会变动. 下图就是老衲正在拖数据的瞬间:
一松手:
这种拖数据,虽然很简便, 但也有一个问题, 就是只能向着一个方向拖, 或上或下, 或左或右. 假设现在有一个数据要求, 有两个变量,相当于让你同时向下向右拖动怎么办?
例如不同利率, 不同年限下房贷的问题(这真是一个令人悲伤的例子, 施主请看破红尘吧):
普通公式也可以做到,但是就是需要向右拖N次, 或者向下拖M次. 等你拖好, 妹子已经下班,和别人一起吃麻辣烫去了.
模拟运算则可以一下子把这个6*6的结果全算出来.
操作很繁琐, 接下来的内容请点赞, 给施主增加信心:
先在左上角放一个本息合计公式:
然后选中所有的可变利率及可变年限:
然后选择”模拟运算表”
点击确定之后就可以Duang了:
唉,这果然是一个令人伤心的例子.
——–阿耨多罗三藐三菩提——–
高级要点是如何自定义一个函数.
刚才的例子,为了计算日期对应的季度, 使用了一个漫长的公式. 现在看看这个, 一个函数就直接命中靶心,赏心悦目~
这个Quarter函数,少侠的Excel里面是找不到的, 因为这是老衲自创. 它的真实面目是这样的(感谢 @黄老邪的提醒):
没错,这就是第五层心法乾坤大挪移第一级, 也就是VBA.
——–六道轮回分割线——–
章三:图/表
各位善男子善女人久等.老衲今天为各位解说五轮真经的第三层, 又称无上正等正觉图形图表经. 如是我闻:
第一级:表格
主要入口在这里:
也可以使用这个:
表格创建完成后,点击表格中的内容, 会出现一个新的密法空间:
这些东西都是干嘛的呢?
首先是表名称.子曰:“名不正,则言不顺; 言不顺,则事不成; 事不成, 则饮西北风”. 达尔文在加拉帕戈斯群岛发现的奇行种生物程序猿,对名称就非常关注, 程序猿对名称的关注主要是认为能方便后续使用. 更直观, 也不容易出错。
以上图出现过的公式距离
=VLOOKUP([对应级数],个税速算表,4,FALSE)
“个税速算表”就是一个表格的名字,这样的话选择范围就不是一个类似于 =D30:F37 这样很难记忆的字符串, 而变成一个非常容易理解的对象.而[对应级数]这种列名也一目了然, 如果不加命名, 就得换成=F22:F26, 还要考虑绝对地址和相对地址,非常麻烦。
起个好名字就成功了一半哦~
切片器:切片器诞生于2010年. 其实就是一种更美观的筛选。
那筛选又是什么:
筛选一共只有两路18式.属于最简易的功夫, 但是日常防身非常实用, 希望各位有时间能多多操演, 不过没时间的话, 老板也会逼着你天天操演,所以这个不必多解释.
此外再说一下表格的另外一个好处: 生成透视表特别方便.
如果是普通数据, 如果想要生成数据表, 必须全部选中:
但如果是表格的话,随便选中表中任意一个单元格, 即可开始操作:
既然事已至此,老衲顺道说一下条件格式这个惠而不费的功能, 自己使用还是给他人展示都非常美观:
下面做个集大成的演示(哎呀呀,老衲狗眼被晃瞎了, 看来只能明日再叙了)
——–阿赖耶识分割线——–
图表经 第二级图表
图表主练手少阳三焦经,内力不深, 招式繁多. 以老衲愚见, 这级偏向华而不实. 不过既然至此,老衲依旧为诸位善男子善女人逐一解说.
图表共有十式, 常用者六, 不常用者四. 看起来招式并不多对吧?
但其中每一式下可能有若干变招,故而常见的总数是: 52式 (自定义式未计入)
常用图式,可通过Excel上面的tips来理解 (将鼠标hover在某图式上就可以看到):
后面不太常用的四式可以稍微详细点说说.
——–诸行无常分割线——–
散点图
散点图是展示两变量关系强弱的图形.
老衲举个蒸栗(正例).一个国家的人均寿命和该国家的人均GDP有没有关系呢?
找了一份2014年的公开数据(不一定完全正确哦)
就用它作图(注意老衲的手法,千万注意, 否则图是画不出来的):
Duang:
能看出来点规律吗?似乎不是很容易对吧. 喝! 目下才是真正显示手腕的时刻!
再看一遍:
看来钱可通神这四个字果然是有些道理.
刚才的例子也表明, 真正控制图表的, 不在图上,而是在属性格式里.
那老衲再举一个例子,民主指数和人均GDP(购买力平价法)之间的关系:
咦,这个相关性就要差一些了. 有很多有钱的一点也不民主,但民主的基本还算有钱.
散点图在老衲看来只是一个半成品,它不能显示数据所属的”系列”, 结果就是上面这一大片圆点,你是无法直观识别每个点是属于谁的.
这个时候就必须配合第三方标签工具来完成:例如 XY Chart Labeler
顺道再说一句,如果施主打算自己也写这么个XY Chart Labeler, 功力需要达到第五层心法的第二级, 也就是VSTO. 掌握了VSTO,Excel世界的大门就算正式打开了,理论上就没有什么能够阻止施主了.
泡泡图
这是散点图的一种变招,散点图只能选取两列, 而泡泡图必须选取三列, 第三列就是用来计算泡泡面积的, 继续用上面的数据做例子,我们不但要看看民主指数和人均GDP的关系, 还得看看这个国家的总体量, 省得被一群小国忽悠:
这三列全都选中,然后选择泡泡图:
结果如何,各位不妨亲自试试^_^
——–四无量心分割线——–
雷达图
雷达图主要是对两个(多个)对象的多个属性进行综合比较的时候使用. 一般来说越圆, 各项指标就越均衡; 圈的面积越大,综合实力就越高.
注意事项: 属性值作为行(hang), 对象作为列. 这样默认就能输出正确的雷达图. (样例数据来自汽车之家,老衲不是车托)
好,出图了:
如果数据写成了这样:
那默认出图就会是这样:
但其实也没啥问题.这时候需要右键点击图片: 选择数据
然后切换一下行列就OK了
选择数据是非常基本而关键的知识点.万望各位施主对此能提高重视.
——————————-
曲面图
曲面图主要是表现什么呢…….老衲个人理解是………. 这个主要表现 z = f”( f(x),f'(y) ) 这样的计算式 (这完全不是中文好吗?! 老衲也知道啊, 但是就是不知道中文怎么讲啊!!)
曲面图和”模拟运算表” 可谓是天生的一对, 地造的一双. 什么? 你已经忘了什么叫”模拟运算表”? 那其实也没啥关系,只要你不是科研/教学人员, 那曲面图和模拟运算表在实际工作中, 遭遇到的概率小于万分之一。
不过行文至此, 图还是要上的(背后的数据就是使用模拟运算表得来的)。
是不是很酷炫? 还能3D旋转哦. 但这个图究竟能干啥呢?让老衲吃包辣条再思考一下…….
——————————-
股价图
顾名思义, 就是做出股价分析的图表. 但老衲生平从不炒股(因为没钱),都不知道这个东西怎么看…..
画一张还是可以的:
这张图对应的数据是这样的:
最多5列,分别是交易量, 开盘价, 高点, 低点, 收盘价.顺序不能随便改哦.
预祝炒股的各位同学今年好收成.
——-以下是干货分割线——-
关于图表的干货终于要来了!那就是怎么把图表变漂亮~追求美乃是人之天性, 老衲但做浅说.
请看老衲标记的部分, 再加上空白的大背景,一般而言能控制的便是这些.想要变漂亮就要从每一个标记点上做道场:
一旦开始对图表进行操作,千万不要触碰快速布局和图表样式模板哦, 否则:
然后就要确定一个所谓美观的范例. 如果不知道猫长什么样, 笔下又怎么可能画出一只猫呢?不知道漂亮的Excel图表是什么样子, 那即便对所有的操作无比精熟,又怎么可能做出一张漂亮的Excel图表呢?
(A同学默默地交给老衲一张图: 我觉得这张图很帅, 就要这样的吧)
…………
老衲花了半天功夫, 终于找到了一个合适的示例: 电影票房火箭飞升,虽然立意不高, 但是配色看着还挺喜庆~
老衲这里没有原始数据,所以绘图不能100%复刻. 先把已有的数据列出来.
然后标记上对应的颜色(需要使用第三方取色器,老衲使用的是QQ截图……)
来画个八维太极圆环图吧(饼图的变招)
好像哪里不对,这和原图没有任何相似之处啊.
切换一下行列(忘了如何切换行列的请往前翻):
好像有点意思了, 不过这也太丑了. 丑得让老衲又开始思考苦海无边,回头是岸的道理了…..
但这就是Excel自带模板的真面目……
好, 图表美化经中真正的大招来了: 天罡地火 辅助列!!!
为了弥补Excel自带模板的先天不足, 制图时, 很可能需要添加一些数据,来实现美观的效果.
原有数据(提前切换了行列)添加辅助列之后变成这样:
至于辅助列里面的数据怎么来的,很遗憾地告诉大家, 是老衲随意编造的.这个其实需要一些尝试和反复修改.
请看大图!
似乎更丑了…….
吃包辣条冷静了一下,老衲觉得问题主要是图中每个环都瘦比飞燕, 如果变成杨玉环想必会漂亮得多.那就来试试吧:
于是乎:
眼尖的同学是不是可以看出一点眉目了?
接下来就简单了:
1.去掉每个环区的边框, 使其彼此紧凑. 2. 扇区起始角度顺时针移动30度.3.将辅助列的部分改成”无填充”
填上一个底色让大家看清晰一些:
是不是有点意思了^_^
然后就是给每个环区赋上颜色.看结果吧:
然后添加文本说明(老衲只加一个示意吧,迪塞尔的光头照请各位意会)
和原图比较一下:
是不是感觉有几分神似?
——-四种清净分隔线——-
第三层表格/图表的主要内容都说得差不多了.迷你图很简单, 一试便知. 今天剩下的时间, 就讲讲第三层另一个屠龙神技吧: 般若白象功PowerView.
使用这个功能, 能生成所谓的动态图表, 怎么个动态法呢?
就是选中某个数据系列或者筛选项时,对应的数据系列会变色/高亮.
还有就是使用一个切片器(还记得这是什么吗?)可以同时控制多个图表(即所谓的联动)
当然如果真的有这种需求存在, 那Power View应该还是有点价值的. 在老衲的生涯里, 这种需求非但极少,而且均属锦上添花的作用, 最后都是用VBA解决的……
另外一个略有用的功能就是, PowerView能根据地名自动绑定Bing地图.
老衲伪造了一份美国各州人口表(使用了Randbetween函数):
将这两列选中后, 点击”Power View”,这个时候就可以去泡咖啡了.
大概两分钟吧, 终于生成了一个全新的工作表(Sheet):
这个还是挺方便的.
但是老衲从前见过的地图,一般要求画成这样(这个老衲当年也是用VBA解决的):
所以老衲称之为屠龙神技, 还是有自己的道理的. 顺便一句, PowerView的功能,是用silverlight实现的(可以理解成微软家山寨flash),而silverlight已经被微软判了死刑……..春草碧色,春水渌波,送君黄泉,伤如之何, 伤如之何,阿弥陀佛……..
(至于怎么画上面的图, 可以参见老衲的另一个答案: excel上怎么做数据地图? -靳伟的回答)
第三层真经讲解完毕. 不日更新第四层.
——–正理因明分割线——–
章四: 数据透视表
数据透视表(pivot table)这个]翻译比较古怪. 不过名称不是大问题,只要理解数据透视表能做什么即可.
数据透视表是一种简易报表, 可以对不同的数据行列进行数据汇总.
数据透视表的入口在此:
生成了Pivottable之后的主要控制区:
开始举例,老衲这回还得请出王二和李明来:
此时前世孽缘来了,一个自称是老板的人,让你算一下李明和王二现在卖出的东西的平均价格是多少.
用公式sumif是可以实现的,但现在有更好的办法来了.
选中这个表格, 插入数据透视表:
然后输入一个计算字段( 计算字段CalculatedField是Pivot table中的重点功能, 要着重注意):
然后在右边拖一拖:
我们用Sumif核算一下
看来没什么问题^_^
这时候老板又发话了,按照颜色和销售人员各统计一下总销售金额. (高达八成的老板都是这样) 怎么办呢? 很简单,再拖一下:
就是这么方便,就是这么任性!
老板继续发话,”你这么搞完全没有理解我身为老板的一片苦心! 我是让你制作两个表, 一个统计人员, 一个统计颜色, 然后给我一个过滤表单,这样我可以按日期看人员和颜色的变化趋势.”
这个说来很简单,只需将Pivot table整个圈中, 复制黏贴, 然后改一下字段即可.
过滤项呢,也是将字段拖入到筛选器即可:
但现在有两个pivot table, 是否有方法同时操作两个pivot table呢?
有, 那就是切片器(从前也提起过哦)
选中一个Pivot table, 添加一个切片器。
添加切片器之后,右键选择”报表连接”, 继续添加连接的pivot table, 两个都选中:
这样,用这个切片器, 就可以达成一个切片器来控制多个pivot table的目标:
顺道说一下,”日程表”也是切片器的一种, 只不过外观是特别优化过的罢了:
眼尖心细的少侠会发现,在PivotTable操作中, 有几个选项一直是灰色的, 例如:
还有:
这是因为它们都需要特殊的奇门兵器和外道功夫:
老衲由于近来深研佛法三宝(合称PPT), 一时没有准备OLAP真经, 所以这节暂时跳过,待机缘成熟再来补完。
至于Power Pivot, 各位可以直接视之为”不服跑个分儿”版的数据透视表. 严格点说,它预期起到的作用是简易的数据库(例如Access), 而工作方式比较像数据透视表。
另外想要在Excel中突破一张表最多100万行的限制, 也得仰仗这位的大肚能容. 在Power Pivot中,一张表的最大行数为20亿行. 详情请见: PowerPivot Capacity Specification
但老衲还是认为, 如果必须应对上亿行的数据, 学习一下数据库–例如SQL server, Oracle, MySQL–是很有必要的, 好过使用这个Power Pivot. 所以这个PowerPivot暂不深表.
数据透视图和普通的图表几乎没什么不同, 只不过能和一个数据透视表彼此联通, 控制表的同时, 可以影响到图的展示内容.不作为重点.
这一章通常来说, 最常用的还是 计算字段 和 计算项.望勤为操演.
余者待老衲重新准备一下, 来日方长.
——–众因缘生法分割线——–
章五: VBA / VSTO
老衲痛感逝者如斯, 不舍昼夜, 因而决定提前讲说章五.也就是乾坤大挪移心法.
请各位注视自己的Excel, 是否能找到我神功入口?
找不到也正常,毕竟是奇门秘籍, 一般都藏之名山大川, 幽谷白猿之中. 请从这里找寻:
“开发工具”一定要选中才行.
这个里面常用的又是”代码”和”控件”
举个栗子, 请各位看个大概:
1. 点击录制宏:
然后对Volume列进行排序操作:
然后点击一下”停止录制”:
然后点击左侧的VisualBasic:
可以看到代码了:
这就是VBA奥义最简单的例子.
各位少侠中, 可能有不少人第一次发现这个入口. 对于编程(没错, 就是编程)可能也不太了解,所以有一些基本概念要澄清一下:
这些基本概念分别是: 对象, 属性与方法, 集合.
因为老衲遁入空门前是一名光荣的人民厨师,所以就用厨师来做一下比喻.
一个厨师, 就是一个对象. 一群厨师(同类别的对象), 就是一个集合. 集合也是对象的一种.像”顺峰颐和园路东口分店后厨大师傅们”就是一个集合, 也是一种对象.
我们来定义一个厨师:
Dim someone as Chef
现在在代码世界就有了一个叫someone的厨师了.
那此Someone有哪些属性呢? 例如身高178cm, 体重73kg, 月工资15000 (顺峰店啊, 人均消费过千,大师傅没有上万的薪水, 你们还愿意去吃吗?
身高 = someone.Height
‘这时候身高=178, 前面这个 ‘ 号要注意, 这个是注释, 开头写上’ 号的一行, 在程序眼里是不存在的,只是方便奇行种程序猿(达尔文在加拉戈帕斯群岛发现的哦)来阅读的.
体重 = someone.Weight
‘这时候体重= 73
工资 = someone.Salary
‘这时候工资= 15000
那”顺峰颐和园路东口分店后厨大师傅们”也可以是一个对象, 为了举例方便,大师傅集合就叫Chefs.
Dim 顺峰颐和园路东口分店后厨大师傅们 as Chefs
那顺峰颐和园路东口分店后厨大师傅们 有哪些属性呢? 例如总人数15人,每月工作时间21.75天
顺峰颐和园路东口分店后厨大师傅们.count = 15 (等于号, 表示是赋值操作, 把右边的值写到左边去,前提是左边的东西可读写, 刚才这句话相当于为大师傅强行指派了一个总数, 当然通常来说,这个count很可能是只读的)
顺峰颐和园路东口分店后厨大师傅们.WorkDays = 21.75
好, 对象, 集合, 属性三个都说过了, 那方法又是什么?
方法是对象能做出的行动.
例如someone这个对象, 本质上是一名光荣的人民厨师.他能干什么呢?
someone.fries() ‘炒/炸
someone.boil() ‘煮
someone.stew() ‘炖
…..’英文能表达的动作太少了, 完全不适用于中式厨师啊
…..
那”顺峰颐和园路东口分店后厨大师傅们”这个对象呢?
顺峰颐和园路东口分店后厨大师傅们.work()
顺峰颐和园路东口分店后厨大师傅们.Dismiss()
…..
…..’还有扯淡吹水等动作就不逐一列举了.
有这些概念, 少侠基本就明白自己面对的是些什么东西了.
然后老衲还要以厨师工作举例, 说明一下语言(VBA)和IDE是什么.
编程都有语言, 语言本质上就是一种人类和机器相互沟通的工具, 人类告诉机器怎么来运作, 如何执行动作,创作出猪肉料理来. 在厨师界, VBA就是中式菜, C#就是西式菜, Java就是印尼菜. 不同的语言在思路上是很不一样的,例如Java讲的就是普适口味, 不再众口难调; VBA讲的就是快熟快上; Erlang讲的就是明火多灶;但是目标都是把饭做熟.
那IDE(Integrated Development Environment)是什么? 当然就是厨房啦.有全套厨具, 灶台, 烟机, 还有一个宝贝哦: 全方位支持的速查菜谱.
Excel自带一个VBA的IDE, 虽说简陋得紧, 一副从大清朝穿越过来的样子, 不过好歹也算功能齐全, 聊可一用.(到了VSTO可就是使用全套顶级大厨厨房了, 保证乐不思蜀~)
这个厨房分为几个常用功能区:
菜单区:这个是控制枢纽, 各个命令的总入口都在这里。
工程区:在这里可以直观地管理/组织你的代码。
属性区:对于在工程区选中的对象, 在属性区可以直观地显示该对象的各个属性。
代码区:就是写入VBA并调试执行的地方
监视窗口:对于运行中的对象, 我们想知道它在中间状态中的各个属性值变化, 可以通过这个窗口来观察. 如果自己做过牛排, 或许知道探针式温度计.这两个道理很相近。
老衲说了这么许多基础知识, 各位施主恐怕已经昏昏欲睡了吧. 没事,咱迅速炒个回锅肉, 大家精神一下:
第一步: 起火,找一个新锅(建一个新Module).
第二步: 写代码, 炒回锅肉.写完了之后点上面的三角箭头
第三步: 回锅肉装盘亮相:
够短平快吧?~~
从回锅肉到全世界, 中间的困难主要在于对各个食材(对象,类库)的熟悉程度. 再往上则是编程思维(例如编程Pattern). 因此老衲也不打算写太多了,可以参考: Getting Started withVBA in Excel 2010
循序渐进,必至大道.
——–破我执分割线——–
应某位少侠的要求, 提前讲解一下Excel(2013)的局限性,如是我闻:
1. Excel(2013)及之前,大概占用普通Windows电脑内存的25%-30%, 即容易崩溃, 或出现各种不稳定症状. 尤其是32位Windows.该数字出于老衲的经验. 因而一个大内存是很有必要的.
2. Excel(2013)的一张工作表,可以容纳1,048,576行 乘以 16,384 列. 那么如果少侠家资丰厚, 随便拿出两百万行数据怎么办? 请使用SQLserver express(express版本是免费的)来辅助进行数据预处理. Access理论上也可行, 但是有失简陋,所以老衲向来不用, 从而知之甚少.
3.一个单元格能放32,767个字符.
4. 回退能退100步.但使用了VBA就不好说了.
5.一个下拉列表单里能放10000个选项.
6.一个函数里面最多使用255个参数.
7. 公式里面的函数嵌套, 可以嵌套64层.(说7层的那是还没更新到2013, 请加速更新)
8.公式的长度不能超过8192字符.
9. 一个图表里最多可以放255个数据系列.(一般来说放二三十个就已经糊满了)
10. 面积图, 不能用平滑曲线.需要使用别的方式实现.
11. 柱状图,不能同时两个柱状的数据系列分别对应主坐标轴和次坐标轴. 需要变为一个柱状图,一个折线图.
本站非明确注明的内容,皆来自转载,本文观点不代表清新电源立场。