нужна формула для расчета в Excel
надеюсь на помощь знатоков Excel:
имеется три графы в таблице учета рабочего времени: дата, дневное сальдо и сумма (в часах и минутах).
вторая графа имеет положительные числа (часы переработки), отрицательные (недоработанные часы), а так же пустые ячейки (например, в дни отпуска)
необходимо в четвертой графе посчитать часы переработки, начиная с конца к началу, но без учета недоработанных часов.
т..е. например, сейчас имеются 100 часов переработки.
после подсчета, описанного выше, будет видно, в какой день достигнется 100 часов в прошлом, т.к. отсчет от "сегодня" до какой-то даты в прошлом.
Приложите эксель-файл с примером.
пока что есть только пдф-файл
после конвертации в экселе останутся видимыми графы: дата, дневное сальдо, сумма
справа добавится графа сумма переработанных часов (бирюзовая маркировка) без учета недоработанных часов (желтая маркировка)
суммирование должно идти от последней даты в прошлое...число (красная маркировка) когда-то покажется, например, в 2022ом году и будет расти, конечно, дальше
Anhang
спасибо Вам большое! вторая картинка соответствует поставленной задаче.
я после работы еще раз посмотрю, кажется, у меня будет вопрос по формуле на третьей картинкe.
Пожалуйста. Я думаю, когда введёте её в ячейку, там станет всё понятно.
да, формула понятна и работает с обычными числами.
в эксель-файле после конвертации на данный момент в ячейках таблицы числа, а если нажать на ячейку, то там "ссылки" на те таблицы, из которых была сдела сводная, они в таком формате =Sheet107!I29
формула работает и там, однако только частично: она хоть и распознает негативные числа, но почему-то все равно отнимает их при суммировании.
видимо, нужно сначала ячейки привести в формат чисел, а не "ссылок", чтобы формула заработала правильно
и еще один вопрос: формула будет работать в формате обычных чисел до 100.
однако ее нужно как-то изменить, чтобы она работала в формате часов и минут, т.е. после сложения чисел после запятой, достигнув 60, она переходила в следующий час и соответственно, чтобы после запятой не было чисел выше 59?
если изменить нельзя, тo понадобится еще одна графа, где сначала прийдется работать с долями часа, где 15 минут считаются как 0,25 часа и в следующей графе переводить назад в часы и минуты?
дневное сальдо (как и графа сумма) уже в часах и минутах, но таблица этого не знает, поэтому формула начнёт суммировать их как обычные числа до 100, например
2,35 + 4,45
будут посчитаны как 6,80 (обычное число)
а должны быть кak 7,20 (в часах и минутах)
т.е. или в формуле где-то должно быть заложено про: сумма 60 после запятой = 1 до запятой или про формат hh:mm
или позже понадобится дополнительный специальный формат ячеек в графе "сумма переработанных без учета недоработанных часов" - но будет ли он работать по всей длине таблицы, если там уже заложена формула суммирования с условием (Ваша третья картинка в сообщении нр. 4)?
про работы с долями часа это было скорее рассуждение вслух на тот случай, если нет технической возможности получить опсанный выше результат суммирования (7,20 вместо 6,80)
формула С6/24 не подходит, т.к. время тогда искажается: вместо 3 ч 49 мин получается 3 ч 29 мин и 24 сек
потому что таблица думает 3,49 это 3 и 49 часа, а не 3 часа и 49 мин
3 целых 49 сотых часа или 3 часа и 29 минут 24 секунды. Время отображается в Экселе в виде ЧЧ:ММ:СС и никак иначе. Если есть запятая, то это десятичное число. Вы либо приводите данные к стандартным значениям, либо колдуйте над преобразованиями.
а есть ли такая формула, которая переводит 0,44 в 0:44?
другими словами, которая меняет вид записи, а не формаt (запятая заменяется на двоеточие)
Попробуйте поспрашивать у чата ГПТ. Конечно то, что и как Вы описали в первом сообщении он вряд ли сразу поймет, но вот наводящие или уточняющие, односложные вопросы, скорее всего поймет.
Вот я для Вас спросил.
а есть ли такая формула, которая переводит 0,44 в 0:44?
другими словами, которая меняет вид записи, а не формаt (запятая заменяется на двоеточие)
ответ
Да, можно использовать формулу для замены запятой на двоеточие и преобразования чисел в формат времени. Однако Excel по умолчанию воспринимает числа, представленные в десятичной форме, как время в днях, и требуется преобразование для того, чтобы отобразить это именно в виде "часы:минуты".
Для этого можно воспользоваться следующей формулой:
=TEXT(INT(A1)*60 + (A1-INT(A1))*100, "00:00")
Вот что делает эта формула:
INT(A1)— извлекает целую часть числа (часы).(A1-INT(A1))*100— извлекает дробную часть и преобразует её в минуты (умножает на 100, чтобы получить 44 из 0,44).TEXT(..., "00:00")— форматирует результат в виде "часы:минуты".
Пример:
- Если в ячейке A1 стоит 0,44, результат будет 0:44.
Таким образом, эта формула позволяет изменить формат записи числа на вид "часы:минуты", где целая часть числа — это часы, а дробная — минуты.
Zurück
Liste