数据分析与Excel_excel数据验证颜色填充

数据分析与Excel 1

1 数据分析

• 随着互联网+的不断深入，网络中的数据量飞速膨胀。身处信息的海洋，在这个数据高速爆发的时代，企业想要快速发展，不能只简单地靠历史的经验，想要快速成功，就要认清数据、企业、社会三者之间的联系，因此就出现了数据分析这个职业。尤其是在以数据驱动为首的百度、美团、以及京东等企业中，数据分析都扮演了重要的角色。
• 现在的传统企业也慢慢意识到了数据驱动的重要性，大部分企业也都在学着用数据分析解决问题，或者提升业绩，这类公司在进行数字化转型。比如：银行企业的中国银联，交通银行的东方航空，通信行业的移动、联通、电信等都在通过数据进行探索。

1.1 什么是数据分析

• 数据分析是通过技术手段，对业务进行流程梳理、指标监控、问题诊断以及效果评估，它的目的是对过去发生的现象进行评估和分析，并在这个基础上对未来事物的发生和发展做出预期分析处理，以此指导未来的一些关键性决策。

• 随着数据量的不断增长，数据处理以及信息挖掘技术也在迅速发展，人们对于数据的处理也不仅仅是数据存储以及信息的简单探索，而是结合一些模型的应用进一步分析。虽然现在出现了大量数据分析技术，如：Python、R等编程语言以及MySQL、Hadoop等数据存储技术，但是Excel凭借其操作简单、灵活以及宽广的覆盖面，在数据分析中占据着一席之地。

1.2 数据分析流程

数据分析流程可以概括为：数据理解、提取数据、数据清洗、数据分析、数据可视化、撰写报告。

2 Excel的初步认识

2.1 对Excel的了解

• 表格能够收集信息。
• 表格能够进行求和、求均值等统计学的基本运算。
• 一堆写不明白的函数。
• 计算机二级office科目需要考。
• 会计专用工具。

2.2 什么是excel？

• Excel是Office办公软件中的组件之一。
• Excel专长于对表格中的数据进行计算和统计管理，通常用于财务或其他数据管理的表格制作。
• 同时Excel还有很好的可视化能力，可用于制作各种行业报告。

2.3 Excel文件后缀名

• . xls 和 . xlsx

. xls是以前的版本， 新版本的excel文件后缀名都是. xlsx。

• 为什么 . csv 不是？

  csv文件是最通用的一种文件格式，csv文件本本质上是文本文件，默认使用逗号作为分隔符。很多软件都兼容csv文件。

3 Excel的用法

3.1 创建与保护

3.1.1 创建工作簿、工作表、单元格

工作簿：我们常说的表格文件其实就是工作簿，创建工作簿，即创建一个Excel文件，

工作表：就是我们下方切换的N个活动页，创建一个工作簿的时候会自动的创建一个工作表。在一个工作簿中可以按照需求创建多个工作表，对工作表的名字按照表格的内容进行重命名。工作表支持增删改，也可移动工作表的位置。对工作表中的内容进行编辑，即对单元格进行编辑。

单元格：每个工作表是由单元格构成的，可以对单元格的内容进行编辑。单元格就是可以看见的每一个方格。

3.1.2 保护功能

（1）保护工作簿：防止其他用户对工作簿的结构进行更改，如移动、删除或者添加工作表

（2）保护工作表：通过限制其他用户的编辑能力来防止他们进行不需要的更改。

(3) 保护单元格： 防止数据被篡改。

3.1.3 操作方法

(1) 保护单元格：选中单元格单元格 --> 开始 --> 格式 —> 锁定单元格、保护工作表

• 保护单元格的时候，先不要保护工作表，如果已经保护了工作表，必须先解除对工作表的保护，才可以对单元格的内容进行锁定，锁定后，再保护工作表，完成这些操作后，工作表中的内容不可修改， 只能解除后，才可以。

(2) 保护工作表：在指定工作表点击鼠标右键 --> 保护工作表

(3) 保护工作簿：审阅 —> 保护工作簿 ，

3.2 快速输入

1. 填充柄的使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xLHt7K13-1661255093880)(C:\Users\Lenovo\Desktop\填充柄的使用.png)]

• 单元格的样式就是单元格填充的颜色，字体的样式，大小。

• 案例展示
  

• 注意：填充数字的时候，点击鼠标左键，默认是将第一个表格的内容和格式全部复制下来，选中单元格，将鼠标放到单元格右下角后点击鼠标右键，拖到最后，就可以在填充柄中选择想要的填充的内容格式。年月日同样也是一样的操作，点击鼠标右键选择，工作日填充就是对应的填充的时间都是工作日的时间，跳过了周六，周日.

• 注意等差数列，等比数列必须要填好前两个单元格的内容，相当于告知等差数列的首项，公差，等比数列的首项，公比，后面的单元格内容就可以依次填充下去。

2. 快速填充

• 操作步骤：从数据后方相邻单元格输入内容，选中单元格，鼠标放置到单元格右下角后向下拖拽，选择快速填充。即可按照输入的内容，按照这个规律对下方的单元格进行填充。

• 案例展示
  

3. 自定义列表填充

• 自定义列表填充就是提前编辑好填充内容，填充的时候，选择自定义列表中编辑好的内容，按照该内容进行填充。注意 : 要提前编辑好填充内容。

• 方法：文件---->选项----->高级---->编辑自定义列表

  注意：自定义的内容一行写一个后换行

• 自定义设置展示
  

• 案例展示

写好第一行的内容，点击鼠标左键，向下拖拽即可填充内容。

4. 多个不连续单元格填入相同数据

• 第一步：ctrl +鼠标左键（一直按着ctrl键，鼠标左键点击选中要写入数据的区域）选中单元格，

• 第二步：填入数据（填数据的时候，可以对单元格的格式和内容都进行编辑，填入数据的时候，就在鼠标所在的单元格输入内容，不要点击其他）

• 第三步：ctrl +Enter 补全数据（第二步完成后就这按ctrl+enter就可以完成将选中的区域都填充成相同的数据）

• 案例展示
  

5 . 多个不连续单元格填充不同数据

方法：

• 第一步：先选中下方数据，

• 第二步：选中空的单元格---->按ctrl+G，点击定位条件，选中空值，

• 第三步：单元格引用----->在函数编辑区域写=后，再在下方选中的空白单元上方点击第一个数据，就可以显示第一个数据的位置，然后按ctrl+Enter.就可以填充多个不连续的单元格数据。

• 案例展示
  

6. 文本记忆输入

注意：必须连续单元格输入才会生效

• 第一步：按Alt+向下箭头
• 第二步：直接输入内容，根据提示选择回车

3.3 数据验证

数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉菜单列表，方便用户选择输入。

1. 数据有效性

选中单元格---->数据---->数据验证---->设置---->允许：序列---->来源

• 案例展示
  

2. 圈释无效数据

• 步骤：选中数据区域---->点击菜单栏数据----->选择数据验证----->根据数据类型选择验证的数据---->点击圈释无效数据------>取消圈释可以点击清除验证标识圈。
• 例题展示：
  

3.4 定位条件

• 输入步骤：开始----->查找和选择------>定位条件（Ctrl+G或者F5）（或者快捷键ctrl+f,查找到数据后选择替换，填写替换成的内容，后点击全部替换）.

• 要求：使用定位条件选择区域中空单元格，填入100，按组合键Ctrl+Enter填充

3.5 单元格查找和替换

方法：Ctrl+F查找，填写查找的定位条件后，ctrl+G填写定位条件。点击全部替换

3.6 快速选择数据

• 操作：按住ctrl+shift+方向键
• 优点：如果起始行或者起始列出现空值会出现选择不全，再次点击下键就可以选中，点击左右键，就可以选中旁边的几列。

3.7 选择性粘贴

操作步骤：
（ 1） 输入需要增加的比例。
（2） 将需要变化的数据提前复制一份。
（3） 复制比例，选中数据，单击右键，选择性粘贴打开，选择正确的选项即可。

• 案例展示
  

3.8 冻结单元格

• 操作步骤：视图 --> 冻结窗格
• 注意：如果要冻结前N行，要选到第N+1行再冻结，冷冻结同理。
• 如果点击第n行m列的单元格，点击视图里面的冻结窗口，那么就会冻结前n-1行和前m-1列。

3.9 导入外部数据

Excel支持从外部导入数据，导入途径包含：本地各类存储数据的文件、数据库、网站等。

• 操作方式：数据 –> 获取数据 –> 选择对应功能选项

3.10 导入数据测试页

• 从csv文件导入数据，直接打开csv文件测试，注意要选择字符编码格式。（选utf-8）
• 也可以从导入网页中导入数据。

3.11 Excel数据类型

有5种数据类型，分别是

• 文本：姓名、性别、住址、及不要计算的数值，如手机号、银行卡等。

• 数值：整数、小数、科学计数等需要计算的数字

• 日期和时间：如：2022/7/8

• 错误值: 错误值通常是在使用公式时，由于种种原因没能返回需要的结果，而是返回了一串不同字符组成的错误代码。常见错误值：

  • #value！---->键入公式的方式错误。或者引用的单元格错误。
  • #DIV/0! ---->这个错误值是说数字被0除了，因为0不能当被除数。
  • #NAME? ---->在公式中是因为公式名称中出现拼写错误
  • #N/A! ---->公式找不到引用的值
  • #REF! ---->公式引用无效单元格时将显示 #REF! 错误。 当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。
  • #NUM! ---->公式或函数中包含无效数值时
  • ###### ---->如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#####
  • #NULL! ----> 如果在公式中使用了不正确的区域运算符，或者在区域引用之间使用了交叉运算符（空格字符）来指定不相交的两个区域的交集，将显示此错误。

• 如何查看Excel数据类型：type()

  • 数字=1
    文本=2
    逻辑值=4
    误差值=16
    数组=64
    复合数据=128

3.12 单元格格式

对整个单元格以及单元格中的数据应用格式：

• 方式一：更改单元格中文本格式，可以将单元格中文本格式改为数值、货币、时间等Excel定义格式，也可以改为按照一定规则设定的自定义格式。

• 方式二：将单元格想象成图片框，框中的图片就是数据。 设置单元格的格式可能包括添加边框、添加颜色或阴影，或更改单元格的大小和样式。

（1）自定义单元格格式

• 设置单元格格式在excel设置单元格格式在Excel中来说应该是一种司空见惯的操作，几乎任何接触到Excel的人都会用到这一功能，但是能把单元格格式（特别是自定义格式）用好、用高级的人却凤毛麟角。

• 学会Excel自定义格式，可以完成一些看起来非常神奇、非常高级的效果，如：

  • 条件格式

  • 巧加单位

  • 输入提速

（2）调出自定义单元格格式对话框

方式1：选择要设置格式的区域---->开始---->单元格---->格式----->设置单元格格式

方式2：选择要设置格式的区域---->鼠标右键----->点击设置单元格格式

(3）设置单元格格式认识

• 在对话框的第一个分区[数字]中，左侧的[分类]列表中有常规、数值、货币等项目，这些项目时预设的格式，根据提示很容易掌握。

• 重点要学习最后一项[自定义]格式，选择[自定义]格式后，在右侧的[类型]编辑框，可以输入自定义格式代码。同时，可以看到下面有很多系统预设的代码。我们可以直接使用这些预设的代码，也可以根据代码的规则，写出更加符合实际需求的代码。

(4) 格式及占位符

• G/通用格式 ：以常规的数字显示,相当于"分类"列表中的"常规"选项。 代码：G/通用格式。 效果：52 显示为 52；52.5显示为 52.5

• #数字占位符 ： 只显示有意义的0，不显示无意义的0，小数点后数字大于‘#’ 的数量，则按照“#”的位数四舍五入，

  • 代码：###.##效果

    181.2显示为181.2,

    18.4328显示18.43.

    #,##0 效果—>1556.122显示1,556

• 0 数字占位符 ： 如果单元格的内容大于占位符，则显示实际数字，如果小于占位符的数量，则用0补足，

  • 代码：00000.效果：

    5201314 显示为 5201314；

    521 显示为 00521

    解析：5201314位数大于占位符的位数，因此显示实际数字；520的位数小于占位符位数，因此用0补足）。

    用0补足的情况，不能改变原有数字的大小，因此整数部分0补足在前面，小数部分，0补足在后面。

    • 图片展示：
      
      

（5）？数字占位符

在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐；也可以用于具有不同位数的分数。

• 如图
  +

(6) @ 文本占位符。

如果只使用单个@，作用是引用原始文本；如果使用多个@, 则可以重复文本。

• 示例@*- ，123显示为123------------------

（7） * 重复下一次字符，直到充满列宽

（8）" " 用文本显示接下来的字符，

“文本”：用文本格式显示双引号里面的文本，输入后会自动变为双引号表达。

• 展示：

（9）[ ] 条件格式代码

• 可以将条件或者颜色（颜色代码也是一种条件）写入[]，从而实现自定义条件。

• 默认情况下自定义格式的四个区段的条件是固定的，即分别为"正数格式;负数格式;零值格式;文本格式",使用[ ]可以自行设置每个区段的条件）

• 可以根据单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他“。

• 格式：

  区段1；区段2;区段3;区段4

  区段1：[条件1]要返回的值

  区段2：[条件2]要返回的值

  不满足条件1、2要返回的值

  文本格式

（10） 颜色

• 用指定的颜色显示字符。

• 可有八种颜色可选：红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

• 代码：[蓝色];[红色];[黄色];[绿色]

• 效果：显示结果为正数为蓝色, 负数显示红色 , 零显示黄色 , 文本则显示为绿色。

• 颜色代码的另一种方式，[颜色N]：是调用调色板中颜色, N 是 [1,56] 之间的整数。

• 代码：[颜色1];[颜色2];[颜色3];[颜色4] 效果：显示结果为正数为调色板上第1种颜色……。

（11） 时间和日期代码

• “YYYY"或"YY”：按四位（1900_{9999）或两位（00}99）显示年

• “DD"或"D”：以两位（01~31）或一位（1-31）来表示天。

• “DD"或"D”：以两位（01~31）或一位（1-31）来表示天。

（12）__ 在数字格式中创建N个字符宽的空格

(13) 空 表示：隐藏

以上这些代码可以进行组合使用，从而创建出更复杂的自定义格式。

3.13 条件格式

• 条件格式设置:选中单元格；开始；条件格式

  1. 借助“数据条”直观分析数据。

  **2. **使用“色阶”创建热图。

  3. 借助“图标集”为数据分类。

  4. 条件格式的新建、删除、编辑。

  使用条件格式，可以让数据更为直观的呈现在眼前，便于理解数据信息。

3.14 添加辅助列

题意：让一个有数据的表格的每一行数据都空开一行。

方法：

• 先在最前面插入一列，给列编号，按照等差数列进行编辑，首项为1，公差为2的等差数列，进行排序，知道表示完所有数据。

• 在最后面继续插入等差数列，首项为2，公差为2的等差数列。

• 选中所有数据，排序，按照第一列的数字进行自定义排序，排好后，就会插入空的一行，

• 删除第一列，得到想要的结果。

3.15 拆分

当数据都排在一起，无法直观的看清数据，需要对数据进行拆分。

• 方法一：数据，分列

  操作步骤：先选中要拆分的数据----->选中数据分列----->按照分列方式选取分割符号----->选取填充区域后点击完成。

• 方法二：使用快速填充，先手动收入第一行数据，然后选中数据，单击鼠标右键，选择快速填充，就可以根据第一行填的规律，填充好剩下的数据。

• 展示：