Формула, которая перемещается вправо при перетаскивании вниз

Question

У меня есть формула суммы:

=SUM(Monday!B5+Tuesday!B5+Wednesday!B5+Thursday!B5+Friday!B5+Saturday!B5+Sunday!B5)

Можно ли сделать так, чтобы при копировании этой формулы формула стала:

=SUM(Monday!C5+Tuesday!C5+Wednesday!C5+Thursday!C5+Friday!C5+Saturday!C5+Sunday!C5)

и так далее?

Answer 1

Вы спрашивали, как скопировать формулу A1 вниз, но заставить ее измениться на B1 вместо стандартного ожидаемого A2. Каким-то образом нам нужно попросить Excel двигаться вниз, но сосчитать вбок; т.е. перейти от ряда 1 к ряду 2; но считать столбцы от А до Б.

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

Несколько способов сделать это. Можно было бы использовать коды символов, то есть преобразовывать числа в алфавиты. =code("A") - 65 и так далее.

=CHAR(65+ROW()-1)&1 будет переводиться как:

=CHAR(65+ROW()-1)&1
=CHAR(65+1-1)&1
=CHAR(66-1)&1
=CHAR(65)&1
=CHAR(65)&1
="A"&1
=A1

Предполагая, что вы находитесь в A1, B5 также можно записать как = CHAR(65+ROW()+1-1)& 5

Обратите внимание, что я предполагаю, что вы начинаете со столбца A; -1 обозначает столбец A; поэтому, если вы начинаете с B, используйте -2. 65 является кодом А.

Используйте эту формулу в своей первой / начальной ячейке, заменив каждый B5 на =CHAR(65+COLUMN()+1-1)&5 .

Бит ROW() будет увеличиваться при движении вниз; но бит CHAR переведет его в идентификаторы столбцов, т. е. A,B,C... Кроме того, фиксированные части, т.е. строки в формуле, должны быть заключены в кавычки.

=SUM(
  Monday!B5+
  Tuesday!B5+
  Wednesday!B5+
  Thursday!B5+
  Friday!B5+
  Saturday!B5+
  Sunday!B5
 )

становится (B заменяется на &CHAR(65+ROW())&)

=SUM(
  "Monday!"&CHAR(65+ROW())&5+
  "Tuesday!"&CHAR(65+ROW())&5+
  "Wednesday!"&CHAR(65+ROW())&5+
  "Thursday!"&CHAR(65+ROW())&5+
  "Friday!"&CHAR(65+ROW())&5+
  "Saturday!"&CHAR(65+ROW())&5+
  "Sunday!"&CHAR(65+ROW())&5
 )

Но это просто выдаст строку с ошибкой #value, потому что она попытается добавить слово Monday & B5. Нам нужно заключить каждую строку в INDIRECT().

Окончательная формула

=SUM(
  INDIRECT("Monday!"&CHAR(65+ROW())&5)+
  INDIRECT("Tuesday!"&CHAR(65+ROW())&5)+
  INDIRECT("Wednesday!"&CHAR(65+ROW())&5)+
  INDIRECT("Thursday!"&CHAR(65+ROW())&5)+
  INDIRECT("Friday!"&CHAR(65+ROW())&5)+
  INDIRECT("Saturday!"&CHAR(65+ROW())&5)+
  INDIRECT("Sunday!"&CHAR(65+ROW())&5)
 )

Дайте мне знать о любых вопросах.

Обратите внимание, что это может быть не самый быстрый / самый короткий метод. В Excel, как и в любом другом программном обеспечении, есть несколько способов сделать одно и то же.

Answer 2

Я хотел бы предложить вам, что Формула поможет вам СУММИТЬ следующую колонку вместо строки, пока вы перетаскиваете ее вниз.

Проверьте снимок экрана ниже.

Теперь позвольте мне сначала объяснить сценарий.

Диапазон данных, который я использовал, - M1:N8, а Sheets - 8 & 9.

Примечание: формулы написаны на листе 8.

Ячейка М9 (красный цвет) имеет формулу,

= SUM(Sheet8!M1+Sheet9!М1).

Ячейка M11 (синий цвет) имеет значение 48, а формула

=SUM(SUBTOTAL(9,OFFSET($M$1,{0,1,2,3,4,5,6,7},ROWS(M$11:M11)-1,{1},1)))

NB. Эта формула добавляет данные из M1:M8, если вы хотите суммировать диапазон ячеек.

Когда вы перетащите эту формулу вниз, получите сумму в столбце N.

Теперь, наконец, ячейка M14 (зеленый цвет) имеет значение 40 и формула

=SUM(SUBTOTAL(9,OFFSET(Sheet8!$M$1,{0},ROWS(M$14:M14)-1,{1},1))+SUM(SUBTOTAL(9,OFFSET(Sheet9!$M$1,{0},ROWS(M$14:M14)-1,{1},1))))

Примечание: эта формула получает сумму M1 как из Sheet8, так и из Sheet9. Когда вы перетащите его вниз, он выберет значения ячейки N1 из обоих листов.

Надеюсь, это поможет вам. Я разместил решение после того, как оно было проверено мной.