Сводная таблица данных с накоплением

[Alxxxx74]

Уважаемые форумчане! Прошу помочь вас разобраться в моей проблеме.
Передо мной стоит задача, с которой в одиночку мне не справиться. Имеется организация "А" и организация "Б", между которыми происходит постоянный взаимный товарный оборот, то есть товары отгружаются в обе стороны. При чем, товарный поток направленный в сторону "А", значительно превышает товарный поток исходящий в "Б" организацию. Отгрузка товаров в организацию "А" происходит на условиях отсрочки платежа, которая не является постоянной величиной. Организация "А" погашает обязательства за отгруженный товар перед организацией "Б" как денежными средствами, так и товаром (в виде взаимозачета). Однако организация "А", постоянно нарушает сроки установленные величиной отсрочки. Возникла необходимость сформировать расчет, в котором отражалось бы количество дней просроченной оплаты и пени за просрочку платежей. У меня имеется книга эксель, в которой на нескольких листах внесены исходные данные, а так же лист расчета, который собственно и хотелось автоматизировать (лист "Расчеты"). Суть проблемы для меня заключается в следующем:
Так как организация "А" оплачивает за отгруженный товар суммы, которые имеются у нее на счетах, то эти "кусочки" день ото дня необходимо "накапливать", для того, чтобы учесть полное погашение конкретной отгрузки, а сумму, которая последней "закроет" эту отгрузку, дробить и разницу учитывать на следующую отгрузку.
Имея даты поступлений денежных средств, можно вычислить количество дней просрочки до полного погашения задолженности по конкретной отгрузке, при чем исходя из остатка задолженности по ней.
Теоретически я догадываюсь, что сделать это можно только с помощью сводной таблицы, однако они для меня "темный лес". В примере, на листе "Расчеты", я выделил розовым цветом те ячейки, которые хотелось бы автоматизировать.
Заранее благодарен!

[Alxxxx74]

Жаль, но похоже задача осталась неподъемной  

Кстати, в примере есть выпадающие списки, которые должны заслуживать внимания. Они достаточно изящны, компактны и динамически расширяемы, т.е. теперь не нужно каждый раз править формулу в диапазоне списка, а просто увеличить размер таблицы со справочником, добавить нужные значения и выпадающий список увеличится сам по себе.

[NooBasTiK]

Жаль, но похоже задача осталась неподъемной  

Кстати, в примере есть выпадающие списки, которые должны заслуживать внимания. Они достаточно изящны, компактны и динамически расширяемы, т.е. теперь не нужно каждый раз править формулу в диапазоне списка, а просто увеличить размер таблицы со справочником, добавить нужные значения и выпадающий список увеличится сам по себе.

Так в розовых ячейках у вас формулы прописаны они и так автоматизированны

[Alxxxx74]

Автоматизация требуется в столбцах E, F и H. Автоматизация столбцов C и G сделана исходя из данных занесенных фактически вручную в столбцы E и H 

[NooBasTiK]

Автоматизация требуется в столбцах E, F и H. Автоматизация столбцов C и G сделана исходя из данных занесенных фактически вручную в столбцы E и H  

На сколько я понял столбец E набирается исходя из сумм по цене партии?

[Alxxxx74]

Автоматизация требуется в столбцах E, F и H. Автоматизация столбцов C и G сделана исходя из данных занесенных фактически вручную в столбцы E и H  

На сколько я понял столбец E набирается исходя из сумм по цене партии?

Столбец E - это ни что иное, как отражение дат в которые заходили суммы из листа ДДС. Последняя сумма, если она больше остатка задолженности, переходит на следующую отгрузку. Но бывает и наоборот, когда поступившая сумма больше сумм нескольких отгрузок, тогда ею закрывается следующие отгрузки, а остаток переходит на следующую незакрытую.

Чтобы было понятнее, вот такой вот пример:

Произведено две отгрузки. Сумма каждой из них, например 1 000 000.
Дата погашения первой, скажем 01.01.12, второй - 15.01.12
Поступил платеж в 100 000 - 31.12.11, второй в 400 000 - 03.01.12, третий в 700 000 - 16.01.12.
Считаем так- остаток просроченной задолженности 900 000 (1 000 000 - 100 000). Считаем % за два дня просрочки с 900 000. Далее, с 500 000 считаем % за 13 дней просрочки. Третья сумма в 700 000 закрывает полностью первую отгрузку и часть этой суммы в 200 000 переходит на покрытие следующей отгрузки, но уже с 1-м днем просрочки, т.е. за 1 день просрочки начисляем % с целиковой суммы второй отгрузки... и т.д. Суммы поступлений в основном разнятся, поэтому количество "кусочков", на которые дробится расчет 5 за просрочку так же всегда неодинаково.
Таким образом, препятствием для полной автоматизации, становится прикрепление последующих поступающих сумм, до полного погашения конкретно взятой отгрузки, вычисление промежуточных значений количества дней просрочки.

[Alxxxx74]

Попробовал соединить данные двух таблиц в одну, с целью сделать сводную таблицу, ничего не вышло, так как достаточно плохо знаком со сводными таблицами. Снова тупик 

[Alxxxx74]

Чего с чем соединить надо?
Только кратко, без "многабукаф"

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

[Serge 007]

Попробовал соединить данные двух таблиц в одну...

Да мне в общем-то не то чтобы соединить надо.

?!

Мне нужно получить автоматическое закрытие отгрузок и оплат. Вычисление дат этих оплат, для получения количества дней просрочки.

Опишите задачу (для уже приложенного Вами ранее файла) в стиле: "Из ячейки А1 листа Расчеты данные должны быть в ячейке А2 Листа ДДС, потому что..."

Постараюсь помочь

[Alxxxx74]

В идеале мне видится это так (строки 10):
Лист "Расчеты". Стобцы A10, B10, D10, I10, J10 - это группа, данные в которых получаются из таблицы "Отгрузка" из листа "Товарная движуха". Разворот группы должен состоять из столбцов C, E, F, G, H. Данные для столбцов E, F, H - получаются из таблицы "ДДС" листа "ДДС". Данные столбца C расчитываются изходя из разниц между датами группового столбца B10 и данных разворота в столбце E. Данные столбца G расчитываются исходя из переменной годовой процентной ставки (указанной например в ячейке С1) и данных полученных в результате вычислений количества дней просроченных промежуточных задолженностей (см. формулы в ячейках столбца G). Заранее благодарю за отзывчивость! 

[Alxxxx74]

Всё-равно нифига не понятно
Почему Дате прихода сырья 10.08.2009 соответствуют даты 14.09.2009 и 16.09.2009?
И почему дата в ДДС 16.09.2009 =  260 000,00, а на листе Расчёты она разбита на разные дни по 18 491,13 и 241 508,87?

Дате прихода сырья 10.08.2009 соответствует дата 07.09.2009 (10.08.2009 + 28), а даты 14.09.2009 и 16.09.2009 расположены в этой таблице так, потому что данные заносились вручную. Разбивка суммы 260 000 сделана так, потому как часть в 18 491,13 полностью закрывает первую отгрузку сырья, а оставшаяся от нее часть переходит на покрытие следующей отгрузки, естественно с той же датой.

[Alxxxx74]

Это можно сделать формулами. Но как запихать их ИМЕННО в Вашу форму - пока не представляю. Она Вам очень нужна? Может изменим?

Можно. А как? Мне главное не форма, а содержание. Важно наглядно видеть цифры просроченной оплаты, и соответственно сумму процентов за это.

[ZORRO2005]

Произведено две отгрузки. Сумма каждой из них, например 1 000 000.
Дата погашения первой, скажем 01.01.12, второй - 15.01.12
Поступил платеж в 100 000 - 31.12.11, второй в 400 000 - 03.01.12, третий в 700 000 - 16.01.12.
Считаем так- остаток просроченной задолженности 900 000 (1 000 000 - 100 000). Считаем % за два дня просрочки с 900 000. Далее, с 500 000 считаем % за 13 дней просрочки. Третья сумма в 700 000 закрывает полностью первую отгрузку и часть этой суммы в 200 000 переходит на покрытие следующей отгрузки, но уже с 1-м днем просрочки, т.е. за 1 день просрочки начисляем % с целиковой суммы второй отгрузки... и т.д. Суммы поступлений в основном разнятся, поэтому количество "кусочков", на которые дробится расчет 5 за просрочку так же всегда неодинаково.

Предлагаю сделать  просто и наглядно.
См. вложение

[Alxxxx74]

Это конечно неплохо, но нет наглядности для дальнейшего доказательства правоты требований... Но все равно благодарю!

[ZORRO2005]

Это конечно неплохо, но нет наглядности для дальнейшего доказательства правоты требований... Но все равно благодарю!

2-ой вариант во вложении