导图社区 Excel 办公技巧
Excel办公使用技巧汇总,基本操作,数据分析,函数运用
编辑于2020-07-21 18:40:48Excel办公使用技巧汇总
基本操作
重要快捷键
按下Enter,向下移动一个活动单元格;按下Tab,向右移动一个活动单元格
选择连续输入数据的单元格范围,同时按下Shift+Ctrl+方向键
确认输入的公式引用了哪几个单元格内容,可选择公式所在单元格并按下F2
绝对引用:利用F4(可多次按)输入$
$A$1——固定列和行
A$1——固定行
$A1——固定列
A1——不固定
函数运用
乘法运算
PRODUCT(A1:A3)
连接单元格(字符串)内容
CONCATENATE(A1:A3)
计算单元格个数
COUNTA():更为常用;指定参数范围内,计算除空白格之外的单元格个数(统计包含数据,无论是数字还是文本,的单元格数量)
COUNT():指定参数范围内,计算含有数值的单元格数量(只计算数字单元格个数)
快速计算总和
选中单元格,按下Alt + =
分类计算总和
SUMIF(参数一,参数二,参数三):在指定的区域(参数一)内,只计算与指定的值(参数二)相同行的、指定列(参数三)里的数值总和
参数一:用于条件判断的区域
参数二:在参数一指定的范围内,需要计算总和的行的判定条件
参数三:实际求和的区域
判断单元格中是否包含特定的字符串
COUNTIF(参数一,参数二):指定范围(参数一)中,计算出参数二所指定的值或条件一致的单元格数
参数一:计算其中非空单元格数目的区域
参数二:在参数一范围内计算数目的条件
统计销量大于500的产品
消除重复数据
如何判断数据是否有重复
COUNTIF(参数一,参数二),计算在 A 列中与单元格 A2有相同数值的单元格的数目
另一种办法
选中并删除重复的单元格
COUNTIF($A$2 : A2,A2)
数据索引(垂直)
VLOOKUP(参数一,参数二,参数三,参数四):
参数一:检索值(为获取需要的数值,含有能够作为参考值的单元格)
参数二:检索范围(在选定的最左列查找检索值的范围)
参数三:指定对应参数二范围左数第几列的数值
参数四:0,或FALSE
操作技巧:SUMIF、COUNTIF、VLOOKUP函数,都以整列作为选取范围
制作有起始值的连续数据(例如对数据进行编号1~1000)
起始单元格输入1,并选中此格
【开始】-【填充】-【系列】
【序列产生在】选择【列】,【终止值】输入1000
输入连续数字
ROW():显示该单元格位于工作表中的第几行的数值
COLUMN():显示该单元格位于工作表中的第几列的数值
输入日期
输入可计算的数据(如2014年1月1日),应在半角模式下,将年、月、日用 / 隔开
2014/1/1,此数据格式可用于日期加减运算
Ctrl+;——快速输入今天日期
Ctrl+:——快速输入现在时间
TODAY():此单元格将自动更新数据为当前日期
根据出生年月计算年龄
DATEDIF(起始日期,结束日期,单位)
单位包括:Y-年,M-月,D-日,计算年龄则选择Y
已知出生日期单元格为B2,则在年龄单元格中输入=DATEDIF(B2,TODAY(),“Y”)
若要计算出“×个月”,将参数三设置为“YM”
若要计算出“×日”,将参数三设置为“MD”
整理数据
如何判断字符串是否相同
在B2单元格中输入=EXACT(A1,A2):判断A1与A2单元格的字符串是否相同,输出结果到B2中(TRUE/FALSE)
将全角字符转换为半角字符
ASC()
删除指定文字
SUBSTITUTE(参数一,参数二,参数三):在参数一制定的字符串单元格/范围内,将参数二指定的文字替换为参数三指定的文字;如果参数三输入空白,相当于删除指定文字
定位最后一个单元格
快捷键Ctrl+End
定位功能:Ctrl+G打开【定位】,点击【定位条件】,选择【最后一个单元格】
利用【条件格式】定制单元格格式
【新建规则】-【使用公式确定要设置格式的单元格】
去掉输入公式中的绝对引用$
每隔一行标注不同颜色
【新建规则】-【使用公式确定要设置格式的单元格】
=MOD(ROW(),2)=1/0,参数二指定的数值除以参数一指定的数值后得到的余数为1(或0),即可以选定奇数/偶数行
冻结窗口
设置冻结窗口时,要保持A1单元格始终出现在窗口上
如果想固定显示前三行的内容,就要选到整个第四行操作【冻结】
如果想固定显示前三列的内容,就要选到整个第四列操作【冻结】
按上述要求指定单元格【冻结】行+列
数据分析
商业数据分析的三大指标
同比率(与去年/上季度等相比是增加还是减少)
预算与实际相比(是否达到目标)
构成的比率(每项各占多少比例)
实际数据,以百分比(比例,比率)呈现才是有说服力的数据