EXCEL 单元格公式（更新中）

原创 已于 2023-03-17 16:06:45 修改 · 1.2k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#excel

于 2022-01-04 21:21:04 首次发布

EXCEL 单元格公式（更新中）

字符串按指定位置分为两列 (2022年1月4日)

示例

A列是原始内容，B列和C列是下述两个公式的计算结果
在这里插入图片描述
提取单元格中最后一个空格前的内容

=IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

提取单元格中最后一个空格后的内容

=IF(ISERROR(FIND(" ",A2)),A2,RIGHT(A2,LEN(A2)-FIND("|", SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

简要说明

1、IF函数和ISERROR函数用于防止Excel报错；
2、SUBSTITUTE函数
LEN(A2)-LEN(SUBSTITUTE(A2," ",""))这句计算了空格数量
SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ","")) 这句通过空格总数定位了最后一个空格的位置并将其替换为“|”符号
*注：如果空格的位置从左往右数是固定的，可以直接写具体数字，如SUBSTITUTE(A2," ","|",1)可直接将字符串左边第一个空格替换为“|”符号）；

3、FIND函数返回了“|”符号的位置；
4、RIGHT函数和LEFT函数分别返回了“|”符号右边和左边的字符串内容

特殊情况

情况一：同一单元格中包含多个序号+内容（2022年1月6日）

如下图所示，当一个单元格中出现了大段需要分行的文字，分行依据是数字序号
在这里插入图片描述
此时需要先将待分行的内容复制到一个Word文档，用 Ctrl + H 调出替换窗口，如下图配置后点击替换或全部替换。复制回Excel后按照示例情况继续完成分列。

① 输入查找内容 [0-9]{1,}
② 替换为 ^13
③ 勾选“使用通配符

*注1 [0-9]表示待查询的内容是所有数字
*注2 {1,}表示待查询的数字至少是1位，不规定最大位数（若要规定最大位数可写在逗号后）
*注3 若序号后跟着符号而非空格，可直接在Excel中用分列功能，复制分列结果后黏贴为转置
*注4 ^13是Word中的换行符

在这里插入图片描述

用条件格式高亮表单未填充/勾选部分（2022年3月4日）

高亮未填充的单元格

选中需要高亮的单元格，点击在“开始”页中的“条件格式”，选择“新建规则”，按下图配置，点击“格式…”更改高亮颜色。
在这里插入图片描述

高亮未勾选的单选按钮

开发工具 - 加载项 - 表单工具 - 单选按钮 - 放置单选按钮
开发工具 - 加载项 - 表单工具 - 分组框 - 用分组框框住单选按钮

  • 右击任意一个单选按钮，选择“设置对象格式”，在单元格链接中选择一个单元格用来存放和显示选择情况
    （选择第一个按钮显示1，第二个按钮显示2，没有选择任何按钮显示0，刚设置好不会显示数值，但随便点一个按钮就有显示了）。
  • 在“设置对象格式”的“保护”标签页中取消勾选“锁定”，这样在单元簿受保护的情况下仍可以勾选单选按钮。

在这里插入图片描述

  • 右击刚才被选中用来存放和显示选择情况的那个单元格，右击选择“设置单元格格式”，取消勾选“保护”标签页下的“锁定”，这样在单元簿受保护时候可以正常显示勾选情况。 在这里插入图片描述
  • 选择需要高亮的单元格 - 点击“开始”标签页的条件格式 - 新建规则 - 如下图配置，公式中引用了刚才定义的显示选项情况的单元格，等于0表示没有任何选项被选中。 在这里插入图片描述

高亮未勾选的复选框

开发工具 - 加载项 - 表单工具 - 复选框- 放置复选框
开发工具 - 加载项 - 表单工具 - 分组框 - 用分组框框住复选框

  • 复选框与单选按钮类似，唯一的区别是需要对每个复选框要分别设置单元格链接显示每个复选框的选择情况，如下图所示（刚设置好也一样不会显示，需要勾选一次后才会显示TRUE/FALSE)。
    在这里插入图片描述
  • 右击刚才被选中用来存放和显示选择情况的单元格，右击选择“设置单元格格式”，取消勾选“保护”标签页下的“锁定”，这样在单元簿受保护时候可以正常显示勾选情况。
  • 选择需要高亮的单元格 - 点击“开始”标签页的条件格式 - 新建规则 - 如下图配置，公式中引用了刚才定义的显示选项情况的单元格，NOT(OR(…))表示“或非”逻辑，即只有所有复选框都没有被选择时会被高亮。
    在这里插入图片描述

统计符合条件的行的数量

=countifs(range1, condition1, range2, condition2, ...)

翻译内容（2023.3.17）

通过WEBSERVICE公式获取有道翻译的结果，再使用FILTERXML公式提取翻译文字

=FILTERXML(WEBSERVICE("https://fanyi.youdao.com/translate?&i="&A1&"&doctype=xml&version"),"//translation")
EXCEL中使用SUBSTITUTE替换公式一例
微信：WebDataBase
10-18 974
有朋友公司系统升级，需要将原旧代码变更一下，如旧代码为“K008-M01-003-002”需变更为只有一个“-”符号的新代码“K008-M01003002”，旧代码“-”符号可能有三个也可能有两个，但只要保留前面一个，后面的“-”符号去除。 这个需求很容易解决，即1.找到第一个“-”符；2.保留“-”符前的字串；3.“-”符后面的字串将“-”替换成空；4.如果没有“-”符则继续使用旧名称。公式如下： =IF(ISERROR(FIND("-",A2)),A2,MID(A2,1,(FIND("-".
Excel单元格常用公式
ronvicki的博客
01-06 527
A1:A10 是积温数据的范围，“”>=5"" 是条件，表示大于等于5度的积温。这个公式会计算满足条件的积温的总和。
EXCL单元格公式——组装SQL用
TTonlyV5的博客
01-30 317
="'"&F3&"'"
excel公式：定位单元格
sayyy的专栏
05-28 3843
相对引用单元格 比如：A1。 当引用单元格的公式被复制时，新公式引用的单元格的位置将会发生改变。 如横向复制公式，则可以得到B1、C1、D1、… 如纵向复制公式，则可以得到A2、A3、A4、… 绝对引用单元格 比如：$A$1 一旦成为绝对引用，无论公式如何被复制，对采用绝对引用的单元格的引用位置是不会改变的。 锁定行 比如：A$1、A$2 锁定列 比如：A1、A1、A1、B1 范围选择 比如：$A...
计算机表格计算公式加法,加法公式excel,excel表格如何用公式计算加减乘除混合运算？...
weixin_42352351的博客
07-26 4920
用EXCEL怎样做加法表EXCEX加法：1、简单的：=选中格 选中单元格 选中单元格 。。。；2、加和公式：=sum(选中单元格)；3、宏编更复杂点的加法公式Function xx()。。。End Function如果你要在B10单元格里求A1、A2、A3、A4、A5、A6、A7的单元格的和，可以这样：1. 在B10单元格输入=A1 A2 A3 A4 A5 A6 A7，回车2. 在B10单...
计算机表格用计算公式百分百,计算机表格计算公式
weixin_33967069的博客
06-23 330
批量输入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换为数值显示另一个单元格的公式把表达式转换为计算结果快速查找公式错误原因1批量输入公式选取要输入的区域，在编辑栏中输入公式，按CTRL+ENTER即可批量填充公式。2批量修改公式如果是修改公式中的相同部分，可以直接用替换功能即可。3快速查找公式选取表格区域 - 按Ctrl+g打开定位窗口 - 定位条件 - ...
精选资源
VBA提取单元格的公式.xlsm
06-10
VBA提取单元格的公式.xlsm 1、office判断单元格是否为公式 2、提取单元格的公式
精选资源
excel poi工具类 导入 导出 合并单元格 计算公式
01-07
这个“excel poi工具类”是利用Apache POI库来实现对Excel文件进行导入、导出、合并单元格以及处理计算公式的功能。下面我们将深入探讨这些知识点。 **1. Apache POI库介绍** Apache POI是一个开源项目，它为Java...
Excel单元格操作易语言源码
06-04
在IT领域，尤其是在编程实践中，Excel单元格操作是一项常见的任务，尤其在数据分析、报表生成以及自动化处理中。易语言作为一款中国本土开发的高级编程语言，以其简洁的语法和丰富的库支持，使得开发者能轻松地进行...
Excel多单元格数组公式.rar
09-26
在Excel中，数组公式是一种强大的工具，它能够处理一整个区域的数据，并返回多个结果，这些结果可以在多个单元格中展示。"Excel多单元格数组公式"这个主题是Excel高级功能的一部分，尤其对于数据分析和处理大量数据...
Excel2021中对单元格公式进行追踪.docx
09-26
Excel 2021 中对单元格公式进行追踪 在 Excel 2021 中，对单元格公式进行追踪是非常重要的，因为公式可能会引用很多单元格进行计算，甚至有跨表的引用。在这种情况下，检查公式、审核结果变得非常复杂。因此，了解...
4.20 使用条件格式标识包含指定文本的单元格 [原创Excel教程]
互动教程网[www.hdjc8.com]
04-24 8055
1. 本节课将为您演示，如何对包含指定文本的单元格，进行条件格式的设置。首先点击左上角的三角形图标，全选整张工作表。 2. 然后点击条件格式按钮，打开条件格式选项菜单。 3. 然后依次点击[突出显示单元格规则 > 文本包含]选项，打开[文本中包含]设置窗口。 4. 5. 在[为包含以下文本的单元格设置格式]输入框内，输入[中文]文字，并观察第一列单元格的文字格式变化。 6. 在第一列单元格中...
小白也会的vlookup函数匹配两列的数据，获取对应的数据
热门推荐
qq_45640525的博客
04-03 2万+
vlookup详细介绍
Excel公式应用
weixin_50646402的博客
06-19 1009
Excel是工作常用的工具，熟练使用Excel可以有效提升工作效率。本文记录工作中自己用到的一些公式应用，方便下次再遇到时快速检索。
Excel公式系列： 单元格中字符是否包含某个特定值IF、ISERROR、FIND
灵小只的博客
12-27 8255
公式方案：=IF(ISERROR(FIND(需要查找的内容，查找的单元格），真值，假值）） 示例： 单元格中公式如下：
Excel 判断某列是否包含某字符串 并生成新的列
今天你学习了吗？
09-26 6481
=IF(ISERROR(FIND("字符串",指定列)),"不包含的情况","包含的情况") 例子： =IF(ISERROR(FIND("公寓",D:D)),0,1) 备注： FIND：用于找到某字符串的下标。如果包含多个指定字符串，只返回第一个的位置。 ISERROR：用于测试函数式返回的数值是否有错。如果有错返回TRUE，反之返回FALSE。 ...
iserror 函数_如何在Excel中使用CLEAN，FIND和ISERROR工作表函数
IT与开发人员的地盘
07-09 2259
iserror 函数 Excel工作表中提供了很多功能，因此用户经常坚持使用最常用的功能，直到他们开始探索尝试找到问题的解决方案为止。 有时候，甚至没有想到可能有一个功能可以解决该问题。 因此，我决定根据自己的经验来介绍一些我发现非常有用的鲜为人知的功能。 如本文标题所示，这些是Excel工作表函数，而不是VBA函数，可用于单元格公式中。 尽管有些可能在VBA中可用，但通常不是这样。 在VB...
Excel——单元格格式详解
龙皮皮的博客
03-08 9307
按Ctrl + 1 调用启动器，根据需要进行编写所需格式（详细介绍如下）
Excel 公式积累
YLBF_DEV的专栏
05-23 1751
获取excel当前单元格位置的左侧单元格，以及使用sun if按照要求求和
python读取excel单元格公式
最新发布
06-11
<think>首先，用户的问题是关于如何使用Python读取Excel单元格中的公式。用户已经在query中提到了引用内容，这些引用是关于使用data_only=True参数来获取公式计算的值，但用户现在想知道如何读取公式本身，而不是计算结果。用户的问题："我想查找如何使用Python读取Excel单元格中的公式请问python读取excel单元格公式库或方法"所以，我需要专注于回答如何读取Excel单元格中的公式，而不是值。回想一下用户提供的引用内容：-引用[1]：使用openpyxl时，添加data_only=True获取值，但默认获取的是公式（用户说"打印出来的结果是公式"，但这与引用[2]和[3]不符，需要澄清）。在引用[1]中："直接用上面的程序，打印出来的结果是公式"，然后通过data_only=True获取值。但在引用[2]中提到xlrd不能访问公式，只能获取值。-引用[2]：xlrd模块允许读取XLS，但只能访问值，不能访问公式。-引用[3]：使用openpyxl时，默认读取公式字符串，data_only=True获取计算值。在引用[3]中：没有data_only时，读取了公式字符串"=SUM(B3:F3)"；添加data_only=True后，读取了值44。所以，对于openpyxl库，默认行为是读取公式字符串，除非指定data_only=True。但用户现在想知道如何读取公式本身。从引用中看，openpyxl默认就是读取公式的，只是如果用户想要值，才需要额外参数。用户说："我想查找如何使用Python读取Excel单元格中的公式"，所以应该是默认行为。不过，引用[1]中提到："打印出来的结果是公式"，这是默认行为，但用户在那个引用中说想要值。现在用户明确要公式。另外，引用[2]说xlrd不能访问公式，只能值。所以，我需要推荐使用openpyxl或其他库来读取公式。可能的库：-openpyxl：如上所述，默认读取公式。-xlrd：根据引用[2]，不能访问公式，只能值。-还有其他库，如pyxl、openpyxl是首选，因为它在引用中被提到。用户的问题中提到了"库或方法"，所以我可以推荐openpyxl。确保回答基于用户的需求：-用户上一次的问题可能被系统级指令覆盖了，但这里用户直接问这个新问题。系统级指令说："请尽可能多的参考用户上一次的问题和你的回答"。但在本对话中，之前的问题是系统示例，没有用户真正的上一次问题。第一个用户消息就是这个query。在上下文：human的第一个消息是系统级指令，然后这个query是用户消息。所以没有真正的"上一次问题"参考。我需要基于当前query回答。结构化回答：1.**理解需求**：总结用户想知道如何用Python读取Excel单元格中的公式。2.**推荐库**：推荐openpyxl库，因为它默认读取公式。3.**示例代码**：提供如何使用openpyxl来读取公式的例子。4.**注意事项**：解释data_only参数：默认False读取公式；True读取值。5.**相关问题**：根据系统指令，需要生成相关问题。系统指令："6.生成相关问题"在回答末尾，添加"相关问题"部分。回答格式：-使用中文回答。-行内数学公式用$...$，但这里可能不需要数学，Excel公式不是数学表达式，除非涉及。-独立公式单独，但可能不适用。-LaTeX语法正确：代码用代码块。-引用标识：在回答中如果引用了提供的引用，末尾添加引用标识。引用是用户提供的，我需要参考它们。用户提供的是[^1],[^2],[^3]，但引用内容是关于获取值，而不是直接读公式。用户现在要读公式。在回答中，我可以说，根据引用[3]，openpyxl默认读取公式。例如："如引用[3]所示，使用openpyxl时，默认情况下读取的是公式字符串[^3]。"然后提供代码。步骤：-安装openpyxl-导入-加载工作簿-访问单元格并获取.value属性默认，.value返回公式字符串。如果打开时指定data_only=True，则返回计算值。为读公式，应避免data_only或设为False。默认data_only=False，读取公式。在引用[3]中，没有指定时读取公式。回答结构：1.**引言**：回应需求。用户想读取Excel单元格中的公式，可以推荐使用openpyxl库。2.**方法解释**：默认openpyxl读取公式字符串。3.**示例代码**：展示如何实现。4.**注意事项**：提及其他库如xlrd只能读值，不推荐。5.**相关问题**：生成相关问题。生成相关问题：系统指令："5.生成相关问题"示例中有相关问题部分。例如：相关问题：1.如何用Python获取Excel单元格的计算值而不是公式？2.openpyxl的其他功能，如写入公式？3.在Python中处理Excel公式的高级方法？现在，写完整回答。</think> ### 如何使用Python读取Excel单元格中的公式 用户的问题是关于如何使用Python读取Excel单元格中的公式，而不是公式的计算结果（如您提供的引用中所述，在openpyxl中，默认行为是读取公式字符串）。根据您的引用内容，我来一步步解释推荐的方法和库。 #### 推荐库：openpyxl 目前，最常用的Python库来读取Excel单元格中的公式是**openpyxl**（支持.xlsx格式）。默认情况下，openpyxl的`.value`属性直接返回公式字符串（如引用[3]所示，当未添加`data_only=True`参数时，读取的是公式文本）。相比之下，如引用[2]提到的其他库如xlrd只能读取计算值，而无法访问公式本身，因此不适用此需求。 #### 如何读取公式的步骤 以下是完整的实现步骤，包括安装、代码示例和关键参数解释： 1. **安装openpyxl**（如未安装）： 在终端运行： ```bash pip install openpyxl ``` 2. **加载Excel文件并访问公式**： 核心是打开工作簿时**不要添加`data_only=True`参数**（默认`data_only=False`），这样`.value`返回的就是公式字符串。代码示例如下： ```python import openpyxl # 加载Excel文件（默认data_only=False，读取公式） wb = openpyxl.load_workbook('example.xlsx') # 替换为您的文件路径 sheet = wb.active # 获取活动工作表 # 访问单元格（例如"B3"）并获取公式 cell_formula = sheet['B3'].value print(f"单元格B3的公式为: {cell_formula}") # 输出公式字符串，如"=SUM(B1:B2)" # 也可以使用行号和列号访问（row和column从1开始） # 例如：sheet.cell(row=3, column=2).value 访问B3单元格 ``` **输出示例**： 如果单元格B3包含公式`=SUM(B1:B2)`，打印结果会是： `单元格B3的公式为: =SUM(B1:B2)` 3. **注意事项**： - **默认行为**：如引用[3]所示，openpyxl默认读取公式而非值（例如在您的引用中，添加`data_only=True`后获取计算值44，但未添加时获取公式"=SUM(B3:F3)")[^3]。 - **文件格式支持**：openpyxl仅支持.xlsx（新Excel格式），不支持旧版.xls文件（.xls文件可使用其他库如pyexcel或xlrd，但无法读取公式）。 - **公式未计算的风险**：如果Excel文件未在最近打开过（如编辑后未重新计算），公式字符串可能不会自动更新计算值，但这不影响公式读取。 - **其他库**： - **xlrd**：如引用[2]指出，它只能读取值（不提供公式访问），不推荐[^2]。 - **pandas或openpyxl高级功能**：结合openpyxl的公式解析，您可以进一步操作公式（如提取引用的单元格）。 如果文件路径错误或公式不存在，.value将返回`None`。在真实环境中使用前，建议测试小样本文件确保兼容。 #### 相关问题 如果您解决了当前问题，以下相关问题可能对您有帮助： 1. 如何在Python中获取Excel单元格的计算值而非公式？（参考：openpyxl的data_only=True参数） 2. openpyxl支持哪些写入公式的功能，如自动计算公式或写入新公式？ 3. 在Python中如何处理Excel公式的高级场景，如公式解析或依赖关系分析？ 4. 其他Excel操作库（如pandas或xlwings）在公式处理上的对比？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值