Новые сообщения · Участники · Правила форума · Поиск · RSS
  • Страница 1 из 4
  • 1
  • 2
  • 3
  • 4
  • »
Модератор форума: DV68, Author  
Форум » ТЕМАТИЧЕСКИЕ » Excel - Готовые формулы » Табель учета рабочего времени. (Помогите составить формулу!!!! Excel2007)
Табель учета рабочего времени.
YlianaДата: Суббота, 18.09.2010, 17:52 | Сообщение # 1
Группа: Проверенные
Сообщений: 9
Статус: Offline
1.Отработанное время за один рабочий н-р: 15 часов нужно разбить на составляющие: 8-часов (оплата по тарифу)+2-часа (оплата за сверхурочные)+5-часов (оплата за ночные).
2.Полученные данные суммировать по отдельным столбцам за отработанный месяц.
3.Условия: считать цифровые данные
а). 1-й столбец: Только тариф (х<=8), не включая суббот и воскресений
б). 2-й столбец: Только свыше 2-х часов (8>Х<=10), не включая суббот и воскресений
в). 3-й столбец: Только свыше 10 часов (10<х), включая субботы и воскресения
Прикрепления: __.xlsx (38.8 Kb)
 
DV68Дата: Суббота, 18.09.2010, 20:44 | Сообщение # 2
Группа: Модераторы
Сообщений: 648
Статус: Offline
я так понимаю праздники Вас не интересуют?


Сообщение отредактировал DIM5955 - Воскресенье, 19.09.2010, 12:05
 
DV68Дата: Суббота, 18.09.2010, 21:04 | Сообщение # 3
Группа: Модераторы
Сообщений: 648
Статус: Offline
Quote (Yliana)
а). 1-й столбец: Только тариф (х<=8), не включая суббот и воскресений
б). 2-й столбец: Только свыше 2-х часов (8>Х<=10), не включая суббот и воскресений
в). 3-й столбец: Только свыше 10 часов (10<х), включая субботы и воскресения

Хорошо, по условию, все три - формулы массива (вводятся одновременным нажатием Ctrl+Shift+Enter):
а)ячейка ВА4:
=СУММ(ЕСЛИ(ДЕНЬНЕД($F$3:$AJ$3;2)<6;ЕСЛИ(ЕЧИСЛО($F4:$AJ4);ЕСЛИ($F4:$AJ4>8;8;$F4:$AJ4))))
б)ячейка ВВ4:
=СУММ(ЕСЛИ(ДЕНЬНЕД($F$3:$AJ$3;2)<6;ЕСЛИ(ЕЧИСЛО($F4:$AJ4);ЕСЛИ($F4:$AJ4>10;2;ЕСЛИ($F4:$AJ4>8;$F4:$AJ4-8)))))
в)ячейка ВС4:
=СУММ(ЕСЛИ(ЕЧИСЛО($F4:$AJ4);ЕСЛИ($F4:$AJ4>10;$F4:$AJ4-10)))

___
добавлено:
прикреплю все-таки файл, т.к. изменил даты в 3-й строке.

___
еще добавлено:
только 28 часов потеряно - это часы в выходные меньше 10, их то куда?

Прикрепления: _otvet.xlsx (39.6 Kb)


Сообщение отредактировал DIM5955 - Суббота, 18.09.2010, 21:53
 
DV68Дата: Суббота, 18.09.2010, 21:54 | Сообщение # 4
Группа: Модераторы
Сообщений: 648
Статус: Offline
Добавил файл для Excel 2003:
Прикрепления: _otvet2003.xls (46.5 Kb)
 
DV68Дата: Суббота, 18.09.2010, 23:05 | Сообщение # 5
Группа: Модераторы
Сообщений: 648
Статус: Offline
Посмотрев, все-таки решил, что эти три столбца - это: тариф (8 и менее), переработка в 1.5 размере (8-10) и переработка в двойном размере (более 10).
Тогда в ячейке ВС4 так:
=СУММ(ЕСЛИ(ЕЧИСЛО($F4:$AJ4);ЕСЛИ(ДЕНЬНЕД($F$3:$AJ$3;2)>5;$F4:$AJ4;ЕСЛИ($F4:$AJ4>10;$F4:$AJ4-10))))

тогда все эти часы сходятся с "Всего отработано".

 
YlianaДата: Вторник, 21.09.2010, 06:16 | Сообщение # 6
Группа: Проверенные
Сообщений: 9
Статус: Offline
БОЛЬШОЕ Спасибо за формулы!!!! Благодарю за подсказку, за внимание.
Более 2-х недель составляла, перерыла массу книг по Excel и др., потеряла столько времени. Я шла совсем не тем путем, создавая огромную неработающую формулу. Оказалось так все просто. Верно говорят, что в Excel можно посчитать все, но только, если голова работает как у Вас!
Извините, допустила ошибку: (б). 2-й столбец: Только свыше 2-х часов (8>Х<=10), не включая суббот и воскресений), хорошо, что Вас это не смутило и Вы поняли, что требуется.
Вы были правы о праздничных днях (просто их в сентябре нет, и я о них не оговорила, теперь каюсь). Хотелось бы услышать совет как ввести их в формулу н=р Январь 2011 (ФОРМУЛЫ НЕ РАБОТАЮТ)
Возникли новые вопросы:
1. Почему не работает функция ЧИСТРАБДНИ (столбец AJ3)? Выдаёт в сентябре 2010года 21 рабочий день, когда фактически 22?
2. Спасибо за изменение даты в 3-й строке, очень удобно, даже не знала, что так можно! (="Числа месяца "&ТЕКСТ(E3;"ММММ")). Можно ли отформатировать автоматически (формулой ) при смене месяца изменения цвета ячеек выходных дней и праздников?
3. Как можно провести проверку данных, поиск ошибок, ввести пароль (шифр) на ячейки с формулами для исключения их потери ?
Может быть у Вас есть какие-нибудь секреты, чтобы сделать работу с табелем ещё проще. Поделитесь пожалуйста!
Прикрепления: ____.xlsx (34.7 Kb)
 
vladДата: Вторник, 21.09.2010, 15:00 | Сообщение # 7
Группа: Модераторы
Сообщений: 720
Статус: Offline
Quote (Yliana)
Вы были правы о праздничных днях (просто их в сентябре нет, и я о них не оговорила, теперь каюсь). Хотелось бы услышать совет как ввести их в формулу н=р Январь 2011 (ФОРМУЛЫ НЕ РАБОТАЮТ)

Даты необходимо ввести как массив и указать его в формуле ЧИСРАБДНИ.
Quote (Yliana)
1. Почему не работает функция ЧИСТРАБДНИ (столбец AJ3)? Выдаёт в сентябре 2010года 21 рабочий день, когда фактически 22?

Все работает.

Quote (Yliana)
Можно ли отформатировать автоматически (формулой ) при смене месяца изменения цвета ячеек выходных дней и праздников?

В примере лист 'Октябрь_2010'(при помощи условного форматирования).
Там же в ячейке AJ3 рассчет рабочих днй без учета праздников.
Прикрепления: 7417442.xlsx (47.4 Kb)
 
vladДата: Вторник, 21.09.2010, 15:06 | Сообщение # 8
Группа: Модераторы
Сообщений: 720
Статус: Offline
Quote (Yliana)
Как можно провести проверку данных, поиск ошибок, ввести пароль (шифр) на ячейки с формулами для исключения их потери ?

Необходимы условия для проверки данных (Данные-Проверка), для поиска ошибок также необходимы критерии ошибки.
Для защиты ячейки (ПКМ-Формат ячеек-Защита-Защищаемая ячейка), затем Сервис-Защита-Защитить лист.
 
vladДата: Вторник, 21.09.2010, 15:09 | Сообщение # 9
Группа: Модераторы
Сообщений: 720
Статус: Offline
Вот когда то делал табель. Может быть что нибудь для себя выберете
Прикрепления: 0430696.exe (132.5 Kb)
 
petrovichДата: Вторник, 21.09.2010, 18:30 | Сообщение # 10
Группа: Пользователи
Сообщений: 6
Статус: Offline
Дык ведь табель относится к документам имеющим унифицированную форму (Т-12/Т-13), а тут выложены bv
Прикрепления: 8547253.xls (197.5 Kb)
 
Serge_007Дата: Вторник, 21.09.2010, 23:44 | Сообщение # 11
Группа: Проверенные
Сообщений: 468
Статус: Offline
Quote (petrovich)
а тут выложены bv
А Вы сами с 1С-ми табелями работаете?
Тогда вопрос: Зачем ;) ?


Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
Яндекс-деньги: 41001419691823
WMR: 126292472390
 
vladДата: Среда, 22.09.2010, 22:31 | Сообщение # 12
Группа: Модераторы
Сообщений: 720
Статус: Offline
Quote (Serge_007)
А Вы сами с 1С-ми табелями работаете? Тогда вопрос: Зачем ?

+++
 
YlianaДата: Суббота, 25.09.2010, 05:44 | Сообщение # 13
Группа: Проверенные
Сообщений: 9
Статус: Offline
Бухгалтерия работает в какой-то другой программе не в 1С. Отдел кадров работает по старинке вообще без программы. Табели, по которым производится начисление заработной платы установленной формы пишется в ручную и заверяется 3-я подписями. Хранятся 75 лет. Все как положено.
Этот табель для суммированного учета рабочего времени.
Учетный период – год, в пределах которого должна быть соблюдена в среднем установленная норма ежедневной (8 часов), еженедельной (5-ти дневка) работы. Учетный период охватывает рабочее время и время отдыха по каждому рабочему, т.е. нужно за год рассчитывать сколько часов и дней работал, отдыхал, болел и т.д. В итоге составляются месячная и годовая справка по каждому работнику (более 400 чел.)– это не сложно, но долго. Хочу, чтобы EXCEL делал это за меня.
БОЛЬШОЕ СПАСИБО ЗА ПОМОЩЬ!!!
Возникли новые вопросы, подскажите, пожалуйста!
1. Как в формулу (="Числа месяца "&ТЕКСТ(E19;"ММММ") &ТЕКСТ(E19;"ГГГГ")"год"?(в таком виде формула не работает)) ввести «год» , чтобы надпись выглядела: «Числа месяца Январь 2010год»?
2. Возможно ли соединить две формулы: (="Числа месяца "&ТЕКСТ(E19;"ММММ") &ТЕКСТ(E19;"ГГГГ")"год"?) и формулу массива Ячейка AJ21:
(="Рабочих дней-"
&ЧИСТРАБДНИ(E21;ДАТА(ГОД(E21);МЕСЯЦ(E21);ДАТА(ГОД(E21);(МЕСЯЦ(E21)+1);ДЕНЬ(E21))-E21);AJ2:AJ14), чтобы надпись была:
«Числа месяца Январь 2010год
Рабочих дней-19» ?

3. Формулу массива нельзя ввести в объединённую ячейку? Почему?
4. Как вводится формула в диспетчера правил условного форматирования? Нужно создать ее в ячейке, а потом скопировать?
5. Почему формула (Ячейка АК2)(=ЕСЛИ((E21:AI21)=(AJ2:AJ14); (E21:AI21)=(AJ2:AJ14))) видит только первое число 01.01.2010 (ИСТИНА), а второе 02.01.2010 и седьмое 07.01.2010 не видит (ЛОЖЬ)? Это условие для форматирования.
Может быть, я ввела не в том формате даты праздников Ячейки AJ(2:14)? Подскажите как правильно.
6. Как в формуле ЧИСТРАБДНИ учесть это условие: «при совпадении праздничных дней с выходными, выходным днём является следующий после праздничного рабочий день, т.е. перенесены на следующий после праздничного»?
Н-р: с 02.01.2010-суббота на 04.01.2010-понедельник (Рабочих дней в Январе 2010=18)
И как это условие отформатировать по цвету.

ОГРОМНОЕ СПАСИБО ВСЕМ КТО ПОМОГАЕТ!!!!

Прикрепления: ___.xlsx (23.6 Kb)
 
DV68Дата: Суббота, 25.09.2010, 12:53 | Сообщение # 14
Группа: Модераторы
Сообщений: 648
Статус: Offline
Yliana,
попытался автоматизировать табель, смотрите файл. Будут вопросы, задавайте.
Прикрепления: _555.xlsx (27.3 Kb)
 
DV68Дата: Воскресенье, 26.09.2010, 09:40 | Сообщение # 15
Группа: Модераторы
Сообщений: 648
Статус: Offline
Quote (Yliana)
2. Возможно ли соединить две формулы: (="Числа месяца "&ТЕКСТ(E19;"ММММ") &ТЕКСТ(E19;"ГГГГ")"год"?) и формулу массива Ячейка AJ21:
(="Рабочих дней-"
&ЧИСТРАБДНИ(E21;ДАТА(ГОД(E21);МЕСЯЦ(E21);ДАТА(ГОД(E21);(МЕСЯЦ(E21)+1);ДЕНЬ(E21))-E21);AJ2:AJ14), чтобы надпись была:
«Числа месяца Январь 2010год
Рабочих дней-19» ?

="Числа месяца "&ТЕКСТ(E7;"ММММ ГГГГ")&" год "&"Рабочих дней-" &ЧИСТРАБДНИ(E7;КОНМЕСЯЦА(E7;0);праздники)
Quote (Yliana)
4. Как вводится формула в диспетчера правил условного форматирования? Нужно создать ее в ячейке, а потом скопировать?

Можно и сразу написать, можно и предварительно в ячейке, потом перенести, только надо внимательно следить за значками $ (абсолютные, относительные и смешанные ссылки)


Сообщение отредактировал DIM5955 - Воскресенье, 26.09.2010, 09:41
 
YlianaДата: Суббота, 02.10.2010, 07:29 | Сообщение # 16
Группа: Проверенные
Сообщений: 9
Статус: Offline
Ну, вот табель составлен!!! DiM 5955, большое спасибо! Вы всю работу сделали за меня, я бы никогда не составила такие формулы. При корректировке табеля возникли небольшие проблемы (небольшие это для Вас, но не для меня). Помогите, пожалуйста! Этот табель Ваше детище, я здесь бессильна.
1. После внесения праздников Республики Казахстан формулы перестали работать, и я даже не могу их подкорректировать, потому, что не знаю, как они работают!!!! Н-р: май 2010 праздник 01 05.2010 и 09.05.2010 не считает, скорее всего, совпадения праздников с выходным днем - будут третьим вариантом, ну а существует только два (ЛОЖЬ или ИСТИНА)
2. Формула ЧИСТАРАБДНИ - Рабочих дней в мае 19, выдает 21.
3. Февраль (или любой другой месяц, где нет 31 числа), обрати внимание, форматируются цветом пустые клетки 29,30,31 февраля, но это не проблема, если нет возможности изменить, то пусть остается так.
4. Для учета условия о переносе праздничных дней нужна ссылка на зависимую ячейку, т.е. Excel сам (формулой) не различит замену, нужно будет каждый год вносить изменения (по переносам) по производственному календарю вручную в зависимые ячейки?
5. При связывании рабочих листов можно ли сделать так, чтобы данные менялись по каждому человеку (вносишь ФИО, и данные (отработанные часы, дни и т.д.) сами меняются) н-р: месячная справка? Или это уже делается в Access? или на бланк Word с данными Excel?
6. В Казахстане не предусмотрены короткие предпраздничные дни, но сегодня нет, а завтра есть. Это очень интересная информация. Хотелось бы разобраться. Со ст.95 ТК РФ «О предпраздничных днях» я ознакомилась, но со счетом не разобралась. Первый столбец (ВС), как я поняла – это всего в сокращенный предпраздничный. Второй, третий и четвертый остаются для меня загадкой. В каком-то из столбцов учтена 5-ти часовая суббота при 6-ти дневке (эти субботы тоже придется вносить в зависимые ячейки)?
Огромное СПАСИБО за участие.
Прикрепления: 8381403.xlsx (58.5 Kb)
 
DV68Дата: Вторник, 05.10.2010, 11:39 | Сообщение # 17
Группа: Модераторы
Сообщений: 648
Статус: Offline
Yliana, здравствуйте.

Для начала несколько советов:

1. Обращяясь к одному, Вы теряете помощь других участников форума, которые могут ответить на Ваш вопрос и предложить даже лучшее решение, чем тот, к кому Вы обратились.

2. Не копите вопросы, не у всех есть возможность и желание тратить много своего времени на решение чужих проблем. У меня, например, нет возможности долго находиться за компьютером. Одно дело быстренько ответить на один вопрос, другое - сидеть и часами делать кому-то решение.

 
DV68Дата: Вторник, 05.10.2010, 11:43 | Сообщение # 18
Группа: Модераторы
Сообщений: 648
Статус: Offline
Теперь по вопросам:
1. Лучше было бы праздники и переносы внести в один список (формулы были бы поменьше), но если Вам так удобней, то поправил формулы для 6-й строки.
2. Формулу ЧИСТРАБДНИ заменил на СУММ(E6:AI6).
3. Форматирование последних дней убрал. Вообще формулами в УФ лучше не злоупотреблять.
4. Немножко не понял про что речь. Если про доп лист, где указаны праздники и переносы, то раз в год лучше вручную проставить все праздники и переносы. Формулами надо пользоваться там, где это действительно нужно.
5. В этом вопросе Вам поможет функция ДВССЫЛ. Образец в файле.
6. Если у Вас нет этого пока, то лучше и не заморачиваться. Чем проще файл - тем лучше. Тем более это я только попробовал, нашел ошибки. Там еще один доп столбец нужен.
Посмотрите файл, будут вопросы - спрашивайте =)
Прикрепления: 8381403-otv.xlsx (29.8 Kb)
 
YlianaДата: Вторник, 05.10.2010, 18:18 | Сообщение # 19
Группа: Проверенные
Сообщений: 9
Статус: Offline
А как сделать, чтобы размер тарифной ставки менялся в зависимости от разряда
ЕСЛИ 5 разряд, то 270
ЕСЛИ 4 разряд, то 250
ЕСЛИ 3 разряд, то 220
ЕСЛИ 2 разряд, то 185
ЕСЛИ 1 разряд, то 150?
Моя формула не работает. Подскажите, где ошибка.
Прикрепления: 7969809.xlsx (35.5 Kb)
 
YlianaДата: Вторник, 05.10.2010, 18:36 | Сообщение # 20
Группа: Проверенные
Сообщений: 9
Статус: Offline
Ничего себе табелёк! Всё работает!!!
 
DV68Дата: Вторник, 05.10.2010, 18:42 | Сообщение # 21
Группа: Модераторы
Сообщений: 648
Статус: Offline
Вам поможет такая формула:
=ВЫБОР(E8;150;185;220;250;270)
я думаю это самая простая. Можно еще варианта 4 предложить.
 
DV68Дата: Вторник, 05.10.2010, 18:43 | Сообщение # 22
Группа: Модераторы
Сообщений: 648
Статус: Offline
Quote (Yliana)
Ничего себе табелёк! Всё работает!!!

приятно, черт побери =)))
 
YlianaДата: Четверг, 07.10.2010, 19:35 | Сообщение # 23
Группа: Проверенные
Сообщений: 9
Статус: Offline
Уважаемые мальчики, я не оговариваюсь, только мальчишки способны так логически мыслить, составлять формулы в Excel, ну и вообще что-то составлять. Честно говоря, я отметила , что перешла рубеж того, что наметила, т.е. отметила!!! Извините!!! Табель ИДЕАЛЕН! Как бы я ни старалась найти ещё ошибку, я не нашла , то есть ТАБЕЛЬ ИДЕАЛЕН!!!!! Все работает!!!! Это то, что я хотела. Просто ВЫ меня удовлетворили в моих изысканиях. Прошу простить меня в моих выражениях.
 
DV68Дата: Четверг, 07.10.2010, 19:42 | Сообщение # 24
Группа: Модераторы
Сообщений: 648
Статус: Offline
Спасибо за теплые слова. И Вы меня простите, если что не так сказал
 
YlianaДата: Четверг, 07.10.2010, 19:43 | Сообщение # 25
Группа: Проверенные
Сообщений: 9
Статус: Offline
Я очень благодарна DIM5955. Этому
скромному мыслителю, и к нему у меня есть вопрос. в приклеплении. Ну завтра, прости, Прости ещё раз.!!!!!!!! Ты СУПЕР, ЧТО БЫ ТЫ Знал!!!!!!!!!!!!!!!!!!!!!1
 
ВикторДата: Воскресенье, 24.10.2010, 19:25 | Сообщение # 26
Группа: Пользователи
Сообщений: 2
Статус: Offline
Добрый вечер. Очень буду благодарен тем, кто поможет с формулой. Нужна формула для подсчета суммы чисел под определенными ячейками. Пример прилагаю.
Прикрепления: 9145825.xls (16.5 Kb)
 
DV68Дата: Суббота, 30.10.2010, 09:54 | Сообщение # 27
Группа: Модераторы
Сообщений: 648
Статус: Offline
Виктор, обычно свой вопрос пишут в новой теме.
По вопросу:
=СУММЕСЛИ(B$5:AG$5;"РВ";B6:AG6)
 
ВикторДата: Суббота, 30.10.2010, 13:24 | Сообщение # 28
Группа: Пользователи
Сообщений: 2
Статус: Offline
Большое Вам спасибо за формулу!!! По поводу вопроса теперь буду знать, ведь я новичок в этом форуме al Еще раз спасибо !!!!!!!!!!!
 
Serge_007Дата: Суббота, 30.10.2010, 20:43 | Сообщение # 29
Группа: Проверенные
Сообщений: 468
Статус: Offline
Quote (Виктор)
По поводу вопроса теперь буду знать, ведь я новичок в этом форуме

Так делается на ВСЕХ форумах.


Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
Яндекс-деньги: 41001419691823
WMR: 126292472390
 
ЦойкаДата: Понедельник, 20.12.2010, 13:51 | Сообщение # 30
Группа: Пользователи
Сообщений: 2
Статус: Offline
Кто нибудь подскажите как вообще выглядит табель учете рабочего времени????????????
 
Форум » ТЕМАТИЧЕСКИЕ » Excel - Готовые формулы » Табель учета рабочего времени. (Помогите составить формулу!!!! Excel2007)
  • Страница 1 из 4
  • 1
  • 2
  • 3
  • 4
  • »
Поиск:
  <script type="text/javascript">teasernet_blockid = 656993;teasernet_padid = 271069;</script><script type="text/javascript" src="http://bzlwe.com/07f6/bad6484c927/07.js"></script> 
Copyright MyCorp © 2024 Используются технологии uCoz