» Excel FAQ (часть 4)

Mint86
Цитата:

Да я жду с нетерпением...

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

Цитата:

Идея: у тебя получается 2,26. Т.е. просрочка 2 полных месяца. Дробная часть - это количество дней как бы в текущем месяце. Надо похимичить, если число месяцев больше 12.

Совершенно верно! Т.е. взносов всего должно быть три (кредит на три месяца), то получится что оплачено 26% первого взноса (первого месяца) и по 100% второго и третьего месяца. Выплата как бы идет с конца, т.е. если клиент принесет деньги, то сначала погашается остаток первого взноса (первого месяца), а потом второго и т.д.

ЗЫ насчет запутанности формулы я согласен

All
Кто мне напомнит (все время забываю). Есть три числа 20 12 2008. Была функция с параметром формат даты, что можно было получить 20.12.2008. Какая именно?

Mint86
Надо рассматривать 2 случая:
1) модуль Количество просроченных взносов <=1
Тут все просто Сегодня () - Дата погашения основной суммы по графику в течении месяца и есть количество дней просрочки.
2) модуль Количество просроченных взносов >1
Дата выдачи кредита:
День = День(А)
Месяц = МЕСЯЦ(A)-(ЦЕЛОЕ(-D)-ЦЕЛОЕ(-D/12)*12)
Год = Год (А) - ЦЕЛОЕ(-D/12)
эти 3 числа превращаем в дату.
Количество дней просрочки = Сегодня() - получившаяся дата.

Цитата:

Кто мне напомнит (все время забываю). Есть три числа 20 12 2008. Была функция с параметром формат даты, что можно было получить 20.12.2008. Какая именно?

Если три числа в трех ячейках то может так: =A1&"."&B1&"."&C1
Чет функцию не припомню...


Цитата:

Надо рассматривать 2 случая:
1) модуль Количество просроченных взносов <=1
Тут все просто Сегодня () - Дата погашения основной суммы по графику в течении месяца и есть количество дней просрочки.

И действительно

Тока вот насчет второго пункта... можно примерчик?

Mint86
Цитата:

Если три числа в трех ячейках то может так: =A1&"."&B1&"."&C1

Нет. Во первых сложность с январем. Функция Месяц вернет 1. А нужен 01. А в параметрах той функции было что-то типа "DD:ММ:YYYY". Сейчас поиском в версии для печати найду.

Цитата:

Тока вот насчет второго пункта... можно примерчик

Да без проблем.
71 строка: Дата погашения 10.12.2008 коэффициент -3,65. Меня на самом деле дробная часть этого коэффициента немного сбила в начале. Дело в том, что она не учитывется в расчетах.
День выдачи кредита =10
Месяц выдачи кредита = 12 - 3 полных = 9.
Год = 2008.
Задолженность: Сегодня - 10.09.2008

79 строка. Дата погашения 20.12.2008. Коэффициент 14,5. Вот тут я впервые у тебя увидел положительный коэффициет. Тогда мои формулы выше чуть неверны (я думал, что он всегда отрицательный) и везде надо использовать модуль
День выдачи кредита =20
Месяц выдачи кредита = 12 - 2 полных (ибо в году еще будет 12) = 10
Год = 2007.
Задолженность: Сегодня - 20.10.2007

в строке 20 у тебя имхо ошибка. Задолженность 26 дней.

в 125 строке вообще не понятно что. Ибо выплата 21.12.2008. А коэффициент -2 ровно. Но сегодня то 27.12.2008 Т.е. у него 2 месяца полных и еще 6 дней. В общем я выкладываю свои расчеты. http://www.filehoster.ru/files/by2951
Я там выделил все проблемные места.

All
C функцией я похоже наврал.

Mushroomer

не могу поверить что конструкция с "&" сработала

ДАТА(год;месяц;день)
Возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был Общий, результат будет отформатирован как дата.

crotoff
Цитата:

не могу поверить что конструкция с "&" сработала

А что ей будет? важно было 1 в 01 преобразовать

Цитата:

ДАТА(год;месяц;день) Возвращает целое число

Нужно было, чтобы возращалась дата.

Mushroomer

По поводу расчета:
Цитата:

71 строка: Дата погашения 10.12.2008 коэффициент -3,65. Меня на самом деле дробная часть этого коэффициента немного сбила в начале. Дело в том, что она не учитывется в расчетах.
День выдачи кредита =10
Месяц выдачи кредита = 12 - 3 полных = 9.
Год = 2008.
Задолженность: Сегодня - 10.09.2008

Расчет правильный!

Цитата:

79 строка. Дата погашения 20.12.2008. Коэффициент 14,5. Вот тут я впервые у тебя увидел положительный коэффициет. Тогда мои формулы выше чуть неверны (я думал, что он всегда отрицательный) и везде надо использовать модуль
День выдачи кредита =20
Месяц выдачи кредита = 12 - 2 полных (ибо в году еще будет 12) = 10
Год = 2007.
Задолженность: Сегодня - 20.10.2007

Моя ошибка не сказал, дело в том что положительные коэффициенты считать не надо, так как это переплата со стороны клиента.


Цитата:

в 125 строке вообще не понятно что. Ибо выплата 21.12.2008. А коэффициент -2 ровно. Но сегодня то 27.12.2008 Т.е. у него 2 месяца полных и еще 6 дней.

Там чуть-чуть не то. Там получается что не 2 месяца полных а два просроченных взноса (это разные вещи). Ведь первый взнос был просрочен 21.11.2008 года, а декабрьский 21.12.2008 (27.12.2008 - 21.12.2008=6). Т.е. 36 дней просрочки от первого просроченного платежа. Конечно согласен что отчет этот мягко говоря запутан, но что поделать этот отчет формирует банковская программа, которую руководство не планирует пока менять, так что приходится работать с тем что есть

ЗЫ ваш файл пока не смог скачать. В выходные в форум посмотреть не удалось а на работе доступ токо на slil.ru. Если возможно перезалить буду благодарен.
Да, и спасибо огромное

All
Переложите, пожалуйста, для Mint86 файл с http://www.filehoster.ru/files/by2951 на slil.ru . Сам я это сделать сейчас не могу.

Mushroomer
Готово

У меня не получается круговая диаграмма. При добавлении нового ряда, тот вставляется в заголовок, а при создании остальных вообще ничего не происходит, как будто стоит запрет на создании такой диаграммы. Что это такое? И как строится круговая диаграмма?

ignasio
Цитата:

Вот, если покажите, как прикрепить файл, то прикреплю

Выложи на любой файловый обменник (www.webfile.ru, ifolder.ru .....) и опубликуй ссылку в топике.

Как перевести время из десятичного числа в запись типа чч:мм?

Есть таблица с колонками километража и скорости. В 3-ей колонке вычисляется функцией время поездки, но записывается десятичным числом. А надо чч:мм.

Tom_Reedle
Не уверен что поможет. но попробуй формулу =ТЕКСТ(A1;"ЧЧ:ММ")
Выложи пример одной строки. Возможно надо просто временной формат ячейки выбрать.

Постарайтесь ответить на мой вопрос. Пожалуйста. Очень надо.

Mushroomer

не подошло

пример:

А B C D
12 60 0.2 ?

где А - длина в км
B - скорость в км/ч
С - результат деления А на B
а D - это С , но в чч:мм

Tom_Reedle
Цитата:

чч:мм

А может быть чч > 24 часов? И что должно в этом случае отобразиться? Самое обидное, я помню, что что-то подобное уже было. Вот только вспомнить не могу

Tom_Reedle
а если так:
формула в D1 =(A1/B1)/24 и в D установить формат ячеек "время" с выбором подходящего варианта (час:мин) (но в таком варианте не будет видно секунд, а если время будет 12 минут 55 секунд - отобразится, как 00:12. Может быть, лучше будет и секунды показать?)
А насчёт времени больше 24 часов в списке форматов "время" имеется и вариант, когда часы не сбрасываются в 0 после 24, а отображаются, как 25:00:00 и т.д.

Mushroomer

по-моему, я уже все опции отбражения времени попробовала. Здесь что-то другое нужно

VictorKos

а смысл? 12/60 / 24 = 0,0083
вместо 00:12

Tom_Reedle
=ОКРУГЛВНИЗ(C2;)&":"&ОКРУГЛ((C2-ОКРУГЛВНИЗ(C2;))*60;)
где в ячейке c2 - время в часах

или не, лучше так
=ОКРУГЛВНИЗ(C2;)&":"&ЕСЛИ(ОКРУГЛ((C2-ОКРУГЛВНИЗ(C2;))*60;)<10;0;"")&(ОКРУГЛ((C2-ОКРУГЛВНИЗ(C2;))*60;))
покрасивше будет смотреться

Цитата:

а смысл? 12/60 / 24 = 0,0083
вместо 00:12

0,0083 в формате "время" будет 11 минут 57 секунд (а 0,00833 - ровно 12 минут)

Если можете, расскажите: что делает пункт: Консолидация в меню Данные?

ignasio
Цитата:

расскажите: что делает пункт: Консолидация в меню Данные?

http://www.planetaexcel.ru/tip.php?aid=126

VictorKos

точно, заработало! спасибо огромное!

Не сочтите за труд, подскажите как одним действием подставить формулу всем строкам. А то проходить вручную по каждой строке, меняя А2 на А3 и т.д. как то грустно.

Tom_Reedle
Цитата:

Не сочтите за труд, подскажите как одним действием подставить формулу всем строкам. А то проходить вручную по каждой строке, меняя А2 на А3 и т.д. как то грустно.

кошмар. Это одно из базовых действий в Excel.
1) Встань на ячейку с готовой формулой
2) В правом нижнем углу прямоугольника ячейки появится черная точка.
3) поднеси к этой точке мышку, до появления вместо точки крестика
4) как увидишь крестик, нажимаешь левую кнопку мыши и (не отпуская кнопку мыши) перемещаешь мышь вниз (или влево) по столбцу (по строке).

Можно ли снять защиту с защищенных ячеек как-то? Срочно интересует данный вопрос.

Fraerok
Можно, таких программ полно в нете, ищи. Вообще защита листа мягко говоря "непрочная", можно и специально написанным макросом сбить.

Цитата:

Fraerok
Можно, таких программ полно в нете, ищи. Вообще защита листа мягко говоря "непрочная", можно и специально написанным макросом сбить.

дада! в соседнем топике про макросы такой макрос в шапке висит - защиту снимает шустро;)

Добавлено:

Цитата:

кошмар. Это одно из базовых действий в Excel.
1) Встань на ячейку с готовой формулой
2) В правом нижнем углу прямоугольника появится черная точка.
3) поднеси к этой точке мышку, до появления вместо точки крестика
4) как увидишь крестик, нажимаешь левую кнопку мыши и (не отуская кнопку мыши) перемещаешь мышь вниз (или влево) по столбцу (по строке).

плюс пиццот! добавим в шапку?!

Mushroomer

спасибо!

ага, кошмар ну, я просто еще даже не новичок в экселе...
кстати, буду очень признательна за рекомендацию толкового пособия/учебника в сети по экселю