excel - 如何在 x 列和 y 行上生成随机值范围,这些值总和为 EXCEL 2007 中行和列上的特定值
问题描述
我试图在 3 个单独的列和不断变化的行数中动态生成从 0 到 5 的随机值。每行的值总和必须为 5,并且列上的特定值。每列的总和是不同的。
我的工作表计算 3 列中每一列所需的值和所需的行数。
我对公式有平均技能,而对 VBA 没有,但有兴趣学习。
解决方案
解释
下面的代码使用在 B 列中找到的数字并计算一个随机数,循环直到找到 B 列中的最后一行。轴完全由宏格式化。该宏唯一需要运行的是 B 列中的值。该代码已被大量注释以供进一步解释。
有关如何在工作簿中包含宏的步骤
- 第 1 步:打开 VBA(在 Excel 中的热键:alt-F11)
第2步:
创建按钮的步骤
第 1 步:如果您的 Excel 没有从标题栏创建按钮的选项,请按照以下步骤操作,否则跳到第 2 步。
第2步:
第 3 步:单击您的按钮/享受您的宏。
代码(已测试)
Sub test()
' dim your variables. this tells vba what type of variable it is working with
Dim lRow As Long
' defining wb is easier than typing out ThisWorkbook everytime
Dim wb As Workbook: Set wb = ThisWorkbook
' defining ws is easier than typing out Worksheets("Sheet1") everytime
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
' find the last row in column b (2) in the above defined ws
lRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
' loop through rows 3 to last row
For i = 3 To lRow
' generate a random number between 0 and the row contents of column B (5)
ws.Cells(i, 3).Value = Int(Rnd() * (ws.Cells(i, 2).Value + 1))
' generate a random number between 0 and the difference between column B and colum C
ws.Cells(i, 4).Value = Int(Rnd() * (ws.Cells(i, 2).Value - ws.Cells(i, 3).Value))
' subtract the difference between column B and the sum of column C and column D
ws.Cells(i, 5).Value = ws.Cells(i, 2).Value - (ws.Cells(i, 3).Value + ws.Cells(i, 4).Value)
Next i
' sum column C (column 3) and place the value in C2
ws.Cells(2, 3).Value = Application.WorksheetFunction.Sum(Range(Cells(3, 3), Cells(lRow, 3)))
' sum column D (column 4) and place the value in D2
ws.Cells(2, 4).Value = Application.WorksheetFunction.Sum(Range(Cells(3, 4), Cells(lRow, 4)))
' sum column E (column 5) and place the value in E2
ws.Cells(2, 5).Value = Application.WorksheetFunction.Sum(Range(Cells(3, 5), Cells(lRow, 5)))
' format from A3 to the last row in column A - cell alignment / merge cells / value
With ws.Range(Cells(3, 1), Cells(lRow, 1))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Value = "row sum"
End With
' format from C1 to E1 - cell alignment / merge cells / value
With ws.Range(Cells(1, 3), Cells(1, 5))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Value = "column sum"
End With
' format from B3 to the last row in column B - color formatting
With ws.Range(Cells(3, 2), Cells(lRow, 2)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' format from C2 to E2 - color formatting
With ws.Range(Cells(2, 3), Cells(2, 5)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
推荐阅读
- node.js - TypeORM + Webpack 导致 SyntaxError: Unexpected token for entity file
- r - 在ggplot中标记特定点
- azure - 在 PhoneFactor-InputOrVerify Technical Profile(B2C 自定义用户流程)中,是否可以允许用户更改其默认手机号码?
- python - 如何通过 Python 中的 URL 下载 Azure Blob 存储文件?
- python - 用django连接mysql数据库
- php - 如何在 Codeigniter 配置文件中使用环境变量?
- python-3.x - 熊猫对整行重新采样应用功能?
- postgresql - PostgreSQL如何检查表是否存在?
- git - 修改 git 分支以从上游提交分支
- rest - 无法读取 HTTP 消息:ion:JSON 解析错误:VALUE_STRING 中的意外输入结束:在套接字上读取意外 EOF