[решено] Excel. Неправильное суммирование рабочих часов

[Tolea3]

На работе в отделе кадров много лет велся учет рабочего времени в таблице excel но число рабочих дней, доп. часы, выходные и тд они вычитывали в уме или на калькуляторе и писали в ячейку нужную цифру. Как то они узнали что я знаю excel и попросили сделать формулы что бы итоги вычитались автоматически. Все колонки рассчитывали правильно точь в точь, радости не было предела у той женщине. Но когда ввели данные на следующий месяц то нашли 1 баг, колонка с дополнительными часами (отработанные в выходные) неправильно суммирует часы. Если поставить на выходные дни 1,4 и 1,4 то в колонке итог получается 2,8 а надо чтобы вышло 3,20 (3 часа и 20 минут). Посмотрел пару видео уроков на ютубе по этой теме и единственное решение везде показывали что надо писать не 1,4 а 1:40 и чуток по шаманить с итоговой формулой. Данный метод рабочий но нам не подходит так как все ячейки у нас имеют общии формат что бы могли туда писать как и число как и букву (напривер в - выходной день) и если написать туда 1:40 ячейка меняет формат и на следующий месяц если нужно туда поставить букву то будет отображаться каракули вместо буквы. Надо менять обратно формат ячейки на общии а это непосильная задача для человека который годы вычитывал итоги в уме и писал туда цифру. Как сделать чтобы итог доп. рабочие часы 1,4 и 1,4 получалось 3,20?
P.S. исходную таблицу прикрепляю.

[Iska]

Я — сразу пас:
https://i.imgur.com/QMPcoez.png
:).

везде показывали что надо писать не 1,4 а 1:40 »
Ага. Потому что 1:40 — это один час сорок минут. А 1.4 — это 9 часов 36 минут.

Как сделать чтобы итог доп. рабочие часы 1,4 и 1,4 получалось 3,20? »
Перестать заниматься ерундой, начать изучать Microsoft Excel и использовать его должным образом.

[Tolea3]

Перестать заниматься ерундой, начать изучать Microsoft Excel и использовать его должным образом. »
Это понятно тока я делаю не для себя а для простого человека. Открыл поставил буквы цифры и получил итог.

[megaloman]

Tolea3, Ваша таблица мной не понята (молдавский, румынский?) и где надо суммировать - не знаю. ИМХО, надо определить для Ваших требований самодельную функцию, например:Function SumTimDec(rall As Range)
SumTimDec1 = 0
SumTimDec2 = 0
For Each r In rall
If IsNumeric(r) Then
SumTimDec1 = SumTimDec1 + Int(r)
SumTimDec2 = SumTimDec2 + (r - Int(r)) * 100
End If
Next
SumTimDec = SumTimDec1 + Int(SumTimDec2 / 60) + Round((SumTimDec2 Mod 60) / 100, 2)
End FunctionНедостаток - файл придется хранить как xlsm. Прилагаю файл со своим примером, переименуйте его из txt в xlsm.

[Tolea3]

Прилагаю файл со своим примером, переименуйте его из txt в xlsm. »
Ваш пример отлично работает но не пойму как внедрить его в мой документ. Если можете внедрите пожалуйста вы. Да тут всё на молдавском языке. Колонка ,,АМ" где надо внедрить изменения имеет красный цвет текста.

[megaloman]

Tolea3, Там какая-то сложная формула, я не разобрался в ее смысле. Тупо просуммировать моей функцией время в строке - я внедрил в Вашу таблицу. Функцию можно увидеть (у меня Excel 2010) в Лента -> Разработчик -> Visual Basic
Если это Вас не устраивает, я должен понимать алгоритм суммирования, чтобы что-то делать. Там куча Ваших таблиц, формулы размножаются стандартным образом.

[Tolea3]

megaloman, премного вам благодарен. Первый раз столкнулся в excel с элементами программирования но немного изменив код добился нужного результата. Ваш вариант суммировал все числа а мне надо было только те которые написаны с запитой так как целые числа суммируются в другом столбце и рассчитывается столько рабочих дней было у человека.
Раз пошла такая пьянка спрошу еще кое что. Человек внеся данные в таблицу иногда может ошибаться и написать дополнительные рабочее часы без запитой (например 6) и эти часы не будут взяты в итоговую колонку с доп. часами. Надо всегда написать вместо 6 например 6,01 и тогда будет все нормально. А можем ль мы сделать так что бы ваша функция брала в расчет те цифры которые написаны красным цветом? и не важно с запитой или без.

Function SumaCifrelorCuVirgula(rall As Range)
SumTimDec1 = 0
SumTimDec2 = 0
For Each r In rall
If IsNumeric(r) Then
If Int(r) <> r Then
SumTimDec1 = SumTimDec1 + Int(r)
SumTimDec2 = SumTimDec2 + (r - Int(r)) * 100
End If
End If
Next
SumaCifrelorCuVirgula = SumTimDec1 + Int(SumTimDec2 / 60) + Round((SumTimDec2 Mod 60) / 100, 2)
End Function

[Tolea3]

Сам задал вопрос и сам решил. Нашёл как суммировать только красные цифры не зависимо с запитой или без но нашёл новую проблему. Если в ячейку написать цифру и она будет черной и потом изменить ей цвет на красный то формула не срабатывает сразу. Надо либо стереть значение и написать заново или сделать любые изменения в данном ряду.

Function SumaCifrelorCuVirgula(rall As Range)
SumTimDec1 = 0
SumTimDec2 = 0
For Each r In rall
If r.Font.Color = vbRed Then
If IsNumeric(r) Then
SumTimDec1 = SumTimDec1 + Int(r)
SumTimDec2 = SumTimDec2 + (r - Int(r)) * 100
End If
End If
Next
SumaCifrelorCuVirgula = SumTimDec1 + Int(SumTimDec2 / 60) + Round((SumTimDec2 Mod 60) / 100, 2)
End Function

[Iska]

цифры которые написаны красным цветом? »
Поясните, что Вы имеете в виду? Цвет шрифта? Да, можно:
…
If r.Font.ColorIndex = 3 Then ' Если цвет шрифта диапазона красный…
…
End If
…
Номера умолчальных цветов палитры, например, здесь: PatternColorIndex Property [Excel 2003 VBA Language Reference] | Microsoft Docs (https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224997(v%3Doffice.11)).

но нашёл новую проблему. Если в ячейку написать цифру и она будет черной и потом изменить ей цвет на красный то формула не срабатывает сразу. Надо либо стереть значение и написать заново или сделать любые изменения в данном ряду. »
Это не проблема, это данность: изменение цвета шрифта не является изменением содержимого ячеек и не вызывает пересчёта. Научите нажимать F9 после изменения цвета шрифта.

[Tolea3]

Iska, работают оба варианта, и ваш и мой но проблема остаётся. Если например пишу цифру 5, изначально она черная, меняю цвет на красный и формула не срабатывает, надо стереть 5 и потом заново написать 5 или любую другую цифру и только после этого формула будет её суммировать.

[megaloman]

Tolea3, Основывать зависимость вычислений от дифференциации цвета штанов ячеек - плохая идея, так как: "изменение цвета шрифта не является изменением содержимого ячеек и не вызывает пересчёта"(Iska). Да, плюс к этому, если цвет ячейки случайно назначили не красным, а каким-нибудь красно-зелёным, получим не то что хотелось.
Могу предложить другую идею: основное время писать цифрами (7,50), а дополнительное - цифрами с + (7,50+) на конце. Вот доработанная функция.Function SumTimDec(rall As Range, Optional Flag As Integer = 1)
SumTimDec1 = 0
SumTimDec2 = 0
For Each r In rall
If IsNumeric(r) Then
If Flag = 1 And Right(r, 1) <> "+" Then
SumTimDec1 = SumTimDec1 + Int(r)
SumTimDec2 = SumTimDec2 + (r - Int(r)) * 100
End If
If Flag = 2 And Right(r, 1) = "+" Then
rr = CDec(r)
SumTimDec1 = SumTimDec1 + Int(rr)
SumTimDec2 = SumTimDec2 + (rr - Int(rr)) * 100
End If
End If
Next
SumTimDec = SumTimDec1 + Int(SumTimDec2 / 60) + Round((SumTimDec2 Mod 60) / 100, 2)
If Flag = 2 Then SumTimDec = CStr(SumTimDec) + "+"
End FunctionЧто касается цвета ячеек в зависимости от значения - есть условное форматирование. Пример прикрепляю.

[Tolea3]

megaloman, спасибо за идею. я спрошу человека как ему будет удобнее из двух вариантов и сделаю финальный вариант.

[Iska]

но проблема остаётся. »
Научите нажимать F9 после изменения цвета шрифта. »