如何将Excel范围分配给2D数组？

Question

您好！请问如何将Excel中的Range("G2:AA1000")赋值给一个二维数组？如果可能的话，如何在对这个二维数组进行一些操作后将其返回到相同的范围？在分配一个区域给一个二维数组之后，如何从该2D矩阵中识别每行？

谢谢！

- arun_roy

Answer 1

使用数组轻松地对某个区域进行更改，并将其写入相同的位置或其他位置。

以下示例代码将使用数组从一个区域复制数据到另一个区域：

Sub example()
Dim testdata()
testdata = Range("A1:B13")
Range("D1:E13") = testdata ' simple copy
Range("G1") = testdata ' copy only 1 cell
Range("I1:K22") = testdata 'try to copy too much
End Sub

testdata数组从1开始，并将扩展到范围中指定的行数和列数。在本例中，testdata（1,1）指的是从A1获取的数据，testdata（1,2）指的是B1，以此类推， testdata（13,1）指的是A13 ，而 testdata（13,2）则指的是B13 。

下一行中将范围设置为数组将复制数组到指定位置。

• 如果区域比原始数组小，则只会复制足以填充该空间的数组部分，因此 Range("D1") = testdata 只会将一个单元格放置在工作表上。
• 如果您指定了更大的区域，则#N/A将填充未由数组元素覆盖的区域，因此 Range("A1:A3") = testdata 将用数组中的数据填充A1和A2，但A3将具有#N/A

示例程序结果：
注：A1:B13是原始数据，随后使用range(??)=testdata进行复制

- SeanC

---

很好的解释，但最后两点不是很清楚。#N/A是什么？ - arun_roy

#N/A 是 Excel 在没有可用信息时产生的错误。请参见 维基百科 上的第一个定义。 - SeanC

我还不是很清楚。任何图形演示都可以让我理解！ - arun_roy

Answer 2

这是一个读取工作表数据范围、对数组进行操作，然后将其写回到同一工作表的示例。

    Sub RangeArray()

    Dim Rng As Range
    Dim Arr()
    Dim ArrItem
    Dim i As Long, j As Long
    Dim rUB as Long, cUB as Long

     Set Rng = Worksheets("Sheet1").Range("A1:G19")
    rUB = Rng.Rows.Count    'Row upper bound
    cUB = Rng.Columns.Count  ' Column upper bound

    ReDim Arr(1 To rUB, 1 To cUB)

   'Read worksheet range into array
    For i = 1 To rUB
       For j = 1 to cUB
          Arr(i, j) = Rng.Cells(i, j).Value
       Next
    Next

   'Do something to array 
    For i = 1 To rUB
       For j = 1 To cUB
          If i <> j Then
             Arr(i, j) = Arr(i, j) / (i * j)
          End If
       Next
    Next

   'Write array back to worksheet
    Set Rng = Worksheets("Sheet1").Range("I1")
    For i = 1 To rUB
       For j = 1 To cUB
          Rng.Offset(i - 1, j - 1).Value = Arr(i, j)
       Next
    Next

    End Sub

- chuff

---

不使用循环是否可能？ - arun_roy

实际上，是的，@Tukai。使用数组是一种老习惯。这个也可以工作： 子范围数组() Dim A() A = Sheet1.Range("A1.G19") '对数组进行操作 Sheet1.Range("i1.i19").Value = A 结束子程序 - chuff

Answer 3

是的，一个Excel范围可以在一次赋值中分配给一个二维数组。在C++/CLI中，它看起来像这样:

cli::array<Object^, 2>^ arrData = safe_cast<cli::array<Object^, 2>^>(rg->Value[Excel::XlRangeValueDataType::xlRangeValueDefault]); 在c#或Visual Basic中，它会显得简单得多（例如在这里 https://www.automateexcel.com/vba/assign-range-to-array/，顺便提一下，在dotnet中，对象现在扮演了变体数据类型的角色）。请注意，它必须是一个二维数组，并且返回的数组具有基于一的索引，而不是基于零的索引。

对于大型数据集，这种方法比循环快得多。循环会生成许多COM对象。我用一个33000行的Excel范围比较了两种方法，将数据导入数组几乎是瞬间完成的，而使用循环则需要很长时间，并且会使CPU发热。

- Jens Kluge

Answer 4

一种遍历范围的方法是使用For...Next循环和Cells属性。使用Cells属性，您可以将循环计数器（或其他变量或表达式）替换为单元格索引号。在以下示例中，变量counter被替换为行索引。该过程遍历范围C1:C20，将绝对值小于0.01的任何数字设置为0（零）。

Sub RoundToZero1()
    For Counter = 1 To 20
        Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
        If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
    Next Counter
End Sub

- Alex Gordon

---

我不是在寻找这个！我正在寻找一个可以用单个语句完成的任务！而且 Range("G2:AA1000") 应该像这样，而不仅仅是单列！ - arun_roy

为什么你使用了那个 0.01 的值，想知道逻辑是什么 - 意图是什么？ - arun_roy