

首页  思维导图  详情



精通EXCEL

2020-03-13 17:52:02   2  举报





AI智能生成

excel基础知识整理

Excel

excel

EXCEL

思维导图

模版推荐

作者其他创作

大纲/内容

函数

文本清洗函数

查找

find

截取值

left

right

mid

拼接

concatenate

替换

replace

substitute

去两端空格

trim

计算长度

len

关联匹配函数

vlookup

返回指定范围内查找指定值，的指定列的值

一般用于合并表

vlookup（匹配值，查找范围，返回列的索引，近似/模糊匹配）

相当于根据某一值，连接表

index

返回指定范围，指定行、列的值

match

返回某范围内匹配某值的位置

row

返回所在行

col

offset

偏移

hyperlink

创建一个快捷方式（跳转），用来打开存储在网络服务器、Intranet或internet中的文件。

逻辑运算函数

true

false

判断条件为真，返回 ...，否则返回...

and

一个参数为真，即为真

not

统计计算函数

subtotal

函数中的瑞士军刀（统计函数序号，参数）

randbtween

生成随机数

rank

排名（参与排名的数值，排名的数值区域，升序/降序）

=RANK(F28,$F$22:$F$32,1)

count

计算非空单元格个数

countif

计算符合给定条件的区域中的非空单元格个数

=COUNTIF(C2:C32,"<2000")

countifs

=COUNTIFS(C2:C32,">1000",C2:C32,"<2000")

countifs（求和区域，条件区域，条件1，条件区域2，条件2）

sum

给定区域求和

sumif

sumifs

=SUMIFS(C2:C32,C2:C32,">1000",C2:C32,"<2000")

sumproduct

返回对应区域累加求和值

AVERAGE

averageif

AVERAGEIFS

=AVERAGEIFS(E2:E32,E2:E32,">2000",E2:E32,"<3000")

max

最大

min

最小

ROUND

按指定位数对数字进行四舍五入

ROUND(对谁操作, 哪位操作（>0 小数点右侧，反之）) ，返回是操作之后的值

int

向下取整

MOD 

返回两数相除的余数

时间序列函数

year

month

weekday

一周中的第几天

weeknum

本周是一年中的第几周

day

date

参数分别是年月日

now

today

days()

时间之差

Datedif(start_date,end_date,unit （Y M 或D）)

计算2个日期之间的天/月/年

=DATEDIF(A2,A32,"D")

基础

快捷方式

ctrl+N

打开空白单元格

录入数据

下一个单元格

tab

enter

填充柄

使用：鼠标放在右下角，成细十字状，开始拖动

右下角出现选项

复制填充

序列填充

格式刷

单击

刷一次

双击

连续刷

字符串使用双引号

设置单元格格式

常规在开始中，不常规点三角，或右键

get

拖拽设置行高

保护部分单元格和筛选

保护单元格

先设置单元格-保护解锁，再选中部分锁定，再保护工作表

筛选

开始中简单筛选

高级

基础函数

sum

avg

count

max

sumif

sumifs

countif

countifs

rank

排序

逻辑函数

and

not

示例

判断闰年

=IF(MOD(B2,4)>0,"平年",IF(MOD(B2,100)>0,"闰年",IF(MOD(B2,400)>0,"平年","闰年")))

数据透视表

定义/用途

可以快速汇总、分析大量数据表格的   交互式分析工具

提升工作效率

常见场景

找出同类数据在不同时期的某种特定关系

以友好的方式查看大量数据表格

对数值数据快速分类汇总、按分类查看数据信息

建立交叉表格

快速计算数值数据的汇总信息，差异，个体占总体的百分比等

数据源经常变化

对数据源的要求

每列数据第一行包含列标题

不包含空行、空列、空单元格、合并单元格

去除空值方法

f5定位-定位条件-空值，输入0 ，ctrl+enter 全部替换为0

查找替换

不包含同类字段

判断依据：看上去既可作为标题，也可作为数据的内容

例如：姓名部门  一月    二月                                500   1000

改为：姓名部门  月份   金额                             一月   500                             二月  1000

创建数据透视表

插入-数据透视表-选中数据源/一般放到新的表中

数据透视表操作：

1. 开始可以全部选中，数据透视表会自动分配

实际可以根据具体业务拖拽想要查看的数据

认识数据透视表

基本术语

数据源

创建数据透视表的数据来源，可以是单元区域、定义的名称、另一个数据透视表、外部数据等

字段

数据源中列标题

项

每个字段中包含的数据

四大区域

行区域

列区域

值区域

筛选区域

可以对整个数据透视表进行筛选

基本操作

数据源变更时需刷新，

增加行等时，需变更数据源

设置数据透视表样式

排序，筛选，切片器

切片器

多个数据透视表中共享切片器

满足

数据透视表来源一个数据源

数据透视表共享一个数据透视表缓存

创建

1.选中一个数据透视表−分析−插入切片器

2.创建链接

单机切片器−切片器工具−报表链接

作用

使数据筛选更简单直接

分组

数值数据

选中数值−分析−分组−组字段

文本数据

手动选择数据−右击创建分组

日期数据

与数值数据相同

数据分析

设置数据计算方式

值区域单元格右击-值显示方式/值字段设置

总计的百分比

所有数据总和显示为百分百，其他为占总和的百分比

行、列汇总百分比

行列处为百分百

百分比

选中一个基准为百分百，其他为相对他的百分比

父类汇总百分比（只能在行和列有2个以上时可以使用）

父行汇总百分比

含义

每个值占整个的百分比

父列汇总百分比

父级汇总百分比

区别，父行父列百分百为整体的整体，（1+2=50% ，+2+3=100%）此处父级为百分百，即1百分比+2百分比=百分百

差异和差异百分比

有一个基准，相对基准的差异

按某一字段汇总（百分比）

加上前边的值

计算字段

定义

虚拟的字段，数据透视表的列，不在数据源中

只能出现在值区域

创建

选择值字段数据-分析-字段、项目和集-计算字段

修改

删除

计算项

临时添加的虚拟数据

计算项不会出现在字段列表窗格，而计算字段会

相当添加了一个数据行，添加方式与计算字段不同

创建

选择行或列字段中的单元格-字段、项目和集-计算项

多出插入项，应用：可计算具体数据的差异等

注：有分组和自定义汇总时不能使用计算项

数据透视图

认识

与普通图表的区别

数据源类型

数据透视图可使用外部数据

可创建图表的类型

具有交互性

数据透视图是动态图

格式设置丢失问题

刷新后可能丢失标签等丢失问题

与数据透视表关系

限制

无法创建气泡图、散点图、骨架图等

无法切换数据源的位置

无法调整数据标签大小

若添加了趋势线，数据透视表中变更数据，趋势线会消失 

创建

分析-数据透视图

 Collect

Get Started

Excel 2003从入门到精通

 Collect

Get Started

新编Word Excel PPT 2013从入门到精通

 Collect

Get Started

Excel从入门到精通

 Collect

Get Started

Word／Excel／PPT 2016商务办公从入门到精通





0 条评论

下一页