Всем привет! Сегодня я хочу поделиться суперполезным скриптом, который я создал для решения распространенной проблемы в повседневной жизни.

Если вы когда-нибудь пробовали суммировать «длительность» в Google Таблицах, вы могли заметить, что формулы СУММЕСЛИ и СУММЕСЛИ не работают для суммирования продолжительности событий или продуктов на основе определенных критериев. Это может быть препятствием в зависимости от типа расчета, который вам нужно выполнить. Но не волнуйтесь! Google Таблицы позволяют создавать сценарии на JavaScript и использовать их в качестве пользовательских формул.

В моем скрипте я создал два варианта: первый принимает один критерий, а второй — до двух. Я планирую улучшить это в будущем, чтобы сделать функцию еще более гибкой.

Следует отметить, что пользовательские формулы не возвращают значения, которые можно вычислить программой напрямую. Чтобы обойти эту проблему, вы можете обернуть результат функцией =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