最常用的72大Excel操作使用技巧方法汇总

2017-04-27 01:02 阅读 8,664 次 评论 0 条

1、Excel判断分数成绩是否及格,怎么做?

答:excel判断分数成绩是否及格可以用IF进行区间判断。

=IF(A1>60,"及格","不及格")

2、Excel频率统计用什么函数?

答:FREQUENCY以一列垂直数组返回某个区域中数据的频率分布,具体用法回复frequency或 频率查看示例。

3、Excel什么时候要用$符号呢?

答:复制公式时,单元格的引用位置不想发生变化时,就在行号或列标前加$,了解详情回复 “绝对引用”查看教程

4、合并单元格后里面的数据或文字我都想保留如何处理?

答:多个单元格都含有内容,如果要在合并后保留所有单元格的内容,可以用下面的方法。

1.选取单元格区域,并把列宽拉到可以容下所有单元格合并后的宽度。

2.开始选项卡 - 编辑 - 两端对齐。把多个单元格的内容合并到一个单元格中

3.在分隔的空隔处按alt+enter键添加强制换行符,换行。

4.再合并单元格

5、插入表格后发现行数不够,怎样才能再次快速插入更多行?

答:需要插入多少行就选取多少行,然后就会一次插入多少空行

6、合并单元格后里面的数据或文字我都想保留如何处理

答:多个单元格都含有内容,如果要在合并后保留所有单元格的内容,可以用下面的方法。

1.选取单元格区域,并把列宽拉到可以容下所有单元格合并后的宽度。

2.开始选项卡 - 编辑 - 两端对齐。把多个单元格的内容合并到一个单元格中

3.在分隔的空隔处按alt+enter键添加强制换行符,换行。

4.再合并单元格

7、如何复制粘贴行宽

答:粘贴后的粘贴选项中,会有 保留源列宽的选项。如下图所示

8、Excel如何把数值批量转换成文本

答:数据 - 分列 - 第三步选文本

9、Excel如何把空值全部填写为“0“

答:定位 - 条件 - 空值 - 在编辑栏中输入 0,按 ctrl+enter完成输入

10、Excel - 2010的插入特殊符号在哪里?

excel2010版本的特殊符号, 很多同学在搜这个问题 ,可以肯定的说excel2010版本里没有这个命令的。大家不用再费心找了,如果要插入符号,可以从插入选项卡 - 符号 里找找。在微信平台回复“特殊符号”可以查看更多符号输入方法

11、Excel如何去掉地址栏的名称

excel如何去掉地址栏的名称,excel2003版,插入菜单 - 名称 - 定义 - 在弹出的窗口找到该名称 点删除按钮 ,2010版,公式选项卡 - 名称管理器 -找到名称点删除

12、如何按日期显示星期?

问:excel如何按日期显示成星期

答:公式

=TEXT(A2,"aaaa")

=WEEKDAY(A2,2)

也可以通过格式设置把日期显示成星期

13、Excel如何隐藏公式?

在excel里,如何隐藏公式,让公式别人看不到呢?

答:在excel里隐藏公式是通过设置单元格格式设置的。

选取公式所在单元格,右键菜单中点设置单元格格式,然后在弹出的单元格格式窗口点保护选项卡,勾选隐藏公式选项。

最后通过工具 - 保护 -保护工作表(excel2010里是通过 审阅 - 保护工作表)。然后在单元格或编辑栏里就看不到公式了。

14、Excel表格中下拉复制数字时,为什么不变大呢?怎么才能递增?

答:复制数字时,只需要按ctrl键再下拉,数字就会发生变化了。

15、Excel中平方函数是哪个?

excel中平方函数是哪个?

在excel中我们有时需要计算一个数的平方,该怎么计算呢,excel提供了两种方法

1 使用脱字节符号^

例 3的2次方可以写成 =3^2 结果是9

2 使用平方函数

=POWER(3,2) 表示3的2次方,如果是4的3次方可以写为=POWER(4,3)

16、Excel合并单元格复制公式怎操作呀

excel表格中,如果想在合单元格中复制公式,会提示大小一样中断你的复制,这时候我们可以用快捷键填充公式。

选中所有包含合并单元格的区域(第一个单元格要含公式),双击编辑栏中公式,然后按ctrl+回车键填充即可。

17、Excel数字不能求和,怎么办?

数据导入Excel中后居然是以文本形式存在的(数字默认是右对齐,而文本是左对齐的),即使是重新设置单元格格式为数字也无济于事。

下面的方法可以帮你快速转换成可以计算的数字

选取数字区域,第一个单元格一定是包括数字的,而且单元格左上方有绿三角,选取后打开绿三角,点转换为数字。如下图所示

18、Excel产生随机数,怎么做?

答:excel提供了一个可以生成随机数的函数rand,用它可以生成指定范围的随机数

=rand()*(最大数-最小数)+最小数。比如生成 10~100之间的随机数

=rand()*90+10

如果要生成随机的整数呢

=int(rand()*90+10 ) 也可以用=RANDBETWEEN(1,100)

19、Excel中如何开根号

问:excel里开根号怎么做,例如 9开2次方,结果是3

答:在excel里开根号可以用^符号完成,如

9^(1/2)的结果是3

20、Excel筛选用不了

问:在excel中使用自动筛选时,可用不了了,怎么回事?

答:导致筛选无法使用有很多原因,最常见的原因是工作表或工作簿被保护了,你可以检查一下是不是工作表添加了保护密码。

21、四分之一怎么打

答:先输入0 然后再输入空格,再输入1/4。

22、Excel中如何限制使用筛选按钮

答:excel中如何限制使用筛选按钮呢?答案可能出乎大家的意料之外,如果你是EXCEL2003的用户,可以执行:工具菜单 - 选项 - 视频 - 对象选择全部不显示,你再看看自动筛选已先法再使用了。

23、Excel偶数行怎么选取和删除?

答:excel选取偶数行有很多方法,但最好的方法还是辅助列+自动筛选方法。具体的步骤为

1.在最后的辅助列中,在2行输入数字1,选取1行和2行向下拖动复制,复制后辅助列的偶数行为填充成1。

2.筛选 - 自动筛选,选取值为1的行。

3.选取所有显示的偶数行区域。

如果是删除偶数行,直接删除筛选出的偶数行即可。

24、Excel里隐藏的第一行显示出来

答:选取第二行,然后拖动向上选取,取消隐藏,或者 全选,格式 - 行 - 取消隐藏

25、Excel打开默认设置成03版,如果做

设置默认打开03版的方法;

到excel 2003 的安装目录,把03版的excel.exe 改成 excel2003.exe,然后就可以按正常的方法设置默认打开excel 文件为03版的

26、Excel表格计算公式不能用了

表格中的公式仍然存在,但是更改了其中一个单元格的数字,其公式结果怎么不变了呢?就是两两相乘的公式,之前都是自动更新出结果,现在不得行了

输入新的数字,仍然是原来的结果

答:工具菜单 - 选项 - 重新计算 你看看是手动还是自动

27、Excel每页显示表头,怎么设置?

问:在打开表格时,因为表格比较长,怎么在每一页的最上面设置表头。

答:在EXCEL打印预览视图中,打开“页面设置”对话框中的“工作表”标签,单击“顶端标题行”文本框右侧的[压缩对话框]按钮,选定表头和顶端标题所在的单元格区域,再单击该按钮返回到“页面设置”对话框,单击[确定]按钮。

28、Excel多条件求和函数有哪些

在EXCEL里,有哪些函数可以完成多条件求和呢?

SUMIF函数和COUNTIF函数用法差不多.多条件的处理方法如下:

1.多项目求和

=SUM(SUMIF(B31:B35,{"A","C"},C31:C35))

2.03版本可以用SUMPRODUCT函数替代.

=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)

3.07版本可以用SUMIFS替换:

=SUMIFS(D2:D11,A2:A11,"公司1",B2:B11,"人事部")

多条件汇总,也可以用SUMPRODUCT函数完成。

=SUMPRODUCT(条件1 *条件2*条件3*数据列)

=sumproduct((a1:a10="财务")*(b1:b10="出纳")*c1:c10)‘统计A列是财务,同时B列是出纳的C列数据之和

29、两列数据查找相同值对应的位置

=MATCH(B1,A:A,0)

30、已知公式得结果

定义名称=EVALUATE(Sheet1!C1)

已知结果得公式

定义名称=GET.CELL(6,Sheet1!C1)

31、强制换行

用Alt+Enter

32、超过15位数字输入

这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入'

33、如果隐藏了B列,如果让它显示出来?

选中A到C列,点击右键,取消隐藏

选中A到C列,双击选中任一列宽线或改变任一列宽

将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。

34、Excel中行列互换

复制,选择性粘贴,选中转置,确定即可

35、Excel是怎么加密的

(1)、保存时可以的另存为>>右上角的"工具">>常规>>设置

(2)、工具>>选项>>安全性

36、关于COUNTIF

COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:A10,">=90")

介于80与90之间需用减,为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90")

37、根据身份证号提取出生日期

(1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15, DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号"))

(2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1

38、想在SHEET2中完全引用SHEET1输入的数据

工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2。

39、一列中不输入重复数字

[数据]--[有效性]--[自定义]--[公式]

输入=COUNTIF(A:A,A1)=1

如果要查找重复输入的数字

条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色

40、直接打开一个电子表格文件的时候打不开

“文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上

41、Excel下拉菜单的实现

[数据]-[有效性]-[序列]

42、 10列数据合计成一列

=SUM(OFFSET($A,(ROW()-2)*10+1,,10,1))

43、查找数据公式两个(基本查找函数为VLOOKUP,MATCH)

(1)、根据符合行列两个条件查找对应结果

=VLOOKUP(H1,A1:E7,MATCH(I1,A1:E1,0),FALSE)

(2)、根据符合两列数据查找对应结果(为数组公式)

=INDEX(C1:C7,MATCH(H1&I1,A1:A7&B1:B7,0))

44、如何隐藏单元格中的0

单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵,如果用公式就要看情况了。

45、多个工作表的单元格合并计算

=Sheet1!D4+Sheet2!D4+Sheet3!D4,更好的=SUM(Sheet1:Sheet3!D4)

46、获得工作表名称

(1)、定义名称:Name

=GET.DOCUMENT(88)

(2)、定义名称:Path

=GET.DOCUMENT(2)

(3)、在A1中输入=CELL("filename")得到路径级文件名

在需要得到文件名的单元格输入

=MID(A1,FIND("*",SUBSTITUTE(A1,"","*",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))))+1,LEN(A1))

(4)、自定义函数

Public Function name()

Dim filename As String

filename = ActiveWorkbook.name

name = filename

End Function

47、如何获取一个月的最大天数

:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01

数据区包含某一字符的项的总和,该用什么公式

=sumif(a:a,"*"&"某一字符"&"*",数据区)

最后一行为文本:

=offset($b,MATCH(CHAR(65535),b:b)-1,)

最后一行为数字:

=offset($b,MATCH(9.9999E+307,b:b)-1,)

或者:=lookup(2,1/(b1:b1000<>""),b1:b1000)

评委打分中,如何去掉两个以上最高分,两个以上最底分,求剩余人员的平均分?同时显示出被去掉的分数。

看看trimmean()函数帮助。

被去掉的分数:

最大两个:=large(data,)

最小两个:=small(data,)

怎样很简单的判断最后一位是字母

right(a1)*1

出错的字母

=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母")

=IF(ISERR(RIGHT(A1)*1),"字母","数字")

如何 设置单元格,令其不接受包含空格的字符

选定A列

数据——有效性——自定义——公式

=iserror(find(" ",a1))

数据--有效性--自定义--公式

=len(a1)=len(trim(a1))

原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22……

现在是=sum(n(offset(a1,(row(1:10)-1)*3,)))

在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?

=INDIRECT("A1"&"!"&"E1") A1为工作表名

奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))

偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))

查看字符串字数

=LEN(A1)

求非空单元格数量

公式计算出来的数据,COUNTA不能用的(否则空字符也计算进去了)

=COUNTIF($E:$E536,"?*")

动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.

=SUM(INDIRECT("A1:A"&ROW()-1))

48、比较好用的Excel文档修复工具

excelRecovery

49、 Excel开方运算

将8开3次方,可以用这个公式,在单元格中输入=8^(1/3)

50、单元格中的数据分散对齐

文本格式》全角输入

51、查找工作表中的链接

Ctrl+~ 或编辑》链接

52、如何让空单元格自动填为0

选中需更改的区域》查找》空》替换》0

53、把word里的数字转换到excel

方法有多种,选中》复制》设置输入单元格为文本》选择性粘贴》值

选中》表格转换为文本》粘贴》分列》对分列选项设置为文本

另存为文本文件》excel中打开文本文件》对导入文本对话框进行对应设置

54、查找重复内容公式

=IF(COUNTIF(A:AA2)>1”重复””")。

55、用出生年月来计算年龄公式

=TRUNC((DAYS360(H6”2009/8/30″FALSE))/3600)。

56、从输入的18位身份证号的出生年月计算公式

=CONCATENATE(MID(E274)”/”MID(E2112)”/”MID(E2132))。

57、从输入的身份证号码内让系统自动提取性别

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

58、求和

=SUM(K2:K56) ——对K2到K56这一区域进行求和;

59、平均数

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

60、排名

=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;

61、等级

=IF(K2>=85”优”IF(K2>=74”良”IF(K2>=60”及格””不及格”)))

62、学期总评

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

63、最高分

=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;

64、最低分

=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;

65、分数段人数统计:

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

(2) =COUNTIF(K2:K56”>=95″)-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;

(3)=COUNTIF(K2:K56”>=90″)-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;

(4)=COUNTIF(K2:K56”>=85″)-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;

(5)=COUNTIF(K2:K56”>=70″)-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;

(6)=COUNTIF(K2:K56”>=60″)-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;

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

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

如:=COUNTIF(C2:C351”男”) ——求C2到C351区域(共350人)男性人数;

66、优秀率

=SUM(K57:K60)/55*100

67、及格率

=SUM(K57:K62)/55*100

68、标准差

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

69、条件求和

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

70、 多条件求和

{=SUM(IF(C3:C322=”男”IF(G3:G322=110)))} ——假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求 一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。

71、根据出生日期自动计算周岁

=TRUNC((DAYS360(D3NOW( )))/3600)

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

72、函数COUNT计数

函数COUNT计数时,将把数值型的数字计算进去;但是错误值、空值、逻辑值、日期、文字则被忽略。如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值,请使用函数COUNTA(COUNTIF按EXCEL的说明也行,但常出毛病)。

示例(一)  1、我要是写成=COUNT(B1,D1),那就是计算机B1和D1两个单元格中有几个数字(不包括C1单元格),

2、但是如果我写成=COUNT(B1:D1),注意,中间用冒号了,那就是计算机从B1单元格到D1单元格中数字的个数了,(这就包括数字单元格了)

3、再有,我写成=COUNT("B1","D1","123","hello"),那结果就是1,因为只有"123"一个数字,B1和D1因为加了引号,所以是字符了,不是单元格。

4、如果A1为1,A5为3,A7为2,其他均为空,则:

COUNT(A1:A7) 等于 3 备注:计算出A1到A7中,数字的个数

COUNT(A4:A7) 等于 2 备注:计算出A4到A7中,数字的个数

COUNT(A1:A7, 2) 等于4 备注:计算A1到A7单元格和数字2一起,一共是多少个数字(A1到A7中有3个,加上数字2,一共4个)

示例(二)  在数据库(sql server),它的格式为:count(),括号里表示要统计的对象。

如果括号内是用星号(数字键8上面那个),就表示统计所有的内容。如果是个具体的某一行或列的内容,则表示该行或者列的内容。(例:count(学生),则表示统计所有学生的个数)。

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:最常用的72大Excel操作使用技巧方法汇总 | 算法君

发表评论


表情