|
王佩丰第一讲 excel 的基础课程 历史上其他的数据处理软件与microsoft excel 1977年，苹果公司开发了自己的第一款数据处理软件，那款软件卖得非常好。后来，lotos123公司发行额自己的lotos123.excel这款软件开发，是因为微软公司看到了lotos卖得非常好，它最早期的目标就是做到lotos123所有能做的事情，并且做的更好。我们现在的这款软件中，仍然保留着对于loros123的支持。可以帮助我们，手里有很多单据的时候，你希望都直接显示结果，有没有一种在写公式的过程省略等号呢。在选项里面勾选lotus，选择转换lotus123公式。 excel中公式必须以等号开头，没有以等号开头，就不会被运算。 excel能做些什么？ 数据存储；数据处理（需要讲到函数的运用）；数据分析（从数据中得到结果，用语做判断总结和预算）；数据呈现 excel中的重要概念 excel的工作簿文件 后缀是xls的一般是2003年以前的 xlw 是工作区文件 怎么并排查看不同表格的内容呢 只有点击试图里的新建窗口，然后点击全部重排，然后点击垂直并排。就可以了。实际上做出来的两个窗口，是互为镜像的关系。所以改变一个，另一个也会随之改变。其实不需要每天都重排，如果要做数据对比的话。很遗憾，保存工作区这个功能已经废止了。所以没办法用了。 工作簿、工作表和单元格 新建工作表 在底下所有表格后面又一个加号，点击即可新建工作表 在工作表的标签上面右键，找删除，就可以删除工作表了 更改工作表的标签颜色 在表的标签上右键，然后选择“修改工作表标签颜色”，就可以自己更改工作表标签颜色了。 插入/删除多个工作表 点击一个工作表标签，然后按住shift，然后再点击一个工作表，就可以选中读个工作表了，然后在标签上点击右键，选择插入，然后选择工作表，然后现在插入的就是三张表额（也就是连选了几个工作表，就能够同时新建几个工作表）。（按住shift进行连选，点中第一个和最后一个就能选中连续的几个工作表）。 反之，删除也一样。用shift连选，然后删除就可以了。 插入行/列，插入多个行或列，移动行或列，调整行高列宽 1，2，3，4行 A，B，C，D列（横行竖列），便于定位 定位到每个单元格 插入空行，直接点击行前的数字，然后右键，选择插入，这样就Ok了。（右键，选择删除就可以删除了） 也可以同时新建多个空行。（选中多行之后，右键，然后选择插入，就可以插入多行了） 列也是一样的。 如果我不小心弄反了c列和d列，我怎么吧两列的次序交换呢？ 当我们点击任意一个单元格的时候，如果我们把鼠标一道段元歌表框线上，鼠标聚会变成有上下左右四个方向的箭头。如果把鼠标拉一下，就可以把这个单元格直接拉走（然后我也可以把它拉回来） 这里我们可以先选中c列整列，然后把鼠标放到边框线上，然后鼠标变成上下左右四个方向的箭头，然后直接拉到d列，这时，就会出现窗口，询问是否替换目标单元格内容，当然不能选择替换，替换了就覆盖掉了，原本d列的内容就会消失。所以我们应该按住shift键去拖拽，然后就可以原来的虚线现在就变成了一跳竖线，而不是原来的框了。我们只需要把它挡在d列的右侧，（也就是e列的前面），然后再松开鼠标，在松开shift键，这样两列就把顺序交叉过来了。 调整行高列宽 当我们把鼠标移动到列标签的边框线上时，鼠标样式又变得不同了。（一个竖线，两边是箭头）拉一下鼠标，就可以把列调宽或者窄。也可以不自己左右拉。你可以直接双击，然后单元格大小就可以自动调整到能够容纳内容的大小（自动调整为最窄）。 也可以选择多列，然后你左右拉，你选中的这几列，同时被调整。选择多列，然后在边框线上双击一下，然后就都调整为最窄。 单元格选取、整行整列选取、数据区域选取 怎么样快速到达一个表格的底部呢？ 选中一个单元格，然后把鼠标移动到它的（下面的）边框线上，然后把双击鼠标，这样就可以到达表格的底部了。 如果随意选择一个单元格，然后在它上面的边框线上双击，那么就会回到表格的顶部。 最左面和最右面也同理。 使用小工具：冻结窗格、填充柄、查找替换 冻结窗格 看很多数据的时候，往下滑的时候，看不到表头，就会忘记表里面的内容是什么。 但是有些表格怎么样做到滑动的时候一直在顶上不动的呢？ 在视图当中，有一项是冻结窗格。点击一下，其中有一项是冻结首行，点击一下，可以看到表格的第一行出现了一条黑线，然后接下来我们再滚动，第一行就不动了。（想取消冻结的话，还是点击冻结窗格，然后单机取消冻结首行，就可以了） 也可以冻结首列。 但有的时候，我们需要的不只是冻结首行。（因为表头信息） 这时候，要选择想要冻结行的下一行的第一个单元格，然后，点击冻结窗格，选中冻结拆分格。然后就会看到，该单元格上面的行都冻结了。（例如，点第四行，那么冻结的就是前三行） 类似地，也可以冻结列。 比如，选择冻结窗格中的冻结首列。 （例如，选中b4单元格，然后在点击，冻结拆分窗格，最后不仅冻结了前三行，也冻结了第一列） 也就是选中单元格的左侧和上部的行和列会被冻结。 填充柄 其实就是拖拽 比如我在f1单元格里写一个1，然后回车，会发现此时单元格的右下角又一个小正方形。当我把鼠标移动到这个小方块上的时候，鼠标的样式再次变化，变成了实心的加号。 此时，当我们吧鼠标左键按住往下拖时，会发现，我们拖拽出来了很多“1”。 但此时如果不拖拽，而是在第二个单元格中写“2”的话。我同时选中f1和f2,那么就出现接着下去的“3，4，5，6，7，8……”（居然会自己找规律） 然后我在个g1单元格中写上今天的日期（即用Ctrl键+：键，按住Ctrl敲冒号），然后再向下拖拽，然后发现，日期的话，也是很自然的一天一天填下来。 那为什么有时候是顺序的，有的时候是复制呢？ 比方说，我在d1单元格打“1”，我向下拖拽时按住ctrl，那么我得到的就是序列。 但是日期的话，我按住ctrl键再来拖拽的话就是复制。 但是如果输入2021年3月31日，我们拖拽时，不用左键，而是按住右键，就会出来提示，问是否要按照工作日填充。选中工作日填充，就会填充出星期一到星期五。（右键填充，特别的填充） 拖拽出的东西是可以设置的。 点击文件-选项-高级-创建排序和填充序列的列表 然后点击左侧“新序列”，然后右侧就可以输入序列，然后输入，输入完了，就点击右侧的“添加”，就可以了。 我怎么可以快速选中很多行？ 现在名称框中输入单元格名称，比如A10000，然后回车，就会自动跳到该单元格。 然后我可以在名称框中输入2：900，意思是2到900行这样一个范围，回车，然后我选中了这样一个范围 王佩丰第二讲 1.使用单元格格式工具梅花表格 （1）“设置单元格格式”的对话框在哪里？ 开始-对齐方式栏-点击右下小箭头，是指单元格格式的对话框就出来了。 也可以直接选中某个单元格，然后右键，再选择设置单元格格式 合并单元格 选中要合并的单元格，然后点击 开始-对齐方式栏-合并后居中。 表格的边框？ 开始-字体栏-边框（图标在填充颜色的左边） 比如选择所有框线 合并并居中下拉 跨越合并 随手选择一大块单元格，然后点击跨越合并，然后就可以看到这一大块中，每一行的安远隔都核定成了一个单元格，但是每列并没有合并。（即中间有横线，并不是一整块空白） 表格中的斜线怎么设置？ 选中单元格，右键，点击设置单元格格式，然后，选择边框。 这里选中两行两列的单元格，然后随便选择边框线样式，然后因为单元格中有很多条线条，可以依据具体情况，点击，然后设置。 斜线？ 还是直接边框，然后点一下，就可以设置斜线了。 斜线上下的字怎么写进去的？ 首先，双击单元格就可以写字了。 但是如果我想在下一行继续写呢？ 我就必须按住alt，敲击回车，然互就可以到达下一行。 首先，在一个单元格中输入两行字。第一行输完了，然互按住alt，敲击回车，然后就可以输入第二行字了。然后设置斜线。然后再按回车，就出现了斜线上和斜线下两行字，然后选择左对齐，然后自己调整字的位置，做到美观。 格式刷。 点击格式刷，然后把某个格式刷一下，然后再选择一块数据，就会自动粑之前的格式刷过去。 选中-格式刷-再选中 很多地方要刷，先选中想要刷的格式，然后，双击格式刷，然后就可以随便刷了。不想刷了，就按esc键，然后鼠标就恢复正常了。 2.单元格数字格式 在excel当中，会去掉无意义的数字格式。比如，输入2400.00，会直接省略成2400，输入007，会变成7. 设置单元格格式-数字-分类-数值-小数数位，可以自己设置 千分位分隔符，就是千位数字之后的逗号。 点击数字-分类-货币，还可以添加货币符号。 还可以选择，如果是负数，要不要变成红颜色的。 数字-分类-会计专用，会计专用这种格式，他的货币符号是在单元格最左侧的。（而货币格式下，货币符号是在数字前面，贴着数字的） 数字是0的情况下，会计专用会显示为横线，而货币会显示为0。 图片 数字-分类-日期 excel的日期系统，微软采用了1900纪年方式，比如数字1就是1900年1月1日。所以以此类推，1000就是1902年9月26日。 图片 所以当输入今天的日期，2021年4月1日，我们点击设置单元格格式-数字-分类-常规，2021.4.1就会变成44287. 数字-分类-时间 我们打数字1，然后改成时间，就会显示0：00,1.5，就显示12:00.，2，也是0:00. 数字-分类-科学计数 10000，变为1.00E+04，意思是10的4次方 图片 数字-分类-特殊 邮政编码 中文大写数字 中文小写数字 数值永远是数值，只是显示格式变了，复制的话，最后粘贴出来的还是原本的数值。自定义，mmm表示英文月份缩写。y是年，m是月，d是日。aaa显示为一二三四五六日，aaaa显示为星期一星期二星期三…… 文本和其他格式不通用 在excel 中，数字分为两类。表示大小和多少的含义。不表示大小和多少。 在excel中写身份证号不能直接写（因为身份证号有18位，而excel只能精确到15位，所以回车之后，后四位就会变成0000），需要提前告诉excel要输入的内容并不是一个表示大小或者多少的概念，而是一个所有数字都要意义的一个编码。所以怎么做呢？ 选中要输入身份证号的一列，然后设置单元格格式-数字-文本。这样就不会自动变成科学计数，也不会使得后四位变为0000.（即输入什么就是什么，excel不会擅自删改内容） 但是文本格式之后，再也无法通过“设置单元格格式”改为常规样式。所以，文本格式不能随便改变。 如果一组数字是文本格式，那么这组数字不能运算。但是仍然可以把它改为数字。选中文本格式的数字，然后这时，这些文本格式数字的左上角会出现黄色菱形感叹号，点击这个感叹号，然后，选择“转换为数字”。那么就转换为数字了。 图片 如果数字太多，看不出谁是文本，谁是数字，直接随便找个单元格写“1”，然后右键，复制，然后选择表中数字区域，然后右键，选择选择性粘贴-乘。然后所有的文本格式数字，就会被强制转化为数字格式。 图片 3.使用“分列”工具 怎么把txt文件直接拷进excel？ 首先复制txt，然后直接选中A1单元格，然后直接把内容复制进去。然后就会发现很乱，而且所有的表格内容全都在A列的各行里。 然后我们选中整个A列，然后点击开始菜单里的数据，点击数据工具栏的分列。然后就会出现使用分隔符号哈市用固定宽度分。看情况，但一般是选择用分隔符号分。然后就去选择用什么分隔符号分。excel中提到的符号都是半角英文符号。 如果要按照中文符号分，我们选择'其他’，然后输入相应的中文符号。 图片 图片 有些情况下，日期是文本格式，跟文本格式的数字不同，不能通过黄色叹号转变。这时要把它改为常规样式，只要选中这些文本格式的日期，然后数据-分列，然后直接点击“完成”。 这样这些文本格式的日期，就可以直接变成常规格式。 王佩丰第三讲 1.查找与替换 任何文本和编辑类工具里都带有查找和替换的功能。 1.快速查找 开始-查找与选择-替换-选项 图片 这里勾选单元格匹配。 就可以把苏州替换为苏州市，而不会吧苏州市替换为苏州市市。 只替换完全符合的选项。 2.按格式查找 颜色的替换。 替换填充色 查找与替换-格式-填充-点选颜色 图片 图片 清除格式 从一种格式变为另一种格式。 *和?都是通配符 *在excel中指的是任意值 （就是任意一个东西，一或者多个字符） ?(英文的半角问号）(问号只代表一个字符） 这里如何吧“张*”替换为“经理的亲戚呢？ 图片 这里波浪线“~”的意思是使得后面的字符（一个字符）不生效。 这样就能替换了。 ctrl+f，查找的快捷键 ctrl+h，替换的快捷键 2.定位工具 1.通过名称框定位单元格及区域位置 2.定义名称 选择某个区域，然后在名称框中输入一个名字，然后回车，这个名字 就被保留了下来，下一次就可以比较容易找到。 3.定位条件 查找与选择-定位条件 图片 红色角标（批注） 选中单元格-鼠标右键-插入批注 显示或隐藏批注 审阅-显示所有批注 改变批注框形状 如何把有批注的单元格设置为红色的填充色？ 查找与选择-定位条件-批注-确定 这样把所有的有批注的单元格都定位了。然后直接填充颜色为红色。就可以了。 把所有带有公式的单元格替换为红色？ 查找与选择-定位条件-公式-确定 这样有公式的单元格就全部选中了。然后我么点击一下颜料桶，红色。就好了。 在工作中，原本（内容中）带有合并单元格的表，你必须先取消掉合并，二期扼要保证每一行中都带有内容，这样excel才能识别。 先选定一个区域，然后输入一个内容，然后按住ctrl，打回车。该区域内所有的单元格内都是这个内容。 根据表格内已有内容，把空单元格补满 定位条件-空值 选定空单元格 图片 选中表格数据区域 图片 定位条件-空值 图片 选择一个已选好的空单元格，输入“=”和键盘上下左右的上键。然后按住ctrl，回车。 这样就填好了。 图片 把excel中的图片全部选中？ 定位条件-对象-确定 王佩丰第四讲 排序与筛选 1.排序 1.简单排序 开始-排序与筛选 图片 按照数学成绩降序排列。 图片 直接选中数学成绩分数中的任意一个单元格，然互开始-排序和筛选-降序（就从数学最高分我那个最低分排列） 图片 2.多条件排序 数学最重要，语文其次，英语再次。 图片 只是随便选中一个单元格，然后开始-排序和筛选-自定义排序 图片 然后点击添加条件 图片 添加了次要条件 语文-数值-降序 英语-数值-降序 英语次于数学和语文。 最后就排好序了。 图片 3.按颜色排序 颜色一样的怎么排序？ 图片 自定义。 图片 excel自动排序的规则：字母和数字 4.自定义排休次序 图片 5.用排序插入行 制作工资条 2.筛选 excel筛选中老是出bug。 1.使用筛选 开始-排序也筛选-筛选， 然后表头上就会出现很多个下拉箭头 图片 想要把筛选出来的数据复制到另外一个表中去，却发现，粘贴了原来的整个表，怎么解决？ 筛选完了以后，查找和选择-定位调价-可见单元格 然后再复制 筛选完了，让原本所有数据都出现，还是在下拉箭头那里，勾选“全选”。 图片 图片 图片 数字筛选 文本筛选 2.在筛选中使用多个条件 3.高级筛选 图片 图片 图片 勾选不重复记录 制作 图片 且 图片 或 条件区域 必须带有表头。 两个条件在同一行里，那么就是且。 不在同一行里就是或 快速选中整个表格区域。 最左上角 按住ctrl和shift 上下左右方向键 敲右键 在敲下键 然后就OK了。 王佩丰第五讲 1.分列汇总工具 1.认识分类汇总 图片 按什么分类，汇总什么，怎么汇总？ 2.使用分类汇总前先排序 3.分类汇总的嵌套 4.复制分类汇总的结果区域 5.使用分类汇总批量合并内容相同的单元格 2.设置数据有效性 1.设置整数数据有效性 2.设置文本长度数据有效性 3.设置序列数据有效性 4.数据有效性的其他设置 王佩丰第六讲 数据透视表 1.创建数据透视表 每列是一个属性，我们成为一个字段 图片 插入之后，右键，选择数据透视表选项，选择显示，勾选经典数据数据透视表布局。改成经典形式。 图片 直接把右侧出现的数据透视表字段 用鼠标左键按住，然后拖入三个字段中（即行字段，列字段和值字段） 图片 2.更改数据透视表汇总方式 双击左上就可以更改（求和，平均值……） 图片 3.数据透视表中的组合 图片 把日期按照季度组合一下。 图片 图片 4.汇总多列数据 图片 5.在透视表中使用计算 图片 图片 2016版中叫做 计算、项目和集，点击，然互选择计算字段 图片 错误值 图片 6.利用筛选字段自动创建工作表 图片 放到顶上那个页字段上去 图片 王佩丰第七讲 认识函数与公式 1.认识excel公式 公式要以等号开头（这个等号并没有意义） 1.算术运算符+ - * / % & ^ &是连字符 就是把数据左右相连 比较运算符= > < >= <= <> <>是不等于 2.公式中的比较判断 比较运算符的结果：TRUE FALSE true 可以当1来处理 false可以当0来处理 3.运算符优先级 文本格式也能够加减乘除，但是其他运算不行。 图片 图片 单元格名称是相对的。 图片 选中j4（如图），然后按住键盘里的F4，把J4锁死。 图片 图片 混合引用不是锁死整个单元格，而是锁一半。 2.认识函数 sum 求和 直接自己手打函数名 函数公式 等号开头，接着写函数名，函数名后接括号。 括号里面填上参数。（把光标放在括号里，然后直接鼠标选中要计算的区域） average 平均 max 最大 min 最小 rank 排名 count和counta 计数 函数中的参数用逗号分隔 图片 批量填充 按住ctrl 再回车 王佩丰第八讲 if函数逻辑判断 1.if函数 1.if函数的基本用法 函数语法：IF(logical_est, [value_if _true], [value_if_false]) 如果，那么，否则 图片 2.if函数的嵌套 图片 =IF(B2="理工","LG",IF(B2="文科","WK","CJ")) 所有的符号必须都是英文半角 图片 =IF(G2="本地",H2+30,IF(G2="本省",H2+20,H2+10)) 图片 =IF(I2>=600,"第一批",IF(I2>=400,"第二批","落榜")) 3.如何尽量回避if函数的嵌套 if嵌套层次非常多，就应该考虑用别的函数 4.用if函数处理运算错误 Iserror函数 iserror是 “是错误”的意思 图片 =IF(ISERROR(D35/C35),0,D35/C35) 2.and函数与or函数 1.and函数：表示“且”的关系 图片 =IF(AND(A3="男",B3>=60),1000,0) 2.or函数：表示“或”的关系 图片 =IF(OR(B12>=60,B12<=40),1000,0) 图片 =IF(OR(AND(A20="男",B20>=60),AND(A20="女",B20<=40)),1000,0) 王佩丰第九讲 1.使用countif函数 countif，区域和条件（range,criteria） countif只把前十五位作为判断对象 2.常见应用示例 图片 =IF(COUNTIF(G:G,A2)=0,"未体检","已体检") 3.countifs函数 多个条件 图片 =COUNTIFS(E:E,J5,D:D,I5) 王佩丰第十讲 sumif函数 range，criteria，sum_range 区域，条件，求和区域 图片 =SUMIF(A:A,F3&"*",B:B) sumif容错能力。 sumif函数第三参数的简写。 简写第三参数，要注意第一参数是从哪里开始选的，不能错位。第三参数的范围会自动扩充为与第一参数一样大。 sumifs函数 参数 求和区域，条件区域1，条件1，条件区域2，条件2，条件区域3，条件3…… 王佩丰第十一讲 使用vlookup函数 1.vlookup函数语法 'VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) 在某个区域里找到某个数据，并且把这个数据相关的某个值拿过来 查找谁的什么东西。 第一参数是找谁，就选谁 第二参数必须包含查找列和引用列， 找什么，什么就必须在第二参数的最左侧。 第三参数指的是要返回选区的第几列 0表示精确匹配 1表示模糊匹配 找汪梅的原始分 图片 =VLOOKUP(G6,$B$5:$E$10,4,0) 记得最后一定要写0。 跨表选择之后，记得不要随便点别的表。 可以跨表引用。 2.vlookup中使用通配符 3.vlookup模糊查找 永远对应的是小于等于本身的值 也就是二分法。 用模糊匹配时候，需要查找值所在列必须从小到大排序。 4.使用isna函数处理数字格式引起的错误 5.Hlookup函数 hlookup用得很少。 就是竖着的。 第十二讲 1.回顾vlookup函数 图片 不能从右向左查找。 2.match与index函数 函数语法 =INDEX(数据源!B:B,MATCH(查询!A2,数据源!A:A,0)) match 负责查找 参数：找谁，查找范围（哪一列），精查找还是模糊查找（0,1，-1） 横着找和竖着找都可以。 index 负责引用 参数：查找范围（哪一列，A,B,C,D……），哪一行（1,2,3,4,5……） match+index与vlookup函数比较 使用match与vlookup函数嵌套返回多列结果 认识column函数 column就是列的意思。 第十四讲 日期函数 1.认识和日期 回归日期格式 2.日期函数 第十五讲 条件格式与公式 1.使用简单的条件格式 2.定义多重条件的条件格式 3.使用公式定义条件格式 第十六讲 简单文本函数 1.使用文本截取字符串 2.获取文本中的信息 3.关于身份证 身份证号18位 前六位代表地区 中间八位代表生日 倒数第二位是性别位 最后一位表示的是验证位 就是表示身份证的真假 原来十五位身份证 原来是六位生日 最后一位是性别位 len lenb 本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。 |
