excel标题动态读取列数据 java excel动态引用列

转载

云端筑梦大师 2024-03-02 08:53:55

文章标签 excel标题动态读取列数据 java spring java 后端条件判断 文章分类 Java 后端开发

文章目录

前言
一、问题拆解
二、问题逐渐解决引入的关联函数

获取记录数
获取A列区域的开始单元格位置
获取A列区域的结束单元格位置
获取A列区域的极值
条件判断并获取最终结果

总结

前言

前两天，有个朋友提出了个Excel使用的问题，他的数据大致是这样的

他的需求是用什么方式能够快速根据B列信息计算A列的相关区域数据，并在在C列（最大最小值）相应区域的单元格显示。

excel标题动态读取列数据 java excel动态引用列_excel标题动态读取列数据 java

举个例子
当B2单元格显示为“负数”时，C2单元格显示A2~A8之内的最小数值，即为 -1606
当B9单元格显示为“正数”时，C9单元格显示A9~A23之内的最大正数，即为 5290

这个问题，看似简单，我真正尝试时才发现要真想做到动态，需要应用多多个Excel函数的组合应用。

一、问题拆解

任何复杂的问题，都是由多个简单的问题组合而成的。对于Excel的问题而言，一般来说，复杂的问题，就是一堆Excel函数组合而成。这里我们先按照Excel的函数思路对问题做个拆解

解决问题，首先需要做个条件判断。
需要逐行对B列的数据做判断，条件判断有3个分支结果，分别对应值为“正数”、“负数”、空的情况。、
获取A列对应区域的最大（最小）值
当A列相应区域为“负数”时，获取最小值，当A列相应区域为“正数”时，则获取最大值
获取要计算的A列区域的开始单元格位置
通过分析，可以看出当B列有值时，其所在行所对应的A列单元格就是A列参与计算相应区域的开始单元格
获取要计算要计算的A列区域的结束单元格位置
很显然，结束单元格的位置与开始单元格的位置具有一定的关联关系，它们直接具有如下关系
结束单元格的行号=开始单元格的行号+相关区域和记录数-1
获取要计算的A列区域的记录数
A列区域的记录数与B列的非空值具有一定关联关系，只要确定了B列两个上下相邻区域的行号，也就确定了对应A列区域的记录数

二、问题逐渐解决引入的关联函数

获取记录数

要解决该问题首先需要确定当前行的行号。
在Excel中具有函数 Rows()
可确定当前单元格所在行的行号，这个函数与Rows(array)不同，这个函数可不需要参数直接返回当前所在行的值。
其次要获取B列的相对地址
这时候需要引入Address函数，该函数语法为

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
其主要参数为：
row_num    必需。 一个数值，指定要在单元格引用中使用的行号。
column_num    必需。 一个数值，指定要在单元格引用中使用的列号。

然后引用相对地址并获取相关的值
这里引入了Indirectly 函数，该函数相关说明为

基本语法：INDIRECT(ref_text, [a1])
必填参数：ref_text 为R1C1 样式引用
作用：返回由文本字符串指定的引用。

最后获取相应的行号
这里引入了Match函数，该函数相关说明为

语法：
MATCH(lookup_value, lookup_array, [match_type])
参数说明：
◾ lookup_value    必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。
lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
◾ lookup_array    必需。 要搜索的单元格区域。
◾ match_type    可选。 数字 -1、0 或 1。 
作用：
在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

综合使用这些函数，可以获取相应的记录数，相关范例如下

excel标题动态读取列数据 java excel动态引用列_java_02

对应的函数运算结果为

excel标题动态读取列数据 java excel动态引用列_后端_03

获取A列区域的开始单元格位置

采用与获取记

使用相同的函数，我们可以获取对应的A列区域的开始单元格位置

获取A列区域的结束单元格位置

根据之前的问题拆解分析，我们结合开始单元格位置与记录数的结果，获取到相应结束单元格位置

获取A列区域的极值

通过使用Excel的极值函数，我们可以计算出相关区域的极大值（或极小值），相关函数说明如下

函数：MAX(number1, [number2], ...)
作用：返回一组值中的最大值。

函数：MIN(number1, [number2], ...)
作用：返回一组值中的最小值。

条件判断并获取最终结果

上述几个步骤，我们已经可以计算出第一行的相关值，由于B列的值是有变化的，所以我们需要引入IF函数来做条件判断生成相关最终结果，IF是Excel相对较常用函数。
相关范例如下

=IF(B2="负数",MIN(INDIRECT(ADDRESS(ROW(),1)):INDIRECT(ADDRESS(ROW()+MATCH("正数",INDIRECT(ADDRESS(ROW(),2)):$B$1000,0)-2,1))),IF(B2="正数",MAX(INDIRECT(ADDRESS(ROW(),1)):INDIRECT(ADDRESS(ROW()+MATCH("正数",INDIRECT(ADDRESS(ROW(),2)):$B$1000,0)-2,1))),""))

其相关运算结果如下

excel标题动态读取列数据 java excel动态引用列_spring_10