Есть ли способ в Excel, чтобы формула растягивала свой экстент строки, чтобы включить смежные вставленные строки?

Question

В Microsoft Excel 2011 для Mac OS X у меня есть несколько столбцов числовых чисел и последняя строка внизу, которая показывает максимальное значение для каждого столбца. Мой набор данных упорядочен так, что самые последние значения добавляются внизу. Было бы хорошо, если бы я мог просто вставить строку внизу, между последней строкой данных и строкой формулы, и автоматически обновить строку формулы, чтобы включить вставленную строку, однако вставленная строка всегда опускается при вставке в дно.

Самое близкое, к чему я смог приблизиться, - это оставить пустую строку между моими данными и строкой сводки формул внизу, но формулы содержат пустую строку в своем экстенте. В формулах игнорируются пустые значения в их расчете max() , затем, когда я хочу добавить еще одну строку к данным, я выбираю пустую строку и вставляю новую пустую строку над ней, куда я помещаю новую строку данных ,

Это довольно хорошая работа, но мне все еще интересно, есть ли более простой способ сказать Excel всегда расширять формулу, чтобы включить вставленные строки, которые примыкают к значениям формулы?

Answer 1

Работа с Excel 2010 на ПК, но должна быть похожа

Пара способов.

1. Во-первых, использовать таблицу, выделить данные, перейти на вставку> таблица, следует отформатировать ее чередующимися строками. Затем вы можете использовать формулы, такие как = sum(Table1 [myCol]) для суммирования всего столбца

2. Динамические формулы. Последняя строка может быть возвращена с использованием формулы, подобной = match(1e300, A:A, 1), в предположении, что она числовая, и присвоить ей именованный диапазон (lRow). Используйте формулу как = сумма (A1: косвенный ("A" & lRow))

3. Или косвенный может быть правильным в именованном диапазоне, но тогда он будет работать только для 1 столбца на именованный диапазон. = косвенный ("A" & match(1e300, A:A, 1)), назначенный для именованного диапазона (lCellA), и вы можете использовать формулу = sum(A1: lCellA)

Были бы другие, но один из них должен работать. Если вам нужно дальнейшее объяснение именованных диапазонов, вы можете сообщить мне.

Answer 2

Включает ли Mac Excel 2011 возможность форматирования областей в виде таблиц Excel?

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

Например, у меня есть лист, который извлекает пользовательские данные из SharePoint. В этой таблице есть столбец с именем "UPN", поэтому я могу подсчитать строки в таблице по формуле:

=SUBTOTAL(103, Table6[UPN])

(предполагается, что таблица называется "Table6")

Вы можете сделать другие расчеты, такие как:

=MIN(Table6[FirstAccessTime])

И подсчет суммы с:

=SUBTOTAL(109,Table6[Count])