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

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

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

모든 것이 순조롭게 진행되었다면 다음과 같은 결과가 나올 것입니다.

팁이었는데 마음에 드셨으면 좋겠고, 제안사항이 있으시면 댓글로 남겨주세요. 포옹.