Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
количество дней в месяце
 
#1
24.11.2012 20:59:35
Добрый вечер. Поискал по форуму не нашел ответ... :(  
Составляю график отпусков. Отпуска каждого сотрудник разбиваются на 2 части. График построил. Есть дата начала, окончания, длительности обеих частей отпуска. Необходимо посчитать количество дней отпуска всех сотрудников по месяцам. Т.е. посчитать потери рабочего времени на каждый месяц. Спасибо
 
 
 
#2
24.11.2012 21:26:14
у меня есть только варианты  
1) с 12-ю доп. столбцами  
2) VBA (макрос или UDF)  
 
скучные варианты :(  
одной формулой?.. хотел бы я посмотреть на это.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#3
24.11.2012 21:29:35
{quote}{login=ikki}{date=24.11.2012 09:26}{thema=}{post}у меня есть только варианты  
1) с 12-ю доп. столбцами  
2) VBA (макрос или UDF)  
 
скучные варианты :(  
одной формулой?.. хотел бы я посмотреть на это.{/post}{/quote}  
 
по 1 варианту как действовать?
 
 
 
#4
24.11.2012 21:34:16
напротив каждой фамилии - 12 столбцов (по одному на каждый месяц года)  
формулой в каждом столбце считаем кол-во дней этого месяца, попадающее в один из диапазонов отпусков.  
ну и суммы по столбцам.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#5
24.11.2012 21:35:48
{quote}{login=ikki}{date=24.11.2012 09:34}{thema=}{post}напротив каждой фамилии - 12 столбцов (по одному на каждый месяц года)  
формулой в каждом столбце считаем кол-во дней этого месяца, попадающее в один из диапазонов отпусков.  
ну и суммы по столбцам.{/post}{/quote}  
какие функции использовать?
 
 
 
#6
24.11.2012 21:42:14
мин(), макс(), сумм()  
 
см. файл.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
24.11.2012 21:44:20
пс. арифметику сделал как у вас - дата конца диапазона НЕ принадлежит отпуску.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
24.11.2012 21:45:25
{quote}{login=ikki}{date=24.11.2012 09:42}{thema=}{post}мин(), макс(), сумм()  
 
см. файл.{/post}{/quote}  
спасибо, разбираюсь... :)
 
 
 
#9
24.11.2012 22:37:07
А мне думается, что можно эту задачу решить формулами. Я попробую.  
Только вопрос. Например, в 16-ой строке у Вас начало 01, а конец 16 при длительности 15. Если 01 - первый день, а 16 - последний, то длительность д.б. 16.  
Или я что-то не понимаю.
 
 
 
#10
24.11.2012 22:42:53
Простите, не в 16, а в 7
 
 
 
#11
25.11.2012 10:34:19
{quote}{login=ikki}{date=24.11.2012 09:26}{thema=}{post}  
одной формулой?.. хотел бы я посмотреть на это.{/post}{/quote}  
Вариант без доп.столбцов (в желтых ячейках)
 
 
 
#12
25.11.2012 12:55:00
2 MCH  
Да, моя мысль бежала в том же направлении. Но Вы докрутили, а я нет! Вы молодец, а я - так себе. :о))  
Только вопрос с единицами на границах начала и конца отпуска остаются. У Петрова вторая часть отпуска, как следует из файла, заканчивается 06/10/2003. Но по всем побочным данным (даже если учитывать, что это понедельник) это первый день выхода на работу и тогда, подсчитанная в октябре 5 - верное значение. Я бы данные в заголовках столбцов по-другому обозвал. Но это уже как автору удобнее.  
И ещё: столбец КОНМЕСЯЦА, наверное, лишний. Эти данные можно "зашить" в конечную формулу столбца "С".
 
 
 
#13
25.11.2012 13:18:29
{quote}{login=iba2004}{date=25.11.2012 12:55}{thema=}{post}И ещё: столбец КОНМЕСЯЦА, наверное, лишний. Эти данные можно "зашить" в конечную формулу столбца "С".{/post}{/quote}  
Функция КОНМЕСЯЦА в 2003 офисе (а я предпочитаю работать в этой версии офиса) работает только с подключеным "Пакетом анализа", при этом она (функция) не может возвращать массивы (опять же в 2003 офисе), поэтому "зашить" ее в конечную формулу не получится.    
В 2007/2010/2013 Excel данных ограничений нет.  
Для того, чтобы формула работала в любой версии Excel независимо от наличия/отсутствия "Пакета анализа", можно избавится от функции КОНМЕСЯЦА, заменив ее на ДАТА  
Так например формулу:  
=КОНМЕСЯЦА(A1;0)  
можно заменить на:  
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;)
 
 
 
#14
25.11.2012 13:26:12
2 MCH  
Спасибо Вам за подробное объяснение.  
Не знал о таких нюансах. Не довелось сталкиваться.
 
 
 
#15
25.11.2012 13:54:20
Толко что протестировал в 2010, КОНМЕСАЦА не возвращает массив при записи:  
=КОНМЕСЯЦА(A1:A20;0)  
 
Придется подставлять костыль:  
=КОНМЕСЯЦА(ТРАНСП(A1:A20);0)  
или, для сохранения вертикальности массивы:  
=КОНМЕСЯЦА(ТРАНСП(ТРАНСП(A1:A20));0)  
 
В данной ситуации даже проще записать:  
=ДАТА(ГОД(A1:A20);МЕСЯЦ(A1:A20)+1;)
 
 
 
#16
25.11.2012 16:28:53
2 МСН  
Странно! У меня всё в "десятке" работает (20-ая строка). Или мы с Вами о разных вещах?
 
 
 
#17
25.11.2012 17:57:20
Я немного увлекся в пояснениях.    
В данной задаче КОНМЕСЯЦА можно использовать, т.к. на вход функции подаем только одно значение, но если в КОНМЕСЯЦА будет подаваться массив (при реализации других задач), то на выходе массив не получим
 
 
 
#18
25.11.2012 18:08:24
2 MCH  
понял. Спасибо!  
Тем не менее, я Вам благодарен.
 
 
 
#19
25.11.2012 19:10:28
{quote}{login=MCH}{date=25.11.2012 10:34}{thema=Re: }{post}{quote}{login=ikki}{date=24.11.2012 09:26}{thema=}{post}  
одной формулой?.. хотел бы я посмотреть на это.{/post}{/quote}  
Вариант без доп.столбцов (в желтых ячейках){/post}{/quote}  
мда... кто б сомневался!!! :)  
посмотрел. вроде бы понял, как сделано :)  
но вот повторить при необходимости... :(  
Михаил, всё равно большое спасибо. ;)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#20
25.11.2012 19:50:41
{quote}{login=ikki}{date=25.11.2012 07:10}{thema=Re: Re: }{post}  
посмотрел. вроде бы понял, как сделано :)  
но вот повторить при необходимости... :({/post}{/quote}  
Саш, а там ничего особо хитрого то и нет, за основу взял твою формулу, даже алгоритм не проверял, просто засунул ее в массив.  
Возможно схитрил с функцией ТЕКСТ для обнуления отрицательных значений, т.к. МАКС массив не может возвращать
 
 
 
#21
25.11.2012 19:58:23
ну вот об этом-то и речь :)  
в этом вся разница - я попробовал запихнуть МАКС() в формулу массива, получил облом - и успокоился.  
а до хитрости не додумался - да еще и не просто ТЕКСТ, а с вариантами "больше-меньше-равно".
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
Страницы: 1
Читают тему
Наверх