Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 5224|回复: 20

求教

[复制链接]
发表于 2019-8-24 16:18 | 显示全部楼层 |阅读模式
将M2粘贴到N2重复7次,S得到7次不同的结果,然后取7次S的最大值在AE列显示。如何编写VBA。

本人用录制宏的办法,会显示U至AA列,后AE取最大值。显示U至AA列会占用大量单元格和内存,也会很慢。

要求不要显示U至AA列! 11.png 新建 Microsoft Excel 工作表 (2).zip (24.55 KB, 下载次数: 3)
发表于 2019-8-24 18:15 | 显示全部楼层
1、在开始拷贝粘帖之前,加上 Application.ScreenUpdating = False,关闭屏幕自动更新;
2、在完成拷贝粘帖之后,加上 Application.ScreenUpdating = True,恢复屏幕自动更新;
这样程序速度会快很多!
另外,宏录制的代码要优化,那些选择(select、selection)可以精简掉,程序运行并不需要激活单元格。
参考以下代码,后续自己试试修改。
  1. Sub 宏2()
  2.     '加上这句,关闭屏幕自动更新,请养成习惯
  3.     Application.ScreenUpdating = False
  4.    
  5.     '精简后的代码
  6.     Range("M2").Copy
  7.     Range("N2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  8.         :=False, Transpose:=False
  9.     Range("S4:s53").Copy
  10.     Range("U4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  11.         :=False, Transpose:=False
  12.    
  13.     '其他代码自己参照上述代码修改
  14.     '操作很有规律,可以考虑用 for 循环代替,在循环体内将目标单元格的 Colume 值加 1 即可,实现从 U 到 Y 列,增加可读性,减少代码量
  15.         
  16.     '程序结束前要重新打开屏幕自动更新,否则即使程序运行完毕还会一直影响 Excel,需要手工刷新。
  17.     Application.ScreenUpdating = True
  18. End Sub
复制代码
回复

使用道具 举报

 楼主| 发表于 2019-8-24 22:29 | 显示全部楼层
本帖最后由 青龙北斗星 于 2019-8-24 22:50 编辑

谢谢楼上给的方案,但我要的不是这样效果,而是在程序内部统计7次结果的最大值,只显示统计的最大值,不需要显示显示U至AA列的6次结果,
回复

使用道具 举报

发表于 2019-8-25 09:35 | 显示全部楼层
本帖最后由 rardge2015 于 2019-8-25 09:41 编辑

那最后把U到AA列用clear清除不就好了?
删除 U 到 AA 列之前,先把结果结果列保存为数值,目前是公式,一删除结果也跟着变了。

计算过程需要临时存储的地方,要么就用 U 到 AA 列,最后删除;要么就在内存中建立数组。
从你工作表的方式来看是程序+函数模式,感觉用 U 到 AA 列更方便些。
但用数组的话程序执行会快些,但代码多了,要在多维数组中循环找最大值。

回复

使用道具 举报

发表于 2019-8-25 10:35 | 显示全部楼层
研究了一下你的公式,读懂了需求,试试这个吧,仅需要 B 到 K 列,其他都可以删除了。
  1. Sub test()
  2.     Dim srcData As Variant, tmpData As Variant, tarData As Variant
  3.     Dim intCurr As Integer, intNext As Integer, intLen As Integer
  4.     Dim i As Integer, n As Integer
  5.    
  6.     '将原始数据(B4到K53)读入数组
  7.     srcData = Sheets("Sheet1").Range("B4").CurrentRegion
  8.    
  9.     '取数组一维长度
  10.     intLen = UBound(srcData, 1)
  11.    
  12.     '创建临时数组与结果数组,数组长度与 srcData 一维等长
  13.     ReDim tmpData(1 To intLen)
  14.     ReDim tarData(1 To intLen)
  15.     For i = 1 To intLen
  16.         tmpData(i) = 0
  17.         tarData(i) = 0
  18.     Next
  19.    
  20.     '指定从哪列数据开始计算,假设从第5列开始,也就是表中的 F 列
  21.     intCurr = 5
  22.    
  23.     '判断次列的序号
  24.     If intCurr = 10 Then
  25.         intNext = 1
  26.     Else
  27.         intNext = intCurr + 1
  28.     End If
  29.    
  30.     '按题意,循环 7 次
  31.     For n = 1 To 7
  32.         '当前列与次列相乘取个位数
  33.         For i = 1 To intLen
  34.             tmpData(i) = srcData(i, intCurr) * srcData(i, intNext) Mod 10
  35.             
  36.             '每次保留最大的值
  37.             If tmpData(i) > tarData(i) Then
  38.                 tarData(i) = tmpData(i)
  39.             End If
  40.         Next
  41.     Next
  42.    
  43.     '将结果写入工作表
  44.     Sheets("Sheet1").Range("AF4").Resize(intLen, 1) = WorksheetFunction.Transpose(tarData)
  45. End Sub
复制代码
回复

使用道具 举报

 楼主| 发表于 2019-8-25 11:45 | 显示全部楼层
rardge2015 发表于 2019-8-25 10:35
研究了一下你的公式,读懂了需求,试试这个吧,仅需要 B 到 K 列,其他都可以删除了。

谢谢,辛苦了!我试一下!
回复

使用道具 举报

 楼主| 发表于 2019-8-25 12:09 | 显示全部楼层
青龙北斗星 发表于 2019-8-25 11:45
谢谢,辛苦了!我试一下!

试了,但没有取出最大值,只显第一次的计算结果。
回复

使用道具 举报

 楼主| 发表于 2019-8-25 12:16 | 显示全部楼层
本帖最后由 青龙北斗星 于 2019-8-25 13:16 编辑
青龙北斗星 发表于 2019-8-25 12:09
试了,但没有取出最大值,只显第一次的计算结果。

不用理会次列的数据,即计算部分留给工作表计算,只是将S列得到7次不同的结果取最大值。
回复

使用道具 举报

发表于 2019-8-25 12:54 | 显示全部楼层
抱歉,当前指针没移动,把以下代码重新贴一下。
如果仅仅是变换计算方式,但规则不变(仍然是当前列与次列计算),我在代码总标注了在哪里修改计算公式。

  1.     '指定从哪列数据开始计算,假设从第5列开始,也就是表中的 F 列
  2.     intCurr = 5
  3.    
  4.     '按题意,循环 7 次
  5.     For n = 1 To 7
  6.         '当前列及次列指针移动
  7.         If intCurr < 10 Then
  8.             intNext = intCurr + 1
  9.         ElseIf intCurr = 10 Then
  10.             intNext = 1
  11.         Else
  12.             intCurr = 1
  13.             intNext = intCurr + 1
  14.         End If
  15.    
  16.         '当前列与次列相乘取个位数
  17.         For i = 1 To intLen
  18.             tmpData(i) = srcData(i, intCurr) * srcData(i, intNext) Mod 10 '在这里修改计算公式
  19.             
  20.             '每次保留最大的值
  21.             If tmpData(i) > tarData(i) Then
  22.                 tarData(i) = tmpData(i)
  23.             End If
  24.         Next
  25.         
  26.         intCurr = intCurr + 1
  27.     Next
复制代码
回复

使用道具 举报

 楼主| 发表于 2019-8-25 13:29 | 显示全部楼层
rardge2015 发表于 2019-8-25 12:54
抱歉,当前指针没移动,把以下代码重新贴一下。
如果仅仅是变换计算方式,但规则不变(仍然是当前列与次列 ...

十分感谢!辛苦了!
还有如果计算部分留给工作表计算,只是将S列得到7次不同的结果取最大值。又如何修改呢,因为计算方式有时会有变化!

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 01:59 , Processed in 1.047240 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表