你早该这么玩EXCEL

很多人对于EXCEL的应用之居于表面，而且，也只是仅仅使用了其中一小部分功能。那么，怎样了解和使用更多功能呢？

你早该这么玩EXCEL

参数表

函数运用

Vlookup

按列查找，最终返回该列所需查询列序所对应的值

Column

返回单元格的列号

Len

返回文本字符串中的字符数

If

执行真假值判断，根据逻辑计算的真假值，返回不同结果

源数据表

注意

①勿用标题占用工作表首行

②字段依次排序

③勿用列来分隔

用设置单元格格式取代

④合计行是多余的

可添加属性辅助列

⑤勿加多余表头，勿用√，要用文字表述

⑥禁止合并

⑦禁止数据留空

⑧数据集中，禁止多表

⑨禁止单元格内容存在复合属性

禁止符号、短语、句子、中文数值

⑩独揽变出汇总表的工作

小技巧

乾坤大挪移 P22

巧妙删除空白列 P25

单元格一键批量录入 P33

没有中文的中文大写字母 P43

切开单元格 P49

条件格式 P71

自动重算→手动重算 P75

最牛快捷键 P159

选中X:X列所有非空单元格，Ctrl+Shift→↓

文本很长又如何 P179

“自动更正选项”

瞬间求和 P194

选中数据区域→F5(定位到空值)→Alt+=→完成

技能心得

批注不宜过多

可添加属性辅助列

分类汇总不用也罢

案例

Min-Max分析表 P65

Vlookup

Sumif

Sum

If

采购情况表

描述产品用代码(物料号)

五味良药

顾全局：背景确认

定性

例：修改现有表格，创建新表格，重塑工作流程，设计的表格需供多人操作

定量

例：每月十几条销售数据，几百条报价信息手工录入，销售明细表应尽量详细，报价记录表要精简字段，均可设计为一份源数据表记录多年的销售/报价记录

定损

例：数据录入需规范，字段分类要清晰，业务字段要详尽

知目的：明确需求

需求明确，就可以确定工作次序和工作重点

例：新建一份报价记录表

勾轮廓：字段设定

字段分析

把用中文描述的设计表格的目的翻译成EXCEL源数据表中的字段名称

如：我想分析全国销售趋势

全国=地址

销售=数量

趋势=日期

字段拓展

拓展

通过基础字段得到不同属性的相关字段：供应商字段→供应商名称&供应商所在地

拆解

通过基础字段得到同属性不同级别的字段：地址字段→省、市、县

字段补全

责任追溯：录入员姓名

代码管理：代码/序列号：避免出错/沟通成本增加

定结构：流程解析

工作顺序

令表格字段读起来有逻辑

先概括，后明细

找到表格属性对应的黄金法则

录入方式

①手工录入

工作重点

敲击键盘输入内容/滑动鼠标选择单元格内容

此字段排在最左列

②复制粘贴

从其他表格粘贴/导入系统数据

此字段排在中间列

③公式链接

公式自动关联

此字段排在最右列

细打磨：表格装修

①清晰

检查字段是否需要带单位

②安全

单元格的数据录入安全

数据有效性

用选择的方式完成录入

工作表的数据录入安全

禁止搞乱设定的公式/禁止在公式单元格手工录入

①选中允许录入的单元格区域，设置单元格格式，取消勾选保护标签中的“锁定”

②调用保护工作表功能，取消勾选“选定锁定单元格”，确定

③智能

Vlookup(查找与引用函数)

作用：查找某单元格数据在源数据库中是否存在，如存在，则返回源数据库中同行指定列的单元格内容，如不存在，则返回#N/A

四个参数

①Lookup_value 用什么找

②Table_array 去哪里找

③Col_index_num 返回第几个值

④Range_lookup 0精确/1模糊

注意

数据库首列必须为待匹配字段

在单元格输入=Vlookup→Ctrl+A打开参数面板，加()失效

Column

作用：返回单元格的列号

If/Len

借助这两个函数隐藏空白单元格的#N/A

④美观

文本对齐

文本在垂直方向居中对齐，同列数据水平方向采用同样的对齐方式

文本大小

对于数据明细，10号字比默认的12号字更精致

字体

中文：宋体

英文：Arial/Times New Roman

慎用特型字体

网格线

去除后更清爽

单元格边框

同类数据区域采用相同边框

禁止大面积使用粗边框/虚线边框

色彩

不宜超过3种颜色

多用不同层次的同种颜色/同层次的不同颜色

慎用大红/大黄/大绿

突出标题行

设置标题行的单元格填充色

修改字体/字形/颜色/大小

便与数据区域区分开来

简化数据区域

待录入区域不填充颜色

慎用下划线/倾斜

深度美化

数据区域

手工录入、复制粘贴、公式链接的数据区域要用不同的颜色区分

以告知使用者哪里需要填写，哪里需要粘贴

字体大小

稍大

需要录入/经常查看的单元格字体

稍小

公式生成的明细数据字体

边框

虚线

弱化明细数据/非重点数据

粗实线

分隔录入方式不同的数据区域

工作表

不同颜色工作表标签

区分汇总表、源数据表及参数表

明确告知使用者哪个工作表需要填写，哪个仅供参考

合理运用颜色管理降低出错风险，提高工作效率

分类汇总表

数据透视表

做法

轻松选中，调用功能 P88

设置参数，一步完成 P89

认识界面，熟悉“车间” P91

拖拽之间，变化万千 P93

小技巧

还我汇总项 P99

拆分源数据偏方 P103

拆成多个汇总表

拖拽跟着文字走 P108

...在...的...总和

用透视表拖拽进行去重统计 P132

透视表可复制 P137

复制在同一工作表内进行分别设置

注意

①列字段：1个是标准，2个是极限

②工具栏路径

视图→工具栏→数据透视表

注意

①要站着，别躺着

②通常按月汇总就足够

③字段主次要分明

关注焦点在一级

④日期摆放有讲究

页/行/列字段，哪样看着最舒适

⑤传播表格时注意数据保护

选择性粘贴可避免泄露隐藏明细

⑥预约源数据

设置透视表第二步设置

⑦定时备份源数据

技能心得

将不必要、重叠的工作表删掉

汇总表隐藏明细

在数字上双击即可

变出的工作表与源数据表没有关联性

数据与源数据关联，注意刷新

！一表一刷

小技巧

拆分日期有妙招 P121

年/季度/月/日/时/分/秒

日期的标准格式

①2013/2/7②2013-2-7

日期快捷输入

①Ctrl+；→Enter当前日期 ②Ctrl+Shift+；→Enter当前时钟

“表”亦有道

赢得同事的认可

①建立高品质形象

用心雕琢用于与他人沟通的表格

表格整洁、美观

为使用者考虑的智能化设置

保护公司数据安全的制作方式

②好的经验书面总结

口口相传不如记录在案

记录和整理的同时更好地认识自己

也许下一个工作标准就从你开始

③保管好报表

确保报表存在于电脑中

对报表进行有效的分类

定期做备份

④善于分享

人与人之间最好的沟通方式

聊天过程就是分享信息的过程

彼此分享知识和经验，共同成长

加深感情，培养信任

赢得老板的认可

①比吩咐的多做一点

接到任务时，问清楚

要的是什么

什么时间要

有没有其他要求

了解他的目的再动手做

接到任务后，认真做

明细数据要附带好

图表要附带好

美化要做好

对特别的数据进行标注，做简单的分析和说明

情况允许下再做一份相关的数据与之进行对比

确保在汇报时面对提问能够对数据情况了然于胸

②汇报进度并按时完成

主动汇报

汇报内容

能否按原计划完成，若不能，是由什么原因造成，预计推迟多久

遇到了什么困难，需要什么帮助

已经解决的问题或取得的成就不用交代

按时完成

即使有不可控因素也要尽量按时完成

体现工作能力和计划性

接受任务时，要明确完成时间，若有异议最好当场提出，事后如有困难就要自己克服

如果不能按原计划完成，要说明理由

@发生了不可预期的特殊事件

③邮件就是你的脸

杜绝错别字

邮件发出前，不读上3遍也别点“发送”

记得带附件

养成习惯

写邮件前先插入附件

指出双胞胎邮件的不同

情境

第一封忘带附件

又发一封与第一封一模一样但带附件的邮件

使收件人丈二和尚摸不着头脑

正确处理方法：要说明它与第一封邮件的区别

忘带附件型

相同标题，后添“(带附件)”

附件修改型

相同标题，后添“V2(注：以本附件为准)”

致电收件人

邮件发出后，马上致电收件人，告知以第二封邮件为准

加上一句，如有任何问题，请随时找我

空白邮件不礼貌

即使对方只要一个附件，也是可以写点东西的

注重礼仪，不要丢弃了传统书信的要素

称呼

问候语

正文

祝颂语

具名

日期

示例

张总：您好！附件是2010年市场推广计划表，请查阅。如有任何问题，请随时联系。祝好！小李 2010年2月4日

对附件进行简单描述

为了让收件人第一时间得到最准确的信息，在正文中要对附件内容做简单的描述

示例

……请查收。根据数据分析结果，得到两个基本结论：第一，……；第二，……。行动建议如下：……

④给老板做多选题

忌

问答题

单选题

宜

多选题

示例

……所以，现在一共有A\B\C三个方案，

我的建议是：……(方案排序)，说明理由，

您看怎么办？

图表

杜绝华而不实

EXCEL图表不是PPT，焦点在数据不在演示

简单、清晰、直观就是好图表

关注内容，而非形式

心动不如行动

重视行动是关键

心动

渴求数据

形式好，数据全

行动

分析数据

采取行动

小技巧

鼠标将数据变成图形 P200

图表粘贴在Word/PPT后的数据关联变更

“粘贴链接”

让图表动起来 P214

数据透视表→图表向导

无外乎3种图表

俯视大饼

使用情境

对比份额

对比分布

使用效果

通过扇面大小说明比例关系

不强调数值本身

不推荐使用类型

3D效果

分裂效果

端正柱子

使用情境

比较数值

例：对比不同省份所有行业的全年营业收入

例：某家企业不同年度的营业收入

使用效果

关注多个数值间的大小关系

关注单个数值本身

不推荐使用类型

立体柱子

装“点”折线

使用情境

各时期的绝对数值

多个时期的变化趋势(数据变化)

例:一年12个月的趋势

注意

少于6个对比数，不推荐使用折线图

少于6个对比数，柱形图表现力更好

使用效果

数据点越多，越能体现出规律

不推荐使用类型

3D效果

无数据点效果

图表美化

标准1：干净

杜绝花哨

保持背景和色彩的干净

标准2：原配

使用默认的图表类型

不要尝试其他不必要的类型

标准3：大小

图大

字小

标准4：简繁

简洁

不要显示过多信息

美化方法

在需要美化的地方双击鼠标→设置面板出现

概念图

参考阅读 P207

注意

可怕的信息泄露

图表粘贴在PPT中，双击会泄露整个工作簿的源数据

粘贴方式

粘贴为图表对象，源数据跟着走

粘贴为链接：PPT在源数据本机才可看到源数据