excel处理
excel中默认的世界第一天就是1900.1.1
快捷键
shift + F8 多选( = ctrl+鼠标左键)
ctrl + shift +方向 可以快速选中这一行(列)的左(右)边的连续文本(空格) ,类似于同一行(列)的全选
ctrl + f 查找和替换
ctrl + h 替换
ctrl + e 快速填充
ctrl + g 定位
如果有大量的市区,要去掉一个字!市,只需要吧这个字替换为空格
常规 就是没有格式
会计专用 数字0会用 符号- 表示
1保护功能
1.1工作簿的加密
一个个的excel文件我们叫他们工作簿
操作为: 文件>信息>保护工作簿>用密码进行加密
(去掉密码就是吧输入的密码删掉…没有删除密码的按钮)
1.2工作表的加密
(基本上不用) (工作表就是excel文件(工作簿)中的sheet1,sheet2…)
如果你要保护工作表,就得点击 审阅 > 保护工作簿 > 输入密码 不需要密码就是不输入密码
但是还是可以操作数据,只不过不能点击工作表了…只保护了结构
删除密码就是再次点击保护工作簿
1.3单元格的保护
1.保护选中的单元格(不允许修改选中部分)
先crtl + a 选中全部 ,然后 开始 > 字体|对齐方式|数字 > 保护 >取消锁定(默认锁定)
然后去 审阅 > 保护工作表
就可以锁定选中的单元格了
2.保护全部单元格
开始 > 字体|对齐方式|数字 > 保护 > 锁定(默认锁定) 然后去 审阅 > 保护工作表
撤销:要先撤销保护工作表,不然没办法点击 字体|对齐方式|数字
2 快速输入数据
2.1自定义列表
文件 > 选项 > 高级 > (往下拉) > 编辑自定义列表 > 新序列 > 添加(注意使用英文逗号) > 确定 > 确定
2.2填充柄
1.文字+数字 点击框框右下角往下拉,是添加 ,比如cyu001 下啦就是002 003 004…
2.数字 往下拉,是重复的
想让他添加,就按住ctrl然后在下啦
3.等差,等比啥的
第一种 : 点击单元格鼠标右键右键右键，下拉，选择序列，输入步长值和终止值(最大值,可为零)啥的
第二种 :选中部分 开始 > 编辑一栏 > 填充 > 序列
2.3 填充柄 + 快捷菜单
常用
1.工作日填充 点击单元格鼠标右键右键右键，下拉，序列 > 日期 > 工作日
or 开始 > 编辑一栏 > 填充 > 序列
2.以月填充 和上面一样,不过是在日期中,选则月
3.以年填充 和上面一样,不过是在日期中,选则月
2.4 多个不连续单元格同数据
多用于填充众数,中位数啥的
先输入值,然后选中所有需要填充的,再按crtl + 回车
2.5 文本记忆式输入法
鸡肋
在同一列表格中 第一个值,第二个值,第三个值就可以使用 右键 > 从下拉列表中选择 , 但是他会把上面所有的东西(第一个值,第二个值,如果还有其他值)都排列在这里面
2.6 数字验证方法
下拉选框
方法1：数据 > 数据验证 > 数据验证 > 允许 > 序列 > 来源 > 输入或者选中(你想要选择的值)(单行or单列)
想要复制数值
方法2：数据 > 数据验证 > 数据验证 > 允许 > 序列 > 来源 > 选中
多行变一行
数据验证只能使用单行或者单列的数据
数据验证默认情况下是不允许使用多行多列的
这样做才可以用多行多列
- 案例：多行多列放在一个下拉选框
- 公式 > 名称管理器 > 新建 > 名字 QF > (引用位置)单列or单行（特殊注意） > 数据 > 数据验证 > 序列 > 输入 =QF > 公式 > 名称管理器- > 编辑 > 全选中 > 完成
步骤如下:1.创建一个名称管理器(单行or单列)
2.创建数据验证并并引用名称管理器
3.重新编辑名称管理器的数据范围
!!!不能直接选中多行多列,必须先是单行单列,第二次编辑才能选中多行多列…
2.7数字自定义格式
输入1按回车就变成了男 输入0按回车就变成了女
开始 > 字体/对齐方式/数字 > 数字 > 自定义 > 类型 > 输入 [=1]“男”; [=0]“女”
注意一次性不要输入太多的值,会报错
注意:只能有2个值，0或者1 (虽然现在可以使用2 . 3 .4啥的,但是因为要写到数据库中,数据库中只使用0和1,所以我们才使用0和1)
英文符号
2.8函数输入(10-99)
注意 直接输入,不要按回车
在框框中输入 =INT(10+89*RAND())
绝对没输错,但是为啥不出结果呢
2.9同时填充多个工作表
连续的单元格使用shift
不连续的单元格使用ctrl 然后输入100,然后ctrl + enter
2.10巧妙的利用自动更正选项,快速录入数据
文件 > 选项 > 校对 > 自动更正选项 > 自动更正 > 添加
作用就是你输入 生日,就会自动变成19980311
3.导入数据
1.txt数据
数据 > 自其他来源 > 自文本 > 预览 > 分隔符 > 逗号 > 确定
2.导入Access数据库
数据 > 自其他来源 > 自文本 > 自Access数据库 > 分隔符 > 逗号 > 确定
4.excel的数据
4.1 数据类型
文本类型: 姓名,职务,描述,住址…
数值类型: 年龄 1,2,2,5,2,6,2
逻辑值类型 : Ture(1) ,False(0)
错误值类型:
[#####] 错误类型：超过单元格宽度，一般在手机、身份证、日期等长数字时会出现。。
[#DIV/0!] 错误类型：除数为0，或除数引用的单元格为空值。
[#NAME!] 错误类型：不能识别的文本或其他参数。
[#N/A] 错误类型：公式中没有可用数值，目标或参数缺失。
[#NULL] 错误类型：公式中的交集输入不正确
[#NUM!] 错误类型：无效数字值（无法接受的参数
[#REF!] 错误类型：引用地址失效 一般是删除了表格中个别单元格，造成公式引用地址失效。
[#VALUE!] 错误类型：参数类型错误 参数的数据格式错误，函数中使用的变量或参数类型错误。
4.2自定义数据格式
0代表数字, @代表文本 %就是在后面加个% 0.0 就是后面有一位小数 ()就是吧数字放在()内
0.0 就是
输入数据之后,自动添加一个 文本值,比如输入50000,然后就变成了 50000元每年
- 开始 > 字体/对齐方式/数字 > 数字 > 自定义 > 0"元/年" 整数
输入 第一 变成第一季度 - 开始 > 字体/对齐方式/数字 > 数字 > 自定义 > @“季度” 文本/字符串
数据检查 输入负数就是红色 - 开始 > 字体/对齐方式/数字 > 数字 > 自定义 > [绿色]0.0%;红色;[黑色]0.0;
绿色是12-12.0% 红色是12-(12.0%) 黑色是0-0
注意:数字格式最多可包含四个代码部分，各个部分用分号分隔。这些代码部分按先后顺序定义正 数、负数、零值和文本的格式。 <正数>;<负数>;<零>;<文本>
4.3 文本型数据转换为数值型数据
方法:使用六个公式之一转换, 如结果没有变化,把单元格格式设置为常规
注意:双击之后会出问题
转换之后看上去还是文本型数据,但是是可以使用函数计算的
- =A1*1
- =A1/1
- =A1+0
- =A1-0
- =–A1(减负运算)
- =VALUE(A1)
当目标是逻辑值False , ture的时候 - =A1*1
- =A1/1
- =A1+0
- =A1-0
- =–A1(减负运算)
- =N(A1) N函数
5.标准化规范化
5.1日期格式
2012/5/1
标准的日期格式是可以做计算的 加一减一
不规范日期一般有三种1. 2014.1.1 2.140101 3. 20140101
注意啦: 一个条件对应一个解决方法
-
2014.1.1
方法1.开始 > 查找和选择 > (选中文本)替换
方法2.方法为 ctrl + f > 替换 (吧.替换为 / ) ,这里会有出错的列,这个时候需要选中,在 开始 中, 吧常规改为短日期就可以了 -
140101
输入 =–TEXT(选中值,"#-00-00") 这个时候出现的值为 距离1900.1.1的天数,吧常规改为短日期就可以啦 ,表格中的其他值,这个时候还没有改变,只需要双击,已经改过了的框框的右下角的那个点就可以…具体咋回事我也不知道 -
20140101 固定宽度/日期格式 分列
先把要改变的文本复制过来, 然后 数据 > 分列 >下一步 > 下一步 > 日期 > 完成
5.2规范数据(用函数)
-
不规范的日期
1.1 把. 替换为 /
可以用ctrl + f 用替换更改为 /
不过还有一个方法 =SUBSTITUTE(目标 , “.”,"/") 注意全部使用双引号,不要使用单引号
1.2 分列的替换方法
=–TEXT(目标 , “#-00-00”) -
不规范时间
122040 十二点二十分钟四十秒
=–TEXT(目标,“00!:00!:00”)
| 不规范日期 | 应用函数 | 规范日期 |
|---|---|---|
| 1998.8.8 | SUBSTITUTE | 1998/8/8 |
| 95.8.8 | SUBSTITUTE | 95/8/8 |
| 2014.12.12 | SUBSTITUTE | 2014/9/9 |
| 20080808 | TEXT | 2008/8/8 |
| 990909 | TEXT | 1999/9/9 |
| 20140909 | TEXT | 2014/9/9 |
| 不规范时间 | 应用函数 | 规范日期 |
| 122040 | TEXT | 12:20:40 |
| 234030 | TEXT | 23:40:30 |
6.批量删除单引号
取消大区域内的单引号
有时候爬取下来的数据 ,格式中会有一个单引号,但是文本中却不显示
- 点击空格
- 点击格式刷
- 格式刷要去掉单引号的单元格(格式刷刷的是格式,而不是内容)
7.组合键
-
ctrl + shift +方向
可以使用组合键选择一行 多行 一列 多列 多行多列
ctrl + shift + →(选中数据行) 然后 ctrl + shift + ↓(选中数据列) 就能选中所有的连续的文本内容(遇到空格就会暂停)
atrl + a 有时候会选中整个excel 表格 -
ctrl + =
输出一列所有数据的总和
计算总和的时候,选中所要相加的数据,他会吧每一列的数据相加, 在所选中的区域的最下面一行输出答案,所以选择范围的时候, 要多选中一行空白表格
3.选中不连续单元格组合键
方法一 : 按住ctrl + 鼠标左键点点点
方法二 : 按一次shift + f8 (添加选定模式)就可以点点点了 (不需要一直按着ctrl)
8.选数据+复制公式
比如 一张销量表格, 要增加20%销量的总和, 在一个表格中输入 =目标*1.2 ,就可以了,然后双击这个表格的右下角,在按ctrl + = 就得到了销量增加后的总和(但是这里好像丢失了最后一个数据,所以需要按住ctrl 在添加一个空白表格来放置答案),这样得到了每一个销量增加后的具体数值
9.拆分冻结
一个excel表格,在数据很多的时候,上下滑动,为了体验更好,就需要吧首行首列固定,不会随着老板的鼠标滑动而消失, 就需要冻结窗格
方法是选中第二行第二列的窗口, 审阅 > 冻结窗口 , 这样就可以固定第一行第一列了
基本操作2
10.选择空单元格
开始 > 查找和选择 > 定位条件 > 空值 就找到了所有的空值
数据不完整的时候可以选中空值,然后填充众数和中位数
11.复制分类汇总结果
就是只显示分类的标题, 除此之外的内容需要手动点开 , 才能看到
而这个标题叫做可见单元格,是不现实隐藏数据的
开始 > 查找|选择 > 定位条件 > 可见单元格 就可以只复制 分类的标题了
12.选中错误单元格
选中文本(用ctrl + shift+ 方向) > 开始 > 查找和选择 > 公式 是没有任何用的
选中文本(用ctrl + shift+ 方向) > 开始 > 查找和选择 > 定位条件 > 公式 > 只勾选错误 就选中了文本范围内的所有错误 然后可以给一个0,在按住ctrl + enter 就把所有的错误归零了
13.选择性粘贴
13.1.运算
- 案例1：将数据增加比例25%
可以直接乘1.25,但是这里不是学这个,所以看下面,乘1.25之后再用快速填充功能
选中运算数据 > ctrl+c > 选中被运算数据 > 开始 > 粘贴 > 选择性粘贴 > 加减乘除 > 确定
不能使用快速填充功能 - 案例2：转置（行 > 列）
选中数据 > ctrl+c > 点击要复制的地方 > 开始 > 粘贴 > 选择性粘贴 > 转置 > 确定
可以多行多列 一次性转置 - 案列3 : 链接图片
选中数据 > ctrl + c > 点击要复制的地方 > 开始 > 粘贴 > 选择性粘贴 > 链接图片 > 确定
注意: 随着单元格的修改而修改
应用场景: 动态图表, 大盘
13.2.数据转换乘
通过选择性粘贴的乘,实现数据类型转换 (这里不使用 文本型数据转换为数值型数据 的六种方法方法)
- 案例：将文本型数据转换为数值型数据。
- 单元格输入1 > 选中1 ctrl + c > 选中要被修改的数据 > 开始 > 粘贴 > 选择性粘贴 > 乘 > 确定
13.3.把两列数据合为1列
https://www.bilibili.com/video/BV1Fy4y1z7DH?p=43&spm_id_from=pageDriver 看这个视频吧,我感觉我自己写不清楚
比如 部门名称 金额 这两列要合并为一列
第一行写部门,第二行写金额
-
案例：将金额插入到对应部门名称下面
-
创建辅助列(空列) > 添加对应数量的整数(在第一行输入一个1,在往下拖,拖到我们的源数据的最后一行) > 添加对应数量的小数(1到2之间随便的一个小数,设置为常规,再来一个和源数据同行数的列表,往下拖,就是说在辅助列总共有两个新的列表) > 数据 > 升序 > 扩展选定区域 > 点击排序(这个时候就把源数据扩展了,插入了两个(n)新的列表,源数据就会在每一行之间插入一行(n-1)个空的单元格 > 复制右栏数据, > 选中左栏第二个空单元格 > 粘贴 > 选择性粘贴 > 跳过空单元
注意：整数数量和小数数量都要保证数据完整性
14.查找功能
14.1.统计同2填充色个数
一个有颜色背景的数据表格中,要把白色背景的数字为1000的换为8000,要这么做
选中文本 > 开始 > 选择和替换 > 替换 > 选项 > 第一行查找内容的格式 > 从单元格选择格式 > (出现一个彩色笔)选中白色的1000 > 点击全部替换
注意 : 这个格式用完就要删除这个格式!!!具体原因还未知
格式没有取消的话,后面在使用查找是查找不到任何东西的
15.单元格匹配
把成绩表中的0分 替换为 补考二字
开始 > 查找和选择 > 替换 但这个方法会把成绩为70的这个0 也替换为补考二字,所以不可以
所以在替换的时候要以整个单元格为单位来替换
开始 > 查找和选择 > 替换 > 勾选单元格匹配 > 全部替换
15.1.辅助列
隔行插入一空行
比如你有26个数据,要在每行数据中间插入一个空格
这样做: 先插入一个辅助列26行,从1到26 ,在第27行插入一个1-2的小数(随意),在下拉27行
在选中辅助列这一列 , 数据 > 排序 ,就可以了
如果需要在新插入的空值填入东西,就这样
复制要插入的文本 > 选中所有文本 > 开始 > 查找和选择 > 定位条件 > 空值 > crtl + c
插入两行空格就只需要,插入第一个小数列,之后,在插入第二个小数列
16.圈释无效数据
选中文本 > 数据 > 数据验证 > 圈释无效数据
数据范围的设定方法如下: 数据 > 数据验证 > 设置 > 允许中选定整数(或者其他的有范围的都可以) > 输入最小值和最大值
tips :可以根据数值的类型来选择圈释的范围
17.快速填充
非常的智能,随意,会输出相同格式的文本或者是数字 ,不过使用之前要先清除目标单元格的格式,不然有空可能导致无法使用,也可以吧要复制的单元格的样式清空
第一种方式: 选择一个有数据单元格的右下角,往下拉,拉完以后 ,选择快速填充, 填充的值会根据第一个单元格的规律来
第二种: ctrl + e
只要在这一列中,都可以使用快速填充
bug为: 湖南快速填充只会有两个字,比如黑龙江就会只输出黑龙
18.分列源数据
- 案例：一个对战表格, 查询有多少国家参加世界杯,(就是要去除重复值)
表格中的数据为 VS韩国 啥的 - 实现：
- 将VS替换成， 开始 > 查找和选择 > 替换
- 按，分列 数据 > 分列 > 分隔符号 > 逗号
- 将第二列剪切到第一列后面,就是吧两列值变成一列
- 选中这一列文本, 数据 > 删除重复值 > 全选 > 确定
19.数据整理
- 案例：将爬取的歌单输入到excel中
歌单类似: 01.我的母亲02.金瓶似的小山03.请喝一杯下马酒04.爱在天地间01. 东女国 - 实现：
- 数据 > 分列 > . > 完成
- 复制数据 > 选中要粘贴数据的地方 > 开始 > 选择性粘贴 > 转置
- 快速填充/ctrl+e
- 如果有特殊字符缺失可以使用替换 eg:(藏语 > (藏语) (这里是少了半边括号)
20.全年合并
在同一个excel表格中的,工作簿,合并为一个工作簿 ,视频中的列子是两份两份数据不一样的两份一毛一样的工作簿 (上半年的数据和下半年的数据 合并为全年的数据)
- 案例：将上一年和本年的数据合并计算
- 实现：
- 鼠标选中表格的最右上角的单元格 > 数据 > 合并计算 > 选中第一份工作簿,添加整个表格(不能只选择数据) > 添加 >再点击第二份工作簿,添加整个表格(不能只选择数据) > 添加 > 点击首行 > 点击最左列 > 确定 (完美的方法,无视数据列的位置和个数,就是说可以有一个表格少几列,但是效率低)
这种方法底层使用的全是if判断,就是那每一个框框里面的东西去比较,看列名和行名是否相同
第二种方法:
这种方法要求每一列的列名都对应上,因为这种方法不管列名是否相同,只负责把数据相加
1.在数据空表的最右上方的单元格输入 =点击第一份工作簿 + 点击第二份工作簿 在回车就 好了,但是数据列名对不上的话,数据就错啦
- 鼠标选中表格的最右上角的单元格 > 数据 > 合并计算 > 选中第一份工作簿,添加整个表格(不能只选择数据) > 添加 >再点击第二份工作簿,添加整个表格(不能只选择数据) > 添加 > 点击首行 > 点击最左列 > 确定 (完美的方法,无视数据列的位置和个数,就是说可以有一个表格少几列,但是效率低)
21.行内容差异
就是检查每一行的内容格式(包含数据与公式)是否一样,比如有的值是直接输入的,有的值是用公式得出的
开始 > 查找和选择 > 定位条件 >行内容差异单元格 (不过不太清楚为啥会标注出差异,用什么座位对比的基础呢)
22.删除表格中的对象
就是表格中的单独存在的单元格,一个个的,
就是 开始 > 查找和选择 > 定位条件 > 对象
23.拼接字符串
用&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 就可以吧两个单元格的内容拼接在一起了
367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
