表格之道.docx
《表格之道.docx》由会员分享，可在线阅读，更多相关《表格之道.docx（19页珍藏版）》请在冰点文库上搜索。
表格之道
Ch1表格之道
获取规范计算分析转化输出
Ch2准备工作
快捷键：
Alt+数字快速访问栏
Ctrl+Z撤销
Ctrl+Y恢复
Ctrl+S保存
选择类快捷键：
Ctrl+PgUp/PgDn工作表转换
Shift+→选择多个方框
Ctrl+→区域内跳动
Ctrl+Shift+→选中一个完整的区域
Ctrl+1设置单元格格式
Tab正序移动Shift+Tab倒序移动
F5定位功能键
选中不连续的区域/单元格/对象
Ctrl+A选中整个表
Ctrl+鼠标不连续无规则区域
Shift+鼠标超长不连续区域
功能类快捷键：
功能
对应快捷键
类型
Ctrl+1
显示“设置单元格格式”对话框。
格式类
Ctrl+B
应用或取消加粗格式设置。
格式类
Ctrl+L
显示“创建表”对话框。
格式类
Ctrl+C
复制选定的单元格。
复制粘贴类
Ctrl+V
粘贴
复制粘贴类
Ctrl+Alt+V
选择性粘贴
复制粘贴类
Ctrl+X
剪切选定的单元格。
复制粘贴类
Ctrl+Y
重复上一个命令或操作（如有可能）。
命令撤销类
Ctrl+Z
使用“撤消”命令来撤消上一个命令或删除最后键入的内容。
命令撤销类
Ctrl+F
显示“查找和替换”对话框，其中的“查找”选项卡处于选中状态。
通用类
Ctrl+S
使用其当前文件名、位置和文件格式保存活动文件。
通用类
Ctrl+N
创建一个新的空白工作簿。
通用类
Ctrl+F1
将显示或隐藏功能区。
通用类
F1
显示“Excel帮助”任务窗格。
功能键
F2
编辑单元格
功能键
F3
显示“粘贴名称”对话框。
功能键
F4
重复上一个命令或操作（如有可能）。
功能键
F5
显示“定位”对话框。
功能键
F9
计算所有打开的工作簿中的所有工作表。
功能键
F11
在单独的图表工作表中创建当前范围内数据的图表。
功能键
●会计当中做表需要加下划线时，最好使用边框当中的底边框
●倾斜和下划线功能不建议使用，需要区别的话使用加粗
●巧用替换功能
其他编辑技巧：
ØShift键移动区域
ØCtrl+Enter批量填充内容
Ø填充公式Tab可直接加括号
Ø快速调整行列宽
Ø选择性粘贴消除公式
常见符号
CTRL+；
现在日期
CTRL+SHIFT+；
现在时间
=today（）
现在日期
=now（）
现在时间
ALT41420（小键盘）
√
ALT41409（小键盘）
×
快速填充鼠标双击
预览合计数表格右下角状态栏（选项）
Ch3获取数据
OCR技术光学字符识别
从网页导入数据
利用调查表获取信息
手动录入数据：
使用TAB键进行移动
添加数据有效性（数据验证）减轻工作量：
根据所选内容创建
遇到重复内容使用Ctrl+D/R
小键盘提高录入速度和准确率
Ch4规范数据
格式的基本概念：
属性
数字
文本
特征
右对齐
左对齐
格式变化
各种变化
相对固定
长度
超过11位转为科学计数法
相对稳定
四则运算
可以计算
经常无法计算
Ø文本储存长数字不会变成科学计数法（身份证号）
Ø数字格式特殊数字转化为中文大写
Ø日期以1900/01/01为起点日期和时间均为数字
Ø文本转为数字选择性粘贴乘以1or分列
4-4电子表格的通用格式：
Excel的作用是处理和分析，不是供展示（需符合一定的标准，电脑容易识别）
电子表格是简化了的数据库
错误
合并单元格、没有序号、乱加表头、胡乱合计、不符合数据透视表、错误理解0、分裂表格、数字文本化、单元格非原子化、内容不统一
1.完整的字段标题
2.首字段做序号
3.数字和日期正确
4.有效性避免错误
5.符合透视表的规范
6.没有不规范项（空格、合并单元格等）
Ø套用格式的意义：
更易于阅读
更易于添加数据
帮助完善字段标题
优秀的初级数据分析平台（排序、筛选、查重）
取消表格：
设计→转化为区域→清除格式
Ch5字段调整
5-1函数简介
根据现有数据，新增一列数据
怎么新增函数：
输入等于号新增，按TAB键完善
5-2函数分类
1.主干函数：
逻辑判断、查找引用
2.零件函数：
时间日期、统计、文本、随机
5-3单元格命名
引用时按F3显示粘贴名称
Ø公式——根据所选内容创建→批量命名
命名规则
Excel名称框不允许使用RC
空格会被下划线自动填充
与单元格相同的名称为避免冲突不得引用（32/A4）
符号只能包含.或者_
5-4名称应用、数据验证
建立新表设置数据有效性→根据所选内容创建→F4重复上一步操作→创建数据有效性（Alt+D+L）→来源（F3）
Ø出错警告
5-5相对引用
相对引用、绝对引用、混合引用变换快捷键F4
快速填充Ctrl+Enter
5-6零件函数之统计函数
级别
函数名称
作用
1
+
加
1
-
减
1
*
乘
1
/
除
1
power
开方次方
2
sum
求和
2
average
求平均
2
count
计数
2
max
最大值
2
min
最小值
3
sumif
条件求和
3
counif
条件计数
3
averageif
条件求平均
4
sumifs
多条件求和
4
counifs
多条件计数
4
averageifs
多条件求平均
函数中存在中文要加英文半角双引号
5-7零件函数之日期函数
函数名称
作用
today
返回今天日期
year
提取年份
month
提取月份
day
提取天数
weekday
计算周几
weeknum
计算第几周
date
日期函数
5-8零件函数之文本函数
拆：
leftrightmid
合：
concatenate/&
转：
text（转格式）trim（去除字符串前后的空格，只在中间保留一个空格作连接用）
量：
len（测量文本长度）
找：
find（返回一个字符在一个字符串中的起始位置）
智能填充
Ø数据分列数字改成日期格式eg.20151022→2015/10/22
5-10零件函数之随机函数
随机randrandbetweennorm.inv
取整round
绝对值abs
5-11If函数概述
Ø嵌套函数
Ø辅助函数AndOr
ØIFERROR
比较运算符
含义
示例
=（等号）
等于
A1=B1
>（大于号）
大于
A1>B1
<（小于号）
小于
A1>=（大于等于号）
大于等于
A1>=B1
<=（小于等于号）
小于等于
A1<=B1
<>（不等号）
不等于
A1<>B1
5-12If函数嵌套
函数栏换行Alt+Enter
5-13If函数and与or
And和or函数作逻辑判断
5-14查找引用函数之Vlookup函数简介
1.一般有两张表格
2.有某一共通字段（字段内顺序和个数可不一样）
3.A表缺少某字段而B表有该字段
下拉时要注意固定选中的区域（或者命名某区域）
Vlookup是竖列查找，Hlookup是横排查找
5-15Vlookup具体应用
若有出现无共通字段，即出现错误，可在vlookup函数外嵌套iferror函数
5-16Vlookup模糊查询
5-17Index与match函数
5-19Indirect函数
制作二级数据验证
Ch6生成表格
6-1数据透视表简介
Alt+3插入数据透视表快速访问栏
6-2数据透视表构成要素
字段列表
行区域
列区域
筛选器
值区域
6-5源数据的矫正
参数表→规范→数据源表→产生→分析表（数据透视表）
日期只能用/和-
长数字要先设为文本格式再输入
先清除格式再空格替换为空值
6-9数据透视表美化
●关闭+/－按钮：
通常只有在数据透视表表超过一屏时才会用到，而数据透视表本身为结果导向性的分析窗口，使用该功能的机会较少，应予以关闭
●分类汇总——不显示分类汇总：
对每一级字段进行汇总，此举会导致表格的碎片化，应选择不显示
●总计——对行列禁用总计：
并不是每张表盒都需要观察总计数，一般等需要再打开
●报表布局——以表格形式显示：
表格布局更加符合中国人的阅读习惯，更易于理解
●修改值字段标题
6-10数据透视表的编辑功能
自定义排序：
选项——高级——编辑自定义列表
切片器
日程表
6-11数据透视表的计算功能
运用正常的函数的时候先设置：
分析——选项——取消选择生成GetPivotData
Ch7格式修改
7-1自定义格式
在数字前面加0（不用文本格式的情况下）
0“KG”数字后面出现文本单元格仍然可以计算（实际仍未数字）
●0是数字占位符，@是文本占位符
隐藏数字自定义格式;;;正数格式；负数格式；零格式；文本格式
加颜色[color]
表格内容的修正：
数字与日期对齐
数值格式会在数字后面加上一个空格，可在自定义格式内修改
标题要反色显示
可直接用Ctrl+L创建表，并自动冻结首行
首行应冻结
调整正确的行高和列宽
选中整个表，分别在列宽和行高处双击
隐藏多余行列
默认在A1单元格保存（保存时光标放在A1单元格）
对表格进行解释可以放在批注里
标题颜色加第二行的颜色（字体颜色为黑）；加倒数第二行颜色（字体颜色为白）
多表格同时查看：
（视图的功能）
多工作簿比较
全部重排全部查看
多工作表比较
全部重排选中当前活动工作簿窗口新建窗口
多行数据比较
拆分
Ch8数据输出
纸张、平面、图标、BI工具
8-1让表格在纸张上呈现
打印预览Ctrl+F2
页面布局（右下角）：
可以看到打印预览
可以添加页眉、页脚（页面顺序）
不需要的信息可以隐藏之后再打印
页面布局——打印区域
分页预览（右下角）
打印预览可以设定页边距
页面设置（分页预览中）页边距
8-2打印在细节上的调整：
设置页边距
设置纸张方向
设置缩放比例
设置打印区域
格式样式：
Ctrl+L
设置页眉、页脚、标题
按需固定字段标题（页面布局——打印标题——）
表格居中
不建议使用网格线，套用表格式较好
8-3表格在平面上展示
在网页、海报、画册中展示
不属于数据视觉化、信息图标的范畴
平面设计：
排版（对齐、对比、重复和亲密性）
色彩的搭配
内容的筛选
文案
慎用颜色
万物对齐
少用边框
8-4图标的用途与分类
柱形图：
最百搭、适用于显示少量二位数据、可采用时间为横轴（效果不及折线图）
条形图：
纵向柱形图、适用反应大量单维数据信息、不适合显示时间维度
饼图：
各项目所占百分比、数量较多时我们会使用复合饼图、环装饼图
折线图：
反映时间轴变化的主要方式、适用于多列数据在同一时间段内趋势变化的比较、折线图的变种——面积图具有更强的视觉观感（一般堆积时使用）
散点图：
二维表神器
8-5数据的选择
柱形图字段五个以内，信息条数4个以内
8-6格式与设计选项卡
设计——添加表格元素
快速行列转换
设计——插入形状——插入文本框
8-7快速构建精美的商业图表
●主标题概括内容，显示导向性
●副标题解释图表细节（可以包括单位）
●图例（超过两个向量时）融入图表中
●弱化图片背景和网格线
●主标题副标题图表与数据来源左对齐
●颜色相近
●无衬线字体
8-8商业图表的细节
图表降噪消除无效信息
坐标轴的优化：
Ø删除重复部分
Ø纵坐标宜少不宜多
Ø横坐标宜短不宜长
Ø按需使用，能省则省
设置坐标轴格式
8-9强调个别数据的小技巧
数据标签
辅助线
画中画
8-10图表色彩搭配
参考经济学人的图表
Adobe配色
Excel中无取色器
ColorPix空格键锁定
8-11保存你的图表为模板
8-12迷你图
迷你图只是格式，单元格为空值
8-13制作复合商业图表
散点与气泡：
反映个人情况（不带名称）
散点图：
用于反映两列字段
气泡图：
用于反映三列字段
复合图表：
插入图表→组合
8-14将图表输出为其他形式
以图片的形式复制到Word
无轮廓、加底色
Ch9商业BI与实践
9-1使用简便的方式实现商业BI
商业智能BusinessIntelligence
Ch10房地产企业的管理精细化
388
590
693
794
1295
1899
23100
26105
28117
32120
35121
38122
44125
47153
48155
54175
57179
60182
65191
73200
82203
86205
升级会员 