|
目录 认识excel1同一excel工作簿查看不同sheet工作表中的相关数据 视图-新建窗口-全部重排（选择重排的方式：垂直并排、水平并排）-不同窗口显示需要对比的不同sheet工作表。 注意：两个窗口实际互为镜像关系，修改一个窗口的数据，另一个窗口也会跟着变。 e.g垂直并排
2保存工作区（.xlw） 即保存表格的布局样式，再次打开仍是保存时的样式，保存为.xlw格式。
3一次插入多个工作表 点击sheet1-长按shift-点击sheetn-右键插入工作表，即实现了一次插入多个工作表。 4移动某列到同一工作表的不同位置 选中整列-长按shift-鼠标呈现十字箭头-移动到需要的地方 5快速到达工作表的边界（前提：单元格是连续的） 选中某一单元格-鼠标呈现十字箭头-四个方向都可以双击 6快速选中有效单元格（前提：单元格是连续的） 选中某一单元格-长按ctrl+shift+不同的方向键 7填充 Ctrl+；是当日日期，左键拖拽填充，右键拖拽可以选择填充的规则
8编辑自定义 选项-高级-编辑自定义-左边新序列-输入新序列规则 e.g张三-enter-李四- enter -王五- enter车-赵六- enter…… 注意：每个数值后一定要enter excel格式设置1单元格内画斜线 ①单元格内画一条斜线
单元格内填写好内容-设置单元格格式-边框斜线-alt+enter对不同内容进行分行-空格移动内容位置 ②单元格内画多条斜线
直接插入形状 注意：设置单元格里的边框斜线可以随单元格变化，而插入的形状斜线是不会随单元格变化的 2设置单元格格式（数值） 设置单元格格式-数值-千位分隔符（1,000,000） 货币-货币符号（￥） 会计专用（会计专用与货币相似，只是会计专用的货币符号在单元格的最左侧） 日期（microsoft采用的是1900-1-1的日期，所有日期变换成的数字都是距离1900-1-1的天数） 特殊（直接转换中文大小写） 自定义 ;;; 隐藏 aaaa星期“几” aaa“几” yyyy-mm-dd xx（年）-xx（月）-xx（日） yyyy"年"m"月"d”日” xx年xx月xx日 @”市” 在单元格内容后面加“市” 单元格里面的数字可以按照数值的正负标注成不同的颜色 3设置单元格格式（文本） 注意：单元格格式里面的数值与文本不可以来回切换 文本数值转换为数值，单元格左上角的警惕号-转换成数字 有时txt会转换到xlsx中，此刻一般会用到分列 e.g在excel中，文本格式的2019-3-28日期转换成2019年3月28日 选中某列分列-分列结束（此处可以不进行实际分列，只是转换下格式）-设置单元格格式（转换成日期格式） Excel查找、替换和定位1替换 ① 颜色字体替换 替换-高级-填充-全部替换 ② 精确替换 替换-高级-单元格匹配（即精确匹配，查找内容为单元格里的全部内容）-替换 ③ 模糊替换 替换-高级-格式（模糊替换的格式，e.g张*、李？等）-替换为 注意：？表示一个字符，*表示多个字符，？？可以表示两个字符 在代码里，~后面的通配符（*/?）不生效，e.g某人叫张*替换为张经理
2添加批注 插入的是一般形状的批注： 右键-插入批注-编辑批注内容（右键可编辑、删除、显示/隐藏批注） 审阅里可显示/隐藏所有批注 插入的是特殊形状的批注： 插入-形状（随便添加一个形状）-绘图工具（格式）-编辑形状（右键）-添加到快速访问工具栏 编辑批注-更改形状 注意：批注也可以设置格式 3定位（ctrl+g） ① 批注 选中所有带批注的单元格 ② 公式 选中所有带公式的单元格 ③ 对象 同时选中表中所有的图片 ④ 空值 一般用到单元格的合并与拆分 选中合并的单元格-合并后居中（即拆分为最小单元格）-定位空值（即选中了所有空的单元格）-=↑（即等于各个最小单元格相邻的上面的值）-ctrl+enter e.g
Excel排序、筛选1自定义排序 主要key-依据-次序 次要key-依据-次序 注意：自定义排序里面也可以按照颜色排序 e.g在成绩等排序时经常有字段的重要性及数据的重复性，因此经常用到依次从后向前排序的方法，即依次向前直接点击排序。 （第一列数据并排，第二列大小；第二列数据并排，第三列大小……） e.g部门自定义排序 自定义排序-依据数值-次序（新序列，自己编写） 2把第一行表头插入到每一行的数据中 e.g工资条 先做出对应数量的表头（放在数值下面）-给数值行和表头行添加一列（数值不重复且表头行的数据和数值行的数据交叉）-自定义排序新添加的列
3打印时在第二页自动添加表头 页面设置-工作表-顶端标题行（选择表头） 4筛选 e.g筛选一车间、二车间…五车间、财务部、销售部等数据中的车间数据
等于（*车间） 5数据高级筛选 数据-高级筛选-方式（将筛选结果复制到其他地方）-列表区域（要筛选的区域）-条件区域（如果是去重复值的，此处不用填写）-复制到（结果存放的地方） 注意：去重要勾选选择不重复的记录 6高级筛选多个条件 先复制粘贴出这些条件到某一区域a（两个或多个条件是and关系写在同一行，两个或多个条件是or关系写在不同行） 数据-）高级筛选-方式-列表区域-条件区域（复制出来的条件区域a）-复制到 注意：此刻不用选择不重复记录
Excel分类汇总、数据有效性
数据-分类汇总-分类字段-汇总方式-选定汇总项 ¨ 替换当前分类汇总 ¨ 汇总结果显示数据下方 2对多个字段进行分类汇总时 注意：对多个字段进行自定义排序 不要勾选下面的替换当前分类汇总 分类字段与选定汇总项不同 有时会粘贴汇总的结果，注意定位可见单元格 3使用分类汇总批量合并内容相同的单元格 排序-分类汇总-（除去表头）定位空值-合并后居中-分类汇总全部删除-格式刷刷格式至分类字段 4数据有效性
6数据有效性的出错警告
注意 ：取消表中的格式（数据有效性-全部清除） Excel数据透视表1创建数据透视表 插入-数据透视表-右键数据透视表选项（显示为经典数据透视表布局方便使用）-拖拉字段直接到数据透视表中的相应位置-可更改计数、求和等方式 注意双击数据区域中的某一单元格可显示该单元格的详细信息 e.g 双击数据区域的单元格338，在新的工作表中会显示出单元格338的详细信息
注意：数据透视表右侧的工具栏不小心被关掉后，点击数据表中的任何区域，右键显示字段列表 2数据透视表中创建组 3汇总多列数据
拉不同的字段至不同的列，若是排在了同一列只需拉至后一列即可 注意：数据透视表可嵌套不同的数据模板（美化图表的工具）
4创建计算字段 数据透视表-选项-域、项目和集-计算字段（名称'新列名称’、公式'双击字段写公式’）
注意：删除某一行或列，在透视表右侧工具栏，右键删除 计算结果可以更改格式
对于错误值可以选择不显示，e.g #DIV/0！，右键-数据透视表选项-布局和格式-格式（相对错误值显示'无’）
4批量一次性创建多张工作表并命好名称（前提名称在同一张工作表中的同一列） 插入-数据透视表-字段（拖至数据透视表的最上行）-数据透视表-选项-选项-显示报表筛选页-选中字段-确定
同时删除表格里的内容 Shift键选中所有工作表-复制空白行粘贴覆盖掉表中的数值 创建组
认识excel公式、函数1选中-F4-锁定 即实现绝对应用
2基本函数公式 Sum/average/count/max/min/rank 注意：rank使用时一般会用到绝对引用 rank（参数,区域） 跳跃式计算要先定位空值，再ctrl+enter Excel中的if函数1if（logical-test,[value-if-true],[value-if-false]） if中可以嵌套2、3个if 2iserror判断对错经常与if连用
3and函数（and里面可以添加多个条件）
4or函数
5and与or函数
Excel中的countif函数1countif（range,criteria）
2countifs(range1,criteria1, range2,criteria2……) 2条件格式
条件格式-新建规则-使用公式确定要设置格式的单元格-公式-格式 3设置数据有效性 e.g在A列设置不允许输入重复值 数据-数据有效性-自定义-公式（=countif(A:A,a1)<2） 4countif与countifs的区别 Countif是满足单个条件 Countifs是满足多个条件=COUNTIFS(C2:C22,">=80",D2:D22,">=80") Sumif函数1sumif(条件区域，条件，求和区域) 注意：sumif与countif都是之统计前15位，注意在条件上添加&’*’ 2sumif(A:A,j5&k5,G:G)，针对多个条件 3sumifs(求和区域，条件区域，条件1，条件2…) 4设置sumif的数据有效性
出库量不能大于实际库存量 Vlookup函数1vlookup中第二区域若不是整列，要绝对引用 2只有关键字的匹配(连接通配符) *代表字符或无字符 Vlookup(A2&”*”,数据源！B:E,4,0) 3vlookup模糊匹配 对于数据来说只匹配小于该数据的最大值，即最接近该数据的小值 注意：模糊匹配时，查找区域的数据要从小到大排列 一般用在计算提成方面 3数值格式转化成文本格式 数值只能计算，文本可以连接，若对数据进行连接，excel会自动把数值当成文本来对待，所以【数值&””】可以转化成文本 4文本转换成数值【文本*1】【--文本】即负负文本得正文本 5对于格式不同的数据进行匹配
公式 =IF(ISNA(VLOOKUP(I2*1,$E$2:$G$6,3,0)),VLOOKUP(I2&"",$E$2:$G$6,3,0),VLOOKUP(I2*1,$E$2:$G$6,3,0)) Isna()函数是判断括号里的结果是否是#N/A 注意：一般还是转换成统一的格式进行匹配 6横向的数据用hlookup函数
7Vlookup计算个税
Match与Vlookup嵌套使用1vlookup只能实现左侧是id引用右侧的数据，且只能引用数值 2match与index嵌套可实现左右两侧的引用，且可引用图片 ① Match（lookup_value，lookup_array，match_type）即查找位置 lookup_value：需要在数据表（lookup_array）中查找的值，也可以是文本 lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据 match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况（为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列；为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列；为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列）
② Index（array，row_num,[column_num]）即引用 Array为单元格区域，必须为一维数据 Row_num为数组中某行的行序号
Column_num是数组中某列的列序号
③ Index与match嵌套 Match查找，index引用 世界上本无vlookup，用的index与match的人多了，便形成了vlookup
注意：嵌套时经常用到绝对引用 3match与vlookup返回多列结果
注意：嵌套时的混合引用 Match也可查找文本 邮件合并1excel中的数据批量填充到word文档中 邮件-邮件合并-邮件合分步向导-下一步开启-下一步选取收件人-浏览需要导入的excel表格-双击excel表（注意：如果选择错了表格，点击选择另外的表格）-下一步撰写信函-其他项目选择需要插入的字段-预览结果-再次预览结果可以返回编辑界面 -完成并合并-编辑单个文档-合并到新文档-生成一个新的文档（一页中包含一条数据） -完成并合并-发送电子邮件-选择收件人，发邮件 -目录-完成并合并-编辑单个文档-合并到新文档-生成一个新文档（一页中包含很多数据） 2邮件合并后的资金日期格式处理 ALT+F9查看邮件中日期或者资金的源代码，再次ALT+F9是返回原界面，返回之后注意单击F9进行刷新 Excel常用日期与时间计算1计算结束时间
excel中的整数时间是代表“天”,所以【“90天”/24小时/60分钟】 2计算时长
注意：设置单元格格式为常规 3计算结束/开始日期
注意：日期在excel中其实是一个数字，所以可以直接相加减 4计算工龄=datedif（start_serial_number,end_serial_number,return_type【”y”,[“m”],[“d”]】） =datedif（开始时间，结束时间，”ym”,[“md”],[“yd”]） “ym”指除去整年剩余的月数 “md”指除去整月剩余的天数
5计算间隔年月日
6计算第几周=weeknum（serial_number, return-type）
7计算周几=weekday(serial_number,return_type)
注意：写好公式后要设置成星期的格式 8第几周周几
9自定义周几=text(serial_number,”aaaa”) 注意：先设置自定义星期aaaa
10自定义日期=text(serial_number,”0000-00-00”) 注意：先设置自定义日期0000-00-00
11根据间隔月份计算结束日期=date（year,month,day） 求年份=year(serial_number) 月份=month(serial_number) 日=day(serial_number)
12计算本月最后一天=date（year,month,day） 注意：本月最后一天即为下月的前一天 day=0即为下月的前一天 day=1即为下月的第一天
13计算本月天数
14计算本月剩余天数
条件格式与公式1为数据透视表中的数据制作数据条和切片器 注意：在插入数据透视表时要选中非空值的单元格，否则透视表中会出现空白的行和列 在数据透视表中经常对日期列进行右键-创建组-按月或者季度分组 制作数据条：选中数据-条件格式-数据条
插入切片器：选中数据-插入-切片器（切片器也可看做是筛选器，可用来添加新的维度）
添加的新维度可以切换（筛选） 2条件格式类型 ① 突出显示单元格规则 >/</<<（注意：min<介于=<max）/=/⊆/发生日期/重复值 ② 项目选取规则 选取数据中的max/min/max%/min%/>average/<average ③ 数据条 即筛选器，添加新的数据维度 ④ 色阶 一般用于处理温度，表示随着数据的升降，颜色的深浅跟着改变 ⑤ 图标集 ⑥ 新建规则对应 Ø 数据条 Ø 突出显示，可以查找错误值#DIV!0 Ø Max.min Ø 比较选定的值 Ø 重复值 Ø 公式 选中需要设置条件格式的字段（注意：在选中数据时一般不要选中表头，条件格式中的公式经常用到混合引用） ⑦ 管理规则即条件格式中的公式 2利用条件格式将日期为周末的标记为红色 选中日期（除表头）-条件格式-新建规则-公式=WEEKDAY(A2,2)>5 将周末整行标记为红色 选中所有数据（除表头）-条件格式-新建规则-公式= WEEKDAY($A2,2)>5 利用条件格式标记未来15天内将要过生日的员工 =DATEDIF(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31),"d")-DATEDIF(B2,TODAY()-1,"yd")<=15 =本年的总天数-到今天为止除去到某一日期整年后剩余的天数=到今天为止本年剩余的天数 文本函数1文本函数包含 Ø =Left(text,num_chars)，num_chars从左至右截取的字符数，左截取 Ø =Right(text,num_chars)，右截取 Ø =mid(text,start_num,num_chars), start_num查找字符串文本中的起始位置，中间截取 Ø =find(find_text,within_text,[start_num))，find_text要查找的字符，求某一字符的位置 Ø =len(text)求字符个数 Ø =lenb(text)求字节个数 注意：英文字母/数字/符号一个字符都代表一个字节，而汉字是一个字符代表两个字节 2文本函数结合运用 Ø 身份证倒数第二个偶数代表女性，奇数代表男性 15位的是倒数第一位，18位的是倒数第二位
Ø 截取单位len/lenb
Ø 截取特定字符前后
注意：100是取巧，因为后面要截取的字符已知不超过100个 Ø 截取地区码
注意：文本函数*1才能当做数学函数运算 Ø 计算出生年月date(year,month,day)
Ø 求身份证性别mod是求余函数
数学函数1数学函数包含 Ø =Round(number,num-digits)，求四舍五入，num-digits四舍五入后的小数点个数 Ø =roundup(number,num-digits)，无条件向上进位 Ø =rounddown (number,num-digits)，无条件向下舍去 Ø =Int(number)，取整，结果都是小于该值的整数 Ø =mod(number,divisor)=mod(被除数，除数)，求余 2row与column函数 =row()求行数 =column()求列数 注意：match()查找与特定字符有关，row()与column()与位置有关 3round、roundup、rounddown函数应用
4int函数应用
5row与column函数应用
注意：row与column后的数字是随单元格位置的变化而变化的 一行的转置也可选中数据-复制-移到需要粘贴的位置-选择性粘贴-转置
注意：row*（相隔的行数+1）
注意：row*（相隔的行数+1），column-与位置有关的规律数 数组1数组即为矩阵，数组外一定要加大括号ctrl+shift+enter 注意：一个值可以与一组值相比较计算，true=1,false=0,因此计算结果的true/false*1可以与其他值进行计算
注意：sumproduct=大括号+sum Sumproduct返回相应的数组或区域乘积的和 2lookup(lookup_value,lookup_vector,[result_vector]) lookup_value,要查找的内容 lookup_vector,要查找的区域 result_vector,返回需要的某列 注音：lookup没有第四参数，即没有精确/模糊匹配参数，一般lookup都是模糊
E4=$A$2:$A$92是true/false (E4=$A$2:$A$92)*1是1/0 0/((E4=$A$2:$A$92)*1)是0/#DIV0!(即错误值) 而lookup只能查找正确的数据
Indirect函数1indirect(ref_text,[a1]),ref_text单元格的引用 Indirect与index的引用比较
2引用多表中的数据问题
注意：&字符的运用，因为A:G是不变的，所以要””
注意：多表引用时的混合引用 3indirect与数据有效性
Ø 选中省份下面的城市-公式-定义名称（注意定义的名称要与indirect引用的内容一致） Ø 给每列设置数据有效性（每列的标题先不要填写，在最后有限性设置完，再取消第一行的标题栏的有效性）-序列-序列内容的区域 Ø 后面列的有效性-序列-indirect(前列对应单元格) 图表基础1图表中的元素
2了解主次坐标轴 注意：主次坐标轴的刻度与刻度的max/min 3折线图与柱形图结合图表
4制作计划于实际对比图
注意：坐标轴的刻度单位 5制作双向柱形图（旋风图）
插入-条形图-设置次坐标轴-次坐标轴的刻度固定大小-删掉上面的次坐标轴-下面的主坐标轴设置数值格式为0%;0%-分类轴（y轴）标签设置为高或者低-逆刻度值是指分类轴的上下换位置-复制背景图片前先设置背景图片的艺术效果 5利用复制粘贴更改数据系列显示样式
制作好条形图后直接插入心形-复制-粘贴到原条形区域-右键-设置数据系列格式-填充-层叠 注意：若想拉开心形间的距离可以在心形形状上在插入一个无填充的矩形 若插入的无填充矩形不好选中-开始-查找与选择-选择对象-点击矩形大概区域 6甘特图（一般用在项目进度上）
插入堆积条形图-把日期条设置成完全隐藏的格式-设置坐标轴的刻度-设置分类轴的逆刻度-日期刻度的固定值大小是按照日期的数字格式确定的 6.1动态甘特图
动态甘特图里条形分为3段（隐藏的日期段、已完成、未完成）-所以选取的是计划开始时间、已完成、未完成数据（注意if函数的应用）-c18一般是指当日的日期-插入的滚动条刻度设置为日期的刻度范围（注意是实际范围，不是数值大小）-滚动条链接一个空单元格-c18与空单元格之间建立一定的联系（c18=b2+空单元格） 7巧用图表模板 注意：上面的这些图表都可以粘贴到excel中作为模板使用 粘贴到excel中-图表工具-另存为模板 附加：图片-右键-大小和属性-属性-对象位置-大小和位置随单元格而变 坐标轴-右键-设置坐标轴格式-显示单位 PPT图表链接与动画1双坐标柱形图
插入簇状柱形图-设置主次坐标轴-选择次坐标轴-选择数据-添加两个系列值为0的新系列-图表工具布局-选中一个系列值为0的系列-设置所选内容格式为主/次坐标轴-选中一个系列-选择数据-把一个空的新序列向上移 注意：把系列值为0的图例删掉 2饼图美化
插入三维饼图-右键三维旋转-取消自动缩放-高度调小-右键-三维格式-棱台-右键-数据标签居中 3双层饼图
双层饼图哪一个在上面先做哪一个 插入二维饼图-选择数据-添加（注意：系列值选择添加的是后饼图的数据）-新系列的水平轴标签选择的是新分类-右键设置前饼图为次坐标轴-向外同时拖动次坐标轴可以看到后面的主坐标轴-单个向内拖动次坐标轴-添加数据标签 注意：要设置边框 4图表插入到PPT中 Ø 复制图表-直接粘贴到PPT中（此时的格式是随PPT的主体系列更改的） Ø 复制图表-PPT中保留原格式（格式不随PPT的主题格式变化） Ø 复制图表-PPT中保留链接数据（若更改excel中的原数据，PPT中的表格工具设计里刷新数据可以直接在PPT中更改图表样式） Ø 复制图表-保留原格式和链接数据（若更新excel中的数据，在新打开PPT时会提示是否更新数据，此时的更新是整个PPT中的数据都进行更新，不用再单个更新） 5PPT中图表的动画设置 选中图表-动画-动画进入方式-动画-动画窗格-右键-效果选项-图表动画-组合图表中选择按分类中的元素（或其他）-在动画窗格里可以看到多个动画对象-选中第二个对象shift至图表中的最后一个对象-右键-从上一项开始之后开始-播放 数据透视表动态区域1 OFFSET(reference,rows,cols,height,width) e.g以A1单元格为例，OFFSET（A1，1,1,2,2）即以A1单元格为参照物，下移1行，右移1列，选取两行两列，所以offset得到的是数据区域 把整个表格用offset定义为一个动态数据区域后，可以插入一个数据透视表-表/区域填写为定义的动态数据区域 选取动态数据区域，用offset（$A$1，0,0，counta($A:$A),b） Counta($A:$A)代表A列非空单元格数，b代表选取的列数 定义名称动态数据区域，编辑公式为offset 然后在数据透视表中选中某行数据刷新即可随着表格及时更新 2offset函数经常与数据透视表、各种条形折线图连用 注意：有时一个表格会用到两个offset函数来定义名称 e.g取后10天的成交量（后10天的数据随着数据的增删而变，所以是动态图）
定义名称-日期（=offset($A$1,counta($a:$a)-10,0,10,1)） 定义名称-成交量（=offset($B$1,counta($a:$a)-10,0,10,1)） 插入空白柱形图-选择数据-添加新系列（成交量，=表名称!定义的名称） 水平轴标签编辑（=表名称!定义的名称） |
|
|
来自： 昵称66704360 > 《待分类》
