Расширенный выбор ячеек в Excel

Question

Я новичок в этой разновидности 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, в формуле?

Answer 1

Мое первое предложение будет заключаться в том, чтобы преобразовать этот диапазон в таблицу («Вставка» -> «Таблица»), а затем вы можете добавить строку «Итоги», на которую можно ссылаться из другого листа / рабочей книги, по ее имени, и тогда вам не нужно будет беспокоиться о количество рядов

Если это не вариант, то вы можете сделать это:

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

=SUM(Month!$D:$D)

(это предполагает, что столбец прибыли не имеет других чисел, кроме прибыли).

И для баланса предыдущего месяца формула @Jesse должна работать, но только если ваша таблица начинается с первой строки, а если нет, вы можете попробовать это:

=INDEX(Month!$E:$E,MATCH(TRUE,ISBLANK(Month!$E:$E),0)-1)

при вводе обязательно используйте CTRL+SHIFT+ENTER . Это позволит использовать ISBLANK во всем столбце, чтобы найти первую пустую ячейку, а затем извлечь значение чуть выше него (которое будет последней строкой).

Чтобы сделать это более динамичным и перспективным, я бы предложил использовать именованные диапазоны, поэтому, если потребуется, вам не придется изменять все ссылки на столбцы.

Answer 2

Не используйте косвенные, с этим уровнем расчета будет больно. Косвенный является непостоянным и заставит каждую отдельную формулу в вашем листе пересчитывать каждый раз, когда вы что-либо меняете.

Вы можете использовать индекс вместо этого. Будет выглядеть так:

=INDEX(Month!A:E,COUNTA(Month!A:A),COLUMN())

Вы ссылаетесь только на целые столбцы, и смещение столбцов вычисляется автоматически с помощью функции COLUMN() .

Answer 3

Другой простой способ - вставить имена в рабочую книгу. Январь, февраль и т.д. Используйте их для определения последней строки путем подсчета количества записей в столбце даты.

Например, введите имя "Ян" и RefersTo = COUNT(январь!A:A)+2 Это даст вам счетчик строк, и вы можете использовать его в своей формуле с косвенным для построения ссылки на диапазон.

Последняя ячейка в строке будет = INDIRECT("E" & Jan), и вы можете использовать эти ссылки в сводной таблице в другом месте, если хотите.

Предполагается, что числа (даты) в столбце А. Он будет игнорировать заголовок, если он текстовый, но в строке 2 убедитесь, что у него нет числового значения, иначе будет искажен счетчик строк.