[Правка: я добавил изображения для n1 и n2 с 1 по 10 и упростил их. Спасибо @ Máté Juhász за отзыв.]
Есть несколько подходов к этому.
Кажется, что каждый раз, когда я отвечаю, я вначале говорю "Используйте таблицы". Этот совет очень хорош и здесь, потому что вы хотите, чтобы каждая формула была одинаковой для всего столбца, и он дает преимущество хранения формулы каждого столбца один раз, а не один раз на ячейку.
Так,
Составьте таблицу для вашей формулы
- Измените А1 на "n1"
- Измените B1 на "n2 = 1"
- Заменить С1 на "n2 = 2"
- Выделите B1 ..С1
- Перетащите точку в правом нижнем углу выбранного диапазона на ALM1, чтобы получить «n2 = 1000»
- На ленте "Вставить" вставьте таблицу. В диалоговом окне "Создать таблицу" измените диапазон данных на
=$A$1:$ALM$2 и убедитесь, что установлен флажок "Моя таблица имеет заголовки".
- В A2 добавьте формулу
ROW()-1
- Скопируйте этот лист на новый лист в вашей рабочей тетради на потом.
- Хорошо, теперь приходит твоя математика. Добавьте формулу в b2, но вместо n1 используйте
(ROW()-1) , а вместо n2 используйте (COLUMN()-1) . Я просто сделал таблицу умножения, чтобы моя формула (n1 * n2) была =(ROW()-1)*(COLUMN()-1) .
- Нажмите на ленту "Дизайн таблицы", которая будет доступна только в том случае, если курсор находится внутри таблицы, и выберите "Общая строка" и "Первый столбец" на ленте. ("Первая колонна" предназначена только для косметических целей)
- Измените B3 на
=SUM([n2=1])+IF(COLUMN()=2,0,A3)
- Отредактируйте B3, выделите упоминание "A3", затем нажмите на ячейку A3. Это должно изменить формулу B3 на что-то вроде
=SUM([n2=1])+IF(COLUMN()=2,0,Table1[[#Totals],[n1]])
- Выделите B2 ..B3 и перетащите или скопируйте формулы до конца строки в столбец ALM. Теперь у вас должна быть таблица с итоговой строкой, которая дает сумму n1 = 1 и n2 = 1 к 1000
- Чтобы изменить n1 на 1000, в левом конце ленты "Table Design" находится кнопка "Resize table". Щелкните по нему и измените размер таблицы на
=$A$1:$ALM$1002
Суммы
Если это все, что вам нужно, вы можете остановиться сейчас. Но если вам нужна сумма для любого заданного значения от n1 и n2 до 1000, то мы могли бы пойти одним из двух способов:
Вариант суммирования 1: легко, но медленно
В этом случае у нас есть две вкладки. Приведенный выше будет содержать таблицу, содержащую «не суммированные» значения формулы. И затем тот, который мы создадим, когда мы пытаемся суммировать значения n1 =(от 1 до 4) и n2(от 1 до 4), мы получим все значения, выполнив следующее:
Table1[[#Headers],[n1]] дает нам верхнюю левую ячейку этой первой таблицы.
OFFSET(Table1[[#Headers],[n1]],1,1) дает ячейке один вниз и один вправо, то есть первую ячейку данных, где n1 = 1 и n2 = 1.
Смещение имеет необязательные 4-й и 5-й аргументы, которые определяют высоту и ширину диапазона возвращаемых ячеек. Используя эти необязательные аргументы, =OFFSET(Table1[[#Headers],[n1]],1,1, n1 , n2 ) дает нам все значения, которые мы хотим суммировать!
Итак, чтобы использовать это,
- перейдите на тот новый лист, который мы создали на шаге 9.
- В B2 этого второго листа измените формулу на
=SUM(OFFSET(Table1[[#Headers],[n1]],1,1,row()-1,column()-1)) Таким образом, когда n1 равно 5, а n2 равно 3, вы получите сумму прямоугольника, начиная с ячейки B2 в Table1, высотой 5 строк и шириной 3 столбца.
- Скопируйте ячейку B2 в буфер обмена.
- Выделите C2 ..ALM2
- Вставить
- Иди принеси кофе. Если вы действительно хотите перейти к n1 = 1000 и n2 = 1000, предыдущий метод Sum займет очень много времени! Эта таблица ячеек, использующая этот метод SUM, будет иметь 1000 * 1000 ячеек, и каждая из них будет смотреть на Sheet1!B2, 999 ^ 2 из этих ячеек также будут включать Sheet1!C3 и т.д., Так что формула SUM(), где n1 = 1000 и n2 = 1000, будет рассматривать миллион ячеек, даже если все, кроме одной, уже были суммированы ранее!
(Обратите внимание, что я изменил стиль таблицы для второго листа на "Table Style Medium 4")
Вариант суммирования 2: сумма по строкам, затем сумма по столбцам
Вместо этого мы можем сделать что-то гораздо более эффективное, если в Excel будет работать сумма. На следующих скриншотах показан этот оптимизированный метод. Обратите внимание, что я переименовал таблицы (и вкладки, но это не имеет значения), поэтому в следующих формулах используются "Формула" и "N1Sums", а не "Table1" и "Table2".
Вместо этой таблицы сумм мы можем использовать две таблицы.
Формула столбца B второй таблицы имеет вид =Formula[@[n2=1]]+IF(COLUMN()=2,0,[@n1]) , а затем вам придется перетаскивать ее через другие столбцы, чтобы Формула столбца C имеет вид =Formula[@[n2=2]]+IF(COLUMN()=2,0,[@[n2=1]]) . Это будет иметь промежуточные суммы для каждого ряда. По сути, эта формула гласит: «добавьте мою соответствующую ячейку в первой таблице плюс ячейку слева от меня».
Третья таблица похожа, за исключением того, что ее формула гласит: «добавьте мою соответствующую ячейку во вторую таблицу плюс ячейку над мной ». Формула столбца B имеет вид =N1Sums[@[n2=1]]+IF(ROW()=2,0,OFFSET([@[n2=1]],-1,0)) , и тогда вам придется перетащите его в другие столбцы, чтобы формула столбца C была равна =N1Sums[@[n2=2]]+IF(ROW()=2,0,OFFSET([@[n2=2]],-1,0))
На следующих скриншотах показан этот оптимизированный метод.
, 
