Страницы: 1
RSS
Агрегация данных из большой таблицы в более компактную
 
Добрый день!
Стоит задача сделать агрегированную таблицу из большой таблицы платежей и оплат.
Исходная таблица имеет такие столбцы:
Дата | Номер договора | Сумма к оплате | Срок оплаты | Поступившая оплата
На ее основе необходимо сделать более компактную таблицу без пропусков, чтобы поступившие оплаты зачитывались в соответствии с самыми ранними сроками оплаты:
Дата | Номер договора | Срок оплаты | Сумма к оплате | Сколько оплатили | Сколько должны оплатить с учетом поступивших оплат
Например, платеж 25.02.2022 по договору 2 в размере 400 сначала должен покрыть сумму 300 от 08.02.2022 со сроком оплаты 22.02.2022, а затем сумму 200 от 19.02.2022 со сроком оплаты 25.03.2022. Таким образом, по договору 2 останется оплатить 100 (и эта сумма будет указана в строчке со сроком оплаты 25.03.2022)
Как можно это реализовать с помощью формул без макросов?
Заранее спасибо!
 
romashka123, обычную сводную используйте
Изменено: Mershik - 11.11.2022 12:57:45
Не бойтесь совершенства. Вам его не достичь.
 
В дополнение к этому - вторая задача.
Добавляется еще один столбец - Лимит.
Необходимо получить все то же самое, только при этом нужно показать только те строки, кумулятивная сумма оплаты которых не превышает значения лимита. В частности, 17.02.2022 выставлен счет на сумму 800, и таким образом кумулятивно, начиная с начала таблицы, общая сумма всех выставленных счетов - 2900 превышает 2500. Поэтому от суммы к оплате остается только 2900-2500=400 (выделено желтым). Следующее выставление счета 19.02.2022 уже не учитывается в этой таблице.
Какие в этом случае должны быть формулы?
 
Цитата
написал:
romashka123, обычную сводную используйте
Спасибо. Сводная таблица только для агрегации данных.
Но как произвести необходимые вычисления и получить итоговую таблицу?
Например, платеж 25.02.2022 по договору 2 в размере 400 сначала должен покрыть сумму 300 от 08.02.2022 со сроком оплаты 22.02.2022, а затем сумму 200 от 19.02.2022 со сроком оплаты 25.03.2022. Таким образом, по договору 2 останется оплатить 100 (и эта сумма будет указана в строчке со сроком оплаты 25.03.2022)
 
romashka123,
1. Я не понял, почему по первому договору всего оплачено 400 в изначальных данных, а в результате у Вас 300.
2. Можно сводной таблицей решить (Power Pivot). Можно и в Power Query.
3. Не вижу Ваших попыток. Наводку я дал. Если уже что-то конкретное не будет получаться - задавайте вопрос.
Ну либо вы "напиво" даете (размещаете тему в платном разделе) и Вам реализуют.
 
Цитата
romashka123 написал:
Сводная таблица только для агрегации данных.
Это Вы откуда такое взяли? Забудьте эту ерунду!
Результат моей сводной корректен?
 
Цитата
написал:
Цитата
romashka123 написал:
Сводная таблица только для агрегации данных.
Это Вы откуда такое взяли? Забудьте эту ерунду!
Результат моей сводной корректен?
Да, корректен. Могли бы Вы прислать Ваш файл со сводной таблицей?
 
romashka123,
1. Не используйте кнопку цитирования для копии всего сообщения. Исправьте, иначе модераторы будут недовольны.
2. Я уже писал. Я не вижу Ваших попыток. Хотите помощи - я Вам подсказал, что можно решить в Power Pivot. Можно и в Power Query, и макросом.
Решать полностью за Вас (а не подсказывать) я не буду :) А сейчас Вы просите именно это.
 
Спасибо.
Power Pivot у меня нет в моей версии Excel.
Есть Power Query.
Пока с помощью группировки удалось получить только такую таблицу. Однако это не то, что мне нужно. Как получить корректную сводную таблицу - Дата | Номер договора | Срок оплаты | Сумма к оплате | Сколько оплатили | Сколько должны оплатить с учетом поступивших оплат ?
ДатаНомер договораСуммы счетовСуммы оплат
02.02.2022 0:00:001100null
03.02.2022 0:00:003200null
05.02.2022 0:00:004400null
08.02.2022 0:00:002300null
10.02.2022 0:00:005100null
13.02.2022 0:00:001500null
14.02.2022 0:00:001400null
16.02.2022 0:00:003100null
17.02.2022 0:00:005800null
19.02.2022 0:00:002200null
25.02.2022 0:00:002null400
26.02.2022 0:00:001null300
27.02.2022 0:00:001null100
 
romashka123, а какая у Вас версия excel? Может, просто нужно включить надстройку PowerPivot?
 
MS Office для дома и бизнеса 2016. Такой надстройки, к сожалению, нет в списке надстроек.
 
Если в PQ, то примерно так:
1. Из данных отфильтруйте оплаты, суммируете из по договору и создайте из этого запись (имена полей - номера договоров, значения - суммы). Во втором шаге вы эту сумму подтянете к первой строке счетов соответствующего договора.
2. Из данных отфильтруйте и отсортируйте счета. И в рамках каждого договора раскидывайте соответствующую сумму оплаты по каждому счету до 0 (с помощью List.Generate).

P.S. Мне лень это делать в 100 раз :) Это не на 5 мин задача :)
Изменено: surkenny - 11.11.2022 18:20:58
 
romashka123, красоту сами наводите в запросе, набросал как набросал, что-то PQ у меня работает как-будто в 10 FPS.
А я пойду переустанавливать офис :-)
Ну и вот это
Цитата
surkenny написал:
1. Я не понял, почему по первому договору всего оплачено 400 в изначальных данных, а в результате у Вас 300.
сделал как понял
Изменено: whateverlover - 12.11.2022 12:50:52
 
whateverlover, Спасибо большое. С помощью Вашего запроса обработала реальные данные. Все работает. Однако у меня не было опыта работы с Power Query, и я пока еще плохо понимаю это.
А как реализовать более простой вариант (без лимита)? Просто, чтобы получилась обычная сводная таблица с подсчетами?
 
Всем привет.
Еще раз спасибо за помощь. Осваиваю Power Query.

Решение whaterlover, к сожалению, пока не удалось понять.

Прилагаю другое решение - небольшой файл Excel - там есть исходная таблица и агрегированная таблица, которую необходимо получить, а также описание шагов, которые необходимо осуществить. Внутри зашит запрос Power Query (см. запрос CALCULATION). Этот запрос пока позволяет реализовать задачу частично.

Необходимо получить агрегированную таблицу (справа) из таблицы с информацией о выставленных счетах и входящих платежах (слева), при этом входящие платежи нужно зачитывать в счет контрактов с самыми ранними сроками платежа на основе номера контракта.
При этом сначала производить зачет самых ранних платежей с самым высоким приоритетом, где 1 - самый высокий приоритет и так далее.

Удалось реализовать зачет платежей с самими ранними сроками платежа на основе номера контракта, но не получается учесть ПРИОРИТЕТ.

Как это можно реализовать?

Заранее большое спасибо за помощь.
Страницы: 1
Наверх