图书 馆员 / 制图表 / 常用电子表格公式 Excel操作技巧教程

   

常用电子表格公式 Excel操作技巧教程

2010-08-18  图书 馆员
【只会简单的Excel根本不行,又一批小技巧来了!】其实,很多上班族对Excel并不很熟练,这里特地为大家整理了一些办公中最常见的Excel小技巧,非常实用!赞转给需要的小伙伴们![噢耶](网络)


 【Excel技巧】手把手教你如何快速、准确录入数据

下图为动态图,建议在有wifi的情况下查看

财务会计excel分享12期 :

涨姿势,教你下拉菜单的制作!最全面最快速的方法!

一级下拉:

二级下拉:

 


常用电子表格公式

1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")

2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)

3、从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))

4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"",""),IF(MOD(MID(C2,17,1),2)=1,"",""))公式内的“C2”代表的是输入身份证号码的单元格。

 1、求和:=SUM(K2:K56)  ——K2K56这一区域进行求和;

2、平均数:=AVERAGE(K2:K56)  ——K2 K56这一区域求平均数;

3、排名:=RANK(K2K$2:K$56)  ——55名学生的成绩进行排名;

4、等级:=IF(K2>=85,"",IF(K2>=74,"",IF(K2>=60,"及格","不及格")))

5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的平时总评期中期末三项成绩;

6、最高分:=MAX(K2:K56) ——K2K56区域(55名学生)的最高分;

7、最低分:=MIN(K2:K56) ——K2K56区域(55名学生)的最低分;

8、分数段人数统计:

1=COUNTIF(K2:K56,"100") ——K2K56区域100分的人数;假设把结果存放于K57单元格;

2  =COUNTIF(K2:K56,">=95")K57 ——K2K56区域9599.5分的人数;假设把结果存放于K58单元格;

3=COUNTIF(K2:K56,">=90")SUM(K57:K58)  ——K2K56区域9094.5分的人数;假设把结果存放于K59单元格;

4=COUNTIF(K2:K56,">=85")SUM(K57:K59)  ——K2K56区域8589.5分的人数;假设把结果存放于K60单元格;

5=COUNTIF(K2:K56,">=70")SUM(K57:K60)  ——K2K56区域7084.5分的人数;假设把结果存放于K61单元格;

6=COUNTIF(K2:K56,">=60")SUM(K57:K61)  ——K2K56区域6069.5分的人数;假设把结果存放于K62单元格;

7=COUNTIF(K2:K56,"<60") ——K2K56区域60分以下的人数;假设把结果存放于K63单元格;

说明:COUNTIF函数也可计算某一区域男、女生人数。

如:=COUNTIF(C2:C351,"") ——C2C351区域(共350人)男性人数;

9、优秀率:=SUM(K57:K60)/55*100

10、及格率:=SUM(K57:K62)/55*100

11、标准差:=STDEV(K2:K56) ——K2K56区域(55)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);

12、条件求和:=SUMIF(B2:B56,""K2:K56) ——假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;

13、多条件求和:=SUM(IF(C3:C322="",IF(G3:G322=1,1,0)))——假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(12345),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按CtrlShiftEnter组合键(产生……”){}不能手工输入,只能用组合键产生。

14、根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW( )))/360,0)

———假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。

15、在Word中三个小窍门:

①连续输入三个“~”可得一条波浪线。

②连续输入三个“-”可得一条直线。

连续输入三个“=”可得一条双直线。
一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如:
A1
1时,C1显示红色
0<A1<1
时,C1显示绿色
A1<0
时,C1显示黄色
方法如下:
1
、单元击C1单元格,点格式”>“条件格式,条件1设为:
公式=A1=1
2、点格式”->“字体”->“颜色,点击红色后点确定

条件2设为:
公式=AND(A1>0,A1<1)
3、点格式”->“字体”->“颜色,点击绿色后点确定

条件3设为:
公式=A1<0
格式”->“字体”->“颜色,点击黄色后点确定

4
、三个条件设定好后,点确定即出。
二、EXCEL中如何控制每列数据的长度并避免重复录入
1
、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置""有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"
2
、用条件格式避免重复。
选定A列,点"格式"->"条件格式",将条件设成公式=COUNTIF($A:$A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"
这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。
三、在EXCEL中如何把B列与A列不同之处标识出来?
(一)、如果是要求AB两列的同一行数据相比较:
假定第一行为表头,单击A2单元格,点格式”->“条件格式,将条件设为:
单元格数值” “不等于
”=B2
格式”->“字体”->“颜色,选中红色,点两次确定

用格式刷将A2单元格的条件格式向下复制。
B
列可参照此方法设置。
(二)、如果是A列与B列整体比较(即相同数据不在同一行):
假定第一行为表头,单击A2单元格,点格式”->“条件格式,将条件设为:
公式
”=COUNTIF($B:$B,$A2)=0
格式”->“字体”->“颜色,选中红色,点两次确定

用格式刷将A2单元格的条件格式向下复制。
B
列可参照此方法设置。
按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。
四、EXCEL中怎样批量地处理按行排序
假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作?
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以,这一问题不能用排序来解决。解决方法如下:
1
、假定你的数据在AE列,请在F1单元格输入公式:
=LARGE($A1:$E1,COLUMN(A1))
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在FJ列。如有需要可用选择性粘贴/数值复制到其他地方。
注:第1步的公式可根据你的实际情况(数据范围)作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1))
五、巧用函数组合进行多条件的计数统计
例:第一行为表头,A列是姓名B列是班级C列是语文成绩D列是录取结果,现在要统计班级语文成绩大于等于104录取结果重本的人数。统计结果存放在本工作表的其他列。
公式如下:
=SUM(IF((B2:B9999="
")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
输入完公式后按Ctrl+Shift+Enter,让它自动加上数组公式符号"{}"
六、如何判断单元格里是否包含指定文本?
假定对A1单元格进行判断有无"指定文本",以下任一公式均可:
=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"","
")
=IF(ISERROR(FIND("指定文本",A1,1)),"","") 
求某一区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数,某个数重复多次出现只算一个。有两种计算方法:
一是利用数组公式:
=SUM(1/COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter,让它自动加上数组公式符号"{}"
二是利用乘积求和函数:
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
七、一个工作薄中有许多工作表如何快速整理出一个目录工作表
1
、用宏3.0取出各工作表的名称,方法:
Ctrl+F3
出现自定义名称对话框,取名为X,在引用位置框中输入:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100)
确定
2
、用HYPERLINK函数批量插入连接,方法:
在目录工作表(一般为第一个sheet)的A2单元格输入公式:
=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
将公式向下填充,直到出错为止,目录就生成了。

 
在Excel中巧做复杂表头
在Excel中,制作表头是常有的事,但鉴于中美文化的差异,中文表格中的表头部分有时很复杂,制作起来比较困难,如果遇到要制作像图1这样的表头,那就更麻烦了。下面介绍一下笔者做工作中总结的方法。

十分复杂的表头

1.首先做斜线表头部分,确定一个4×4 的区域,画上斜线,填入文字如图2。

先填入文字

2.调整文字的对齐方式及表格各列的宽度,得到理想的表格斜线头部部分,选定该 4×4区域,设置框线,完成该部分制作,如图3。

调整文字对齐方式

3.制作横向表格头部,合理合并单元格,填入文字,确定对齐方式,完成制作,如图4。


合并单元格

4.制作纵向表格头部,与(3)类似。合理确定表格线,完成表格制作。
 
 
Excel操作技巧教程(共九章)

Excel操作技巧教程中是曾老师使用Excel多年的经验总结,意在提高大家使用电子表格的工作效率,是让大家操作Excel更快、更简便。Excel暗藏着很多精妙的设置而没有被人发现,他可以使用你的工作更快,更高效,曾老师都会把他们一一的展现在大家的面前,相信会让你眼前一亮,惊叹不已。学习此套教程,需要大家有一定的excel基础,建议先学习本站的《Excel2003教程》和《Excel函数应用教程》,再来学习这一套录制老师:曾贤志提问与交流:点击进入

 

标   题 日 期 点击
第一章:常用命令讲解 9月20日 7413
1-1a.关于录入 9月20日 71528
1-1b.关于录入 9月20日 29126
1-2a.粘贴知多少 9月20日 28488
1-2b.粘贴知多少 9月20日 20630
1-3.剪贴板 9月20日 15232
1-4a.填充 9月20日 16415
1-4b.填充 9月20日 11150
1-4c.填充 9月20日 8926
1-5.语音 9月20日 9418
1-6a.排序 9月20日 11320
1-6b.排序 9月20日 6915
1-7.记录单 9月20日 7542
1-8.分列 9月20日 6886
1-9.列表 9月20日 5866
1-10a.安全设置(单元格区域保护) 9月20日 7757
1-10b.安全设置(行列及工作表保护) 9月20日 4926
1-10c.安全设置(工作簿保护) 9月20日 4160
1-10d.安全设置(防止文件打开\删除\复制) 9月20日 4997
第二章:查找替换定位 9月20日 1059
2-1a.查找 9月20日 6380
2-1b.查找 9月20日 3422
2-1c.查找 9月20日 2954
2-2.替换 9月20日 3411
2-3a.定位(快速制作工资条) 9月20日 8223
2-3b.定位 9月20日 4042
2-3c.定位 9月20日 2800
第三章:选择性粘贴 9月20日 814
3-1.选择性粘贴之粘贴 9月20日 5570
3-2.选择性粘贴之运算    
3-3.选择性粘贴之其它 9月20日 2987
第四章:基础技巧操作实例 9月20日 904
4-1.图片(保存与生成) 9月20日 5943
4-2.单元格另类填充 9月20日 4880
4-3.用格式刷合并单元格 9月20日 4807
4-4.多行多列求和与提取数字 9月20日 4782
4-5.多单元格内容并入一个单元格 9月20日 4110
4-6.快速制作工资条 9月20日 6036
4-7.自定义格式结果转换与交叉引用查询 9月20日 2663
4-8.EXCEL三种水印加法(居中、平铺、拉伸) 9月20日 3766
4-9.其它技巧 9月20日 2996
第五章:Excel条件格式 9月20日 512
5-1.条件格式基础 9月20日 622
5-2a.条件格式运用(单据核对) 9月20日 508
5-2b条件格式运用(突破三个条件限制) 9月20日 346
5-2c.条件格式运用(行提示) 9月20日 380
5-3条件格式运用(日历设置) 9月20日 335
5-4a条件格式运用(隔行填色与重复填色) 9月20日 293
5-4b条件格式运用(数据比对) 9月20日 284
5-4c条件格式运用(当天生日提醒) 9月20日 231
5-5a条件格式(带筛选的隔行填色) 9月20日 186
5-5b条件格式(提前生日提醒) 9月20日 183
5-5c条件格式(动态标识活动单元格行列) 9月20日 190
5-6条件格式(动态标识课程表) 9月20日 190
5-7条件格式(03版突破3种到8种条件格式设置) 9月20日 165
第六章:筛选 9月20日 177
6-1a.自动筛选 9月20日 290
6-1b.自动筛选 9月20日 178
6-1c.自动筛选(通配符筛选) 9月20日 161
6-1d.自动筛选(与或条件筛选) 9月20日 158
6-2a.高级筛选基础知识 9月20日 194
6-2b.高级筛选(单列“与”的条件筛选) 9月20日 169
6-2c.高级筛选(单列“或”的条件筛选) 9月20日 112
6-2d.高级筛选(单列的“或"+"与”条件混合筛选) 9月20日 118
6-2e.高级筛选(多列的“与”条件筛选) 9月20日 96
6-2f.高级筛选(多列的“或"+"与”条件混合筛选) 9月20日 100
6-2g.高级筛选(最复杂的条件筛选) 9月20日 124
6-2h.高级筛选(公式条件筛选) 9月20日 126
6-2i.高级筛选(公式条件筛选) 9月20日 78
6-2j.高级筛选(公式+一般条件的混合筛选) 9月20日 86
6-2k.高级筛选(波形符“~”运用) 9月20日 102
第七章:数据有效性 9月20日 91
7-1a.数据有效性“数字”相关条件 9月20日 252
7-1b.数据有效性“数字”相关条件 9月20日 172
7-1c.数据有效性“数字”相关条件 9月20日 141
7-2a.数据有效性之序列(几种下拉列表的做法) 9月20日 243
7-2b.数据有效性之序列(下拉列表的切换制作) 9月20日 224
7-2c.数据有效性之序列(二级下拉菜单) 9月20日 230
7-2d.数据有效性之序列(跨表引用列表+名称) 9月20日 195
7-2e.数据有效性之序列(跨表引用函数+名称) 9月20日 169
7-3a.数据有效性之自定义(累计提示与重复限制) 9月20日 82
7-3b.数据有效性之自定义(双关键字限制) 9月20日 69
7-3c.数据有效性之自定义(位数与重复同时控制) 9月20日 64
7-3d.数据有效性之自定义(库存负数与上限控制) 9月20日 59
7-3e.数据有效性之自定义(大小写控制) 9月20日 50
7-4.数据有效性之自定义(输入法智能切换) 9月20日 67
第八章: EXCEL模拟分 9月20日 24
8-1a.单变量的模拟运算表 9月20日 100
8-1b.双变量模拟运算表(九九乘法表) 9月20日 89
8-1c.方案 9月20日 80
第九章:EXCEL规划分析 9月20日 51
9-1a.逆向分析(单变量求解) 9月20日 95
9-1b.逆向分析(规划求解) 9月20日 78
此套教程光盘版 9月18日 136
Excel操作技巧教程 9月18日 0
Excel操作技巧教程 9月18日 1

 

Excel中只选中包含文本的单元格的技巧

       在一个Excel工作表中,通常会包含许多类型的数据,诸如文本、数值、货币、日期、百分比等等,而有时会需要从这些不同类型的数据中只选中某种类型的数据,例如文本,然后对其进行删除、填充、锁定或修改格式等操作。本文要介绍的就是如何在Excel中实现只选中包含文本的单元格。
  具体操作步骤如下。

  方法一:使用“定位条件”
  1.按F5键,或选择菜单命令“编辑|定位”(也可按快捷键Ctrl+G),打开如图1所示的“定位”对话框。

Excel中只选中包含文本的单元格的技巧 - 理睬 - 理睬
图1
  2.在“定位”对话框中,单击“定位条件”按钮。
  3.在“定位条件”对话框中,选择“常量”,如图2所示,然后只选中“文本”复选框,选中后单击“确定”按钮即可。同理,如果要只选择工作表中的数字,也可以用上述同样的方法。

Excel中只选中包含文本的单元格的技巧 - 理睬 - 理睬
图2方法二:使用条件格式
  使用“条件格式”可以一次性改变特定类型数据的格式,可以改变的格式包括字体样式、下划线、删除线和颜色。例如,我们意图将工作表中所有的文本都改为红色,通常的做法是先选中这些文本,然后再改变其颜色,而使用“条件格式”会很快完成这一操作。
  1.在工作表中选择包含数据的区域。
  2.选择菜单命令“格式|条件格式”。
  3.在“条件格式”对话框中,从“条件1”下方选择“公式”,然后在右方输入框中输入=Istext(A1),如图3所示。

Excel中只选中包含文本的单元格的技巧 - 理睬 - 理睬
图3
  4.单击“条件格式”对话框中的“格式...”按钮,打开“单元格格式”对话框,然后将颜色设置为红色,如图4所示。

Excel中只选中包含文本的单元格的技巧 - 理睬 - 理睬
图4
  
5.单击“确定”按钮回到“条件格式”对话框,然后再单击“确定”按钮,关闭对话框即可。使用这种方法,当以后再次向该区域中添加文本时,文本也会自动变为红色。

这个Excel图表技巧,它实用!实用!狠实用!
这是前几天一个同学关于excel图表的一个提问,它也是很多excel图表用户很想实现的一个功能。它就是图表分层填充颜色。

【例】如下图所示的excel图表中,从下至上分三种蓝色作为背景,分别显示差(<50)、中(<100)、优(<150)3个档次。


制作方法:

1、设计源数据表,把50、100和150输入到CDE列中。


2、选取A列和E列,插入图表 - 柱状图


3、在柱子上右键单击 - 设置数据系列格式,把系列重叠调整最大,分类间距调整为0%



4、依次复制D、C、B列数据,粘到图表上,结果如下图所示。


5、选取利润系列并单击右键 - 设置数据系列格式 - 把坐标轴设置为次坐标轴,然后把分类间隔调整为20%


6、在左侧坐标轴上右键 - 设置坐标轴格式 - 最大值150

然后再把次坐轴标的最大值也设置为150,设置完成后效果:


7、选取次坐标轴,按delete键删除,然后分别修改各个系列柱子的颜色,最后添加上数据标签,最后完成效果如下图所示。


兰色说:本次图表技巧需要掌握的重点有4个

1、调整分类间距

2、调整重叠比例

3、设置次坐轴

4、设置坐标轴最大值。


【技术贴:经典Excel技巧】又一组比较经典的Excel技巧,包括阻止自动创建连接,快速设置格式,快速生成图表,手机号分段显示,给文档加密等,绝对经典,还不赶快get!

 
经典Excel公式
 经典Excel公式 - 被了 - 被了经典Excel公式 - 被了 - 被了
 
经典Excel公式 - 被了 - 被了
 
经典Excel公式 - 被了 - 被了
经典Excel公式 - 被了 - 被了
 
经典Excel公式 - 被了 - 被了
经典Excel公式 - 被了 - 被了
 
经典Excel公式 - 被了 - 被了
 
经典Excel公式 - 被了 - 被了

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    《个图VIP服务协议》

    全部>>