个人总结EXCEl实战技巧由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“常用excel技巧总结”。
基本功能 条件格式: 快速比较不同区域的数值
（一）单元格数值>>.>”不等于”>>>引用单元格 标记不存在人员:“=COUNTIF($F$2:$F$7,$B4)=0” 标记重复录入=COUNTIF($A$1:$A2,$A2)>1 快速比较不同区域的数值
（二）公式>>>>” =OR(EXACT(G21,$H$21:H$30))=FALSE”
在工作日历中突显周末日: =”WEEKDAY(G40,2)>5”,” =WEEKDAY(G40,2)=6,” =WEEKDAY(G40,2)=7”
工作表中活动单元格所在行列高亮显示
“=(ROW()=CELL(“row”))+(COLUMN()=CELL(“col”))” CELL(“ROW”)的用途是返回工作表中活动单元格的行号 CELL(“COL”)的用途是返回工作表中活动单元格的列号
需要VBA代码来实现让工作表在活动单元格发生改变时执行重新计算 Private Sub Worksheet_SelectionChange(ByVal Target As Range)Calculate End Sub
工作表中特定单元格所在行列高亮显示 =($J14=“小计”)+(J$14=“小计”)>0 上述公式使用的是相对引用，目的是判断J列与第一行中是否会出现“小计”字样，如果出现，则在相同的行、列中应用醒目的格式。=($J14=“小计”)+(J$14=“小计”)=OR(($J14=“小计”),(J$14=“小计”))
永恒间隔颜色: “=AND(LEN($A2)>0,MOD(ROW(),2)=1” “=MOD(SUBTOTAL(3,B$2:B2),2)=0”
日期提醒7前后
“=ABS(DATE(YEAR(TODAY()),MONTH($K30),DAY($K30))-TODAY())
特殊数值提醒
单元格数值>>.>”不等于”>>>引用单元格
名称:1.插入名称 2.使用名称框 3.指定名称 名称命名的为与不为规则：
1.名称可以是任意字符与数字组合在一起，但不能以数字开头，更不能以数字作为名称，如1ecfo。同时名称不能与单元格地址相同如,B3。如果要以数字开头，可在前面加上下划线，如 _1ecfo。2.不能以字母R,C,r,c作为名称，因为R,C,在R1C1引用样式中表示工作表的行，列。3.名称中不能包含空格，可以用下划线或者点号代替。
4.不能使用除下划线，点号和反斜号(/)以外的其他符号，允许用问号(？)，但不能作为名称的开头，如yhy?就可以，而?yhy就不可以。
5.被定义为Print_Titles的区域将成为当前工作表的打印的顶端标题行和左端标题行。6.被定义为Print_Area的区域将被设置为工作表的打印区域。
数据有效性: 杜绝负数库存” =R15
此公式的含义是：用单元格数值与匹配相应产品的库存数量比较，只有小于或等于时菜满足允许输入销售数量的条件。此公式还有作用，如果A列中输入了库存表中不存在的产品，也将不允许输入销售数量
强制序时录入” =MAX($V$18:$V18)”输入的日期必须大于当列中最大的.多级选择录入
“=$Z$6:$AD$6”上级(标题)“=OFFSET($Y$7，MATCH($AF7,$Z$6:$AD$6,),COUNTA(OFFSET($Y$7，MATCH($AF7,$Z$6:$AD$6,),100)))”分级(内容)COUNTA(OFFSET(A1，,10,2))相当于计数A1:B10 分解:A1移0行0列的10行2列数据
在单元格中创建下拉列表
快速切换有效性的来源:” =OFFSET(List.1，A6-1)” E6为list编号,共三列LIST.1 List.1 List.2 List.3 甲 甲 A 1 乙 B 2 丙 C 3 丁 D 4 戊 E 5 己 F 6 庚 G 7 辛 H 8 壬 I 9 癸 J 10 动态源的数据有效性” =INDIRECT(“B”&5)相当于B5的值.=INDIRECT(“A10”)始终对单元格 A10 进行引用
1.图表方面: A.定义名称:date=”INDIRECT(“AutoData!$B$2:$B$”&AutoData!$E$15)” Sale=”INDIRECT(“AutoData!$C$2:$c$”&AutoData!$E$15)” B.设置水平滚动条(窗体)>>>>设置控件格式>>>控制 C.设置数据源:
2.单元格方面:CODE与CHAR 相互转换 A设置显示单元格=”DEAN”
B.定义名称DEAN”=INDIRECT(“Sheet1!$”&CHAR(Sheet1!$L$4)&“$4”)” 数据源在同行不同列 或定义名称GUANG=INDIRECT(“Sheet1!$B”&“$”&Sheet1!$M$4)数据源在同列不同行 C.设置水平滚动条(窗体)>>>>设置控件格式>>>控制
限制重复值的录入
“=COUNTIF($A$2:$B$11,M9)=1”
针对双关键字限制重复值
“=SUM(N($M27&“|”&$N27=$M$27:$M$36&“|”&$N$27:$N$36))=1”
在合并两个单元格的内容时增加了字符“|”，目的是为了避免因两个条件直接组合而出现本不应该出现的雷同。如业务员“李五”和客户“光商场”的组合，与业务员“李五光”和客户“商场”的组合。
数据分析
排序与筛选
规则：要先排序优先级较低的，或排序排序优先级最高的。
对于相同比划数的汉字，EXCEL按照其内码顺序排列，而不是按照比划顺序进行的排列。筛选，自动筛选；工具，保护，保护工作表，勾选“使用自动筛选”选项。
排序的常见的故障
1.没有正确选择数据区域，而自动选择的区域中包含有空格。2.内存不足：
增加电脑的物理内存；
优化电脑的性能：如关闭暂时不需要的其他程序，删除临时文件；
减少排序的数据区域。
3.数据区域中包含有格式化为文本的数字。4.排序区域包含合并单元格。分类汇总
使用分类汇总功能以前，必须先对数据列表中需要进行分类的字段进行排序，本表是已经排序后的状态！F5 定位条件 选定可见单元格
1.两次分类汇总，第二次取消替除当前分类汇总
说明：
分类汇总的实质是，EXCEL为数据列表创建汇总项，并使用分级显示视图，当汇总要求较简单时，它不失为分析数据的得力工具。
但如果数据列表较庞大，并且汇总要求较为复杂时，更好的分类汇总工具是数据透视表。多层次的数据浏览 选中数据区域
“数据”——“组与分级显示”——“自动建立分级显示”。取消：
“数据”——“组与分级显示”——“清除分级显示”。在受保护的工作表中调整分级显示视图 Private Sub Workbook_Open()Worksheets(“分类汇总2”).Protect Paword:=“pwd”, userinterfaceonly:=True Worksheets(“分类汇总2”).EnableOutlining = True End Sub
数据透视表
用二维表创建数据透视表 快速统计重复项目
在数据透视表种添加计算项 自动刷新数据透视表
方法1.工作簿打开时刷新透视表：“数据透视表选项”。方法2.单个透视表的刷新：“查看代码”VBA代码 private sub worksheet_activate()activesheet.pivottables“数据透视表4”.pivotcache.refresh end sub 注意““数据透视表4””不是指的工作表，是透视表的名字（在“表选项”）
函数
统计求和
统计选定区域数据的个数
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},$A2))find函数用于查找数字字符在单元格中是否存在； count函数用于统计引用区域或数组中数值的个数。
=IF(COUNTA($A22:$D22)=4,“完毕”,“”)=IF(COUNTBLANK($A23:$D23),“”,“完毕”)
认识COUNTIF函数 公式 含义
=COUNTIF(A41:A59,8)数值8的单元格个数，文本008也算 =COUNTIF(A41:A59,“>8”)数值大于8的单元格个数 =COUNTIF(A41:A59,“>=8”)数值大于等于8的单元格个数 =COUNTIF(A41:A59,“>”&A5)数值大于A5的值8的单元格个数 =COUNTIF(A41:A59,“8”)不等于8(含文本008)的所有单元格个数 =COUNTIF(A41:A59,“”)非真空单元格个数，相当于COUNTA =COUNTIF(A41:A59,“”“”)区域内所有单元格个数 =COUNTIF(A41:A59,“=”)真空单元格个数
=COUNTIF(A41:A59,“”)真空及空文本（假空）单元格个数 =COUNTIF(A41:A59,“>
=COUNTIF(A41:A59,”*“)文本（含空文本）单元格个数 =COUNTIF(A41:A59,”*8*“)包含字符8的文本单元格个数 =COUNTIF(A41:A59,”a?“)以a开头且只有两个字符的单元格个数 =COUNTIF(A41:A59,”?B*“)第2个字符为b的单元格个数 =COUNTIF(A41:A59,A10&”*“)以A10单元格字符开头的单元格个数 =COUNTIF(A41:A59,”??“)字符长度为2的文本单元格个数 =COUNTIF(A41:A59,TRUE)内容为逻辑值TRUE的单元格个数 =COUNTIF(A41:A59,#DIV/0!)被0除错误的单元格个数 =COUNTIF(A41:A59,”#DIV/0!“)被0除错误的单元格个数
认识SUMIF函数 公式
含义
=SUMIF(A2:A10,”A001“,C2:C10)汇总工号为”A001“的销售量
=SUMIF(A2:A10,”A001“,C2)简写方式，汇总工号为”A001“的销售量
=SUMIF(A2:A10,A2,C2:C10)汇总工号为A2单元格的销售量
=SUMIF(A2:A10,”A001“,C3)简写方式，汇总工号为”A001“的销售量，其中A2对应C
3、A3对应C4„„A10对应C11
=SUMIF(A2:A10,”A*“,C2:C10)汇总工号以”A“开头的销售量
=SUMIF(B2:B10,”?珠*“,C2:C10)汇总商品名称第2个字为”珠“的销售量
单字段多条件求和
单日最高销售量为：“=MAX(SUMIF(A190:A198,A190:A198,B190:B198))”
单日销售最高的日期：”=INDEX(A190:A198,MATCH(D190,SUMIF(A190:A198,A190:A198,B190),0))”数组
使用通配符模糊求和
SUMIF(A217:A224, B228&”*“&C228&”*“,B217:B224)B228和C228是包含的关键字
乘法口决表(混合引用)=IF(B$1>$A2,”“,B$1&”*“&$A2&”=“&B$1*$A2)
查找与引用
VLOOKUP VLOOKUP的模糊查找；且 range_lookup 为 TRUE(1)，则使用小于等于 lookup_value 的最大值
VLOOKUP的数组查找。”=VLOOKUP(A29,{0,”差“;60,”中“;80,”良“;90,”优“},2,TRUE)”
VLOOUP IF 综合应用: “=IF($B50
INDEX ； MATCH ；ADDRESS “=INDEX(E77:G85,MATCH(B78,D77:D85,0),MATCH(B79,E76:G76,0))”
“=ADDRESS(MATCH($B78,$D$76:$D$85,0),MATCH($B79,$E$76:$G$76,0)+4)”
=HYPERLINK(”#LINK1“,”定位“)LINK1为名称,”定位”为标签
返回最后一条数据记录”=LOOKUP(G197,IF(A196:A204=G196,B196:D204))”
在数据清单中隔行（列）插入一个空行: “=IF(MOD(ROW(),2)=0,”“,OFFSET(Sheet1!$A$1,INT(ROW()/2),COLUMN()-1))” 在数据清单中隔行（列）插入两个空行
“=IF(MOD(ROW(),3)=0,”“,(IF(MOD(ROW(),3)=2,”“,OFFSET(Sheet1!$A$1,INT(ROW()/3),COLUMN()-1))))”
特别函数: =A2&REPT(“#”,16-LEN(A2))A2结尾处用”#”号填充至16位 = REPT(“#”,16-LEN(A2))&A2 A2左侧用”#”号填充至16位
=REPT(”#“,8-LEN(A2)/2)&A2&REPT(”#“,16-LEN(A2)/2)A2侧用”#”号填充至16位
TEXT(A1,”AAAA”)日期星期星期几
查找?或*前面要加~
DATEDIF(A1,TODAY(),”Y”))计算过去某一天至今的时间
DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)当月天数30或31
ATL+;复制当前选中的显示区域 ATL+ENTER
批量文本格式至数值>>>>>批量加上0即可转换
自定义显示格式变成值>>>CTRL+C 两次>>>全部粘贴>>>编辑/选择性粘贴>>>文本
简单数组公式(CTRL+ATL+ENTER)计算数学大于50小于60的人数(C列是数学成绩){=SUM((C2:C15>=50)*(C2:C15
排名函数 分数 100 100 90 94 98 94 93 排名 1 1 5 3 2 3 4 {=SUM(IF($B$1:$H$1
分别计算各商品的销售额，可利用数组公式来实现(H列是商品,I列单价,J列数量){=SUM(IF(H13:H21=”商品1“,I13:I21*J13:J21,0))}
求各班级的语文优秀者 96分以上为优秀,用数组公式示例如下(E列语文分数,I列班级){=SUM(($E$35:$E$43>=96)*($I$35:$I$43=TEXT($B47,0)))}
计算B产品8月份销量(B列产品,C列日期,E列销量){=SUM(($B$65:$B$73=”B“)*(MONTH($C$65:$C$73)=8)*($E$65:$E$73))}
计算8月份前不包括,B产品销量和8月后,不包括C产品销量：
{=SUM(IF(((MONTH($C$65:$C$73)($B$65:$B$73=”B“))*((MONTH($C$65:$C$73)>=8)($B$65:$B$73=”C")),$E$65:$E$73))}
数组四舍五入求和 {=SUM(ROUND(J11:J19,1))}
一组数据中不重复数据的个数 {=SUM(1/COUNTIF(B8:K9,B8:K9))}
去丢一个最小值及最大值求和：
=SUM(B9:K10)-SUM(SMALL(B9:K10,{1,1}))-SUM(LARGE(B9:K10,{1,1}))
求取范围下列数值中小于0或大于5的数值之和(B为数值区域){=SUM(IF((b5),b))}
求区域(B7:K8)中文本单元格的个数{=SUM(IF(ISTEXT(B9:K10),1,0))}
www.ChinaHRD.Net 散发人性道德光辉 沉淀人类管理智慧轻松运用EXCEL建立人事信息库接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以自动生成相......
所谓超级短线，当天买当天卖或今天买明天卖就是它的形象写照，选超级短线股的原则就是要选择市场的强势股龙头股，做超短线不考虑个股的基本面，属纯技术分析。一、要有敏锐的市场......
第一讲 培训师层级划分及培训课程设计步骤一、培训师的三个层次按着西方的标准培训师分为三个等级。作为一个刚入门的培训师，他的基本能力是比较差的。作为初级培训师，在学员......
以下是跑业务零单的失败的原因： 任何一个单子谈成搞定的是一家公司的综合实力，而不是某个业务员单独能完成的！因为家装业务员不是卖西瓜，业主点头你就可以卖了。这中间还要有设......
企业员工培训实战技巧 培训的方法要根据培训的人数、培训的专业及单位现有的师资、设备、资源等方面的情况而定。培训计划可以采取业余的时间学习，也可以采取在职培训或离职......
