需要通过Excel中的另一个选项卡中的所有行来复制一个选项卡中的所有行 [英] Need to replicate All rows in one tab by All rows in another tab in Excel

问题描述

Hello Excel Vlookup专家，

Hello Excel Vlookup Experts,

我正在尝试获取VLOOKUP公式以执行以下操作.复制样本"选项卡中的记录数，以获取选项"选项卡中的所有项目.我相信这将是在Excel中实现的某种分布式操作，尽管我认为可以使用编码来完成.

I am trying to get a VLOOKUP formula to do the following. Replicate the number of records from the "sample" tab for as many items there are in the "options" tab. I believe this would be some type of distributive action achieved in Excel although I suppose it could be done using coding.

(A)这是初始的样本"数据集.实际样本集中实际上有41565行，但我包括了前10行以作说明.

(A) Here is the initial "sample" data set. There are actually 41565 rows in the actual sample set but I have included the first 10 for illustration.

(B)这是初始的选项"数据集.

(B) Here is the initial "options" data set.

(C)这是我要寻找的内容的代表..完整的41565 x 9 = 374085记录.

(C) Here is a representation of what I'm looking for..for the full 41565 x 9 = 374085 records.

我还担心的是，即使纠正了公式以使其正确分配，此方法也很耗时.我仍然需要手动向下拖动374085行的公式，然后每次将值复制/粘贴到另一个选项卡中.我希望有一个更好的解决方案，可以通过VBScript或通过Excel或其他一些超越Excel的编码(例如使用JSON)的方法来实现.这是另一天的努力.

My concern also is that this approach is time consuming even if the formula is corrected to make it distribute correctly. I still need to manually drag down down the formulas manually for 374085 rows and then copy/paste values into another tab each time. I'm hoping there is a better solution either via VBScript or some other method either via Excel or perhaps coding outside of excel such as using JSON. That's an effort for another day.

过去，使用Excel可以成功地复制较小的数据集，其中两个选项卡中的记录数相同.到目前为止，在复制一组样本记录(即10条记录)时，已使用下面的公式，该公式可重复9次，重复90次重复给定的电子邮件地址，邮政编码，城市和州.

In the past with Excel I have had some success in replicating smaller datasets where the number of records of in both tabs were the same. So far when it comes to replicating a set of sample records (i.e. 10 records) have used this formula below which allows for repeating 9 times for 90 repetitions of a given Email Address, Zipcode, City, State.

电子邮件地址(在公式"选项卡的B2单元格处开始)

Email Address (begin at Cell B2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$B$2:$B$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))

ZipCode(在公式"标签上的C2单元格开始)

ZipCode (begin at Cell C2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$C$2:$C$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))

城市(在公式"选项卡的D2单元格处开始)

City (begin at Cell D2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$D$2:$D$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))

状态(在公式"标签上的单元格E2处开始)

State (begin at Cell E2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(sample!$E$2:$E$11,CEILING((ROW()-1)/(COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11)),1)))

对于服务类型"列，发生了另一次重复. 10x9x10

For the 'Service Type' column, a different repetition is occurring. 10x9x10

在复制一组样本记录(即10条记录)时，我使用了下面的公式，该公式允许重复范围，说明最多900行(样本中的10行x选项中的9行x需要的10倍) (选项卡中的项可以多次放入示例行).但是，按原样"将显示#REF！超过900，因为它尚无必要进行调整，以说对下一个900重复该过程，对下一个900重复此过程，并对存在的行重复一次.

When it comes to replicating a set of sample records (i.e. 10 records) I have used this formula below which allows for repeating the range accounting for up to 900 rows (10 rows in sample x 9 rows in options x 10 times need for items in options tab to multiple into sample rows). However, "as-is" it will display #REF! beyond 900 as it does not yet have the needed adjustment to say repeat the process it did for the first 900 again for the next 900 and again for as many rows that exists.

(在公式"标签上的单元格F2处开始)

(begin at Cell F2 on Formulas tab)

=IF(ROWS(options!$A$1:$A$2)>COUNTA(options!$A$1:$A$9)*COUNTA(sample!A$2:$A$11),"",INDEX(options!$A$1:$A$9,CEILING((ROW()-1)/COUNTA(sample!A$2:$A$11),1)))

任何人都可以对公式进行最少的修复，这样它就会起作用，或者更好的是一种通用的，简化的方法来解决该数据集的工作，这将更灵活地处理包含两个数据集的小型和大型数据集被连接的行没有相同数量的元素吗?

Can anyone provide at minimal a fix to the formula so it will work or better a more universal and streamlined approach to solving that work for data sets that will is more flexible in handling both small and large data sets where the 2 data set rows being joined don't have the same number of elements?

推荐答案

如果我正确理解了您的问题，强烈建议您使用VBA:

If I understood your problem correctly, I strongly suggest VBA:

Option Explicit

Sub CopyRecords()
    Dim wb As Excel.Workbook
    Dim wsSource As Excel.Worksheet
    Dim wsDest As Excel.Worksheet
    Dim wsExp As Excel.Worksheet
    Dim lastcell As Object
    Dim lLastRow As Long
    Dim lLastOpt As Long
    Dim i As Long
    Dim j As Long

' Set Workbook
    Set wb = ActiveWorkbook

' Set Worksheets
    Set wsSource = wb.Sheets("sample")
    Set wsDest = wb.Sheets("output")
    Set wsExp = wb.Sheets("options")

' Determine # of rows
    Set lastcell = wsSource.Cells(1, 1).SpecialCells(xlLastCell)
    lLastRow = lastcell.Row

' Copy Header
    wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(1, 5)).Copy wsDest.Range(wsDest.Cells(1, 1), wsDest.Cells(1, 5))
    wsDest.Cells(1, 6) = wsExp.Cells(1, 1)

' Last row in Options
    lLastOpt = 10

' Loop through the rows
    For i = 0 To lLastRow - 2

' copy the record lLastOpt times (or whatever is required) and add the option. To make it more flexible, use another "lastcell" variable
        For j = 2 To lLastOpt
            wsSource.Range(wsSource.Cells(i + 2, 1), wsSource.Cells(i + 2, 5)).Copy wsDest.Range(wsDest.Cells(i * (lLastOpt - 1) + j, 1), wsDest.Cells(i * (lLastOpt - 1) + j, 5))   ' adjust column as necessary
            wsDest.Cells(i * (lLastOpt - 1) + j, 6) = wsExp.Cells(j, 1)
        Next j
    Next i
End Sub

其他任何事情都变得非常复杂.

Anything else gets awfully complicated.

这篇关于需要通过Excel中的另一个选项卡中的所有行来复制一个选项卡中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！