在 Excel 中复制公式模式

Question

我有一个如下所示的电子表格：。

我想使用第一个表中的值填充第二个表。显而易见（且费力）的方法就是手动执行此操作，例如 B7 中有公式=B2，B8 中有公式，=D2依此类推。

有没有办法让 Excel 识别这种模式，即第二个表中的一列等于第一个表中一行中的第二个值？当我尝试拖动单元格的右下角时，会出现错误的模式。

Answer 1

不，没有确切的方法，你有两种解决方法：

1. 使用转置：

   • 删除空列
   • 选择您的数据并按 CTRL+C
   • 转到主页-粘贴-转置

2. 使用公式

   • 填充标题
   • 在 B7 中输入=INDEX($B$2:$H$4,MATCH(B$6,$A$2:$A$4,0),MATCH($A7,$B$1:$H$1,0))
   • 向右下方填充公式

Answer 2

我没时间了，所以我不能保证它是有效的，甚至编码得很好，但这个 VBa 可以工作。（编辑，也没有意识到你有一个可以接受的答案，但无论如何都会保留这个）

运行 VBa 时没有撤消选项，因此请先备份。

Option Explicit

Sub doTheThing()

Dim userStartRowInColA As Integer
userStartRowInColA = 2                   'update this as needed, in your example I assume the rows start on row 2

Dim userColDifference As Integer
userColDifference = 2                   'in your example, the top table is every 2 rows, hence the 2



Dim startRowInColA As Integer
startRowInColA = userStartRowInColA

Dim vals As String
vals = ""

Dim items As String
items = ""

Dim valsMissedTwo As Boolean
valsMissedTwo = False

Dim startCol As Integer
startCol = 65

Do While (True)

Dim col As String
col = Chr(startCol)

If Range(col & 1).Value = "" And valsMissedTwo Then
    Exit Do
Else
    valsMissedTwo = False
End If

If Range(col & 1).Value = "" And Not valsMissedTwo Then
    valsMissedTwo = True
End If

If Range(col & 1).Value <> "" Then
    vals = vals + Range(col & 1).Value + ","
End If


startCol = startCol + 1
Loop



Do While Range("A" & startRowInColA).Value <> ""

items = items + Range("A" & startRowInColA).Value + ","

startRowInColA = startRowInColA + 1
Loop


Dim table2StartCol As Integer
Dim table2StartRow As Integer
table2StartRow = startRowInColA + 1
table2StartCol = 66


Dim splitVals() As String
splitVals = Split(vals, ",")

Dim splitItems() As String
splitItems = Split(items, ",")

'add the items as cols
For startCol = 1 To UBound(splitItems)
    If splitItems(startCol - 1) <> "" Then
        Range(Chr(65 + startCol) & startRowInColA + 5).Value = splitItems(startCol - 1)
    End If
Next startCol


'add the vals on left as rows

For startCol = 1 To UBound(splitVals)
    If splitVals(startCol - 1) <> "" Then
        Range("A" & startCol + startRowInColA + 5).Value = splitVals(startCol - 1)
    End If
Next startCol

'now to populate

Dim sr As Integer
sr = startRowInColA + 6

Dim sc As Integer
sc = 66

Dim oSr As Integer
oSr = userStartRowInColA


Dim i As Integer
i = 0

Dim j As Integer
j = 0



Do While (True)
Do While Range(Chr(sc) & oSr).Value <> ""

    Range(Chr(sc + i) & sr).Value = Range(Chr(sc + j) & oSr).Value

i = i + 1
oSr = oSr + 1

Loop

j = j + userColDifference
i = 0
oSr = userStartRowInColA
sr = sr + 1
If Range("A" & sr).Value = "" Then
    Exit Do
End If

Loop


End Sub

前

后

如您所见，您不需要创建第二张表，它也会自动完成

如何在 MS Office 中添加 VBA？