【数据分析】——EXCEL

EXCEL学习大纲

一、基本操作

部分单元格编辑

选中 需要编辑的 单元格，右键选择 设置单元格格式，选择 最右边的 选项卡 保护，把 锁定前面默

认的 勾 去掉

设置 保护 当前工作表

填充

• 文字+数字
  • 数字部分递增
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状，左键下拉
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状，双击
  • 内容不变填充：
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状。 按住 ctrl + 左键下拉
• 文字
  • 递增：
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状。 按住 ctrl + 左键下拉
  • 文字不变
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状，左键下拉
    • 把鼠标定位在第一个单元格右下方，变成黑色 + 形状，双击
• 等差数列
  • 首先需要 在 前面两个单元格设置好一个 等差数列的 数字，鼠标定位到 第二个单元格右下角，变成 + 形状。 下拉或者双击

利用自定义列表填充

• 比如 第一个单元格 是 a，下拉显示 a,b,c,d,e,f,g
  • 方法：文件--》选项--》高级--》常规---》编辑自定义列表--》默认选中新序列--》右边输入序列中 每一行输入一个内容

在多个不连续的单元格中填充相同的数据

• ctrl + 左键，选中多个不连续的单元格
• 直接输入要填充的内容
• ctrl + enter 执行

文本记忆输入

• 场景：前面的单元格输入了内容，后面的单元格想从前面的内容中选择。那么可以 点击 右键--》 从下拉列表中选择，就可以 选择 前面单元格 输入的 内容

数据有效性

• 在 单元格中 先定义要 填充的 数据
• 在某个单元格中，需要在 之前定义的数据中选择，内容也可以自己输入。但是单元格中的值一定要是 刚才定义的数据。 方法：选择 菜单栏的 数据---》数据验证---》数据验证--》在 允许条件中选择 序列--》点击 来源 右边的 标识--》选中 刚才 定义的 数据---》选中之后 再把 标识 点击一下，点击确定

设置单元格的格式

• 通过 自定义数据格式 改变 数据 显示的样式，而不改变 原始的 类型
  • 比如把 1 通过自定义数据格式 改变成 0001。 方法：首先 打开 设置单元格格式的 弹框，然后选择 自定义， 在 右边 类型 下面 输入 格式化的 内容 0000，那么 1 的显示样式就是 0001.
    • 注意： 0关键字的用法，如果 0 的个数 小于或等于 原始 数字的个数，那么按照原始的数字进行显示。 如果 0的 个数 大于 原始 数字的 格式，那么 原始数字的个数前面 会用0 来补充
  • 把1变成 0001，也可以 直接 把 1的单元格格式 变成 文本 格式
• 自定义格式中的 . 代表 小数点
• 自定义格式中的 % 表示把 数字变成 百分比 显示
• 自定义格式中的 , 表示 千分位 。 格式化代码 0,0
• 自定义格式中的 ! 表示 不转义 特殊字符，比如直接显示 . ，那么需要使用 !.
• 自定义格式中的 @ 表示 显示 原样的 字符

单元格 自定义数据格式

• 正数格式; 负数格式; 零格式; 文本格式
  • 比如：正数显示a，负数显示b，0显示c，文本显示d。格式化代码： "a";"b";"c";"d"
  • 比如：正数文字显示蓝色，负数文字显示红色，0不显示。 格式化代码： [蓝色];[红色];
• 通过判断条件设置
  • 比如：大于5用红色，小于5用蓝色，等于5用绿色。 格式化 代码： [>5][红色];[<5][蓝色]; [绿色]

数值类型通过 text 方法 可以转化成 文本类型

• 方法使用： =text(可以是数值或者是单元格的引用，"自定义格式")
  • 比如把 1 变成 0001， 写法: =text(1, "0000")

不标准的日期 转成 标准的日期

• 文本格式的日期，需要 把它转成 数值 类型
  • 比如 文本类型的 2019/1/1, 先通过 =value('2019/1/1') 转成 数值类型，然后就可以 转成 年月日的 日期形式
• 不标准的日期，虽然是日期格式，但是 中间是 . 相连的 不标准形式
  • 比如： 2019.01.01， 需要 先 通过 替换 功能，把 . 替换 成 / ，然后再转
• 不标准的日期，100102，需要 用 text 方法转成 标准的 文本类型 日期。 再 转换成 数值类型，代码： =--text('100102', "2000!/00!/00") , 然后再转
• 不标准日期， 20190101，可以使用 数据的 分裂功能，点击 数据 --》 分裂--》最后一步 选择 日期 YMD 格式

在 原始的一列数据 上面 乘以 1.25 ，需要使用 选择性粘贴 -- 乘

• 先在 一个 空的 单元格 上面 输入 1.25
• ctrl + c 选中 1.25
• 选择 要相乘的 那一列 数据
• 点击 菜单栏 开始--》最左边 粘贴功能--》选择性粘贴---》乘

查找特定值和特定单元格格式的内容

• 查找和替换---》替换
• 在查找内容中输入查找的值
• 点击 选项，在 查找内容后面的 格式中 选择 从单元格选择格式，变成 吸管状后 吸取 要查找的单元格

在 数据验证中 选择多行多列的值

• 创建名称管理器：公式--》名称管理器--》新建--》输入名称 data ---》引用位置 先选择单行或者单列
• 点击 需要创建数据验证的 单元格， 点击 数据---》数据验证--》数据验证--》允许中 选择 序列--》在来源数 输入 =data
• 在 修改 之前创建的 名称管理器 data，把 单行单列 修改 为 数据的多行多列的全部区域

二、Excel自定义格式

1、自定义格式的结构

方式一：

代码结构组成代码码分为四个部分，中间用 “ ； ” 号分隔，具体如下：

正数格式；负数格式；零格式；文本格式

两个代码部分，则第一部分用于正数和零，第二部分用于负数

一个代码部分，则该部分将用于所有数字

要跳过某一代码部分，然后在其后面包含一个代码部分，则必须为要跳过的部分包含结束分号。

方式二：

特定条件

大于条件值；小于条件值；等于条件值；文本

例如 : [ 红色 ][<=100] ; [ 蓝色 ][>100]

方式三：

特定条件

条件值 1 ；条件值 2 ；同时不满足条件值 1 ， 2 ；文本

2、使用方法

1. 设置单元格格式 --> 自定

2. 使用 TEXT(value, format text

例： =TEXT(1234.456,"00.00") 显示结果为 1234.46

2、各参数的含义

自定义格式类似正则表达式，用一些特定的符号可以表达特定含义。

1). “G/ 通用格式 ” ：以常规的数字显示，默认值。

例：代码： “G/ 通用格式 ” 。 10 显示为 10 ； 10.1 显示为 10.1 。

2). 0 ：显示数字，如果数字位数少于格式中的零的个数，则显示无意义的零。

例：代码： “00000” 。 1234567 显示为 1234567 ； 123 显示为 00123

例：代码： “00.000” 。 100.14 显示为 100.140 ； 1.1 显示为 01.100

3). # ：只显示有效数字而不显示无意义的零

4). ？：为无意义的零在小数点两边添加空格，以便使小数点对齐。

5). ，：显示千位分隔符或者将数字以千倍显示。

6). "." ：小数点。如果外加双引号则为字符。

7). “%” ：百分比。

8). “/” ：显示下一个字符。

和 “””” 用途相同都是显示输入的文本，且输入后会自动转变为双引号表达。不同的 “/” 是显后面的文本，双引号是显示双引中间的文本。

9). “*” ：重复下一次字符，直到充满列宽。

文本的重复显示要使用 @

例：代码： “@*-” 。 “ABC” 显示为 “ABC-----------------”

数字的显示可以使用 # 或者 0 或者 ?

例：代码： “#*-” 。 “123” 显示为 “123-----------------”

10). “_” （下划线）：留下一个和下一个字符同等宽度的空格

例：

正数后面留一个与）等宽的空格，负数不留空格，这样能保证整数和复数对齐

$#,##0.00_) 表示 1234.567 为 $1234.56

($#,##0.00) 表示 -1234.678 为 ($1234.67)

代码： $#,##0.00_);($#,##0.00)

11). “ 文本 ” ：显示双引号中的文本。

例：代码： " 中国 @" 显示为 “ 中国 xxx”

12). [ 颜色 ] ：用指定的颜色显示字符。

常用可选色：红色、黑色、黄色，绿色、白色、蓝色和洋红。

例：代码： “[ 绿色 ];[ 红色 ];[ 黑色 ];[ 蓝色 ]” 。显示结果为正数为率色，负数显示红色，零显示黑色，文本则显示为蓝色

13). [ 条件 ] ：可以单元格内容判断后再设置格式。

条件格式化只限于使用三个条件，其中两个条件是明确的，另一个是 “ 所有的其他 ” 。条件要放到方括号中。必须进行简单的比较。

显示结果是单元格数值大于零显示正数，小于零显示 “ 负数 ” ，其他情况显示 " 零 " ，非数字类型显示 " 文本 " 。

例：代码： [>0] 正数 ;[<0] 负数 ; 零 ; 文本

或：代码：整数 [>0]; 负数 [<0]; 零 ; 文本

14). “ ！ ” ：显示 “"” 。

由于引号是代码常用的符号。在单元格中是无法用 """ 来显示出来 “"” 。要想显示出来，须在前加入 “ ！ ”

例：代码： “#!"” 。 “10” 显示 “10"”

例：代码： “#!"!"” 。 “10” 显示 “10""”

15 ） 日期和时间

a. 日期设置

符合	含义
m	将月显示为不带前导零的数字
mm	根据需要将月显示为带前导零的数字
mmm	将月显示为缩写形式（ Jan 到 Dec ）
mmmm	将月显示为完整名称（ January 到 December ）
mmmmm	将月显示为单个字母（J 到 D ）
d	将日显示为不带前导零的数字
dd	根据需要将日显示为带前导零的数字
ddd	将日显示为缩写形式（ Sun 到 Sat ）
dddd	将日显示为完整名称（ Sunday 到 Saturday ）
yy	将年显示为两位数字
yyyy	将年显示为四位数字

例：代码： “YYYY-MM-DD” 。 2005 年 1 月 10 日显示为： “2005-01-10”

例：代码： “YY-M-D” 。 2005 年 10 月 10 日显示为： “05-1-10”

b. 星期设置

“AAAA” ：日期显示为星期。

c. 时间设置

“H” 或 “HH” ：以一位（ 0~23 ）或两位（ 01~23 ）显示小时

“M” 或 “MM” ：以一位（ 0~59 ）或两位（ 01~59 ）显示分钟

“S” 或 “SS” ：以一位（ 0~59 ）或两位（ 01~59 ）显示秒

[H] 或 [M] 或 [SS] ：显示大于 24 小时的小时或显示大于 60 的分或秒。

例：代码： “HH:MM:SS” 。 “23:1:15” 显示为 “23:01:15”

3、关于特殊数字的显示

例：代码： “[DBNum1]” 。 “1232” 显示为 “ 一千二百三十二 ”

代码： “13” 显示为 “ 壹拾叁 ”

例：“123” 显示为 “1 百 2 十 3”

练习Excel文件：Excel练手数据文件资源-CSDN文库