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

Вход

Регистрация

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

 

= Мир MS Excel/количество дней для расчёта отпускных - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » количество дней для расчёта отпускных (Формулы/Formulas)
количество дней для расчёта отпускных
Никанор Дата: Пятница, 05.01.2018, 14:00 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Полное название темы, которое не поместилось в заголовок. Вот полное название темы:

Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9.

Расчёт месяцев и дней для расчёта отпускных, если работает меньше 12 месяцев и устроен не с первого числа месяца

Помогите, пожалуйста, изменить – написать формулы в диапазон ячеек:
B20:В31 – это название месяцев;
D20:D31 – это количество дней для расчёта отпускных – календарные дни минус праздничные.


Условия для формул в диапазон ячеек B20:В31
Ячейка D9 – дата, когда сотрудник принят (устроен на работу);
Ячейка Е15 срок отпуска, – с какого числа включительно начинается отпуск.

1-ое условие: если сотрудник работал больше года, то в диапазон ячеек B31 заполняются все 12 месяцев. В самую нижнюю ячейку B31 заполняется месяц, который предшествует месяцу, указанному в ячейке Е15.
2-ое условие: если сотрудник работал меньше 12-ти месяцев, то в ячейку B31 заполняется месяц, который предшествует месяцу, указанному в ячейке Е15, а в ячейки выше заполняются месяцами, которые предшествуют месяцу, указанному в ячейке B31.
3-ее условие: если сотрудник работал меньше 12-ти месяцев (меньше года) – месяцы в которых сотрудник не работал пустые – чистые.

Сейчас формулы в диапазоне ячеек B20:В31 считают правильно для расчёта больничных. Так как для расчёта больничных заполняются только полные месяцы работы.
Сейчас формулы правильно считают в следующих случаях:
1. Если сотрудник работал больше 12-ти месяцев (больше года) – если в ячейке Е9 указано что работал больше 1 года и если в ячейке В10 указано больше 12 месяцев;
2. Если сотрудник проработал меньше 12 месяцев (меньше года) и устроился первого числа месяца.

А неправильно сейчас формулы в диапазоне ячеек B20:В31 считают в следующем случае:
1. Если сотрудник работает меньше 12-ти месяцев (меньше года) и устроился не первого числа – со 2-го числа по последнее число месяца.

Я для удобства восприятия добавил рисунок с условиями для формул диапазона B20:В31

Условия для формул в диапазон ячеек D20:D31 – это диапазон ячеек для расчёта дней отпуска. В этом столбце сейчас формулы выполняют условия:
1. Если пустая левая ячейка столбца В (название месяца) – то и пустая ячейка столбца D.
2. Если заполнена ячейка столбца В (название месяца) – то и заполнена ячейка столбца D и там «работает» формула количество календарных дней месяца слева минус количество дней праздников, указанных в таблице для расчёта количества дней отпуска за 2016 год в диапазоне ячеек I20:O31 и в таблице за 2017 год в диапазоне ячеек R20:X31.

Я добавил таблицу для расчёта количества дней отпуска за 2018 год в диапазоне ячеек Z20:AF31.

1-ое условие для формул, которые нужны в диапазон D20:D31 – добавить, что бы считало с учётом таблицы за 2018 год.
2-ое условие если возможно: если сотрудник устроился не первого числа месяца, а потом в этом месяце будет праздничный день, что бы количество дней для расчёта отпуска считало с условием даты приёма.
Пример для 2-го условия: сотрудник устроился 5 января 2018 года. Формула должна считать в диапазоне D20:D31 должна считать количество дней января минус 1 день.
Без праздника результат должен быть календарных дней: 31 минус 5 + 1 = 27 дней.
А для таблицы расчёта дней отпусков 27 дней минус 1 праздничный день = 26 дней.
К сообщению приложен файл: __________12___.xlsx (23.3 Kb) · 5572728.jpg (70.9 Kb)


Сообщение отредактировал Никанор - Пятница, 05.01.2018, 16:01
 
Ответить
СообщениеПолное название темы, которое не поместилось в заголовок. Вот полное название темы:

Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9.

Расчёт месяцев и дней для расчёта отпускных, если работает меньше 12 месяцев и устроен не с первого числа месяца

Помогите, пожалуйста, изменить – написать формулы в диапазон ячеек:
B20:В31 – это название месяцев;
D20:D31 – это количество дней для расчёта отпускных – календарные дни минус праздничные.


Условия для формул в диапазон ячеек B20:В31
Ячейка D9 – дата, когда сотрудник принят (устроен на работу);
Ячейка Е15 срок отпуска, – с какого числа включительно начинается отпуск.

1-ое условие: если сотрудник работал больше года, то в диапазон ячеек B31 заполняются все 12 месяцев. В самую нижнюю ячейку B31 заполняется месяц, который предшествует месяцу, указанному в ячейке Е15.
2-ое условие: если сотрудник работал меньше 12-ти месяцев, то в ячейку B31 заполняется месяц, который предшествует месяцу, указанному в ячейке Е15, а в ячейки выше заполняются месяцами, которые предшествуют месяцу, указанному в ячейке B31.
3-ее условие: если сотрудник работал меньше 12-ти месяцев (меньше года) – месяцы в которых сотрудник не работал пустые – чистые.

Сейчас формулы в диапазоне ячеек B20:В31 считают правильно для расчёта больничных. Так как для расчёта больничных заполняются только полные месяцы работы.
Сейчас формулы правильно считают в следующих случаях:
1. Если сотрудник работал больше 12-ти месяцев (больше года) – если в ячейке Е9 указано что работал больше 1 года и если в ячейке В10 указано больше 12 месяцев;
2. Если сотрудник проработал меньше 12 месяцев (меньше года) и устроился первого числа месяца.

А неправильно сейчас формулы в диапазоне ячеек B20:В31 считают в следующем случае:
1. Если сотрудник работает меньше 12-ти месяцев (меньше года) и устроился не первого числа – со 2-го числа по последнее число месяца.

Я для удобства восприятия добавил рисунок с условиями для формул диапазона B20:В31

Условия для формул в диапазон ячеек D20:D31 – это диапазон ячеек для расчёта дней отпуска. В этом столбце сейчас формулы выполняют условия:
1. Если пустая левая ячейка столбца В (название месяца) – то и пустая ячейка столбца D.
2. Если заполнена ячейка столбца В (название месяца) – то и заполнена ячейка столбца D и там «работает» формула количество календарных дней месяца слева минус количество дней праздников, указанных в таблице для расчёта количества дней отпуска за 2016 год в диапазоне ячеек I20:O31 и в таблице за 2017 год в диапазоне ячеек R20:X31.

Я добавил таблицу для расчёта количества дней отпуска за 2018 год в диапазоне ячеек Z20:AF31.

1-ое условие для формул, которые нужны в диапазон D20:D31 – добавить, что бы считало с учётом таблицы за 2018 год.
2-ое условие если возможно: если сотрудник устроился не первого числа месяца, а потом в этом месяце будет праздничный день, что бы количество дней для расчёта отпуска считало с условием даты приёма.
Пример для 2-го условия: сотрудник устроился 5 января 2018 года. Формула должна считать в диапазоне D20:D31 должна считать количество дней января минус 1 день.
Без праздника результат должен быть календарных дней: 31 минус 5 + 1 = 27 дней.
А для таблицы расчёта дней отпусков 27 дней минус 1 праздничный день = 26 дней.

Автор - Никанор
Дата добавления - 05.01.2018 в 14:00
Никанор Дата: Воскресенье, 07.01.2018, 23:52 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Наверное, нет ни одного ответа на мой вопрос, потому что я очень много написал в условиях вопроса.
Ниже напишу очень кратко.

Вот похожий мой вопрос, с этим же файлом, на который был дан правильный ответ:
К дате прибавить ячейку, в которой формула с текстом. Вот ссылка этого вопроса: http://www.excelworld.ru/forum/2-36511-2

Если очень коротко, то в файле _11112_.xlsx формула
Код
=ЕСЛИ(B20="";"";ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$I$20:$O$31;7;);ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$R$20:$X$31;7;)))
правильно считает.
Единственно, что я добавил таблицу для расчёта количества дней отпуска за 2018 год в диапазоне ячеек Z20:AF31.

1-ое условие для формулы, которые нужны в диапазон B20:B31 – добавить, что бы считало с учётом таблицы за 2018 год. То есть надо как то в формулу, которая написана выше, добавить диапазон ячеек Z20:AF31.

2-ое условие для формулы, которые нужны в диапазон B20:B31 – если сотрудник работал меньше 12-ти месяцев (ячейка D9 – дата, когда сотрудник принят на работу, то первый месяц в диапазоне ячеек B20:В31 равняется месяцу, указанному в ячейке D9.

Ячейка D9 – дата, когда сотрудник принят (устроен на работу);
Ячейка Е15 срок отпуска, – с какого числа включительно начинается отпуск.

Если невозможно написать формулу что бы выполнялись два условия, то хотя бы что бы выполнялось 1-ое условие.


Сообщение отредактировал Никанор - Воскресенье, 07.01.2018, 23:58
 
Ответить
СообщениеНаверное, нет ни одного ответа на мой вопрос, потому что я очень много написал в условиях вопроса.
Ниже напишу очень кратко.

Вот похожий мой вопрос, с этим же файлом, на который был дан правильный ответ:
К дате прибавить ячейку, в которой формула с текстом. Вот ссылка этого вопроса: http://www.excelworld.ru/forum/2-36511-2

Если очень коротко, то в файле _11112_.xlsx формула
Код
=ЕСЛИ(B20="";"";ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$I$20:$O$31;7;);ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$R$20:$X$31;7;)))
правильно считает.
Единственно, что я добавил таблицу для расчёта количества дней отпуска за 2018 год в диапазоне ячеек Z20:AF31.

1-ое условие для формулы, которые нужны в диапазон B20:B31 – добавить, что бы считало с учётом таблицы за 2018 год. То есть надо как то в формулу, которая написана выше, добавить диапазон ячеек Z20:AF31.

2-ое условие для формулы, которые нужны в диапазон B20:B31 – если сотрудник работал меньше 12-ти месяцев (ячейка D9 – дата, когда сотрудник принят на работу, то первый месяц в диапазоне ячеек B20:В31 равняется месяцу, указанному в ячейке D9.

Ячейка D9 – дата, когда сотрудник принят (устроен на работу);
Ячейка Е15 срок отпуска, – с какого числа включительно начинается отпуск.

Если невозможно написать формулу что бы выполнялись два условия, то хотя бы что бы выполнялось 1-ое условие.

Автор - Никанор
Дата добавления - 07.01.2018 в 23:52
gling Дата: Понедельник, 08.01.2018, 01:06 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Действительно, букаф очень много. Но глядя на формулу можно предположить, что если все года разместить сверху вниз (в столбцы I:O), то можно убрать в формук ле ЕСЛИОШИБКА, но нужно тогда увеличить диапазон. Примерно так
Код
=ЕСЛИ(B20="";"";ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");I:O;7;))
А месяц можно определить так
Код
=ЕСЛИ(ДАТАМЕС(D9;12)>E15;"Месяц - "&ТЕКСТ(D9;"ММММ");"Больше 12 месяцев")


ЯД-41001506838083

Сообщение отредактировал gling - Понедельник, 08.01.2018, 01:38
 
Ответить
СообщениеДействительно, букаф очень много. Но глядя на формулу можно предположить, что если все года разместить сверху вниз (в столбцы I:O), то можно убрать в формук ле ЕСЛИОШИБКА, но нужно тогда увеличить диапазон. Примерно так
Код
=ЕСЛИ(B20="";"";ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");I:O;7;))
А месяц можно определить так
Код
=ЕСЛИ(ДАТАМЕС(D9;12)>E15;"Месяц - "&ТЕКСТ(D9;"ММММ");"Больше 12 месяцев")

Автор - gling
Дата добавления - 08.01.2018 в 01:06
Никанор Дата: Вторник, 09.01.2018, 15:36 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
gling, Здравствуйте!
Извините что отвечаю не сразу – не было доступа к оригиналу файла и к Интернету что бы проверить.
Спасибо за ответ.
Но эти две формулы "не работают".
Результат #ЗНАЧ!
Прикрепляю в файле эти "неработающие" формулы.
К сообщению приложен файл: __-__________12.xlsx (23.1 Kb)


Сообщение отредактировал Никанор - Вторник, 09.01.2018, 15:37
 
Ответить
Сообщениеgling, Здравствуйте!
Извините что отвечаю не сразу – не было доступа к оригиналу файла и к Интернету что бы проверить.
Спасибо за ответ.
Но эти две формулы "не работают".
Результат #ЗНАЧ!
Прикрепляю в файле эти "неработающие" формулы.

Автор - Никанор
Дата добавления - 09.01.2018 в 15:36
Che79 Дата: Вторник, 09.01.2018, 15:55 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Никанор, проверьте Лист 1-1, может , так нужно? Вам же написали
все года разместить сверху вниз (в столбцы I:O)
. Вы это на реальном файле сделали?
К сообщению приложен файл: _321123_.xlsx (34.4 Kb)


Делай нормально и будет нормально!
 
Ответить
СообщениеНиканор, проверьте Лист 1-1, может , так нужно? Вам же написали
все года разместить сверху вниз (в столбцы I:O)
. Вы это на реальном файле сделали?

Автор - Che79
Дата добавления - 09.01.2018 в 15:55
Никанор Дата: Вторник, 09.01.2018, 16:39 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, Спасибо за быстрый ответ!
Вы это на реальном файле сделали?
Отвечаю: Да на реальном.
Вышенаписанные формулы дают неправильный результат.
В этом примере название первого месяца в диапазоне ячеек B20:В31 должен быть апрель 2017 – так как сотрудник проработал меньше 12 месяцев и устроился в апреле 2017, а последний ноябрь 2017 – так как в ячейке Е15 месяц декабрь 2017.
Последний месяц в ячейке В31 должен быть месяц, который предшествует месяцу, указанному в ячейке Е15.

И мне было бы удобнее, если бы таблицы размещать не сверху вниз, а слева – направо, как в оригинале файла.

Мне кажется как-то надо добавить в формулы оригинала – первоначалный файл в диапазон ячеек D20:D31 диапазон ячеек Z20:AE31 данные за 2018 год.


Сообщение отредактировал Никанор - Вторник, 09.01.2018, 16:52
 
Ответить
СообщениеChe79, Спасибо за быстрый ответ!
Вы это на реальном файле сделали?
Отвечаю: Да на реальном.
Вышенаписанные формулы дают неправильный результат.
В этом примере название первого месяца в диапазоне ячеек B20:В31 должен быть апрель 2017 – так как сотрудник проработал меньше 12 месяцев и устроился в апреле 2017, а последний ноябрь 2017 – так как в ячейке Е15 месяц декабрь 2017.
Последний месяц в ячейке В31 должен быть месяц, который предшествует месяцу, указанному в ячейке Е15.

И мне было бы удобнее, если бы таблицы размещать не сверху вниз, а слева – направо, как в оригинале файла.

Мне кажется как-то надо добавить в формулы оригинала – первоначалный файл в диапазон ячеек D20:D31 диапазон ячеек Z20:AE31 данные за 2018 год.

Автор - Никанор
Дата добавления - 09.01.2018 в 16:39
Che79 Дата: Вторник, 09.01.2018, 17:28 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Цитата Никанор, 09.01.2018 в 16:39, в сообщении № 6 ()
мне было бы удобнее, если бы таблицы размещать не сверху вниз, а слева – направо
Тогда, для D20 например так, с учетом трёх таблиц по годам
Код
=ЕСЛИ(B20="";"";ЕСЛИОШИБКА(ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$I$20:$O$31;7;);ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$R$20:$X$31;7;));ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$Z$20:$AF$31;7;)))

По поводу первого месяца в B20 - не обещаю, что быстро вернусь с правильным ответом. Пока мало что понял. Тут, наверное, проще дождаться вариантов от других помогающих.


Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата Никанор, 09.01.2018 в 16:39, в сообщении № 6 ()
мне было бы удобнее, если бы таблицы размещать не сверху вниз, а слева – направо
Тогда, для D20 например так, с учетом трёх таблиц по годам
Код
=ЕСЛИ(B20="";"";ЕСЛИОШИБКА(ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$I$20:$O$31;7;);ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$R$20:$X$31;7;));ДЕНЬ(КОНМЕСЯЦА(B20;0))-ВПР(ТЕКСТ(B20;"ММММ ГГГГ");$Z$20:$AF$31;7;)))

По поводу первого месяца в B20 - не обещаю, что быстро вернусь с правильным ответом. Пока мало что понял. Тут, наверное, проще дождаться вариантов от других помогающих.

Автор - Che79
Дата добавления - 09.01.2018 в 17:28
gling Дата: Вторник, 09.01.2018, 22:17 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Что то нарисовал в файле, но не знаю это ли надо. Таблицы с праздниками чуть изменил.
Цитата
Последний месяц в ячейке В31 должен быть месяц, который предшествует месяцу, указанному в ячейке Е15.
Это почти как на листе Вар2. Можно сдвинуть всё на строку ниже, если это критично, но не понятно.
К сообщению приложен файл: -12.xlsx (34.7 Kb)


ЯД-41001506838083

Сообщение отредактировал gling - Вторник, 09.01.2018, 22:34
 
Ответить
СообщениеЧто то нарисовал в файле, но не знаю это ли надо. Таблицы с праздниками чуть изменил.
Цитата
Последний месяц в ячейке В31 должен быть месяц, который предшествует месяцу, указанному в ячейке Е15.
Это почти как на листе Вар2. Можно сдвинуть всё на строку ниже, если это критично, но не понятно.

Автор - gling
Дата добавления - 09.01.2018 в 22:17
Никанор Дата: Среда, 10.01.2018, 09:34 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
gling, Здравствуйте!
Спасибо за ответ – но неправильно. Если изменить данные в ячейке Е15 то в ячейке В31 месяц не меняется и результат неправильный.
 
Ответить
Сообщениеgling, Здравствуйте!
Спасибо за ответ – но неправильно. Если изменить данные в ячейке Е15 то в ячейке В31 месяц не меняется и результат неправильный.

Автор - Никанор
Дата добавления - 10.01.2018 в 09:34
Никанор Дата: Среда, 10.01.2018, 10:15 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, Здравствуйте!
Спасибо за ответ! Всё верно, так как я и хотел. Я проверил несколько вариантов – Ваша формула для диапазона ячеек D20:D31 правильно считает.

По поводу первого месяца в B20 - не обещаю, что быстро вернусь с правильным ответом. Пока мало что понял.

Попробую сформулировать лучше, конкретнее и понятнее условия для диапазона ячеек B20:В31.
Условие для В31 – всегда название месяца в ячейке В31 – месяц, который предшествует месяцу, указанному в ячейке Е15.
Условия для ячеек B20:В31:
1 условие: если разница месяцев в ячейке В10 =РАЗНДАТ(D9;E15;"M") больше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" больше 1 года, то в диапазон ячеек B20:В30 заполняются 11 месяцев, что предшествуют месяцу в ячейке В31. И все ячейки B20:В31 заполнены.
2 условие: если разница месяцев в ячейке В10 =РАЗНДАТ(D9;E15;"M") меньше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" меньше 1 года, то в диапазон ячеек B20:В30 первым заполняется месяц, который указан в ячейке в ячейке Е15. В этом случае количество заполненных месяцев (ячеек) B20:В30 равняется: первый месяц = ячейке Е15, а последний месяц = месяц, который предшествует месяцу, указанному в ячейке Е15.

Прикрепляю файл с изменённой формулой для столбца D, которую написал Che79
К сообщению приложен файл: ____D__________.xlsx (23.3 Kb)
 
Ответить
СообщениеChe79, Здравствуйте!
Спасибо за ответ! Всё верно, так как я и хотел. Я проверил несколько вариантов – Ваша формула для диапазона ячеек D20:D31 правильно считает.

По поводу первого месяца в B20 - не обещаю, что быстро вернусь с правильным ответом. Пока мало что понял.

Попробую сформулировать лучше, конкретнее и понятнее условия для диапазона ячеек B20:В31.
Условие для В31 – всегда название месяца в ячейке В31 – месяц, который предшествует месяцу, указанному в ячейке Е15.
Условия для ячеек B20:В31:
1 условие: если разница месяцев в ячейке В10 =РАЗНДАТ(D9;E15;"M") больше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" больше 1 года, то в диапазон ячеек B20:В30 заполняются 11 месяцев, что предшествуют месяцу в ячейке В31. И все ячейки B20:В31 заполнены.
2 условие: если разница месяцев в ячейке В10 =РАЗНДАТ(D9;E15;"M") меньше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" меньше 1 года, то в диапазон ячеек B20:В30 первым заполняется месяц, который указан в ячейке в ячейке Е15. В этом случае количество заполненных месяцев (ячеек) B20:В30 равняется: первый месяц = ячейке Е15, а последний месяц = месяц, который предшествует месяцу, указанному в ячейке Е15.

Прикрепляю файл с изменённой формулой для столбца D, которую написал Che79

Автор - Никанор
Дата добавления - 10.01.2018 в 10:15
Che79 Дата: Среда, 10.01.2018, 12:30 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Никанор, попробовал сделать чётко по Вашему описанию из поста #10. Получилось так. Для B20
Код
=ЕСЛИ(ИЛИ($B$10>12;РАЗНДАТ($D$9;$E$15;"ym")>1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-13);ЕСЛИ(ИЛИ($B$10<12;РАЗНДАТ($D$9;$E$15;"ym")<1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-1)))
Проверьте на реальном файле. У меня же два встречных вопроса
1) Что делать, если РАЗНДАТ() = 1 год (12 месяцев)? У Вас этого условия просто нет. У Вас только либо >, либо <.
2) Как то, что мы сейчас пытаемся сделать, соотносится с названием темы
Цитата Никанор, 05.01.2018 в 14:00, в сообщении № 1 ()
Если дата в ячейке не первое число результат тот же месяц
?


Делай нормально и будет нормально!
 
Ответить
СообщениеНиканор, попробовал сделать чётко по Вашему описанию из поста #10. Получилось так. Для B20
Код
=ЕСЛИ(ИЛИ($B$10>12;РАЗНДАТ($D$9;$E$15;"ym")>1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-13);ЕСЛИ(ИЛИ($B$10<12;РАЗНДАТ($D$9;$E$15;"ym")<1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-1)))
Проверьте на реальном файле. У меня же два встречных вопроса
1) Что делать, если РАЗНДАТ() = 1 год (12 месяцев)? У Вас этого условия просто нет. У Вас только либо >, либо <.
2) Как то, что мы сейчас пытаемся сделать, соотносится с названием темы
Цитата Никанор, 05.01.2018 в 14:00, в сообщении № 1 ()
Если дата в ячейке не первое число результат тот же месяц
?

Автор - Che79
Дата добавления - 10.01.2018 в 12:30
Никанор Дата: Среда, 10.01.2018, 19:43 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, Спасибо, но эта формула "не работает". Я проверил на реальном файле. Реальный файл был в моём сообщении № 10. Файл: ____D__________.xlsx(23Kb).
Объясняю что неправильно на реальном файле:
в этом примере в ячейке D9 стоит 3 апреля 2017 г.
в ячейке B10 указано что работает 8 полных месяцев, и значит в диапазоне ячеек B20:В31 первый месяц должен быть апрель 2017, а вышенаписанная формула даёт результат в ячейку B20 декабрь 2016. Этого не должно быть, так как сотрудник устроился 3 апреля – а в декабре 2016 он не работал.
А если эту формулу "протянуть" вниз в ячейке B21 получается январь 2017; в ячейке B22 февраль 2017; в ячейке B23 март 2017, в вышеназванных месяцах сотрудник не работал и этих месяцев не должно быть.

1) Что делать, если РАЗНДАТ() = 1 год (12 месяцев)? У Вас этого условия просто нет. У Вас только либо >, либо <.

Отвечаю: совершенно правильный вопрос, может суть именно в нём. Я не чётко указал условия. Правильно было бы написать: ЕСЛИ больше или равно 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" больше или равно 1 год, то в диапазон ячеек B20:В30 заполняются 11 месяцев, что предшествуют месяцу в ячейке В31. И все ячейки B20:В31 заполнены.
А ЕСЛИ меньше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" меньше 1 года, то в диапазон ячеек B20:В30 первым заполняется месяц, который указан в ячейке в ячейке Е15. В этом случае количество заполненных месяцев (ячеек) B20:В30 равняется: первый месяц = ячейке Е15, а последний месяц в ячейку В31 = месяц, который предшествует месяцу, указанному в ячейке Е15.

2) Как то, что мы сейчас пытаемся сделать, соотносится с названием темы

Отвечаю: Название темы: Если дата в ячейке не первое число результат тот же месяц. А ниже в условиях вопроса я дописал: Полное название темы, которое не поместилось в заголовок. Вот полное название темы: Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9.
Именно это мы и пытаемся делать – первоначальная формула правильно считала всегда, за исключением того случая, если работал меньше 12 месяцев (или меньше года) и дата в ячейке D9 не первое число месяца. Это полностью соответствует названию темы.
А если РАЗНДАТ() было меньше < 1 года (меньше <12 месяцев) в ячейке D9 было первое число формула считала правильно.
 
Ответить
СообщениеChe79, Спасибо, но эта формула "не работает". Я проверил на реальном файле. Реальный файл был в моём сообщении № 10. Файл: ____D__________.xlsx(23Kb).
Объясняю что неправильно на реальном файле:
в этом примере в ячейке D9 стоит 3 апреля 2017 г.
в ячейке B10 указано что работает 8 полных месяцев, и значит в диапазоне ячеек B20:В31 первый месяц должен быть апрель 2017, а вышенаписанная формула даёт результат в ячейку B20 декабрь 2016. Этого не должно быть, так как сотрудник устроился 3 апреля – а в декабре 2016 он не работал.
А если эту формулу "протянуть" вниз в ячейке B21 получается январь 2017; в ячейке B22 февраль 2017; в ячейке B23 март 2017, в вышеназванных месяцах сотрудник не работал и этих месяцев не должно быть.

1) Что делать, если РАЗНДАТ() = 1 год (12 месяцев)? У Вас этого условия просто нет. У Вас только либо >, либо <.

Отвечаю: совершенно правильный вопрос, может суть именно в нём. Я не чётко указал условия. Правильно было бы написать: ЕСЛИ больше или равно 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" больше или равно 1 год, то в диапазон ячеек B20:В30 заполняются 11 месяцев, что предшествуют месяцу в ячейке В31. И все ячейки B20:В31 заполнены.
А ЕСЛИ меньше 12 месяцев или в ячейке Е9 РАЗНДАТ(D9;E15;"YM" меньше 1 года, то в диапазон ячеек B20:В30 первым заполняется месяц, который указан в ячейке в ячейке Е15. В этом случае количество заполненных месяцев (ячеек) B20:В30 равняется: первый месяц = ячейке Е15, а последний месяц в ячейку В31 = месяц, который предшествует месяцу, указанному в ячейке Е15.

2) Как то, что мы сейчас пытаемся сделать, соотносится с названием темы

Отвечаю: Название темы: Если дата в ячейке не первое число результат тот же месяц. А ниже в условиях вопроса я дописал: Полное название темы, которое не поместилось в заголовок. Вот полное название темы: Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9.
Именно это мы и пытаемся делать – первоначальная формула правильно считала всегда, за исключением того случая, если работал меньше 12 месяцев (или меньше года) и дата в ячейке D9 не первое число месяца. Это полностью соответствует названию темы.
А если РАЗНДАТ() было меньше < 1 года (меньше <12 месяцев) в ячейке D9 было первое число формула считала правильно.

Автор - Никанор
Дата добавления - 10.01.2018 в 19:43
gling Дата: Среда, 10.01.2018, 19:56 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
А может так надо? Вар2.
К сообщению приложен файл: 9222373.xlsx (34.8 Kb)


ЯД-41001506838083
 
Ответить
СообщениеА может так надо? Вар2.

Автор - gling
Дата добавления - 10.01.2018 в 19:56
Pelena Дата: Среда, 10.01.2018, 20:00 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата Никанор, 10.01.2018 в 19:43, в сообщении № 12 ()
полное название темы

На мой взгляд, тема про определение количества дней для расчёта отпускных
Не?


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата Никанор, 10.01.2018 в 19:43, в сообщении № 12 ()
полное название темы

На мой взгляд, тема про определение количества дней для расчёта отпускных
Не?

Автор - Pelena
Дата добавления - 10.01.2018 в 20:00
Никанор Дата: Среда, 10.01.2018, 20:13 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Pelena, Здравствуйте!
тема про определение количества дней для расчёта отпускных
Не?

Не совсем так. Если в общем, то тему можно назвать: период для расчёта отпускных. Или количество дней для расчёта отпускных (Как Вы пишите).
А если конкретизировать, то мне нужна формула с условием: Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9. Именно это я и указал в теме, только кратко. Так что думаю, название темы я назвал конкретно и правильно.
 
Ответить
СообщениеPelena, Здравствуйте!
тема про определение количества дней для расчёта отпускных
Не?

Не совсем так. Если в общем, то тему можно назвать: период для расчёта отпускных. Или количество дней для расчёта отпускных (Как Вы пишите).
А если конкретизировать, то мне нужна формула с условием: Если дата в ячейке D9 не первое число месяца и работал меньше 12-ти месяцев – результат в диапазоне ячеек B20:В31 – месяц, указанный в ячейке D9. Именно это я и указал в теме, только кратко. Так что думаю, название темы я назвал конкретно и правильно.

Автор - Никанор
Дата добавления - 10.01.2018 в 20:13
Che79 Дата: Среда, 10.01.2018, 20:17 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Никанор, а, может, так? Для B20
Код
=ЕСЛИ(ИЛИ($B$10>=12;РАЗНДАТ($D$9;$E$15;"ym")>=1);ЕСЛИ(КОНМЕСЯЦА($E$15;СТРОКА($A1)-13)<КОНМЕСЯЦА($D$9;0);"";КОНМЕСЯЦА($E$15;СТРОКА($A1)-13));ЕСЛИ(ИЛИ($B$10<12;РАЗНДАТ($D$9;$E$15;"ym")<1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-1)))
К сообщению приложен файл: 180110.xlsx (24.2 Kb)


Делай нормально и будет нормально!
 
Ответить
СообщениеНиканор, а, может, так? Для B20
Код
=ЕСЛИ(ИЛИ($B$10>=12;РАЗНДАТ($D$9;$E$15;"ym")>=1);ЕСЛИ(КОНМЕСЯЦА($E$15;СТРОКА($A1)-13)<КОНМЕСЯЦА($D$9;0);"";КОНМЕСЯЦА($E$15;СТРОКА($A1)-13));ЕСЛИ(ИЛИ($B$10<12;РАЗНДАТ($D$9;$E$15;"ym")<1);КОНМЕСЯЦА($E$15;СТРОКА($A1)-1)))

Автор - Che79
Дата добавления - 10.01.2018 в 20:17
Никанор Дата: Среда, 10.01.2018, 23:11 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Che79, именно так, то что надо – все мои пожелания выполнены.
Я проверил на реальном файле разные варианты: когда сотрудник «Устроен с» (ячейка D9) и Срок отпуска включительно с (ячейка Е15):
Ваши формулы для диапазонов ячеек B20:В31 и D20:D31 в Вашем файле 180110.xlsx(24Kb) и в моём реальном «работают» – считают, так как надо!
Большое Вам спасибо, что подправили не совсем чётко сформулированный мной вопрос и написали формулы, такие как я хотел.


Сообщение отредактировал Никанор - Четверг, 11.01.2018, 12:50
 
Ответить
СообщениеChe79, именно так, то что надо – все мои пожелания выполнены.
Я проверил на реальном файле разные варианты: когда сотрудник «Устроен с» (ячейка D9) и Срок отпуска включительно с (ячейка Е15):
Ваши формулы для диапазонов ячеек B20:В31 и D20:D31 в Вашем файле 180110.xlsx(24Kb) и в моём реальном «работают» – считают, так как надо!
Большое Вам спасибо, что подправили не совсем чётко сформулированный мной вопрос и написали формулы, такие как я хотел.

Автор - Никанор
Дата добавления - 10.01.2018 в 23:11
Никанор Дата: Четверг, 11.01.2018, 12:44 | Сообщение № 18
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
gling, Здравствуйте!
Извините что отвечаю не сразу.
Только сейчас проверил с разными вариантами Ваш файл 9222373.xlsx(35Kb) Вар2.
Я проверил разные варианты: когда сотрудник «Устроен с» (ячейка D9) и Срок отпуска включительно с (ячейка Е15):
Ваши формулы для диапазонов ячеек B20:В31 дают правильный результат.
Формула в ячейке B20
Код
=ЕСЛИ(И(ДАТАМЕС($D$9;12)>$E$15;МЕСЯЦ($D$9)<=МЕСЯЦ($E$15)-(13-СТРОКА(A1)));ЕСЛИ(B19="";ТЕКСТ($D$9;"ММММ ГГГГ");ДАТАМЕС(B19;1));ЕСЛИ(ДАТАМЕС($D$9;12)>$E$15;"";ТЕКСТ(ДАТАМЕС($E$15;-(13-(СТРОКА(A1))));"ММММ ГГГГ")))
правильная.
И формула в ячейке D20 тоже правильная
Код
=ЕСЛИ(B20="";"";ДЕНЬ(КОНМЕСЯЦА(--B20;0))-СУММПРОИЗВ(--(ТЕКСТ($K$20:$AF$31;"ММММ ГГГГ")=ТЕКСТ(--B20;"ММММ ГГГГ"))))


Большое спасибо за помощь, правильный ответ и уделённое моему вопросу время.
Позвольте задать Вам вопрос: Почему в трёх таблицах для расчёта количества дней отпуска за 2016 – 2018 годы в диапазоне ячеек I20:I31 (таблица за 2016 год); R20:R31 (таблица за 2017 год) и Z20:Z31 (таблица за 2018 год) стоит Условное форматирование и ячейки где указыны месяцы с сентября по декабрь выделены красным цветом?
 
Ответить
Сообщениеgling, Здравствуйте!
Извините что отвечаю не сразу.
Только сейчас проверил с разными вариантами Ваш файл 9222373.xlsx(35Kb) Вар2.
Я проверил разные варианты: когда сотрудник «Устроен с» (ячейка D9) и Срок отпуска включительно с (ячейка Е15):
Ваши формулы для диапазонов ячеек B20:В31 дают правильный результат.
Формула в ячейке B20
Код
=ЕСЛИ(И(ДАТАМЕС($D$9;12)>$E$15;МЕСЯЦ($D$9)<=МЕСЯЦ($E$15)-(13-СТРОКА(A1)));ЕСЛИ(B19="";ТЕКСТ($D$9;"ММММ ГГГГ");ДАТАМЕС(B19;1));ЕСЛИ(ДАТАМЕС($D$9;12)>$E$15;"";ТЕКСТ(ДАТАМЕС($E$15;-(13-(СТРОКА(A1))));"ММММ ГГГГ")))
правильная.
И формула в ячейке D20 тоже правильная
Код
=ЕСЛИ(B20="";"";ДЕНЬ(КОНМЕСЯЦА(--B20;0))-СУММПРОИЗВ(--(ТЕКСТ($K$20:$AF$31;"ММММ ГГГГ")=ТЕКСТ(--B20;"ММММ ГГГГ"))))


Большое спасибо за помощь, правильный ответ и уделённое моему вопросу время.
Позвольте задать Вам вопрос: Почему в трёх таблицах для расчёта количества дней отпуска за 2016 – 2018 годы в диапазоне ячеек I20:I31 (таблица за 2016 год); R20:R31 (таблица за 2017 год) и Z20:Z31 (таблица за 2018 год) стоит Условное форматирование и ячейки где указыны месяцы с сентября по декабрь выделены красным цветом?

Автор - Никанор
Дата добавления - 11.01.2018 в 12:44
Pelena Дата: Четверг, 11.01.2018, 13:34 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Условное форматирование было в Вашем исходном файле. Зачем оно, Вам виднее :)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеУсловное форматирование было в Вашем исходном файле. Зачем оно, Вам виднее :)

Автор - Pelena
Дата добавления - 11.01.2018 в 13:34
Никанор Дата: Четверг, 11.01.2018, 13:57 | Сообщение № 20
Группа: Проверенные
Ранг: Обитатель
Сообщений: 495
Репутация: 12 ±
Замечаний: 0% ±

Excel 2007
Спасибо за помощь.
Меня всё устраивает я доволен формулами которые мне написали.

Единственное что хотелось бы узнать, можно ли выполнить следующее условие для диапазона ячеек D20:D31 (Календарные дни минус праздничные):
ЕСЛИ сотрудник работал меньше 12-ти месяцев (или меньше 1 года) – в ячейке В10 меньше 12 месяцев или РАЗНДАТ($D$9;$E$15) меньше 12-ти месяцев (или меньше 1 года) и в ячейке D9 (Устроен с:) не первое число месяца, что бы в диапазоне ячеек D20:D31 (Календарные дни минус праздничные) считало количество дней в месяце трудоустройства.
Сейчас если в ячейке D9 написать (Устроен с:) 31 мая, то в диапазоне ячеек D20:D31 результат будет количество дней в мае: 31 день минус 3 дня праздничных = 28, а должно быть 1 день. Так как устроился в последний день.
А если это моё условие невозможно выполнить, то я буду вручную изменять количество дней в диапазоне ячеек D20:D31 если работал меньше 12-ти месяцев и устроился не первого числа.
Но хотелось бы это сделать с помощью формулы.


Сообщение отредактировал Никанор - Четверг, 11.01.2018, 14:54
 
Ответить
СообщениеСпасибо за помощь.
Меня всё устраивает я доволен формулами которые мне написали.

Единственное что хотелось бы узнать, можно ли выполнить следующее условие для диапазона ячеек D20:D31 (Календарные дни минус праздничные):
ЕСЛИ сотрудник работал меньше 12-ти месяцев (или меньше 1 года) – в ячейке В10 меньше 12 месяцев или РАЗНДАТ($D$9;$E$15) меньше 12-ти месяцев (или меньше 1 года) и в ячейке D9 (Устроен с:) не первое число месяца, что бы в диапазоне ячеек D20:D31 (Календарные дни минус праздничные) считало количество дней в месяце трудоустройства.
Сейчас если в ячейке D9 написать (Устроен с:) 31 мая, то в диапазоне ячеек D20:D31 результат будет количество дней в мае: 31 день минус 3 дня праздничных = 28, а должно быть 1 день. Так как устроился в последний день.
А если это моё условие невозможно выполнить, то я буду вручную изменять количество дней в диапазоне ячеек D20:D31 если работал меньше 12-ти месяцев и устроился не первого числа.
Но хотелось бы это сделать с помощью формулы.

Автор - Никанор
Дата добавления - 11.01.2018 в 13:57
Мир MS Excel » Вопросы и решения » Вопросы по Excel » количество дней для расчёта отпускных (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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