Excel常用功能大全（详解版）

1、Excel 常 ? 功能 ? 全（详解版）?录认 识 excel excel 格式设置Excel 查找、替换和定位Excel 排序、筛选Excel 分类汇总、数据有效性Excel 数据透视表认识 excel 公式、函数Excel 中 的 if 函 数 Excel 中的 countif 函数Sumif 函数Vlookup 函数Match 与Vlookup 嵌套使? 邮件合并Excel 常 ? 期与时间计算条件格式与公式?本函数数学函数数组Indirect 函数图表基础PPT 图表链接与动画数据透视表动态区域认识 excel1同? excel ? 作簿查看不同 sheet ? 作表中的相关数据视图

2、-新建窗? -全部重排（选择重排的 ?式：垂直并排、 ?平并排）-不同窗?显?需要对?的不同sheet ?作表。注意：两个窗 ?实际互为镜像关系，修改 ?个窗?的数据，另 ?个窗?也会跟着变。e.g 垂直并排2保存 ? 作区（ .xlw ）即保存表格的布局样式，再次打开仍是保存时的样式，保存为.xlw 格式。3? 次插 ? 多个 ? 作表点击sheet1- 长按shift- 点击sheetn- 右键插 ?作表，即实现了 ?次插?多个?作表。4移动某列到同? 作表的不同位置选中整列 -长按shift- ?标呈现?字箭头-移动到需要的地?5快速到达 ? 作表的边界（前提：单元格是连续的）选中某?单

3、元格-?标呈现?字箭头-四个?向都可以双击6快速选中有效单元格（前提：单元格是连续的） 选中某?单元格-长按 ctrl+shift+ 不同的?向键7填充Ctrl+ ；是当 ? 期，左键拖拽填充，右键拖拽可以选择填充的规则8编辑 ? 定义选项-?级 - 编辑?定义 -左边新序列 - 输?新序列规则e.g 张三 -enter- 李四- enter - 王五- enter 车-赵六- enter 注意：每个数值后 ?定要enterexcel 格式设置1单元格内画斜线单元格内画 ?条斜线单元格内填写好内容 -设置单元格格式 -边框斜线 -alt+enter 对不同内容进 ?分?-空格移动内容位置单元格

4、内画多条斜线直接插?形状注意：设置单元格 ?的边框斜线可以随单元格变化，?插?的形状斜线是不会随单元格变化的2设置单元格格式（数值）设置单元格格式 -数值 -千位分隔符（ 1,000,000 ）货币-货币符号（￥）会计专 ?（会计专 ? 与货币相似，只是会计专? 的货币符号在单元格的最左侧）? 期（ microsoft 采 ? 的是 1900-1-1 的 ? 期，所有 ? 期变换成的数字都是距离 1900-1-1 的天数）特殊（直接转换中? 写）? 定义 ; 隐藏aaaa 星期 “? ”aaa ? ”“yyyy-mm-dd xx （年） -xx （ ?） -xx （ ?） yyyy"

5、 年"m" ? "d ”? ” x年x xx ? xx? 市”在单元格内容后? 加“市 ”单元格 ?的数字可以按照数值的正负标注成不同的颜? 3设置单元格格式（? 本）注意：单元格格式 ?的数值与 ?本不可以来回切换?本数值转换为数值，单元格左上?的警惕号-转换成数字有时txt会转换到 xlsx 中，此刻 ?般会?到分列e.g 在excel 中， ? 本格式的 2019-3-28 ? 期转换成 2019 年 3? 28 ?选中某列分列 -分列结束（此处可以不进 ?实际分列，只是转换下格式）-设置单元格格式（转换成 ?期格式）Excel 查找、替换和定位1替换 颜?

6、字体替换替换-?级 -填充-全部替换 精确替换替换-?级 -单元格匹配（即精确匹配，查找内容为单元格?的全部内容）-替换 模糊替换替换-?级 -格式（模糊替换的格式， e.g张*、李？等） -替换为注意：？表 ?个字符，*表?多个字符，？可以表 ?两个字符在代码?， 后?的通配符（ */?）不?效， e.g某?叫张 *替换为张经理2添加批注插?的是?般形状的批注：右键-插?批注 -编辑批注内容（右键可编辑、删除、显?/隐藏批注） 审阅?可显? /隐藏所有批注插?的是特殊形状的批注：插? -形状（随便添加 ?个形状）-绘图?具（格式） -编辑形状（右键） -添加到快速访问 ?具栏编辑批注 -更改

7、形状注意：批注也可以设置格式3定位（ ctrl+g ） 批注选中所有带批注的单元格 公式选中所有带公式的单元格 对象同时选中表中所有的图? 空值?般?到单元格的合并与拆分选中合并的单元格 -合并后居中（即拆分为最 ?单元格）-定位空值（即选中了所有空的单元格）-=（即等于各个最 ?单元格相邻的上 ?的值）-ctrl+entere.gExcel 排序、筛选1? 定义排序主要key- 依据-次序次要key- 依据-次序注意： ?定义排序 ?也可以按照颜 ?排序e.g 在成绩等排序时经常有字段的重要性及数据的重复性，因此经常? 到依次从后向前排序的? 法，即依次向前直接点击排序。（第?列数据并排，第

8、 ?列?；第?列数据并排，第三列 ? ）e.g 部门 ? 定义排序?定义排序-依据数值 -次序（新序列， ?编写） 2把第 ? 表头插 ? 到每 ? 的数据中e.g ? 资条先做出对应数量的表头（放在数值下 ?） -给数值?和表头?添加?列（数值不重复且表头 ?的数据和数值 ?的数据交叉） -?定义排序新添加的列3打印时在第? 页 ? 动添加表头页?设置 -?作表 - 顶端标题 ?（选择表头） 4筛选e.g 筛选 ? 车间、 ? 车间 五车间、财务部、销售部等数据中的车间数据右键-?本筛选 - 结尾是（车间）等于（ *车间）5数据 ? 级筛选数据-?级筛选 -?式（将筛选结果复制到其他地 ?）

9、-列表区域（要筛选的区域） - 条件区域（如果是去重复值的，此处不 ?填写） -复制到（结果存放的地 ?）注意：去重要勾选选择不重复的记录6? 级筛选多个条件先复制粘贴出这些条件到某 ?区域 a（两个或多个条件是and 关系写在同 ?，两个或多个条件是or关系写在不同?）数据-）?级筛选 -?式 -列表区域 -条件区域（复制出来的条件区域a）-复制到注意：此刻不 ?选择不重复记录Excel 分类汇总、数据有效性1分类汇总前? 定要注意先排序数据-分类汇总 -分类字段 -汇总?式 - 选定汇总项¨替换当前分类汇总¨汇总结果显? 数据下? 2对多个字段进? 分类汇总时注意：对多

10、个字段进 ?定义排序不要勾选下 ?的替换当前分类汇总分类字段与选定汇总项不同有时会粘贴汇总的结果，注意定位可见单元格3使 ? 分类汇总批量合并内容相同的单元格排序-分类汇总 -（除去表头）定位空值- 合并后居中 -分类汇总全部删除 -格式刷刷格式 ?分类字段4数据有效性设置A列仅能输? 5001000 之间的整数设置B列仅能输 ?字符串长度为8位的产品编码设置C 列付款?式中仅能输 ?现?、转账、 ?票6数据有效性的出错警告注意 ：取消表中的格式（数据有效性-全部清除）Excel 数据透视表1创建数据透视表插? -数据透视表 -右键数据透视表选项（显 ?为经典数据透视表布局 ?便使?） -拖拉

11、字段直接到数据透视表中的相应位置 -可更改计数、求和等 ?式注意双击数据区域中的某 ?单元格可显 ?该单元格的详细信息5设置某张表的数据有效性 （保护表格数据不被修改）选中整张表格 -数据有效性 -允许（ ?定义）-公式（随便输?）e.g 双击数据区域的单元格338 ，在新的 ? 作表中会显?出单元格338 的详细信息注意：数据透视表右侧的 ?具栏不 ?被关掉后，点击数据表中的任何区域，右键显?字段列表2数据透视表中创建组3汇总多列数据拉不同的字段 ?不同的列，若是排在了同 ?列只需拉 ?后?列即可注意：数据透视表可嵌套不同的数据模板（美化图表的?具）4创建计算字段数据透视表 -选项- 域、项

12、?和集 -计算字段（名称 '新列名称 、公式 '双击字段写公式 ）注意：删除某 ?或列，在透视表右侧 ?具栏，右键删除计算结果可以更改格式对于错误值可以选择不显?e.，g #DIV/0 ！，右键 - 数据透视表选项 -布局和格式 -格式（相对错误值显? '? ）4批量 ? 次性创建多张? 作表并命好名称（前提名称在同? 张 ? 作表中的同? 列）插? -数据透视表 - 字段（拖 ?数据透视表的最上 ?）-数据透视表 -选项 -选项-显?报表筛选页 -选中字段 -确定同时删除表格 ?的内容Shift 键选中所有? 作表-复制空 ? 粘贴覆盖掉表中的数值创建组认识 exce

13、l 公式、函数1选中 -F4- 锁定 即实现绝对应?F42基本函数公式Sum/average/count/max/min/rank注意： rank 使?时?般会?到绝对引?rank （参数 ,区域） 跳跃式计算要先定位空值，再ctrl+enterExcel 中的 if 函数1if（ logical-test,value-if-true,value-if-false）if中可以嵌套 2、3个if2iserror 判断对错经常与 if连?3and 函数（ and ? 可以添加多个条件）4or 函数5and 与or函数Excel 中的 countif函数1countif （ range,criter

14、ia ）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( 条件区域，条件，

15、求和区域)注意： 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 模糊匹配对于数据来说只匹配 ?于该数据的最 ?值，即最接近该数据的 ?值注意：模糊匹配时，查找区域的数据

16、要从?到?排列?般?在计算提成 ?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注意： ?般还是转换成统 ?的格式进

17、?匹配6横向的数据? hlookup 函数7Vlookup 计算个税Match 与Vlookup嵌套使?1vlookup 只能实现左侧是 id 引 ? 右侧的数据，且只能引? 数值2match 与index 嵌套可实现左右两侧的引?，且可引 ? 图? Match （ lookup_value ， lookup_array ， match_type ）即查找位置lookup_value：需要在数据表（ lookup_array）中查找的值，也可以是?本lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某? 或某 ?列，即必须为?维数据match_type：表 ?查询的

18、指定 ?式， ?数字 -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 为单元格区域

19、，必须为?维数据Row_num为数组中某 ?的?序号Column_num是数组中某列的列序号Index 与match 嵌套Match查找， index引?世界上本? vlookup，? 的 index 与match的?多了，便形成了vlookup注意：嵌套时经常 ?到绝对引? 3match与vlookup返回多列结果注意：嵌套时的混合引?Match 也可查找 ? 本邮件合并1excel 中的数据批量填充到 word ? 档中邮件-邮件合并 -邮件合分步向导 -下?步开启 -下?步选取收件? - 浏览需要导 ?的 excel 表格-双击 excel 表（注意： 如果选择错了表格，点击选择另外的表

20、格） -下?步撰写信函 -其他项?选择需要插 ?的字段 -预览结果 -再次预览结果可以返回编辑界?-完成并合并 - 编辑单个 ? 档-合并到新 ? 档-? 成 ? 个新的 ? 档（ ? 页中包含 ? 条数据）-完成并合并 - 发送电 ? 邮件-选择收件 ?，发邮件-? 录 - 完成并合并 -编辑单个 ? 档-合并到新 ? 档-? 成 ? 个新 ? 档（ ? 页中包含很多数据） 2邮件合并后的资? 期格式处理ALT+F9 查看邮件中 ? 期或者资 ? 的源代码，再次ALT+F9 是返回原界 ?，返回之后注意单击 F9 进 ? 刷新Excel 常 ? 期与时间计算1计算结束时间excel 中的整数

21、时间是代表“天”所,以【 “ 9天0 ” /2?4时 /60 分钟】2计算时长注意：设置单元格格式为常规3计算结束 /开始 ? 期注意： ?期在 excel 中其实是 ?个数字，所以可以直接相加减4计算 ? 龄 =datedif （start_serial_number,end_serial_number,return_type 【” y ” , “ m】”）, “ d”=datedif （开始时间，结束时间， ” ym” ,“ md”） ,“ yd ”指除去整年剩“余的ym”? 数指除去整 ?“剩余m的d”天数5计算间隔年?6计算第 ? 周=weeknum （ serial_number,

22、return-type）7计算周? =weekday(serial_number,return_type)注意：写好公式后要设置成星期的格式8第 ? 周周?9? 定义周? =text(serial_number,” aaaa ”) 注意：先设置 ?定义星期 aaaa10 ? 定义 ? 期 =text(serial_number,” 0000-00-00”) 注意：先设置 ?定义?期0000-00-0011 根据间隔 ? 份计算结束? 期=date （year,month,day ） 求年份 =year(serial_number)? 份 =month(serial_number)? =day(

23、serial_number)12 计算本 ? 最后 ? 天=date （ year,month,day ）注意：本 ?最后?天即为下 ?的前?天day=0 即为下 ? 的前 ? 天day=1 即为下 ? 的第 ? 天13 计算本 ? 天数14 计算本 ? 剩余天数条件格式与公式1为数据透视表中的数据制作数据条和切? 器注意：在插 ?数据透视表时要选中 ?空值的单元格，否则透视表中会出现空 ?的?和列在数据透视表中经常对? 期列进 ? 右- 创键建组 -按 ? 或者季度分组制作数据条：选中数据-条件格式 -数据条插?切?器：选中数据-插? -切?器（切?器也可看做是筛选器，可 ?来添加新的维度）

24、添加的新维度可以切换（筛选）2条件格式类型 突出显 ?单元格规则>/</<<（注意： min< 介于=<max ） /=/? / 发 ? 期 / 重复值 项?选取规则选取数据中的 max/min/max%/min%/>average/<average 数据条即筛选器，添加新的数据维度 ? 阶?般?于处理温度，表 ?随着数据的升降，颜 ?的深浅跟着改变 图标集 新建规则对应? 数据条? 突出显 ?，可以查找错误值 #DIV!0? Max.min? ? 较选定的值? 重复值? 公式选中需要设置条件格式的字段（注意：在选中数据时?般不要选中表头，条件格

25、式中的公式经常?到混合引?） 管理规则即条件格式中的公式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=本年

26、的总天数 -到今天为 ? 除去到某 ? 期整年后剩余的天数 =到今天为 ? 本年剩余的天数?本函数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) 求字节

27、个数注意：英 ?字母 /数字/符号?个字符都代表 ?个字节， ?汉字是?个字符代表两个字节2? 本函数结合运? ? 份证倒数第? 个偶数代表? 性，奇数代表男性15 位的是倒数第? 位，18 位的是倒数第? 位? 截取单位 len/lenb? 截取特定字符前后注意： 100 是取巧，因为后 ?要截取的字符已知不超过100 个? 截取地区码注意： ?本函数*1才能当做数学函数运算? 计算出 ? 年? date(year,month,day)? 求 ? 份证性别 mod 是求余函数数学函数1数学函数包含? =Round(number,num-digits)，求四舍五?，num-digits 四舍五

28、 ? 后的 ? 数点个数? =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 函

29、数应?注意： row 与column 后的数字是随单元格位置的变化?变化的?的转置也可选中数据-复制 -移到需要粘贴的位置 -选择性粘贴 -转置注意： row* （相隔的 ?数 +1）注意： row* （相隔的 ?数 +1）， column- 与位置有关的规律数数组1数组即为矩阵，数组外? 定要加 ? 括c号trl+shift+enter注意： ?个值可以与 ?组值相?较计算，true=1,false=0, 因此计算结果的 true/false*1 可以与其他值进 ?计算注意： sumproduct= ?括号 +sumSumproduct 返回相应的数组或区域乘积的和2lookup(looku

30、p_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单元格的引?In

31、direct 与index 的引 ? 较2引 ? 多表中的数据问题注意： & 字符的运 ?，因为A:G 是不变的，所以要 ”注意：多表引 ?时的混合引? 3indirect 与数据有效性? 选中省份下? 的城市- 公式-定义名称（注意定义的名称要与indirect 引 ? 的内容 ? 致）? 给每列设置数据有效性（每列的标题先不要填写，在最后有限性设置完，再取消第? 的标题栏的有效性） -序列-序列内容的区域? 后 ? 列的有效性 -序列 -indirect( 前列对应单元格 )图表基础1图表中的元素2了解主次坐标轴注意：主次坐标轴的刻度与刻度的max/min 3折线图与柱形图结合图表

32、4制作计划于实际对? 图注意：坐标轴的刻度单位5制作双向柱形图（旋风图）插? -条形图 - 设置次坐标轴 -次坐标轴的刻度固定 ?-删掉上?的次坐标轴 -下?的主坐标轴设置数值格式为0%;0%- 分类轴（ y轴）标签设置为 ?或者低 -逆刻度值是指分类轴的上下换位置-复制背景图 ?前先设置背景图?的艺术效果5利 ? 复制粘贴更改数据系列显? 样式制作好条形图后直接插 ?形- 复制-粘贴到原条形区域 -右键-设置数据系列格式 -填充-层叠注意：若想拉开 ?形间的距离可以在 ?形形状上在插 ?个?填充的矩形若插 ? 的 ? 填充矩形不好选中 -开始- 查找与选择 -选择对象 -点击矩形 ? 概区域

33、6? 特图（ ? 般 ? 在项 ? 进度上）插?堆积条形图 -把?期条设置成完全隐藏的格式-设置坐标轴的刻度 -设置分类轴的逆刻度-?期刻度的固定值?是按照?期的数字格式确定的6.1 动态 ? 特图动态?特图?条形分为3段（隐藏的 ?期段、已完成、未完成）-所以选取的是计划开始时间、已完成、未完成数据（注意 if函数的应 ?） -c18 ?般是指当 ?的?期- 插?的滚动条刻度设置为 ?期的刻度范围（注意是实际范围，不是数值 ?）-滚动条链接 ?个空单元格-c18 与空单元格之间建 ?定的联系（c18=b2+ 空单元格）7巧 ? 图表模板注意：上 ?的这些图表都可以粘贴到excel 中作为模板使? 粘贴到 excel 中-图表?具 -另存为模板附加：图? -右键- ?和属性-属性- 对象位置 -?和位置随单元格 ?变坐标轴 -右键- 设置坐标轴格式 -显 ? 单位PPT 图表链接与动画1双坐标柱形图插?簇状柱形图 -设置主次坐标轴 -选择次坐标轴 - 选择数据 -添加两个系列值为 0的新系列 -图表?具布局-选中?个系列值为 0的系列 -设置所选内容格式为主/次坐标轴 -选中?个系列 -选择数据 -把?个空的新序列向上移注意：把系列值为 0的图例删掉2饼图美化插?三维饼图-右键三维旋转 -取消?动缩放 -?度调? -右键-