常用操作
Excel函数
函数基础知识
通配符
通配符主要用于查找、统计等运算中。
| * | 表示任何字符 |
|---|---|
| ? | 表示任何单个字符 |
| ~ | 表示用于解除字符的通配 |
数字运算符
| 算术运算符 | 含义 |
|---|---|
| +（加号） | 加法 |
| –（减号） | 减法/负数 |
| *（星号） | 乘法 |
| /（正斜杠） | 除法 |
| %（百分号） | 百分比 |
| ^（脱字号） | 乘方 |
比较运算符
| 名称 | 符号 |
|---|---|
| 等于 | = |
| 大于 | > |
| 大于等于 | >= |
| 小于 | < |
| 小于等于 | <= |
| 不等于 | <> |
- 结果：TRUE和FALSE
作用
自动查看
- (1)点击公式所属单元格
- (2)点击公式选项卡
- (3)在公式审核中找到公式求值功能
- 手动查看 选中公式按F9
运算符优先级
| 运算符 | 说明 |
|---|---|
| :（冒号） | 区域运算符，生成对两个引用之间的所有单元格的引用，包括这两个引用 |
| （单个空格） | 联合运算符，将多个引用合并为一个引用 |
| ,（逗号） | 交叉运算符，求多个区域交叉引用的单元格的区域（只能有一个单元格交叉） |
| - | 负数（如 –1） |
| % | 百分比 |
| ^ | 乘方 |
| * 和 / | 乘和除 |
| + 和 – | 加和减 |
| & | 连接两个文本字符串 |
| = | 比较运算符 |
| < > | |
| <= | |
| >= | |
| <> |
几种常用函数的进阶用法
- SUM累计余额计算
- 采用相对引用，逐步扩大求和的单元格区域
- 计算迟到时间
- 比如上班时间是9:00
- 迟到时间=MAX(打卡时间,”9:00”)-“9:00”
修正采购订单
数组
- 是有序的元素序列。
- 组成数组的各个变量称为数组的元素
- 数组格式
- 用花括号{}表示数组。
- 如{10,20,30;40,50,60}。其中，30后面，跟的是分号；，表示40要换行了。
- 数组的各个元素间用逗号与分号分隔，两者意义一定要理解，分号分隔表示的是数组的行分隔。
- 数组创建
- 1、引用单元格数据创建数组（F9）
- 2、手动输入创建数组
- 3、计算创建数组 （F9）
- 数组中可以使用任何类数据
- 数字、文本、逻辑值、错误值
- 数组公式使用
- 按住Ctrl+shift然后按enter
- 数组运算即单独对每一个单元格进行计算，然后汇总
数组间的计算
ROW：返回行号
- ROW(reference)
- Reference为需要得到其行号的单元格或单元格区域
- 案例
- ROW（）返回当前单元格行号 ROW（B2）返回2
- ROW（B2:B4）Ctrl+shift+ennter组合键，使用数组公式。
- 生成序号：”P-“&ROW（）-2
- 生成重复序号：”P-“&INT((ROW(D1)-1)/3)+1
- INT函数，将数字向下舍入到最接近的整数。
- 语法INT(number）其中number是需要进行向下舍入取整的实数。
COLUMN: 返回列标
TRANSPOSE转置函数
- TRANSPOSE（单元格区域）
- 例如公式 =TRANSPOSE(A1:B4) 会选取单元格 A1 到 B4，并将它们水平排列。
案例
转置与转置函数TRANSPOSE
- 利用ROW函数构建数列进行表格转换
逻辑函数与信息函数
IF函数
- 基本使用
- IF函数，根据条件判断，然后返回指定内容
- IF(logical_test,value_if_true,value_if_false)
- IF(条件，如果为真时返回的值，如果为假时返回的值)
- 例：
- IF（E2>=60,”及格”,”不及格”）
- 注意：返回值为文本时加双引号，双引号必须为半角输入
- IF函数，根据条件判断，然后返回指定内容
- 数组让IF函数拥有查询功能
- 案例
- 计算产品为甲,等级为2级的总数量
- {=SUM(IF(B3:B16&C3:C16=”甲2级”,D3:D16,””))}
- 案例
- 最值条件让IF函数简化
- OR函数
- OR，判断多个条件至少有一个成立
- 例：OR(E2>=60,F2>=60)
- 最值条件让IF函数简化
- 使用常规方法
- =IF(OR(C2<60,D2<60,E2<60,F2<60,G2<60),”不合格”,”合格”)
- 使用最值条件
- {=IF(MAX(—(C2:G2<60)),”不合格”,”合格”)}
- 使用常规方法
- OR函数
省略IF函数参数写法
IFS 函数检查是否满足一个或多个条件，且返回符合第一个 TRUE 条件的值。 IFS 可以取代多个嵌套 IF 语句，并且有多个条件时更方便阅读。
- 通常情况下，IFS 函数的语法如下：
- =IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)
- =IFS(条件1，条件1成立时输出结果，条件2，条件2成立时输出结果，…….)
请注意，IFS 函数允许测试最多 127 个不同的条件。 但不建议在 IF 或 IFS 语句中嵌套过多条件。 这是因为多个条件需要按正确顺序输入，并且可能非常难构建、测试和更新。
OR/AND/NOT函数设置条件
AND，判断多个条件同时成立
- 例：AND(G2>=60,H2>=60,I2>=60)
- OR，判断多个条件至少有一个成立
- 例：OR(G2>=60,H2>=60,I2>=60)
- NOT，对逻辑值求反
- 例：NOT(-1) 输出 =>false
- 只有0代表的是False，其他数字都代表True
- 乘号代替逻辑函数AND，多条件应用
- AND(logical1,logical2, …)
- Logical1, logical2, … 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。
- AND最后返回的是单值TRUE 或 FALSE，不是数组，要想返回数组可以用乘号代替。
- 案例
- 求仓库1当中零件刀片的数量
- =SUM((B2:B161=”刀片”)(F2:F161=”仓库1”)G2:G161)
- 求仓库1当中零件刀片的数量
- AND(logical1,logical2, …)
- 加号代替逻辑函数OR，多条件应用
- OR(logical1,logical2, …)
- Logical1, logical2, … 表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。
- OR最后返回的是单值TRUE 或 FALSE，不是数组，要想返回数组可以用加号代替。
- 案例
- 求钨钢刀、刀片、钨钢棒的库存数量
- =SUM(((B2:B161=H2)+(B2:B161=I2)+(B2:B161=J2))*G2:G161)
- 求钨钢刀、刀片、钨钢棒的库存数量
- OR(logical1,logical2, …)
加号与乘号代替逻辑函数AND与和OR或，混合使用
XOR(logical1, [logical2],…)
- 参数：可以检验的 1 至 254 个条件，可为 TRUE 或 FALSE，且可为逻辑值、数组或引用。
- 参数都相同，返回true；参数不同，返回false
识别公式中的错误值
| 错误值 | 注释 | | —- | —- | | ##### | 列宽太窄；格式不对，如使用了负日期或时间 | | #VALUE! | 此错误表示使用的参数或操作数的类型不正确 | | #DIV/O! | 当公式被零除时(除数是0或者空） | | #NAME? | 无法识别的名称 | | #N/A | 当在函数或公式中没有可用数值时 | | #REF! | 删除了引用的单元格(单元格引用无效时) | | #NUM! | 公式或函数中某个数字有问题时将产生错误值 | | #NULL! | 当试图为两个并不相交的区域指定交叉点时 |
错误值####
- 原因：列宽太窄；格式不对，如使用了负日期或时间
- 解决方法:
- 如果使用了负日期或时间
- (1)如果使用的是1900日期系统，那么日期和时间必须为正值。
- (2)如果对日期和时间进行减法运算，应确保建立的公式是正确的。
- (3)如果公式是正确的，但结果仍然是负值，可以通过将相应单元格的格式设置为非日期或时间格式来显示该值。
- 列宽太窄
- (1)调整列宽。
- (2)更改单元格的数字格式，使数字适合现有单元格宽度。例如,可以减少小数点后的小数位数。
- 如果使用了负日期或时间
- #VALUE!
- 原因：此错误表示使用的参数或操作数的类型不正确
- 可能包含以下一种或几种错误，按照下面的方法解决就行。
- (1)当公式需要数字或逻辑值(例如TURE 或 FALSE)时，却输入了文本。
- (2)输入或编辑数组公式，没有按组合键Ctrl+Shift+Enter，而是按了 Enter键。
- (3)将单元格引用、公式或函数作为数组常量输入。
- (4)为需要单个值(而不是区域)的运算符或函数提供区域。
- #DIV/0!
- 当公式被零除时(除数是 0 或者空）
- (1)输入的公式中包含明显的除以零的计算，如”=5/0”
- (2)使用了对空白单元格或包含零作为除数的单元格的单元格引用
- (1)输入的公式中包含明显的除以零的计算，如”=5/0”
- 当公式被零除时(除数是 0 或者空）
- #NAME?
- 无法识别的名称
- (1)使用了EUROCONVERT函数，而没有加载”欧元转换工具”宏。
- (2)使用了不存在的名称。
- (3)名称拼写错误。
- (4)函数名称拼写错误。
- (5)在公式中输入文本时没有使用双引号。
- (6)区域引用中漏掉了冒号。
- (7)引用的另一张工作表未使用的单引号引起。
- #N/A
- 当在函数或公式中没有可用数值时,将出现此错误
- (1)缺少数据，在其位置输入了#N/A或 NA()；修改为数组元素一样多
- (2)为HLOOKUP、LOOKUP、MATCH或 VLOOKUP 工作表函数的lookup_value 参数赋予了不正确的值，修改为正确的值就行
- (1)缺少数据，在其位置输入了#N/A或 NA()；修改为数组元素一样多
- 当在函数或公式中没有可用数值时,将出现此错误
- #REF!
- 当单元格引用无效时,会出现此错误。
- (1)删除了其他公式所引起的单元格，或将已移动的单元格粘贴到了其他公式所引起的单元格上。
NUM!
- 如果公式或函数中使用了无效的数值，则会出现此错误。
- 图片中开始时间比结束时间大，出现错误，需要修改。
- #NULL!
- 如果指定了两个并不相相交的区域的交点,则会出现错误。具体表现在:
- (1)使用了不正确的区域运算符。
- (2)区域不相交。
- 提示：引用之间的交叉运算符为空格。错误修改为相交的区域就行。
识别公式错误的函数
| 函数 | 注释 | | —- | —- | | ISERROR(value) | 识别所有错值
参数value，成立时返回真（true），不成立返回假（false） | | ISERR(value) | 除#N/A外的错误值
参数value，成立时返回真（true），不成立返回假（false） | | ISNA(value) | #N/A错误值
参数value，成立时返回真（true），不成立返回假（false） | | IFERROR(value, value_if_error) | 如果 value 或 value_if_error 是空单元格，则 IFERROR 将其视为空字符串值 (“”)。
如果 value 是数组公式，则 IFERROR 为 value 中指定区域的每个单元格返回一个结果数组
如果value是错误值，则返回value_if_error，否则返回value | | IFNA(value, value_if_na) | value 必需。 用于检查错误值 #N/A 的参数。
value_if_na 必需。 公式计算结果为错误值 #N/A 时要返回的值。
如果value是错误值 #N/A ，则返回value_if_na，否则返回value |
- 如果指定了两个并不相相交的区域的交点,则会出现错误。具体表现在:
识别数字与文本
信息函数：ISNUMBER与ISTEXT
信息函数ISODD与ISEVEN
- 函数语法：
- ISODD(number)
- 参数说明：number：表示需要判断是否为奇数的数字。
- ISEVEN(number)
- 参数说明：number：表示需要判断是否为偶数的数字。
- ISODD(number)
- 函数语法：
MID函数
信息函数ISREF函数与ISFORMULA
ISBLANK函数
- 用于判断指定的单元格是否为空，其语法结构为：ISBLANK（value）
ISLOGICAL函数
N(value)
语法：NA( )，没有任何参数
PHONETIC(reference)
- 参数：Reference 必需。文本字符串或对单个单元格或包含 furigana 文本字符串的单元格区域的引用。
- 如果 reference 为单元格区域，则返回区域左上角单元格中的 furigana 文本字符串。
- 如果 reference 为不相邻单元格的区域，将返回错误值 #N/A。
- PHONETIC函数会忽略空白单元格。
- PHONETIC函数不支持数字、日期、时间、逻辑值、错误值等。
- PHONETIC函数不支持任何公式生成的值。
数据汇总处理函数
SUM函数
- SUM(number1,number2,…)
- SUM(单个或多个单元格)
- 可以用,逗号，对多个区域求和
- SUM(列名)
- 例如，SUM(A:A)
- SUM(行名)
- 例如，SUM(1:1)
- SUM函数和”与”条件统计计数汇总数据
- 案例：
- =SUM(N(E2:E96=”生产”))
- =SUM((E2:E96=”生产”)*(F2:F96=”工人”))
- =SUM((E2:E96=”生产”)*(J2:J96))
- =SUM((E2:E96=”生产”)(F2:F96=”工人”)(J2:J96))
- 案例：
SUM函数和”或”条件统计计数汇总数据
SUMPRODUCT函数
语法是
- =SUMIF(range，criteria，sum_range)
sumif函数的参数如下：
SUMIFS函数
- SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)
- SUMIFS(求和区域，条件1区域，条件值1，条件2区域，条件2，……）
- 条件同时满足时求和
注意事项
COUNT函数和CONNTA函数
- 语法
- COUNT(value1, [value2], …)统计数字
- COUNTA(value1, [value2], …)统计非空值的单元格个数
- 参数
- value1必需参数，表示要计数的值的第一个参数。
- value2, …可选参数，表示要计数的值的其他参数，最多可包含 255 个参数。
- 语法
COUNTBLANK(range)
COUNTIF函数是对指定区域中符合指定条件的单元格进行计数。
- 语法：countif（range，criteria）
- 参数：range 要计算其中非空单元格数目的区域
- 参数：criteria 以数字、表达式或文本形式定义的条件
- 条件设置
- (1) 求真空单元格个数：=COUNTIF(数据区,””)
- (2) 非真空单元格个数:：=COUNTIF(数据区,”<>”) 相当于counta()函数
- (3) 文本型单元格个数:：=COUNTIF(数据区,”*”) 假空单元格也是文本型单元格
- (4) 区域内所有单元格个数： =COUNTIF(数据区,”<>”””)，如果数据区内有”，该公式不成立.
- (5) 逻辑值为TRUE的单元格数量 =COUNTIF(数据区,TRUE)
-
COUNTIFS函数多条件计数
COUNTIFS函数
- countifs 用来计算多个区域中满足给定条件的单元格的个数，可以同时设定多个条件。
- 语法：countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
- 参数：criteria_range1：为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域），
AVERAGE函数
- 语法：AVERAGE( number, number2,……)
- 参数说明：
- Number，number2，为要计算平均值的1~30个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。
- 如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。
AVERAGEA函数
- 语法：AVERAGEA(value1,value2,…)
- 参数说明：Value1, value2,… 为需要计算平均值的 1 到30个参数、单元格区域或数值。
语法：
- AVERAGEIF(range, criteria, [average_range])
参数：
AVERAGEIFS函数
- 语法：averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,…)
- 参数average_range表示：求平均值区域——参与计算平均值的单元格。
- 参数criteria_range,criteria_range2，…表示：条件区——criteria条件所在的范围。
- 参数criteria1,criteria2，…表示：条件——是用来定义计算平均值的单元格。 （形式可以是数字、表达式、单元格引用或文本的条件。用来定义将计算平均值的单元格。 ）
日期与时间函数
Excel数据透视表
常用技巧
若有收获，就点个赞吧
0 人点赞
