实现 Excel 多列数据组合

前言

因好朋友的工作要求需要在 Excel上实现 3种数据的排列组合,如果使用编程语言无非就是嵌套循环,而要在 Excel 实现没怎么实现过,在实现后觉得挺有意思的特此记录方便后续非工程师的人员可以方便实现该需求。

效果预览

![![在这里插入图片描述](https://img-blog.csdnimg.cn/e58a86d6a4cc496cbc9a1c6f650f17bd.png](https://img-blog.csdnimg.cn/586704f0bd8445db9f73ff38a6ca7b81.png

Excel函数须知

COUNTA()
用于解决统计某列非空单元格数目,常用的一般是 COUNT(),但是他无法处理非数字列的统计,本文用于计算集合的最大值。
举例 COUNTA(A:A),效果图中做了减 1的操作,所以显示为 3

INDEX()
在给定单元格区域中,返回特定的单元格值或者引用,本文用于赋值。
举例 INDEX(A:A, 2), 返回的结果就是 上海。

ROW()
返回一个引用的行号,本文用于实现遍历。

MOD()
返回两数相除的余数,本文用于实现递归循环。

实现说明

如效果图的结果来看

在 J列的函数公式:INDEX(A:A,(ROW(J2)-2)/($F$2 * $G$2) + 2)

步骤公式说明
0ROW(J2)-2因为需要过滤掉 表头,并且从 0 开始所以需要 - 2
1$F$2 * $G$2即产品名称和配件的组合数目,即循环次数, 2 * 5
2(ROW(J2)-2) / ($F$2 * $G$2)取整数进行分类汇总
3INDEX(A:A, (ROW(J2)-2) / ($F$2 * $G$2) + 2)在 A列范围内,而 + 2 则是确保正确取值过滤无意义数据,即A0,A1

如此实现,那 I列数据就会如下图成功遍历 30条有效数据(3 * 2 * 10)

K列的函数公式:=INDEX(B:B,MOD((ROW($J2)-2) / $G$2,$F$2) + 2)L列的函数公式:=INDEX(C:C,MOD(ROW($J2)-2)),$G$2) + 2)

上述公式一般都大同小异,需要注意是 K 列是 / $G$2,L列 没有 / (可以理解为 / 1),这就是对应的循环次数的不一致,从而实现程序上的嵌套循环。

想法

很有趣的体验,需了解循环次数防止异常,又要合理使用 $符号确保支持水平扩展,从实现复杂度来看非常简单,不过跟用程序实现,前者更具成就感。

### 实现Excel数据有效性多级联动 为了实现Excel 中的数据有效性多级联动功能,可以按照以下方法操作: #### 使用辅助列和INDIRECT函数创建动态下拉列表 1. **准备基础数据** 假设有一份员工名单及其所属部门的信息,在`Sheet2`中整理好这些信息。例如,“销售部”的成员有张三、李四;“技术部”的成员有王五、赵六。 2. **定义名称范围** 在公式选项卡里找到【名称管理器】,新建两个名称:“部门”用于表示所有可能的一级类别(即各个部门),其引用位置为包含全部部门名称的区域;另一个名为“人员”,利用`INDIRECT`函数指向随选定部门变化而变动的具体人员名单所在单元格区间。对于“人员”,可设定公式如下: ```excel =IF(A2="","",INDIRECT(A2)) ``` 此处假定A列为展示并供选择部门的位置,此表达式的含义是在选择了特定部门之后返回对应于该部门下的工作人员姓名数组,如果未作任何选择则为空字符串[^1]。 3. **应用数据验证规则** 转到希望显示最终结果的工作表(比如`Sheet1`)。针对想要加入首层筛选条件(这里是部门)的单元格执行常规的数据校验流程——指定允许类型为“序列”,来源处输入刚才建立好的代表一级类目的命名区域名“部门”。 随后处理次层级关联部分:同样选取目标单元格实施数据检验措施,不过这次把允许项设成“序列”,而在源框内键入上述构建完毕用来获取相应子集元素集合的那个带有间接引用特性的自定义名字“人员”。这样就完成了基于前者所做抉择自动更新后者可用选项的任务[^2]。 通过这种方式能够有效地建立起具有层次结构关系的选择界面,不仅限于两级,理论上支持更多级别的嵌套组合,只要合理规划各阶段间逻辑联系即可达成预期效果。 #### 结合VBA增强交互体验 除了依靠内置的功能外,还可以借助 VBA 编写宏代码进一步优化用户体验。当用户在一个单元格做出更改时触发 `Worksheet_Change` 事件来即时刷新其他依赖于此值的相关联控件状态,从而确保整个系统的同步性和一致性。 ```vba Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then ' 假设部门位于 A 列 With Me.Range("B:B") ' B 列放置具体人员 .ClearContents Application.EnableEvents = False On Error Resume Next .Validation.Delete .Validation.Add Type:=xlValidateList, AlertStyle:= _ xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=INDIRECT(A" & Target.Row & ")" On Error GoTo 0 Application.EnableEvents = True End With End If End Sub ``` 这段脚本会在检测到 A 列中的内容发生变化时清除并重新加载 B 列里的项目清单,保证两者之间的连贯性。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值