やあ、みんな！今日は、日常のよくある問題を解決するために作成した、非常に便利なスクリプトを共有したいと思います。

Google スプレッドシートで「期間」を追加しようとしたことがある方は、特定の基準に基づいてイベントや商品の期間を追加する場合、SUMIF 式と SUMIFS 式が機能しないことに気づいたかもしれません。実行する必要がある計算の種類によっては、これが障害となる場合があります。でも心配しないでください。 Google スプレッドシートを使用すると、JavaScript スクリプトを作成し、カスタム数式として使用できます。

私のスクリプトでは 2 つのバリエーションを作成しました。1 つ目は 1 つの基準を受け入れ、2 つ目は最大 2 つの基準を受け入れます。将来的にはこれを改善して、より柔軟な機能を提供する予定です。

カスタム数式はプログラムによって直接計算可能な値を返さないことを覚えておく価値があります。これを回避するには、結果を =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))

すべてがうまくいけば、次の結果になるはずです:

これは単なるヒントでした。気に入っていただければ幸いです。ご提案がある場合は、コメントに残してください。ハグ。