Как перетащить несколько экземпляров диапазона одновременно в Excel

Question

Изменить: этот вопрос полностью пересмотрен, чтобы включить скриншоты, как это было предложено комментаторов.

Я отслеживаю оценки учеников в Excel, используя электронную таблицу примерно так

Я часто пишу формулы Excel, которые используют один и тот же диапазон снова и снова:

=SUM(C5:E5)/(COUNTA(C5:E5)+COUNTBLANK(C5:E5))

А затем я хочу добавить еще несколько столбцов в диапазон, то есть изменить его на:

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

=SUM(C5:F5)/(COUNTA(C5:F5)+COUNTBLANK(C5:F5))

Но есть ли способ перетащить их всех одновременно? (Из ответов, видимо, нет.) Это становится более важным, поскольку формулы становятся более сложными, например:

=(SUM(AF5:AO5)-IF(COUNTBLANK(AF5:AO5)>0,0,MIN(AF5:AO5)))/(COUNTA(AF5:AO5)+COUNTBLANK(AF5:AO5)-1)0

Это формула для отбрасывания низшей оценки. Я копирую и вставляю эту формулу один раз для каждой строки (для каждого ученика в классе). Затем, если я хочу добавить назначение, мне нужно отредактировать каждую отдельную копию диапазона AF5:AO5 на AF5:AN5.

Есть ли способ переписать эту формулу, чтобы использовать указанный диапазон только один раз? Я предпочитаю использовать одну ячейку без VBA для простоты.

Исследование: я не могу найти что-либо отдаленно связанное с Google. Самое близкое, что я мог найти, это обсуждение того, как редактировать выборки, а не формулы.

@Michthan упоминает именованные диапазоны со смещением, но это не позволяет использовать одно и то же имя для нескольких студенческих записей.

Answer 1

Начиная с комментария @ Kyle на мой вопрос, вот как создать относительный именованный диапазон:

Начиная с оригинального листа:

С этой формулой для ячейки F5:

=SUM(C5:E5)/(COUNTA(C5:E5)+COUNTBLANK(C5:E5))

Выберите ячейку с первой формулой, которую вы хотите редактировать на листе (в примере F5)

Затем нажмите «Лента формул» и кнопку «Диспетчер имен».

Нажмите New и введите имя для диапазона (например, тесты). Удалите все в поле «Относится к» и выберите данные из первой строки:

Теперь вот ключевой шаг. Чтобы сделать это относительной ссылкой, удалите $ перед той частью ссылки, которая должна измениться. Поскольку числовая часть изменяется по мере перехода от строки к строке, мы будем удалять каждый $ перед 5:

Нажмите OK и Закрыть, чтобы вернуться к рабочему листу. Теперь в первой формуле везде используйте именованный диапазон:

=SUM(quizzes)/(COUNTA(quizzes)+COUNTBLANK(quizzes))

Теперь у вас есть формула с использованием диапазона имен, который можно скопировать! Скопируйте ячейку F5 в ячейки F6:F9.

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

Еще раз спасибо @Kyle за предложение этого решения.