本文最后更新于 2024-10-16,文章内容距离上一次更新已经过去了很久啦,可能已经过时了,请谨慎参考喵。

title: Excel常用快捷键及小技巧
tags:
  - Excel
  - Office
categories:
  - Office
top_img: false
cover: '/upload/cdn0files/20200721090708.jpg'
copyright: false
abbrlink: 60150ec6
date: 2020-01-22 10:18:48
updated: 2020-01-22 10:18:48

本文作者:萌萌哒ㄛ团团(博主大学舍友)

常用快捷键

Alt + Enter

强制换行

Tab

向右输入

Shift + Enter

向上输入

Shift + Tab

向左输入

Ctrl + Enter

在多个单元格同时输入数据(在编辑栏)

F4

重复上一个操作步骤

Ctrl + 方向键

快速定位到表格端点

Ctrl + Shift + 方向键

快速定位到有数据表格的端点

Shift + 单元格网格线双击

快速选中数据,根据网格线双击位置选择

Ctrl + G

快速定位

Ctrl + H

查找与替换

分数输入格式

x(空格)m/n

系统会默认化简到最简

插入大量小数

选项—>高级—>自动插入小数点

如果要保留两位,那么输入数据要比原数据大100倍,以此类推,使用完毕后建议关闭

快速复制到多个表

选中要复制的内容,在左下角选择多个表,填充—>至同组工作表即可

将数值转换为文本

在数值前加上'符号即可

格式刷

先选中要刷的格式的单元格,点击格式刷选择即可,如果需要刷不连续的单元格,则双击格式刷按钮即可

通配符

?表示一个字符,*表示所有

高级筛选

首先制作条件区域,第一行必须是标题行且与原表标题一致,第二行必须是筛选的值条件,在数据-->排列和筛选-->高级中操作,如果条件在一行的表示与的关系,在不同行的时候表示

彻底隐藏工作表

Alt + F11打开窗口,将visible属性改为2即可

快速填充序列号

Ctrl + Shift + 下方向键全选列,然后填充-->序列-->设置步长

表格美化要点

  • 不显示网格线,设置浅色边框
  • 数字和英文字体设置为Arial字体,汉字使用等线或微软雅黑
  • 正文使用10号字体,标题使用12号字体,大标题使用20号字体
  • 表头和标题设置深色底纹,白色文字
  • 隔行填充浅色底纹
  • 一张表使用同一色系,不同区域用颜色深浅来区别

计算被标记的不同列的和

查找后全选,取别名,使用sum函数计算

公式部分

向右填充前不变,向下填充后不变

Ctrl + Shift + Enter 结束函数输入并计算

数据类型

文本

可以运算,如:

A&"与"&B  A与B

数值

文本型数字和数值型数字,数值型数字可以用于计算,在函数名之前加 --可将返回值变成数值型数字,在输入文本型数字时,在数据前加,分列可以改变字符集

日期

日期也是一种数值,也可以进行计算,日期规定:1代表一整天。从1900年1 月0日(实际不存在)到9999年,小数代表时间。

逻辑值

在四则运算中TRUE为1,FALSE为0,可参与计算

在逻辑判断时,FALSE为0,其他非0数值为TRUE

在比较运算中,TRUE>FALSE>文本>数值

数组的定义和分类

数组是一个或多个元素按照行列方式组合的集合

  • 常量数组: {1,2,3}{1;2;3}
  • 区域数组: 引用单元格内的数组
  • 内存数组: 通过公式计算得来的,单元格放不下,在内存中暂时存放,或者不需要展示,依然可以参与计算

数组维度和计算

  • 一维数组:{1,2,3}{1;2;3}
  • 二维数组:{1,2,3,4;1,2,3,4}

数组运算

同方向一维数组运算,相对应的数据运算,多出来的部分返回错误值

二维数组运算:与线性代数中数组计算完全相同

函数

SUM()函数

只能求和,参数可以是常量,也可以单元格引用,参数最多为254个,参数与参数之间为相加(逗号隔开),不需要使用运算符

AVERAGE()函数

求平均值,参数可以引用单元格也可以手动输入,当内容全部为文本时,会返回错误值,不会忽略错误值

MAX()/MIN()函数

求极值,不会忽略错误值

COUNT()函数

计数,出现错误值时会忽略

注意:

引用单元格内的逻辑值会不参与计算,但是手动输入的逻辑值会参与计算,把TRUE当1计算,手动输入常量时需要半角双号引起来,数值之间使用半角逗号隔开

IF()函数

IF(条件,真值,假值)

条件成立返回真值,否则返回假值,例如:IF(F16 = "真",1,-1)表示F16单元格内内容为“真”时返回1,否则返回-1,当判断条件 件,当有多个条件时需要嵌套,当不需要返回内容的时,将返回参数设置 为""(半角符号)即可,如:IF(F16=10,"对","")也可以判断该单元格如: IF(1,"真","假")

VLOOKUP()函数

查找函数

=VlOOKUP(查找值,查找区域,查找序列,查找方式)
  • 查找值:需要查找内容的条件
  • 查找区域:查找内容所在的区域,可以多选不能少选
  • 查找序列:需要查找的内容在查找条件后的第几列
  • 查找方式:精确查找为0,相似查找为1

注意:

  1. 当查找内容为空时返回错误值

  2. 查找值有重复时,只能返回第一个值

       		 3. 需要查找很多内容需要下拉填充时,将查找区域改为绝对引用,否则查找条件随着填充区域变化而变化,选中查找区域,快捷键`F4`
       		 4. 查找区域必须将查找值作为第一列
       		 5. 函数可以跨、表跨工作簿使用
    

AND()/OR()函数

=AND(逻辑值1,逻辑值2........逻辑30)

有一个为FALSE则返回FALSE,选中逻辑条件,按F9可以查看每一步结果,可以参与乘法计算如:AND(F16=XX,E8)=(F16=XX)*(E8)

=OR(逻辑值1,逻辑值2........)

有一个返回TRUE则返回TRUE,全为FALSE则返回FALSE,可参与加法计算:OR(F16,E8)=(F16=XX)+(E8)

应用:

=IF((F16=XX)+(E8>200),200,0)	或
=IF((F16=XX)*(E8>200),200,0)

在逻辑判断时,FALSE为0,其他非0数值为TRUE

函数的嵌套

例如:

=IF(OR(F16=XX,E8>250),250,0)

MOD()函数

计算余数

MOD(被除数,除数)

结果的正负只取决于除数的正负

INT()/TRUNC()/ROUND()函数

保留小数函数

INT(数字)		//返回不大于目标数值的最大整数
TRUNC(数字,保留小数位数)		//对目标值进行截取,如果是0是可以省略,也可以是负数,表示保留整数位
ROUND(数字,保留小数位数)		//四舍五入保留
ROUNDUP(数字,保留小数位数)		//无条件向上舍入
ROUNDDOWN(数字,保留小数位数)		//无条件向下舍入

CEILING()/FIOOR()函数

CEILING(待取整数,基数)		//返回大于等于待取整数且是基数的整倍数
FLOOR(待取整数,基数)		//返回小于等于待取整数且是基数的整倍数

注意:

当待取整数为正,基数为负时,返回错误值,因为返回值是大于或小于基数的整数倍

RAND()/RANDBETEEEN(A,B)函数

返回随机数

=RAND()		//返回0到1之间的小数
=RANDBETEEEN(A,B)		//返回A到B之间的整数
=RAND()*(N-M)+M

SUMIF()函数

SUMIF(条件判断区域,条件,求和区域)

当条件判读区域与求和区域在同 一区域时可以省略球和区域,也可以有多个参数如:SUMIF(条件判断区域, {“条件1“,”条件2“},求和区域),该函数可以结合通配符使用,如:SUMIF(条件 判断区域,"?条件*",求和区域),也可以嵌套使用,如:=SUM(SUMIF($C$2:$C $15,{"入","出"},D2:D15)*{1,-1})

注意:

当求和区域与条件区域是二维时,条件区域与求和区域尺寸必须一致,数据一一对应,在条件区域第一列判断时会返回求和区域第一列数据