여러분, 안녕하세요! 오늘은 일상생활에서 흔히 발생하는 문제를 해결하기 위해 제가 만든 매우 유용한 스크립트를 공유하고 싶습니다.

Google 스프레드시트에서 '기간' 합계를 계산해 본 적이 있다면 SUMIF 및 SUMIFS 수식이 특정 기준에 따라 이벤트 또는 제품의 기간을 합산하는 데 작동하지 않는다는 것을 알 수 있습니다. 이는 수행해야 하는 계산 유형에 따라 장애물이 될 수 있습니다. 하지만 걱정하지 마세요! Google 스프레드시트를 사용하면 자바스크립트로 스크립트를 만들고 이를 맞춤 수식으로 사용할 수 있습니다.

내 스크립트에서는 두 가지 변형을 만들었습니다. 첫 번째 변형은 하나의 기준을 허용하고 두 번째 변형은 최대 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