Текущий баланс списка счетов на основе даты и учетной записи

Я использую Excel 2010 и создал пользовательскую форму, которая отображает полный список счетов-фактур из рабочих листов («InvoiceListIncome») с использованием элемента управления ListBox, рабочий лист имеет 13 столбцов и тысячи строк, пример изображения ниже; Проблема, с которой я столкнулся, заключается в том, что диапазон строк баланса ("InvoiceListIncome_Balance")" отображает баланс каждого отдельного счета-фактуры , что мне нужно, так это текущий баланс, начинающийся с первого диапазона счетов («InvoiceListIncome_Date»), поэтому с каждой транзакцией он добавляет текущий + все предыдущие счета для этой учетной записи.

Макрос, который я сейчас использую:

Range("InvoiceListIncome_Balance").FormulaR1C1 = "=SUM(RC[-1])-SUM(RC[-2])"

который просто вычитает то, что оплачено в столбце F, из цены товара в столбце G, но я не могу понять, как добавить все предыдущие остатки по дате, я могу либо получить его для суммирования полного диапазона с учетной записью клиентов в качестве критерия, либо просто активный ряд.

Я бегал по кругу, пытаясь понять это уже несколько дней, любая помощь будет очень признательна, спасибо.

Вот пример того, что мне нужно; введите здесь описание изображения


excel excel-formula vba
person James    schedule 18.08.2011    source источник
comment
Можете ли вы попытаться объяснить некоторые более конкретные детали? Звучит так, как будто я мог бы помочь, подтолкнув в правильном направлении.   -  person RonnieDickson    schedule 18.08.2011
comment
Спасибо, Ронни, я добавил пример того, что мне нужно.   -  person James    schedule 18.08.2011

Ответы (2)


arrow_upward
2
arrow_downward

Предполагая, что в столбцах A, D и E у вас есть Account, Price и Paid соответственно, а первая строка данных находится в строке 2, попробуйте следующую формулу в строке 2:

=SUMIF($A$2:$A2,$A2,$E$2:$E2)-SUMIF($A$2:$A2,$A2,$D$2:$D2)

Скопируйте формулу вниз по столбцу. Это предполагает, что ваши данные отсортированы в возрастающем хронологическом порядке, как вы показываете.

Кроме того, если эта формула работает, у вас есть неправильный знак в 4-й строке снизу столбца, который вы хотите????

person RonnieDickson    schedule 19.08.2011
comment
Большое спасибо, Ронни, сработало отлично! Просто заменил A, D и E на A, E и F, и все сработало именно так, как я себе представлял. Последние несколько недель я ломал голову, пытаясь понять это. еще раз большое спасибо за вашу помощь очень ценится! ;) - person James; 21.08.2011

arrow_upward
0
arrow_downward

Вам нужна промежуточная сумма, поэтому вы хотите включить баланс строки (n-1) при расчете баланса для строки n.

Основываясь на первом изображении, внесите следующие изменения:

  • формула в G2: =F2-E2
  • формула в G3: =G2+F3-E3
  • скопировать формулу из G3 вниз в конец списка; последнее значение - ваш текущий баланс

изменить

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

чтобы разбить клиента (при условии, что столбец клиента отсортирован), вы должны использовать следующую формулу

  • формула в G2: =IF(A2=A1,G1+F2-E2,F2-E2)
  • скопировать формулу из G2 вниз в конец списка

(если этот.аккаунт = предыдущий.аккаунт, то вычислите.текущую.сумму, иначе создайте.новый.баланс)

Это будет работать до тех пор, пока имя вашего первого клиента ‹> «Учетная запись» :-)

person MikeD    schedule 18.08.2011
comment
Это не работает, поскольку предполагается, что существует только 1 клиент, и добавляются все счета-фактуры, а не для каждого отдельного клиента. - person James; 18.08.2011
comment
Из того, что я вижу, он по-прежнему добавляет балансы индивидуально, а не суммирует все предыдущие счета для этой учетной записи, и я не уверен, как и где добавить код, который вы указали внизу; (если this.account = previous.account, тогда вычислите.running.total, иначе создайте.new.balance) Большое спасибо за вашу помощь;) - person James; 18.08.2011
comment
код внизу просто иллюстрирует, что делает =IF(...) - вы нигде его не вводите - person MikeD; 18.08.2011