重复的单元格跳过10行 [英] Duplicated cells skip 10 rows

问题描述

我有一个包含两页的Excel电子表格，第1页包含一些文本和公式，我希望复制这些文本和公式以为我的项目编写2,000行奇数的C#代码.极其重复，所以我认为我可以使用Excel为我编写它. Sheet 2 包含我数据库的摘录，我希望使用该摘录来填充这些值.我的Excel代码部分如下所示，分布在10行和5列中:

I have an Excel spreadsheet with two sheets, Sheet 1 contains some text and formulas I wish to duplicate down to write 2,000 odd lines of C# code for my project. Extremely repetitive, so I thought I could use Excel to write it for me. Sheet 2 contains an extract from my database which I wish to use to populate that values with. My section of Excel code looks like this and is spread out over 10 rows and 5 columns:

new AccountingPeriod()              
    {           
        MonthCovered="=Sheet2!B2",
        StartDate=DateTime.Parse("Sheet2!C2"),
        EndDate=DateTime.Parse("Sheet2!D2"),
        AccountingPeriodDescription="Sheet2!E2",
        Active='=Sheet2!F2',
        April='=Sheet2!G2,
        TaxYear="Sheet2!H2"
    },      

当我突出显示这10行并使用Excel复制工具时，我想要单元格引用(例如，在我的电子表格 Sheet1！D3 = Sheet2！B2 中，我需要 Sheet1！D13= Sheet2！B3 NOT Sheet2！B13 )增加1，而不是像我的电子表格中那样增加10.

When I highlight these 10 rows and use the Excel duplicate tool I want the cell references (for example, in my spreadsheet Sheet1!D3 = Sheet2!B2, I want Sheet1!D13 = Sheet2!B3 NOT Sheet2!B13) to increment by 1, not 10 as it's doing in my spreadsheet.

我们尝试了一种替代方法，即编写一个宏，以便为每个填充的行在 Sheet2 中插入10个空白行，以使重复的引用在 Sheet1 中是正确的，但我们目前在使它无法正常工作方面惨遭失败.

We have tried an alternative solution of writing a macro to insert 10 blank rows in Sheet2 for every populated row so that the duplicated references are correct in Sheet1 but we're currently failing miserably at getting that to work correctly.

推荐答案

请使用

Instead of a direct reference that is going to change on a 1-to-1 with the copy, use the INDEX function with a little maths to achieve the 10-to-1 row stagger.

在Sheet1！D3中，它引用了Sheet2！B2.

In Sheet1!D3 this references Sheet2!B2.

=INDEX(Sheet2!B:B,INT(ROW(1:1)/10)+2)

在Sheet1！D13中，它引用了Sheet2！B3.

In Sheet1!D13 this references Sheet2!B3.

补偿功能可以完成相同的操作，但是它是一个易失性函数，只要工作簿中的任何内容发生更改，它都会重新计算.INDEX提供相同的功能，同时保持非易失性，并且仅在影响返回值的值发生变化时才会重新计算.

The OFFSET function can accomplish the same thing but it is a volatile function that recalculates whenever anything in the workbook changes. INDEX provides the same functionality while remaining non-volatile and will only recalculate when something that affects its returned value changes.

这篇关于重复的单元格跳过10行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！