Компьютерный форум OSzone.net  

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)
-   Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)
-   -   [решено] Excel. Неправильное суммирование рабочих часов (http://forum.oszone.net/showthread.php?t=345010)

Tolea3 27-04-2020 17:00 2919185
Excel. Неправильное суммирование рабочих часов
 
Вложений: 1
На работе в отделе кадров много лет велся учет рабочего времени в таблице 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 27-04-2020 18:25 2919196
Я — сразу пас:
Скрытый текст

:).

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

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

Tolea3 27-04-2020 18:55 2919201
Цитата:
Цитата Iska
Перестать заниматься ерундой, начать изучать Microsoft Excel и использовать его должным образом. »
Это понятно тока я делаю не для себя а для простого человека. Открыл поставил буквы цифры и получил итог.

megaloman 27-04-2020 19:22 2919204
Вложений: 1
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 27-04-2020 21:17 2919219
Вложений: 1
Цитата:
Цитата megaloman
Прилагаю файл со своим примером, переименуйте его из txt в xlsm. »
Ваш пример отлично работает но не пойму как внедрить его в мой документ. Если можете внедрите пожалуйста вы. Да тут всё на молдавском языке. Колонка ,,АМ" где надо внедрить изменения имеет красный цвет текста.

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

Tolea3 28-04-2020 02:32 2919234
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 28-04-2020 03:29 2919236
Сам задал вопрос и сам решил. Нашёл как суммировать только красные цифры не зависимо с запитой или без но нашёл новую проблему. Если в ячейку написать цифру и она будет черной и потом изменить ей цвет на красный то формула не срабатывает сразу. Надо либо стереть значение и написать заново или сделать любые изменения в данном ряду.
Код:
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 28-04-2020 03:41 2919237
Цитата:
Цитата Tolea3
цифры которые написаны красным цветом? »
Поясните, что Вы имеете в виду? Цвет шрифта? Да, можно:
Код:
…
    If r.Font.ColorIndex = 3 Then ' Если цвет шрифта диапазона красный…
        …
    End If
…
Номера умолчальных цветов палитры, например, здесь: PatternColorIndex Property [Excel 2003 VBA Language Reference] | Microsoft Docs.

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

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

megaloman 28-04-2020 08:14 2919249
Вложений: 1
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 28-04-2020 13:25 2919273
megaloman, спасибо за идею. я спрошу человека как ему будет удобнее из двух вариантов и сделаю финальный вариант.

Iska 28-04-2020 17:20 2919288
Цитата:
Цитата Tolea3
но проблема остаётся. »
Цитата:
Цитата Iska
Научите нажимать F9 после изменения цвета шрифта. »


Время: 15:03.

Время: 15:03.
© OSzone.net 2001-