Бухгалтерский учет для отражения сальдо
В приведенном выше примере я пытаюсь определить остаток средств для арендатора, который ежемесячно производит частичные платежи. Как видите, колонка I отражает то, что должно быть балансом, и первый ряд является правильным. Однако вторая строка неверна, поскольку не учитывает баланс строки 1.
Я хочу, чтобы остаток был отражен для этого одного арендатора, а также отслеживать других арендаторов в том же журнале. Если бы это был SQL, это было бы довольно просто. Просто:
DECLARE AmountPaid int = (SELECT SUM(AmountPaid) FROM table WHERE tenantname = @TenantName)
DECLARE Rent int = (SELECT SUM(MonthlyRent) FROM table WHERE tenantname = @TenantName)
RETURN MonthlyRent - AmountPaid
В любом случае, я ищу это в Excel без макросов и без VB. Inline является предпочтительным, но я готов к тому, что ответ будет "не может быть сделано так".
2 ответа
Три варианта для вас.
Первый вариант
Это требует, чтобы арендная плата вносилась только один раз в месяц, независимо от количества транзакций в месяц. Поместите следующую формулу в I2 и скопируйте:
=SUMIF($A$1:$A2,A2,$C$1:$C2)-SUMIF($A$1:$A2,A2,$F$1:$F2)+SUMIF($A$1:$A2,A2,$G$1:$G2)-SUMIF($A$1:$A2,A2,$H$1:$H2)
Второй вариант
Если вы хотите указывать ежемесячную арендную плату в каждой строке, вам понадобится вспомогательный столбец. Как я уже говорил в комментариях, Excel не может угадать, использовать ли номер или нет. Поэтому добавьте столбец между C и D для Y или N. Тогда формула будет иметь вид:
=SUMIFS($C$1:$C2,$A$1:$A2,A2,$D$1:$D2,"Y")-SUMIF($A$1:$A2,A2,$G$1:$G2)+SUMIF($A$1:$A2,A2,$H$1:$H2)-SUMIF($A$1:$A2,A2,$I$1:$I2)
Третий вариант Мой выбор
Один последний аргумент для одной строки на транзакцию.
Арендная плата взимается в первый раз, независимо от того, когда арендная плата оплачена, и поэтому в первую очередь баланс должен отражать это. Поздние сборы имеют конкретную дату, на которую они начисляются независимо от даты платежа и должны быть добавлены к балансу на эту дату.
С формулами суммирования, приведенными выше, мы можем хранить все это на одном листе. Формула будет:
=SUMIF($A$1:$A2,A2,$E$1:$E2)-SUMIF($A$1:$A2,A2,$D$1:$D2)
Я предлагаю сводную таблицу - из ваших первых семи столбцов (A для ROWS, C, F и G для суммы значений) и рассчитанное поле суммы, скажем, BALANCE, которое будет:
=C + G - F
детали могут быть затем извлечены путем сверления, чтобы удовлетворить.