嘿伙计们！今天我想分享一个超级有用的脚本，它是我为解决常见的日常问题而创建的。

如果您曾经尝试在 Google 表格中添加“持续时间”，您可能已经注意到，SUMIF 和 SUMIFS 公式不适用于根据特定条件添加事件或产品持续时间。这可能会成为一个障碍，具体取决于您需要执行的计算类型。但别担心！ Google 表格可让您创建 JavaScript 脚本并将其用作自定义公式。

在我的脚本中，我创建了两种变体：第一个接受一个标准，第二个接受两个标准。我计划将来改进这一点，使功能更加灵活。

值得记住的是，自定义公式不会返回程序可直接计算的值。为了解决这个问题，您可以使用 =VALUE() 函数包装结果。然后，只需应用与数据类型相对应的格式 - 在我们的例子中为“持续时间”。准备好查看脚本了吗？

创建用于测试的通用数据

首先，让我们生成数据来测试公式。我为此使用了我们的朋友 GPT。

脚本

我尝试尽可能最好地记录一切。我决定将其分成更小的函数，并使用更具声明性的内容来提高代码的清晰度。

function todosSaoArrays(...arrays) {
  return arrays.every(Array.isArray);
}

function todosArraysTemOMesmoTamanho(...arrays) {
  const lengths = arrays.map((arr) => arr.length);
  return lengths.every((val) => val === lengths[0]);
}

function converterHMSParaSegundos(hms) {
  // Quebra a string do formato HH:MM:SS em partes
  const partes = String(hms).split(":");

  // Converte as partes em números inteiros
  const [horas, minutos, segundos] = partes;

  // Converte horas e minutos para segundos e soma com os segundos
  const totalSegundos =
    Number(horas) * 3600   Number(minutos) * 60   Number(segundos);

  return Number(totalSegundos);
}

function converterSegundosParaHMS(segundos) {
  // Calcula o número de horas, minutos e segundos
  const horas = Math.floor(segundos / 3600);
  const minutos = Math.floor((segundos % 3600) / 60);
  const segundosRestantes = segundos % 60;

  // Adiciona zero à esquerda para garantir que tenha sempre dois dígitos
  const formatoHoras = String(horas).padStart(2, "0");
  const formatoMinutos = String(minutos).padStart(2, "0");
  const formatoSegundos = String(segundosRestantes).padStart(2, "0");

  // Retorna o formato HH:MM:SS
  return `${formatoHoras}:${formatoMinutos}:${formatoSegundos}`;
}

/**
 * Soma as horas baseado um critério.
 *
 * @param {string[]} intervalo_soma - Conjunto de intervalos de tempo em formato HH:MM:SS.
 * @param {number[]} intervalo_de_criterios - Conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {number} criterio - O critério para o qual as horas devem ser somadas.
 * @returns {string} Soma das durações passadas, ou uma mensagem de erro.
 */
function somarHorasSe(intervalo_soma, intervalo_de_criterios, criterio) {
  if (!todosSaoArrays(intervalo_soma, intervalo_de_criterios))
    return "Passe os intervalos para o calculo!";

  if (!todosArraysTemOMesmoTamanho(intervalo_soma, intervalo_de_criterios))
    return "Os intervalos devem ter o mesmo tamanho";

  // Filtra os intervalos de tempo para o critério específico
  const horasParaSomar = intervalo_soma.filter(
    (linha, index) =>
      String(intervalo_de_criterios[index]).trim() == String(criterio).trim()
  );

  // Converte as horas filtradas para segundos
  const horasEmSegundos = horasParaSomar.map((n) =>
    converterHMSParaSegundos(n)
  );

  // Soma todos os segundos
  const somaDosSegundos = horasEmSegundos.reduce((acumulador, valorAtual) => {
    return acumulador   valorAtual;
  }, 0);

  // Converte o total de segundos de volta para o formato HH:MM:SS
  return converterSegundosParaHMS(somaDosSegundos);
}

/**
 * Soma as horas baseado em critérios.
 *
 * @param {string[]} intervalo_soma - Conjunto de intervalos de tempo em formato HH:MM:SS.
 * @param {number[]} intervalo_de_criterios1 -  Primeiro conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {number} criterio1 - O primeiro critério para o qual as horas devem ser somadas.
 * @param {string[]} intervalo_de_criterios2 -  Segundo conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {string} semestre - O segundo critério para o qual as horas devem ser somadas.
 * @returns {string} Soma das durações passadas, ou uma mensagem de erro.
 */
function somarHorasSe2(
  intervalo_soma,
  intervalo_de_criterios1,
  criterio1,
  intervalo_de_criterios2,
  criterio2
) {
  if (
    !todosSaoArrays(
      intervalo_soma,
      intervalo_de_criterios1,
      intervalo_de_criterios2
    )
  )
    return "Passe os intervalos para o calculo!";

  if (
    !todosArraysTemOMesmoTamanho(
      intervalo_soma,
      intervalo_de_criterios1,
      intervalo_de_criterios2
    )
  )
    return "Os intervalos devem ter o mesmo tamanho";

  // Filtra os intervalos de tempo para os critérios passados
  const horasParaSomar = intervalo_soma.filter(
    (linha, index) =>
      String(intervalo_de_criterios1[index]) == String(criterio1).trim() &&
      String(intervalo_de_criterios2[index]).trim() === String(criterio2).trim()
  );

  // Converte as horas filtradas para segundos
  const horasEmSegundos = horasParaSomar.map((n) =>
    converterHMSParaSegundos(n)
  );

  // Soma todos os segundos
  const somaDosSegundos = horasEmSegundos.reduce((acumulador, valorAtual) => {
    return acumulador   valorAtual;
  }, 0);

  // Converte o total de segundos de volta para o formato HH:MM:SS
  return converterSegundosParaHMS(somaDosSegundos);
}

如何使用？

条件可以是文本或数字，但时间格式必须为“纯文本”。

转到脚本应用程序：

粘贴脚本和“CTRL S”。准备好。使用它的过程与本机公式相同。

应用公式后，我们可以返回使用 VALUE 将其视为程序可以理解的类型，您的代码应如下所示：

=VALUE(somarHorasSe2($C$2:$C$11;$D$2:$D$11;C$14;$E$2:$E$11;$B15))

如果一切顺利，这应该是您的结果：

这只是一个提示，我希望你喜欢它，如果你有建议，请在评论中留下。拥抱。