Hey Leute! Heute möchte ich ein äußerst nützliches Skript teilen, das ich zur Lösung eines alltäglichen Problems erstellt habe.

Wenn Sie jemals versucht haben, „Dauer“ in Google Sheets hinzuzufügen, ist Ihnen möglicherweise aufgefallen, dass die Formeln SUMIF und SUMIFS nicht zum Hinzufügen von Ereignis- oder Produktdauern basierend auf bestimmten Kriterien funktionieren. Dies kann je nach Art der Berechnung, die Sie durchführen müssen, ein Hindernis sein. Aber keine Sorge! Mit Google Sheets können Sie JavaScript-Skripte erstellen und diese als benutzerdefinierte Formeln verwenden.

In meinem Skript habe ich zwei Varianten erstellt: Die erste akzeptiert ein Kriterium und die zweite bis zu zwei. Ich habe vor, dies in Zukunft zu verbessern, um die Funktion noch flexibler zu gestalten.

Es ist zu beachten, dass benutzerdefinierte Formeln keine Werte zurückgeben, die direkt vom Programm berechnet werden können. Um dies zu umgehen, können Sie das Ergebnis mit der Funktion =VALUE() umschließen. Wenden Sie dann einfach die dem Datentyp entsprechende Formatierung an – in unserem Fall „Dauer“. Sind Sie bereit, das Skript auszuprobieren?

Erstellen allgemeiner Daten zum Testen

Generieren wir zunächst Daten, um die Formel zu testen. Ich habe dafür unseren Freund GPT verwendet.

Skript

Ich habe versucht, alles so gut wie möglich zu dokumentieren. Ich habe beschlossen, es in kleinere Funktionen zu unterteilen und etwas Deklarativeres zu verwenden, um die Klarheit des Codes zu erhöhen.

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);
}

Wie benutzt man?

Die Kriterien können Text oder Zahlen sein, aber die Zeiten müssen als „Klarer Text“ formatiert sein.

Gehen Sie zur Skript-App:

Fügen Sie das Skript ein und drücken Sie „STRG S“. Bereit. Die Verwendung ist der gleiche Vorgang wie bei einer nativen Formel.

Sobald die Formel angewendet wurde, können wir sie mithilfe von VALUE wieder als Typ behandeln, den das Programm versteht. Ihr Code sollte so aussehen:

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

Wenn alles gut gelaufen ist, sollte dies Ihr Ergebnis sein:

Es war nur ein Tipp, ich hoffe, es hat Ihnen gefallen, und wenn Sie Vorschläge haben, hinterlassen Sie diese im Kommentar. Umarmungen.