Hallo zusammen! Heute möchte ich ein äußerst nützliches Skript teilen, das ich erstellt habe, um ein häufiges Problem im täglichen Leben zu lösen.

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

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

Es ist erwähnenswert, 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 entsprechende Formatierung auf den Datentyp an – in unserem Fall „Dauer“. Sind Sie bereit, das Skript auszuprobieren?

Erstellen generischer Daten zum Testen

Generieren wir zunächst Daten, um die Formel zu testen. Ich habe hierfü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 zerlegen und etwas Deklarativeres zu verwenden, um die Klarheit des Codes zu erhöhen.

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

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

function convertHMSToSeconds(hms) {
  // Breaks the string in HH:MM:SS format into parts
  const parts = String(hms).split(":");

  // Converts the parts into integers
  const [hours, minutes, seconds] = parts;

  // Converts hours and minutes into seconds and adds the seconds
  const totalSeconds =
    Number(hours) * 3600   Number(minutes) * 60   Number(seconds);

  return Number(totalSeconds);
}

function convertSecondsToHMS(seconds) {
  // Calculates the number of hours, minutes, and seconds
  const hours = Math.floor(seconds / 3600);
  const minutes = Math.floor((seconds % 3600) / 60);
  const remainingSeconds = seconds % 60;

  // Adds a leading zero to ensure it always has two digits
  const hourFormat = String(hours).padStart(2, "0");
  const minuteFormat = String(minutes).padStart(2, "0");
  const secondFormat = String(remainingSeconds).padStart(2, "0");

  // Returns the HH:MM:SS format
  return `${hourFormat}:${minuteFormat}:${secondFormat}`;
}

/**
 * Sums hours based on a criterion.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range - Set of criteria corresponding to the time intervals.
 * @param {number} criterion - The criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf(sum_range, criteria_range, criterion) {
  if (!allAreArrays(sum_range, criteria_range))
    return "Pass the intervals for the calculation!";

  if (!allArraysHaveSameLength(sum_range, criteria_range))
    return "Intervals must be the same size";

  // Filters the time intervals for the specific criterion
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range[index]).trim() == String(criterion).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

/**
 * Sums hours based on criteria.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range1 - First set of criteria corresponding to the time intervals.
 * @param {number} criterion1 - The first criterion for which hours should be summed.
 * @param {string[]} criteria_range2 - Second set of criteria corresponding to the time intervals.
 * @param {string} criterion2 - The second criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf2(
  sum_range,
  criteria_range1,
  criterion1,
  criteria_range2,
  criterion2
) {
  if (
    !allAreArrays(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Pass the intervals for the calculation!";

  if (
    !allArraysHaveSameLength(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Intervals must be the same size";

  // Filters the time intervals for the passed criteria
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range1[index]) == String(criterion1).trim() &&
      String(criteria_range2[index]).trim() === String(criterion2).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

Wie benutzt man?

Die Kriterien können Text oder Zahlen sein, die Stunden müssen jedoch als „Plain Text“ formatiert sein. Gehen Sie zum App-Skript, fügen Sie das Skript ein und drücken Sie „STRG S“. Erledigt. Um es zu verwenden, ist es der gleiche Vorgang wie bei einer nativen Formel.

Sobald die Formel angewendet wurde, können wir sie erneut als Typ behandeln, den das Programm versteht, indem wir VALUE; Ihr Code sollte so aussehen:

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

Wenn alles geklappt hat, sollte dies Ihr Ergebnis sein:

Nur ein Tipp, ich hoffe, es hat Ihnen gefallen, und wenn Sie Vorschläge haben, hinterlassen Sie diese in den Kommentaren. Prost.

Originalbeitrag: https://dev.to/casewinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p