【excel入门学习】

自用！

excel学习

快速填充/智能填充

方法：

1.使用快捷键：ctrl+e

2.开始->填充->快速填充/智能填充

3.拖拽或双击填充柄，然后选择快速填充

注意点：

1.要填充的列的左侧要有数据

快速分析

方法

1.选中表格，右下角的快速分析图标

2.选中表格，ctrl+q

3.wps：开始-》条件格式 可以做格式分析

4.选中要插入的单元格，在插入-》图表 中可以做迷你图

注：

1.在开始->条件格式中可以查看到格式规则

特殊格式的录入

时间

1.正规的是以 - 或者 / 来分割日期的

2.ctrl+;可以显示当天的日期

ctrl+shift+；显示当时时间

输入 =now 函数，回车 可以直接显示当前的日期和时间

百分比

方法

在开始下有文本格式设置

1.直接输入带有%

2.先设置单元格格式，再输入数字

3.输入小数之后，再点击格式转换

注

1.在开始，格式，百分比右侧有增加小数位数和减少小数位数

分数

直接输入分数，会被当做日期

方法
1.先输入 0 空格 再输入分数，即可

2.先输入小数，再格式变为分数

身份证（过长数字的录入）

excel中输入数字，最长可以输入15位；需要调整为文本格式

方法
1.开始下修改格式为文本

2.在数字的最前方输入英文的引号’

原位填充

作用

1.ctrl+回车 光标停在当前单元格，不会到下一个单元格

2.批量输入：选中需要填充的单元格-》开始输入-》ctrl+回车 会让选中的单元格都填上输入的内容

从网上获取数据

方法
1.在数据选项卡下，获取数据（wps为导入数据），自网站

2.数据选项卡下，自网站

导入数据之后，可以设置每隔多长时间刷新一次数据

在连接属性中进行设置

注
1.在office2016版本中，叫做新建查询

2.从网站上获取的表格没有标题时，或需要修改时候，不可以直接修改，直接修改的话，刷新后又还原了

点击表格任意位置，会有查询的选项卡，点击查询选项卡下的编辑功能，弹出的新窗口进行编辑，编辑之后直接点击关闭并上载即可

清洗数据

一.要求不更改数据源的情况，则需要新建查询，步骤如下：

步骤

1.先将原表转换为超级表：选中整个表格，使用快捷键：ctrl+t

或者选中区域，在插入中点击表格

（注：在表格选项卡中，选择转换为区域，即可转为普通表格）

2.数据选项卡-》来自表格/区域，可以打开power query编辑器

3.拆分列可以按照字符进行分割拆分

4.新增列，编辑代码为：

if [字段名]条件 then … else …

自定义格式：数字格式代码

数值占位符：0 # ？

1.0用来改变数值显示位数，如：0.0 表示显示一位小数；000.000 表示显示百位及三位小数，不够的用0填充

2.#作为数字占位符，只显示有意义的数值，不够的部分，不显示

3.？无意义的部分用空格来填充

文本占位符：@

@符号可以让输入的文本重复显示

@ “英文状态的双引号” 可以给输入的文本添加后缀、单位等

组合设置 0;-0;-;@

分别是 正数;负数;零值;文本 的格式设置

日期格式设置： y m d a

如 2022年2月6日 18:50 星期日

格式	显示
yyyy	2022
yy	22
mmm	Feb
mm	02
m	2
dd	06
d	6
aaaa	星期日
aa	日
dddd	Sunday

颜色设置

用中括号[红色]

如 [绿色]￥#,#??“元”;[红色]↓-0.##;0;@

条件设置格式

使用中括号来输入条件，如

注意：只能是 if ;else if; else 三个部分

数据验证

1.在数据选项卡的有效性，或数据下的数据工具，数据验证中

可以设置条件、输入信息及出错警告信息

2.设置下拉列表

3.已经有数据了之后，可以圈释无效数据

注：

1.复制粘贴方式填写表格时候，数据验证不起作用

2.复制粘贴可以把验证部分也粘贴过去：粘贴-》鼠标右键-》选择性粘贴-》验证

条件格式

1.可以通过条件显示不同样式来找到想要的数据，或标记数据

2.隐藏重复值：先标记唯一值-》筛选，根据标记的颜色筛选

查找

1.在开始选项卡右侧的查找和选择-》转到/定位；快捷键为ctrl+g；使用定位条件进行定位

2.可以设置选择对象；双击后可以取消选择对象

函数

函数使用方法

在开始-》求和下可以找到使用函数的入口 ；在公式下可以找到函数入口

=函数名(参数)

小tips：

1.alt+=可以求和

2.输入函数名称之后，快捷键ctrl+a可以显示参数测试面板

绝对引用与相对引用

拖拽填充柄填充，默认使用的是相对引用；添加美元符号$则变为绝对引用，例如$K4,拖动之后固定列数一直是K列；

快捷键F4可以两处都加上$

常用函数

统计函数

count(范围) 返回数字个数；文本类型不计算入内

counta(范围) 非空单元格个数

countblank(范围) 空值单元格个数

countif(范围,条件) 计算满足条件的单元格个数，单条件计算 如 =COUNTIF(F25:F42,“及格”) =COUNTIF(G47:G64,"<60")

​ =COUNTIF(G47:G64,"<"&G47) 使用&连接符来链接单元格

countifs(范围1,条件1,范围2,条件2…) 计算满足条件的单元格个数，多条件计算 ， 各条件是且的关系或关系使用多个单条件相加

通配符

excel可以用不同的符号代表特定的内容，如

*代表 任意多个字符

?代表 一个字符

~代表 转义字符

求和

文本信息不参与计算

sum 注：可以使用快捷键：alt+=

average 空值不占个数，0值占个数

sumif(条件判断的区域，条件，需要求和的区域)

sumifs(条件判断的区域1，条件1，需要求和的区域1，条件判断的区域2，条件2，需要求和的区域2…)

averageif

averageifs

sumproduct 可以进行数组的求和；实际是组内对应元素相乘再求和，如求销售总额=单价*数量，，各个商品的加起来

函数可以嵌套使用，如=SUMPRODUCT(G19:G26,SUMIF(B18:B44,F19:F26,C18:C44))

排名

rank(数值，范围，排序方式)

vlookup函数

查找引用，找到到某行数据，并引用其中某个数值

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

(1)lookup_value 需要在数据表首列进行搜索的值，可以是数值，引用或字符串

(2) table_array 要在其中搜索数据的文字、数字或逻辑值表，可以是对区域或区域名称的引用

(3) col_index_num 返回匹配的序列号，表中首个值列的序号为1

(4) range_lookup 逻辑值：大致匹配用True或省略，精确匹配用False

注意：

1.要查找的值必须在首列

2.使用模糊匹配时候，对比的数据要升序排列呀

3.在函数计算中给列换位置：IF({1,0},D55:D64,C55:C64)

查找替换

查找：ctrl+f

替换：ctrl+h

定位是找对象用的

查找的通配符

*代表任意多个字符

?代表一个字符

~转义符

分列

一个单元格里用符号分隔的许多数据，怎么拆分出来

一些小技巧

1.累加：快捷键：alt+=

2.excel2016，三维地球

3.另存为快捷键：F12 可能会需要加Fn

可以修改作者；设置密码

4.选中当前有数据的列：ctrl+shift+方向键向下

ctrl+方向键可以跳到最上或者最下

5.tab+enter 跳到下一行的首个位置；需要搭配一开始就用tab来向右换位置，才能行

tab：下一个位置，向右；shift+tab向左

shift+enter：向上

6.推荐使用跨列剧中，而不是合并居中，先选中几个单元格，在对齐方式详情中，对齐窗口下的

快捷键ctrl+1可以显示该设置窗口

7.在单元格双击上下边框可以定位到首行或数据的最后行

8.只选择一个单元格之后，点击冻结窗格，会冻结该单元格上一行及左侧列

9.使每一页都有标题行

先在布局中，打印设置的页面，选择标题行，再去打印预览