问题:怎样才算精通Excel?

又到一年年终时,各种报表疯狂袭来,小伙伴们的问题也是此起彼伏层出不穷,所以罂粟姐姐使出洪荒之力,结合大家常见的问题总结了Excel的10个核心技巧,希望能够拯救正在数据海洋里挣扎的你们。

1. 数据透视表提示“数据源引用无效”

MOSS 2007中包含了一个Excel
Service,很多朋友都对其相当感兴趣。这里要介绍一下它的组成部分,以及具体它能做什么和不能做什么

回答:EXCEL精通不容易,但是熟练掌握一些技巧还是可以使EXCEL用起来得心应手。下面简单介绍几个高效率的技巧。

规范性的数据输入可以减少后期数据整理的工作量,也可以极大地提高数据统计工作的效率。

数据透视表是产品经理平时做数据分析时,最常用的Excel工具之一了。但是大家有时在建立数据透视表时,会被系统提示“数据源引用无效”。

下面这个地址是官方的blog

快速计算乘方

用EXCEL计秒速计算数字的多次方。比如计算8的10次方,输入公式就可以算出。欧博国际网站 1

1、自定义单元格格式

欧博国际网站 2

快速增加标题行

增加多个标题行反复地复制粘贴是非常低效率的。

步骤:1、选中区域,按打开定位窗口,-;

2、在其中一行首个空值中输入,再按+。欧博国际网站 3

利用Excel单元格的自定义格式我们可以实现一些看起来非常神奇和有用的效果。

这是个很蛋疼的情况,但我教你一个办法可以解决90%以上的问题:关掉当前的Excel文件,然后检查文件名,去掉其中包含“[
]”的那一部分,然后就可以正常使用数据透视表了。

从下图可以看出,它包含前端和后端两个部分,前端的Excel Web
Access是给SharePoint用来作为Web Part的,而Excel Web
Service是对外的程序API,开发人员可以通过它调用操作。后端两个部分,主要是一个Windows服务(计算服务),它是一个计算引擎,也可以提供对自定义函数的调用

快速按字符拆分数据

多个数据集中在一行,逐个换行太费劲,有更快捷的方法。

步骤:将单元格调整至两个字符大小,选中单元格,点击–。欧博国际网站 4

操作步骤:点击单元格-单击右键-设置单元格格式-自定义-输入根据数据规律填入的数据类型(或者使用快捷键Ctrl+1调出设置单元格格式)

2. 对数刻度轴在Excel中的使用

欧博国际网站 5

快速复制文本中的数字

既有文本又有数字的数据,使用选择性粘贴快速复制数字。

步骤:复制整个表格,在空白单元格右键点击–。

欧博国际网站 6

主要规则:“#”,数字占位符,一个#代表一个数字;“@”,文本占位符;“,”,千位分隔符;“.”,小数点;“%”,百分比。

产品经理们平时在分析一些数据时,会发现有些数据的量级是百级的,有些数据是千级的,有些数据是万、甚至10W级的。这些数据都是同一个产品的日常数据,如果强行将它们放到一个表格里,就会变成下面的样子,数组A基本是一条直线,你看不出它的任何趋势了:

关于具体的编程,不在这里详细介绍,有兴趣的朋友可以参考专门的书籍或者帮助文档

快速填充较大的数列

填充数值较多的序列,鼠标拉动填充比较麻烦,直接使用填充功能最快捷。

步骤:在单元格输入首个数字,点击–,输入和,再确定数列就会自动生成。欧博国际网站 7

回答:excel非常强大,功能非常多,所以想精通excel是很困难的,下面分享几个excel技巧,让你更好的操作excel:

单元格设置斜线分割

欧博国际网站 8

分离文字和数字

使用Ctrl+E 就可以从字符串分离出文字和数字。

欧博国际网站 9

.Excel文件的格式转换

将Excel文件的格式转换到其他的格式,其实用迅捷PDF在线转换网站就可以了,轻松将Excel文件格式转换成你想要的任何格式。

欧博国际网站 10

快速输入多个序号

欧博国际网站 11

四舍五入,小数秒变整数

方法:选中区域,按ctrl+shift+1 即可快速把区域内小数变成整数。

欧博国际网站 12

欧博国际网站 13

快速输入☑和☒

在Excel表格中能否输入☑和☒这两个符号呢?其实只要将你单元格的字体设置为Wingdings
2,然后输入R和S就可以有对错符号了。

欧博国际网站 14

任务完成情况的输入

详细操作如图所示

欧博国际网站 15

插入图形

快速的插入你想要插入的图形,比如说正方形原型等等。

欧博国际网站 16

多个单元格的内容输入

选中所有单元格,在编辑栏输入数据,按下Ctrl键的同时,按Enter键。

欧博国际网站 17

回答:谢谢,非常高兴回答这个问题!

Excel是一个非常强大的办公软件,我记得之前有人问过我Excel都能做什么?我的回答有三个:

第一、能用Excel去编写一些游戏;

第二、能用Excel做数据处理、图像可视化、分析数据提炼等工作;

第三、还可以辅助财务部门、人事等多个部门处理很多的数据及处理日常的工作;

欧博国际网站 18

那怎么才算精通excel呢?

我认为最好的回答就是:遇到Excel问题能够迅速的解决掉!因为无论你的函数应用的再好,技巧会的再多,问题解决不了也是无济于事!

本身我每天会在我的头条号中分享办公软件的应用技巧!我的希望也是成为Excel大神级人物;但这个过程还是很漫长的。

举个实战例子吧,让大家掌握比应用小技巧更值得掌握的日常高手必备知识!

场景再现:我朋友再整理发票的时候,只知道总金额是1150.元。但不知道是哪几张发票组成的了,需要从很多的发票列表中找出。

如下图例子所示:

欧博国际网站 19

尽管只有18张发票,但是学过高中数学的同学都知道,如果一单一单去凑数,就是一个排列组合问题,如果运气好的话,可能很快就凑出来了,但如果运气不好呢?更何况这只是例子,如果有成千上万的数字,怎么可能挨个去揍呢?

操作办法:

查找一下自己的excel,“数据”中有没有“规划求解”这项!如图:

欧博国际网站 20

第一步、如果没有显示“规划求解”功能,需要添加:点击“文件”——“选项”——“加载项”找到“规划求解加载项”,鼠标点击转到即可。

欧博国际网站 21

第二步、需要添加一个辅助列。再运算结果处D2输入=SUMPRODUCT(B2:B19,C2:C19)如图:

欧博国际网站 22第三步、点击“数据”——“规划求解”。

设置目标:为D2单元格

目标值:就是总和1150

设置可变单元格为辅助列C2:C15

然后依次添加可变单元格的3个约束条件(其原理就是在辅助列中不断用1和0去测试,在F2中返回结果),如下图所示:

欧博国际网站 23

备注:其中三个约束条件;第一个条件:辅助列<
=1;第二个条件:辅助列=整数(切记这个地方选择int);第三个条件:辅助列》=0。

点击“求解”后出现:

欧博国际网站 24

最后,点击确定;结果就出来了!标准“1”的就是要找的两张发票!
欧博国际网站 25

关注我的头条号,欢迎随时交流!每天分享办公软件应用技巧!

关注我的微信公众号:IT微技能加油站(点击微信添加朋友——公众号——IT微技能加油站),定期分享你想要的职场生存技能及自媒体相关知识!

也欢迎关注:睡前读书十分钟(点击微信添加朋友——公众号——睡前读书十分钟),定期分享如何把控心态及工作生方式的文章;及时了解世界、读懂人生!

回答:我来回答不是本问题吧,我对EXcel还是有所了解的

怎样才算是精通Excel?

老衲多年前遭遇异缘, 获得Excel五轮真经一部, 其中Excel心法共有五层,
每层除了心法之外还有招式. 老衲对Excel虽然所知甚少, 但觉得只要苦练心法,
层层递进, 直达第五层, 应该就算是精通Excel了.

欧博国际网站,(不过根据老衲多年以来面试经验, 一般自称精通Excel的,
大多都是初入江湖的小朋友, 功力一般在一二层之间. 此外,
Excel是数据处理工具, 用Excel打游戏或绘画者, 虽然可能是豪侠巨擘,
但我觉得不应该作为精通Excel这件事的参照系. )

——————-一切有为法分割线———————–

Excel五轮心法:总纲章一. 基础应用/Short Cut章二. 函数/公式章三.
图表/图形章四. 数据透视表章五. VBA / VSTO跋: Excel局限性和小技巧

——————-三法印分割线———————–

总纲: 先贤有言, 三生万物. 可见万物皆数也. 雷蒙三圣雷奥兹云, 数理昭昭,
惟精惟一. Excel心法, 以纷繁之数始, 需去其杂芜糟粕, 截之以齐;
续而密经实纬, 攒之以方; 再而形诸图表, 文饰藻绘, 方可示人. 至于心法高处,
名为VBA, 千变万化, 言语不可尽其妙也.

下图就是雷奥兹(Ray Ozzie), Excel大法开山祖师, 和比尔门,
鲍尔默并称雷蒙三圣.

欧博国际网站 26

翻译一下:Excel操作上, 第一步是对数据进行清洗, 去除不合规格的脏数据,
将数据调整成整齐合理的格式. 然后添适宜的数据辅助列, 补充数据维度.
最后是将处理好的数据以美观的图/表形式向他人展示. 最高级的功能叫做VBA,
VBA的使用非常灵活强大, 不是几句话能说清楚的.

Excel招式繁多, 先给各位施主一个直观印象. 一般止于二级菜单.
重点功能老衲用星号标记:

欧博国际网站 27

——————-四圣谛分割线———————–

章一. 基础操作 / Shortcuts基础操作中的入门法门是数据整理.
这个是最基本的柱础, 腰马合一, 力从地起. 但很不幸的,
大多号称精通Excel的少侠们尚未具备这个意识.

原始数据一般都长成这样.

欧博国际网站 28

这是个糟糕的数据样本, 但是还不是最糟的.
从不同的人手里收集原始数据的时候, 这种情况特别常见.

欧博国际网站 29

好的数据格式是:

欧博国际网站 30

世间任何功夫都是由浅入深, 循序渐进,
数据整理就是其中最基本最重要的入门招式. 不过入门招式,
往往也意味着很辛苦, 别无捷径, 唯手熟心细尔.

数据整理之起式: 清洗吾宗神秀大师有云:
身是菩提树,心如明镜台,时时勤拂拭,莫使有尘埃. 心需拂拭, 同理,
数需清洗. 使其平熨齐整, 利于后续使用.

清洗的对象, 简称脏数据. 一般有如下几种情况: 1. 同名异物:
例如公司里面有两个李明, 如果不加区别地导入数据并进行合并统计,
可能就会出问题. 2. 同物异名: 例如性别, 有的人写成男女, 有的人写成M/F,
有的人干脆写成0/1. 3. 单位错乱: 例如金额, 人民币和美元一旦混同,
那绝对是一场灾难4. 规格不合: 例如身份证号为9527. 5. 格式混乱:
最典型的就是日期! 例如10/6/11, 根本说不清楚是11年10月6日,
还是11年6月10日, 抑或是10年6月11日, 因为美式日期, 英式日期,
中式日期各自都不相同! 老衲生平目睹过的日期惨案足足有12306桩那么多!
另外一种是分位符. 美利坚的分位符是”,” 而欧罗巴诸国的分位符是”.”
如果是一位叫Chateaubriand的美国同事发过来一个数字”123.456″,
根本说不清楚这是一百挂零, 还是十万有余.
老衲生平目睹过的分位符惨案足足有1024桩那么多!

假使少侠天资过人, 心如明镜, 眼疾手快, 刷刷刷刷检出来”123.456″个脏数据,
怎么处理呢?

数据整理之承式: 规制做数据之前, 先要和其他人协商好,
各个数据都是什么格式, 不同数据表之间的格式是否要统一,
之间是否有依赖关系. 如果数据不满足依赖关系如何处理.

例如先约定好, 性别一律写成”男/女”. 如果写成M/F的, 那么M就当成男性,
F就当成女性来处理(使用替换, 或者使用中间映射表). 写成Nan/Ny的,
直接当脏数据抛弃掉.

数据整理之转式: 分组在数据预处理中, 分组是一个很重要的手段,
例如各位少侠要面对的是本公司的工资表, 想看看整体是否失衡,
可以将资历分为中低高三组(日企), 对应人员的工资进行汇总;
但具体资历分层的节点的把握, 则需要小心, 必要时还需要反复尝试.
例如可分成a. 工作1年以下, b. 工作1年-3年c. 工作3年-5年d.
工作5年以上跑出来一看, wow, 公司是大学生创业基金支援的,
全部员工都是工作1年以下……这个时候就得按更细粒度的月来进行划分了.

数据整理之合式: 聚类聚类则更灵活, 例如最早登记报册的只有员工的姓名工号,
乱糟糟一大把,
业余活动组织不起来怎么办?这个时候找IT要一下各人上班的时候的浏览网页,
从网页记录推算一下各人爱好, 然后按照爱好进行聚类, 变成篮球俱乐部,
羽毛球娱乐部, DOTA俱乐部…..这以后的工作就好开展了.

上述四种, 强调意识, 不限于方法.


老衲一生中, 对于Excel最喜欢的功能是表格格式, 不单单是因为美观整齐,
表格格式还集成了筛选, 排序, 甚至冻结窗格的功能,
叫表格格式这么平淡的名字, 若依老衲, 应该起名叫”般若波罗蜜多”格

欧博国际网站 31

在表格格式内别有乾坤:

欧博国际网站 32

然后说说神鬼莫测七招式: 数据工具

欧博国际网站 33

先说说分列. 这种密集大魔王造出的数据, 大家想必都很常见吧.
贴到Excel里面还依然是密密麻麻令人头晕目眩.

欧博国际网站 34

只需将之选中, 使用”分列”式:

欧博国际网站 35

Duang(此处应该有掌声)

欧博国际网站 36

接下来是”速填”式, 说实话, 这是个相对比较无聊的招式. 如果功力达到第二层,
使用公式函数, 取而代之乃是易如反掌.

不过既然至此, 老衲继续用上面的例子:

欧博国际网站 37

从”代码缩略”下面一直选到底, 点击”快速填充” . Duang:

欧博国际网站 38

删除重复项实而不华, 特别推荐. 非常简单, 不赘述. 数据验证意正身正, 实用,
略繁琐, 多人协作时很推荐. 不赘述. 合并计算和”快速填充”有点相似,
如果功力递进到第四层, 使用数据透视表来取代之简直是易如反掌.
且便捷不止十倍. 先不详述.

模拟分析是堪称第一层的屠龙绝技, 看似厉害, 其实用处很小.
而且这个模拟分析和公式/函数关联极大, 留待第二章详述.

关系则是一种高不成低不就的招式. 不能说没用, 但它的功能呢,
其一可以使用辅助列取代, 其二可以使用vlookup取代, 其三可以使用数据库取代.
等说到数据透视表的时候再详述不迟.

中级法门是数据导入, 早期数据清洗什么的, 早在入库前就已经有人替你完成了,
便当之至:

欧博国际网站 39

连个SQL server看看:

欧博国际网站 40

能练到这一级, 少侠, 你功力已然不浅了. 想必SQL这种中等功夫你也有过粗练.
(没练过的看这里: SQL基础教程语句汇总
)至于MDX这种小无相功, 功力也不在Excel五轮心经之下, 但修习全凭机缘,
老衲先不多讲了. (好奇想练练的看这里: MDX的基本语法及概念 )

注意事项:Excel初级法门中有一道奇毒, 名为”合并单元格”,
想老衲数度往生极乐, 多缘于”合并单元格”之手. 作为原始数据,
尽量不要使用”合并单元格”, 这个功能在后续处理数据的时候会带来大量的麻烦.
足以令人呕血三升. “合并单元格”一般是在最后一步,
确定数据不再修改的时候才可以使用.

另外和别的人协作处理数据的时候,
最好将处理好的原始数据和呈现数据一起提交给他人, 方便他人未来进一步修改.

Shortcuts是用来省时间的, 相比来说只能算是小技巧.
最好的参考资料就是微软的官方说明书:Keyboard shortcuts in
Excel至于哪些Shortcuts比较重要, 个人觉得是单元格位置的操作,
能避免在万千数据里频繁地拖动滚动条.

——————-色想受行识分割线———————–

章二. 函数/公式

此二者非常容易混淆, 画张图比较容易说清楚:

欧博国际网站 41

只要在上方公式区输入的, 等号之后的内容, 都属于公式(绿色). 而函数(红色),
则是后面带一对括号的那些内容.

这一节的初级要点是熟悉Excel现有的函数库

欧博国际网站 42

欧博国际平台,其中比较普世的是以下四类:

欧博国际网站 43

数量不多, 经常去 [该网站因为政策法规不予展示] 上搜一下, 就知道用法了.
这个常用函数里面有一个人气堪比AKB48的, 那就是VLOOKUP
(以及他的妹妹HLOOKUP)

VLOOKUP其实就是建立两个表的关联, 将B表的内容, 自动导入到A表:

欧博国际网站 44

以下是用法详解, 实在看不懂老衲的字迹的, 可私信:

欧博国际网站 45

———————–断见取见———————-中级要点是如何将这么多函数做成一个复杂的公式.
复杂的公式, 核心就是函数的嵌套, 函数里面套函数, 招中有招,
直至八八六十四招. 如长江大河, 滔滔不绝. 函数的嵌套最多可以套64层(Excel
2013, 从前Excel 2003-2007为最多套7层). 函数的总字数长度可达恒河沙数
(老衲记不清具体数量的时候就暂时说恒河沙数).

下图这个例子就是一个简单的多层嵌套, 主要是if逻辑上的嵌套.

欧博国际网站 46

不过心有五蕴, 人有三昧, 简言之就是地球人还没有进化成三体星人的形态,
嵌套的数量一多, 就会令人心毒盛起, 头晕目眩, 前列腺紧张,
根本看不清楚自己在写什么.

臣子恨, 何时雪?怎么办? 中间列!

这回老衲举一个实用的例子, 个税计算:

正统的个税计算算法是这样的:

欧博国际网站 47

写成公式是这样的:

欧博国际网站 48

把公式摘出来给各位欣赏一下:

=IF([月工资]-3500<=0,0,IF([月工资]-3500<=1500,([月工资]-3500)*0.03,IF([月工资]-3500<=4500,([月工资]-3500)*0.1-105,IF([月工资]-3500<=9000,([月工资]-3500)*0.2-555,IF([月工资]-3500<=35000,([月工资]-3500)*0.25-1005,IF([月工资]-3500<=55000,([月工资]-3500)*0.3-2755,IF([月工资]-3500<=80000,([月工资]-3500)*0.35-5505,IF([月工资]-3500>80000,([月工资]-3500)*0.45-13505,0))))))))

看到这个公式是不是感到口干舌燥, 头晕目眩, 前列腺紧张?

但如果使用中间列, 将公式拆解, 每个子部分做成一列, 就会立刻神清气爽:

欧博国际网站 49

最后将不需要的列隐藏起来, Mission complete~

然后该说说模拟运算了.

公式可以拖动, 其中参考的单元格在拖动的时候位置也会变动.
下图就是老衲正在拖数据的瞬间:

欧博国际网站 50

一松手:

欧博国际网站 51

这种拖数据, 虽然很简便, 但也有一个问题, 就是只能向着一个方向拖,
或上或下, 或左或右. 假设现在有一个数据要求, 有两个变量,
相当于让你同时向下向右拖动怎么办?

例如不同利率, 不同年限下房贷的问题(这真是一个令人悲伤的例子,
施主请看破红尘吧):

欧博国际网站 52

普通公式也可以做到, 但是就是需要向右拖N次, 或者向下拖M次. 等你拖好,
妹子已经下班, 和别人一起吃麻辣烫去了.
模拟运算则可以一下子把这个6*6的结果全算出来.

操作很繁琐, 接下来的内容请点赞, 给施主增加信心:

先在左上角放一个本息合计公式:

欧博国际网站 53

然后选中所有的可变利率及可变年限:

欧博国际网站 54

然后选择”模拟运算表”

欧博国际网站 55

点击确定之后就可以Duang了:

欧博国际网站 56

唉, 这果然是一个令人伤心的例子.

——————-阿耨多罗三藐三菩提——————–

高级要点是如何自定义一个函数.

刚才的例子, 为了计算日期对应的季度, 使用了一个漫长的公式. 现在看看这个,
一个函数就直接命中靶心, 赏心悦目~

欧博国际网站 57

这个Quarter函数, 少侠的Excel里面是找不到的, 因为这是老衲自创.
它的真实面目是这样的(感谢

@黄老邪

的提醒):

欧博国际网站 58

没错, 这就是第五层心法乾坤大挪移第一级, 也就是VBA.

——————-六道轮回分割线———————–

章三: 图/表各位善男子善女人久等. 老衲今天为各位解说五轮真经的第三层,
又称无上正等正觉图形图表经. 如是我闻:

第一级: 表格

主要入口在这里:

欧博国际网站 59

也可以使用这个:

欧博国际网站 60

表格创建完成后, 点击表格中的内容, 会出现一个新的密法空间:

欧博国际网站 61

这些东西都是干嘛的呢?

欧博国际网站 62

首先是表名称. 子曰:“名不正,则言不顺; 言不顺,则事不成; 事不成,
则饮西北风”. 达尔文在加拉帕戈斯群岛发现的奇行种生物程序猿,
对名称就非常关注, 程序猿对名称的关注主要是认为能方便后续使用. 更直观,
也不容易出错.

以上图出现过的公式距离

=VLOOKUP([对应级数],个税速算表,4,FALSE)

“个税速算表”就是一个表格的名字, 这样的话选择范围就不是一个类似于
=D30:F37 这样很难记忆的字符串, 而变成一个非常容易理解的对象.
而[对应级数]这种列名也一目了然, 如果不加命名, 就得换成=F22:F26,
还要考虑绝对地址和相对地址, 非常麻烦.

起个好名字就成功了一半哦~

切片器: 切片器诞生于2010年. 其实就是一种更美观的筛选.

欧博国际网站 63

那 筛选又是什么:

欧博国际网站 64

筛选一共只有两路18式. 属于最简易的功夫, 但是日常防身非常实用,
希望各位有时间能多多操演, 不过没时间的话, 老板也会逼着你天天操演,
所以这个不必多解释.

此外再说一下表格的另外一个好处: 生成透视表特别方便.

如果是普通数据, 如果想要生成数据表, 必须全部选中:

欧博国际网站 65

但如果是表格的话, 随便选中表中任意一个单元格, 即可开始操作:

欧博国际网站 66

既然事已至此, 老衲顺道说一下条件格式这个惠而不费的功能,
自己使用还是给他人展示都非常美观:

欧博国际网站 67

下面做个集大成的演示(哎呀呀, 老衲狗眼被晃瞎了, 看来只能明日再叙了)

欧博国际网站 68

——————-阿赖耶识分割线———————–

图表经 第二级 图表

欧博国际网站 69

图表主练手少阳三焦经, 内力不深, 招式繁多. 以老衲愚见, 这级偏向华而不实.
不过既然至此, 老衲依旧为诸位善男子善女人逐一解说.

图表共有十式, 常用者六, 不常用者四. 看起来招式并不多对吧?

欧博国际网站 70

但其中每一式下可能有若干变招, 故而常见的总数是: 52式 (自定义式未计入)

欧博国际网站 71

常用图式, 可通过Excel上面的tips来理解 (将鼠标hover在某图式上就可以看到):

欧博国际网站 72

后面不太常用的四式可以稍微详细点说说.

———————–诸行无常分割线————————散点图散点图是展示两变量关系强弱的图形.

老衲举个蒸栗(正例). 一个国家的人均寿命和该国家的人均GDP有没有关系呢?

找了一份2014年的公开数据(不一定完全正确哦)

欧博国际网站 73

就用它作图(注意老衲的手法, 千万注意, 否则图是画不出来的):

欧博国际网站 74

Duang:

欧博国际网站 75

能看出来点规律吗? 似乎不是很容易对吧. 喝! 目下才是真正显示手腕的时刻!

欧博国际网站 76

再看一遍:

欧博国际网站 77

看来钱可通神这四个字果然是有些道理.

刚才的例子也表明, 真正控制图表的, 不在图上, 而是在属性格式里.

欧博国际网站 78欧博国际网站 79

那老衲再举一个例子, 民主指数和人均GDP(购买力平价法)之间的关系:

欧博国际网站 80

咦, 这个相关性就要差一些了. 有很多有钱的一点也不民主,
但民主的基本还算有钱.

散点图在老衲看来只是一个半成品, 它不能显示数据所属的”系列”,
结果就是上面这一大片圆点, 你是无法直观识别每个点是属于谁的.

这个时候就必须配合第三方标签工具来完成: 例如 XY Chart Labeler
(下载地址:The XY Chart Labeler
Add-in)

欧博国际网站 81

顺道再说一句, 如果施主打算自己也写这么个XY Chart Labeler,
功力需要达到第五层心法的第二级, 也就是VSTO. 掌握了VSTO,
Excel世界的大门就算正式打开了, 理论上就没有什么能够阻止施主了.

泡泡图

这是散点图的一种变招, 散点图只能选取两列, 而泡泡图必须选取三列,
第三列就是用来计算泡泡面积的, 继续用上面的数据做例子,
我们不但要看看民主指数和人均GDP的关系, 还得看看这个国家的总体量,
省得被一群小国忽悠:

欧博国际网站 82

这三列全都选中, 然后选择泡泡图:

欧博国际网站 83

结果如何, 各位不妨亲自试试^_^

——————-四无量心分割线——————

雷达图雷达图主要是对两个(多个)对象的多个属性进行综合比较的时候使用.
一般来说越圆, 各项指标就越均衡; 圈的面积越大, 综合实力就越高.

注意事项: 属性值作为行(hang), 对象作为列. 这样默认就能输出正确的雷达图.
(样例数据来自汽车之家, 老衲不是车托)

欧博国际网站 84

好, 出图了:

欧博国际网站 85

如果数据写成了这样:

欧博国际网站 86

那默认出图就会是这样:

欧博国际网站 87

但其实也没啥问题. 这时候需要右键点击图片: 选择数据

欧博国际网站 88

然后切换一下行列就OK了

欧博国际网站 89

选择数据是非常基本而关键的知识点. 万望各位施主对此能提高重视.


曲面图曲面图主要是表现什么呢…….老衲个人理解是………. 这个主要表现
z = f”( f(x), f'(y) ) 这样的计算式 (这完全不是中文好吗?! 老衲也知道啊,
但是就是不知道中文怎么讲啊!!)

曲面图和”模拟运算表” 可谓是天生的一对, 地造的一双. 什么?
你已经忘了什么叫”模拟运算表”? 那其实也没啥关系, 只要你不是科研/教学人员,
那曲面图和模拟运算表在实际工作中, 遭遇到的概率小于万分之一.

不过行文至此, 图还是要上的(背后的数据就是使用模拟运算表得来的).

欧博国际网站 90

是不是很酷炫? 还能3D旋转哦. 但这个图究竟能干啥呢?
让老衲吃包辣条再思考一下…….

——————————————股价图

顾名思义, 就是做出股价分析的图表. 但老衲生平从不炒股(因为没钱),
都不知道这个东西怎么看…..

画一张还是可以的:

欧博国际网站 91

这张图对应的数据是这样的:

最多5列, 分别是交易量, 开盘价, 高点, 低点, 收盘价. 顺序不能随便改哦.

欧博国际网站 92

预祝炒股的各位同学今年好收成.

——————以下是干货分割线—————-关于图表的干货终于要来了!
那就是怎么把图表变漂亮~ 追求美乃是人之天性, 老衲但做浅说.

请看老衲标记的部分, 再加上空白的大背景, 一般而言能控制的便是这些.
想要变漂亮就要从每一个标记点上做道场:

欧博国际网站 93欧博国际网站 94

一旦开始对图表进行操作, 千万不要触碰快速布局和图表样式模板哦, 否则:

欧博国际网站 95

下场请参见: 第二次衝擊

然后就要确定一个所谓美观的范例. 如果不知道猫长什么样,
笔下又怎么可能画出一只猫呢? 不知道漂亮的Excel图表是什么样子,
那即便对所有的操作无比精熟, 又怎么可能做出一张漂亮的Excel图表呢?

(A同学默默地交给老衲一张图: 我觉得这张图很帅, 就要这样的吧)

欧博国际网站 96

……………….

……………….

……………….

欧博国际网站 97

老衲花了半天功夫, 终于找到了一个合适的示例: 电影票房火箭飞升,
虽然立意不高, 但是配色看着还挺喜庆~

欧博国际网站 98

老衲这里没有原始数据, 所以绘图不能100%复刻. 先把已有的数据列出来.

欧博国际网站 99

然后标记上对应的颜色(需要使用第三方取色器, 老衲使用的是QQ截图……)

欧博国际网站 100

来画个八维太极圆环图吧(饼图的变招)

欧博国际网站 101

好像哪里不对, 这和原图没有任何相似之处啊.

切换一下行列(忘了如何切换行列的请往前翻):

欧博国际网站 102

好像有点意思了, 不过这也太丑了. 丑得让老衲又开始思考苦海无边,
回头是岸的道理了…..

但这就是Excel自带模板的真面目……

好, 图表美化经中真正的大招来了: 天罡地火 辅助列!!!

为了弥补Excel自带模板的先天不足, 制图时, 很可能需要添加一些数据,
来实现美观的效果.

原有数据(提前切换了行列)添加辅助列之后变成这样:

欧博国际网站 103

至于辅助列里面的数据怎么来的, 很遗憾地告诉大家, 是老衲随意编造的.
这个其实需要一些尝试和反复修改.

请看大图!

欧博国际网站 104

似乎更丑了…….

吃包辣条冷静了一下, 老衲觉得问题主要是图中每个环都瘦比飞燕,
如果变成杨玉环想必会漂亮得多. 那就来试试吧:

欧博国际网站 105

于是乎:

欧博国际网站 106

眼尖的同学是不是可以看出一点眉目了?

接下来就简单了:

  1. 去掉每个环区的边框, 使其彼此紧凑. 2. 扇区起始角度顺时针移动30度.
    3.将辅助列的部分改成”无填充”

欧博国际网站 107

填上一个底色让大家看清晰一些:

回答:呵呵,这样划分如何?😄😊

欧博国际网站 108

回答:简单的来讲:

1.能用excel制作套用模板制作图表为入门(实际大多数人都只是填充数据)。

2.能应用Excel函数制作数据分析模板,跨文件夹提取数据,生成动态图表为熟练。(函数至少掌握30个以上,才能较为高效的实现)

3.能使用vba编写代码,并掌握数据分析理论,能用Excel实现数据挖掘,为精通。

So,如果不懂统计学、不懂数据库、不懂VBA,顶多就是一个图表制作员,不能称之为精通。

微软MVP,也未必精通Excel,大多数只是数据整理员和制图员。

回答:我使用Excel超过10年,现在是微软认证讲师MCT,MOS2013/2016认证大师。然而要说精通这两个字还是有点心虚的。

欧博国际网站 109


Excel博大精深,光函数就有400多个,估计没有几个人全部懂得,一般能灵活应用其中最常用的70个左右就已经足以覆盖绝大多数的日常工作了。

所以,如果一个人他能利用使用的函数达到100个,那么他算不算精通呢?

下面就按照我的理解,怎样才能算的上精通Excel吧。

  1. 快捷方式

常用的快捷方式要熟练使用(比如Alt+=, Ctrl+Alt +V, F4, F5, Ctrl+Enter
等等等等)。这是效率提升的第一要素。

  1. 函数应用

遇到问题能快速反应利用哪个函数或者函数组合能够解决问题。如果遇到一些平时很少用的函数(比如GetPivotData,PMT,CubeSet等),能通过查找快速学会并正确使用。

  1. 数据透视表

数据透视表是Excel的灵魂,数据分析的利器。包括但不限于:数据分组,统计方式,值显示方式,计算字段,计算项,切片器,日程表。

  1. 图表

不一定要多炫酷,但是一定要懂得每一类图表的应用场景,优劣势。最终呈现的图表规范,符合逻辑,画面配色清爽。

五. 数据清洗

在2013及之前的版本中,叫“查询”,功能相对比较弱。在2016版本中,PowerQuery已经被添加到Excel中了(可以理解为“超级查询”吧)。PowerQuery专门用于数据查询和数据清洗,支持市面上绝大多数数据库,不受Excel100万行的限制,功能非常强大。像多工作簿合并,合并查询,追加查询,格式转换等等都非常强悍。不难学,但是能做到很多函数做不到的事情。

  1. 数据建模

在2013和2016版本中都有这一加载项–PowerPivot。主要针对多表,通过PowerPivot能建立更复杂的数据模型,从而进行更复杂的数据分析。入门不难,熟练使用需要大量的练习。

  1. VBA

不一定要应用VBA来建立复杂的自动化系统,但是起码要懂得应用VBA来对工作簿,工作表,单元格进行操作。因为有些时候面对一些问题,使用VBA要比使用函数来得更高效。


以上,就是我对“精通Excel”的理解。

想学习Excel,没有捷径可走,只能踏踏实实地,系统地学,再加上大量的实战应用,方能熟练掌握。

如果有小伙伴对此有任何想法和疑问,也欢迎回复讨论。

回答:人们常说精通Excel前提是熟练应用VBA编程语言。我觉得VBA只是Excel的一个拐棍,常拄拐容易造成,咱们连日常走路也变得困难起来。本人就是一个例子,对于公司的一个生产大表,总觉得Excel自带功能不好用,所以自己给后台写了很多限制规则及自动统计代码,最后结果造成表格功能扩展行较差,维护困难,容易出现录制上的问题。一个同事对该表格进行了重新制作,使用简单的条件格式套用就解决了问题,虽然数据安全性降低,不过足以满足日常需要。这次VBA的使用相较与Excel内置功能,有些画蛇添足。

记得刚接触Excel,感觉这东西很简单,会快速录入即为大神~后来接触到一些微软MVP大神讲解的公开课,才逐步了解到Excel数组函数,数据透视,SQL语言的妙用,觉得熟练应用这些才是精通Excel。而不久自己又被VBA语言所吸引,在Excel社区接触到很多Excel
VBA爱好者。自己通过学习确实得到很大提升。不过随着了解深入,我发现VBA只是Excel一种解决方案,会用VBA跟会用Excel函数没有太大区别。而这时自己意识到,Excel不过是一种数据处理分析工具,真正的神往往是用他来处理数据,处理完了自动存入数据库(如Access),或是将Excel作为一个数据接口链接到各种操作软件中。更有甚者使用Excel做日常办公的规划软件,到时间了打开Excel会自动汇总数据并做出工作规划。这些不止需要代码也需要对Excel的充分了解。

回过头来,感觉能至少熟练掌握50个左右的Excel内置函数即可成神(因为这才是下了功夫的人),常说一通则全通,没有一定的积累是做不到的。日常我们使用的Excel功能也就局限于数据透视、做做图表、整理整理数据。不过能熟练操作的也是少之又少。有些人自我封神,我很崇拜做财务的,因为他们天天跟数据打交道,Excel水平非常高~其中也有滥竽充数的,我接触到一个案例:将财务数据做到40多兆,表格逻辑乱七八糟~里面处处陷进,多重公式嵌套,对于他来说也许这就是Excel的顶峰了~让人很无奈……感觉Excel的精髓是简单点,让自己能懵逼的表格不是好表格~

说说自己对Excel的理解,自己比较推崇“三表”概念,制表也是按照这个套路来(思路简单,可以套用别人较为成熟的逻辑),对于庞大的数据体,自己会采用Access解决方案,哪怕费点事,写几句SQL~图表制作是一门学问,也是一门艺术,自己有自知之明,没打算在上面深造……数据透视现在自己很少用了,使用他比较占用内存,费点事订制一些数据统计的程序吧。

不过说起来你能在公司工作环境中成神,也说明贵公司不重视“基建工作”。成熟的办公体系会讲数据一步到位,应该体现团队,而不是个人Excel水平。现在工作环境,Excel使用就是傻瓜式的,点一下程序就会完成数据处理工作。这种环境下成神的概率就很低了。现在有的人也是用Csharp进行Excel开发的,说起来这些人才算是神吧,相交于VBA的小打小闹,这些规则制定者才是神。接触过基于第三方类库的编程,感觉这些不太属于Excel的范畴了,强行做一些Excel和其他软件的接口,使得Excel显得有点小家子气了~还是原来那个干干净净的Excel比较好。

当然笔者针对的是office的Excel软件,如果您是在wps成神的,我只能是更加崇拜了,本人不喜欢用wps,原因也许是它的功能太多了,不太干净了,简简单单的白纸才能任由自己书写。

想要附些图,手机操作很麻烦~不知道怎么用电脑弄,希望大神们指导一下,也可以一块探讨一下。会附图了我想贴点VBA“速成秘籍”,可能对于成手来说有些东西不够简单,不过对于新手可是能马上上手的东西,很有用的东西,希望有一天能跟大家分享~😀


纯手机手打,希望看到人能赞一下,关注一下就更好了,以后可能有Excel的福利哟~🙂🙃

回答:1、基础操作熟练,熟悉大多数快捷键,技巧运用熟练

2、图表制作熟练,能制作动态美观的图表

3、可随心所欲地使用数据透视表

4、精通常用的50个函数,对数组公式颇有研究

5、VBA实战经验丰富,思路开阔,可轻易做几个小游戏或数据库管理系统

案例1:将数字以千分位格式输入

欧博国际网站 110

下面这篇文章可能比较有帮助

欧博国际网站 111千分位格式输入

那么如何在一个表格中看清楚它们的增减趋势呢?使用“对数刻度”。第1步,选中坐标轴,右键选择“设置坐标轴格式”:

案例2:输入员工的工号

欧博国际网站 112

重点列举一下,Excel Service能做什么以及不能做什么

欧博国际网站 113输入工号

欧博国际网站 114

支持

函数
除少数例外,几乎支持所有 Excel
工作表函数。有关详细信息,请参阅下一节支持的和不支持的工作表函数。

日期
Windows 和 Macintosh 日期系统。

Excel 表格
Excel 表格数据、列标题、计算列、总行数、结构化引用和样式。

单元格
单元格值,包括合并单元格和单元格内容溢出。

名称
定义的名称和命名区域。

计算
计算和重新计算设置,包括自动、除表外自动、手动以及针对区域或整个工作表的迭代计算设置。有关详细信息,请参阅
Excel Web Access 帮助中的在 Excel Services 中计算和重新计算数据。

图表
图表、图表范围和数据透视图报表。有关详细信息,请参阅 Excel Web Access
帮助中的在 Excel Services 中使用图表和数据透视图报表。

格式
单元格和单元格区域格式、工作簿中的条件格式(除使用数据条和图标以外)和数字格式。

连接
与外部数据源(包括 OLAP 数据透视表)的连接。

假设分析
假设分析工具的结果,包括单变量求解、数据表、方案、规划求解和系列。

合并
区域中合并的数据。

数据源
SQL Server 2000、Microsoft SQL Server 2005、OLEDB 提供程序和 ODBC
驱动程序。

 

案例3:地名或者公司名称全称的快速输入

第2步,选择“对数刻度”,然后就能解决数据组间量级差别太大看不出趋势的问题了:

不支持

功能
注释VBA
Visual Basic for Applications (VBA) 代码、宏、加载项和用户定义的函数
(UDF)。

注释    程序员可以按许多方式自定义 Excel
Services,包括创建用户定义的函数 (UDF)。有关详细信息,请参阅 Microsoft
Office SharePoint Server 2007 软件开发工具包 (SDK)。

旧版宏语言
Microsoft Excel 4.0 宏函数和 Microsoft 5.0 对话框工作表。

控件
窗体工具栏控件、工具箱控件和所有 ActiveX 控件。

XML
XML 映射和嵌入式智能标记。

安全和隐私
受保护的工作簿、工作表或区域,以及具有信息权限管理 (IRM) 的工作簿。

注释   要在 Excel Services 中保护工作簿,请使用 Microsoft Windows
SharePoint Services 权利和权限。

图像和对象
链接的或嵌入的对象或图像、插入的图片、自选图形、艺术字和关系图(如组织结构图)。

墨迹
所有墨迹功能,其中包括绘图、书写和批注。

OLE 和 DDE
对象链接和嵌入 (OLE) 对象以及动态数据交换 (DDE) 链接。

显示的公式
工作簿与显示的公式一起保存。

数据验证
防止输入无效数据以及创建下拉列表。

数据源
用于 Microsoft Business Solutions 的数据检索服务、Windows SharePoint
Services 列表、Microsoft SQL
Server、外部数据区域(也称为查询表)以及链接到 Windows SharePoint
Services 列表的表。

查询
Web 查询和文本查询。

对链接工作簿的外部引用
创建对特定单元格区域的、对特定单元格区域的已定义名称的或作为名称定义一部分的外部引用(也称为链接)。

注释
注释的显示和调整。

合并
数据透视表中合并的数据。

共享工作簿
共享工作簿和解决冲突更改。

数字签名
工作簿中可见的和不可见的数字签名。

附加工具栏
在将工作簿转换为 Excel 2007 之前通过使用 Office Excel 2003
附加到工作簿的自定义工具栏。

 

更多信息,可以参考下面的说明

欧博国际网站 115输入名称全称

欧博国际网站 116

案例4:做多条件判断,90分以上为优秀,60分以上及格,60分以下不及格([>=90]优秀;[>=60]及格;不及格)

3. 批量用分号连接一列

欧博国际网站 117多条件判断

日常工作中,有时你会遇到要批量输入一批号码,号码之间需要用分号“;”隔开。如果号码少还好,我们可以用笨办法一个一个手工输入,但是如果号码有几百、上千个,那一天你都在输入号码,没法腾出时间做更重要的事情了。我来教你一个简单的方法可以将一批号码批量用分号连接。举个例子,将下图中B列里的数字批量用分号连接:

2、数据有效性

欧博国际网站 118

数据有效性是对单元格或单元格区域输入的数据从内容到数量上的限制,这样就可以依靠系统检查数据是否正确有效。

在E2中输入等号“=”,然后选择要分号连接的区域,如这里的B2:B16,这个区域可以是多行多列:

案例1:设置一级下拉列表

欧博国际网站 119

操作步骤:“数据-数据验证-设置-允许-序列-选择数据来源(也可手动输入数据,用逗号分隔)

输入好公式以后,选中该公式,按下键盘上的F9键,我们会看到如下图的结果,数据已经合并到一个单元格了,并且用分号“;”连接好了,删除数据首部和尾部不需要的字符,首部的“={”(没有引号)和尾部的“}”(没有引号)即可:

欧博国际网站 120设置一级下拉列表

欧博国际网站 121

案例2:设置二级下拉列表

4. 找出两列中不同的项

操作步骤:设置一级下拉列表-定义名称-设置二级下拉列表(=INDIRECT

有时我们需要将两列数据中重复的项找出来,将它们删掉或者做其他处理。第1步,建立辅助列:

欧博国际网站 122设置一级下拉列表及自定义名称欧博国际网站 123设置二级下拉列表

欧博国际网站 124

注意:因为B2单元格最开始为空,所以提示“源当前包含错误”,直接点击确定即可。

第2步,在辅助列的单元格里面输入函数:=IF(ISERROR(VLOOKUP(C2,A$2:A$23,1,0)),”不重复”,”重复”),意思是查找C2单元格在A2到A23中,是否存在。存在为0,用“重复”表示;不存在为1,用“不重复”表示。

将数据整理为规范的数据不仅可以使图表更加整齐美观,符合公司或者个人常用习惯,也能够极大地方便后期的数据统计分析。

欧博国际网站 125

3、分列

再对辅助列做个筛选即可选择你想要的数据啦。

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注