Как сохранить текущий баланс платежей и счетов в Excel

Question

У меня есть ряд ячеек с 12 ежемесячными столбцами, от G4 (январь) до R4 (декабрь), в которые я буду вводить сумму, выплачиваемую для ежемесячного выставления счетов. [Обратите внимание, что ранний комментарий обсуждает несколько лет в нескольких строках. Однако это уже не так; это только один год, в ряду 4.]

Когда счет имеет причитающуюся сумму, это стандартная сумма, стоимость которой хранится в ячейке F4.

Если месяц не имеет причитающейся суммы, я оставляю соответствующую ячейку G4:R4 пустой. Если сумма подлежит выплате, я ввожу уплаченную сумму (ноль, если ничего) в соответствующей ячейке.

Я хотел бы, чтобы в ячейке A4 отображалась текущая сумма непогашенного остатка (причитающиеся суммы за вычетом уплаченных сумм). Пример: ежемесячный счет 100,00 (F4). Я заплатил 75 долларов в январе (G4), поэтому итоговая сумма, которую я все еще должен, составляет 25,00 (A4). Я заплатил 50 долларов в феврале на счет за февраль (H4), поэтому теперь я должен 75 (A4).

Обратите внимание, что никогда не будет случая, когда нет выставленной суммы, но я плачу к предыдущему балансу; при наличии предыдущего баланса всегда будет выставляться счет за стандартную сумму. Таким образом, пустая ячейка в G4:R4 означает, что ничего не было выставлено; значение в G4:R4 означает, что стандартная сумма была выставлена и введенная сумма была оплачена.

Answer 1

В комментариях вы указали, что диапазон ячеек для ваших месяцев равен G4:R4

Теперь для вашего ответа:

Формула, которую вы хотите включить в свой промежуточный итог (A4):

= ЕСЛИ (СУММА ($ G4:$ R4) <$ F $ 4 * COUNTA ($ G4:$ R4), $ F $ 4 * COUNTA ($ G4:$ R4) - СУММА ($ G4:$ R4), 0)

Давайте разберем это и проанализируем части:

COUNTA($ G4:$ R4)

COUNTA - это сокращение от "Count All". Он подсчитывает количество непустых ячеек в диапазоне G4:R4. Это потому, что вы сказали, что формула должна игнорировать пробелы. Кроме того, это отслеживает, сколько месяцев прошло.

F4 * COUNTA($ G4:$ R4)

Это общая сумма задолженности. (Ежемесячный платеж)* (количество месяцев)

SUM($ G4:$ R4)

Это общая сумма, уплаченная. По умолчанию это игнорирует пробелы.

Так что вместе мы в основном имеем

IF(TotalPaid <TotalOwed) отображает TotalOwed - TotalPaid

Остальное дисплей 0

Answer 2

В А4 проще говоря:

=$F4*COUNT($G4:$R4)-SUM($G4:$R4)

РЕДАКТИРОВАТЬ: после того, как ваш комментарий убрал некоторые знаки «$», чтобы формулу можно было легко скопировать в 15 необходимых строк.

Answer 3

Примечание. Этот ответ был написан до выяснения вопроса и не отражает нынешнюю формулировку. Я оставляю это на месте только потому, что комментарии документируют причины пересмотра вопроса.

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

В вашем биллинговом цикле могут быть свои особенности, а учетный период может быть в предыдущем году. Если вычисление количества циклов выставления счетов на сегодняшний день не является простым, этот расчет может стать еще одним вопросом для суперпользователя. Для простоты в этом примере, скажем, январь является платежным периодом 1, и вас не интересует день месяца, когда должен быть выставлен счет.

Для этого простого примера A4 может содержать:

=$F$4*MONTH(TODAY())-SUM(G4:R4)

Я предполагаю, что вы копируете A4 в A5 на следующий год, когда вы используете значения в G5:R5. Некоторые заметки:

• Если стандартная сумма выставления счетов может меняться из года в год, не привязывайте F4 со знаками доллара; каждая строка будет иметь соответствующее значение F столбца.
• Если стандартная сумма выставления счетов может измениться в середине года, вам потребуется более сложная формула.
• Эта формула предполагает, что все уравновешивается в декабре и ничего не переносится на следующий год. Если это не так, вы можете добавить остаток на конец года в ячейку, логичным местом будет начало следующего года и включить его в свою сумму.