Горизонтальная ссылка на автозаполнение вертикальной формулы - 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, убедитесь, что вставили -> special -> transpose. Это разместит горизонтальную ячейку вертикально. Затем вы можете удалить горизонтальные ячейки, которые вы создали с помощью формулы. Причина, по которой я говорю, делайте это в ячейках от B до BB, если вы сделали это в ячейке A, а затем попытались вставить поверх нее, это не позволило бы вам и вернулось бы с ошибкой. Это только одно из многих решений.