Início / coding / Perguntas / 79598783
Accepted
nightfire36
Asked: 2025-04-30 00:02:48 +0800 CST

Existe uma maneira de contar instâncias de um número específico em várias fórmulas?

  • 772

Tenho ajudado meu departamento de contabilidade, e eles dão horas extras para quem trabalha em turnos extras. Se você trabalhar 5 ou 7 dias seguidos em um determinado turno, você ganha um número extra de horas além das horas trabalhadas. Então, você pode ganhar 5*10 mais 4 horas adicionais, totalizando 54 horas, em vez de 50.

O problema é que a planilha utilizada registra os turnos de cada mês e soma os dias. Basicamente, em julho, por exemplo, uma pessoa trabalhou 5 e 7, então a célula de julho é marcada como "=5+7". Precisamos saber tanto o número de dias no turno quanto quantas horas de bônus conceder ao longo do ano.

Sei que existe uma maneira melhor de contabilizar isso, mas estamos presos à planilha antiga porque é assim que a contabilidade exige. Existe uma maneira de contar as vezes que uma célula contém 7 ou 5 e adicionar isso a uma nova coluna?

Uma determinada linha pode ter uma aparência semelhante a esta para um funcionário:

Julho Agosto Setembro
=5+7 5 = 7+7

Então, para esse funcionário, eu gostaria de três colunas que exibissem o total de dias (31), o número de turnos de 5 dias consecutivos (2) e turnos de 7 dias consecutivos (3).

Atualmente, eles estão fazendo isso manualmente, mas é muita coisa para fazer manualmente para os mais de 100 funcionários, e erros são cometidos. Também ficou claro que eles querem fazer os cálculos dessa forma em cada célula, então não consigo contornar isso. Tentei usar a função Cell, mas não sei se funciona para várias células ao mesmo tempo. Acho que poderia fazer um countif e usar uma função cell para analisar todos os 12 meses, mas isso parece deselegante.

excel

3 respostas

  1. Best Answer

    Supondo que essas sejam fórmulas verdadeiras e não textos que parecem fórmulas:

    =LET(
    y,A2:C2,
    ar,--TEXTSPLIT(TEXTJOIN("+",,IF(ISFORMULA(y),SUBSTITUTE(FORMULATEXT(y),"=",""),y)),"+"),
    HSTACK(SUM(ar),SUM(--(ar=5)),SUM(--(ar=7))))

    insira a descrição da imagem aqui

    • 2

  2. Outra solução:

    =LET(
   data, A2:C2,
   shifts, TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1)),
   VSTACK(
     GROUPBY(shifts, shifts, COUNT, , 0),
     HSTACK("Total", SUM(shifts))
   )
 )

    insira a descrição da imagem aqui

    Como funciona

    Primeiro, obtemos os turnos de n dias consecutivos dos dados A2:C2usando uma combinação de FORMULATEXTeREGEXEXTRACT

    insira a descrição da imagem aqui

    shifts ← TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1))

    Depois, costumamos GROUPBYagrupar esses valores por contagem.

    GROUPBY(shifts, shifts, COUNT, , 0)

    Finalmente o total é empilhado abaixo usandoVSTACK

    VSTACK(
  GROUPBY(shifts, shifts, COUNT, , 0),
  HSTACK("Total", SUM(shifts))
)

    Se você quiser que o resultado seja exibido horizontalmente, você pode usarTRANSPOSE

    =LET(
   data, A2:C2,
   shifts, TOCOL(--REGEXEXTRACT(CONCAT(IFNA(FORMULATEXT(data), data)), "\d", 1)),
   TRANSPOSE(VSTACK(
     GROUPBY(shifts, shifts, COUNT, , 0),
     HSTACK("Total", SUM(shifts))
   ))
 )

    insira a descrição da imagem aqui

    • 2

  3. Para cada linha de funcionário, você precisará de três fórmulas:

    1. Total de dias (assumindo que os dados estão nas colunas B a M para janeiro a dezembro):

      =SUMPRODUCT(B2:M2)

    2. Contagem de turnos de 5 dias :

      =SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " 5 ")))/LEN("5")

    3. Contagem de turnos de 7 dias :

      =SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " "))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B2:M2), "=", ""), "+", " "), " ", " 7 ")))/LEN("7")
    • 0

relate perguntas