Как заставить многие формулы столбцов игнорировать одни и те же строки в Excel?

Question

У меня есть несколько столбцов, и в конце каждого из них у меня есть строки SUM и MEAN. Теперь я хочу игнорировать некоторые строки, делая таким образом =SUM(A1:A15) в =SUM(A1:A13;A15) если я хочу игнорировать строку 14. Делать это для каждой формулы - утомительная задача.

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

Проблема в том, что у меня есть несколько столбцов, при таком подходе мне придется обновлять ячейку B1:B15 для каждого столбца. Как упростить этот процесс обновления?

Answer 1

Вы можете использовать функцию OFFSET чтобы сместить весь диапазон на определенную величину. Например, если в ячейке ="A1:A15" был текст C1 , вы можете получить диапазон B1:B15 , используя следующую формулу:

=OFFSET(INDIRECT(C1), 0, 1)

Для справки, функция определена в Excel как =OFFSET(reference, rows, columns, [height], [width]) . Чтобы переобучить ширину / высоту исходного диапазона, не указывайте аргументы height или width . Также обратите внимание, что аргументы rows и columns могут быть положительными или отрицательными (так что вы можете смещать как вверх / вниз, так и влево / вправо).

---

Еще одно замечание: функция OFFSET работает со всеми диапазонами, поэтому, если в предыдущем примере вы ввели текст ="A1:A12,A15" возвращаемый диапазон после использования OFFSET для смещения вправо, один столбец будет B2:B12,B15 как вы ожидаете.