EXCEL学习（上）

目录

一.基本操作

1.用户界面

2.快捷键

3.工作表操作

二.数据获取

1.数据类型

 2.内外部数据的获取

三.公式与函数 

 4.名称的定义和引用

5.函数

5.1统计函数

 5.2数学与三角函数

---

一.基本操作

（单元格区域自动填充：选中一个单元格右下角+拖拽；单元格选择：选中后按住移动鼠标指针）

1.用户界面

2.快捷键

Ctrl+N新建空白工作簿；Ctrl+S保存工作簿；Ctrl+O打开；Ctrl+W或Alt+F4关闭

3.工作表操作

[操作位置-下部标签栏]（1）插入：后面点+；前面右键-插入；（2）重命名：右键；（3）颜色：右键；（4）移动：按住左键拖动标签到目的位置释放；（5）复制：右键-移动或复制-选择表-勾选“建立副本”-确定；（6）隐藏：右键；（7）显示：任意标签上单击鼠标右键-取消隐藏-选择-确定；（8）删除：右键。

 

二.数据获取

1.数据类型

1.1 数值型数据：可运算，默认靠右对齐；字符型数据：不可计算，默认靠左对齐

1.2 转换：（1）数值型转为字符型：选择单元域-数据选项卡-数据工具-分列-分割符号-下一步-Tab键勾选-下一步-文本-完成

（2）字符型转换为数值型：单击警示图标

 2.内外部数据的获取

2.1内部数据

EXCEL文件扩展名 .xlsx，.xls,.csv。在数据分析时常用csv格式的数据，可能会遇到以下问题：（1）使用excel打开csv文件时出现乱码。由于excel文件默认ANSI编码，若从数据库中导出的csv文件编码方式为UTF-8或Unicode就可能会乱码。利用记事本重新编码：记事本查看编码-另存-更改编码为ANSI-excel打开无乱码

（2）将excel文件存储为csv格式后丢失工作表。csv只能保存当前工作表的内容。

2.2外部数据获取

（1）从文本（2）从网站（支持表格数据，不支持图片）（3）数据库【可以通过数据选项卡获取数据启动Power Query编辑器】

三.公式与函数 

注：公式中输入符号时需要英文状态如,"

1.运算符【+】【-】【*】【/】【^】通配符【？】【*】
通配符怎么用？你需要知道的使用技巧 - 知乎 (zhihu.com)

2.单元格引用：[工作簿名]工作表名！单元格引用

3.地址引用：区别相对地址和绝对地址。相对地址即为拖拽时单元格会对应着改变，而绝对地址固定，在要保持引用不变的列或行值前加上$。

相对地址拖拽：

 

绝对地址拖拽：

 

 4.名称的定义和引用

使用名称可以快速选择已命名的单元格或单元格区域，在公式中引用名称可以实现精确引用。

（1）语法规则 （2）适用范围：同一工作簿下；若在不同工作表，需“工作表名！名称”

（3）为单元格区域定义名称：a.快速定义名称：选中单元格-右键-定义名称；b.选中区域，公式-定义名称；c.将现有行和列标题转换为名称：选择要命名的区域，包括行或列标题，公式-定义的名称-根据所选内容创建-勾选指定的包含标题的位置，结果时是不含现有行或列标题的区域被命名为该标题，如下图

（4）引用名称：在单元格输入公式时可以直接输入名称让其参与运算

 

 （5）管理名称：名称管理器

5.函数

以下按照EXCEL函数库分类，均可在公式-插入函数-选择类别处找到然后进行参数设置，列出以下在单元格的操作方便理解，若熟悉可以直接在单元格操作而不借助函数库

5.1统计函数

（1）求平均

算术平均数：=AVERAGE( number, number2,……)

单条件求平均：=AVERAGEIF(range, criteria, [average_range])

多条件求平均：=averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

几何平均数：=GEOMEAN(n1,n2,,...)

调和平均数：=HARMEAN(n1,n2,...)

（2）计数

包含数字的单元格个数：COUNT()

非空单元格个数：COUNTA()

空单元格个数：COUNTBLANK()

单条件：COUNTIF(range,criteria)

多条件：COUNTIFS(range1,criteria1,range2,criteria2,...)

(3)其他

求最值：MAC()最大值MIN()最小值;MEDIAN()中值

排序：RANK(number,ref,order)【number指被排序的单元格，ref指该排序单元格域，order为0表示降序，1为升序】

频率分布函数：FREQUENCY(data_array,bins_array)对数据进行分段统计【data_array会自动忽略非数值的单元格数据，bins_array遇到非数值单元格返回错误值#N/A；该函数以一列垂直数组返回某个区域中数据的频率分布】【若再拖拽会显示溢出】

例.分段统计销量（分段点表示上界）

 5.2数学与三角函数

（1）求和

一般求和：=SUM(number1,[number2],...)//=SUM(B1:B7)

单条件求和：=SUMIF(range，criteria，sum_range)【range是条件所在的单元格区域，criteria是求和条件如数字、字符、逻辑表达式，sum_range是实际要求和的值所在的区域】

多条件求和：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

【第一个参数：sum_range 是需要求和的实际单元格；第二个参数：criteria_range1为计算关联条件的第一个区域；第三个参数：criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本；第四个参数：criteria_range2为计算关联条件的第二个区域；第五个参数：criteria2为条件2。（输入条件时不打[]）

 求几组数组对应元素乘积的和：SUMPRODUCT(array1,arrar2,...)

例1.计算商品总价

一般情况下，可以将中间的逗号换成乘号：=SUMPRODUCT(C2:C12*D2:D12)。但当求和区域中有文本的时候，在两个数组之间使用逗号，会把文本当成0来处理。使用逗号前提条件，两个数组的行、列数必须一样。

例2.计算指定条件的总价（条件转化为逻辑值后参与运算）

先使用(A2:A12=”大食堂”)，判断A列的部门是不是等于指定的部门，得到一组由TRUE[1]和FALSE[0]构成的逻辑值。即可将指定条件的单价*数量和相加。

 例3.计算指定月份指定部门的总价

例4.条件分别位于行、列

在公式中直接写日期的时候，先加上一对半角引号，然后再乘以1变成日期序列值。（表中1/8会自动变成1月8日，在公式中仍采取这种写法才可）

例5.同一区域设置多个条件：计算2月白菜和土豆的总采购量【条件之间使用+】

例6.含模糊查找：统计A列型号中有“摩托罗拉”字样的手机销量

 

ISNUMBER(FIND(“摩托罗拉”,A2:A12))这部分，先使用FIND函数在A2:A12数据区域中查找关键字，如果包含关键字，就返回表示关键字位置的数值，否则就返回错误值。然后使用ISNUMBER函数逻辑判断FIND函数的数组结果是不是数值，如果是数值，说明是包含关键字的，如果不是数值，那就是不包含关键字了。 

（2）其他

INT(number):将数值向下取整为最接近的整数。

MOD（number,divisor):返回两数相除的余数，正负性与除数divisor一致

例.发挥你的钞能力计算发放钞票张数

ps:下一期学习剩余的四大类函数；之后学习EXCEL数据处理-图表-数据分析。诚请uu们留下意见和建议QAQ