Добрый день, я новичок в экселе, прошу не проходите мимо. Необходимо довести до ума формулы для расчета просрочки по этапам. У меня где-то ошибки в условиях, по 4му этапу, например, показывает из-за пустой ячейки 44 тысячи. Пример расчета на основе 5-го этапа: 1) 2 262 215 (сумму этапа) *52 (дня просрочки)*1/300*5,5%=21566,45 руб. (1ый акт вх. 22.05) просрочка в днях с 01.04 по 22.05 2) 718 491*24*1/300*5,5%=3161,36 руб. (2ой акт с 23.05 по 15.06 просрочка). 3) 359 567,4 * 154 * 1/300 *4,25% =7844,56 (16.06-16.11 (текущая дата на момент последних расчетов в ячейке N1)
В документе присутствует макрос для ключевой ставки на указанную дату.
Добрый день, я новичок в экселе, прошу не проходите мимо. Необходимо довести до ума формулы для расчета просрочки по этапам. У меня где-то ошибки в условиях, по 4му этапу, например, показывает из-за пустой ячейки 44 тысячи. Пример расчета на основе 5-го этапа: 1) 2 262 215 (сумму этапа) *52 (дня просрочки)*1/300*5,5%=21566,45 руб. (1ый акт вх. 22.05) просрочка в днях с 01.04 по 22.05 2) 718 491*24*1/300*5,5%=3161,36 руб. (2ой акт с 23.05 по 15.06 просрочка). 3) 359 567,4 * 154 * 1/300 *4,25% =7844,56 (16.06-16.11 (текущая дата на момент последних расчетов в ячейке N1)
В документе присутствует макрос для ключевой ставки на указанную дату.TVkills
По-видимому, при неполной информации по проблеме аналитической базы данных потребуется: 1. описание алгоритма расчетов (в терминах показателей, а не чисел) и 2. изменение формул расчетов для нескольких полей. Примечания в приложенном файле.
Добрый день.
По-видимому, при неполной информации по проблеме аналитической базы данных потребуется: 1. описание алгоритма расчетов (в терминах показателей, а не чисел) и 2. изменение формул расчетов для нескольких полей. Примечания в приложенном файле.NikitaDvorets
Контракт на выполнение работ по строительству. Контрактом предусмотрен график выполнения работ. Просрочка идёт начиная со следующего дня после предусмотренного контрактом.
1) Согласен, заголовок G1 не может быть в расчете, но хотелось подобрать некие универсальные формулы, что бы расчеты велись по одной формуле для всего листа. 1ый этап выполнен полностью, остаток 0, но предъявлен позднее установленного срока.
2) "Заголовок не точен, имеется ввиду "Удельная стоимость просроченного акта"?" тут немного сложнее, смотрите (опять же на примере 5го этапа). Сумма работ по 5му этапу (общая, по контракту) составляет 2262215 руб, работы должны были быть выполнены Подрядчиком в марте, но предъявлены только в мае частично на сумму 1 543 724, соответственно, на основную сумму надо начислить неустойку с просрочкой с 01.04. по 22.05, что составляет 52 дня: 2 262 215 * 52 *1/300 * 5,5% = 21 566,45 руб. Следующий акт предъявлен в июне на сумму 358923,6 руб. надо начислить неустойку на вот эту разницу (остаток, невыполненные работы которые не выполнялись определенный срок) и на дату следующего акта считаем: 2 262 215 - 1 543 724 = 718 491 руб. Считаем: 718 491 * 24 *1/300 * 5,5% = 3 161,36 руб.
Потому, как по одной строке (где общая стоимость работ по этапу контракта указана) необходимо выполнить два расчета, я сделал две группы столбцов, K, L, M в них я планировал сделать расчет неустойки с первого "закрытия" работ, а насчитывать сумму на остаток планировал в O, P, Q (т.к. в одной строке и 2 млн общей стоимости и 700 тысяч "остатка" и неустойку надо от каждой суммы посчитать на разные даты).
Следующий расчет идет уже от 359 567,40: 359 567,4 * 154 * 1/300 *4,25% =7844,56 (уже на текущую дату (16.11 в ячейке N1) т.к. дальше идёт уже следующий этап, а этот не "закрыт" в ноль), соответственно ставка снижена и рассчитывается на дату N1, от которой и считается просрочка в днях: с 16.06 по 16.11)
3) Ошибка в формуле? Зачем сравнивать соседние порядковые номера этапов, если очевидно, что последующий >= предыдущему? Идея была в том, что бы в расчет попадали дни для расчета неустойки на "остаток", т.к. для расчета дней просрочки необходимо вычитать из последующей даты предыдущую в рамках одного этапа (что бы из 8ой строки по 5-му этапу не вычиталась дата из 7ой с 6ым этапом). 6-я строка: столбец К=52 дня (с 01.04 по 22.05), столбец О=24 дня (с 23.05 по 15.06).
Сделано криво, безусловно, но на большее моих скромных познаний, увы не хватает.
Контракт на выполнение работ по строительству. Контрактом предусмотрен график выполнения работ. Просрочка идёт начиная со следующего дня после предусмотренного контрактом.
1) Согласен, заголовок G1 не может быть в расчете, но хотелось подобрать некие универсальные формулы, что бы расчеты велись по одной формуле для всего листа. 1ый этап выполнен полностью, остаток 0, но предъявлен позднее установленного срока.
2) "Заголовок не точен, имеется ввиду "Удельная стоимость просроченного акта"?" тут немного сложнее, смотрите (опять же на примере 5го этапа). Сумма работ по 5му этапу (общая, по контракту) составляет 2262215 руб, работы должны были быть выполнены Подрядчиком в марте, но предъявлены только в мае частично на сумму 1 543 724, соответственно, на основную сумму надо начислить неустойку с просрочкой с 01.04. по 22.05, что составляет 52 дня: 2 262 215 * 52 *1/300 * 5,5% = 21 566,45 руб. Следующий акт предъявлен в июне на сумму 358923,6 руб. надо начислить неустойку на вот эту разницу (остаток, невыполненные работы которые не выполнялись определенный срок) и на дату следующего акта считаем: 2 262 215 - 1 543 724 = 718 491 руб. Считаем: 718 491 * 24 *1/300 * 5,5% = 3 161,36 руб.
Потому, как по одной строке (где общая стоимость работ по этапу контракта указана) необходимо выполнить два расчета, я сделал две группы столбцов, K, L, M в них я планировал сделать расчет неустойки с первого "закрытия" работ, а насчитывать сумму на остаток планировал в O, P, Q (т.к. в одной строке и 2 млн общей стоимости и 700 тысяч "остатка" и неустойку надо от каждой суммы посчитать на разные даты).
Следующий расчет идет уже от 359 567,40: 359 567,4 * 154 * 1/300 *4,25% =7844,56 (уже на текущую дату (16.11 в ячейке N1) т.к. дальше идёт уже следующий этап, а этот не "закрыт" в ноль), соответственно ставка снижена и рассчитывается на дату N1, от которой и считается просрочка в днях: с 16.06 по 16.11)
3) Ошибка в формуле? Зачем сравнивать соседние порядковые номера этапов, если очевидно, что последующий >= предыдущему? Идея была в том, что бы в расчет попадали дни для расчета неустойки на "остаток", т.к. для расчета дней просрочки необходимо вычитать из последующей даты предыдущую в рамках одного этапа (что бы из 8ой строки по 5-му этапу не вычиталась дата из 7ой с 6ым этапом). 6-я строка: столбец К=52 дня (с 01.04 по 22.05), столбец О=24 дня (с 23.05 по 15.06).
Сделано криво, безусловно, но на большее моих скромных познаний, увы не хватает.TVkills
Сообщение отредактировал TVkills - Среда, 25.11.2020, 16:40
потому что нет выполнения по этому этапу (физически не сделаны работы и/или нет акта выполненных работ) и неустойку надо в данном случае расчитывать от полной стоимости этапа на текущую дату (ключевая ставка и количество дней исходя из единственного числа в столбце N "текущая дата").
потому что нет выполнения по этому этапу (физически не сделаны работы и/или нет акта выполненных работ) и неустойку надо в данном случае расчитывать от полной стоимости этапа на текущую дату (ключевая ставка и количество дней исходя из единственного числа в столбце N "текущая дата").TVkills
расчет периода неустойки "На остаток", рассчитывающий разницу дат в случае признака отсутствия данных. Формулами заполнены оба столбца (заливка синим цветом), остальные формулы пока не оптимизировались. Просьба проверить базу данных на соответствие целям расчета.
Понятно. По этапу 4 в столбец N вводим
Код
=ЕСЛИ(ИЛИ(E5=0;F5=0;H5=0);"+";"")
- это признак "+" отсутствия данных, В столбец O вводим
расчет периода неустойки "На остаток", рассчитывающий разницу дат в случае признака отсутствия данных. Формулами заполнены оба столбца (заливка синим цветом), остальные формулы пока не оптимизировались. Просьба проверить базу данных на соответствие целям расчета.NikitaDvorets
Привел столбец L в соответствие с формулой в L6. 1ый этап закрыт полностью единственным актом (K, L, M), и там нет "остатков" (этап выполнен полностью, остаток 0), соответственно лучше сделать так, что бы в O, P, Q были нули (что бы если в суде расчеты показать не возникало вопросов), я пока сделал так, что бы в Q расчет не производился, если в G будет "0".
В столбце "O" немного не правильные условия, смотрите, по Вашей формуле считает дни просрочки, хотя срок исполнения не наступил, срок выполнения - ноябрь 2020 года, сейчас 26ое, а там по 320 дней везде (в конце таблицы). О6 и О7 - даты правильные. чуть выше них - ячейка О5 (выполнения нет, зато есть просрочка) стоит 320 дней, просрочка должна рассчитываться с 02.03.2020 по 16.11.2020 (значение из ячейки N1), и там точно не 320 будет, а примерно 259~
Привел столбец L в соответствие с формулой в L6. 1ый этап закрыт полностью единственным актом (K, L, M), и там нет "остатков" (этап выполнен полностью, остаток 0), соответственно лучше сделать так, что бы в O, P, Q были нули (что бы если в суде расчеты показать не возникало вопросов), я пока сделал так, что бы в Q расчет не производился, если в G будет "0".
В столбце "O" немного не правильные условия, смотрите, по Вашей формуле считает дни просрочки, хотя срок исполнения не наступил, срок выполнения - ноябрь 2020 года, сейчас 26ое, а там по 320 дней везде (в конце таблицы). О6 и О7 - даты правильные. чуть выше них - ячейка О5 (выполнения нет, зато есть просрочка) стоит 320 дней, просрочка должна рассчитываться с 02.03.2020 по 16.11.2020 (значение из ячейки N1), и там точно не 320 будет, а примерно 259~TVkills
NikitaDvorets, уже лучше =) 1ый акт закрыли, полностью, там просрочка примерно 109, 110 дней (если со след. дня считать) 2 строка (столбцы K L M), т.е. не должно идти расчетов на остаток его нет (G2=0) (в столбцах O, P, Q можно нули сделать?) Последняя строка ячейка O96, срок не наступил (стоит 178 дней)
NikitaDvorets, уже лучше =) 1ый акт закрыли, полностью, там просрочка примерно 109, 110 дней (если со след. дня считать) 2 строка (столбцы K L M), т.е. не должно идти расчетов на остаток его нет (G2=0) (в столбцах O, P, Q можно нули сделать?) Последняя строка ячейка O96, срок не наступил (стоит 178 дней)TVkills
Сообщение отредактировал TVkills - Четверг, 26.11.2020, 13:00
Попробую уточнить - какой/какие показатели (+наличие/отсутствие данных) говорят о том, что просрочка отсутствует? Нужно ли в этом случае ставить период просрочки = 0?
Попробую уточнить - какой/какие показатели (+наличие/отсутствие данных) говорят о том, что просрочка отсутствует? Нужно ли в этом случае ставить период просрочки = 0?NikitaDvorets
NikitaDvorets, срок по контракту меньше текущей даты. Пусть, например, там лучше будет минус (как выше, как индикатор контроля сроков, точнее остатка его запаса).
Январские каникулы 21 года забыл в столбик с выходными включить, кстати.
NikitaDvorets, срок по контракту меньше текущей даты. Пусть, например, там лучше будет минус (как выше, как индикатор контроля сроков, точнее остатка его запаса).
Январские каникулы 21 года забыл в столбик с выходными включить, кстати.TVkills
Сообщение отредактировал TVkills - Четверг, 26.11.2020, 14:47
Дни все-равно не правильно считает, 2, 3 строки например, 178 дней, там просрочка с марта по май, там ~80 дней должно быть, 180 это почти полгода, где-то ошибка.
можно я лучше заменю "-" на "0" вот так, что бы общую сумму считал, а то #ЗНАЧ! в Q мешается "итогам" под таблицей внизу указанного столбца.
Дни все-равно не правильно считает, 2, 3 строки например, 178 дней, там просрочка с марта по май, там ~80 дней должно быть, 180 это почти полгода, где-то ошибка.
На мой взгляд, нужны разные формулы расчета периода просрочки в зависимости от того, есть ли у договора "продолжение на следующую строчку или нет (т.е. всё умещается в одну строку). Если нужны разные формулы при нескольких строках одного договора, нужно прописать алгоритм расчета периода неустойки для различных строк.
Убрал "лишнее условие в анализе периода просрочки, посмотрите, насколько это "критично", считает правильно строки 2-3, но есть отрицательные значения:
На мой взгляд, нужны разные формулы расчета периода просрочки в зависимости от того, есть ли у договора "продолжение на следующую строчку или нет (т.е. всё умещается в одну строку). Если нужны разные формулы при нескольких строках одного договора, нужно прописать алгоритм расчета периода неустойки для различных строк.NikitaDvorets
NikitaDvorets, это все один договор, одного этапа, Вы имели в виду, очевидно.
Цитата
Ошибка в формуле? Зачем сравнивать соседние порядковые номера этапов, если очевидно, что последующий >= предыдущему?"
Цитата
На мой взгляд, нужны разные формулы расчета периода просрочки в зависимости от того, есть ли у договора "продолжение на следующую строчку или не...
вот примерно исходя из этой логики, я и пытался сравнивать номера этапов последующего с предыдущим Попутно вопрос назрел, почему условное форматирование слетать может? в 70й строке в столбце О если вставить
то этот "0" перестает выделяться серым, хотя в диапазоне правил находится... из-за того, что он вместо "значения" приобрел "текстовый" формат? Без кавычек нормально работает.
NikitaDvorets, это все один договор, одного этапа, Вы имели в виду, очевидно.
Цитата
Ошибка в формуле? Зачем сравнивать соседние порядковые номера этапов, если очевидно, что последующий >= предыдущему?"
Цитата
На мой взгляд, нужны разные формулы расчета периода просрочки в зависимости от того, есть ли у договора "продолжение на следующую строчку или не...
вот примерно исходя из этой логики, я и пытался сравнивать номера этапов последующего с предыдущим Попутно вопрос назрел, почему условное форматирование слетать может? в 70й строке в столбце О если вставить
то этот "0" перестает выделяться серым, хотя в диапазоне правил находится... из-за того, что он вместо "значения" приобрел "текстовый" формат? Без кавычек нормально работает.TVkills
Сообщение отредактировал TVkills - Четверг, 26.11.2020, 17:53
NikitaDvorets, он просрочку перестаёт таким образом считать, видно же, что в половине таблицы нули встали. Например 11 строка 8й этап.с 37-го и вниз до конца - там вообще наглядно.
NikitaDvorets, он просрочку перестаёт таким образом считать, видно же, что в половине таблицы нули встали. Например 11 строка 8й этап.с 37-го и вниз до конца - там вообще наглядно.TVkills
Да, вижу. Нужно понять полностью алгоритм расчетов. Просьба пояснить содержание полей: - Дата вх. - Рабочий +1 - Просрочено дней - чем отличается от поля [на остаток/ период просрочки] - Сумма. Дополнительно. Просьба пояснить - можно ли вместо второй и последующих строк по одному и тому же этапу продлить данные по одной строке? Если нет - то почему? Ответ на этот вопрос даст лучшее понимание проблемы.
Да, вижу. Нужно понять полностью алгоритм расчетов. Просьба пояснить содержание полей: - Дата вх. - Рабочий +1 - Просрочено дней - чем отличается от поля [на остаток/ период просрочки] - Сумма. Дополнительно. Просьба пояснить - можно ли вместо второй и последующих строк по одному и тому же этапу продлить данные по одной строке? Если нет - то почему? Ответ на этот вопрос даст лучшее понимание проблемы.NikitaDvorets
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
