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

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

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

カスタム数式はプログラムで直接計算できる値を返さないことに注意してください。これを回避するには、結果を =VALUE() 関数でラップします。次に、対応する書式設定をデータ型 (この場合は「期間」) に適用するだけです。スクリプトをチェックアウトする準備はできましたか?

テスト用の汎用データの作成

まず、数式をテストするためのデータを生成しましょう。これには、友人の GPT を使用しました。

スクリプト

私は可能な限りすべてを文書化しようとしました。コードをより明確にするために、それを小さな関数に分割し、より宣言的なものを使用することにしました。

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

使い方は？

条件にはテキストまたは数字を使用できますが、時間は「プレーン テキスト」として書式設定する必要があります。アプリ スクリプトに移動してスクリプトを貼り付け、「CTRL S」を押します。終わり。使い方はネイティブの数式と同じです。

式が適用されると、VALUE を使用してプログラムが理解できる型として再び扱うことができます。コードは次のようになります:

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

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

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

元の投稿: https://dev.to/caseWinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p