1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【GAS/VBA】任意のサイズの乱数表をワークシート上に生成する

Posted at

ワークシート上に適当なデータを散らかしたい時ってありませんか?
私はあります。ので、そういう時に使えるスクリプトを作りました。

Googleスプレッドシート
function RandTable() {
  tmp = Browser.inputBox('乱数表のサイズをカンマ区切りで入力してください').split(',')
  row = +tmp[0]
  col = +tmp[1]
  tgtCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell()
  //処理をキャンセルできる
  if (Browser.msgBox(
      tgtCell.getA1Notation()+'から'+row+''+col+'列の乱数表を書き込みます',
      Browser.Buttons.OK_CANCEL) === 'ok') {
    rowArr = []
    for(var i=1; i<=row; i++) {
      colArr = []
      for(var j=1; j<=col; j++) {
        colArr.push(Math.round(Math.random()*100))
      }
      rowArr.push(colArr)
    }
    tgtCell.offset(0,0,row,col).setValues(rowArr)
  }
}
Excel
Sub RandTable()
    Dim tmp As Variant
    tmp = Split(InputBox("乱数表のサイズをカンマ区切りで入力してください"), ",")
    Dim row As Long, col As Long
    row = tmp(0)
    col = tmp(1)
    Dim tgtCell As Range
    Set tgtCell = Selection
    ' 処理をキャンセルできる
    If (MsgBox(tgtCell.Address(0, 0) & "から" & row & "行" & col & "列の乱数表を書き込みます", _
        vbYesNo + vbInformation) = vbYes) Then
        Dim table() As Long
        ReDim table(1 To row, 1 To col)
        Dim i As Long, j As Long
        For i = 1 To row
            For j = 1 To col
                table(i, j) = Round(Rnd() * 100)
            Next
        Next
        tgtCell.Resize(row, col) = table
    End If
End Sub

Googleスプレッドシートの場合はカスタム関数にすることも出来ます。

カスタム関数
function RandTableEasy(row,col) {
  rowArr = []
  for(var i=1; i<=row; i++) {
    colArr = []
    for(var j=1; j<=col; j++) {
      colArr.push(Math.round(Math.random()*100))
    }
    rowArr.push(colArr)
  }
  return rowArr
}

Excelはそろそろワークシート関数で配列の戻り値を受け取れるようになるんでしょうか?

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?