Excel学习笔记.docx
《Excel学习笔记.docx》由会员分享，可在线阅读，更多相关《Excel学习笔记.docx（14页珍藏版）》请在冰豆网上搜索。
Excel学习笔记
第一节入门
一、入门
1、分清工作簿和工作表
工作簿：
一个Excel文件
工作表：
Excel里面的sheet就是工作表
2、将Excel2003和Excel更高版本做出的文件相互转化
3、管理工作表打开是第一时间看到的内容
工作簿打开之后是上一次关闭时的页面。
4、新建、删除工作表
单击右键新建、删除工作表
5、重命名工作表
双击工作表名称直接重命名。
6、给工作标签设置颜色
右键—工作表标签颜色
7、移动、复制工作表
复制：
按住Ctrl键不放，拖拽标签，就可以复制工作表（类似于ps里面的alt）
移动：
8、到达有内容的电子表格的最边界
Ctrl+方向键
二、构建表格基本的框架
1、选择行/列
连续朝指定方向选择：
Ctrl+shift+方向键
2、新建、删除行/列（记住快捷键）
新建：
Ctrl+shift+加号
删除：
Ctrl+减号
注：
选中几行加几行。
3、调整行高、列宽（手动、统一、自动）
调整行高：
格式—行高
调整列宽：
①格式—列宽
②选中所在内容的整列—两列中间双击自动调整列宽。
4、移动行、列
移动列：
选中所需要移动的列—按住shift—放到所需移动的列的右边边框线，在出现十字标志之后移动。
移动行：
选中所需要移动的行—按住shift—放到所需移动的行的下边边框线，在出现十字标志之后移动。
5、隐藏行、列
隐藏：
选中所需的行、列—单击右键—隐藏
释放：
选中最边缘的行、列，按住鼠标左键不放，往右/下拖一下，松手之后单击右键，取消隐藏。
三、单元格格式
1、快捷键：
Ctrl+1
2、让数据显示的和实际的数据一样。
方式：
文件—选项—高级—将精度设为所显示的精度
注：
只针对一个工作簿，非全部工作簿。
3、会计型和货币型的人民币符号所在的位置不一样，同时货币型和会计型默认增加千位分隔符。
4、文本型
（1）表示序号的时候将单元格格式设置为文本，例如：
001；
（2）输入身份证号的时候先将单元格格式设置为文本，如果在输入确定之后再转文本，后三位将会变成0；
（3）超过九位数字将无法序列填充，可以设置前面相同的数字为x，之后在序列填充完毕之后将x替换为之前的数字；
（4）数字前面加一个英文状态的单引号，可以将单元格格式设置为文本；
5、日期时间型
（1）规范格式：
①2018-11-22
②2018/11/22
③2018.11.22
（2）当前系统日期：
Ctrl+；
（3）当前系统时间：
Ctrl+shift+；
（4）查看日期对应星期几
设置单元格格式—数字—自定义—去掉类型框中最后面的：
和@，之后加上aaaa显示中文星期几，加上dddd显示英文星期几；
（5）日期和时间都可以转换为数字；
（6）设置一个时间段（即可以超过24:
00）
设置单元格格式—时间：
选择xx时xx分—自定义—在类型框里的h上加上[]。
（7）设置时间显示格式
设置单元格格式—自定义—默认的为：
yyyy.m.d—y（表示年显示为几位数，可以设置为yy或者yyyy），m（表示月显示为几位数，可以设置为mm或者m），d（表示日显示为几位数，可以设置为d或者dd）
6、百分比型
将小数转换为百分比：
单元格格式—百分比。
7、分数型
（1）输入2/1：
01/2
（2）让带分数显示为分数
设置单元格格式—自定义—把前面的#去掉。
8、为数字加后缀
选中所需要加单位的单元格—设置单元格格式—自定义—如果是中文单位，则设置为0.0千克，如果是英文的单位，则设置为0.0”kg”。
9、为数字加前缀
选中所需要加前缀的单元格—设置单元格格式—自定义—“所需要加的前缀”0。
10、按照层次设置分数
设置单元格格式—自定义—类型：
[<60]不及格;及格
11、隐藏单元格里的内容
设置：
选中所需隐藏的单元格—设置单元格格式—自定义—；；；
取消：
选中所需显示的单元格—设置单元格格式—自定义—删掉三个分号。
四、对齐方式
合并居中和跨列居中对齐的区别
合并居中：
将所选单元格合并之后居中
跨列居中：
将所选单元格不合并且居中
方法：
选中所需单元格—设置单元格格式—对齐—水平对齐—跨列居中。
第二节Excel之条件格式排序筛选定位
一、套用表格格式
1、套用的时候记得选择区域，如果有合并单元格一定不要选择合并单元格。
2、镶边行：
一行一个颜色，两行颜色不一样，便于观察。
3、如果数据中的分类汇总为灰色，则单击右键，选中表格选项，之后将表格转化成区域。
二、条件格式
把符合特定条件的数据设置格式突出显示（选择条件格式之前要选择区域）
三、定位条件
1、定位通用快捷键：
Ctrl+g或者F5
注：
删除选中单元格里的内容使用delete键。
2、批量填充数据快捷键：
Ctrl+Enter。
3、补齐下面的数据：
选中整列，之后定位空值，之后输入：
（=+上键）+（Ctrl+Enter）。
注：
最好将本列复制一下，之后在原地粘贴数值本身，因为一般都会带公式，这样一来就可以取消本身所带的公式。
4、找出两列（同行）中不同的字符：
首先选中需要查找的两列，之后用F5调出单元格，在定位条件中选择行内容差异单元格。
四、数据排列
排序列：
主要关键字、次要关键字
排序依据：
数值、单元格颜色、字体颜色
排序次序：
升序、降序、自定义序列
注：
1、如果排序中主要关键字显示为列一列二，原因是没有勾选右上角的数据包含标题。
2、同时根据两个条件排序的话在自定义排序中选择添加条件，用来添加次要关键字以及次要排序条件。
3、按照笔画排序：
自定义排序—选项—笔画排序。
4、按照职务排序：
自定义排序—选项—次序—输入序列—将需要排列的顺序从上到下打出来（每打一个换一行）
五、工资条的制作
第一步：
选择标题行复制；
第二步：
选中下面的所有单元行；
第三步：
在选中的地方点击右键，添加复制的单元行，选择活动单元格下移；
第四步：
在右边添加辅助列，从第一行开始分别为1、2……，直到所复制的单元行的最后一行；
第五步：
复制辅助序号，向下粘贴两次（因为其中需要空白行）；
第六步：
选中辅助行，之后按照升序排列（扩展选定区域）
第七步：
删除标题行（不是大标题，是写有辅助的标题行）以及辅助列；
第八步：
添加框线；
第九步：
定位空值，之后在添加框线中取消所有竖线。
六、自动筛选（Ctrl+shift+L）
1、筛选出管理部工资大于10000的员工信息和行政部所有员工的信息
（1）数据—高级
（2）
2、查未到人员
（1）将已到人员标题也改为姓名；
（2）选中列表区域和条件区域；
（3）选择方式为在原有区域显示筛选结果；
（4）将现在全部人员行中显示的姓名标红；
（5）取消筛选；
（6）在自定义排序中按照字体颜色排序；
第三节Excel数据选项卡
一、获取外部数据。
1、将文本导入Excel
数据—自文本—将文件原始格式修改为简体中文—第二步不用改—将需要改的列格式修改（比如说把身份证格式改为文本）—完成。
2、将处于同一格的学校姓名分为两列
在需要拆分的列后面插入空列—数据—分列—固定宽度—调整数据预览。
二、数据分列。
1、提取身份证号码中的出生年月日。
数据—分列）—固定宽度—将身份证中需要的信息分隔开—前六位和后四位选择不导入—将中间的数据格式选择为日期—完成。
三、数据有效性。
1、打开位置：
数据—数据有效性
2、自动填入数据方式
数据—数据有效性—设置—允许—序列—来源（输入自己需要的数据，用英文逗号隔开）—完成。
3、手动输入限制重复数值。
数据—数据有效性—允许：
自定义—公式：
=countif（E:
E（所需范围）,E2（起始坐标））=1（次数）。
注：
仅限于手动输入。
四、合并计算。
1、计算行数。
函数：
=rows（起始行：
结束行）
2、表格合并计算。
使用条件：
当表格的格式一样（即各个标题一样，只有数据不一样）。
使用方式：
数据—合并计算—函数：
求和—引用位置：
需要计算的几个表格中，选中一个，点击添加（带着标题添加），之后继续选中一个，点击添加（带着标题添加）—标签位置：
勾选首行、最左列—确定。
五、单变量求解
题目：
解法：
数据—模拟分析—单变量求解—目标单元格：
三十天后的体重格—目标值（想要达到的体重）：
70—可变单元格（从哪一项入手改变饮食习惯，从而减轻体重）：
每天运动格——确定。
含义：
想要达到理想体重，要从哪一个方面入手，如果想要达到理想体重，那么入手的那个方面每天需要作出多少改变。
六、模拟分析（不会）
第四节Excel之分类汇总数据透视表与图表专题
一、分类汇总
1、统计每个部门的平均工资。
根据部门排序—分类汇总—分类字段：
部门—汇总方式—求和—确定。
2、统计每个部门每个学历层次的平均工资。
数据—排序—主要关键字：
部门—次要关键字：
学历—确定。
数据—分类汇总—分类字段：
学历—取消勾选替换当前分类汇总—确定。
注：
分类汇总只能针对普通区域，不能针对表格区域。
二、电子表格的打印。
1、调整行高列宽
2、纸张方向
3、打印区域的设置
（1）页面布局—打印区域（记得添加框线）。
（2）让每一页都有标题：
页面布局—打印标题—工作表—打印区域：
选中所有需要打印的部分—顶端标题行：
选中需要每一页都有的标题—确定。
4、缩放打印
5、设置页眉页脚
三、表格保护
整个文件保护：
文件—信息—权限：
保护工作簿—用密码进行加密。
表格保护：
选中整张工作表—单击右键—设置单元格格式—保护—取消勾选锁定—确定—选中需要锁定的表格—单击右键—设置单元格格式——锁定—审阅—保护工作表—根据自己的需要设置。
取消表格保护：
选中已经被保护的表格，之后在刚才为保护工作表的地方点击取消工作表保护。
工作表和工作簿的区别：
工作簿就像一本小册子，而工作表就像这本册子中一页一页的内容。
四、冻结窗格
1、始终都看到标题行：
视图—冻结窗格—冻结首行。
2、冻结拆分窗格：
将选中的这一格上方和左方所有单元格都冻结。
五、数据透视表
1、将一维表格（只有一个标题栏）转换为二维表格（有左边和上面两个标题栏）：
（1）制作方式：
选中需要的区域—插入—数据透视表—选择—选择放置数据透视表的位置—确定—自动打开字段列表（如果找不到的话可以在选项—字段列表里面找到）—根据自己的需求将字段拖入下方的空格中。
（2）将所创建的表格按月统计：
日期旁边点击右键—创建组—选择起始于和终止于—选择步长—点击确定。
（3）取消总计：
设计—总计—对行列禁用总计。
注：
（1）所选中的区域不能出现合并单元格和空值，即所选区域必须是标准的一维表。
（2）双击姓名可以详细查看这个人的数据。
第五节Excel函数公式篇之常用函数专题
一、根据部门名称标红部门所在一行。
1、列出所需要使用的部门—选中某一个空格，之后点击数据、数据有效性—设置—允许（序列）—来源（选中刚才列出的所需要的使用的部门）—确定；
2、全选表格—开始—条件格式—新建规则—使用公式确定要设置格式的单元格—选中部门列的任意一个部门—去掉数字前面的$符号（字母前面的s符号不去掉）—继续输入=，在=后面加上刚才设置的那个空格的坐标—设置突出显示方式—确定。
二、公式篇入门
1、注意事项：
（1）单元格格式不能是文本；
（2）等号开头；
（3）标点必须是英文标点。
2、运算符
+（加），-（减），*（乘），/（除），>（大于），<（小于），>=（大于等于），<=（小于等于），<>（不等于），&（合并）。
3、五大常用函数：
（1）sum（）求和函数
（2）average（）求平均
（3）count（）求个数（不能求文本和空单元格）
（4）max（）求最大
（5）min（）求最小
4、求第几大数字
使用函数：
large
格式：
=large（需要求的区域,第几大数字）
5、求第几小数字
使用函数：
small
格式：
small（需要求的区域,第几小数字）
6、if函数（逻辑判断条件，语句1，语句2）
（1）判断分数是否及格
格式：
=IF（所判断的坐标>=60,”及格”,”不及格”）
（2）将分数按照大小分等级
方法一：
格式：
=IF（G34>=90,"优秀",IF（G34>=80,"良好",IF（G34>=60,"及格",IF（G34<60,"不及格"））））
方法二：
第一步：
点击f（x）
—选择函数：
IF—确定；
第二步：
在第一行框中输入：
所判断的坐标>=90—第二行中输入：
”良好”—第三行不输入；
第三步：
点击IF框
；
第四步：
在第一行框中输入：
所判断的坐标>=80—第二行中输入：
”良好”—第三行不输入；
第四步：
重复第三步；
第五步：
在第一行框中输入：
所判断的坐标>=60—第二行中输入：
”及格”—第三行输入：
”不及格”。
7、rank函数（排名对象，数据区域，顺序）
（1）注意：
数据区域需要绝对应用，顺序可省略。
（2）格式：
=rank（排名对象,排名区域（按下F4，如果按下F4之后没有显示出绝对引用，则按住FN不放，再按下F4）,1）。
注：
（1）排名区域后面的数字，如果输入1，则是升序，如果输入0，则是降序，默认为降序；
（2）双击排好的区域右下角可以自动将下面的排名排好。
（3）求比例
格式：
=月支出框/年支出框（按下F4，绝对引用），之后将小数转化为百分比。
（4）求乘积
格式：
=L$2*$J3
注：
在需要不变的行或列前加上$。
8、countif（范围,条件）单条件求个数
countif（数据范围1,条件1,数据范围2,数据2）
单条件求个数格式：
方法①：
=countif（所求区域,”条件”）
方法②：
=countif（所求区域,所求单元格）
多条件求个数格式：
方法①：
=countifs（所求区域,”条件”,所求区域,”条件”）
方法②：
=countifs（所求区域,所求单元格，所求区域,所求单元格）
注：
多条件中两个条件的区域大小需要一样。
9、sumif单条件求总和
格式：
=sumif（计算求和区域,条件区域,条件,条件区域,条件）
10、sumproduct（先乘积后求和函数）
注：
需要两边区域的空格数量一致。
（1）例：
，sumproduct求的就是总销售额。
过程：
每一行的单价乘销量之后所有行相加的结果。
格式：
方法①：
=sumproduct（（条件1,条件2）*计算求和区域）
注：
不能有文本
方法②：
=sumproduct（（条件1*条件2）*计算求和区域）（可以有文本）
注：
可以有文本
（2）生产部女职工的工资总和
格式：
=sumproduct（（区域=条件）*（区域=条件）*计算求和区域）
例如：
（3）column（求出当前列数）
格式：
=column（）
第六节Excel函数公式篇之文本函数专题
一、函数解析
1、left（）从左边取字符串
格式：
=left（所选定的单元格，需要取的字符数）
2、right（）从右边取字符串
格式：
=right（所选定的单元格，需要取的字符数）
3、mid（）从中间取字符串
格式：
=mid（所选定的单元格,需要开始取的字符顺序号,取的长度）
例：
=mid（E2,7,8）
4、len（）返回字符串的字符数
格式：
=len（所选定的单元格）
注：
字符数是指单元格内字符的数量，一个字符算一个。
5、lenb（）返回字符串的字节数
格式：
=lenb（所选定的单元格）
注：
字节数是指单元格内字节的数量，一个汉字算两个字节，一个英文字母或者一个数字算一个字节。
6、求单元格内的汉字数量。
格式：
=len（所选定的单元格）-lenb（所选定的单元格）
注：
两个所选定的的单元格为同一个单元格。
7、find（）对原始数据中某个字符串进行定位，以确定其位置。
8、身份证号码提取出生日期并且转换为年月日
格式：
（1）--TEXT（MID（D3,7,8）,”0000-00-00”）+0
（2）TEXT（MID（D3,7,8）,”0000-00-00”）+0，之后将格式转换为日期。
9，身份证号码提取男女
格式：
=if（mod（mid（包含有身份证号码的单元格,17,1）,2）=1,”男”,”女”）
注：
mod函数是用来求余数的。
10、字符串联合
格式：
=字符串格&字符串格
11、phonetic（把一组数据联合在一起）
12、函数
①sqrt（返回平方根）
翻译：
求一个数的平方根
②int（求整数，向下四舍五入）
翻译：
去掉小数点后面的数
③roundup（向上四舍五入）
翻译：
去掉小数点后面的数，之后在这个数值上加一。
④rounddown（向下四舍五入）
翻译：
据说和第二个一样。
⑤mod（求余数）
翻译：
求一个除法的余数。
⑥large（求第k大的数据）
⑦small（求第k小的数据）
⑧numberstring（将小写数字转化成大写）
第七节Excel函数公式篇之日期函数与查询函数专题
一、vlookup（）跨表纵向查询填充函数
格式：
vlookup（查询对象，查询的数据区域，结果在数据区域中的列数，精确匹配还是近似匹配）
通俗版：
vlookup（找啥，上哪里找，告诉我结果在第几列，找一模一样的还是差不多的）
例如：
=vlookup（C2,$J$1:
$L$18,2,FALSE）
（查询的是图书名称）
注：
①查询的数据区域为绝对引用。
②如果两个表不在同一个表中，在使用的过程中不要返回到之前的那个表，一直在后面所引用的那个表完成接下来的操作。
③如果不能引用的时候用左对齐。
二、lookup（）在一行或一列中查找值，返回另一行或另一列对应位置的值
格式：
=lookup（查询对象，查询的数据区域，对应的结果值）
例如：
在“2012级法律”工作表中，利用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为：
学号的第三位为专业代码、第四位代表班级序号，及01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。
方法一：
=lookup（MID（[@学号],3,2）,{“01”,”02”,”03”,”04”},{“法律一班”,”法律二班”,”法律三班”,”法律四班”}）
方法二：
”法律”&numberstring（mid（[@学号],4,1）,1）&”班”
三、查询函数
1、match（）找位置
格式：
=match（查询对象,查询区域,匹配类型）
2、index（）用于返回表格或区域中的值或值的引用
格式：
=（数据区域,行,列）
例如：
求员工在某月的交易额。
四、日期函数专题
1、datedif（）起始日期到终止日期之间相隔了多少个日期单位。
格式：
=dateedif（所求单元格（某个有年月日的日期）,today（）,”所求的时间长度单位（和起始日期相隔多少年/月/日，格式为”y”,”m”,”d”）”
2、weekday（）返回一个日期所对应的日期的星期数
格式：
=weekday（日期,返回值类型的数字）
例如：
=weekday（E15,2）
3、days360将一年按照360天来算。
=days360（“起始日期”,”终止日期”）
四、行与列
1、row（）求当前单元格所在的行数
2、column（）求当前单元格所在的列数
3、rows（）求当前区域一共选中了多少行
4、columns（）求当前区域一共选中了多少列
五、逻辑函数专题
1、且：
两边都为真才是真
2、或：
有一个真就是真
升级会员 