加载中…怎样才算精通 Excel？（下）
(2015-08-17 16:07:38)
标签：
育儿 |
怎样才算精通 Excel？（下）
文/靳伟（知乎）
本文系作者授权“清南”发布，如要转载请与作者本人联系。
-------------------四无量心分割线------------------
雷达图
雷达图主要是对两个(多个)对象的多个属性进行综合比较的时候使用. 一般来说越圆, 各项指标就越均衡; 圈的面积越大, 综合实力就越高.
注意事项: 属性值作为行(hang), 对象作为列. 这样默认就能输出正确的雷达图. (样例数据来自汽车之家, 老衲不是车托)
http://pic2.zhimg.com/5cbec5f15ea523510cb4b649cc19b4c9_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/412f3b277dc5ea5b97046c1e9e206b86_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/53abc6f2e0f007fddc2b4df9e50deb37_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic1.zhimg.com/a8252485a73c76522a704fb23ce2c464_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/bc645328165fa5bc83bdda029b405cd6_b.jpgExcel？（下）" TITLE="怎样才算精通
选择数据是非常基本而关键的知识点.
万望各位施主对此能提高重视.
------------------------------------------
曲面图
曲面图主要是表现什么呢.......老衲个人理解是.......... 这个主要表现 z = f''( f(x), f'(y) ) 这样的计算式 (这完全不是中文好吗?! 老衲也知道啊, 但是就是不知道中文怎么讲啊!!)
曲面图和"模拟运算表" 可谓是天生的一对, 地造的一双. 什么? 你已经忘了什么叫"模拟运算表"? 那其实也没啥关系, 只要你不是科研/教学人员, 那曲面图和模拟运算表在实际工作中, 遭遇到的概率小于万分之一。
不过行文至此, 图还是要上的(背后的数据就是使用模拟运算表得来的)。
http://pic4.zhimg.com/75b7cc270d5a37fcefbaf80bd3292583_b.jpgExcel？（下）" TITLE="怎样才算精通
是不是很酷炫? 还能3D旋转哦. 但这个图究竟能干啥呢? 让老衲吃包辣条再思考一下.......
------------------------------------------
股价图
顾名思义, 就是做出股价分析的图表. 但老衲生平从不炒股(因为没钱), 都不知道这个东西怎么看.....
画一张还是可以的:
http://pic2.zhimg.com/b726a5454af1f0c0d9ccaed8c5a47791_b.jpgExcel？（下）" TITLE="怎样才算精通
最多5列,
分别是交易量, 开盘价, 高点, 低点, 收盘价.
顺序不能随便改哦.
http://pic4.zhimg.com/db57e48d73e7a905afdb00ad6400ce63_b.jpgExcel？（下）" TITLE="怎样才算精通
------------------以下是干货分割线----------------
关于图表的干货终于要来了!
那就是怎么把图表变漂亮~
追求美乃是人之天性, 老衲但做浅说.
请看老衲标记的部分, 再加上空白的大背景,
一般而言能控制的便是这些.
想要变漂亮就要从每一个标记点上做道场:
http://pic4.zhimg.com/17859cc9aac9c1923c78fcf02ac6f053_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/37a771e24b8be0da204d5b52e5a4c0a5_b.jpgExcel？（下）" TITLE="怎样才算精通
下场请参见: 第二次衝擊
然后就要确定一个所谓美观的范例. 如果不知道猫长什么样, 笔下又怎么可能画出一只猫呢? 不知道漂亮的Excel图表是什么样子, 那即便对所有的操作无比精熟, 又怎么可能做出一张漂亮的Excel图表呢?
(A同学默默地交给老衲一张图: 我觉得这张图很帅, 就要这样的吧)
http://pic4.zhimg.com/52ca623d886c2f1b52a6e236801569a7_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/7c998a6c4a4315b129ccb182cdde0f5b_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/25d379ae6108c6233efff3d93cccd7ae_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/42dbaf9c2155ba0d10b8f3a7bba8c20b_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/b91ae8c1c0e8a153d7725f5f894c3ddb_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/195d4875b360f82588e2078dba70d892_b.jpgExcel？（下）" TITLE="怎样才算精通
切换一下行列(忘了如何切换行列的请往前翻):
http://pic1.zhimg.com/8ef86efd83838a361ddb5277d383b2d0_b.jpgExcel？（下）" TITLE="怎样才算精通
好像有点意思了, 不过这也太丑了. 丑得让老衲又开始思考苦海无边, 回头是岸的道理了.....
但这就是Excel自带模板的真面目......
好, 图表美化经中真正的大招来了: 天罡地火 辅助列!!!
为了弥补Excel自带模板的先天不足, 制图时, 很可能需要添加一些数据,
来实现美观的效果.
原有数据(提前切换了行列)添加辅助列之后变成这样:
http://pic3.zhimg.com/df7c74ec32931be50be445fa96f6f88a_b.jpgExcel？（下）" TITLE="怎样才算精通
请看大图!
http://pic4.zhimg.com/a977db9382164f3857755ebb3ffaf027_b.jpgExcel？（下）" TITLE="怎样才算精通
吃包辣条冷静了一下,
老衲觉得问题主要是图中每个环都瘦比飞燕, 如果变成杨玉环想必会漂亮得多.
那就来试试吧:
http://pic4.zhimg.com/a52442216241f7f495f0595b283d1383_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/5a24b72994dfad298be88e87a3f57057_b.jpgExcel？（下）" TITLE="怎样才算精通
接下来就简单了:
1.
去掉每个环区的边框, 使其彼此紧凑. 2.
http://pic2.zhimg.com/82adaa96007ae21d3d79b157af9a83f5_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/23fd1150b5d745cf64f8ff78575dd1dd_b.jpgExcel？（下）" TITLE="怎样才算精通
然后就是给每个环区赋上颜色.
看结果吧:
http://pic2.zhimg.com/e38f643f8987b62650cf87a8ebe6f201_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/475593904b6bc1f4373b19c4858069c3_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/0783495459bf72fe7f32eecdd4bede0b_b.jpgExcel？（下）" TITLE="怎样才算精通
----------------------四种清净分隔线------------------------
第三层表格/图表的主要内容都说得差不多了.
迷你图很简单, 一试便知. 今天剩下的时间, 就讲讲第三层另一个屠龙神技吧: 般若白象功Power
View.
http://pic4.zhimg.com/170a3139078343289c3dc7e42030f84b_b.jpgExcel？（下）" TITLE="怎样才算精通
使用这个功能, 能生成所谓的动态图表, 怎么个动态法呢?
就是选中某个数据系列或者筛选项时,
对应的数据系列会变色/高亮.
还有就是使用一个切片器(还记得这是什么吗?)可以同时控制多个图表(即所谓的联动)
当然如果真的有这种需求存在, 那Power View应该还是有点价值的. 在老衲的生涯里, 这种需求非但极少, 而且均属锦上添花的作用, 最后都是用VBA解决的......
另外一个略有用的功能就是, Power
View能根据地名自动绑定Bing地图.
老衲伪造了一份美国各州人口表(使用了Randbetween函数):
http://pic4.zhimg.com/c9fd9ddba2d5f5bbc036957d780e9083_b.jpgExcel？（下）" TITLE="怎样才算精通
将这两列选中后, 点击"Power View",
这个时候就可以去泡咖啡了.
大概两分钟吧, 终于生成了一个全新的工作表(Sheet):
http://pic2.zhimg.com/758445b2f14b86b5202214dcd126a0d1_b.jpgExcel？（下）" TITLE="怎样才算精通
但是老衲从前见过的地图,
一般要求画成这样(这个老衲当年也是用VBA解决的):
http://pic3.zhimg.com/e8605c73aec06fe7ec933966d7413ed2_b.jpgExcel？（下）" TITLE="怎样才算精通
所以老衲称之为屠龙神技, 还是有自己的道理的. 顺便一句, PowerView的功能, 是用silverlight实现的(可以理解成微软家山寨flash), 而silverlight已经被微软判了死刑........春草碧色,春水渌波,送君黄泉,伤如之何, 伤如之何, 阿弥陀佛........
(至于怎么画上面的图, 可以参见老衲的另一个答案: excel上怎么做数据地图？ - 靳伟的回答)
第三层真经讲解完毕. 不日更新第四层.
---------------正理因明分割线------------------
第四层: 数据透视表
数据透视表(pivot table)这个]翻译比较古怪. 不过名称不是大问题,
只要理解数据透视表能做什么即可.
数据透视表是一种简易报表, 可以对不同的数据行列进行数据汇总.
数据透视表的入口在此:
http://pic2.zhimg.com/94e506a6f2ac7c36b79992f422fcc591_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/4c6463b39fc0e92d89ec195fa8234da3_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/003aa229b4eecd28f7cf36356c07d971_b.jpgExcel？（下）" TITLE="怎样才算精通
用公式sumif是可以实现的,
但现在有更好的办法来了.
选中这个表格, 插入数据透视表:
http://pic3.zhimg.com/8bb54ad3d2844f81c21bf70bb83cb75e_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/2faf46012ac648e6b5416f8f4dee13b7_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/fa9595f1d16c57ca7091f575825247eb_b.jpgExcel？（下）" TITLE="怎样才算精通
这时候老板又发话了,
按照颜色和销售人员各统计一下总销售金额. (高达八成的老板都是这样) 怎么办呢? 很简单,
再拖一下:
http://pic3.zhimg.com/eab04ffa377ffb232aabaf85a15cbd56_b.jpgExcel？（下）" TITLE="怎样才算精通
老板继续发话,
"你这么搞完全没有理解我身为老板的一片苦心! 我是让你制作两个表, 一个统计人员, 一个统计颜色, 然后给我一个过滤表单,
这样我可以按日期看人员和颜色的变化趋势."
这个说来很简单,
只需将Pivot table整个圈中, 复制黏贴, 然后改一下字段即可.
http://pic2.zhimg.com/7ad5c282a1cf3beef6c73ca6fbcb17e5_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/d0776903c0fd0ff868408db5a0a87a1b_b.jpgExcel？（下）" TITLE="怎样才算精通
但现在有两个pivot table, 是否有方法同时操作两个pivot table呢?
有, 那就是切片器(从前也提起过哦)
选中一个Pivot table, 添加一个切片器。
http://pic4.zhimg.com/2f16d85603b245667fab6a974decb6e7_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/5817804fce088a46bdb7a69b43db20bb_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic1.zhimg.com/d7babef777a96df94c736b0b48efdf3c_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/05403e84ad53dd46cebd4c00b5d216fe_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/74c669007875f9820505c8ea46e204c5_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/079e7e8e66df69255dcd032e2e2a694a_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/533b5cf44779ff59b57e0ef0310941d9_b.jpgExcel？（下）" TITLE="怎样才算精通
老衲由于近来深研佛法三宝(合称PPT), 一时没有准备OLAP真经, 所以这节暂时跳过, 待机缘成熟再来补完。
至于Power Pivot, 各位可以直接视之为"不服跑个分儿"版的数据透视表. 严格点说, 它预期起到的作用是简易的数据库(例如Access), 而工作方式比较像数据透视表。
http://pic4.zhimg.com/46f8835d31b104904298b25c5dad70db_b.jpgExcel？（下）" TITLE="怎样才算精通
另外想要在Excel中突破一张表最多100万行的限制, 也得仰仗这位的大肚能容. 在Power Pivot中, 一张表的最大行数为20亿行. 详情请见: PowerPivot Capacity Specification
但老衲还是认为, 如果必须应对上亿行的数据, 学习一下数据库--例如SQL server, Oracle, MySQL
--是很有必要的, 好过使用这个Power Pivot. 所以这个Power
Pivot暂不深表.
数据透视图和普通的图表几乎没什么不同, 只不过能和一个数据透视表彼此联通, 控制表的同时, 可以影响到图的展示内容.
不作为重点.
这一章通常来说, 最常用的还是 计算字段 和 计算项.
望勤为操演.
余者待老衲重新准备一下, 来日方长.
---------------众因缘生法分割线----------------
章五: VBA / VSTO
老衲痛感逝者如斯, 不舍昼夜, 因而决定提前讲说章五.
也就是乾坤大挪移心法.
请各位注视自己的Excel, 是否能找到我神功入口?
http://pic2.zhimg.com/89eefe47f22a93cd27fe67c0c18fae11_b.jpgExcel？（下）" TITLE="怎样才算精通
"开发工具"一定要选中才行.
http://pic2.zhimg.com/4ec376023045929fba411af0ab772599_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/bb3fb6114070acfa29bb450803d09eb9_b.jpgExcel？（下）" TITLE="怎样才算精通
举个栗子, 请各位看个大概:
1. 点击录制宏:
http://pic2.zhimg.com/bf81eb7452f161bd09136a5921ddc6d5_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/acf6a463a7999292ec970f79bbca2f11_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic4.zhimg.com/a6249b0e8bb19ff9380586b597896493_b.jpgExcel？（下）" TITLE="怎样才算精通
Sub 宏4()'
ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort.SortFields.Add Key:= _ Range("表13[[#All],[Volume]]"), SortOn:=xlSortOnValues, Order:=xlDescending _ , DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End WithEnd Sub
这就是VBA奥义最简单的例子.
各位少侠中, 可能有不少人第一次发现这个入口. 对于编程(没错, 就是编程)可能也不太了解,
所以有一些基本概念要澄清一下:
这些基本概念分别是: 对象, 属性与方法, 集合.
因为老衲遁入空门前是一名光荣的人民厨师,
所以就用厨师来做一下比喻.
一个厨师, 就是一个对象. 一群厨师(同类别的对象), 就是一个集合. 集合也是对象的一种.
像"顺峰颐和园路东口分店后厨大师傅们"就是一个集合, 也是一种对象.
我们来定义一个厨师:
Dim someone as Chef
现在在代码世界就有了一个叫someone的厨师了.
那此Someone有哪些属性呢? 例如身高178cm, 体重73kg, 月工资15000 (顺峰店啊, 人均消费过千, 大师傅没有上万的薪水, 你们还愿意去吃吗?
身高 = someone.Height
'这时候身高=178, 前面这个 ' 号要注意, 这个是注释, 开头写上' 号的一行, 在程序眼里是不存在的, 只是方便奇行种程序猿(达尔文在加拉戈帕斯群岛发现的哦)来阅读的.
体重 = someone.Weight
'这时候体重= 73
工资 = someone.Salary
'这时候工资= 15000
那"顺峰颐和园路东口分店后厨大师傅们"也可以是一个对象, 为了举例方便, 大师傅集合就叫Chefs.
Dim 顺峰颐和园路东口分店后厨大师傅们 as Chefs
那顺峰颐和园路东口分店后厨大师傅们 有哪些属性呢? 例如总人数15人, 每月工作时间21.75天
顺峰颐和园路东口分店后厨大师傅们.count = 15 (等于号, 表示是赋值操作, 把右边的值写到左边去, 前提是左边的东西可读写, 刚才这句话相当于为大师傅强行指派了一个总数, 当然通常来说, 这个count很可能是只读的)
顺峰颐和园路东口分店后厨大师傅们.WorkDays = 21.75
好, 对象, 集合, 属性三个都说过了, 那方法又是什么?
方法是对象能做出的行动.
例如someone这个对象, 本质上是一名光荣的人民厨师.
他能干什么呢?
someone.fries() '炒/炸
someone.boil() '煮
someone.stew() '炖
.....'英文能表达的动作太少了, 完全不适用于中式厨师啊
.....
那"顺峰颐和园路东口分店后厨大师傅们"这个对象呢?
顺峰颐和园路东口分店后厨大师傅们.work()
顺峰颐和园路东口分店后厨大师傅们.Dismiss()
.....
.....'还有扯淡吹水等动作就不逐一列举了.
有这些概念, 少侠基本就明白自己面对的是些什么东西了.
然后老衲还要以厨师工作举例, 说明一下语言(VBA)和IDE是什么.
编程都有语言, 语言本质上就是一种人类和机器相互沟通的工具, 人类告诉机器怎么来运作, 如何执行动作,
创作出猪肉料理来. 在厨师界, VBA就是中式菜, C#就是西式菜, Java就是印尼菜. 不同的语言在思路上是很不一样的,
例如Java讲的就是普适口味, 不再众口难调; VBA讲的就是快熟快上; Erlang讲的就是明火多灶;
但是目标都是把饭做熟.
那IDE(Integrated Development Environment)是什么? 当然就是厨房啦.
有全套厨具, 灶台, 烟机, 还有一个宝贝哦: 全方位支持的速查菜谱.
Excel自带一个VBA的IDE, 虽说简陋得紧, 一副从大清朝穿越过来的样子, 不过好歹也算功能齐全, 聊可一用. (到了VSTO可就是使用全套顶级大厨厨房了, 保证乐不思蜀~)
http://pic2.zhimg.com/bc4a7f7eb30ab54a2b842400cced4761_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic2.zhimg.com/b17944c11f783c53f72eb153469c77a5_b.jpgExcel？（下）" TITLE="怎样才算精通
工程区:
在这里可以直观地管理/组织你的代码。
属性区:
对于在工程区选中的对象, 在属性区可以直观地显示该对象的各个属性。
代码区:
就是写入VBA并调试执行的地方
监视窗口:
对于运行中的对象, 我们想知道它在中间状态中的各个属性值变化, 可以通过这个窗口来观察. 如果自己做过牛排, 或许知道探针式温度计.
这两个道理很相近。
http://pic4.zhimg.com/d1d43e85da794058801185e3f1f3dac3_b.jpgExcel？（下）" TITLE="怎样才算精通
第一步: 起火,
找一个新锅(建一个新Module).
第二步: 写代码, 炒回锅肉.
写完了之后点上面的三角箭头
http://pic4.zhimg.com/fabae20f8f796d7b78e1ade50f00ebf3_b.jpgExcel？（下）" TITLE="怎样才算精通
http://pic3.zhimg.com/9b72b290f844b10e499e11dba791a2ca_b.jpgExcel？（下）" TITLE="怎样才算精通
从回锅肉到全世界, 中间的困难主要在于对各个食材(对象,
类库)的熟悉程度. 再往上则是编程思维(例如编程Pattern). 因此老衲也不打算写太多了,
可以参考:
循序渐进,
必至大道.
---------------破我执分割线-------------------
应某位少侠的要求, 提前讲解一下Excel(2013)
的局限性,
如是我闻:
1. Excel(2013)及之前,
大概占用普通Windows电脑内存的25%-30%, 即容易崩溃, 或出现各种不稳定症状. 尤其是32位Windows.
该数字出于老衲的经验. 因而一个大内存是很有必要的.
2. Excel(2013)的一张工作表,
可以容纳1,048,576行 乘以 16,384 列. 那么如果少侠家资丰厚, 随便拿出两百万行数据怎么办? 请使用SQL
server express(express版本是免费的)来辅助进行数据预处理. Access理论上也可行, 但是有失简陋,
所以老衲向来不用, 从而知之甚少.
3.
一个单元格能放32,767个字符.
4. 回退能退100步.
但使用了VBA就不好说了.
5.
一个下拉列表单里能放10000个选项.
6.
一个函数里面最多使用255个参数.
7. 公式里面的函数嵌套, 可以嵌套64层.
(说7层的那是还没更新到2013, 请加速更新)
8.
公式的长度不能超过8192字符.
9. 一个图表里最多可以放255个数据系列.
(一般来说放二三十个就已经糊满了)
10. 面积图, 不能用平滑曲线.
需要使用别的方式实现.
11. 柱状图,
不能同时两个柱状的数据系列分别对应主坐标轴和次坐标轴. 需要变为一个柱状图,
一个折线图.
来源邀稿：靳伟
http://www.changweibo.com/ueditor/php/upload/20150817/14397988841774.jpgExcel？（下）" />
http://www.zhihu.com/question/24277854/answer/45459174
喜欢
0
赠金笔