Горизонтальная ссылка на вертикальную формулу автозаполнения - Microsoft Excel 2007

Question

Я пытаюсь создать график регистрации сотрудников за год с одной вкладкой просмотра и 12 справочными вкладками для ввода данных. В пределах заданного месяца даты располагаются горизонтально, D4:AG4, а регистрация сотрудников представлена ​​цифрой «1», отмеченной вертикально в каждом столбце дня. Затем я создал формулу на вкладке просмотра, , Sheet1для каждого дня этого месяца, чтобы получить сумму всех зарегистрированных сотрудников в этот день, например:

A1=Sum(Sheet2!D4:D53)
A2=Sum(Sheet2!E4:E53)

Проблема в том, что каждая запись для дня, и я пытаюсь не вводить эти формулы 365 раз за весь год. Есть ли функция, которую я могу использовать для автозаполнения вниз, не изменяя при этом ячейки ссылок по вертикали? Вот что делает автозаполнение прямо сейчас:

A3=Sum(Sheet2!F5:F54)
A4=Sum(Sheet2!F6:F55)

Answer 1

Тип

=SUM(OFFSET(Sheet2!$D$4,,ROW()-1,50))

и Sheet1!A1перетащите/скопируйте его вниз в A30.

Если все ваши ежемесячные данные организованы в последовательно пронумерованные листы Sheet2... Sheet13, то вы можете использовать эту формулу

=SUM(OFFSET(INDIRECT(ADDRESS(4,4,,,"Sheet"&(COLUMN()+1))),,ROW()-1,50))

и A1скопируйте его, чтобы заполнить весь диапазон A1:L30и обработать все месяцы.

Вот пример со случайными данными:

Answer 2

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

В вашем случае, простой способ сделать это — сначала автоматически заполнить формулу по горизонтали на 53 пробела (ячейки B-BB, позже я объясню, почему). Затем скопируйте эти 53 ячейки, затем, когда вы собираетесь вставить в A1, убедитесь, что вставили -> специальная -> транспонировать. Это разместит горизонтальную ячейку вертикально. Затем вы можете продолжить и удалить горизонтальные ячейки, которые вы создали с помощью формулы. Причина, по которой я говорю сделать это в ячейке B-BB, заключается в том, что если вы сделали это в ячейке A, а затем попытались вставить поверх нее, это не позволит вам и вернет ошибку. Это только одно из многих решений.