VBA（2023数模1）

最新推荐文章于 2024-07-21 22:49:59 发布

1-----2

最新推荐文章于 2024-07-21 22:49:59 发布

阅读量74

点赞数

分类专栏：数学建模文章标签：数学建模

本文链接：https://blog.csdn.net/m0_51965407/article/details/132796154

版权

数学建模专栏收录该内容

3 篇文章 0 订阅

订阅专栏

VBA（2023数模1）

开始看了A题，也写了一晚上，但上一年也是C题，就觉得会
对于C题，也只能对第一问看看，数据量太大了，这是这道题感悟，另一方面，之前参加一次比赛自己不是主力，没体会过建模的难处，这次理解了。
对于第一问，要分析各品类的销售量的分布规律，而附件1是品类和单品之间的关系，附件2才是单品流水账。所以要研究随时间的变化情况，就要合并两个表格。
合并即是把附件2的单品编码变成对应的品类。比如
在这里插入图片描述
这一条，就是

属于辣椒类，就要将这一行的102900005117056变成后面的1011010504，可以使用替换功能来进行替换，但总共有251中单品，于是查阅GPT，能够使用VBA，进行操作
下面是GPT对VBA介绍

简单的说，他就是代替我们做一些大工程。
现在我们让其将附件2中第一行代表的都变为1011010504
如下操作：文件-》更多-》选项-》自定义功能区-》开发工具勾上-》确定
在这里插入图片描述
回到初始位置，

点击开发工具-》Visual Basic-》点击对应的表格，双击-》右边就会出现白色

代码如下

Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010504"
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        If cell.Value = "102900005117056" Then
            ' 将旧值替换为新值
            cell.Value = replaceValue
        End If
    Next cell
End Sub

现在替换的就是一个数，点击运行，在这里插入图片描述
就是如下图所示

代码段里面最重要的是实际范围、旧值、新值，把这些替换成你所需要的
所以通过对所有种类都进行一遍，那也待251遍
所以需要整合一下
运行6遍就好了

//第一块代码
Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    Dim oldValues() As Variant
    Dim i As Long
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010101"
    
    ' 用旧值填充数组
    oldValues = Array("102900005115168", "102900005115199", "102900005115625", "102900005115748", "102900005115762", "102900005115779", "102900005115786", "102900005115793", "102900005115816", _
                      "102900005115823", "102900005115854", "102900005115861", "102900005115878", "102900005115885", "102900005115908", "102900005115946", "102900005115960", _
                      "102900005115977", "102900005115984", "102900005116639", "102900005116776", "102900005116790", "102900005116806", "102900005118572", "102900005118817", _
                      "102900005118831", "102900005119975", "102900005122654", "102900005128748", "102900011000175", "102900011000571", "102900011002414", "102900011006689", _
                      "102900011006948", "102900011006955", "102900011007464", "102900011007471", "102900011007495", "102900011008133", "102900011008164", "102900011008485", _
                      "102900011008492", "102900011008515", "102900011008522", "102900011008676", "102900011015384", "102900011015391", "102900011021644", "102900011022849", _
                      "102900011022924", "102900011023464", "102900011026502", "102900011026618", "102900011027462", "102900011027615", "102900011029688", "102900011030042", _
                      "102900011030059", "102900011030097", "102900011030103", "102900011030110", "102900011030134", "102900011030141", "102900011030158", "102900011030400", _
                      "102900011030417", "102900011030905", "102900011031216", "102900011032176", "102900011032282", "102900011032480", "102900011032589", "102900011032787", _
                      "102900011033081", "102900011033173", "102900011033234", "102900011033241", "102900011033531", "102900011033562", "102900011033586", "102900011033906", _
                      "102900011033920", "102900011034200", "102900011034217", "102900011034224", "102900011034231", "102900011034316", "102900011034323", "102900011034354", _
                      "102900011035481", "102900011035764", "102900011035771", "102900011035849", "102900011036686", "102900051000890", "102900051009220", "102900051010455", _
                      "102900051010790", "106971563780002", "106972776821582")
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        For i = LBound(oldValues) To UBound(oldValues)
            If cell.Value = oldValues(i) Then
                ' 将旧值替换为新值
                cell.Value = replaceValue
                Exit For
            End If
        Next i
    Next cell
End Sub

//第二块代码
//当然可以使用代码来实现在 Excel 中将多个数据替换为一个数据。以下是使用 VBA（Visual Basic for Applications）的示例代码：

Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010201"
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        If cell.Value = "102900005116714" Or cell.Value = "102900011000632" Or cell.Value = "102900011009970"  Or _
        cell.Value = "102900011033913" Or cell.Value = "102900011034026" Then
            ' 将旧值替换为新值
            cell.Value = replaceValue
        End If
    Next cell
End Sub 

//第三块代码
//当然可以使用代码来实现在 Excel 中将多个数据替换为一个数据。以下是使用 VBA（Visual Basic for Applications）的示例代码：

Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    Dim oldValues() As Variant
    Dim i As Long
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010402"
    
    ' 用旧值填充数组
    oldValues = Array("102900005116042", "102900005116899", "102900005118824", "102900011001561", "102900011001691", "102900011007969", "102900011009277", "102900011010891", "102900011018132", _
                      "102900011021842", "102900011023976", "102900011024010", "102900011032114", "102900011032732", "102900011034569", "102900011035511", "102900011035962", _
                      "102900051000944", "102900051006229")
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        For i = LBound(oldValues) To UBound(oldValues)
            If cell.Value = oldValues(i) Then
                ' 将旧值替换为新值
                cell.Value = replaceValue
                Exit For
            End If
        Next i
    Next cell
End Sub 

//第四块代码
Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010501"
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        If cell.Value = "102900005116257" Or cell.Value = "102900005116509" Or cell.Value = "102900011000335" Or _
        cell.Value = "102900011009444" Or cell.Value = "102900011016909" Or cell.Value = "102900011022764" Or _
        cell.Value = "102900011033975" Or cell.Value = "102900011033982" Or cell.Value = "102900011033999" Or _
        cell.Value = "102900051000463" Then
            ' 将旧值替换为新值
            cell.Value = replaceValue
        End If
    Next cell
End Sub

//第五块代码
Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    Dim oldValues() As Variant
    Dim i As Long
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010504"
    
    ' 用旧值填充数组
    oldValues = Array("102900005116219","102900005116226", "102900005116233", "102900005116905", "102900005116943", "102900005117056", "102900005117209", "102900005119968", "102900005123880", _
                      "102900005125808", "102900011000328", "102900011000861", "102900011001219", "102900011009772", "102900011016701", "102900011022030", "102900011023648", _
                      "102900011027479", "102900011028407", "102900011029176", "102900011029275", "102900011029299", "102900011029305", "102900011031100", "102900011031582", _
                      "102900011031735", "102900011031742", "102900011031759", "102900011032022", "102900011032145", "102900011032206", "102900011032213", "102900011032220", _
                      "102900011032237", "102900011032244", "102900011032251", "102900011032343", "102900011032350", "102900011032367", "102900011032848", "102900011034262", _
                      "102900011034439", "102900011035078", "102900011036242", "102900051004294")
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        For i = LBound(oldValues) To UBound(oldValues)
            If cell.Value = oldValues(i) Then
                ' 将旧值替换为新值
                cell.Value = replaceValue
                Exit For
            End If
        Next i
    Next cell
End Sub

//第六块代码
Sub ReplaceMultipleValues()
    Dim searchRange As Range
    Dim replaceValue As String
    Dim oldValues() As Variant
    Dim i As Long
    
    ' 设置要搜索和替换的范围
    Set searchRange = Worksheets("Sheet1").Range("C2:C878504") ' 更改为你的实际范围
    
    ' 设置要替换为的值
    replaceValue = "1011010801" ' 修改为您要替换成的新值
    
    ' 用旧值填充数组
    oldValues = Array("102900005115250", "102900005116530", "102900005116547", "102900005116837", "102900005116912", "102900005117353", "102900005119098", "102900005119104", _
                      "102900005119944", "102900005125815", "102900011001806", "102900011001813", "102900011007044", "102900011008577", "102900011009246", "102900011010563", _
                      "102900011011058", "102900011011546", "102900011011669", "102900011011782", "102900011012482", "102900011012871", "102900011012994", "102900011013274", _
                      "102900011018095", "102900011021675", "102900011021699", "102900011023075", "102900011026793", "102900011030561", "102900011030608", "102900011030615", _
                      "102900011030622", "102900011030639", "102900011030912", "102900011030929", "102900011031599", "102900011031841", "102900011031858", "102900011031926", _
                      "102900011031995", "102900011032619", "102900011032626", "102900011032633", "102900011032640", "102900011033937", "102900011033944", "102900011033968", _
                      "102900011034330", "102900011034538", "102900011034705", "102900011035740", "102900011035788", "102900011036068", "102900011036266", "102900051009336", _
                      "106930274220092", "106930274620090", "106931885000035", "106931885000356", "106949711300068", "106949711300167", "106949711300259", "106956146480197", _
                      "106956146480203", "106957634300010", "106957634300058", "106958851400125", "106971533450003", "106971533455008", "106973223300667", "106973990980123")
    
    ' 循环遍历搜索范围中的每个单元格
    For Each cell In searchRange
        ' 检查单元格的值是否需要替换
        For i = LBound(oldValues) To UBound(oldValues)
            If cell.Value = oldValues(i) Then
                ' 将旧值替换为新值
                cell.Value = replaceValue
                Exit For
            End If
        Next i
    Next cell
End Sub

上面一共六块代码，每一次输入一块，最后就实现了替换，核心在于构建数组，也可以善用or。
最终弄出来，再用excel的替换功能就能够达到每一行都是6类中的一个。就如这样。
在这里插入图片描述

1-----2

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
VBA（2023数模1）

属于辣椒类，就要将这一行的102900005117056变成后面的1011010504，可以使用替换功能来进行替换，但总共有251中单品，于是查阅GPT，能够使用VBA，进行操作。对于第一问，要分析各品类的销售量的分布规律，而附件1是品类和单品之间的关系，附件2才是单品流水账。对于C题，也只能对第一问看看，数据量太大了，这是这道题感悟，另一方面，之前参加一次比赛自己不是主力，没体会过建模的难处，这次理解了。上面一共六块代码，每一次输入一块，最后就实现了替换，核心在于构建数组，也可以善用or。
复制链接

扫一扫