1、连接符&
A | B | C | 效果 | 输入的公式 |
1 | 2 | 3 | 123 | =A1&B1&C1 |
1 | 2 | 3 | 1-2-3 | =A2&“-”&B1&“-”&C1 |
2、行列转置报错
在全选表格数据,将其以转置形式复制到另一张表时,有时会在粘贴时报错:无法在此处粘贴此内容,因为复制区域和粘贴区域的大小不同。
出现这个错误的原因是,全选,选错了,ctrl+A全选数据时若选的是整个表格(有内容的表格+空白表格),就会报错,选的是有数据内容的全部表格,就不会报错。
将光标定位的有内容的任一表格,再进行全选,选择的就是有数据内容的全部表格。
3、在excel中快速选中包含数据和指定区域的单元格
1)选中所有包含数据的单元格
用鼠标选中包含数据的单元格,任意单元格即可(必须是包含数据的),此时同时按下CTRL和A键,就会自动选取所有包含数据的单元格。
请注意:如果初始鼠标所在的区域是数据单元格以外,那么按下CTRL和A键则会选择整张表格,而非是数据单元格,如下图。
2)选择指定行的数据
用鼠标选中指定单元格,同时按下CTRL、SHIFT和→键,选择的区域就是从单元格开始的向右的同一行中的数据。
3)选择指定列的数据
用鼠标选中指定单元格,同时按下CTRL、SHIFT和下方向键,选择的区域就是从单元格开始的向右的同一行中的数据。
从2)3)可以看出,想选择哪个方向的数据就在组合键中按哪个方向即可。也可以通过上下左右同时选中整个含数据的单元格。非常有趣也非常有用。
4、将公式计算出来的错误值变成0或不显示
IFERROR函数
5、vlookup函数、sumif函数、offset函数
这几个函数是我最近一段时间在用的,具体用法不详述了。只是说一件事情。
昨天在用vlookup函数的时候发现计算结果与我自己手算的结果不一致,怎么都找不出原因,后来才发现是公式的最后一个参数没有选择true还是false,不选择的话,默认是true就是模糊匹配,这样结果就不对了。
想说点啥呢,就是这些函数的用法我以为我记住了,实际并没记住,就算当时理解了记住了,过段时间也忘了。所以,下次用这函数前,要么注意下Excel提示出的这些公式的参数,要么百度下这些函数的各参数的解释,做到函数的准确使用,这样才能保证计算的结果是准确的。
5、将一列有1000个数据自动变成100列,每列10个数据
=INDIRECT(ADDRESS((COLUMN(A$1)-1)*10+ROW($A1),1))
C1粘贴这个公式,下拉到C10;然后选中C1:C10,整体右拉100列。
公式中涉及到四个函数,分别是indirect,address,column,row
excel+百度,简直无敌了。我今天遇到一个类似的任务,在复制了20多次后,终于忍不住百度了下,结果还真百度到了,真是提高工作效率啊。百度到的案例不一定完全适用于自己的情况,需要自己修改,修改前先了解下涉及到的函数的用法(主要是函数内各参数的含义),就很容易改了。
6、选择性复制表格对角线、对角线上方、对角线下方数据
强大的IF函数
=IF(ROW(A1)=COLUMN(A1),A1,"") 为复制表格对角线数据
=IF(ROW(A1)<COLUMN(A1),A1,"") 为复制表格对角线上方数据
=IF(ROW(A1)>COLUMN(A1),A1,"") 为复制表格对角线下方数据
将鼠标移动到键入公式的单元格右下角,鼠标会变成“十”字号,分别向下向右拖动,即可实现对表格对角线数据的选择性复制。
7、删除一个区域中的0值,并且其余单元格左移
再次感受到,当Excel中的操作需要重复3次以上时,一定请记得百度。
1)选中要操作的区域; 2)ctrl+F查找全部的0值;3)在查找的窗口中对查找结果crtl+A进行全选;4)回到表中,发现所有的0值的单元格已变成了灰色,在灰色区域任意位置,单击鼠标右键-删除-选择右边单元格左移。
8、右键-列宽、隐藏
列宽设置大些可以将某些显示不出来的数据显示出来,比如单元格内时间数据,如果单元格列宽太小,会显示为####。
隐藏功能则可以将不想显示出来但又不想删除的列隐藏起来。
9、统计有内容的单元格个数
=COUNTA(B:B) //统计出B列有文字的单元格个数
=COUNTIF(B:B,"灭霸") //统计出B列中灭霸的单元格个数
=COUNT(B:B) //统计出B列非空单元格个数(不管是数字还是文字都是非空)
10、excel日期大小的比较
=IF(A1>B1,"true","false"),当A1日期大于B1时显示true,否则显示false。
11、某列首次出现非0值的时间
有这么一组数据,第一列是时间,第二列是销售量,总共13行,想知道第二列中首次销售量不为0 的时间。
=INDEX($A$1:$A$13,MATCH(1=1,IF(ROW(1:13)>MATCH(0,B$1:B$13,0),B$1:B$13,)>0,))
输入公式后,同时按ctrl+shift+enter运行公式,因为这是一个数组公式。
12、在excel中把中文转为拼音
有事会有如题12所描述的需求,我在网上找到了一种解决方法https://www.jianshu.com/p/1e1502133340
13、按周统计数据
①可以用数据透视表;②借助weeknum函数
数据
2012-3-9
公式 说明 结果
=WEEKNUM(A2) 2012-3-9 所在一年中的周数,一周开始于星期日(默认值)。 10
=WEEKNUM(A2,2) 2012-3-9 所在一年中的周数,一周开始于星期一(第二个参数 2)。11
14、自动填充序列
双击填充序列的十字,不用自己下拉,就可以把整列填充上。
15、计算百分比的简便方法
通常计算百分比时,我会用公式XXX*100%的方式,今天发现没必要,只需要把XXX那一列的数字格式由常规或数字改为百分比即可。