02excel基础及函数

02excel基础及函数

1.Excel操作

1.1添加辅助列

在第一列前加入一个空列 --》 在空列中有数据的行写入等差数列 --》在没有数据的行写入另外一个等差数列–》点击开始–》点击排序和筛选–》点击自定义排序–》选择第一列列名–》点击确定

1.2拆分

操作：数据 --> 分列

2.Excel基本公式

2.1基础知识

1. 使用 Excel 进行数学计算

   你可以在 Excel 中进行加、减、乘、除运算，无需使用任何内置函数。只需使用部分基本运算符：+、-、*、/。所有公式均以等号 (=) 开头。

   若要相加，选择单元格 F3，键入 =C3+C4，然后按 Enter。

   若要相减，选择单元格 F4，键入 =C3-C4，然后按 Enter

   若要相乘，选择单元格 F5，键入 =C3*C4，然后按 Enter。

   若要相除，选择单元格 F6，键入 =C3/C4，然后按 Enter。

   可以使用脱字符 (^) 对值进行幂运算，例如 =C3^C4。使用 Shift+6 输入该符号

2. 有关公式、单元格和区域的详细信息

   Excel 由组成行和列的单个单元格构成。行采用数字编号，列采用字母标明。共有一百多万行和 16,000 多列，可将公式放入其中任何位置。

   公式可包含单元格引用、单元格引用范围、运算符和常数。以下均为公式的示例：

   =A1+B1
   =10+20
   =SUM(A1:A10)

   你会注意到，上面的第三个示例中使用了 SUM 函数。函数是预先构建的命令，可采用一个值或多个值，以特定方式进行计算并返回结果。例如，SUM 函数采用单元格引用或指定区域，并对它们求和。在此示例中，它使用单元格 A1 到 A10，并对它们求和。Excel 拥有 400 多个函数，你可以在“公式”选项卡上进行浏览。

   带有函数的公式以等号开头，随后是函数名称，后接用括号括起来的参数（函数用于计算的值）。

   按 Enter 即可确认公式。执行该操作后，公式将进行计算，结果将显示在单元格中。若要查看公式本身，可查看功能区下方的编辑栏，或按 F2 进入“编辑”模式，你将在单元格中看到公式。再次按 Enter 完成公式并计算结果。

   常数是在单元格或公式中输入的值。虽然 =10+20 计算结果可能与 =A1+B1 相同，但常数不是一个好方法。为什么呢？因为在不选择单元格并进行查找的情况下，无法轻松查看常数。稍后更改可能有难度。将常数放入单元格简单得多，你可轻松在其中进行调整，并在公式中进行引用。

   例如：选择下方值为 12 的黄色单元格。你将看到我们对一个区域的单元格使用 SUM 函数。我们未直接在公式中键入“4”或“8”。

   给单元格提供内容的方式：

   1）直接输入数据 - 输入的是什么，单元格内容就是什么

   2）“=单元格地址” - 直接引用单元格内容

   3）“=Excel公式” - 计算公式结果来给单元格提供内容（公式中涉及到的数据可以是具体的数据，也可以是引用单元格内容）

   4）“=函数调用表达式” - 获取函数返回值，来提供单元格内容

2.2单元格引用

注意：应用单元格的时候，如果引用地址没有锁定，往下拉单元格行号不断加1，往右拉单元格，列号不断加1

D1 - 行号和列号都没有锁定

$D$1 - 行号和列号都锁定

$D1 - 列号锁定，行号不锁定

D$1 - 行号锁定，列号不锁定

F4可以切换锁定方式

练习 本利和

2.3单列变多列

方法1:找到第一行和原始数据的引用关系，写出引用，然后直接往下拖

方法2:直接通过写出引用地址关系，然后将A变成"=A"

2.4 多列变单列

操作步骤：

1.通过引用复制原来的数据
2.在第一列的最后引用第二列第一个数据
3.按住第一列最后添加的单元格往后拖，再往下拖
4.选择性粘贴第一列数据的数值，删除多余的数据

2.5跨表引用

与单元格引用同理

3.Excel重要函数

3.1注意事项

学习Excel前需要明确的事

在学习Excel函数之前，大家要明确一件事，Excel现在涉及到了一些新函数，这些新函数可能只存在于Office365、Office2019及更新版本之中，所以建议大家在学习时安装较新的版本，但是因为电脑系统、电脑环境的原因，大家量力而行。后续函数均有详细版本标识，请仔细辨别。

另外，在 Windows RT 计算机上创建公式和使用 Excel 工作表函数时，您可能会发现其计算结果与基于传统的 x86 体系结构的 PC 上的结果有细微的不同。 这是由于 Windows RT 中使用的处理器的设计和基于 x 86 的计算机中使用的处理器的设计之间存在差异。 这些差异发生在非常高的精度级别中，如第 14个或第 15 个有效位。

配置要求

1. windows7系统最高只能安装office2016，安装最低要求：office2016。
2. 如果电脑安装有office365、office2019、office2021等版本，无须重新安装。
3. 因各种原因安装有比office2016更低版本，请卸载后安装新版本。
4. 因电脑系统限制（如windows7系统）请大家自行解决调整。

3.2文本函数

注意：LEFTB()函数并不适用于所有语言，只有在将 DBCS 语言设置为默认语言时，函数 LEFTB 才会将每个字符按 2 个字节计数。 否则，函数 LEFTB 的行为与 LEFTB 相同，即将每个字符按 1 个字节计数。
支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

下方的RIGHTB()、MIDB()函数同理

1. LEFT()和LEFTB()

   LEFT()

   从文本字符串的第一个字符开始返回指定个数的字符

   用法：LEFT(文本内容，个数=1)注意：文本内容可以是双引号引起来的，也可以引用单元格

   LEFTB()

   基于所指定的字节数返回文本字符串中的第一个或前几个字符

2. RIGHT()和RIGHTB()

   RIGHT()

   根据所指定的字符数返回文本字符串中最后一个或多个字符

   用法：RIGHT(文本内容，个数)

   RIGHTB()

   根据所指定的字节数返回文本字符串中最后一个或多个字符

3. MID()和MIDB()

   MID()

   用法：MID(文本内容,开始位置,取出个数)

   返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

   MIDB()

   根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。

4. LEN()和LENB()

   LEN()

   返回文本字符串中的字符个数

   LENB()

   返回文本字符串中用于代表字符的字节数。

   lenb()-len()=DBSC语言的个数

   用法：LEN(文本内容)

5. FIND()和FINDB()

   FIND()

   用于从一个文本串中定位另一个文本串的起点并返回字符数

   FINDB()

   用于从一个文本串中定位另一个文本串的起点并返回字节数

   用法：FIND(要找的字符，对象字符，起始位置)

6. SEARCH()和SEARCHB()

   SEARCH()

   用于从一个文本串中定位另一个文本串的起点并返回字符数

   SEARCHB()

   用于从一个文本串中定位另一个文本串的起点并返回字节数

   注意：FIND()和SEARCH()区别：FIND()区分大小写，SEARCH()不区分大小写。FIND()的find_text参数不能写通配符，SEARCH()的find_text参数可以写通配符

7. REPLACE()和REPLACEB()

   REPLACE()

   将旧字符串的指定位置（字符下标）指定字符数的字符串替换为新字符串

   REPLACEB()

   将旧字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串

   用法：REPLACEB(文本内容,开始位置,替换次数,替换内容)

8. 其他函数

   LOWER()

   将一个文本字符串中的所有大写字母转换为小写字母。

   UPPER()

   将文本转换为大写字母。

   REPT()

   将文本重复一定次数

   SUBSTITUTE()

   在文本字符串中用新文本替换旧文本

   TRIM()

   除了单词之间的单个空格之外，移除文本中的所有空格

   TEXT()

   自定义单元格格式

   VALUE()

   将表示数字的文本字符串转换为数字

3.3数学函数

1. ABS() 返回数字的绝对值

2. INT() 向小取整

3. MOD() 返回两数相除的余数（结果的符号和余数相同）

4. RAND() [0,1)之间的随机数

5. RANDBETWEEN() [a,b]之间的随机整数

6. ROUND() 将数字四舍五入到指定位数

7. SQRT() 返回算术平方根

8. SUM() 将单个值、单元格引用或者区域相加，或者将三者的组合相加

9. SUMIF() 对范围中符合指定条件的值求和

   SUMIF(条件范围,条件,求和范围)

   注意：1）条件需要用双引号引起来

   ​ 2）求和范围如果和条范围一致，求和范围可以省略

   ​ 3）如果条件中需要引用单元格内容，将单元格地址放在引号外面，并且在单元格地址前面加“&”

   ​ 4）条件中可以用’’ 作为通配符代表任意符号出现任意次数，如果想要 * 在条件中表示本身而不是通配符，在星号前面加~

10. SUMIFS() 计算满足多条件的全部参数的值

    SUMIFS(求和范围，条件范围已1，条件1，条件范围2，条件2,…)

    Excel中的比较运算符：

    （>、<、=、<=、>=、<>(不等于)）

3.4统计函数

1. 统计函数:
   AVERAGE() 返回其参数的平均值(求指定范围内所有数字和除以数字的个数)

2. AVERAGEA() 返回其参数的平均值，包括数字、文本和逻辑值(求指定范围内所有数字和除以所有数据的个数)

3. AVERAGEIF() 返回区域中满足给定条件的所有单元格的平均值

4. AVERAGEIFS() 返回满足多个条件的所有单元格的平均值

5. COUNT() 计算参数列表中数字的个数

6. COUNTA() 计算参数列表中值的个数

7. COUNTIF() 计算区域内符合给定条件的单元格的数量

8. COUNTIFS() 计算区域内符合多个条件的单元格的数量

9. FREQUENCY() 以垂直数组的形式返回频率分布(分箱)

   FREQUENCY(统计对象范围，分箱节点对应的范围）

10. MAX()/MIN() 返回参数列表中的最大（最小）值

11. RANK() 返回一列数字的数字排位（数字排位是相对于列表中其他值的大小）

12. RANK.AVG() 返回一列数字的数字排位（数字排位是相对于列表中其他值的大小，如果多个值具有相同的排位，则返回平均排位）

13. RANK.EQ() 返回一列数字的数字排位，其大小与列表中其他值相关，如果多个值具有相同的排位，则返回该组值的最高排位