☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Как вести текущий баланс платежей и выставленных счетов в Excel

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

Если в счете имеется сумма к оплате, то это стандартная сумма, значение которой хранится в ячейке F4.

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

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

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

решение1

На А4 проще говоря:

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

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

решение2

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

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

Формула, которую вам нужно будет подставить в вашу промежуточную сумму (A4), выглядит следующим образом:

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

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

СЧЁТA($G4:$R4)

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

F4*СЧЁТA($G4:$R4)

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

СУММА($G4:$R4)

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

Итак, вместе мы, по сути, имеем

ЕСЛИ( TotalPaid < TotalOwed ) отобразить TotalOwed - TotalPaid

В противном случае отобразить 0

решение3

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

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

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

В этом простом примере А4 может содержать:

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

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

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

Связанный контент