Блог

Главная - Вопросы по программированию - Сумма счета в строке n, оплата в строке n k, с использованием дат

Сумма счета в строке n, оплата в строке n k, с использованием дат

#excel #date #excel-formula

#excel #Дата #excel-формула

Вопрос:

У меня есть таблица платежей для расчета процентов. Столбец, в котором применяется платеж, берет свои значения из столбца, в котором перечислены итоговые суммы счета, однако платеж применяется через k дней после окончания периода.

рассматриваемая таблица

Я добился частичного успеха, используя эту формулу:

 IFERROR(INDEX($F$12:$F$25,MATCH(D12,$G$12:$G$25,1)),0)

Где столбец G — вспомогательный столбец с датами платежа, в основном конец периода k, но поскольку он учитывает только конец периода, в месячные и полугодовые периоды иногда ближайшая меньшая дата платежа приходилась на тот же период, поэтому я также ДОЛЖЕН учитывать начало периода, чтобы этоне происходит. Мне помогли использовать формулу массива, подобную этой:

 =IFERROR(INDEX(F:F,SMALL(IF(F$12:F12>0,ROW(F$12:F12)),COUNT(1/((C$12:C12-C$12>C$7) (D$12:D12-C$12>C$7))))),0)

И это работает хорошо, и для этого не требуется вспомогательный столбец. Но поскольку это формула массива, и эта таблица не для моего использования, это не подходит.

Я хотел бы знать, могу ли я сделать это без формулы массива и встроенных функций Excel 2013.

Редактировать: эта формула делает это:

=SUMPRODUCT(($D$12:$D$25 $C$7>=C12)*($D$12:$D$25 $C$7<=D12)*($F$12:$F$25))

Но если в столбце D есть пробелы, которые являются результатом формулы, она возвращает ошибку. Таким образом, следующая формула более стабильна:

=SUMIFS($F$12:$F$25,$D$12:$D$25,">="amp;C12-$C$7,$D$12:$D$25,"<="amp;D12-$C$7)

Этот метод эффективно помещает строки платежей в диапазон дат, к которым он принадлежит.

Ответ №1:

По крайней мере, вы можете SMALL заменить AGGREGATE следующим образом:

 =IFERROR(INDEX(F:F;AGGREGATE(15;6;(ROW(F$12:F12)/(F$12:F12>0));COUNT(1/((C$12:C12-C$12>C$7) (D$12:D12-C$12>C$7)))));0)

AGGREGATE(15;6;;) аналогично SMALL , но игнорирует ошибки. Это позволяет вам отключить IF(F$12:F12>0,ROW(F$12:F12)) для частного (ROW(F$12:F12)/(F$12:F12>0)) . Каждый номер строки, деленный на a FALSE , выдает ошибку, которая игнорируется AGGREGATE .

Что касается COUNT -part, я не могу сказать, что он делает, поскольку мои результаты не похожи на ваши (я скопировал вашу формулу). Я думаю, это работает как смещение.

Можете ли вы протестировать мою формулу и сказать мне, работает ли она без cse? Иначе нам нужно найти замену для COUNT -part . В настоящее время я не могу протестировать его, поскольку я в Office 365 и не знаю, могу ли я активировать старую функциональность CSE.

введите описание изображения здесь

Комментарии:

1. Часть подсчета заключалась в вычислении k аргумента из SMALL функции. Изнутри наружу, C$12:C12 чтобы оценить от текущей строки до вершины, например, в пятой строке таблицы, это выглядит следующим C$12:C17 образом. C$12:C12-C$12 возвращает прошедшие дни. C$12:C12-C$12>C$7 возвращает дни, прошедшие выше срока оплаты, он будет возвращать 0, пока не найдет один, но как только он найдет один, он найдет следующий и так далее, вот почему SMALL была использована функция, потому что она ранжирует их только тогда, когда они выше. Обратный счет используется для игнорирования значений с меньшим DIV/0! значением.

2. Но мне также сложно поддерживать, мне потребовалось слишком много времени, чтобы понять его механизм, поскольку я до сих пор не совсем понимаю, почему он суммирует следующее число. Я не мог этого понять, поэтому я отбросил его. Затем кто-то помог мне найти еще два решения, которые я мог понять. Один с SUMPRODUCT, а другой с SUMIF. Я опубликую их позже, как только смогу, чтобы обновить эту тему. Кстати, ваша формула вернула 0 во всех строках для моего набора данных. Тем не менее, большое вам спасибо. Я опубликую решение в ближайшие часы. Должен ли я проголосовать?

3. Но C$12:C12-C$12>C$7 с фиксированной строкой в C$12 выдает всегда true, так как на вашем изображении C12 пусто. Также думал об SUMPRODUCT этом.

4. Работает ли это так, как ожидалось, если вы удалите сумму в середине столбца итог счета?

5. Для набора данных, который вы любезно создали для выполнения своих тестов, эта формула поместит сумму платежа там, где она должна быть, в соответствии со столбцами C и D: =SUMIFS($E$12:$E$25,$D$12:$D$25,">="amp;C12-$C$7,$D$12:$D$25,"<="amp;D12-$C$7) Извините за задержку ответа, это была сумасшедшая неделя.