Я новичок в этой разновидности StackExchange, так что если это относится к другому разделу, пожалуйста, перенесите его; хотя я посчитал, что это будет лучшим местом.
Я создаю рабочий лист Excel, который просто хранит основные финансовые данные в 5 столбцах ( Check Number, Date of Transaction, Description, Profit from Transaction, и Balance After Transaction) и неопределенных строках. Каждый рабочий лист представляет один месяц, а каждая рабочая книга представляет год. Когда я делаю или получаю платеж, я сохраняю его как новую строку, что, по сути, делает количество строк в месяц неопределенным. BalanceЯчейка каждой транзакции представляет собой сумму ячейки Balanceстроки над ней и Profitячейки ее строки. Я хочу, чтобы каждый месяц начинался со специальной строки (первой после заголовков столбцов), которая отображает сводкупоследнийтранзакции месяца. Например, Balance After Transactionячейка будет отображать баланс последней строки, а Profit from Transactionячейка будет отображать общую прибыль месяца)
Я знаю, что если бы я знал, что в каждом месяце у меня ровно 100 расходов, я мог бы добиться этого в марте, используя следующие формулы для прибыли и баланса соответственно:
=February!E2 - February!E102
=February!E102
Однако я делаюНЕТЯ знаю, сколько строк будет в таблице каждого месяца, и мне хотелось бы максимально автоматизировать этот процесс (например, если я найду пропущенные или дублирующиеся расходы в январе, мне не хочется обновлять все формулы, указывающие на остаток на конец января).Как сделать так, чтобы Excel автоматически использовал последнее введенное значение в столбце любой таблицы Excel в формуле?
решение1
Моим первым предложением будет преобразовать этот диапазон в таблицу (Вставка -> Таблица), а затем вы сможете добавить строку «Итоги», на которую вы сможете ссылаться с другого листа/рабочей книги по ее имени, и тогда не нужно будет беспокоиться о количестве строк.
Если это не вариант, то можно сделать так:
Чтобы получить сводку прибыли за предыдущий месяц, вам следует использовать
=SUM(Month!$D:$D)
(предполагается, что в столбце прибыли нет других чисел, кроме прибыли).
А для остатка за предыдущий месяц формула @Jesse должна сработать, но только если ваша таблица начинается с первой строки, а если нет, то можно попробовать следующее:
=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)
при вводе обязательно используйте CTRL+SHIFT+ENTER. Это найдет применение ISBLANKво всем столбце, чтобы найти первую пустую ячейку, а затем извлечь значение, расположенное прямо над ней (что будет последней строкой).
Чтобы сделать этот процесс более динамичным и перспективным, я бы предложил использовать именованные диапазоны, чтобы при необходимости вам не пришлось менять все ссылки на столбцы.
решение2
Не используйте косвенный, с этим уровнем расчета это будет болезненно. Косвенный метод изменчив и приведет к тому, что каждая формула в вашей таблице будет пересчитываться каждый раз, когда вы что-то меняете.
Вместо этого можно использовать index. Будет выглядеть так:
=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())
Вы ссылаетесь только на целые столбцы, а смещение столбцов рассчитывается автоматически с помощью COLUMN()функции.
решение3
Другой простой способ — вставить имена в рабочую книгу. Янв, Фев и т. д. Используйте их для определения последней строки, подсчитав количество записей дат в столбце дат.
Например, введите имя «Янв» и RefersTo =COUNT(Янвр!A:A)+2 Это даст вам количество строк, и вы сможете использовать его в своей формуле с косвенным указанием для построения ссылки на диапазон.
Последняя ячейка в строке будет =INDIRECT("E"&Jan), и вы можете использовать эти ссылки в сводной таблице в другом месте, если захотите.
Это предполагает наличие чисел (дат) в столбце A. Если заголовок является текстом, он будет проигнорирован, но в строке 2 убедитесь, что в ней нет числового значения, иначе это исказит количество строк.