Excel: установить формулу по умолчанию для каждой ячейки в столбце

Question

У меня есть столбец со следующей формулой в каждой ячейке, который вычисляет разницу в километрах между двумя ячейками слева от каждой ячейки:

=IF(AND(INDIRECT(ADDRESS(ROW(),COLUMN()-2))<>"", INDIRECT(ADDRESS(ROW(), COLUMN()-1))<>""), INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "")

Моя проблема в том, что каждый раз, когда я вставляю новую строку между существующими данными, все ячейки в этой строке пусты, и мне нужно вручную поместить этот код в пустую ячейку.

Могу ли я установить для каждой ячейки в столбце (исключая заголовок) определенную формулу, даже когда я вставляю новую строку?

Я попытался установить столбец в виде таблицы, и он успешно завершал автозаполнение ячеек всякий раз, когда я вставлял новую строку, однако у меня есть другие столбцы, которые я также хочу автозаполнять, и Excel не позволяет мне вставлять новую строку, если Я делаю более одного столбца в таблице.

Answer 1

Я собирался просто опубликовать это видео, но, очевидно, мне нужно набрать не менее 30 символов.

Как насчет «Вот простой способ сделать это». Черт, это только 29.

Для всех, кто интересуется, значок "Вставить картинку" не будет принимать .mov, но есть довольно удобный сайт , который преобразует видеофайлы в анимированные GIF-файлы.

Answer 2

Вот небольшая демонстрация, которую вы можете адаптировать к вашей реальной схеме. В демоверсии:

1. Интересующий столбец - столбец B
2. если вставлено более одной строки, ничего не произойдет
3. если вставлена строка ниже области данных, ничего не происходит
4. если строка удалена, ничего не происходит
5. если вставлена строка в середине данных, ячейка в столбце B будет заполнена либо формулой выше, либо формулой ниже
6. если нет формулы выше или ниже новой ячейки, ничего не происходит

Вставьте следующую подпункт в область кода рабочего листа:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, r1 As Range, r2 As Range

    Set r = Target
    If r.Rows.Count > 1 Then Exit Sub
    If r.Columns.Count <> Cells.Columns.Count Then Exit Sub
    If r.Row = 1 Then Exit Sub

    Set r1 = Intersect(r, Columns(2))
    If r1.Value <> "" Then Exit Sub

    If r1.Offset(-1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(-1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

    If r1.Offset(1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

End Sub