2020-10
13

Excel的高端玩法

By xrspook @ 8:43:14 归类于: 烂日记

数据本身没有问题,如果我们不能让它们确立某种关系,只是因为我们对那个东西还不够了解而已。在Excel里做一件事,你可以通过很多方法,比如说函数,比如说VBA,比如说SQL查询,又或者Power Query或Power Pivot。当然,我这里所说的,主要是针对查询,或者说数据清洗类的东西。如果纯粹是针对单元格的格式化,函数以及Power BI系列以及SQL是没办法做到的。

同样一个数据,用不同的方法都可以得出目标答案,但是哪个会更简便快捷一些呢?函数我觉得挺被动的,尤其是在处理大量数据的时候,效率非常低。因为在处理一些复杂东西的时候通常要用到数组函数,即便不需要用到数据函数本身,其实也在运用着数组函数的变体。而且函数这种东西受Excel本身版本的限制,越是低版本的Excel越是没办法轻而易举地实现某些逼格的功能。于是就出现了你不得不为了某个功能升级Excel,又或者因为你的伙伴升级了Excel,用了一些高端的函数,但是你却看不到,工作就没办法继续下去了。SQL和VBA是两个大杀器,很早以前Excel就已经支持。与其说他们是Office软件的一部分,不如说这两个东西更接近于编程语言。我对Excel里面的SQL不是十分熟悉,因为至今为止,虽然已经折腾了不少网站,但是我从未试过操作数据库。SQL在Excel可以用,但我觉得可能在Access里SQL会用得更顺手一些。比如说如果改变数据源,比如移动文件之后,SQL需要重新连接。若没有VBA的帮助,这是无解的。我不喜欢用SQL的其中一个原因是它会在硬盘的某个位置生成某个数据库。

VBA这个东西强大到任何你想到想不到的东西都可以控制,无论是数据本身还是说单元格的格式,一律通杀,它甚至可以让Excel自杀,又或者让你的系统自杀。VBA用得好不好直接决定了某个脚本的运行效率。是对初级用户来说,VBA的学习成本实在是高,除非你从来不打算要建立自己的规则而纯粹只是用别人的东西。

至于Power BI系列的Power Query和Power Pivot现在我仍然处在甚至还不能说入门的阶段,我只是稍微了解了一点这两个东西。在数据清洗和建立关系的时候,它们实在太强大了。但是要使用这两个东西,Excel的版本就必须有要求。所以这也导致了不少免费用户直接绕过这两个强大的东西。我也不知道为什么自己在使用Excel高级函数几乎还没入门的情况下,我就去折腾M语言。我觉得那个东西一定程度上颠覆了我对数据的理解。Power Query对数据的处理方式就像通过各种蹂躏就能得出你想要的东西,其间你没有修改原数据,所以实际上在写M语言的时候就像是手工编写一个宏,而那个宏要比一般的VBA简洁很多。之所以简洁,一定程度是因为那是在高级套用的前提下。Power Query里玩的数据转换实际上是在折叠、删除以及扩充,一定程度上就像是在用类似于递归或者迭代的方式。

别人把时间耗在应付考试上,我把时间耗在折腾自己上。

2020-09
19

我喜欢Excel

By xrspook @ 20:53:41 归类于: 烂日记

Excel的一般公式,我比较熟练,一些高级公式的叠加,我需要找教程套用,但起码我知道那是可以做到的。一般的数据透视表,是我一直以来用得相对来说最顺溜的东西,至于高级的数据透视表,也就是超级数据透视表我几乎不了解它的高级用法。在数据的筛选查询方面,之前我用的是公式,而近期,我知道了有Power Query这种神器。在这之前,我已经知道可以SQL语言查询。去年我开始系统学习了Excel VBA。这让我大大提升了某些工作的效率。当然这是非常有针对性的。对我来说,要开发一个VBA脚本需要好些时间,并不是一写就能用的那种类型,期间要经过不少修改。所以其实总的来说,对Excel的了解我还是比较全面的。

也正是因为有这样的经历,所以当我遇到某些综合性的问题的时候,当别人把目光主要集中在某个他们很熟悉的版块的时候,我会凭借我的直觉找问题,而不局限于他们觉得出问题的那个地方。比如在把SQL查询跟VBA结合的时候,别人会把精力放在SQL查询有没有写错上面。SQL有没有写错,其实我根本没看,对我来说那些东西太长了,看不懂,而且那个人写的VBA脚本缩进很有问题,看得我很郁闷,所以我就更加没有心情在那里琢磨。那既然能计算出一个正确答案,说明那个查询语句应该没什么问题。也正是因为写脚本的人的那堆东西格式比较混乱,所以我有理由怀疑那是拼凑起来的脚本,因为居然在脚本的开头连变量的定义都没有。为什么VBA里没有进行规范的变量定义,后面也居然可以照样使用呢?这让我有点惊讶,毕竟这是个VBA,不是python。C语言里,如果不先进性变量定义,后面根本用不了。在我记忆之中,VBA的变量在使用之前是需要先定义的。最终我发现是那个人的脚本之所以出错,是因为某些语句的套用搞错了,为什么他会把那个东西放在里?我觉得大概是因为他没有明白他一开始做的那个with是什么意思。但如果你问我为什么他把那堆东西套在里面会出错,而且是某些地方出错,不是全部出错,我回答不出来。理论上这种错误能在恰当的调试中体现出来,但实际上,VBA的调试句子我还用得不算很熟练。或者你会说,这是因为我的VBA学习还不够系统化,但我觉得我已经用了学习VBA最靠谱的那本书了。可以肯定的是,一些很基础的调试方式我还没掌握,如果我学会了那些东西,我可以大大提升我的调试效率,把错误定位得更精准。VBA脚本这种东西,我觉得最根本的是必须得理解。如果纯粹是各种套用,基础功能的确可以快速实现,但是当遇到的问题比较综合的时候,就会出现一些他们完全料想不到的状况。那种状况有可能与脚本本身的内容无关,与脚本的结构有关。

相对来说,Excel里我用得最弱的是高级公式的套用。如何用一个非常复杂的公式解决一些高端的问题是我一直以来都不大上心,或者说记得不够好的部分。非常复杂的公式,尤其是数组公式,虽然能解决一些神一般的问题,但问题是,其实那些公式需要耗费大量资源,所以在处理大数据的时候,非常有可能出状况。我是一个实用主义者,能做到某个功能,但是做起来的效率不高不好,我为什么要选择那种只是看上去很炫酷的方式呢?情况就像用VBA解决同一问题的时候,如果只是在工作表层面处理和先用内存数组处理再在工作表层面表达,效率千差万别。

Excel对我来说,除了要最终结果,过程也得追求高效和方便。

2020-08
21

懒精自动化

By xrspook @ 8:24:58 归类于: 烂日记

对别人来说,做某件事就是从系统上读取某些数字,抄在纸上,然后不断地敲计算器,得出某些数据,然后再按照某些格式整理成别人要的样子。之前我也是这么干的,但是我觉得,这实在是太累人了。我要做的不仅仅是读取系统上的数据,我做的是读取我自己的数据,然后跟系统上的做比对。做的东西必须是最小单位。明细对好了,后面的汇总是水到渠成的事。我觉得,汇总数据这种事,规则我定下来,结果应该是自动生成的,管好我的明细数据,汇总数据就不会出乱子。汇总数据出来了以后,还得按照某些格式整出来,这其实是一个填坑的过程,但是应该怎么填呢?逻辑的思路就是,如果有数据就展示出去,如果没有数据,就为空。这是主要思路,但某些细节问题其实也是需要考虑的,比如说数据与数据之间的标点符号:逗号,分号,句号,冒号等等。没有数据的时候,那个地方是空,但是在连接两个数据之间,有些人默认带入了标点符号,于是就会出现一个神经质的状态。数据与数据之间有一大堆莫名其妙的标点符号,正常情况下,那些东西都不应该存在。按照人的思路,那些东西根本不可能存在,但为什么有那些东西呢?显然是那个做判定的人没有把标点符号写到判定里。什么时候应该逗号,什么时候应该句号,当没办法判定哪个才是最后一个的时候,标点符号怎么办?其实真的不能判定吗?肯定是可以的,当某个元素的序号等于这个数组的最后一位,显然那个东西就是最后一个了。在Excel的世界里。尤其是不考虑VBA,只考虑前台的公式,这个逗号的判定在我现在的能力范围之内,的确有点难,所以遇到这种情况,我干脆每个数据给它一行,每行数据的末尾,不加任何标点符号。

昨天我突然意识到,Excel的公式编写是可以插入空格和回车的,当然,空格和回车要加在恰当的地方。比如加在数据连接的地方,不要加在某个函数里。又或者其实加在某个函数里面也是可以的,但是如果你在表达字符串的地方加入了很多空格,最终的结果肯定会哭。也正是因为回车的加入,让我的公式看上去更整洁了,我不用纠结不断地眼睛急转弯。复制粘贴也变得相当的容易。的确复制粘贴很简单,但是粘贴得多了,每一行的数据框架都是一样,只是修改了里面的部分内容就会让我自然而然想到使用循环。如果Excel的公式里有for或者while这种循环该多爽。换句话说,为什么就不能有循环呢?之所以这样,大概是因为Excel,没想过你要么在某个单元格里面展示一堆合并起来的字符串。这种字符串的合并,理论上更应该出现在Word或者普通的txt里面。

我首先写了个索引,然后写了个数据,接着对数据进行数据透视表,最后写了个很长的公式。根据数据透视表和数据透视表以外的一组数据,生成了两段字符串。字符串里面所有数字的格式全部都严格按照我需要的模样输出。这样做的确实现了我想要的功能,但我感觉还是有点麻烦,如果写个VBA,加入数组,这些东西很容易就能表达出来。从前前台看来,也会干净利落很多,但问题是,加入VBA的话。Excel的保存格式就得改变。对小白来说,VBA是个洪水猛兽。他们不知道该不该允许宏这个东西。如果我是他们,我不了解这个东西。这个东西的确有安全隐患,我肯定不会主动打开,即便我知道这个东西可能很强大。

开源的东西用起来得花一些步骤。组装好的东西用起来的确用很爽,但问题是,万一里面有猫腻呢?归根到底,还是自给自足、自娱自乐比较好。

2020-08
13

喜欢VS Code

By xrspook @ 11:30:56 归类于: 烂日记

我感觉自己越发喜欢VS Code了,这个东西的确就是我想要的写码工具,如果早点认识它,从前我就不需要走那么多的网路了。用VS Code写过代码以后,我对它是一发不可收拾的喜欢,但我始终不习惯在暗黑界面码中文,毕竟写代码和码中文不一样,写代码的时候思路是自己的,但积木的类型是固定的,这就意味着写码的时候如果有Emmet会相当便捷快速不出错,但如果在写blog的时候,尤其不是写代码技术类的时候,Emmet会变得毫无用处。同样我不需要额外纠结的还有中文语法。写blog是很自由的事,试想一下满屏的各种颜色波浪线,侧面和下面还有各种数字圈圈那将多么的恼人,当然了,貌似VS Code还没有厉害到可以有中文语法自动检测这种功能,我估计英文可能已经有了,Office几百万年前就已经有的功能VS Code可能天生就内置了,即便没有,让你绝对想不到的插件也肯定能实现。Offcie有中文语法检查的功能,但那个东西,笑而不语就好,别当真。

写码为啥要VS Code呢?不仅仅是Emmet功能,写过的函数,设定过的变量往后再用的时候,敲几个字就能有提示,就更不用说,使用默认的系统函数时能马上显示出详细的函数说明。自己写的变量一个颜色,系统函数一个颜色,保存以后还能自动语法检查,妈妈再也不怕我会犯格式上的错误。但话说回来,格式不错,不代表代码就一定可以运行出你想要的效果。毕竟代码的核心仍然应该是你的思路。

我不知道为什么大家都说暗黑模式写码护眼,护不护眼我不知道,但我知道白色界面看五颜六色的高亮会刺眼,相对来说,背景主色调是深色就没有这种不和谐。是不是因为这样,所以一开始的电脑都是黑屏白字而不是反过来呢?因为他们完全可以把那个东西做成计算器那样白屏黑字。现在我的习惯挺奇怪,写码的时候我用黑屏,写blog或者写其它文章的时候用白屏。我感觉自己用白屏的时候字体大小得设定到起码是黑屏的1.5倍。

去年我第一次接触R语言,觉得那个东西实在太伟大了!今年年初我的计划是学习R语言,但结果Python捷足先登了。VS Code搭配Python那是爽得一逼的节奏,因为Python是很大路的程序语言,所以很多东西已经非常完备了,相对来说R语言小众一点。无论是官方自带适配的还是第三方插件都比较弱。我用过R语言写码,那个东西简直是太难写了!一大串东西写好,要修改其中的一些,还不能鼠标快速插入,只能用键盘光标移过去,实在太逆天!当时我用的不过是系统函数,很多参数的位置和写法都是固定的,无数次我运行失败都是因为我手贱敲错了。试想如果这放在VS Code,有自动的语法提醒纠正,这种烦恼根本不存在。同样比较烦人的写码还有Excel VBA。运行的时候他们会弹出一个代码告诉你出错了,代码到底什么意思呢?到底是哪里卡死了呢?他们明明都得出代码了,却不直接告诉你到底是什么意思,很多时候,运行卡住是因为我手贱把一些系统参数的名字写错了…… 又是参数名字写错!如果在VS Code,这种事情根本不会发生啊!

写代码的方法千万条,但能边写边提醒你不犯低级错误的通常都不是某个程序语言自带的编辑器……

2020-07
30

VBA批量打印凭证

By xrspook @ 9:44:34 归类于: 烂日记

昨天我打印了一大堆记账凭证面单。理论上我可以用邮件合并的那套表格去的,但实际上,我依然用的是单独一个Excel文件带控件的打印方式。需要打印的凭证面单有很多。估计超过10张。如果只是一两张,用控件的方式挺好,控件我设置为可以利用上下键,选上一张和下一张。也可以在输入函数的地方直接手动填写数字。只打印某一张或者某几张的情况下,这样做很方便。但当我要打印的东西是一大堆的时候显然这就不靠谱了,万一我手抖一抖,按两下,跳跃了呢?万一我按完,选择以后快速打印按钮按偏了呢?我根本不知道自己打印了多少张,中间有没有漏掉。邮件合并可以有选择范围,我可以选择某个范围内的记录,生成一个新的文件,然后直接打那个。如果我生成一堆工作表,我还得把它们删掉,新建一个文件,我还得删文件。与其这样,不如让机器去执行,我一边数据递增一边答应。人肉会按漏,但对机器来说太简单了。虽然这样不断提交打印,会比全部一起打印慢一点,但实际上,对我来说,一点影响都没有,因为我配套使用的是打印速度非常慢的针式打印机。如果我用的是激光打印机,而且还是非常快的那种,打印内容非常少的话。我这样的操作会影响打印速度,但现在,我做的做法非常简单且完美。之前我想不通,在Excel里要如何实现批量邮件合并的功。昨天,几乎可以说不费吹灰之力我就实现了,而我做的仅仅是写了一个几行字的宏而已,而具体某些步骤要怎么写代码可以先录制一个宏,看看参考一下。我最需要参考的是快速打印要怎么表达。因为VBA批量打印太方便,所以我通常都忘记先选择针式打印机。针式打印机不是我的默认打印机,所以按下批量打印以后,当我的激光打印机开始轰鸣,我才记起自己搞错了,所以我得迅雷不及掩耳地关掉激光打印机的电源,然后重新选择针式打印机。我觉得应该完善一下这个脚本,我应该把打印机也设置好,默认为针式打印机,这样我就不会有用错打印机的烦恼了。

这个脚本非常简单,前面的部分我已经写过了,用的是指定行打印的思路。首先输入两个数字,如果第2个数字留空,就默认等于第1个数字。如果第1个数字也空的话,打印不会执行。当第1个数字和第2个数字都靠谱了,会进入一个循环,步长为1。里面的语句超简单。说白了就是一个让某个单元格里的数字自增,然后快速打印该页面,因为这个页面我已经设置了打印区域,所以一切都很简单。之前我还在想,要做个控件,在Excel里面输入起始和结束页码,但实际上这根本没必要,直接弹窗挺方便。当然,就严谨性来说,我的脚本是有缺陷的,比如万一我输入的东西不是数字还是其他字符呢?万一我输入的数字不是整数,不是Excel表格里行数范围内的东西,而是一个浮点小数呢?我当然可以对输入进行严格的限制,限制它的范围,也限制它的数据类型,但这样显然就有点过了,毕竟这种打印完全是用来方便我自己的,没必要做到这么完美无缺。

现在,妈妈再也不用担心我会漏掉打印某些凭证面单了。

© 2004 - 2020 我的天 | Theme by xrspook | Power by WordPress