Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Долг по кредиту на дату
 
#1
19.04.2019 13:35:43
Добрый день. Есть список, представляющий собой информацию об остатках по кредиту Х на произвольные даты. Кредит Х представляет собой несколько траншей. Транши представлены отдельными блоками и отделены друг от друга только форматированием (линией). Добавить столбец данных с признаком Транш 1, Транш 2 и т.д. - нельзя. Помогите, пожалуйста, написать формулу для определения задолженности по кредиту (=сумма задолженностей по траншам) на интересующую дату. Пример прилагаю.
 
 
 
#2
19.04.2019 13:52:44
futurama3000, формулой не возможно...формула не распознает форматирование.
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
19.04.2019 13:52:59
Добрый день.
Если ничего нельзя, то можно в лоб в F2 и растягивать вправо:
Код
=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$42;ПОИСКПОЗ(F$1;$B$2:$B$42;1);1);0)+ЕСЛИОШИБКА(ИНДЕКС($C$43:$C$82;ПОИСКПОЗ(F$1;$B$43:$B$82;1);1);0)+ЕСЛИОШИБКА(ИНДЕКС($C$83:$C$122;ПОИСКПОЗ(F$1;$B$83:$B$122;1);1);0)+ЕСЛИОШИБКА(ИНДЕКС($C$123:$C$163;ПОИСКПОЗ(F$1;$B$123:$B$163;1);1);0)+ЕСЛИОШИБКА(ИНДЕКС($C$164:$C$204;ПОИСКПОЗ(F$1;$B$164:$B$204;1);1);0)+ЕСЛИОШИБКА(ИНДЕКС($C$205:$C$245;ПОИСКПОЗ(F$1;$B$205:$B$245;1);1);0)
Кому решение нужно - тот пример и рисует.
 
 
 
#4
19.04.2019 13:57:39
Пытливый, в лоб не годится. Этот Кредит Х - лишь один из многих.
Mershik, решение есть. Привязка - не только форматирование, но и то, что по каждому траншу даты упорядочены по возрастанию
Изменено: futurama3000 - 19.04.2019 13:59:42
 
 
 
#5
19.04.2019 14:04:33
мб так?
Код
=СУММПРОИЗВ(((ДАТА(ГОД($B$2:$B$245);МЕСЯЦ($B$2:$B$245)+1;ДЕНЬ(1))-1)=G1)*$C$2:$C$245)
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
 
 
#6
19.04.2019 14:07:53
futurama3000, чет не пойму почему вас там стоит 3, 4...
Код
=СУММПРОИЗВ(($B$2:$B$245<=F$1)*($B$2:$B$245>=КОНМЕСЯЦА(F1;-1)+1)*$C$2:$C$245)
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#7
19.04.2019 14:09:39
Александр, на 31.01.2019 ваша формула дает результат 2 100 000 000. Должно быть 3 400 000 000
 
 
 
#8
19.04.2019 14:16:51
Mershik, даты представлены "рваные", т.е. не все. Если есть данные на 22.01.2019, а затем есть данные на 22.04.2019 - то это означает, что долг на 31.01.2019 не менялся и равен 22.01.2019
 
 
 
#9
19.04.2019 14:23:43
futurama3000, Так, разъясните логику ваших подсчётов, я просуммировал январь с январем...
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
 
 
#10
19.04.2019 14:28:53
Александр, Транш 2 (стр 43-82) у вас суммируется? похоже, что нет
Транш 2: есть информация на 23.11.2018, затем на 23.02.2019. На 31.01.2019 долг равен долгу на 23.11.2018
Изменено: futurama3000 - 19.04.2019 14:32:28
 
 
 
#11
19.04.2019 14:58:26
Хотелось бы прояснить задачу:

1) Итак имеются Кредиты (не только x, но и y, z и т.д, возможно так и стоило бы указать);
2) Каждый кредит имеет несколько дат;
3) Каждый блок траншев хранит в себе разные кредиты только с одной датой.

Следовательно требуется посчитать один и тот же кредит во всех датах, которые меньше или равны указанной.
Может тогда так попробовать (тут нужный нам кредит должен иметь имя "Кредит y") :

Код
=СУММПРОИЗВ(($B$2:$B$245<=F$1)*($A$2:$A$245="Кредит y")*$C$2:$C$245)
 
 
 
#12
19.04.2019 15:14:15
Tefra, давайте всё же оперировать одним кредитом, состоящим из нескольких траншей. Наличие нескольких кредитов в задаче ничего принципиально не поменяет.
на 31.01.2019 - у вас 4 800 000 000. Должно быть 3 400 000 000
Давайте посмотрим формулу в #3. Вот она должна быть по логике вычислений такой, но универсальной, не "в лоб"
Изменено: futurama3000 - 22.04.2019 10:27:08
 
 
 
#13
22.04.2019 10:28:28
всем привет. Друзья, обновляю
 
 
 
#14
22.04.2019 13:03:10
Код
Function Транш(Даты As Range, Суммы As Range, Дата As Range)
    arrD = Даты.Value
    arrS = Суммы.Value
    
    tSumm = 0
    
    For i = 1 To UBound(arrD, 1)
        If arrD(i, 1) > Дата.Value Then
            Транш = Транш + tSumm
            tSumm = 0
        Else
            tSumm = arrS(i, 1)
        End If
    Next i
End Function
 
 
 
#15
22.04.2019 16:03:24
Код
=СУММ(
ИНДЕКС($C$1:$C$863;
Ч(ИНДЕКС(ЕСЛИОШИБКА(
ЕСЛИ(
ИНДЕКС(($A$1:$A$863="Кредит Х")*($B$1:$B$863<=H$1);Ч(ИНДЕКС(СТРОКА($1:$863);)))   >    ИНДЕКС(($A$1:$A$863="Кредит Х")*($B$1:$B$863<=H$1);Ч(ИНДЕКС(СТРОКА($1:$863)+1;)));
СТРОКА($A$1:$A$863);
0);
0);))
)
)
 
 
 
Страницы: 1
Наверх