您的位置:澳门新葡8455最新网站 > 编程教学 > excel有何样高等技艺,也足以玩

excel有何样高等技艺,也足以玩

发布时间:2019-10-06 15:45编辑:编程教学浏览(193)

    Excel 也得以玩 RESTExcel 也足以玩 REST Excel 也足以玩 REST

    问题:excel有啥样高等工夫?

    乘胜 RESTFul API 的稳步普遍,如若能在 Excel 中平素调用那几个API,将会给大家管理和剖判数据拉动点不清好处。大家先来看叁个在线的 OData Service数据: Chrome 浏览器中张开,展现结果如下图:

    回答:

    图片 1

    Excel有哪些高等本事?小编也来讲说自家的意见,纯属个人见解。

    这么些 url 以 OData 标准提供的 Rest 服务,json 数据能平昔将数据导入到 Excel 中吗?答案是自然的。Excel 提供了二种形式:方法一是在数据页面中的赢得外界数据

    所谓手艺,正是手艺,自身并不曾高端/低等之分。只要能帮大家化解难题,那就必然是实用的技巧。

    图片 2

    Excel中,每种成效点,皆有关照的职业必要和现象,所以,各种功用点都有用,都非常高端。如,vlookup和index+match,都能查询到数值,但,说不出那么些高端,哪个低等。

    措施二是经过 Power Query 工具。Power Query 在 Excel 2015中一度是松手的。在此以前的本子须求在微软官方网址下载、安装还要加载 (作为 COM 加载项)。下图展现了 Power Query 怎么着将数据加载到 Excel 中:

    这里,笔者就介绍多少个自己以为大概被许多少人低估的工夫呢。

    图片 3

    透视表

    成都百货上千人总括深入分析数据还在用五花八门复杂的公式,进而低估了透视表的强硬效用。

    透视表,简轻便单多少个操作,基本上就能够化解平日十分八的表格。

    图片 4

    多张职业表,那也不值得一提

    非但集聚,还能够拆分数据

    图片 5

    画个图表,那也是不言自明

    图片 6

    那样的透视表,你低估了她的效劳了呢?

    • 多少分列

    相信广大表亲都遭到过格式的麻烦。日期格式不统一?数字和文书混合,Excel傻傻分不清?拆分单元格内容?其实,分列轻轻巧松就化解了。

    图片 7

    • 列表格式

    图片 8

    都想着如何总计解析,其实,你是否想到过,总结分析的困难度,十分大是决议于数据源的格式。而,平日习于旧贯中,大家屡屡会在数据源中加多一多元看似很酷的效果与利益,如,合併单元格,多维数据等。殊不知,那些都强化了总结分析的难度。

    为此,提出大家对源数据的保存,都用列表的情势。

    此间仅作引玉之砖,应接咱们谈谈。

    本篇不对 Power Query 导入 OData 的细节举行表达。首要打算缓和几个不以为奇问题:如何将 Rest Web API 的数目导入到 Excel,越发是 json 格式的数码。从本身的问询的话,貌似 Excel 并从未提供 json 格式数据的一直导入,OData 数据才足以!

    越来越多实用小本领,记得关怀自个儿哦!

    回答:

    不用一说Excel高端手艺就说VBA,VBA的确很好,但是,要是一项手艺不能够为绝大多数普通客户使用,即便高端,也不应当被誉为“最高”。

    相比较来说,小编越来越推荐Excel2015的新职能Power Query、Power Pivot等Power种类(Excel2009或Excel二零一一可到微软官方下载相应的插件)——这几项本领才是真“高端”,不独有功用特别无敌,而且,Excel新手也能自在精晓!

    以下从利用评价、成效概要、应用案例、学习曲线相比、学习材质等四个地点拓宽认证。

    本来,要是只是为了把网络上 Rest API 的数目放到 Excel 中,能够挑选过多别的工具,比方笔者心爱的 Python。Python + pandas 能够如此来做:

    首先,看一下用过的人对那多少个新职能的商酌

    # encoding: utf8import requestsimport pandas as pd url = 'https://services.odata.org/V3/OData/OData.svc/Products?$format=json'products = requests.get.json()['value']df = pd.DataFrame# re-order columnsdf = df[['ID','Name','Description','ReleaseDate','DiscontinuedDate','Rating','Price']]writer = pd.ExcelWriter('products.xlsx')df.to_excel(writer, 'Sheet1')writer.save()
    

    1、逆天给力的Power Query

    图片 9

    输出结果如下,尽管不是很奇妙,但一定简单:

    2、被誉为引爆Excel数据分析、20年来最大改善的Power Pivot

    图片 10

    图片 11

    3、可视化地图只需3步的神器Power Map

    图片 12

    设若不依赖外界工具,Excel 本人能实现吗?答案也是自然的。以下正是 HOW 了。

    4、破土而出的逆天神器Power BI

    图片 13

    1、Alt + F11 进入 VBE 界面,添加对 Microsoft WinHTTP Service 5.1 的引用。

    其次、Power连串新职能究竟是哪些?

    Power种类分别承担数据深入分析全经过从数额联网整理→总计分析→图表展现等的例外首要专门的职业,相应成效主题如下所示:

    • Power Query正是用来做多少查询、转换的,还记得Excel里的数量导入吗?你能够明白为它的晋级版;

    • Power Pivot是用来做多少建立模型和剖判的,还记得Excel里的数据透视吗?你能够领悟为它的拔尖版;

    • Power View是用来做多少呈现的,还记得Excel里的图纸吗?你能够精通为它的整合版;

    • Power Map是特别用来做多少的地图化浮现的,那是Excel里原本未有的。

    • Power BI可以驾驭为微软将那四块功能做了个大整合,然后推出的一个单身的工具。

    图片 14

    图片 15

    其三,举2个应用例子

    2、编写测验代码,测量检验数据得到:

    1、批量汇总Excel多工作簿多职业表数据只需4步

    step01-从文件夹获取数据(假如数量仅在1个职业簿内,选拔“从专门的学问簿”)

    图片 16

    图片 17

    step02-用Excel.Workbook函数分析职业簿多表新闻(如若全部表都在1个工作簿里,此步骤能够简单)

    图片 18

    step03-展开具备工作簿的有所职业表新闻

    图片 19

    step04-打开具备专门的学业表的数码明细(假设指望只导入在那之中有的表,能够在此步骤举行筛选)

    图片 20

    经过上述轻易的4个步骤,全部职业簿的具有专门的学问表数据将聚齐到一块,如下图所示:

    图片 21

    假若还亟需对在那之中的数量进行其余的操作,那么能够持续在Power Query里成功,那样,现在在新的数码出现时,能够一贯在结果表里一键刷新,就象从前供给用VBA导入时点个按键同样方便,完全不须要再另行操作!

    Public Sub test_get_rest_data() Dim req As WinHttp.WinHttpRequest Set req = CreateObject("WinHttp.WinHttpRequest.5.1") req.SetTimeouts 60000, 60000, 60000, 60000 Dim url As String url = "https://services.odata.org/V3/(S(rsgp3gk40ractowux3driji4))/OData/OData.svc/Products?$format=json" req.Open "GET", url, False req.Send Dim resText As String resText = req.ResponseText Debug.Print resTextEnd Sub
    

    2、多表数据联合剖析,用Power Pivot建立模型只需拉根线

    以订单表、订单明细表以及产品主数据三表关联剖析为例。

    图片 22

    Step01-依次将数据增进到数据模型

    图片 23

    加载实现后,Power Pivot中数量如下:

    图片 24

    Step02-切换来事关图视图

    点击“关系视图”,看见3个表的剧情分别展现在3个不等的规模里,用鼠标按住那些规模的最上部名称区域就足以按要求拖放到差异职位。

    图片 25

    Step03-创设表间关系

    订单表、订单明细表、产品表之间的关系是:订单表里的种种订单对应订单明细表里五个订单(产品)项目,订单明细里的出品得以从成品表里获取更详尽的相干消息。

    图片 26

    结果如下:

    图片 27

    按同样的艺术还足以创设订单明细表和制品表之间的涉及,最终结果如下:

    图片 28

    那样,3个表之间的涉及就建好了,后续就足以一贯从各样表里拖拽须要的音讯实行数量透视等解析,如下所示:

    图片 29

    图片 30

    图片 31

    比如说,要深入分析各个产品类别的销量:

    图片 32

    WinHTTPReq 对象我们自行参谋微软的提携:

    第四,相比较一下Excel函数、VBA以及Power Query的就学曲线

    以Power Query为例相比一下Power连串功用的就学曲线:

    图片 33

    能够看到,Power连串作用可以在相当长的时光内领会其大多数的利用要点,并学有所用——应用到实在工作中去解决难题,那么些过程比学习Excel函数还短,收效更加大。

    Public Function doGet(url As String) As String Dim req As WinHttp.WinHttpRequest Set req = CreateObject("WinHttp.WinHttpRequest.5.1") req.SetTimeouts 60000, 60000, 60000, 60000 req.Open "GET", url, False req.Send doGet = req.ResponseTextEnd FunctionPublic Sub test_doGet() Dim url As String url = "https://services.odata.org/V3/OData/OData.svc/Products?$format=json" Dim resText As String resText = doGet Debug.Print resTextEnd Sub
    

    第五,越来越多的选择参谋案例

    Excel神技巧Power连串的应用远不仅于地点四个简单例子,还应该有一种类原本Excel里必需透过特别复杂难懂的神长公式、VBA代码恐怕SQL才具化解乃至难以解决的难点,通过Power体系作用能够相当的轻便的消除掉。

    接下去还要化解多少个难点:

    1、如Power Query实战案例

    图片 34

    • json 格式解析
    • 数码导入到工作表

    2、如Power Pivot应用案例

    图片 35


    图片 36


    VBA 分析 json 作者在 Github 上找到了三个缓和方案: VBA 二个特别模块中,模块命名称为 JsonConverter。这几个模块的 parseJson() 方法将获得的 json 字符串调换到 Dictionary。注意 Dictionary 并非 VBA 内置对象,须要加上对 Microsoft Scripting Runtime 的引用。

    本人是大海,微软认证Excel专家,集团签订Power BI顾问

    以下是 parseJson() 的测量检验代码:

    让大家联合读书,共同升高!

    回答:

    作者个人在数额方面专门的工作也某些年了,对excel来讲,用的照旧比很多,尽管有一些时候,会用另外的解析工具,举例:python、凯雷德等。不过日常工作的大大多都以excel。excel的高端本事在此间就谈一下民用的有个别感触。

    Public Sub parseJson() Dim jsonData As String jsonData = doGet("https://services.odata.org/V3/OData/OData.svc/Products?$format=json") Dim parsedDict As Dictionary Set parsedDict = JsonConvertor.parseJson Dim val As Dictionary For Each val In parsedDict Debug.Print val, val, val("Description") NextEnd Sub
    

    第一、高等筛选,我感到能够算得上多少个尖端技能,突破常规的单列筛选的限制,可筛选多列,每列设置一定的标准。

    高档筛选,在excel的法力栏里未有筛选那么泾渭显著,能够看得出来,那个效应自身使用的不是那么频仍。不过真正仍旧挺不错的,其实在平凡的干活中,相信部分的人也和自家一样,喜欢用筛选这几个职能,实际不是尖端筛选那个成效,每列单独筛选。

    图片 37

    数据导入到 Excel 职业表:

    第二、正是大家所说的VBA功用,VBA可以使得大家依照我们自个儿的逻辑对石英钟格实行管理。

    行事中笔者会蒙受拆分石英表格的事,常常会用到VBA去管理,今年VBA的做点就出去了,固然大家得以经过有个别别样的主意去做完成拆分的靶子,但VBA在您三遍逻辑达成以往,能够完毕后续的拆分变得特别的快,运转一下,等一会结出就出去了。上学的时候老师还须求用VBA做多少个粗略的种类,当然VBA的读书有自然的难度。

    图片 38

    如上是自家列举的八个在专门的学问中感觉相对来讲是比较高端的本领,当然excel是极其强劲,能够说九成之上的数量分析工具excel都得以胜任。款待在口不择言中发布不一致的观念。

    回答:

    图片 39

    教程:

    回答:

    EXCEL的功用首要显示在3个方面:一是分界面中的种种操作,二是各个函数的效益,三是通过VBA完成复杂的特性要求。

    从EXCEL的数量管理功能上来讲,比方对于10万行的数码进行拍卖,VBA的管理速度最慢,各个内置函数的管理速度次之,EXCEL的各样常规管理操作则最快(如筛选)。但从功效达成上来讲,VBA能促成的效劳最多,种种内置函数14次之,各界面包车型地铁效劳操作开关则不得不促成那么些常用操作。

    使用EXCEL,重要基于自个儿的必要来读书,一些非常少用到的东西,知道就行了,需求用时再去查看协助也来得及。各界面包车型大巴功效开关功用,是必供给明白和垄断(monopoly)的,即便不纯熟一些开关命令的用法,也要清楚这多少个按键有哪些遵守,以备要求用时去询问扶助。常用的函数是必须求调控的,至于VBA,一般人相当少用得上,能垄断(monopoly)最佳,驾驭不了也没怎么不满。

    回答:

    自个儿能说有人用Excel单元格作为像素点,然后使用Excel作画吗~感到那是最原始的功用,也是最高大上的作用……

    自个儿不是Excel的死忠粉,所以直接没研商过这一个是怎么完结的~很敬佩作画的那些人。前日见到有人用Excel生成二维码和条形码的录制,这么些须求加载控件,然而那一个实在是个好效率,没有须要大家再在网页上追寻有关职能插件了,何况还是能当公式的批量转变。

    实际数据透视也算Excel比较高档点的职能吗,终归平常工作精通使用数据透视的仍然少数,需求多少总计专门的工作的小童鞋,可以研商一下~专门的学问中常使用的图形模版,报表格式模版,这一个模版的定做也是必得的技艺。

    自然作者还想提的是Excel的VBA编制程序,大概会点编制程序让咱们看起来更了不起上~

    Public Sub writeToSheet() Dim jsonText As String jsonText = doGet("https://services.odata.org/V3/OData/OData.svc/Products?$format=json") Dim parsedDict As Dictionary Set parsedDict = JsonConvertor.parseJson Dim Values As Variant ReDim Values(parsedDict.Count, 6) Dim headerDict As Dictionary Set headerDict = parsedDict.Item Dim k As Variant Dim i As Long For Each k In headerDict.Keys Sheet1.Range.Offset.Value = k i = i + 1 Next Dim valueDict As Dictionary Dim vk As Variant i = 0 For Each valueDict In parsedDict Values = valueDict Values = valueDict Values = valueDict("Description") Values = valueDict("ReleaseDate") Values = valueDict("DiscontinuedDate") Values = valueDict Values = valueDict i = i + 1 Next Sheet1.Range(Cells, Cells(parsedDict.Count + 1, 7)) = ValuesEnd Sub
    

    运维后分界面如下:

    图片 40

    雄起雌伏将持续教师 WinHttp 怎么样实施 POST, PUT 和 DELETE 央浼,进而完成 CRUD 操作,也将介绍 WinHttp 管理 cookie 的点子。

    本文由澳门新葡8455最新网站发布于编程教学,转载请注明出处:excel有何样高等技艺,也足以玩

    关键词:

上一篇:没有了

下一篇:机器学习,数据清洗