当前位置: 首页 > news >正文

【数据分析】Excel中的常用函数公式总结

目录

  • 0 引用方式
    • 0.1 相对引用
    • 0.2 绝对引用
    • 0.3 混合引用
    • 0.4 3D引用
    • 0.5 命名引用
  • 1 基础函数
    • 1.1 加法、减法、乘法和除法
    • 1.2 平均数
    • 1.3 求和
    • 1.4 最大值和最小值
  • 2 文本函数
    • 2.1 合并单元格内容
    • 2.2 查找
    • 2.3 替换
  • 3 逻辑函数
    • 3.1 IF函数
    • 3.2 AND和OR函数
    • 3.3 IFERROR函数
  • 4 统计函数
    • 4.1 COUNT函数
    • 4.2 COUNTIF函数
    • 4.3 SUMIF函数
    • 4.4 SUBTOTAL函数
  • 5 时间和日期函数
    • 5.1 TODAY和NOW函数
    • 5.2 DATEDIF函数
  • 6 高级数学和三角函数
    • 6.1 POWER函数
    • 6.2 SQRT函数
    • 6.3 SIN、COS和TAN函数
    • 6.4 ROUND函数
  • 7 数据筛选和查找
    • 7.1 VLOOKUP函数
    • 7.2 HLOOKUP函数
    • 7.3 INDEX和MATCH函数
  • 8 函数计算错误
    • 8.1 #DIV/0! - 除零错误
    • 8.2 #VALUE! - 类型不匹配或无效的参数
    • 8.3 #NAME? - 未识别的函数或公式名
    • 8.4 #REF! - 无效的引用
    • 8.5 #NUM! - 数值错误
    • 8.6 #N/A! - 无法使用的值

0 引用方式

在这里插入图片描述

0.1 相对引用

相对引用是Excel中最基本的引用方式之一。当公式中引用了单元格时,公式会根据相对位置自动调整引用。例如,如果你在单元格B2中输入公式=A1,那么当你复制这个公式到B3时,公式会自动调整为=A2,保持相对位置的关系。

A1: 10
B2: =A1    // 结果为10
B3: =A2    // 结果为10(相对引用自动调整)

0.2 绝对引用

绝对引用是一种在复制公式时保持引用不变的方法。在Excel中,使用$符号来创建绝对引用。例如,=$A$1会始终引用第一行的A列,无论公式被复制到哪里。

A1: 10
B2: =$A$1   // 结果为10
C2: =$A$1   // 结果为10(绝对引用不变)

0.3 混合引用

混合引用是相对引用和绝对引用的结合。可以将$符号放在行或列的其中一个,使其保持不变。例如,= $A1将保持列A不变,但是行数会随着复制而改变。

A1: 10
B2: =$A1   // 结果为10
B3: =$A2   // 结果为10(列A不变,行数相对引用)

0.4 3D引用

3D引用能够引用多个工作表中的单元格。语法为Sheet1:Sheet3!A1,表示引用从Sheet1到Sheet3的A1单元格。这在处理多个相关工作表时非常有用。

Sheet1: A1: 10
Sheet2: A1: 20
Sheet3: A1: 30Summary Sheet: B1: =SUM(Sheet1:Sheet3!A1)   // 结果为60 (10 + 20 + 30)

0.5 命名引用

命名引用是一种给单元格或单元格范围分配易记名称的方式。通过“公式”选项卡中的“定义名称”功能,可以为单元格或范围分配一个名称,然后在公式中使用该名称引用。
在这里插入图片描述

A1: 10定义名称 MyValue 为 =Sheet1!$A$1B2: =MyValue   // 结果为10

1 基础函数

1.1 加法、减法、乘法和除法

加法:C1=A1+B1

减法:C1=A1-B1

乘法:C1=A1*B1

除法:C1=A1/B1

1.2 平均数

计算一组数字的平均值:

B1=AVERAGE(A1:A10)

1.3 求和

计算一组数字的总和:

B1=SUM(A1:A10)

1.4 最大值和最小值

最大值:

B1=MAX(A1:A10)

最小值:

B1=MIN(A1:A10)

2 文本函数

2.1 合并单元格内容

合并A1和B1单元格的内容:C1=A1&B1

2.2 查找

在文本中查找特定字符串,并返回所查找关键词(第一个文本字符串)在第二个文本字符串的下标位置(从1开始):

C1=SEARCH("关键词", A1)

2.3 替换

替换文本中的特定字符串:

C1=SUBSTITUTE(A1, "旧值", "新值")

3 逻辑函数

3.1 IF函数

基本的IF语句,根据条件表达式的值(IF括号中第一个值)返回结果,若条件为真,返回IF括号中的第二个值,否则返回第三个值:

A2=IF(A1>10, "大于10", "小于等于10")

3.2 AND和OR函数

AND函数:如果其所有参数均为 TRUE,则返回 TRUE:

C1=AND(A1>10, B1<20)

OR函数:如果任一参数为 TRUE,则返回 TRUE:

C1=OR(A1>10, B1<20)

3.3 IFERROR函数

如果公式的计算结果错误,则返回指定的值;否则返回公式的结果。

C1=IFERROR(A1/B1, "除数不能为零")

4 统计函数

4.1 COUNT函数

计算参数列表中数字的数量:

B1=COUNT(A1:A10)

4.2 COUNTIF函数

根据条件计算符合条件的数字的数量:

B1=COUNTIF(A1:A10, ">10")

4.3 SUMIF函数

根据条件计算符合条件的数字的总和:

B1=SUMIF(A1:A10, ">10")

4.4 SUBTOTAL函数

对数据进行分类汇总,使用方法:

SUBTOTAL(function_num,ref1,[ref2],...)

其中,function_num是函数指定的参数,不同数值对应不同的具体函数:

function_num(包含隐藏值)function_num(忽略隐藏值)函数含义
1101AVERAGE求平均
2102COUNT求数字的数量
3103COUNTA求不为空的单元格的个数
4104MAX求最大值
5105MIN求最小值
6106PRODUCT求参数中数字的乘积
7107STDEV求样本标准差
8108STDEVP求总体标准差
9109SUM求和
10110VAR求样本方差
11111VARP求总体方差

注:

  • STDEV的计算方法是将样本的平均数的方差进行开平方得出的。
    STDEV = ∑ ( X i − X ˉ ) 2 n − 1 \text{STDEV} = \sqrt{\frac{\sum{(X_i - \bar{X})^2}}{n-1}} STDEV=n1(XiXˉ)2
    • X i X_i Xi代表数据集中的每个数据点。
    • X ˉ \bar{X} Xˉ 代表数据集的平均值。
    • n n n代表数据点的数量。
  • STDEVP的计算方法是先求一组资料中各数值与其算术平均数离差平方和的平均数,然后取其平方根。
    STDEVP = ∑ ( X i − X ˉ ) 2 n \text{STDEVP} = \sqrt{\frac{\sum{(X_i - \bar{X})^2}}{n}} STDEVP=n(XiXˉ)2
  • VAR的计算方法是:
    VAR = ∑ ( X i − X ˉ ) 2 n − 1 \text{VAR} = \frac{\sum{(X_i - \bar{X})^2}}{n-1} VAR=n1(XiXˉ)2
  • VARP的计算方法是:
    VARP = ∑ ( X i − X ˉ ) 2 n \text{VARP} = \frac{\sum{(X_i - \bar{X})^2}}{n} VARP=n(XiXˉ)2

5 时间和日期函数

5.1 TODAY和NOW函数

返回当前日期:

A1=TODAY()

返回当前日期和时间:

A1=NOW()

5.2 DATEDIF函数

计算两个日期之间相隔的天数、月数或年数,用于计算年龄很有用:

C1=DATEDIF(A1, B1, "D") //返回A1日期和B1日期之间相隔的天数

其中,第三个参数Unit即指定计算相隔的是天数、月数或年数等,具体如下:

Unit返回结果
“Y”一段时期内的整年数
“M”一段时期内的整月数
“D”一段时期内的整天数
“MD”开始日期和结束日期的天数之差,忽略日期中的月份和年份
“YM”开始日期和结束日期的月份之差, 忽略日期中的天和年份
“YD”开始日期和结束日期的日期部分之差, 忽略日期中的年份

注:第二个日期参数需在第一个日期参数之前,否则结果将为#NUM!。

6 高级数学和三角函数

6.1 POWER函数

计算一个数的指数:

B1=POWER(A1, 2) //返回A1中数的平方

6.2 SQRT函数

计算平方根:

B1=SQRT(A1) //返回根号A1中的数

6.3 SIN、COS和TAN函数

计算角度的正弦值:

B1=SIN(A1)

计算角度的余弦值:

B1=COS(A1)

计算角度的正切值:

B1=TAN(A1)

6.4 ROUND函数

将数字四舍五入到指定的位数:

A1=ROUND(3.75, 0)   // 舍入到整数,结果为 4
B1=ROUND(3.75, 1)   // 舍入到一位小数,结果为 3.8
C1=ROUND(123, -1)   // 舍入到整十,结果为 120
D1=ROUND(4567, -2)  // 舍入到整百,结果为 4600
E1=ROUND(1234, -3)  // 负数表示舍入到整数位数,结果为 1000

ROUND函数的基本语法如下:

ROUND(number, num_digits)

其中,

  • number:要舍入的数字。
  • num_digits:舍入到的小数位数。可以为正数、负数或零。
    • 正数:舍入到指定的小数位数。
    • 负数:舍入到整数倍数,例如 -1 表示舍入到十位,-2 表示舍入到百位,以此类推。
    • 零:舍入到整数。

7 数据筛选和查找

7.1 VLOOKUP函数

基于某个键值在表格中按行查找数据:

VLOOKUP函数参数如下:

VLOOKUP(要查找的内容,要查找的内容所在范围,包含要返回的值的范围内的列号,近似(TRUE)或精确匹配(FALSE))
C1=VLOOKUP(A1, B1:D10, 2, FALSE)

注:当存在多条满足条件的记录时,只能返回第1个满足条件的记录。

7.2 HLOOKUP函数

基于某个键值在表格中按列查找数据,类似于VLOOKUP,HLOOKUP函数参数如下:

HLOOKUP(要查找的内容,要查找的内容所在范围,包含要返回的值的范围内的行号,近似(TRUE)或精确匹配(FALSE))
C1=HLOOKUP(A1, B1:D10, 2, FALSE)

注:当存在多条满足条件的记录时,只能返回第1个满足条件的记录。

7.3 INDEX和MATCH函数

结合使用INDEX和MATCH进行更灵活的查找:

C1=INDEX(B1:D10, MATCH(A1, A1:A10, 0), 2)

其中,INDEX 函数返回表格或区域中的值或值的引用,MATCH 函数在引用或数组中查找值并返回该值在所查找范围中的相对位置。

8 函数计算错误

Excel中如果遇到函数计算错误,可能有以下几种情况。

8.1 #DIV/0! - 除零错误

这个错误表示一个公式尝试除以零。

A1: 10
B1: 0
C1: =A1/B1   // #DIV/0!

解决方法: 在除数前加入条件判断,确保除数不为零。

C1: =IF(B1<>0, A1/B1, "N/A")

8.2 #VALUE! - 类型不匹配或无效的参数

这个错误表示公式中使用了无效或不匹配的数据类型。

A1: "ABC"
B1: =A1+1   // #VALUE!

解决方法: 确保公式中使用的参数类型匹配,或使用适当的函数进行类型转换。

B1: =VALUE(A1)+1   // 结果为 101

8.3 #NAME? - 未识别的函数或公式名

这个错误表示Excel无法识别使用的函数或公式名称。

A1: 10
B1: =SUMM(A1)   // #NAME?

解决方法:检查拼写错误,确保使用的是正确的函数或公式名称。

B1: =SUM(A1)   // 结果为 10

8.4 #REF! - 无效的引用

这个错误表示公式引用了不存在的单元格或范围。

A1: 10
B1: =A1+C1   // #REF!

解决方法: 确保公式引用的单元格或范围存在,并且没有被删除。

B1: =A1   // 结果为 10

8.5 #NUM! - 数值错误

这个错误表示公式包含了一个数学错误,例如负数的平方根。

A1: -1
B1: =SQRT(A1)   // #NUM!

解决方法: 检查公式中的数学运算,确保不包含无法计算的数学操作。

B1: =SQRT(ABS(A1))   // 结果为 1

8.6 #N/A! - 无法使用的值

这个错误表示公式中引用的值无法使用,通常是因为查找函数未找到匹配项。

A1: 100
B1: =VLOOKUP(A1, Sheet2!A:B, 2, FALSE)   // #N/A!

解决方法:使用适当的错误处理函数,例如IFNA,来处理这种情况。

B1: =IFNA(VLOOKUP(A1, Sheet2!A:B, 2, FALSE), "Not Found")

相关文章:

【数据分析】Excel中的常用函数公式总结

目录 0 引用方式0.1 相对引用0.2 绝对引用0.3 混合引用0.4 3D引用0.5 命名引用 1 基础函数1.1 加法、减法、乘法和除法1.2 平均数1.3 求和1.4 最大值和最小值 2 文本函数2.1 合并单元格内容2.2 查找2.3 替换 3 逻辑函数3.1 IF函数3.2 AND和OR函数3.3 IFERROR函数 4 统计函数4.1…...

ESLint prettier 配置代码风格

环境同步&#xff1a; 1、ESlint&#xff0c;开启保存自动修复 配置文件 .eslintrc.cjs prettier 风格配置 https://prettier.io 使用单引号 不使用分号 每行宽度至多80字符 不加对象|数组最后逗号 换行符号不限制&#xff08;win mac 不一致&#xff09; vue组件名称…...

涤生大数据实战:基于Flink+ODPS历史累计计算项目分析与优化(上)

涤生大数据实战&#xff1a;基于FlinkODPS历史累计计算项目分析与优化&#xff08;一&#xff09; 1.前置知识 ODPS&#xff08;Open Data Platform and Service&#xff09;是阿里云自研的一体化大数据计算平台和数据仓库产品&#xff0c;在集团内部离线作为离线数据处理和存…...

jvm一级缓存

1、利用JVM缓存。脱离redis。 2、导包&#xff0c;springboot自带此包。如没有可以导&#xff1a;com.google.guava:guava:20.0的包。 3、直接上代码&#xff1a; package com.leo.cache;import com.alibaba.fastjson.JSONObject; import com.google.common.cache.Cache; im…...

鸿蒙(HarmonyOS)项目方舟框架(ArkUI)之Web组件

鸿蒙&#xff08;HarmonyOS&#xff09;项目方舟框架&#xff08;ArkUI&#xff09;之Web组件 一、操作环境 操作系统: Windows 10 专业版、IDE:DevEco Studio 3.1、SDK:HarmonyOS 3.1 二、Web组件 提供具有网页显示能力的Web组件&#xff0c;ohos.web.webview提供web控制能…...

【Linux】学习-深入了解文件的读与写

深入了解语言级别(C语言)文件操作的"读"与"写" 在学习前&#xff0c;我们先要知道在Linux下的一个原则&#xff1a;一切皆是文件 如何理解呢&#xff1f;举个外设的例子&#xff0c;比如键盘和显示器&#xff0c;这两个外设也可以其实本质上也是文件&…...

java实战:销售订单30分钟未支付自动取消

本文将介绍如何使用Java实现一个销售订单在30分钟内未支付则自动取消的系统。我们将探讨如何使用Spring的定时任务功能来检查订单状态&#xff0c;并在订单未支付的情况下执行取消操作。 一、需求分析 在电商系统中&#xff0c;为了管理库存和避免长时间占用资源&#xff0c;…...

一、西瓜书——绪论

第一章 绪论 1.独立同分布 通常 假设 样本空间 中 全 体样 本 服 从 一 个 未 知 “ 分 布 ” ( d i s t r i b u t i o n ) D , 我们获得的每个样本都是独立地从这个分布上采样获得的&#xff0c; 即 “ 独 立同 分布 ” ( i n d e p e n d e n t a n d i d e n t ic a …...

如何连接ChatGPT?无需科学上网,使用官方GPT教程

随着AI的发展&#xff0c;ChatGPT也越来越强大了。 它可以帮你做你能想到的几乎任何事情&#xff0c;妥妥的生产力工具。 然而&#xff0c;对于许多国内的用户来说&#xff0c;并不能直接使用ChatGPT&#xff0c;不过没关系&#xff0c;我最近发现了一个可以直接免科学上网连…...

qt学习:串口

头文件 #include <QSerialPort> #include <QSerialPortInfo> 模块 QT core gui serialport 编程步骤 配置一个ui界面&#xff0c;五个QComboBox和一个按钮和一个QTextEdit 添加一个成员 private:QSerialPort *serial; 在构造函数中初始化ui端口列表和…...

145. 二叉树的后序遍历

给你一棵二叉树的根节点 root &#xff0c;返回其节点值的 后序遍历 。 示例 1&#xff1a; 输入&#xff1a;root [1,null,2,3] 输出&#xff1a;[3,2,1]示例 2&#xff1a; 输入&#xff1a;root [] 输出&#xff1a;[]示例 3&#xff1a; 输入&#xff1a;root [1] 输…...

Postgresql 的编译安装与包管理安装, 全发行版 Linux 通用

博客原文 文章目录 实验环境信息编译安装获取安装包环境依赖编译安装安装 contrib 下工具代码 创建用户创建数据目录设置开机自启动启动数据库常用运维操作 apt 安装更新源安装 postgresql开机自启修改配置修改密码 实验环境信息 Ubuntu 20.04Postgre 16.1 编译安装 获取安装…...

【Java EE初阶十】多线程进阶二(CAS等)

1. 关于CAS CAS: 全称Compare and swap&#xff0c;字面意思:”比较并交换“&#xff0c;且比较交换的是寄存器和内存&#xff1b; 一个 CAS 涉及到以下操作&#xff1a; 下面通过语法来进一步进项说明&#xff1a; 下面有一个内存M&#xff0c;和两个寄存器A,B; CAS(M,A,B)&am…...

与AI对话:编写高效Prompt的指南

与AI对话&#xff1a;编写高效Prompt的指南 一、明确目标 引导AI提供特定格式或内容答案的策略一、明确需求二、使用示例三、设置参数四、分步询问五、使用关键词 利用关键词引导AI重点关注核心内容的技巧一、确定关键概念二、使用专业术语三、强调重要性四、避免相关术语的混淆…...

QML用ListView实现带section的GridView

QML自带的GridView只能定义delegate&#xff0c;没有section&#xff0c;类似手机相册带时间分组标签的样式就没法做。最简单的方式就是组合ListViewGridView&#xff0c;或者ListViewFlow&#xff0c;但是嵌套View时&#xff0c;子级View一般是完全展开的&#xff0c;只显示该…...

docker之程序镜像的制作

目录 一、每种资源的预安装&#xff08;基础&#xff09; 安装 nginx安装 redis 二、dockerfile文件制作&#xff08;基础&#xff09; 打包 redis 镜像 创建镜像制作空间制作dockerfile 打包 nginx 镜像 三、创建组合镜像&#xff08;方式一&#xff09; 生成centos容器并…...

Git - 每次 git pull/push 时需要账号和密码解决方案

问题描述 在提交项目代码或者拉取代码的时候&#xff0c;每次 git 都要输入用户名密码&#xff0c;很烦~ 解决方案 让服务器记下来用户名和密码&#xff0c;此时输入一次&#xff0c;以后再 git push /pull 的时候就不用再输账号和密码了 # 配置 git 记录用户名和密码 git c…...

C语言中在main函数之后运行的函数

在 Linux 平台上&#xff0c;atexit 函数同样是一个用于注册终止处理函数的库函数&#xff0c;它是 C 标准库 <stdlib.h> 的一部分。atexit 函数允许你注册一个或多个函数&#xff0c;这些函数会在 main 函数执行结束后&#xff0c;或者在调用 exit 函数时&#xff0c;由…...

pytorch训练指标记录之tensoboard,wandb

详解Tensorboard及使用教程_tensorboard怎么用-CSDN博客文章浏览阅读5.1w次&#xff0c;点赞109次&#xff0c;收藏456次。目录一、什么是Tensorboard二、配置Tensorboard环境要求安装三、Tensorboard的使用使用各种add方法记录数据单条曲线(scalar)多条曲线(scalars)直方图(hi…...

C语言——oj刷题——实现字符串逆序

当我们需要逆序一个字符串的内容时&#xff0c;可以通过C语言编写一个函数来实现。下面将详细介绍如何通过C语言实现这个功能&#xff0c;并附上代码示例。 1、实现原理 要逆序一个字符串的内容&#xff0c;可以使用两个指针来交换字符串中对应位置的字符。具体实现原理如下&am…...

SkyWalking 10.2.0 SWCK 配置过程

SkyWalking 10.2.0 & SWCK 配置过程 skywalking oap-server & ui 使用Docker安装在K8S集群以外&#xff0c;K8S集群中的微服务使用initContainer按命名空间将skywalking-java-agent注入到业务容器中。 SWCK有整套的解决方案&#xff0c;全安装在K8S群集中。 具体可参…...

基于距离变化能量开销动态调整的WSN低功耗拓扑控制开销算法matlab仿真

目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.算法仿真参数 5.算法理论概述 6.参考文献 7.完整程序 1.程序功能描述 通过动态调整节点通信的能量开销&#xff0c;平衡网络负载&#xff0c;延长WSN生命周期。具体通过建立基于距离的能量消耗模型&am…...

【Oracle APEX开发小技巧12】

有如下需求&#xff1a; 有一个问题反馈页面&#xff0c;要实现在apex页面展示能直观看到反馈时间超过7天未处理的数据&#xff0c;方便管理员及时处理反馈。 我的方法&#xff1a;直接将逻辑写在SQL中&#xff0c;这样可以直接在页面展示 完整代码&#xff1a; SELECTSF.FE…...

day52 ResNet18 CBAM

在深度学习的旅程中&#xff0c;我们不断探索如何提升模型的性能。今天&#xff0c;我将分享我在 ResNet18 模型中插入 CBAM&#xff08;Convolutional Block Attention Module&#xff09;模块&#xff0c;并采用分阶段微调策略的实践过程。通过这个过程&#xff0c;我不仅提升…...

postgresql|数据库|只读用户的创建和删除(备忘)

CREATE USER read_only WITH PASSWORD 密码 -- 连接到xxx数据库 \c xxx -- 授予对xxx数据库的只读权限 GRANT CONNECT ON DATABASE xxx TO read_only; GRANT USAGE ON SCHEMA public TO read_only; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; GRANT EXECUTE O…...

如何理解 IP 数据报中的 TTL?

目录 前言理解 前言 面试灵魂一问&#xff1a;说说对 IP 数据报中 TTL 的理解&#xff1f;我们都知道&#xff0c;IP 数据报由首部和数据两部分组成&#xff0c;首部又分为两部分&#xff1a;固定部分和可变部分&#xff0c;共占 20 字节&#xff0c;而即将讨论的 TTL 就位于首…...

云原生安全实战:API网关Kong的鉴权与限流详解

&#x1f525;「炎码工坊」技术弹药已装填&#xff01; 点击关注 → 解锁工业级干货【工具实测|项目避坑|源码燃烧指南】 一、基础概念 1. API网关&#xff08;API Gateway&#xff09; API网关是微服务架构中的核心组件&#xff0c;负责统一管理所有API的流量入口。它像一座…...

ubuntu22.04有线网络无法连接,图标也没了

今天突然无法有线网络无法连接任何设备&#xff0c;并且图标都没了 错误案例 往上一顿搜索&#xff0c;试了很多博客都不行&#xff0c;比如 Ubuntu22.04右上角网络图标消失 最后解决的办法 下载网卡驱动&#xff0c;重新安装 操作步骤 查看自己网卡的型号 lspci | gre…...

第八部分:阶段项目 6:构建 React 前端应用

现在&#xff0c;是时候将你学到的 React 基础知识付诸实践&#xff0c;构建一个简单的前端应用来模拟与后端 API 的交互了。在这个阶段&#xff0c;你可以先使用模拟数据&#xff0c;或者如果你的后端 API&#xff08;阶段项目 5&#xff09;已经搭建好&#xff0c;可以直接连…...

Windows电脑能装鸿蒙吗_Windows电脑体验鸿蒙电脑操作系统教程

鸿蒙电脑版操作系统来了&#xff0c;很多小伙伴想体验鸿蒙电脑版操作系统&#xff0c;可惜&#xff0c;鸿蒙系统并不支持你正在使用的传统的电脑来安装。不过可以通过可以使用华为官方提供的虚拟机&#xff0c;来体验大家心心念念的鸿蒙系统啦&#xff01;注意&#xff1a;虚拟…...