博文

Power Query学习笔记1:多列聚合求和

图片
 需求分析:公司在全国有多个销售区域,每个销售区域又有多个项目组。现需从各销售区、各项目组收取1-12月的销售数据并汇总,汇总字段按照销售区区分。 此文目的:多个表格的简单整合比较容易,使用PQ可以实现自动更新,数据处理能力也大大提升。此文针对的是多表汇总后进行的按销售区域聚合的步骤。 解决思路:数据聚合可用的方法有”分类汇总“或”数据透视表“,但是因为上一步骤使用PQ对多表进行了整合,再对整合后的表另外单独数据透视就有点繁琐,何不一步到位在PQ中进行聚合?PQ中自带“分组依据”功能。下面是例表,同时先看下“分组依据”如何使用。 “分组依据”功能对于需要求和的列数不多的情况下很方便,但如果1-12月均需要进行求和,就要在第④步中操作12次。所以我想对这一步进行简化。 我们需要用到的函数是Table.Group[ 官方文档 ],先用最简单的形式写出这个函数,看看会抓取到什么结果。 可以看到,Group按照区域名称抓取了每个区域的数据,汇总成一个Table放在我们构建的”数据“列。对于单个Table,我们无需再进行聚合,直接对其每列数字进行相加就可以了。拿”北京“区域为例,先把它转换为列表,再使用List.Sum函数相加。 还要使用Table.FromRows函数把列表转换为一行表格。为了标识列,使用Table.ColumnNames函数获取源表的列名和汇总的结果拼接在一起。 退回步骤一,既然我们有了每个区域的汇总表格,那就可以在步骤一中用步骤二的代码对”数据“列进行替换了。 这时再对步骤一的"数据"列进行扩展即可。 最终表的效果如图

“元宇宙”将深刻影响的六大行业

 近期《黑客帝国:矩阵重启》上映了,全球观众又跟随基努·里维斯回到了Matrix的虚拟世界。抛开机器与人类的对立,Matrix放在今天来说也相当于一个“元宇宙”,就和《头号玩家》里的绿洲一样,只是在Matrix中人类没有选择权。 “元宇宙”是什么这里不再赘述,普遍认为,“元宇宙”将是一个包罗万象、无限逼近真实的虚拟世界,在未来某一天,元宇宙可能取代现实世界,成为人类生活的主要场景。如果这种愿景最终发生,将会对现在的某些行业产生深刻的影响,甚至颠覆。 1.电信产业 前几年4G技术刚刚普及时,“无限流量”成了运营商吸引用户的手段,大家乐观认为,随着基础设施的完善,“5元30M”的时代一去不复返,流量越来越不值钱,最终大家都可以用很低的价格体验”无限流量“。然而事实是,现在三大运营商均已取消”无限流量“套餐,即使进入5G时代,”无限流量“也没有回归的迹象,最多就是”达量限速“。究其原因,有工信部的整治,也有运营商的盈利需要,但本质是,现在已经进入流量世界,无论是看短视频,即时通讯,还是玩游戏,甚至打电话(手机厂商的”畅连“技术),都需要流量,内容的出产速度远远大于基础设施的建设速度。而“元宇宙”基本是现实社会的映射,其数据量更是天量级别,到时除非基础技术发生革命性变革,否则我们将进入一个“流量紧缺”时代,甚至现在的“宽带”都要按量收费了。因为数据传输已经实质上成为基础设施产业,很有可能未来的流量费变成一种“税”,由政府收取。 2.商业地产 2020年突然爆发的新冠疫情让许多公司第一次尝试在家办公,有些公司会发现,在家办公不仅没有影响工作效果,反而节约了租金、水电等成本。在元宇宙里,在家办公的效果更加显著,不是单纯的通过即时通讯派发任务,通过远程系统操作办公软件,而是像在真实世界中一样,能够和老板、同事、合作伙伴面对面交流,就像《星球大战》里绝地武士们通过全息投影开会。企业雇佣员工目的是获取的是员工的idea,如果idea通过线上传播实施,还有什么理由一定要坐在办公室里呢?再者,现在已经有个趋势,就是线上劳动者逐步增多,如视频主播,推广代理等之前被称为“自由职业”的群体,未来对写字楼的需求即使不会下降,也没有增长的理由。商业地产开发可能会萎缩到很小的规模。 3.设计行业 设计如今是个门槛较高的行业,不仅要有天赋,也要花费一定精力学习各种复杂的软件和工具。但是在元宇宙中,可能

VBA学习笔记4:再说数组赋值

之前以为自己把数据的概念搞清楚了,在工作中多次涉及到相关知识点,但还是有一次出了问题。在给数组变量赋值时,发现直接引用单元格的方式行不通了,提示“类型不匹配”。我用了如下语句:  Dim sht() sht = Worksheets(1).Range("B4:B6") 在 《VBA学习笔记2:数组的使用》 中,我曾总结: 直接引用区域也是创建数组的办法,因为range的默认方法是value,所以直接使用arr=range("A1:B6")这样的语句就能为数组赋值了。 但是用 sht = Worksheets(1).Range("B4:B6") 却出现错误。使用Debug检查发现一些问题。 首先用 Debug.Print TypeName(Worksheets(1).Range("B4:B6")) 检查了数据类型,返回“range”,而dim sht()时,sht的默认属性是variant,是否是这个原因造成无法赋值呢? 但是当我把Worksheets(1)去掉时,这个语句居然奏效了,虽然 Debug.Print TypeName(Range("B4:B6")) 出来的还是“range”类型,但sht = Range("B4:B6")的赋值确实是有效的。 那如果我要赋值的数据不是当前工作表呢?试着把Dim sht()去掉,也就是说赋值前不声明数组变量, sht = Worksheets(1).Range("B4:B6") 居然也奏效了, Debug.Print TypeName(Worksheets(1).Range("B4:B6")) 依然返回“range“。 再试一下,如果直接声明Dim sht,或Dim sht as variant,则后面的语句无论加不加worksheets都能生效。 这让人感觉有点混乱,稍微一个不小心就会出错。在微软官方文档并没有找到相关说明。 总结了一下引用区域对数组赋值的两个规则: 1.range对象可以为未声明变量或variant变量(Dim x)赋值,其值会转换为variant类型。 2.声明数组变量后再赋值,只适合当前工作表,不能引用range的父对象。

VBA学习笔记3:Inputbox的函数与方法

 问题来源:一个项目需要使用inputbox来获取用户输入值,如果用户点击了“取消”或者输入了未在备选里的值将自动退出程序。获取输入值我使用了 num = inputbox(optfm) 语句,接下来使用 If num = false Then Exit Sub 语句来退出。测试中却发现点击了“取消”程序并未退出,而是继续执行了后面的语句。搜索后发现,原来inputbox语句分“函数”和“方法”两种,其返回值不同,所以处理方式也不相同。 Inputbox函数,是VBA的内置函数,无论是在Excel、Word,任何使用VB脚本语言的程序都能使用,而Inputbox方法,完整写法是Application.Inputbox,是Excel这个App特有的,其语法与Inputbox函数稍有不同,是为Excel量身定做的。使用中,直接输入Inputbox就是函数的用法,使用“方法”的话就必须在前面加上Application.。 Inputbox函数和方法的主要区别 1.提示文字的限制不同。两个语句第一个参数均是提示文本,但是Inputbox函数的提示文本最大可以是1024个字符,而Inputbox方法最大是255个字符。两者均可在提示文本中使用Chr(10)换行符。 2.Inputbox方法多了一个Type参数,这个参数可以指定返回数据的类型,如果省略则默认返回文本。此参数在Excel很有用,如果使用参数8(range类型),则可以直接用鼠标选择对应的单元格区域。 3.此点最为重要,Inputbox函数如果 用户选择"取消  ",  函数将返回一个零长度字符串 ("");而Inputbox方法用户选择“取消”,会直接 返回  False。 引用微软的官方总结: InputBox  方法与  InputBox  函数的区别在于,前者可以对用户输入进行选择性验证,并能与 Excel 对象、错误值和公式结合使用。 回到开头的问题,可以得出结论,如果使用了 num = inputbox(optfm) 语句,则后面判断用户是否“取消”要使用 if num="" Then Exit Sub ;如果使用 num = Application.Inputbox(optfm) ,后面的判断应使用 if num=False Then Exi

VBA学习笔记2:数组的使用

对于办公人员来说,使用数据最大的好处是,需要写入或计算的数据会在内存里组织好后一次性贴入单元格内,而不是逐个贴入。这样可以大大提升VBA代码的运行速度,特别是针对数据量大的表格,避免了屏幕反复刷新的情况。本文梳理几个常与数组配合使用的知识点。 1.数组的参数 数组的参数指明对数组的引用,类似对单元格区域的引用,如果是二维数组,同样遵循“先行后列”原则。如: range("A1:B6")(3,2)     '引用区域 "A1:B6"中第3行第2列的值 arr(3,2)     '引用二维数组 第3行第2列的值 但是,Range("A1:B6")(3) 是引用区域"A1:B6"中的第3个单元格的值(先行后列),而对二维数组arr使用“arr(3)”语句的话,就会“下标越界”,因为二维数组必须使用两个参数。 2.数组的产生 数组产生的方式有多种,每种方式产生的结果有细微差异。 ①最简单的方式,和工作表中的数组公式类似,即使用{}括住数组的内容,横向数组采用“,”分隔,纵向数组采用“;”分隔。VBA中的数组还要在大括号外面加上一对“[]”。 Sub 数组产生() Dim arr(), arr1() arr = [{"语文";"数学";"英语";"体育"}]     '产生纵向二维数组 arr1 = [{"语文","数学","英语","体育"}]      '产生横向一维数组 Debug.Print arr(2, 1)     ‘结果:数学 Debug.Print arr1(2)      ‘结果:数学 End Sub 上例,arr是纵向数组,因为VBA中只有横向一维数组,没有纵向一维数组,所以arr是二维数组,引用时必须用两个参数。而arr1则是一维数组。 ②以上手工录入数组元素的方式,中括号可以用evaluate函数代替。但是evaluate函数的参数是文本类型,所以要在大括号外加上双引号。为了避免产生歧义,大括号内的双引号要变成”双双引号“。如:arr = evaluate("{&

VBA学习笔记1:错误语句的处理方式

案例分析:现有工作簿A和工作簿B,A中有一些工作表与B中的工作表同名,也有一些工作表B中没有。需要把A和B同名的那些工作表内容从B数值化粘贴到A对应的表里。 此案例看起来比较简单,最重要的一步就是复制粘贴,很容易实现;通过A工作簿里的表名去找B里对应的表也比较容易,把表名设定为变量即可。难点在于A有,B没有的工作表如何处理。 首先设置几个变量,便于后面的引用: Dim Nonesht As String '在B中找不到对应工作表的提示 Dim shtname As String '需要复制粘贴的表名 Dim Desbook As Workbook '目标工作簿A Dim Soubook As Workbook '来源工作簿B 因为工作簿名比较长,所以用Desbook和Soubook来表示。用如下语句开启循环,确保A中的每个工作表都能进入循环中。同时为shtnam赋值当前A中工作表的表名,以便后续复制粘贴时可以引用。 For i = 1 To Desbook.Worksheets.Count Shtname = Desbook.Worksheets(i).Name 如果A中的工作表在B中没有对应,我初步的想法是用goto语句跳转。如何判断shtname是否在B工作簿中呢?我用了is nothing语句 If Soubook.Worksheets(Shtname) Is Nothing Then     GoTo skip Else 但是这个语句会报”下标溢出“的错误,因为如果Shtname不在B里,那表达式Soubook.Worksheets(Shtname)就是错误的。 所以我在前面加上了on error resume next的语句,期望如果报错忽略继续运行,结果依然报错。 这样犯了逻辑上的错误。系统无法判断一个错误的东西是不是不存在!既然Worksheets(Shtname)的语句错误,那后面is nothing根本无从谈起。 改进后如下:      On Error Resume Next      shtnam = Soubook.Worksheets(Shtname).Name         If Err <> 0 Then             Nonesht

投资和投机

很多人以为自己在做投资,实际上他们是在赌博。 提到“投机”,大多数人脑中会浮现出一个精明的,眼中透出贪婪的资本家形象,而说到“投资”,很多人会想到那个成熟稳重,谈笑风生的首富巴菲特先生。由此可见,“投机”一词的地位显然不如“投资”高。从赚钱的角度看,这两者却无分别。 投资,是指购买一项能够持续产生现金流的资产。比如购买一套房产,把它用于出租,每月都能收到固定的租金;投入现金开办一家工厂,生产出市场热销的商品,持续不断的获得利润。投机,可简单理解为低买高卖赚差价。它的实现形式更为广泛,有时甚至只需要投入少量资本就可获得巨大利益。比如外汇买卖、买多卖空。 投资有较高的门槛,想买房,想开厂,一般人都需要贷款才行。最近两年靠支付宝的推广,购买基金产品更加容易,越来越多的人会把基金作为个人资产管理的一部分,也就是投资股票。不过如果在今年二月份买了重仓大家一致认为是“价值投资”的白酒、医药的基金,后面的半年会很痛苦。问题的关键是,你无法知道基金经理究竟是不是在按照你所理解的“投资”在做投资。 投资的另一个门槛在于,如何判断投资标的的真实价值。就像做生意一样,天下没有稳赚不赔的生意,不然为什么会有人加盟了奶茶店,却破产关门呢?对于股票投资来说,我们买卖上市公司的股票,主要靠其公开披露的信息判断其财务、业务的健康与否,靠行业的分析判断其生意的发展前景。这其中需要掌握足够的财务知识,企业管理知识,宏观经济知识,随时了解国家大政方针。投资所能遭受的,只有系统性风险,而不能有赚钱逻辑上的风险。比如瑞幸咖啡,如果在全国开店开了 2000 家,还做不到 1 家店能够盈利,那就是企业经营的逻辑出了问题。如果不能识别这些问题,就远远谈不上是真正的“投资”。 那是否投机会简单些?投机同样门槛很高。门槛之一是对市场心理的判断。投机是一个与市场所有参与者斗智斗勇的过程,今天出了一个国家领导人和美国领导人通话的新闻,成千上万的市场参与者对此有不同的解读和猜想。投机者的判断必须和大多数人的判断一致才有可能从中获利。而大多数人的判断并不会有一个公告牌来统计,即使统计了,他们说的也不一定是真话。 投机的门槛之二是自我心理控制。这是投资和投机都会存在的一个问题。但做投资的时候,可以不管股价的波动,专心持股;对于投机来讲,股价没有朝有利自己的方向发展,其实是在告诉你做错了。这时候是及时止损,还