Excel（1）

什么是数据分析/h3>

数据分析是指用适当的统计分析方法对收集来的大量数据进行分析，提取有用信息和形成结论而对数据加以详细研究和概括总结的过程。

重点：对业务的理解。

工具：Excel、SQL、SAS、SPSS、R、Python、Hadoop、Spark、Scala等。

路径：收集数据—数据清洗—数据探索—数据分析—结果检验–数据可视化

认识Excel

一、Excel软件简介

1. 公式必须以=开头，否则不会进行运算。

   要大范围手工输入很多公式时若要省略=：Excel—偏好设置—兼容性–转换Lotus123公式（平时不建议如此）

2. Excel能做什么/p>

   数据存储—>数据处理（函数应用）—>数据分析—>数据呈现

二、Excel中一些重要概念

1. 几种常用文件类型

   XLS（2003版前）、XLSX （2007、2010版）工作簿文件

2. 整个打开的是Excel的主程序，中间区域是工作簿，用小的放大、缩小及关闭键控制。

3. 需要切换两张表时（可同时看到两个窗口的内容，互为镜像，修改时同时变化，便于比较）:

   窗口—新建窗口–（若为两个窗口）重排窗口—垂直

4. 关于工作簿、工作表、单元格的操作

   a. 新建工作表：下方+按钮（删除/重命名工作表：在工作表标签上右键）

   重要工作表的标记：在工作表标签上右键–工作表标签颜色（去掉颜色：改回无颜色）

   插入多张工作表：按shift连选（点第一个、最后一个）多张工作表，再插入工作表，插入的就是多张工作表（删除多张工作表：按shift连选后右键删除）

   b. 横行竖列，定位单元格。

   插入空行：第四行前加一行，选中第四行行号，右键选择插入（删除整行：选中行号，右键选择删除）

   插入多个空行：选择多行，右键点击插入，会在前方插入多个空行。

   【列与行插入同理】

   拖走某个单元格：选中单元格–把鼠标放在单元格边框线上–鼠标变为抓手–可以把单元格拉走

   交换两列次序：选中一列，把鼠标放在选中的一列的边框线，鼠标变为抓手，按住shift拖拽（没按shift的话会替换）

   调整行高列宽：双击行列边线会自动调整为可容纳内容的最窄的长度。

   选中多列调整列宽：把多列同时调整为同一宽度。

   选中多列双击行列边线：这几列的宽度都调整为刚刚能容纳内容的最窄宽度。

   如何到达表格边界区域：选中一个有数据的单元格，鼠标移动到单元格边线，下边线双击到达表格最下，上边线双击到达表格最上，左、右同理。（必须是连续的数据区域，数据中间不能有空行）

三、小工具

1. 冻结窗格（便于查看表头）

   视图–冻结窗格—冻结首行（取消再点一次）

   冻结前几行：选中A4单元格—拆分–冻结窗格，则前三行被冻结。（冻结当前单元格上方）

   冻结A列：选中B4单元格—拆分–冻结窗格，则前三行和A列都冻结。（冻结当前点选单元格的上方和左侧）

2. 填充柄（拖拽）

   选中单元格，鼠标放于右下角，点击左键下拉。

   Control键+:键：输入今天的日期。（下拉自动填充）

   下拉有两种：按序列和按复制，按control键再下拉可以改变下拉的种类。

   点击右键下拉：选择以何种方式填充。（例：以工作日填充）

   自定义拖拽顺序：Excel—偏好设置–自定义序列—新序列–输入序列—添加。

   第2行选到第900行：上方名称框显示单元格名字，在名称框写A900后回车，可到该格位置；名称框中输入2：900，回车选中整个区域。

   批量填充：选中多行，输入值，Command+回车。

四、使用单元格设置工具美化表格

1. 设置单元格格式的对话框

   选中单元格（选中一个区域）—右键–设置单元格格式

   表格边框线：边框–所有框线

   合并多行单元格：合并后居中右侧小三角–跨越合并（选中区域按行合并）。

2. 对齐选项卡，设置文字对齐方式。

3. 设置边框线

   选中区域后右键–设置单元格格式—边框–斜线（输入内容：一个单元格内输入一行–按alt+回车换行–靠左对齐–上一行加空格到右边）

4. 格式刷

   选中有格式的表–点击格式刷–再选中无格式的表

   选中有格式的表格，双击格式刷，可任意改其他单元格，按esc退出

五、单元格数字格式

（单元格格式设置不会改变值的大小，看编辑栏）【单元格格式设置相当于化妆，本质没变】

1. 数字格式

   选中单元格–右键–设置单元格格式—数字—数值–小数位数、使用千分位分隔符

   货币：钱币符号跟着数字走

   会计专用：钱币符号在最左侧

   日期：微软采用1900计年方式（复制粘贴日期时若变为数值，更改单元格格式—数字–日期）

   自定义：类型框中最后加元字（例：192837.00元）

   自定义：类型框中（；；；是隐藏的意思，yyyy/mm/dd(日期设置)，dd-mmm(英文月份简写)-yyyy，aaa（一到日），aaaa（星期一到星期日）。

2. 数字格式-文本

   Excel中数字分为两类：一类有大小多少的含义，可加减乘除；另一类没有大小和多少的含义，可左右相连。

   Excel中数值有15位精确度。

   身份证号：选中一列–设置单元格格式–文本格式（文本数字左上角带绿箭头且靠左对齐）

   文本不能通过直接设置单元格格式直接转换。

   一组数字相加为零，则可能为本文，选中后点左上角黄色三角！号–转换为数字。

   一列数字不知是否有文本：在一空格中写数字1—复制该格–选中这列数字后右键—选择性复制—乘（让这列数字全部乘以1，excel强制转换文本为数值）

六、分列工具

【文本格式与常规格式可以用分列转换，其他格式也可以用分列更改】

txt文档数据复制到excel：选中A1单元格直接粘贴–选中A列–数据选项卡—分列—分隔符号（注意中文逗号和英文逗号，选对分隔符后预览选定数据中会分好列）

把文本格式的日期改为数值型：数据—分列–完成

七、查找与替换

查找与替换：编辑–查找

整个单元格都是我们要查找的内容才替换：编辑—查找—替换–单元格匹配

模糊查找：张*（星号是通配符：表示任何多个值）；张英文问号：表示一个字符）；原本就带问号和星号的内容，查找张_※或者张~让后面的字符不生效）

查找快捷键：command+F

定位快捷键：control+G

八、定位工具

1. 通过名称框（左上角）定位单元格区域位置

   A10000 回车（定位到A10000）

   A9000：B10000 回车 （选中A9000到B10000）

   9000:10000 回车（选中9000行到10000行）

   定义名称：选中区域，在名称框输入自己定义的名称（取名只能用中文或单词，如：常用区域）

2. 定位：编辑—查找–转到—定位条件（定位就是把这一类单元格选中）

a. 批注（单元格右上角红三角）

选中单元格–右键–插入批注

选中单元格—右键–显示/隐藏批注/编辑批注

显示所有批注：审阅选项卡-显示所有批注

设置批注格式：选中单元格右键–显示批注—上方开始标签—格式–设置批注格式

b. 把有批注的单元格设置为红色填充：编辑—查找—转到—定位条件—批注—确定–更改背景填充色

c. 把有公式的单元格设置为红色填充：编辑—查找—转到—定位条件—公式—确定–更改背景填充色

d. 填充解除单元格合并后遗留的空白单元格：

选中合并的单元格–合并后居中–选中区域–编辑—查找—转到—定位条件–空值—确定—输入=按键盘向上箭头 –control+回车

批量填充单元格：选中一个区域–其中一个单元格填值—control+回车，则区域内所有单元格都为该值

e. 批量删除图片（网页数据拷到excel会带过来很多图片）：

编辑—查找—转到—定位条件—对象—确定–delete

九、排序

开始选项卡–排序和筛选

数据选项卡–排序、筛选、高级筛选

1. 简单排序

   任选排序依据的区域的一个单元格—排序和筛选—降序

2. 多条件排序

   排序和筛选–自定义排序—输入要排序的依据（后面的是次要依据，前面的是主要依据）

3. 若只能定义一个主要关键字时，有多个条件：

   先按次要关键词排序，再按主要关键词排序（倒着来）

4. 按颜色排序

   排序和筛选–自定义排序–排序依据（单元格颜色）

5. 自定义排序（汉字升降序排序会按拼音首字母排序）

   排序和筛选–自定义排序—顺序–自定义列表

6. 工资条（利用排序插入行）

   a. 最后一列添加顺序序列：0，1，2，3…… （第一行输入0，单元格右下角按住control下拉拖出顺序序列）

b. 需要插入的行最后一列添加顺序序列：1.5，2.5，3.5……（第一行输入1.5，第二行输入2.5，选中这两个单元格右下角按住control下拉拖出顺序序列）

c. 点选最后一列的任意一格–排序和筛选—升序

1. 打印时每一页都有顶端表头

   页面布局标签–页面设置–工作表–顶端标题行选中第一行

十、筛选

1. 筛选

   排序和筛选—筛选–表头下拉三角–选择筛选条件

   若复制粘贴后与未筛选一样：编辑—查找—转到—定位条件—仅可见单元格–再复制粘贴

2. 高级筛选

   筛选不重复数据：数据选项卡—筛选旁边的高级–选择列表区域–选择不重复的记录

   列表区域选区域快捷键：点选A1单元格–control+shift+按键盘向右向下箭头。

   条件区域：在表格空白区域写上条件的表头和对应的条件。

   两条件为或的关系：条件区域中两条件不在一行（两条件同行表示且）

   【或关系条件区域举例】

   部门 发生额

   财务部

   ＞3000

3. 若条件区域中带公式/变量会出错，不写表头或写错表头可以。

十一、分类汇总

1. 认识分类汇总

   数据选项卡–分类汇总—分类字段（按什么分类），选定汇总项（把什么汇总），汇总方式（怎么汇总）

   使用分类汇总前必须先排序，把相同值分组。

   左侧按钮1、2、3用来查看分类汇总的结果。

   删除分类汇总：选中数据–分类汇总—全部删除。

2. 分类汇总嵌套

   分类汇总两次，第二次分类汇总时取消勾选替换当前分类汇总。

3. 复制分类汇总的结果区域

   编辑—查找—转到—定位条件—仅可见单元格（alt+;）

4. 使用分类汇总批量合并内容相同的单元格

   先合并旁边的单元格，把合并单元格的格式粘贴过来（选择性粘贴）或者用格式刷。

   先排序–数据选项卡–分类汇总–所属区域分类把所属区域汇总按计数方式–选中合并的单元格–定位条件–空值–合并单元格–数据选项卡删去分类汇总–复制合并单元格的格式选择性粘贴为格式。

十二、设置数据有效性

1. 设置整数数据有效性

   选中一列–数据选项卡–数据验证—设置–允许：整数

2. 设置文本长度数据有效性

   选中一列–数据选项卡–数据验证—设置–允许：文本长度

3. 设置序列数据有效性

   选中一列–数据选项卡–数据验证—设置—允许：序列–填写来源（英文逗号分隔开）–生产了下拉框

   二级下拉框：二级关联下拉框–函数

   让值无法修改：选中区域–数据选项卡–数据验证—设置—允许：自定义—公式：0

   修改出错警告：数据选项卡–数据验证—出错警告–样式

4. 数据有效性的其他设置

   在不同列单元格中自动切换输入法：数据选项卡—数据验证–输入法模式

十三、数据透视表（重点）

【一种数据汇总统计工具】

流水账表：一列叫一个字段，一行叫一个记录。

1. 创建数据透视表

   a. 选中任意一个单元格—插入选项卡–数据透视表—确定

   b. 在数据透视表分析中右键–数据透视表选项–经典数据透视表布局

   c. 右侧数据透视表字段—字段名拖入下方对应的框中（行、列、值）

2. 更改数据透视表汇总方式：双击左上角“计数项：求和项”–汇总方式

   到数据是怎么得来的：双击透视表中某个数据

3. 数据透视表中的组合

   数据透视表中的日期组合为季度：点中一个日期单元格–右键—组合—按季度汇总

   数据透视表中的数值组合：点中一个数值单元格–右键—组合—起始于（0）、终止于（好除的最大值）、方式（步长）

4. 汇总多列数据

   去掉分类汇总：双击员工姓名–将分类汇总改为无

   如果值字段有多个但上下排列了：可以拉第一个的名称到第二个的栏目框，就可以左右显示

   修改数据透视表字段：双击表头（求和项：生产数量）—改为平均值/最大值/最小值

   修改表头名称：在上方编辑框中修改

   美化数据透视表样式：上方设计–选择模板

5. 在透视表中使用计算

   a. 点击透视表中任一单元格–数据透视表分析—字段、项目和集–计算字段–eg：名称：利润率、公式： =（合同金额-成本）/合同金额 【字段是在下方双击选的，不是手打的】

   b. 选中区域—右键–设置单元格格式—数字–百分比

   c. 怎样消除错误值：数据透视表中任意位置右键–数据透视表选项—显示–勾选对于错误值，显示为：

（在数据透视表中加入公式计算一个字段，建议在表内加入）

1. 怎样生成数量众多的工作表

   建数据透视表–将需要分页显示的项目拉入筛选器和值–数据透视表分析—选项右侧小三角形–显示报表筛选页–选定要显示的报表筛选页字段—确定

   要删除因此产生的数据透视表：按住shift键选中所有表–在第一个工作表中，复制空白单元格，覆盖数据表，这样就会清除所有数据透视表

来源：番大茄子