获取VIP特权
Home > 模板攻略 > Excel教程 > Excel 有哪些酷炫的技能,让你Excel制作如虎添翼

2019-09-10 09:26:26

0已点赞

Excel 有哪些酷炫的技能,让你Excel制作如虎添翼




当然是Power Pivot,让我彻底从VLOOKUP中解放出来,比如下面的数据

构建关系,目前实际使用时会有十几个表,原始数据每天3000+,一个月汇总一个文件。

然后就是第一张图的透视表啦,如果你添加人员或者数据,只需要刷新就行啦

当然一般都是使用Power Query从文件或文件夹导入到查询中,然后建立模型。

每天3000+,每天5个逐日数据表+固定1个组织结构表+1个目标表,每天按日、月汇总,以前想都不敢想,现在每天搞好数据源,刷新下,一分多钟搞定~~

另外PQ可以从很多地方获取数据源,XML、JSON、CSV、文件夹、数据库甚至可以自己书写网络请求去获取数据。

当我抓取了附近5000条短视频数据后...

==============

还有VBA做的各种小玩意,想用啥就写啥

emmmmmm。。。送你们一个批量合并首值单元格的吧,后面两个合并和拆分可以使用其他方式实现~效果如下:

代码如下,使用时注意下最后一个数值:

' 可以多列选择,区域选择(仅选择部分也可以)
' 但应该注意注意一个数值
' 不要选择多个选区,即不要按住Ctrl选择多个区域
Sub 批量合并仅首值单元格()
    Dim rg As Range
    Dim lngStartColumn, lngEndColumn, lngEndRow, lngStartMerge As Long
    Dim r, c As Long
    ' 重新设置选区
    lngStartColumn = Selection.Column
    lngEndColumn = Selection.Columns.Count + lngStartColumn - 1
    lngEndRow = ActiveSheet.UsedRange.Rows.Count
    ' 开始遍历选区
    For c = lngStartColumn To lngEndColumn
        lngStartMerge = 1
        For r = 2 To lngEndRow
            If ActiveSheet.Cells(r, c).Text <> "" Then
                ActiveSheet.Range(Cells(lngStartMerge, c), Cells(r - 1, c)).Merge
                lngStartMerge = r
            End If
        Next
        ActiveSheet.Range(Cells(lngStartMerge, c), Cells(r - 1, c)).Merge
    Next
End Sub

这两个很炫酷~但是第一个更加强大,VBA也很强大在模型关系上不如PP简单粗暴。


8月28日更新

有不少人对PowerQuery【PQ】和PowerPivot【PP】很感兴趣, 我就更新点相关的内容

1 PQ和PP在哪?

答:没记错的2013是自带PP的,没有的话2010和2013可以通过插件解决。2010和2013用的很少,这里就以现在我用的2016为例。PQ在“数据”选项卡中

PP的开启需要先打开“开发工具”,没打开的话可以右键任意选项卡,然后选择“自定义功能区”

然后在“开发工具”选项卡下点击“COM加载项”,勾选PP

勾选完成后即显示PowerPivot选项卡

2 PQ和PP难学吗?

答:入门都很简单,不需要什么编程技巧。比如PQ中的界面

如果这个功能满足不了你,你可以使用“高级编辑器”自己书写过程【上面的功能会自动生成函数过程,初学函数时可以先使用上面的功能操作,然后查看具体的代码】

3 PQ的重点?

答:PQ的操作基于行进行。不同于Excel表格,PQ和PP设计的很像数据库,所以使用记录(Recode)和列表(List)组成表(Table),你可以认为记录代表行,列表代表列。其中

记录(record)类似其他语言的字典:

列表(list)类似其他语言的数组:

注意:对于外围的括号实际上是和我们平时语言的方法是相反的,列表使用{},记录使用[]。

4 PP的重点?


PP的操作基于列进行。所以如果想对行进行计算就需要引入“上下文”,这也是PP的重点:“上下文”。如果你搞定了上下文,基本上往后的学习过程基本上就是时间问题。我们称计算公式所处的环境叫做计算上下文,它又分为行上下文和筛选上下文。

【20190830:修正对上下文的描述】

行上下文:在计算列中,计算列基于列的计算,也就是计算列不能直接计算每行中的数据,只能通过行上下文来实现列中每一行的计算。

筛选上下文:在度量值中,每一个筛选条件都是筛选上下文【数据透视表中的行、列、筛选】。比如:二组每个成员7月中的销售额。

需要注意的是:计算列在模型被初始化时就已经不变了,除非你刷新当前的模型。所以,计算列是最先被计算的。

5 怎么学习PP和PQ?

答:个人感觉PQ并不难,就是各种函数调用可能比较复杂,但是慢慢思考还是很容易的。但是PP的上下文就比较复杂了,需要很谨慎的对待。我是看文档和网上一些文章学习的,对于PP如果外语功底好的可以看下《DAX圣经》,网上有众筹的复印版不到200源。

总的来说,一般用户,只有PP需要慢慢琢磨下,看看文档和其他人的文章就好了。

6 相关的网址:

Power Query:docs.microsoft.com/zh-c

Power Pivot:docs.microsoft.com/zh-c

SQL BI:sqlbi.com/articles/

DAX GUIDE【推荐】:dax.guide/


欢迎关注个人公众号【Excel自学之路

如果帮到您!点个赞吧!

相关模板

3秒登陆,即可下载

注册即同意《我拉网服务使用协议》&《我拉网隐私政策》

QQ

QQ

邮箱

电话

提交

手机绑定

绑定手机号,账户更安全

中国 +86

手机号格式错误

按住滑块,拖拽到最右边
>>

验证码错误

温馨提示:微信绑定手机号,手机端也可以登录,我拉网将对用户隐私信息给予严格保密。

取消