Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Позиция элемента суммы накопления с дополнительным условием - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Позиция элемента суммы накопления с дополнительным условием (Формулы/Formulas)
Позиция элемента суммы накопления с дополнительным условием
konsvg Дата: Вторник, 26.05.2020, 10:39 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Доброго всем времени суток!
Здравствуйте.

Нужно решение с помощью формул
Маленькое предварительное замечание. Пользуюсь Excel 2016, его и указал в профиле

Я создаю файл для анализа дебиторской задолженности. Клиенты по условию договоров сначала платят аванс, затем в рассрочку погашают оставшуюся часть суммы договора. Необходимо определить дату поступления аванса. Клиенты могут выплачивать аванс одним платежом или несколькими, могут единым платежом превысить сумму аванса - в этом и кроется проблема. Если бы количество клиентов было небольшим, можно было бы каждому договору дополнительный столбец выделить. Но клиентов - несколько тысяч. Нужно какое-то другое решение.

Если сузить задачу, то надо определить позицию ячейки в массиве данных, в которой определенному номеру договора будет соответствовать своя, заранее заданная, накопленная сумма (т.е. размер аванса).

Для одного условия - определения накопленной суммы - я формулу написал (суммы платежей в столбце "С", накопленную сумму задавал в яч. H1):
Код
=ПОИСКПОЗ(2;2/(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C$1;;;СТРОКА($C$4:$C$47)))<=H$1))


Но добавить второе условие (номер договора) - не получилось. Не знаю, как с этим справиться. Знаний не хватает....(((
К сообщению приложен файл: _2-_.xlsx (12.6 Kb)


Константин

Сообщение отредактировал konsvg - Вторник, 26.05.2020, 10:44
 
Ответить
СообщениеДоброго всем времени суток!
Здравствуйте.

Нужно решение с помощью формул
Маленькое предварительное замечание. Пользуюсь Excel 2016, его и указал в профиле

Я создаю файл для анализа дебиторской задолженности. Клиенты по условию договоров сначала платят аванс, затем в рассрочку погашают оставшуюся часть суммы договора. Необходимо определить дату поступления аванса. Клиенты могут выплачивать аванс одним платежом или несколькими, могут единым платежом превысить сумму аванса - в этом и кроется проблема. Если бы количество клиентов было небольшим, можно было бы каждому договору дополнительный столбец выделить. Но клиентов - несколько тысяч. Нужно какое-то другое решение.

Если сузить задачу, то надо определить позицию ячейки в массиве данных, в которой определенному номеру договора будет соответствовать своя, заранее заданная, накопленная сумма (т.е. размер аванса).

Для одного условия - определения накопленной суммы - я формулу написал (суммы платежей в столбце "С", накопленную сумму задавал в яч. H1):
Код
=ПОИСКПОЗ(2;2/(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ($C$1;;;СТРОКА($C$4:$C$47)))<=H$1))


Но добавить второе условие (номер договора) - не получилось. Не знаю, как с этим справиться. Знаний не хватает....(((

Автор - konsvg
Дата добавления - 26.05.2020 в 10:39
AlexM Дата: Вторник, 26.05.2020, 12:04 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4449
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
С дополнительным столбцом
К сообщению приложен файл: _2-01.xlsx (12.8 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеС дополнительным столбцом

Автор - AlexM
Дата добавления - 26.05.2020 в 12:04
konsvg Дата: Вторник, 26.05.2020, 14:33 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
С дополнительным столбцом

Благодарю за ответ.
Но я специально сообщил, что с доп.столбцом - не вариант.
Если бы количество клиентов было небольшим, можно было бы каждому договору дополнительный столбец выделить. Но клиентов - несколько тысяч. Нужно какое-то другое решение.

Решение, насколько подозреваю - из области обработки массивов. Вот как к нему подобраться...


Константин
 
Ответить
Сообщение
С дополнительным столбцом

Благодарю за ответ.
Но я специально сообщил, что с доп.столбцом - не вариант.
Если бы количество клиентов было небольшим, можно было бы каждому договору дополнительный столбец выделить. Но клиентов - несколько тысяч. Нужно какое-то другое решение.

Решение, насколько подозреваю - из области обработки массивов. Вот как к нему подобраться...

Автор - konsvg
Дата добавления - 26.05.2020 в 14:33
AlexM Дата: Среда, 27.05.2020, 07:21 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4449
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Как вы планируете выводить результат, например для трёх договоров?



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеКак вы планируете выводить результат, например для трёх договоров?

Автор - AlexM
Дата добавления - 27.05.2020 в 07:21
Nic70y Дата: Среда, 27.05.2020, 08:07 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8712
Репутация: 2262 ±
Замечаний: 0% ±

Excel 2010
формула массива
Код
=ПОИСКПОЗ(H1;ЕСЛИОШИБКА(--ПСТР(-СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E47)-3);H2;СМЕЩ(C4;;;СТРОКА(E4:E47)-3))*(E4:E47=H2);2;15);""))+3


ЮMoney 41001841029809
 
Ответить
Сообщениеформула массива
Код
=ПОИСКПОЗ(H1;ЕСЛИОШИБКА(--ПСТР(-СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E47)-3);H2;СМЕЩ(C4;;;СТРОКА(E4:E47)-3))*(E4:E47=H2);2;15);""))+3

Автор - Nic70y
Дата добавления - 27.05.2020 в 08:07
AlexM Дата: Среда, 27.05.2020, 09:12 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4449
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Nic70y, очень близок был к решению без доп.столбца. Это решение подтолкнуло.
Нет у меня ЕСЛИОШИБКА() поэтому без нее. У меня получилось без проверки E4:E47=H2
Код
=ПОИСКПОЗ(1=1;СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E47));H2;СМЕЩ(C4;;;СТРОКА(E4:E47)))>=H1;)+3
Формула массива



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Среда, 27.05.2020, 11:50
 
Ответить
СообщениеNic70y, очень близок был к решению без доп.столбца. Это решение подтолкнуло.
Нет у меня ЕСЛИОШИБКА() поэтому без нее. У меня получилось без проверки E4:E47=H2
Код
=ПОИСКПОЗ(1=1;СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E47));H2;СМЕЩ(C4;;;СТРОКА(E4:E47)))>=H1;)+3
Формула массива

Автор - AlexM
Дата добавления - 27.05.2020 в 09:12
konsvg Дата: Четверг, 28.05.2020, 15:49 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
hands hands
AlexM и Nic70y
Я очень вам благодарен!!!!
Формулы замечательные (столько нового узнал об обработке массивов)
Все работает, как часики. И даже лучше))


Константин
 
Ответить
Сообщениеhands hands
AlexM и Nic70y
Я очень вам благодарен!!!!
Формулы замечательные (столько нового узнал об обработке массивов)
Все работает, как часики. И даже лучше))

Автор - konsvg
Дата добавления - 28.05.2020 в 15:49
AlexM Дата: Четверг, 28.05.2020, 19:34 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4449
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
konsvg, так правильнее будет
Код
=ПОИСКПОЗ(1=1;СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E99)-3);H2;СМЕЩ(C4;;;СТРОКА(C4:C99)-3))>=H1;)
формула в сообщении №6, неверно определит позицию при <=12000 и 2314
При создании формул с массивами, чтобы понимать что происходит при вычислении, массивы можно просматривать. Для этого в строке формул выделяете интересующий фрагмент формулы и нажимаете F9
Прикладываю файл, который сохранил в копилке знаний.
К сообщению приложен файл: ____.xls (48.0 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеkonsvg, так правильнее будет
Код
=ПОИСКПОЗ(1=1;СУММЕСЛИ(СМЕЩ(E4;;;СТРОКА(E4:E99)-3);H2;СМЕЩ(C4;;;СТРОКА(C4:C99)-3))>=H1;)
формула в сообщении №6, неверно определит позицию при <=12000 и 2314
При создании формул с массивами, чтобы понимать что происходит при вычислении, массивы можно просматривать. Для этого в строке формул выделяете интересующий фрагмент формулы и нажимаете F9
Прикладываю файл, который сохранил в копилке знаний.

Автор - AlexM
Дата добавления - 28.05.2020 в 19:34
konsvg Дата: Пятница, 29.05.2020, 10:21 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
AlexM, я очень признателен Вам.
За интерес к задаче, разъяснения и неравнодушие.
Спасибо


Константин
 
Ответить
СообщениеAlexM, я очень признателен Вам.
За интерес к задаче, разъяснения и неравнодушие.
Спасибо

Автор - konsvg
Дата добавления - 29.05.2020 в 10:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Позиция элемента суммы накопления с дополнительным условием (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!