Excel автоматически добавляет дополнительную строку с формулами для указанного количества лет

Question

Я ищу, чтобы построить модель. Я хотел бы иметь страницу ввода, где вы вводите диапазон лет, которые вы хотели бы охватить. В зависимости от введенного количества лет, я хочу, чтобы таблица (на другом листе) была заполнена существующими формулами.

Чтобы быть более конкретным, у меня есть таблица, полная формул, в которой есть строки для каждого года, начиная с 2005-2014.
Моя просьба выглядит следующим образом:

Есть ли способ сделать страницу ввода, которая занимает годы, изменить таблицу и добавить / вычесть строки на основе введенного диапазона? Например, мы изменили диапазон на входной странице на 2005-2018, тогда моя таблица добавит еще 4 строки с уравнениями.

Answer 1

Решение
Чтобы автоматизировать это, используйте некоторый код VBA, который изменяет размер вашей таблицы после обновления начального и конечного лет.

Код
Вот моя реализация этого, с годами в "Sheet1" (ячейки B1 и B2) и таблицей в "Sheet2". Обратите внимание, что вы должны добавить этот код на лист с указанием лет, потому что код должен запускаться всякий раз, когда вы что-то меняете на этом листе. Так что в моем случае этот код добавляется в "Sheet1".

Private Sub Worksheet_change(ByVal Target As Range)
    Dim StartYear, EndYour As Range
    Dim UpdateTable As ListObject
    Dim NrOfRows, OldNrOfRows As Integer

    ' Set some ranges
    Set StartYear = Worksheets("Sheet1").Cells(1, 2)
    Set EndYear = Worksheets("Sheet1").Cells(2, 2)
    Set UpdateTable = Worksheets("Sheet2").ListObjects("Table1")

    ' Check if start or end years have changed
    If (Not Intersect(StartYear, Target) Is Nothing) Or (Not Intersect(EndYear, Target) Is Nothing) Then
        ' Store the new and old number of rows
        OldNrOfRows = UpdateTable.ListRows.Count - 1
        NrOfRows = EndYear.Value - StartYear.Value + 1

        ' Resize the table
        UpdateTable.Resize UpdateTable.Range.Resize(1 + NrOfRows)

        'Delete cells below the table if it gets smaller
        If OldNrOfRows > NrOfRows Then
            UpdateTable.Range.Offset(NrOfRows + 1, 0).Resize(OldNrOfRows - NrOfRows + 1).Delete
        End If
    End If
End Sub

Некоторые моменты объяснения кода
Некоторые ссылки на функции / методы / свойства Microsoft для объяснения кода:

• Excel может вызывать подпрограмму всякий раз, когда значение в ячейке изменяется. Эта подпрограмма называется Worksheet_change а изменяемая ячейка задается как значение диапазона.
• Я использую функцию пересечения, чтобы проверить, изменились ли ячейки, которые содержат год начала / окончания. Конструкция NOT-NOTHING может усложнить чтение, но эффект состоит в том, что IF вводится, если NOT-NOTHING изменяется.
• Чтобы изменить размер таблицы, я использую метод Resize объекта ListObject (для которого требуется объект диапазона) и метод Resize объекта диапазона (для которого требуется один или два типа int).
• Наконец, если таблица становится меньше, мне пришлось вручную удалить содержимое под новой таблицей. Здесь снова я использую некоторые вложенные функции смещения диапазона и изменения размера диапазона, чтобы удалить правильный диапазон.