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

Сара работает по одной и той же схеме смены каждую неделю, поэтому в ячейке, которая относится к понедельнику, у меня "8", по 8 часов отработано. Если Сара заказывает в этот понедельник выходной, я все равно хочу показать 8, чтобы показать, что ей заплатят за 8 часов, но я также хочу поставить H в той же ячейке, чтобы указать, что она принимает его как выходной.

Вне календаря, в другом столбце, я использовал функцию SUM для вычисления общего количества часов, отработанных за этот месяц.

В другом столбце рядом с этим у меня есть COUNTIF для числа раз, когда появляется буква «H», которая затем истощает ее отпускные на этот год.

Проблема в том, что если я введу "8 H" в одну ячейку, функция SUM не будет работать, и удалит 8 из ее общего количества месячных часов, что означает, что ей не будут платить, но она должна быть, но она считается H.

Существует ли формула для подсчета числа и буквы в одной ячейке и отображения их в соответствующих столбцах без пропуска данных?

Условное форматирование, похоже, не работает только с цветом заливки, поэтому я застрял.

Спасибо!

2 ответа2

1

Как это устроено:

  • Напишите H в ячейке A5 Sarah в A6 и John в A7 .
  • Введите эту формулу в ячейку B6 чтобы сосчитать праздники и заполнить.

    =SUMPRODUCT((LEN(B2:G2)-LEN(SUBSTITUTE(B2:G2,$A$5,"")))/LEN($A$5))
    
  • Для подсчета общих часов напишите эту формулу в ячейку D6 и заполните.

     =Sum(B2:G2)
    
  • Запишите эту формулу в ячейку C6 чтобы рассчитать часы отдыха. и заполнить вниз.

      =SUMPRODUCT(--SUBSTITUTE(B2:G2,$A$5," "))-D6
    
  • Введите эту формулу в ячейку E6 чтобы рассчитать общее количество часов и заполнить.

     =C6+D6
    

Вы получите полную иллюстрацию праздников, а также общие, праздничные и общие часы.

Замечания:

  • Так как ваши каникулы оплачиваются так, что в ячейке E6 я подвел итог Хол.Часы с генералом, в противном случае вы можете рассчитать заработную плату на генерала.

  • При необходимости измените ссылки на ячейки в формуле.

0

Попробуйте это решение: дважды щелкните по ячейке, в которой вы хотите указать общее количество праздничных часов, и вставьте:

= СУММ (ЕСЛИ (ЕЧИСЛО (FIND (G2, А2: Е5)), ЗНАЧЕНИЕ (ЛЕВЫЙ (А2: Е5, FIND (G2, А2: Е5)-1)), 0))

затем нажмите Ctrl + Shift + Enter, чтобы создать эту формулу массива. Измените формулу в соответствии с вашими диапазонами.

IMG # 1

Затем суммируйте праздничные и обычные часы.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .