本文于2023年9月3日首发于本人同名公众号:Excel活学活用,更多文章案例请搜索关注!
快速浏览
往期合集:【2023年3月】【2023年4月】【2023年5月】【2023年6月】【2023年7月】【2023年8月】
实用案例
|收费管理系统|中医诊所收费系统||日期控件|简单的收发存||电子发票管理助手|Excel表格拆分神器|
|Excel多种类型文件合并||电子发票登记系统(Access版)||批量生成凭证抽查底稿|
收费使用项目
|财务管理系统|
内容提要
批量求不同等差数列的公差及项
大家好,我是冷水泡茶,今天在知乎上看到一个提问,怎么实现红框里批量等差数列?
乍一看,我心想这还不简单,定位、条件空值,输入公式=A1+1,正准备写回答,看到他下面的效果图,我知道我too simple了:
啥情况?还有小数?原来他这个等差序列的差不全是1。那咋搞呢?用公式吗?一时也想不出来,用公式还得另外弄一列,我瞄了一眼其他人的回答,公式也是老长老长的,头晕。看来还是得用VBA啊:
基本思路:
1、我们要取得等差序列的第一个数字(首项)和最后一个数字(末项),并且要取得这两个数字之间的间隔数(项数),就可以求出差值(公差),再逐个累加求得每一项的值。
2、具体做法我们可以循环这一列,记下前后两个数字的行号m、n,求出他们对应数字之间,n-m+1个等差数列的公差。
3、再循环m~n,把它们之间的空格逐行按公差递增填上数字。
VBA代码
在模块1,建立GenerateSequence过程:
Sub GenerateSequence() Dim ws As Worksheet Dim rng As Range Dim lastRow As Integer Dim dif As Double '等差数列的公差 Set ws = ThisWorkbook.Sheets("Sheet1") With ws lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set rng = .Range("a1:a" & lastRow) With rng For i = 2 To .Rows.Count - 1 If .Cells(i, 1) = "" Then If .Cells(i, 1) <> .Cells(i - 1) Then m = i - 1 End If If .Cells(i, 1) <> .Cells(i + 1) Then n = i + 1 '两个数a、b之间,生成n个元素的等差数列公式:b = a + (n - 1)d dif = (.Cells(n, 1) - .Cells(m, 1)) / (n - m) For j = m + 1 To n - 1 .Cells(j, 1) = .Cells(j - 1, 1) + dif Next End If End If Next End With End WithEnd Sub
代码解析:
1、首先定义一些变量。
2、把Sheet1表的第一列的数据区域设置为一个区域rng。
3、循环rng,从第2行开始,找到一个上一行有数字的空行,则它的上一行就是数列的开头,再找到一个下一行有数字的空行,则它的下一行就是数列的结束。
4、通过等差数列求公差公式:d = (b - a) / (n - 1),计算公差dif。这里a为第一个数,b为第二个数,n为包括ab在内的行数,也就是等差数列的项数,在我们的程序中,项数是n-m+1,带到公式中就是n-m。
5、接着循环m~n,把空白单元格的值设为上一个单元格加上公差。
总结
1、代码本身并不复杂,象这种顺序定位两个数字m和n的方法,我们在【Excel VBA 工程项目报价表数据提取整理/格式调整】和【Excel VBA 按同一个项目合并单元格文字】都有用到。
2、本例中另一个比较关键的地方是计算公差,如果不知道计算等差数列公差的公式的话,可能不知从何下手。实际上,我也不知道,但我凭直觉,就写了一个两个数的差除以他们行号的差,居然蒙对了。看结果是没有问题,但我还是要琢磨清楚其中的道理,在纸上写一个等差数列(说得这么玄乎,实际是就1,2,3,4,5),验证公式没毛病,但心里还是不踏实,这只是一个特例。于是,就上网去搜了搜:
但我还是不太敢确定,于是我又问这个公式是怎么来的?
AI就是方便!
~~~~~~End~~~~~~
喜欢就点个赞、点在看、留个言、分享一下呗!感谢!