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-09
3

这是暗暗的报复吗

By xrspook @ 9:24:09 归类于: 烂日记

从粉到黑其实完全不需要经过什么大波澜,可能只是无意之间等一件事而已。一直以来,我都是Office的粉丝,非常拒绝WPS,现在我依然不喜欢WPS,尤其当我有点迷恋上VBA以后。之所以VBA,因为数据透视表已经在某些方面无法满足我懒的需求了。但我只是一个新手,所以即便是很简单的东西,我的VBA脚本也要经过很长时间才能琢磨出来。

爱上周我突然发现自己Microsoft 365上面的Power Pivot不能用了。昨天,我又突然发现,原来自己连Power Query也不能用。这两个不能用,意味着所有高端的Office功能全部歇菜。这些东西全部都不能用以后,我付费的Microsoft 365和从前不用钱,而且体积还很小、几乎不出错的Office 2003,仅仅只剩下界面比较好看,公式多了一些而已。从Office 2003到Office 365,我花了很长时间,因为一直以来我都不觉得自己必须改变。因为工作上某些公式打不开,某些功能很厉害,但我做不到。在深思熟虑以后,我才终于换到了Office 365,但是当我的Office 365。用了不到两年之后,Office 365,变成了Microsoft 365,进而发生了Office软件里最强大的功能居然失效这种事。这是我电脑自己的问题吗?但我又不能完全保证这只是某台电脑的问题,为什么单位这台机就不能开,家里那个就没问题呢?两台机都是64位的Win7,理论上应该差不多。之所以会发生这种事,是不是因为家里那台机我没有进行升级呢?虽然微软说2020年1月他们已经停止对Win7的支持,从那里以后,Microsoft 365仅仅能得到安全更新,但实际上不是这样的,因为他们新的函数,我通过更新也能使用了,而且在我完全没有察觉的前提下,插入界面也多了一个叫做加载项的东西,那里的内容一定是新的,之前我没见过,不仅仅是我没见过,我之所以判定那是新的东西,因为那个功能居然还没有翻译成中文,部分东西是英文的。这就意味着这个功能并不完全成熟,就像在Office 2016的家庭及学生版里,Power Query用的是英文界面,到了Office 365,那个东西已经是内置功能,不再是加载项了,那已经融入到了软件本身。既然微软条款上说,只对Win7系统下的Microsoft 365,进行安全更新、没有新功能,但实际上新的函数可以用,界面上也有一些不知道是什么东西的家长进怎么理解?我非常有可能相信,两个强大的Power数据处理是为他们故意屏蔽掉的,但这只是我的猜测,或许真的是我的电脑出了状况才导致那两个不能用呢。我需要回家好好验证一下。功能不能用,我把错误代码拿去搜索,得出的结果很少,而且通常都是Power BI的。数据合并这种东西如果不用Power Query,就自己写个VBA。至于Power Pivot的思路,应该能在其它更强大的数据分析软件里用,因为这可是Office 2010就可以插件使用的功能啊!

微软的作死,只会让他死得更快!

2020-08
21

懒精自动化

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

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

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

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

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

2020-05
10

Excel里写长公式

By xrspook @ 9:42:47 归类于: 烂日记

不知不觉好像我已经快一个星期都没有碰python了,原因是在家的时候我懒惰,在单位的时候,一心在整理各种各样的数据,也正是因为我正在整理数据,所以其实在我的骨子里是念念不忘想使用python这个大招的。在数据处理方面。我觉得,我正在用Excel人肉操作的那些,如果以一个正确的方式丢给python,那绝对是几秒钟就能完事。明明我知道可以这么干,但现在我还没到达那个境界。

昨天我整了一个巨长的公式。在最开始的阶段,我在普通的Windows记事本里折腾,但是当公式嵌套得越来越多以后,显然普通的记事本把我直接看晕了。我把那个东西放回Excel,但实际上Excel这个怪物根本不给我显示到底那些一对又一对的括号谁跟谁匹配。我也试过把那条长长的公式放到VS Code里,我随便打了个py文件往里面放Excel公式。虽然某些地方高亮了,但是括号匹配还是很不行。如果那不是一个Excel公式,而是一个python文件,我早就给它写很多注释、搞很多回车了。不就是个嵌套了三层的if嘛。但实际上,如果这是在python,完全可以不嵌套,用三组平行的if就解决问题了。最终,我把那条很长的公式贴到了Notepad++里。经过一番折腾,我觉得Notepad++才是最适合编辑Excel长公式的工具。虽然Notepad++不能自动生成成对的括号,但是在判断括号对应性方面,我感觉已经足够了。他们会把成对的括号用加粗的红色显示。当你选择这一边的括号的时候,那边的括号就红色加粗了,这样我就能搞清楚自己编辑到了哪一层括号。习惯了用python之后,什么括号,什么使用范围之类的东西全部用冒号、用缩进就解决了,回到Excel里要用一句话表达,本来用三句短话就能表达清楚的东西的确挺烦人。为什么Excel编辑公式就不能用回车,不能用缩进解决问题呢?如果有几个回车的话,显然那条公式到底在表达什么就很明白了。在Excel的某个单元格里回车,那就是要执行公示的节奏。如果是数组公式,你还得用组合键结束。有时我实在不明白他们为什么非得一定要用组合键呢?为什么不能在公式外面加一个什么函数包裹,让软件明白就在执行数组公式呢?

习惯了python的简洁与人性化后,回到Excel让我各种不习惯。在python里,单引号和双引号都可以用来表达那是字符串,但Excel要表达字符串,要表达某些固定格式,必须用双引号。另外一个人让我不习惯,因为我已经彻底忘记的就是在Excel里面不等于用的是<>,而在其他编程语言里面,不等于的表达方式是!=。所以当我在Excel里使用!=的时候,Excel懵逼了。于是我不得不去搜索,进而发现是我自己搞迷糊了。还记得小学的时候,家长们总担心孩子如果一边学汉语拼音一边学英语会不会张冠李戴,用汉语拼音来读英语或者用英语来读汉语拼音。这是两种彻底不一样的东西,虽然他们的最终结果就只是个发音而已。我是小学一年级开始学汉语拼音的,三年级的时候开始学英语口语。在我开始在学校正规接触英语之前,我的汉语拼音已经很熟练了,而在我接触编程语言之前,我接触过Excel,但只是接触过而已。对二者的深入了解几乎可以说是平行发展的,所以混淆二者的某些基础用法我觉得可以理解。

最终,我把那个我想做到的效果用一条很长的公式表达了出来。那条公式针对的不是具体的某些单元格里面的东西,所指代的位置全部都是相对的。所以根本不存在拖拉以后会出现状况。有了这条公式我就彻底做到了对某个仓开始入库、结束入库以及期间库存精准定筛选定位。

那条长公式在成功整出来之前,我先在纸上列出了几个条件。代码这种东西是非常讲究思路的啊啊啊。

2020-04
29

半桶水的烦恼

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

要实现某个功能,有非常多的做法,到底要怎么做才能避免出错呢?Excel的函数非常牛逼,把那些公式弄好了,简直就是天下无敌的节奏。公司的原理很简单,但是套用起来做着做着就傻瓜了。有可能是手贱,标点符号按错了,更大的可能性是单元格引用出错。我已经不记得用Office 2003的时候是个什么状态了。反正在Office 365下面,如果在一个工作簿里面应用了别的工作表,把带公式的工作表复制到另外一个工作簿里的时候,那些引用的单元格会以绝对地址的形式继续指向原来工作簿的某个表。显然,如果只是指向本工作簿的话,找不到地址,顶多显示错误,但是如果那东西继续指向原表,会引发很多问题。因为那是一个绝对地址,即便你把工作放在同一个文件夹下面,还是不能解决问题。恐怖的绝对地址会导致云同步、在多人协作的时候,发生状况。为什么Office 2003下就没有绝对地址和相对地址这个烦恼呢?在Office 365下,数据透视表的绝对地址算是终于改正过来了,但我想不到公式居然也这样。这是一个令人非常崩溃的事情!如果工作表里面的公式是大量的,这将是一个灭门灾难。如果某一个工作表里面的公式是大量的,而另外一个工作表里面的源数据也是大量的,外加还有一个数据透视表指向了源数据。无论是动哪一个,都会导致另外的那个上的工作推倒重来。为什么就不能设置某个工作簿里面的链接采用相对工作簿内的连接,而不使用外联呢?停止了外联,就意味着那个单元格的数据从一个动态的东西变成了静态的。绝对地址这个问题,在云同步、在多设备协作的情况之下,根本是无法操作的,为什么微软会犯如此低级的错误呢?数据透视表的傻逼是发生在Office 2010版之后的。公式上的傻逼,之前貌似我还没遇到过。可能并不是因为不存在,而纯粹是因为我没有用到这种功能。

但Excel不能一次性满足我所有想要的东西的时候,我就会想到要不要自己写个脚本解决一切难题。人想得到的逻辑,用脚本都能实现,前提是必须考虑出一种能够包容所有例外的规则。如果不能包含特殊情况,脚本写出来是毫无意义的,因为最终还是得人手去加工,这非常不科学。所以一定程度上,我更喜欢用数据透视表,自动处理那些东西,而不是手动设置公式。设置公式理论上是一个一劳永逸的过程,但实际上只要你手动在上面修改了一些东西以后,就像蝴蝶效应一样,后果不堪设想。某一次的修改,会导致往后半天都找不出原因。如果用数据透视表的话,双击单元格就会到达最基础的那个数据。查错是非常简单的,但是,用了公式以后,然后你又因为某次犯傻在某个你以为设置了公式的单元格里面输入了常数,那将导致一次令人绝望的debug。不要问我为什么会知道……

与其让我查找错误,还不如让我总结特点制定规则,让他们不犯错误。当然,不犯错误,是根本不可能的。

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